القوائم المنسدلة المرتبطة

وصف

في Excel يمكنك بسرعة وسهولة إنشاء قائمة منسدلة ، ولكن هل سبق لك أن حاولت إنشاء قائمة منسدلة تابعة؟ هناك عدة طرق لإنشاء مثل هذه القائمة، لذلك دعونا نلقي نظرة عليها.

الطريقة الأولى: الدالة INDIRECT

تعتمد هذه التقنية على تطبيق الدالة INDIRECT، والتي يمكنها القيام بشيء واحد بسيط - تحويل محتويات أي خلية محددة إلى عنوان نطاق يفهمه Excel. أي إذا كانت الخلية تحتوي على النص " А1 "، فإن الدالة ستعطي مرجعًا للخلية نتيجة لذلك А1 . إذا كانت الخلية تحتوي على كلمة " Auto "، فسوف تقوم الدالة بإخراج مرجع إلى النطاق المسمى بالاسم Auto إلخ.

خذ على سبيل المثال هذه القائمة من موديلات السيارات Toyota, Ford و Nissan :

حدد القائمة الكاملة لنماذج تويوتا (من الخلية А2 وصولاً إلى نهاية القائمة) وقم بتسمية هذا النطاق Toyota في القائمة إدراج - اسم - تعيين (Insert - Name - Define) . ثم سنكرر نفس الشيء مع القوائم Ford و Nissan ، مع تحديد أسماء النطاقات وفقًا لذلك Ford و Nissan .

لنقم الآن بإنشاء القائمة المنسدلة الأولى لاختيار ماركة السيارة. حدد خلية فارغة وافتح القائمة البيانات - التحقق (Data - Validation) ، ثم من القائمة المنسدلة نوع البيانات حدد اختيارا قائمة وفي الميدان مصدر - تسليط الضوء على الخلايا ذات الأسماء التجارية (الخلايا الصفراء في مثالنا). بعد النقر على ОК القائمة المنسدلة الأولى جاهزة:

لنقم الآن بإنشاء قائمة منسدلة ثانية، والتي ستعرض نماذج العلامة التجارية المحددة في القائمة الأولى. تمامًا كما في الحالة السابقة، حدد خلية فارغة وافتح القائمة البيانات - التحقق - إضافي قائمة . في الحقل مصدر سوف تحتاج إلى إدخال الصيغة التالية:

=INDIRECT(F3)

أين:

• F3 - عنوان الخلية التي تحتوي على القائمة المنسدلة الأولى - استبدلها بالقائمة المنسدلة الخاصة بك.

الجميع. بعد النقر على نعم سيتم اختيار محتويات القائمة الثانية حسب اسم النطاق المحدد في القائمة الأولى.

سلبيات هذه الطريقة:

• لا يمكن للنطاقات الديناميكية المحددة بواسطة صيغ نوع OFFSET أن تعمل كنطاقات ثانوية (تابعة). يمكنك استخدامها للقائمة الأساسية (المستقلة)، ولكن يجب تعريف القائمة الثانوية بشكل صارم، بدون صيغ.
• يجب أن تتطابق أسماء النطاقات الثانوية مع عناصر القائمة المنسدلة الأساسية. أي أنه إذا كان يحتوي على نص به فجوات، فيجب استبداله بتسطير، وما إلى ذلك.
• تحتاج إلى إنشاء العديد من النطاقات المسماة يدويًا.

الطريقة الثانية. قائمة المطابقة (OFFSET وMATCH)

تتطلب هذه الطريقة قائمة مرتبة من تطابقات طراز الصنع من النوع التالي:

لإنشاء قائمة منسدلة أساسية للعلامات التجارية، يمكنك استخدام الطريقة المعتادة الموضحة أعلاه، وهي:

• أعط اسم النطاق D1:D3 (على سبيل المثال العلامات التجارية )
• حدد في علامة التبويب بيانات (Data) فريق التحقق من البيانات (Data validation)
• حدد خيار التحقق من القائمة المنسدلة قائمة (List) وتحديد الجودة مصادر (Source) = العلامات التجارية أو ببساطة حدد الخلايا D1:D3 (إذا كانت موجودة في نفس الورقة مثل القائمة).

ولكن بالنسبة لقائمة النماذج التابعة، سيتعين عليك إنشاء نطاق مسمى باستخدام الوظيفة OFFSET، والتي ستشير ديناميكيًا فقط إلى خلايا النماذج الخاصة بعلامة تجارية معينة. لهذا:

• اضغط على Ctrl + F3 أو استخدم الزر مدير الاسم (Name manager) على علامة التبويب الصيغ (Formulas) . في الإصدارات قبل عام 2003، كان هذا أمر قائمة إدراج - اسم - تعيين (Insert - Name - Define)
• قم بإنشاء نطاق مسمى جديد بأي اسم (على سبيل المثال عارضات ازياء ) وفي الميدان وصلة (Reference) في الجزء السفلي من النافذة، أدخل الصيغة التالية يدويًا:

=OFFSET( $A$1 ; MATCH($G$7;$A:$A;0)-1 ; 1 ; COUNTIF($A:$A;$G$7) ; 1 )

الدالة OFFSET قادرة على إصدار مرجع إلى نطاق بالحجم المطلوب، مع إزاحته بالنسبة إلى الخلية الأولية بعدد معين من الصفوف والأعمدة. في نسخة أكثر قابلية للفهم، يكون بناء جملة هذه الوظيفة كما يلي:

=OFFSET( start_cell ; التحول إلى الأسفل ; Shift_right ; range_size_in_rows ; range_size_in_columns )

لذا:

• start_cell - نأخذ الخلية الأولى من قائمتنا، أي أ1 ;
• التحول إلى الأسفل - نحسب الدالة MATCH، والتي، ببساطة، تقوم بإخراج الرقم التسلسلي للخلية ذات العلامة التجارية المحددة (G7) في النطاق المحدد (الأعمدة و );
• Shift_right =1 لذا نريد الرجوع إلى النماذج الموجودة في العمود المجاور ( في );
• range_size_in_rows - نقوم بالحساب باستخدام الدالة COUNTIF، والتي يمكنها حساب عدد التكرارات في القائمة (العمود A) للقيم التي نحتاجها - ماركات السيارات ( G7 );
• range_size_in_columns =1 لذلك نحن بحاجة إلى عمود واحد مع النماذج.

يجب أن تكون النتيجة شيء من هذا القبيل:

يبقى إضافة قائمة منسدلة إلى الخلية بناءً على الصيغة التي تم إنشاؤها G8 . لهذا:

• حدد خلية G8
• حدد في علامة التبويب بيانات (Data) فريق التحقق من البيانات (Data validation) أو في القائمة البيانات - التحقق (Data - Validation)
• حدد خيار التحقق من القائمة المنسدلة قائمة (List) وأدخل باسم مصادر (Source) العلامة تساوي اسم نطاقنا، أي = عارضات ازياء .

مقالات حول الموضوع:

• قائمة منسدلة في الخلية
• تلميح عند ملء البيانات في الخلية