Jak zwrócić w VBA permutacje elementów zbioru


1. VBA Permutacje elementów zbioru

W tym artykule dowiesz się w jaki sposób za pomocą języka VBA zwrócić wszystkie permutacje elementów danego zbioru. Przykłady pokazane w artykule dotyczą zarówno permutacji z powtórzeniami jak i bez powtórzeń. Pisząc kod VBA będziemy korzystali z takich funkcjonalność VBA jak Pętle For, Tablice VBA oraz z instrukcji warunkowej VBA IF. Ostatnio zostałem poproszony przez jednego z czytelników bloga – Pana Andrzeja o pomoc w utworzeniu skryptu VBA zwracającego wszystkie permutacje elementów zbioru bez powtórzeń. Poniżej szybkie wyjaśnienie tego,  czym są permutacje oraz opis jak podejść do takiego zadania.

Czym są permutacje elementów zbioru? W dużym skrócie permutacje to przekształcenia zbioru polegające na zamianie kolejności jego elementów.  Dla przykładu zbiór 3 elementowy z elementami o wartościach {1, 2, 3} możemy przekształcić na 6 różnych sposobów np:

  • 1, 2, 3
  • 1, 3, 2
  • 2, 1, 3
  • 2, 3, 1
  • 3, 1, 2
  • 3, 2, 1
Excel VBA - Permutacje w VBA

Excel VBA – Permutacje w VBA

Pokazana powyżej permutacja to tzw. permutacja bez powtórzeń. Oznacza to tyle, że każdy z elementów występuje w przekształconym zbiorze tylko jeden raz. Liczba możliwych kombinacji dla zbioru n-elementowego wynosi n-silnia (n!). Tak więc liczba możliwych kombinacji dla zbioru złożonego z 3 elementów w tym wypadku wynosi 3!, a więc 6. Dla zbioru 5-elementowego liczba możliwych kombinacji do uzyskania wyniesie 5!, a więc 120.

wzór na liczbę permutacji bez powtórzeń: n!

W artykule zajmiemy się także permutacjami z powtórzeniami. Wzór możliwych kombinacji dla permutacji z powtórzeniami jest nieco bardziej skomplikowany, jednak napisanie programu jest bardzo proste, stąd zajmiemy się nim na końcu artykułu. Sam wzór na liczbę możliwych kombinacji wygląda następująco:

wzór na liczbę permutacji z powtórzeniami: n! / (a! * b! * c! … * n!)

Więcej informacji na temat permutacji możesz znaleźć pod tym adresem: wikipedia permutacje

 

2. Zwracanie wszystkich permutacji zbioru bez powtórzeń w VBA Excel

W VBA permutacje nie są możliwe do uzyskania za pomocą prostych metod, bądź funkcji. Jeśli działamy na zbiorze n-elementowym i chcemy uzyskać wszystkie możliwe kombinacje elementów tego zbioru powinniśmy napisać w tym celu dosyć skomplikowany kod. Dla celów ćwiczenia wynik działań będzie wyświetlany w oknie VBA Immediate Window. Docelowo wyniki będą wyświetlane w komórkach arkusza Excel. By włączyć obsługę Immediate Window w VBA przejdź do zakładki Widok i wybierz element o nazwie Immediate Window. Sam schemat kodu VBA dla permutacji bez powtórzeń wygląda następująco:

Opis działania:

  • W przykładzie została utworzona tablica n-elementowa o indeksach od 1 do n.
  • Do każdego z pól tablicy przypisana została wartość ze zbioru
  • Za pomocą funkcji LBound i Ubound zwracany jest indeks pierwszego i ostatniego elementu tablicy
  • Za pomocą funkcji warunkowych IF VBA sprawdzana jest informacja, czy element nie występuje już w nowym przekształconym zbiorze, by uniknąć powtórzeń elementów
  • Za pomocą zagnieżdżonych pętli For tworzymy cykl wyszukujący wszystkie możliwe permutacje
  • Po każdorazowym cyklu pętli program zwraca elementy zbioru w formie okna immediate za pomocą polecenia Debug.print
  • Pętla For i instrukcja warunkowa VBA IF powinny zagnieżdżać się tyle razy ile elementów jest w zbiorze

 

2.1 Przykład 1: Zwracanie wszystkich permutacji zbioru bez powtórzeń w VBA Excel

