עצה: נסה להשתמש בפונקציות XLOOKUP ו - XMATCH החדשות, בגירסאות משופרות של הפונקציות המתוארות במאמר זה. פונקציות חדשות אלה פועלות בכל כיוון ומחזירות התאמות מדויקות כברירת מחדל, ובכך מקלות ונוחות יותר להשתמש בהן מאשר פעילויות הקדם שלהן.
נניח שיש לך רשימה של מספרי מיקום משרדים, ועלך לדעת אילו עובדים נמצאים בכל משרד. הגיליון האלקטרוני ענק, כך שייתכן שתרצה לחשוב כי זו משימה מאתגרת. למעשה, קל מאוד לבצע זאת באמצעות פונקציית בדיקת מידע.
הפונקציות 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 (ארגומנט שלישי).
הארגומנט הרביעי ריק, ולכן הפונקציה מחזירה התאמה משוערת. אם היא לא היתה עושה זאת, היה עליך להזין אחד מהערכים בעמודות C או D כדי לקבל תוצאה כלשהי.
כאשר אתה מרגיש בנוח עם VLOOKUP, הפונקציה HLOOKUP קלה באותה קלות. אתה מזין את אותם ארגומנטים, אך הוא מחפש בשורות במקום בעמודות.
שימוש ב- INDEX וב- MATCH במקום ב- VLOOKUP
קיימות מגבלות מסוימות בשימוש ב- VLOOKUP - הפונקציה VLOOKUP יכולה רק לחפש ערך משמאל לימין. משמעות הדבר היא שהעמודה המכילה את הערך שתחפש צריכה תמיד להיות ממוקמת מימין לעמודה המכילה את הערך המוחזר. כעת, אם הגיליון האלקטרוני שלך אינו בנוי בדרך זו, אל תשתמש ב- VLOOKUP. השתמש בשילוב של הפונקציות INDEX ו- MATCH במקום זאת.
דוגמה זו מציגה רשימה קטנה שבה הערך שברצונך לחפש בו, חיפה, אינו בעמודה הימנית ביותר. לכן, לא ניתן להשתמש ב- VLOOKUP. במקום זאת, נשתמש בפונקציה MATCH כדי למצוא את Chicago בטווח B1:B11. הוא נמצא בשורה 4. לאחר מכן, הפונקציה INDEX משתמשת בערך זה כארגומנט בדיקת המידע, ומוצאת את האוכלוסיה עבור Chicago בעמודה הרביעית (עמודה D). הנוסחה שבה נעשה שימוש מוצגת בתא A14.
לקבלת דוגמאות נוספות לשימוש ב- 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
דוגמה אחרונה זו משתמשת בפונקציות 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 |
