כיצד להשתמש בפונקציית INDEX ו-MATCH באקסל

2024 מְחַבֵּר: Abigail Brown | [email protected]. שונה לאחרונה: 2023-12-17 06:48

מה צריך לדעת

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

מאמר זה מסביר כיצד להשתמש בפונקציות INDEX ו-MATCH יחד בכל הגירסאות של Excel, כולל Excel 2019 ו-Microsoft 365.

מהן הפונקציות INDEX ו-MATCH?

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

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

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

INDEX ו-MATCH תחביר וארגומנטים

כך צריך לכתוב את שתי הפונקציות כדי שאקסל יבין אותן:

=INDEX(מערך, row_num, [column_num])

array הוא טווח התאים שבו הנוסחה תשתמש. זה יכול להיות שורה ועמודה אחת או יותר, כגון A1:D5. זה נדרש.
row_num היא השורה במערך שממנה יש להחזיר ערך, כגון 2 או 18. זה נדרש אלא אם קיים column_num.
column_num היא העמודה במערך שממנה יש להחזיר ערך, כגון 1 או 9. זה אופציונלי.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value הוא הערך שאתה רוצה להתאים ב-lookup_array. זה יכול להיות מספר, טקסט או ערך לוגי שהוקלד באופן ידני או שאליו מתייחסים באמצעות הפניה לתא. זה נדרש.
lookup_array הוא טווח התאים שיש להסתכל דרכם. זה יכול להיות שורה בודדת או עמודה בודדת, כגון A2:D2 או G1:G45. זה נדרש.
match_type יכול להיות -1, 0 או 1. הוא מציין כיצד lookup_value מותאם לערכים ב-lookup_array (ראה להלן). 1 הוא ערך ברירת המחדל אם ארגומנט זה מושמט.

באיזה סוג התאמה להשתמש
סוג התאמה	מה זה עושה	Rule	דוגמה
1	מוצא את הערך הגדול ביותר הקטן או שווה ל-lookup_value.	יש למקם את ערכי מערך_החיפוש בסדר עולה (לדוגמה, -2, -1, 0, 1, 2; או A-Z;, או FALSE, TRUE.	lookup_value הוא 25 אבל הוא חסר ב-lookup_array, אז המיקום של המספר הקטן הבא, כמו 22, מוחזר במקום זאת.
0	מוצא את הערך הראשון ששווה בדיוק ל-lookup_value.	ערכי מערך_החיפוש יכולים להיות בכל סדר.	lookup_value הוא 25, אז הוא מחזיר את המיקום של 25.
-1	מוצא את הערך הקטן ביותר שגדול או שווה ל-lookup_value.	יש למקם את ערכי מערך_החיפוש בסדר יורד (לדוגמה, 2, 1, 0, -1, -2).	lookup_value הוא 25 אבל הוא חסר ב-lookup_array, אז המיקום של המספר הבא בגודלו, כמו 34, מוחזר במקום זאת.

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

נוסחאות INDEX ו-MATCH לדוגמה

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

INDEX דוגמאות

=INDEX(A1:B2, 2, 2)

=INDEX(A1:B1, 1)

=INDEX(2:2, 1)=INDEX(B1:B2, 1)

בדוגמה הראשונה הזו, ישנן ארבע נוסחאות INDEX שאנו יכולים להשתמש בהן כדי לקבל ערכים שונים:

=INDEX(A1:B2, 2, 2) מסתכל דרך A1:B2 כדי למצוא את הערך בעמודה השנייה ובשורה השנייה, שהיא סטייסי.
=INDEX(A1:B1, 1) מסתכל דרך A1:B1 כדי למצוא את הערך בעמודה הראשונה, שהוא Jon.
=INDEX(2:2, 1) מסתכל על כל מה בשורה השנייה כדי לאתר את הערך בעמודה הראשונה, שהיא Tim.
=INDEX(B1:B2, 1) מסתכל דרך B1:B2 כדי לאתר את הערך בשורה הראשונה, שהיא איימי.

MATCH דוגמאות

=MATCH("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)

להלן ארבע דוגמאות קלות לפונקציית MATCH:

=MATCH("Stacy", A2:D2, 0) מחפש את סטייסי בטווח A2:D2 ומחזיר 3 כתוצאה.
=MATCH(14, D1:D2) מחפש 14 בטווח D1:D2, אך מכיוון שהוא לא נמצא בטבלה, MATCH מוצא את הערך הבא בגודלו זה קטן או שווה ל-14, שבמקרה זה הוא 13, שנמצא במיקום 1 של מערך_lookup.
=MATCH(14, D1:D2, -1) זהה לנוסחה שמעליו, אבל מכיוון שהמערך אינו בסדר יורד כמו -1 דורש, אנו מקבלים שגיאה.
=MATCH(13, A1:D1, 0) מחפש 13 בשורה הראשונה של הגיליון, מה שמחזיר 4 מכיוון שהוא הפריט הרביעי במערך הזה.

INDEX-MATCH דוגמאות

הנה שתי דוגמאות שבהן נוכל לשלב INDEX ו-MATCH בנוסחה אחת:

מצא הפניה לתא בטבלה

=INDEX(B2:B5, MATCH(F1, A2:A5))

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

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

MATCH(F1, A2:A5) מחפש את הערך F1 (8795) בערכת הנתונים A2:A5. אם נספור את העמודה לאחור, נוכל לראות שזה 2, אז זה מה שפונקציית MATCH גילתה.
מערך INDEX הוא B2:B5 מכיוון שבסופו של דבר אנחנו מחפשים את הערך בעמודה זו.
ניתן לשכתב את פונקציית INDEX כעת, מכיוון ש-2 הוא מה שמצא MATCH: INDEX(B2:B5, 2, [column_num]).
מכיוון ש- column_num הוא אופציונלי, אנחנו יכולים להסיר את זה כדי להישאר עם זה: INDEX(B2:B5, 2).
אז עכשיו, זה כמו נוסחת INDEX רגילה שבה אנחנו מוצאים את הערך של הפריט השני ב-B2:B5, שהוא אדום.

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

=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))

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

