كيف تحسب Z-Score باستخدام Microsoft Excel

Z-Score هي قيمة إحصائية تخبرك عن عدد الانحرافات المعيارية التي تحدثها قيمة معينة من متوسط مجموعة البيانات بالكامل. يمكنك استخدام صيغتي AVERAGE و STDEV.S أو STDEV.P لحساب المتوسط والانحراف المعياري لبياناتك ثم استخدام هذه النتائج لتحديد Z-Score لكل قيمة.

ما هو Z-Score وماذا تفعل وظائف AVERAGE و STDEV.S و STDEV.P?

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

= (DATAPOINT-AVERAGE (مجموعة البيانات)) / STDEV (مجموعة البيانات)

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

للوهلة الأولى ، فإن درجة 95٪ هي أكثر إثارة للإعجاب ، ولكن ماذا لو قدم المعلم من الدرجة الثانية امتحانًا أكثر صعوبة؟ يمكنك حساب نقاط Z لكل درجة من الطلاب بناءً على متوسط النتائج في كل فصل والانحراف المعياري للدرجات في كل فصل. يمكن مقارنة نتائج Z-Scores للطالبين أن الطالب الذي حصل على نتيجة 87٪ كان أفضل مقارنة بباقي الفصل الدراسي مقارنة بالطالب الذي حصل على 98٪ من النقاط مقارنة ببقية طلاب الصف.

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

القيمة الإحصائية الأخرى التي نحتاجها هي "الانحراف المعياري" ، ولدى Excel وظيفتين مختلفتين لحساب الانحراف المعياري بطرق مختلفة قليلاً.

كانت الإصدارات السابقة من Excel تحتوي فقط على الدالة "STDEV" ، التي تقوم بحساب الانحراف المعياري أثناء معالجة البيانات على أنها "عينة" من مجموعة سكانية. Excel 2010 كسر ذلك إلى وظيفتين تقوم بحساب الانحراف المعياري:

STDEV.S: هذه الوظيفة مماثلة لوظيفة "STDEV" السابقة. يقوم بحساب الانحراف المعياري أثناء معالجة البيانات على أنها "عينة" من السكان. قد تكون عينة من السكان شيئًا مثل البعوض المعين الذي تم جمعه لمشروع بحثي أو سيارات تم وضعها جانباً واستخدامها لاختبار سلامة التصادم.
STDEV.P: تقوم هذه الدالة بحساب الانحراف المعياري أثناء معالجة البيانات ككل السكان. إن كل السكان سيكونون مثل كل البعوض على الأرض أو كل سيارة في مرحلة إنتاج نموذج معين.

الذي تختاره يعتمد على مجموعة البيانات الخاصة بك. عادة ما يكون الفرق صغيرًا ، ولكن ستكون نتيجة الدالة "STDEV.P" أصغر دائمًا من دالة "STDEV.S" لنفس مجموعة البيانات. إنه نهج أكثر تحفظًا للافتراض أن هناك تباينًا أكبر في البيانات.

لنلقي نظرة على مثال

على سبيل المثال ، لدينا عمودين ("القيم" و "Z-Score") وثلاث خلايا "مساعدة" لتخزين نتائج الدالتين "AVERAGE" و "STDEV.S" و "STDEV.P". يحتوي عمود "القيم" على عشرة أرقام عشوائية متمركزة حول 500 ، وعمود "Z-Score" هو المكان الذي سنقوم بحساب Z-Score باستخدام النتائج المخزنة في الخلايا "المساعدة"..

أولاً ، سنقوم بحساب متوسط القيم باستخدام الدالة "AVERAGE". حدد الخلية حيث ستخزن نتيجة الدالة "AVERAGE".

اكتب في الصيغة التالية واضغط أدخل -أو - استخدم قائمة "الصيغ".

= AVERAGE (E2: E13)

للوصول إلى الوظيفة من خلال قائمة "الصيغ" ، حدد القائمة المنسدلة "مزيد من الوظائف" ، وحدد الخيار "إحصائي" ، ثم انقر فوق "AVERAGE".

في نافذة وسيطات الدالة ، حدد كل الخلايا في عمود "القيم" كمدخل للحقل "Number1". لا داعي للقلق بشأن حقل "Number2".

الآن اضغط على "موافق".

بعد ذلك ، نحتاج إلى حساب الانحراف المعياري للقيم باستخدام الدالة "STDEV.S" أو "STDEV.P". في هذا المثال ، سنوضح لك كيفية حساب كلتا القيمتين ، بدءًا من "STDEV.S". حدد الخلية التي سيتم تخزين النتيجة فيها.

لحساب الانحراف المعياري باستخدام الدالة "STDEV.S" ، اكتب هذه الصيغة واضغط على Enter (أو قم بالوصول إليه من خلال قائمة "الصيغ").

= STDEV.S (E3: E12)

للوصول إلى الوظيفة من خلال قائمة "الصيغ" ، حدد القائمة المنسدلة "مزيد من الوظائف" ، وحدد الخيار "إحصائي" ، وقم بالتمرير لأسفل ، ثم انقر فوق الأمر "STDEV.S".

في نافذة وسيطات الدالة ، حدد كل الخلايا في عمود "القيم" كمدخل للحقل "Number1". لا داعي للقلق بشأن حقل "Number2" هنا ، سواء.

الآن اضغط على "موافق".

