Organizacja danych w arkuszu kalkulacyjnym

Organizacja danych w arkuszu kalkulacyjnym jest prosta. Poszczególne kolumny zawierają zmienne, czyli wartości cechy, zjawiska czy faktu. Aby tabela stała się bardziej czytelna, w komórkach nagłówkowych kolumn możemy wprowadzić nazwę danej zmiennej.

Jeśli jednak takie zestawienie zawiera znacznie więcej danych, a my chcemy raportować ich część albo relacje, które między nimi zachodzą, to możemy się posłużyć tabelami przestawnymi. Tabele te, jak sama nazwa wskazuje, dają nam możliwość „przestawiania” istniejących danych, a także ukrywania danych niespełniających określonych kryteriów, wyświetlania w pożądanej kolejności lub wykonywania obliczeń. Tabele przestawne stanowią zatem tę funkcjonalność arkusza, która ułatwia analizę danych.

Plik do pobrania zawiera arkusz z nazwami kontynentów oraz krajów, a także odpowiadającą temu krajowi liczbę ludności. Na podstawie tych danych chcemy przedstawić dziesięć najbardziej zaludnionych krajów w zestawieniu, a następnie dziesięć najbardziej zaludnionych krajów z kontynentu azjatyckiego.

Pobierz przykładowe dane:

R19fZBw4x0AaT

Przycisk do pobrania plików. Jeden plik w formacie XLSX, drugi plik w formacie ODS.

Plik ZIP o rozmiarze 17.52 KB w języku polskim

Wyświetlanie wierszy

Microsoft Excel

Aby rozwiązać postawiony problem, wykorzystamy tabelę przestawną.

W pierwszej części zadania chcemy wybrać dziesięć najbardziej zaludnionych krajów. Posłuży nam do tego narzędzie filtrowaniefiltrowaniefiltrowanie.

Na podstawie zestawu danych w arkuszu z obszaru A1:C31 tworzymy tabelę przestawną, lokując ją w tym samym lub nowym arkuszu. Zdecydowaliśmy się na komórkę E2 w tym samym arkuszu.

ROqJfi9ri2RPo
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

Budujemy tabelę, przenosząc pole KRAJ do obszaru Wiersze, a pole LICZBA LUDNOŚCI do obszaru Wartości. Teraz musimy przefiltrować tablicę tak, aby wyświetliły się tylko kraje spełniające warunki zadania. W tym celu klikamy przycisk filtrowania znajdujący się w polu o nazwie KRAJ. Następnie wybieramy opcję Filtry wartościPierwsze 10...

RJPDHSKIZmhU1
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

W wyświetlonym oknie dialogowym ustalamy kolumnę, według której ma się dokonać filtrowanie. W naszym przypadku będzie to Suma z LICZBA LUDNOŚCI.

RPK4jTdi0MfNF
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

Tak wygląda ostateczny efekt:

R8UvRv7N5sbQg
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
LibreOffice Calc

Aby rozwiązać postawiony problem, wykorzystamy tabelę przestawną.

W pierwszej części naszego zadania mamy wybrać dziesięć najbardziej zaludnionych krajów. Posłuży nam do tego narzędzie AutofiltrfiltrowanieAutofiltr.

Na podstawie zestawu danych w arkuszu z obszaru A1:C31 tworzymy tabelę przestawną, lokując ją w tym samym lub nowym arkuszu. My zdecydowaliśmy się na komórkę E2, w tym samym arkuszu.

R1BxemuGaWCJ0
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

Budujemy tabelę przenosząc pole KRAJ do obszaru Pola wierszy, a pole LICZBA LUDNOŚCI do obszaru Pola danych. Teraz musimy przefiltrować tablicę tak, aby wyświetliły się tylko kraje spełniające warunki zadania. Aby to osiągnąć, otwieramy okno właściwości tabeli przestawnej. Następnie w polu Pola wierszy klikamy dwukrotnie na KRAJ, a następnie w nowo wyświetlonym oknie wybieramy przycisk Opcje.... W polu Pokaż automatycznie zaznaczamy opcję Pokaż:, wybieramy 10 elementów, z listy Od: wybieramy Góra, a z listy Używane pole: opcję Suma - LICZBA LUDNOŚCI.

RbxweJzZROZxB
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

Tak wygląda ostateczny efekt:

R1OVMwArbbZx6
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

Wyświetlanie wierszy w tabeli przestawnej

Microsoft Excel

Jeśli jednak chcemy dokonać takiego samego wyboru tylko dla wybranych krajów, na przykład azjatyckich, wówczas musimy użyć innej funkcjonalności oferowanej przez narzędzie filtrowanie w tabeli przestawnej.

