Zakres dynamiczny

Zasady tworzenia nazwanych zakresów komórek opisałam w notce:
Nazwa zakresu
Tak zdefiniowane zakresy są jednak sztywne, zapisane na stałe.  Chcąc je rozszerzyć – trzeba wprowadzić zmiany, edytując odwołanie nazwy.
Nazwy można jednak także opisywać formułą automatycznie zmieniającą zakres wchodzących w jej skład komórek.

Załóżmy, że mamy tabelkę jak na screenie:

Jak zdefiniować taki zakres, który automatycznie zmieni się po dopisaniu wartości w kolejnych komórkach?  Można to zrobić poprzez taką formułę:
=PRZESUNIĘCIE(Arkusz3!$A$1;;;ILE.NIEPUSTYCH(Arkusz3!$A:$A);1)

Tak zdefiniowany zakres będzie się automatycznie zmieniać, gdy dopiszemy kolejne wiersze. W wyniku działania funkcji – wysokość zakresu jest określona  przez liczbę niepustych komórek w kolumnie A.
Bardzo przydatne narzędzie m.in. w liście wybieralnej.


 

 

Uniwersalna lista świąt

W wielu różnych rozwiązaniach Excela np.grafiki,  harmonogramy itp. istotnym elementem są daty świąt.  Pewne dni są przewidywalne – Nowy Rok zawsze wypada 1 stycznia, Święto Niepodległości – 11 listopada, ale co z datami świąt ruchomych? Owszem, można zajrzeć do kalendarza  i sprawdzić datę Wielkanocy czy Bożego Ciała, ale w kolejnym roku trzeba to zrobić ponownie. Na szczęście można to zrobić raz, a dobrze – wykorzystując po prostu formuły Excela.

Na rok 2019 wygląda to tak:

Po zmianie w komórce A1 roku na 2020 wygląda to tak:

Jak zrobić taką listę? W komórce A3 wyznaczającej datę Nowego Roku jest formuła:
=DATA(A1;1;1)
Podobnie dla A4:
=DATA(A1;1;6)
W analogiczny sposób ustawiamy daty świąt mających swoje stałe daty w kalendarzu.
Święta  ruchome powiązane są natomiast z Wielkanocą. Przypada zawsze w niedzielę po pierwszej wiosennej pełni księżyca. W Excelu wyznaczamy ją formułą w komórce A5:
=ZAOKR.W.DÓŁ(DATA(A1;5;DZIEŃ(MINUTA(A1/38)/2+56));7)-34
Drugi dzień Wielkanocy (komórka A6) jest już prosty:
=A5+1
Zielone Świątki to formuła:
=A5+49
Natomiast Boże Ciało (komórka A10) to:
=A5+60

W ten sposób można ustawić listę świąt na dany rok. Wystarczy zmienić wartość w oznaczającej rok komórce A1 i nastąpi automatyczne przeliczenie.

 

 

Kurs Excel - zaawansowane techniki

 

Kalkulator wyborczy

Kalkulator wyborczy czyli plik Excela przeliczający głosy na mandaty. Uwzględnia przekroczenie progu wyborczego (5% dla partii i 8% dla koalicji). Oczywiście jest oparty o metodę D’Hondta. W roku podwójnych wyborów może się przydać.

Plik Excela do pobrania jest tu:

Kalkulator wyniku wyborów

Plik Excela jest bez makr. A jak działa?

Do wypełnienia są żółte pola. W komórce D6 wpisujemy liczbę mandatów do podziału, w komórkach C4:M4 – oznaczenie T/N dla koalicji, a w komórkach C5:M5 – liczbę głosów uzyskanych przez poszczególne partie. I już całą reszta jest obliczana automatycznie.

Komórka B10 to suma wszystkich głosów czyli suma zakresu C5:M5.
W wierszu 6 mamy formułę np. w C6: =C5/$B$10 – w pozostałych kolumnach wiersza 6 – analogicznie. W ten sposób obliczamy procent uzyskanych głosów dla poszczególnych partii.
W komórce C7 (i analogicznie w kolumnach od D do M) jest formuła:
=JEŻELI(C4=”T”;JEŻELI(C6>=8%;”T”;”N”);JEŻELI(C6>=5%;”T”;”N”))
– następuje tu sprawdzenie, w zależności od tego czy jest to koalicja czy nie, czy został przekroczony próg wyborczy.

Metoda H’ondta polega na tym, że w pierwszym kroku wstawiamy do tabeli wszystkie wyniki, 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 kolumnie nie ma wartości lub wynik działania formuły w wierszu 7 jest “N”  – jest ona pobierana do wiersza 14, jeżeli nie – ZERO.

Od wiersza 15 – formuła wygląda tak:
np. w komórce C15: =JEŻELI(LUB(C14=””;C14jeżeli w komórce  powyżej jest wartość co najmniej 1 – jest ona dzielona przez wartość w komórce B15 czyli 2. Analogicznie – w kolejnych wierszach.

Formuła B13 jest natomiast formuła:

=MAX.K(C14:M313;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:C313;”>=” &$B13);0)

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

I już, wszystko jasne. – można wspomóc PKW kalkulatorem wyborczym.