בפוסטים קודמים הראנו איך להשתמש ב-VLOOKUP עם מספר טבלאות או עם טבלה מרובת עמודות.
בפוסט זה ננתח מקרה בו הנתונים נמצאים במספר טבלאות, אבל אין אנו יודעים מראש באיזו טבלה לחפש.
נניח שמחזיקים את מספרי העובד של מפעל דמיוני בחוברת Excel, כל מחלקה בגיליון נפרד (שיטת ארגון נתונים לא מומלצת אבל מאוד נפוצה עפ"י ניסיוני)
בגיליון אחר, רוצים להכניס את שם העובד ולקבל את המספר שלו. כמובן, אפשר לבצע את השליפה עם VLOOKUP. הבעיה: איננו יודעים באיזו מחלקה לחפש.
אציג שני פתרונות: עם פונקציות Excel ועם פונקציה מוגדרת ע"י המשתמש (UDF – user defined function)
פונקציות Excel
בשלב הראשון (ולא הכרחי) נגדיר את טווחי הטבלאות בתוך שמות (names)
ב-Excel "קלאסי" (גרסאות 97-2003) נשתמש בשילוב של פונקציות IF, ISERROR ו-VLOOKUP
=IF(ISERROR(VLOOKUP(C3,ייצור,2,0)),IF(ISERROR(VLOOKUP(C3,לוגיסטיקה,2,0)),
VLOOKUP(C3,אחזקה,2,0),VLOOKUP(C3,לוגיסטיקה,2,0)),VLOOKUP(C3,ייצור,2,0))
ב-Excel 2007 החיים שלנו יהיו יותר קלים ונוכל להשתמש ב-IFERROR
=IFERROR(VLOOKUP(C7,ייצור,2,0),IFERROR(VLOOKUP(C7,לוגיסטיקה,2,0),VLOOKUP(C7,אחזקה,2,0)))
שני הפתרונות מוגבלים. ב-Excel "קלאסי" ישנה מגבלה של מספר פונקציות IF שאפשר לשלב בתוך נוסחה (מקסימום של שבע פונקציות). ב-Excel 2007 אפשר לשלב כמות הרבה יותר גדולה של פונקציות IF, אבל הנוסחה נעשית מסורבלת וקשה לבדיקה.
פתרון עם UDF (פונקציה מוגדרת ע"י המשתמש)
במודול רגיל של עורך ה-Vba כותבים את הקוד הבא
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _ Col_num As Integer, Optional Range_look As Boolean) '''''''''''''''''''''''''''''''''''''''''''''''' 'Published by OzGrid.com ''''''''''''''''''''''''''''''''''''''''''''''''' Dim wSheet As Worksheet Dim vFound On Error Resume Next For Each wSheet In ActiveWorkbook.Worksheets With wSheet Set Tble_Array = .Range(Tble_Array.Address) vFound = WorksheetFunction.VLookup _ (Look_Value, Tble_Array, _ Col_num, Range_look) End With If Not IsEmpty(vFound) Then Exit For Next wSheet Set Tble_Array = Nothing VLOOKAllSheets = vFound End Function |
הפונקציה תופיע באשף הפונקציות, תחת הקטגוריה
מכיוון שכמות השורות בכל טבלה אינה ידועה לנו, נשתמש בטווח העמודות B:C כארגומנט ה-table_array
