excel一列平均分成多列_基于多个条件的Excel平均

excel一列平均分成多列

In Excel, you can use the SUMIF and COUNTIF functions, to sum and count values, based on criteria. Did you know that you can also calculate an Excel average, based on multiple criteria?

在Excel中，可以使用SUMIF和COUNTIF函数根据条件对值进行求和和计数 。 您是否知道您还可以基于多个条件来计算Excel平均值 ？

平均一个标准 (Average with One Criterion)

If you only have one criterion, you can use the AVERAGEIF function. In the screen shot below, the average quantity is calculated for any orders where Pens were sold.

如果只有一个条件，则可以使用AVERAGEIF函数。 在下面的屏幕截图中，将计算出售笔的任何订单的平均数量。

=AVERAGEIF(A1:A10,"Pen",B1:B10)

= AVERAGEIF(A1：A10，“ Pen”，B1：B10)

多个条件的平均值 (Average for Multiple Criteria)

If you need multiple criteria for the average, and you’re using Excel 2007 or a later version, you can use the AVERAGEIFS function.

如果您需要多个平均值标准，并且使用的是Excel 2007或更高版本，则可以使用AVERAGEIFS函数。

In the next screen shot, the AVERAGEIFS formula checks for Pen in column A, and a quantity greater than or equal to 10, in column B. The criteria values are entered on the worksheet – cells D3 and E3 – where they can be changed easily.

在下一个屏幕快照中，AVERAGEIFS公式在A列中检查Pen，在B列中检查大于或等于10的量。标准值输入到工作表上-单元格D3和E3-可以轻松更改它们。

For the rows that meet those criteria – rows 6 and 10 -- an average quantity is calculated.

对于满足这些条件的行(第6和第10行)，将计算平均数量。

=AVERAGEIFS(B2:B10,A2:A10,D3,B2:B10,">="&E3)

= AVERAGEIFS(B2：B10，A2：A10，D3，B2：B10，“> =”＆E3)

下载样本文件 (Download the Sample File)

To see these average formulas, and other examples, you can download the sample file from my Contextures website: Excel Average Formulas.

若要查看这些平均公式和其他示例，可以从Contextures网站上下载示例文件： Excel Average Formulas 。

The sample file is zipped, and is in xlsx format (Excel 2007 and later versions).

该示例文件已压缩，并且为xlsx格式(Excel 2007及更高版本)。

翻译自: https://contexturesblog.com/archives/2013/03/19/excel-average-based-on-multiple-criteria/

excel一列平均分成多列