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