נוסחת EXCEL: INDEX ו- MATCH עם מספר קריטריונים

נוסחה כללית | _+_ | סיכום

לחיפוש ערכים עם INDEX ו- MATCH, תוך שימוש בקריטריונים מרובים, תוכל להשתמש בנוסחת מערך. בדוגמה המוצגת, הנוסחה ב- H8 היא:

{= INDEX (range1, MATCH (1,(A1=range2)*(B1=range3)*(C1=range4),0))}

הערה: זהו נוסחת מערך , ויש להזין אותו עם שליטה + shift + enter, למעט ב- Excel 365 .

הֶסבֵּר

זו נוסחה מתקדמת יותר. ליסודות, ראה כיצד להשתמש ב- INDEX וב- MATCH .

מצטיין שגיאה # לא

בדרך כלל, נוסחת INDEX MATCH מוגדרת עם ערכת MATCH שתסתכל בטווח של עמודות אחת ותספק התאמה המבוססת על קריטריונים נתונים. ללא שרשור ערכים ב- טור עוזר או בנוסחה עצמה, אין דרך לספק יותר מקריטריונים אחד.

נוסחה זו פועלת סביב מגבלה זו באמצעות היגיון בוליאני ליצור מַעֲרָך של אחד ואפסים לייצוג שורות התואמות את כל 3 הקריטריונים, ולאחר מכן השתמש ב- MATCH כדי להתאים את הראשון שנמצא. המערך הזמני של האפסים והאפסים נוצר בעזרת קטע זה:

 {= INDEX (E5:E11, MATCH (1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))}

כאן אנו משווים את הפריט ב- H5 מול כל הפריטים, הגודל ב- H6 מול כל הגדלים והצבע ב- H7 מול כל הצבעים. התוצאה הראשונית היא שלושה מערכים של תוצאות TRUE/FALSE כך:

 (H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)

עֵצָה: השתמש ב- F9 כדי לראות תוצאות אלו . פשוט בחר ביטוי בשורת הנוסחאות והקש F9.

פעולת המתמטיקה (כפל) הופכת את ערכי TRUE FALSE לשניות 1 ו 0:

כיצד להפוך את הפסקה להצטיין

 {TRUETRUETRUEFALSEFALSEFALSETRUE}*{FALSEFALSETRUEFALSEFALSETRUEFALSE}*{TRUEFALSETRUEFALSEFALSEFALSETRUE}

לאחר הכפל, יש לנו מערך יחיד כזה:

 {1110001}*{0010010}*{1010001}

אשר מוזנת לפונקציית MATCH כמערך החיפוש, עם ערך חיפוש של 1:

 {0010000}

בשלב זה, הנוסחה היא נוסחה סטנדרטית של INDEX MATCH. הפונקציה MATCH מחזירה 3 ל- INDEX:

  MATCH (1,{0010000})

ו- INDEX מחזירה תוצאה סופית של $ 17.00.

מערך ויזואליזציה

המערכים שהוסברו לעיל יכולים להיות קשים לדמיון. התמונה למטה מציגה את הרעיון הבסיסי. העמודות B, C ו- D מתאימות לנתונים בדוגמה. עמודה F נוצרת על ידי הכפלת שלוש העמודות יחד. זהו המערך שנמסר ל- MATCH.

INDEX ו- MATCH עם קריטריונים מרובים - ויזואליזציה של מערך

גרסה שאינה מערך

אפשר להוסיף נוסחה נוספת ל- INDEX, תוך הימנעות מהצורך להיכנס כנוסחת מערך עם הפקודה + shift + enter:

 = INDEX (E5:E11,3)

הפונקציה INDEX יכולה להתמודד עם מערכים באופן מקורי, כך שה- INDEX השני מתווסף רק כדי 'לתפוס' את המערך שנוצר עם פעולת ההיגיון הבוליאני ולהחזיר את אותו מערך שוב ל- MATCH. לשם כך, INDEX מוגדר עם אפס שורות ועמודה אחת. הטריק בשורת האפס גורם ל- INDEX להחזיר עמודה 1 מהמערך (שהיא ממילא כבר עמודה אחת).

מדוע שתרצה את גרסת ה- non-array? לפעמים אנשים שוכחים להזין נוסחת מערך עם הפקודה + shift + enter והנוסחה מחזירה תוצאה לא נכונה. אז, נוסחה שאינה מערך היא יותר 'חסינת כדורים'. עם זאת, הפשרה היא נוסחה מורכבת יותר.

הערה: ב Excel 365 , אין צורך להזין נוסחאות מערך בצורה מיוחדת.