כיצד להשתמש ב- VLOOKUP ב- Excel

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

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

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

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

החלק הקשה ביותר בשימוש ב- VLOOKUP הוא להבין בדיוק לשם מה. אז בואו נראה אם ​​נוכל לקבל את זה ברור קודם:

VLOOKUP מאחזר מידע ממאגר / רשימה המבוסס על מופע שסופק של המזהה הייחודי.

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

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

בואו ליצור דוגמה לכך: תבנית חשבונית שנוכל להשתמש בה שוב ושוב בחברה הפיקטיבית שלנו.

ראשית אנו מתחילים את Excel, ואנחנו יוצרים לעצמנו חשבונית ריקה:

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

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

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

אז יצרנו את מאגר המוצרים שלנו, שנראה כך:

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

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

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

מופיעה תיבה המאפשרת לנו לבחור כל אחת מהפונקציות הזמינות ב- Excel.

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

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

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

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

נשלים את הטיעונים לפי הסדר, מלמעלה למטה.

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

לחץ על סמל הבורר מימין לארגומנט הראשון:

לאחר מכן לחץ פעם אחת על התא המכיל את קוד הפריט (A11), ולחץ על Enter :

הערך של "A11" מוכנס לוויכוח הראשון.

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

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

לאחר מכן אנו בוחרים את מסד הנתונים כולו, לא כולל את שורת הכותרת:

... ולחץ על Enter . טווח התאים המייצג את מסד הנתונים (במקרה זה "'מאגר מוצרים'! A2: D7") מוזן אוטומטית עבורנו לוויכוח השני.

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

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

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

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

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

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

זהו זה! הזנו את כל המידע הדרוש ל- VLOOKUP להחזרת הערך הדרוש לנו. לחץ על הלחצן אישור ושים לב שהתיאור המתאים לקוד הפריט "R99245" הוזן כהלכה לתא B11:

הנוסחה שנוצרה עבורנו נראית כך:

אם נכניס קוד פריט אחר לתא A11, נתחיל לראות את כוחה של פונקציית VLOOKUP: תא התיאור משתנה כך שיתאים לקוד הפריט החדש:

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

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

שים לב שההבדל היחיד בין שתי הנוסחאות הוא הארגומנט השלישי ( Col_index_num ) השתנה מ- "2" ל- "3" (מכיוון שאנחנו רוצים לאחזר נתונים מהעמודה השלישית במסד הנתונים).

אם החלטנו לקנות 2 מהפריטים הללו, נכניס "2" לתא D11. לאחר מכן נכניס נוסחה פשוטה לתא F11 כדי לקבל את סך השורה:

= D11 * E1

... שנראה ככה ...

השלמת תבנית החשבונית

למדנו הרבה על VLOOKUP עד כה. למעשה, למדנו את כל מה שנלמד במאמר זה. חשוב לציין שניתן להשתמש ב- VLOOKUP בנסיבות אחרות מלבד מסדי נתונים. זה פחות נפוץ ועשוי להיות מכוסה במאמרי How-To Geek בעתיד.

תבנית החשבונית שלנו טרם הושלמה. על מנת להשלים אותו, היינו עושים את הפעולות הבאות:

  1. היינו מסירים את קוד הפריט לדוגמא מתא A11 ואת ה- "2" מתא D11. זה יגרום לנוסחאות VLOOKUP שנוצרו לאחרונה להציג הודעות שגיאה:



    אנו יכולים לתקן זאת על ידי שימוש מושכל בפונקציות IF () ו- ISBLANK () של Excel . אנו משנים את הנוסחה שלנו מכאן ...      = VLOOKUP (A11, 'מאגר מוצרים'! A2: D7,2, FALSE) ... לזה ... = IF (ISBLANK (A11), "", VLOOKUP (A11, 'מאגר מוצרים'! A2 : D7,2, FALSE))


  2. נעתיק את הנוסחאות בתאים B11, E11 ו- F11 עד לשאר שורות הפריטים של החשבונית. שים לב שאם נעשה זאת, הנוסחאות המתקבלות כבר לא יתייחסו כהלכה לטבלת מסד הנתונים. נוכל לתקן זאת על ידי שינוי הפניות התאים למסד הנתונים להפניות מוחלטות לתאים. לחלופין - ואפילו טוב יותר - נוכל ליצור שם טווח לכל מאגר המוצרים (כגון "מוצרים"), ולהשתמש בשם טווח זה במקום בהפניות לתאים. הנוסחה תשתנה מזה ...      = IF (ISBLANK (A11), "", VLOOKUP (A11, 'מאגר מוצרים'! A2: D7,2, FALSE)) ... לזה ...       = IF (ISBLANK (A11), "" , VLOOKUP (A11, מוצרים, 2, FALSE) ... ואז העתק את הנוסחאות לשאר שורות פריטי החשבונית.
  3. אנחנו בוודאי "המנעול" התאים המכילים נוסחאות שלנו (או ליתר דיוק לפתוח אחרים תאים), ולאחר מכן להגן על גליון, על מנת להבטיח כי הנוסחאות שלנו בנויות בקפידה אינם מוחלפים בטעות כשמישהו מגיע כדי למלא את החשבונית.
  4. נשמור את הקובץ כתבנית , כך שניתן יהיה לעשות בו שימוש חוזר על ידי כל מי שבחברה שלנו

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

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