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

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

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

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

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

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

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

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

השגיאה # n/A תוצאות מאחר "Kale" מופיע בעמודה השניה (Produce) של הארגומנט table_array A2:C10 ערך בדיקת המידע. במקרה זה, Excel מחפש אותו בעמודה A, לא עמודה b.

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

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

INDEX ו- 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 תחזיר את ההתאמה הקרובה ביותר הראשונה בטבלה, שעשוי להיות לא הנתונים שאתה מחפש.

תחביר

כדי לבנות תחביר עבור אינדקס/התאמה, עליך להשתמש בארגומנט המערך/הפניה מתוך בפונקציה 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 הוא לא מה להשלים אותה, להימשך זמן כדי לוודא כי התאים שאליהם יש הפניה אין מוסתרים רווחים או תווים שאינם מודפסים. בנוסף, ודא כי התאים ייעשה שימוש בשיטות את סוג הנתונים הנכונים. לדוגמה, תאים בעלי מספרים שיש לעצב כאותיות מספרולא טקסט.

כמו כן, שקול להשתמש בפונקציה ' ניקוי ' או חיתוך לניקוי נתונים בתאים.

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

אם הארגומנט 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.

למידע נוסף

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

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

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

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

×