如何获取Excel中的列等笛卡尔积? [英] How can i obtain cartesian product like columns in excel?

问题描述

我有2列:

Col1    Col2 
  1       A
  2       B
  3       C

我需要得到的是仅使用Excel将这些列的所有可能组合转换为新列.那可能吗?我没有使用Excel的经验.

And what i need to get is all the possible combinations of those columns into new columns using just Excel. Is that possible? I'm not experienced using excel.

预期结果:

Col3 Col4 Col5 Col6 Col7 Col8
  1   A     2    A   3    A
  1   B     2    B   3    B
  1   C     2    C   3    C

提前谢谢！

推荐答案

以下是关于一种可能性的几点说明.

Here are a few notes on one possibility.

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
    & ThisWorkbook.FullName _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

''Late binding, so no reference is needed

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set rs2 = CreateObject("ADODB.Recordset")

cn.Open strCon


strsql = "SELECT * " _
       & "FROM [Sheet2$a:a]"

rs2.Open strsql, cn, 3, 3

i = 1
Do While Not rs2.EOF
    strsql = "SELECT * " _
           & "FROM [Sheet2$a:a] a, " _
           & "[Sheet2$b:b] b " _
           & "WHERE Col1 = " & rs2!Col1

    rs.Open strsql, cn, 3, 3

    ''Pick a suitable empty worksheet for the results
    Worksheets("Sheet3").Cells(2, i).CopyFromRecordset rs

    i = i + rs.fields.Count

    rs2.movenext
    rs.Close
Loop

''Tidy up
Set rs = Nothing
rs2.Close
Set rs2 = Nothing
cn.Close
Set cn = Nothing

这篇关于如何获取Excel中的列等笛卡尔积?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！