EXCEL函数常用技巧浅析)1)SUBTOTAL函数浅析.xlsVIP

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