אפשר ליצור רשימות נפתחות (drop down lists) ב-Excel במספר שיטות. במאמר הזה נתאר איך ליצור את הרשימות באמצעות הפונקציונאליות "אימות נתונים" (Data Validation).
אימות נתונים מאפשר לשלוט בערכים המותרים לתא או טווח תאים מסוים בגיליון. לדוגמה, בתא מסוים מאפשרים להכניס שמות של לקוחות שמופיעים ברשימה הנמצאת בטווח מסוים:
רשימת הלקוחות נמצאת בטווח A3:A25; אחרי שבחרנו את התא בו רוצים שתפתח הרשימה מפעילים את האופציה "אימות נתונים" בטאב "נתונים". בוחרים באפשרות "רשימה" ומגדירים את הטווח המכיל את רשימת הלקוחות בחלונית "מקור".
אחרי לחיצה על "אישור" מופיע משולש קטן בפינה השמאלית העליונה של התא. לחיצה על המשולש פותחת את רשימה ומאפשרת לבחור ערך מתוכה
לשיטה הזאת יש שתי מגבלות:
1 – טווח הרשימה חייב להימצא באותו גיליון בו מפעילים את אימות הנתונים
2 – הרשימה אינה דינאמית. כל הוספה של לקוח חדש מחייבת שינוי של הגדרת ה"מקור".
בהמשך נראה איך להתגבר על המגבלות האלה.
כאשר טווח הרשימה לאימות נמצא בגיליון אחר מגיליון האימות, מגדירים את טווח הרשימה כ"שם" (name)
בטאב "נוסחאות" פותחים את מנהל השמות, נותנים שם (חייב להתחיל באות, ללא רווחים בין המילים וללא סימנים מיוחדים) ומגדירים את הטווח.
עכשיו, משתמשים בשם במקום הטווח המפורש בהגדרת המקור
הדרך הכי נוחה להכניס את השם בחלונית המקור היא להשתמש במקש F3 (הדבקת שם)
על מנת להתגבר על המגבלה השנייה נשתמש שוב פעם בשמות. אפשר ליצור טווחים דינמיים באמצעות פונקציות שיוצרות טווחים: OFFSET ו-INDEX. במאמר זה לא נכנס להסבר מדוקדק של הטכניקה ורק נראה איך לבנות את הטווחים.
בניית טווח דינאמי עם OFFSET
פותחים את מנהל השמות (Ctrl+F3), בוחרים באפשרות "חדש", בתיבת שם כותבים שם (רצוי משמעותי, לדוגמה lstCust1) ובתיבת ה"מפנה ל" כותבים את הנוסחה הבאה:
=OFFSET(customers!$A$3,0,0,COUNTA(customers!$A:$A)-1,1)
בסוף התהליך לוחצים על אישור.
OFFSET משתמשת בתא A3 כ"עוגן" ליצירת הטווח. הפונקציה COUNTA() סופרת כמה תאים לא ריקים ישנם בכל העמודה A; מהתוצאה של COUNTA מפחיתים 1 (הכותרת של הרשימה).
כל פעם שמוסיפים או גורעים שם מהרשימה, הטווח מתעדכן באופן אוטומאטי.
בניית טווח דינאמי עם INDEX
OFFSET הינה פונקציה נדיפה (volatile, על פונקציות נדיפות נקדיש מאמר בעתיד) ולכן יש מי שמעדיף לא לעשות שימוש בה. האלטרנטיבה היא להשתמש ב-INDEX:
=customers!$A$3:INDEX(customers!$A:$A,COUNTA(customers!$A:$A)+1)
[...] בפוסט הקודם בנושא ראינו איך ליצור רשימות נפתחות (drop down lists) עם אימות נתונים (Data Validation). [...]
ראשית תשבחות – כל הכבוד !!! אתר מוסבר באפן ברור ועינייני (קצר ולעניין)
בעיה :
בעת הגדרת רשימות תלויות כאשר הרשימות מתיחסות לטווח דינמי ולא סטאטי בשימוש באימות נתונים
ישנה בעיה
לא ניתן לבחור ערך ברשימה התלוייה – הרשימה פשוט לא נפתחת בגלל שברשימה שבא היא תלויה הוגדר טווח דינמי (בעזרת פקודת INDEX)
אשמח לתשובה
חג פורים שמח,
דוד
היי דוד, מכיוון שאתה מדבר על רשימות נפתחות תלויות, אני מניח שאתה משתמש ב-INDIRECT להגדרת הטווח.
הפונקציה INDIRECT עובדת אך ורק עם טקסט, לא עם נוסחאות ולכן הרשימה לא נפתחת.
בקרוב אפרסם פוסט בנושא.
תודה על המחמאות!
שלום,
תודה רבה על פוסטים! מאוד עוזר…
ראיתי איפשהו בפוסט כדרך אגב הערה כיצד מבטלים רשימה, ז"א אם הגדרתי קבוצת פרמטריפ כרשימה – כיצד אני מבטלת אותה?
כי כמו שציינת – הגדרתי רשימה דרך הכי פשוטה, ועכשיו כשאני רוצה להוסיף עד פרמטר – היא לא נותנת. מה אני עושה?
תודה
הי,
אם הכוונה לבטל את הרשימה שיצרת עם אימות נתונים, כל מה שעליך לעשות הוא לסמן את התא (אן תאים) הרלוונטי, ךפתוח את תפריט אימות הנתונים וללחוץ על כפתור "נקה הכל".
לגבי הוספת ערכים לרשימה, אם הטווח קשיח (כגון A$1$:$A$20) צריך לעדכן את הטווח על מנת שיכלול גם את הערך החדש; אם הגדרת את הטווח של הרשימה כ"טבלה", העדכון הוא אוטמטי.
שלום,
ברצוני לדעת כיצד ניתן לשלב ערכים מספריים ברשימות נפתחות.
תודה מראש
היי אסתר, מתנצל על האיחור להגיב. הרשימות הנפתחות יכולות לכלול ערכים מספריים, טקסט וגם שילוב של שניהם. או שהתכוונת למשהו אחר?