Markaa waxaa lagu waydiistay inaad xisaabiso kala duwanaanshiyaha adigoo isticmaalaya Excel , laakiin ma hubtid waxa ay taasi ka dhigan tahay ama sida loo sameeyo. Ha werwerin, waa fikrad fudud oo xataa hawl fudud. Waxaad noqon doontaa kala duwanaansho waqti ka dib!

Waa maxay Kala duwanaanshuhu?

" Kala duwanaansho^(Variance) " waa hab lagu cabbiro celceliska fogaanta celceliska. "macnaha" waa wadarta dhammaan qiyamka ku jira xog-ururinta oo loo qaybiyay tirada qiyamka. Kala duwanaanshuhu^(Variance) wuxuu ina siinayaa fikrad ah in qiyamka xogtaas la dhigay ay u janjeeraan, celcelis ahaan, inay si isku mid ah ugu dhegganaadaan celceliska ama ay ku kala firdhiyaan meel kasta.

Xisaab ahaan, kala duwanaanshuhu maaha mid adag:

1. Xisaabi celceliska go'an qiyamka. Si loo xisaabiyo celceliska, qaado wadarta dhammaan qiimayaasha oo loo qaybiyay tirada qiimayaasha.
2. Qaado qiime kasta oo ku jira jaan-goyntaada oo ka jar celceliska.
3. Laba^(Square) jibbaaran qiyamka ka dhashay (si aad u tirtirto tirooyinka taban).
4. Isku dar^(Add) dhammaan qiimayaasha labajibbaaran.
5. Xisaabi celceliska qiyamka labajibbaaran si aad u hesho kala duwanaanshiyaha.

Markaa sida aad arki karto, ma aha qiimo adag in la xisaabiyo. Si kastaba ha ahaatee, haddii aad leedahay boqollaal ama kun oo qiyam ah, waxay ku qaadan doontaa weligeed inaad gacanta ku qabato. Markaa waa wax fiican in Excel uu si otomaatig ah u samayn karo hawsha!

Maxaad u isticmaashaa Kala duwanaanshaha?

Kala duwanaanshuhu laftiisu waxa uu leeyahay tiro adeegsi. Marka loo eego dhinaca tirakoobka oo kaliya, waa hab wanaagsan oo gaabni ah oo lagu muujinayo sida loo faafiyay xogta. Maalgashadayaashu waxay isticmaalaan kala duwanaansho si ay u qiyaasaan khatarta maalgashi la bixiyay.

Tusaale ahaan, adiga oo qaadanaya qiimaha saamiyada^{(stock’s value)} in muddo ah oo aad xisaabiso kala duwanaanshaheeda, waxa aad fikrad fiican ka heli doontaa isbeddelkeeda hore. Marka la eego malo-awaalka ah in wixii hore ay saadaaliyaan mustaqbalka, waxay la macno tahay in wax kala duwanaansho yar yahay uu ka badbaado badan yahay oo la sii saadaalin karo.

Waxa kale oo aad is barbar dhigi kartaa kala duwanaanshaha shay wakhtiyo kala duwan. Tani waxay kaa caawin kartaa in la ogaado marka arrin kale oo qarsoon ay saameynayso wax, beddeleysa kala duwanaanshaheeda.

Kala duwanaanshuhu waxa kale oo uu si xooggan ula xidhiidhaa tirokoob kale oo loo yaqaan weecsanaanta caadiga ah. Xasuusnoow^(Remember) in qiyamka loo isticmaalo xisaabinta kala duwanaanshiyaha ay laba jibaaran yihiin. Tani waxay ka dhigan tahay in kala duwanaanshiyaha aan lagu muujin isla halbeegga qiimihii asalka ahaa. weecasho heerku waxa uu u baahan yahay in la qaato xididka labajibbaaran ee kala duwanaanshiyaha si loo soo celiyo qiimihii unugtiisii ​​asalka ahayd. Markaa haddii xogtu ahayd kiiloogaraam markaa weecaadda caadiga ahi waa sidoo kale.

Kala duwanaanshaha Tusaalaha ee u dhexeeya Dadweynaha^{(Between Population)} iyo Tusaalaha^{(Sample Variance)}

Waxaa jira laba nooc oo kala duwanaansho ah oo leh habab yar oo kala duwan oo Excel ah . Midkee^{(Which one)} ay tahay inaad doorato waxay kuxirantahay xogtaada. Haddii xogtaadu ay ku jirto "dadweynaha" oo dhan markaa waa inaad isticmaashaa kala duwanaanshaha dadweynaha. Xaaladdan "dad" macnaheedu waa inaad qiimo kasta u leedahay xubin kasta oo ka mid ah kooxda dadka la beegsanayo. 

Tusaale ahaan, haddii aad eegayso miisaanka dadka bidixda leh, markaa dadka waxaa ku jira shakhsi kasta oo Dunida ku nool oo gacanta bidix ah. Haddii aad wada miisaanto, waxaad isticmaali lahayd kala duwanaanshaha dadweynaha. 

Dabcan, nolosha dhabta ah waxaan caadi ahaan dejinaa muunad yar oo ka timid dad badan. Xaaladdaas oo aad isticmaali lahayd kala duwanaanshiyaha muunad. Kala duwanaanshaha dadku^(Population) wali waa wax la taaban karo marka loo eego dad yar. Tusaale ahaan, shirkaddu waxay yeelan kartaa dhawr boqol ama dhawr kun oo shaqaale ah oo xog ku saabsan shaqaale kasta. Waxay u taagan yihiin "dad" macnaha tirakoobka.

Doorashada Qaanuunka Kala Duwanaanshaha Saxda ah

Waxaa jira saddex nooc oo muunad ah oo kala duwanaansho ah iyo saddex nooc oo kala duwanaansho dadweyne ah gudaha Excel:

• VAR , VAR.S iyo VARA ee kala duwanaanshaha muunada.
• VARP , VAR.P iyo VARPA ee kala duwanaanshaha dadweynaha.

Waad iska indho tiri kartaa VAR iyo VARP . Kuwaani waa duugoobay oo kaliya waxay ku dhow yihiin waafaqid xaashida dhaxalka ah.

Taasi waxay ka tagaysaa VAR.S^(VAR.S) iyo VAR.P , kuwaas oo loogu talagalay xisaabinta kala duwanaanshaha qiimaha tirooyinka iyo VARA iyo VARPA , oo ay ku jiraan xargaha qoraalka.

VARA iyo VARPA waxay u rogi doonaan xadhig kasta oo qoraal ah qiimaha nambarada 0, marka laga reebo "RUN" iyo "BEEN". Kuwaas waxaa loo rogaa 1 iyo 0 siday u kala horreeyaan.

Farqiga ugu weyn ayaa ah in VAR.S iyo VAR.P ay ka boodaan wax kasta oo aan tiro ahayn. Tani waxay ka saaraysaa kiisaskaas wadarta tirada qiimaha, taas oo macnaheedu yahay celceliska qiimaha wuu ka duwanaan doonaa, sababtoo ah waxaad u qaybinaysaa tiro yar oo kiisas ah si aad u hesho celceliska.

Sida loo xisaabiyo kala duwanaanshaha Excel

Waxa kaliya ee aad u baahan tahay si aad u xisaabiso kala duwanaanshaha Excel waa go'an qiyam ah. Waxaan u adeegsan doonaa VAR.S^(VAR.S) tusaalaha hoose, laakiin qaacidada iyo hababku waa isku mid iyadoon loo eegin qaacidada kala duwanaanshaha ee aad isticmaasho:

1. Haddii aad u malaynayso in aad diyaarsan tahay kala duwanaansho ama go'an gaar ah, dooro unugga madhan^{(empty cell)} ee aad dooratay.

1. Meesha qaacidooyinka, ku qor =VAR.S(XX:YY) halkaasoo qiyamka X iyo Y lagu beddelo tirooyinka unugga ugu horreeya iyo kuwa ugu dambeeya.

