כיצד ליצור רשימה נפתחת ב- Excel (המדריך היחיד שאתה צריך)

רשימה נפתחת היא דרך מצוינת לתת למשתמש אפשרות לבחור מתוך רשימה מוגדרת מראש.

ניתן להשתמש בו בעת הגשת משתמש למלא טופס, או בעת יצירת לוחות מחוונים אינטראקטיביים של Excel.

רשימות נפתחות נפוצות למדי באתרים/אפליקציות והן אינטואיטיביות מאוד למשתמש.

צפה בוידאו - יצירת רשימה נפתחת ב- Excel

במדריך זה תלמד כיצד ליצור רשימה נפתחת ב- Excel (לוקח לזה רק כמה שניות) יחד עם כל הדברים המדהימים שאתה יכול לעשות עם זה.

כיצד ליצור רשימה נפתחת ב- Excel

בחלק זה, תלמד את השלבים המדויקים ליצירת רשימה נפתחת של Excel:

1. שימוש בנתונים מתאים.
2. הזנת נתונים באופן ידני.
3. שימוש בנוסחת OFFSET.

#1 שימוש בנתונים מתאים

נניח שיש לך רשימת פריטים כפי שמוצג להלן:

להלן השלבים ליצירת רשימה נפתחת של Excel:

1. בחר תא שבו ברצונך ליצור את הרשימה הנפתחת.
2. עבור אל נתונים -> כלי נתונים -> אימות נתונים.
3. בתיבת הדו -שיח אימות נתונים, בכרטיסייה הגדרות, בחר רשימה כקריטריונים לאימות.
   • ברגע שאתה בוחר רשימה, שדה המקור מופיע.
4. בשדה המקור, הזן = $ A $ 2: $ A $ 6, או פשוט לחץ בשדה המקור ובחר את התאים באמצעות העכבר ולחץ על אישור. פעולה זו תוסיף רשימה נפתחת בתא C2.
   • ודא כי האפשרות הנפתחת בתא מסומנת (המסומנת כברירת מחדל). אם אפשרות זו אינה מסומנת, התא אינו מציג תפריט נפתח, אולם תוכל להזין ידנית את הערכים ברשימה.

הערה: אם ברצונך ליצור רשימות נפתחות במספר תאים בבת אחת, בחר את כל התאים שבהם ברצונך ליצור אותו ולאחר מכן בצע את השלבים שלעיל. ודא שהפניות לתאים הן מוחלטות (כגון $ A $ 2) ולא יחסיות (כגון A2, או A $ 2 או $ A2).

#2 על ידי הזנת נתונים באופן ידני

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

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

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

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

כל הפריטים שהוזנו בשדה המקור, מופרדים בפסיק, מוצגים בשורות שונות ברשימה הנפתחת.

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

#3 שימוש בנוסחאות Excel

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

ניתן להשתמש בכל נוסחה המחזירה רשימת ערכים ליצירת רשימה נפתחת ב- Excel.

לדוגמה, נניח שיש לך את מערך הנתונים כפי שמוצג להלן:

להלן השלבים ליצירת רשימה נפתחת של Excel באמצעות הפונקציה OFFSET:

• בחר תא שבו ברצונך ליצור את הרשימה הנפתחת (תא C2 בדוגמה זו).
• עבור אל נתונים -> כלי נתונים -> אימות נתונים.
• בתיבת הדו -שיח אימות נתונים, בכרטיסייה הגדרות, בחר רשימה כקריטריונים לאימות.
  • ברגע שאתה בוחר רשימה, שדה המקור מופיע.
• בשדה מקור, הזן את הנוסחה הבאה: = OFFSET ($ A $ 2,0,0,5)
  • וודא כי האפשרות הנפתחת בתוך התא מסומנת.
• לחץ על אישור.

פעולה זו תיצור רשימה נפתחת המפרטת את כל שמות הפירות (כפי שמוצג להלן).

הערה: אם ברצונך ליצור רשימה נפתחת במספר תאים בבת אחת, בחר את כל התאים שבהם ברצונך ליצור אותה ולאחר מכן בצע את השלבים שלעיל. ודא שהפניות לתאים הן מוחלטות (כגון $ A $ 2) ולא יחסיות (כגון A2, או A $ 2 או $ A2).

איך נוסחה זו עובדת ??

במקרה לעיל, השתמשנו בפונקציית OFFSET ליצירת הרשימה הנפתחת. הוא מחזיר רשימה של פריטים מהרא

הוא מחזיר רשימה של פריטים מהטווח A2: A6.

להלן התחביר של הפונקציה OFFSET: = OFFSET (הפניה, שורות, עמודים, [גובה], [רוחב])

