ARTICLE · 1390022
哪个EXCEL大佬琢磨的文本间数字求和公式,用绝了>1和{-1,1},简直是点睛之笔.不看就后悔!

欢迎转发和点一下“在看”,文末留言互动!
置顶公众号或设为星标及时接收更新不迷路

小伙伴们好,今天是周末,和大家分享一道烧脑的题目。题目比较有意思,要求计算两个文本之间所有的数据之和。

观察一下源数据,这道题目中的要求其实有两个:1,最后两个文本;2,两文本间必须要有数值。
因此,这道题目越久转换成了:
1,确定起止文本位置
2,确定文本间有数字
朋友们,该怎样写公式呢?
01
公式很长,但逻辑思路还是比较简单的

在单元格N2中输入下列公式,三键确认并向下拖曳即可。
=IF(OR(COUNT($A2:$M2)>11,AND(COUNT($A2:$M2)<3,COUNT(A2))),"",SUM(OFFSET($A2,,LARGE(IFERROR((FREQUENCY(COLUMN($A:$M),ISTEXT($A2:$M2)*COLUMN($A:$M))>1)*ROW($1:$13),0),2),1,SUM(LARGE(IFERROR((FREQUENCY(COLUMN($A:$M),ISTEXT($A2:$M2)*COLUMN($A:$M))>1)*ROW($1:$13),0),{2,1})*{-1,1})-1)))公式比较复杂,简单介绍一下吧。总体上讲,这是一个IF函数结构。
OR(COUNT($A2:$M2)>11,AND(COUNT($A2:$M2)<3,COUNT(A2)))这部分作为IF函数的第一参数,条件判断。当某一行中,数值数量大于11个,该行的第一个数据为数字并且该行中数字个数小于3个时,IF函数就返回空值。
SUM(OFFSET($A2,,LARGE(IFERROR((FREQUENCY(COLUMN($A:$M),ISTEXT($A2:$M2)*COLUMN($A:$M))>1)*ROW($1:$13),0),2),1,SUM(LARGE(IFERROR((FREQUENCY(COLUMN($A:$M),ISTEXT($A2:$M2)*COLUMN($A:$M))>1)*ROW($1:$13),0),{2,1})*{-1,1})-1))上面这部分是IF函数的第二参数。我们着重讲讲这部分。
这部分中主要是一个OFFSET函数,外侧嵌套了一个SUM函数。
$A2,,这是它的第一参数和第二参数。第二参数省略了,表示不在行方向上偏移。
LARGE(IFERROR((FREQUENCY(COLUMN($A:$M),ISTEXT($A2:$M2)*COLUMN($A:$M))>1)*ROW($1:$13),0),2)这个是OFFSET函数的第三参数,需要偏移的列数。
ISTEXT($A2:$M2)*COLUMN($A:$M)找出当前行中文本位置对应的列号,其结果是{1,0,0,0,5,0,0,0,0,10,0,12,13}
FREQUENCY(COLUMN($A:$M),ISTEXT($A2:$M2)*COLUMN($A:$M))在其上对列号进行计频,该结果是{1;0;0;0;4;0;0;0;0;5;0;2;1;0}
LARGE(IFERROR((FREQUENCY(COLUMN($A:$M),ISTEXT($A2:$M2)*COLUMN($A:$M))>1)*ROW($1:$13),0),2)逻辑判断后利用IFFERROR函数将错误值转换为0,然后提取到1-13中文本字符对应的数字。再用LARGE函数将第二大的数字提取出来。
怎么样确定文本间有没有数字呢?其实已经判断了!公式中FREQUENCY()>1这部分就是这个作用。如果FREQUENCY函数计频的结果是1,那说明数字是连续的。屏除计频点为1的数字,就是除去了没有数字间隔的文本。
这个就是OFFSET函数的第三参数,也是OFFSET函数横向偏移的量。
数字1是OFFSET函数的第四参数,是偏移后区域的行高。
SUM(LARGE(IFERROR((FREQUENCY(COLUMN($A:$M),ISTEXT($A2:$M2)*COLUMN($A:$M))>1)*ROW($1:$13),0),{2,1})*{-1,1})-1这部分是OFFSET函数的第五参数,是偏移后区域的列宽。
IFERROR((FREQUENCY(COLUMN($A:$M),ISTEXT($A2:$M2)*COLUMN($A:$M))>1)*ROW($1:$13),0)这部分返回的结果是{0;0;0;0;5;0;0;0;0;10;0;12;0;0},和前面部分介绍的思路是一样的。
LARGE(IFERROR((FREQUENCY(COLUMN($A:$M),ISTEXT($A2:$M2)*COLUMN($A:$M))>1)*ROW($1:$13),0),{2,1})利用LARGE函数提取到第1大值和第2大值。
SUM(LARGE(IFERROR((FREQUENCY(COLUMN($A:$M),ISTEXT($A2:$M2)*COLUMN($A:$M))>1)*ROW($1:$13),0),{2,1})*{-1,1})-1再乘以{-1,1},利用SUM函数求得列宽。
我就知道你“在看”
















































