1. Riix Gelida^(Enter) si aad u dhamaystirto xisaabinta.

Haddii kale, waxaad qeexi kartaa qiyam gaar ah, markaas oo qaacidadu u egtahay =VAR.S(1,2,3,4) . Iyadoo tirooyinka lagu bedelay wax kasta oo aad u baahan tahay si aad u xisaabiso kala duwanaanshiyaha. Waxaad gacanta ku geli kartaa ilaa 254 qiyam oo sidan oo kale ah, laakiin ilaa aad haysato sacab muggiis oo qiyam ah, had iyo jeer way fiican tahay inaad xogtaada geliso meel kala duwan oo unug ah ka dibna aad isticmaasho nooca tirada unugyada ee qaacidada aan kor ku soo hadalnay.

Waxaad ka heli kartaa Excel at, Er, Excel

Xisaabinta kala duwanaanshuhu waa khiyaamo faa'iido leh oo lagu ogaanayo qof kasta oo u baahan inuu sameeyo xoogaa shaqo ah oo tirakoob ah gudaha Excel . Laakiin haddii mid ka mid ah ereyada Excel ee aan ku isticmaalnay maqaalkan uu yahay mid jahawareer leh, tixgeli inaad hubiso Barashada Aasaasiga ah ee Microsoft Excel - Barashada Sida loo Isticmaalo Excel^{(Microsoft Excel Basics Tutorial – Learning How to Use Excel)} .

Haddii, dhanka kale, aad diyaar u tahay wax badan, hubi Ku dar Khadka Dib-u-celinta Tooska ah ee Qorshaha Scatter Excel^{(Add a Linear Regression Trendline to an Excel Scatter Plot)} si aad u sawirto kala duwanaansho ama dhinac kasta oo kale oo xogtaada ah ee la xidhiidha macnaha xisaabta.

How to Calculate Variance in Excel

Ѕo you’ve been asked to calculate variance using Excel, but you aren’t sure what that means or how to do it. Don’t worry, it’s an eаsy conceрt and even easier process. You’ll be a variance pro in no time!

What Is Variance?

“Variance” is a way to measure the average distance from the mean. The “mean” is the sum of all values in a dataset divided by the number of values. Variance gives us an idea of whether the values in that data set tend, on average, to stick uniformly to the mean or scatter all over the place.

Mathematically, variance isn’t that complex:

1. Calculate the mean of a set of values. To calculate the mean, take the sum of all the values divided by the number of values.
2. Take every value in your set and subtract it from the mean.
3. Square the resulting values (to cancel out negative numbers).
4. Add all the squared values together.
5. Calculate the mean of the squared values to get the variance.

So as you can see, it’s not a hard value to calculate. However, if you have hundreds or thousands of values, it would take forever to do manually. So it’s a good thing that Excel can automate the process!

What Do You Use Variance For?

Variance by itself has a number of uses. From a purely statistical perspective, it’s a good shorthand way to express how spread out a set of data is. Investors use variance to estimate the risk of a given investment.

For example, by taking a stock’s value over a period of time and calculating its variance, you’ll get a good idea of its volatility in the past. Under the assumption that the past predicts the future, it would mean that something with low variance is safer and more predictable.

You can also compare the variances of something across different time periods. This can help detect when another hidden factor is influencing something, changing its variance.

Variance is also strongly-related to another statistic known as the standard deviation. Remember that the values used to calculate variance are squared. This means that variance is not expressed in the same unit of the original value. The standard deviation requires taking the square root of variance to return the value to its original unit. So if the data was in kilograms then the standard deviation is as well.

Choosing Between Population and Sample Variance

There are two subtypes of variance with slightly different formulas in Excel. Which one you should choose depends on your data. If your data includes the entire “population” then you should use population variance. In this case “population” means that you have every value for every member of the target population group. 

For example, if you’re looking at the weight of left-handed people, then the population includes every individual on Earth who’s left-handed. If you’ve weighed them all, you’d use population variance. 

