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

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

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

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

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

להלן דוגמה של אופן השימוש בפונקציה VLOOKUP.

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

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

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

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

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

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

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

שימוש INDEX ו- MATCH במקום VLOOKUP

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

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

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

נסה זאת

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

דוגמה ל- 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 דוגמאות

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

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

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

חשבונית

City

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

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

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

Dallas

25/4/12

3346

דאלאס

28/4/12

3372

דאלאס

1/5/12

3414

דאלאס

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

אור יהודה

1/5/12

3501

Tampa

6/5/12

למידע נוסף

כרטיס לעיון מהיר: רענון בנושא VLOOKUP

פונקציות בדיקת מידע והפניה (חומר עזר)

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

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

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

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

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

×