Sortowanie tabeli

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.


Kurs Excel dla pracowników administracji

 

Liczba czy tekst wyglądający jak liczba?

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.


Kurs Efektywna praca w Excelu - porady i triki

 

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.


A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

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