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向下填充公式即可得到全部的组合。