Przeczytaj
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:
Wyświetlanie wierszy
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.
![Ilustracja przedstawia otwarte okno dialogowe Tabela przestawna z innej tabeli lub zakresu. Opcja Tabela/zakres: Przeczytaj!$A$1:$C$31. Wybierz, gdzie chce umieścić tabelę przestawną, opcja istniejący arkusz, lokalizacja: Przeczytaj!$E$2. Na dole okna znajdują się przyciski: OK, Anuluj.](https://static.zpe.gov.pl/portal/f/res-minimized/ROqJfi9ri2RPo/1677594047/2FclME5wHjEZ41uSlNGrtyId0o4RZQ2G.png)
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ści
i Pierwsze 10...
![Ilustracja przedstawia otwarte okno dialogowe Filtry wartości i dalej Pierwsze 10 oraz zaznaczone opcje: Zaznacz wszystko, Afganistan, Algieria, Arabia Saudyjska, Argentyna, Bangladesz, Birma, Brazylia. Na dole okna znajdują się przyciski OK oraz Anuluj.](https://static.zpe.gov.pl/portal/f/res-minimized/RJPDHSKIZmhU1/1677594047/Bau7a8wsEidP1sICJy8VFt3uBBob9Pqb.png)
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
.
![Ilustracja przedstawia fragment otwartego okna dialogowego Filtr 10 pierwszych wartości (KRAJ) i zaznaczone opcje: Pokaż górne, 10, Elementy, według Suma z LICZBA LUDNOŚCI. Na dole okna znajdują się przyciski OK oraz Anuluj.](https://static.zpe.gov.pl/portal/f/res-minimized/RPK4jTdi0MfNF/1677594047/1QXNCH7sDWkrTEOgWWIFTuxlEbXnapmq.png)
Tak wygląda ostateczny efekt:
![Ilustracja przedstawia fragment arkusza z danymi w dwóch kolumnach. W kolumnie pierwszej znajduje się nagłówek Kraj, w kolumnie drugiej Suma z Liczba Ludności. Poniżej znajdują się następujące dane. Wiersz 2: Bangladesz; 156050883. Wiersz 3: Brazylia; 198739269. Wiersz 4: Chiny; 1338612968. Wiersz 5: Egipt; 83082869. Wiersz 6: Etiopia; 85237338. Wiersz 7: Filipiny; 97976603. Wiersz 8: Indie; 1166079217. Wiersz 9: Filipiny; 97976603. Wiersz 10: Indie; 1166079217. Wiersz 11: Indonezja; 240271522. Wiersz 12: Japonia; 127078679. Wiersz 13: Meksyk; 111211789. Wiersz 14: Suma końcowa 3604341137.](https://static.zpe.gov.pl/portal/f/res-minimized/R8UvRv7N5sbQg/1677594048/2dySoG7plewyW6DYuAxTGckoDnGVaa89.png)
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.
![Ilustracja przedstawia otwarte okno dialogowe Układ tabeli przestawnej. Znajduje się tu pięć okien: Filtry, Pola kolumn, Pola wierszy, Pola danych i Pola dostępne. W oknie pola dostępne zaznaczona jest opcja Liczba ludności. Poniżej zaznaczono opcje: Kolumny podsumowania, Wiersze podsumowania oraz Źródło, Wybór $Przeczytaj.$A$1:$C$32. Cel Wybór: $Przeczytaj.$E$2. Na dole okna znajdują się przyciski: Pomoc, OK oraz Anuluj.](https://static.zpe.gov.pl/portal/f/res-minimized/R1BxemuGaWCJ0/1677594048/3PT8W8otjSlfvMURI2NKJWXUdxAT5yGS.png)
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
.
![Ilustracja przedstawia otwarte okno dialogowe Opcje pola danych. Sortuj według Kraj, opcja ręcznie. Opcja wyświetlania, Układ tabelaryczny. Pokaż automatycznie: zaznaczona opcja Pokaż 10 elementy, Od: Góra, Używane pole: Suma – Liczba ludności. Na dole okna znajdują się przyciski: Pomoc, OK oraz Anuluj.](https://static.zpe.gov.pl/portal/f/res-minimized/RbxweJzZROZxB/1677594048/2FMZ9mSalCBW53Y4jzEGCcLH6sy52opD.png)
Tak wygląda ostateczny efekt:
![Ilustracja przedstawia fragment arkusza z danymi w dwóch kolumnach. W kolumnie pierwszej znajduje się nagłówek Kraj, w kolumnie drugiej Suma z Liczba Ludności. Poniżej znajdują się następujące dane. Wiersz 2: Bangladesz; 156050883. Wiersz 3: Brazylia; 198739269. Wiersz 4: Chiny; 1338612968. Wiersz 5: Egipt; 83082869. Wiersz 6: Etiopia; 85237338. Wiersz 7: Filipiny; 97976603. Wiersz 8: Indie; 1166079217. Wiersz 9: Filipiny; 97976603. Wiersz 10: Indie; 1166079217. Wiersz 11: Indonezja; 240271522. Wiersz 12: Japonia; 127078679. Wiersz 13: Meksyk; 111211789. Wiersz 14: Razem wynik 3604341137.](https://static.zpe.gov.pl/portal/f/res-minimized/R1OVMwArbbZx6/1677594049/27s1u0cIrMj9rKmcfIYkP1wtaHlwtyDI.png)
Wyświetlanie wierszy w tabeli przestawnej
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).
![Ilustracja przedstawia otwarty arkusz z danymi. Ukazane są dwie kolumny E i F. Kolumna E ma nagłówek Kontynent, kolumna F ma nagłówek Suma i przy nim otwarte okno Wszystko z nazwami kontynentów. W oknie tym zaznaczona jest Azja. W tle okna, w kolumnach E i F znajdują się dane.](https://static.zpe.gov.pl/portal/f/res-minimized/R8UMfKmrwtibv/1677594049/bhHU72gSKeZM8WDWlhKarI7w5THVDX30.png)
Tak wygląda tabela przestawna po zastosowaniu filtra:
![Ilustracja przedstawia tabelę po zastosowaniu filtra. Ukazany jest fragment arkusza z danymi w dwóch kolumnach E i F. W wierszu pierwszym, w kolumnie E znajduje się nadgłówek Kontynent, a w kolumnie F nagłówek Azja. W wierszu trzecim, w kolumnie E znajduje się nagłówek Kraj, w kolumnie F Suma z Liczba Ludności. Poniżej znajdują się następujące dane. Wiersz 2: Afganistan; 33609937. Wiersz 3: Bangladesz; 156050883. Wiersz 4: Birma; 48137741. Wiersz 5: Chiny; 1338612968. Wiersz 6: Filipiny; 97976603. Wiersz 7: Indie; 1166079217. Wiersz 8: Indonezja; 240271522. Wiersz 9: Iran; 66429284. Wiersz 10: Japonia; 127078779. Wiersz 11: Korea Południowa; 48508972. Wiersz 12: Suma końcowa 3322755806.](https://static.zpe.gov.pl/portal/f/res-minimized/R1OgfrpYUbIpG/1677594049/214F4LYQrDR89CwiRp5VffV7YgboalwH.png)
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).
![Ilustracja przedstawia otwarty arkusz z danymi. Ukazane są dwie kolumny. Kolumna pierwsza ma nagłówek Kontynent, kolumna druga ma nagłówek Suma i przy nim otwarte okno Wszystko z podanymi niżej nazwami kontynentów. W oknie tym zaznaczona jest Azja. Na dole okna znajdują się dwa przyciski: OK, Anuluj. W tle okna, w kolumnach E i F znajdują się dane.](https://static.zpe.gov.pl/portal/f/res-minimized/R1Mo3ZjhNY7ez/1677594050/1g3RYTwt5dZA4zd22GWcUWYx3ce7eCxf.png)
Tak wygląda tabela przestawna po zastosowaniu filtra:
![Ilustracja przedstawia tabelę po zastosowaniu filtra. Ukazany jest fragment arkusza z danymi w dwóch kolumnach E i F. W wierszu pierwszym, w kolumnie E znajduje się nadgłówek Kontynent, a w kolumnie F nagłówek Azja. W wierszu trzecim, w kolumnie E znajduje się nagłówek Kraj, w kolumnie F Suma z Liczba Ludności. Poniżej znajdują się następujące dane. Wiersz 2: Afganistan; 33609937. Wiersz 3: Bangladesz; 156050883. Wiersz 4: Birma; 48137741. Wiersz 5: Chiny; 1338612968. Wiersz 6: Filipiny; 97976603. Wiersz 7: Indie; 1166079217. Wiersz 8: Indonezja; 240271522. Wiersz 9: Iran; 66429284. Wiersz 10: Japonia; 127078779. Wiersz 11: Korea Południowa; 48508972. Wiersz 12: Razem wynik 3322755806.](https://static.zpe.gov.pl/portal/f/res-minimized/RdhJkNBc74O3D/1677594050/2W9HXfKZeAeTFa1NTVE7an5otgRexNy6.png)
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.
![Ilustracja przedstawia fragment arkusza kalkulacyjnego. Ukazane są kolumny od a do g oraz wiersze od 1 do 12. Kolumna a została zatytułowana kategoria. Kolumna b została zatytułowana usługa. Kolumna c została zatytułowana pierwszy kwartał 2021. Kolumna d została zatytułowana drugi kwartał 2022. Kolumna e została zatytułowana trzeci kwartał 2023. Kolumna f została zatytułowana zwarty kwartał 2024. Kolumna g została zatytułowana rok 2021. W wierszach od 2 do 12 znajdują się następujące dane, w wierszu 2, eksploatacja, wymiana oleju, 3200,00 złotych, 4520,00 złotych, 2530,00 złotych, 300,00 złotych, 10550,00 złotych. W wierszu 3, eksploatacja, wymiana opon, 200,00 złotych, 3587,00 złotych, 120,00 złotych, 4580,00 złotych, 8487,00 złotych. W wierszu 4, uszkodzenie, łatanie opon, 200,00 złotych, 210,00 złotych, 200,50 złotych, 231,52 złotych, 842,02 złotych. W wierszu 5, eksploatacja, wymiana żarówek, 100,00 złotych, 110,00 złotych, 121,00 złotych, 131,10 złotych, 462,10 złotych. W wierszu 6, eksploatacja, wymiana paska rozrządu, 3541,00 złotych, 4500,00 złotych, 1200,00 złotych, 12547,00 złotych, 21788,00 złotych. W wierszu 7, uszkodzenie, remont silnika, 12000,00 złotych, 3210,00 złotych, 21450,00 złotych, 12457,00 złotych, 49117,00 złotych. W wierszu 8, eksploatacja, wymiana oleju, 3200,00 złotych, 4520,00 złotych, 120,00 złotych, 300,00 złotych, 10550,00 złotych. W wierszu 9, eksploatacja, wymiana filtra kabinowego, 200,00 złotych, 120,00 złotych, 121,00 złotych, 354,00 złotych, 795,00 złotych. W wierszu 10, uszkodzenie, regulacja zwieszenia, 780,00 złotych, 1574,00 złotych, 4410,00 złotych, 1478,00 złotych, 8242,00 złotych. W wierszu 11, uszkodzenie, regeneracja turbiny, 12354,00 złotych, 14754,00 złotych, 1240,00 złotych, 800,00 złotych, 29148,00 złotych. W wierszu 12, uszkodzenie, wymiana katalizatora, 3574,00 złotych, 800,00 złotych, 1200,00 złotych, 3000,00 złotych, 8574,00 złotych.](https://static.zpe.gov.pl/portal/f/res-minimized/R1VX3orxMFx26/1677594050/kx4dBOMX3wcyKmwCGwxLucHakK8WCXbA.png)
Aby otrzymać poprawny wynik, wykonujemy następujące czynności:
Tworzymy tabelę przestawną z zakresu komórek A1:G12.
Do tabeli w oknie
Pola tabeli przestawnej
wybieramy pola:KATEGORIA
(przenosimy do obszaruFiltry
),USŁUGA
(przenosimy do obszaruWiersze
) irok 2021
(przenosimy do obszaruWartości
).Klikamy prawym przyciskiem myszki na komórkę z
Suma z rok 2021
i wybieramy opcjęUstawienia pola wartości...
Klikamy zakładkę
Pokazywanie wartości jako
i wybieramy opcję% sumy końcowej
.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ą.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.
![Ilustracja przedstawia fragment arkusza kalkulacyjnego. Ukazane są kolumny od a do b oraz wiersze od 1 do 10. W wierszu 1 wpisano, w komórce a 1 Kategoria, w komórce b 1 eksploatacja. Wiersz 2 jest pusty. W wierszu 3 znajdują się nazwy kolumn. Kolumna a została zatytułowana etykiety wierszy, po prawej stronie nazwy znajduje się przycisk służący do rozwijania listy. Kolumna b została zatytułowana suma z, rok 2021. W wierszach od 4 do 10 mamy następujące dane, w wierszu 4, wymiana filtra kabinowego 1,68%. W wierszu 5, wymiana filtra paliwa 11,01%. W wierszu 6, wymiana oleju 22,31%. W wierszu 7, wymiana opon 17,95%. W wierszu 8, wymiana paska rozrządu 46,08%. W wierszu 9, wymiana żarówek 0,98%. W wierszu 10, suma końcowa 100,00%.](https://static.zpe.gov.pl/portal/f/res-minimized/R1NiOTa0Cc2KG/1677594051/vun7uS1SunjyCtB3qeWCFrd2GJ0gjQ9Q.png)
![Ilustracja przedstawia fragment arkusza kalkulacyjnego. Ukazane są kolumny od a do b oraz wiersze od 1 do 9. W wierszu 1 wpisano, w komórce a 1 Kategoria, w komórce b 1 uszkodzenie. Wiersz 2 jest pusty. W wierszu 3 znajdują się nazwy kolumn. Kolumna a została zatytułowana etykiety wierszy, po prawej stronie nazwy znajduje się przycisk służący do rozwijania listy. Kolumna b została zatytułowana suma z, rok 2021. W wierszach od 4 do 9 mamy następujące dane, w wierszu 4, łatanie opon 0,88%. W wierszu 5, regeneracja turbiny 30,39%. W wierszu 6, regulacja zawieszenia 8,59%. W wierszu 7, remont silnika 51,20%. W wierszu 8, wymiana katalizatora 8,94%. W wierszu 9, suma końcowa 100,00%.](https://static.zpe.gov.pl/portal/f/res-minimized/R1Dj63SsLpTt2/1677594051/18RkLyZFPnvP0L0Z6tcjMpnZt7fXiEMi.png)
Aby otrzymać poprawny wynik, wykonujemy następujące czynności:
Tworzymy tabelę przestawną z zakresu komórek A1:G12.
Do tabeli w oknie
Układ tabeli przestawnej
wybieramy pola:KATEGORIA
(przenosimy do obszaruFiltry
),USŁUGA
(przenosimy do obszaruPola wierszy
) irok 2021
(przenosimy do obszaruPola danych
).Klikamy prawym przyciskiem myszki na komórkę z
Suma - rok 2021
wybieramy opcjęWłaściwości...
Klikamy dwukrotnie
Suma - rok 2021
, rozwijamy poleWartość wyświetlana
, klikając na znak plusa obok i z rozwijanej listyTyp:
wybieramy opcję% przez całość
.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ą.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.
![IlustracjaIlustracja przedstawia fragment arkusza kalkulacyjnego. Ukazane są kolumny od a do b oraz wiersze od 1 do 10. W wierszu 1 wpisano, w komórce a 1 Kategoria, w komórce b 1 eksploatacja po prawej stronie nazwy znajduje się przycisk służący do rozwijania listy. Wiersz 2 jest pusty. W wierszu 3 znajdują się nazwy kolumn. Kolumna a została zatytułowana usługa, po prawej stronie nazwy znajduje się przycisk służący do rozwijania listy. Kolumna b została zatytułowana suma z, rok 2021. W wierszach od 4 do 10 mamy następujące dane, w wierszu 4, wymiana filtra kabinowego 1,68%. W wierszu 5, wymiana filtra paliwa 11,01%. W wierszu 6, wymiana oleju 22,31%. W wierszu 7, wymiana opon 17,95%. W wierszu 8, wymiana paska rozrządu 46,08%. W wierszu 9, wymiana żarówek 0,98%. W wierszu 10, razem wynik 100,00%.](https://static.zpe.gov.pl/portal/f/res-minimized/RKOcgRT3cD2jv/1677594052/ccLSwnFz7CnqGExAOdPMSPsulQ8VkHFZ.png)
![Ilustracja przedstawia fragment arkusza kalkulacyjnego. Ukazane są kolumny od a do b oraz wiersze od 1 do 9. W wierszu 1 wpisano, w komórce a 1 Kategoria, w komórce b 1 uszkodzenie po prawej stronie nazwy znajduje się przycisk służący do rozwijania listy. Wiersz 2 jest pusty. W wierszu 3 znajdują się nazwy kolumn. Kolumna a została zatytułowana etykiety wierszy, po prawej stronie nazwy znajduje się przycisk służący do rozwijania listy. Kolumna b została zatytułowana suma z, rok 2021. W wierszach od 4 do 9 mamy następujące dane, w wierszu 4, łatanie opon 0,88%. W wierszu 5, regeneracja turbiny 30,39%. W wierszu 6, regulacja zawieszenia 8,59%. W wierszu 7, remont silnika 51,20%. W wierszu 8, wymiana katalizatora 8,94%. W wierszu 9, razem wynik 100,00%.](https://static.zpe.gov.pl/portal/f/res-minimized/R1K0u1m5fHNFu/1677594052/jeGZhJMqcc31P8OUNzQOnCnBG2YoX38d.png)
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
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