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

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 Function_num (包含隐藏值)(忽略隐藏值) 1 101 AVERAGE 数学平均值 2 102 COUNT 数字的个数 3 103 COUNTA 非空的个数 4 104 MAX 最大值 5 105 MIN 最小值 6 106 PRODUCT 乘积 7 107 STDEV 标准偏差 8 108 STDEVP 标准偏差 9 109 SUM 求和 10 110 VAR 方差 11 111 VARP 方差 SUBTOTAL 函数功能比较全面, 与其它专门函数相比有其独特性与局限性 1: 可以忽略隐藏的单元格, 对可见单元格的结果进行运算; 经常配合筛选使用, 但只对行隐藏有效, 对列隐藏无效; 此处第一参数与对二参数对隐藏的区别在于手工隐藏, 对筛选的效果是一样的。 2: 支持三维运算, 这是我们要学习的重点部分; 3: 只支持单元格区域的引用 4: 第一参数支持数组参数技巧解析一: 下表为某学校一班本学期语文测试各学生的得分,现在我们要对每个学生本学期最高分进行求和: 姓名语文测试 1语文测试 2语文测试 3语文测试 4语文测试 5 米米菜 72 81 85 62 69 菜头 86 76 87 72 88 大头菜 80 84 67 62 76 坤哥 89 86 84 66 68 路上菜 75 66 88 74 79 芳芳 61 75 61 64 82 剁椒鱼头 86 83 79 76 64 公式解析: 用 OFFSET 函数,产生 7个一维平面,利用 SUBTOTAL 函数对三维的支持取得每一个平面的最大值, =OFFSET(C34:G34,ROW(1:7),) 再用 SUM 函数对取得的最大值进行求和技巧解析二: 下表为某公司 B产品 2010 度每月销售金额,公司计划 2010 年 B产品销售 250 万元,现要查询在第几月份完成公司计划销售额月度销售金额验算计划销售额完成目标月 1月########### ￥ 351, ￥ 2,500, 8月 2月########### ￥ 719, =INDEX(B62:B73,MIN(IF(SUBTOTAL(9,OFFSET(C62,,,ROW(1:12)))>=F62,ROW(1:12)))) 3月########### ￥ 1,020,508.