辅助列解决组合问题的一个案例

世界客都

前 言
   论坛的各位老师大家晚上好啊，今天是2024年的5月30日，很久没有写文章了，今天想简单来写一写。
   这篇文章的内容基于以下论坛求助帖：https://club.excelhome.net/threa ... tml?_dsign=c3ddc249。
   如果是每天都要汇总的数据，我当然选择用VBA写个代码或者用365高版本公式或其它低版本长公式一次性汇总，每天上班的时候运行一下就可以得出结果，一劳永逸，虽然前期写代码写公式辛苦点，但没所谓，反正每天都要做的。但如果是只需要做一次的或者很急的数据汇总呢？我会毫无犹豫选择用辅助列来完成，用辅助列可以大大降低汇总数据的难度，性价比很高。
正 文
   在上述帖子中，楼主问到的是10支球队，分5组的分法。
   首先，我们先看看分法有几种，看看数据规模有多大。然后再根据计算的方法看看怎么来写辅助列。
第一种解法：首先我们对10支球队进行一个全排列，结果是=PERMUT(10,10)=3628800种。这里有些朋友可能对排列有一些疑问，我数学书上学的排列是用A来表示的，为何表格用PERMUT来表示，对组合COMBIN大家倒没啥异议，因为书上也是用COMBIN首字母C表示组合。书上的A取自英文Arrangement的首字母，其实：排列的真正准确英文是Permutation，应该用P表示。在2000年以前的数学书籍（尤其是70,80年代的经典数学书籍）中，全部采用P表示排列，但是到2000年后的书却变成了用A表示排列，实在是搞不懂。
继续......我们用12345678910这10个数字代表10支球队的名字，我们来看看其中一个组合方式：
{3,10}，{5,1}，{2,7}，{4,9}，{8,6}
我们显然注意到：其实这5个组合谁排在前，谁排在后都是同一个组合，也就是说，把这5个组进行一个全排列
得到的=PERMUT(5,5)=120种排列其实都对应一个组合方式就是：{3,10}，{5,1}，{2,7}，{4,9}，{8,6}
紧接着，其实这5个组合里面的2个元素互换位置，得到的显然还是同一个组合方式，例如3,10互换，4,9互换等。
那么显然一共有2*2*2*2*2=32种组合方式都对应
{3,10}，{5,1}，{2,7}，{4,9}，{8,6}这种方式
于是最后的结果为：
=PERMUT(10,10)/PERMUT(5,5)/2^5=945种组合方式。
第二种解法：
将10支球队分成5组，第1组有COMBIN(10,2)种选择，第2组有COMBIN(8,2)种选择，第3组有COMBIN(6,2)种选择，第4组有COMBIN(4,2)种选择，第5组只有2个人了显然只有1种选择。
但最后的组合数并不是COMBIN(10,2)*COMBIN(8,2)*COMBIN(6,2)*COMBIN(4,2)
这是因为，例如：
{3,10}，{5,1}，{2,7}，{4,9}，{8,6}
这种和
{3,10}，{5,1}，{4,9}，{8,6}，{2,7}
这种其实是同一个组合方式
一共有PERMUT(5,5)种组合方式对应的其实是一种组合方式，所以答案是：COMBIN(10,2)*COMBIN(8,2)*COMBIN(6,2)*COMBIN(4,2)/PERMUT(5,5)
第三种解法：
我们还是用12345678910这10个数字代表10支球队的名字，跟1组合的方式有多少种？显然是9种。
那么剩下8个人的组合方式有多少种？咦，我们可以仿照刚刚的做法，也就是先定1个人，再找有几种方式跟这个人进行组合，显然是7个人。以此类推，答案就是9*7*5*3
因为最后只剩下了2个人，就只有1种方法了。
用EXCEL的函数就是FACTDOUBLE（双倍阶乘）表示就是：
FACTDOUBLE(9)
根据以上讨论，假如是偶数个球队（设为N，N>0）两两分组有几种方案呢？答案就是FACTDOUBLE(N-1)
在这里我要赞赏一下原贴11楼的丢丢表格老师，我以为知道这个算法的人不是很多呢。。。哈哈。丢丢老师真见多识广哈。
按照解法3的思路，在附件中，我们假设10支球队的名字为：A,B,C,.....J。
首先先在B列生成一个序列，1-945的数字序列，这是方便我们在接下来的列中鼠标双击左键可以快速填充公式。
紧接着，我在C-L列用了10个公式来生成组合情况。
大家不要认为10个公式啊，这么麻烦，其实这10个公式的原理都是非常简单的，而且格式都是高度统一的。这10个公式简简单单就可以合并成一个公式右拉下拉：就是

1. =INDEX($A:$A,SMALL(IF(COUNTIF($B1:B1,$A$1:$A$10),"",ROW($1:$10)),(INT(MOD(ROW(A1)-1,FACTDOUBLE(13-COLUMN()))/FACTDOUBLE(MAX(1,11-COLUMN())))+1)^ISEVEN(COLUMN())))

复制代码

我拆开了10个公式目的就是为了让大家清晰的知道每一步的计算过程。当然是十分简单的。
   最后，感谢大家阅读我的文章，谢谢支持。——世界客都2024/5/30
    辅助列.zip (72.69 KB, 下载次数: 21)

2024-5-30 22:49 上传

点击文件名下载附件

thy645

菜鸟，看不懂。

499279666

帮一个萌新交一下。

1. =LET(g,BASE(SEQUENCE(2^10-1),2,10),k,MID(UNIQUE(REDUCE(1,{2;3;4;5},LAMBDA(X,Y,TOCOL(REPLACE(X,SEQUENCE(,Y+1),,Y))))),{1,2,3,4,5},1),m,HSTACK(k,{6,7,8,9,10}*k^0),n,BYROW(--MID(FILTER(g,LEN(SUBSTITUTE(g,0,))=5),COLUMN(A:J),1),LAMBDA(s,CONCAT(SORTBY(TOROW(A1:A10),s)))),UNIQUE(DROP(REDUCE(0,n,LAMBDA(x,y,VSTACK(x,BYROW(m,LAMBDA(r,TEXTJOIN(",",,SORT(BYCOL(WRAPROWS(MID(y,r,1),5),LAMBDA(h,CONCAT(SORT(h)))),,,1))))))),1)))

复制代码

魚。。

学习完了，捧个场。VB来一个

你是那块小饼干

我 脑子不够用