מה עושים כאשר VLOOKUP מחזיר N/A#?
פונקציית VLOOKUP היא אחת הפונקציות השימושיות ביותר בעבודת היומיום שלנו.
עם זאת, לפונקציה הזו יש נטייה להשתבש מעת לעת, ולהחזיר לכם את הערך N/A# מבלי שאתם מצליחים להבין מה התפספס בפונקציה שלכם.
למה זה קורה ואיך פותרים את זה?
אני נוהג לקרוא לתופעה הזו “בעית המשולשים הירוקים” – על שם המשולשים הירוקים שמהווים את הסממן שמשהו בנתונים שלכם אינו כשורה:
מצליחים לראות את המשולשים הירוקים?
בעיה זו נוצרת כאשר מחפשים ערכים מספריים בין טבלאות שונות – למשל חיפוש של מספר תעודת זהות בתוך טבלה של מספרי ת.ז. הבעיה נובעת מכך שהתא שאתם בטוחים שהוא מספר – מוסווה בעצם כתא מסוג TEXT – ולכן הנוסחה פשוט לא תעבוד, כי אקסל אינו “מבין” תא שכזה. אם תנסו להמיר בצורה הרגילה את פורמט התא מ- TEXT ל- NUMBER – אתם תגלו שזה פשוט לא עוזר. השגיאה הזו נפוצה מאוד כשעובדים עם טבלאות שהודבקו מקבצים חיצוניים שקיבלתם.
איך פותרים את הבעיה?
ישנן שיטות רבות לפתרון הבעיה. מומלץ לקרוא עד הסוף, גם כדי להכיר את כל השיטות (לכל אחת יתרונות וחסרונות משלה) – ובמיוחד כדי להפנים את ההערות החשובות לגבי הבעיה הזו.
שיטה ראשונה – השיטה הידנית
חפשו את המשולשים הירוקים בעמודה הראשונה הרלוונטית לטבלת הנתונים שעליה תריצו את פונקציית ה- VLOOKUP. זיהיתם משולשים ירוקים? מצוין. אם תעמדו עם סמן העכבר על התא הבעייתי, יקפוץ לכם ליד התא סימן של תמרור אזהרה. לחצו עליו, ולחצו CONVERT TO NUMBER (המר למספר). אם מדובר בעמודה שלמה, או בחלק ממנה – בחרו את התא הראשון מלמעלה שהצלחתם לזהות, וסמנו כלפי-מטה את כל התאים הרלוונטיים. כעת שוב פעם יופיע לכם תמרור אזהרה שיאפשר לכם לבצע את התהליך עבור כל הבחירה שלכם בבת אחת.
השיטה הבסיסית – פתרון ידני
לא הצלחתם לזהות משולשים ירוקים? זה לא אומר שהבעיה אינה קיימת, אלא רק שהיא מסתתרת היטב. בואו ננסה לחשוף את המשולשים!
הדבקה כערכים
נניח שאתם חושדים שעמודה A מכילה מספרים שמעוצבים כטקסט.
העתיקו את כל הטור הבעייתי , והדביקו אותו מחדש בשיטה של “הדבקה כערכים”:
כך נראית הדבקה כערכים
כעת התאים הבעייתיים “יסגירו” את עצמם ויחשפו את “המשולשים הירוקים”, ותוכלו להחיל עליהם את ההמרה למספרים כרגיל, על פי אחת השיטות שמוסברות בדף הזה.
שיטה שנייה – שימוש בפונקציית VALUE
פונקציית VALUE הינה פונקצייה שמקבלת תא טקסטואלי שמכיל מספרים, והופכת אותו לתא מספרי רגיל. איך אפשר להשתמש בה?
אופציה א’ – להוסיף עמודת עזר שבה מריצים את הפונקצייה הבאה:
=Value(A1)
A1 במקרה שלנו הוא התא הבעייתי. נגרור את הנוסחה לאורך כל העמודה. הערך החדש שהתקבל הוא מספר לכל דבר, ואפשר לעבוד איתו כרגיל.
אופציה ב’ – לשלב את הפונקציה ישירות בתוך פונקציית VLOOKUP, כדלקמן:
(vlookup(value(a1),range,2,0)=
שימו לב שאופציה ב’ שימושית רק כאשר אתם יודעים שהבעיה טמונה בערכים שמופיעים בגיליון שמכיל את הערכים שלגביהם אתם מבצעים את הבדיקה, ולא הגיליון שאליו אתם מפנים את הפונקציה כדי לחפש את הערך.
שיטת שלישית – הדבקה מיוחדת
אם מדובר בטווח תאים שמכיל אך ורק מספרים – לחצו על תא ריק כלשהו בגיליון, הקלידו בו את הספרה “1”, והעתיקו אותה. כעת סמנו את כל האזור הבעייתי, קליק ימני, Paste Special, ואז שוב Paste Special, סמנו Multiply (הכפלה) ולחצו OK. כעת אקסל יבצע “הדבקה מיוחדת” שבמסגרתה יכפיל את הספרה 1 על כל הטור. התוצאה שנקבל הוא את אותו הטור בדיוק (הרי הכפלה ב- 1 לא משפיעה מתמטית בשום צורה שהיא על המספר), רק שכעת עיצובו בתור טקסט יתבטל. מגניב, לא?
שיטה רביעית – שימוש במאקרו
נניח שכל מאמציכם עלו בתוהו, ולא הצלחתם מסיבה כזו או אחרת לזהות היכן מתחבאים המשולשים הירוקים, או לחילופין – יש לכם מסדי נתונים גדולים מאוד, ואינכם יכולים לעבור על הכל בעין ואתם זקוקים לסיוע של המחשב – זה הזמן לעשות שימוש במאקרו.
חשוב מאוד – לפני שאתם מריצים את המאקרו, שמרו את כל קבצי האקסל שאתם עובדים עליהם. הוראה זו נכתבה בדם!
פתחו את עורך המאקרואים באקסל (ALT+F11), לחצו בסרגל הכלים למעלה INSERT, ולאחר מכן לחצו על MODULE.
כעת נפתח בפניכם מסך הזנה לבן. הדביקו לתוכו את הקוד הבא:
Sub ConvertTextNumberToNumber()
For Each WS In Sheets
On Error Resume Next
For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
If IsNumeric(r) Then r.Value = Val(r.Value)
Next
Next
End Sub
כעת כל הקובץ שלכם (על כל הגיליונות שבו) נקי מתאים בעייתיים. מזל טוב!
הערות חשובות
1. השגיאה יכולה להיות גם בגיליון המקור, וגם בגיליון החיפוש. כלומר – ייתכן שהיא קרתה או בטבלת החיפוש שלנו, או בערך עצמו שביקשנו לחפש בטבלה המקורית. אל תסתפקו בבדיקת הטבלה של החיפוש, ובידקו גם את הערך עצמו על מנת לוודא שהתאים הרלוונטיים חפים ממשולשים ירוקים. אם אתם לא בטוחים היכן נמצאת השגיאה, הריצו את המאקרו שהזכרנו קודם לכן בשיטה הרביעית, על מנת לנקות את כל הגיליונות מהבעיה הזו.
2. רצוי לציין שהתקלה הזו יכולה לשבש לכם גם פונקציות אחרות, כמו SUM שלא סוכם כמו שצריך, ואפילו יכול לשבש PIVOT TABLES. היו ערניים.
ועוד טיפ קטן ונחמד לפני שאתם הולכים…
לפעמים אתם דווקא תרצו להפוך תא מספרי לתא שמעוצב כטקסט? למשל כשאתם רוצים לכתוב מספר טלפון שמתחיל בספרה 0. אם לא תעשו כלום – הספרה 0 תיעלם כל פעם, כי אקסל מפרש את הסיפרה 0 כמיותרת משום שאין לה שום ערך במספר כאשר היא מופיעה כספרה הראשונה. כדי לעקוף את הבעיה – פשוט הוסיפו גרש בודד ( ‘ – האות W במקלדת) לפני הספרה הראשונה, וזה יבהיר לתוכנת האקסל כי אתם מעוניינים שהמספר יעוצב כטקסט.