كيفية عمل قائمة منسدلة في Excel

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

يوفر Excel العديد من الطرق المختلفة لإنشاء قائمة منسدلة ، بالإضافة إلى أنه يسمح لك بتخصيصها بمرونة. دعونا نحلل هذه الأساليب بمزيد من التفصيل.

عملية إنشاء القائمة

لإنشاء قائمة منبثقة ، انقر فوق عناصر القائمة على طول المسار "البيانات" - "التحقق من صحة البيانات". سيتم فتح مربع حوار حيث تريد العثور على علامة التبويب "المعلمات" والنقر عليها إذا لم يتم فتحها من قبل. يحتوي على الكثير من الإعدادات ، ولكن عنصر "نوع البيانات" مهم بالنسبة لنا. من بين كل المعاني ، "القائمة" هي المعنى الصحيح.

عدد الطرق التي يتم من خلالها إدخال المعلومات في القائمة المنبثقة كبير جدًا.

1. إشارة مستقلة لعناصر القائمة مفصولة بفاصلة منقوطة في حقل "المصدر" الموجود في نفس علامة التبويب في نفس مربع الحوار.
   

   

2. بيان أولي للقيم. يحتوي حقل المصدر على النطاق الذي تتوفر فيه المعلومات المطلوبة.
   

   

3. تحديد نطاق مسمى. طريقة تكرر الطريقة السابقة ، ولكن من الضروري فقط تسمية النطاق بشكل أولي.
   

   

ستؤدي أي من هذه الطرق إلى النتيجة المرجوة. لنلقِ نظرة على طرق إنشاء قوائم منسدلة في مواقف الحياة الواقعية.

بناء على بيانات من القائمة

لنفترض أن لدينا جدولاً يصف أنواع الفاكهة المختلفة.

لإنشاء قائمة في قائمة منسدلة بناءً على مجموعة المعلومات هذه ، عليك القيام بما يلي:

1. حدد الخلية المحجوزة للقائمة المستقبلية.
2. ابحث عن علامة التبويب البيانات على الشريط. هناك نضغط على "التحقق من البيانات".
   

   

3. ابحث عن العنصر "نوع البيانات" وقم بتبديل القيمة إلى "قائمة".
   

   

4. في الحقل الذي يشير إلى خيار "المصدر" ، أدخل النطاق المطلوب. يرجى ملاحظة أنه يجب تحديد المراجع المطلقة بحيث لا تتغير المعلومات عند نسخ القائمة.
   

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

مع تسجيل البيانات اليدوي

في الحالة أعلاه ، تمت كتابة القائمة من خلال إبراز النطاق المطلوب. هذه طريقة مناسبة ، ولكن في بعض الأحيان يكون من الضروري تسجيل البيانات يدويًا. هذا سيجعل من الممكن تجنب ازدواجية المعلومات في المصنف.

لنفترض أننا نواجه مهمة إنشاء قائمة تحتوي على خيارين محتملين: نعم ولا. لإنجاز المهمة ، من الضروري:

1. انقر فوق الخلية للحصول على القائمة.
2. افتح "البيانات" وهناك تجد قسم "فحص البيانات" المألوف لدينا.
   

   

3. مرة أخرى ، حدد نوع "القائمة".
   

   

4. هنا تحتاج إلى إدخال "نعم ؛ لا "كمصدر. نرى أن المعلومات يتم إدخالها يدويًا باستخدام فاصلة منقوطة للتعداد.

بعد النقر فوق "موافق" ، لدينا النتيجة التالية.

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

إنشاء قائمة منسدلة باستخدام وظيفة OFFSET

بالإضافة إلى الطريقة الكلاسيكية ، من الممكن استخدام الوظيفة تصرفلإنشاء قوائم منسدلة.

دعونا نفتح الورقة.

لاستخدام وظيفة القائمة المنسدلة ، عليك القيام بما يلي:

1. حدد خلية الاهتمام حيث تريد وضع القائمة المستقبلية.
2. افتح علامة التبويب "البيانات" ونافذة "التحقق من صحة البيانات" بالتسلسل.
   

   

3. اعد القائمة". يتم ذلك بنفس الطريقة مثل الأمثلة السابقة. أخيرًا ، يتم استخدام الصيغة التالية: = OFFSET (A $ 2 ؛ 0 ؛ 0 ؛ 5). ندخلها حيث يتم تحديد الخلايا التي سيتم استخدامها كوسيطة.

ثم سيقوم البرنامج بإنشاء قائمة بقائمة من الفواكه.

بناء الجملة لهذا هو:

= OFFSET (المرجع ، line_offset ، column_offset ، [الارتفاع] ، [العرض])

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