Po dodaniu pola KONTYNENT do obszaru Filtry uzyskujemy możliwość wyboru dziesięciu najbardziej zaludnionych krajów jedynie z kontynentu azjatyckiego. W tym celu klikamy przycisk filtrowania znajdujący się w nowym polu tabeli przestawnej (w komórce F1) i wybieramy Azję (jak to pokazuje ilustracja poniżej).

R8UMfKmrwtibv
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

Tak wygląda tabela przestawna po zastosowaniu filtra:

R1OgfrpYUbIpG
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
LibreOffice Calc

Jeśli jednak chcemy dokonać takiego samego wyboru tylko dla wybranych krajów, na przykład azjatyckich, wówczas musimy użyć innej funkcjonalności oferowanej przez narzędzie filtrowanie w tabeli przestawnej.

Po dodaniu pola KONTYNENT do obszaru Filtry uzyskujemy możliwość wyboru dziesięciu najbardziej zaludnionych krajów jedynie z kontynentu azjatyckiego. W tym celu klikamy przycisk filtrowania znajdujący się w nowym polu tabeli przestawnej (w komórce F1) i wybieramy Azję (jak to pokazuje ilustracja poniżej).

R1Mo3ZjhNY7ez
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

Tak wygląda tabela przestawna po zastosowaniu filtra:

RdhJkNBc74O3D
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

Przykład

W arkuszu Przykład znajduje się zestawienie wysokości faktur za poszczególne usługi naprawcze w warsztacie samochodowym. Chcemy odpowiedzieć na pytanie, które z usług mają pod względem przychodów liczonych dla roku 2021 najwyższy udział procentowy w swojej kategorii.

R1VX3orxMFx261
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Microsoft Excel

Aby otrzymać poprawny wynik, wykonujemy następujące czynności:

  1. Tworzymy tabelę przestawną z zakresu komórek A1:G12.

  2. Do tabeli w oknie Pola tabeli przestawnej wybieramy pola: KATEGORIA (przenosimy do obszaru Filtry), USŁUGA (przenosimy do obszaru Wiersze) i rok 2021 (przenosimy do obszaru Wartości).

  3. Klikamy prawym przyciskiem myszki na komórkę z Suma z rok 2021 i wybieramy opcję Ustawienia pola wartości...

  4. Klikamy zakładkę Pokazywanie wartości jako i wybieramy opcję % sumy końcowej.

  5. Następnie z pola KATEGORIA klikamy przycisk filtra (komórka B1) i wybieramy wartość: eksploatacja, odnotowując tę usługę, której koszty mają najwyższy procentowo udział w kosztach usług związanych z eksploatacją.

  6. Powtarzamy krok nr 5, wybierając tym razem wartość uszkodzenia.

Wyniki, dla kategorii eksploatacja i uszkodzenia prezentują się tak jak na dwóch poniższych ilustracjach.

R1NiOTa0Cc2KG
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
R1Dj63SsLpTt2
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
LibreOffice Calc

Aby otrzymać poprawny wynik, wykonujemy następujące czynności:

  1. Tworzymy tabelę przestawną z zakresu komórek A1:G12.

  2. Do tabeli w oknie Układ tabeli przestawnej wybieramy pola: KATEGORIA (przenosimy do obszaru Filtry), USŁUGA (przenosimy do obszaru Pola wierszy) i rok 2021 (przenosimy do obszaru Pola danych).

  3. Klikamy prawym przyciskiem myszki na komórkę z Suma - rok 2021 wybieramy opcję Właściwości...

  4. Klikamy dwukrotnie Suma - rok 2021, rozwijamy pole Wartość wyświetlana, klikając na znak plusa obok i z rozwijanej listy Typ: wybieramy opcję % przez całość.

  5. Następnie z pola KATEGORIA klikamy przycisk filtra (komórka B1) i wybieramy wartość: eksploatacja, odnotowując tę usługę, której koszty mają najwyższy procentowo udział w kosztach usług związanych z eksploatacją.

  6. Powtarzamy krok nr 5, wybierając tym razem wartość uszkodzenia.

Wyniki dla kategorii eksploatacja i uszkodzenia prezentują się tak jak na dwóch poniższych ilustracjach.

RKOcgRT3cD2jv
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
R1K0u1m5fHNFu
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

Z obu zestawień możemy odczytać interesujące nas wyniki: w kategorii eksploatacja usługą o najwyższym procentowym udziale w ogólnych kosztach jest wymiana paska rozrządu, a w kategorii uszkodzenie – remont silnika.

Słownik

filtrowanie
filtrowanie

stosowanie kryteriów (poprzez wskazanie wybranych wartości znajdujących się w bazie danych) w celu ograniczania (selekcji) wyświetlania tylko do tych rekordów, które spełniają dane kryterium; filtrowanie wspomaga analizę danych zawartych w arkuszu