إنشاء قائمة منسدلة ديناميكية تابعة في Excel (خطوة بخطوة)
هنا في هذا البرنامج التعليمي ، سنقدم خطوة بخطوة كيفية إنشاء قائمة منسدلة تابعة تعرض الخيارات بناءً على القيم المحددة في القائمة المنسدلة الأولى. بمعنى آخر ، سننشئ قائمة تحقق من صحة بيانات Excel بناءً على قيمة قائمة أخرى.
قم بعمل قائمة منسدلة ديناميكية تابعة
10 ثوانٍ لعمل قائمة منسدلة تابعة بأداة يدوية
أنشئ قائمة منسدلة ديناميكية تابعة في Excel 2021 أو Excel 365
بعض الأسئلة التي قد تطرحها حول هذا البرنامج التعليمي
فيديو: إنشاء قائمة منسدلة تابعة لبرنامج Excel
قم بعمل قائمة منسدلة ديناميكية تابعة
الخطوة 1: اكتب إدخالات القوائم المنسدلة
1. أولاً ، اكتب الإدخالات التي تريد ظهورها في القوائم المنسدلة ، كل قائمة في عمود على حدة.
إشعار أن تكون العناصر الموجودة في العمود الأول (المنتج) كأسماء Excel للقوائم التابعة لاحقًا. على سبيل المثال ، هنا ستكون الفاكهة والخضروات هي أسماء العمود B2: B5 و C2: C6 بشكل منفصل.
انظر لقطة الشاشة:
2. ثم قم بإنشاء جداول لكل قائمة بيانات.
حدد نطاق العمود A1: A3 ، انقر فوق إدراج > طاولات ومكاتب ، ثم في مربع الحوار إنشاء جدول ، حدد الجدول يحتوي على رؤوس خانة الاختيار. انقر OK.
ثم كرر هذه الخطوة لإنشاء جداول للقائمتين الأخريين.
يمكنك عرض جميع الجداول والإشارة إلى النطاقات في Name Manager (اضغط على CTRL + F3 لفتحه).
الخطوة 2: إنشاء أسماء النطاقات
في هذه الخطوة ، تحتاج إلى إنشاء أسماء للقائمة الرئيسية وكل قائمة تابعة.
1. حدد العناصر التي تظهر في القائمة الرئيسية (A2: A3).
2. ثم انتقل إلى مربع الاسم التي بجانبها شريط الصيغة.
3. اكتب الاسم فيه ، هنا تسميه كـ منتج .
4. صحافة أدخل مفتاح لإكمال.
ثم كرر الخطوات المذكورة أعلاه لإنشاء أسماء منفصلة لكل قائمة تابعة.
هنا يسمي العمود الثاني (B2: B5) بالفاكهة ، والعمود الثالث (C2: C6) بالخضروات.
يمكنك عرض جميع أسماء النطاقات في مدير الاسم (اضغط على CTRL + F3 لفتحه).
الخطوة 3: أضف القائمة المنسدلة الرئيسية
بعد ذلك ، أضف القائمة المنسدلة الرئيسية (المنتج) ، وهي قائمة منسدلة عادية للتحقق من صحة البيانات ، وليست قائمة منسدلة تابعة.
1. أولاً ، قم بإنشاء جدول.
حدد خلية (E1) ، واكتب رأس العمود الأول (منتج ) ، وانتقل إلى خلية العمود التالية (F1) ، اكتب رأس العمود الثاني (العناصر ). سوف تضيف القائمة المنسدلة إلى هذا الجدول.
ثم حدد هذين العنوانين (E1 و F1)، انقر إدراج ثم حدد طاولات ومكاتب في مجموعة الجداول.
في مربع الحوار "إنشاء جدول" ، حدد الجدول يحتوي على رؤوس مربع، وانقر فوق OK.
2. حدد الخلية E2 التي تريد إدراج القائمة المنسدلة الرئيسية فيها ، انقر فوق البيانات علامة التبويب وانتقل إلى أدوات البيانات مجموعة للنقر التحقق من صحة البيانات > التحقق من صحة البيانات.
3. في مربع حوار التحقق من صحة البيانات ،
- اختار قائمة في ال السماح الجزء،
- اكتب الصيغة أدناه في مصدر شريط المنتج هو اسم القائمة الرئيسية ،
- انقر OK.
=Product
يمكنك أن ترى تم إنشاء القائمة المنسدلة الرئيسية.
الخطوة 4: إضافة قائمة منسدلة تابعة
1. حدد الخلية F2 التي تريد إضافة القائمة المنسدلة التابعة لها ، انقر فوق البيانات ، وانتقل إلى مجموعة أدوات البيانات للنقر التحقق من صحة البيانات > التحقق من صحة البيانات.
2. في مربع حوار التحقق من صحة البيانات ،
- اختار قائمة في ال السماح الجزء،
- اكتب الصيغة أدناه في مصدر شريط ، E2 هي الخلية التي تحتوي على القائمة المنسدلة الرئيسية.
- انقر OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))
إذا كان E2 فارغًا (لم تحدد أي عنصر واحد في القائمة المنسدلة الرئيسية) ، فسترى رسالة منبثقة على النحو التالي ، انقر فوق نعم للمتابعة.
الآن تم عمل القائمة المنسدلة التابعة.
الخطوة 5: اختبر القائمة المنسدلة التابعة.
1. حدد فاكهة في القائمة المنسدلة الرئيسية (E2) ، ثم انتقل إلى القائمة المنسدلة التابعة (F2) للنقر فوق رمز السهم ، ومعرفة ما إذا كانت عناصر الفاكهة موجودة في القائمة ، ثم حدد عنصرًا واحدًا من القائمة المنسدلة التابعة.
2. صحافة علامة التبويب مفتاح لبدء صف جديد في جدول إدخال البيانات ، حدد نباتي، وانتقل إلى الخلية التالية على اليمين ، ولاحظ ما إذا كانت العناصر النباتية موجودة في القائمة ، ثم حدد عنصرًا واحدًا من القائمة المنسدلة التابعة.
- إذا لم يتم تحديد أي عنصر في القائمة المنسدلة الرئيسية (عمود المنتج) ، فلن تعمل القائمة المنسدلة التابعة (عمود العنصر).
- إذا كنت ترغب في إعادة تعيين أو مسح محتويات القائمة المنسدلة التابعة بعد تحديد التغيير ، فالرجاء الانتقال إلى هذه المقالة كيفية مسح خلية القائمة المنسدلة التابعة بعد تحديد التغيير في إكسيل؟، فهو يقدم رمز VBA لمساعدتك.
- إذا كنت تريد إنشاء قائمة منسدلة من 3 مستويات ، فهذه المقالة كيفية إنشاء قائمة منسدلة تعتمد على مستويات متعددة في Excel؟ سوف يساعدك.
10 ثوانٍ لعمل قائمة منسدلة تابعة بأداة يدوية
كوتولس ل إكسيل يوفر أداة قوية لجعل القائمة المنسدلة التابعة أسهل وأسرع ، دعنا نرى:
قبل اتباع الخطوات أدناه ، من فضلك انقر لتنزيل Kutools for Excel لفترة تجريبية مجانية مدتها 30 يومًا أولا.
الخطوة 1: اكتب إدخالات القائمة المنسدلة
أولاً ، قم بترتيب بياناتك كما هو موضح أدناه:
الخطوة 2: تطبيق أداة Kutools
1. حدد البيانات التي قمت بإنشائها ، انقر فوق كوتولس علامة التبويب وانقر فوق قائمة منسدلة لعرض القائمة الفرعية ، انقر فوق القائمة المنسدلة الديناميكية.
2. في قائمة المعالين المنسدلة
- افحص ال الوضع ب التي تطابق وضع البيانات الخاصة بك ،
- إختار ال مجموعة الانتاج، يجب أن يكون عمود نطاق الإخراج مساويًا لعمود نطاق البيانات ،
- انقر Ok.
الآن تم إنشاء القائمة المنسدلة التابعة.
- يدعم الوضع B إنشاء قائمة منسدلة ثالثة أو أكثر من المستويات:
- إذا تم ترتيب بياناتك كما تظهر لقطة الشاشة أدناه ، فأنت بحاجة إلى استخدام الوضع A ، يدعم الوضع A فقط لإنشاء قائمة منسدلة تعتمد على مستويين.
- مزيد من التفاصيل حول كيفية استخدام Kutools لإنشاء قائمة منسدلة تابعة ، يرجى زيارة هذا البرنامج التعليمي .
أنشئ قائمة منسدلة ديناميكية تابعة في Excel 2021 أو Excel 365
إذا كنت في Excel 2021 أو Excel 365 ، فهناك طريقة أخرى يمكنك من خلالها إنشاء قائمة منسدلة ديناميكية تابعة بسرعة باستخدام وظائف جديدة فريد و منقي.
لنفترض أن بيانات المصدر مرتبة كما هو موضح في لقطة الشاشة ، يرجى اتباع الخطوات التالية لإنشاء القائمة المنسدلة الديناميكية.
الخطوة 1: استخدام الصيغة للحصول على عناصر للقائمة المنسدلة الرئيسية
حدد خلية ، على سبيل المثال ، الخلية G3 ، واستخدام الدالتين UNIQUE و FILTER لاستخراج القيم الفريدة من منتج القائمة التي ستكون مصدر القائمة المنسدلة الرئيسية ، ثم اضغط على أدخل الرئيسية.
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
الخطوة 2: قم بإنشاء القائمة المنسدلة الرئيسية
1. حدد الخلية التي تريد وضعها في القائمة المنسدلة الرئيسية ، على سبيل المثال ، الخلية D3، اضغط هنا البيانات علامة التبويب ، وانتقل إلى أدوات البيانات مجموعة للنقر التحقق من صحة البيانات > التحقق من صحة البيانات.
2. في مربع حوار التحقق من صحة البيانات ،
- اختار قائمة في ال السماح الجزء،
- اكتب الصيغة أدناه في مصدر بار،
- انقر OK.
=$G$3#
الآن يتم إنشاء القائمة المنسدلة الرئيسية.
الخطوة 3: استخدام الصيغة للحصول على عناصر من القائمة المنسدلة التابعة
حدد خلية ، على سبيل المثال ، الخلية H3 ، باستخدام وظيفة FILTER لتصفية العناصر بناءً على القيمة الموجودة في الخلية D3 (العنصر المحدد في القائمة المنسدلة الرئيسية) ، اضغط على أدخل الرئيسية.
=FILTER(B3:B20, A3:A20=D3)
الخطوة 4: قم بإنشاء القائمة المنسدلة التابعة
1. حدد خلية ستضع القائمة المنسدلة التابعة ، على سبيل المثال ، الخلية E3، اضغط هنا البيانات علامة التبويب ، وانتقل إلى أدوات البيانات مجموعة للنقر التحقق من صحة البيانات > التحقق من صحة البيانات.
2. في مربع حوار التحقق من صحة البيانات ،
- اختار قائمة في ال السماح الجزء،
- اكتب الصيغة أدناه في مصدر بار،
- انقر OK.
=$H$3#
الآن تم إنشاء القائمة المنسدلة التابعة بنجاح.
عند إضافة عناصر جديدة أو إجراء بعض التغييرات في A3: A20 ، سيتم تحديث القائمة المنسدلة تلقائيًا.
فرز القائمة المنسدلة أبجديا
إذا كنت ترغب في ترتيب العناصر في القائمة المنسدلة أبجديًا ، يمكنك استخدام الصيغة أدناه لجدول الإعداد.بالنسبة إلى القائمة المنسدلة الرئيسية (الصيغة الموجودة في الخلية G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))بالنسبة إلى القائمة المنسدلة التابعة (الصيغة الموجودة في الخلية H3):
=SORT(FILTER(B3:B20, A3:A20=D3))الآن يتم ترتيب كلتا القائمتين المنسدلتين أبجديًا من الألف إلى الياء.
للترتيب أبجديًا من Z إلى A ، يرجى استخدام الصيغة أدناه:
بالنسبة إلى القائمة المنسدلة الرئيسية (الصيغة الموجودة في الخلية G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)بالنسبة إلى القائمة المنسدلة التابعة (الصيغة الموجودة في الخلية H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)بعض الأسئلة التي قد تطرحها:
1. لماذا إدراج جدول لكل قائمة بيانات؟
سيساعدك إدراج جدول لقائمة البيانات في التحديث التلقائي للقائمة المنسدلة بناءً على التغييرات في قائمة البيانات. على سبيل المثال ، إضافة "أخرى" في قائمة البيانات الأولى ، ثم ستتم إضافة القائمة المنسدلة الرئيسية تلقائيًا مع "الآخرين".
2. لماذا استخدام جدول لوضع القوائم المنسدلة؟
عند الضغط على مفتاح Tab لإضافة سطر جديد إلى الجدول ، ستتم إضافة القوائم المنسدلة تلقائيًا في السطر الجديد أيضًا.
3. كيف تعمل الوظيفة غير المباشرة؟
غير مباشر تستخدم الدالة لتحويل سلسلة نصية إلى مرجع صالح.
4. كيف تعمل الصيغة غير المباشرة (SUBSTITUTE (E2 & F2، ""، ""))؟
أولا، استبدل وظيفة استبدال النص بنص آخر. تستخدم هنا لإزالة المسافات من الأسماء المدمجة (E2 و F2). ثم غير مباشر تقوم الدالة بتحويل السلسلة النصية (المحتويات المدمجة بواسطة E2 و F2) إلى مرجع صالح.
أفضل أدوات إنتاجية المكتب
عزز مهاراتك في Excel باستخدام Kutools for Excel، واختبر كفاءة لم يسبق لها مثيل. يقدم Kutools for Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية وتوفير الوقت. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير
- تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
- فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
- يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
