תרגילים קורס אקסל מתקדם פתרונות - Vlookup
תוכן הדף
- הסבר כללי פונקציית Vlookup
- פתרונות לדוגמה פונקציית Vlookup תרגילים נבחרים - סרטונים
רוצים לדעת לעבוד עם פונקציית Vlookup בצורה מיטבית? הרשמו היום לסדנת היכרות עם Excel-iT בנושא פונקציית Vlookup!
פונקציית Vlookup הסבר כללי
1. שימוש בפונקציה לצורך חיפוש התאמה מדויקת - FALSE/0
2. שימוש בפונקציה לצורך חיפוש בהתאמה משוערת - TRUE/1
פונקציית Vlookup הינה אחת הפונקציות החשובות באקסל והשימוש בה הוא שכיח מאוד. הפונקציה עוזרת לנו לעדכן נתונים מטבלה אחת לטבלה אחרת, כמו כן היא עוזרת לנו להשוות נתונים בין טבלאות שונות והכל בצורה מהירה מאוד ודינאמית כך שכל שינוי שיעשה בנתוני המקור שלנו יתעדכן באופן מידי בתוצאת הפונקציה.
לא ניתן לעבוד בצורה יעילה ומתקדמת באקסל ללא שליטה טובה בפונקציה זו.
תחביר פונקציית Vlookup
Lookup value - הערך אותו נחפש בטווח/טבלה ממנה אנו שולפים את הנתון התואם עבורו. חשוב לחדד שזה אינו הערך אותו אנו מעוניינים לקבל/לחלץ מהטבלה אלא הערך אותו אנו נותנים לאקסל לחפש בטבלה. ** שימו לב שערך זה חייב להימצא תמיד בעמודה הראשונה בטווח/טבלה ממנה אנו שולפים את הנתונים.
Table array– הטווח (בד"כ טבלה) שבו אנו רוצים לחפש את הערך.
Column index number – מספר העמודה בטווח המציין את מיקום הערך אותו אנו מעוניינים לקבל חזרה.
Range lookup – כאן אנו צריכים להגדיר האם ברצוננו לבצע חיפוש בהתאמה מדויקת או משוערת כלומר לבחור 0 /False עבור התאמה מדויקת או 1/True עבור התאמה משוערת.
דוגמה 1 התאמה מדויקת 0 או False:
נשתמש בהתאמה מדויקת בכדי לבצע חיפוש התואם באופן מדויק ל ערך ה Lookup value שלנו, במקרה זה "צרפת". במידה והערך לא ימצא בטווח שלנו, הפונקציה תחזיר לנו טעות מסוג #N/A.
מקרים אפשריים בהם נקבל טעות:
• הערך צרפת לא קיים כלל בטווח שלנו
• הערך קיים אבל בצורה שונה לדוגמה רווח מיותר לפני או אחרי המילה או טעות באיות/הקלדה של ערך ה Lookup value שלנו.
• הערך קיים וזהה אבל מוגדר כ DATA TYPE שונה.
בדוגמה זו אנו מעוניינים לדעת מה עלות השילוח ליעד צרפת.
Lookup Value - "צרפת" או תא E1
Table array - נסמן את הטווח (כל טבלת המחירים) כלומר הטווח A1:C8 – יש לשים לב לכך שבמידה ונרצה לגרור את הפונקציה על תאים נוספים יש לקבע את הטווח $A$1:$C$8.
Column index number – 2 מפני שאנו מעוניינים לקבל את עלות השילוח – ערכים אלו נמצאים בעמודה מס 2 בטווח.
Range lookup – 0 או False מפני שבמקרה זה אנו מעוניינים לחפש את הערך צרפת בדיוק כפי שהוא מופיע בתא E1
דוגמה מס 2 התאמה משוערת 1 או TRUE
נשתמש בהתאמה משוערת בעיקר כשאנו מעוניינים לעבוד עם טווחים בין מספרים.
בדוגמה זו אנו מעוניינים לחלץ את אחוז העמלה אותה יקבל סוכן מכירות שמכר בסכום חודשי מצטבר של 7,500 ₪ בחודש מסוים. טווח הנתונים שלנו כולל טבלת עמלות לפי מדרגות.
אם נשתמש בהתאמה מדויקת נקבל טעות מפני שהערך 7,500 לא קיים בטווח שלנו. אבל מפני שהערך 7,500 נמצא בין הערכים 7,000 ל 9,000 נקבל חזרה את הערך 1.5% במידה ונעבוד עם התאמה משוערת.
ניתן לחשוב על התאמה משוערת או כטווחים או הערך הקרוב ביותר מלמטה. כלומר גם עבור הערך 8,999 נקבל 1.5% וזאת מפני ש 7,000 הוא הערך הקרוב ביותר ל 8,999 מלמטה.
Lookup value – 7,500 או תא E1
Table array - נסמן את הטווח (כל טבלת המחירים) כלומר הטווח A1:B5 – יש לשים לב לכך שבמידה ונרצה לגרור את הנוסחה על תאים נוספים יש לקבע את הטווח $A$1:$B$5.
Column index number – 2 מפני שאנו מעוניינים לקבל את עלות השילוח – ערכים אלו נמצאים בעמודה מס 2 בטבלה.
Range lookup – 1 או True מפני שבמקרה זה אנו מעוניינים לחפש את הערך 7,500 שיתכן מאוד שאינו מופיע בטווח.
אילוצים והערות פונקציית Vlookup:
1. הערך אותו נחפש חייב להימצא בעמודה מס 1 בטבלה – כלומר העמודה הימנית ביותר כאשר כיוון הגיליון הנו מימין לשמאל או עמודה שמאלית ביותר כאשר כיוון הגיליון הנו משמאל לימין. להלן דוגמה לטווח תקין מול טווח שאינו תקין.
2. הערכים בטווח שלנו צריכים להיות חד ערכיים כלומר רצוי מאוד שהערך "צרפת" בטווח יופיע פעם אחת בלבד. במידה והערך צרפת יופיע יותר מפעם אחת אנו נקבל חזרה את הנתון עבור הערך הראשון בטווח. לפי הדוגמה בטבלה זו אנו נקבל חזרה תמיד את הערך 700 ש"ח.
3. סוג ה Data type חייב להיות זהה בין ערך החיפוש שלנו (lookup value) לערך בטווח. נקודה זו חשובה במיוחד כאשר אנו עובדים עם מחרוזת מספרים וזאת מפני שלעיתים קורה מצב בו סוג ה Data type של מחרוזת ערך החיפוש שלנו שונה מסוג ה Data type של מחרוזת הערך הקיים הטווח. לדוגמה – אחד מוגדר כטקסט והשני כמספר וזאת למרות שויזואלית שניהם נראים כמספר זהה.
4. כאשר אנו משתמשים בהתאמה משוערת – המספרים (טווחים) יהיו מסודרים בסדר עולה.
פונקציית Vlookup - סרטונים
פתרון תרגיל מס' 3 - קובץ "מתקדמים 2" - שם גיליון - "Vlookp 3"
להורדת קובץ התרגיל Vlookup 3 לחצו כאן
IFERROR המשך פתרון תרגיל מס' 3 - קובץ "מתקדמים 2" - שם גיליון - "Vlookp 3"
פתרון תרגיל בקובץ " Vlookup 2"