說明
Use this function to calculate the sample standard deviation of a dataset. Can be used with CHILDREFS.
STDEV.S enables you to measure the variability in a dataset when you have a sample of the entire population. This makes it useful for measuring investment risk or analyzing sales performance variability.
Note: STDEV.S assumes that its arguments are a sample of the population. If your data represents the entire population, you should use STDEV.P instead.
語法
STDEV.S(number_1, […, number_254])
Inputs
This function accepts the following arguments:
| 名稱 | 必要 | 說明 | Valid input |
|---|---|---|---|
number_1 |
是 | The first number or range in the sample. | A number, a reference to a cell containing a number, a cell range encompassing cells containing numbers, or a formula which results in any of these. |
[number_n] |
沒有 | Additional sample population values. | A number, a reference to a cell containing a number, or a formula which results in either of these. Up to 254 additional values from within the overall population can be considered. |
範例
範例資料
| A | B |
|---|---|
| 銷售 | 區域 |
| 10000 | 北部 |
| 15000 | 南 |
| 12000 | 東部 |
| 18000 | 西 |
| 13500 | Central |
Sample formulas
| 公式 | 說明 | 結果 |
|---|---|---|
=STDEV.S(A2:A6) |
Calculates the sample standard deviation of all sales figures. | 3130.05 |
=STDEV.S(A2:A4) |
Calculates the sample standard deviation of the first three sales figures. | 2516.61 |
=STDEV.S(A2, A4, A6) |
Calculates the sample standard deviation of non-contiguous sales figures. | 1803.12 |
=STDEV.S(A2:A6)/AVERAGE(A2:A6) |
Calculates the coefficient of variation (relative standard deviation). | 0.2298 (22.98%) |
附註
- STDEV.S assumes that its arguments are a sample of the population. To use the entire population, use STDEV.P.
- Non-numeric values (empty cells, text values, and logical values) are ignored.
- STDEV.S requires at least two numeric values to calculate; otherwise, it returns a #DIV/0! error.
- The function uses the "n-1" method for calculating standard deviation, which is considered more accurate for samples.
- Large datasets or extreme outliers can affect the accuracy of the standard deviation calculation, as the sample set may not accurately represent the complete dataset.
提示
- Be careful when interpreting results from small samples, as they may not accurately represent the population's variability.
- Combine STDEV.S with AVERAGE to calculate the coefficient of variation, which allows you to compare the variability of different datasets.
Related functions
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
LARGE
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
RANK.AVG
RANK.EQ
SMALL
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVPA