לְהִצטַיֵן

23 דברים שכדאי לדעת על VLOOKUP

23 Things You Should Know About Vlookup

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



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

זה יכול לגרום לתוצאות להיראות נורמלי לחלוטין , למרות שהם לגמרי לא נכון . תאמין לי, זה לא משהו שאתה רוצה לנסות להסביר לבוס שלך, אחרי שהיא כבר שלחה את הגיליון האלקטרוני שלך להנהלה :)





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

1. כיצד פועל VLOOKUP

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



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

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

סקירה כללית של אופן פעולת VLOOKUP

בטבלה שלעיל, מזהי העובדים נמצאים בעמודה 1 בצד שמאל וכתובות הדוא'ל בעמודה 4 מימין.

כדי להשתמש ב- VLOOKUP, אתה מספק 4 פיסות מידע, או 'טיעונים':

  1. הערך שאתה מחפש ( חיפוש_ערך )
  2. טווח התאים המרכיבים את הטבלה ( מערך שולחן )
  3. מספר העמודה שממנה ניתן לאחזר תוצאה ( column_index )
  4. מצב ההתאמה ( טווח_מבט , TRUE = משוער, FALSE = מדויק)

וִידֵאוֹ: כיצד להשתמש ב- VLOOKUP (3 דקות)

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

2. VLOOKUP נראה רק נכון

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

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

VLOOKUP יכול להסתכל רק מימין לעמודה של ערך החיפוש

אתה יכול להתגבר על מגבלה זו באמצעות INDEX ו- MATCH במקום VLOOKUP.

3. VLOOKUP מוצא את ההתאמה הראשונה

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

VLOOKUP תמיד מוצא את ההתאמה הראשונה

הערה: ההתנהגות יכולה להשתנות כאשר משתמשים ב- VLOOKUP במצב התאמה משוער. מאמר זה מסביר את הנושא בפירוט.

4. VLOOKUP אינו תלוי רישיות

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

VLOOKUP אינו תלוי רישיות

אנו מציעים גם הכשרה בתשלום עבור VLOOKUP ו- INDEX/MATCH

5. ל- VLOOKUP יש שני מצבי התאמה

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

דוגמה להתאמה מדויקת VLOOKUP - התאמת סרטים

הנוסחה ב- H6 לשנת החיפוש המבוססת על התאמה מדויקת של שם הסרט היא:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

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

דוגמה להתאמה משוערת ב- VLOOKUP - עמלות

הנוסחה ב- D5 עושה התאמה משוערת לאחזור העמלה הנכונה:

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

6. זהירות: VLOOKUP משתמש בהתאמה משוערת כברירת מחדל

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

להתאמה מדויקת, השתמש FALSE או 0. להתאמה משוערת, הגדר את טווח_הסתכלות כ- TRUE או 1:

 
= VLOOKUP (value,table,column,TRUE) // approximate match = VLOOKUP (value,table,column,FALSE) // exact match

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

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

כדי להימנע מבעיה זו, הקפד להשתמש ב- FALSE או אפס כארגומנט הרביעי כאשר אתה רוצה התאמה מדויקת.

7. אתה יכול לאלץ את VLOOKUP לבצע התאמה מדויקת

כדי לאלץ את VLOOKUP למצוא התאמה מדויקת, הקפד להגדיר את הארגומנט 4 (range_lookup) ל- FALSE או אפס. שתי נוסחאות אלה שוות ערך:

 
= VLOOKUP (value, data, column, FALSE) = VLOOKUP (value, data, column, 0)

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

8. אתה יכול להגיד ל- VLOOKUP לבצע התאמה משוערת

כדי להשתמש ב- VLOOKUP במצב התאמה משוער, השמיט את הארגומנט הרביעי (range_lookup) או ספק אותו כ- TRUE או 1. שלוש נוסחאות אלה שוות ערך:

 
= VLOOKUP (value, data, column) = VLOOKUP (value, data, column, 1) = VLOOKUP (value, data, column, TRUE)

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

וִידֵאוֹ: כיצד להשתמש ב- VLOOKUP להתאמות משוערות

9. בהתאמות משוערות יש למיין נתונים

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

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

10. VLOOKUP יכול למזג נתונים בטבלאות שונות

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

VLOOKUP למזג נתונים על ידי הצטרפות לטבלאות -לפני

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

VLOOKUP למזג נתונים על ידי הצטרפות לטבלאות -לאחר מכן

קישור: דוגמה למיזוג עם VLOOKUP .

וִידֵאוֹ: כיצד להשתמש ב- VLOOKUP למיזוג טבלאות .

