从数学上讲，您可以通过从特定数据集的最大值中减去最小值来计算范围。它表示数据集中值的分布，对于测量可变性很有用——范围越大，数据的分布和可变性就越大。
幸运的是，使用Excel函数查找一组数据的范围很容易。这是如何做到的。
如何通过3 个步骤
查找和计算范围(Calculate Range)
在Microsoft Excel(Microsoft Excel)中查找范围的最简单方法是使用MIN和MAX函数。MIN函数计算一组数据中的最大值，而MIN函数(MIN)计算最小值。
首先，确保您的数据在工作表中整齐地组织，以便于分析。然后，您可以按如下
方式使用MIN和MAX函数：(MAX)
- 选择数据集之外的一个单元格（在我们的示例 D1 中）。在此单元格中，键入 =MAX 并从下拉菜单中选择 =MAX。
- 通过单击和拖动选择您的单元格范围。或者，您可以手动在单元格中键入单元格范围（例如，=MAX (B2: B15 )。按 Enter(Press Enter)确认。
- 选择(Select)另一个单元格（在我们的示例中为 D2）并键入 =MIN。对此单元格重复(Repeat)步骤 2。
- 现在您需要找出这些值之间的差异。为此，请选择一个单元格（在我们的示例中为 D3）并通过键入 =（包含MAX值的单元格）-（包含MIN值的单元格）来使用减法函数。例如，=D1-D2。
通过将这些函数组合在一个单元格中，可以在一个快捷步骤(one shortcut step)中计算值的范围。为此，我们将使用我们的示例，其中数据集包含在单元格 B2 到B15中。
使用这些单元格，范围函数将如下所示：
=MAX(B2:B15)-MIN(B2-B15)
您所要做的就是更改单元格值以匹配您的数据。
如何在Excel中查找条件范围(Excel)
如果您的数据集有一些异常值，则可以计算一个忽略异常值的条件范围。例如，看看这个示例截图：
几乎所有的值都在 40 到 80 之间，但有两个在 1,000 左右，两个最低值接近 1。如果要计算范围但忽略这些值，则需要忽略 5 和 900 以上的值. 这就是MAXIFS和MINIFS函数的用武之地。
MAXIFS添加一个忽略超过某个数字的值的条件，而MINIFS忽略低于某个数字的值（每个条件用逗号分隔）。
因此，对于我们的示例，MAX函数将变为：
=MAXIFS(B2:B15,B2:B15,”<900”)
MIN 函数将变为：
=MINIFS(B2:B15,B2:B15,”>5”)
在这种情况下，用于计算条件范围的总Excel公式将是：
=MAXIFS(B2: B15 ,B2: B15 ,”<900”)-MINIFS(B2: B15 ,B2: B15 ,”>5”)
注意：MAXIFS和MINIFS函数仅在Excel 2019和Microsoft Office 365中可用，因此如果您使用的是旧版本，则需要单独包含 IF 函数。例如：=MAX(B2: B15 )-MIN(IF(B2: B15 >5,B2: B15 )
数据分析(Data Analysis Has)从未如此简单(Been Easier)
Microsoft Excel 是分析数据的强大工具(powerful tool for analyzing data)。通过本教程，您可以轻松计算任何数据集的范围，即使您需要删除异常值。
How to Find and Calculate Range in Excel
Mathemаtically, you calculate a range by subtracting the mіnіmum value from the mаximum value of a рarticular dataset. Іt represents the spread of values within a datаset and is useful for measuring variability – the larger the range, the more spread out and variable your dаta is.
Luckily, finding the range of a set of data is easy when using Excel functions. Here’s how to do it.
How to Find and Calculate Range in 3 Steps
The easiest way to find the range in Microsoft Excel is by using the MIN and MAX functions. The MIN function calculates the highest value in a set of data, while the MIN function calculates the smallest value.
First, ensure that your data is neatly organized in your worksheet for ease of analysis. Then, you can use the MIN and MAX functions as follows:
- Select a cell outside of your dataset (in our example D1). In this cell, type =MAX and select =MAX from the drop-down menu.
- Select your range of cells by clicking and dragging. Alternatively, you can type the cell range into the cell manually (for example, =MAX(B2:B15). Press Enter to confirm.
- Select another cell (in our example D2) and type =MIN. Repeat step 2 for this cell.
- Now you need to find the difference between these values. To do this, select a cell (in our example, D3) and use the subtract function by typing =(the cell that contains MAX value)-(the cell that contains MIN value). For example, =D1-D2.
It’s possible to calculate the range of values in one shortcut step by combining these functions in one cell. To do so, we’ll use our example where the dataset is contained in cells B2 to B15.
Using these cells, the range function would look like the following:
=MAX(B2:B15)-MIN(B2-B15)
All you have to do is change the cell values to match your data.
How to Find a Conditional Range in Excel
If your set of data has a few outliers, it’s possible to calculate a conditional range that ignores the outliers. For instance, take a look at this example screenshot:
Almost all of the values are between 40 and 80, yet there are two around 1,000 and the two lowest values are close to 1. If you wanted to calculate the range but ignore those values, you would need to ignore values under 5 and over 900. This is where the MAXIFS and MINIFS functions come in.
MAXIFS adds a condition that ignores values over a certain number, while MINIFS ignores values lower than a certain number (with each condition separated by a comma).
So, for our example, the MAX function would become:
=MAXIFS(B2:B15,B2:B15,”<900”)
And the MIN function would become:
=MINIFS(B2:B15,B2:B15,”>5”)
In this case, the total Excel formula for calculating the conditional range will be:
=MAXIFS(B2:B15,B2:B15,”<900″)-MINIFS(B2:B15,B2:B15,”>5″)
Note: The MAXIFS and MINIFS functions are only available in Excel 2019 and Microsoft Office 365, so if you’re using an older version, you will need to include the IF function separately. For example: =MAX(B2:B15)-MIN(IF(B2:B15>5,B2:B15)
Data Analysis Has Never Been Easier
Microsoft Excel is a powerful tool for analyzing data. With this tutorial, you can easily calculate the range of any dataset, even if you need to remove outliers.
