实现 Excel 多列数据组合

前言

因好朋友的工作要求需要在 Excel上实现 3种数据的排列组合，如果使用编程语言无非就是嵌套循环，而要在 Excel 实现没怎么实现过，在实现后觉得挺有意思的特此记录方便后续非工程师的人员可以方便实现该需求。

效果预览

Excel函数须知

COUNTA()
用于解决统计某列非空单元格数目，常用的一般是 COUNT()，但是他无法处理非数字列的统计，本文用于计算集合的最大值。
举例 COUNTA(A:A)，效果图中做了减 1的操作，所以显示为 3

INDEX()
在给定单元格区域中，返回特定的单元格值或者引用，本文用于赋值。
举例 INDEX(A:A, 2), 返回的结果就是 上海。

ROW()
返回一个引用的行号，本文用于实现遍历。

MOD()
返回两数相除的余数，本文用于实现递归循环。

实现说明

如效果图的结果来看

在 J列的函数公式：INDEX(A:A,(ROW(J2)-2)/($F$2 * $G$2) + 2)。

步骤	公式	说明
0	ROW(J2)-2	因为需要过滤掉 表头，并且从 0 开始所以需要 - 2
1	$F$2 * $G$2	即产品名称和配件的组合数目，即循环次数， 2 * 5
2	(ROW(J2)-2) / ($F$2 * $G$2)	取整数进行分类汇总
3	INDEX(A:A, (ROW(J2)-2) / ($F$2 * $G$2) + 2）	在 A列范围内，而 + 2 则是确保正确取值过滤无意义数据，即A0,A1

如此实现，那 I列数据就会如下图成功遍历 30条有效数据（3 * 2 * 10）

在 K列的函数公式：=INDEX(B:B,MOD((ROW($J2)-2) / $G$2,$F$2) + 2)
在 L列的函数公式：=INDEX(C:C,MOD(ROW($J2)-2)),$G$2) + 2)

上述公式一般都大同小异，需要注意是 K 列是 / $G$2，L列 没有 / （可以理解为 / 1），这就是对应的循环次数的不一致，从而实现程序上的嵌套循环。

想法

很有趣的体验，需了解循环次数防止异常，又要合理使用 $符号确保支持水平扩展，从实现复杂度来看非常简单，不过跟用程序实现，前者更具成就感。