יישום והגדרה של פונקציית Excel זו
VLOOKUP היא פונקציית Excel שבעזרתה המשתמש יכול לחפש ולהעריך את תוכן הטבלה. פונקציה זו זמינה בגרסאות מ- Excel 2007 עבור Windows ו- Mac.
מהו ה- VLOOKUP?
יש להסביר כאן את השימושים האפשריים ב- VLOOKUP באמצעות דוגמה: בזה אתה חובב ספרות גדול ולכן יצרת גיליון אלקטרוני משלך שבו תוכל למיין בזהירות את הספרים שאספת. כל יצירה רשומה עם מידע על הקטגוריות הבאות:
-
מְחַבֵּר
-
כותרת
-
מספר עמוד
-
שנת הוצאה לאור
עכשיו תרצה לתת לחבר טיפ לספר לקחת איתך בפגישה הבאה שלך. למרבה הצער, אתה יכול לחשוב רק על המחבר, לא על שם הספר. כאן נכנס לתמונה VLOOKUP, מכיוון שהוא יכול להשתמש בערך קלט זה כדי לזרוק את המידע שאתה מחפש במכה אחת.
כיצד משתמשים ב- VLOOKUP?
עוד לפני שחושבים על גיבוש נוסחאות, יש לקבוע היכן שדה הקלט ושדות הפלט השונים ימוקמו מאוחר יותר. לשם כך, יש טעם ליצור טבלה נפרדת שבתחילה ריקה ובכך מאפשרת מקום למידע שהוזכר. אם אתה מעצב את הטבלה החדשה הזו על סמך הדוגמה של הטבלה הקיימת, יהיה לך יתרון לחסוך זמן מאוחר יותר.
על בסיס זה, ניתן ליצור את נוסחת VLOOKUP באופן ידני או באופן אוטומטי על ידי Excel. למתחילים כדאי להשתמש בגישה האחרונה על מנת להכיר בהדרגה את המבנה ואת השפעת הנוסחה. לשם כך, הלחצן "הוספת פונקציה" נבחר בכרטיסייה "נוסחאות". ה- VLOOKUP מוסתר בחלון שנפתח. לאחר האישור נפתח שוב חלון בו ניתן למלא את ארבעת הפרמטרים של הנוסחה. אלו הם:
-
קריטריון חיפוש
-
מַטרִיצָה
-
מדד עמודות
-
אזור_התייחסות
לכן הטיוטה הגולמית של הנוסחה נראית כך:
= VLOOKUP (קריטריון חיפוש, מטריצה, אינדקס עמודה, טווח_קישור)
וביישום אפשרי אחד כזה:
= VLOOKUP (H3; A3: E40; 5)
קריטריון חיפוש
כדי שהפונקציה תדע איזה ערך צריך לשמש כנקודת התחלה, השורה שנבחרה כשדה הקלט שני שלבים קודם לכן מצויינת בשדה "קריטריון חיפוש". בדוגמה שלנו, שם של כותב הספר "פיליפ פולמן" מוזן שם. זה הופך את הנוסחה לגמישה ואין צורך להתאים אותה שוב ברגע שהערך שהוזן משתנה.
מַטרִיצָה
שדה הקלט "מטריקס" מתאר את הטבלה שבה ניתן למצוא את המידע שיש להוציא. מטריצה מיוחדת זו מכילה אפוא גם את הטורים של שם הספר, מספר העמוד ושנת הפרסום.
המטריצה נבחרת לחלוטין פעם אחת ללא הכותרות מצד שמאל למעלה לשוליים הימניים התחתונים. בדרך זו, Excel יודע איזה תוכן יש לקחת בחשבון בעת הערכה.
מדד עמודות
שדה הקלט של "אינדקס העמודות" מבקש מהמשתמש להגדיר את העמודה של המטריצה שבה מופיע רק הערך המבוקש. הקצאת העמודות ממוספרת באופן כרונולוגי. המשמעות היא שהעמודה הראשונה בטבלה מקבלת את הערך 1, השנייה את הערך 2 וכו '. בדוגמה שלנו זה מתאים לאינדקס עמודה 1 למחבר, אינדקס עמודה 2 לכותרת, אינדקס עמודה 3 למספר העמוד. ומדד העמודות 4 לשנת הפרסום.
על מנת שהטבלה תהיה גמישה ככל האפשר, ניתן לקשר את כותרת העמודה במקום המספר. יש בכך יתרון כי הנוסחה ניתנת להעברה גם לשורות אחרות ללא בעיות, שכן ניתן להתאים את כותרת העמודה בכל פעם בגמישות.
תשומת הלב: ה- VLOOKUP קורא את המטריצה משמאל לימין, ולכן יש למקם את אינדקס העמודות מימין לעמודה לצורך קריטריון החיפוש על מנת שהפונקציה תביא אותו בחשבון!
אזור_התייחסות
הפרמטר "טווח_חיפוש" משלים את נוסחת VLOOKUP על ידי ציון הדיוק שבו מוערכת הטבלה. עם זאת, הוא שונה מהרכיבים שהוזכרו בעבר בנוסחה מכיוון שהוא אופציונלי. אם הערך 0 הוזן כ"לא נכון ", Excel מחפש רק את הערך שצוין כקריטריון החיפוש. עם הערך 1 עבור "נכון", עם זאת, החיפוש ממשיך לערכים ברורים אם לא ניתן היה למצוא את הערך המדויק.
ציון פרמטר זה הוא אופציונלי מכיוון שערך 1 מוגדר כברירת מחדל. הגדרה זו תהיה שימושית מאוחר יותר ב- VLOOKUP המתקדם עם קריטריוני חיפוש מרובים.
המיזוג
ברגע שהוגדרו כל הפרמטרים הדרושים, ניתן להשתמש ב- VLOOKUP. לאחר הזנת קריטריון החיפוש ואישור הפונקציה, הערך שאתה מחפש מופיע בשורה שהוגדרה כשדה פלט.
בדוגמה שלנו מוצג כעת שם הספר "מצפן הזהב", המתאים למחבר שהוזן. על מנת לברר במהירות את מספר העמוד ושנת הפרסום, אין צורך לעשות דבר מעבר לגרירת נוסחת VLOOKUP הקיימת לתאים הבאים. זה כל כך קל מכיוון שמדד העמודות של ה- VLOOKUP נקשר לכותרת העמודה של הטבלה הראשונה וגם הטבלה השנייה בנויה באותו סדר.
במקרה שהטבלאות צריכות להיות שונות זו מזו או שתתרחש שגיאה למרות הכל, ניתן לשנות את נוסחת VLOOKUP גם באופן ידני. לשם כך יש להתאים את הספרה הלפני אחרונה של מדד העמודות לעמודה של הערך החדש שיש להפיק.
VLOOKUP עם קריטריוני חיפוש מרובים
לעתים קרובות מספיק שקריטריון חיפוש אחד אינו מספיק כדי להעריך במדויק טבלת אקסל גדולה. אז הגיוני להריץ את VLOOKUP עם מספר קריטריוני חיפוש. לשם כך יש להוסיף את הנוסחה הקיימת בפונקציית IF נוספת. באופן זה ניתן לקחת בחשבון עד שמונה קריטריוני חיפוש שונים במהלך היישום.
VLOOKUP במספר גיליונות אלקטרוניים של Excel
אם לא ניתן למצוא את קריטריון החיפוש רק בטבלה אחת, אלא אולי גם בטבלה אחרת, ניתן להתאים את נוסחת VLOOKUP בהתאם. לשם כך יש להציב הן את הפונקציה if והן את הפונקציה ISERROR מול הנוסחה הקיימת. לשם כך נדרשים חמישה פרמטרים:
-
קריטריון חיפוש
-
מטריקס 1 ומטריקס 2
-
מדד עמוד 1 ומדד עמוד 2
התוצאה נראית כך:
= IF (ISERROR (VLOOKUP (קריטריון חיפוש, מטריצה 1, טור-אינדקס 1, 0));
VLOOKUP (קריטריון חיפוש; מטריצה 2; אינדקס עמודה 2.0); VLOOKUP (קריטריון חיפוש; מטריצה 1; אינדקס עמודה 1;))
וביישום אפשרי אחד כזה:
= IF (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
קריטריון החיפוש משמש להוספת הערך שיש לחפש בשתי הטבלאות. Matrix1 ו- Matrix2 מגדירים את אזורי התא המתאימים של שתי הטבלאות. מדד העמודות 1 ועמוד אינדקס 2 משמשים להגדרת פירוט רב יותר באילו עמודות בטבלאות המתאימות יש לחפש.
אם הערך שאתה מחפש מתרחש בשתי הטבלאות, Excel יפיק את התוצאה מהטבלה הראשונה. עם זאת, אם הערך לא נמצא באחת משתי הטבלאות, תופיע הודעת שגיאה. היתרון של הנוסחה הוא ששתי הרשימות לא חייבות להיות בעלות אותו מבנה או להיות באותו גודל.
הקצה ערכים לקטגוריות באמצעות VLOOKUP
פונקציה נוספת של VLOOKUP מאפשרת לחלק את הערכים המפורטים באופן אוטומטי לאותיות ולנבואות לבחירתך. בדוגמה הקודמת שלנו, יש להוסיף עמודת טבלה נוספת לסוג הספר. הספרים באורך של עד 50 עמודים אמורים להיכלל בז'אנר של הסיפור הקצר, ואילו ספרים שבין 51 ל -150 עמודים מוקצים לרומן ומ- 151 עמודים לרומן. כדי לאפשר זאת, אין צורך בנוסחה נוספת ב- VLOOKUP, רק בשימוש בסוגריים מתולתלים "{}". הנוסחה המוגמרת נראית כך:
= VLOOKUP (B1; {1. "סיפור קצר"; 51. "Novella"; 151. "Novel"}; 2)
התוכן של הסוגריים המתולתלים מציין מטריצה המגדירה את השטח של סוג ספר בהתאמה. הקצאת אורך הצד לסוג המתאים שוכנת אפוא בתוך הסוגריים המתולתלים. הנוסחה משתמשת בזוגות ערכים, כל אחד מופרד בנקודה. המטריצה {1. "סיפור קצר"; 51. "נובלה"; 151. "רומן"} נקראת כך:
"מתוך הצגה אחת סיפור קצר, מתוך 51 הצגת נובלה, מתוך 151 הצגת רומן."
ניתן להתאים מטריצה זו בקלות למשימות שונות. זה נוגע מצד אחד לגודל ומספר המטריצות כמו גם ייעודן. אז אפשר להוציא מחרוזות או מספרים כתוצאה במקום אותיות בודדות. כל שעליך לעשות הוא להתאים את האותיות בנוסחה.
VLOOKUP במספר גליונות עבודה
פונקציה נוספת של VLOOKUP מאפשרת למשתמשיה לקשר תוכן הממוקם בגיליונות אלקטרוניים שונים. בדוגמה שלנו, אפשרות זו יכולה להיות שימושית כאשר המידע ממיין לראשונה בגליונות עבודה שונים ולאחר מכן מתעדכן בטבלת סיכום.
תארו לעצמכם, בנוסף לספרים שלכם, אתם גם מפרטים את הסרטים שנאספו בגיליון אלקטרוני של Excel. לאחר מכן אתה משלב את שני האוספים בטבלה אחת גדולה.
היתרון של הליך זה טמון לא רק בסדר המוגדל, אלא גם בהימנעות מטעויות אפשריות. אם ברצונך ליצור ערך חדש או לעדכן ערך קיים, אינך צריך לחפש בטבלה הגדולה, אלא תוכל לגשת לכמה קטנים יותר. לאחר מכן הערכים מועברים אוטומטית לטבלת האקסל המסכמת. זה הופך את הכתיבה בשולחן הגדול למיותרת, שבמקרה הטוב נמנעת מצער מצער ומשרשרת של הודעות שגיאה לאחר מכן.
איך נראית הנוסחה?
פונקציה זו מתאפשרת שוב על ידי הוספת נוסחה נוספת. בעוד שחיפוש עם מספר קריטריונים דורש נוסחת IF נוספת, עבודה עם מספר גליונות עבודה דורשת נוסחה INDIRECT. זה מאפשר לציין טווח מגליון אלקטרוני אחר למטריצת VLOOKUP.
= VLOOKUP (קריטריון חיפוש; INDIRECT (מטריצה); אינדקס עמודה; טווח_קישור)
תשומת הלב: נוסחה זו תפעל רק אם הטבלאות הבודדות בגיליונות השונים יש את אותן שמות כמו כותרות העמודות של הטבלה הכללית. ניתן למנות טבלאות שלמות ב"שדה שם "בפינה השמאלית העליונה מעל רשת התא. ניתן לצפות בטבלאות שכבר נקראו בשילוב המקשים Ctrl + F3.
התמודדות עם הודעות שגיאה שעולות
עבודה עם טבלאות Excel מקושרות יכולה להוביל לבעיות לא רצויות. זה כולל במיוחד פלט של ערכים שגויים. במקרה שהערך השגוי 0 הוא פלט, יש בעיה קטנה בהגדרות של Excel, שניתן לתקן במהירות.
הודעת השגיאה הנפוצה #NV, לעומת זאת, היא פונקציה מכוונת של VLOOKUP, המצביעה בפני המשתמש כי הערך הנדרש אינו זמין. פתק זה יכול להיות מעוצב בצורה שונה בעזרת נוסחה.
VLOOKUP - סקירה כללית
VLOOKUP היא פונקציית Excel שימושית שניתן להשתמש בה לחיפוש והערכה של טבלאות. יתרונותיה ניכרים ביישום הידידותי למשתמש והגמיש. באופן זה, כל מי שעובד באופן קבוע עם טבלאות אקסל יכול ליהנות מהפונקציה. יהיה זה האספן הפרטי שיוצר שולחנות קטנים משלו, או החברה הגדולה המעבדת מערכי נתונים משמעותיים יותר.
אם לעומת זאת, עדיין יש לך בקשות ללא מענה ש- VLOOKUP לא הצליח לענות עליהן, תוכל לצפות לאפשרות נוספת של Excel: מיקרוסופט מציעה למשתמשי Excel 365 את ה- XLOOKUP החדש מאז תחילת 2022-2023. זה בונה על הכישורים של VLOOKUP ומשלים אותם עם פונקציות נוספות, לפעמים אפילו פשוטות יותר. לכן, נפתחת בשלב זה גם שגרה חדשה בהערכת הנתונים.
