크로스 조인이나 데카르트 제품을 엑셀로 하는 방법이 있나요?

크로스 조인이나 데카르트 제품을 엑셀로 하는 방법이 있나요?

지금은 일반적인 데이터베이스를 사용할 수 없기 때문에 일시적으로 엑셀을 사용하고 있습니다.좋은 생각 있어요?

여기에는 Dim1(ABC), Dim2(123), Dim3(XYZ)의 3차원이 있습니다.

VBA 없이 표준 Excel을 사용하여 2차원 데카르트 제품을 만드는 방법은 다음과 같습니다.

1) dim1을 수직으로, dim2를 수평으로 플롯합니다.교차로에서 치수 부재 연결:

2) 데이터 추출.ALT-D-P를 사용하여 피벗 테이블 마법사를 실행합니다(ALT를 누르지 말고 한 번 누르십시오).[ Multiple consolidation ranges ](복수 통합 범위)를 선택합니다.-> 1페이지 작성..--> 모든 셀(헤더 포함)을 선택하여 목록에 추가한 후 다음을 누릅니다.

3) 결과값을 수직으로 플롯하여 연결된 문자열을 분해한다.

보일라, 넌 크로스 조인이야.다른 치수를 추가해야 할 경우 이 알고리즘을 다시 반복합니다.

건배.

콘스탄틴

다음은 피벗 테이블을 사용하여 임의의 수의 목록으로 구성된 데카르트 곱을 생성하는 매우 쉬운 방법입니다.

https://chandoo.org/wp/generate-all-combinations-from-two-lists-excel/

이 예는 2개의 리스트에 대한 것이지만 임의의 수의 테이블 및/또는 열에 대해 작동합니다.

피벗 테이블을 작성하기 전에 값 목록을 테이블로 변환해야 합니다.

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

Excel 수식을 사용하는 방법은 다음과 같습니다.

|    |       A        |       B        |       C        |
| -- | -------------- | -------------- | -------------- |
|  1 |                |                |                |
| -- | -------------- | -------------- | -------------- |
|  2 | Table1_Column1 | Table2_Column1 | Table2_Column2 |
| -- | -------------- | -------------- | -------------- |
|  3 |       A        |       1        |       X        |
| -- | -------------- | -------------- | -------------- |
|  4 |       B        |       2        |       Y        |
| -- | -------------- | -------------- | -------------- |
|  5 |       C        |       3        |       Z        |
| -- | -------------- | -------------- | -------------- |
|  6 |                |                |                |
| -- | -------------- | -------------- | -------------- |
|  7 |      Col1      |      Col2      |      Col3      |
| -- | -------------- | -------------- | -------------- |
|  8 |   = Formula1   |   = Formula2   |   = Formula3   |
| -- | -------------- | -------------- | -------------- |
|  9 |   = Formula1   |   = Formula2   |   = Formula3   |
| -- | -------------- | -------------- | -------------- |
| 10 |   = Formula1   |   = Formula2   |   = Formula3   |
| -- | -------------- | -------------- | -------------- |
| 11 |      ...       |      ...       |      ...       |
| -- | -------------- | -------------- | -------------- |

Formula1: IF(ROW() >= 8 + (3*3*3), "", INDIRECT(ADDRESS(3 + MOD(FLOOR(ROW() - 8)/(3*3), 3), 1)))
Formula2: IF(ROW() >= 8 + (3*3*3), "", INDIRECT(ADDRESS(3 + MOD(FLOOR(ROW() - 8)/(3)  , 3), 2)))
Formula3: IF(ROW() >= 8 + (3*3*3), "", INDIRECT(ADDRESS(3 + MOD(FLOOR(ROW() - 8)/(1)  , 3), 3)))

모든 것을 지배하기 위한 하나의 일반 공식!

결과

공식

MOD(CEILING.MATH([index]/PRODUCT([size of set 0]:[size of previous set]))-1,[size of current set])+1

의 각 위치서 세트에는 세트 내 각 요소의 인덱스(순서 위치)가 포함됩니다.i가 이즈가 has이다n_i [4세트는 4세트는 []세트는 4세트는 [4세트는 4세트는 ]세트는 []세트는]n_1 ,n_2 ,n_3 ,n_4 ] ]

하면 ""를 할 수 .index각가 여러 ).index([table of the set],[this result],[column number of attribute]).

설명.

공식의 두 가지 주요 성분인 순환 성분과 분할 성분이 설명되었습니다.

사이클링 컴포넌트

=MOD([partitioning component]-1, [size of current set])+1

• 세트의 가능한 모든 값을 순환합니다.
• modulo 함수는 결과가 세트의 크기를 "주변으로" 이동하고 가능한 값의 "범위를 벗어나"는 것을 방지하기 위해 필요합니다.
• -1 ★★★★★★★★★★★★★★★★★」+1를 사용하면, 1 베이스의 번호부여(인덱스 세트)로부터 제로 베이스의 번호부여(모듈로 동작의 경우)로 이행할 수 있습니다.

