إكسل: اختر من القائمة. القائمة المنسدلة في خلية ورقة العمل

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

كيف يمكن أن يكون هذا مفيدًا لنا؟

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

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

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

1- أسرع طريقة .

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

2- استخدم القائمة.

دعونا نلقي نظرة على مثال صغير نحتاج فيه إلى إدخال نفس أسماء المنتجات باستمرار في الجدول. اكتب في عمود البيانات التي سنستخدمها (على سبيل المثال، أسماء المنتجات). في مثالنا - في النطاق G2:G7.

حدد خلية جدول (يمكنك الحصول على عدة خلايا في وقت واحد) حيث تريد استخدام الإدخال من قائمة محددة مسبقًا. بعد ذلك، في القائمة الرئيسية، حدد علامة التبويب البيانات - التحقق من الصحة. بعد ذلك، انقر فوق نوع البيانات (السماح) وحدد خيار القائمة. ضع المؤشر في حقل المصدر وأدخل فيه العناوين مع القيم المرجعية للعناصر - في حالتنا G2:G7. يوصى أيضًا باستخدام الروابط المطلقة هنا (اضغط على F4 لتعيينها).

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

يمكنك أيضًا استخدام نطاق مسمى كمصدر.

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

بالإضافة إلى ذلك، يمكن أن يكون المصدر كنطاق عادي من الخلايا والنطاق المسمى موجودين على أوراق أخرى في المصنف الخاص بك.

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

استخدم فاصلة منقوطة، أو فاصلة، أو أي حرف آخر قمت بتعيينه كفاصل عناصر لفصل القيم. (يرى لوحة التحكم - الساعة والمنطقة - التنسيقات - متقدم - الأرقام.)

3- إنشاء عنصر تحكم .

لنقم بإدراج كائن جديد في الورقة - عنصر تحكم "مربع تحرير وسرد" ثم ربطه بالبيانات الموجودة في ورقة Excel. نحن نفعل:

1. افتح علامة التبويب المطور. إذا لم تكن مرئية، فستحتاج في Excel 2007 إلى النقر فوق زر Office - خيارات - إظهار علامة تبويب المطور في مربع الاختيار على الشريط (زر المكتب - خيارات - إظهار علامة تبويب المطور في الشريط) أو في الإصدارات 2010-2013، انقر بزر الماوس الأيمن على الشريط، حدد الأمر تخصيص الشريط وقم بتمكين عرض علامة تبويب المطور باستخدام مربع الاختيار.
2. ابحث عن الرمز المطلوب بين عناصر التحكم (انظر الصورة أدناه).

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

لكننا لسنا بحاجة إلى هذا الرقم، بل إلى الكلمة المقابلة له. نستخدم الدالة INDEX (INDEX باللغة الإنجليزية). يسمح لك بالعثور على إحداها في قائمة القيم وفقًا لرقمها التسلسلي. كوسائط INDEX، حدد نطاق الخلايا (F5:F11) والعنوان مع رقم التسلسل الناتج (F2).

نكتب الصيغة في F3 كما هو موضح في الشكل:

الفهرس (F5:F11،F2)

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

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

4- عنصر ActiveX

نتبع الطريقة السابقة بشكل مشابه، ولكن حدد الرمز الموجود أدناه مباشرةً - من قسم "عناصر ActiveX".

نحدد قائمة بالقيم المقبولة (1). يرجى ملاحظة أنه يمكنك هنا تحديد أعمدة متعددة لعرضها مرة واحدة. ثم نختار العنوان الذي سيتم إدراج الموضع المطلوب فيه من القائمة (2)، ونشير إلى عدد الأعمدة التي ستستخدم كبيانات مصدر (3)، ورقم العمود الذي سيتم الاختيار منه لإدخاله في الورقة (٤). إذا قمت بتحديد العمود رقم 2، فلن يتم إدراج الاسم الأخير، ولكن سيتم إدراج الموضع في A5. يمكنك أيضًا تحديد عدد الأسطر التي سيتم عرضها في القائمة. الافتراضي هو 8. ويمكن تمرير الباقي باستخدام الماوس (5).

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

5 - قائمة الإكمال التلقائي

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

إليك ما قد يبدو عليه الإكمال التلقائي في مثال بسيط:

