كيفية حساب المتوسط المرجح في جدول محوري في Excel؟
حساب المتوسط المرجح للبيانات في Excel هو حاجة شائعة، خاصة عندما تساهم نقاط البيانات بشكل غير متساوٍ في النتيجة النهائية. بالنسبة للنطاقات البسيطة، توفر دالّتا SUMPRODUCT وSUM حلاً سريعًا. ومع ذلك، عند العمل مع الجداول المحورية، قد تلاحظ أن الحقول المحسوبة لا تدعم هذه الدوال بشكل أصلي. يمكن أن يؤدي هذا إلى تعقيد الأمور عند الرغبة في حساب المتوسط المرجح مباشرة داخل الجدول المحوري. فهم هذه القيود والتعلم عن أساليب بديلة يمكن أن يساعدك في تلخيص بياناتك بكفاءة في مختلف السيناريوهات. يستكشف هذا المقال طرقًا مختلفة لحساب المتوسط المرجح في الجدول المحوري، مع تغطية الحلول التقليدية والميزات الأحدث المتاحة في Excel.
احسب المتوسط المرجح في جدول محوري في Excel
رمز VBA - أتمتة حساب المتوسط المرجح في الجدول المحوري
Power Pivot (نموذج البيانات) - استخدام DAX لحساب المتوسط المرجح في الجدول المحوري
احسب المتوسط المرجح في جدول محوري في Excel
لنفترض أن لديك جدولًا يعرض بيانات المبيعات لأنواع مختلفة من الفاكهة، مع أعمدة مثل الفواكه, الوزنو سعر الوحدة، وقد قمت ببناء جدول محوري يلخص هذه القيم، كما هو موضح أدناه.
عندما تحتاج إلى حساب سعر المتوسط المرجح لكل نوع من الفاكهة - مما يعني أنك تريد أن تعكس مساهمة كل نقطة بيانات بناءً على وزنها - لا تسمح الجداول المحورية باستخدام دالة SUMPRODUCT أو وظائف متقدمة مشابهة في الحقل المحسوب. الحل التالي يتغلب على هذا القيد بإضافة عمود مساعد إلى بيانات المصدر وحساب المتوسط المرجح عبر الخيارات المدمجة في الجدول المحوري.
1. ابدأ بإضافة عمود مساعد يُسمى القيمة في بيانات المصدر.
أدخل عمودًا جديدًا فارغًا، وأعطه العنوان القيمة، وفي الصف الأول (مثل C2)، أدخل الصيغة =D2*E2 (حيث D2 هو الوزن و E2 هو سعر الوحدة - قم بالتعديل حسب الحاجة لعناوين الأعمدة الخاصة بك). بعد ذلك، اسحب مقبض التعبئة لأسفل لملء الصيغة عبر جميع الصفوف. هذه الخطوة تضرب وزن كل عنصر في سعره للحصول على السعر الإجمالي الموزون لذلك العنصر. انظر لقطة الشاشة:
نصائح:
- تأكد من عدم وجود خلايا مدمجة في جدول المصدر الخاص بك، حيث يمكن أن تسبب أخطاء في الصيغ.
- إذا كنت تتعامل مع مجموعات بيانات كبيرة، تحقق مرتين من أن الصيغة تم تطبيقها على جميع الصفوف ذات الصلة.
- إذا تغيرت تعيينات الأعمدة، قم بتحديث الصيغة وفقًا لذلك.
2. بعد ذلك، حدّث الجدول المحوري ليعكس العمود المساعد المضاف. اختر أي خلية داخل الجدول المحوري، مما سيؤدي إلى ظهور أدوات الجدول المحوري علامة التبويب السياقية. اضغط على تحليل (أو خيارات، حسب إصدار Excel الخاص بك) > تحديث. هذه الخطوة تضمن ظهور القيمة الحقل الجديد ضمن قائمة حقول الجدول المحوري.
3. لإضافة حقل متوسط مرجح محسوب، انتقل إلى تحليل > الحقول والعناصر والمجموعات > الحقل المحسوب. هذا سيفتح مربع الحوار إدراج الحقل المحسوب، حيث يمكنك إعداد الحساب المخصص الخاص بك.
ملاحظة: سيستخدم الحقل المحسوب الحقول التي تم تعريفها بالفعل في بياناتك. تأكد من إضافة وتحديث جميع الأعمدة الضرورية قبل هذه الخطوة.
4. في مربع الحوار إدراج الحقل المحسوب، اكتب متوسط مرجح (أو اسم مميز آخر) في مربع الاسم . بالنسبة لحقل الصيغة ، أدخل =Amount/Weight. تأكد من استخدام أسماء الحقول الدقيقة من بياناتك المصدر - فهي حساسة لحالة الأحرف ويجب أن تتطابق تمامًا. ثم اضغط على موافق لإضافة الحقل المحسوب للوزن.
استكشاف الأخطاء وإصلاحها:
- إذا رأيت أخطاء #DIV/0!، تأكد من أن قيم الوزن لا تحتوي على أصفار.
- إذا لم يظهر الحقل المحسوب، تأكد من صحة كتابة وحالة أسماء الحقول.
الآن سيظهر متوسط السعر المرجح لكل نوع من الفاكهة في صفوف المجاميع الفرعية في الجدول المحوري. النتيجة تضمن أن حساب متوسط السعر يعكس حقًا تأثير وزن كل إدخال.
المزايا: متوافق مع إصدارات Excel القديمة؛ لا تتطلب إضافات أو ميزات متقدمة.
العيوب: يتطلب تعديل بيانات المصدر عبر أعمدة مساعدة؛ إعادة الحساب قد تكون أقل ديناميكية عند تحديث البيانات.
نصيحة عملية: للإبلاغ الدوري، ضع في اعتبارك إبقاء صيغة العمود المساعد ديناميكية أو أتمتة التحديث باستخدام ماكرو.
Power Pivot (نموذج البيانات) - استخدام DAX لحساب المتوسط المرجح في الجدول المحوري
مع الإصدارات الحديثة من Excel، تتيح إضافة Power Pivot (المعروفة أيضًا بنموذج البيانات) خيارات حساب جديدة باستخدام صيغ DAX (Data Analysis Expressions). هذا يمكّنك من حساب المتوسط المرجح مباشرة في الجدول المحوري دون إنشاء أعمدة مساعدة إضافية في بياناتك الأساسية.
السيناريوهات المطبقة: مثالية عند العمل مع مجموعات بيانات كبيرة أو الجداول المتصلة، وعندما تريد أن يتم تحديث الحسابات تلقائيًا مع بياناتك. هذا النهج مفيد بشكل خاص لتحليلات الأعمال واللوحات المعلوماتية حيث يُفضل الحفاظ على جدول مصدر نظيف.
التعليمات:
- تمكين إضافة Power Pivot انتقل إلى ملف > خيارات > إضافات. في قائمة إدارة، حدد COM Add-ins، انقر على Go، وتحقق من Power Pivot.
- أضف البيانات إلى Power Pivot حدد الجدول الخاص بك في ورقة العمل، ثم انقر على Power Pivot > إدارة لفتح نافذة Power Pivot.
- إنشاء جدول محوري من Power Pivot في نافذة Power Pivot، اذهب إلى الصفحة الرئيسية > جدول محوري.
ثم اختر مكان إدراجه (مثل ورقة عمل موجودة) واضغط موافق.
- قم ببناء الجدول المحوري وأضف مقياسًا في قائمة حقول الجدول المحوري الجديدة، اسحب الحقول إلى المناطق المناسبة. ثم انقر بزر الماوس الأيمن على اسم الجدول واختر إضافة مقياس.
- تحديد المقياس في مربع الحوار مقياس:
- قم بتسمية المقياس (مثل، متوسط السعر المرجح).
- أدخل التعبير DAX التالي للمتوسط المرجح.
=SUMX(Table1, Table1[Weight] * Table1[Price]) / SUM(Table1[Weight])(استبدل Table1، [الوزن]، و[السعر] بأسماء الجدول والحقل الفعلية الخاصة بك.) - انقر موافق لإضافته.
- استخدام المقياس في الجدول المحوري المقياس المضاف حديثًا سيظهر في قائمة الحقول ويمكن سحبه إلى منطقة القيم مثل أي حقل آخر.
نصائح واستكشاف الأخطاء وإصلاحها:
- صيغ DAX ليست حساسة لحالة الأحرف ولكن يجب أن تتطابق أسماء الحقول والجداول مع نموذجك.
- تغيير البيانات الأساسية يُحدّث المقياس تلقائيًا في الجدول المحوري الخاص بك.
- إذا حصلت على نتائج فارغة أو غير متوقعة، تحقق من وجود قيم وزن صفرية أو مفقودة وتأكد من تحديث نموذج البيانات بشكل صحيح.
المزايا: لا تحتاج إلى تعديل بيانات المصدر؛ الحسابات تُحدّث فورًا مع تغييرات البيانات وتتيح تلخيصات متقدمة.
العيوب: Power Pivot غير متاح في جميع إصدارات Excel وقد يتطلب إعدادًا أوليًا؛ المستخدمون غير المألوفين بـ DAX قد يواجهون منحنى تعلم.
اكتشف سحر Excel مع Kutools AI
- التنفيذ الذكي: قم بعمليات الخلية، وتحليل البيانات، وإنشاء المخططات - كل ذلك يتم من خلال أوامر بسيطة.
- الصيغ المخصصة: قم بإنشاء صيغ مصممة خصيصًا لتسهيل سير عملك.
- برمجة VBA: كتابة وتطبيق أكواد VBA بسهولة.
- تفسير الصيغ: فهم الصيغ المعقدة بسهولة.
- ترجمة النصوص: تجاوز الحواجز اللغوية داخل جداول البيانات الخاصة بك.
مقالات ذات صلة:
أفضل أدوات الإنتاجية لمكتب العمل
عزز مهاراتك في Excel باستخدام Kutools لـ Excel، واختبر كفاءة غير مسبوقة. Kutools لـ Excel يوفر أكثر من300 ميزة متقدمة لزيادة الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة الأكثر أهمية بالنسبة لك...
Office Tab يجلب واجهة التبويب إلى Office ويجعل عملك أسهل بكثير
- تفعيل تحرير وقراءة عبر التبويبات في Word، Excel، PowerPoint، Publisher، Access، Visio وProject.
- افتح وأنشئ عدة مستندات في تبويبات جديدة في نفس النافذة، بدلاً من نوافذ مستقلة.
- يزيد إنتاجيتك بنسبة50%، ويقلل مئات النقرات اليومية من الفأرة!
جميع إضافات Kutools. مثبت واحد
حزمة Kutools for Office تجمع بين إضافات Excel وWord وOutlook وPowerPoint إضافة إلى Office Tab Pro، وهي مثالية للفرق التي تعمل عبر تطبيقات Office.
- حزمة الكل في واحد — إضافات Excel وWord وOutlook وPowerPoint + Office Tab Pro
- مثبّت واحد، ترخيص واحد — إعداد في دقائق (جاهز لـ MSI)
- الأداء الأفضل معًا — إنتاجية مُبسطة عبر تطبيقات Office
- تجربة كاملة لمدة30 يومًا — بدون تسجيل، بدون بطاقة ائتمان
- قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد