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

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

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

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

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

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

الخطوة 3: الآن، احسب المتوسط الأسبوعي. في الخلية F1، أدخلAverage. في الخلية 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)، ثم اسحبها لأسفل. وستحصل بذلك على المتوسط الأسبوعي البسيط دون تمييز بين السنوات. ويظهر أدناه مثال على النتيجة:

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

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

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

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

2. حدد الجدول الذي يحتوي على عمود الأسبوع الجديد، ثم انقر علىKutools > المحتوى > دمج متقدم للصفوف. ولأن Kutools تعمل بناءً على النطاق المحدد، تأكد من أن تحديدك يشمل جميع البيانات ذات الصلة، بما في ذلك الأعمدة التي تريد دمجها وعمود التجميع الأسبوعي.

3. في مربع الحوار المفتوح «دمج الصفوف Based on Column»، قم بتنفيذ الخطوات التالية:
(1) انقر على عمود الفاكهة وعيّنه لـدمج (باستخدام الفاصلة كفاصل) للحفاظ على أسماء الفواكه معًا لكل أسبوع.
(2) بالنسبة لعمود المبلغ، عيّن العملية علىحساب > المتوسطليقدّم تلقائيًّا المتوسط الأسبوعي.
(3) عيّن عمود الأسبوع كـالمفتاح الأساسيليكون التجميع مبنيًّا على الأسابيع.
(4) انقر علىموافقللمعالجة.

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

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

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

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

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

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

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

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

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

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

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

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

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