الطريقة الأولى: أشر إلى مصدر كبير معروف.

أبسط وأسهل خدعة. أولاً، نتبع خوارزمية الإجراءات المعتادة: في القائمة، حدد البيانات - التحقق من الصحة في علامة التبويب. من قائمة نوع البيانات (السماح)، حدد خيار القائمة. ضع المؤشر في حقل المصدر. دعونا نحتفظ بمجموعة بهامش كبير في القائمة: على سبيل المثال، حتى السطر 55، على الرغم من أن لدينا فقط 7 مشغولين. تأكد من تذكر تحديد مربع الاختيار "تجاهل الفارغ...". عندها لن يزعجك "احتياطي" القيم الفارغة.

في الواقع أسهل طريقة، ولكنها ليست مريحة للغاية. بعد كل شيء، قد ينفد المكان المحجوز ...

بالطبع، يمكنك أيضًا تحديد العمود بأكمله كمصدر:

لكن معالجة مثل هذا العدد الكبير من الخلايا يمكن أن تبطئ الحسابات إلى حد ما.

الطريقة الثانية: استخدام نطاق مسمى.

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

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

دعونا نسلط الضوء على قائمة الأسماء المتوفرة لدينا A2:A10. ثم قم بتسميته عن طريق ملء حقل "الاسم" الموجود على يسار شريط الصيغة. لنقم بإنشاء قائمة بالقيم في C2. كمصدر له نشير إلى التعبير

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

ويمكن أيضًا فرز القائمة لتسهيل استخدامها.

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

الطريقة الثالثة. سوف يساعدنا الجدول "الذكي".

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

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

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

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

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

الجدول 1 [العمود 1]

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

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

ستبدو الصيغة الآن كما يلي:

غير مباشر ("الجدول 5 [المنتج]")

Table5 هو الاسم الذي تم تعيينه تلقائيًا إلى "الجدول الذكي". قد يكون الأمر مختلفًا بالنسبة لك. في علامة التبويب تصميم، يمكنك تغيير الاسم القياسي إلى اسمك الخاص (ولكن بدون مسافات!). باستخدامه، يمكننا بعد ذلك الوصول إلى طاولتنا على أي ورقة من الكتاب.

"المنتج" هو اسم العمود الأول والوحيد لدينا، ويتم تعيينه حسب عنوانه.

تأكد أيضًا من وضع التعبير بأكمله بين علامتي اقتباس للإشارة إليه كمتغير نصي.

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

نأمل أن تتمكن الآن من استخدام القوائم لإدخال البيانات المتكررة بشكل متكرر في جدول دون أخطاء.

إليك بعض المعلومات المفيدة لك:

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

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

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

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

سيتم فتح نافذة. في علامة التبويب "خيارات"، في القسم المنسدل "نوع البيانات"، حدد "قائمة".


سيظهر خط في الأسفل للإشارة إلى المصادر.


يمكنك تقديم المعلومات بطرق مختلفة.

أولا دعونا تعيين اسم. للقيام بذلك، قم بإنشاء مثل هذا الجدول على أي ورقة.

حدده وانقر بزر الماوس الأيمن. انقر فوق الأمر "تعيين اسم".

أدخل اسمك في السطر أعلاه.

اتصل بنافذة "التحقق من البيانات" وفي حقل "المصدر"، حدد الاسم بوضع علامة "=" أمامه.


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

لقد تعلمت كيفية إنشاء قائمة منسدلة في خلية Excel. ولكن يمكن القيام بالمزيد.

استبدال بيانات Excel الديناميكية

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

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


تأكد من تحديد المربع أدناه.

سوف تتلقى هذا التصميم.

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

غير مباشر("الجدول1[المدن]")

لمعرفة اسم الجدول، انتقل إلى علامة التبويب "تصميم" وانظر إليه. يمكنك تغيير الاسم إلى أي اسم آخر.


تقوم الدالة INDIRECT بإنشاء مرجع إلى خلية أو نطاق. الآن يرتبط العنصر الخاص بك في الخلية بمصفوفة البيانات.

دعونا نحاول زيادة عدد المدن.


