יש לנו רשימה של הזמנות עם מספרים ושמות של סחורות. הייתי רוצה למשל לשלוף מהשולחן לפי מספר הזמנה את כל הסחורה הכלולה בו. פחות או יותר ככה:
תכונה נפלאה VLOOKUP (VLOOKUP) במצב כזה זה יעזור רק חלקית, כי הוא מסוגל לחלץ נתונים רק לפי ההתאמה הראשונה שנמצאה, כלומר רק ייתן לנו תפוחים. כדי למצוא ולחלץ את כל הפריטים מהטבלה, עדיף להשתמש בנוסחת מערך. כמו זה:
=מדד($B$2:$B$16;הכי פחות(IF($E$2=A2: A16;קו(B2:B16)-1;»»);קו()-5))
יש להזין אותו באופן הבא:
- בחר את התאים שבהם התוצאות אמורות להיות מוצגות (בדוגמה שלנו, זה הטווח D6:D20)
- enter (העתק את הנוסחה לתא הראשון) של הטווח
- ללחוץ Ctrl + משמרת + זן
חיסור יחידה בשבר STRING(B2:B16)-1 נעשה בגלל כותרת הטבלה. מאותה סיבה, כדי לפצות על השינוי בטווח המתקבל ביחס לזה המקורי, המספר חמש בשבר מופחת STRING()-5
כדי להסתיר את #NUM! שגיאה שתופיע בתאים ריקים בטווח המתקבל D6:D20, תוכל להשתמש בפונקציות בדיקת השגיאות IF ו-EOSH, ולהחליף את הנוסחה שלנו בנוסחה קצת יותר מורכבת:
=IF(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))
ב-Excel 2007 הופיעה פונקציית IFERROR נוחה יותר - היא מאפשרת לך לפתור את הבעיה בצורה קומפקטית יותר:
=IFERROR(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)
PS
בגרסה האנגלית של Excel, הפונקציות הללו ייראו כך:
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))
=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)
- שימוש בפונקציית VLOOKUP כדי למצוא נתונים בטבלה
- גרסה משופרת של הפונקציה VLOOKUP2 שיכולה לחפש בכל עמודה ולא רק בערך הראשון
- פונקציות VLOOKUP2 ו- VLOOKUP3 מהתוסף PLEX
- מהן נוסחאות מערך ולמה הן משמשות?