כל הסודות של פונקציית Excel VLOOKUP למציאת נתונים בטבלה וחילוץ אותם לטבלה אחרת

2024 מְחַבֵּר: Malcolm Clapton | [email protected]. שונה לאחרונה: 2023-12-17 03:56

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

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

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

VLOOKUP הוא קיצור של v אֲנָכִי נ.ס בְּדִיקָה. כמו כן, VLOOKUP - חיפוש אנכי. עצם השם של הפונקציה רומז לנו שהיא מחפשת בשורות הטבלה (אנכית - איטרציה על השורות ותיקון העמודה), ולא בעמודות (אופקי - איטרציה על העמודות ותיקון השורה). יש לציין של-VLOOKUP יש אחות - ברווזון מכוער שלעולם לא יהפוך לברבור - זוהי פונקציית HLOOKUP. HLOOKUP, בניגוד ל-VLOOKUP, מבצע חיפושים אופקיים, אבל הרעיון של Excel (ואכן הרעיון של ארגון נתונים) מרמז שלטבלאות שלך יש פחות עמודות והרבה יותר שורות. לכן אנחנו צריכים לחפש לפי שורות פעמים רבות יותר מאשר לפי עמודות. אם אתה משתמש בפונקציית HLO לעתים קרובות מדי באקסל, אז סביר להניח שלא הבנת משהו בחיים האלה.

תחביר

לפונקציה VLOOKUP יש ארבעה פרמטרים:

= VLOOKUP (;; [;]), פה:

- הערך הרצוי (לעיתים רחוקות) או הפניה לתא המכיל את הערך הרצוי (רובם המכריע של המקרים);

- התייחסות לטווח של תאים (מערך דו-ממדי), בעמודה הראשונה (!) שבה יחפשו את ערך הפרמטר;

- מספר עמודה בטווח שממנו יוחזר הערך;

- זהו פרמטר חשוב מאוד שעונה על השאלה האם העמודה הראשונה של הטווח ממוינת בסדר עולה. אם המערך ממוין, אנו מציינים את הערך TRUE או 1, אחרת - FALSE או 0. אם פרמטר זה מושמט, ברירת המחדל היא 1.

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

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

איך בדיוק פועלת נוסחת VLOOKUP?

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

  

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

  

זרימות עבודה של נוסחאות

VPR סוג I

VPR סוג II

מסקנה לנוסחאות בצורת I

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

מסקנה לנוסחאות מסוג II

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

ביצועי VLOOKUP

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

• אני צריך מחשב חזק יותר;
• אני צריך נוסחה מהירה יותר, למשל, אנשים רבים יודעים על INDEX + MATCH, שהוא כביכול מהיר יותר ב-5-10%.

ומעטים האנשים שחושבים שברגע שמתחילים להשתמש ב-VLOOKUP מסוג I ומבטיחים שהעמודה הראשונה ממוינת בכל אמצעי, מהירות ה-VLOOKUP תגדל פי 57. אני כותב במילים - חמישים ושבע פעמים! לא 57%, אלא 5,700%. בדקתי עובדה זו בצורה אמינה למדי.

הסוד של עבודה מהירה כזו טמון בעובדה שניתן ליישם אלגוריתם חיפוש יעיל ביותר על מערך ממוין, הנקרא חיפוש בינארי (שיטת חצויה, שיטת דיכוטומיה). אז VLOOKUP מסוג I מיישם אותו, ו-VLOOKUP של סוג II חיפושים ללא אופטימיזציה כלל. הדבר נכון גם לגבי פונקציית MATCH הכוללת פרמטר דומה ופונקציית LOOKUP שפועלת רק על מערכים ממוינים ונכללת באקסל לצורך תאימות עם Lotus 1-2-3.

חסרונות של הנוסחה

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

כמה היבטים של יישום הנוסחה בחיים האמיתיים

חיפוש טווח

המחשה קלאסית של חיפוש טווח היא המשימה של קביעת הנחה לפי גודל הזמנה.

חפש מחרוזות טקסט

כמובן, VLOOKUP מחפש לא רק מספרים, אלא גם טקסט. יש לזכור שהנוסחה אינה מבחינה בין המקרה של תווים. אם אתה משתמש בתווים כלליים, אתה יכול לארגן חיפוש מעורפל. יש שני תווים כלליים: "?" - מחליף כל תו אחד במחרוזת טקסט, "*" - מחליף כל מספר של כל תווים.

מרחבי לחימה

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

פורמט נתונים שונה

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

= VLOOKUP (−− D7; מוצרים! $ A $ 2: $ C $ 5; 3; 0) - אם D7 מכיל טקסט והטבלה מכילה מספרים;

= VLOOKUP (D7 & ""); מוצרים! $ A $ 2: $ C $ 5; 3; 0) - ולהיפך.

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

• שלילה כפולה -D7.
• הכפלה באחד D7 * 1.
• תוספת אפס D7 + 0.
• העלה לעוצמה הראשונה D7 ^ 1.

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

כיצד לדכא # N / A

זה מאוד נוח לעשות עם הפונקציה IFERROR.

לדוגמה: = IFERROR (VLOOKUP (D7; מוצרים! $ A $ 2: $ C $ 5; 3; 0); "").

אם VLOOKUP מחזיר את קוד השגיאה # N / A, אז IFERROR יירט אותו ויחליף את פרמטר 2 (במקרה זה, מחרוזת ריקה), ואם לא מתרחשת שגיאה, אז הפונקציה הזו תעמיד פנים שהיא לא קיימת בכלל, אבל יש רק VLOOKUP שהחזיר תוצאה נורמלית.

מַעֲרָך

לעתים קרובות הם שוכחים להפוך את ההתייחסות למערך למוחלטת, וכאשר מתיחה המערך "צף". זכור להשתמש ב-$ A $ 2: $ C $ 5 במקום A2: C5.

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

רעיון אפילו טוב יותר יהיה להכריז על מערך זה כטווח בעל שם.

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

ובכן, על גבול הגאונות - לסדר את המערך בצורה.

שימוש בפונקציה COLUMN כדי לציין את העמודה לחילוץ

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

יצירת מפתח מורכב עם & "|" &

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

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