الإجراء العكسي - استبدال البيانات من القائمة المنسدلة إلى جدول Excel - يعمل بكل بساطة. في الخلية التي تريد إدراج القيمة المحددة فيها من الجدول، أدخل الصيغة:

عنوان الخلية

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

كيفية إزالة (حذف) القائمة المنسدلة في إكسيل

افتح نافذة إعدادات القائمة المنسدلة وحدد "أي قيمة" في قسم "نوع البيانات".

سوف يختفي العنصر غير الضروري.

العناصر التابعة

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


في هذه الحالة، قم بتسمية كل عمود. حدد بدون الخلية الأولى (العنوان) وانقر بزر الماوس الأيمن. حدد "الاسم".

سيكون هذا اسم المدينة.


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


ولذلك، سوف نقوم بإعادة تسمية هذه المدن بشرطة سفلية.


نقوم بإنشاء العنصر الأول في الخلية A9 بالطريقة المعتادة.


وفي الثانية نكتب الصيغة:

غير مباشر (A9)

سترى أولاً رسالة خطأ. يوافق.

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

كيفية إعداد القوائم المنسدلة التابعة في Excel باستخدام البحث

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


بالنسبة للقائمة الثانية، تحتاج إلى إدخال الصيغة:

إزاحة($A$1,MATCH($E$6,$A:$A,0)-1,1,COUNTIF($A:$A,$E$6),1)

تقوم MATCH بإرجاع رقم الخلية مع المدينة المحددة في القائمة الأولى (E6) في المنطقة المحددة SA:$A.
يحسب COUNTIF عدد التطابقات في نطاق بالقيمة الموجودة في الخلية المحددة (E6).

لقد حصلنا على قوائم منسدلة مرتبطة في Excel مع شرط المطابقة ونطاق البحث عنها.

تحديد متعدد

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


سيتم فتح نافذة المطور. تحتاج إلى إدراج الخوارزمية التالية فيه.

ورقة عمل فرعية خاصة (ByVal Target As Range) عند حدوث خطأ، استأنف التالي إذا لم يكن Intersect(Target, Range("C2:F2")) لا شيء وTarget.Cells.Count = 1 ثم Application.EnableEvents = False إذا Len(Target.Offset) (1, 0)) = 0 ثم Target.Offset(1, 0) = الهدف الآخر Target.End(xlDown).Offset(1, 0) = نهاية الهدف إذا كان Target.ClearContents Application.EnableEvents = True End إذا End Sub

يرجى ملاحظة أنه في السطر

إذا لم يكن Intersect(Target, Range("E7")) لا شيء وTarget.Cells.Count = 1 إذن

يجب عليك إدخال عنوان الخلية مع القائمة. بالنسبة لنا سيكون E7.

ارجع إلى ورقة عمل Excel وقم بإنشاء قائمة في الخلية E7.

عند تحديده ستظهر القيم أسفله.

سيسمح لك الكود التالي بتجميع القيم في الخلية.

Private Sub Worksheet_Change (ByVal Target As Range) عند حدوث خطأ، استأنف التالي إذا لم يكن Intersect(Target, Range("E7")) لا شيء وTarget.Cells.Count = 1 ثم Application.EnableEvents = False newVal = الهدف Application.Undo oldval = الهدف إذا لين (أولدفال)<>0 وأولدفال<>newVal ثم Target = Target & ""، وnewVal Else Target = newVal End إذا كان Len(newVal) = 0 ثم Target.ClearContents Application.EnableEvents = True End If End Sub

بمجرد تحريك المؤشر إلى خلية أخرى، سترى قائمة بالمدن المحددة. لقراءة هذا المقال.


لقد أوضحنا لك كيفية إضافة قائمة منسدلة وتغييرها في خلية Excel. نأمل أن تساعدك هذه المعلومات.

أتمنى لك يوماً عظيماً!

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

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

الخطوة 1.انتقل إلى علامة التبويب "بيانات"، والذي يقع على اللوحة العلوية، ثم في الكتلة "العمل مع البيانات"حدد أداة التحقق من البيانات (تظهر لقطة الشاشة الرمز الذي تمثله).

الخطوة 2.الآن افتح علامة التبويب الأولى "خيارات"،وتثبيت "قائمة"في قائمة نوع البيانات.

