כיצד לתקן שגיאת #N/A בפונקציה VLOOKUP

נושא זה מתאר את הסיבות הנפוצות ביותר ל- VLOOKUP לקבלת תוצאה שגויה בפונקציה, ומספק הצעות לשימוש באינדקסוהתאמה במקום זאת.

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

בעיה: ערך בדיקת המידע אינו מופיע בעמודה הראשונה בארגומנט הtable_array

אילוץ אחד של VLOOKUP הוא שהיא יכולה לחפש רק ערכים בעמודה הימנית ביותר במערך הטבלאות. אם ערך בדיקת המידע שלך אינו מופיע בעמודה הראשונה של המערך, תראה את השגיאה #N/A.

בטבלה הבאה, אנו מעוניינים לאחזר את מספר היחידות שנמכרו עבור קאלה.

שגיאה #NA ב-VLOOKUP: ערך בדיקת מידע אינו בעמודה הראשונה של מערך הטבלה

תוצאות השגיאה #N/A מכיוון שערך בדיקת המידע "קאלה" מופיע בעמודה השניה (תוצר) של הארגומנט Table_array A2: C10. במקרה זה, Excel מחפש אותו בעמודה A, לא בעמודה B.

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

שקול להשתמש באינדקס/התאם במקום זאת

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

קיימים כמה יתרונות של שימוש באינדקס/התאמה במקום VLOOKUP:

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

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

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

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

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

תחביר

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

= INDEX (array או reference, MATCH (lookup_value, lookup_array, [match_type])

בוא נשתמש באינדקס/התאמה כדי להחליף את VLOOKUP מהדוגמה שלעיל. התחביר ייראה כך:

= INDEX (C2: C10, MATCH (B13, B2: B10, 0))

באנגלית פשוטה משמעות הדבר:

= INDEX (החזר ערך מ-C2: C10, שיתאים (קאלה, שנמצא במקום כלשהו במערך B2: B10, שבו הערך המוחזר הוא הערך הראשון שמתאים לקאלה))

ניתן להשתמש בפונקציות INDEX ו-MATCH כתחליף ל-VLOOKUP

הנוסחה מחפשת את הערך הראשון ב-C2: C10 שמתאים לקאלה (ב-B7) ומחזירה את הערך ב-C7 (100), שהוא הערך הראשון שמתאים לקאלה.

הבעיה: ההתאמה המדויקת אינה נמצאת

כאשר הארגומנט הrange_lookup הוא FALSE — והפונקציה VLOOKUP אינה מצליחה למצוא התאמה מדויקת בנתונים שלך — היא מחזירה את השגיאה #N/a.

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

כמו כן, שקול להשתמש בפונקציה clean או TRIM כדי לנקות נתונים בתאים.

הבעיה: ערך בדיקת המידע קטן מהערך הקטן ביותר במערך

אם הארגומנט range_lookup מוגדר כ-TRUE – וערך בדיקת המידע קטן מהערך הקטן ביותר במערך — תראה את השגיאה #N/a. הפונקציה TRUE מחפשת התאמה משוערת במערך ומחזירה את הערך הקרוב ביותר הקטן מערך בדיקת המידע.

בדוגמה הבאה, ערך בדיקת המידע הוא 100, אך אין ערכים בטווח B2: C10 שנמוך מ-100; מכאן השגיאה.

שגיאת N/A בפונקציה VLOOKUP כאשר ערך בדיקת המידע קטן מהערך הקטן ביותר במערך

הפתרון:

  • תקן את ערך בדיקת המידע לפי הצורך.

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

בעיה: עמודת בדיקת המידע אינה ממוינת בסדר עולה

אם הארגומנט range_lookup מוגדר כ-TRUE — ואחת מעמודות בדיקת המידע אינה ממוינת בסדר עולה (א-ת) – תראה את השגיאה #N/a.

הפתרון:

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

  • השתמש בפונקציה INDEX/MATCH כדי לחפש ערך בטבלה לא ממוינת.

הבעיה: הערך הוא מספר נקודה צפה גדול

אם יש לך ערכי זמן או מספרים עשרוניים גדולים בתאים, Excel מחזיר את השגיאה #N/A עקב נקודת דיוק צפה. מספרים מנקודה צפה הם מספרים העוקבים אחרי נקודה עשרונית. (Excel מאחסן ערכי זמן כמספרים של נקודות צפות). ל-Excel אין אפשרות לאחסן מספרים בעלי נקודות ציפה גדולות מאוד, ולכן כדי שהפונקציה תפעל כראוי, מספרי הנקודה הצפה יצטרכו להיות מעוגלים ל-5 מקומות עשרוניים.

הפתרון: קצר את המספרים על-ידי עיגולם עד חמישה מקומות עשרוניים באמצעות הפונקציה ROUND .

האם יש לך שאלה בנושא פונקציה ספציפית?

פרסם שאלה בפורום הקהילה של Excel

עזור לנו לשפר את Excel

האם יש לך הצעות לשיפור הגירסה הבאה של Excel? אם כן, עיין בנושאים תחת User Voice עבור Excel.

למידע נוסף

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

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

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

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

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

×