Excel从多列各取一个单元格值进行组合一例

成长路上除了

    最近在工作中遇到一个问题，需要在Excel中从多列各取一个单元格值进行组合，得到全部的组合数据。如图所示，要得到“颜色”、“领型”和“尺码”的全部组合，放到以E2为左上角的区域中。经过一番摸索后总结了几种方法，分别用公式、 VBA、Microsoft Queny和Access来实现，写在这里供大家参考。以下图的三列为例，所用的Office版本为Excel2013和Access2013，其他版本的操作方法类似。

方法一：公式法

       本例“颜色”有5种，“领型”有4种，“尺码”有6种，因而序号有5×4×6个，在E2输入公式得到序号：

   =IF(ROW(A1)>(COUNTA(A$2:A$10)*COUNTA(B$2:B$10)*COUNTA(C$2:C$10)),"",ROW(A1))

       每种“颜色”有4×6个，可用OFFSET函数在F列依次得到。在F2输入公式：

    =IF(E2="","",OFFSET(A$1,(COUNTA(B$2:B$10)*COUNTA(C$2:C$10)+ROW(A1)-1)/
(COUNTA(B$2:B$10)*COUNTA(C$2:C$10)),))

       对应每种颜色，每种“领型”有6个，可用OFFSET和MOD函数组合在G列得到结果。在G2输入公式：

    =IF(E2="","",OFFSET(B$2,MOD((COUNTA(C$2:C$10)+ROW(A1)-1)/COUNTA(C$2:C$10)
-1,COUNTA(B$2:B$10)),))

       同样，在H2输入公式得到循环出现的“尺码”：

    =IF(E2="","",OFFSET(C$1,MOD(ROW(A1)-1,COUNTA(C$2:C$10))+1,))

       为了让添加或删除数据后也能得到全部组合结果，公式中用COUNTA函数得到各列数据的个数。选择E2:H2向下填充公式即可得到全部的组合。

       方法二：用VBA

       按Alt+F11，打开VBA编辑器，在代码窗口中输入代码：

Sub 三列各取一个单元格值组合()
  Dim ColA, ColB, ColC, Result()
  Dim i As Integer, j As Integer, k As Integer, m As Integer
  Dim Total As Long
  
  ColA = Range("A2", [A1].End(xlDown))
  ColB = Range("B2", [B1].End(xlDown))
  ColC = Range("C2", [C1].End(xlDown))
  
  Total = UBound(ColA) * UBound(ColB) * UBound(ColC)
  ReDim Result(1 To Total, 1 To 4)
  For i = 1 To UBound(ColA)
        For j = 1 To UBound(ColB)
            For k = 1 To UBound(ColC)
                m = m + 1
                Result(m, 1) = m
                Result(m, 2) = ColA(i, 1)
                Result(m, 3) = ColB(j, 1)
                Result(m, 4) = ColC(k, 1)
            Next k
        Next j
  Next i
  
  Range("E2", [H2].End(xlDown)).ClearContents
  Range("E2:H2").Resize(m).Value = Result
End Sub

       运行后即可在E2:H2列得到所有的组合结果。