الخطوه 3.الآن في مجال إدخال البيانات "مصدر"يمكنك تحديد القيم التي ستشكل القائمة المنسدلة. يمكنك القيام بذلك بالطرق التالية:

في مذكرة!لكي لا يتم إدخال نطاق من الخلايا يدويًا، فقط انقر بزر الماوس الأيسر لتحديد الخلية الأولى ذات القيمة، ثم ضع نقطتين ثم حدد الخلية الأخيرة ذات القيمة باستخدام الماوس.

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

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

فيديو - إنشاء قوائم منسدلة في برنامج Excel

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

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

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

   

2. بعد ذلك، سيتم فتح قائمة اختيار النمط. لا يلعب الأسلوب أي دور آخر غير المظهر، لذا اختر أي دور يناسب ذوقك.

   

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

   

4. بعد الانتهاء من الإجراءات، سوف تتلقى النوع التالي من النطاق.

   

5. الآن انقر بزر الماوس الأيسر على الخلية التي توجد بها القائمة المنسدلة وانتقل إلى علامة التبويب "بيانات"(تخبرك الطريقة السابقة بكيفية القيام بذلك).

   

6. في مجال الإدخال "مصدر"تحتاج إلى إدخال وظيفة مع بناء الجملة "=غير مباشر("اسم الجدول[الرأس]")". تعرض لقطة الشاشة مثالاً أكثر تحديدًا.

   

إذن القائمة جاهزة. سوف تبدو مثل هذا.

في مذكرة!تتميز هذه الطريقة باختلاف جوهري عن الأولى - حيث يتم استخدام الطريقة الجاهزة كقائمة، وليس مجموعة من القيم. وهذا يعني أن أي تغييرات في الجدول سوف تنعكس في القائمة المنسدلة.

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

إذا قمت بحذف خلية (في هذه الحالة قمنا بحذف "البتولا")، فسينعكس ذلك أيضًا في محتويات القائمة المنسدلة.

في مذكرة!تعاملنا في هذه الطريقة مع ما يسمى بـ “الطاولة الذكية”. إنها قابلة للتوسيع بسهولة، وهذه الخاصية مفيدة للعديد من عمليات المعالجة باستخدام جداول Excel، بما في ذلك إنشاء قائمة منسدلة.

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

لإنشاء قوائم منسدلة تابعة، اتبع هذه الإرشادات خطوة بخطوة:

1. أولاً، تحتاج إلى إنشاء نطاق مسمى. انتقل إلى علامة التبويب "الصيغ"، ثم حدد ""مدير الاسم""و "يخلق".

   

   

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

   

3. باستخدام نفس الأسلوب، قم بإنشاء العديد من النطاقات المسماة بقدر ما توجد تبعيات منطقية تريد إنشاءها. في هذا المثال، هناك نطاقان آخران: "الشجيرات"و "أعشاب".

   

4. افتح علامة التبويب "بيانات"(في الطريقة الأولى يُشار إلى المسار إليها) مع الإشارة إلى أسماء النطاقات المسماة في المصدر، كما هو موضح في لقطة الشاشة.

   

   

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

   

   

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

فيديو - القوائم المنسدلة ذات الصلة: سهلة وسريعة

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

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

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

2. يختار " نوع البيانات" -"قائمة" وحدد نطاق القائمة

3. إذا كنت تريد مطالبة المستخدم بإجراءاته، فانتقل إلى علامة التبويب " الرسالة المراد إدخالها" واملأ عنوان الرسالة ونصها

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

4. يمكنك أيضًا اختياريًا إنشاء رسالة ستظهر عند محاولة إدخال بيانات غير صحيحة

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

5. إذا كانت قائمة القيم موجودة في ورقة أخرى، فلن تتمكن من إنشاء قائمة منسدلة باستخدام الطريقة الموضحة أعلاه (حتى Excel 2010). للقيام بذلك، سوف تحتاج إلى إعطاء القائمة اسما. ويمكن القيام بذلك بعدة طرق. أولاً: حدد القائمة وانقر بزر الماوس الأيمن، في قائمة السياق حدد " تعيين اسم"

بالنسبة لإصدارات Excel الأقل من 2007، تبدو نفس الخطوات كما يلي:

