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 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ę.
Funkcja Wyszukaj.Pionowo to jedna z funkcji wyszukiwania i odwołań Excela. Wyszukuje wartość w pierwszej kolumnie zadeklarowanego zakresu tabeli i zwraca wartość z określonej kolumny tego samego wiersza .
Argumenty funkcji to:
Szukana_wartość – wartość do odszukania w pierwszej kolumnie tabeli
Tabela_tablica– zakres danych do odszukania i pobierania danych
Nr_indeksu_kolumny – kolumna, z której zostanie pobrana wartość wynikowa funkcji
Przeszukiwany_zakres – wartość typu logicznego. Prawda (wartość domyślna) – dla wyszukiwania najlepszego dopasowania. Dla wyszukania dokładnej wartości – argument ten musi mieć wartość Fałsz.
Jeżeli szukana wartość nie zostanie znaleziona – w wyniku działania funkcji otrzymamy błąd #N/D!.
W praktycznym zastosowaniu najważniejsze są zasady:
szukana wartość musi być w pierwszej kolumnie, a zwracana wartość w jednej z kolumn na prawo;
funkcja zawsze zwraca odwołanie do pierwszej znalezionej wartości. Jeżeli argument Szukana_wartość występuje również w kolejnych wierszach, nie są one brane pod uwagę;
jeżeli ostatni argument ma wartość PRAWDA (to wartość domyślna), pierwsza kolumna (czyli ta, w której szukamy dopasowania) musi być posortowana rosnąco, gdyż w innym przypadku możemy otrzymać bzdurne wyniki.
Odpowiednikiem funkcji w VBA jest funkcja arkuszowa Vlookup.
Pliki Excela zawierające kod VBA (czyli makra i/lub procedury) mogą niestety zawierać również szkodliwe oprogramowanie i mogą być niebezpieczne dla naszego komputera. Ma to znaczenie szczególnie przy otwieraniu plików nieznanego pochodzenia, pochodzących gdzieś z internetu i warto się zabezpieczyć.
Ustawienia bezpieczeństwa makr dostępne są na karcie Developer w grupie opcji Kod:
Można je też ustawić na karcie Plik –> Opcje –> Centrum Zaufania –> Ustawienia Centrum Zaufania.
Dostępne są tu następujące opcje:
Ja preferuję tu drugą opcję czyli wyłączenie makr i włączenie powiadomień. Dotyczy to ustawień ogólnych Excela i dotyczy wszystkich plików. Po jej wyborze, przy otwarciu pliku z makrami zobaczymy taki komunikat:
Wystarczy, jeżeli naciśniemy przycisk Włącz zawartość i wszystkie makra będą dostępne.
Jest to z pewnością bardzo pożyteczne działanie, ale w sytuacji aplikacji znanych i sprawdzonych, z pewnego źródła – często uciążliwe. Jak się pozbyć tych komunikatów? Przynajmniej w stosunku do tych treści, których jesteśmy pewni?
Służy do tego inna z opcji Centrum Zaufania – Zaufane lokalizacje
Po naciśnięciu przycisku Dodaj nową lokalizację… , otwiera się nowy formularz, w którym definiujemy wybrany folder:
Z reguły warto od razu zaznaczyć także checkbox Podfoldery tej lokalizacji są także zaufane.
W ten sposób może wybrać zaufane lokalizacje na naszym komputerze. Jeżeli chcemy zdefiniować jakiś dysk sieciowy, konieczne jest zaznaczenie wcześniejsze checkboxa Zezwalaj na zaufane lokalizacje w mojej sieci.
