Sidaad ogtahay, waxaa jira xisaabaadyo badan oo xisaabeed oo aad ku samayn karto Microsoft Excel . Casharradan, waxaan kugu socon doonaa tillaabooyinka ku lug leh xisaabinta Z-Score ee Excel .

Haddii tani ay tahay markii ugu horeysay ee aad isticmaasho Excel , waxaan kugula talineynaa inaad qaadato shan daqiiqo si aad u daawato casharrada bilawga ah ee Microsoft Excel^{(Microsoft Excel beginner’s tutorial)} . Waxaad baran doontaa sida ay u shaqeeyaan shaqooyinka muhiimka ah, hagitaannada gaaban, abuurista buugaag shaqo, habaynta xogta, iyo wax kasta oo ay tahay inaad ka ogaato isticmaalka barnaamijka xaashida bilawga ah.

Markaad taas dhammayso, u gudub qaybta xigta si aad u barato sida loo xisaabiyo dhibcaha Z gudaha Excel . Laakiin marka hore, aan si kooban u sharaxno Z-Score , isticmaalkeeda, sababta aad ugu baahan karto inaad xisaabiso mid, iyo sida loo sameeyo.

Waa maxay buundada Z-ka?

Z-Score (sidoo kale loo yaqaan "Standard Score") waa mitir oo muujinaya xiriirka ka dhexeeya qiyamka qaybinta. Si sax ah, waxa ay qeexaysaa booska qiyamka ee xog-ururinta marka loo eego celceliskooda iyo weecankooda heerka. Z-Score waxay u ogolaataa cabbirka saxda ah iyo isbarbardhigga qiyamka kaydka xogta.

Habka xisaabeed ee lagu xisaabinayo Z-Score waa (x-µ) / σ ; halka x = Cell Value , µ = Mean , iyo σ = Standard Deviation .

Shirkaduhu waxay mararka qaarkood isticmaalaan Z-Score si ay u saadaaliyaan oo ay u qiyaasaan kicitaan soo socda. Intaa waxaa dheer, waa mitir weyn oo lagu hubinayo booska maaliyadeed ee machad. Cilmi-baarayaashu^{(Researchers)} waxay sidoo kale isticmaalaan Z-Score si ay u barbar dhigaan indho-indheynta laga helay muunado ama dad kala duwan.

Sida loo xisaabiyo Z-Score gudaha Excel

Mar haddii Z-Score ay tahay shaqo celcelis ahaan iyo weecan heerka caadiga ah, waxaad u baahan doontaa inaad marka hore xisaabiso celceliska iyo weecanka caadiga ah ee xogtaada. In kasta oo aad ka soo saari karto celceliska iyo leexashada heerka unug kasta, waxaan ku abuurnay tiirar u go'ay "Mean" iyo "Standard Deviation" xaashida shaqada. Waxaan sidoo kale u abuurnay tiir loogu talagalay " Z-Score ."

Dukumeenti muunadeedkayagu waxa uu ka kooban yahay qiimaynta waxqabad ee 10 shaqaale ah oo ka tirsan shirkadda waraaqaha ah. Hadda aynu xisaabino Z-Score ee qiimaynta shaqaalaha.

Xisaabi celceliska celceliska^{(Calculate Mean Average)}

Si aad u xisaabiso celceliska celceliska xogtaada, nooca =AVERAGE( , dooro qiimaha kowaad^{(first value)} ee xogta kaydinta, riix furaha tiirka^{(column key)} , dooro qiimaha u dambeeya^{(last value)} ee xadka xog-ururinta, riix furaha xidhitaanka^{(closing parenthesis)} , oo taabo Gelida^(Enter) . Qaaciddu waa inay eegtaa. sida kan hoose:

=AVERAGE(B2:B11)

Waa inaad ku aragtaa celceliska ama celceliska qiimaha xogta kaydinta unugga aad galisay qaacidada.

Xisaabi leexashada caadiga ah^{(Calculate Standard Deviation)}

Excel sidoo kale wuxuu ka dhigayaa mid aad u fudud in lagu xisaabiyo weecanka caadiga ah ee xogtaada^{(calculate the standard deviation of your dataset)} dhowr dhagsi. 

Ka dooro unug ku jira tiirka “Standard Deviation”, nooca =STDEV.P( , ka dibna dooro qiimaha ugu horreeya^{(first value)} ee kala duwan, taabo furaha tiirka^{(column key)} , door qiimaha ugu dambeeya, geli khaanadda xidhitaanka^{(closing parenthesis)} , oo taabo Gelida^(Enter) . Haddii aad tahay Shaki ku jiro, caanaha natiijadu waa inay la mid noqotaa tan hoose:

=STDEV.P(B2:B11)

Ku xisaabi Z-Score gudaha Excel: Habka 1^{(Calculate Z-Score in Excel: Method 1)}

Excel waxa uu leeyahay shaqo STANDARDIZE ah oo bixisa Z-Score ee xogta qaybinta. Dooro unugga koowaad ee tiirka Z-Score oo raac tillaabooyinka hoose.

1. Tag Formulas tab oo dooro Formulas More^{(More Formulas)} .

1. Hogaami mouse-kaaga xulashada Tirakoobka^{(Statistical)} oo dooro HEERKA^{(STANDARDIZE)} .

Taasi waxay bilaabi doontaa daaqad cusub oo Doodaha Shaqada^{(Function Arguments)} halkaas oo aad awoodi doonto inaad xisaabiso Z-Score ee qaybinta.

1. Geli^(Enter) tixraaca unugga ee qiimaha ugu horreeya ee goobta "X".

1. Geli^(Enter) tixraaca unugga ee celceliska xisaabta ee goobta "Mean" oo taabo F4 ee kumbuyuutarkaaga si aad u xirto tixraaca unugga.

1. Ugu dambeyntii, geli tixraaca unugga heerka weecasho ee goobta "Standard_dev" oo taabo F4 si aad u xirto tixraaca unugga. Qalabku wuxuu soo bandhigi doonaa horudhac ku saabsan qiimaha-Z^(Z-value) . Riix OK si aad u sii waddo.

Si aad u hesho Z-Score ee qiyamka kale, u rog cursorka dhinaca hoose ee midig ee unugga oo jiid calaamadda plus (+) icon hoos tiirka.

Excel wuxuu koobi doonaa qaacidada hoos tiirka wuxuuna si toos ah u soo saari doonaa Z-Score ee qiimayaasha kale ee safafka u dhigma.

Ku xisaabi Z-Score gudaha Excel: Habka 2^{(Calculate Z-Score In Excel: Method 2)}

Sidaan hore u soo sheegnay, waxaad ku heli kartaa dhibcaha Z-Score adiga oo ka jaraya celceliska kaydka xogta barta xogta oo aad natiijada u qaybisid jaangooyooyinka caadiga ah. Isticmaalka (x-µ) / σ, waxaad ku xisaabin kartaa z-score gudaha Excel adiga oo qiyamkan gacanta ku gelinaya. 

1. Dooro unugga koowaad ee tiirka Z-Score , ku qor calaamad siman ( = ) oo ay ku xigto khaanad furan^{(open parenthesis)} , oo dooro qiimaha ugu horreeya ee tiirka ay ku jiraan kaydka Z-Score ee^(Z-Score) aad rabto inaad xisaabiso. Dabadeed^(Afterward) , ku qor jihaynta^(hyphen) , dooro macnaha xisaabta, taabo F4 si aad u samayso celceliska saxan/go'an, oo taabo summada qawlka dhow^{(close parenthesis)} . Ugu dambayntii, taabo furaha hore-slash^{(forward-slash)} ( / ), dooro weecanka caadiga ah, oo tabo F4^(F4) si aad u xidho tixraaca unugga.

Qaaciddada kama dambaysta ahi waa inay u ekaataa tan: =(B2-$C$2)/$D$2 . Taabo Gelida^(Enter) si aad ufuliso qaacidada

Ogsoonow in qaacidada kaliya ay xisaabin doonto Z-Score qiimaha ugu horreeya ee unugga la doortay.

1. U rog jiirkaaga^(Hover) dhinaca midig ee hoose ee unugga ugu horreeya ee Z-Score oo jiid calaamadda plus (+) icon hoos tiirka.

Turjumaada Z-Score

Xogtaagu waxay u badan tahay inay ka koobnaan doonaan isku dhafka taban iyo buundooyinka Z-score . Dhibcaha Z-^(Z-Score) togan ayaa tilmaamaya in qiimaha/dhibcaha uu ka sarreeyo celceliska celceliska xogta. Dhibcaha Z-dabiiciga^(Z-Score) ah, dabcan, wuxuu sheegayaa liddi ku ah: qiimihiisu wuxuu ka hooseeyaa celceliska celceliska. Haddii dhibicda xogta Z-Score ay tahay eber (0), taasi waa sababta oo ah qiimaheedu wuxuu la mid yahay celceliska xisaabta.

Dhibicda xogeed ee weyn, ayaa sareeysa Z-Score . Ku gudub xaashida shaqada oo waxaad ogaan doontaa in qiimayaasha yar yar ay leeyihiin buundooyinka Z-Score . Sidoo kale^(Likewise) , qiyamka ka yar celceliska xisaabta waxay yeelan doonaan dhibco Z-score ah.

Tusaale ahaan xaashida shaqada ee tusaalaha ah, waxaad ogaan doontaa in “Michael” uu lahaa qiimaynta ugu sareysa (78) iyo Z-Score (1.679659). "Dwight" iyo "Kevin" dhanka kale labaduba waxay lahaayeen qiimeynta ugu hooseeya (34) iyo ugu hooseeya Z-Score ( -1.59047 ).

Noqo^(Become) Khabiir Excel : Kaydka Waxbarashada

Hadda waxaad taqaanaa sida loo xisaabiyo Z-Score ee xogta set. Hoosta faallooyin ku rid haddii aad hayso su'aalo ama talooyin kale oo waxtar leh oo ku saabsan xisaabinta Z-Score gudaha Excel . 

Waxaan kugula talineynaa inaad akhrido casharrada la xiriira Excel ee xisaabinta kala duwanaanshiyaha^{(calculating variance)} , ka saarida safafka nuqulka ah^{(removing duplicate rows)} ee ku jira xogtaada, iyo sida loo isticmaalo shaqooyinka kooban ee Excel^{(use summary functions in Excel)} si loo soo koobo xogta. 

How to Calculate Z-Score in Excel

As you know, there are many mathematicаl calculations you can perform with Microsoft Excеl. In this tutorial, we’ll walk you through the steps involvеd in 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

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida loo isticmaalo Excel's What-If Analysis

• Sida Loo Hagaajiyo Safka gudaha Excel

• Sida loo qoro IF Formula/Bayaanka gudaha Excel

• 3 Siyood Oo Unugyada Excel Loogu Kala Qaybiyo

• Sida Loo Mideeyo Unugyada, Tiirarka & Safafka gudaha Excel

• 2 siyaabood oo loo Isticmaalo Shaqada Transpose ee Excel

• Sida Loo Sameeyo Liisaska Hoos-u-dhaca ee Isku Xirka Badan ee Excel

• Sida Loo Raadiyo Excel

• Sida loo Hagaajiyo #N/A Khaladaadka gudaha Foomamka Excel sida VLOOKUP

• Sida Looga Saaro Safka Kooban Ee Excel

• Sida Loo Geliyo CSV ama TSV Xaashida Shaqada ee Excel

• Sida loo isticmaalo Flash Fill ee Excel

• Sida Loo Abuuro VBA Macro Ama Script gudaha Excel

• Sida Loo Sameeyo Shaxda Pivot Fudud ee Excel

• Google Sheets vs Microsoft Excel - Waa maxay faraqa u dhexeeya?

• Hal-Column iyo Xog-urur badan oo Aasaaska ah oo ku kala soocida xaashida faafinta ee Excel

• Sida Loogu Mideeyo Xogta Faylal Badan oo Excel ah