ثانية: يستخدم مدير الاسم(إصدارات Excel أعلاه 2003 - علامة التبويب " الصيغ" - مجموعة " أسماء محددة")، والذي يتم استدعاؤه في أي إصدار من Excel بواسطة اختصار لوحة المفاتيح السيطرة+F3.
مهما كانت الطريقة التي تختارها، في النهاية سيتعين عليك إدخال اسم (لقد قمت بتسمية النطاق بقائمة قائمة) وعنوان النطاق نفسه (في مثالنا هذا هو "2"!$A$1:$A$3)

6. الآن في الخلية التي تحتوي على القائمة المنسدلة، أدخل اسم النطاق في حقل "المصدر".

7. مستعد!

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

وهذا هو، يدويا، من خلال ; (فاصلة منقوطة) أدخل القائمة في الحقل " مصدر"، بالترتيب الذي نريد رؤيته (سيتم عرض القيم المدخلة من اليسار إلى اليمين في الخلية من الأعلى إلى الأسفل).

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

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

سننظر في هذه المقالة في كيفية إنشاء قوائم منسدلة من أنواع مختلفة في جدول Excel.

قم بإنشاء قائمة منسدلة بسيطة

للقيام بذلك، في الخلايا A1:A7 نقوم بإدخال البيانات التي سيتم عرضها في القائمة. الآن دعونا نختار الخلية التي سننشئ فيها قائمة منسدلة - B2.

انتقل إلى علامة التبويب "البيانات" وانقر على زر "التحقق من البيانات".

في علامة التبويب "المعلمات"، في حقل "نوع البيانات"، حدد "القائمة". يمكنك إدخال القيم في حقل المصدر بطرق مختلفة:

1 – أدخل القيم للقائمة يدويا، مفصولة بفواصل منقوطة؛

2 – الإشارة إلى نطاق الخلايا التي تم إدخال بيانات القائمة المنسدلة فيها؛

3 - حدد الخلايا ذات الأسماء، وانقر عليها بزر الماوس الأيمن واختر "تعيين اسم" من القائمة.

حدد الخلية B2 ثم ضع "=" في حقل "المصدر"، ثم اكتب الاسم الذي تم إنشاؤه.

لذلك قمنا بإنشاء قائمة منسدلة بسيطة في برنامج Excel.

إذا كان لديك عنوان عمود، وتحتاج إلى ملء كل صف بالقيم، فحدد ليس خلية واحدة، بل نطاقًا من الخلايا - B2:B9. ثم يمكنك تحديد القيمة المطلوبة في كل خلية من القائمة المنسدلة.

إضافة قيم إلى القائمة المنسدلة - القائمة الديناميكية

في هذه الحالة، سنقوم بإضافة قيم إلى النطاق المطلوب، وستتم إضافتها تلقائيًا إلى القائمة المنسدلة.

حدد نطاق الخلايا - D1:D8، ثم في علامة التبويب "الصفحة الرئيسية"، انقر فوق "تنسيق كجدول" وحدد أي نمط.

قم بتأكيد موقع البيانات وحدد مربع "جدول بالرؤوس".

في الأعلى نكتب عنوان الجدول - "الموظفون" ونملأه بالبيانات.

حدد الخلية التي ستكون بها القائمة المنسدلة وانقر على زر "التحقق من البيانات". في النافذة التالية، في حقل "المصدر"، اكتب ما يلي: =INDIRECT("Table1"). لدي جدول واحد على ورقة، لذلك أكتب "جدول 1"، إذا كان هناك جدول آخر - "جدول 2"، وهكذا.

الآن دعونا نضيف اسم موظف جديد إلى قائمتنا: Ira. لقد ظهرت في القائمة المنسدلة. إذا قمنا بحذف أي اسم من الجدول، فسيتم حذفه أيضًا من القائمة.

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

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

في الورقة 2، حدد خلية واحدة أو نطاقًا من الخلايا، ثم انقر فوق الزر "التحقق من صحة البيانات".

انتقل إلى الورقة 1، ضع المؤشر في حقل "المصدر" وحدد نطاق الخلايا المطلوب.

يمكنك الآن إضافة الأسماء في الورقة 1، وستتم إضافتها إلى القوائم المنسدلة في الورقة 2.

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

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

نسمي الأول "الاسم"، والثاني - "اسم العائلة"، والثالث - "الأب".

لنقم بإنشاء نطاق آخر سيتم فيه كتابة الأسماء المخصصة. دعنا نسميها "الموظفين".

نقوم بإنشاء القائمة المنسدلة الأولى، والتي ستتكون من أسماء النطاقات. حدد الخلية E1 وفي علامة التبويب "البيانات"، حدد "التحقق من صحة البيانات".

في حقل "نوع البيانات"، حدد "قائمة"، وفي حقل المصدر، إما أدخل "=الموظفين" أو حدد نطاقًا من الخلايا التي تم تعيين اسم لها.

تم إنشاء القائمة المنسدلة الأولى. الآن في الخلية F2 سنقوم بإنشاء قائمة ثانية، والتي يجب أن تعتمد على القائمة الأولى. إذا اخترنا "الاسم" في الأول، سيتم عرض قائمة الأسماء في الثاني؛ إذا اخترنا "اسم العائلة"، سيتم عرض قائمة بالألقاب.

حدد الخلية وانقر على زر "التحقق من البيانات". في حقل "نوع البيانات"، حدد "قائمة"، وفي الحقل المصدر، أدخل ما يلي: =INDIRECT($E$1). هنا E1 هي الخلية التي تحتوي على القائمة المنسدلة الأولى.

باستخدام هذا المبدأ، يمكنك إنشاء قوائم منسدلة تابعة.

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

الآن أنت تعرف كيفية إنشاء قائمة منسدلة في Excel.

كيفية إنشاء قائمة منسدلة تتكون من عدة خلايا في وقت واحد (على سبيل المثال، بحيث يكون للاسم تكلفة)

شكرا، كل شيء يعمل بشكل جيد.

القائمة المنسدلة التي تحتوي على قيم من ورقة أخرى لا تعمل، لأن النافذة عند فتح التحقق من البيانات لا تسمح بالعمل مع النوافذ الأخرى، خاصة مع ورقة أخرى!

تسمح لك القائمة المنسدلة التابعة بالقيام بخدعة غالبًا ما يشيد بها مستخدمو قوالب Excel. خدعة تجعل العمل أسهل وأسرع. خدعة ستجعل منحنياتك مريحة وممتعة.

مثال على إنشاء قائمة منسدلة تابعة في خلية Excel

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

قام كل بائع أولاً بتحديد مجموعة منتجات، ثم منتجًا محددًا من هذه المجموعة. يجب أن يتضمن النموذج الاسم الكامل للمجموعة وفهرس المنتج المحدد. نظرًا لأن كتابة هذا يدويًا ستستغرق وقتًا طويلاً (ومزعجًا)، فقد توصلت إلى حل سريع وبسيط جدًا - قائمتان منسدلتان معتمدتان.

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

يريد مستخدم قالب ميزانية المنزل الحصول على نفس النتيجة حيث يحتاج إلى فئة وفئة فرعية من النفقات. مثال على البيانات في الشكل أدناه:

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

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

أعترف أنه في نسخة ميزانية المنزل المقترحة، أقتصر على فئة واحدة فقط، لأن مثل هذا التقسيم للنفقات يكفي بالنسبة لي (يعتبر اسم النفقات / الدخل فئة فرعية). ومع ذلك، إذا كنت بحاجة إلى تقسيمها إلى فئات فرعية، فإن الطريقة التي أصفها أدناه مثالية. استخدمه بحرية!

والنتيجة النهائية تبدو هكذا:

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

ولتحقيق ذلك، نحتاج إلى إنشاء جدول بيانات مختلف قليلاً عما لو كنا نقوم بإنشاء قائمة منسدلة واحدة. يجب أن يبدو الجدول بهذا الشكل (النطاق G2:H15):

جدول بيانات مصدر Excel العامل

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

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

حسنا إذا. الآن، سأصف خطوات إنشاء قائمة منسدلة تابعة واحدة تلو الأخرى.

1. أسماء نطاقات الخلايا

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

