夜雨聆风学习资料网

ARTICLE · 1390022

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

哪个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函数求得列宽。

好了朋友们,今天和大家分享的内容就是这些了!喜欢我的文章请分享、转发、点赞和收藏吧!如有任何问题可以随时私信我哦!

我就知道你“在看”

推荐阅读

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料