الدوال الإحصائية Statistical Functions - الجزء الأول - اكسل

مرحبًا بك في مقال الدوال الإحصائية، في سلسلة مقالات حول كيفية إستخدام برنامج مايكروسوفت إكسل.

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

تعمل الدوال الإحصائية على تطبيق عملية حسابية لمجموعة من الخلايا في ورقة العمل. فعلى سبيل المثال، تُستخدم دالة الجمع SUM لجمع القيم الموجودة في نطاق معين من الخلايا، ويرد في القائمة التالية الدوال الإحصائية المستخدمة عادًة في الجدول الآتي "الدوال الإحصائية شائعة الاستخدام".

الدالة	النتائج التي تُخرجها
دالة القيمة المطلقة ABS	القيمة المطلقة للعدد.
دالة المتوسط الحسابي AVERAGE	المتوسط أو الوسط الحسابي لمجموعة من الأرقام.
دالة العد COUNT	عدد مواقع الخلايا الموجودة في نطاق معين يحتوي على أرقام.
دالة العد COUNTA	عدد مواقع الخلايا الموجودة في نطاق معين يحتوي على أرقام أو بيانات.
دالة القيمة الأعلى MAX	أعلى قيمة رقمية في مجموعة من الأرقام.
دالة القيمة المتوسطة MEDIAN	الرقم الأوسط في مجموعة من الأرقام. (نصف الأرقام في المجموعة أعلى من القيمة المتوسطة ونصف الأرقام في المجموعة أقل من القيمة المتوسطة)
دالة القيمة الأقل MIN	أقل قيمة رقمية في مجموعة من الأرقام.
دالة القيمة الأكثر تكرارًا MODE	الرقم الذي يتواجد بصورة متكررة في مجموعة من الأرقام.
دالة الضرب PRODUCT	نتيجة ضرب كل القيم الموجودة في نطاق معين من الخلايا.
دالة الجذر التربيعي SQRT	الجذر التربيعي الموجب لعدد معين.
دالة الانحراف المعياري STDEV.S	الانحراف المعياري لمجموعة من الأرقام بناءً على عينة منهم.
دالة الجمع SUM	إجمالي كل القيم الرقمية في مجموعة.

الدوال الإحصائية شائعة الاستخدام

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

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

دالة الجمع SUM

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

إختر ورقة العمل "تفاصيل الميزانية" لفتحها.
حدد الخلية C12.
اكتب علامة يساوي (=).
اكتب اسم الدالة SUM.
اكتب قوسًا مفتوحًا (().
اضغط على الخلية C3 واسحب لأسفل حتى الخلية C11. هذا سيُدخل النطاق C3:C11 في الدالة.
اكتب قوس إغلاق ()).
اضغط على مفتاح الإدخال Enter. ستحسب الدالة الإجمالي لعمود "الإنفاق الشهري"، وهو 1496 دولارًا أمريكيًا (أو ما يعادله بالعملة المحلية).

يوضح الشكل الآتي شكل دالة الجمع SUM المضافة إلى ورقة عمل "تفاصيل الميزانية" قبل الضغط على مفتاح الإدخال Enter.

إضافة دالة الجمع SUM إلى ورقة عمل

وكما هو موضح في الشكل أعلاه، فقد أُضيفت دالة الجمع SUM إلى الخلية C12؛ ومع ذلك، فهذه الدالة مطلوبة أيضًا لحساب الإجماليات في عمودي الإنفاق السنوي ونفقات العام الماضي. يمكن نسخ الوظيفة ولصقها في مواقع الخلايا هذه بفضل المرجع النسبي.

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

اختر الخلية C12 في ورقة العمل تفاصيل الميزانية.
اضغط على زر النسخ في علامة التبويب الرئيسية Home في شريط إكسل.
حدد الخلايا D12 و E12.
اضغط على زر اللصق في علامة التبويب الرئيسية Home في شريط إكسل. يؤدي هذا إلى لصق دالة الجمع SUM في الخليتين D12 و E12 وحساب الإجماليات لهذه الأعمدة.
حدد الخلية F11.
اضغط على زر النسخ في علامة التبويب الرئيسية Home في شريط إكسل.
اضغط الخلية F12، ثم اضغط على زر اللصق في علامة التبويب الرئيسية Home في شريط إكسل. نظرًا لأن لدينا الآن إجماليات الصف 12، يمكننا لصق معادلة النسبة المئوية للتغيير في هذا الصف.

يوضح الشكل التالي نتائج دالة الجمع SUM التي أُضيفت إلى الخلايا C12 و D12 و E12؛ بالإضافة إلى أن معادلة النسبة المئوية للتغيير نُسخت ولُصقت في الخلية F12. لاحظ أن هذا الإصدار من الميزانية يُخطط لتقليل معدل الإنفاق بنسبة 1.7٪ عن العام الماضي.

نتائج دالة الجمع SUM في ورقة عمل تفاصيل الميزانية

التأكد من صحة العمل

عندما تنوي استخدام دالة إحصائية في نطاق محدد من الخلايا في ورقة عمل، تأكد من أن موقعَي الخلية مفصول بينها بنقطتين وليس بفاصلة؛ حيث إذا أدخلت موقعي الخلية مفصول بينها بفاصلة مثلًا، فستنتج الدالة نتائج، ولكنها ستُطبق على خليتين فقط بدلًا من نطاق من الخلايا.

دالة الجمع SUM بإضافة موقعي خلية

وكما هو موضح في الشكل أعلا، ستجمع دالة الجمع SUM القيم الموجودة في الخليتين C3 و C11 فقط، وليس النطاق C3:C11.

المراجع المطلقة (حساب نسبة الإجماليات)

نظرًا إلى أن الإجماليات أُضيفت جميعها إلى الصف 12 من ورقة العمل "تفاصيل الميزانية"، يمكننا الآن إضافة النسبة المئوية لإجمالي الحساب في العمود B بدءًا من الخلية B3.

تُظهر النسبة المئوية للإجماليات النسبة المئوية لكل قيمة في عمود الإنفاق السنوي بالنسبة إلى الخلية D12، لكن انتبه! فبعد إنشاء المعادلة سيتوجب عليك إلزاميًا إيقاف تشغيل خاصية المرجع النسبي في إكسل قبل نسخ ولصق المعادلة إلى باقي الخلايا في العمود. وتتوقف خاصية المرجع النسبي في إكسل من خلال استخدام المراجع المطلقة. توضح الخطوات التالية كيفية القيام بذلك:

حدد الخلية B3 في ورقة عمل "تفاصيل الميزانية".
اكتب علامة يساوي (=).
حدد الخلية D3.
اكتب الشرطة المائلة (/).
حدد الخلية D12.
اضغط على مفتاح الإدخال Enter. ستلاحظ أن المرافق المنزلية تمثل 16.7٪ من الميزانية السنوية.

إضافة معادلة لحساب النسبة المئوية للإجماليات

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

قد يكون هذا مناسبًا لمرجع الخلية الأول في المعادلة D3، ولكن ليس لمرجع الخلية الثاني D12. يوضح الشكل الموالي ما يحدث ألصقنا المعادلة في النطاق B4:B12 في حالتها الحالية.

الخطأ #DIV/0 الناتج عن المرجع النسبي

يمكنك ملاحظة أن إكسل ينتج رمز الخطأ الموجود في خلية تحتوي على صيغة أو دالة تحاول القسمة على صفر. الخطأ في الكود يعني أن إكسل يحاول قسمة رقم على صفر وهو أمر مستحيل.

وبالنظر إلى المعادلة الموجودة في الخلية B4، ستلاحظ أن مرجع الخلية الأول تغير من D3 إلى D4؛ وهذا لا بأس به لأن غرضنا الآن تقسيم معدل الإنفاق السنوي للتأمين على إجمالي معدل الإنفاق السنوي في الخلية D12. ومع ذلك غير إكسل أيضًا مرجع الخلية D12 إلى D13، ولكن ستنتج الصيغة رمز الخطأ !DIV/0 لأن موقع الخلية D13 فارغ.

لتصحيح الخطأ الناجم عن القسمة على الصفر والموضح في الشكل "الخطأ !DIV/0 الناتج عن المرجع النسبي"، يجب أن نضيف مرجعًا مطلقًا للخلية D12 في الصيغة. يمنع المرجع المطلق إكسل من استخدام المراجع النسبية لتغيير مرجع خلية مُستخدم في صيغة أو دالة؛ ويُطبق المرجع المطلق على مرجع الخلية عن طريق كتابة علامة الدولار ($) أمام حرف العمود ورقم الصف. يُشار إلى هذا أيضًا باسم إغلاق الخلية. توضح الخطوات التالية كيفية تحقيق ذلك:

اضغط مرتين على الخلية B3.
ضع مؤشر الفأرة أمام D12، واضغط. يجب أن يكون المؤشر أمام حرف العمود D في مرجع الخلية D12.
اضغط على مفتاح F4، وسترى علامة الدولار ($) مُضافة أمام حرف العمود D ورقم الصف 12. يمكنك أيضًا كتابة علامة الدولار أمام حرف العمود ورقم الصف.
اضغط على مفتاح الإدخال Enter.
اضغط على الخلية B3.
اضغط على زر النسخ في علامة التبويب الرئيسية Home في شريط إكسل.
حدد النطاق B4:B11.
اضغط على زر اللصق في علامة التبويب الرئيسية Home في شريط إكسل.

يوضح الشكل الآتي، النسبة المئوية لإجمالي المعادلة مع مرجع مطلق مُضاف إلى D12. لاحظ أنه في الخلية B4 سيظل مرجع الخلية كما هو D12 بدلاً من التغيير إلى D13 كما هو موضح في الشكل السابق "الخطأ #DIV/0 الناتج عن المرجع النسبي". وستلاحظ أيضًا أنه النسب المئوية حُسبت وحُددت نتائجها في باقي الخلايا في العمود، ويُصحح الخطأ الناجم عن القسمة على صفر.

إضافة مرجع مطلق إلى مرجع خلية في معادلة

المراجعة على المهارة: المراجع المطلقة

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

دالة العد COUNT

الدالة التالية التي سنضيفها إلى ورقة العمل "تفاصيل الميزانية" هي دالة العد. تُستخدم دالة العد COUNT لتحديد عدد الخلايا التي تحتوي على بيانات رقمية في نطاق مُعين. بالنسبة لورقة عمل "تفاصيل الميزانية"، سنستخدم دالة العد لحساب عدد العناصر المخطط لها في عمود معدل الإنفاق السنوي (العمود D). توضح الخطوات التالية كيفية إضافة دالة العد COUNT إلى ورقة العمل باستخدام قائمة الدوال الرياضية:

حدد الخلية D13 في ورقة عمل "تفاصيل الميزانية".
اكتب علامة يساوي (=).
اكتب الحرف C.
اضغط على السهم المتجه لأسفل على شريط التمرير لقائمة الدوال، وابحث عن كلمة "العد".
اضغط مرتين على كلمة "العد COUNT" في قائمة الدوال.
حدد النطاق D3:D11.
اكتب قوس إغلاق ()).
اضغط على مفتاح الإدخال Enter. تنتج الدالة ناتجًا قدره 9 نظرًا لوجود 9 عناصر مخططة في ورقة العمل.

يوضح الشكل الآتي نافذة قائمة الدوال الذي يظهر بعد إكمال الخطوتين 2 و 3 لدالة العد. توفر قائمة الدوال طريقة بديلة لإضافة دالة إلى ورقة العمل.

استخدام قائمة الدوال لإضافة دالة العد

ويوضح الشكل الآتي ناتج دالة العد بعد الضغط على مفتاح الإدخال. تحسب الدالة عدد الخلايا الموجودة في النطاق D3:D11 التي تحتوي على قيمة رقمية. تُشير النتيجة "9" إلى أن هناك 9 فئات مخططة لهذه الميزانية.