دعونا نخصص أسماء للنطاقين. قائمة بجميع الفئات وقائمة العمل للفئات. ستكون هذه النطاقات هي A3:A5 (قائمة الفئات في ورقة العمل الخضراء في الصورة الأولى) وG3:G15 (قائمة الفئات المتكررة في ورقة العمل الأرجوانية).

لتسمية قائمة الفئات:

1. حدد النطاق A3:A5.
2. في مربع الاسم (المربع الموجود على يسار شريط الصيغة)، أدخل الاسم "الفئة".
3. قم بالتأكيد باستخدام مفتاح Enter.

قم بتنفيذ نفس الإجراء لنطاق قائمة عمل الفئة G3:G15، والذي يمكنك تسميته "Work_List". سوف نستخدم هذا النطاق في الصيغة.

2. قم بإنشاء قائمة منسدلة للفئة

سيكون الأمر بسيطًا:

1. حدد الخلية التي تريد وضع القائمة فيها. في حالتي هو A12.
2. من قائمة البيانات، حدد أداة التحقق من صحة البيانات. تظهر نافذة "التحقق من قيم الإدخال".
3. حدد القائمة كنوع البيانات.
4. كمصدر، أدخل: = الفئة (الصورة أدناه).
5. قم بالتأكيد بموافقة.

والنتيجة هي على النحو التالي:

القائمة المنسدلة للفئة.

3. قم بإنشاء قائمة منسدلة تابعة لفئة فرعية

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

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

في مصدر القائمة، أدخل الصيغة التالية:

عرض نافذة "التحقق من قيم الإدخال":

التحقق من صحة قيم الإدخال لفئة فرعية في القائمة المنسدلة التابعة

كما ترون، الحيلة الكاملة للقائمة التابعة هي استخدام الدالة OFFSET. حسنًا ، كل ذلك تقريبًا. تساعدها الدالتان MATCH وCOUNTIF. تتيح لك وظيفة OFFSET تحديد النطاقات ديناميكيًا. أولاً، نحدد الخلية التي يجب أن يبدأ منها تغيير النطاق، وفي الوسائط اللاحقة نحدد حجمها.

في مثالنا، سيتم نقل النطاق عبر عمود الفئة الفرعية في ورقة العمل (G2:H15). سنبدأ بالانتقال من الخلية H2، والتي تعد أيضًا الوسيطة الأولى لوظيفتنا. في الصيغة، تتم كتابة الخلية H2 كمرجع مطلق لأنني أفترض أننا سنستخدم القائمة المنسدلة في العديد من الخلايا.

نظرًا لأنه يتم فرز ورقة العمل حسب الفئة، فإن النطاق الذي يجب أن يكون مصدر القائمة المنسدلة سيبدأ من حيث تظهر الفئة المحددة لأول مرة. على سبيل المثال، بالنسبة لفئة الطعام، نريد عرض النطاق H6:H11، وبالنسبة لفئة النقل - النطاق H12:H15 وما إلى ذلك. لاحظ أننا نتحرك على طول العمود H طوال الوقت، والشيء الوحيد الذي يتغير هو البداية النطاق وارتفاعه (أي عدد العناصر في القائمة).

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

يتم تحديد ارتفاع النطاق بواسطة الدالة COUNTIF. إنها تحسب جميع تكرارات الفئة، أي كلمة التغذية. عدد مرات ظهور هذه الكلمة، سيكون عدد المواضع الموجودة في نطاقنا. عدد المواضع في النطاق هو ارتفاعه. ها هي الوظيفة:

وبطبيعة الحال، تم تضمين كلتا الوظيفتين بالفعل في وظيفة OFFSET الموضحة أعلاه. لاحظ أيضًا أنه في كل من الدالتين MATCH وCOUNTIF، هناك إشارة إلى نطاق يسمى WorkList. كما ذكرت سابقًا، ليس عليك استخدام أسماء النطاقات، يمكنك فقط إدخال $H3:$H15. ومع ذلك، فإن استخدام أسماء النطاقات في الصيغة يجعل قراءتها أبسط وأسهل.

هذا كل شئ:

قم بتنزيل مثال لقائمة منسدلة تابعة في Excel

صيغة واحدة، حسنًا، ليست بهذه البساطة، لكنها تسهل العمل وتحمي من الأخطاء عند إدخال البيانات!