זה דורש חמישה ארגומנטים, כאשר ציינו את ההפניה כ- A2 (נקודת ההתחלה של הרשימה). שורות/עמודים מוגדרים כ- 0 מכיוון שאיננו רוצים לקזז את תא ההתייחסות. הגובה מוגדר כ -5 מכיוון שיש חמישה אלמנטים ברשימה.

כעת, כאשר אתה משתמש בנוסחה זו, הוא מחזיר מערך המכיל את רשימת חמשת הפירות ב- A2: A6. שים לב שאם אתה מזין את הנוסחה בתא, בחר אותה ולחץ על F9, תראה שהיא מחזירה מערך של שמות הפירות.

יצירת רשימה נפתחת דינמית ב- Excel (באמצעות OFFSET)

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

להלן דרך להפוך אותו לדינאמי (ואין זה אלא שיפור קל בנוסחה):

• בחר תא שבו ברצונך ליצור את הרשימה הנפתחת (תא C2 בדוגמה זו).
• עבור אל נתונים -> כלי נתונים -> אימות נתונים.
• בתיבת הדו -שיח אימות נתונים, בכרטיסייה הגדרות, בחר רשימה כקריטריונים לאימות. ברגע שאתה בוחר רשימה, שדה המקור מופיע.
• בשדה המקור, הזן את הנוסחה הבאה: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ""))
• ודא שהאפשרות הנפתחת בתא מסומנת.
• לחץ על אישור.

בנוסחה זו, החלפתי את הארגומנט 5 ב- COUNTIF ($ A $ 2: $ A $ 100, "").

הפונקציה COUNTIF סופרת את התאים הלא ריקים בטווח A2: A100. מכאן שפונקציית OFFSET מתאימה את עצמה כך שתכלול את כל התאים הלא ריקים.

הערה:

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

העתק רשימות נפתחות להדבקה ב- Excel

אתה יכול להעתיק להדביק את התאים עם אימות הנתונים לתאים אחרים, וזה יעתיק גם את אימות הנתונים.

לדוגמה, אם יש לך רשימה נפתחת בתא C2 ואתה רוצה להחיל אותה גם על C3: C6, פשוט העתק את התא C2 והדבק אותו ב- C3: C6. פעולה זו תעתיק את הרשימה הנפתחת ותהפוך אותה לזמינה ב- C3: C6 (יחד עם התפריט הנפתח, היא תעתיק גם את העיצוב).

אם אתה רק רוצה להעתיק את התפריט הנפתח ולא את העיצוב, להלן השלבים:

• העתק את התא עם התפריט הנפתח.
• בחר את התאים שבהם ברצונך להעתיק את התפריט הנפתח.
• עבור לדף הבית -> הדבק -> הדבק מיוחד.
• בתיבת הדו -שיח הדבק מיוחד, בחר אימות באפשרויות הדבק.
• לחץ על אישור.

פעולה זו תעתיק רק את התפריט הנפתח ולא את עיצוב התא המועתק.

זהירות בעת עבודה עם רשימה נפתחת של Excel

עליך להיות זהיר כאשר אתה עובד עם רשימות נפתחות ב- Excel.

כאשר אתה מעתיק תא (שאינו מכיל רשימה נפתחת) על גבי תא המכיל רשימה נפתחת, הרשימה הנפתחת הולכת לאיבוד.

החלק הגרוע ביותר הוא שאקסל לא תציג התראה או הודעה כלשהי להודיע ​​למשתמש כי תפריט נפתח יוחלף.

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

לפעמים, קשה לדעת אילו תאים מכילים את הרשימה הנפתחת.

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

במקום לבדוק ידנית את כל התאים, יש דרך מהירה לבחור את כל התאים שיש בהם רשימות נפתחות (או כל כלל אימות נתונים).

• עבור לדף הבית -> מצא ובחר -> עבור למבצע.
• בתיבת הדו -שיח מעבר למיוחד, בחר אימות נתונים
  • לאימות הנתונים יש שתי אפשרויות: הכל ואותו דבר. כולם היו בוחרים את כל התאים שבהם חל כלל אימות נתונים. אותו הדבר יבחר רק את התאים בעלי אותו כלל אימות נתונים כמו של התא הפעיל.
• לחץ על אישור.

פעולה זו תבחר מיידית את כל התאים שבהם חלה עליו כלל אימות נתונים (זה כולל גם רשימות נפתחות).

עכשיו אתה יכול פשוט לעצב את התאים (לתת גבול או צבע רקע) כך שייראו ויזואלית ולא תעתיק בו תא אחר בטעות.

להלן טכניקה נוספת של ג'ון אקמפורה שתוכל להשתמש בה כדי תמיד להשאיר את סמל החץ הנפתח גלוי. תוכל גם לראות כמה דרכים לעשות זאת בסרטון זה מאת מר אקסל.

