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

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

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

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

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

בדוגמה שלעיל, השתמשתי בפריטים ב- A2: A6 כדי ליצור תפריט נפתח ב- C3.

לקרוא: להלן מדריך מפורט כיצד ליצור רשימה נפתחת של Excel.

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

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

להלן דוגמה למה אני מתכוון ברשימה נפתחת תלויה ב- Excel:

אתה יכול לראות שהאפשרויות בתפריט נפתח 2 תלויות בבחירה המתבצעת בתפריט הנפתח 1. אם אני בוחר 'פירות' בתפריט הנפתח 1, יוצגו לי שמות הפירות, אך אם אני בוחר ירקות בתפריט הנפתח 1, אז אני מוצגים לי שמות הירקות ב- Drop Down 2.

זה נקרא רשימה נפתחת מותנית או תלויה ב- Excel.

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

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

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

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

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

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

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

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

אפס/נקה את התוכן של הרשימה הנפתחת התלויה באופן אוטומטי

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

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

אתה יכול להשתמש ב- VBA כדי לוודא שתוכן הרשימה הנפתחת התלויה מתאפס בכל פעם שהרשימה הנפתחת הראשית משתנה.

להלן קוד ה- VBA לניקוי התוכן של רשימה נפתחת תלויה:

תת פרטי גליון עבודה_שינוי (יעד ByVal כטווח) על חידוש השגיאה הבא אם Target.Column = 4 ואז אם Target.Validation.Type = 3 ואז Application.EnableEvents = False Target.Offset (0, 1) .ClearContents מסתיים אם סיום אם exitHandler: Application.EnableEvents = Sub Sub Sub Exit True

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

הנה איך לגרום לקוד הזה לעבוד:

• העתק את קוד ה- VBA.
• בחוברת העבודה של Excel שבה יש לך את הרשימה הנפתחת התלויה, עבור לכרטיסייה מפתחים, ובתוך קבוצת 'קוד' לחץ על Visual Basic (תוכל גם להשתמש בקיצור המקשים - ALT + F11).
• בחלון עורך VB, משמאל בחוקר הפרויקטים, תראה את כל שמות גליון העבודה. לחץ פעמיים על הרשימה הנפתחת.
• הדבק את הקוד בחלון הקוד מימין.
• סגור את עורך VB.

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

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

להלן השלבים t0 להדגיש חוסר התאמה ברשימות הנפתחות התלויות:

• בחר את התא בעל הרשימות הנפתחות התלויות.
• עבור לדף הבית -> עיצוב מותנה -> כלל חדש.
• בתיבת הדו -שיח כלל עיצוב חדש, בחר 'השתמש בנוסחה כדי לקבוע אילו תאים לעצב'.
• בשדה הנוסחה, הזן את הנוסחה הבאה: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
• הגדר את הפורמט.
• לחץ על אישור.

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

אולי תאהב גם את מדריכי האקסל הבאים:

• חלץ נתונים על סמך בחירה ברשימה הנפתחת.
• יצירת רשימה נפתחת עם הצעות חיפוש.
• בחר מספר פריטים מתוך רשימה נפתחת.
• צור מספר רשימות נפתחות ללא חזרה.
• חסוך זמן עם טפסי הזנת נתונים ב- Excel.