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列得到所有的组合结果。

    方法三：用Microsoft Query

1.定义名称：

    选择A列数据所在的A1:A6区域，选择“公式→定义名称”，弹出“定义名称”对话框。

   

    单击“确定”，Excel新建一个名称“颜色”，区域为“A1:A6"。依次选择另外两列数据定义名称“领型”和“尺码”。保存工作簿。

    2.用Microsoft Query建立查询。

    在“数据”选项卡的“获取外部数据”组中，选择“自其他来源→来自Microsoft Query”，弹出“选择数据源”对话框。

   

    双击“Excel Files*”，弹出“选择工作簿”对话框，找到并选择刚才保持的工作簿，本例为“工作簿2.xlsx”，单击“确定”。

   

    弹出“查询向导”对话框，依次将“颜色”、“领型”和“尺码”添加到右侧区域中。注意添加的顺序将影响到最终结果中列的位置。

   

    单击对话框右上角的“×”或“取消”按钮关闭对话框。弹出“是否要在Microsoft Query中编辑此查询？”对话框，单击“是”。

  

    返回到Microsoft Query查询窗口，可以看到所建立的查询已符合我们的要求。

   

    单击“文件→将数据返回Microsoft Excel”，在弹出的对话框中选择数据的存放位置，本例为以E1为左上角的区域，故选择E1单元格。

   

    单击“确定”，得到三列各单元格值全部组合的结果。

   

    方法四：用Access

1.打开Access2013，新建一个空数据库。

    2.在Excel中复制三列数据所在的区域A1:C7，然后在Access窗口左侧的“所有Access对象”窗格中右击，选择“粘贴”。

   

    弹出一个对话框，提示是否让数据的第一行包含标题，选择“是”。

   

    这样就在数据库中建立了一个表，名称为“Sheet1”。再进行两次粘贴，建立另外的两个表“Sheet11”和“Sheet12”。

   

    3.在Access2013中选择“创建→查询设计”，弹出“显示表”对话框，依次双击“表”选项卡中刚才新建的三个表添加到查询中。

   

    4.关闭“显示表”对话框，在查询下方的区域中选择三个表中的不同字段。由于表中的某列包括空值，在“颜色”和“领型”字段中添加条件“<>""”以排除空值。

   

    5.选择“查询工具-设计”选项卡，单击“运行”，得到查询结果。

   

    6.按Ctrl+A全选查询结果并复制，将其粘贴到Excel中即可。