Wstęp do VBA na przykładzie metody D’Hondta

O ile samo programowanie nie każdemu jest konieczne do życia, to może być przydatne, szczególnie wtedy, jeżeli ktoś wykonuje dużo żmudnych i powtarzalnych czynności w Excelu.

Metoda D’Hondta

Metoda D’Hondta to jedna z najpopularniejszych metod przeliczania poparcia udzielonego komitetom wyborczym (zazwyczaj są to komitety partyjne) na liczbę mandatów (posłów, radnych, europosłów), które przysługują tym komitetom. Wynik wyborczy, czyli liczbę głosów¹ wszystkich komitetów dzielimy przez kolejne liczby naturalne, zaczynając od 1, dla każdego komitetu tworzy się ciąg ilorazów: poparcie/1; poparcie/2; poparcie/3, itd. Z tak powstałych ilorazów wybieramy tyle największych, ile mandatów mamy do podziału, a mandat o określonym numerze przysługuje temu komitetowi, którego iloraz miał ten sam numer na liście największych ilorazów (licząc od najwyższych).

Dla lepszego zrozumienia popatrzmy na przykład. Dla przykładu wybrałem wynik wyborów do sejmu z 2015 roku w okręgu 31². Poniżej w tabelce te wyniki, przy czym ograniczyłem się do tych komitetów, które w skali kraju przekroczyły próg wyborczy — jest ich pięć, a liczba porządkowa lp odpowiada ich pozycji na pełnej (nieskróconej) liście komitetów w okręgu. Do podziału w tym okręgu było 12 mandatów.

Jeżeli przekopiujemy to do Excela, tak aby napis „lp” znajdował się w komórce A1, to mamy przygotowane dane, na których możemy napisać program, który rozdzieli nam mandaty na poszczególne komitety.

Zanim jednak zaczniemy, to zróbmy sobie tabelkę z kolejnymi ilorazami, aby lepiej zrozumieć metodę d’Hondta. W kolejnych kolumnach mamy ilorazy poparcia przez kolejne liczby naturalne. Posiadaczom największych ilorazów przypadnie tyle mandatów, ile największych ilorazów posiadają (łącznie mamy 12 mandatów, bo tyle mandatów przypada na okręg 31). Widzimy, że komitet z numerem 1 i 2 mają po 5 mandatów (pogrubione ilorazy poniżej), zaś komitet 3 i 4 mają po jednym mandacie.

Programowanie w Excelu

Do rozpoczęcia przygody z programowaniem zazwyczaj potrzebujemy edytora tekstu, w którym zapiszemy kod programu, i kompilatora, który zamieni nasz kod na wykonywalny program. W praktyce jednak wykorzystuje się środowisko programistyczne, czyli program, który oprócz edytora i kompilatora ma również inne narzędzia, które usprawniają cały proces zarządzania tworzonym przez nas oprogramowaniem (np. debugger, który bardzo ułatwia odnajdywanie i poprawę błędów, albo kolorowanie składni). Okazuje się, że Excel jest poniekąd takim właśnie środowiskiem. Dodatkowo mamy bardzo ułatwione zadanie, jeżeli do programu potrzebujemy wczytać dużą liczbę danych, nie potrzebujemy czytać ich z zewnętrznych plików ani podpinać programu do bazy danych, ponieważ sam plik Excela może te dane przechowywać (aczkolwiek w niektórych przypadkach łatwiej byłoby stworzyć relacyjną bazę danych). Żeby zacząć przygodę, mając otwarty program Excel, naciśnijmy klawisze Alt+F11 (w niektórych laptopach Alt + Fn + F11), na naszych oczach powinien pojawić się widok podobny do tego na rysunku.

Po lewej mamy eksplorator projektów (jeśli go nie widać, to z paska narzędzi wybieramy: View i tam jest Project Explorer, albo skrótem Ctrl+R), tu myszką zaznaczamy, do którego obiektu przypiszemy makro, niech to będzie: Ten_skoroszyt. Po prawej mamy okno, w którym możemy pisać nasz program, tzn. edytor kodu (jeśli go nie widać, to z menu wybieramy: View i Code, albo F7). Teraz możemy napisać nasz program.

Kod programu w VBA

Sub dhont() ' zaczynamy procedurę

    Dim TablicaIlorazow() As Double ' tworzymy miejsce na kolejne ilorazy kolejnych komitetów
    Dim Mandaty As Integer, LiczbaKomitetow As Integer, PozycjaMandatu As Integer
    Dim Wynik() As Integer

    Mandaty = Worksheets("dane").Cells(2, 7)          ' zczytujemy z Excela liczbę mandatów do przydzielenia
    LiczbaKomitetow = Worksheets("dane").Cells(2, 8)   ' zczytujemy z Excela liczbę komitetów, które potencjalnie mogą otrzymać mandaty

    ReDim TablicaIlorazow(1 To LiczbaKomitetow, 1 To Mandaty) ' podajemy dokładny rozmiar tablicy na ilorazy kolejnych komitetów
    ReDim Wynik(1 To LiczbaKomitetow)

    For i = 1 To LiczbaKomitetow
        TablicaIlorazow(i, 1) = Worksheets("dane").Cells(1 + i, 3) ' wczytujemy z Excela poparcie dla kolejnych komitetów
    Next i ' i przypisujemy do ilorazu powstałego z dzielenia poparcia przez 1

    For i = 1 To LiczbaKomitetow ' dla wszystkich komitetów
        For k = 2 To Mandaty ' dla mandatów od drugiego do ostatniego (w ramach komitetu z numerem i)
            TablicaIlorazow(i, k) = TablicaIlorazow(i, 1) / k ' dzielimy poparcie przez liczbę naturalną odpowiadającą numerowi mandatu
        Next k                                                ' i zapisujemy w odpowiednim miejscu w tablicy ilorazów
    Next i

    For licznikMandatow = 1 To Mandaty
        maxVal = 0
        KomitetZNajwIlorazem = 0 ' nr komitetu z największym ilorazem

        ' Znajdujemy największy iloraz, przeszukując wszystkie wartości TablicaIlorazow(i, k); jeżeli mamy
        ' wartość większą niż dotychczasowa największa (maxVal), to teraz ona staje się największą
        ' (maxVal = TablicaIlorazow(i, k)), w tym miejscu zmienna i wskazuje nr komitetu z największym ilorazem
        For i = 1 To LiczbaKomitetow
            For k = 1 To Mandaty
                If TablicaIlorazow(i, k) > maxVal Then
                    maxVal = TablicaIlorazow(i, k)
                    KomitetZNajwIlorazem = i
                End If
            Next k
        Next i

        ' dodajemy jeden mandat komitetowi, który ma największy iloraz
        Wynik(KomitetZNajwIlorazem) = Wynik(KomitetZNajwIlorazem) + 1

        ' w tablicy ilorazów zerujemy największy wynik, aby nie pojawił się, gdy będziemy przydzielać kolejny
        ' mandat, tzn. gdy licznikMandatow zwiększymy o 1, korzystamy tu z tego, że Wynik(KomitetZNajwIlorazem)
        ' jest równy numerowi ostatniego zdobytego mandatu dla komitetu z największym ilorazem
        TablicaIlorazow(KomitetZNajwIlorazem, Wynik(KomitetZNajwIlorazem)) = 0

    Next licznikMandatow

    Worksheets("dane").Cells(1, 5) = "l. mandatów"
    For i = 1 To LiczbaKomitetow
        Worksheets("dane").Cells(1 + i, 5) = Wynik(i) ' wyświetlamy wyniki w kolejnych wierszach w 5 kolumnie
    Next i

End Sub ' kończymy procedurę

Objaśnienia

Zapis:

Dim TablicaIlorazow() As Double

oznacza tyle, że deklarujemy zmienną o nazwie TablicaIlorazow, która jest tablicą, która będzie przechowywać liczby z podwójną precyzją (As Double). Tablice to struktury, które przechowują wartości (liczby, teksty, inne tablice) w sposób uporządkowany, tzn. każda wartość ma swój numer w tablicy, dzięki czemu łatwo ją pobrać i można to robić w sposób zorganizowany. Wiemy, że zmienna TablicaIlorazow jest tablicą, bo decydują o tym nawiasy, bez nawiasów, tzn. zmienna tak zadeklarowana Dim TablicaIlorazow As Double byłaby zwykłą zmienną (miejscem w pamięci przeznaczonym do przechowywania pojedynczej wartości). Podobnie zapis

Dim Wynik() As Integer

oznacza tyle, że deklarujemy tablicę o nazwie Wynik, która będzie przechowywać liczby całkowite (As Integer), tzn. liczby, które nie mają części dziesiętnych (przecinków). Z kolei:

Dim Mandaty As Integer, LiczbaKomitetow As Integer, PozycjaMandatu As Integer

to deklaracja trzech zmiennych do przechowywania liczb całkowitych. Zapis

Mandaty = Worksheets("dane").Cells(2, 7)

oznacza tyle, że do zmiennej Mandaty przypisujemy liczbę, która jest w arkuszu dane, w komórce, która jest w drugim wierszu i siódmej kolumnie. Podobnie zapis

LiczbaKomitetow = Worksheets("dane").Cells(2, 8)

oznacza tyle, że do zmiennej LiczbaKomitetow przypiszemy liczbę, która jest w arkuszu dane, w komórce, która jest w drugim wierszu i ósmej kolumnie. Zapis:

ReDim TablicaIlorazow(1 To LiczbaKomitetow, 1 To Mandaty)

oznacza tyle, że nasza tablica będzie od teraz dwuwymiarowa, tzn. każda wartość w tablicy będzie dostępna poprzez podanie dwóch liczb, pierwsza z zakresu od 1 do LiczbaKomitetow i druga liczba w zakresie od 1 do Mandaty. Zapis:

ReDim Wynik(1 To LiczbaKomitetow)

to deklaracja tego, że elementy w tablicy Wynik będą dostępne przez podanie jednej liczby z zakresu od 1 do LiczbaKomitetow. Zapis:

For i = 1 To LiczbaKomitetow
TablicaIlorazow(i, 1) = Worksheets("dane").Cells(1 + i, 3)
Next i

to pętla przebiegająca wszystkie wartości i od i=1 do i=LiczbaKomitetow. Dla każdego i z tego zakresu TablicaIlorazow w miejscu o współrzędnych (i,1) zapisze wartość z Excela z arkusza dane, z komórki, która jest w wierszu z numerem i+1 i kolumnie z numerem 3. Podobnie zapis:

For i = 1 To LiczbaKomitetow
For k = 2 To Mandaty
TablicaIlorazow(i, k) = TablicaIlorazow(i, 1) / k
Next k
Next i

to dwie pętle, pierwsza (zewnętrzna — gdzie zmienną, która się zmienia, jest i) przebiega wszystkie komitety, druga (wewnętrzna) przebiega mandaty (a raczej liczby naturalne odpowiadające mandatom) od drugiego do ostatniego (w ramach ustalonego komitetu), dla komitetu z numerem i oraz mandatu z numerem k dzielimy poparcie (bo wartość TablicaIlorazow(i, 1) jest równa poparciu komitetu z numerem i) przez liczbę naturalną k i zapisujemy w odpowiednim miejscu (tzn. w miejscu o współrzędnych (i,k)) w tablicy ilorazów. Mamy taki zapis, złożony z 3 pętli:

For licznikMandatow = 1 To Mandaty
maxVal = 0
KomitetZNajwIlorazem = 0
For i = 1 To LiczbaKomitetow
For k = 1 To Mandaty
If TablicaIlorazow(i, k) > maxVal Then
maxVal = TablicaIlorazow(i, k)
KomitetZNajwIlorazem = i
End If
Next k
Next i
Wynik(KomitetZNajwIlorazem) = Wynik(KomitetZNajwIlorazem) + 1
TablicaIlorazow(KomitetZNajwIlorazem, Wynik(KomitetZNajwIlorazem)) = 0
Next licznikMandatow

Pierwsza, najbardziej zewnętrzna pętla przebiega licznikMandatow od 1 do ostatniego mandatu (czyli do wartości zapisanej w zmiennej Mandaty). Dla każdego mandatu (dla każdego licznikMandatow) chcemy znaleźć komitet, który zdobędzie ten mandat. W tym celu używamy dwóch pętli, które przebiegną wszystkie wartości w tabeli TablicaIlorazow (pierwsza przebiega od 1 do LiczbaKomitetow, druga od 1 do Mandaty — do ostatniego mandatu). W ten sposób znajdujemy największy iloraz, przeszukując wszystkie wartości TablicaIlorazow(i, k); jeżeli mamy wartość większą niż dotychczasowa największa (maxVal), to teraz ona staje się największą (maxVal = TablicaIlorazow(i, k)), w tym miejscu zmienna i wskazuje nr komitetu z największym ilorazem. Mając numer komitetu z największym ilorazem, tablicę wynik dla tego numeru powiększamy o jeden, tzn.:

Wynik(KomitetZNajwIlorazem) = Wynik(KomitetZNajwIlorazem) + 1

Pozostaje ten zapis:

TablicaIlorazow(KomitetZNajwIlorazem, Wynik(KomitetZNajwIlorazem)) = 0

W tablicy ilorazów zerujemy największy wynik, aby nie pojawił się, gdy będziemy przydzielać kolejny mandat, tzn. gdy licznikMandatow zwiększymy o 1 (odpowiada za to Next licznikMandatow), korzystamy tu z tego, że Wynik(KomitetZNajwIlorazem) jest równy numerowi ostatniego zdobytego mandatu dla komitetu z największym ilorazem. Poza tymi trzema pętlami wyświetlamy wyniki:

Worksheets("dane").Cells(1, 5) = "l. mandatów"
For i = 1 To LiczbaKomitetow
Worksheets("dane").Cells(1 + i, 5) = Wynik(i)
Next i

W komórce, która jest w pierwszym wierszu i piątej kolumnie, wstawiamy napis: l. mandatów. Następnie pętla, przebiegająca numery wszystkich komitetów, w 5 kolumnie i w wierszu o numerze 1+i (tzn. 1+numer komitetu) wyświetli liczbę mandatów przysługującą temu komitetowi.

Znak apostrofu (pojedynczego cudzysłowu) pozwala zapisać komentarz, który w edytorze VBA podświetli się na zielono. Kod z poprzedniego akapitu jest dość mocno skomentowany, możemy go skopiować i wkleić do edytora kodu. Cały kod możemy zobaczyć w działaniu, wykorzystując debugger i okno: locals (View i Locals Window). Naciskając klawisz F8 (albo Fn + F8, albo wybieramy myszką z paska narzędzi: Debug i Step Into), wykonamy program krok po kroku, w tym czasie w oknie locals możemy zobaczyć wartości zmiennych (również najeżdżając myszką na zmienną). Dobrze spróbować to wykonać, bo z jednej strony zapoznamy się z debuggerem, a z drugiej możemy wyrobić sobie intuicję, jak działają poszczególne instrukcje.

Co zamiast Excela

Większość makr Excela można napisać i wykonać w pakiecie LibreOffice. W tym celu na początku piszemy:

Option VBASupport 1

Więcej informacji tu: https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03103350.html. Natomiast, jeżeli chcemy mieć sam Excel, to moim zdaniem najlepiej mieć go w wersji z dożywotnią licencją, a nie w wersji abonamentowej. Jeszcze niedawno (nie wiem, czy nadal tak jest) można było w niektórych sklepach kupić legalny pakiet Office w bardzo dobrych cenach, od osób (albo firm), które już go używały, zrezygnowały z tego i odsprzedały sklepowi (stąd niska cena).

VBA a AI

Nie jest tajemnicą, że wielkie modele językowe (ChatGPT, Claude) programują lepiej niż większość ludzi, VBA nie jest tu wyjątkiem, mając podstawową wiedzę o VBA z pomocą AI można bardzo ułatwić sobie życie.

Edycja 13.08.2026

Przypisy