Każdą tabelę i zakres danych można posortować w dowolny sposób. Służy do tego narzędzie Excela Sortuj w grupie opcji Sortowanie i Filtrowanie na karcie Dane.
kliknij, aby powiększyć
Jak to wygląda w praktyce? Wykorzystajmy tu znany z wątku statystyki wyborcze plik kandydatów do sejmu w wyborach 2023r.
Zaznaczamy całe kolumny (choć można precyzyjnie zaznaczyć tylko zakres, w którym są dane) i naciskamy Sortuj.
kliknij, aby powiększyć
Otworzy się formularz, w którym definiujemy parametry, zgodnie z którymi chcemy posortować dane.
kliknij, aby powiększyć
I kolejny poziom:
kliknij, aby powiększyć
Im wyższy poziom, tym wyższa kolejność sortowania.
A efekt końcowy wygląda tak:
kliknij, aby powiększyć
Oczywiście można ustalić tu własne zasady – w zależności od potrzeb.
Bardzo często analizując jakiś plik Excela będący na przykład zrzutem z jakiegoś systemu możemy zauważyć, że nie zawsze wartości wyglądające jak liczby, rzeczywiście nimi są. Może się zdarzyć, że jest to po prostu tekst. Z reguły Excel automatycznie wstawia w takich komórkach komentarz:
kliknij, aby powiększyćkliknij, aby powiększyć
Na pewno trzeba coś z tym zrobić, gdyż na takich komórkach zawodzą wszelkie działania matematyczne i nie da się zrobić większości analiz czy podsumowań. Warto tu zauważyć, że wszystkie takie przypadki wyróżniają się w jeden charakterystyczny sposób, widoczny na pierwszy rzut oka. Jeżeli nie zastosujemy dodatkowego formatowania – wartości będące liczbami są wyrównane do prawej, tekst – do lewej.
Co można z tym zrobić? Oczywiście można poklikać w każdej komórce i zamienić tekst na liczbę, ale dla większej ilości danych jest to na pewno żmudna, pracochłonna i nieopłacalna praca.
Ja w takich przypadkach najczęściej robię tak:
tworzę dodatkową kolumnę , gdzieś poza zakresem danych i tam wstawiam formułę iloczynu tej wartości tekstowej
i liczby 1.
kliknij, aby powiększyć
przeciągam dane wzdłuż kolumny
kopiuję dane w tej dodatkowej kolumnie i wklejam je jako wartości do pierwotnej kolumny.
I już, gotowe.
Zwracam jednak uwagę na jeden problem. Jeżeli jako tekst jest zapisana bardzo duża liczba – po konwersji na liczbę, automatycznie zmieni się jej zapis na naukowy i nieco ją zaokrągla. Nawet zmiana na formatowanie liczbowe (np.z separatorem tysięcznym) powoduje, że otrzymana wartość różni się od pierwowzoru. Jeżeli taki tekst liczby jest np. numerem przesyłki i trzeba go porównać z jakimś innym wykazem – bezpieczniej jest zrobić to jednak na wartościach tekstowych, bez konwersji.
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łę…
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 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:
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ę.
