كيفية حساب المتوسط ​​المرجح في Excel بالنسب المئوية

يمنحك حساب المتوسط ​​المرجح في Excel بالنسب المئوية القدرة على التعامل مع البيانات التي لها درجات متفاوتة من الأهمية.

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

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

دعنا ندخله.

جدول المحتويات

• ما هو المتوسط ​​المرجح؟
  • باستخدام النسب أو الوحدات
• إعداد ورقة عمل Excel الخاصة بك
  • خلايا التنسيق للنسب المئوية
• كيفية حساب المتوسط ​​المرجح في Excel
  • استخدام وظيفة Excel SUMPRODUCT
  • القسمة على الوزن الإجمالي
  • تعليمات لبرنامج Excel
  • كسر الصيغة
• سيناريوهات العالم الحقيقي
  • أنظمة الدرجات
  • تحليل مالي
• 4 الأخطاء الشائعة واستكشاف الأخطاء وإصلاحها
  • 1. البيانات والأوزان المنحرفة
  • 2. لا يمثل النسب المئوية
  • 3. مراجع الخلية غير صحيحة
  • 4. استخدام وظيفة خاطئة
• قولنا النهائي

ما هو المتوسط ​​المرجح؟

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

المتوسط ​​المرجح هو نوع من المتوسط ​​يأخذ في الاعتبار الأهمية النسبية لمجموعة من القيم.

لنقارن هذا بالمتوسط ​​البسيط الذي ربما استخدمته عدة مرات. يعامل المتوسط ​​الحسابي البسيط جميع القيم بالتساوي عند حساب النتيجة.

مع المتوسط ​​المرجح ، ليست كل القيم متساوية في الوزن. يتم تعيين الأوزان للقيم وفقًا لأهميتها أو ملاءمتها.

من الناحية الرياضية ، يتم حساب المتوسط ​​المرجح من خلال:

1. ضرب كل قيمة مع وزنها المقابل
2. جمع المنتجات للحصول على الإجمالي
3. قسمة الإجمالي على مجموع الأوزان

يمكن تمثيل الصيغة الرياضية على النحو التالي:

المتوسط ​​المرجح = (؟ (القيمة × الوزن)) /؟ (الوزن)

يمكنك كتابة الصيغة الرياضية عند العمل باستخدام وحدات الماكرو و VBA.

ومع ذلك ، يستخدم هذا البرنامج التعليمي بعض الوظائف المفيدة في Microsoft Excel لتبسيط العملية الحسابية. ستجد أنها اختصارات سهلة الاستخدام.

باستخدام النسب أو الوحدات

تستخدم بعض العمليات الحسابية الموزونة النسب المئوية ، والبعض الآخر يستخدم الوحدات.

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

إذا كان قلم الرصاص خمسين سنتًا والقلم دولارًا ، فإن الأوزان هي 0.5 و 1.0 على التوالي.

من المرجح أن تستند درجات الكلية إلى النسب المئوية. على سبيل المثال ، قد يتم تعيين 40٪ من الدرجة الإجمالية لامتحان منتصف الفصل الدراسي ، بينما يتم تعيين 60٪ للامتحان النهائي.

تركز هذه المقالة على حساب المتوسطات وعرضها باستخدام النسب المئوية.

إعداد ورقة عمل Excel الخاصة بك

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

1. أدخل قيم البيانات الخاصة بك في عمود واحد (على سبيل المثال ، العمود B).
2. قم بتعيين أوزان كنسبة مئوية في عمود منفصل (على سبيل المثال ، العمود C).
3. تأكد من أن الأوزان تصل إلى 100٪.

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

يقرر المدرب النسبة المئوية للوزن لكل سمة ثم يسجل اللاعب. يعطي المتوسط ​​المرجح صورة أكمل لكيفية قياس اللاعب.

انسخ هذا الجدول إلى قالب Excel الخاص بك:

يتم تمثيل الأوزان أعلاه كنسب مئوية ومجموعها 100٪.

خلايا التنسيق للنسب المئوية

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

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

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

كيفية حساب المتوسط ​​المرجح في Excel

يتضمن حساب المتوسط ​​المرجح سلسلة من الخطوات البسيطة باستخدام الدالتين SUMPRODUCT و SUM.

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

استخدام وظيفة Excel SUMPRODUCT

تُستخدم صيغة Excel SUMPRODUCT لمضاعفة صفيفين أو أكثر من صفائف القيم وإرجاع مجموع منتجاتها.

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

القسمة على الوزن الإجمالي

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

افترض أن الوزن الإجمالي يمثل مجموع الكميات في العمود ب.

باستخدام الدالة SUM ، مثل هذا: SUM (B2: B4) ، ستحصل على الوزن الإجمالي.

يستخدم هذا الرقم للتقسيم إلى نتيجة نتيجة SUMPRODUCT.

تعليمات لبرنامج Excel

اتبع هذه الخطوات في ورقة عمل Excel الخاصة بك :

