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

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

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

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

הנה תזכורת מהירה לגבי שימוש ב- VLOOKUP.

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

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

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

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

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

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

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

נסה זאת

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

VLOOKUP בפעולה

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

רמז    לפני הדבקת נתונים ב- 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 באותה שורה.

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

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

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

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

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

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

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

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

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

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

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

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

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

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).

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

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

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

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

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

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

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

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

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

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

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

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

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

INDEX ו- MATCH בפעולה

דוגמה זו משתמשת בשילוב של הפונקציות 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

Dallas

25/4/12

3346

Dallas

28/4/12

3372

Dallas

1/5/12

3414

Dallas

1/5/12

3451

Dallas

2/5/12

3467

Dallas

2/5/12

3474

Dallas

4/5/12

3490

Dallas

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

עוד על פונקציות בדיקת מידע

לראש הדף

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

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

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

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

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

×