يعد Microsoft Excel (يشار إليه غالبًا باسم Excel) برنامجًا قويًا لجداول البيانات مصممًا لتخزين مجموعات كبيرة من البيانات. كما أنه يمكّن المستخدمين من تنظيم البيانات وتحليلها وحتى إجراء العمليات الحسابية الأساسية والمعقدة على البيانات المسجلة. تتضمن مهام تحليل البيانات الأساسية في Excel بشكل أساسي حساب أو إيجاد المتوسط (أو الوسط الحسابي) والوضع والوسيط.
في هذا البرنامج التعليمي، نناقش طرقًا أو حلولًا مختلفة خطوة بخطوة حول كيفية حساب المتوسط في برنامج Excel. قبل مناقشة الطرق، دعونا أولا نفهم بإيجاز مفهوم المتوسط وفوائده.
ما هو يعني في إكسل؟
في Excel، المتوسط الحسابي ليس سوى متوسط عدد يتم حسابه عادةً عن طريق إضافة كافة أرقام مجموعة البيانات المحددة ثم قسمة المجموع على إجمالي عدد القيم (أو العدد) للعناصر المحددة.
على الرغم من أنه من السهل حساب المتوسط الحسابي لمجموعات البيانات الصغيرة في الحياة الواقعية، إلا أنه يصبح من الصعب العمل مع مجموعات البيانات الكبيرة. وهنا يأتي دور برنامج جداول البيانات القوي مثل Excel. يمكنه بسهولة حساب متوسط مئات إلى آلاف الأرقام. كما أنه أقل عرضة للأخطاء.
هناك العديد من الاستخدامات الأساسية للمتوسط في تحليل البيانات، مثل:
- لمقارنة البيانات التاريخية
- لتسليط الضوء على مؤشرات الأداء الرئيسية (مؤشرات الأداء الرئيسية) وعروض الأسعار
- للعثور على نقطة المنتصف لنقاط البيانات المعطاة
- لتحديد إدارة المشاريع وخطط العمل
السيناريو الأساسي الذي قد نحتاج فيه إلى حساب المتوسط في Excel هو عندما نحتاج إلى معرفة متوسط مبيعات عنصر ما في شهر كامل، أو ربع سنة، أو سنة، وما إلى ذلك.
كيفية حساب المتوسط في إكسيل؟
لسوء الحظ، لا توجد دالة MEAN محددة لحساب أو تحديد المتوسط الحسابي للبيانات. ومع ذلك، هناك عدة طرق فعالة. بشكل عام، يمكننا عادةً تطبيق المفهوم الرياضي المتمثل في إضافة القيم ثم قسمة مجموعها على عدد القيم داخل خلايا Excel. علاوة على ذلك، هناك وظائف موجودة مثل AVERAGE وAVERAGEA وAVERAGEIF وAVERAGEIFS، والتي تساعدنا أيضًا في حساب المتوسط الحسابي في حالات استخدام مختلفة.
للحصول على فكرة أفضل عن كيفية عمل هذه المفاهيم لحساب الوسط الحسابي، دعونا نناقش كل واحد على حدة مع مثال:
حساب المتوسط في Excel: الطريقة العامة
يمكننا تطبيق الدالتين SUM وCOUNT في ورقة Excel لاستخدام أسلوب إضافة القيم وتقسيمها على عدد القيم المضمنة.
جافا الرياضيات الأسرى
على سبيل المثال، لنفترض أننا أعطينا الأرقام من 1 إلى 5 ونحتاج إلى حساب المتوسط الحسابي. رياضياً، نقوم أولاً بجمع كل هذه الأرقام، أي (1+2+3+4+5). بعد ذلك، نقسم SUM على 5 لأنه العدد الإجمالي للأرقام المعطاة.
وبالتالي، تصبح الصيغة الكاملة لحساب المتوسط كما يلي:
(1+2+3+4+5)/ 5
يؤدي هذا إلى إرجاع الوسط الحسابي 3.
في برنامج Excel، يمكننا استخدام الصيغة الرياضية المذكورة أعلاه من خلال دمجها مع الدالتين SUM وCOUNT بالطريقة التالية:
=SUM(1,2,3,4,5)/COUNT(1,2,3,4,5)
هنا، تقوم الدالة SUM بجمع القيم المعطاة، بينما تحدد الدالة COUNT عدد القيم المعطاة.
الآن، إذا قمنا بتسجيل هذه الأرقام الخمسة في النطاق A1:A5، فإن الصيغة أدناه في الخلية A6 ستحسب المتوسط الحسابي:
=SUM(A1:A5)/COUNT(A1:A5)
النتيجة (أو الوسط) التي تم الحصول عليها في الصورة أعلاه هي نفسها التي حسبناها من الصيغة الرياضية، أي، 3.
مشرف بوويرشيل
حساب المتوسط في Excel: الدالة المتوسطة
وبما أن المتوسط عبارة عن متوسط، فإن دالة AVERAGE في Excel هي الطريقة الأكثر فعالية لحساب المتوسط الحسابي. إنها إحدى الوظائف الإحصائية الأكثر استخدامًا لحساب المتوسط لنطاق معين إذا لم يكن هناك شرط أو قيود خاصة. إنها وظيفة Excel المضمنة.
بناء الجملة
بناء جملة الدالة AVERAGE في Excel كما يلي:
=المتوسط(number1، [number2]، ...)
الرقم 1 هو الوسيطة المطلوبة، مما يعني أن وسيطة واحدة إلزامية في الدالة AVERAGE. ومع ذلك، يمكننا توفير وسائط متعددة بأشكال مختلفة مثل الأرقام والخلايا والنطاقات والمصفوفات والثوابت وما إلى ذلك. عند حساب المتوسط الحسابي، يجب علينا توفير وسيطتين على الأقل في الدالة AVERAGE.
خطوات حساب المتوسط باستخدام الدالة AVERAGE
دعونا الآن نفهم عمل الدالة AVERAGE ونحسب الوسط الحسابي لمجموعة من الأرقام في ورقة Excel باستخدام بيانات العينة.
لنفترض أننا أدرجنا بعض الفواكه في ورقة إكسل مع أسعارها المقابلة. أسماء الفواكه مسجلة في العمود A (A2:A9)، وأسعارها في العمود التالي B (B2:B9). نحن بحاجة إلى إيجاد أو حساب متوسط السعر (أو المتوسط الحسابي للأسعار) في الخلية B10.
نحتاج إلى تنفيذ الخطوات التالية لحساب الوسط الحسابي في ورقة العمل النموذجية الخاصة بنا:
- أولاً، يجب علينا تحديد الخلية أو تمييزها لتسجيل الوسط الحسابي الناتج، أي الخلية B10.
- بعد ذلك، نحتاج إلى إدراج اسم الدالة بدءًا من علامة التساوي وتوفير الوسائط اللازمة في الدالة. في حالتنا، نكتب علامة المساواة (=) والدالة AVERAGE. يمكننا أيضًا اختيار الوظيفة من قائمة الوظائف التي يقترحها Excel.
- بعد إدخال اسم الوظيفة، يجب علينا إدخال قوس البداية وتوفير مجموعتنا الفعالة من مجموعات البيانات. بما أن لدينا بيانات في خلايا مجاورة أو نطاق (من B2 إلى B9)، فيمكننا تمرير النطاق بأكمله في الدالة بالطريقة التالية:
- وأخيرًا، يجب علينا إدخال قوس الإغلاق والضغط على يدخل اضغط على لوحة المفاتيح للحصول على النتيجة الفعالة أو المتوسط الحسابي لمجموعة البيانات المحددة. الصورة أدناه توضح المتوسط الحسابي للأسعار في الخلايا من B2 إلى B9:
في الصورة أعلاه، استخدمنا الخلايا المجاورة وقمنا بتوفير مجموعة كاملة من مجموعات البيانات دفعة واحدة. ومع ذلك، يمكننا أيضًا حساب متوسط الخلايا غير المتجاورة في ورقة Excel الخاصة بنا عن طريق توفير الخلايا بشكل فردي.
لنفترض أننا نحتاج فقط إلى حساب المتوسط الحسابي لأسعار ثلاث فواكه فقط: التفاح والمانجو والعنب. في هذه الحالة يمكننا تمرير كل خلية على حدة في الدالة بالطريقة التالية:
= المتوسط (B2، B3، B6)
هنا، B2 وB3 وB6 هي الخلايا الفردية بأسعار التفاح والمانجو والعنب.
على الرغم من إمكانية استخدام الدالة AVERAGE مع الخلايا الفارغة والنصوص والأصفار والقيم المنطقية وحتى الوسائط المختلطة، إلا أنها تتجاهل الخلايا الفارغة والنصوص والقيم المنطقية أثناء العملية الحسابية. ومع ذلك، يتم تضمين الخلايا ذات القيمة الصفرية في الدالة AVERAGE.
حساب المتوسط في Excel: الدالة AVERAGEA
الدالة AVERAGEA هي نسخة متقدمة قليلاً من الدالة AVERAGE. تساعدنا الدالة AVERAGEA أيضًا في حساب المتوسط الحسابي لمجموعات بيانات معينة وتعمل في جميع إصدارات Excel. إحدى فوائد الدالة AVERAGEA هي أنها يمكن أن تتضمن قيمًا نصية وأنواع البيانات المنطقية أثناء حساب المتوسط في Excel. يتم تقييم القيم النصية كأصفار، بينما يتم تقييم القيم المنطقية TRUE وFALSE على أنها 1 و0، على التوالي.
بناء الجملة
بناء جملة الدالة AVERAGEA في Excel كما يلي:
نقل وسائل الإعلام
=AVERAGEA(value1, [value2], ...)
مثل الدالة AVERAGE، تكون الوسيطة الأولى في الدالة AVERAGEA مطلوبة. ومع ذلك، يمكننا استخدام وسائط متعددة حسب الحاجة.
بناء جملة الدالتين AVERAGE وAVERAGEA متشابه تقريبًا؛ ومع ذلك، فإن مفهوم عملهم مختلف قليلاً. يمكن للدالة AVERAGE حساب المتوسط الحسابي للقيم الرقمية فقط، بينما يمكن للدالة AVERAGEA حساب نفس الشيء لأي نوع بيانات، بما في ذلك النص والقيم المنطقية.
خطوات حساب المتوسط باستخدام الدالة AVERAGEA
دعونا نستعيد مجموعات البيانات السابقة حيث لدينا أسماء الفاكهة في العمود A (A2:A9) وأسعارها في العمود B (B2:B9). ومع ذلك، ليس لدينا سعر العنب هذه المرة (المسجل كـ 'غير متوفر' في الخلية المقابلة B6)، كما هو موضح أدناه:
يمكننا تنفيذ الخطوات التالية وحساب القيمة المتوسطة باستخدام الدالة AVERAGEA:
- مثل الطريقة السابقة، نقوم أولاً بتحديد الخلية الناتجة B10 لتسجيل المتوسط. بعد ذلك، نكتب علامة المساواة (=)، وندخل الدالة AVERAGEA أو نختارها، ثم نمرر نطاق البيانات بالكامل إلى الدالة الموجودة بين القوسين.
- وأخيرا، يجب علينا الضغط على يدخل مفتاح للحصول على مخرجات فعالة (أو متوسطة).
تُرجع الصورة أعلاه متوسط الإخراج 57.125 باستخدام الدالة AVERAGEA.
إذا استخدمنا الدالة AVERAGE في هذا المثال وقمنا بحساب الوسط الحسابي، فإننا نرى فرقًا كبيرًا بين النتائج التي حصلت عليها كلتا الدالتين. يظهر متوسط الإخراج 65.285 مع الدالة AVERAGE، كما هو موضح أدناه:
الاختلافات في المتوسط الحسابي في هذا المثال منطقية لأن رقم عدد القسمة يختلف لكلتا الدالتين. عدد الأرقام للدالة AVERAGEA هو 8، حيث تقوم أيضًا بتقييم النص كبيانات صالحة. ولكن بالنسبة للدالة AVERAGE، فإن عدد القسمة هو 7 نظرًا لأنها لا تتضمن بيانات TEXT في حسابها.
بدلاً من بيانات TEXT، إذا كانت مجموعة البيانات تحتوي على صفر (0)، فستظهر نفس النتيجة (أو الوسائل الحسابية) لكلتا الدالتين (AVERAGE وAVERAGEA) لأن كلاهما يتضمن صفر كبيانات صالحة في الحساب.
حساب المتوسط في Excel: الدالة AVERAGEIF
الدالة AVERAGEIF هي الدالة ذات الصلة بالدالة AVERAGE وتساعدنا في حساب المتوسط الحسابي بشرط أو معايير معينة. باستخدام الدالة AVERAGEIF، يمكننا فقط حساب المتوسط لأرقام معينة تتبع المعايير المحددة. يقوم برنامج Excel أولاً بتقييم المعايير المحددة، وتحديد موقع الخلايا الفعالة التي تتبع تلك المعايير، ثم يقوم بحساب المتوسط للخلايا الموجودة فقط وفقًا لذلك.
بناء الجملة
بناء جملة الدالة AVERAGEIF في Excel كما يلي:
=AVERAGEAIF(النطاق، المعايير، [متوسط_النطاق])
هنا، النطاق والمعايير هي الوسيطات المطلوبة، في حين أن Average_range اختياري.
- يحتوي النطاق على خلايا ليتم اختبارها وفقًا للمعايير المحددة.
- تشير المعايير إلى الوسيطة حيث يمكننا تحديد الشرط المطلوب اتباعه. يمكن أن يتضمن النص والقيم الرقمية ومرجع الخلايا والنطاق والتعبير المنطقي.
- متوسط_النطاق هو الوسيطة التي نذكر فيها الخلايا (أو النطاق) التي نريد حساب المتوسط لها. إذا لم يتم تحديد هذه الوسيطة، يستخدم Excel النطاق تلقائيًا.
خطوات حساب المتوسط باستخدام الدالة AVERAGEIF
في نفس المثال، لنفترض أننا قمنا بإدراج بعض الفواكه عدة مرات بأسعار مختلفة. هذه المرة، نحتاج فقط إلى حساب المتوسط لأي فاكهة متكررة محددة (أي التفاحة) في الخلية B11. لذلك، نستخدم الدالة AVERAGEIF ونطبق الشرط أثناء حساب المتوسط. يجب علينا تنفيذ الخطوات التالية لحساب متوسط السعر لشركة Apple:
- أولاً، نختار الخلية الناتجة (B11) ونقوم بإدراج صيغة AVERAGEIF بالطريقة التالية:
=AVERAGEIF(A2:B9, 'Apple', B2:B9)
هنا، يمثل A2:B9 النطاق الفعال، وتمثل Apple المعايير/الشرط المطلوب لدينا، ويمثل B2:B9 النطاق المتوسط.
هنا، قمنا بتوفير متوسط_النطاق الاختياري يدويًا لتضمين الخلايا التي تحتوي على الأسعار فقط. في مثالنا، إذا لم نتجاوز النطاق المتوسط، فستستخدم الدالة النطاق الأساسي (A2:B9) مع البيانات النصية وتتسبب في الخطأ #DIV! خطأ.
ملاحظة: من الضروري ملاحظة أن الدالة AVERAGEIF تتجاهل الخلايا الفارغة في متوسط_النطاق الخاص بها. بالإضافة إلى ذلك، إذا كانت أي خلية محددة في المعايير فارغة، فإن الدالة تعاملها كخلية ذات قيمة صفرية.
حساب المتوسط في Excel: دالة AVERAGEIFS
الدالة AVERAGEIFS هي نسخة موسعة أو متقدمة من الدالة AVERAGEIF. تتيح لنا الدالة AVERAGEIF تطبيق شرط واحد فقط في كل مرة. ومع ذلك، باستخدام الدالة AVERAGEIFS، يمكننا تطبيق معايير متعددة ليتم اختبارها في وقت واحد. بالإضافة إلى ذلك، تتيح لنا الدالة AVERAGEIFS استخدام الخلايا ذات القيم المنطقية TRUE وFALSE، مما يساعدنا على اختبار معايير متعددة بسهولة. وهذا غير ممكن أيضًا مع الدالة AVERAGEIF.
بناء الجملة
جافا تحويل السلسلة إلى int
بناء جملة الدالة AVERAGEIFS في Excel كما يلي:
=AVERAGEAIFS(average_range, ومعايير1_range1, ومعايير1, [criteria2_range2, ومعايير2], ...)
هنا، تعد Average_range، ومعايير1_range1، ومعايير1 هي الوسيطات المطلوبة. ومع ذلك، يمكننا توفير الوسائط الاختيارية بناءً على مجموعات البيانات الفعالة لدينا. علاوة على ذلك، تشير المعايير 1 إلى حالة سيتم اختبارها على النطاق 1، وتشير المعايير 2 إلى حالة سيتم اختبارها على النطاق 2، والمزيد.
خطوات حساب المتوسط باستخدام الدالة AVERAGEIFS
لنفكر في نفس بيانات المثال التي استخدمناها في الطريقة السابقة. في الطريقة السابقة استخدمنا الدالة AVERAGEIF وقمنا بحساب المتوسط الحسابي لأسعار الفاكهة المتكررة (التفاح). في هذه الحالة، كان لدينا شرط واحد فقط.
الآن، لنفترض أنه يتعين علينا حساب متوسط أسعار شركة Apple ولكننا ندرج فقط تلك الخلايا التي تكون أسعارها أعلى من 40.
لدينا معياران مختلفان في هذه الحالة، لذلك لا يمكن استخدام الدالة AVERAGEIF.
المعايير 1: في النطاق A2:A9، تكون المعايير الأولية هي التحقق من وجود 'Apple' التي يتم استيفاءها بثلاث خلايا (أو صفوف)
المعايير 2: في النطاق B2:B9، تتمثل المعايير الأخرى في التحقق من الأسعار التي تزيد عن 40 (> 40) والتي يتم استيفاؤها بواسطة خليتين من أصل ثلاث خلايا (أو صف) تحتوي على فاكهة تفاح
وبالتالي، إذا قمنا بحساب المتوسط رياضيًا، فإننا نستخدم الصيغة التالية:
(95+68)/2
هذا يُرجع القيمة المتوسطة لـ 81.5. دعونا نتحقق مما إذا كنا نحصل على نفس النتائج باستخدام الدالة AVERAGEIFS في Excel.
عندما نحتاج إلى اختبار الشروط في Excel، فإننا عادة ما نستعين بالعامل المنطقي OR. يقوم عامل التشغيل OR بإرجاع TRUE وFALSE اعتمادًا على ما إذا كان الشرط المحدد يستوفي أم لا. على سبيل المثال، لنفترض أننا بحاجة إلى التحقق مما إذا كانت الخلية A2 تحتوي على Apple. في هذه الحالة، يمكننا استخدام عامل التشغيل OR مثل هذا:
=OR(A2='Apple')
نقوم بإدراج الشرط أعلاه في ورقة المثال الخاصة بنا لاختبار أو تحديد موقع الخلايا التي تحتوي على فاكهة التفاح. لذلك، نقوم بإنشاء عمود جديد (C) واكتب الشرط المحدد في الخلية C2. بعد ذلك، نقوم بنسخ نفس الشرط في الخلايا المتبقية من العمود C. ويبدو كما يلي:
توضح الصورة أعلاه أن عامل التشغيل OR يُرجع TRUE للخلايا المقابلة لفاكهة Apple، بينما يُرجع FALSE للخلايا الأخرى. بناءً على هذه النتيجة، نقوم الآن بتطبيق الدالة AVERAGEIFS وإدراج شروط متعددة لخلايا القيمة TRUE بالطريقة التالية:
- مثل الطرق الأخرى، نقوم مرة أخرى أولاً بتحديد الخلية الناتجة (أي B12).
- بعد ذلك، نقوم بإدخال صيغة AVERAGEIFS بالطريقة التالية:
=AVERAGEIFS(B2:B9,C2:C9,'TRUE',B2:B9,'>40')
باستخدام صيغة المتوسطات المذكورة أعلاه، قمنا فقط بتضمين الفاكهة 'التفاح' وأسعارها أعلى من 40. - وأخيرا، يجب علينا الضغط على يدخل مفتاح للحصول على نتيجة الصيغة. بالنسبة لمجموعة البيانات الخاصة بنا، قامت الصيغة بإرجاع المتوسط الحسابي لـ 81.5، نفس النتيجة المحسوبة رياضيا.
حالات خاصة
على الرغم من أنه قد يكون لدينا سيناريوهات مختلفة أثناء حساب المتوسط الحسابي في برنامج Excel، إلا أنه تمت مناقشة السيناريوهين الأكثر شيوعًا هنا. ويجب أن نكون حذرين عند حساب الوسط الحسابي في هذه الحالات.
حساب المتوسط بما في ذلك الأصفار
عندما نحتاج إلى حساب المتوسط باستخدام الأصفار، يمكننا عادةً استخدام الدالة AVERAGE وتوفير الخلايا الفعالة، بما في ذلك الخلايا ذات القيمة الصفرية. تتضمن الدالة AVERAGE الأصفار كأرقام صالحة في حسابها.
في مجموعة البيانات النموذجية التالية، نطبق الدالة AVERAGE لحساب المتوسط لدرجات طالبين مختلفين:
الصورة أعلاه توضح القيم المتوسطة المختلفة لكلا الطالبين. إذا نظرنا إلى العلامات، فمجموع العلامات هو نفسه لكلا الطالبين. يحصل الطالب الثاني على علامات صفر في مادة واحدة (خلية القيمة صفر)، بينما يتغيب الطالب الأول (خلية القيمة النصية) في مادة واحدة.
إضافة سلسلة جافا
نظرًا لأن الدالة AVERAGE تتجاهل القيم النصية ولكنها تتضمن قيمًا صفرية، فإن عدد الأقسام يختلف لكل من الطلاب. بالنسبة للطالب الأول، عدد القسمة هو 4، بينما بالنسبة للطالب الآخر هو 5. وبالتالي، هناك اختلاف في متوسط النتائج. ومع ذلك، يمكننا أيضًا استبعاد القيم الصفرية بشكل صريح إذا لزم الأمر.
حساب المتوسط باستثناء الأصفار
عندما نحتاج إلى حساب المتوسط باستثناء الأصفار، يجب علينا الاستفادة من دالة AVERAGEIF في برنامج Excel. يمكننا تطبيق الشرط المحدد في هذه الوظيفة، وتوجيه Excel بعدم تضمين خلايا القيمة الصفرية أثناء الحساب.
يمكن استخدام صيغة AVERAGEIF أدناه لاستبعاد القيم الصفرية أثناء حساب المتوسط في Excel:
=AVERAGEIF(خلية أو نطاق،'>0')
إذا قمنا بإدراج الصيغة أعلاه لبيانات العينة الخاصة بنا، فستظهر نتائج القيمة المتوسطة نفسها لكلا الطالبين.
تتجاهل صيغة AVERAGEIF أعلاه النص وخلايا القيمة الصفرية. إذن، أعداد القسمة هي نفسها (أي 4)، وكذلك الوسط الحسابي لدرجات كلا الطالبين.
نقاط مهمة يجب تذكرها
- من المهم ملاحظة أن أي دالة في Excel، بما في ذلك AVERAGE وAVERAGEA وAVERAGEIF وAVERAGEIFS، يمكنها قبول 255 وسيطة كحد أقصى.
- لا يُنصح بكتابة اسم الوظيفة يدويًا في خلية Excel. من الأفضل تحديد الوظيفة المطلوبة من قائمة الاقتراحات لتجنب الأخطاء الإملائية في أسماء الوظائف وإزالة الأخطاء في النتائج.