SUBTOTAL函数浅析.xlsVIP

筛选配合 SUBTOTAL浅析 EXCEL函数常用技巧浅析(二) 在众多EXCEL函数家族中,SUBTOTAL函数可谓是全能王,可以对数据进行求平均值、计数、最大最小、相乘、标准差、求和、方差。 SUBTOTAL(function_num,ref1,[ref2],...]) Ref1 必需。要对其进行分类汇总计算的第一个命名区域或引用。 Ref2,... 可选。要对其进行分类汇总计算的第 2 个至第 254 个命名区域或引用。 Function_num （包含隐藏值） （忽略隐藏值） AVERAGE COUNT COUNTA MAX MIN STDEVP SUM VAR VARP 数学平均值 数字的个数 非空的个数 最大值 最小值 PRODUCT 乘积 STDEV 标准偏差 求和 方差 姓名 米米菜 菜头 大头菜 坤哥 路上菜 芳芳 剁椒鱼头 语文测试1 语文测试2 语文测试3 语文测试4 语文测试5 下表为某学校一班本学期语文测试各学生的得分，现在我们要对每个学生本学期最高分进行求和： 最高分的总和 公式解析: 用OFFSET函数,产生7个一维平面,利用SUBTOTAL函数对三维的支持取得每一个平面的最大值, =SUM(H47:H53) 验算 技巧解析一： 公式:=SUM(SUBTOTAL(4,OFFSET(C34:G34,ROW(1:7),))) 技巧解析二: SUBTOTAL函数功能比较全面，与其它专门函数相比有其独特性与局限性 月度 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 销售金额 完成目标月 下表为某公司B产品2010度每月销售金额,公司计划2010年B产品销售250万元,现要查询在第几月份完成公司计划销售额 计划销售额 验算 公式解析: 先用OFFSET函数取得一十二个一维平面 =OFFSET(C34:G34,ROW(1:7),) =OFFSET(C62,,,ROW(1:12)) SUBTOTAL(9,OFFSET(C62,,,ROW(1:12))) 然后利用SUBTOTAL函数对三维的支持,对每一个平面进行求和,得到一个累计和 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 再利用IF函数排除累计销售额小于计划销售额的月度,用MIN求出达到销售额达到计划销售的月份 MIN(IF(SUBTOTAL(9,OFFSET(C62,,,ROW(1:12)))=F62,ROW(1:12))) 最后用INDEX函数取出最先达标月度,针对此题可以直接用月取得月度 公式改为 =INDEX(B62:B73,MIN(IF(SUBTOTAL(9,OFFSET(C62,,,ROW(1:12)))=F62,ROW(1:12)))) =MIN(IF(SUBTOTAL(9,OFFSET(C62,,,ROW(1:12)))=F62,ROW(1:12)))月 此贴为chenhh803原创,转载请注明 技巧二:SUBTOTAL函数浅析 =MIN(IF(SUBTOTAL(9,OFFSET(C62,,,ROW(1:12)))=F62,ROW(1:12))) 三:利用SUBTOTAL动态汇总分班打印 班级 姓名 语文 数学 英语 101班 张1 张2 张3 张4 张5 张6 张7 张8 张9 张10 张11 张12 张13 102班 张14 张15 张16 张17 张18 张19 张20 张21 103班 张22 张23 张24 张25 张26 合 计 再用SUM函数对取得的最大值进行求和 当然这些功能EXCEL另外设有专门的函数来完成，我们现在要来学习的就是SUBTOTAL函数能完成一些其它函数很难完成的方法与 技巧，我们先来看一下EXCEL帮助文件中对SUBTOTAL函数的解释： Function_num 必需。1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，用于指定 使用何种函数在列表中进行分类汇总计算。 请对班级进行筛选查看合计栏结果 ，但只对行隐藏有效，对列隐藏无效；此处第一参数与对二参数对隐藏的区别 在于手工隐藏,对筛选的效果是一样的。 =SUBTOTAL(109,C8:C33), 此处效果为一样,只是在针对手工隐藏时有区别 =SUBTOTAL(9,C8:C33) 王五成绩 语文 数学 英语 分数 =SUBTOTAL(9,B38:B40) =SUBTOTAL(109,E38:E40) 此处请手工隐藏数学行,看看效果是否一样 最高分的总和与最低分的总和 区域数组公式 1：可以忽略隐藏的单元格，对可见单元格的结果进行运算;经常配合筛选使用 2：支持三维运算，这是我们要学习的重点部分； 3：只支持单元格区域的引用 =MMULT(COLUMN