Excel – Excel i VBA

Plik csv w Excelu

Plik csv jest bardzo często spotykanym formatem zapisywania danych, szczególnie w przypadkach danych pobieranych z różnego rodzaju baz danych. Zwyczajowo jest otwierany w Excelu, ale nie można zapominać o tym, że nie jest to plik Excela tylko plik tekstowy rozdzielany przecinkami. Oznacza to, że nie powinniśmy otwierać plików csv tylko importować go jako plik tekstowy. Pozwala to także na uniknięcie różnych problemów z formatowaniem liczb, generującymi w efekcie błędy w analizach.

Kontynuując wątek statystyki wyborcze pokażę to na przykładzie arkuszy wyborczych pobranych ze strony PKW:
https://wybory.gov.pl/sejmsenat2023/pl/dane_w_arkuszach
Tym razem jednak pobieram nie plik Excela tylko csv.

Po rozpakowaniu i zapisaniu na dysku, otwieramy Excela i w nowym skoroszycie na karcie Dane wybieramy Z tekstu

Dla nowszych wersji Excela:

Automatycznie otworzy się kreator importu tekstu

Ustawiamy tu formatowanie (najlepiej na UTF-8), żeby prawidłowo wyświetlać polskie litery.
W kolejnym kroku zaznaczamy średnik jako znak podziału

W ostatnim kroku kreatora możemy jeszcze zdefiniować typ danych w kolejnych kolumnach – czy ma być to tekst czy liczba i ewentualnie – jakie formatowanie.

I już – plik tekstowy csv został zaimportowany do Excela.

Operacja odwrotna tj. eksport z Excela do pliku csv to po prostu zapisanie pliku Excela jako pliku csv:

Nie można tu jednak zapominać, że tak zapisany zostanie tylko jeden aktywny arkusz. Poza tym nie zostanie zachowane obramowanie, kolor czy wielkość czcionki itp.

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.

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)

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.

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.

Efekt końcowy wygląda tak:

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.

Funkcja MAX.K

Funkcja MAX.K to jedna z funkcji statystycznych Excela. Zwraca ona k-tą największą wartość z tablicy lub zakresu komórek.
Argumenty funkcji to:

- Tablica – zakres komórek lub tablica wartości, z której chcemy uzyskać wynik. Argument obowiązkowy
- K -liczba całkowita większa od zera, określająca którą z kolei największą wartość chcemy. otrzymać. Argument obowiązkowy.

Pomijane są tu wartości tekstowe. W przypadku gdy wstawiona zostanie błędna wartość k – np. liczba ujemna, ułamkowa czy wiesza od liczby argumentów w Tablicy – wynikiem działania funkcji będzie błąd #LICZBA!

Odpowiednikiem funkcji w kodzie VBA jest funkcja arkuszowa Large.

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.

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.

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:

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…

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

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:

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

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ę.

Kategoria: Excel

Plik csv w Excelu

Like this:

Formatowanie warunkowe za pomocą funkcji

Like this:

Funkcja MAX.K

Like this:

Tabela Przestawna

Like this:

Statystyki wyborcze czyli ćwiczymy Excela

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: