כיצד לפתור שגיאות VLOOKUP ב- Excel

איך ל

על ידיבן סטוקטון

עודכן לאחרונה ב13 בינואר, 2020

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

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

מגבלות VLOOKUP

לפני שתתחיל להשתמש ב- VLOOKUP, עליך להיות מודע לכך שהיא לא תמיד האפשרות הטובה ביותר עבור משתמשי Excel.

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

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

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

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

ישנן כמה מגבלות נוספות לפורמולות VLOOKUP שיכולות לגרום לשגיאות, כפי שנסביר בהמשך.

שגיאות VLOOKUP ו- # N / A

אחת משגיאות ה- VLOOKUP הנפוצות ביותר ב- Excel היא # לא / לא שגיאה. שגיאה זו מתרחשת כאשר VLOOKUP לא מצליח למצוא את הערך שאתה מחפש.

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

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

בדוגמה לעיל, חיפוש אחר א תעודת סטודנט עם ה מספר 104 (בתא G4) מחזירה an # לא שגיאה מכיוון שמספר הזיהוי המינימלי בטווח הוא 105.

אם טווח_מבט הטיעון בסוף הנוסחה VLOOKUP שלך חסר או מוגדר ל אמת, ואז VLOOKUP תחזיר שגיאה מס 'N / A אם טווח הנתונים שלך לא ממוין בסדר עולה.

זה גם יחזיר שגיאה # לא / לא אם ערך החיפוש שלך קטן מהערך הנמוך ביותר בטווח.

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

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

פתרון בעיות #VALUE שגיאות

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

ברוב המקרים זה בדרך כלל בגלל התא שאתה מתייחס אליו כערך החיפוש שלך. ה גודל מקסימלי של ערך בדיקת VLOOKUP הוא 255 תווים.

אם אתה מתמודד עם תאים המכילים מחרוזות תווים ארוכות יותר, VLOOKUP לא יוכל לטפל בהן.

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

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

במקרה זה, זהו התא A4, עם אינדקס חוזר 108 (הערך של B4).

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

יש לכלול הפניות לחוברות עבודה בסוגריים מרובעים כדי שהנוסחה תעבוד כראוי.

אם אתה נתקל בשגיאה #VALUE, בדוק שוב את הנוסחה VLOOKUP כדי לאשר שהפניות שלך לתא נכונות.

#NAME ו- VLOOKUP

אם שגיאת ה- VLOOKUP שלך אינה שגיאה # VALUE או # N / A שגיאה, כנראה שהיא #NAME שגיאה. לפני שאתה נבהל מהמחשבה על זו, סמוך ובטוח - זו שגיאת ה- VLOOKUP הקלה ביותר לתקן.

שגיאה #NAME מופיעה כאשר איגרת שגוי בפונקציה ב- Excel, בין אם זה VLOOKUP או פונקציה אחרת כמו SUM. לחץ על תא ה- VLOOKUP שלך ובדוק שוב אם אימתת נכון VLOOKUP.

אם אין בעיות אחרות, הנוסחה VLOOKUP שלך תעבוד ברגע שתתקן שגיאה זו.

שימוש בפונקציות אחרות של Excel

זו הצהרה נועזת, אבל מתפקדת כמו VLOOKUP ישנה את חייך. לכל הפחות, זה ישנה את חיי העבודה שלך, ויהפוך את Excel לכלי חזק יותר לניתוח נתונים.

אם VLOOKUP אינו מתאים לך, נצל את היתרונות האלה פונקציות Excel מובילות במקום זאת.