在统计学中，标准差是衡量一组数据相对于其平均值的分散程度。简单来说，它告诉您数据点的集合是如何“分散”的。
这对于了解教室中学生成绩的差异或测量某物的温度随时间波动的幅度等事情很有用。它尤其可以帮助您了解可能共享相同平均值的两个数据集之间的差异。
就像两个班级的学生具有相同的基本总体平均成绩，但有一些学生可能在一个班级而不是另一个班级做得更差（或更好）。
在数学上，这是通过取数据集方差的平方根来计算的。在本文中，您将学习如何在Excel中计算标准差。
标准差的典型用途
在 Excel(manipulate data in Excel)中操作数据的方法有很多种，标准差函数只是您可以使用的一种更强大的工具。
人们通常什么时候使用标准差计算？实际上，将其用作许多不同行业的数据分析形式是很常见的。(a form of data analysis)
一些例子包括：
- 人口研究(Population studies)：健康(Health)研究人员可能不仅对确定男性和女性之间代谢率的差异感兴趣，而且对这两组之间代谢率的差异感兴趣。
- 科学证据(Scientific evidence)：跨实验的测量结果与平均值相差较小的结果通常比变化很大的测量结果表明更有力的证据。
- 工业质量(Industrial quality)：衡量从生产线上下来的产品的尺寸或质量是否变化可以表明该机器在可接受的规格范围内生产产品的情况。
- 财务风险(Financial risk)：股票分析师使用标准差来衡量股票(value of stocks)或其他资产的价值变化幅度，这可以表明投资是否有风险。
如何在Excel 中计算(Excel)标准差(Standard Deviation)
无论您为什么需要计算数据集的标准差，Excel都可以让您轻松完成。
您可以在Excel(Excel)中计算两种形式的标准差。
- 样本标准偏差(Sample standard deviation)：使用来自较大总体样本的单个数据集。
- 人口标准差(Population standard deviation)：使用来自整个人口的所有数据集。
在大多数情况下，不可能使用来自整个人群的数据（例如测量女性的代谢率），因此更常见的是使用样本标准差，然后推断整个人群的结果。
Excel中可用的六个标准差公式包括：
- STDEV.S：数值数据集的标准差
- STDEVA：数据集的标准差，包括“False”或 0 等文本字符
- STDEV：与STDEV.S相同，但用于在Excel 2007或更早版本中创建的电子表格
STDEV.P、STDEVPA和STDEVP函数都执行与上述函数相同的方式，但使用来自整个群体而不是样本的数据集。
如何使用STDEV.S和STDEV.P 函数(STDEV.P Function)
在Excel(Excel)中使用标准差函数相当简单。您只需为该函数提供整个数据集。
在以下示例中，我们将获取纽约学校(New York)SAT成绩的政府数据集，并确定数学成绩的标准差。
由于包含数学分数的数据集在 D2 到D461的范围内，因此只需选择您希望标准偏差去的任何单元格并键入：
=STDEV.P(D2:D461)
按Enter完成输入公式。您会看到整个数据总体的标准差为 64.90674。
现在，假设您没有该州所有学校的完整数据集，但您仍想对 100 所学校的样本进行标准差，您可以使用它来推断关于所有学校的结论。
这不会那么准确，但它仍然应该让你了解真相。
由于包含数学分数的数据集在 D2 到D102的范围内，因此只需选择您希望标准差到达的任何单元格并键入：
=STDEV.S(D2:D102)
按Enter完成输入公式。您会看到这个较小数据样本的标准差是 74.98135。
这是一个很好的例子，说明您可以通过更大的样本量获得更准确的图片。例如，对 200 所学校的样本大小使用相同的STDEV.S公式返回 68.51656，这更接近于整个数据总体的实际标准差。
如何使用STDEVA Excel 函数(STDEVA Excel Function)
标准差函数STDEVA很少使用，因为人们使用的大多数数据集都只填充了数字数据。但是您可能会遇到数据中存在文本值的情况。
这就是 STDEVA 处理文本数据的方式。
- TRUE 评估为 1
- FALSE 评估为 0
- 任何其他文本评估为 0
这可能有价值的一个例子是，如果您在一台机器上安装了一个传感器，可以测量高于 0摄氏度(Celsius)的液体温度。
您可以对传感器进行编程，以便如果温度探头断开连接，它会将“FALSE”写入数据流。当您在Excel中执行标准偏差计算时，在计算标准偏差之前，那些“FALSE”数据读数将在数据集中转换为 0。
公式为：
=STDEVA(C2:C100)
完成后按 Enter(Press Enter)。在这种情况下，结果是 4.492659。这意味着不到 100 个点的整个样本数据集与整体平均值相差不到 5 度。
该结果将“FALSE”数据读数考虑为具有 0 度的值。
就像STDEV.S函数的情况一样，如果您有一个包含文本条目的整个数据群，您可以使用STEVPA函数来计算该总体的标准偏差。
请记住(Remember)，如果您使用的是没有其他可用标准差函数的旧版Excel ，您仍然可以使用(Excel)STDEV和STDEVP，它们在Excel中计算标准差的方式与上述示例相同。但是，这些功能不能使用文本或逻辑数据。
请(Make)务必查看我们使用 Excel 的其他有用提示和技巧(tips and tricks for using Excel)。并在下面的评论部分分享您自己对标准差函数的应用。
How To Calculate Standard Deviation In Excel
In statistics, standard deviation is a measure of how dispersed a set of data iѕ relative to its mean. In simple terms, it tellѕ you hоw “spread out” a collection of data points are.
This is useful for things like understanding how varied student grades are in a classroom, or measuring how widely the temperature of something is fluctuating over time. It can especially help you understand the differences between two datasets that may share the same average.
Like two classrooms of students that have the same basic overall average grade, but with a few students that may be doing far worse (or far better) in one classroom and not the other.
Mathematically, this is calculated by taking the square root of the dataset’s variance. In this article you’ll learn how to calculate standard deviation in Excel.
Typical Uses For Standard Deviation
There are many ways to manipulate data in Excel, and the standard deviation functions are just one more powerful tool available to you.
When do people normally use the standard deviation calculation? It’s actually quite common to use this as a form of data analysis across many different industries.
A few examples include:
- Population studies: Health researchers may not only be interested in determining the difference in metabolic rates between men and women, but also how much those rates vary between those two groups.
- Scientific evidence: Measurements across experiments with results that vary less from the mean usually indicate stronger evidence than measurements that vary wildly.
- Industrial quality: Measuring whether the size or quality of a product that comes off a production line varies can indicate how well that machine is producing a product within acceptable specifications.
- Financial risk: Stock analysts use standard deviation to measure how much the value of stocks or other assets vary, which can indicate whether an investment is risky or not.
How To Calculate Standard Deviation In Excel
Regardless why you may need to calculate the standard deviation of a dataset, Excel makes it extremely easy to do so.
There are two forms of standard deviation you can calculate in Excel.
- Sample standard deviation: Uses a single dataset from a sample of a larger population.
- Population standard deviation: Uses all datasets from the entire population.
In most cases, it isn’t possible to use data from an entire population (such as measuring metabolic rate in females), so it’s much more common to use sample standard deviation and then infer the results across the entire population.
The six standard deviation formulas available in Excel include:
- STDEV.S: Standard deviation of a numeric dataset
- STDEVA: Standard deviation of a dataset including text characters like “False” or 0
- STDEV: Same as STDEV.S but used in spreadsheets created in Excel 2007 or earlier
STDEV.P, STDEVPA, and STDEVP functions all perform the same way as the function above but utilize datasets from an entire population rather than a sample.
How To Use The STDEV.S and STDEV.P Function
Using standard deviation functions in Excel is fairly straightforward. You just need to provide the function with the entire dataset.
In the following example, we’ll take a government dataset of SAT scores for New York schools and determine the standard deviation of math scores.
Since the dataset containing the math scores is in the range from D2 through D461, just pick any cell where you want the standard deviation to go and type:
=STDEV.P(D2:D461)
Press Enter to finish entering the formula. You’ll see that the standard deviation for the entire population of data is 64.90674.
Now, imagine that you don’t have the entire dataset for all schools in the state, but you still want to take a standard deviation of a sample of 100 schools that you can use to infer conclusions about all schools.
This won’t be quite as accurate, but it should still give you an idea of the truth.
Since the dataset containing the math scores is in the range from D2 through D102, just pick any cell where you want the standard deviation to go and type:
=STDEV.S(D2:D102)
Press Enter to finish entering the formula. You’ll see that the standard deviation for this smaller sample of data is 74.98135.
This is a good example of how much more accurate a picture you can get with a much larger sample size. For example, the same STDEV.S formula used on a sample size of 200 schools returns 68.51656, which is even closer to the real standard deviation for the entire population of data.
How To Use The STDEVA Excel Function
The standard deviation function STDEVA is rarely used since most datasets people use are filled with only numerical data. But you may have situations where there will be text values inside the data.
This is how STDEVA handles text data.
- TRUE evaluates as 1
- FALSE evaluates as 0
- Any other text evaluates as 0
One example of when this may be valuable is if you had a sensor on a machine measuring the temperature of a liquid above 0 degrees Celsius.
You could program the sensor so that if the temperature probe is disconnected, it writes a “FALSE” into the data stream. When you perform the standard deviation calculation in Excel, those “FALSE” data readings will get converted to a 0 within the dataset before the standard deviation is calculated.
The formula is:
=STDEVA(C2:C100)
Press Enter when you’re done. The result in this case was 4.492659. This means that the entire sample dataset of just under 100 points varied from the overall mean by just under 5 degrees.
This result takes into account the “FALSE” data readings as having a value of 0 degrees.
Just like in the case of the STDEV.S function, if you have an entire population of data that contains text entries, you can use the STEVPA function to calculate the standard deviation for that population.
Remember, if you’re using an older version of Excel that doesn’t have the other standard deviation functions available, you can still use STDEV and STDEVP, which work the same way to calculate standard deviation in Excel as the examples above. However those functions can’t make use of text or logical data.
Make sure to check out our other useful tips and tricks for using Excel. And share your own applications of the standard deviation functions in the comments section below.
