یکی از مفاهیم کاربردی در علم آمار، Z-Score است. امتیاز Z یا Z-Score به این معنی است که فاصله‌ی هر داده از مقدار متوسط اعداد یک مجموعه داده چقدر است و این فاصله بر حسب انحراف معیار بیان می‌شود.
برای محاسبه کردن Z-Score در نرم‌افزار Excel می‌بایست از توابع AVERAGE و STDEV.S یا STDEV.P استفاده کرد. در ادامه به روش محاسبه‌ی امتیاز Z در اکسل می‌پردازیم.
آشنایی با مفهوم Z-Score و ارتباط آن با توابع AVERAGE و STDEV.S و STDEV.P
Z-Score یک مفهوم آماری است که برای مقایسه کردن مقادیر دو مجموعه داده استفاده می‌شود. تعریف ریاضی Z-Score به این صورت است: فاصله‌ی عدد از میانگین مجموعه‌ی اعداد تقسیم بر انحراف معیار.
لذا می‌بایست نتیجه‌ی فرمول زیر را در اکسل برای هر یک اعداد محاسبه کنیم:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
به عنوان مثال فرض کنید که می‌خواهیم نمره‌ی دو دانش‌آموز که معلم متفاوتی دارند را مقایسه کنیم. دانش‌آموز اول ۹۵ درصد نمره را در یک کلاس دریافت کرده و دانش‌آموز دوم، ۸۷ درصد نمره را کسب کرده است. در نگاه اول ممکن است به نظر برسد که وضعیت دانش‌آموز اول با کسب نمره‌ی بالاتر، بهتر است اما در واقع ممکن است معلم دوم، امتحان سخت‌تری برگزار کرده باشد. در نتیجه ۸۷ درصد، نتیجه‌ی بهتری باشد.
برای مقایسه کردن نمره‌ی این دانش‌آموزان، می‌توان از مفهوم Z-Score استفاده کرد که در واقع نمره را نسبت به میانگین و برحسب انحراف معیار بیان می‌کند. ممکن است Z-Score دانش‌آموز دوم بالاتر از دانش‌آموز اول باشد.
و اما ارتباط Z-Score با سه تابع در نرم‌افزار Excel: برای محاسبه کردن میانگین اعداد از AVERAGE استفاده می‌شود. این تابع سلول‌های خالی را نادیده می‌گیرد و مقدار متوسط سایر سلول‌ها را محاسبه می‌کند. تابع بعدی STDEV.S است که تا قبل از انتشار Excel 2010، تابع STDEV نامیده شده بود. این تابع انحراف معیار را محاسبه می‌کند و در این محاسبه از نمونه‌ای از جامعه‌ی آماری استفاده می‌شود. تابع مشابه STDEV.P است که انحراف معیار را با در نظر گرفتن تمام اعداد یک مجموعه‌ی داده محاسبه می‌کند و در واقع تمام جامعه‌ی آماری در نظر گرفته می‌شود. لذا حجم محاسبات بیشتر و دقت نیز بیشتر می‌شود.
محاسبه‌ی Z-Score در اکسل
محاسبه‌ی Z-Score را با مثالی ساده توضیح می‌دهیم. فرض کنید مقادیر اولیه که حاصل اندازه‌گیری آماری، نتایج آزمون و غیره هستند، در ستون E با عنوان Values موجود است. برای هر داده می‌خواهیم Z-Score را محاسبه کنیم و در ستون F روبروی هر داده چاپ کنیم. با توجه به اینکه به مقادیر میانگین و انحراف معیار نیاز داریم، جدول کوچکی برای این مقادیر ایجاد می‌کنیم. مثل تصویر زیر:
ابتدا میانگین اعداد را محاسبه می‌کنیم. برای این کار روی سلول G3 کلیک کنید.
و فرمول زیر را تایپ کنید:
=AVERAGE(E2:E13)
البته می‌توانید علامت مساوی را تایپ کنید و چند حرف ابتدای تابع را تایپ کنید تا اکسل توابعی را پیشنهاد بدهد و سپس روی AVERAGE کلیک کنید. به جای تایپ کردن نام سلول‌ها نیز کافی است حین فرمول‌نویسی، روی سلول موردنظر کلیک کنید.
روش دیگر استفاده از تب Formulas در نوار ابزار بالای صفحه است. روی More Functions کلیک کنید و سپس گزینه‌ی Statistical و AVERAGE را انتخاب کنید.
به این ترتیب پنجره‌ای باز می‌شود. در این پنجره می‌توانید در فیلد Number1 کلیک کرده و سپس سلول‌های ستون E را با روش کلیک و درگ، انتخاب کنید. نیازی به پر کردن فیلد دوم یعنی Number2 نیست.
در نهایت روی OK کلیک کنید. قبل از کلیک کردن می‌توانید حاصل محاسبه را نیز پایین بخش ورود اعداد مشاهده کنید.
گام بعدی محاسبه‌ی انحراف معیار با دو تابع اشاره شده است. روی سلول H3 کلیک کنید.
و فرمول زیر را تایپ کنید:
=STDEV.S(E3:E12)
روش مشابه این است که از منوی Formulas و گزینه‌ی More Functions استفاده کنید. انحراف معیار هم تابعی آماری است و در زیرگروه Statistical قرار می‌گیرد. لذا با کمی اسکرول کردن، آن را مشاهده خواهید کرد.
در پنجره‌ی باز شده، روی فیلد Number1 کلیک کنید و مثل تابع قبلی، با کلیک و درگ توسط موس، سلول‌های E3 الی E12 را انتخاب کنید. نیازی به پر کردن دومین فیلد نیست.
روی OK کلیک کنید.
در مورد تابع STDEV.P نیز روش کار کاملاً مشابه است.
می‌توانید از فرمول زیر استفاده کنید و Enter را فشار بدهید:
=STDEV.P(E3:E12)
و همین‌طور از تب Formulas استفاده کنید.
و مثل دو تابع قبلی، صرفاً اولین فیلد را پر کنید.
و روی OK کلیک کنید.
حال نوبت به محاسبه‌ی Z-Score در سلول‌های ستون F می‌رسد. روی سلول F3 کلیک کنید.
و فرمول زیر را تایپ کنید:
=(E3-$G$3)/$H$3
و Enter را فشار بدهید تا Z-Score اولین داده محاسبه شود.
توجه کنید که در فرمول فوق قبل از نام سلول H3 و در واقع نام ستون و ردیف آن، از علامت $ استفاده شده است. به همین ترتیب در مورد G3 عمل شده است. علت این است که با کپی کردن سلول در سلول‌های بعدی، این دو سلول نباید تغییر کنند اما سلول E3 می‌بایست متناسب با ردیف، تغییر کند.
نتیجه را در تصویر زیر مشاهده می‌کنید. اولین داده از میانگین کوچک‌تر است و فاصله‌ی نسبی آن تا میانگین، منفی 0.15945 است. با ضرب کردن این عدد در انحراف معیار که عدد 6.27، به اختلاف معمولی می‌رسید که ۱ واحد است.
برای کپی کردن فرمول در سلول‌های بعدی، یک روش ساده استفاده از مربع کوچکی است که در گوشه‌ی پایین و راست سلول‌ها نمایش داده می‌شود. می‌توانید این مربع را با کلیک و درگ، بگیرید و به پایین بکشید و رها کنید.
روش دیگر انتخاب کردن سلول‌های ستون F به جز سلول عنوان و فشار دادن کلید میانبر Ctrl + D است.
با این روش نیز فرمول در سلول‌های بعدی کپی می‌شود. بخش مربوط به سلول متناظر در ستون E تغییر می‌کند اما سایر بخش‌های فرمول به دلیل وجود علامت $، ثابت و بی‌تغییر می‌مانند.
از محاسبات تکراری در Excel پرهیز کنید!
در نهایت به یک نکته‌ی مهم توجه کنید: در این مقاله برای محاسبه‌ی انحراف معیار و میانگین، سلول‌هایی مجزا در نظر گرفته شده است. اما در واقع می‌توان مستقیماً در فرمولی که در سلول‌های ستون F تایپ می‌شود، از تابع میانگین و تابع انحراف معیار استفاده کرد.
=(Value-AVERAGE(Values))/STDEV.P(Values)
این روش در مورد چند داده مشکل‌ساز نیست اما زمانی که داده‌ها زیاد است، اگر برای محاسبه‌ی هر Z-Score، یک بار میانگین اعداد و انحراف معیار اعداد محاسبه شود، طبعاً حجم محاسبات بسیار زیاد می‌شود. لذا ممکن است در سیستم‌هایی با سخت‌افزار ضعیف، میزان استفاده از RAM و CPU شدیداً بالا برود و کار طولانی شود. لذا توصیه می‌شود که برای چنین محاسبات حجیمی، تا حد ممکن از محاسبات اضافی و بی‌مورد پرهیز کنید.
howtogeekسیاره‌ی آی‌تی