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ów1 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.2 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. W przedostatniej kolumnie, mamy liczbę mandatów w tym okręgu (12), zaś w ostatniej liczbę komitetów, które przekroczyły próg wyborczy (5 komitetów, miałoby to większe znaczenie, gdybyśmy mieli dane wszystkich komitetów w tym okręgu, a nie tylko te przekraczające próg). 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.
| lp | komitet | l. głosów | procent | Mandaty do podziału | Komitety | ||
| 1 | Komitet Wyborczy Prawo i Sprawiedliwość | 135367 | 32,92% | 12 | 5 | ||
| 2 | Komitet Wyborczy Platforma Obywatelska RP | 116658 | 28,37% | ||||
| 3 | Komitet Wyborczy Wyborców „Kukiz’15” | 41344 | 10,05% | ||||
| 4 | Komitet Wyborczy Nowoczesna Ryszarda Petru | 35591 | 8,66% | ||||
| 9 | Komitet Wyborczy Polskie Stronnictwo Ludowe | 4064 | 0,99% |
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, zaś komitet 3 i 4 mają po jednym mandacie.
| lp | komitet | l. głosów/1 | l. głosów/2 | l. głosów/3 | l. głosów/4 | l. głosów/5 | l. głosów/6 |
| 1 | Komitet Wyborczy Prawo i Sprawiedliwość | 135367 | 67683,5 | 45122,3 | 33841,8 | 27073,4 | 22561,2 |
| 2 | Komitet Wyborczy Platforma Obywatelska RP | 116658 | 58329,0 | 38886,0 | 29164,5 | 23331,6 | 19443,0 |
| 3 | Komitet Wyborczy Wyborców „Kukiz’15” | 41344 | 20672,0 | 13781,3 | 10336,0 | 8268,8 | 6890,7 |
| 4 | Komitet Wyborczy Nowoczesna Ryszarda Petru | 35591 | 17795,5 | 11863,7 | 8897,8 | 7118,2 | 5931,8 |
| 9 | Komitet Wyborczy Polskie Stronnictwo Ludowe | 4064 | 2032,0 | 1354,7 | 1016,0 | 812,8 | 677,3 |
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. debuger, 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óry 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 Manadaty 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ępne 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 numer 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 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ąca 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ć takie ćwiczenie, bo z jednej strony zapoznamy się z debbugerem, 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).
- Możemy zamiast liczby głosów wziąć procentowe poparcie dla tych komitetów, gdyby nie to, że poparcie procentowe czasem chcielibyśmy zaokrąglić wynik byłby taki sam. Przy zaokrąglaniu czasami (nie często) możemy dostać wynik nieco przekłamany, jednak pisząc program, który będzie prognozował rozkład mandatów pomiędzy różne komitety wyborcze (dla wyborów, które mają być w przyszłości) wygodniej (dla użytkownika) posługiwać się poparciem procentowym.↩︎
- Gdy tworzymy taki program, który prognozę poparcia w skali kraju przeliczy na liczbę mandatów, to staniemy przed problemem w jaki sposób poparcie w kraju przełoży się na poparcie w okręgach. W takiej sytuacji zazwyczaj wykorzystuje się wzorce z poprzednich wyborów, stąd ten program:https://logikazycia.eu/kalkulator-wyborczy-2023/ pisany przed 15.10.2023 nie daje dokładnie takiego podziału mandatów jakie miało miejsce po 15.10.2023.↩︎