& Excel-Subtotal-FunctionExcel SUBTOTAL FunctionThis page describes the Excel Subtotal function. However, Excel also has a Subtotal command that inserts subtotals into a data table.For details, see the .Basic DescriptionThe Excel Subtotal function performs a specified calculation (e.g. the sum, product, average, etc.) for a supplied set of values.The syntax of the function is:SUBTOTAL( function_num, ref1, [ref2], ... )Where the arguments are as follows:function_num-An integer that denotes the calculation type (e.g. sum, average, etc.).(See the table below for possible values for function_num).ref1, [ref2], ...-One or more references to cells containing the values that the calculation is to be performed on.(Blank cells and cells containing non-numeric values are ignored in the calculation).& Table 1: Possible values for function_numfunction_num(include hidden values)function_num(ignore hidden values)Function1101210231034104510561067107810891091011011111The possible values for the function_num argument are shown in Table 1 (see rightabove).For each arithmetic operation, there is an option to ignore or to include hidden values in the calculation. Note that:The ignore/include hidden values option only applies to rows that have been hidden using the 'Hide' command in the row formatting options.If rows of data have been filtered out using the , these are not included in any Subtotal calculations (regardless of the ignore/include hidden values option).The Subtotal function is designed for columns or vertical ranges of data. Although the function can be used to perform arithmetic operations on vertical or horizontal ranges of data, the 'ignore hidden values' options will not ignore data in hidden columns.Subtotal Function ExamplesThe following examples all use a simple company spreadsheet, that shows monthly sales figures, between January and March, for 3 teams.Example 1 - No Values HiddenIn cells C12 and C13 of the spreadsheet below, the Subtotal function is used to calculate the sum and the average monthly sales per team. As all of the cells in the range of sales figures are visible, the calculations include all values in the sales column:Note that, in the example above, as all cells are visible, we could have set the function_num argument to be either 9 or 109 for the sum, and to be either 1 or 101 for the average calculation. Both options would give the same two results.Example 2 - Values Hidden Using Excel AutofilterIn this example, the teams column of the spreadsheet has been filtered, using the , to show Team 1 only. In this case, the Subtotal function's calculations will be for the visible cells only (i.e. for Team 1 only):Note that, in the example above, because the rows for Teams 2 & 3 have been filtered out, using the , we could have used a function_num argument of either 9 or 109 for the sum and a function_num argument of either 1 or 101 for the average. Both options would perform the calculation on the visible cells only.Example 3 - Values Hidden Using Row FormattingIn the spreadsheet below, rows 3, 4, 6, 7, 9, and 10 have been hidden using row formatting. I.e. by highlighting these rows, right clicking with the mouse and selecting Hide.Once again, the Subtotal function has been used in cells C12 and C13 of the spreadsheet, to calculate the sum and average of the monthly sales across the visible teams.Because the non-visible rows have been hidden using the row formatting option, if you want to only include the visible cells in the calculations, it is necessary to set the function_num argument to the value 109 for the sum calculation, and to the value 101 for the average calculation.Further details and examples of the Excel Subtotal Function are provided on the .Subtotal Function ErrorIf you get an error from the Excel Subtotal function this is likely to be one of the following:Common Error#VALUE!-Occurs if the supplied function_num argument is not one of the permitted values (an integer between 1 & 11 or between 101 & 111).#DIV/0!-Occurs if required calculation involves a division by zero (e.g. finding the average, standard deviation or variance for a range of cells that do not contain any numeric values).推荐这篇日记的豆列
······Excel2010新增函数中的多面手AGGREGATE
您现在的位置：&&>>&&>>&&>>&正文
Excel2010新增函数中的多面手AGGREGATE
作者： 文章来源： 点击数： 更新时间： 10:47:46
&&& AGGREGATE是Excel 2010中新增的函数之一，它可以返回列表或数据库中的合计。 AGGREGATE不仅可以实现诸如SUM、AVERAGE、COUNT、LARGE等19个函数的功能，而且还可以忽略隐藏行、错误值、空值等。如果区域中包含错误值，SUM等函数将返回错误，这时用 AGGREGATE函数就非常方便了。其引用形式的语法为：
&&& AGGREGATE(function_num, options, ref1, [ref2], …)
&&& 其中第一个参数“function_num&”为一个介于1到19之间的数字，指定要使用的函数，见下表。
计算平均值
计算参数中数字的个数
计算区域中非空单元格的个数
返回参数中的最大值
返回参数中的最小值
返回所有参数的乘积
基于样本估算标准偏差
基于整个样本总体计算标准偏差
基于样本估算方差
计算基于样本总体的方差
返回给定数值的中值
返回数组或区域中出现频率最多的数值
返回数据集中第k个最大值
返回数据集中的第k个最小值
PERCENTILE.INC
返回区域中数值的第K(0≤k≤1)个百分点的值
QUARTILE.INC
返回数据集的四分位数（包含0和1）
PERCENTILE.EXC
返回区域中数值的第K（0&k&1)个百分点的值
QUARTILE.EXC
返回数据集的四分位数(不包括0和1）
&&& 第二个参数“options”为为一个0至7之间的数字，指定要忽略的项目。在单元格中输入该函数的第二个参数时，Excel会自动给出该参数的选择列表，如图。
&&& 第三个参数“ref1”为区域引用或一个数组，第四个可选参数“[ref2]”为某些函数必需的第二个参数。
&&& AGGREGATE函数与SUBTOTAL函数有点类似，当SUBTOTAL函数的第一个参数为“101-111”之间的数字时，就会忽略隐藏值，但SUBTOTAL函数不能忽略错误值。
&&& 例如在下图的A2:F12区域是由公式返回的动态数据区域，其中某些单元格可能会返回错误值，要对这些包含错误值的区域求和，在Excel 2010中使用AGGREGATE函数更为简洁。
&&& 如果在G10单元格中输入公式：
&&& =SUM(B10:F10)
&&& 公式返回错误“#N/A”。而公式：
&&& =AGGREGATE(9,6,B10:F10)
&&& 会忽略错误值而返回B10:F10区域的合计值。
&&& 值得注意的是，AGGREGATE函数只能忽略隐藏行，而不能忽略隐藏列。
上一篇文章：
下一篇文章：
&其他网友还在看：
　网友评论：（评论内容只代表网友观点，与本站立场无关！）
::发表评论::
评论内容：
请遵守及中华人民共和国其他各项有关法律法规。
严禁发表危害国家安全、损害国家利益、破坏民族团结、破坏国家宗教政策、破坏社会稳定、侮辱、诽谤、教唆、淫秽等内容的评论 。
用户需对自己在使用本站服务过程中的行为承担法律责任（直接或间接导致的）。
评论需要审核通过后才可见，本站管理员有权保留或删除评论内容。
评论内容只代表网友个人观点，与本网站立场无关。
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Excel技巧天地 E-mail: