筛选模式下的分类汇总——关于SUBTOTAL的多维计算和多级别(多重条件)筛选不重复值E.xlsVIP

Subtotal的三维计算 答案及解析 问题 data data Lines 1.00 Order = VAMT 9.00 1.00 1.00 1.00 VAMT 9.00 Vlines 3.00 1.00 1.00 Vlines 3.00 1.00 Vpsn 1: VTAMT VUline .00 VUlines 1.00 .00 VUpsn VUpsn 产品 客户 配件 金额 金额 A B C B 负责人 负责人 项目 完工情况 C data Lines Vlines VUpsn VTAMT 负责人 金额 表一 项目完工情况一览表 项目 完工情况 A 未开工 已开工 B 已完工 C 表二 按各种完工情况筛选汇总后的效果 当筛选条件为 完工情况=全部 已开工 已完工 当筛选条件为 完工情况=未开工 当筛选条件为 完工情况未开工 此为当前筛选情况 VAMT 请做各种情况的筛选，并注意筛选后下图的变化 A VUlines2 =IF(ISNUMBER(1/(MATCH(data,IF(Vlines,data),0)=Lines)),Lines) VUlines3 =IF(FREQUENCY(IF(Vlines,COUNTIF(data,=data)),COUNTIF(data,=data)),Lines) Subtotal的三维计算 Function_num 函数 AVERAGE COUNT COUNTA MAX MIN PRODUCT STDEV STDEVP SUM VAR VARP Subtotal语法 Function_num??? 为 1 到 11（包含手动隐藏值）或 101 到 111（忽略手动隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。 （包含手动隐藏值） （忽略手动隐藏值） SUBTOTAL(function_num, ref1, ref2, ...) Mmult Subtotal(4,) Subtotal(5,) 苹果 香蕉 李子 栗子 梨子 荔枝 Subtotal(3,) Subtotal(6,) 语文 数学 英语 孙二 张三 李四 王五 赵六 钱七 Subtotal(9,) subtotal(1,) 表二水果上市的销售数量 表二水果单价 5号 6号 7号 单价 苹果 香蕉 李子 栗子 梨子 荔枝 Subtotal(2,) 表三纸箱规格(厘米) 长 框 高 L001 L002 L003 L004 L005 L006 Subtotal(4,) (列方向) 例1:求表一中每个学生的总成绩（返回内存数组） 例2：求每个学生的最高单科成绩和平均成绩 例3:求各科的最高成绩 例4:求各科的最低成绩 例5:求表二中各种水果有销售的天数 例6:求表二中各种水果销售的金额 例7:求表三中纸箱的体积(立方厘米) 以上介绍了Subtotal中相当于Average,Count,CountA,Max,Min,Product,Sum函数功能部分的三维计算。另外几个参数较少用到，但原理也相同，视具体情况作相应调整即可。 Subtotal不计算筛选隐藏行的统计，适得上述用法还能适用于筛选状态在忽略隐藏行。第一个参数如果大于100，那么对手动隐藏行也有相同的效果。 Ref1, ref2,?? 为要进行分类汇总计算的 1 到 29 个区域或引用。这里的1-29针对物理区域和引用，用offset引用产生的多个区域引用只算一个参数。 P001 P002 P003 M01 M03 M02 问题 答案及解析 多级别筛选不重复值 说明： 测试题1 本题有三个要点：subtotal的三维计算、多重条件筛选不重复值、内存数组。其中内存数组已经在测试1中讨论了这里不再重复。 表四现金收入支出表 收入 支出 Sumif Mmult+Sumif 例8:求每日余额(内存数组) Subtotal(6,0) 环比成长率 例9：求每年较第一年的定基成长率(内存数组) 表五 环比成长率表 在前面的测试讨论题中，我们曾多次讨论过Sumif，Countif及数据库函数三维计算的应用。但我们发现象Average,Count,CountA,Max,Min,Product,Sum等不带判断条件参数的函数却无法进行三维计算。值得庆幸的是Subtotal为我们提供了这样的方法。这里我们将用例举的方式来做介绍。 表一学生成绩表 总结 为突出主题从网友中的答案中，选出了4个完全符合题意的解法，并在保留作者主要意图的基础上，做了些调整。 此题涵盖的Excel函数的许多高级应用，如果能认真将其参透，Excel水平一定能够再上一个台阶：） 同时为了更好的说明问题，对原题中不够严谨的地方做了修正。 为突出重点，不再对所有的公式一一解析，只提取其中的一些共性和差异来加以分析，并用10