ל- Excel יש הרבה פונקציות - כ -450+ מהן.
ורבים מהם פשוט מדהימים. כמות העבודה שתוכל לבצע בעזרת כמה נוסחאות עדיין מפתיעה אותי (גם לאחר שהשתמשתי ב- Excel 10+ שנים).
ובין כל הפונקציות המדהימות האלה, משולבת הפונקציות INDEX MATCH בולטת.
אני מעריץ ענק של משולבת INDEX MATCH והבהרתי את זה די הרבה פעמים.
אפילו כתבתי מאמר על אינדקס התאמה מול VLOOKUP שעורר מעט ויכוחים (אתה יכול לבדוק את פרק ההערות לגבי זיקוקים).
והיום, אני כותב מאמר זה המתמקד אך ורק ב- Index Match כדי להראות לך כמה תרחישים פשוטים ומתקדמים שבהם תוכל להשתמש בשילוב הנוסחה החזק הזה ולבצע את העבודה.
הערה: ישנן נוסחאות חיפוש אחרות ב- Excel - כגון VLOOKUP ו- HLOOKUP ואלו מצוינות. אנשים רבים מוצאים שקל יותר להשתמש ב- VLOOKUP (וזה נכון גם). אני מאמין ש- INDEX MATCH היא אופציה טובה יותר במקרים רבים. אבל מכיוון שאנשים מתקשים, זה פחות מתרגל. אז אני מנסה לפשט את זה בעזרת הדרכה זו.עכשיו לפני שאני אראה לכם כיצד השילוב של INDEX MATCH משנה את עולם האנליסטים ומדעני הנתונים, הרשו לי להציג בפניכם תחילה את החלקים הבודדים - פונקציות INDEX ו- MATCH.
פונקציית INDEX: מוצאת את הערך המבוסס על קואורדינטות
הדרך הקלה ביותר להבין כיצד פועלת פונקציית אינדקס היא לחשוב על זה כעל לווין GPS.
ברגע שתגיד ללוויין את קואורדינטות הרוחב והאורך, הוא יידע בדיוק לאן ללכת ולמצוא את המיקום הזה.
כך שלמרות שיש לו מספר מדהים של שילובים ארוכי טווח, הלוויין ידע בדיוק היכן לחפש.
חיפשתי במהירות את מקום העבודה שלי וזה מה שקיבלתי.
בכל מקרה, מספיק גיאוגרפיה.
בדיוק כמו שלוויין זקוק לקואורדינטות קו אורך ואורך, הפונקציה INDEX באקסל תזדקק למספר השורה והעמודה כדי לדעת לאיזה תא אתה מתכוון.
וזוהי פונקציית Excel INDEX בקליפת אגוז.
אז הרשה לי להגדיר זאת במילים פשוטות עבורך.
הפונקציה INDEX תשתמש במספר השורות ובמספר העמודות כדי למצוא תא בטווח הנתון ולהחזיר את הערך בו.
כשלעצמו, INDEX היא פונקציה פשוטה מאוד, ללא תועלת. אחרי הכל, ברוב המקרים לא סביר שתכירו את מספר השורות והעמודות.
אבל…
העובדה שאתה יכול להשתמש בו עם פונקציות אחרות (רמז: MATCH) שיכולות למצוא את מספר השורה ומספר העמודה הופכת את INDEX לפונקציית Excel חזקה במיוחד.
להלן התחביר של הפונקציה INDEX:
= INDEX (מערך, שורה_מספר, [col_num]) = INDEX (array, row_num, [col_num], [area_num])
- מערך - א טווח תאים או קבוע מערך.
- מספר_מספר - מספר השורה שממנו יש להביא את הערך.
- [col_num] - מספר העמודה שממנה יש להביא את הערך. אמנם זהו טיעון אופציונלי, אך אם לא מסופק row_num, יש לתת אותו.
- [area_num] - (אופציונלי) אם ארגומנט מערך מורכב מטווחים מרובים, מספר זה ישמש לבחירת ההפניה מכל הטווחים.
לפונקציה INDEX יש 2 תחביר (רק לידיעתך).
הראשון משמש ברוב המקרים. השני משמש במקרים מתקדמים בלבד (כגון חיפוש תלת כיווני) עליו נעסוק באחת הדוגמאות בהמשך הדרכה זו.
אבל אם אתה חדש בפונקציה זו, זכור את התחביר הראשון.
להלן סרטון המסביר כיצד להשתמש בפונקציה INDEX
פונקציית MATCH: מוצאת את המיקום המבוסס על ערך חיפוש
אם נחזור לדוגמא הקודמת של אורך ורוחב, MATCH היא הפונקציה שיכולה למצוא את המיקומים האלה (בעולם הגיליונות האלקטרוניים של Excel).
בשפה פשוטה, הפונקציה Excel MATCH יכולה למצוא את המיקום של תא בטווח.
ועל סמך מה הוא ימצא את מיקום התא?
מבוסס על ערך החיפוש.
לדוגמה, אם יש לך רשימה כפי שמוצג להלן וברצונך למצוא בה את מיקום השם 'סימן', תוכל להשתמש בפונקציית MATCH.
הפונקציה מחזירה 3, מכיוון שזו המיקום של התא עם השם Mark בו.
פונקציית MATCH מתחילה לחפש מלמעלה למטה את ערך החיפוש (שהוא 'סימן') בטווח שצוין (שהוא A1: A9 בדוגמה זו). ברגע שהוא מוצא את השם, הוא מחזיר את המיקום בטווח הספציפי הזה.
להלן התחביר של הפונקציה MATCH ב- Excel.
= MATCH (חיפוש_ערך, חיפוש_ערך, [התאמה_סוג])
- ערך_חיפוש - הערך שעבורו אתה מחפש התאמה ב- lookup_array.
- חיפוש_ערך - טווח התאים שבו אתה מחפש את Lookup_value.
- [התאמה_סוג] - (אופציונלי) זה מציין כיצד Excel צריך לחפש ערך תואם. זה יכול לקחת שלושה ערכים -1, 0 או 1.
הבנת טיעון סוג התאמה בפונקציית MATCH
יש עוד דבר אחד שאתה צריך לדעת על הפונקציה MATCH, והוא על האופן שבו הוא עובר את הנתונים ומוצא את מיקום התא.
הארגומנט השלישי של הפונקציה MATCH יכול להיות 0, 1 או -1.
להלן הסבר כיצד פועלים טיעונים אלה:
- 0 - זה יחפש התאמה מדויקת של הערך. אם נמצאה התאמה מדויקת, הפונקציה MATCH תחזיר את מיקום התא. אחרת, היא תחזיר שגיאה.
- 1 - זה מוצא את הערך הגדול ביותר שהוא פחות או שווה לערך החיפוש. כדי שזה יעבוד, יש למיין את טווח הנתונים שלך בסדר עולה.
- -1 - זה מוצא את הערך הקטן ביותר שהוא גדול או שווה לערך החיפוש. כדי שזה יעבוד, יש למיין את טווח הנתונים שלך בסדר יורד.
להלן סרטון המסביר כיצד להשתמש בפונקציה MATCH (יחד עם ארגומנט סוג ההתאמה)
לסיכום וניסוי במילים פשוטות:
- INDEX זקוק למיקום התא (מספר שורה וטור) ונותן את ערך התא.
- MATCH מוצא את המיקום באמצעות ערך חיפוש.
בואו לשלב אותם ליצירת תחנת כוח (INDEX + MATCH)
כעת, לאחר שיש לך הבנה בסיסית כיצד פועלות פונקציות INDEX ו- MATCH בנפרד, בוא נשלב בין שני אלה ולמד על כל הדברים הנפלאים שהוא יכול לעשות.
כדי להבין זאת טוב יותר, יש לי כמה דוגמאות שמשתמשות בשילוב INDEX MATCH.
אתחיל בדוגמא פשוטה ולאחר מכן אראה לך גם כמה שימושים מתקדמים.
לחץ כאן להורדת קובץ הדוגמה
דוגמה 1: חיפוש פשוט באמצעות משולבת INDEX MATCH
בואו נעשה בדיקה פשוטה עם INDEX/MATCH.
להלן טבלה שבה יש לי את הציונים לעשרה תלמידים.
מהטבלה הזו, אני רוצה למצוא את הסימנים של ג'ים.
להלן הנוסחה שיכולה לעשות זאת בקלות:
= INDEX ($ A $ 2: $ B $ 11, MATCH ("ג'ים", $ A $ 2: $ A $ 11,0), 2)
עכשיו, אם אתה חושב שניתן בקלות לעשות זאת באמצעות פונקציית VLOOKUP, אתה צודק! זה לא השימוש הטוב ביותר בתדהמות INDEX MATCH. למרות שאני מעריץ של INDEX MATCH, זה קצת יותר קשה מ- VLOOKUP. אם כל מה שאתה רוצה לעשות הוא להביא נתונים מעמודה מימין, אני ממליץ לך להשתמש ב- VLOOKUP.
הסיבה שהראיתי את הדוגמה הזו, שאפשר לעשות אותה בקלות גם עם VLOOKUP היא להראות לך כיצד INDEX MATCH פועל במסגרת פשוטה.
עכשיו הרשה לי להראות יתרון ב- INDEX MATCH.
נניח שיש לך את אותם הנתונים, אבל במקום שיהיה לך אותם בעמודות, יש לך אותם בשורות (כפי שמוצג להלן).
אתה יודע מה, אתה עדיין יכול להשתמש בשילוב INDEX MATCH כדי לקבל את הציונים של ג'ים.
להלן הנוסחה שתתן לך את התוצאה:
= INDEX ($ B $ 1: $ K $ 2,2, MATCH ("ג'ים", $ B $ 1: $ K $ 1,0))
שים לב שעליך לשנות את הטווח ולהחליף את חלקי השורות/העמודות בכדי לגרום לנוסחה זו לפעול גם עבור נתונים אופקיים.
לא ניתן לעשות זאת באמצעות VLOOKUP, אך עדיין תוכל לעשות זאת בקלות עם HLOOKUP.
שילוב INDEX MATCH יכול להתמודד בקלות עם נתונים אופקיים ואנכיים.לחץ כאן להורדת קובץ הדוגמה
דוגמה 2: חיפוש שמאל
זה נפוץ יותר ממה שאתה חושב.
פעמים רבות, ייתכן שתידרש להביא את הנתונים מעמודה הנמצאת משמאל לעמודה בעלת ערך החיפוש.
משהו כפי שמוצג להלן:
כדי לברר את המכירות של מייקל, יהיה עליך לחפש בצד שמאל.
אם אתה חושב VLOOKUP, תן לי לעצור את זכותך שם.
VLOOKUP אינו מיועד לחפש ולאחזר את הערכים משמאל.
האם אתה עדיין יכול לעשות זאת באמצעות VLOOKUP?
כן אתה יכול!
אבל זה יכול להפוך לנוסחה ארוכה ומכוערת.
אז אם אתה רוצה לבצע חיפוש ולשאוף נתונים מהעמודות משמאל, מוטב לך להשתמש בשילוב INDEX MATCH.
להלן הנוסחה שתקבל את מספר המכירות של מייקל:
= INDEX ($ A $ 2: $ C $ 11, MATCH ("מייקל", C2: C11,0), 2)
דוגמה 3: חיפוש דו כיווני
עד כה ראינו את הדוגמאות בהן רצינו להביא את הנתונים מהעמודה הסמוכה לעמודה שיש לה את ערך החיפוש.
אך בחיים האמיתיים, הנתונים מתפרשים לעתים קרובות במספר עמודות.
INDEX MATCH יכול להתמודד בקלות עם חיפוש דו כיווני.
להלן מערך נתונים של ציוני התלמיד בשלושה נושאים שונים.
אם אתה רוצה להביא במהירות את הציונים של תלמיד בכל שלושת המקצועות, אתה יכול לעשות זאת עם INDEX MATCH.
הנוסחה להלן תעניק לך את הציונים עבור ג'ים עבור כל שלושת הנושאים (העתק והדבק בתא אחד וגרור כדי למלא תאים אחרים או העתק והדבק על תאים אחרים).
= INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))
תן לי להסביר במהירות גם את הנוסחה הזו.
נוסחת INDEX משתמשת ב- B2: D11 כטווח.
MATCH הראשון משתמש בשם (ג'ים בתא F3) ושואב את המיקום שלו בעמודת השמות (A2: A11). זה הופך למספר השורות שממנו צריך להביא את הנתונים.
נוסחת MATCH השנייה משתמשת בשם הנושא (בתא G2) כדי לקבל את המיקום של שם הנושא הספציפי הזה ב- B1: D1. לדוגמה, מתמטיקה היא 1, פיזיקה היא 2 וכימיה היא 3.
מכיוון שמיקומי MATCH אלה מוזנים לפונקציית INDEX, היא מחזירה את הציון בהתבסס על שם התלמיד ושם הנושא.
נוסחה זו היא דינאמית, מה שאומר שאם תשנה את שם התלמיד או את שמות הנושאים, היא עדיין תעבוד ותביא את הנתונים הנכונים.
דבר אחד גדול בשימוש ב- INDEX/MATCH הוא שגם אם תחליף את שמות הנושאים, זה ימשיך לתת לך את התוצאה הנכונה.דוגמה 4: ערך חיפוש מתוך עמודה/קריטריונים מרובים
נניח שיש לך מערך נתונים כפי שמוצג להלן וברצונך להביא את הסימנים עבור 'Mark Long'.
מכיוון שהנתונים נמצאים בשתי עמודות, אינני יכול לחפש את מארק ולקבל את הנתונים.
אם אעשה זאת כך, אני אקבל את נתוני הסימנים עבור מארק פרוסט ולא את מארק לונג (כי פונקציית MATCH תיתן לי את התוצאה עבור ה- MARK שהוא עומד).
אחת הדרכים לעשות זאת היא ליצור טור עוזר ולשלב את השמות. ברגע שיש לך את עמודת העזר, תוכל להשתמש ב- VLOOKUP ולקבל את נתוני הציונים.
אם אתה מעוניין ללמוד כיצד לעשות זאת, קרא הדרכה זו בנושא שימוש ב- VLOOKUP עם מספר קריטריונים.
אך עם שילוב INDEX/MATCH, אינך צריך טור עוזר. באפשרותך ליצור נוסחה המטפלת בקריטריונים מרובים בנוסחה עצמה.
הנוסחה שלהלן תיתן את התוצאה.
= INDEX ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))
תן לי להסביר במהירות מה נוסחה זו עושה.
חלק MATCH בנוסחה משלב את ערך החיפוש (סימן וארוך) וכן את מערך החיפוש כולו. כאשר $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 משמש כמערך החיפוש, הוא בעצם בודק את ערך החיפוש מול המחרוזת המשולבת של שם פרטי ושם משפחה (מופרד על ידי סמל הצינור).
זה מבטיח שתקבל את התוצאה הנכונה מבלי להשתמש בעמודות עזר.
אתה יכול לעשות סוג זה של חיפוש (שבו יש מספר עמודות/קריטריונים) גם עם VLOOKUP, אך עליך להשתמש בעמודת עזר. שילוב INDEX MATCH מקל על ביצוע זה מעט ללא עמודות עזר.דוגמה 5: קבל ערכים משורה/עמודה שלמה
בדוגמאות לעיל, השתמשנו בפונקציה INDEX כדי לקבל ערך מתא ספציפי. אתה מספק את מספר השורה והעמודה, והוא מחזיר את הערך בתא הספציפי הזה.
אבל אתה יכול לעשות יותר.
תוכל גם להשתמש בפונקציה INDEX כדי לקבל את הערכים משורה או מעמודה שלמה.
ואיך זה יכול להיות שימושי אתם שואלים!
נניח שאתה רוצה לדעת את הציון הכולל של ג'ים בכל שלושת הנושאים.
אתה יכול להשתמש בפונקציה INDEX כדי לקבל תחילה את כל הסימנים של ג'ים ולאחר מכן להשתמש בפונקציית SUM כדי לקבל סך.
בוא נראה כיצד לעשות זאת.
להלן ציוני כל התלמידים בשלושה מקצועות.
הנוסחה שלהלן תעניק לי את הציון הכולל של ג'ים בכל שלושת הנושאים.
= SUM (INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))
תן לי להסביר כיצד נוסחה זו עובדת.
הטריק כאן הוא להשתמש ב 0 כמספר העמודה.
כאשר אתה משתמש ב 0 כמספר העמודה בפונקציה INDEX, הוא יחזיר את כל ערכי השורה. באופן דומה, אם אתה משתמש ב 0 כמספר השורה, הוא יחזיר את כל הערכים בעמודה.
אז החלק למטה של הנוסחה מחזיר מערך ערכים - {97, 70, 73}
INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)
אם רק תזין נוסחה זו לעיל בתא ב- Excel ותלחץ על enter, תראה #ערך! שְׁגִיאָה. הסיבה לכך היא שזה לא מחזיר ערך אחד, אלא מערך של ערך.
אך אל דאגה, מערך הערכים עדיין קיים. אתה יכול לבדוק זאת על ידי בחירת הנוסחה ולחץ על מקש F9. הוא יראה לך את התוצאה של הנוסחה שבמקרה זה היא מערך בעל שלושה ערכים - {97, 70, 73}
עכשיו, אם תעטוף נוסחת INDEX זו בפונקציית SUM, היא תיתן לך את סכום כל הסימנים שקלע ג'ים.
אתה יכול גם להשתמש באותו כדי לקבל את הציונים הגבוהים, הנמוכים והממוצעים של ג'ים.
בדיוק כפי שעשינו זאת עבור סטודנט, אתה יכול לעשות זאת גם עבור נושא. לדוגמה, אם אתה רוצה את הציון הממוצע בנושא, תוכל לשמור את מספר השורה כ- 0 בנוסחת INDEX וזה ייתן לך את כל ערכי העמודות של אותו נושא.
לחץ כאן להורדת קובץ הדוגמה
דוגמה 6: מצא את הציון של התלמיד (טכניקת התאמה משוערת)
עד כה, השתמשנו בנוסחת MATCH כדי לקבל את ההתאמה המדויקת של ערך החיפוש.
אבל אתה יכול גם להשתמש בו לביצוע התאמה משוערת.
עכשיו, מה לעזאזל ההתאמה המשוערת?
הרשה לי להסביר.
כאשר אתה מחפש דברים כגון שמות או מזהים, אתה מחפש התאמה מדויקת. אך לפעמים, עליך לדעת את הטווח בו נמצאים ערכי החיפוש שלך. בדרך כלל זה המצב במספרים.
לדוגמה, כמורה בכיתה, ייתכן שתרצה לדעת מה הציון של כל תלמיד בנושא, והציון נקבע על פי הציון.
להלן דוגמה, שבה אני רוצה את הציון לכל התלמידים והציון נקבע על פי הטבלה מימין.
אז אם תלמיד מקבל פחות מ 33, הציון הוא F ואם הוא מקבל פחות מ 50 אבל יותר מ 33, זה E, וכן הלאה.
להלן הנוסחה שתעשה זאת.
= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)
תן לי להסביר כיצד נוסחה זו עובדת.
בפונקציה MATCH, השתמשנו ב- 1 כארגומנט [match_type]. טיעון זה יחזיר את הערך הגדול ביותר שהוא פחות או שווה לערך החיפוש.
המשמעות היא שנוסחת MATCH עוברת את טווח הסימנים, וברגע שהיא תמצא טווח סימנים השווה לערך סימני החיפוש פחות או פחות, היא תעצור שם ותחזיר את מיקומה.
אז אם ערך סימן החיפוש הוא 20, הפונקציה MATCH תחזיר 1 ואם היא 85, היא תחזיר 5.
והפונקציה INDEX משתמשת ערך מיקום זה כדי לקבל את הציון.
חשוב: כדי שזה יעבוד, הנתונים שלך צריכים להיות ממוינים בסדר עולה. אם זה לא, אתה יכול לקבל תוצאות שגויות.שים לב שניתן לבצע את האמור לעיל באמצעות נוסחת VLOOKUP שלהלן:
= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, TRUE)
אבל פונקציית MATCH יכולה להתקדם צעד נוסף כאשר מדובר בהתאמה משוערת.
תוכל גם לקבל נתונים יורדים ולהשתמש בשילוב INDEX MATCH כדי למצוא את התוצאה. לדוגמה, אם אני משנה את סדר טבלת הציונים (כפי שמוצג להלן), אני עדיין יכול למצוא את הציונים של התלמידים.
לשם כך כל שעלי לעשות הוא לשנות את הארגומנט [match_type] ל- -1.
להלן הנוסחה בה השתמשתי:
= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)VLOOKUP יכול גם לבצע התאמה משוערת אך רק כאשר הנתונים ממוינים בסדר עולה (אך זה לא עובד אם הנתונים ממוינים בסדר יורד).
דוגמה 7: חיפושים רגישים לאותיות
עד כה כל החיפושים שעשינו לא היו רגישים לאותיות.
המשמעות היא שבין אם ערך החיפוש היה ג'ים או ג'ים או ג'ים, זה לא משנה. תקבל את אותה התוצאה.
אבל מה אם אתה רוצה שהחיפוש יהיה רגיש לאותיות.
בדרך כלל זה קורה כאשר יש לך מערכי נתונים גדולים ואפשרות לחזרה או שמות/מזהים מובחנים (כשההבדל היחיד הוא זה)
לדוגמה, נניח שיש לי את מערך הנתונים הבא של תלמידים שבהם ישנם שני תלמידים עם השם ג'ים (ההבדל היחיד הוא שאחד מופיע בתור ג'ים ואחד אחר בתור ג'ים).
שימו לב שיש שני תלמידים עם אותו שם - ג'ים (תא A2 ו- A5).
מכיוון שחיפוש רגיל לא יעבוד, עליך לבצע חיפוש תלוי -רישיות.
להלן הנוסחה שתתן לך את התוצאה הנכונה. מכיוון שזו נוסחת מערך, עליך להשתמש ב- Control + Shift + Enter.
= INDEX ($ B $ 2: $ B $ 11, MATCH (TRUE, EXACT (D3, A2: A11), 0), 1)
תן לי להסביר כיצד נוסחה זו עובדת.
הפונקציה EXACT בודקת התאמה מדויקת של ערך החיפוש (שהוא 'ג'ים' במקרה זה). הוא עובר על כל השמות ומחזיר שקר אם זה לא התאמה ואמיתי אם זה התאמה.
אז הפלט של הפונקציה EXACT בדוגמה זו הוא - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
שים לב שיש רק TRUE אחד, כלומר כאשר הפונקציה EXACT מצאה התאמה מושלמת.
לאחר מכן הפונקציה MATCH מוצאת את המיקום של TRUE במערך המוחזר על ידי הפונקציה EXACT, שהיא 4 בדוגמה זו.
ברגע שיש לנו את המיקום, הפונקציה INDEX משתמשת בה כדי למצוא את הסימנים.
דוגמה 8: מצא את ההתאמה הקרובה ביותר
בואו נתקדם מעט עכשיו.
נניח שיש לך מערך נתונים שבו אתה רוצה למצוא את האדם בעל ניסיון העבודה הקרוב ביותר לניסיון הנדרש (מוזכר בתא D2).
אמנם נוסחאות חיפוש אינן מיועדות לעשות זאת, אך תוכל לשלב זאת עם פונקציות אחרות (כגון MIN ו- ABS) כדי לבצע זאת.
להלן הנוסחה שתמצא את האדם בעל הניסיון הקרוב לזה הנדרש ויחזיר את שמו של האדם. שים לב שהניסיון צריך להיות הכי קרוב (שיכול להיות פחות או יותר).
= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))
מכיוון שזו נוסחת מערך, עליך להשתמש ב- Control + Shift + Enter.
הטריק בנוסחה זו הוא לשנות את ערך החיפוש ומערך החיפוש כדי למצוא את ההבדל המינימלי בניסיון בערכים הנדרשים והאמיתיים.
לפני שאני מסביר את הנוסחה, הבה נבין כיצד היית עושה זאת באופן ידני.
תעבור על כל תא בעמודה ב 'ותמצא את ההבדל בחוויה בין מה שנדרש לזה שיש לאדם. ברגע שיש לך את כל ההבדלים, תמצא את המינימום ותביא את שמו של אותו אדם.
זה בדיוק מה שאנחנו עושים עם הנוסחה הזו.
הרשה לי להסביר.
ערך החיפוש בנוסחת MATCH הוא MIN (ABS (D2-B2: B15)).
חלק זה נותן לך את ההבדל המינימלי בין הניסיון הנתון (שהוא 2.5 שנים) לבין כל החוויות האחרות. בדוגמה זו הוא מחזיר 0.3
שים לב שהשתמשתי ב- ABS כדי לוודא שאני מחפש את הקרוב ביותר (שיכול להיות פחות או יותר מהחוויה הנתונה).
כעת, ערך מינימלי זה הופך לערך החיפוש שלנו.
מערך החיפוש בפונקציית MATCH הוא ABS (D2- $ B $ 2: $ B $ 15).
זה נותן לנו מערך מספרים שממנו נגרע 2.5 (הניסיון הנדרש).
אז עכשיו יש לנו ערך חיפוש (0.3) ומערך חיפוש ({6.8; 0.8; 19.5; 21.8; 14.5; 11.2; 0.3; 9.2; 2; 9.8; 14.8; 0.4; 23.8; 2.9})
הפונקציה MATCH מוצאת את המיקום של 0.3 במערך זה, שהוא גם המיקום של שם האדם שיש לו את החוויה הקרובה ביותר.
לאחר מכן מספר המיקום הזה משמש את הפונקציה INDEX להחזרת שם האדם.
קשור בנושא: מצא את ההתאמה הקרובה ביותר ב- Excel (דוגמאות באמצעות נוסחאות חיפוש)
לחץ כאן להורדת קובץ הדוגמה
דוגמה 9: השתמש ב- INDEX MATCH עם תווים כלליים
אם אתה רוצה לחפש ערך כשיש התאמה חלקית, עליך להשתמש בתווים כלליים.
לדוגמה, להלן מערך של שם החברה ושווי השוק שלה ואתה רוצה לקבל את שווי השוק. נתונים לשלוש החברות מימין.
מכיוון שלא מדובר בהתאמות מדויקות, לא ניתן לבצע בדיקה רגילה במקרה זה.
אבל אתה עדיין יכול לקבל את הנתונים הנכונים על ידי שימוש בכוכבית (*), שהיא תו כללי.
להלן הנוסחה שתיתן לך את הנתונים על ידי התאמת שמות החברות מהעמודה הראשית ושליפת נתון שווי השוק עבורה.
= INDEX ($ B $ 2: $ B $ 10, MATCH (D2 & "*", $ A $ 2: $ A $ 10,0), 1)
תן לי להסביר כיצד נוסחה זו עובדת.
מכיוון שאין התאמה מדויקת של ערכי החיפוש, השתמשתי D2 & "*" כערך החיפוש בפונקציית MATCH.
כוכבית היא תו כללי המייצג כל מספר תווים. המשמעות היא שאפל* בנוסחה תהיה כל מחרוזת טקסט שמתחילה במילה אפל ויכולה לכלול מספר תווים לאחריה.
ולכן כאשר תפוח עץ* משמש כערך החיפוש ונוסחת MATCH מחפשת אותו בעמודה A, היא מחזירה את המיקום של 'Apple Inc.', כפי שהיא מתחילה במילה Apple.
תוכל גם להשתמש בתווים כלליים כדי למצוא מחרוזות טקסט בהן ערך החיפוש נמצא בין לבין. לדוגמה, אם אתה משתמש ב- * Apple * כערך החיפוש, הוא ימצא כל מחרוזת שיש בה את המילה apple בכל מקום.
הערה: טכניקה זו פועלת היטב כאשר יש לך רק מופע אחד של התאמה. אבל אם יש לך מספר מופעים של התאמה (למשל Apple Inc ו- Apple Corporation, אז פונקציית MATCH תחזיר את המיקום של מופע ההתאמה הראשון בלבד.
דוגמה 10: בדיקת שלוש כיוונים
זהו שימוש מתקדם ב- INDEX MATCH, אך עדיין אכסה אותו כדי להראות לכם את העוצמה של השילוב הזה.
זכור שאמרתי שלפונקציה INDEX יש שני תחביר:
= INDEX (מערך, שורה_מספר, [col_num]) = INDEX (array, row_num, [col_num], [area_num])
עד כה בכל הדוגמאות שלנו, השתמשנו רק בדוגמא הראשונה.
אך לחיפוש תלת כיווני, עליך להשתמש בתחביר השני.
תן לי להסביר תחילה מה המשמעות של מבט משולש.
בחיפוש דו כיווני, אנו משתמשים בנוסחת INDEX MATCH כדי לקבל את הציונים כאשר יש לנו את שם התלמיד ואת שם הנושא. לדוגמה, הבאת סימני ג'ים במתמטיקה היא חיפוש דו כיווני.
מבט משולש יוסיף לו ממד נוסף. לדוגמה, נניח שיש לך מערך נתונים כפי שמוצג להלן ואתה רוצה לדעת את הציון של ג'ים במתמטיקה בבחינה באמצע טווח, אז זה יהיה חיפוש תלת כיווני.
להלן הנוסחה שתיתן את התוצאה.
= INDEX (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "בדיקת יחידה", 1, IF (G $ 3 = "אמצע טווח", 2,3))))
הנוסחה לעיל בדקה שלושה דברים - שם התלמיד, הנושא והבחינה. לאחר שהוא מוצא את הערך הנכון, הוא מחזיר אותו בתא.
תן לי להסביר כיצד נוסחה זו עובדת על ידי פירוק הנוסחה לחלקים.
- מערך - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): במקום להשתמש במערך יחיד, במקרה זה, השתמשתי בשלושה מערכים בתוך סוגריים.
- row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): הפונקציה MATCH משמשת לאיתור המיקום של שם התלמיד בתא $ F $ 5 ברשימת שם התלמיד.
- col_num - MATCH (G $ 4, $ B $ 2: $ D $ 2,0): הפונקציה MATCH משמשת לאיתור המיקום של שם הנושא בתא $ B $ 2 ברשימת שם הנבדק.
- [area_num] - IF (G $ 3 = "בדיקת יחידה", 1, IF (G $ 3 = "אמצע טווח", 2,3)): ערך מספר האזור מספר לפונקציה INDEX באיזה משלושת המערכים להשתמש כדי להביא את הערך. אם הבחינה היא מונח יחידה, הפונקציה IF תחזיר 1 והפונקציה INDEX תשתמש במערך הראשון כדי להביא את הערך. אם הבחינה היא לטווח בינוני, נוסחת IF תחזיר 2, אחרת היא תחזיר 3.
זוהי דוגמה מתקדמת לשימוש ב- INDEX MATCH, ואין סיכוי שתמצא מצב כשאתה צריך להשתמש בזה. אבל עדיין טוב לדעת מה נוסחאות Excel יכולות לעשות.
לחץ כאן להורדת קובץ הדוגמה
מדוע INDEX/MATCH עדיף על VLOOKUP?
או שזה?
כן, זה - ברוב המקרים.
אני אציג את התיק שלי בעוד זמן מה.
אבל לפני שאני עושה את זה, הרשה לי לומר זאת - VLOOKUP היא פונקציה שימושית ביותר ואני אוהב אותה. זה יכול לעשות הרבה דברים ב- Excel ואני משתמש בו מדי פעם בעצמי. אחרי שאמרתי את זה, זה לא אומר שלא יכול להיות משהו טוב יותר, ו- INDEX/MATCH (עם יותר גמישות ופונקציונליות) עדיף.
אז אם אתה רוצה לבצע בדיקה בסיסית, מוטב שתשתמש ב- VLOOKUP.
INDEX/MATCH הוא VLOOKUP על סטרואידים. וברגע שתלמד INDEX/MATCH, ייתכן שתמיד תעדיף להשתמש בו (במיוחד בגלל הגמישות שיש לו).
מבלי למתוח אותו רחוק מדי, הרשה לי לתת לך במהירות את הסיבות מדוע INDEX/MATCH עדיף על VLOOKUP.
INDEX/MATCH יכול להסתכל שמאלה (כמו גם מימין) של ערך החיפוש
כיסיתי את זה באחת הדוגמאות למעלה.
אם יש לך ערך שנמצא משמאל לערך החיפוש, אינך יכול לעשות זאת באמצעות VLOOKUP
לפחות לא רק עם VLOOKUP.
כן, אתה יכול לשלב VLOOKUP עם נוסחאות אחרות ולסיים את זה, אבל זה נהיה מסובך ומבולגן.
INDEX/MATCH, לעומת זאת, מיועד לחפש בכל מקום (שמאלה, ימינה, למעלה או למטה)
INDEX/MATCH יכול לעבוד עם טווחים אנכיים ואופקיים
שוב, עם כל הכבוד ל- VLOOKUP, זה לא נועד לעשות זאת.
אחרי הכל, ה- V ב- VLOOKUP מייצג אנכי.
VLOOKUP יכול לעבור רק על נתונים שהם אנכיים, בעוד INDEX/MATCH יכול לעבור נתונים אנכית וגם אופקית.
כמובן, יש את הפונקציה HLOOKUP לדאוג לחיפוש אופקי, אבל זה לא VLOOKUP אז … נכון?
אני אוהב את העובדה ששילוב INDEX MATCH גמיש מספיק לעבודה עם נתונים אנכיים ואופקיים כאחד.
VLOOKUP לא יכול לעבוד עם נתונים יורדים
כשמדובר בהתאמה המשוערת, VLOOKUP ו- INDEX/MATCH נמצאים באותה רמה.
אך INDEX MATCH תופס את הנקודה מכיוון שהוא יכול להתמודד גם עם נתונים הנמצאים בסדר יורד.
אני מציג זאת באחת הדוגמאות במדריך זה שבו עלינו למצוא את ציון התלמידים על סמך טבלת הציונים. אם הטבלה ממוינת בסדר יורד, VLOOKUP לא יפעל (אבל INDEX MATCH יעבוד).
INDEX/MATCH יכול להיות מעט מהיר יותר
אני אהיה כנה. לא ביצעתי את המבחן הזה בעצמי.
אני מסתמך על החוכמה של אמן אקסל - צ'רלי קיד.
ההבדל במהירות ב- VLOOKUP וב- INDEX/MATCH כמעט ולא מורגש כאשר יש לך ערכות נתונים קטנות. אבל אם יש לך אלפי שורות ועמודות רבות, זה יכול להיות גורם מכריע.
במאמרו קובע צ'ארלי קיד:
"במקרה הגרוע ביותר, שיטת INDEX-MATCH היא מהירה בערך כמו VLOOKUP; במיטבה, זה הרבה יותר מהיר ".
INDEX/MATCH אינו תלוי במיקום העמודה בפועל
אם יש לך מערך נתונים כפי שמוצג להלן כשאתה מביא את הציון של ג'ים בפיזיקה, אתה יכול לעשות זאת באמצעות VLOOKUP.
וכדי לעשות זאת, תוכל לציין את מספר העמודה כ- 3 ב- VLOOKUP.
הכל בסדר.
אבל מה אם אני מוחק את העמודה מתמטיקה.
במקרה זה, נוסחת VLOOKUP תישבר.
למה? - מכיוון שהיה מקודד לשימוש בעמודה השלישית, וכשאני מוחק עמודה בין לבין, העמודה השלישית הופכת לעמודה השנייה.
השימוש ב- INDEX/MATCH, במקרה זה, טוב יותר מכיוון שאתה יכול להפוך את מספר העמודה לדינאמי באמצעות MATCH. אז במקום מספר עמודה, הוא בודק את שם הנושא ומשתמש בזה כדי להחזיר את מספר העמודה.
אין ספק שאתה יכול לעשות זאת על ידי שילוב של VLOOKUP עם MATCH, אך אם אתה משלב בכל זאת, מדוע שלא תעשה זאת עם INDEX שהוא הרבה יותר גמיש.
בעת שימוש ב- INDEX/MATCH, תוכל להוסיף/למחוק עמודות בבטחה במערך הנתונים שלך.
למרות כל הגורמים הללו, יש סיבה ש- VLOOKUP כל כך פופולרי.
וזו סיבה גדולה.
קל יותר להשתמש ב- VLOOKUP
VLOOKUP לוקח רק ארבעה ארגומנטים לכל היותר. אם אתה יכול לעטוף את הראש סביב ארבעת אלה, אתה מוכן ללכת.
ומכיוון שרוב מקרי החיפוש הבסיסיים מטופלים גם על ידי VLOOKUP, היא הפכה במהירות לפונקציית Excel הפופולרית ביותר.
אני קורא לזה מלך פונקציות האקסל.
INDEX/MATCH, לעומת זאת, קצת יותר קשה לשימוש. יתכן שתקבל ניסוי אם תתחיל להשתמש בו, אבל למתחילים קל יותר להסביר וללמוד VLOOKUP.
וזה לא משחק סכום אפס.
לכן, אם אתה חדש בעולם החיפוש ואינך יודע כיצד להשתמש ב- VLOOKUP, כדאי ללמוד זאת.
יש לי מדריך מפורט על השימוש ב- VLOOKUP ב- Excel (עם הרבה דוגמאות)
הכוונה שלי במאמר זה היא לא להציב שתי פונקציות מדהימות זו מול זו. רציתי להראות לך את העוצמה של שילוב INDEX MATCH וכל הדברים הגדולים שהוא יכול לעשות.
מקווה שמצאת מאמר זה שימושי.
ספר לי את דעתך בסעיף ההערות, ובמידה ותמצא טעות במדריך זה, אנא יידע אותי.
