Vlookup מתקדם בשילוב עם פונקציית Indirect
נושאי המאמר
1. סרטון דוגמה
2. הצגת המטרה
מעוניינים לשדרג את אופן העבודה באקסל אצלכם במשרד ?
» לחצו כאן למידע והזמנת קורס אקסל לארגונים המתקיים במשרדי הלקוח
» לחצו כאן למידע והזמנת קורס אקסל לארגונים המתקיים אונליין ב Zoom
רוצים ללמוד אקסל בצורה מקיפה ולעבוד בצורה חכמה ומהירה ? ובקיצור כמו תותחי אקסל אמיתיים ?
» לחצו כאן למידע והרשמה לקורס אקסל מתקדם, מקיף פרקטי ומעניין! ולמדו את כל הכלים, הטריקים והפונקציות החשובות!
______________________________________________________________________________
הרשמו ל Newsletter של Excel-iT וקבלו את קבצי התרגול עבור כל סרטוני הבלוג שלנו להרשמה
______________________________________________________________________________
סרטון דוגמה
הצגת המטרה
בדוגמה זו יש לנו קובץ שנתי המורכב ממספר גיליונות כך שכל גיליון מייצג חודש באותה השנה.
חשוב לציין ששם הגיליון הוא שם החודש. הגליונות הם ינואר, פברואר.....ועד דצמבר (ראו תמונה מס' 1).
כל גיליון מכיל טבלה זהה במבנה העמודות אך עם נתונים שונים עבור שעות עבודה של כל עובד באותו החודש בפרוייקט (ראו תמונה מס' 2).
בנוסף יש גיליון מרכז אליו אנו מעוניינים למשוך את הנתונים המתאימים לכל אחד מהעובדים עבור כל אחד מהחודשים (ראו תמונה מס' 3).
** שמות העמודות בטבלה (החודשים) צריכים להיות זהים ממש לשמות הגיליונות (שגם הם חודשים)
המטרה היא לעשות זאת ע"י הקלדת נוסחה אחת בלבד ולגרור אותה על פני הטבלה כולה.
כדי לבצע את הפעולה אנו צריכים שטווח החיפוש (range lookup) בפונקציית ה Vlookup שלנו יהיה דינאמי כך שנוכל לשלוח את פונקציית Vlookup לחפש את הערכים בגיליונות שונים לפי החודש הרצוי.
כדי לעשות זאת אנו צריכים להשתמש בפונקציית Indirect
תמונה מס' 1
תמונה מס' 2
תמונה מס' 3
פונקציית Indirect
לקריאה נוספת בדף התמיכה של Microsoft בנושא פונקציית Indirect
פונקציה זו מפנה לטווח המצויין כטקסט. כלומר היא יודעת לתרגם טקסט המייצג טווח מסויים ולהפנות את החישוב לטווח המצויין.
נניח שיש לנו את הטבלה הבאה (תמונה מס' 4):
תמונה מס' 4
לצורך הדוגמה נניח שאנו מעוניינים לסכום את הערכים בטווח התאים B2:B4
לצורך הדוגמה אנו מעוניינים לרשום בתא C2 את הטווח הרצוי ולקבל את הסכום של הטווח. כלומר אנו רוצים שהערך בתא C2 יהיה "B2:B4" (תמונה מס' 5)
תמונה מס' 5
במצב הזה במידה ונקליד (sum(C2 נקבל חזרה טעות מפני שתא C2 מכיל טקסט.
כאן נכנסת לתמונה פונקציית Indirect
הפונקציה תפנה לטווח הרשום בתא C2 ולא תבצע את החישוב על הערך בתא C2 שזה הבדל חשוב.
יש להקליד את הנוסחה הבאה:
Sum(Indirect(C2))
תמונה מס' 6
מפני שתא C2 מכיל את הטקסט "B2:B4" ופונקציית Indirect תפנה לטווח זה יוצא ש:
Sum(Indirect(C2)) = Sum(B2:B4)
כלומר יש זהות בין שתי הנסחאות לעיל.
כמובן שבלשב זה ניתן להקליד בתא C2 כל טווח נתונים אותו אנו מעוניינם לסכום. לדוגמה נשנה מ "B2:B4" ל "B2:B8"
שילוב של פונקציית Vlookup ופונקציית Indirect
אז עכשיו כשאנו מבינים אך עובדת פונקציית Indirect אנו יכולים לבנות נוסחה בה ה Range lookup ישתנה באופן דינאמי.
זכרו ששמות העמודות בטבלה (החודשים) צריכים להיות זהים ממש לשמות הגיליונות (שגם הם חודשים)
להלן כמה מאפיינים של מבנה הנתונים שלנו שצריך לזכור:
- כל הטבלאות בכל הגיליונות מהם נמשוך נתונים ממוקמות באותן עמודות
- כל הטבלאות בעלות מספר עמודות זהה
- מספר השורות יכול להשתנות בין הטבלאות
מפני שמספר השורות יכול להשתנות מטבלה לטבלה ההפנייה שלנו תהיה לטווח "A:D" וזאת כדי שתמיד נתפוס את כל הנתונים.
כאשר אנו מפנים נוסחה או פונקצייה לגיליון אחר באותה חוברת עבודה ההפנייה נרשמת באופן הבא:
שם הגיליון אחריו סימן קריאה (!) ואחריו הטווח הנבחר בגיליון עצמו.
נניח ונפנה לגליון בשם ינואר לטווח a:d ההפניה תראה כך:
ינואר!A:D
בניית ה- Range Lookup אותו נזין בפונקציית Vlookup:
1. הטווח כולו מוקלד בתוך פונקציית Indirect
2. שם החודש מופיע בראש כל עמודה ולכן החלק הראשן יהיה תא הכותרת של העמודה הראשונה מקובע על פני השורות ובדוגמה זו D$1 (ראו תמונה מס' 7)
3. יש להוסיף לשם (ע"י שימוש בסימן &) את סימן הקריאה ! ואת הטווח בכל גיליון שאנו יודעים כבר שבדגמה זו הוא טווח קבוע "A:D" ונקבל ← "D$1&"!A:D
יש לזכור שכתובת זו נכנסת לתוך פונקציית Indirect:
(Indirect("D$1&"!A:D
מפני שתא D1 מכיל את הערך "ינואר" יוצא שנוסחה זו מחזירה לנו את ההפנייה ל
ינואר!A:D
שזה בדיוק מה שאנחנו צריכים.
כאשר נגרור את הנוסחה לעמודה הבאה ו D1 ישתנה ל E1 (המכיל את הערך פברואר) נקבל:
פברואר!A:D
שזה למעשה אותו הטווח רק בגיליון אחר! הנוסחה ידעה לעבור גיליון עקב היכול שלה לקורא הפניה שרשומה קטקסט בכותרת העמודה.
תמונה מס' 7
היכרות עם נוסחה אחת יכולה לחסוך שעות עבודה רבות.
אז אם אתם רוצים וצריכים להשתמש בצורה מיטבית באקסל, צרו עמנו קשר.