كيفية استخدام صيغة المتوسط ​​المرجح في إكسيل

متى يتم استخدام المتوسط ​​المرجح

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

هنا مثال. لنفترض أنني أتطلع لشراء منزل جديد، لكنني لست متأكدًا من متوسط ​​القيمة السوقية في الحي الذي أعيش فيه. ميزانيتي هي 350 ألف دولار، لذا ألقي نظرة على الأسعار في خمسة منازل مختلفة:

• 1,000,000 دولار
• 800000 دولار
• 400000 دولار
• 300000 دولار
• 250,000 دولار

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

• 1,000,000 دولار (1)
• 800,000 دولار (2)
• 400,000 دولار (10)
• 300,000 دولار (25)
• 250,000 دولار (15)

يتيح لي استخدام صيغة المتوسط ​​المرجح أن أضع في الاعتبار أنه يتم بيع منزل واحد فقط مقابل مليون دولار، في حين أن 25 ضعفًا من المنازل يتم بيعها بسعر 300000 دولار. باستخدام معادلة المتوسط ​​المرجح، أحصل على متوسط ​​قدره 336,792 دولارًا، وهو مبلغ مناسب تمامًا لغرفة القيادة الخاصة بي.

إنه مثل السحر، أليس كذلك؟ وإليك كيف يعمل.

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

لحساب المتوسط ​​المرجح في Excel، استخدم الدالتين SUMPRODUCT وSUM في الصيغة التالية:

=SUMPRODUCT(X:X,X:X)/SUM(X:X)

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

لا يزال الخلط؟ دعنا نستعرض الخطوات في القسم التالي.

استخدام SUMPRODUCT لحساب المتوسط ​​المرجح في Excel

فيما يلي خطواتي لاستخدام SUMPRODUCT.

1. أقوم بإدخال بياناتي في جدول بيانات ثم أقوم بإضافة عمود يحتوي على وزن كل نقطة بيانات.

2. بعد ذلك، أكتب =SUMPRODUCT لبدء الصيغة وإدخال القيم.

3. وأخيرًا، أنقر فوق “إدخال” للحصول على نتائجي.

إليك ما يحدث تحت الغطاء.

أولاً، تقوم المعادلة بضرب كل درجة بوزنها:

• 86 × 15 = 1290
• 55 × 10 = 550
• 78 × 20 = 1560
• 92 × 15 = 1380
• 64 × 40 = 2560

بعد ذلك، فهو يجمع بين هذه القيم:

• 1290 + 550 + 1560 + 1380 + 2560 = 7340

وأخيرًا، تقسم المعادلة القيمة المجمعة على القيمة الإجمالية لأوزاننا:

• 7340 / (15 + 10 + 20 + 15 + 40) = 73.4

كيفية العثور على المتوسطات المتحركة المرجحة في إكسيل

الاختلاف المفيد في المتوسط ​​المتحرك المرجح هو المتوسط ​​المتحرك المرجح.

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

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

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

للعثور على متوسط ​​متحرك مرجح، فإنك تعطي وزنًا أكبر للقيم بناءً على الوقت.

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

وهنا ما يبدو:

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

• اليوم الأول: 10 (× 100)
• اليوم الثاني: 15 (× 200)
• اليوم الثالث: 15 (× 150)
• اليوم الرابع: 25 (× 300)
• اليوم الخامس: 35 (× 100)

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

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

وهذا يعني أن إجماليات الأيام 2 و3 و4 و5 تنطبق جميعها – فهي تتحول يومًا واحدًا فقط إلى اليسار. وفي الوقت نفسه، حصل يومنا الخامس الجديد على 200 مشاهدة. أوزاننا تقول نفس الشيء؛ كل ما يتغير هو عدد المشاهدات نظرًا لارتباطها بآخر خمسة أيام.

باستخدام بياناتنا الجديدة، يتم حساب المتوسط ​​المتحرك المرجح لدينا على النحو التالي:

• اليوم الأول: 10 (× 200)
• اليوم الثاني: 15 (× 150)
• اليوم الثالث: 15 (× 300)
• اليوم الرابع: 25 (× 100)
• اليوم الخامس: 35 (× 200)

ونتيجة لذلك حصلنا على متوسط ​​جديد وهو: 182.5

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

WMA = (القيمة 1 × (الوزن)) + (القيمة 2 × (الوزن)) + (القيمة 3 × (الوزن)) + (القيمة 4 × (الوزن)) + (القيمة 5 × (الوزن)) / الوزن الإجمالي

أفضل من المتوسط: إتقان عمليات Excel

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

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

ملاحظة المحرر: تم نشر هذا المنشور في الأصل في أبريل 2022 وتم تحديثه من أجل الشمولية.