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