חפש טקסט | כיצד להשתמש בטקסט VLOOKUP ב- Excel? (עם דוגמאות)

VLOOKUP עם טקסט

אחד הקריטריונים הבסיסיים לעבודה עם VLOOKUP הוא שערך "בדיקת החיפוש" צריך להיות זהה בתא התוצאה וגם בטבלת הנתונים הראשית, אך לפעמים למרות שערך החיפוש נראה זהה בכל אחד מהתאים עדיין בסופו של דבר נקבל ערך שגיאה. כ- # N / A !. הסיבה לכך היא שהתבנית של ערך vlookup חייבת להיות שונה בכל אחד מהתאים. אז במאמר זה נראה לכם כיצד לעבוד עם פורמט הטקסט של ערך Vlookup.

דוגמה ל- VLOOKUP לטקסט

להלן דוגמה ל- Excel VLOOKUP לטקסט.

תוכל להוריד תבנית VLOOKUP לטקסט של Excel כאן - VLOOKUP לטקסט תבנית Excel

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

בנתונים שלעיל הכל נראה כמו מספרים אך כאשר אנו מסכמים אותו עלינו לקבל ערך כולל של 3712054 אך כאשר אנו מיישמים את פונקציית ה- SUM excel אנו מקבלים את המספר שלמטה.

הסיבה לכך היא מכיוון שמספרים מסוימים נשמרים כערכי טקסט. אז איך נזהה ערכי טקסט?

אנו יכולים לזהות ערכי טקסט באמצעות פונקציית ה- Excel של ISNUMBER. הפונקציה ISNUMBER תחזיר TRUE אם ערך התא שנבחר הוא מספר או שהוא יחזיר FALSE.

אז בתא B5 & B6, קיבלנו את התוצאה כ- FALSE, כלומר מספרי תאים A5 & A6 מאוחסנים כערכי טקסט.

VLOOKUP דורש פורמט מספר מדויק

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

מטבלה 1 עלינו להחיל את פונקציית VLOOKUP כדי לגרום לעמודת ההכנסות לגרום לטבלה 2.

לכן, החל את פונקציית VLOOKUP בטבלה 2.

אנו מקבלים את התוצאה הבאה.

התוצאה של פונקציית VLOOKUP בתאים E6 & E7 מציגה # N / A !.

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

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

החל את הפונקציה ISNUMBER לזיהוי ערכים שאינם מספריים.

כפי שניתן לראות ISNUMBER זיהה ערך לא מספרי בטבלה 2.

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

שיטה 1: להמיר מספרים מעוצבים בטקסט לערכים מספריים באמצעות הדבק מיוחד

ראשית, הזן מספר 1 בכל אחד מהתאים בגליון העבודה והעתק את התא הזה.

כעת בחר ערכי מזהה מוצא בטבלה 2 ופתח את תיבת הדו-שיח הדבק מיוחד.

כדי לפתוח הדבק תיבת דו-שיח מיוחדת לחץ על ALT + E + S.

אנו מקבלים את תיבת הדו-שיח הבאה.

בחלון ההדבקה המיוחד בחר "הכפל" כאפשרות.

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

שיטה 2: המרה באמצעות פונקציית VALUE

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

כדי להתגבר על נושא זה בהחלת פונקציית החיפוש עלינו לצרף את הפונקציה VALUE.

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

שיטה 3: מה אם המספרים נשמרים כטקסט בטבלה הראשית

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

כפי שנוכל בתמונה לעיל בטבלה הראשית (טבלה 1) הערכים עצמם נשמרים כטקסט. במקרים כאלה, עלינו לכלול את פונקציית TEXT עבור ערך החיפוש בפונקציה VLOOKUP.

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

מצב IFERROR בודק אם ערך החיפוש הוא מספרי או לא, אם הוא מספרי נפעיל LOOKUP רגיל או אחר נפעיל את פונקציית TEXT ב- Excel.

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

דברים שיש לזכור

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