נוסחת Vlookup פועלת רק כאשר מערך הטבלה בנוסחה אינו משתנה, אך אם יש עמודה חדשה שהוכנסה לטבלה או שנמחקה עמודה הנוסחה נותנת תוצאה שגויה או משקפת שגיאה, כדי להפוך את הנוסחה ללא שגיאות ב במצבים דינמיים כאלה אנו משתמשים בפונקציית ההתאמה כדי להתאים למעשה את אינדקס הנתונים ולהחזיר את התוצאה בפועל.
שלב VLOOKUP עם Match
הנוסחה vlookup היא הפונקציה הנפוצה ביותר המשמשת לחיפוש ולהחזרת אותו ערך באינדקס העמודה שצוין או הערך ממדד עמודות אחר בהתייחס לערך המותאם מהעמודה הראשונה. האתגר העיקרי העומד בפני השימוש ב- vlookup הוא כי אינדקס העמודות שיש לציין הוא סטטי ואין לו פונקציונליות דינמית. במיוחד כאשר אתה עובד על מספר קריטריונים הדורשים ממך לשנות את אינדקס עמודות ההפניה באופן ידני. בכך מתמלא צורך זה על ידי שימוש בנוסחת "MATCH" כדי לקבל אחיזה או שליטה טובים יותר באינדקס העמודות המשתנה לעתים קרובות בנוסחת VLOOKUP.
פורמולה של VLookup ו- Match
מספר 1 - נוסחת VLOOKUP
הנוסחה של פונקציית VLOOKUP ב- Excel
כאן כל הטיעונים שיש להזין הם חובה.
- Lookup_value - כאן יש להזין תא הפניה או טקסט עם מרכאות כפולות כדי להיות מזוהים בטור העמודות.
- מערך טבלאות - ארגומנט זה מחייב להזין את טווח הטבלאות היכן שיש לחפש את Lookup_value והנתונים שיש לאחזר נמצאים בטווח העמודות המסוים.
- Col_index_num - בטיעון זה, יש להזין את מספר אינדקס העמודות או את ספירת העמודה מהעמודה הראשונה המפנה, שממנו יש לשלוף את הערך המקביל מאותה המיקום כמו הערך שחיפשו בעמודה הראשונה.
- [Range_lookup] - טיעון זה ייתן שתי אפשרויות.
- TRUE - התאמה משוערת: ניתן להזין את הארגומנט כ- TRUE או כמספרת "1", המחזירה את ההתאמה המשוערת המתאימה לעמודת הייחוס או לעמודה הראשונה. יתר על כן, יש למיין ערכים בעמודה הראשונה במערך הטבלה בסדר עולה.
- FALSE - התאמה מדויקת: - כאן הארגומנט שיש להזין יכול להיות FALSE או מספרי "0". אפשרות זו תחזיר את ההתאמה המדויקת של הערך המתאים לזיהוי מהמיקום בטור העמודות הראשון. אי חיפוש בערך מהעמודה הראשונה יחזיר הודעת שגיאה "# N / A".
# 2 - נוסחת התאמה
פונקציית התאמה מחזירה את מיקום התא של הערך שהוזן עבור מערך הטבלה הנתון.
כל הטיעונים שבתחביר הם חובה.
- Lookup_value - כאן הארגומנט שהוזן יכול להיות הפניה לתא של הערך או מחרוזת טקסט עם מרכאות כפולות שמיקום התא נדרש להימשך.
- Lookup_array - יש להזין את טווח המערך של הטבלה שאת הערך או את תוכן התא הרצוי לזהות.
- [סוג התאמה] - טיעון זה מספק שלוש אפשרויות כמוסבר להלן.
- "1-פחות" - הנה הטיעון להיות שהוזן מספרי "1" אשר יחזיר את הערך כי הוא פחות או שווה ערך הבדיקה. וגם יש למיין את מערך החיפוש בסדר עולה.
- "0-התאמה מדויקת" - כאן הארגומנט שיש להזין צריך להיות מספרי "0". אפשרות זו תחזיר את המיקום המדויק של ערך החיפוש המותאם. עם זאת, מערך החיפוש יכול להיות בכל סדר.
- "-1-גדול מ-" - הטיעון שיש להזין צריך להיות מספרי "-1". האפשרות השלישית מוצאת את הערך הקטן ביותר שגדול או שווה לערך החיפוש. כאן יש למקם את סדר מערך החיפוש בסדר יורד.
# 3 - VLOOKUP עם נוסחת MATCH
= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [בדיקת טווח])
כיצד להשתמש ב- VLOOKUP עם נוסחת ההתאמה ב- Excel?
הדוגמה שלמטה תעזור להבין את תפקוד הנוסחה vlookup והתאמה בעת ההרכבה.
אתה יכול להוריד VLookup זה עם תבנית התאמה של Excel כאן - VLookup עם התאמת Excel תבניתשקול את טבלת הנתונים שלהלן המתארת את המפרט של הרכב הנתון לרכישה.
כדי לקבל את הבהירות של הפונקציה המשולבת עבור פונקצית vlookup והתאמה, נבין כיצד פועלת הנוסחה האישית ואז נגיע לתוצאות ההתאמה של vlookup בהרכבה.
שלב 1 - הבה נשתמש בנוסחת vlookup ברמה אישית כדי להגיע לתוצאה.
הפלט מוצג להלן:
כאן ערך החיפוש מופנה ל- $ B9 שהוא המודל "E" ומערך החיפוש ניתן כטווח טבלת הנתונים עם הערך המוחלט "$", אינדקס העמודות מופנה לעמודה "4" שהיא הספירה עבור בעמודה "סוג" ובחיפוש הטווח ניתן התאמה מדויקת.
כך מוחלת הנוסחה הבאה להחזרת הערך בעמודה "דלק".
הפלט מוצג להלן:
כאן ערך החיפוש עם המחרוזת המוחלטת "$" מוחל על ערך החיפוש ו- lookup_array עוזר לתקן את תא ההתייחסות גם אם הנוסחה מועתקת לתא אחר. בעמודה "דלק" עלינו לשנות את אינדקס העמודות ל- "5" כאשר הערך שממנו יש צורך בנתונים להשתנות משתנה.
שלב 2 - עכשיו נניח את נוסחת ההתאמה כדי לאחזר את המיקום עבור ערך החיפוש הנתון.
הפלט מוצג להלן:
כפי שניתן לראות בצילום המסך לעיל, כאן אנו מנסים לאחזר את מיקום העמודה ממערך הטבלה. במקרה זה, מספר העמודה שיש למשוך מכונה תא C8 שהוא העמודה "סוג" וטווח החיפוש שיש לחפש ניתן כטווח כותרות העמודות וסוג ההתאמה מקבל התאמה מדויקת כדי להיות כמו " 0 ”.
כך הטבלה שלהלן תתן את התוצאה הרצויה למיקומים בעמודה "דלק".
כעת כאן העמודה לחיפוש ניתנת להיות תא D8 ואינדקס העמודות הרצוי מוחזר להיות "5".
שלב 3 - כעת ישמש את נוסחת ההתאמה בתוך פונקציית vlookup כדי לקבל את הערך ממיקום העמודה המזוהה.
הפלט מוצג להלן:
בנוסחה שלעיל, פונקציית ההתאמה ממוקמת במקום פרמטר אינדקס העמודות של פונקציית vlookup. כאן פונקציית ההתאמה תזהה את תא ההפניה לערך בדיקה "C8" ותחזיר את מספר העמודה דרך מערך הטבלה הנתון. מיקום עמודה זה ישמש את המטרה כקלט לטיעון אינדקס העמודות בפונקציה vlookup. מה בתורו יעזור ל- vlookup לזהות את הערך שיוחזר ממספר אינדקס העמודות שהתקבל?
באופן דומה, יישמנו את vlookup עם נוסחת ההתאמה גם עבור העמודה "דלק".
הפלט מוצג להלן:
בכך נוכל להחיל פונקציית שילוב זו גם בעמודות אחרות "סוג" ו"דלק ".
דברים שיש לזכור
- ניתן להחיל את VLOOKUP על ערכי בדיקה רק בצד שמאל הקדמי ביותר. כל הערכים הקיימים לחיפוש בצד ימין של טבלת הנתונים יחזירו את ערך השגיאה "# N / A".
- טווח הטבלה_מערך שהוזן בארגומנט השני צריך להיות התייחסות מוחלטת לתאים "$", זה ישמור על טווח מערך הטבלה הקבוע בעת החלת נוסחת החיפוש על תאים אחרים, אחרת תאי ההפניה לטווח מערכי הטבלה יעבור לתא הבא התייחסות.
- הערך שהוזן בערך החיפוש לא צריך להיות קטן מהערך הקטן ביותר בעמודה הראשונה במערך הטבלה, אחרת הפונקציה תחזיר את ערך השגיאה "# N / A".
- לפני החלת התאמה משוערת "TRUE" או "1" בארגומנט האחרון, זכור תמיד למיין את מערך הטבלה בסדר עולה.
- פונקציית ההתאמה מחזירה רק את מיקום הערך במערך הטבלה vlookup ולא מחזירה את הערך.
- במקרה של פונקציית התאמה אין אפשרות לזהות את המיקום של ערך החיפוש במערך הטבלה אז הנוסחה מחזירה "# N / A" בערך השגיאה.
- פונקציות Vlookup והתאמה אינן רגישות לאותיות רישיות כאשר מתאימים את ערך החיפוש לערך הטקסט התואם במערך הטבלה.