11. VLOOKUP יכול לסווג או לסווג נתונים

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

דוגמה קלאסית היא ציונים, שבהם עליך להקצות ציון על סמך ציון:

VLOOKUP נהג לקטלג - הקצאת ציונים

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

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

VLOOKUP משמש לקטלוג - הקצאת קבוצות שרירותיות

12. הפניות מוחלטות הופכות את VLOOKUP לנייד יותר

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

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

הפניות מוחלטות הופכות את נוסחאות VLOOKUP לניידות יותר

13. טווחים בשם הופכים את VLOOKUP לקריא יותר (ונייד יותר)

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

לדוגמה, בדוגמה של נתוני העובדים לעיל, אתה יכול לקרוא לתא הקלט 'מזהה' ולאחר מכן לתת את השם בטבלה 'נתונים', תוכל לכתוב את הנוסחה כדלקמן:

טווחים בשם הופכים את נוסחאות VLOOKUP לקלות יותר לקריאה

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

14. הכנסת עמודה עלולה לשבור נוסחאות VLOOKUP קיימות

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

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

הכנסת עמודה בטבלה עלולה לשבור את VLOOKUP

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

15. ניתן לחשב מדד עמודות באמצעות ROW או COLUMN

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

לדוגמה, עם נתוני העובדים להלן, אנו יכולים להשתמש בפונקציה COLUMN ליצירת אינדקס עמודה דינמית. עבור הנוסחה הראשונה בתא C3, COLUMN כשלעצמו יחזיר 3 (מכיוון שעמודה C היא השלישית בגליון העבודה) ולכן עלינו פשוט לחסר אחת, ולהעתיק את הנוסחה:

דוגמה לשימוש ב- COLUMN לחישוב מדד העמודות עבור VLOOKUP

כל הנוסחאות זהות ללא צורך בעריכה לאחר.

הנוסחה בה אנו משתמשים היא:

 
= VLOOKUP (id,data, COLUMN ()-1,0)

16. השתמש ב- VLOOKUP + MATCH לאינדקס עמודה דינמי לחלוטין

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

זה נקרא לפעמים חיפוש דו כיווני מכיוון שאתה מחפש את השורה והעמודה למעלה.

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

לדוגמה, נניח שיש לך מכירות לחודש, שפרצו על ידי איש המכירות:

בדיקת VLOOKUP דו כיוונית - כיצד לחפש את החודש?

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

חיפוש דו כיווני VLOOKUP באמצעות MATCH כדי לקבל את אינדקס העמודות

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

 
= VLOOKUP (H2,data, MATCH (H3,months,0),0)

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

17. VLOOKUP מאפשר תווים כלליים להתאמה חלקית

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

Excel מספק שתי תווים כלליים: כוכבית (*) תואמת תו אחד או יותר, וסימן שאלה (?) תואם לתו אחד.

לדוגמה, תוכל להקליד כוכבית ישירות לתוך התא ולהתייחס אליו כערך חיפוש באמצעות VLOOKUP. במסך למטה, הכנסנו 'Mon*' ל- H3, שהוא טווח בשם 'val'. זה גורם ל- VLOOKUP להתאים לשם 'Monet'.

VLOOKUP עם תווים כלליים - שימוש בכוכבית ישירות

הנוסחה במקרה זה פשוטה:

 
= VLOOKUP (val,data,1,0)

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

VLOOKUP עם תווים כלליים - כוכבית מחוברת לערך החיפוש

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

 
= VLOOKUP (val&'*',data,1,0)

הערה: היזהר עם תווים כלליים ו- VLOOKUP. הם נותנים לך דרך קלה ליצור 'התאמה עצלה', אך הם גם מקלים על מציאת ההתאמה הלא נכונה.

18. אתה יכול ללכוד שגיאות #N/A ולהציג הודעה ידידותית

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

לאחר שתתחיל להשתמש ב- VLOOKUP, אתה צפוי להיתקל בשגיאת #N/A, המתרחשת כאשר VLOOKUP אינו מצליח למצוא התאמה.

איך להשיג פי באקסל

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

VLOOKUP עם שגיאה #לא רלוונטית

הנוסחה במקרה זה היא התאמה מדויקת סטנדרטית לחלוטין:

 
= VLOOKUP (E6,data,2,0)

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

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

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

שגיאה VLOOKUP #לא רלוונטית שנלכדה עם IFERROR

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

 
= IFERROR ( VLOOKUP (E6,data,2,0),'Not found')

