|
送人玫瑰，手有余香，请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径！ 【置顶公众号】或者【设为星标】及时接收更新不迷路 小伙伴们好，今天要和大家分享一道比较常见的，但是又有一定难度的题目。利用公式来进行分段汇总。今天这个题目的逻辑思路你掌握了之后，以后再遇到类似的题目就可以轻松解决了。 题目是这样子的： 每一组数字都有一个或者几个空格间隔，如何利用公式对每一组数字进行求和汇总呢？ 在单元格E2中输入公式“=IFERROR(MOD(SMALL(IFERROR(ROW($1:$21)/1%%+1/SUMIF(OFFSET(A$1,ROW($1:$21)-1,,-FREQUENCY(ROW($1:$21),(A$1:A$21="")*ROW($1:$21))),">0")^-1,""),ROW(A1)),10^4),"")”，三键回车并向下拖曳即可。 思路： FREQUENCY(ROW($1:$21),(A$1:A$21="")*ROW($1:$21))部分，利用FREQUENCY函数对ROW($1:$21)在(A$1:A$21="")*ROW($1:$21)这个序列上计频。这是一个常用的技巧。由于(A$1:A$21="")*ROW($1:$21)返回的是对应的空值部分，因此计频的结果的含义是每个数值部分中数字的个数。这部分的结果为{0;0;3;1;1;0;2;0;0;0;4;1;0;0;3;0;0;3;1;0;2;0} OFFSET(A$1,ROW($1:$21)-1,,-FREQUENCY(ROW($1:$21),(A$1:A$21="")*ROW($1:$21)))部分，以单元格A1为基点进行偏移。偏移后的数据区域的高度分别对应为-FREQUENCY(ROW($1:$21),(A$1:A$21="")*ROW($1:$21))的值，也就是向上 1/SUMIF(OFFSET(A$1,ROW($1:$21)-1,,-FREQUENCY(ROW($1:$21),(A$1:A$21="")*ROW($1:$21))),">0")^-1部分，SUMIF函数按条件“>0”来求和，求和后因为有0值影响后面的计算，因此1/SUMIF()^-1部分的操作是将所有的零值转换为错误值，其它正常的数值没有影响 完成后行号ROW($1:$21)扩大1万倍，并和上面这一步的值相加 取最小值后再用MOD函数对10^4求余，结果就是数值区域的汇总值了
在单元格E2中输入公式“=-SUM(-A$1:INDEX(A:A,SMALL(IF(A$1:A$20%>A$2:A$21,ROW($1:$20)),ROW(A1)),),E$1:E1)”，三键回车并行下拖曳即可。 思路： A$1:A$20%>A$2:A$21部分也是一个常用的技巧，它返回每组数据中最后一个数字的行号 SMALL函数依次提取出第1、2、3..小的值，由INDEX函数返回A列中的位置。这一个它实际上是完成了以单元格A1为起点，上面各个返回数值为终点的单元格区域 转换为负值后，和单元格区域E$1:E1求和。随着公式向下拖曳，E$1:E1也会不断变达，包含了前面每一步的汇总值。实际上是完成了减去前面每步汇总值的目的 求和后将负值转换为正值即可得到正确答案
在单元格E2中输入公式“=IFERROR(SUM(OFFSET(A$1,,,MAX(SMALL(IF((A$1:A$20>0)*(A$2:A$21=0),ROW($1:$20)),ROW(A1)))))-SUM(E$1:E1),"")”，三键回车并向下拖曳即可。 这个公式和上面第二个的思路大同小异，朋友们自己动手类分析一下吧。如有问题可以私信我哦！
|