بعد ذلك ، سنقوم بحساب الانحراف المعياري باستخدام وظيفة "STDEV.P". حدد الخلية حيث سيتم تخزين النتيجة.

لحساب الانحراف المعياري باستخدام الدالة "STDEV.P" ، اكتب هذه الصيغة واضغط على Enter (أو قم بالوصول إليها من خلال قائمة "الصيغ").

= STDEV.P (E3: E12)

للوصول إلى الوظيفة من خلال قائمة "الصيغ" ، حدد القائمة المنسدلة "مزيد من الوظائف" ، وحدد الخيار "إحصائي" ، وقم بالتمرير لأسفل ، ثم انقر فوق صيغة "STDEV.P".

في نافذة وسيطات الدالة ، حدد كل الخلايا في عمود "القيم" كمدخل للحقل "Number1". مرة أخرى ، لن تحتاج إلى القلق بشأن حقل "Number2".

الآن اضغط على "موافق".

الآن بعد أن قمنا بحساب المتوسط والانحراف المعياري لبياناتنا ، لدينا كل ما نحتاجه لحساب Z-Score. يمكننا استخدام صيغة بسيطة تشير إلى الخلايا التي تحتوي على نتائج الدالتين "AVERAGE" و "STDEV.S" أو "STDEV.P".

حدد الخلية الأولى في عمود "Z-Score". سنستخدم نتيجة الدالة "STDEV.S" لهذا المثال ، ولكن يمكنك أيضًا استخدام النتيجة من "STDEV.P."

أدخل الصيغة التالية واضغط على Enter:

= (E3- $ G $ 3) / $ H $ 3

بدلاً من ذلك ، يمكنك استخدام الخطوات التالية لإدخال الصيغة بدلاً من كتابتها:

انقر فوق الخلية F3 واكتب = (
حدد الخلية E3. (يمكنك الضغط على اليسار السهم مفتاح مرة واحدة أو استخدام الماوس)
اكتب علامة الطرح -
حدد الخلية G3 ثم اضغط F4 لإضافة الأحرف "$" لجعل مرجع "مطلق" للخلية (سوف يمر عبر "G3"> "$G$3 ">" G$3 ">"$G3 ">" G3 "إذا استمر الضغط F4)
نوع ) /
حدد الخلية H3 (أو I3 إذا كنت تستخدم "STDEV.P") واضغط F4 لإضافة حرفين "$".
اضغط دخول

تم حساب Z-Score للقيمة الأولى. وهو الانحراف المعياري 0.15945 دون المتوسط. للتحقق من النتائج ، يمكنك مضاعفة الانحراف المعياري بهذه النتيجة (6.271629 * -0.15945) والتحقق من أن النتيجة تساوي الفرق بين القيمة والمتوسط (499-500). كلا النتائج متساوية ، لذا فإن القيمة منطقية.

لنقوم بحساب Z-Scores لبقية القيم. قم بتمييز عمود "Z-Score" بالكامل بدءًا من الخلية التي تحتوي على الصيغة.

اضغط Ctrl + D ، الذي يقوم بنسخ الصيغة في الخلية العلوية لأسفل خلال جميع الخلايا المحددة الأخرى.

الآن تم "تعبئة" الصيغة إلى كافة الخلايا ، وسيقوم كل منها دائمًا بالإشارة إلى الخلايا "AVERAGE" و "STDEV.S" أو "STDEV.P" الصحيحة بسبب الأحرف "$". إذا حصلت على أخطاء ، فارجع وتأكد من تضمين الأحرف "$" في الصيغة التي أدخلتها.

حساب Z-Score دون استخدام خلايا "Helper"

تخزن الخلايا المساعدة نتيجة ، مثل تلك التي تخزن نتائج الدالتين "AVERAGE" و "STDEV.S" و "STDEV.P". يمكن أن تكون مفيدة ولكنها ليست ضرورية دائمًا. يمكنك تخطيها تمامًا عند حساب Z-Score باستخدام الصيغ المعممة التالية ، بدلاً من ذلك.

هنا واحد باستخدام وظيفة "STDEV.S":

= (القيمة AVERAGE (القيم)) / STDEV.S (القيم)

واحد باستخدام وظيفة "STEV.P":

= (القيمة AVERAGE (القيم)) / STDEV.P (القيم)

عند إدخال نطاقات الخلية لـ "القيم" في الدالات ، تأكد من إضافة مراجع مطلقة ("$" باستخدام F4) بحيث لا تقوم بحساب متوسط أو انحراف معياري لنطاق مختلف عند "ملء". من الخلايا في كل صيغة.

إذا كان لديك مجموعة كبيرة من البيانات ، فقد يكون استخدام الخلايا المساعدة أكثر فاعلية لأنه لا يحسب نتيجة الدالتين "AVERAGE" و "STDEV.S" أو "STDEV.P" في كل مرة ، مما يوفر موارد المعالج و تسريع الوقت الذي يستغرقه حساب النتائج.

كذلك ، يأخذ "$ G $ 3" عددًا أقل من وحدات البايت للتخزين وأقل من ذاكرة الوصول العشوائي (RAM) للتحميل من "AVERAGE ($ E $ 3: $ E $ 12).". هذا مهم لأن الإصدار القياسي 32 بت من Excel يقتصر على 2 غيغابايت من ذاكرة الوصول العشوائي (لا يحتوي الإصدار 64 بت على أي قيود على مقدار ذاكرة الوصول العشوائي التي يمكن استخدامها).