湖南龙网

  1. 首页 > 生活百科 > >

excel公式教程：求字符串中的数字组成的数能够被指定数整除的数的个数

小知识生活百科

在单元格A1中输入一个任意长度的字母数字字符串，请使用公式返回该字符串中能够被3、5或7整除的数字的数量 。这里，“字符串中的数字”指字符串中可以被认为是数字的任意长度的连续子字符串 。例如，字符串：
XX30X5XXX42XX771
包含有13个数字：3，0，30，5，4，2，42，7，7，1，77，71，771
因为：
3可以被3整除
0可以被3、5或7整除
30可以被3或5整除
5可以被5整除
4不能被3、5或7整除
2不能被3、5或7整除
42可以被3或7整除
7可以被7整除
7可以被7整除
1不能被3、5或7整除
77可以被7整除
71不能被3、5或7整除
771可以被3整除
所以，该字符串中能够被3、5或7整除的数字的数量为9 。
那么，如何编写这个公式求出这个数量呢？
先不看答案，自已动手试一试 。
公式
所需要的数组公式：
=SUM(0+(MMULT(IFERROR(0+(MOD(INDEX(MID(A1,Arry1,IF(1+LEN(A1)-Arry1>=TRANSPOSE(Arry1),TRANSPOSE(Arry1))),N(IF(1,Arry3)),N(IF(1,Arry4))),{3,5,7})=0),0),{1;1;1})>0))
公式解析
公式中的Arry1、Arry2、Arry3和Arry4是定义的四个名称 。
名称：Arry1
引用位置：=ROW(INDIRECT(“1:” & LEN($A1)))
名称：Arry2
引用位置：=ROW(INDIRECT(“1:” & LEN($A1)^2))-1
名称：Arry3
引用位置：=1+INT((Arry2)/LEN($A1))
名称：Arry4
引用位置：=1+MOD((Arry2),LEN($A1))
1. 先来看看MID函数部分：
MID(A1,Arry1,IF(1+LEN(A1)-Arry1>=TRANSPOSE(Arry1),TRANSPOSE(Arry1)))
依次来解析MID函数的两个参数 。
（1）对于第一个参数start_num，即Arry1：
ROW(INDIRECT(“1:” & LEN($A1)))
生成从1到单元格A1中字符串长度的整数组成的数组：
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}
（2）对于第二个参数num_chars，即：
IF(1+LEN(A1)-Arry1>=TRANSPOSE(Arry1),TRANSPOSE(Arry1))
在对此IF语句进行解析之前，先看一个常用的方式，即对于该参数简单地指定其为：
TRANSPOSE(Arry1)，即：
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}
此时的MID函数部分为：
MID(A1,Arry1,TRANSPOSE(Arry1))
转换为：
MID(“XX30X5XXX42XX771”,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16},{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16})
可以看到，传递给函数的两个数组是正交的：一个是单列数组（对应参数start_num），另一个是单行数组（对应参数num_chars） 。这样，这两个数组将生成一个16行16列的矩阵，包含MID函数的所有256个可能结果 。例如，生成的数组的第一个元素等于MID(A1,1,1)的结果“X”，第二个元素等于MID(A1,1,2)的结果“XX”，依此类推，最终的结果为：
{“X”,”XX”,”XX3″,”XX30″,”XX30X”,”XX30X5″,”XX30X5X”,”XX30X5XX”,”XX30X5XXX”,”XX30X5XXX4″,”XX30X5XXX42″,”XX30X5XXX42X”,”XX30X5XXX42XX”,”XX30X5XXX42XX7″,”XX30X5XXX42XX77″,”XX30X5XXX42XX771″;”X”,”X3″,”X30″,”X30X”,”X30X5″,”X30X5X”,”X30X5XX”,”X30X5XXX”,”X30X5XXX4″,”X30X5XXX42″,”X30X5XXX42X”,”X30X5XXX42XX”,”X30X5XXX42XX7″,”X30X5XXX42XX77″,”X30X5XXX42XX771″,”X30X5XXX42XX771″;”3″,”30″,”30X”,”30X5″,”30X5X”,”30X5XX”,”30X5XXX”,”30X5XXX4″,”30X5XXX42″,”30X5XXX42X”,”30X5XXX42XX”,”30X5XXX42XX7″,”30X5XXX42XX77″,”30X5XXX42XX771″,”30X5XXX42XX771″,”30X5XXX42XX771″;”0″,”0X”,”0X5″,”0X5X”,”0X5XX”,”0X5XXX”,”0X5XXX4″,”0X5XXX42″,”0X5XXX42X”,”0X5XXX42XX”,”0X5XXX42XX7″,”0X5XXX42XX77″,”0X5XXX42XX771″,”0X5XXX42XX771″,”0X5XXX42XX771″,”0X5XXX42XX771″;”X”,”X5″,”X5X”,”X5XX”,”X5XXX”,”X5XXX4″,”X5XXX42″,”X5XXX42X”,”X5XXX42XX”,”X5XXX42XX7″,”X5XXX42XX77″,”X5XXX42XX771″,”X5XXX42XX771″,”X5XXX42XX771″,”X5XXX42XX771″,”X5XXX42XX771″;”5″,”5X”,”5XX”,”5XXX”,”5XXX4″,”5XXX42″,”5XXX42X”,”5XXX42XX”,”5XXX42XX7″,”5XXX42XX77″,”5XXX42XX771″,”5XXX42XX771″,”5XXX42XX771″,”5XXX42XX771″,”5XXX42XX771″,”5XXX42XX771″;”X”,”XX”,”XXX”,”XXX4″,”XXX42″,”XXX42X”,”XXX42XX”,”XXX42XX7″,”XXX42XX77″,”XXX42XX771″,”XXX42XX771″,”XXX42XX771″,”XXX42XX771″,”XXX42XX771″,”XXX42XX771″,”XXX42XX771″;”X”,”XX”,”XX4″,”XX42″,”XX42X”,”XX42XX”,”XX42XX7″,”XX42XX77″,”XX42XX771″,”XX42XX771″,”XX42XX771″,”XX42XX771″,”XX42XX771″,”XX42XX771″,”XX42XX771″,”XX42XX771″;”X”,”X4″,”X42″,”X42X”,”X42XX”,”X42XX7″,”X42XX77″,”X42XX771″,”X42XX771″,”X42XX771″,”X42XX771″,”X42XX771″,”X42XX771″,”X42XX771″,”X42XX771″,”X42XX771″;”4″,”42″,”42X”,”42XX”,”42XX7″,”42XX77″,”42XX771″,”42XX771″,”42XX771″,”42XX771″,”42XX771″,”42XX771″,”42XX771″,”42XX771″,”42XX771″,”42XX771″;”2″,”2X”,”2XX”,”2XX7″,”2XX77″,”2XX771″,”2XX771″,”2XX771″,”2XX771″,”2XX771″,”2XX771″,”2XX771″,”2XX771″,”2XX771″,”2XX771″,”2XX771″;”X”,”XX”,”XX7″,”XX77″,”XX771″,”XX771″,”XX771″,”XX771″,”XX771″,”XX771″,”XX771″,”XX771″,”XX771″,”XX771″,”XX771″,”XX771″;”X”,”X7″,”X77″,”X771″,”X771″,”X771″,”X771″,”X771″,”X771″,”X771″,”X771″,”X771″,”X771″,”X771″,”X771″,”X771″;”7″,”77″,”771″,”771″,”771″,”771″,”771″,”771″,”771″,”771″,”771″,”771″,”771″,”771″,”771″,”771″;”7″,”71″,”71″,”71″,”71″,”71″,”71″,”71″,”71″,”71″,”71″,”71″,”71″,”71″,”71″,”71″;”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″}

推荐阅读


上一篇：粥放冰箱里能保存几天

下一篇：pandigitals excel公式教程： 1/17和其他全数字