Kategoria: Przykłady rozwiązań

Filtrowanie tabeli

Trafiłam ostatnio na ciekawy przypadek. Tabela Excela, w jednej z kolumn – daty. Kolumna sformatowana jako data, dodatkowo jeszcze włączona poprawność danych, że musi tam być data. Wszystko działa prawidłowo.

kliknij, aby powiększyć

W oddzielnej komórce, poza tabelą – numer roku jako pojedyncza liczba (tu akurat 2022). Bez problemu ręcznie ustawiam filtr niestandardowy – działa:

kliknij, aby powiększyć

Filtr działa i wskazuje właściwe rekordy. To samo zrobiłam za pomocą procedury VBA, odwołując się do komórki I1 arkusza, gdzie jest wartość roku:

Public Sub Filtruj()
Dim JROK As Integer
Dim JDataOD As Date
Dim JDataDO As Date
With Arkusz1
JROK = .Range(„I1”)
JDataOD = DateSerial(JROK, 1, 1)
JDataDO = DateSerial(JROK, 12, 31)
.ListObjects(„Tabela1”).Range.AutoFilter Field:=6, Criteria1:= _
„>=” & JDataOD, Operator:=xlAnd, Criteria2:=”<=” & JDataDO
End With
End Sub

Również pięknie działa. Niestety, nie wszędzie. Na komputerze docelowym. gdzie miał pracować – – filtr uruchamiany procedurą nie działał. Wprawdzie tu przedstawiam tylko symulację problemu, prawdziwy plik był bardziej rozbudowany, ale problem wystąpił właśnie w tym miejscu. Co ciekawe –  przy sztywnym wpisaniu kryteriów w kodzie – było OK.
Cóż, nie wiem jakie były ustawienia systemowe tamtego komputera,
w zasadzie wiem tylko, że to Excel 2021 i nic poza tym. Udało mi się rozwiązać problem w wypróbowany już wcześniej sposób. Za każdym razem, gdy mam do czynienia z problemami z datami (zarówno w Excelu jak i Accessie) – zamieniam daty na liczby.
Data i czas to liczby
W tym konkretnym przypadku – wstawiam do tabeli nową kolumnę (można ją ukryć), która będzie bezpośrednio odwoływać się do daty, ale będzie sformatowana jako liczba.

kliknij, aby powiększyć

Po dopasowaniu procedury VBA:

Public Sub Filtruj()
Dim JROK As Integer
Dim JDataOD As Date
Dim JDataDO As Date
Dim LDataOD As Long
Dim LDataDO As Long
With Arkusz1
JROK = .Range(„I1”)
JDataOD = DateSerial(JROK, 1, 1)
JDataDO = DateSerial(JROK, 12, 31)
LDataOD = CLng(JDataOD)
LDataDO = CLng(JDataDO)
.ListObjects(„Tabela1”).Range.AutoFilter Field:=7, Criteria1:= _
„>=” &LDataOD, Operator:=xlAnd, Criteria2:=”<=” &LDataDO
End With
End Sub

Zadziałało od razu bez problemu.

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:M4 – T 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.

kliknij, aby powiększyć

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

kliknij, aby powiększyć

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.