רשימה נפתחת שערכיה משתנים בהתאם לבחירת הנתונים

כאשר אנחנו יוצרים רשימה נפתחת באקסל (נתונים > אימות נתונים > רשימה), כל הערכים יופיעו בה, גם אם כבר השתמשנו בחלקם.
אולם, לפעמים עולה דרישה להציג רק ערכים שעדיין לא השתמשנו בהם, או להגביל את מספר הפעמים שבהן ניתן להשתמש בכל ערך.
אם יש לכם אקסל 365, זו מטלה פשוטה יחסית.
במאמר הזה נלמד כיצד ליצור רשימה נפתחת, שמשתנה בהתאם לערכים שכבר נבחרו ממנה.

אז מה יש לנו?

טבלה בשם Names, שמכילה את שמות העובדים:

וטבלת שיבוצים למשמרות, עם רשימה נפתחת לבחירת שמות העובדים מהרשימה:

המטרה

לא לשבץ את אותו העובד לשתי משמרות (פעמיים) באותו היום

במה נשתמש?

• אימות נתונים כדי ליצור את הרשימה הנפתחת
• בפונקציה FILTER לצמצום הרשימה, כך שתכיל תמיד רק את העובדים שעדיין לא שובצו
• בפונקציה COUNTIF כדי לדעת כמה פעמים השתמשנו בכל שם

ואיך עושים את זה?

ראשית, ניצור רשימת עזר, שעליה נבסס את אימות הנתונים.
הרשימה הזו צריכה להכיל רק את השמות שעדיין לא שיבצנו.
מכיוון שקל יותר לכתוב נוסחאות אקסל מבפנים החוצה, כלומר – קודם את החישובים הפנימיים, ורק אחר כך להקיף אותם בחישובים החיצוניים,
נבדוק קודם כל כמה פעמים מופיע כל שם ברשימה, ואת זה נעשה באמצעות הפונקציה COUNTIF:

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

בניית הרשימה שעליה נבסס את אימות הנתונים

אם נבסס את אימות הנתונים על רשימת השמות, לא תהיה מגבלה להזנת אותו השם מספר פעמים. לכן, אנחנו צריכים לבסס אותו על רשימה אחרת, שבה בכל פעם שנשתמש בשם כלשהו, הוא יגרע מהרשימה, ואת זה נעשה באמצעות הפונקציה FILTER:

=FILTER(NameTbl[שמות],COUNTIF(F6:H7,NameTbl[שמות])=0)

לאחר שיצרנו את הרשימה, ניצור בתוך טבלת השיבוצים אימות מותנה, שלוקח את הנתונים מרשימת העזר, ולא מרשימת השמות הראשית:

מה בעצם עשינו כאן?

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

בואו נראה:

בהתחלה מופיעה הרשימה השלמה, משום שאף שם לא נבחר:

וגם בטבלת השיבוצים מופיעים כל השמות:

אבל מה יקרה כשנבחר שם מתוך הרשימה?

ראשית, רשימת העזר תצטמצם:

שנית, הרשימה הנפתחת שמבוססת עליה, לא תכיל את השמות שכבר השתמשנו בהם:

כמובן שניתן להתאים את החישוב בהתאם לצורך.
אם לדוגמה, אנחנו מעוניינים לאפשר הזנה של אותו השם פעמיים לכל היותר, נשנה את התנאי כך שיבדוק האם מספר המופעים של השם קטן מ-2:

=FILTER(Names[שמות],COUNTIF(F6:H7,Names[שמות])< 2)

במקרה הזה, בפעם הראשונה שנבחר שם, הוא עדיין יופיע, אבל לאחר שנבחר אותו שוב, הוא יעלם מרשימת העזר, ולא יופיע גם באימות הנתונים!

מעוניינים לתרגל עם הקובץ? תוכלו להוריד אותו מכאן:

כאן תוכלו ללמוד עוד על הפונקציה FILTER

כאן תוכלו ללמוד כיצד להשתמש בפונקציה FILTER כדי לסנן רשימה על פי שני קריטריונים באותה העמודה
כאן, איך להשתמש בפונקציה FILTER כדי לסנן על פי שני ערכים בעמודות נפרדות, בתנאי 'וגם'
וכאן על פי תנאי 'או'

מעוניינים ללמוד בקורס על פונקציות המערך החדשות?
צרו קשר כאן ואחזור אליכם בהקדם