روابط سريعة
Z-Score هي قيمة إحصائية تخبرك بعدد الانحرافات المعيارية التي تصادف أن تكون قيمة معينة من متوسط مجموعة البيانات بأكملها. يمكنك استخدام صيغ AVERAGE وSTDEV.S أو STDEV.P لحساب المتوسط والانحراف المعياري لبياناتك ثم استخدام تلك النتائج لتحديد Z-Score لكل قيمة.
ما هي Z-Score وماذا تفعل وظائف AVERAGE وSTDEV.S وSTDEV.P؟
تُعد Z-Score طريقة بسيطة لمقارنة القيم من مجموعتين مختلفتين من البيانات. يتم تعريفه على أنه عدد الانحرافات المعيارية بعيدًا عن المتوسط الذي تقع فيه نقطة البيانات. تبدو الصيغة العامة كما يلي:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
إليك مثال للمساعدة في التوضيح. لنفترض أنك تريد مقارنة نتائج اختبار اثنين من طلاب مادة الجبر الذين يدرسهم معلمون مختلفون. تعلم أن الطالب الأول حصل على 95% في الامتحان النهائي في أحد الفصول، والطالب في الفصل الآخر حصل على 87%.
للوهلة الأولى، تكون درجة 95% أكثر إثارة للإعجاب، ولكن ماذا لو أعطى مدرس الصف الثاني امتحانًا أكثر صعوبة؟ يمكنك حساب Z-Score لدرجات كل طالب بناءً على متوسط الدرجات في كل فصل والانحراف المعياري للدرجات في كل فصل. يمكن أن تكشف مقارنة درجات Z للطالبين أن الطالب الحاصل على درجة 87% كان أداؤه أفضل مقارنة ببقية طلاب فصله مقارنة بالطالب الحاصل على درجة 98% مقارنة ببقية طلاب فصله.
القيمة الإحصائية الأولى التي تحتاجها هي “المتوسط” وتقوم وظيفة “المتوسط” في Excel بحساب تلك القيمة. فهو ببساطة يجمع كل القيم الموجودة في نطاق الخلايا ويقسم هذا المجموع على عدد الخلايا التي تحتوي على قيم رقمية (يتجاهل الخلايا الفارغة).
القيمة الإحصائية الأخرى التي نحتاجها هي “الانحراف المعياري” ولدى Excel وظيفتين مختلفتين لحساب الانحراف المعياري بطرق مختلفة قليلاً.
الإصدارات السابقة من Excel كانت تحتوي فقط على وظيفة “STDEV”، التي تحسب الانحراف المعياري أثناء التعامل مع البيانات على أنها “عينة” من المجتمع. قام Excel 2010 بتقسيم ذلك إلى وظيفتين تحسبان الانحراف المعياري:
- ستيف.س: هذه الوظيفة مطابقة للوظيفة “STDEV” السابقة. يقوم بحساب الانحراف المعياري أثناء التعامل مع البيانات باعتبارها “عينة” من السكان. قد تكون عينة من السكان شيئًا مثل البعوض المعين الذي تم جمعه لمشروع بحثي أو السيارات التي تم وضعها جانبًا واستخدامها لاختبارات السلامة في حالات التصادم.
- ستيف.ب: تحسب هذه الدالة الانحراف المعياري أثناء التعامل مع البيانات على أنها المجموعة الكاملة. سيكون مجموع السكان بمثابة كل البعوض على الأرض أو كل سيارة في عملية إنتاج لنموذج معين.
الذي تختاره يعتمد على مجموعة البيانات الخاصة بك. عادةً ما يكون الفرق صغيرًا، لكن نتيجة الدالة “STDEV.P” ستكون دائمًا أصغر من نتيجة الدالة “STDEV.S” لنفس مجموعة البيانات. إنه نهج أكثر تحفظًا هو افتراض وجود المزيد من التباين في البيانات.
دعونا ننظر إلى مثال
على سبيل المثال، لدينا عمودين (“Values” و”Z-Score”) وثلاث خلايا “مساعدة” لتخزين نتائج الوظائف “AVERAGE” و”STDEV.S” و”STDEV.P”. يحتوي عمود “القيم” على عشرة أرقام عشوائية تتمحور حول 500، وعمود “Z-Score” هو المكان الذي سنقوم فيه بحساب Z-Score باستخدام النتائج المخزنة في الخلايا “المساعدة”.
أولاً، سنقوم بحساب متوسط القيم باستخدام الدالة “المتوسط”. حدد الخلية التي ستخزن فيها نتيجة الدالة “المتوسط”.
اكتب الصيغة التالية واضغط على زر الإدخال – أو – استخدم قائمة “الصيغ”.
=AVERAGE(E2:E13)
للوصول إلى الوظيفة من خلال قائمة “الصيغ”، حدد القائمة المنسدلة “مزيد من الوظائف”، وحدد الخيار “إحصائي”، ثم انقر فوق “المتوسط”.
في نافذة وسيطات الدالة، حدد كافة الخلايا الموجودة في عمود “القيم” كمدخل للحقل “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” > “$ز$3″ > “ز$3″ > “$G3″ > “G3” إذا واصلت الضغط على F4)
- يكتب
)/ - حدد الخلية H3 (أو I3 إذا كنت تستخدم “STDEV.P”) واضغط على F4 لإضافة الحرفين “$”.
- اضغط على Enter
تم حساب Z-Score للقيمة الأولى. وهو 0.15945 انحرافات معيارية أقل من المتوسط. وللتحقق من النتائج يمكنك ضرب الانحراف المعياري بهذه النتيجة (6.271629 * -0.15945) والتأكد من أن النتيجة تساوي الفرق بين القيمة والوسط (499-500). كلتا النتيجتين متساويتين، لذا فإن القيمة منطقية.
دعونا نحسب درجات Z لبقية القيم. قم بتمييز عمود “Z-Score” بالكامل بدءًا من الخلية التي تحتوي على الصيغة.
اضغط على Ctrl+D، الذي يقوم بنسخ الصيغة الموجودة في الخلية العلوية للأسفل عبر كافة الخلايا المحددة الأخرى.
الآن تمت تعبئة الصيغة في كافة الخلايا، وستشير كل منها دائمًا إلى الخلايا “AVERAGE” و”STDEV.S” أو “STDEV.P” الصحيحة بسبب الأحرف “$”. إذا ظهرت لك أخطاء، فارجع وتأكد من تضمين الأحرف “$” في الصيغة التي أدخلتها.
حساب Z-Score دون استخدام الخلايا “المساعدة”.
تقوم الخلايا المساعدة بتخزين النتيجة، مثل تلك التي تخزن نتائج الوظائف “AVERAGE” و”STDEV.S” و”STDEV.P”. يمكن أن تكون مفيدة ولكنها ليست ضرورية دائمًا. يمكنك تخطيها تمامًا عند حساب Z-Score باستخدام الصيغ المعممة التالية بدلاً من ذلك.
إليك واحدة تستخدم الدالة “STDEV.S”:
=(Value-AVERAGE(Values))/STDEV.S(Values)
وواحد باستخدام وظيفة “STEV.P”:
=(Value-AVERAGE(Values))/STDEV.P(Values)
عند إدخال نطاقات الخلايا لـ “القيم” في الوظائف، تأكد من إضافة مراجع مطلقة (“$” باستخدام F4) بحيث لا تحسب المتوسط أو الانحراف المعياري لنطاق مختلف عند “التعبئة” من الخلايا في كل صيغة.
إذا كانت لديك مجموعة بيانات كبيرة، فقد يكون استخدام الخلايا المساعدة أكثر كفاءة لأنها لا تحسب نتيجة الدالتين “AVERAGE” و”STDEV.S” أو “STDEV.P” في كل مرة، مما يوفر موارد المعالج و تسريع الوقت المستغرق لحساب النتائج.
كما أن “$G$3” يستغرق عددًا أقل من البايتات للتخزين وذاكرة وصول عشوائي (RAM) أقل للتحميل من “AVERAGE($E$3:$E$12).”. وهذا أمر مهم لأن الإصدار القياسي 32 بت من Excel يقتصر على 2 غيغابايت من ذاكرة الوصول العشوائي (لا يحتوي الإصدار 64 بت على أي قيود على مقدار ذاكرة الوصول العشوائي التي يمكن استخدامها).
(العلامات للترجمة)مايكروسوفت أوفيس(ر)مايكروسوفت إكسل(ر)أوفيس