القائمة المنسدلة في Excel مع استبدال البيانات (+ استخدام وظيفة OFFSET)

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

لإنشاء قائمة ديناميكية مع دعم لإدخال معلومات جديدة ، يجب عليك:

1. حدد خلية الاهتمام.
2. قم بتوسيع علامة التبويب "البيانات" وانقر على "التحقق من صحة البيانات".
3. في النافذة التي تفتح ، حدد عنصر "القائمة" مرة أخرى وحدد الصيغة التالية كمصدر للبيانات: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. انقر فوق موافق.

هذا يحتوي على وظيفة COUNTIF، لتحديد عدد الخلايا المملوءة على الفور (على الرغم من أن لديها عددًا أكبر من الاستخدامات ، إلا أننا نكتبها هنا لغرض معين).

لكي تعمل الصيغة بشكل طبيعي ، من الضروري تتبع ما إذا كانت هناك خلايا فارغة على مسار الصيغة. لا ينبغي أن يكونوا كذلك.

قائمة منسدلة ببيانات من ورقة أخرى أو ملف Excel

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

1. قم بتنشيط الخلية حيث نضع القائمة.
2. نفتح النافذة التي نعرفها بالفعل. في نفس المكان الذي أشرنا فيه سابقًا إلى مصادر نطاقات أخرى ، تتم الإشارة إلى صيغة بالتنسيق = غير مباشر (“[List1.xlsx] Sheet1! $ A $ 1: $ A $ 9”). بطبيعة الحال ، بدلاً من List1 و Sheet1 ، يمكنك إدراج اسمي الكتاب والورقة ، على التوالي. 

انتباه! ورد اسم الملف بين قوسين معقوفين. في هذه الحالة ، لن يتمكن Excel من استخدام الملف المغلق حاليًا كمصدر للمعلومات.

وتجدر الإشارة أيضًا إلى أن اسم الملف نفسه لا يكون منطقيًا إلا إذا كان المستند المطلوب موجودًا في نفس المجلد مثل المجلد الذي سيتم إدراج القائمة فيه. إذا لم يكن كذلك ، فيجب عليك تحديد عنوان هذا المستند بالكامل.

إنشاء قوائم منسدلة تابعة

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

تحتاج إلى اتباع هذه الخطوات لإنشاء قوائم تتأثر نتيجتها بالخيار المحدد في قائمة أخرى.

1. إنشاء القائمة الأولى بأسماء النطاقات.
   

   

2. عند نقطة دخول المصدر ، يتم تمييز المؤشرات المطلوبة واحدة تلو الأخرى.
   

   

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

الآن كل شيء جاهز.

كيفية تحديد قيم متعددة من القائمة المنسدلة؟

في بعض الأحيان لا يمكن إعطاء الأفضلية لقيمة واحدة فقط ، لذلك يجب تحديد أكثر من قيمة. ثم تحتاج إلى إضافة ماكرو إلى رمز الصفحة. باستخدام مجموعة المفاتيح Alt + F11 يفتح محرر Visual Basic. ويتم إدخال الرمز هناك.

تغيير ورقة العمل الفرعية الخاصة (الهدف ByVal كنطاق)

    على خطأ استئناف التالي

    إذا لم يتقاطع (الهدف ، النطاق («2: Е9»)) لا شيء والهدف. الخلايا العدد = 1 ثم

        Application.EnableEvents = خطأ

        إذا كان Len (Target.Offset (0، 1)) = 0 ثم

            الهدف. الإزاحة (0 ، 1) = الهدف

        آخر

            Target.End (xlToRight). الإزاحة (0 ، 1) = الهدف

        إنهاء حالة

        الهدف. المحتويات الواضحة

        Application.EnableEvents = صحيح

    إنهاء حالة

نهاية الفرعية 

لكي تظهر محتويات الخلايا أدناه ، نقوم بإدخال الكود التالي في المحرر.

تغيير ورقة العمل الفرعية الخاصة (الهدف ByVal كنطاق)

    على خطأ استئناف التالي

    إذا لم يتقاطع (الهدف ، النطاق («2: К2»)) لا شيء والهدف. الخلايا العدد = 1 ثم

        Application.EnableEvents = خطأ

        إذا كان Len (Target.Offset (1، 0)) = 0 ثم

            الهدف. الإزاحة (1 ، 0) = الهدف

        آخر

            Target.End (xlDown). الإزاحة (1 ، 0) = الهدف

        إنهاء حالة

        الهدف. المحتويات الواضحة

        Application.EnableEvents = صحيح

    إنهاء حالة

نهاية الفرعية