Of course, in real life we usually settle for a smaller sample from a larger population. In which case you’d use sample variance. Population variance is still practical with smaller populations. For example, a company may have a few hundred or few thousand employees with data on each employee. They represent a “population” in the statistical sense.

Choosing the Right Variance Formula

There are three sample variance formulas and three population variance formulas in Excel:

• VAR, VAR.S and VARA for sample variance.
• VARP, VAR.P and VARPA for population variance.

You can ignore VAR and VARP. These are outdated and are only around for compatibility with legacy spreadsheets.

That leaves VAR.S and VAR.P, which are for calculating the variance of a set of numerical values and VARA and VARPA, which include text strings.

VARA and VARPA will convert any text string to the numerical value 0, with the exception of “TRUE” and “FALSE”. These are converted to 1 and 0 respectively.

The biggest difference is that VAR.S and VAR.P skip over any non-numerical values. This excludes those cases from the total number of values, which means the mean value will be different, because you’re dividing by a smaller number of cases to get the mean.

How to Calculate Variance in Excel

All you need to calculate variance in Excel is a set of values. We’re going to use VAR.S in the example below, but the formula and methods are exactly the same regardless of which variance formula you use:

1. Assuming you have a range or discrete set of values ready, select the empty cell of your choice.

1. In the formula field, type =VAR.S(XX:YY) where the X and Y values are replaced by the first and last cell numbers of the range.

1. Press Enter to complete the calculation.

Alternatively, you can specify specific values, in which case the formula looks like =VAR.S(1,2,3,4). With the numbers replaced with whatever you need to calculate the variance of. You can enter up to 254 values manually like this, but unless you only have a handful of values it’s almost always better to enter your data in a cell range and then use the cell range version of the formula discussed above.

You Can Excel at, Er, Excel

Calculating variance is a useful trick to know for anyone who needs to do some statistical work in Excel. But if any of the Excel terminology we used in this article was confusing, consider checking out Microsoft Excel Basics Tutorial – Learning How to Use Excel.

If, on the other hand, you’re ready for more, check out Add a Linear Regression Trendline to an Excel Scatter Plot so you can visualize variance or any other aspect of your data set in relation to the arithmetic mean.

Related posts

• Kudar Khadka Dib-u-Celinta Toosan ee Qorshaha Kala firdhisan ee Excel

• Sida loo sameeyo Histogram gudaha Excel

• Sida loo xafido erayga sirta ah ee Excel File

• Sida Loo Sameeyo Jadwalka socodka gudaha Word iyo Excel

• Sida Loo Sameeyo Calaamadaha Erayga laga bilaabo xaashida faafinta ee Excel

• Adeegso Excel si aad u ogaato dulsaarka waxtarka leh ee ka imanaya heerka dulsaarka magaca ah

• Sida Loo Bedelo Taariikhda Kooxda Microsoft

• Isticmaal Hawlaha Kooban si aad u soo koobto Xogta ku jirta Excel

• Sida loola socdo isbedelada Excel

• Sida loo shaandheeyo xogta gudaha Excel

• Sida loo sameeyo garaafka fudud ama jaantuska gudaha Excel

• Sida Si Toos ah Loogu Soo Kabto Dukumeenti Word OneDrive

• Sida loo saaro, loo jebiyo, ama loo jebiyo erayga sirta ah ee Excel XLS ee la iloobay

• Geli xaashida shaqada ee Excel Word Doc

• Sida Loogu Beddelayo Inta u dhaxaysa Waraaqaha Shaqada ee Excel

• Sida loo Kooxeeyo Waraaqaha Shaqada ee Excel

• U isticmaal Excel Qalab ahaan si aad xogta uga koobbiso Shabakadda

• Sida loo isticmaalo Sparklines gudaha Excel

• Adeegso Excel Mobile's cusub ee shaqada "Ka Geli Xogta Sawirka".

• Sida Loo Abuuro Liiska Qaybinta Muuqaalka