Formatowanie warunkowe za pomocą funkcji

Formatowanie warunkowe za pomocą funkcji to jeden z najczęściej spotykanych sposobów wizualizacji danych. Bardzo elastyczny
i dający naprawdę dużo możliwości dynamicznego zaznaczenia tych komórek, które spełniają kryterium.

Przycisk Formatowania warunkowego jest na karcie Narzędzia Główne w grupie opcji Style.

kliknij, aby powiększyć

Wybieramy Nową formułę…

kliknij, aby powiększyćFormuła, która decydować, czy komórka ma zostać sformatowana, musi być oparta o funkcję logiczną Jeżeli():
=Jeżeli(Warunek;1;0)

Dobrym przykładem może być tu lista kandydatów do Sejmu w wyborach parlamentarnych 2023. 
W kolumnie P (Czy przyznano mandat) jest  informacja Tak lub Nie. Wierszy jest ok.6500.
Jak zaznaczyć wszystkie wiersze kandydatów, którzy otrzymali mandat? 
W polu formuły wpisujemy:
=JEŻELI($P2=”Tak”;1;0)

kliknij, aby powiększyć

Zwracam uwagę na zapis adresu komórki w funkcji – odwołanie do kolumny musi być bezwzględne, do wiersza względne czyli $P2.

Kolejny krok – to ustawienie formatowania. Naciskamy przycisk Formatuj i wybieramy jakie ma być zaznaczenie komórek spełniających kryteria.

kliknij, aby powiększyć

Po naciśnięciu OK, okno się zamknie. Musimy wejść ponownie (poprzez Zarządzaj regułami…) i ustalić zakres komórek, w którym ma wystąpić formatowanie zgodnie z tą regułą. Musimy go zdefiniować w formie adresu bezwzględnego. Następnie naciskamy przycisk Zastosuj i OK.

kliknij, aby powiększyć

Efekt końcowy wygląda tak:

kliknij, aby powiększyć

Oczywiście można dołożyć kolejne reguły dla tego samego czy innego zakresu.
Można na przykład dołożyć czerwoną czcionkę dla tych  pozycji, które w kolumnie F mają wpisane Kobieta. 
Formuła reguły będzie wyglądać tak:
=JEŻELI($F2=”Kobieta”;1;0)
Oczywiście zaznaczy to czerwoną czcionką wszystkie kandydatki. Jeżeli chcemy tak zaznaczyć tylko te, które otrzymały mandat – formuła musi wyglądać tak:
=JEŻELI(ORAZ($P2=”Tak”;$F2=”Kobieta”);1;0)

Formatowanie warunkowe ma naprawdę sporo możliwości wizualizacji danych. Warto jednak pamiętać, że może to obciążać plik. Przy dużej ilości danych da się to niestety zauważyć.
I jeszcze jedno – kod VBA nie odczytuje formatowania komórek wprowadzonego za pomocą formatowania warunkowego Chcąc sprawdzić, jakie na przykład jest kolor tła czy czcionki trzeba odwołać się do formuły reguły, nie do koloru.


 

Tabela Przestawna

Tabela przestawna to idealne narzędzie do analizy danych. Pozwala na szybką i sprawną agregację danych, tworzenie zestawień i raportów. Jest naprawdę świetnym narzędziem, szczególnie w przypadku dużej ilości danych.
Skorzystam tu z przykładu danych opublikowanych na stronie PKW:
https://wybory.gov.pl/sejmsenat2023/pl/dane_w_arkuszach
dostępne są listy kandydatów do sejmu i senatu, uzupełniony już o liczbę otrzymanych głosów oraz decyzję, kto z otrzymał mandat.

Listę kandydatów do sejmu opisywałam już tu:
Statystyki wyborcze czyli ćwiczymy Excela
Chcąc w oparciu o te dane stworzyć tabelę przestawną, zaznaczamy po prostu wszystkie kolumny i z karty Wstawianie wybieramy Tabela przestawna.

kliknij obrazek, aby powiększyć

Otworzy się formularz, gdzie definiujemy miejsce wstawienia tabeli przestawnej – domyślnie jest to nowy arkusz.

Po zatwierdzeniu zobaczymy diagram i siatkę list pól tabeli.

