לְהִצטַיֵן

קבל התאמה שנייה עם INDEX / MATCH

Get Nth Match With Index Match

נוסחת Excel: קבל התאמה n עם INDEX / MATCHנוסחה כללית | _+_ | סיכום

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





{= INDEX (array, SMALL ( IF (vals=val, ROW (vals)- ROW ( INDEX (vals,1,1))+1),nth))}

איפה טווחים בשם הם אמץ (D4: D11), תְעוּדַת זֶהוּת (I3) וכן מזהים (C4: C11).

שים לב שזהו נוסחת מערך ויש להזין אותו באמצעות Control + Shift + Enter.





הֶסבֵּר

בבסיס, נוסחה זו היא פשוט נוסחת INDEX שמחזירה את הערך במערך במיקום נתון. הערך עבור n מסופק בעמודה H, וכל העבודה 'הכבדה' שהנוסחה עושה היא להבין את השורה שממנה ניתן לאחזר ערך, כאשר השורה מתאימה להתאמה 'n'.

הפונקציה IF עושה את העבודה כדי להבין אילו שורות מכילות התאמה, והפונקציה SMALL מחזירה את הערך ה- n מהרשימה הזו. בתוך IF, המבחן הלוגי הוא:



 
{= INDEX (amts, SMALL ( IF (ids=id, ROW (ids)- ROW ( INDEX (ids,1,1))+1),H6))}

מה שמניב מערך זה:

כיצד לספור את מספר התווים בתא
 
ids=id

שים לב לזהות הלקוח במיקומים 1 ו -4, המופיעים כ- TRUE. הארגומנט 'ערך אם נכון' ב- IF יוצר רשימה של מספרי שורות יחסיים עם הביטוי הזה:

 
{TRUEFALSEFALSETRUEFALSEFALSEFALSE}

שמייצר מערך זה:

 
 ROW (ids)- ROW ( INDEX (ids,1,1))+1

מערך זה 'מסונן' על ידי תוצאות הבדיקה הלוגיות, והפונקציה IF מחזירה את תוצאת המערך הבאה:

 
{1234567}

שים לב שיש לנו מספרי שורה תקפים לשורה 1 ולשורה 2.

מערך זה מעובד לאחר מכן על ידי SMALL, אשר מוגדר לשימוש בערכים בעמודה H להחזרת ערכים 'n'. הפונקציה SMALL מתעלמת אוטומטית מהערכים ההגיוניים TRUE ו- FALSE במערך. בסופו של דבר, הנוסחאות מצטמצמות ל:

 
{1FALSEFALSE4FALSEFALSEFALSE}

שגיאות טיפול

ברגע שאין התאמות נוספות עבור מזהה נתון, הפונקציה SMALL תחזיר שגיאת #NUM. תוכל לטפל בשגיאה זו באמצעות פונקציית IFERROR , או על ידי הוספת לוגיקה לספירת התאמות והפסקת העיבוד ברגע שהמספר בעמודה H גדול מספירת ההתאמה. ה דוגמה כאן מראה גישה אחת.

קריטריונים מרובים

כדי להוסיף קריטריונים מרובים, אתה משתמש היגיון בוליאני , כפי ש מוסבר בדוגמה זו .

הסופר דייב בראנס


^