אתה יכול להשתמש בפונקציית Excel MATCH כדי למצוא את המיקום היחסי של ערך מסוים בטווח של תאים או מערך.
פונקציית MATCH דומה לפונקציית VLOOKUP מכיוון ששניהם מסווגים תחת פונקציות חיפוש/הפניה של Excel. VLOOKUP מחפש ערך מסוים בעמודה ומחזיר ערך באותה שורה בעוד הפונקציה MATCH מחפשת ערך מסוים בטווח ומחזירה את המיקום של אותו ערך.
הפונקציה MATCH של Excel מחפשת ערך שצוין בטווח של תאים או מערך ומחזירה את המיקום היחסי של ההופעה הראשונה של ערך זה בטווח. ניתן להשתמש בפונקציית MATCH גם כדי לחפש ערך מסוים ולהחזיר את הערך המתאים לו בעזרת הפונקציה INDEX (בדיוק כמו Vlookup). בוא נראה כיצד להשתמש בפונקציית Excel MATCH כדי למצוא את המיקום של ערך בדיקת מידע בטווח של תאים.
פונקציית MATCH של Excel
פונקציית MATCH היא פונקציה מובנית באקסל והיא משמשת בעיקר לאיתור המיקום היחסי של ערך בדיקת מידע בעמודה או בשורה.
תחביר של פונקציית MATCH:
=MATCH(lookup_value,lookup_array,[match_type})איפה:
ערך_חיפוש - הערך שברצונך לחפש בטווח מוגדר של תאים או במערך. זה יכול להיות ערך מספרי, ערך טקסט, ערך לוגי או הפניה לתא שיש לה ערך.
מערך_חיפוש – מערכי התאים שבהם אתה מחפש ערך. זה חייב להיות עמודה בודדת או שורה בודדת.
התאמה_סוג – זהו פרמטר אופציונלי שניתן להגדיר ל-0,1 או -1 וברירת המחדל היא 1.
- 0 מחפש התאמה מדויקת, כאשר היא לא נמצאה, מחזיר שגיאה.
- -1 מחפש את הערך הקטן ביותר שגדול או שווה ל-lookup_value כאשר מערך בדיקת המידע בסדר עולה.
- 1 מחפש את הערך הגדול ביותר הקטן או שווה לערך ה-look_up כאשר מערך ה-lookup בסדר יורד.
מצא מיקום של התאמה מדויקת
נניח שיש לנו את מערך הנתונים הבא שבו אנו רוצים למצוא את המיקום של ערך מסוים.
בטבלה זו, אנו רוצים למצוא את המיקום של שם עיר (ממפיס) בעמודה (A2:A23), אז אנו משתמשים בנוסחה זו:
=MATCH("ממפיס",A2:A23,0)הארגומנט השלישי מוגדר ל-'0' מכיוון שאנו רוצים למצוא התאמה מדויקת של שם העיר. כפי שניתן לראות כי שם העיר "ממפיס" בנוסחה הוא באותיות קטנות ואילו בטבלה האות הראשונה של שם העיר היא באותיות גדולות (ממפיס). ובכל זאת, הנוסחה מסוגלת למצוא את המיקום של הערך שצוין בטווח הנתון. זה בגלל שפונקציית ה-MATCH אינה תלוית רישיות.
הערה: אם ה-lookup_value לא נמצא בטווח החיפוש או אם תציין טווח חיפוש שגוי, הפונקציה תחזיר את השגיאה #N/A.
אתה יכול להשתמש בהפניה לתא בארגומנט הראשון של הפונקציה במקום בערך ישיר. הנוסחה שלהלן מוצאת את מיקום הערך בתא F2 ומחזירה את התוצאה בתא F3.
מצא מיקום של התאמה משוערת
ישנן שתי דרכים בהן תוכל לחפש התאמה משוערת או מדויקת של ערך הבדיקה ולהחזיר את מיקומו.
- דרך אחת היא למצוא את הערך הקטן ביותר שגדול או שווה (ההתאמה הבאה בגודלה) לערך שצוין. ניתן להשיג זאת על ידי הגדרת הארגומנט האחרון (match_type) של הפונקציה כ-'-1'
- דרך נוספת היא הערך הגדול ביותר שהוא קטן או שווה (ההתאמה הקטנה הבאה) לערך הנתון. ניתן להשיג זאת על ידי הגדרת ה-match_type של הפונקציה כ-'1'
המשחק הקטן ביותר הבא
אם הפונקציה לא מצליחה למצוא התאמה מדויקת לערך שצוין כאשר סוג ההתאמה מוגדר ל-'1', היא מאתרת את הערך הגדול ביותר שקצת פחות מהערך שצוין (שמשמעותו הערך הקטן הבא) ומחזירה את מיקומו . כדי שזה יעבוד, אתה צריך למיין את המערך בסדר עולה, אם לא זה יגרום לשגיאה.
בדוגמה, אנו משתמשים בנוסחה שלהלן כדי למצוא את ההתאמה הקטנה הבאה:
=MATCH(F2,D2:D23,1)כאשר נוסחה זו לא הצליחה למצוא את ההתאמה המדויקת לערך בתא F2, היא מצביעה על המיקום (16) של הערך הקטן הבא, כלומר 98.
המשחק הבא הגדול ביותר
כאשר סוג ההתאמה מוגדר ל-'-1' והפונקציה MATCH לא יכולה למצוא התאמה מדויקת, היא מוצאת את הערך הקטן ביותר שגדול מהערך שצוין (שמשמעותו הערך הבא בגודלו) ומחזירה את מיקומה. יש למיין את מערך הבדיקה בסדר יורד עבור שיטה זו אחרת היא תחזיר שגיאה.
לדוגמה, הזן את הנוסחה הבאה כדי למצוא את ההתאמה הבאה בגודלה לערך הבדיקה:
=MATCH(F2,D2:D23,-1)פונקציית MATCH זו מחפשת את הערך ב-F2 (55) בטווח החיפוש D2:D23, וכאשר היא לא מוצאת את ההתאמה המדויקת, היא מחזירה את המיקום (16) של הערך הבא בגודלו, כלומר 58.
התאמת תווים כלליים
ניתן להשתמש בתווים כלליים בפונקציית MATCH רק כאשר match_type מוגדר ל-'0' וערך הבדיקה הוא מחרוזת טקסט. ישנם תווים כלליים שניתן להשתמש בהם בפונקציית MATCH: כוכבית (*) וסימן שאלה (?).
- סימן שאלה (?) משמש כדי להתאים כל תו או אות בודדת למחרוזת הטקסט.
- כוכבית (*) משמש כדי להתאים כל מספר של תווים עם המחרוזת.
לדוגמה, השתמשנו בשני תווים כלליים של '?' ב-lookup_value (Lo??n) של הפונקציה MATCH כדי למצוא ערך התואם את מחרוזת הטקסט עם כל שני תווים (במקומות התווים הכלליים). והפונקציה מחזירה את המיקום היחסי של הערך התואם בתא E5.
=MATCH("Lo??n",A2:A22,0)
אתה יכול להשתמש בתו כללי (*) באותו אופן כמו (?), אבל כוכבית משמשת כדי להתאים כל מספר של תווים בעוד סימן שאלה משמש כדי להתאים כל תו בודד.
לדוגמה, אם אתה משתמש ב-'sp*', הפונקציה יכולה להתאים לרמקול, מהירות או spielberg וכו'. אבל אם הפונקציה מוצאת ערכים מרובים/כפולים התואמים לערך הבדיקה, היא רק תחזיר את המיקום של הערך הראשון.
בדוגמה, הכנסנו "Kil*o" בארגומנט lookup_value. אז הפונקציה MATCH() מחפשת טקסט שמכיל 'Kil' בהתחלה, 'o' בסוף וכל מספר תווים ביניהם. 'Kil*o' מתאים לקילימנג'רו במערך ולכן הפונקציה מחזירה את המיקום היחסי של הקילימנג'רו, שהוא 16.
INDEX ו-MATCH
רק לעתים נדירות נעשה שימוש בפונקציות MATCH. לעתים קרובות הם התחברו לפונקציות אחרות כדי ליצור נוסחאות חזקות. כאשר פונקציית MATCH משולבת עם פונקציית INDEX, היא יכולה לבצע חיפושים מתקדמים. אנשים רבים עדיין מעדיפים להשתמש ב-VLOOKUP כדי לחפש ערך, כי זה פשוט יותר אבל INDEX MATCH גמיש ומהיר יותר מ-VLOOKUP.
VLOOKUP יכול לחפש ערך אנכית, כלומר עמודות, בעוד המשולבת INDEX MATCH יכולה לבצע חיפושים אנכיים ואופקיים כאחד.
פונקציית INDEX המשמשת לאחזור ערך במיקום מסוים בטבלה או בטווח. הפונקציה MATCH מחזירה את המיקום היחסי של ערך בעמודה או בשורה. בשילוב, ה-MATCH מוצא את מספר השורה או העמודה (מיקום) של ערך מסוים, והפונקציה INDEX מאחזרת ערך המבוסס על מספר השורה והעמודה.
תחביר של פונקציית INDEX:
=INDEX(array,row_num,[col_num],)בכל מקרה בוא נראה איך INDEX MATCH עובד עם דוגמה.
בדוגמה שלהלן, אנו רוצים לאחזר את ציון 'Quiz2' עבור התלמיד 'אן'. לשם כך נשתמש בנוסחה הבאה:
=INDEX(B2:F20,MATCH(H2,A2:A20,0),3)INDEX זקוק למספר שורה ועמודה כדי לאחזר ערך. בנוסחה שלמעלה, פונקציית MATCH המקוננת מוצאת את מספר השורה (מיקום) של הערך 'Anne' (H2). לאחר מכן אנו מספקים את מספר השורה לפונקציה INDEX עם טווח B2:F20 ומספר עמודה (3), אותם אנו מציינים. והפונקציה INDEX מחזירה את הציון '91'.
חיפוש דו-כיווני עם INDEX ו-MATCH
ניתן גם להשתמש בפונקציות INDEX ו-MATCH כדי לחפש ערך בטווח דו מימדי (בדיקת דו-כיוונית). בדוגמה שלמעלה, השתמשנו בפונקציה MATCH כדי לאתר את מספר השורה של ערך, אך הכנסנו את מספר העמודה באופן ידני. אבל אנחנו יכולים למצוא גם שורה וגם עמודה על ידי קינון של שתי פונקציות MATCH, אחת בארגומנט row_num ואחרת בארגומנט column_num של הפונקציה INDEX.
השתמש בנוסחה זו לבדיקת דו כיוונית עם INDEX ו-MATCH:
=INDEX(A1:F20,MATCH(H2,A2:A20,0),MATCH(H3,A1:F1,0))כידוע, פונקציית MATCH יכולה לחפש ערך גם אופקית וגם אנכית. בנוסחה זו, הפונקציה MATCH השנייה בארגומנט colum_num מוצאת את המיקום של Quiz2 (4) ומספקת אותו לפונקציה INDEX. וה- INDEX מאחזר את הציון.
כעת, אתה יודע כיצד להשתמש בפונקציית ההתאמה ב-Excel.