01 מתוך 03
מצא התאמות משוערות לנתונים עם VLOOKUP של Excel
כיצד פועל פונקציית VLOOKUP
הפונקציה של VLOOKUP של Excel, המייצגת בדיקה אנכית , יכולה לשמש לחיפוש מידע ספציפי הממוקם בטבלה של נתונים או מסד נתונים.
VLOOKUP בדרך כלל מחזיר שדה נתונים יחיד כפלט שלו. איך זה עושה את זה:
- אתה מספק שם או lookup_value המספר VLOOKUP באיזה שורה או רשומה של טבלת הנתונים כדי לחפש את הנתונים הרצויים
- אתה מספק את מספר העמודה - המכונה col_index_num - של הנתונים שאתה מחפש
- הפונקציה מחפשת את הערך lookup_value בעמודה הראשונה של טבלת הנתונים
- VLOOKUP ואז מאתר ומחזיר את המידע שאתה מחפש משדה אחר של אותו רשומה באמצעות מספר העמודה שסופק
מיון הנתונים תחילה
למרות שלא תמיד נדרש, מומלץ בדרך כלל למיין את טווח הנתונים ש- VLOOKUP מחפש בסדר עולה עם העמודה הראשונה של הטווח עבור מפתח המיון.
אם הנתונים אינם ממוינים, ייתכן ש- VLOOKUP יחזיר תוצאה שגויה.
התחביר של פונקציה VLOOKUP של ויכוחים
תחביר של פונקציה מתייחס לפריסה של הפונקציה וכולל את שם הפונקציה, סוגריים וארגומנטים .
התחביר עבור הפונקציה VLOOKUP הוא:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
בדיקה - ערך - (נדרש) הערך לחיפוש - כגון הכמות שנמכרה בתמונה לעיל
table_array - (חובה) זהו טבלת הנתונים ש- VLOOKUP מחפש כדי למצוא את המידע שאחריו.
- Table_array חייב להכיל לפחות שתי עמודות של נתונים
- העמודה הראשונה בדרך כלל מכילה את lookup_value
col_index_num - (חובה) את מספר העמודה של הערך שברצונך למצוא.
- המספור מתחיל בעמודה search_key בתור עמודה 1
- אם col_index_num מוגדר למספר גדול ממספר העמודות שנבחרו בארגומנט table_array #REF! השגיאה מוחזרת על ידי הפונקציה
range_lookup - (אופציונלי) מציין אם הטווח ממוין בסדר עולה.
- הנתונים בעמודה הראשונה משמשים כמפתח המיון
- ערך בוליאני - TRUE או FALSE הם הערכים המקובלים היחידים
- אם מושמט, הערך מוגדר ל- TRUE כברירת מחדל
- אם מוגדר ל- TRUE או מושמט והטורה הראשונה של הטווח אינה מסודרת בסדר עולה, עלולה להתרחש תוצאה שגויה
- אם הוגדר כ- TRUE או מושמט, והתאמה מדויקת לחיפוש זה לא נמצאה, ההתאמה הקרובה ביותר, בגודל או בערך, משמשת כ- search_key
- אם מוגדר ל- FALSE, VLOOKUP מקבל רק התאמה מדויקת עבור החיפוש _value . אם קיימים ערכי התאמה מרובים, ערך ההתאמה הראשון מוחזר
- אם מוגדר FALSE ולא נמצא ערך תואם עבור search_key , שגיאה # N / A מוחזרת על ידי הפונקציה
דוגמה: מצא את שיעור ההנחה עבור כמות שנרכשה
הדוגמה בתמונה לעיל משתמשת בפונקציה VLOOKUP כדי למצוא את שיעור ההנחה המשתנה בהתאם לכמות הפריטים שנרכשו.
הדוגמה מראה שההנחה לרכישת 19 פריטים היא 2%. הסיבה לכך היא כי העמודה כמות מכילה טווחים של ערכים. כתוצאה מכך, VLOOKUP לא יכול למצוא התאמה מדויקת. במקום זאת, יש למצוא התאמה משוערת כדי להחזיר את שיעור ההנחה הנכון.
כדי למצוא התאמות משוערות:
- למיין את הנתונים table_array בסדר עולה;
- הגדר את הארגומנט range_lookup ל- TRUE
בדוגמה, הנוסחה הבאה המכילה את הפונקציה VLOOKUP משמשת כדי למצוא את ההנחה עבור כמויות של מוצרים שנרכשו.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
למרות שנוסחה זו יכולה להיות מוקלדת לתא גליון עבודה, אפשרות נוספת, כפי שנעשה עם השלבים המפורטים להלן, היא להשתמש בתיבת הדו-שיח של הפונקציה כדי להזין את הארגומנטים שלה.
- שימוש בתיבת הדו-שיח לעתים קרובות מקל על הזנת ארגומנטים של פונקציה בצורה נכונה.
פתיחת תיבת הדו-שיח VLOOKUP
השלבים המשמשים להכנסת הפונקציה VLOOKUP שמופיעה בתמונה מעל לתא B2 הם:
- לחץ על תא B2 כדי להפוך אותו לתא הפעיל - המיקום שבו מוצגות תוצאות הפונקציה VLOOKUP
- לחץ על הכרטיסייה נוסחאות .
- בחר Lookup & Reference מהסרט כדי לפתוח את הרשימה הנפתחת של הפונקציה
- לחץ על VLOOKUP ברשימה כדי להעלות את תיבת הדו-שיח של הפונקציה
02 מתוך 03
הזנת ארגומנטים של פונקציה VLOOKUP של Excel
מצביע על הפניות סלולריות
הארגומנטים עבור הפונקציה VLOOKUP מוזנים לשורות נפרדות של תיבת הדו-שיח כפי שמוצג בתמונה למעלה.
ניתן להקליד את ההתייחסויות לתא שישמשו כארגומנטים לקו הנכון, או, כפי שנעשה בשלבים הבאים, ניתן להצביע על הצבעה על הטווח הרצוי של תאים עם מצביע העכבר, כדי להזין אותם בתיבת הדו-שיח .
היתרונות של שימוש בהצבעה כוללים:
- זה מהיר יותר מאשר הקלדת;
- פחות שגיאות נעשים נכנסים הפניות התא הנכון.
באמצעות התייחסות יחסית תאים מוחלטים עם טיעונים
אין זה נדיר להשתמש במספר עותקים של VLOOKUP כדי להחזיר מידע שונה מאותו טבלה של נתונים. כדי להקל על זה, לעתים קרובות ניתן להעתיק VLOOKUP מתא אחד לתא אחר. כאשר הפונקציות מועתקות לתאים אחרים, יש לנקוט בזהירות על מנת להבטיח כי הפניות התא וכתוצאה מכך נכונים בהתחשב במיקום החדש של הפונקציה.
בתמונה לעיל, סימני דולר ( $ ) מקיפים את הפניות התא עבור הארגומנט table_array המציין כי הם הפניות תא מוחלט , כלומר הם לא ישתנו אם הפונקציה מועתקים לתא אחר. זה רצוי כמו עותקים מרובים של VLOOKUP היה כל התייחסות לטבלה זהה של נתונים כמקור מידע.
התא התייחסות התא המשמש lookup_value, לעומת זאת , אינו מוקף סימני דולר, מה שהופך אותו התייחסות התא יחסית. תפוסים תאיים יחסיים משתנים כאשר הם מועתקים כדי לשקף את המיקום החדש שלהם ביחס למיקום הנתונים שאליהם הם מתייחסים.
הזנת ארגומנטים של פונקציה
- לחץ על השורה Lookup _value בתיבת הדו-שיח VLOOKUP
- לחץ על תא C2 בגליון העבודה כדי להזין את הפניה התא כארגומנט search_key
- לחץ על שורת Table_array בתיבת הדו-שיח
- הדגשת תאים C5 עד D8 בגליון העבודה כדי להזין טווח זה כארגומנט Table_array - כותרות הטבלה אינן כלולות
- לחץ על המקש F4 במקלדת כדי לשנות את הטווח להפניות תא מוחלטות
- לחץ על השורה Col_index_num של תיבת הדו-שיח
- הקלד 2 בשורה זו כארגומנט Col_index_num , מכיוון ששיעורי ההנחה ממוקמים בעמודה 2 של ארגומנט Table_array
- לחץ על השורה Range_lookup של תיבת הדו-שיח
- הקלד את המילה True כארגומנט Range_lookup
- לחץ על המקש Enter במקלדת כדי לסגור את תיבת הדו-שיח ולחזור לגליון העבודה
- התשובה 2% (שיעור ההנחה עבור הכמות שנרכשה) יופיע בתא D2 בגליון העבודה
- כאשר אתה לוחץ על D2 התא, את הפונקציה המלאה = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) מופיע בסרגל הנוסחה מעל גליון העבודה
למה VLOOKUP החזיר 2% כתוצאה
- בדוגמה, העמודה כמות אינה מכילה התאמה מדויקת לערך search_key של 19.
- מאחר שהארגומנט is_sorted מוגדר ל- TRUE, VLOOKUP ימצא התאמה משוערת לערך search_key .
- הערך הקרוב ביותר לגודל שעדיין קטן מהערך של search_key של 19 הוא 11.
- VLOOKUP, ולכן, מחפש את אחוז הנחה בשורה המכילה 11, וכתוצאה מכך, מחזיר שיעור הנחה של 2%.
03 מתוך 03
Excel VLOOKUP אינו פועל: # N / A ו- #REF שגיאות
הודעות שגיאה של VLOOKUP
הודעות השגיאה הבאות משויכות ל- VLOOKUP.
# N / A ("ערך לא זמין") שגיאה מוצגת אם:
- החיפוש _ ערך אינו נמצא בעמודה הראשונה של ארגומנט הטווח
- הארגומנט Table_array אינו מדויק. לדוגמה, הארגומנט עשוי לכלול עמודות ריקות בצד השמאלי של הטווח
- הארגומנט Range_lookup מוגדר ל- FALSE ולא ניתן למצוא התאמה מדויקת עבור הארגומנט search_key בעמודה הראשונה של הטווח
- הארגומנט Range_lookup מוגדר ל- TRUE וכל הערכים בעמודה הראשונה של הטווח גדולים יותר מה- search_key
#REF! ("הפניה מחוץ לטווח") שגיאה מוצגת אם:
- הארגומנט Col_index_num גדול ממספר העמודות ב- Table_array