כבר הראינו איך ליצור רשימות נפתחות תלויות ב-Excel באמצעות אימות נתונים והפונקציה INDIRECT.
נניח שתי רשימות של לקוחות: לקוחות שוק מקומי וייצוא. רשימת הלקוחות המקומיים נמצאת בטווח B3:B22; לקוחות ייצוא בטווח D3:D19; בוחרים אתת השוק בתא H2 ובתא H3 יפתח רשימת הלקוחות המתאימה
בתא H2 הגדרנו את אימות הנתונים כך:
ובתא H3 הגדרנו
המגבלה של השיטה היא שהפונקציה INDIRECT מקבלת רק טקסט כארגומנט ולכן לא נוכל להשתמש בשמות (names) הפונים לנוסחאות על מנת ליצור טווחים דינאמיים.
כפי שראינו, אפשר להגדיר את טווחי הלקוחות בצורה דינאמית בעזרת הפונקציות OFFSET או INDEX. למשל, נוכל להגדיר את טווחי הרשימה של לקוחות כך
מקומי =OFFSET(customers!$B$3,0,0,COUNTA(customers!$B:$B)-1,1)
ייצוא =OFFSET(customers!$D$3,0,0,COUNTA(customers!$D:$D)-1,1)
אבל הרשימה התלויה בתא H3 לא תיפתח. כאמור, INDIRECT לא מקבלת נוסחאות כארגומנט אלא אך ורק טקסט המתאר טווח.
יש שתי דרכים להתגבר על המגבלה הזאת:
1 – הדרך הקלה: טבלאות (רק ב-Excel 2007/10 וב-Excel 2003)
2 – הדרך הקשה: נוסחאות (לכל הגרסאות)
פתרון בעזרת טבלאות.
"טבלאות" הוא כלי שהוכנס לראשונה בגרסה 2003 של Excel. את השימוש בטבלאות ליצירת טווחים דינמיים הראינו בפוסט "גרפים דינמיים ב-Excel באמצעות טבלה".
אחרי שהופכים טווח ל"טבלה" אפשר לתת לו שם שנוכל להשתמש בהגדרה של הרשימה הנפתחת באימות נתונים. Excel מעדכנת את הטווח באופן אוטומטי. בסרטון הבא נגדים את התהליך
פתרון בעזרת נוסחאות.
על מנת להתגבר על המגבלה של הפונקציה INDIRECT נשתמש בחישוב ביניים ובטבלת עזר.
הרעיון הוא ליצור טקסט שמתאר את טווחי הלקוחות באופן דינמי. אפשר ליצור טקסט כזה בעזרת הפונקציה CONCATENATE
לטווח לקוחות השוק המקומי נשתמש בנוסחה
=CONCATENATE("B3",":",ADDRESS(COUNTA(B:B)+1,2))
לייצוא
=CONCATENATE("D3",":",ADDRESS(COUNTA(D:D)+1,4))
את התוצאות נארגן בטבלה שבטווח J4:K5 בדוגמה שלנו ובתא K2 נכניס את הנוסחה
=VLOOKUP(H2,$J$4:$K$5,2,0)
בהגדרת הרשימה הנפתחת בתא H3 נשתמש ב:
