Jak mogę utworzyć „Cross-Join” w programie Excel?

Mam 2 arkusze w programie Excel, każdy z 10 rzędami. Czy w programie Excel jest jakiś rodzaj formuły / makra „łączenie krzyżowe”, który pozwala mi utworzyć trzeci arkusz ze wszystkimi wierszami połączonymi krzyżowo (łącznie 100 wierszy).

Jeśli użycie OLEDB nie jest dostępne, poniższe opcje można wykorzystać do wygenerowania produktu krzyżowego

Option Base 1

Function Cross_Product_range(r1 As Range, r2 As Range) As Variant
    Cross_Product_range = Cross_Product_array(r1.Value, r2.Value)
End Function

Function Cross_Product_array(a1 As Variant, a2 As Variant) As Variant
    On Error GoTo ErrorHandler
    Dim TempArray(), k, m
    ReDim TempArray(UBound(a1) * UBound(a2), UBound(a1, 2) + UBound(a2, 2))
    k = 1
    For i = 1 To UBound(a1)
        For j = 1 To UBound(a2)
            m = 1
            For u = 1 To UBound(a1, 2)
                TempArray(k, m) = a1(i, u)
                m = m + 1
            Next
            For u = 1 To UBound(a2, 2)
                TempArray(k, m) = a2(j, u)
                m = m + 1
            Next
            k = k + 1
        Next
    Next
    Cross_Product_array = TempArray
    Exit Function
    ErrorHandler:
    Debug.Print Err
End Function

Po dodaniu powyższej funkcji do modułu vba, Aby utworzyć iloczyn krzyżowy A1:B5i C1:D5wybrać komórki docelowe (powiedzmy E1: H25) i wprowadzić formułę, użyj kombinacji klawiszy Ctrl + Shift + Enter

=Cross_Product_range(A1:B5,C1:D5)

Możesz to zrobić dość łatwo za pomocą Power Query (jeśli masz to w swojej wersji programu Excel):

Skonfiguruj połączenie danych do każdej tabeli, którą chcesz połączyć: Wybierz tabelę w programie Excel, a następnie wybierz „Z tabeli / zakresu” z „Dane” na wstążce. Spowoduje to wyświetlenie edytora Power Query.

Aby wrócić do programu Excel, kliknij małą strzałkę na przycisku „Zamknij i wczytaj”, wybierz „Zamknij i wczytaj do”, a następnie „Tylko połączenie”. W przeciwnym razie skończysz z kopią tabeli w programie Excel ...

Zrób to samo dla drugiego stołu.

Panel „Zapytania i połączenia” powinien być teraz otwarty po prawej stronie okna. Jeśli nie, kliknij „Dane -> Zapytania i połączenia” na wstążce.

Dwie tabele powinny być wymienione jako zapytania.

Następnym krokiem jest utworzenie kwerendy łączącej łączącej te dwa elementy i zwrócenie wyniku do programu Excel:

Kliknij prawym przyciskiem myszy jedno z zapytań w okienku „Zapytania i połączenia” i wybierz „Odwołanie”. Spowoduje to ponowne otwarcie edytora Power Query i utworzenie trzeciego zapytania wskazującego na jedną z twoich tabel.

Teraz wybierz „Dodaj kolumnę -> Niestandardowa kolumna” ze wstążki Power Query ... Formuła dla nowej kolumny będzie nazwą drugiego zapytania - tj. Danych do połączenia krzyżowego. Jeśli w nazwie nie ma spacji, możesz wpisać ją bezpośrednio (np. = Tabela 2, w przeciwnym razie będzie to = # "Tabela 2".

Powinieneś teraz mieć nową kolumnę z etykietą „Niestandardowa” z „Tabelą” w każdej komórce. Kliknij mały symbol w nagłówku kolumny, aby rozwinąć każdą tabelę.

Wszystko idzie dobrze, gotowe! Kliknij „Zamknij i wczytaj”, a tabela połączona powinna pojawić się w nowym arkuszu.

Należy zwrócić uwagę na jedno: to połączenie krzyżowe nie aktualizuje się automatycznie po zmianie danych źródłowych. Będziesz musiał kliknąć prawym przyciskiem myszy i wybrać „Odśwież”, aby ponownie uruchomić zapytanie dotyczące łączenia krzyżowego.

Prawdopodobnie jest to prostsze niż wydaje mi się!