W dzisiejszym przykładzie zobaczymy jak powstaje kombinacja wszystkich wartości w określonym układzie za pomocą formuł w Excelu, ale najpierw krótki wstęp. W zależności od tego jakie kto studia wybrał, czy też na jakim poziomie uczył się matematyki, mógł się spotkać lub też nie z zagadnieniami kombinatoryki. Kombinatoryka to podstawa rachunku prawdopodobieństwa, o którym każdy słyszał i każdy się z nim spotkał, choć niekoniecznie świadomie. Jeśli choć raz liczyłeś jakie masz szanse wygrać na loterii to był to właśnie ten moment. Kombinatoryka to obszerny dział matematyki, który najprościej można określić jako liczenie ilości możliwych podzbiorów lub ciągów wartości i określanie jakie jest prawdopodobieństwo wylosowania określonego układu.

Może się tu pojawić pytanie po co tworzyć taką kombinację. Najczęściej są to dwie sytuacje. Pierwsza to gdy potrzebujemy zbudować wartość/tekst, która będzie kluczem dla naszego słownika, który chcemy aby uwzględniał wszystkie kombinacje wartości. Ma to istotne znaczenie, gdy np. w modelu danych budujemy pośredni słownik, który od razu powinien uwzględniać wszystkie możliwości, a nie tylko te, które obecnie występują. Drugi przykład to gdy taki zestaw kombinacji chcemy wykorzystać jako parametr w formułach. Na przykład do zliczania ilości wystąpień czy sprawdzania poprawności wprowadzanych danych. Jeśli możliwych układów mamy nie więcej niż 20-30 to możemy je wypisać ręcznie. Gorzej jeśli ich liczba sięga setek czy tysięcy.

Przygotowanie danych, z których utworzymy kombinacje wartości w Excelu

Nasze zadanie polega na przygotowaniu wszystkich kombinacji kluczy, które składają się z elementów połączonych w określonej kolejności. W naszym przykładzie klucz będzie zawierał pięć elementów, każdy z nich z osobnej listy. Załóżmy, że te elementy to rok, numer kwartału, miejscowość, numer produktu oraz nazwa klienta.

Określenie liczby kombinacji

Pierwszy krok jest najprostszy ale też najważniejszy dla pisania i sprawdzania formuł. Musimy określić ile tych kombinacji faktycznie występuje. Do tego trzeba określić ile elementów występuje na każdej liście, czyli 3,4,5,3,4. Nasz klucz składa się z 5 elementów połączonych ze sobą. Każdy element ma stałą pozycję w kluczu, czyli chcemy utworzyć ciąg Element1 & Element 2 & Element 3 & Element 4 & Element 5. Policzenie ilości kombinacji można wykonać nie mając pojęcia o kombinatoryce. Weźmy najpierw układ składający się z samego roku i kwartału. Na palcach możemy policzyć, że będzie 12 różnych układów. Wynika to z tego, że dla pierwszego roku możemy dopisać 4 różne kwartały, dla drugiego i trzeciego roku tak samo, więc mamy iloczyn 3 x 4. Dokładając trzeci element dokładamy kolejny iloczyn liczby możliwych wartości elementów na trzecim miejscu. W ten sposób dochodzimy do iloczynu wszystkich ilości elementów z list, co daje nam 720 możliwych kombinacji (3x4x5x3x4).

Przygotowanie formuły z kombinacją wszystkich wartości

W drugim kroku przechodzimy już do pisania formuł. Są dwa podejścia do tego jak chcemy aby zmieniał się nasz klucz, tj. czy najpierw mają zmieniać się wartości na początku klucza czy na końcu. Osobiście preferuję zmiany wartości od końca, ponieważ pozwala to na łatwiejszą weryfikację poprawności obliczeń przelatując po nich wzrokiem. Tak więc naszym pierwszym celem jest zbudowanie formuły, która będzie brała pierwszą wartość z listy 5, potem drugą, trzecią, aż do czwartej po czym wróci do pierwszej. Funkcja w Excelu która daje nam odpowiednie wartości to reszta z dzielenia, czyli funkcja MOD(). Potrzebujemy jeszcze zmienną, tą możemy zrobić przez przygotowanie kolumny z ID albo wykorzystać numer wiersza aktualnej komórki. Druga funkcja to przesunięcie, które będzie pobierać wartości z komórki przesuniętej o określoną ilość wierszy i kolumn. Od numeru wiersza odejmujemy 2 ponieważ zaczynamy w drugim wierszu, a chcemy otrzymywać wartości od 0 do 4. O tyle wierszy chcemy przesuwać się względem odwołania od pierwszej wartości z piątej listy. Nie zmieniamy kolumny, więc trzeci parametr przesunięcia zostaje równy 0. W ten sposób otrzymujemy powtarzalny ciąg nazw klientów.

Połączenie z kolejnymi wartościami z listy

Teraz możemy przejść do łączenia wartości z kolejnej listy. Wykorzystujemy tu podobny mechanizm, lecz z tym wyjątkiem, że wartości z listy 4 nie mogą się zmieniać w każdym kolejnym wierszu. Zmiana musi następować po przejściu przez wszystkie elementy z listy 5, czyli co 4 wartości. Po przejściu przez wszystkie wartości z powrotem powinien się pojawić pierwszy element z listy. Dlatego najpierw musimy wyciągnąć część całkowitą z dzielenia numeru wiersza pomniejszonego o dwa przez liczbę elementów z ostatniej listy. Dopiero wtedy bierzemy resztę zdzielenia. W ten sposób tworzymy kolejną pętlę wartości.

Dalej powtarzamy podobne działania dla kolejnych list. Musimy tylko uwzględnić, że dzielnik dla funkcji MOD to liczba elementów danej listy. Natomiast dzielnik dla części całkowitej z dzielenia to iloczyn elementów wcześniejszych list.

Ograniczenie zakresu funkcji w Excelu

Na sam koniec warto dodać funkcję jeżeli, która spowoduje, że po przekroczeniu ilości wszystkich kombinacji funkcja przestanie wyświetlać wartości. W taki o to sposób otrzymaliśmy kombinację wszystkich wartości z 5 list. Plik z gotowym rozwiązaniem możesz pobrać pod tym linkiem Pobierz plik Excel