19. מספרים כטקסט עלולים לגרום לשגיאת התאמה

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

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

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

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

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

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

 
= VLOOKUP (id&'',planets,2,0)

מספרים שהוזנו כפתרון שגיאה VLOOKUP בטקסט

אם אינך יכול להיות בטוח מתי יהיו לך מספרים ומתי יהיה לך טקסט, תוכל לספק את שתי האפשרויות על ידי עטיפת VLOOKUP ב- IFERROR וכתיבת נוסחה לטיפול בשני המקרים:

 
= IFERROR ( VLOOKUP (id,planets,3,0), VLOOKUP (id&'',planets,3,0))

20. תוכל להשתמש ב- VLOOKUP כדי להחליף הצהרות IF מקוננות

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

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

הקצאת ציונים עם נוסחת IF מקוננת ארוכה

נוסחת IF המקוננת המלאה נראית כך:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

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

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

לאחר הגדרת 'מפתח' טווח בשם לטבלת מפתחות הציונים, נוסחת VLOOKUP פשוטה מאוד ויצרה את אותן ציונים כמו נוסחת IFs המקורית המקוננת:

הקצאת ציונים בנוסחת VLOOKUP פשוטה

עם טבלת מפתחות הציונים בשם 'מפתח' יש לנו נוסחת VLOOKUP פשוטה מאוד:

 
= VLOOKUP (C5,key,2,TRUE)

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

וִידֵאוֹ: כיצד להחליף IFs מקוננים ב- VLOOKUP

21. VLOOKUP יכול להתמודד רק עם קריטריונים בודדים

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

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

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

בעיית קריטריונים מרובים ב- VLOOKUP - כיצד לחפש שם פרטי ושם משפחה?

ראשית, הוסף טור עוזר שפשוט מחבר בין שמות משפחה ושם משפחה יחד:

VLOOKUP קריטריונים מרובים שלב 2 - הוסף טור עוזר המצטרף למספר קריטריונים

לאחר מכן הגדר את VLOOKUP לשימוש בטבלה הכוללת עמודה חדשה זו, והצטרף לשמות פרטיים ושם משפחה לערך החיפוש:

VLOOKUP קריטריונים מרובים שלב 3 - הצטרף לקריטריונים ליצירת ערך חיפוש

הנוסחה הסופית VLOOKUP מחפשת שמות פרטיים ושמות משפחה יחד באמצעות עמודת העזר כמפתח:

 
= VLOOKUP (C3&D3,data,4,0)

22. שני VLOOKUPS מהירים יותר מ- VLOOKUP אחד

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

הרקע: דמיין שיש לך הרבה נתוני הזמנה, נניח, יותר מ -10,000 רשומות ואתה משתמש ב- VLOOKUP כדי לחפש את סך ההזמנות על סמך מזהה ההזמנה. אז אתה משתמש במשהו כזה:

 
= VLOOKUP (order_id,order_data, 5, FALSE)

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

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

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

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

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

הנוסחה הסופית נראית כך:

 
= IF ( VLOOKUP (order_id,order_data,1,TRUE)=order_id,  VLOOKUP (order_id,order_data,5,TRUE), 'Missing')

למדתי גישה זו מצ'רלס וויליאמס מ- FastExcel, שיש לו מאמר פנטסטי ומפורט כאן: מדוע 2 VLOOKUPS טובים יותר מ- VLOOKUP אחד .

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

23. INDEX ו- MATCH יחד יכולים לעשות כל מה ש- VLOOKUP יכול לעשות ועוד

אם תעקוב אחר Excel באינטרנט, סביר להניח שתתקל ב ויכוח VLOOKUP לעומת INDEX/MATCH. הוויכוח יכול להתחמם באופן מפתיע :)

העיקר הוא: INDEX + MATCH יכול לעשות כל מה ש- VLOOKUP (ו- HLOOKUP) יכול לעשות, עם הרבה יותר גמישות, במחיר של קצת יותר מורכבות. לכן, מי שמצדד ב- INDEX + MATCH יטען (בצורה מאוד שפויה) שאולי כדאי שתתחיל ללמוד INDEX ו- MATCH, מכיוון שהוא נותן לך מערכת כלים טובה יותר בסופו של דבר.

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

שני הסנטים שלי הם שאם אתה משתמש ב- Excel לעתים קרובות, אתה תרצה ללמוד כיצד להשתמש ב- INDEX וב- MATCH. זה שילוב חזק מאוד.

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

למידע נוסף על INDEX ו- MATCH, עיין במאמר זה .

הסופר דייב בראנס


^