כדי ליצור רשימה נפתחת באקסל, בחרו את התאים, עברו ל-נתונים > אימות נתונים, הגדירו את אפשר ל-רשימה, הקלידו את הפריטים ב-מקור מופרדים בפסיקים (North,South,East,West) או בחרו את הטווח שמכיל אותם, ולחצו על אישור. עכשיו כל תא מציג חץ עם האפשרויות האלה, וערכים אחרים נדחים.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Region | Sales | |
| 2 | Ana | North | 120 | North | 360 | |
| 3 | Ben | South | 85 | |||
| 4 | Cara | North | 240 | |||
| 5 | Dan | East | 60 | |||
| 6 | Eve | South | 150 |
F2 מציג 360, הסכום של North. בחרו North ב-B3 ו-F2 גדל ב-85 של Ben. גם ל-E2 יש רשימה נפתחת: בחרו שם South ו-F2 מציג במקום זה את הסכום של South. רשימה לקלט ונוסחה שקוראת אותו הם השימוש הנפוץ ביותר ברשימה נפתחת.
איך יוצרים רשימה נפתחת, צעד אחר צעד
- בחרו את התאים שצריכים לקבל את הרשימה, למשל B2:B6.
- עברו ל-נתונים > אימות נתונים (קבוצת כלי נתונים). ב-Windows רצף המקשים הוא Alt, A, V, V.
- בכרטיסיה הגדרות, הגדירו את אפשר ל-רשימה.
- ב-מקור, או שתקלידו את הפריטים מופרדים בפסיקים,
North,South,East,West, או שתלחצו בתיבה ותבחרו בגיליון את הטווח עם הפריטים, מה שכותב=$F$2:$F$5. - השאירו את רשימה נפתחת בתא מסומן (בלעדיו אין חץ, רק הבדיקה).
- לחצו על אישור.
כדי לפתוח את הרשימה מהמקלדת, בחרו את התא והקישו Alt+חץ למטה (Windows) או Option+חץ למטה (Mac). ב-Excel ל-Microsoft 365, הקלדת האותיות הראשונות בתא מצמצמת את הרשימה לפריטים שמתאימים.
שתי כרטיסיות אופציונליות באותו חלון: הודעת קלט מציגה טיפ כשהתא נבחר, ו-התראת שגיאה קובעת מה קורה כשמישהו מקליד ערך שלא ברשימה. עם הסגנון עצור (ברירת המחדל) הקלט נדחה; עם אזהרה או מידע הוא מותר אחרי הודעה. בטלו את הסימון של הצג התראת שגיאה לאחר הזנת נתונים לא חוקיים כדי לאפשר לאנשים להקליד כל דבר ועדיין להציע את הרשימה.
פריטים מוקלדים מופרדים במפריד הרשימה של ההגדרות האזוריות של המחשב. ברוב האזורים שמשתמשים בפסיק עשרוני (צרפת, גרמניה, ספרד, איטליה), זו נקודה פסיק: Nord;Sud;Est;Ouest.
רשימה נפתחת מטווח של תאים
רשימה שמוקלדת בחלון מוסתרת מהעין וצריך לערוך אותה שם. רשימה בתאים קלה יותר לתחזוקה: שנו תא וכל רשימה נפתחת שמשתמשת בו משתנה. כאן האזורים נמצאים ב-E2:E5, והרשימה הנפתחת ב-B2:B6 משתמשת בטווח הזה כמקור.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Regions | |
| 2 | Ana | North | 120 | North | |
| 3 | Ben | South | 85 | South | |
| 4 | Cara | North | 240 | East | |
| 5 | Dan | East | 60 | West | |
| 6 | Eve | South | 150 |
שנו את E5 מ-West ל-Central, ואז פתחו חץ כלשהו בעמודה B: הרשימה מציעה את Central במקום West. הערכים שכבר נבחרו בעמודה B לא משתנים.
כדי להשתמש בטווח בגיליון אחר, שזו הדרך הרגילה להסתיר רשימות, הקלידו את שם הגיליון במקור: =Lists!$A$2:$A$5. כדי שהרשימה תגדל כשמוסיפים פריט בתחתית, הפכו קודם את הפריטים לטבלה (בחרו אותם, הוספה > טבלה) ואז בחרו את העמודה של הטבלה כמקור: ההפניה מתרחבת יחד עם הטבלה.
רשימה נפתחת דינמית עם UNIQUE
כשהפריטים צריכים לבוא מהנתונים עצמם (כל אזור שמופיע בעמודה, פעם אחת כל אחד), בנו את הרשימה עם נוסחה והפנו את הרשימה הנפתחת לתוצאה. =SORT(UNIQUE(B2:B8)) ב-G2 שופכת את האזורים השונים בסדר אלפביתי.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Pick | Sales | Regions | |
| 2 | Ana | North | 120 | South | 235 | East | |
| 3 | Ben | South | 85 | North | |||
| 4 | Cara | North | 240 | South | |||
| 5 | Dan | East | 60 | West | |||
| 6 | Eve | South | 150 | ||||
| 7 | Fay | West | 95 | ||||
| 8 | Gus | East | 110 |
G2 שופך East, North, South, West, והרשימה הנפתחת ב-E2 מציעה את ארבעתם. שנו את B7 ל-Central ו-Central מופיע גם בשפיכה וגם ברשימה.
באקסל, הגדירו את המקור של הרשימה הנפתחת ל-=$G$2#. ה-# אחרי תא פירושו "כל השפיכה של הנוסחה הזו", ולכן הרשימה תמיד באורך המדויק של התוצאה, בלי שורות ריקות בסוף. הפניה לשפיכה דורשת Excel 365 או 2021; תא המקור יכול להיות בגיליון אחר (=Lists!$A$2#). אם בעמודת הנתונים יש תאים ריקים, UNIQUE מחזירה בשבילם 0; השאירו אותם בחוץ עם =SORT(UNIQUE(FILTER(B2:B100,B2:B100<>""))). העמוד על UNIQUE מכסה את הפונקציה בפירוט.
רשימות נפתחות תלויות
רשימה תלויה משתנה לפי הבחירה בתא אחר: בחרו Fruit ב-A2, ו-B2 מציע רק פירות. ב-Excel 365 וב-2021, נוסחת FILTER בונה את הרשימה השנייה: =FILTER(E2:E8,D2:D8=A2) מחזירה את הפריטים שהקטגוריה שלהם מתאימה ל-A2, והרשימה הנפתחת ב-B2 משתמשת בשפיכה הזו כמקור.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Category | Item | Category | Item | Items | ||
| 2 | Fruit | Pear | Fruit | Apple | Apple | ||
| 3 | Fruit | Pear | Pear | ||||
| 4 | Vegetable | Carrot | Kiwi | ||||
| 5 | Vegetable | Leek | |||||
| 6 | Bakery | Bread | |||||
| 7 | Fruit | Kiwi | |||||
| 8 | Bakery | Bagel |
כש-Fruit ב-A2, G2 שופך את Apple, Pear ו-Kiwi, ואלה האפשרויות ב-B2. בחרו Bakery ב-A2: G2 משתנה ל-Bread ו-Bagel. ב-B2 עדיין כתוב Pear עד שבוחרים שוב, כי רשימה נפתחת אף פעם לא משנה ערך שכבר נמצא בתא. באקסל המקור של B2 הוא =$G$2#.
בגרסאות ישנות של אקסל, הדרך הקלאסית משתמשת ב-INDIRECT ובטווחים בעלי שם:
- שימו את הפריטים של כל קטגוריה בעמודה משלה, עם שם הקטגוריה ככותרת: Fruit בעמודה אחת, Vegetable בעמודה הבאה.
- בחרו כל עמודה של פריטים ותנו לה את שם הקטגוריה שלה בתיבת השם (משמאל לשורת הנוסחאות):
Fruit,Vegetable,Bakery. - תנו ל-A2 רשימה נפתחת עם המקור
Fruit,Vegetable,Bakery. - תנו ל-B2 רשימה נפתחת עם המקור
=INDIRECT(A2). INDIRECT הופכת את הטקסט שב-A2 להפניה לטווח עם השם הזה.
השמות חייבים להתאים בדיוק לטקסט של הקטגוריה ולא יכולים להכיל רווחים (השתמשו ב-Dairy_Products, או ב-=INDIRECT(SUBSTITUTE(A2," ","_")) במקור). עוד על INDIRECT בעמוד על INDIRECT.
חיפוש הערך של הפריט שנבחר
רשימה נפתחת היא לעתים קרובות הקלט של טופס הזמנה או של הצעת מחיר: המשתמש בוחר מוצר וחיפוש ממלא את המחיר שלו.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Product | Price | ||
| 2 | Order | Pear | Apple | $1.20 | ||
| 3 | Pear | $1.50 | ||||
| 4 | Carrot | $0.80 | ||||
| 5 | Bread | $2.40 | ||||
| 6 | Milk | $1.10 |
תורכם: החזירו ב-C2 את המחיר של המוצר שנבחר ב-B2 מתוך הטבלה שב-E:F.
כש-Pear נבחר, התשובה היא $1.50. בחרו מוצר אחר ב-B2 והמחיר מתעדכן. גם =XLOOKUP(B2,E2:E6,F2:F6) עובדת; ראו VLOOKUP בשביל הארגומנטים.
צביעת תא לפי הפריט שנבחר
כדי לצבוע את התא לפי מה שנבחר (ירוק בשביל Done, אדום בשביל Late), הוסיפו כלל של עיצוב מותנה לאותם תאים: בחרו את B2:B6, עברו ל-בית > עיצוב מותנה > כללי סימון תאים > שווה ל, הקלידו Late ובחרו עיצוב. כדי לצבוע את כל השורה, בחרו את A2:B6 והשתמשו ב-כלל חדש > השתמש בנוסחה כדי לקבוע אילו תאים לעצב עם =$B2="Late".
| A | B | |
|---|---|---|
| 1 | Task | Status |
| 2 | Quote | Done |
| 3 | Invoice | Late |
| 4 | Order | Open |
| 5 | Report | Late |
| 6 | Survey | Done |
B3 ו-B5 מודגשים. בחרו Late ב-B4 וגם הוא מודגש; בחרו Done ב-B3 וההדגשה נעלמת. העמוד על עיצוב מותנה מכסה כללים בפירוט.
למה רשימה נפתחת לא עובדת
- רשימה נפתחת בתא אינו מסומן ב-נתונים > אימות נתונים. הרשימה עדיין מגבילה את הקלט, אבל אין חץ.
- החץ מוצג רק בתא שנבחר. שום דבר ברשת לא מסמן את התאים האחרים שיש להם רשימה; כדי למצוא אותם, השתמשו ב-בית > חפש ובחר > אימות נתונים.
- בטווח המקור יש תאים ריקים, ולכן הרשימה מציגה שורות ריקות. בחרו רק את התאים המלאים, או השתמשו במקור שנשפך (
=$G$2#), שאין בו תאים ריקים. - פריטים שהוקלדו עם המפריד הלא נכון:
North;Southבאקסל שמשתמש בפסיקים הופך לפריט אחד בשםNorth;South. - רשימה נפתחת מחזיקה ערך אחד. בחירה של פריט שני מחליפה את הראשון; בחירה של כמה פריטים בתא אחד דורשת מאקרו VBA.
כדי להעתיק רשימה נפתחת לתאים אחרים בלי להעתיק את הערך, העתיקו את התא, ואז השתמשו ב-בית > הדבק > הדבקה מיוחדת > אימות. כדי להסיר רשימה, בחרו את התאים ובחרו נתונים > אימות נתונים > נקה הכל.
שאלות נפוצות
איך יוצרים רשימה נפתחת באקסל?
בחרו את התאים, עברו ל-נתונים > אימות נתונים, הגדירו את "אפשר" ל"רשימה", הקלידו את הפריטים בשדה "מקור" מופרדים בפסיקים (North,South,East) או בחרו את הטווח שמכיל אותם (=$F$2:$F$5), ולחצו על אישור.
איך עורכים רשימה נפתחת באקסל?
בחרו תא עם הרשימה, פתחו את נתונים > אימות נתונים ושנו את תיבת המקור. סמנו החל שינויים אלה על כל התאים האחרים עם אותן הגדרות כדי לעדכן כל עותק. אם המקור הוא טווח, עריכה של התאים בטווח משנה את הרשימה בלי לפתוח את החלון.
איך מסירים רשימה נפתחת באקסל?
בחרו את התאים, עברו ל-נתונים > אימות נתונים ולחצו על נקה הכל, ואז על אישור. הערכים שכבר נבחרו נשארים בתאים; רק החץ וההגבלה נעלמים.
איך יוצרים רשימה נפתחת מגיליון אחר?
הקלידו את ההפניה עם שם הגיליון במקור: =Lists!$A$2:$A$6, או לחצו על הגיליון האחר ובחרו את הטווח בזמן שתיבת המקור פעילה. גם טווח בעל שם (נוסחאות > הגדר שם) עובד: =Regions.
איך יוצרים רשימה נפתחת שמתעדכנת אוטומטית?
הפנו אותה לנוסחה שנשפכת: כתבו =SORT(UNIQUE(FILTER(B2:B100,B2:B100<>""))) בתא עזר כמו H2 והשתמשו ב-=$H$2# כמקור. ערכים חדשים בעמודה B מופיעים אז ברשימה מיד, ו-FILTER משאירה שורות ריקות מחוץ לה. זה דורש Excel 365 או 2021.
