excelperfect
标签:Excel公式练习
单元格中包含着一个数字,要求组成这个数字的各位数之和,例如,单元格中的数字是“123”,那么其各位数之和=1+2+3=6。
这类示例在完美Excel公众号中经常出现,就是因为很多公式都经常会使用其中的拆分技巧。这里,再把它拿出来,反复温习。
示例数据如下图1所示。
图1
下面是我首先想到的公式1:
=SUMPRODUCT(MID(B3,ROW(INDIRECT('1:' &LEN(B3))),1)*1)
在单元格C3中输入后,下拉至单元格C10。
公式中,使用:
ROW(INDIRECT('1:'& LEN(B3)))
得到数组:
{1;2;3;4;5;6;7;8;9}
其作为MID函数的参数:
MID(B3,{1;2;3;4;5;6;7;8;9},1)
得到数组:
{'1';'2';'3';'4';'5';'6';'7';'8';'9'}
从而完成了单元格B3中的数字拆分。
将上述结果乘以1转换为数字,最后使用SUMPRODUCT函数求和。
也可以使用公式2:
=SUMPRODUCT(MID(B3,ROW(OFFSET($A$1,,,LEN(B3))),1)+0)
公式中,使用的是:
ROW(OFFSET($A$1,,,LEN(B3)))
得到数组:
{1;2;3;4;5;6;7;8;9}
加上0,将MID函数得到的数字文本转换为数字,让SUMPRODUCT函数对它们进行求和。
还可以使用公式3:
=SUMPRODUCT(MID(B3,ROW($A$1:INDEX(A:A,LEN(B3))),1)+0)
公式中,使用的是:
ROW($A$1:INDEX(A:A,LEN(B3)))
得到数组:
{1;2;3;4;5;6;7;8;9}
看到了吗?这里有三种生成连续数字的方法,即:
ROW(INDIRECT('1:'& LEN(B3)))
或者:
ROW(OFFSET($A$1,,,LEN(B3)))
或者:
ROW($A$1:INDEX(A:A,LEN(B3)))
然后,可以使用它作为MID函数的参数,来拆分数据成单个。
还可以使用下面的数组公式4:
=SUM(MOD(INT(B3/10^( CEILING(LOG(B3),1)-ROW(INDIRECT('a1:a'&(CEILING(LOG(B3),1)))) ) ),10))
很巧妙!有兴趣的朋友可以研究一下其运行原理。
你还有更好的公式吗?欢迎留言。
注:你可以到知识星球完美Excel社群下载本文配套示例工作簿,方便对照研习。
联系客服