파티션 구성 요소

CEILING.MATH([index]/PRODUCT([size of set 0]:[size of previous set]):

• "카르트 인덱스"를 청크로 분할하여 각 청크에 "이름"을 지정합니다.
• "카르타스 지수"는 1부터 데카르트 곱의 요소 수까지의 번호일 뿐이다(각 세트의 크기 곱으로 표시).
• "name"은 "cartesian index"의 청크별 증분 열거일 뿐입니다.
• 각 청크에 속하는 모든 인덱스에 대해 동일한 "이름"을 가지기 위해 "cartesian index"를 파티션 수로 나누고 결과를 "ceil"(반올림)합니다.
• 파티션의 양은 이전 각 결과에 대해 이 세트의 각 요소에 대해 반복해야 하므로 마지막 사이클의 총 크기입니다.
• 따라서 이전 결과의 크기는 모든 이전 집합 크기의 곱입니다(첫 번째 집합 이전의 집합 크기를 포함하여 일반화할 수 있습니다. "set 0"이라고 하며 크기가 1로 일정합니다).

스크린샷 포함

크기 설정

"Set0"과 데카르트 제품의 크기를 포함한 세트 크기를 준비했습니다.

여기서 세트의 크기는 다음과 같습니다.

• "Set0" : 셀에 1개B2
• "Set1" : 셀 내 2개C2
• '세트2' : 셀 내 5개D2
• '세트3' : 셀 내 3개E2

따라서 데카르트 곱의 크기는 30이다.2*5*3셀 내)A2.

결과.

테이블 구조_tbl_CartesianProduct다음 열과 그 수식을 사용합니다.

• 결과:
  • Cartesian Index:=IF(ROW()-ROW(_tbl_CartesianProduct[[#Headers];[Cartesian Index]])<=$A$2;ROW()-ROW(_tbl_CartesianProduct[[#Headers];[Cartesian Index]]);NA())
  • concatenation:=TEXTJOIN("-";TRUE;_tbl_CartesianProduct[@[Index S1]:[Index S3]])
  • Index S1:=MOD(CEILING.MATH([@[Cartesian Index]]/PRODUCT($B$2:B$2))-1;C$2)+1
  • Index S2:=MOD(CEILING.MATH([@[Cartesian Index]]/PRODUCT($B$2:C$2))-1;D$2)+1
  • Index S3:=MOD(CEILING.MATH([@[Cartesian Index]]/PRODUCT($B$2:D$2))-1;E$2)+1
• 스텝 "이전 파티션 크기":
  • Size prev part S1:=PRODUCT($B$2:B$2)
  • Size prev part S2:=PRODUCT($B$2:C$2)
  • Size prev part S3:=PRODUCT($B$2:D$2)
• 순서 "Chunk name" :
  • Chunk S1:=CEILING.MATH([@[Cartesian Index]]/[@[Size prev part S1]])
  • Chunk S2:=CEILING.MATH([@[Cartesian Index]]/[@[Size prev part S2]])
  • Chunk S3:=CEILING.MATH([@[Cartesian Index]]/[@[Size prev part S3]])
• 마지막 단계 "세트 전체를 순환":
  • Cycle chunk in S1:=MOD([@[Chunk S1]]-1;C$2)+1
  • Cycle chunk in S2:=MOD([@[Chunk S2]]-1;D$2)+1
  • Cycle chunk in S3:=MOD([@[Chunk S3]]-1;E$2)+1

*: 데카르트 열거를 생성하는 실제 작업용

PowerQuery의 작은 코드만으로도 문제를 해결할 수 있습니다.

let
  Quelle = Excel.CurrentWorkbook(){[Name="tbl_Data"]}[Content],
  AddColDim2 = Table.AddColumn(Quelle, "Dim2", each Quelle[Second_col]),
  ExpandDim2 = Table.ExpandListColumn(AddColDim2, "Dim2"),
  AddColDim3 = Table.AddColumn(ExpandDim2, "Dim3", each Quelle[Third_col]),
  ExpandDim3 = Table.ExpandListColumn(AddColDim3, "Dim3"),
  RemoveColumns = Table.SelectColumns(ExpandDim3,{"Dim1", "Dim2", "Dim3"})
in RemoveColumns

DAX를 사용해 보다CROSS JOIN자세한 내용은 MSDN을 참조해 주세요.

다음과 같은 표현을 사용할 수 있습니다.CROSSJOIN(table1, table2)데카르트 제품을 만듭니다.

언급URL : https://stackoverflow.com/questions/26999604/is-there-a-way-to-perform-a-cross-join-or-cartesian-product-in-excel