kliknij obrazek, aby powiększyć

Sama tabela będzie widoczna po lewej stronie, a jej układ ustawiamy po stronie prawej.
Załóżmy, że chcemy sprawdzić liczbę głosów oddanych na poszczególne listy. Przeciągamy odpowiednie pola na dolny diagram:

kliknij obrazek, aby powiększyć

Ponieważ pole Liczba głosów ma format liczbowy – domyślnie ustawia się tam wartość sumy. Oczywiście można to zmienić i/lub sformatować – wystarczy kliknąć lewym przyciskiem myszy na daną wartość  i z menu podręcznego wybrać Ustawienia pola wartości…

kliknij obrazek, aby go powiększyć

Można tam też zmienić nazwę, pokazać wartość obliczoną, sformatować wartości – możliwości jest wiele.

kliknij obrazek, aby powiększyć

Chcąc jeszcze bardziej rozbudować tabelę, można np. dołożyć jeszcze kolejne wiersze np. przynależność do partii w ramach poszczególnych list, choć w tym konkretnym przypadku jest to nieco mylące, gdyż ta sama partia w różnych wierszach ma nieco inną nazwę np. PL2050 Szymona Hołowni i Polska 2050 Szymona Hołowni.
Na pewno jednak warto zainteresować ilością mandatów otrzymanych przez każdy z komitetów. Można to zrobić na kilka różnych sposobów, ale to już temat na kolejne notki.
Wstępna wersja mojej analizy wygląda tak:

kliknij obrazek, aby powiększyć

Jest tu też formatowanie warunkowe, porównujące procent głosów oddanych na partię i procent otrzymanych mandatów – namacalny efekt metody D’Hondta.
A dla chętnych – wersja 1  pliku do pobrania tu:

Wyniki wyborów do sejmu – wersja 1

 


Kurs Excel - tabele przestawne  

Statystyki wyborcze czyli ćwiczymy Excela

Przed nami wybory. Niezależnie od wszelki analiz politycznych i socjologicznych warto też na nie spojrzeć od strony Excela. Na stronie PKW:
https://wybory.gov.pl/sejmsenat2023/pl/dane_w_arkuszach
dostępne są listy kandydatów w wyborach w formie plików Excela xlsx oraz csv.  Warto zainteresować się szczególnie listą kandydatów w wyborach do Sejmu. Ponad 6500 kandydatów (czyli z punktu widzenia Excela – rekordów) to świetny materiał do ćwiczeń. Można naprawdę w całkiem fajny sposób podszkolić własne umiejętności.

Po pobraniu, rozpakowaniu i otwarciu pliku mamy taki widok:

kliknij obrazek, aby powiększyć

Teraz wystarczy tylko sformatować.
Zaznaczamy pierwszy wiersz i na karcie Dane wciskamy przycisk Filtruj

kliknij obrazek, aby powiększyć

W ten sposób możemy bez problemu wyfiltrować interesujące nas dane np.:

W nagłówku kolumny wystarczy kliknąć na strzałkę z prawej strony, aby otworzyć filtr i wybrać konkretne dane.
W nagłówku kolumny, w której są wyfiltrowane dane zamiast strzałki widoczny jest znaczek lejka

Chcąc odfiltrować dane w pojedynczej kolumnie klikamy na ten znaczek i zaznaczamy opcję Zaznacz wszystko.

W dużych zakresach danych warto też pomyśleć, aby nie stracić z oczu nagłówka.
Na karcie Widok wybieramy Zablokuj okienka i Zablokuj górny wiersz.

Nawet przewijając w dół do kolejnych wierszy – nagłówek będzie zawsze widoczny.
W bardziej uniwersalnej opcji można ustawić się w dowolnej komórce i wybrać pierwszą opcję czyli Zablokuj Okienka. Od tej pory część arkusza powyżej i z lewej strony wybranej komórki będzie zawsze widoczna.

I jeszcze jedna uwaga. To, co widoczne jest w tym przykładzie to nie jest Tabela jako obiekt Excela, a jedynie Zakres komórek. I nie chodzi tu wcale o formatowanie, obramowanie komórek itp. Ale to już temat na osobną notkę.

 


Kurs Efektywna praca w Excelu - porady i triki

 

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

%d bloggers like this: