SQL 有没有办法在excel中执行交叉连接或笛卡尔积？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26999604/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to perform a cross join or Cartesian product in excel?
提问by user1248831
At the moment, I cannot use a typical database so am using excel temporarily. Any ideas?
目前，我无法使用典型的数据库，因此暂时使用 excel。有任何想法吗？
The 
这
回答by BusinessAlchemist
You have 3 dimensions here: dim1 (ABC), dim2 (123), dim3 (XYZ).
这里有 3 个维度：dim1 (ABC)、dim2 (123)、dim3 (XYZ)。
Here is how you make a cartesian product of 2 dimensions using standard Excel and no VBA:
以下是使用标准 Excel 和无 VBA 制作二维笛卡尔积的方法：
1) Plot dim1 vertically and dim2 horizontally. Concatenate dimension members on the intersections:
1）垂直绘制dim1，水平绘制dim2。在交叉点上连接维度成员：
2) Unpivoting data. Launch pivot table wizard using ALT-D-P (don't hold ALT, press it once). Pick "Multiple consolidation ranges" --> create a single page.. --> Select all cells (including headers!) and add it to the list, press next.
2) 逆向数据。使用 ALT-DP 启动数据透视表向导（不要按住 ALT，按一次）。选择“多个合并范围”--> 创建单个页面..--> 选择所有单元格（包括标题！）并将其添加到列表中，按下一步。
3) Plot the resulting valuesvertically and disassemble the concatenated strings
3)垂直绘制结果值并分解连接的字符串
Voila, you've got the cross join. If you need another dimension added, repeat this algorithm again.
瞧，你已经得到了交叉连接。如果您需要添加另一个维度，请再次重复此算法。
Cheers,
干杯，
Constantine.
康斯坦丁。
回答by Terry
This article helped me perform a cross join in Excel:
这篇文章帮助我在 Excel 中执行了交叉联接：
http://www.excelguru.ca/blog/2016/05/11/cartesian-product-joins-for-the-excel-person/
http://www.excelguru.ca/blog/2016/05/11/cartesian-product-joins-for-the-excel-person/
It requires the Microsoft Add in Microsoft Power Query For Excel https://www.microsoft.com/en-us/download/details.aspx?id=39379
它需要 Microsoft Power Query For Excel 中的 Microsoft Add https://www.microsoft.com/en-us/download/details.aspx?id=39379
回答by Patrick Honorez
Using VBA, you can. Here is a small example:
使用VBA，你可以。这是一个小例子：
Sub SqlSelectExample()
'list elements in col C not present in col B
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
con.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=" & ThisWorkbook.FullName & ";" & _
"DefaultDir=" & ThisWorkbook.FullName & ";ReadOnly=False;"
Set rs = New ADODB.Recordset
rs.Open "select ccc.test3 from [Sheet1$] ccc left join [Sheet1$] bbb on ccc.test3 = bbb.test2 where bbb.test2 is null ", _
con, adOpenStatic, adLockOptimistic
Range("g10").CopyFromRecordset rs '-> returns values without match
rs.MoveLast
Debug.Print rs.RecordCount 'get the # records
rs.Close
Set rs = Nothing
Set con = Nothing
End Sub
回答by warakawa
Here is a very easy way to generate the Cartesian product of an arbitrary number of lists using Pivot tables:
这是使用数据透视表生成任意数量列表的笛卡尔积的一种非常简单的方法：
https://chandoo.org/wp/generate-all-combinations-from-two-lists-excel/
https://chandoo.org/wp/generate-all-combinations-from-two-lists-excel/
The example is for two lists, but it works for any number of tables and/or columns.
该示例适用于两个列表，但它适用于任意数量的表和/或列。
Before creating the Pivot table, you need to convert your value lists to tables.
在创建数据透视表之前，您需要将值列表转换为表。
