לְהִצטַיֵן

כיצד להשתמש ב- INDEX וב- MATCH

How Use Index Match

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



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

פונקציית INDEX | פונקציית MATCH | INDEX ו- MATCH | חיפוש דו כיווני | חיפוש שמאלי | תלוי רישיות | ההתאמה הקרובה ביותר | קריטריונים מרובים | דוגמאות נוספות





הפונקציה INDEX

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

 
= INDEX (B3:B11,4)

שימוש ב- INDEX כדי לקבל את שמו של כוכב הלכת הרביעי
INDEX מחזיר את הערך בשורה הרביעית של הטווח.



וִידֵאוֹ: כיצד לבדוק את העניינים באמצעות INDEX

מה אם אתה רוצה לקבל את קוטר מאדים עם INDEX? במקרה זה, אנו יכולים לספק גם מספר שורה וגם מספר עמודה, ולספק טווח גדול יותר. נוסחת INDEX להלן משתמשת בטווח הנתונים המלא ב- B3: D11, עם מספר שורה של 4 ומספר עמודה של 2:

 
= INDEX (B3:D11,4,2)

שימוש ב- INDEX כדי לקבל את קוטר הכוכב הרביעי
INDEX מאחזר את הערך בשורה 4, עמודה 2.

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

בשלב זה אתה עשוי לחשוב 'אז מה? כל כמה זמן אתה באמת יודע את המיקום של משהו בגיליון אלקטרוני? '

בדיוק. אנו זקוקים לדרך לאתר את עמדת הדברים שאנו מחפשים.

היכנס לפונקציית MATCH.

פונקציית MATCH

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

 
= MATCH ('peach',B3:B9,0)

שימוש ב- MATCH לאיתור מיקום בטווח אנכי
MATCH מחזיר 3, מכיוון ש'אפרסק 'הוא הפריט השלישי. MATCH אינה תלויה באותיות רישיות.

ל- MATCH לא אכפת אם הטווח אופקי או אנכי, כפי שניתן לראות להלן:

 
= MATCH ('peach',C4:I4,0)

שימוש ב- MATCH לאיתור מיקום בטווח אופקי
אותה תוצאה עם טווח אופקי, MATCH מחזיר 3.

וִידֵאוֹ: כיצד להשתמש ב- MATCH להתאמות מדויקות

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

INDEX ו- MATCH יחד

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

מכירות לפי איש מכירות לפי חודש

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

 
= INDEX (C3:E11,5,2) // returns 94

אבל ברור שאנחנו לא רוצים להקשיד מספרים. במקום זאת, אנו רוצים א דִינָמִי הבט מעלה.

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

 
= INDEX (C3:E11, MATCH ('Frantz',B3:B11,0),2)

אם ניקח את הדברים צעד אחד קדימה, נשתמש בערך של H2 ב- MATCH:

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0),2)

INDEX ו- MATCH כדי למצוא מכירות בפברואר לכל שם
MATCH מוצא את 'Frantz' ומחזיר 5 ל- INDEX לשורה.

לסכם:

  1. INDEX זקוק למיקומים מספריים.
  2. MATCH מוצא את העמדות האלה.
  3. MATCH הוא מקונן בתוך INDEX.

בואו נתמודד כעת עם מספר העמודה.

חיפוש דו כיווני עם INDEX ו- MATCH

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

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

 
= MATCH ('Mar',C2:E2,0) // returns 3

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

חיפוש דינאמי עם INDEX ו- MATCH
חיפוש דינאמי לחלוטין דו כיווני עם INDEX ו- MATCH.

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0), MATCH (H3,C2:E2,0))

נוסחת MATCH הראשונה מחזירה 5 ל- INDEX כמספר השורה, נוסחת MATCH השנייה מחזירה 3 ל- INDEX כמספר העמודה. לאחר הפעלת MATCH, הנוסחה מפשטת את:

 
= INDEX (C3:E11,5,3)

ו- INDEX מחזירה נכון 10,525 $, מספר המכירות של Frantz במרץ.

הערה: אתה יכול להשתמש אימות מידע כדי ליצור תפריטים נפתחים לבחירת איש מכירות וחודש.

וִידֵאוֹ: כיצד לבצע חיפוש דו כיווני עם INDEX ו- MATCH

וִידֵאוֹ: כיצד לאתר באגים בנוסחה באמצעות F9 (כדי לראות ערכי החזרה של MATCH)

חיפוש שמאלי

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

חיפוש שמאלי עם INDEX ו- MATCH

קרא הסבר מפורט כאן .

חיפוש תלוי רישיות

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

חיפוש תלוי רישיות עם INDEX ו- MATCH

קרא הסבר מפורט כאן .

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

ההתאמה הקרובה ביותר

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

מצא התאמה הקרובה ביותר עם INDEX ו- MATCH

קרא הסבר מפורט כאן .

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

חיפוש קריטריונים מרובים

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

INDEX ו- MATCH עם מספר קריטריונים

קרא הסבר מפורט כאן .

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

כיצד אוכל לכבות את נעילת הגלילה

דוגמאות נוספות של INDEX + MATCH

להלן כמה דוגמאות בסיסיות יותר של INDEX ו- MATCH בפעולה, לכל אחת הסבר מפורט:

מחבר דייב בראנס קבצים מצורפים קוֹבֶץ אינדקס exceljet ו- match.xlsx


^