فرادرسمحاسبه نمره Z یا Z Score با اکسل – از صفر تا صد
Z-Score یا نمره Z یک مقدار آماری است که به ما اعلام می‌کند یک مقدار خاص چند انحراف معیار از میانگین کل مجموعه داده فاصله دارد. از فرمول‌های AVERAGE و STDEV.S یا STDEV.P می‌توان برای محاسبه میانگین و انحراف معیار داده‌ها استفاده کرد و سپس از این نتایج برای تعیین نمرات Z هر مقدار استفاده می‌کنیم.
نمره Z چیست و تابع‌های AVERAGE و STDEV.S یا STDEV.P چه می‌کنند؟
نمره Z روش ساده‌ای برای مقایسه مقادیر دو مجموعه داده مختلف است. این مفهوم به صورت تعداد انحراف معیارهایی که یک نقطه داده از میانگین فاصله دارد تعریف می‌شود. فرمول کل آن به صورت زیر است:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
مثالی ارائه می‌کنیم که به روشن‌تر شدن موضوع کمک می‌کند. فرض کنید می‌خواهید نتایج آزمون دو دانش‌آموز را در درس جبر که توسط معلم‌های مختلف تدریس شده است مقایسه کنید. می‌دانیم که دانش‌آموز نخست در آزمون نهایی امتیاز 95% و دیگری که در کلاس دیگری درس خوانده امتیاز 87% دریافت کرده است.
در نگاه نخست بدیهی است که امتیاز 95% بسیار عالی است؛ اما اگر معلم کلاس دوم آزمون دشوارتری گرفته باشد چطور؟ در این موارد می‌توانیم اقدام به محاسبه نمره z برای امتیاز هر دانش‌آموز بر اساس نتایج میانگین و انحراف میانگین هر کلاس بکنیم. با مقایسه نمرات z دو دانش‌آموز مشخص می‌شود که دانش‌آموز با امتیاز 87% در مقایسه با بقیه دانش‌آموزان کلاس عملکرد بهتری نسبت به عملکرد دانش‌آموزی با امتیاز 95% در مقایسه با دیگر دانش‌آموزان کلاس خودش داشته است.
مقدار آماری نخست که باید محاسبه کنیم میانگین است که در اکسل با استفاده از تابع AVERAGE محاسبه می‌شود. این تابع همه مقادیر را با هم جمع زده و این مجموع را بر تعداد سلول‌هایی که حاوی مقدار عددی هستند تقسیم می‌کند (یعنی سلول‌های خالی را نادیده می‌گیرد).
مقدار آماری بعدی که باید محاسبه کنیم، انحراف معیار است. اکسل دو تابع متفاوت برای محاسبه انحراف معیار دارد که هر یک به روش نسبتاً متفاوتی عمل می‌کنند.
در نسخه‌های قبلی اکسل تنها تابع STDEV وجود داشت که انحراف معیار را طوری محاسبه می‌کرد که داده‌ها به عنوان نمونه‌هایی از یک جمعیت تصور می‌شدند. از نسخه 2010 اکسل به بعد، این تابع به دو تابع تقسیم شد که انحراف معیار را به روش‌های زیر محاسبه می‌کنند:
STDEV.S
این تابع مشابه تابع STDEV قبلی است و انحراف معیار را طوری محاسبه می‌کند که گویی داده‌ها نمونه‌هایی از یک جمعیت هستند. یک نمونه از یک جمعیت می‌تواند چیزی مانند تعدادی پشه باشد که از یک منطقه برای تحقیق گردآوری شده‌اند. یا می‌توان آن را مانند خودروهایی که تصور کرد که کارخانه به منظور تست ایمنی کنار می‌گذارد.
STDEV.P
این تابع انحراف معیار را به صورتی محاسبه می‌کند که داده‌ها به عنوان کل جمعیت در نظر گرفته می‌شوند. منظور از کل جمعیت این است که گویا همه پشه‌های یک منطقه را گردآوری کرده‌ایم و یا همه خودروهای مدل خاص یک کارخانه را در نظر می‌گیریم.
این که از کدام تابع استفاده می‌کنید به مجموعه داده‌های شما بستگی دارد. تفاوت این دو تابع معمولاً اندک است؛ اما نتیجه تابع STDEV.P روی مجموعه داده یکسان، همواره کمتر از نتیجه تابع STDEV.S خواهد بود. این یک رویکرد محافظه‌کارانه‌تر است و تصور می‌شود که داده‌ها تغییرات بیشتری دارند.
بررسی یک مثال
در مثال زیر ما دو ستون به نام‌های «مقادیر» (Values) و «نمره Z» یا (Z-Score) داریم و سه ستون کمکی نیز برای ذخیره‌سازی نتایج تابع‌های AVERAGE، STDEV.S و STDEV.P داریم. ستون مقادیر شامل ده عدد تصادفی است که پیرامون 500 هستند و ستون نمره Z جایی است که نمره‌های Z با استفاده از نتایج ذخیره شده در سلول‌های کمکی محاسبه می‌شوند:
ابتدا میانگین مقادیر با استفاده از تابع AVERAGE محاسبه خواهد شد. سپس سلولی را که نتیجه تابع AVERAGE در آن ذخیره می‌شود بررسی می‌کنیم.
فرمول زیر را وارد کرده و دکمه اینتر را می‌زنیم و یا از منوی «Formulas» استفاده می‌کنیم:
=AVERAGE(E2:E13)
برای دسترسی به تابع از طریق منوی Formulas باید از منوی بازشدنی More Functions، گزینه Statistical را انتخاب کرده سپس روی Average کلیک کنید.
در پنجره Function Arguments همه سلول‌های ستون Values را به عنوان ورودی برای فیلد Number1 انتخاب کنید. لازم نیست در مورد فیلد Number2 کار خاصی انجام دهید.
اینک روی OK کلیک کنید.
سپس باید انحراف معیار مقادیر را با استفاده از تابع‌های STDEV.S یا STDEV.P محاسبه کنید. در این مثال ما روش محاسبه با هر دو تابع را نمایش می‌دهیم. برای شروع از STDEV.S استفاده می‌کنیم. سلولی را که نتیجه در آن ذخیره خواهد شد انتخاب کنید.
برای محاسبه انحراف معیار با استفاده از تابع STDEV.S، فرمول را وارد کرده و اینتر را بزنید (یا از منوی Formulas استفاده کنید)
=STDEV.S(E3:E12)
برای دسترسی از طریق منوی Formulas باید گزینه Statistical را از منوی بازشدنی More Formulas انتخاب کرده و با کمی اسکرول کردن روی دستور STEEV.S کلیک کنید.
در پنجره آرگومان‌های تابع همه سلول‌های را که در ستون Values قرار دارند را به عنوان فیلد ورودی Numer1 انتخاب کنید. فیلد Number2 را خالی بگذارید بماند.
روی OK کلیک کنید.
سپس انحراف معیار را با استفاده از تابع STDEV.P محاسبه می‌کنیم. سلولی را که نتیجه در آن ذخیره خواهد شد انتخاب کنید.
برای محاسبه انحراف معیار با استفاده از تابع STDEV.P، فرمول زیر را وارد کرده و اینتر را بزنید:
=STDEV.P(E3:E12)
برای دسترسی از طریق منوی Formulas باید گزینه Statistical را از منوی بازشدنی More Formulas انتخاب کرده و با کمی اسکرول کردن روی دستور STDEV.S کلیک کنید.
در پنجره آرگومان‌های تابع همه سلول‌های را که در ستون Values قرار دارند را به عنوان فیلد ورودی Numer1 انتخاب کنید. فیلد Number2 را خالی بگذارید بماند.
روی OK کلیک کنید.
اینک که میانگین و انحراف معیار داده‌های خود را محاسبه کردیم، همه موارد مورد نیاز برای محاسبه نمره Z را در اختیار داریم. به این منظور می‌توانیم از فرمول ساده‌ای استفاده کنیم که به سلول‌های حاوی نتایج تابع‌های AVERAGE و STDEV.S یا STDEV.P ارجاع می‌دهد.
سلول نخست را در ستون Z-Score انتخاب کنید. ما در این مثال از تابع STDEV.S استفاده می‌کنیم؛ اما شما می‌توانید از نتایج STDEV.P نیز استفاده کنید:
فرمول زیر را وارد کرده و اینتر را بزنید:
=(E3-$G$3)/$H$3
به طور جایگزین می‌توانید از مراحل زیر نیز به جای وارد کردن فرمول استفاده کنید:
- روی سلول F3 کلیک کرده و عبارت )= را وارد کنید.
- سلول E3 را انتخاب کنید. برای مثال می‌توانید از دکمه سمت چپ کیبورد استفاده کرده یا با ماوس این کار را انجام دهید.
- علامت منها یعنی «-» را در آن وارد کنید.
- سلول G3 را انتخاب کرده و با زدن دکمه F4 کیبورد کاراکترهای $ را اضافه کنید تا یک ارجاع مطلق به سلول ایجاد شود (در صورتی که به زدن دکمه F4 ادامه دهید به ترتیب روی مقادیر “G3” > “$G$3″ > “G$3″ > “$G3″ > “G3” می‌چرخد).
- کاراکتر / را وارد کنید.
- سلول H3 را انتخاب کنید (اگر می‌خواهید از تابع STDEV.P استفاده کنید سلول I3 را انتخاب کنید) و دکمه 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 استفاده شده است:
=(Value-AVERAGE(Values))/STDEV.S(Values)
و فرمول زیر با استفاده از تابع STDEV.P نوشته شده است:
=(Value-AVERAGE(Values))/STDEV.P(Values)
زمانی که محدوده‌های سلول را برای «Values» در تابع‌ها وارد می‌کنیم باید اطمینان حاصل کنیم که ارجاع‌های مطلق یعنی $ را با استفاده از دکمه F4 اضافه کرده‌ایم. بدین ترتیب هنگامی که همه سلول‌ها را پر می‌کنیم میانگین و انحراف معیار محدوده متفاوتی از سلول‌های در هر فرمول محاسبه نمی‌شود.
اگر یک مجموعه داده بزرگ دارید بهتر است از سلول‌های کمکی استفاده کنید، زیرا نتیجه تابع‌های AVERAGE و STDEV.S یا STDEV.P به طور مکرر محاسبه نمی‌شوند و منابع قبلی که صرف این کار شده ذخیره می‌شود و بدین ترتیب زمان مورد نیاز برای محاسبه نتایج کوتاه‌تر می‌شود.
استفاده از $G$3 نیز به فضای کمتری برای ذخیره‌سازی نیاز دارد و از این رو RAM کمتری نسبت به روش (AVERAGE($E$3:$E$12 اشغال می‌شود. این نکته مهمی است زیرا نسخه استاندارد 32 بیتی اکسل محدود به استفاده از 2 گیگابایت RAM است. البته نسخه 64 بیتی اکسل هیچ محدودیتی در مورد میزان RAM مصرفی خود ندارد.
اگر این مطلب برای شما مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شوند:
- آموزش های مجموعه نرم افزاری آفیس
- مجموعه آموزش‌های نرم‌افزار مایکروسافت اکسل
- مجموعه آموزش‌های آمار، احتمالات و داده‌‌کاوی
- توزیع های آماری F و T — مفاهیم و کاربردها
- توزیع نرمال و توزیع نرمال استاندارد — به زبان ساده
- توزیع های آماری — مجموعه مقالات جامع وبلاگ فرادرس
==
