إزاي نحسب الـ Z Score في Excel؟

ايه هو الـ Z Score أو الدرجة المعيارية؟

الـ “زد سكور” هو طريقة لحساب مدي انحراف القيمة الفعلية عن المتوسط بوحدات الانحراف المعياري، كمان بيتيح لينا مقارنة عينتين من مجتمعات مختلفة، وممكن يكون الـ “زد سكور” سالب أو موجب، القيمة السالبة معناها ان القيمة أقل من القيمة الحسابية، والقيمة الموجبة معناها ان القيمة أعلى من القيمة الحسابية.

الـ “زد سكور” بنحسبه ازاي ؟ بناخد الرقم اللي عاوزين نحسب له الـ “زد سكور”، ونطرح منه المتوسط العام لكل الأرقام اللي في العينة، ونقسم الناتج على الانحراف المعياري للأرقام في العينة، وده بنعمله بالصيغة دي:

الـ “زد سكور” = (الرقم اللي عاوزين نحسب له الـ “زد سكور” – المتوسط العام للأرقام) / الانحراف المعياري للأرقام في العينة.

إقرأ المزيد: إزاي تحول العملات في إكسل؟

ايه اللي بيعمله الـ Z Score في حياتنا اليومية؟

الـ”زد سكور” بنستخدمه في حياتنا اليومية عشان نحسب لو كانت قيمة معينة ضمن المتوسط أو بعيدة عنه، وده بيُستخدم في الإحصاءات والرياضيات. والناس كل يوم بيتعاملوا مع بيانات، زي مدة استخدام الشاشة، ونتيجة الامتحان النهائي، والدرجة في المادة، وكمان كمية الفلوس اللي اتصرفت على الملابس في السنة اللي فاتت مثلًا، وده معناه إننا بنستخدم القياسات الإحصائية زي المتوسط والانحراف المعياري.

لكن احنا بنستخدم الـ Z Score فين؟

1- نقدر نستخدم الـ”زد سكور” للمقارنة بين اتنين من البيانات مختلفين وليهم توزيعات مختلفة.

مثلا، عندنا شيفين ممتازين في عمل وجبة السوفليه، الشيف الأول اسمه ساشا والشيف التاني اسمه ديفيد، ديفيد شارك في مسابقة طبخ وخد درجة 472 من أصل 500، وساشا شاركت بردو في نوع زي كده من المسابقات واخدت درجة 82 من أصل 100. دلوقتي عايزين نقرر مين فيهم الناجح أكتر، طب هنعمل كده ازاي؟
درجة ديفيد أعلى من درجة ساشا من حيث القيمة، لكن مش هينفع نقارنهم بالطريقة دي، وفي الحالات اللي زي دي، بنحول الدرجات النقدية لدرجات “زد سكور”، وبالطريقة دي نقدر نحسب مين فيهم الأفضل في المسابقة.

في المسابقة اللي شارك فيها ديفيد، كان المتوسط 392 والانحراف المعياري 100، وعشان تحسب النتيجة المعيارية هتطرح المتوسط من درجة ديفيد 472 وتقسمها على الانحراف المعياري، والمعادلة الصحيحة لده هي: “472 – 392 /100” وهو +0.08.
ده معناه ان درجة ديفيد 0.08 من الانحراف المعياري يعني أعلى من المتوسط.

أما في مسابقة ساشا، كان المتوسط 52 والانحراف المعياري 30، دلوقتي خلينا نطبق نفس الصيغة اللي استخدمناها في مسابقة ديفيد، وهنطرح المتوسط من الدرجة ونقسمها على الانحراف المعياري، فالمعادلة هتكون: “82 – 52 /30”.
نتيجة درجة ساشا هي +1.0. وده معناه ان ساشا اتفوقت بنسبة 1.0 من الانحراف المعياري عن المتوسط.

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

مكان تاني بنستخدم فيه الـ”زد سكور” وهو لقياس نمو الأطفال في العالم، بنستخدمها عشان نحسب الوزن والطول بالنسبة للعمر أو الطول بالنسبة للوزن. الأطفال اللي بيقعوا تحت -2 أو -3 من الانحراف المعياري بيتشخصوا بالتغذية الغير كافية.