Ok, to był schemat. Teraz czas na przykład z jego użyciem. Plik Excel z zamieszczonymi przykładami załączony jest na końcu artykułu. Poniżej kilka wymagań, które chcemy wziąć pod uwagę przy pisaniu kodu do naszego pierwszego przykładu:

  • Posiadamy zbiór 3-elementowy o elementach {X, Y, Z}
  • Chcemy otrzymać wszystkie możliwe permutacje elementów zbioru bez powtórzeń elementów
  • Zbiory wynikowe mają posiadać taką samą liczbę elementów, jak zbiór który chcemy przekształcić
  • Wynik chcemy zwrócić w oknie Immediate Window

Poniżej kod przykładu:

Excel VBA - permutacje VBA elementów zbioru bez powtórzeń

Excel VBA – permutacje VBA elementów zbioru bez powtórzeń

Opis Przykładu:

  • W przykładzie została utworzona tablica 3-elementowa o indeksach od 1 do 3.
  • Do każdego z pól tablicy przypisana została wartość ze zbioru kolejno X, Y, Z
  • Za pomocą funkcji LBound i Ubound zwróciliśmy indeksy tablicy
  • Za pomocą funkcji warunkowych sprawdzana jest informacja, czy element nie występuje już w nowym przekształconym zbiorze
  • Za pomocą zagnieżdżonych pętli For tworzymy cykl wyszukujący wszystkie możliwe permutacje
  • Po każdorazowym cyklu pętli program zwraca elementy zbioru

 

2.2 Przykład 2: Zwracanie wszystkich permutacji zbioru bez powtórzeń w VBA Excel dla zbioru 8 elementowego

Zróbmy kolejny przykład. Poniżej warunki, które chcemy uwzględnić w naszym programie.

  • Mamy do dyspozycji zbiór 8-elementowy o wartościach tekstowych np Polska, Niemcy, USA, Wielka Brytania, Francja, Hiszpania, Rosja, Brazylia. Liczba możliwych kombinacji będzie w tym wypadku wynosiła 8!, a więc 40320 (Nie ma przeszkód, by wykorzystać w przykładzie wartości liczbowe).
  • Wyniki powinny być zwracane w arkuszu w komórkach od A1 w dół
  • Chcemy zwrócić wszystkie permutacje bez powtórzeń dla zbioru bez podzbiorów

Kod przykładu VBA permutacje bez powtórzeń:

Excel VBA - VBA permutacje elementów zbioru bez powtórzeń

Excel VBA – VBA permutacje elementów zbioru bez powtórzeń

Opis Przykładu:

  • W przykładzie została utworzona tablica 8-elementowa o indeksach od 1 do 8.
  • Do każdego z pól tablicy przypisana została wartość ze zbioru oznaczająca nazwę państwa
  • Za pomocą funkcji LBound i Ubound zwróciliśmy indeksy tablicy
  • Utworzony został licznik kolejnej pętli LongCounter do celów obsługi arkusza Excel
  • Za pomocą funkcji warunkowych sprawdzana jest informacja, czy element nie występuje już w nowym przekształconym zbiorze
  • Za pomocą zagnieżdżonych pętli For tworzymy cykl wyszukujący wszystkie możliwe permutacje
  • Po każdorazowym cyklu pętli program zwraca elementy zbioru do kolejnych kolumn arkusza z wykorzystaniem licznika LongCounter
  • Po każdorazowym cyklu pętli następuje zwiększenie wartości licznika LongCounter o 1, by dodawać dane do kolejnych wierszy

 

3. VBA Permutacja elementów zbioru z powtórzeniami

Permutacje w VBA z powtórzeniami, choć z pozoru wydają się trudniejsze, możemy stworzyć w bardzo prosty sposób. Jedyne co powinieneś zrobić w porównaniu do poprzednich przykładów to usunąć wszystkie instrukcje warunkowe zagnieżdżone w pętach For z powyższych przykładów. Tak więc, jeśli chcesz otrzymać permutacje zbioru np 3-elementowego z powtórzeniami kod możesz zapisać w następujący sposób:

Wyżej załączony przykład zwraca zbiory w oknie immediate. Poniżej analogiczny przykład zwracający zbiory do komórek arkusza. Obydwa przykłady są modyfikacją przykładów z punktu 2 tego artykułu.

Excel VBA - VBA permutacje elementów zbioru z powtórzeniami

Excel VBA – VBA permutacje elementów zbioru z powtórzeniami

Plik z wszystkimi omawianymi przykładami zamieszczam poniżej:

excel_vba_permutacje.xlsm

Podobne przykłady praktycznego użycia języka VBA w Excelu zamieszczam na tej stronie: Makra Przykłady. Przy okazji zapraszam do przejścia kursu VBA mojego autorstwa, znajdziesz go pod tym linkiem: VBA

Dodaj komentarz

Twój adres email nie zostanie opublikowany.