كيفية حساب المتوسط الأسبوعي في Excel؟

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

الخطوة 1: أضف عمود مساعد لتسمية كل صف برقم أسبوعه:

أدخل رقم الأسبوع في الخلية D1، ثم في الخلية D2 أدخل الصيغة =WEEKNUM(A2,2). (هنا، A2 يشير إلى تاريخ الشراء الخاص بك في عمود التاريخ/الوقت. الوسيطة الثانية 2 تخبر Excel بالنظر إلى الأسابيع بدءاً من الاثنين، وهو ما يناسب معظم السيناريوهات التجارية. إذا كان أسبوعك يبدأ يوم الأحد، يمكنك استخدام 1). بعد ذلك، اسحب مقبض التعبئة لأسفل لملء أرقام الأسابيع لكل نطاق البيانات. الآن ستتمكن من رؤية أي أسبوع ينتمي إليه كل صف.

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

أدخل العام في الخلية E1. في الخلية E2، أدخل =YEAR(A2) (مرة أخرى، A2 هي خلية تاريخ الشراء الخاصة بك). اسحب مقبض التعبئة لأسفل، وستحتوي بياناتك الآن على أعمدة للأسبوع والعام للتجميع الأكثر دقة.

الخطوة 3: الآن، احسب المتوسط الأسبوعي. في الخلية F1، أدخل المتوسط. في الخلية F2، أدخل الصيغة التالية: =IF(AND(D2=D1,E2=E1),"",AVERAGEIFS($C$2:$C$39,$D$2:$D$39,D2,$E$2:$E$39,E2)). بعد ذلك، اسحب مقبض التعبئة لأسفل عبر النطاق المقصود.

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

ملاحظات:
(1) إذا ظهرت عدة إدخالات لأسبوع واحد في نفس السنة، يتم عرض قيمة المتوسط فقط في الصف الأول المقابل؛ تُترك الصفوف الأخرى فارغة للوضوح.
(2) D1 وD2 يشيران إلى أعمدة رقم الأسبوع، E1 وE2 يشيران إلى أعمدة السنة، $C$2:$C$39 هو النطاق الذي تحتوي قيمه على المبالغ التي تريد متوسطها، $D$2:$D$39 هو عمود رقم الأسبوع، $E$2:$E$39 هو عمود السنة؛ قم بتعديل هذه النطاقات والمراجع حسب الحاجة لمجموعتك من البيانات.
(3) إذا كنت لا تحتاج إلى مراعاة السنوات وتريد متوسطاً حسب رقم الأسبوع فقط، استخدم في F2: =AVERAGEIF($D$2:$D$39,D2,$C$2:$C$39) واملأ لأسفل. هذا سيوفر المتوسط الأسبوعي البسيط دون التمييز بين السنوات. يتم عرض نتيجة المثال أدناه:

Tip: For very large datasets, copying formulas down can slow Excel. In such cases, see the VBA automation or pivot table approach below for a more scalable solution.

إذا تلقيت أخطاء #DIV/0!، فهذا يعني عادةً أنه لا توجد إدخالات متطابقة لهذا الأسبوع في مجموعة بياناتك. تأكد من عدم وجود فراغات أو أنواع بيانات غير متطابقة في أعمدة المساعدة وأعمدة المبالغ.

تُقدم هذه الطريقة Kutools لـ Excel  Advanced Combine Rows utilities، مما يجعل من السهل حساب جميع المتوسطات الأسبوعية دفعة واحدة بدون إدخال الصيغ يدوياً. Kutools يبسط مهام التجميع المعقدة والتوسط، مما يوفر الوقت ويقلل من الأخطاء اليدوية - خاصة عند التعامل مع القوائم المتوسطة إلى الكبيرة ذات التجميعات والحسابات المتكررة.

1. في عمود مساعد، أدخل الأسبوع في الخلية D1. ثم في D2 اكتب =WEEKNUM(A2,2) (A2 = خلية التاريخ). املأ لأسفل لتسمية كل سجل برقم أسبوعه. يسمح هذا العمود لـ Kutools بتجميع السجلات حسب الأسبوع.

2. حدد الجدول الذي يتضمن عمود الأسبوع الجديد، ثم انقر Kutools > Content > Advanced Combine Rows. بما أن Kutools يعمل على النطاقات المحددة، تأكد من أن تحديدك يغطي البيانات ذات الصلة، بما في ذلك الأعمدة التي سيتم دمجها وعمود التجميع الأسبوعي.

3. في صندوق الحوار المفتوح Combine Rows Based on Column، قم بإعداد العمليات التالية:
(1) انقر فوق عمود الفاكهة وعيّنه لـ Combine (باستخدام الفاصلة كفاصل) للحفاظ على أسماء الفاكهة معاً لكل أسبوع.
(2) بالنسبة لعمود المبلغ، قم بتعيين العملية إلى Calculate > Average بحيث توفر تلقائياً المتوسط الأسبوعي.
(3) عيّن عمود الأسبوع كـ Primary Key بحيث يكون التجميع بناءً على الأسابيع.
(4) انقر Ok للمعالجة.

سيقوم Kutools بتجميع جميع السجلات بسرعة حسب الأسبوع، وسرد جميع الفواكه ذات الصلة، وعرض المتوسط في جدول جديد نظيف، كما هو موضح أدناه:

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

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

الخطوة 1: أضف عمود مساعد إلى جدولك لرقم الأسبوع. في الخلية الأولى من عمود فارغ (مثل D2)، أدخل: =WEEKNUM(A2,2)، مع الإشارة إلى تاريخ الشراء الخاص بك. املأ لأسفل لتسمية كل صف بالأسبوع من السنة.

الخطوة 2: مع تحديد جدولك بالكامل (بما في ذلك عمود الأسبوع الجديد)، انتقل إلى إدراج > PivotTable. في مربع الحوار، أكد نطاقك واختر ورقة عمل جديدة أو موجودة للوضع.

الخطوة 3: في جزء قائمة حقول الجدول المحوري:

• اسحب عمود الأسبوع إلى منطقة الصفوف.
• اختيارياً، لفصل البيانات حسب السنة، اسحب أيضاً عمود السنة (المُنشأ باستخدام =YEAR(A2)) إلى الصفوف فوق الأسبوع.
• اسحب عمود المبلغ إلى منطقة القيم. انقر على القائمة المنسدلة > إعدادات حقل القيمة وعيّن إلى المتوسط.

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

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

المزايا: لا توجد صيغ يدوية، تحديث ديناميكي عند تغيير البيانات، يدعم مجموعات البيانات الكبيرة بسلاسة. القيود: يتطلب عمود مساعد لرقم الأسبوع؛ يجب أن تكون التواريخ الأصلية بصيغة تاريخ معروفة.

المتوسط حسب السنة/الشهر/التاريخ في Excel

متوسط الطوابع الزمنية ليوم في Excel

متوسط لكل يوم/شهر/ربع/ساعة باستخدام الجدول المحوري في Excel