SUBTOTAL函数浅析

EXCEL函数常用技巧浅析技巧二:SUBTOTAL函数浅析

在众多EXCEL函数家族中,SUBTOTAL函数可谓是全能王,可以对数据进行求平均值、计数、最大最小、相乘、标准差、求和

当然这些功能EXCEL另外设有专门的函数来完成，我们现在要来学习的就是SUBTOTAL函数能完成一些其它函数很难完成的技巧，我们先来看一下EXCEL帮助文件中对SUBTOTAL函数的解释：

SUBTOTAL(function_num,ref1,[ref2],...])

Function_num 必需。1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，用于指

使用何种函数在列表中进行分类汇总计算。

Ref1 必需。要对其进行分类汇总计算的第一个命名区域或引用。

Ref2,... 可选。要对其进行分类汇总计算的第 2 个至第 254 个命名区域或引用。

SUBTOTAL函数功能比较全面，与其它专门函数相比有其独特性与局限性

1：可以忽略隐藏的单元格，对可见单元格的结果进行运算;经常配合筛选使用

，但只对行隐藏有效，对列隐藏无效；此处第一参数与对二参数对隐

在于手工隐藏,对筛选的效果是一样的。

2：支持三维运算，这是我们要学习的重点部分；

3：只支持单元格区域的引用

4:第一参数支持数组参数

技巧解析一：

下表为某学校一班本学期语文测试各学生的得分，现在我们要对每个学生本学期最高分进行求和：

公式解析:

用OFFSET 函数,产生7个一维平面,利用SUBTOTAL 函数对三维的支持取得每一个平面的最大值,

=OFFSET(C34:G34,ROW(1:7),)

再用SUM 函数对取得的最大值进行求和

技巧解析二:

下表为某公司B 产品2010度每月销售金额,公司计划2010年B 产品销售250万元,现要查询在第几月份完成公司计划

公式解析:

先用OFFSET 函数取得一十二个一维平面

=OFFSET(C62,,,ROW(1:12))

然后利用SUBTOTAL函数对三维的支持,对每一个平面进行求和,得到一个累计和

再利用IF函数排除累计销售额小于计划销售额的月度,用MIN求出达到销售额达到计划销售的月份

最后用INDEX函数取出最先达标月度,针对此题可以直接用&月取得月度

公式改为

8月=MIN(IF(SUBTOTAL(9,OFFSET(C62,,,ROW(1:12)))>=F62,ROW(1:12)))&"月"

此贴为chenhh803原创,转载请注明

L函数常用技巧浅析(二)

最大最小、相乘、标准差、求和、方差。

能完成一些其它函数很难完成的方法与

略隐藏值）之间的数字，用于指定

名区域或引用。

运算;经常配合筛选使用

数对隐藏的区别

最高分进行求和：

最高分的总和

592

公式:=SUM(SUBTOTAL(4,OFFSET(C34:G34,ROW(1:7),)))

最高分的总和与最低分的总和

592447

区域数组公式

=MMULT(COLUMN(A:G)^0,SUBTOTAL({4,5},OFFSET(C39:G39,ROW(1:7),)))

平面的最大值,

验算

88

83

86

87

81

83

84

592=SUM(H47:H53)

要查询在第几月份完成公司计划销售额

X(B62:B73,MIN(IF(SUBTOTAL(9,OFFSET(C62,,,ROW(1:12)))>=F62,ROW(1:12))))

的月份F62,ROW(1:12)))