Visual Basic - makra do tworzenia iloczynu kartezjańskiego z wybranych parametrów
W mojej pracy z danymi często potrzebuję zintegrować dane z wielu źródeł. Wielokrotnie potrzebne dane są w różnych arkuszach, mają różną strukturę, wymagają zaawansowanej obróbki itp. Aby efektywnie prezentować takie dane istnieje potrzeba zintegrowania ich w formie bazodanowej. Jednak jak stworzyć kombinację wszystkich parametrów (wymiarów), zwłaszcza jeśli jest ich dość dużo? Przy niewielkich bazach można poradzić sobie "na piechotę". Można także wykorzystać takie narzędzia jak np. access lub power query, aby z kilku jednorodnych ciągów zdefiniowanych w osobnych tabelach stworzyć ich kombinację. Jednak w mojej ocenie najszybszym i najbardziej wydajnym sposobem będzie wykorzystanie makr w popularnym excelu.
Na początek warto stworzyć plik z arkuszami wsadowymi i odpowiadającymi im makrami. Dla przykładu weźmy sobie 3 arkusze, w których będą definicje odpowiednio: 2, 3 i 5 parametrów. Poniżej zaprezentowałem zrzut arkusza dot. 2 parametrów:
W kolumnach A i B mamy unikatowe wartości parametru Def1 oraz parametru Def2. Kolumny te możemy wypełniać unikatowymi wartościami w zależności od potrzeb (są one edytowalne). Kolumny E i F z kolei to wynik działania makra, które tworzy iloczyn kartezjański z unikatów (czyli mówiąc najprościej tworzy unikatowe kombinacje "każdy z każdym" dla Def1 i Def2). Makro dla 2 parametrów przedstawia się następująco:
Jak widzimy makro pracuje na przeliczaniu ręcznym, aby potem wrócić do automatycznego. Zakres wynikowy w kolumnach E i F jest najpierw czyszczony, zatem nie ma tutaj ryzyka, że nadpisując stare wyniki danymi o mniejszej liczbie kombinacji zostawimy niepotrzebne rzeczy. Makro działa w pętli w taki sposób, że wypełnia pola wynikowe połączeniami pierwszego elementu z Def1 ze wszystkimi elementami z Def2, drugiego elementu z Def 1 ze wszystkimi elementami z Def2 i tak do końca.
Poniżej pokazałem układ arkusza dla 3 parametrów:
oraz makro dla 3 elementów:
A tutaj arkusz dla 5 elementów:
i makro dla 5 elementów
Tak na prawdę liczbę elementów możemy jeszcze rozszerzać modyfikując powyższe makra, aczkolwiek jak na potrzeby excela to i tak dużo. Aby pracować na większej liczbie kombinacji wymagane są już raczej inne narzędzia bazodanowe.