הטיפ השבועי: הפיכת VLOOKUP לבת-גרירה באקסל 2010

פונקצית VLOOKUP הנה פונקציה "מרגיזה", שכן ניגוד לשאר הפונקציות – היא אינה נהנית מתכונת הכתובות היחסיות של אקסל (Excel), ולכן אינכם יכולים להעתיק ולהדביק אותה בטווחים גדולים על ידי שימוש בנקודת האחיזה למילוי. מדוע? שכן הארגומנט השלישי הנו קבוע.

התבוננו בדוגמה הבאה:

בתא B7 הוזנה הנוסחה =VLOOKUP($A7,$A$1:$E$4,2,FALSE). גרירתה לטווח C7:E7 לא הניבה את התוצאה המצופה, כי הארגומנט השלישי (איזה שדה להחזיר) הנו קבוע (2), ולכן הפונקציה תמיד מחזירה את השדה השני (שם). ניתן לעדכן שדה זה ידנית. זה לא נורא במסד נתונים בן חמש שדות כמו זה שבדוגמה, אך זה לא יעלה על הדעת במסד נתונים בן 500 שדות.

אבל יש פיתרון! לצורך כך, עליכם להכיר את הפונקציה MATCH. הפונקציה מקבלת שלושה ארגומנטים – איבר לחיפוש, ווקטור (מערך חד-מימדי) בו היא מחפשת את האיבר, ואופן החיפוש (כרגע נסתפק בציון שאם ערך אופן החיפוש הנו 0, מתבצע חיפוש מדויק; למידע מלא על הפונקציה MATCH, עיינו במערכת העזרה), ומחזירה את מיקומו של האיבר בוקטור.

באיור בעמוד הקודם הוזנה בתא D1 הנוסחה =MATCH(C1,$A$1:$A$5,0). הנוסחה מחזירה 1 עבור "שנדרה", כי מיקומה של המחרוזת "שנדרה" בווקטור {שנדרה","ירון","אייל","לודה","משה"} הוא 1, ו-#NA עבור "קליאופטרה", כי "קליאופטרה" אינו איבר בווקטור.

אם נחזור עתה לנוסחה המקורית – =VLOOKUP($A7,$A$1:$E$4,2,FALSE), ונחליף את הארגומנט השלישי בנוסחה MATCH(B$6,$A$1:$E$1) (כלומר, הנוסחה תהייה =VLOOKUP($A7,$A$1:$E$4,MATCH(B$6,$A$1:$E$1),FALSE)), נוכל לגרור את VLOOKUP לטווח C2:E2

מדוע? כי עבור "שם", פונקצית ה-MATCH תחזיר לפונקצית ה-VLOOKUP 2 ("שם" הוא האיבר השני בווקטור המאוחסן בטווח $A$1:$E$1), עבור "משפחה" היא תחזיר 3, וכן הלאה.

אלגנטי, הלא כן?

נכתב על ידי ירון כוכבי, IT Support, קבוצת כלנית

טיפים נוספים ניתן למצוא באתר קבוצת כלנית.
אתם מוזמנים לבקר בדף הפייסבוק של קבוצת כלנית.