MATCH(G1, A2:A13, 0) הוא הפריט הראשון שנפתר בנוסחה זו. הוא מחפש G1 (המילה "מאי") ב-A2:A13 כדי לקבל ערך מסוים. אנחנו לא רואים את זה כאן, אבל זה 5.
MATCH(G2, B1:E1, 0) היא נוסחת ה-MATCH השנייה, והיא ממש דומה לראשונה, אבל במקום זאת מחפשת G2 (המילה "ירוק") בכותרות העמודות ב-B1:E1. זה נפתר ל-3.
אנחנו יכולים כעת לשכתב את נוסחת INDEX כך כדי לדמיין מה קורה: =INDEX(B2:E13, 5, 3). זה מחפש בכל הטבלה, B2:E13, את השורה החמישית והעמודה השלישית, שמחזירה $180.

כללי MATCH ו-INDEX

יש כמה דברים שכדאי לזכור בעת כתיבת נוסחאות עם הפונקציות הבאות:

MATCH אינו רגיש לאותיות גדולות, לכן האותיות הגדולות והקטנות מטופלות באופן זהה בהתאמת ערכי טקסט.
MATCH מחזירה N/A מסיבות מרובות: אם match_type הוא 0 ו-lookup_value לא נמצא אם match_type הוא -1 ו-lookup_array אינו בסדר יורד, אם match_type הוא 1 ומערך_lookup אינו נמצא בעלייה סדר, ואם lookup_array אינו שורה או עמודה בודדת.
תוכל להשתמש בתו כללי בארגומנט lookup_value אם match_type הוא 0 ו-lookup_value הוא מחרוזת טקסט. סימן שאלה מתאים לכל תו בודד וכוכבית תואמת לכל רצף של תווים (למשל.למשל, =MATCH("Jo", 1:1, 0)). כדי להשתמש ב-MATCH כדי למצוא סימן שאלה או כוכבית, הקלד תחילה ~.
INDEX מחזיר REF! אם row_num ו-column_num אינם מצביעים על תא בתוך המערך.

פונקציות אקסל קשורות

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

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