كيفية حساب Z-Score باستخدام Microsoft Excel
Z-Score هي قيمة إحصائية تخبرك بعدد الانحرافات المعيارية التي تحدث أن تكون قيمة معينة من متوسط مجموعة البيانات بأكملها. يمكنك استخدام صيغ AVERAGE و STDEV.S أو STDEV.P لحساب المتوسط والانحراف المعياري لبياناتك ثم استخدام هذه النتائج لتحديد درجة Z لكل قيمة.
ما هي Z-Score وماذا تفعل وظائف AVERAGE و STDEV.S و STDEV.P؟
تعتبر Z-Score طريقة بسيطة لمقارنة القيم من مجموعتي بيانات مختلفتين. يتم تعريفه على أنه عدد الانحرافات المعيارية بعيدًا عن متوسط نقطة البيانات. تبدو الصيغة العامة كما يلي:
= (DataPoint-AVERAGE (DataSet)) / STDEV (DataSet)
إليك مثال للمساعدة في التوضيح. لنفترض أنك تريد مقارنة نتائج اختبار اثنين من طلاب الجبر تم تدريسهما من قبل مدرسين مختلفين. أنت تعلم أن أول طالب حصل على 95٪ في الامتحان النهائي في صف واحد ، والطالب في الفصل الآخر حصل على 87٪.
للوهلة الأولى ، تعتبر درجة 95٪ أكثر إثارة للإعجاب ، ولكن ماذا لو أعطى مدرس الفصل الثاني اختبارًا أكثر صعوبة؟ يمكنك حساب Z-Score لدرجة كل طالب بناءً على متوسط الدرجات في كل فصل والانحراف المعياري للدرجات في كل فصل. يمكن أن تكشف مقارنة درجات Z للطالبين أن الطالب الذي حصل على درجة 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".
اكتب الصيغة التالية واضغط على إدخال - أو - استخدم قائمة "الصيغ".
= متوسط (E2: E13)
للوصول إلى الوظيفة من خلال قائمة "الصيغ" ، حدد القائمة المنسدلة "المزيد من الوظائف" ، وحدد الخيار "إحصائي" ، ثم انقر فوق "متوسط".
في نافذة وسيطات الوظيفة ، حدد جميع الخلايا في عمود "القيم" كمدخل للحقل "رقم 1". لا داعي للقلق بشأن حقل "الرقم 2".
الآن اضغط على "موافق".
بعد ذلك ، نحتاج إلى حساب الانحراف المعياري للقيم باستخدام إما دالة "STDEV.S" أو "STDEV.P". في هذا المثال ، سنوضح لك كيفية حساب كلتا القيمتين ، بدءًا من "STDEV.S." حدد الخلية حيث سيتم تخزين النتيجة.
لحساب الانحراف المعياري باستخدام وظيفة "STDEV.S" ، اكتب هذه الصيغة واضغط على Enter (أو قم بالوصول إليها من خلال قائمة "الصيغ").
= STDEV.S (E3: E12)
للوصول إلى الوظيفة من خلال قائمة "الصيغ" ، حدد القائمة المنسدلة "المزيد من الوظائف" ، وحدد الخيار "الإحصائي" ، ثم قم بالتمرير لأسفل قليلاً ، ثم انقر فوق الأمر "STDEV.S".
في نافذة وسيطات الوظيفة ، حدد جميع الخلايا في عمود "القيم" كمدخل للحقل "رقم 1". لا داعي للقلق بشأن حقل "Number2" هنا أيضًا.
الآن اضغط على "موافق".
بعد ذلك ، سنحسب الانحراف المعياري باستخدام وظيفة "STDEV.P". حدد الخلية حيث سيتم تخزين النتيجة.
لحساب الانحراف المعياري باستخدام وظيفة "STDEV.P" ، اكتب هذه الصيغة واضغط على Enter (أو قم بالوصول إليها من خلال قائمة "الصيغ").
= STDEV.P (E3: E12)
للوصول إلى الوظيفة من خلال قائمة "الصيغ" ، حدد القائمة المنسدلة "المزيد من الوظائف" ، وحدد الخيار "الإحصائي" ، وقم بالتمرير لأسفل قليلاً ، ثم انقر فوق صيغة "STDEV.P".
في نافذة وسيطات الوظيفة ، حدد جميع الخلايا في عمود "القيم" كمدخل للحقل "رقم 1". مرة أخرى ، لا داعي للقلق بشأن حقل "الرقم 2".
الآن اضغط على "موافق".
الآن بعد أن حسبنا المتوسط والانحراف المعياري لبياناتنا ، لدينا كل ما نحتاجه لحساب 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 بدون استخدام خلايا "المساعدة"
تقوم الخلايا المساعدة بتخزين نتيجة ، مثل تلك التي تخزن نتائج وظائف "AVERAGE" و "STDEV.S" و "STDEV.P". يمكن أن تكون مفيدة ولكنها ليست ضرورية دائمًا. يمكنك تخطيها تمامًا عند حساب Z-Score باستخدام الصيغ المعممة التالية ، بدلاً من ذلك.
هذه واحدة تستخدم وظيفة "STDEV.S":
= (القيمة المتوسطة (القيم)) / STDEV.S (القيم)
وواحد يستخدم وظيفة "STEV.P":
= (القيمة المتوسطة (القيم)) / STDEV.P (القيم)
عند إدخال نطاقات الخلايا لـ "القيم" في الوظائف ، تأكد من إضافة مراجع مطلقة ("$" باستخدام F4) بحيث لا تقوم بحساب المتوسط أو الانحراف المعياري لنطاق مختلف عند "التعبئة" من الخلايا في كل صيغة.
إذا كانت لديك مجموعة بيانات كبيرة ، فقد يكون من الأفضل استخدام الخلايا المساعدة لأنها لا تحسب نتيجة وظائف "AVERAGE" و "STDEV.S" أو "STDEV.P" في كل مرة ، مما يوفر موارد المعالج و تسريع الوقت المستغرق لحساب النتائج.
أيضًا ، يستغرق تخزين "$ G $ 3" بايت أقل للتخزين وذاكرة وصول عشوائي أقل للتحميل من AVERAGE ($ E $ 3: $ E $ 12). " هذا مهم لأن الإصدار القياسي 32 بت من Excel يقتصر على 2 غيغابايت من ذاكرة الوصول العشوائي (لا يحتوي الإصدار 64 بت على أي قيود على مقدار ذاكرة الوصول العشوائي التي يمكن استخدامها).
