همانطور که می دانید، محاسبات ریاضی زیادی وجود دارد که می توانید با Microsoft Excel انجام دهید . در این آموزش، شما را با مراحل محاسبه Z-Score در اکسل آشنا^(Excel) می‌کنیم.

اگر این اولین باری است که از اکسل^(Excel) استفاده می کنید، توصیه می کنیم پنج دقیقه برای مطالعه این آموزش مبتدی مایکروسافت اکسل^{(Microsoft Excel beginner’s tutorial)} وقت بگذارید . نحوه عملکرد توابع ضروری، میانبرهای پیمایش، ایجاد کتاب های کاری، قالب بندی داده ها و هر آنچه که در مورد استفاده از برنامه صفحه گسترده به عنوان مبتدی باید بدانید را خواهید آموخت.

پس از اتمام کار، به بخش بعدی بروید تا نحوه محاسبه امتیاز Z در اکسل^(Excel) را بیاموزید . اما ابتدا، اجازه دهید Z-Score^(Z-Score) ، کاربردهای آن، چرایی نیاز به محاسبه یک امتیاز و نحوه انجام آن را به طور خلاصه توضیح دهیم .

Z-Score چیست؟

Z-Score (همچنین به عنوان "امتیاز استاندارد" شناخته می شود) معیاری است که رابطه بین مقادیر در یک توزیع را برجسته می کند. به طور دقیق تر، موقعیت مقادیر را در یک مجموعه داده با توجه به میانگین و انحراف معیار آنها توصیف می کند. Z-Score امکان اندازه گیری و مقایسه دقیق مقادیر در یک مجموعه داده را فراهم می کند.

