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

VLOOKUP הוא אחד הפונקציות השימושיות ביותר של Excel, והוא גם אחד הפחות מובן.במאמר זה, אנו demystify VLOOKUP בדרך של דוגמה בחיים האמיתיים.אנו ניצור תבנית חשבונית שימושית עבור חברה פיקטיבית.היא פונקציה.מאמר זה יניח כי הקורא כבר יש הבנה מעמיקה של פונקציות 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), ולחץ על הזן :

הערך של "A11" מוכנס לתוך הארגומנט הראשון.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

= D11 * E11

. .. שנראה כמו זה. ..

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

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

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

1. נסיר את קוד פריט לדוגמה מתוך תא A11 ו "2" מהתא D11.הדבר יגרום לנוסחאות VLOOKUP שנוצרו לאחרונה להצגת הודעות שגיאה:
   
   אנו יכולים לתקן זאת על ידי שימוש מושכל בפונקציות של ( ) ו- ISBLANK( ) של Excel.אנו משנים את הנוסחה שלנו. .. = VLOOKUP( A11, 'מסד נתונים' A2: D7,2, FALSE) . .. זה. .. = IF( ISBLANK( A11) "," VLOOKUP( A11 "
   
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, או פשוט לראות את תבנית חשבונית שהתקבל שלנו, זה ניתן להוריד מכאן.