VLOOKUP היא אחת הפונקציות הנפוצות ביותר ב- Excel. הוא מחפש ערך בטווח ומחזיר ערך מתאים במספר עמודה שצוין.
עכשיו נתקלתי בבעיה שבה הייתי צריך לחפש שורה שלמה ולהחזיר את הערכים בכל העמודות מהשורה הזו (במקום להחזיר ערך בודד).
אז הנה מה שהייתי צריך לעשות. במערך הנתונים הבא, היו לי שמות נציג המכירות והמכירות שהם ביצעו בארבעה רבעים בשנת 2012. הייתה לי תפריט נפתח עם השמות שלהם, ורציתי לחלץ את המכירות המקסימליות של נציג המכירות בארבעת הרבעונים האלה.
אני יכול למצוא 2 דרכים שונות לעשות זאת - באמצעות INDEX או VLOOKUP.
חיפוש כל השורה / העמודה באמצעות נוסחת INDEX
להלן הנוסחה שיצרתי לעשות זאת באמצעות אינדקס
= LARGE (INDEX ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
איך זה עובד:
תן קודם כל להסתכל על הפונקציה INDEX שעוטפת את הפונקציה LARGE.
= INDEX ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
בואו ננתח מקרוב את הטיעונים של הפונקציה INDEX:
- מערך - $ B $ 4: $ F $ 1
- מספר שורה - MATCH (H3, $ B $ 4: $ B $ 13,0)
- מספר עמודה - 0
שים לב שהשתמשתי במספר העמודות כ- 0.
הטריק כאן הוא שכאשר אתה משתמש במספר העמודות כ- 0, הוא מחזיר את כל הערכים בכל העמודות. אז אם אבחר בג'ון בתפריט הנפתח, נוסחת האינדקס תחזיר את כל 4 ערכי המכירה עבור ג'ון {91064,71690,67574,25427}.
עכשיו אני יכול להשתמש בפונקציה Large כדי לחלץ את הערך הגדול ביותר
טיפ מקצועי - השתמש במספר עמודה/שורה כ- 0 בנוסחת אינדקס כדי להחזיר את כל הערכים בעמודות/שורות.
חיפוש כל השורה / העמודה באמצעות נוסחת VLOOKUP
נוסחת אינדקס אמנם מסודרת, נקייה וחזקה, אך דרך VLOOKUP מעט מורכבת. זה בסופו של דבר גם הופך את הפונקציה לתנודתית. עם זאת, יש טריק מדהים שהייתי משתף בחלק זה. להלן הנוסחה:
= LARGE (VLOOKUP (H3, B4: F13, ROW (INDIRECT ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
איך זה עובד
- ROW (INDIRECT ("2:" & COUNTA ($ B $ 4: $ F $ 4))) - נוסחה זו מחזירה מערך {2; 3; 4; 5}. שים לב שמכיוון שהוא משתמש INDIRECT, הדבר הופך את הנוסחה הזו לתנודתית.
- VLOOKUP (H3, B4: F13, ROW (INDIRECT ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE) - הנה החלק הטוב ביותר. כאשר אתה מחבר אותם, הוא הופך ל- VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSE). עכשיו שים לב שבמקום מספר עמודה בודד, נתתי לו מערך של מספרי עמודות. ו- VLOOKUP בודק בצייתנות ערכים בכל העמודות האלה ומחזיר מערך.
- עכשיו פשוט השתמש בפונקציה LARGE כדי לחלץ את הערך הגדול ביותר.
זכור להשתמש ב- Control + Shift + Enter כדי להשתמש בנוסחה זו.
טיפ מקצוען - ב- VLOOKUP, במקום להשתמש במספר עמודה בודד, אם תשתמשו במערך של מספרי עמודות, הוא יחזיר מערך של ערכי חיפוש.
