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

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

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

الخطوة 1: إنشاء قائمة منسدلة

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

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

ستشاهد الآن قائمة منسدلة تعرض البيانات التي حددتها عند النقر فوق السهم. كما قمنا بتنسيق خلية “اختر” لإبرازها.

الخطوة 2: إنشاء جدول استرجاع البيانات

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

قم بتمييز عناوين الصفوف في الجدول 1، ثم اضغط على Ctrl+C، واستخدم Ctrl+V لنسخ القائمة التي تريد أن يكون الجدول 2 فيها.

قم بتحديد البيانات الملصقة ثم انقر عليها بزر الماوس الأيمن، ثم مرر الماوس فوق “فرز”، وانقر فوق “فرز من الألف إلى الياء”.

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

اضغط على Enter لرؤية عنوان العمود في الجدول 2، استخدم اختيار القائمة المنسدلة. حاول تغيير اختيار القائمة المنسدلة لرؤية عنوان العمود في الجدول 2 يتغير.

نحتاج الآن إلى استخراج البيانات ذات الصلة من الجدول 1 إلى الجدول 2 باستخدام INDEX وMATCH. هذا هو الجزء الأكثر تعقيدًا من العملية، ولكن دعنا نحلله حتى تتمكن من العمل خلال العملية بسهولة أكبر. إليك الصيغة:

=INDEX(a,MATCH(b,c,0),MATCH(d,e,0))

أين

• أ هي كل البيانات الموجودة في الجدول 1 (باستثناء عناوين الصفوف والأعمدة)،
• ب هي القيمة الأولى التي نبحث عنها في الجدول 2،
• ج هو نطاق القيم في العمود الأول في الجدول 1،
• د هو عنوان العمود في الجدول 2، و
• هـ يمثل عناوين أعمدة البيانات في الجدول 1.

دعونا نرى هذا في العمل.

في الخلية I3، نريد أن نخبر Excel بالبحث في الجدول 1 وسحب قيمة إلى الجدول 2 من خلال الدالة INDEX.

=INDEX

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

=INDEX($B$3:$F$7,

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

=INDEX($B$3:$F$7,MATCH(H3,

بعد ذلك، يحتاج برنامج Excel إلى معرفة ما سيستخدمه كمرجع للبحث في الجدول 1. في حالتنا، تكون البيانات من الخلية A3 إلى الخلية A7 (أسماء اللاعبين)، لذا قم بتمييز هذا النطاق، واضغط على F4، ثم أضف فاصلة. ثم اكتب 0 (صفر) لإخبار Excel بأننا نبحث عن تطابق دقيق، ثم أغلق الأقواس. أضف فاصلة أخرى.

=INDEX($B$3:$F$7,MATCH(H3,$A$3:$A$7,0),

الآن، يجب علينا تكرار عملية MATCH للبيانات التي ستتغير عند تحديد خيار مختلف في القائمة المنسدلة. في حالتنا، هذا هو رقم اللعبة، لذا بعد كتابة MATCH في صيغتنا للمرة الثانية، سنضغط على I2 ونضغط على F4 ونضيف فاصلة.

=INDEX($B$3:$F$7,MATCH(H3,$A$3:$A$7,0),MATCH($I$2,

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

=INDEX($B$3:$F$7,MATCH(H3,$A$3:$A$7,0),MATCH($I$2,$B$2:$F$2,0))

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

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

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

الخطوة 4: إدراج الرسم البياني وتنسيقه

قم بتمييز جميع البيانات في الجدول 2 (بما في ذلك رؤوس الأعمدة والصفوف)، ثم افتح علامة التبويب “إدراج”، واختر الرسم البياني الذي يناسبك.

لقد ذهبنا مع مخطط عمودي بسيط ثنائي الأبعاد.

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

الخطوة 5: أضف خطًا متوسطًا إلى الرسم البياني الخاص بك

لإضافة خط متوسط ​​إلى الرسم البياني، تحتاج إلى إضافة المزيد من البيانات إلى الجدول 2. أضف عنوان عمود آخر إلى الجدول 2، وأطلق عليه اسم متوسط.

الآن، استخدم دالة AVERAGE لالتقاط متوسط ​​البيانات المحددة. في حالتنا، حددنا اللعبة 4، لذا نريد العثور على متوسط ​​التقييم بين جميع اللاعبين الخمسة لهذه اللعبة. للقيام بذلك، اكتب

​​​​​​​=AVERAGE

​​​​​​​​في الخلية الأولى من هذا العمود الجديد، ثم حدد البيانات في العمود السابق. اضغط على F4 لجعل هذا مرجعًا مطلقًا، ثم أغلق الأقواس واضغط على Enter.

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

نريد الآن إضافة البيانات المتوسطة إلى مخططنا.

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

في نافذة تحديد مصدر البيانات، انقر فوق “إضافة”.

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

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

افتح علامة التبويب “كل المخططات”، وانقر فوق “مجموعة” في القائمة الموجودة على اليسار، ثم قم بتغيير سلسلة البيانات المتوسطة إلى “خط”.

انقر فوق “موافق” لرؤية النتيجة، وقم بتغيير خيار القائمة المنسدلة لرؤية الرسم البياني الخاص بك يتغير ديناميكيًا!

يمكنك بعد ذلك تنسيق الخط الخاص بك بحيث يبرز بالطريقة التي تريدها.

الآن بعد أن حققت هذا الرسم البياني الديناميكي المثير للإعجاب، يمكنك الاطلاع على بعض الطرق لجعله يبرز بشكل أكبر.