1. انقر فوق خلية فارغة ، حيث سيتم عرض المتوسط ​​المرجح.
2. أدخل الصيغة التالية: = SUMPRODUCT (B2: B4، C2: C4) / SUM (B2: B4)
3. اضغط دخول'.

بمجرد الانتهاء من هذه الخطوات ، سيعرض Excel متوسط ​​السعر المرجح في الخلية التي حددتها.

إذا كنت تتبع هذا المثال ، فقد ترى رقمًا طويلًا في خلية النتيجة به العديد من المنازل العشرية.

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

كسر الصيغة

فيما يلي تفصيل للصيغة من القسم السابق:

• يحصل SUMPRODUCT (B2: B4، C2: C4) على مجموع كميات المنتجات مضروبة في أسعارها الخاصة
• يحسب SUM (B2: B4) الوزن الإجمالي (مجموع كميات العناصر)
• SUMPRODUCT (B2: B4، C2: C4) / SUM (B2: B4) يقسم مجموع المنتجات على الوزن الإجمالي ، مما يوفر المتوسط ​​المرجح

سيناريوهات العالم الحقيقي

هناك تطبيقان شائعان لهذه الصيغة يتضمنان أنظمة الدرجات والتحليل المالي.

توضح هذه الأمثلة كيفية تطبيق المتوسطات المرجحة في Excel باستخدام النسب المئوية كأوزان.

أنظمة الدرجات

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

على سبيل المثال ، لنفترض تقسيم الدرجات التالي:

• الواجبات: 40٪ (إجمالي 4 ، قيمة كل منها 10٪)
• الاختبارات القصيرة: 20٪ (إجمالي 2 ، قيمة كل منهما 10٪)
• الامتحان النصفي: 20٪
• الامتحان النهائي: 20٪

لمتابعة المثال ، يمكنك نسخ البيانات الموجودة في هذا الجدول إلى جدول البيانات الخاص بك. ضع العمود الأول في الجدول في العمود A.

لحساب المتوسط ​​المرجح في Excel ، يمكنك استخدام الدالة SUMPRODUCT.

بافتراض أن التقديرات موجودة في العمود B (من B2 إلى B9) وأن الأوزان المقابلة المعبر عنها كنسب مئوية موجودة في العمود C (من C2 إلى C9) ، ستبدو الصيغة كما يلي:

= SUMPRODUCT (B2: B9، C2: C9) / SUM (C2: C9)

تحسب صيغة Excel هذه المتوسط ​​المرجح باستخدام التقديرات والنسب المئوية المتوفرة.

تحليل مالي

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

افترض أنك تمتلك الأسهم التالية في محفظتك:

بافتراض أن أسعار الأسهم موجودة في العمود B (من B2 إلى B4) ، والمشاركة في العمود C (من C2 إلى C4) ، والنسب المئوية للاستثمار في العمود D (من D2 إلى D4) ، ستكون صيغة المتوسط ​​المرجح كما يلي:

= SUMPRODUCT (B2: B4، D2: D4) / SUM (D2: D4)

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

تُظهر لقطة الشاشة هذه العملية الحسابية والنتائج:

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

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

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

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

4 الأخطاء الشائعة واستكشاف الأخطاء وإصلاحها

فيما يلي الأخطاء الأربعة الأكثر شيوعًا التي ستواجهها عند حساب المتوسط ​​المرجح في Excel.

1. البيانات والأوزان المنحرفة

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

يجب إدراج البيانات في الأعمدة المجاورة ، بحيث تتوافق كل قيمة مع وزن كل منها:

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

2. لا يمثل النسب المئوية

عند التعامل مع النسب المئوية كأوزان ، تأكد من التعبير عن الأوزان كقيم عشرية. على سبيل المثال ، إذا تم تمثيل الوزن على أنه 20٪ ، يجب أن يكون المكافئ العشري 0.20.

لتحويل نسبة مئوية إلى رقم عشري ، اقسم النسبة المئوية على 100:

= C2 / 100

من خلال احتساب النسب المئوية بشكل صحيح ، سيعطي حساب المتوسط ​​المرجح نتائج دقيقة.

3. مراجع الخلية غير صحيحة

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

تحقق من أن الصيغ تتضمن نطاقات الخلايا المناسبة لكل من القيم والأوزان.

4. استخدام وظيفة خاطئة

من المحتمل أنك استخدمت وظيفة المتوسط ​​العادي في Excel عدة مرات. لا تُستخدم هذه الوظيفة للمعدلات المرجحة.

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

تتيح لك الوظيفة AVERAGEIF تصفية القيم التي لا تفي بمعايير محددة. ومع ذلك ، من الأفضل تعيين نسبة منخفضة للقيم عند حساب المتوسط ​​المرجح.

قولنا النهائي

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

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

= SUMPRODUCT (data_range، weight_range) / SUM (weight_range)

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

يمكن أن يؤدي إتقان هذه التقنية إلى تعزيز قدرات تحليل البيانات ، مما يمكّن المستخدمين من اتخاذ قرارات أكثر استنارة بناءً على بيانات دقيقة وذات صلة.

هل أنت مستعد لبدء بناء بعض مهارات Excel القوية؟ تحقق من