نصيحة: دايمًا بنضيف علامة زائد أو ناقص للـ”زد سكور”، ونحاول نحولها لرقمين بعد الصفر. ولو القيمة أقل من 1، دايمًا بنضيف الصفر قبل الفاصلة. زي مثلا، 0.8798 فوق المتوسط لازم تتحول لـ +0.88.

2- مقارنة تكرار وتوزيع درجة معينة بين مجموعتين بيانات مختلفتين

من خلال النظر لمقياس الـ”زد سكور” اللي عملناه، ممكن أحدد كام عدد المتسابقين اللي أخدوا أعلى درجة ساشا أو درجة ديفيد.
لما بنشوف مقياس الـ”زد سكور”، بنعرف إنه في ناس اختاروا درجة ديفيد أكتر من اللي اختاروا درجة ساشا، ده لان البيانات الأكتر بيتم جمعها حوالين انحراف معياري واحد أعلى أو أقل من المتوسط.

بيسمح بتخمين النتيجة

بنقدر نستخدم مقياس الـ”زد سكور” عشان نتوقع درجة ساشا أو ديفيد، وبنقدر نستخدم ده في الحسابات الافتراضية.

لكن ازاي ساعدنا تحويل البيانات الأولية لدرجات “زد سكور” في المقارنة بينهم؟ السبب إن توزيع درجات الـ”زد سكور” دايمًا بيكون فيه متوسط 0 وانحراف معياري 1. حتى لو التوزيع الأصلي مش طبيعي، التوزيع الجديد هيكون فيه متوسط 0 وانحراف معياري 1.
ده بيساعدنا في المقارنة بين مجموعتين مختلفتين من البيانات حتي لو تم قياسهم بطرق مختلفة، زي اللي حصل في المثال بتاعنا.

معظم البيانات في توزيع درجات الـ”زد سكور” بيكون بين انحراف معياري واحد أعلى أو أقل من المتوسط، وقليل من البيانات بيكون بين انحرافين معياريين أعلى وأقل من المتوسط، ومن النادر جدًا يكون فيه بيانات تزيد عن 3 انحرافات معيارية.

ليه بنحتاج لـ Z Score؟

زي ما بنقدر نشوف من الأمثلة اللي فوق، بنحتاج الـ Z Score عشان نقارن بين مجموعتين مختلفتين من البيانات عن طريق توحيد البيانات من أي نوع من القياس.

لما بنحول البيانات لـ Z Score بنعمل إعادة مقايسة وتوحيد للبيانات، وده اللي خلي اسمها Standard Score.

إزاي نحسب الزد سكور بالرياضيات؟

الـ Z score أو النتيجة المعيارية بيتم حسابها من خلال عملية حسابية، وهي عبارة عن طرح نقطة البيانات من المتوسط وبعدين بتقسمها على الانحراف المعياري، طب الصطلحات دي معناها ايه؟

لما بنجمع معلومات عن حاجة معينة، بنسميها “بيانات الجماعة” أو “مجموعة البيانات”، وفي المقالة دي البيانات كلها أرقام، بس الحقيقة البيانات ممكن تكون عن أي حاجة. ولما بنحكي عن معلومة وحدة، بنسميها “نقطة البيانات” أو “الفرد”. والاتنين دول بيتم استخدامهم بنفس المعنى، عشان كده لازم تاخد بالك إنهم متبادلين.

إزاي نحسب المتوسط الحسابي؟

المتوسط الحسابي بنسميه بردو “المعدل”، وبيتم حسابه بجمع كل البيانات وتقسيمها على عددها.
وعشان أوضح الفكرة، هقولك مثال: فرضنا اننا كنا عايزين نحسب كام ساعة بنقضيها قدام الشاشة كل يوم، فكتبنا الوقت اللي احنا قضيناه كل يوم لمدة أسبوع، مثلاً: 2، 2، 5، 3، 7، 1، و 1.
جمعنا كل الأرقام دي مع بعض وطلع المجموع 21، وعدد الأرقام اللي جمعناها 7، فقسمنا المجموع على العدد 7 وحسبنا المعدل الحسابي اللي هو 3.
ده معناه اننا بنقضي 3 ساعات قدام الشاشة كل يوم في الأسبوع.

ازاي نحسب الانحراف المعياري؟

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

