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

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

باستخدام عمود في جدول Excel المنسق

تخيل أن لديك جدول Excel المنسق المسمى “الدرجات” التي تحتوي على أسماء اللاعبين ، والدول ، والعشرات ، وتحتاج إلى استخراج بعض البيانات الموجزة. على وجه التحديد ، في الخلية H2 ، تريد إنشاء قائمة منسدلة لجميع الدول المدرجة في العمود B ، وفي الخلايا I2 و J2 ، تعرض أسماء اللاعبين ومتوسط ​​الدرجات ، على التوالي ، وفقًا للأمة المختارة.

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

متعلق ب

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

يتفوق على الكتابة في نفس الخيارات 200 مرة مختلفة يدويًا.

ثم ، في حقل السماح لعلامة تبويب الإعدادات ، حدد “القائمة”.

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

من الناحية المثالية ، ستتمكن من الكتابة:

=Scores(Nation)

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

لحسن الحظ ، هناك طريقتان للتغلب على هذا.

الطريقة 1: الرجوع إلى الخلايا في الجدول

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

الآن ، بعد النقر فوق “موافق” ، عند تحديد الخلية H2 ، يظهر زر منسد ، والذي يمكنك النقر عليه لتحديد خيار من المصدر. لاحظ ، أيضًا ، كيف يتم عرض القيم الفريدة فقط في القائمة – بمعنى آخر ، يتعرف Excel على القيم المكررة ، ويعرضها مرة واحدة فقط.

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

الآن ، يمكنك استخدام وظيفة التصفية في الخلية I2 لسرد لاعبي الأمة المختارة:

=FILTER(Scores(Player),Scores(Nation)=H2)

متعلق ب

كيفية استخدام وظيفة المرشح في Microsoft Excel

هناك أكثر من طريقة لتصفية بياناتك.

أخيرًا ، استخدم وظيفة المتوسط ​​في الخلية J2 لعرض متوسط ​​درجة اللاعبين من هذه الأمة:

=AVERAGEIF(Scores(Nation),H2,Scores(Total))

متعلق ب

كيفية استخدام وظائف المتوسط ​​والمتوسط ​​في Excel

كن انتقائيًا حول ما يجب تضمينه في العمليات الحسابية المتوسطة.

الطريقة 2: اسم النطاق

تتضمن الطريقة الثانية لإنشاء قائمة منسدلة من عمود في جدول تنسيق تسمية نطاق المصدر.

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

بعد ذلك ، في حقل المصدر لمربع مربع التحقق من صحة البيانات ، اكتب علامة متساوية (=) ، تليها الاسم الذي قمت بتعيينه للتو إلى النطاق ، واضغط على Enter. في هذا المثال ، تحتاج إلى الكتابة:

=Nation

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

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

متعلق ب

أنا دائما اسم النطاقات في Excel ، ويجب عليك أيضا

ترتيب مصنف Excel الخاص بك.

الآن ، كما هو الحال مع الطريقة 1 ، يمكنك استخدام وظائف المرشح والمتوسط ​​لإكمال استخراج البيانات.

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

لذلك ، للمرشح في الخلية i2 ، هو:

=FILTER(Scores(Player),Nation=H2)

وبالنسبة للمتوسط ​​في الخلية J2 ، هو:

=AVERAGEIF(Nation,H2,Scores(Total))

باستخدام عمود في مجموعة بيانات غير مفيدة

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

بعد تحديد الخلية التي ستحتوي على القائمة المنسدلة والنقر فوق “التحقق من صحة البيانات” في علامة تبويب البيانات على الشريط ، حدد “القائمة” في حقل السماح.

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

في هذه الحالة ، الكتابة:

=INDIRECT("B2:B"&COUNTA(B:B))

يتضمن جميع القيم في العمود B من الخلية B2 إلى الخلية B22 كمصدر.

دعنا نقطع هذه الصيغة المصدر لأسفل لنرى كيف تعمل.

• = غير مباشر (: هذا يخبر Excel أنك تريد تحديد المصدر باستخدام مرجع ديناميكي.
• “B2: B”: على وجه التحديد ، يبدأ المرجع الديناميكي في الخلية B2 ، وينتهي في خلية أخرى في العمود B.
• & Counta (B: B)): هذا يحسب جميع الخلايا في العمود ب غير فارغ ، ويضيف المجموع إلى المرجع. في هذه الحالة ، تحتوي 22 خلية في العمود B على قيم ، لذلك هذا يتحول B2: B إلى B2: B22.

متعلق ب

كيفية استخدام الوظيفة غير المباشرة في Excel

استخدم سلسلة نصية لإنشاء مرجع.

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

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

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

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