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

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

נושא זה מתאר את הסיבות הנפוצות ביותר לגבי מדוע ייתכן שיש כשל VLOOKUP שלך.

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

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

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

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

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

השגיאה היא מכיוון שהערך בדיקת מידע "Kale" הוא בעמודה השניה (Produce) ב- A2:C10 הארגומנט table_array , כך ש- Excel ההתאמה עבורו בעמודה A, לא עמודה b.

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

השתמש בפונקציה INDEX/MATCH במקום VLOOKUP

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

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

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

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

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

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

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

תחביר

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

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

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

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

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

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

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

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

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

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

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

השתמש בפונקציה CLEAN או TRIM לניקוי נתונים בתאים.

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

אם הארגומנט range_lookup מוגדר כ- TRUE, ערך בדיקת המידע קטן מהערך הקטן ביותר במערך, תראה את השגיאה # n/A. מראות TRUE עבור בקירוב להתאים את המערך ומחזירה הקרובה ביותר ערך קטן יותר מהערך בדיקת מידע.

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

השגיאה n/A ב- VLOOKUP כאשר הערך בדיקת מידע קטן מהערך הקטן ביותר במערך

פתרון:

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

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

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

אם הארגומנט range_lookup מוגדר כ- TRUE, אחת מהעמודות בדיקת המידע שלך לא ממוינים בסדר עולה (A-Z), תראה את השגיאה # n/A.

פתרון:

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

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

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

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

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

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

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

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

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

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

למידע נוסף

Correct שגיאת # n/a

VLOOKUP: לא #NA יותר

בדיקת מידע HLOOKUP, הפונקציה VLOOKUP, להחזיר ערכים שגויים ב- Excel

החשבוני עם נקודה צפה עשויה להחזיר תוצאות לא מדויקות ב- Excel

כרטיס לעיון מהיר: רענון בנושא VLOOKUP

הפונקציה VLOOKUP

מבט כולל על נוסחאות ב- Excel

כיצד להימנע מנוסחאות שגויות

שימוש בבדיקת שגיאות כדי לזהות שגיאות בנוסחאות

כל פונקציות ב- Excel (בסדר אלפביתי)

Excel כל פונקציות (לפי קטגוריה)

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

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

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

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

×