מדוע INDEX ו-MATCH עדיפים על VLOOKUP באקסל

הסברנו בעבר למתחילים כיצד להשתמש בפונקציות הבסיסיות של VLOOKUP (באנגלית VLOOKUP, הקיצור מייצג "פונקציית חיפוש אנכית"). ולמשתמשים מנוסים הוצגו עוד כמה נוסחאות מסובכות.

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

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

יסודות INDEX MATCH

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

תחביר ושימוש בפונקציות INDEX

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

=INDEX(מערך, מספר שורה, מספר עמודה):

  • מערך – האזור בו יתקיים החיפוש;
  • מספר שורה – מספר הקו שיש לחפש במערך שצוין. אם מספר השורה אינו ידוע, יש לציין את מספר העמודה;
  • מספר עמודה - מספר העמודה שתמצא במערך שצוין. אם הערך אינו ידוע, יש צורך במספר שורה.

דוגמה לנוסחה פשוטה:

=INDEX(A1:S10,2,3)‎

הפונקציה תחפש בטווח שבין A1 ל-C10. המספרים מראים מאיזו שורה (2) ועמודה (3) להציג את הערך הרצוי. התוצאה תהיה תא C2.

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

תחביר ושימוש בפונקציית MATCH

הפונקציה MATCH() מחפשת את הערך הרצוי ומציגה את מספרו המשוער באזור החיפוש שצוין.

תחביר searchpos() נראה כך:

=MATCH(ערך לחיפוש, מערך לחיפוש, סוג התאמה)

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

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

=EXPOSE(London,B1:B3,0)

כיצד לעבוד עם הפונקציה INDEX MATCH 

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

=INDEX(עמודה שממנה מוחזר הערך, MATCH(ערך לחיפוש, עמודה לחיפוש, 0))

עדיין מתקשה להבין איך זה עובד? אולי דוגמה תסביר יותר טוב. נניח שיש לך רשימה של בירות עולמיות ואוכלוסייתן:

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

=INDEX(C2:C10, MATCH(Japan, A2:A10,0))

הסבר:

  • הפונקציה MATCH() מחפשת את הערך - "Japan" במערך A2:A10 ומחזירה את המספר 3, כי יפן היא הערך השלישי ברשימה. 
  • נתון זה הולך למספר שורה” בנוסחת INDEX() ואומרת לפונקציה להדפיס ערך משורה זו.

אז הנוסחה לעיל הופכת לנוסחה הסטנדרטית INDEX(C2:C10,3). הנוסחה מחפשת מהתאים C2 עד C10 ומחזירה נתונים מהתא השלישי בטווח הזה, כלומר C4, מכיוון שהספירה לאחור מתחילה מהשורה השנייה.

לא רוצה לרשום את שם העיר בנוסחה? לאחר מכן כתוב אותו בכל תא, אמור F1, והשתמש בו כהפניה בנוסחת MATCH() . ובסופו של דבר אתה מקבל נוסחת חיפוש דינמית:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

מדוע INDEX ו-MATCH עדיפים על VLOOKUP באקסל

חשוב! מספר שורות פנימה מערך INDEX() חייב להיות זהה למספר השורות ב מערך נחשב ב-MATCH(), אחרת תקבל את התוצאה השגויה.

חכה רגע, למה לא פשוט להשתמש בנוסחה VLOOKUP()?

=VLOOKUP(F1, A2:C10, 3, False)

 מה הטעם לבזבז זמן בניסיון להבין את כל המורכבויות האלה של INDEX MATCH?

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

INDEX MATCH או VLOOKUP

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

להלן היתרונות העיקריים של INDEX() ו-MATCH() על פני VLOOKUP():

 

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

 

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

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

  1. אין הגבלה על נפחי החיפוש. בעת שימוש ב-VLOOKUP(), המספר הכולל של קריטריוני החיפוש לא יעלה על 255 תווים אחרת תקבל #VALUE! אז אם הנתונים שלך מכילים מספר רב של תווים, INDEX() ו-MATCH() הם האפשרות הטובה ביותר.
  2. מהירות עיבוד גבוהה. אם השולחנות שלך קטנים יחסית, לא סביר שתבחין בהבדל. אבל, אם הטבלה מכילה מאות או אלפי שורות, ובהתאם, יש מאות ואלפי נוסחאות, INDEX () ו-MATCH () יתמודדו הרבה יותר מהר מאשר VLOOKUP (). העובדה היא שאקסל יעבד רק את העמודות המצוינות בנוסחה, במקום לעבד את כל הטבלה. 

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

דוגמאות נוסחאות 

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

נוסחה לחיפוש מימין לשמאל

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

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

בתא G1, נכתוב את הערך שיימצא, ולאחר מכן נשתמש בנוסחה הבאה כדי לחפש בטווח C1:C10 ולהחזיר את הערך המתאים מ-A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

מדוע INDEX ו-MATCH עדיפים על VLOOKUP באקסל

מיידי. אם אתה מתכנן להשתמש בנוסחה זו עבור תאים מרובים, הקפד לתקן את הטווחים באמצעות כתובת מוחלטת (לדוגמה, $A$2: $A$10 ו-$C$2: 4C$10).

אינדקס חשוף יותר חשוף יותר  כדי לחפש בעמודות ובשורות

בדוגמאות לעיל, השתמשנו בפונקציות אלה כתחליף ל-VLOOKUP() כדי להחזיר ערכים מטווח מוגדר מראש של שורות. אבל מה אם אתה צריך לעשות מטריצה ​​או חיפוש דו-צדדי?

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

