Kalkulator wyborczy

Przed nami rok wyborczy -z pewnością więc może  się przydać oparty o metodę D’Hondta kalkulator wyborczy. Oczywiście w Excelu. Nie zawiera makr i kodu VBA.
Plik jest do pobrania tu:

Kalkulator wyborczy

A jak został stworzony?

kliknij obrazek, aby zobaczyć go w większej rozdzielczości

Do wypełnienia są żółte pola. W komórce D1 wpisujemy liczbę mandatów do podziału, w komórkach C4:M4T lub N dla oznaczenia koalicji, a w C5:M5 – liczbę głosów uzyskanych przez poszczególne partie. I już całą reszta jest obliczana automatycznie.
Wiersz 7 zawiera formułę sprawdzającą, czy został przekroczony prób wyborczy: 5% dla partii, 8 procent dla koalicji.
Formuła w komórce np.C7 to:
=JEŻELI(C4=”T”;JEŻELI(C6>=8%;”T”;”N”);JEŻELI(C6>=5%;”T”;”N”))

Metoda H’ondta polega na tym, że w pierwszym kroku wstawiamy do tabeli wszystkie wyniki (czyli wartości z wiersza 4), w kolejnym wierszu – dzielimy te wartości przez 2, w następnym – przez 3 itd.
Wyniki tych obliczeń są umieszczone od wiersza 14 w dół.

Formuły zawarte w wierszu 14 – to:
np. komórka C14:
=JEŻELI(LUB(C5=””;C7=”N”);0;C5)
– czyli jeżeli w tabeli z danymi w komórce C5 nie ma wartości lub w komórce C7 jest wartość N -w komórce C14 jest wartość 0.  Jeżeli żaden z tym warunków nie jest spełniony – wstawiana jest tam wartość z komórki C5 (czyli liczba otrzymanych głosów)

Od wiersza 15 – formuła wygląda tak:
np. w komórce C9:  
=JEŻELI(LUB(C14=””;C14<1);””;C$14/$B15)
jeżeli w komórce C14 jest wartość większa od 1- jest ona dzielona przez wartość w komórce B15 czyli 2. Analogicznie – w kolejnych wierszach.

Natomiast w komórce B13 jest  formuła:
=MAX.K(C14:M500;D1))

Wyznacza ona k-tą wartość maksymalną z zakresu obliczeń. Wartość k jest wyznaczona przez liczbę mandatów do podziału – zapisana jest w komórce D1. Wartości większe i równe tej wartości minimalnej są oznaczone w obliczeniach na jasnoniebiesko. Tu wykorzystałam formatowanie warunkowe:

W wierszu 8 jest wyznaczona liczba mandatów dla danej partii. Zastosowałam tu formułę:
=JEŻELI(C7=”T”;LICZ.JEŻELI(C14:C500;”>=” &$B13);0)

Następuje tu sprawdzenie – ile w zakresie obliczeń dla danej kolumny występuje liczb niemniejszych od B13.


Literatura informatyczna

Kurs Analiza danych w Excelu dla początkujących

Daty świąt w kalendarzu

Jak w Excelu wyznaczyć daty świąt kalendarzowych? Można to zrobić samymi funkcjami, bez używania makr. I to także dla świąt ruchomych, nie mających swoich stałych dat w kalendarzu.

Po zmianie roku w komórce A1 (podświetlona na żółto), daty automatycznie się zmieniają, wskazując aktualizację dla danego roku.

 

Plik Excela widoczny na screenach można pobrać tu:

Daty świąt w kalendarzu

A zastosowane funkcje (przy założeniu, że w komórce A1 jest wpisany rok)  wyglądają tak:

Data Funkcja Nazwa święta
2022-01-01 DATA(A1;1;1) Nowy Rok
2022-01-06 DATA(A1;1;6) Trzech Króli
2022-04-17 ZAOKR.W.DÓŁ(DATA(A1;5;
DZIEŃ(MINUTA(A1/38)/2+56));7)-34
Wielkanoc
2022-04-18 A5+1 Wielkanoc
2022-05-01 DATA(A1;5;1) 1 Maja
2022-05-03 DATA(A1;5;3) 3 Maja
2022-06-05 A5+49 Zielone Świątki
2022-06-16 A5+60 Boże Ciało
2022-08-15 DATA(A1;8;15) Wniebowzięcie
2022-11-01 DATA(A1;11;11) Wszystkich Świętych
2022-11-11 DATA(A1;11;1) Święto Niepodległości
2022-12-25 DATA(A1;12;25) Boże Narodzenie
2022-12-26 DATA(A1;12;26) Boże Narodzenie

Większość dat z powyższej tabeli wyznaczona jest za pomocą funkcji Excela Data(Rok,Miesiąc,Dzień) i wynika wprost z kalendarza. Zdecydowanie bardziej skomplikowane jest wyznaczenie daty Wielkanocy, przypadającej w danym roku na pierwszą niedzielę po wiosennej pełni księżyca. Jest kilka algorytmów obliczania tej daty, zainteresowanych odsyłam tu:

Wikipedia: wyznaczanie daty Wielkanocy

A skoro coś daje się obliczyć, to z pewnością da się to zrobić także w Excelu. Wynik widoczny jest w tabeli. Powiązane z Wielkanocą są także daty Zielonych Świątków oraz Bożego Ciała, przypadające w 49 i 60 dni po jej dacie.

W praktycznych rozwiązaniach (np. różnego rodzaju grafikach) najczęściej stosuję taką tabelę w osobnym (często ukrytym) arkuszu i przy sprawdzaniu czy w danym dniu wypada święto, korzystam z funkcji Wyszukaj.Pionowo.