وعشان نحسب الانحراف المعياري، لازم نطرح كل الأرقام من المتوسط ونربعها عشان ما تطلعش قيم سالبة. وبعدين نجمع كل الأرقام اللي ربعناها ونقسمها على واحد أقل من عدد الأفراد في مجموعتنا البيانية.

مثال توضيحي لحساب الانحراف المعياري

في مثال الشاشة، إحنا عندنا معلومة إن المتوسط 3، فإحنا هنطرح الرقم 3 من كل رقم في البيانات.
فمثلا لو إحنا قضينا ساعتين قدام الشاشة في يوم الاتنين، ده يبقى “2 – 3 = -1″. وبعدين إحنا هنربع الـ”-1″ اللي جبناه هيطلعنا الرقم 1.

هنطبق نفس القانون على كل فرد في مجموعتنا البيانية، وبعدين نجمع الأرقام دي ونطلع نتيجة الـ”جذر التربيعي لـ30″، وبعدين نقسم النتيجة على واحد أقل من عدد البيانات اللي عندنا، واللي هو ٦. هو ده اللي بيدينا قيمة الانحراف المعياري، واللي هو 0.9.

ازاي نحسب الـ Z Score؟

دلوقتي احنا عندنا المتوسط والانحراف المعياري، ممكن نحسب الـ Z-Score، هنعرف لأي مدى القيم اللي عندنا بتختلف عن المتوسط.، مثلا، إحنا هنحسب الـ”زد سكور” ليوم الاتنين.
هنطرح الرقم 3 اللي هو المتوسط من الرقم اللي عندنا، وبعدين نقسم النتيجة على قيمة الانحراف المعياري، في مثالنا ده هيبقي”2 – 3 = -1″، وبعدين نقسم -1 على 0.9 اللي هي قيمة الانحراف المعياري، والنتيجة هتبقي-1.1، وده معناه إن الوقت اللي إحنا قضيناه في يوم الاتنين قريب جدًا من المتوسط وأقل من معدل الانحراف المعياري بمعدل من ضعف لضعفين.

إزاي تحسب الـ Z-Score في إكسل؟

زي ما شرحنا ليكوا بنحسب الـ Z-Score من خلال طرح قيمة البيانات من المتوسط وبعدين بنقسم الناتج على الانحراف المعياري.
عشان تحسب الـ Z-Score في إكسل، محتاجين الاول نكتب البيانات في العمود الأول تحت بعضها.

ممكن نحسب الـ Z-Score في إكسل باستخدام الصيغ الخاصة بيه، الحاجة الكويسة في استخدام صيغ إكسل هو اننا ممكن نخلي الصيغ تتكيف مع مجموعات بيانات كبيرة، وده هخلينا نحسب بطريقة أسرع وأسهل. خلينا نبدأ ونتعلم ازاي نعمل كده خطوة بخطوة.

الخطوة الأولى: تحديد مجموعة البيانات

يعني فرضنا إننا بنحسب النتايج النهائية للطلاب اللي درسوا اللغة الإنجليزية والأدب، وفرضاً ان درجاتهم هي:
72، 90، 87، 65، 50، 78، 82، 63، 55، و 80.
هنكتب الداتا دي في العمود A من السطر التاني لحد السطر 11، كتبت “Test Scores” في A1، و “Z score” في B1 و C1 عشان الأمور تترتب بشكل سهل، وضفت عمودين “Z score” عشان تشوف إزاي تحسبهم يدوي وأوتوماتيك.

نصيحة: وأنت بتكتب الصيغ، هتشوف الخلية اللي بتشير ليها محددة باللون، فمش هتحتاج تتأكد مرتين من اللي بتكتبه.

الخطوة التانية: احسب المتوسط الحسابي

عشان نحسب الـZ-Score للداتا بتاعتنا، هنحتاج نحسب المتوسط الحسابي، والمتوسط هو القيمة الرياضية المتوسطة لمجموعة من البيانات، ورياضياً بيبقى بجمع كل الأرقام وبعدين نقسمهم على عددهم، لكن في إكسل بنستخدم صيغة بسيطة عشان نحسبه بسرعة.
يلا اكتب “Mean” في أي خلية غير العمود A أو B أو C، مثلاً F2، بعدين اكتب في الخلية الفاضية اللي جنبها، اللي هي G2 في حالتنا، الصيغة دي في الجزء اللي فوق الجدول عشان نحسب المتوسط:

=AVERAGE(A2:A11)

اضغط انتر وهيظهرلك المتوسط الحسابي، وهو 72.2

الخطوة التالتة: حساب الانحراف المعياري

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

نكتب “الانحراف المعياري” في خلية، وليكن F3 للمثال ده، بعدين هنروح للخلية الفاضية اللي جنبها، واللي هي G3 في حالتنا، ونكتب الصيغة في المكان المخصص للصيغ فوق الجدول:

=STDEV.S(A2: A11)

لو كنت بتستخدم آخر إصدارات إكسل، الصيغة هتبقي:

=STDEV.S(A2: A11)

للإصدارات القديمة من إكسل، بنستخدم صيغة STDEV بدل من صيغة STDEV.S اللي هي:

=STDEV(A2: A11)

بعدين نضغط Enter وبيظهر الانحراف المعياري واللي بيكون حوالي 13.55.

نصيحة: لو كنت متأكد انك كنت بتستخدم آخر إصدارات إكسل أو لا، ما تقلقش، إكسل بيقولك تغير الصيغة اللي بتستخدمها لصيغة حديثة.

الخطوة الرابعة: حساب الـ Z-Score يدوي

دلوقتي هنبدأ نحسب الـ z score بتاع كل عينة في الداتا بتاعتنا.

نبدأ بالعينة الأولى اللي قيمتها 72، ونطرح منها المتوسط اللي حسبناه في خانة G2، وبعدين نقسم على الانحراف المعياري اللي حسبناه في خانة G3.

في الخانة الفاضية B2 هنكتب الصيغة دي:

=(A2- G2)/ G3

هيدينا النتيجة -0.01476، دي هي قيمة الـ z score للعينة الأولى، نقدر نحسب الـ z score للعينات التانية بنفس الطريقة، بس بنحط قيمة كل عينة في الخانة اللي فاضية بدل من A2.

بس في طريقة أسهل، بنقفل الـ cells اللي فيهم قيمة الانحراف المعياري والمتوسط باستخدام رمز الدولار ($)، بنحط رمز الدولار قدام رقم الصف وحرف العمود للـ cells اللي عايزين نقفلها، يعني G2 و G3، ومن غير ما نقفل الـ column اللي فيها قيمة العينة A2، عشان نغير القيمة لكل عينة بنفس الصيغة.

في النهاية الصيغة بتاعتنا هتبقى كدا:

=(A2:$G$2)/$G$3

لما بنخلص الصيغة دي، بنقدر نضغط على الزاوية اللي في أسفل يمين الـ cell ونسحبها للأسفل لحد ما نوصل للـ B11، وده هيدينا قيم الـ z score لكل العينات في الداتا بتاعتنا.

مهم: 20 سؤال في Excel للمحللين الماليين لأي Interview

الخطوة الخامسة: استخدام صيغة STANDARDIZE لحساب الـ Z Score

في الخطوة دي هنحسب الـ z score من خلال الصيغة STANDARDIZE أو معيار المعادلة بدل من الحساب اليدوي، يعني لو بنستخدم نفس الداتا بتاعتنا، اللي فيها قيم العينات دي: 72، 90، 87، 65، 50، 78، 82، 63، 55، و80، وبعدين حسبنا المتوسط في خانة G2 والانحراف المعياري في خانة G3.

في البداية عملنا عمودين باسم “Z score”. فلو حطينا القيم في العمود الأول، لازم نحطها في العمود التاني، هندوس على الـ cell الفاضية C2 ونكتب الصيغة اللي هي الـ STANDARDIZE formula في المكان اللي فوق الجدول:

=STANDARDIZE (A2, G2, G3)

ونضغط انتر، والقيمة اللي بتطلع لينا هي نفسها اللي حسبناها في العمود التاني، وهي -0.01476.

هنقدر نستخدم الصيغة دي عشان نحسب الـ z score لكل العينات في الداتا بتاعتنا باستخدام نفس الطريقة اللي استخدمناها في الحساب اليدوي، بس بنقفل الـ cells اللي فيهم قيمة المتوسط والانحراف المعياري بإضافة رمز الدولار ($) جنب رقم الصف وحرف العمود، وبنسيب الـ cell اللي فيها قيمة العينة بدون قفل عشان نغيرها لكل عينة.