יצירת רשימה נפתחת תלויה / מותנית של Excel

להלן סרטון כיצד ליצור רשימה נפתחת תלויה ב- Excel.

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

לפעמים, ייתכן שיש לך יותר מרשימה נפתחת אחת ואתה רוצה שהפריטים המוצגים בתפריט הנפתח השני יהיו תלויים במה שהמשתמש בחר בתפריט הנפתח הראשון.

אלה נקראים רשימות נפתחות תלויות או מותנות.

להלן דוגמה לרשימה נפתחת מותנית/תלויה:

בדוגמה שלמעלה, כאשר הפריטים המופיעים ב'ירידה למטה 2 'תלויים בבחירה שנעשית ב'תפריט נפתח 1'.

כעת נראה כיצד ליצור זאת.

להלן השלבים ליצירת רשימה נפתחת תלויה / מותנית ב- Excel:

• בחר את התא שבו אתה רוצה את הרשימה הנפתחת הראשונה (הראשית).
• עבור אל נתונים -> אימות נתונים. פעולה זו תפתח את תיבת הדו -שיח לאימות הנתונים.
• בתיבת הדו -שיח אימות נתונים, בכרטיסיית ההגדרות, בחר רשימה.
• בשדה מקור, ציין את הטווח המכיל את הפריטים המוצגים ברשימה הנפתחת הראשונה.
• לחץ על אישור. פעולה זו תיצור את התפריט הנפתח 1.
• בחר את מערך הנתונים כולו (A1: B6 בדוגמה זו).
• עבור אל נוסחאות -> שמות מוגדרים -> צור מבחירה (או שאתה יכול להשתמש בקיצור המקשים Control + Shift + F3).
• בתיבת הדו -שיח 'צור שמות מבחירה', סמן את האפשרות בשורה העליונה ובטל את הסימון של כל האחרים. פעולה זו יוצרת 2 טווחי שמות ('פירות' ו'ירקות '). טווח פירות בשם מתייחס לכל הפירות ברשימה וטווח ירקות בשם מתייחס לכל הירקות ברשימה.
• לחץ על אישור.
• בחר את התא שבו אתה רוצה את הרשימה הנפתחת תלויה/מותנית (E3 בדוגמה זו).
• עבור אל נתונים -> אימות נתונים.
• בתיבת הדו -שיח אימות נתונים, בכרטיסיית ההגדרות, ודא שרשימה נבחרה.
• בשדה מקור, הזן את הנוסחה = INDIRECT (D3). כאן, D3 הוא התא המכיל את התפריט הנפתח הראשי.
• לחץ על אישור.

כעת, כאשר אתה בוחר את התפריט הנפתח 1, האפשרויות המופיעות ברשימה הנפתחת 2 יתעדכנו באופן אוטומטי.

הורד את קובץ הדוגמה

איך זה עובד? - הרשימה הנפתחת המותנית (בתא E3) מתייחסת ל = INDIRECT (D3). המשמעות היא שכאשר אתה בוחר 'פירות' בתא D3, הרשימה הנפתחת ב- E3 מתייחסת לטווח בשם 'פירות' (באמצעות הפונקציה INDIRECT) ומכאן מפרטת את כל הפריטים בקטגוריה זו.

הערה חשובה בעת עבודה עם רשימות נפתחות מותנות ב- Excel:

• כאשר ביצעת את הבחירה ולאחר מכן אתה משנה את התפריט הנפתח של האב, התפריט הנפתח התלוי לא ישתנה ולכן יהיה ערך שגוי. לדוגמה, אם תבחר את ארה"ב כמדינה ולאחר מכן תבחר פלורידה כמדינה, ואז תחזור ותשנה את המדינה להודו, המדינה תישאר כפלורידה. להלן הדרכה מצוינת של דברה בנושא ניקוי רשימות נפתחות תלויות (מותנות) ב- Excel בעת שינוי הבחירה.
• אם הקטגוריה הראשית היא יותר ממילה אחת (למשל 'פירות עונתיים' במקום 'פירות'), עליך להשתמש בנוסחה = INDIRECT (SUBSTITUTE (D3, "", "_")), במקום ב- הפונקציה INDIRECT פשוטה המוצגת למעלה. הסיבה לכך היא ש- Excel אינו מאפשר רווחים בטווחים בשם. כך שכאשר אתה יוצר טווח עם יותר ממילה אחת, Excel מוסיף אוטומטית קו -דוק בין המילים. אז הטווח בשם 'פירות העונה' יהיה 'עונת_פירות'. השימוש בפונקציה SUBSTITUTE בתוך הפונקציה INDIRECT מוודא כי רווחים הם הופך לקו תחתון.