寻求生成列值全组合的Excel公式（非VBA，用于C列）

适用Excel 365/2021（动态数组版本） #

直接在C1单元格输入以下公式，公式会自动溢出填充所有组合：

=TOCOL(FILTER(A:A,A:A<>"")&" "&TOROW(FILTER(B:B,B:B<>"")))

• 公式说明：
  • FILTER(A:A,A:A<>"")：提取A列所有非空的颜色值
  • TOROW(FILTER(B:B,B:B<>""))：提取B列所有非空的数字值并转为单行
  • 两者通过&" "&拼接成「颜色 数字」格式，生成二维数组
  • TOCOL将二维数组转为单列，自动溢出到下方单元格

适用旧版Excel（非动态数组版本） #

在C1单元格输入以下公式，手动下拉填充至所有组合完成（总组合数为COUNTA(A:A)*COUNTA(B:B)）：

=INDEX(FILTER(A:A,A:A<>""),INT((ROWS($C$1:C1)-1)/COUNTA(B:B))+1)&" "&INDEX(FILTER(B:B,B:B<>""),MOD(ROWS($C$1:C1)-1,COUNTA(B:B))+1)

• 公式说明：
  • INT((ROWS($C$1:C1)-1)/COUNTA(B:B))+1：计算当前行对应的颜色索引，循环遍历所有颜色
  • MOD(ROWS($C$1:C1)-1,COUNTA(B:B))+1：计算当前行对应的数字索引，循环遍历所有数字
  • INDEX分别取出对应颜色和数字，拼接成目标格式

示例效果 #

内容的提问来源于stack exchange，提问作者Carly Carlyle Adams Cnf