وأخيرًا ، يتم استخدام هذا الرمز للكتابة في خلية واحدة.

تغيير ورقة العمل الفرعية الخاصة (الهدف ByVal كنطاق)

    على خطأ استئناف التالي

    إذا لم يتقاطع (الهدف ، النطاق («C2: C5»)) لا شيء والهدف. الخلايا. العد = 1 ثم

        Application.EnableEvents = خطأ

        newVal = الهدف

        التطبيق

        oldval = الهدف

        إذا كان Len (oldval) <> 0 و oldval <> newVal ثم

            الهدف = الهدف & «،» & newVal

        آخر

            الهدف = newVal

        إنهاء حالة

        إذا كان Len (newVal) = 0 ثم Target.ClearContents

        Application.EnableEvents = صحيح

    إنهاء حالة

نهاية الفرعية

النطاقات قابلة للتعديل.

كيفية عمل قائمة منسدلة مع البحث؟

في هذه الحالة ، يجب أن تستخدم في البداية نوعًا مختلفًا من القائمة. تفتح علامة التبويب "المطور" ، وبعد ذلك تحتاج إلى النقر أو النقر (إذا كانت الشاشة تعمل باللمس) على عنصر "إدراج" - "ActiveX". يحتوي على صندوق تحرير وسرد. سيُطلب منك رسم هذه القائمة ، وبعد ذلك ستتم إضافتها إلى المستند.

علاوة على ذلك ، يتم تكوينه من خلال الخصائص ، حيث يتم تحديد النطاق في خيار ListFillRange. يتم تكوين الخلية حيث يتم عرض القيمة المعرفة من قبل المستخدم باستخدام خيار LinkedCell. بعد ذلك ، تحتاج فقط إلى كتابة الأحرف الأولى ، حيث سيقترح البرنامج تلقائيًا القيم المحتملة.

قائمة منسدلة مع استبدال تلقائي للبيانات

هناك أيضًا وظيفة يتم استبدال البيانات تلقائيًا بعد إضافتها إلى النطاق. من السهل القيام بذلك:

1. قم بإنشاء مجموعة من الخلايا للقائمة المستقبلية. في حالتنا ، هذه مجموعة من الألوان. نختاره.
   

   

2. بعد ذلك ، يجب تنسيقه كجدول. تحتاج إلى النقر فوق الزر الذي يحمل نفس الاسم وتحديد نمط الجدول.
   

   

   

بعد ذلك ، تحتاج إلى تأكيد هذا النطاق بالضغط على الزر "موافق".

نختار الجدول الناتج ونعطيه اسمًا من خلال حقل الإدخال الموجود أعلى العمود A.

هذا كل شيء ، يوجد جدول ، ويمكن استخدامه كأساس لقائمة منسدلة تحتاج إلى:

1. حدد الخلية حيث توجد القائمة.
2. افتح مربع الحوار التحقق من صحة البيانات.
   

   

3. قمنا بتعيين نوع البيانات على "قائمة" ، وكقيم نعطي اسم الجدول من خلال علامة =.
   

   

   

كل شيء ، الخلية جاهزة ، وتظهر فيها أسماء الألوان كما احتجنا في الأصل. يمكنك الآن إضافة وظائف جديدة ببساطة عن طريق كتابتها في خلية تقع في مستوى أدنى قليلاً بعد آخر وظيفة مباشرة.

هذه هي ميزة الجدول ، وهي أن النطاق يزيد تلقائيًا عند إضافة بيانات جديدة. وفقًا لذلك ، هذه هي الطريقة الأكثر ملاءمة لإضافة قائمة.

كيف تنسخ القائمة المنسدلة؟

للنسخ ، يكفي استخدام مجموعة المفاتيح Ctrl + C و Ctrl + V. لذلك سيتم نسخ القائمة المنسدلة مع التنسيق. لإزالة التنسيق ، تحتاج إلى استخدام لصق خاص (في قائمة السياق ، يظهر هذا الخيار بعد نسخ القائمة) ، حيث يتم تعيين خيار "الشروط على القيم".

حدد جميع الخلايا التي تحتوي على قائمة منسدلة

لإنجاز هذه المهمة ، يجب عليك استخدام وظيفة "تحديد مجموعة من الخلايا" في مجموعة "بحث وتحديد".

بعد ذلك ، سيتم فتح مربع حوار ، حيث يجب عليك تحديد العناصر "الكل" و "هذه نفسها" في قائمة "التحقق من صحة البيانات". يحدد العنصر الأول كل القوائم ، بينما يحدد العنصر الثاني فقط القوائم المشابهة لقوائم معينة.