נהל פעילויות, תוכניות, תקציבים – זה קל עם Microsoft 365

חיפוש ערכים באמצעות VLOOKUP‏, INDEX או MATCH

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

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

הפונקציות VLOOKUP ו- HLOOKUP , יחד עם INDEX ו -match,הן כמה מהפונקציות היעילות ביותר ב-Excel.

הערה: התכונה ' אשף בדיקת מידע ' אינה זמינה עוד ב-Excel.

להלן דוגמה של אופן השימוש ב-VLOOKUP.

‎=VLOOKUP(B2,C2:E7,3,TRUE)‎

בדוגמה זו, B2 הוא הארגומנטהראשון – רכיב של נתונים שהפונקציה צריכה לפעול. עבור VLOOKUP, הארגומנט הראשון הוא הערך שברצונך לחפש. ארגומנט זה יכול להיות הפניה לתא, או ערך קבוע כגון "smith" או 21,000. הארגומנט השני הוא טווח התאים, C2-: E7, כדי לחפש את הערך שברצונך לחפש. הארגומנט השלישי הוא העמודה בטווח תאים זה המכיל את הערך שאתה מחפש.

הארגומנט הרביעי הוא אופציונלי. הזן TRUE או FALSE. אם תזין TRUE או תשאיר את הארגומנט ריק, הפונקציה תחזיר התאמה בקירוב של הערך שאתה מציין בארגומנט הראשון. אם תזין FALSE, הפונקציה תתאים לערך המספק את הארגומנט הראשון. במילים אחרות, השארת הארגומנט הרביעי ריק — או הזנת TRUE — מעניקה לך גמישות רבה יותר.

דוגמה זו מראה לך כיצד הפונקציה פועלת. כאשר אתה מזין ערך בתא B2 (הארגומנט הראשון), הפונקציה VLOOKUP מחפשת את התאים בטווח C2: E7 (ארגומנט שני) ומחזירה את ההתאמה הקרובה ביותר בעמודה השלישית בטווח, בעמודה E (ארגומנט 3).

שימוש אופייני בפונקציה VLOOKUP

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

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

שימוש באינדקס ובהתאמה במקום VLOOKUP

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

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

שימוש ב- INDEX וב- MATCH לבדיקת מידע לפי ערך

לקבלת דוגמאות נוספות של שימוש באינדקס והתאמה במקום VLOOKUP, עיין במאמר https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ על-ידי Bill Jelen, Microsoft MVP.

נסה זאת

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

דוגמה של VLOOKUP בעבודה

העתק את הנתונים הבאים לגיליון אלקטרוני ריק.

עצה: לפני הדבקת הנתונים ב-Excel, הגדר את רוחב העמודות A עד C ל-250 פיקסלים ולחץ על גלישת טקסט (הכרטיסיה בית, הקבוצהיישור ).

צפיפות

צמיגות

טמפרטורה

0.457

3.55

500

0.525

3.25

400

0.606

2.93

300

0.675

2.75

250

0.746

2.57

200

0.835

2.38

150

0.946

2.17

100

1.09

1.95

50

1.29

1.71

0

נוסחה

תיאור

תוצאה

‎=VLOOKUP(1,A2:C10,2)‎

באמצעות התאמה מקורבת, הפונקציה מחפשת את הערך 1 בעמודה A, מוצאת את הערך הגדול ביותר הקטן מ- 1 או שווה לו, שהוא 0.946, ולאחר מכן מחזירה את הערך מעמודה B באותה שורה.

2.17

‎=VLOOKUP(1,A2:C10.3,TRUE)‎

באמצעות התאמה מקורבת, הפונקציה מחפשת את הערך 1 בעמודה A, מוצאת את הערך הגדול ביותר בעמודה A שקטן מ- 1 או שווה לו, שהוא 0.946, ולאחר מכן מחזירה את הערך מעמודה C באותה שורה.

100

‎=VLOOKUP(0.7,A2:C10,3,FALSE)‎

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

‎#N/A

‎=VLOOKUP(0.1,A2:C10,2,TRUE)‎

באמצעות התאמה מקורבת, הפונקציה מחפשת את הערך 0.1 בעמודה A. מכיוון ש- 0.1 קטן מהערך הקטן ביותר בעמודה A, מוחזרת שגיאה.

‎#N/A

‎=VLOOKUP(2,A2:C10,2,TRUE)‎

באמצעות התאמה מקורבת, הפונקציה מחפשת את הערך 2 בעמודה A, מוצאת את הערך הגדול ביותר הקטן מ- 2 או שווה לו בעמודה A, שהוא 1.29, ולאחר מכן מחזירה את הערך מעמודה B באותה שורה.

1.71

דוגמה של הפונקציה HLOOKUP

העתק את כל התאים בטבלה זו והדבק אותה בתא A1 בגליון עבודה ריק של Excel.

עצה: לפני הדבקת הנתונים ב-Excel, הגדר את רוחב העמודות A עד C ל-250 פיקסלים ולחץ על גלישת טקסט (הכרטיסיה בית, הקבוצהיישור ).

Axles

Bearings

Bolts

4

4

9

5

7

10

6

8

11

נוסחה

תיאור

תוצאה

‎‎=HLOOKUP("Axles", A1:C4, 2, TRUE) ‎‎

חיפוש צירים (Axles) בשורה 1, והחזרת הערך משורה 2 הנמצא באותה עמודה (עמודה A).

4

‎‎=HLOOKUP("Bearings", A1:C4, 3, FALSE) ‎‎

חיפוש מיסבים (Bearings) בשורה 1, והחזרת הערך משורה 3 הנמצא באותה עמודה (עמודה B).

7

‎‎=HLOOKUP("B", A1:C4, 3, TRUE) ‎‎

חיפוש "B" בשורה 1, והחזרת הערך משורה 3 הנמצא באותה עמודה. מאחר ולא נמצאה התאמה מדויקת עבור "B", הערך הגדול ביותר בשורה 1 שהנו קטן מ- "B" נמצא בשימוש: "Axles", בעמודה A.

5

‎‎=HLOOKUP("Bolts", A1:C4, 4) ‎‎

חיפוש ברגים (Bolts) בשורה 1, והחזרת הערך משורה 4 הנמצא באותה עמודה (עמודה C).

11

‎‎=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE) ‎‎

חיפוש המספר 3 בקבוע המערך בעל שלוש השורות, והחזרת הערך משורה 2 הנמצא באותה עמודה (במקרה זה, שלישית). ישנן שלוש שורות ערכים בקבוע המערך, שכל אחת מהן מופרדת באמצעות נקודה-פסיק (;). מכיוון ש- "c" נמצא בשורה 2 ובאותה עמודה שבה נמצא 3, "c" מוחזר.

c

דוגמאות של אינדקס והתאמה

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

העתק את כל התאים בטבלה זו והדבק אותה בתא A1 בגליון עבודה ריק של Excel.

עצה: לפני הדבקת הנתונים ב-Excel, הגדר את רוחב העמודות A עד D ל-250 פיקסלים ולחץ על גלישת טקסט (הכרטיסיה בית, הקבוצהיישור ).

חשבונית

עיר

תאריך חשבונית

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

3115

Atlanta

7/4/12

‎="Atlanta = "&INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),3),"m/d/yy")‎

3137

Atlanta

9/4/12

‎="Austin = "&INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),3),"m/d/yy")‎

3154

Atlanta

11/4/12

‎="Dallas = "&INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),3),"m/d/yy")‎

3191

Atlanta

21/4/12

‎="New Orleans = "&INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),3),"m/d/yy")‎

3293

Atlanta

25/4/12

‎="Tampa = "&INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),3),"m/d/yy")‎

3331

Atlanta

27/4/12

3350

Atlanta

28/4/12

3390

Atlanta

1/5/12

3441

Atlanta

2/5/12

3517

Atlanta

8/5/12

3124

Austin

9/4/12

3155

Austin

11/4/12

3177

Austin

19/4/12

3357

Austin

28/4/12

3492

Austin

6/5/12

3316

דאלאס

25/4/12

3346

דאלאס

28/4/12

3372

Dallas

1/5/12

3414

Dallas

1/5/12

3451

דאלאס

2/5/12

3467

דאלאס

2/5/12

3474

דאלאס

4/5/12

3490

דאלאס

5/5/12

3503

Dallas

8/5/12

3151

New Orleans

9/4/12

3438

New Orleans

2/5/12

3471

New Orleans

4/5/12

3160

Tampa

18/4/12

3328

Tampa

26/4/12

3368

Tampa

29/4/12

3420

Tampa

1/5/12

3501

Tampa

6/5/12

למידע נוסף

כרטיס לעיון מהיר:

בדיקת מידע ופונקציות הפניה מרענון VLOOKUP (reference)

השתמש בארגומנט table_array בפונקציה VLOOKUP

זקוק לעזרה נוספת?

שפר את הכישורים שלך ב- Office
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×