=INDEX(מערך, MATCH(ערך חיפוש אנכי, עמודת חיפוש, 0), MATCH(ערך חיפוש אופקי, שורת חיפוש, 0))

הבה נסתכל בטבלה למטה וננסה ליצור נוסחה INDEX() EXPRESS() EXPRESS() על מנת להציג נתונים דמוגרפיים במדינה מסוימת עבור שנה נבחרת.

מדינת היעד נמצאת בתא G1 (חיפוש אנכי) ושנת היעד נמצאת בתא G2 (חיפוש אופקי). הנוסחה תיראה כך:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

מדוע INDEX ו-MATCH עדיפים על VLOOKUP באקסל

איך הנוסחה הזו עובדת

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

  • MATCH(G1,A2:A11,0) – מחפש ערך (G1) בטווח A2:A11 ומראה את המספר של ערך זה, במקרה שלנו הוא 2;
  • SEARCH(G2,B1:D1,0) – מחפש ערך (G2) בטווח B1:D1. במקרה זה, התוצאה הייתה 3.

מספרי השורות והעמודות שנמצאו נשלחים לערך המתאים בנוסחת INDEX() :

=INDEX(B2:D11,2,3)‎

כתוצאה מכך, יש לנו ערך שנמצא בתא בצומת של 2 שורות ו-3 עמודות בטווח B2:D11. והנוסחה מציגה את הערך הרצוי, שנמצא בתא D3.

חפש לפי מספר תנאים עם INDEX ו-MATCH

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

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

הנה נוסחת החיפוש הכללית של ריבוי תנאים עבור INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

הפתק: יש להשתמש בנוסחה זו יחד עם קיצור המקלדת CTRL+SHIFT+ENTER.

נניח שאתה צריך למצוא את הערך שאתה מחפש בהתבסס על 2 תנאים: קוֹנֶה и מוצר.

זה דורש את הנוסחה הבאה:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

בנוסחה זו, C2:C10 הוא הטווח שבו יתבצע החיפוש, F1 - המצב הזה, A2:A10 — הוא הטווח להשוואת המצב, F2 – תנאי 2, V2:V10 – טווח להשוואה של מצב 2.

אל תשכח ללחוץ על השילוב בסוף העבודה עם הנוסחה CTRL+SHIFT+ENTER - Excel יסגור אוטומטית את הנוסחה עם סוגרים מסולסלים, כפי שמוצג בדוגמה:

מדוע INDEX ו-MATCH עדיפים על VLOOKUP באקסל

אם אינך רוצה להשתמש בנוסחת מערך לעבודה שלך, הוסף עוד INDEX() לנוסחה והקש ENTER, זה ייראה כמו בדוגמה:

מדוע INDEX ו-MATCH עדיפים על VLOOKUP באקסל

איך הנוסחאות האלה עובדות

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

נוסחה ללא מערך תלויה ביכולת של INDEX() לטפל בהם בעצמו. ה- INDEX() השני בנוסחה תואם לשווא (0), כך שהוא מעביר את כל המערך עם הערכים הללו לנוסחת MATCH() . 

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

AVERAGE, MAX ו-MIN ב-INDEX ו-MATCH

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

INDEX MATCH ו-MAX

כדי למצוא את הערך הגדול ביותר בעמודה D ולהציגו בעמודה C, השתמש בנוסחה: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH ו-MIN

כדי למצוא את הערך הקטן ביותר בעמודה D ולהציגו בעמודה C, השתמש בנוסחה הבאה:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SEARCH INDEX ו-SERPENT

כדי למצוא את הערך הממוצע בעמודה D ולהציג את הערך הזה ב-C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

בהתאם לאופן כתיבת הנתונים שלך, הארגומנט השלישי ל-MATCH() הוא 1, 0 או -1:

  • אם העמודות ממוינות בסדר עולה, קבע 1 (ואז הנוסחה תחשב את הערך המקסימלי, הקטן או שווה לערך הממוצע);
  • אם המיון יורד, אז -1 (הנוסחה תוציא את הערך המינימלי שגדול או שווה לממוצע);
  • אם מערך הבדיקה מכיל ערך השווה בדיוק לממוצע, הגדר אותו ל-0. 

 בדוגמה שלנו, האוכלוסייה ממוינת בסדר יורד, אז שמנו -1. והתוצאה היא טוקיו, שכן ערך האוכלוסייה (13,189) הוא הקרוב ביותר לערך הממוצע (000).

מדוע INDEX ו-MATCH עדיפים על VLOOKUP באקסל

VLOOKUP() יכול גם לבצע חישובים כאלה, אבל רק כנוסחת מערך: VLOOKUP עם AVERAGE, MIN ו-MAX.

INDEX MATCH ו-ESND/IFERROR

סביר להניח שכבר שמתם לב שאם הנוסחה לא יכולה למצוא את הערך הרצוי, היא זורקת שגיאה # N / A. אתה יכול להחליף את הודעת השגיאה הרגילה במשהו אינפורמטיבי יותר. לדוגמה, הגדר את הארגומנט בנוסחה ב-XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

בנוסחה זו, אם תזין נתונים שאינם בטבלה, הטופס ייתן לך את ההודעה שצוינה.

מדוע INDEX ו-MATCH עדיפים על VLOOKUP באקסל

אם אתה רוצה לתפוס את כל השגיאות, אז למעט ב-XNUMX ניתן להשתמש בו טעות:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "משהו השתבש!")

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

אנו מקווים שמצאתם את המדריך שלנו לשימוש בפונקציית INDEX MATCH() מועיל.

השאירו תגובה