|
具体我们直接开干！ 看一个常见问题，排班，6人，每次需要3人，共有多少种组合？ 
友情提醒：下面公式第一眼看上去，100%晕，感兴趣的可以看下面的详解，或者保存公式，以后套用 ▼公式 =TEXTJOIN('|',,REPT($A$1:$A$5,MID(LET(组合,BASE(ROW($1:$31),2,5),TEXT(SMALL(--IF(LEN(组合)-LEN(SUBSTITUTE(组合,1,))=3,组合,9^9),ROW(A1)),'00000')),ROW($1:$5),1))) 
下面我们就来详细说说这公式及思路！ ▍思考01 ：M取N个组合有多少个？ 这个问题，我们可以通过Excel的函数来处理，COMBIN(M,N） 本案例中，5取3，所以我们的组合结果为：
得到这个，我们可以验证结果是符合要求！
▍思考02：组合的原理 所谓组合，也就是从M个中取3个全部组合，那么本质就是取或者不取，这个东西是不是和2进制中1和0一样，1代表取,0代表不取，如果我们列出所有的取和不取组合，那么这个问题就简单了
说到这里，我们就不得不说一下BASE函数 BASE函数：进制转换，3个参数 >参数1：数值
>参数2：进制 > 参数3：长度 比如我们都知道10的二进制是 1010，一般采用 除2反向取余法 第一次：10/2=5余0 第二次：5/2 =2余1 第三次：2/2 =1余0 第四次：1/2 =0余1
进制的问题，我们解决了，下面就是如何列出所有的组合 5个 分别取1个、2个、3个、4个和5个，一共有多少种组合？ 学过COMBIN，简单了！分别再相加即可！合计31
根据他们的算法，有一个简单的规律 就是2^n-1 比如这里的 5个，就是2^5-1=31 ,所以后面我们使用第二种算法处理！ 我们把1-31 全部转成2进制，就可以得到全部的组合情况，正好31组，分别是0和1组成，可以大概看看他们的规律！
那么我们要的5选3，也就是其中正好于3个1，其他都是0即可！
那我们如何找到内容中正好有3个1的部分呢？，替换法，如果把1替换掉的长度和原来的长度差额正好是3，说明满足条件
那么我们就是把这些都提取出来，按照按照位置去提取即可！0不取，1取！ 我们整合一下：
=TEXT(SMALL(--IF( LEN(BASE(ROW($1:$31),2,5))-LEN(SUBSTITUTE(BASE(ROW($1:$31),2,5),1,))=3, BASE(ROW($1:$31),2,5),9^9),ROW(A1)),'00000')
还是有点看不懂是吧！？ 公式比较简单，只是较长吧，我们可以利用LET函数简化一下，大家就能很好明白！
说明：把1替换成空，如果原来的内容长度-替换后的内容长度=3，说明正好有3个1，提取3个内容，把这部分使用SMALL逐个提取出来即可！
有了这个，下面就是如何和内容关联上了！
▍思考03：提取内容 1表示提取1,0不取，那么我们就需要把每一位截取出来，然后通过IF判断即可，或者使用常规套路REPT处理！
逐位提取！ 
那么如何对应数据源呢？Excel TRUE对应非0，FASLE对应0，那么正好可以利用1和0对应IF是否满足条件 =IF(--MID(C1,COLUMN(A:E),1),TRANSPOSE(A1:A5),'')
为了演示直观，我们截取位多列，其实多行即可！这样就不用转置了！同时IF部分我们可以使用REPT函数代替！，重复0次就是空值，重复1就是本身！
最后使用TEXTJOIN合并起来即可，利用第二参数忽略控制 =TEXTJOIN('|',,REPT($A$1:$A$5,MID(C1,ROW($1:$5),1)))
那我们把所有部分合并起来一起写就成了最后的公式！ ▼没有LET函数的朋友，BASE部分多写几次即可！ =TEXTJOIN('|',,REPT($A$1:$A$5,MID(LET(组合,BASE(ROW($1:$31),2,5),TEXT(SMALL(--IF(LEN(组合)-LEN(SUBSTITUTE(组合,1,))=3,组合,9^9),ROW(A1)),'00000')),ROW($1:$5),1)))
根据上面的思路，其实我们可以总结出一个通用一些的公式：
如果内容在A列，取N个在D5 ▼通用公式 =IFERROR(TEXTJOIN(',',,REPT(OFFSET($A$1,,,COUNTA($A:$A)),MID(BASE(LARGE(IF(LEN(SUBSTITUTE(BASE(ROW(INDIRECT('1:'&2^COUNTA($A:$A))),2,COUNTA($A:$A)),0,))=D$5,ROW(INDIRECT('1:'&2^COUNTA($A:$A)))),ROW(A1)),2,COUNTA($A:$A)),ROW(INDIRECT('1:'&COUNTA($A:$A))),1))),'')
OK！今天的内容就到这里，快速试试吧！
|
