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