AVERAGEIF和AVERAGEIFS都是Microsoft Excel中的(Microsoft Excel)统计(Statistical)函数。在这篇文章中,我们将看看它们的语法以及如何使用它们。
AVERAGEIF函数评估范围内满足给定标准的所有数字的平均值。(AVERAGEIF)AVERAGEIF的公式是:
Averageif (Range, criteria, [average_range])
AVERAGEIFS函数返回满足多个条件的范围内所有数字的平均值。(AVERAGEIFS)AVERAGEIFS的公式是:
Averageifs (average_range, criteria_range1, criteria1[criteria_ range2, criteria2] …)
AVERAGEIF 和 AVERAGEIFS 的语法
平均(AVERAGEIF)
- Range:是您要平均的单元格组。范围(Range)是必需的。
- Criteria:Criteria用于验证要平均哪些单元格。标准(Criteria)是必需的。
- Average_range:要平均的单元格范围(Range)。当不存在时,范围参数将取而代之的是平均值。平均(Average_range)范围是可选的。
平均值(AVERAGEIFS)
- Average_range:Average_ Range是一个或多个要平均的单元格。平均(Average_range)范围是必需的。
- Range:要平均的单元格组。范围(Range)是必需的。
- Criteria_range1:评估相关标准的第一个Range 。第一个Criteria_range1是必需的,但第二个 criteria_range 是可选的。
- 标准 1(Criteria 1):标准(Criteria)验证要平均哪些单元格。第一个标准是必需的。第二个标准或之后的任何标准是可选的。
如何在 Excel 中使用 AVERAGEIF
首先,我们要单击需要平均(Average)的单元格。然后键入=Averageif括号。
在括号内,我们将键入Range,因为单元格范围(Range)包含我们想要平均的数据。
在本教程中,我们将键入单元格(D3:D9),或者我们可以单击要平均的列中的单元格并将其拖动到末尾;这将自动将单元格区域放入公式中。
现在,我们要进入Criteria;该标准验证要平均哪些单元格。我们将在本教程中使用面包(Bread),因为它是我们正在寻找的。
我们将进入Average_ 范围(Average_ range)。我们将输入G3:G9因为这些单元格包含我们想要平均的销售额;然后关闭支架。
按Enter,您将看到结果。
另一种选择是去Formulas。在函数库(Function Library)组中,选择更多函数(More Functions)。在下拉菜单列表中,选择Statistical,在其菜单中选择 AVERAGEIF。将出现一个函数参数(Function Argument)对话框。
在Function Arguments对话框中,您可以在其中看到Range ,在其输入框中键入D3:D9 。
在Function Arguments对话框中,您会看到Criteria ,在条件输入框中键入“Bread” 。
在Function Arguments对话框中,您可以在其中看到Average_range ,在其输入框中键入G3:G9 。
单击确定(OK),您将看到结果。
阅读:(Read:) 如何在 Excel 中使用 SUMIF 和 SUMIFS 函数(How to use SUMIF and SUMIFS Functions in Excel)
如何在 Excel 中使用 AVERAGEIFS
在本教程中,我们将查找客户 110 购买的产品的平均销售额。
单击(Click)您希望结果所在的单元格,然后键入=Averageif,括号。
在括号类型G3:G9中,这是Average_range。
现在我们要输入Criteria_range1。键入D3:D9,此单元格范围包含我们要查找的数据。
我们将输入Criteria1,即“面包”(” Bread”),因为它是我们正在寻找的产品。
我们将细化Criteria_range2,即A3:A9,因为我们要识别客户。
我们将输入Criteria2,即“110”,因为这是我们要查找的客户的 ID 号。
按Enter 键,(Enter and )您将看到结果。
另一种选择是去Formulas。在函数库(Function Library)组中,选择更多函数(More Functions)。在下拉菜单列表中,选择Statistical在其菜单中选择AVERAGEIFS;并且会出现一个函数参数(Function Argument)对话框。
在函数参数(Function Arguments)对话框中,您会看到Average_range。在其输入框中键入G3:G9 。
在函数参数(Function Arguments)对话框中,您会看到Criteria_range1。在其输入框中键入D3:D9 。
在Criteria1输入框中,输入Bread。
在Criteria_range2输入框中,键入A3:A9。
在Criteria2输入框中,键入“110”,因为我们正在寻找客户 ID 110。
单击确定(OK)。你会看到结果。
阅读下一篇(Read next): 如何在 Excel 中使用 IF 函数。(How to use the IF Function in Excel.)
How to use AVERAGEIF and AVERAGEIFS in Excel
AVERΑGEIF and AVERAGEIFS are both Statistical functions n Microsoft Excel. In this post, we will take а look at their ѕyntax and how tо use them.
The AVERAGEIF function evaluates the average of all numbers in a range that meet a given criterion. The formula for the AVERAGEIF is:
Averageif (Range, criteria, [average_range])
The AVERAGEIFS function returns the average of all numbers in a range that meets multiple criteria. The formula for AVERAGEIFS is:
Averageifs (average_range, criteria_range1, criteria1[criteria_ range2, criteria2] …)
Syntax of AVERAGEIF and AVERAGEIFS
AVERAGEIF
- Range: is the group of cells you want to average. The Range is required.
- Criteria: The Criteria is used to verify which cells to average. Criteria are required.
- Average_range: The Range of cells to average. When absent, the range parameter will be average instead. Average_range is optional.
AVERAGEIFS
- Average_range: The Average_ Range is one or more cells to average. Average_range is required.
- Range: the group of cells you want to average. The Range is required.
- Criteria_range1: The first Range to evaluate the related criteria. The first Criteria_range1 is required, but the second criteria_ range is optional.
- Criteria 1: The Criteria verify which cells to average. The first criteria are required. The second criteria or any criteria after is optional.
How to use AVERAGEIF in Excel
First, we are going to click the cell where we want the Average. Then type =Averageif bracket.
Inside the bracket, we will type the Range because the Range of cells contains the data we want to average.
In this tutorial, we will type the cell (D3:D9), or we can click on the cell in the column that we want to average and drag it to the end; this will automatically place the range of cells in the formula.
Now, we are going to enter the Criteria; the criteria validate which cells to average. We are going to use Bread in this tutorial because it is what we are looking for.
We are going to enter the Average_ range. We will type G3:G9 because these cells contain the sales we want to average; then close the bracket.
Press Enter, you will see the result.
The other option is to go to Formulas. In the Function Library group, select More Functions. In the drop-down menu list, select Statistical in its menu choose AVERAGEIF. A Function Argument dialog box will appear.
In the Function Arguments dialog box, where you see Range, type D3:D9 into its entry box.
In the Function Arguments dialog box, where you see Criteria, type “Bread” into the criteria entry box.
In the Function Arguments dialog box, where you see Average_range, type G3:G9 into its entry box.
Click OK and you will see your result.
Read: How to use SUMIF and SUMIFS Functions in Excel
How to use AVERAGEIFS in Excel
In this tutorial, we are going to look up the average sale of products bought by customer 110.
Click the cell where you want the result in, then type =Averageif, bracket.
In the bracket type G3:G9, this is the Average_range.
Now we are going to type the Criteria_range1. Type D3:D9, this range of cells contain the data we are looking for.
We will type the Criteria1, which is” Bread” because it is the product we are looking for.
We are going to fine Criteria_range2, which is A3:A9 because we want to identify the customer.
We will enter Criteria2, which is“110,” because this is the ID number of the customer we are looking for.
Press Enter and you will see your result.
The other option is to go to Formulas. In the Function Library group, select More Functions. In the drop-down menu list, select Statistical in its menu select AVERAGEIFS; and a Function Argument dialog box will appear.
In the Function Arguments dialog box, where you see Average_range. Type G3:G9 in its entry box.
In the Function Arguments dialog box, where you see Criteria_range1. Type D3:D9 in its entry box.
In the Criteria1 entry box, type Bread.
In the Criteria_range2 entry box, type A3:A9.
In the Criteria2 entry box, type “110” because we are looking for the customer id 110.
Click OK. You will see the result.
Read next: How to use the IF Function in Excel.