فرمول ریاضی برای محاسبه Z-Score ( (x-µ) / σ است. که در آن x = Cell Value , µ = Mean و σ = Standard Deviation .

گاهی اوقات شرکت ها از Z-Score برای پیش بینی و تخمین ورشکستگی قریب الوقوع استفاده می کنند. علاوه بر این، این یک معیار عالی برای تعیین وضعیت مالی یک موسسه است. محققان^{(Researchers)} همچنین از Z-Score برای مقایسه مشاهدات به دست آمده از نمونه ها یا جمعیت های مختلف استفاده می کنند.

نحوه محاسبه امتیاز Z در اکسل

از آنجایی که Z-Score تابعی از میانگین و انحراف استاندارد است، ابتدا باید میانگین و انحراف استاندارد مجموعه داده خود را محاسبه کنید. اگرچه می‌توانید میانگین و انحراف استاندارد را در هر سلولی استخراج کنید، ما ستون‌های اختصاصی برای «میانگین» و «انحراف استاندارد» در کاربرگ خود ایجاد کردیم. ما همچنین یک ستون برای " Z-Score " ایجاد کردیم.

سند نمونه ما شامل رتبه‌بندی عملکرد 10 کارمند در یک شرکت کاغذی است. حالا بیایید Z-Score امتیاز^(Z-Score) کارمندان را محاسبه کنیم.

میانگین میانگین را محاسبه کنید^{(Calculate Mean Average)}

برای محاسبه میانگین میانگین مجموعه داده خود، =AVERAGE( را تایپ کنید، اولین مقدار^{(first value)} مجموعه داده را انتخاب کنید، کلید ستون را فشار دهید، ^{(column key)}آخرین مقدار را^{(last value)} در محدوده مجموعه داده انتخاب کنید، کلید پرانتز بسته شدن را فشار دهید و ^{(closing parenthesis)}Enter را فشار دهید . فرمول باید به نظر برسد. مانند مورد زیر:

=AVERAGE(B2:B11)

شما باید مقدار میانگین یا میانگین مجموعه داده را در سلولی که فرمول وارد کرده اید ببینید.

محاسبه انحراف استاندارد^{(Calculate Standard Deviation)}

اکسل همچنین محاسبه انحراف استاندارد مجموعه داده شما را^{(calculate the standard deviation of your dataset)} با چند کلیک  بسیار آسان می کند .

یک سلول در ستون "انحراف استاندارد" انتخاب کنید، =STDEV.P( را تایپ کنید، سپس اولین مقدار را^{(first value)} در محدوده انتخاب کنید، کلید ستون^{(column key)} را فشار دهید ، آخرین مقدار را انتخاب کنید، پرانتز بسته شدن^{(closing parenthesis)} را وارد کنید و Enter را فشار دهید . بدون شک، فرمول حاصل باید مشابه فرمول زیر باشد:

=STDEV.P(B2:B11)

محاسبه Z-Score در اکسل: روش 1^{(Calculate Z-Score in Excel: Method 1)}

اکسل^(Excel) دارای یک تابع STANDARDIZE است که Z-Score یک مجموعه داده را در یک توزیع ارائه می کند. اولین سلول در ستون Z-Score را انتخاب کنید و مراحل زیر را دنبال کنید.

1. به تب Formulas بروید و More Formulas را انتخاب کنید .

1. ماوس خود را روی گزینه Statistical نگه دارید و ^{(Statistical)}STANDARDIZE را انتخاب کنید .

با این کار یک پنجره Function Arguments^{(Function Arguments)} راه اندازی می شود که در آن شما می توانید Z-Score توزیع را محاسبه کنید.

1. ^(Enter)مرجع سلول اولین مقدار را در قسمت "X" وارد کنید .

1. ^(Enter)مرجع سلول میانگین حسابی را در قسمت "Mean" وارد کنید و F4 را روی صفحه کلید خود فشار دهید تا مرجع سلول قفل شود.

1. در نهایت، مرجع سلول انحراف استاندارد را در فیلد "Standard_dev" وارد کنید و F4 را فشار دهید تا مرجع سلول قفل شود. این ابزار پیش نمایشی از Z-value را^(Z-value) نمایش می دهد . برای ادامه، OK^(OK) را فشار دهید.

برای دریافت Z-Score برای مقادیر دیگر، مکان نما را به گوشه سمت راست پایین سلول ببرید و plus (+) icon به پایین ستون بکشید.

اکسل^(Excel) فرمول را در پایین ستون کپی می کند و به طور خودکار Z-Score را برای مقادیر دیگر در ردیف های مربوطه ایجاد می کند.

محاسبه Z-Score در اکسل: روش 2^{(Calculate Z-Score In Excel: Method 2)}

همانطور که قبلا ذکر شد، می توانید با کم کردن میانگین مجموعه داده از نقطه داده و تقسیم نتیجه بر انحراف استاندارد، امتیاز Z یک نقطه داده را بدست آورید. با استفاده از (x-µ) / σ، می توانید امتیاز z را در اکسل^(Excel) با وارد کردن دستی این مقادیر محاسبه کنید. 

1. اولین سلول در ستون Z-Score را انتخاب کنید ، علامت مساوی ( = ) و به دنبال آن یک پرانتز باز^{(open parenthesis)} تایپ کنید، و اولین مقدار را در ستون حاوی مجموعه داده هایی که می خواهید Z-Score آنها را محاسبه کنید، انتخاب کنید. پس از آن ، ^(Afterward)خط فاصله^(hyphen) را تایپ کنید، میانگین حسابی را انتخاب کنید، F4 را فشار دهید تا میانگین مطلق/ثابت شود، و نماد بسته شدن پرانتز را فشار دهید. ^{(close parenthesis)}در نهایت، کلید اسلش رو به جلو^{(forward-slash)} ( / ) را فشار دهید، انحراف استاندارد را انتخاب کنید و F4 را فشار دهید تا مرجع سلول قفل شود.

فرمول نهایی باید شبیه به این باشد: =(B2-$C$2)/$D$2 . برای اجرای فرمول Enter را فشار دهید.

توجه داشته باشید که فرمول فقط Z-Score را برای اولین مقدار در سلول انتخاب شده محاسبه می کند.

1. ماوس^(Hover) خود را به گوشه سمت راست پایین اولین سلول Z-Score ببرید و ^(Z-Score)plus (+) icon به پایین ستون بکشید .

تفسیر Z-Score

مجموعه داده شما به احتمال زیاد حاوی ترکیبی از امتیاز Z-Score^(Z-Score) منفی و مثبت خواهد بود. Z-Score مثبت نشان می دهد که مقدار/امتیاز بالاتر از میانگین مجموعه داده است. البته یک Z-Score^(Z-Score) منفی برعکس آن را می گوید: مقدار کمتر از میانگین است. اگر Z-Score یک نقطه داده صفر (0) باشد، به این دلیل است که مقدار آن برابر با میانگین حسابی است.

هرچه یک نقطه داده بزرگتر باشد، Z-Score آن بالاتر است . کاربرگ خود را مرور کنید و متوجه خواهید شد که مقادیر کوچک دارای Z-Score کمتر هستند . به همین ترتیب^(Likewise) ، مقادیر کوچکتر از میانگین حسابی دارای امتیاز Z^(Z-Score) منفی خواهند بود.

به عنوان مثال، در کاربرگ نمونه ما، متوجه خواهید شد که "مایکل" بالاترین امتیاز (78) و بالاترین امتیاز Z^(Z-Score) (1.679659) را داشت. از سوی دیگر، «دوایت» و «کوین» هر دو دارای کمترین امتیاز (34) و کمترین امتیاز Z^(Z-Score) ( -1.59047 ) بودند.

^(Become)به یک متخصص اکسل ^(Excel)تبدیل شوید: مخزن آموزش

اکنون می دانید که چگونه Z-Score یک مجموعه داده را محاسبه کنید. اگر سؤال یا نکات مفید دیگری در مورد محاسبه Z-Score در اکسل^(Excel) دارید، در زیر نظر دهید . 

توصیه می کنیم آموزش های مربوط به اکسل را در مورد محاسبه واریانس^{(calculating variance)} ، حذف ردیف های تکراری^{(removing duplicate rows)} در مجموعه داده خود و نحوه استفاده از توابع خلاصه در اکسل^{(use summary functions in Excel)} برای خلاصه کردن داده ها بخوانید. 

How to Calculate Z-Score in Excel

As you know, there are many mathеmatical calculations you can perform with Mіcrosoft Excel. In this tutorial, we’ll wаlk you through the steps involved іn calculating Z-Score in Excel.

If this is your first time using Excel, we recommend you take five minutes to peruse this Microsoft Excel beginner’s tutorial. You’ll learn how essential functions work, navigation shortcuts, creating workbooks, formatting data, and everything there is to know about using the spreadsheet program as a beginner.

When you’re done with that, proceed to the next section to learn how to calculate Z score in Excel. But first, let’s briefly explain Z-Score, its uses, why you might need to calculate one, and how to get it done.

What Is a Z-Score?

Z-Score (also known as a “Standard Score”) is a metric that highlights the relationship between values in a distribution. More precisely, it describes the position of values in a dataset with respect to their mean and standard deviation. Z-Score allows for accurate measurement and comparison of values in a dataset.

The mathematical formula for calculating Z-Score is (x-µ) / σ; where x = Cell Value, µ = Mean, and σ = Standard Deviation.

Companies sometimes use Z-Score to forecast and estimate impending bankruptcy. Additionally, it’s a great metric for ascertaining the financial position of an institution. Researchers also utilize Z-Score to compare observations obtained from different samples or populations.

How to Calculate Z-Score in Excel

Since Z-Score is a function of mean and standard deviation, you’ll need to first calculate the average and standard deviation of your dataset. Although you can extract the mean and standard deviation in any cell, we created dedicated columns for  “Mean” and “Standard Deviation” in our worksheet. We also created a column for “Z-Score.”

Our sample document contains the performance ratings of 10 employees in a paper company. Now let’s calculate the Z-Score of the employees’ rating.

Calculate Mean Average

To calculate the mean average of your dataset, type =AVERAGE(, select the first value in the dataset, press the column key, select the last value within the dataset range, press the closing parenthesis key, and press Enter. The formula should look like the one below:

=AVERAGE(B2:B11)

You should see the mean or average value of the dataset in the cell you entered the formula.

Calculate Standard Deviation

Excel also makes it pretty easy to calculate the standard deviation of your dataset in a few clicks. 

Select a cell in the “Standard Deviation” column, type =STDEV.P(, then select the first value in the range, press the column key, select the last value, enter the closing parenthesis, and press Enter. If you’re in doubt, the resulting formula should be similar to the one below:

=STDEV.P(B2:B11)

Calculate Z-Score in Excel: Method 1

Excel has a STANDARDIZE function that provides the Z-Score of a dataset in a distribution. Select the first cell in the Z-Score column and follow the steps below.

1. Go to the Formulas tab and select More Formulas.

1. Hover your mouse on the Statistical option and select the STANDARDIZE.

That’ll launch a new Function Arguments window where you’ll be able to calculate the Z-Score of the distribution.

1. Enter the cell reference of the first value in the “X” field.

1. Enter the cell reference of the arithmetic average in the “Mean” field and press the F4 on your keyboard to lock the cell reference.

1. Finally, enter the standard deviation’s cell reference in the “Standard_dev” field and press F4 to lock the cell reference. The tool will display a preview of the Z-value. Press OK to proceed.

To get the Z-Score for other values, hover the cursor to the bottom-right corner of the cell and drag the plus (+) icon down the column.

Excel will copy the formula down the column and automatically generate the Z-Score for other values in the corresponding rows.

Calculate Z-Score In Excel: Method 2

As mentioned earlier, you can obtain a datapoint’s Z-Score by subtracting the mean of the dataset from the datapoint and dividing the result by the standard deviation. Using the (x-µ) / σ, you can calculate z-score in Excel by inputting these values manually. 

1. Select the first cell in the Z-Score column, type an equal sign (=) followed by an open parenthesis, and select the first value in the column containing the datasets whose Z-Score you want to calculate. Afterward, type a hyphen, select the arithmetic mean, press F4 to make the mean absolute/fixed, and press the close parenthesis icon. Finally, press the forward-slash (/) key, select the standard deviation, and press F4 to lock the cell reference.

The final formula should look similar to this: =(B2-$C$2)/$D$2. Press Enter to execute the formula.

Note that the formula will only calculate the Z-Score for the first value in the selected cell.

1. Hover your mouse to the bottom-right corner of the first Z-Score cell and drag the plus (+) icon down the column.

Interpreting Z-Score

Your dataset will most likely contain a mix of negative and positive Z-Scores. A positive Z-Score indicates that the value/score is higher than the dataset’s mean average. A negative Z-Score, of course, tells the opposite: the value lies below the mean average. If a datapoint’s Z-Score is zero (0), that’s because its value equals the arithmetic mean.

The bigger a data point, the higher its Z-Score. Go through your worksheet and you’ll realize that the small values have lower Z-Scores. Likewise, values smaller than the arithmetic mean will have negative Z-Scores.

In our sample worksheet, for instance, you’ll discover that “Michael” had the highest rating (78) and highest Z-Score (1.679659). “Dwight” and “Kevin” on the other hand both had the lowest ratings (34) and lowest Z-Score (-1.59047).

Become an Excel Expert: Tutorial Repository

You now know how to calculate the Z-Score of a data set. Drop a comment below if you have questions or other useful tips regarding calculating Z-Score in Excel. 

We recommend reading Excel-related tutorials on calculating variance, removing duplicate rows in your dataset, and how to use summary functions in Excel to summarize data. 

Related posts

• نحوه حذف خطوط خالی در اکسل

• نحوه استفاده از ویژگی Speak Cells اکسل

• نحوه درج کاربرگ اکسل در Word Doc

• نحوه استفاده از تجزیه و تحلیل What-If اکسل

• نحوه تعمیر یک ردیف در اکسل

• املای سریع اعداد در Word و Excel

• 2 روش برای استفاده از تابع Transpose اکسل

• نحوه استفاده از دستورات If و Nested If در اکسل

• نحوه ایجاد یک Pivot Table ساده در اکسل

• نحوه ادغام داده ها در چندین فایل اکسل

• 3 روش برای تقسیم سلول در اکسل

• با استفاده از ابزار جستجوی هدف تجزیه و تحلیل What-If اکسل

• نحوه استفاده از Flash Fill In Excel

• 4 روش برای استفاده از علامت چک در اکسل

• نحوه مرتب سازی بر اساس تاریخ در اکسل

• تفاوت بین Microsoft Excel Online و Excel For Desktop

• نحوه درج سریع چند ردیف در اکسل

• داده های کاربرگ خود را برای چاپ در اکسل مرکز کنید

• چگونه یک فایل اکسل را برای همکاری آسان به اشتراک بگذاریم

• نحوه ایجاد یک ماکرو یا اسکریپت VBA در اکسل