תוכן
שיעור זה מסביר כיצד להתמודד במהירות עם מצב שבו פונקציה VPR (VLOOKUP) לא רוצה לעבוד ב-Excel 2013, 2010, 2007 ו-2003, וכיצד לזהות ולתקן שגיאות נפוצות ולהתגבר על מגבלות VPR.
בכמה מאמרים קודמים, חקרנו את ההיבטים השונים של הפונקציה VPR באקסל. אם קראת אותם בעיון, כעת עליך להיות מומחה בתחום זה. עם זאת, לא בכדי מאמינים מומחי אקסל רבים VPR אחת התכונות המורכבות יותר. יש לו חבורה של מגבלות ותכונות שהופכות למקור לבעיות ושגיאות רבות.
במאמר זה תמצאו הסברים פשוטים על שגיאות #בְּ (#N/A), מילת #NAME? (#NAME?) ו #ערך! (#VALUE!) המופיעים בעת עבודה עם הפונקציה VPR, וכן טכניקות ושיטות התמודדות איתם. נתחיל מהמקרים הנפוצים ביותר והסיבות הברורות ביותר לכך. VPR לא עובד, אז עדיף ללמוד את הדוגמאות בסדר שבו הן ניתנות במאמר.
תיקון שגיאת #N/A בפונקציית VLOOKUP ב-Excel
בנוסחאות עם VPR הודעת שגיאה #בְּ (#N/A) פירושו לא זמין (ללא נתונים) - מופיע כאשר Excel לא יכול למצוא את הערך שאתה מחפש. זה יכול לקרות מכמה סיבות.
1. הערך הרצוי מאוית שגוי
רעיון טוב לבדוק את הפריט הזה קודם! שגיאות הקלדה מתרחשות לעתים קרובות כאשר אתה עובד עם כמויות גדולות מאוד של נתונים, המורכבות מאלפי שורות, או כאשר הערך שאתה מחפש נכתב בנוסחה.
2. #N/A שגיאה בעת חיפוש התאמה משוערת עם VLOOKUP
אם אתה משתמש בנוסחה עם תנאי חיפוש התאמה משוער, כלומר ארגומנט טווח_תצוגה (range_lookup) הוא TRUE או לא צוין, הנוסחה שלך עשויה לדווח על שגיאה # N / A בשני מקרים:
- הערך שיש לחפש הוא קטן מהערך הקטן ביותר במערך הנבדק.
- עמודת החיפוש אינה ממוינת בסדר עולה.
3. #N/A שגיאה בעת חיפוש התאמה מדויקת עם VLOOKUP
אם אתה מחפש התאמה מדויקת, כלומר טיעון טווח_תצוגה (range_lookup) הוא FALSE והערך המדויק לא נמצא, הנוסחה תדווח גם על שגיאה # N / A. למידע נוסף על איך לחפש התאמות מדויקות ומשוערות עם פונקציה VPR.
4. עמודת החיפוש אינה השמאלית ביותר
כפי שאתה בוודאי יודע, אחת המגבלות המשמעותיות ביותר VPR זה שהוא לא יכול לפנות שמאלה, ומכאן שעמודת החיפוש בטבלה שלך חייבת להיות השמאלית ביותר. בפועל, לעתים קרובות אנו שוכחים מזה, מה שמוביל לנוסחה שאינה פועלת ולטעות. # N / A.
הַחְלָטָה: אם לא ניתן לשנות את מבנה הנתונים כך שעמודת החיפוש תהיה השמאלית ביותר, ניתן להשתמש בשילוב של פונקציות מדד (אינדקס) ו יותר חשוף (MATCH) כחלופה גמישה יותר עבור VPR.
5. מספרים מעוצבים כטקסט
מקור שגיאה נוסף # N / A בנוסחאות עם VPR הם מספרים בפורמט טקסט בטבלה הראשית או בטבלת חיפוש.
זה קורה בדרך כלל כאשר אתה מייבא מידע ממסדי נתונים חיצוניים, או כאשר אתה מקליד אפוסתרוף לפני מספר כדי לשמור על האפס המוביל.
הסימנים הברורים ביותר של מספר בפורמט טקסט מוצגים באיור שלהלן:
בנוסף, ניתן לאחסן מספרים בפורמט כללי (כללי). במקרה זה, יש רק תכונה אחת בולטת - המספרים מיושרים לקצה השמאלי של התא, בעוד כברירת מחדל הם מיושרים לקצה הימני.
הַחְלָטָה: אם זה ערך בודד, פשוט לחץ על סמל השגיאה ובחר המר למספר (המר למספר) מתפריט ההקשר.
אם זה המצב עם מספרים רבים, בחר אותם ולחץ לחיצה ימנית על האזור הנבחר. בתפריט ההקשר שמופיע, בחר עיצוב תאים (עיצוב תאים) > כרטיסייה מספר (מספר) > פורמט מספר (מספרי) ולחץ OK.
6. יש רווח בהתחלה או בסוף
זו הסיבה הפחות ברורה לשגיאה. # N / A בתפקוד VPR, מכיוון שקשה לראות את החללים הנוספים הללו, במיוחד כאשר עובדים עם טבלאות גדולות, כאשר רוב הנתונים אינם מהמסך.
פתרון 1: רווחים נוספים בטבלה הראשית (שם הפונקציה VLOOKUP)
אם מופיעים רווחים נוספים בטבלה הראשית, תוכל להבטיח שהנוסחאות יפעלו כהלכה על ידי צירוף הארגומנט lookup_value (lookup_value) לתוך פונקציה TRIM (מְטוּפָּח):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
פתרון 2: רווחים נוספים בטבלת חיפוש (בעמודת חיפוש)
אם רווחים נוספים נמצאים בעמודת החיפוש - דרכים פשוטות # N / A בנוסחה עם VPR לא ניתן להימנע. במקום VPR ניתן להשתמש בנוסחת מערך עם שילוב של פונקציות מדד (אינדקס), יותר חשוף (MATCH) и TRIM (מְטוּפָּח):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
מכיוון שזו נוסחת מערך, אל תשכח ללחוץ Ctrl + Shift + Enter במקום הרגיל זןכדי להזין את הנוסחה בצורה נכונה.
שגיאה #VALUE! בנוסחאות עם VLOOKUP
ברוב המקרים, Microsoft Excel מדווח על שגיאה #ערך! (#VALUE!) כאשר הערך המשמש בנוסחה אינו תואם לסוג הנתונים. בִּדְבַר VPR, אז יש בדרך כלל שתי סיבות לשגיאה #ערך!.
1. הערך שאתה מחפש ארוך מ-255 תווים
היזהר: תפקוד VPR לא יכול לחפש ערכים המכילים יותר מ-255 תווים. אם הערך שאתה מחפש חורג מהמגבלה הזו, תקבל הודעת שגיאה. #ערך!.
הַחְלָטָה: השתמש בכמה תכונות INDEX+MATCH (אינדקס + התאמה). להלן נוסחה שתתאים למשימה זו:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. הנתיב המלא לחוברת החיפוש לא צוין
אם אתה מאחזר נתונים מחוברת עבודה אחרת, עליך לציין את הנתיב המלא לקובץ זה. ליתר דיוק, עליך לכלול את שם חוברת העבודה (כולל הסיומת) בסוגריים מרובעים [ ], ואחריו את שם הגיליון, ואחריו סימן קריאה. כל הבנייה הזו חייבת להיות מוקפת בכתבים, למקרה ששם הספר או הגיליון מכילים רווחים.
להלן המבנה המלא של הפונקציה VPR לחפש בספר אחר:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
הנוסחה האמיתית עשויה להיראות כך:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
נוסחה זו תחפש את ערך התא A2 בטור B על הגיליון Sheet1 בחוברת העבודה מחירים חדשים וחלץ את הערך המתאים מהעמודה D.
אם חלק כלשהו מנתיב הטבלה מושמט, הפונקציה שלך VPR לא יעבוד וידווח על שגיאה #ערך! (גם אם חוברת העבודה עם טבלת החיפוש פתוחה כעת).
למידע נוסף על הפונקציה VPRבהתייחסות לקובץ אקסל אחר, עיין בשיעור: חיפוש חוברת עבודה אחרת באמצעות VLOOKUP.
3. טיעון Column_num הוא פחות מ-1
קשה לדמיין מצב שבו מישהו מכניס ערך פחות מ 1כדי לציין את העמודה שממנה לחלץ את הערך. למרות שזה אפשרי אם הערך של ארגומנט זה מחושב על ידי פונקציית Excel אחרת המקוננת בתוכו VPR.
אז, אם זה קורה כי הטיעון col_index_num (מספר_עמודה) פחות מ 1פונקציה VPR ידווח גם על שגיאה #ערך!.
אם הטיעון col_index_num (column_number) גדול ממספר העמודות במערך הנתון, VPR ידווח על שגיאה #REF! (#SSYL!).
שגיאה #NAME? ב-VLOOKUP
המקרה הפשוט ביותר הוא טעות מילת #NAME? (#NAME?) – יופיע אם תכתוב בטעות שם פונקציה עם שגיאה.
הפתרון ברור - בדוק את האיות שלך!
VLOOKUP לא עובד (מגבלות, אזהרות והחלטות)
בנוסף לתחביר המסובך למדי, VPR יש יותר מגבלות מכל פונקציית Excel אחרת. בגלל המגבלות האלה, נוסחאות פשוטות לכאורה עם VPR לעתים קרובות מובילים לתוצאות בלתי צפויות. להלן תמצא פתרונות למספר תרחישים נפוצים שבהם VPR טועה.
1. VLOOKUP אינו תלוי רישיות
פונקציה VPR אינו מבחין בין רישיות ומקבל אותיות קטנות ואותיות גדולות כאותם. לכן, אם יש כמה אלמנטים בטבלה שנבדלים זה מזה רק במקרה, הפונקציה VLOOKUP תחזיר את האלמנט הראשון שנמצא, ללא קשר לרישיות.
הַחְלָטָה: השתמש בפונקציית Excel אחרת שיכולה לבצע חיפוש אנכי (LOOKUP, SUMPRODUCT, INDEX ו-MATCH) בשילוב עם מְדוּיָקמקרה שמבדיל. לפרטים נוספים, תוכל ללמוד מהשיעור - 4 דרכים להפוך את VLOOKUP לרגיש רישיות באקסל.
2. VLOOKUP מחזירה את הערך הראשון שנמצא
כמו שאתה כבר יודע, VPR מחזירה את הערך מהעמודה הנתונה התואמת להתאמה הראשונה שנמצאה. עם זאת, אתה יכול לחלץ את הערך השני, השלישי, הרביעי או כל חזרה אחרת על הערך שאתה רוצה. אם אתה צריך לחלץ את כל הערכים הכפולים, תצטרך שילוב של פונקציות מדד (אינדקס), הכי פחות (קטן) ו קו (שׁוּרָה).
3. עמודה נוספה או הוסרה לטבלה
למרבה הצער, הנוסחאות VPR להפסיק לעבוד בכל פעם שעמודה חדשה מתווספת או מוסרת לטבלת החיפוש. זה קורה בגלל התחביר VPR מחייב אותך לציין את הטווח המלא של החיפוש ואת מספר העמודה הספציפית לחילוץ נתונים. באופן טבעי, גם הטווח הנתון וגם מספר העמודה משתנים כאשר אתה מוחק עמודה או מוסיף עמודה חדשה.
הַחְלָטָה: ושוב פונקציות ממהרות לעזור מדד (אינדקס) ו יותר חשוף (התאמה). בנוסחה INDEX+MATCH אתה מגדיר בנפרד עמודות חיפוש ושליפה, וכתוצאה מכך, אתה יכול למחוק או להוסיף כמה עמודות שתרצה מבלי לדאוג שתצטרך לעדכן את כל נוסחאות החיפוש הקשורות.
4. הפניות לתאים משובשות בעת העתקת נוסחה
כותרת זו מסבירה את מהות הבעיה בצורה ממצה, נכון?
הַחְלָטָה: השתמש תמיד בהפניות מוחלטות לתאים (עם הסמל $) ברשומות הטווח, למשל $A$2:$C$100 or $A:$C. בשורת הנוסחאות, תוכל להחליף במהירות את סוג הקישור על ידי לחיצה F4.
VLOOKUP – עבודה עם הפונקציות IFERROR ו- ISERROR
אם אתה לא רוצה להפחיד משתמשים עם הודעות שגיאה # N / A, #ערך! or מילת #NAME?, אתה יכול להציג תא ריק או הודעה משלך. אתה יכול לעשות זאת על ידי מיקום VPR לתוך פונקציה טעות (IFERROR) ב-Excel 2013, 2010 ו-2007 או השתמש במספר פונקציות IF+ERROR (IF+ISERROR) בגרסאות קודמות.
VLOOKUP: עבודה עם הפונקציה IFERROR
תחביר פונקציה טעות (IFERROR) הוא פשוט ומדבר בעד עצמו:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
כלומר, עבור הארגומנט הראשון אתה מכניס את הערך שיש לבדוק אם יש שגיאה, ועבור הארגומנט השני אתה מציין מה להחזיר אם נמצא שגיאה.
לדוגמה, נוסחה זו מחזירה תא ריק אם הערך שאתה מחפש לא נמצא:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
אם ברצונך להציג הודעה משלך במקום הודעת השגיאה הרגילה של הפונקציה VPR, שים את זה במרכאות, כך:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: עבודה עם הפונקציה ISERROR
מאז הפונקציה טעות הופיע ב-Excel 2007, כאשר עובדים בגירסאות קודמות תצטרך להשתמש בשילוב IF (IF) ו EOSHIBKA (ISERROR) כך:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
למשל, הנוסחה IF+ERROR+VLOOKUP, בדומה לנוסחה IFERROR+VLOOKUPהמוצג לעיל:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
זה הכל להיום. אני מקווה שהמדריך הקצר הזה יעזור לך להתמודד עם כל הטעויות האפשריות. VPR ולגרום לנוסחאות שלך לעבוד כהלכה.