湖南龙网

  1. 首页 > 生活百科 > >

excel怎么从矩阵数组中返回满足条件的所有组合数( 七 )

小知识生活百科


MMULT(IFERROR(N(OFFSET(A1,IF(MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT(“1234:4321”))))),{1;1;1;1})=4,MID(ROW(INDIRECT(“1234:4321″)),{1,2,3,4},1)-1,””),{0,1,2,3},,)),0),{1;1;1;1})
转换为：
MMULT({1,6,3,2;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;1,6,4,2;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;1,7,2,2;…},{1;1;1;1})
得到：
{12;0;0;0;0;0;0;0;0;13;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;12;…}
然后，与单元格F2中的目标值比较：
=SUM(0+(MMULT(IFERROR(N(OFFSET(A1,IF(MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT(“1234:4321”))))),{1;1;1;1})=4,MID(ROW(INDIRECT(“1234:4321″)),{1,2,3,4},1)-1,””),{0,1,2,3},,)),0),{1;1;1;1})=F2))
转换为：
=SUM(0+({12;0;0;0;0;0;0;0;0;13;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;12;…}=12))
可以返回结果：
6
至此，公式解析完毕 。
现在，我们来看一下减少数组元素的方法 。上文中，由
ROW(INDIRECT(“1234:4321″))
生成了含有3088个元素的数组，而我们只对其中的24个元素感兴趣，也就是由1234进行排列的元素，因此与我们无关的有3064个元素，占总元素的99%以上 。
这里首先将这个数组减少到只有256个元素，这样无关元素比提高至了91% 。采取下面的公式构造：
TEXT(MMULT(MOD(INT((ROW(1:256)-1)/4^{3,2,1,0}),4),10^{3;2;1;0}),”0000″)
这种构造背后的原理是Excel与数学的综合 。如果我们减少元素的数量，只考虑3×3的类似构造，将更容易解释，也更容易理解 。此时，上面的公式构造等价于：
TEXT(MMULT(MOD(INT((ROW(1:27)-1)/3^{2,1,0}),3),10^{2;1;0}),”000”)
暂时不考虑TEXT和MMULT函数，此公式构造的关键是生成一个由三个整数组成的数组，包含数字0、1和2的所有可能排列 。即：
{0,0,0;0,0,1;0,0,2;0,1,0;0,1,1;0,1,2;0,2,0;0,2,1;0,2,2;1,0,0;1,0,1;1,0,2;1,1,0;1,1,1;1,1,2;1,2,0;1,2,1;1,2,2;2,0,0;2,0,1;2,0,2;2,1,0;2,1,1;2,1,2;2,2,0;2,2,1;2,2,2}
这是上述构造中取27的原因 。对于4个元素取256，因为n的元素的可能排列数为n^n，所以3^3=27，4^4=256 。
这样，公式构造中的：
MOD(INT((ROW(1:27)-1)/3^{2,1,0}),3)
将转换成的数组是什么呢?
实际上，我们在这里所做的就是将一系列以10为底的值转换为以3为底的值 。因此，以10为底的0到26之间的值，我们可以用3的底数表示的等效表为：

excel怎么从矩阵数组中返回满足条件的所有组合数

文章插图
图3
这正是我们要生成的27个排列 。
对于以10为底的给定值，为了确定该值的以3为底的表示形式中存在的3^2、3^1和3^0的数量，首先确定3^2、3^1和3^0的以基数为10的值，然后对所得值取模(模为3) 。例如，以10为底的值7，以3为底的值的表示形式为021，由于3^2=9在7中出现0次且MOD(0,3)=0，3^1=3在7中出现2次且MOD(2,3)=2，3^0=1在7中出现1次且MOD(1,3)=1 。这意味着：
MOD(INT((ROW(1:27)-1)/3^{2,1,0}),3)

推荐阅读


上一篇：PPT 图片、图形对象设置教程及技巧

下一篇：excel怎么在多个工作表满足条件的值