W tym poście nauczymy się, jak uzyskać pełne nazwy miesięcy w tabeli przestawnej.
Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
Najpierw na podstawie naszych danych musimy stworzyć tabelę przestawną. W tym celu zaznaczamy pojedynczą komórkę w danych, następnie wybieramy polecenie Tabela przestawna z karty Wstawianie (rys. nr 2).
Rys. nr 2 – polecenie Tabela przestawna
Otworzy nam się okno Tworzenia tabeli przestawnej, gdzie musimy wybrać miejsce jej wstawienia – wybieramy checkbox Istniejący arkusz i zaznaczamy komórkę, gdzie tabela ma zostać wstawiona. Tak określone parametry tabeli przestawnej zatwierdzamy przyciskiem OK (rys. nr 3).
Rys. nr 3 – okno Tworzenia tabeli przestawnej
Otrzymamy pole tabeli przestawnej wstawione w wybranej przez nas lokalizacji. Otworzy nam się panel boczny Pól tabeli przestawnej, gdzie przeciągamy pole Data do obszaru etykiet Wierszy (zaznaczone strzałką na rys. nr 4).
Rys. nr 4 – przeciągnięte dane do Etykiet Wierszy
Otrzymamy tabelę przestawną z datami przedstawioną na rys. nr 5.
Rys. nr 5 – tabela przestawna z datami
Teraz będziemy chcieli pogrupować sobie te dane po miesiącach, żeby później kiedy przeciągniemy Przychód do pola Podsumowań, móc otrzymać dane pogrupowane i podsumowane według poszczególnych miesięcy (rys. nr 6).
Rys. nr 6 – przeciągnięte dane do pola Suma Wartości
Żeby pogrupować daty musimy kliknąć prawym przyciskiem myszy na dowolną z nich, następnie z podręcznego menu wybrać opcję Grupuj (rys. nr 7).
Rys. nr 7 – opcja Grupuj w podręcznym menu
Otworzy nam się okno Grupowania, gdzie w polu Według wybieramy po czym chcemy pogrupować dane – w naszym przykładzie zaznaczamy Miesiące i Lata. Tak ustawione parametry grupowania zatwierdzamy przyciskiem OK (rys. nr 8).
Rys. nr 8 – okno Grupowania
Otrzymamy pogrupowane dane przedstawione na rys. nr 9.
Rys. nr 9 – pogrupowane dane
Mamy pogrupowane dane według lat i miesięcy. Problem polega na tym, że chcemy aby pokazywały się całe nazwy miesięcy a nie tylko ich skróty. Nigdzie w opcjach tabeli przestawnej nie udało mi się znaleźć takiej opcji. Da się natomiast przy grupowaniach zrobić pewną sztuczkę. Mianowicie tam, gdzie mamy zgrupowane dane da się wpisać inną wartość. Zaznaczamy komórkę z nazwą sty i ręcznie wpisujemy Styczeń. Ważne jest to, że jeśli raz zmienimy nazwę miesiąca to ona zmieni się też dla kolejnych lat zgrupowanych danych. Trochę niestety pracy trzeba wykonać, bo musimy ręcznie zmienić nazwy wszystkich miesięcy w roku, ale otrzymamy zmieniona nazwy dla wszystkich danych, nawet w kolejnych latach (rys. nr 10).
Rys. nr 10 – zmienione nazwy miesięcy w kolejnych latach po ręcznej zmianie nazwy
W tym poście nauczymy się jak przygotować dane do tabeli przestawnej. Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
W przygotowaniu danych do tabeli przestawnej przede wszystkim musimy zadbać o to, aby wszystkie kolumny miały nagłówki. Jak widać na rysunku powyżej kolumna E nie ma takiego nagłówka. Spróbujemy teraz wstawić tabelę przestawną, zaznaczamy cały zakres danych i wybieramy polecenie Tabela przestawna z karty Wstawianie (rys. nr 2).
Rys. nr 2 – polecenie Tabela przestawna
Otworzy nam się okno Tworzenia tabeli przestawnej, w którym wybieramy miejsce jej wstawienia – Nowy arkusz i zatwierdzamy przyciskiem OK (rys. nr 3).
Rys. nr 3 – okno Tworzenia tabeli przestawnej
Excel wyświetli nam komunikat z informacją, że nazwa pola jest nieprawidłowa. Wynika to z faktu, że jedna z kolumn nie ma nagłówka (rys. nr 4).
Rys. nr 4 – komunikat Excela
Musimy zadbać, aby wszystkie kolumny miały wypełniony nagłówek. W nagłówku kolumny E wpisujemy nazwę Ilość. Po tej zmianie nagłówki zostały przedstawione na rys. nr 5.
Rys. nr 5 – prawidłowo wypełnione nagłówki kolumn
Dodatkowo,
jeśli w naszych danych będziemy mieli jakąś pustą kolumnę, to ponownie Excel
nie pozwoli nam wstawić tabeli przestawnej.
Istotne
jest, że możemy wstawiać puste wiersze. Z tym Excel nie robi problemów. Problem
pojawi się jednak przy zaznaczeniu całych danych, ponieważ jeśli użyjemy sobie
standardowego skrótu klawiszowego za zaznaczania całych danych Ctrl+A,
to Excel zaznaczy dane tylko do pierwszego pustego wiersza (rys. nr 6). Jeśli pustych
wierszy było by dużo więcej, ciężko takie dane byłoby nam zaznaczyć.
Rys. nr 6 – zaznaczenie danych tylko do pierwszego pustego wiersza
W takim
przypadku możemy zaznaczyć całość danych (do ostatniej komórki) za pomocą
skrótu klawiszowego Ctrl+Shift+End, ale wtedy Excel zaznaczy dane o
jedną kolumnę więcej, czyli do kolumny H, a kolumna H jest pusta. Możemy przesunąć
takie zaznaczenie za pomocą kolejnego skrótu klawiszowego – Shift+strzałka w
lewo.
Po takim
zaznaczeniu danych (z pustym wierszem), wybieramy polecenie Tabela przestawna z
karty Wstawianie (jak na rys. nr 2). Otworzy nam się okno Tworzenia tabeli
przestawnej, gdzie określamy miejsce wstawienia tabeli – Nowy arkusz.
Zatwierdzamy Tworzenie tabeli przestawnej za pomocą przycisku OK (jak na rys.
nr 3).
Otworzy nam
się nowy arkusz Excela, gdzie możemy zająć się tworzeniem tabeli. Pole
Sprzedawca przeciągamy do obszaru etykiet wierszy (zaznaczone strzałką
na rys. nr 7).
Rys. nr 7 – przeciąganie pola Sprzedawca do obszaru etykiet wierszy
Otrzymamy tabelę przestawną, gdzie jak widać mamy uwzględnione dane z pustego wiersza (rys. nr 8).
Rys. nr 8 – pusty wiersz uwzględniony w tabeli przestawnej
Z tego
względu przy tworzeniu tabeli przestawnej musimy unikać pustych wierszy.
Najlepiej żeby dane do tabeli przestawnej nie zawierały pustych komórek.
Usuwamy pusty wiersz z danych – zaznaczamy cały wiersz, następnie klikamy prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Usuń (rys. nr 9).
Rys. nr 9 – polecenie Usuń
Teraz przechodzimy na arkusz z tabelą przestawną, klikamy w jej obszarze prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 10).
Rys. nr 10 – polecenie Odśwież
Teraz możemy dokończyć naszą tabelę przestawną. Przeciągamy pole Wartość do obszaru Podsumowań wartości (zaznaczone strzałką na rys. nr 11).
Rys. nr 11 – przeciąganie pola Wartość do obszaru podsumowań
Otrzymamy prostą tabele przestawną podsumowującą wyniki sprzedaży poszczególnych sprzedawców przedstawiona na rys. nr 12.
Rys. nr 12 – tabela przestawna
Podsumowując, do stworzenia tabeli przestawnej potrzebujemy spójnych danych, bez pustych komórek, wierszy czy kolumn. Istotne jeszcze jest, że w każdej kolumnie musi być odrębny typ danych, aby wyniki dało się łatwo zinterpretować.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście nauczymy się jak dodać Zestawy w
tabelach przestawnych. Zestawy to nic innego jak zbiory elementów, które
później łatwo możemy dodawać do tabel przestawnych. W naszym przykładzie
stworzymy zestawy zawierające tylko owoce lub tylko warzywa (rys. nr 1).
rys. nr 1 — Stworzone przykładowe zestawy w tabelach przestawnych
Zagadnienie to omówimy sobie na podstawie przykładowych danych
z rysunku nr 2.
rys. nr 2 — Przykładowe dane
Przede wszystkim musimy sobie stworzyć tabelę przestawną,
czyli zaznaczamy pojedynczą komórkę w tabeli (lub cały zakres danych) i
wybieramy polecenie Tabela przestawna z karty Wstawianie (rys. nr 3).
rys. nr 3 — Wstawianie tabeli przestawnej
Otworzy nam się okno Tworzenie tabeli przestawnej,
zaznaczamy, że chcemy stworzyć tabelę przestawną w istniejącym arkuszu (punkt
1) i wskazujemy komórkę (punkt 2), gdzie ma zostać wstawiona. Najważniejsza
sprawa tutaj, żeby mieć dostęp do Zestawów, musimy mieć przynajmniej Excela
2013, żeby w Oknie Tworzenie tabeli przestawnej mieć możliwość zaznaczenia checboxa
Dodaj te dane do modelu danych (punkt nr 3 na rysunku nr 4). Polecenie to jest
związane z dodatkiem do Excela – Power Pivot, skorzystamy tutaj z jego
elementów. Parametry nowej tabeli przestawnej zatwierdzamy przyciskiem OK.
rys. nr 4 — Parametry nowej tabeli
Otworzy nam się okno Pola tabeli przestawnej, gdzie
przeciągamy pole Produkt do obszaru etykiet wierszy i pole Wartość do obszaru
podsumowań wartości. Otrzymamy tabelę przestawną przedstawioną na rysunku nr 5.
rys. nr 5 — Pola tabeli przestawnej
Aby mieć bardziej czytelne dane, sformatujemy sobie kolumnę
Suma wartości, naciskamy prawym przyciskiem myszy na dowolną wartość z tej
kolumny i za pomocą polecenia Format liczby z podręcznego menu, otworzymy okno
Formatowanie komórek. Wybieramy formatowanie walutowe i zatwierdzamy
przyciskiem OK (rys. nr 6).
rys. nr 6 — Formatowanie walutowe
Otrzymamy dane sformatowane wyrażone w złotówkach. W naszych
danych mamy zarówno owoce jak i warzywa, a chcielibyśmy je mieć w osobnych
zestawach. Musimy rozwinąć polecenie Pola, elementy i zestawy(punkt 2 na
rysunku nr 7) z karty Analiza (punkt 1 na rysunku nr 7), a następnie wybrać
polecenie Utwórz zestaw na podstawie elementów z wiersza (punkt 3). Ponieważ
jesteśmy w modelu danych (zaznaczyliśmy ten checkbox przy tworzeniu tabeli
przestawnej) nie będziemy mieć dostępu do poleceń dotyczących pól i elementów.
Będziemy mieć natomiast dostęp do zestawów. Możemy utworzyć zestawy z elementów
w wierszach które potem w tabeli przestawnej trafią do obszaru etykiet wiersza.
Analogicznie zadziała polecenie dla kolumn, jeśli będziemy mieć jakieś elementy
w tabeli przestawnej w obszarze etykiet kolumn.
rys. nr 7 — Polecenie Utwórz zestaw
W poleceniu Utwórz zestaw na podstawie elementów z wiersza,
tworzymy zestaw na podstawie danych, gdzie mamy tylko jedno pole wstawione do
obszaru etykiet wierszy, bo przy większej ilości pól staje się to dużo bardziej
skomplikowane. Otworzy nam się okno Nowy zestaw (rys. nr 8). W pierwszym kroku
musimy nazwać nasz zestaw – w naszym przykładzie zestaw będzie się nazywał Owoce
(punkt nr 1 na rysunku poniżej). Następnie musimy zająć się produktami w tym
zestawie. Przy każdym produkcie jest lista rozwijana ze wszystkimi elementami
dostępnymi dla pola Produkt. Oprócz wymienionych elementów zawierających się w
polu Produkt mamy Wszystko, czyli podsumowanie po wszystkich wartościach. Z
listy usuwamy wszystkie warzywa za pomocą przycisku Usuń wiersz (punkt 2).
rys. nr 8 — Nowy zestaw
Zostawiamy wiersz Wszystko, ponieważ zawiera on cenę
całkowitą. Kiedy zatwierdzimy Nowy zestaw przyciskiem OK otrzymamy tabelę
przestawną zawierającą tylko owoce, ale suma końcowa nam się nie zmieni (będzie
zawierać ceny owoców i warzyw, mimo, że wyświetlać się będą tylko owoce) – rys.
nr 9.
rys. nr 9 — Zestaw Owoce 2 z sumą wszystkich produktów
Zostawiony w zestawie element Wszystko zawsze będzie
pokazywać cenę całkowitą (razem warzyw i owoców), więc usuniemy go sobie, żeby
nie przeszkadzał nam w obliczeniach. Wybieramy polecenie Zarządzaj zestawami z
karty Analiza (rys. nr 10).
rys. nr 10 Zarządzaj zestawami
Otworzy nam się okno Menedżer zestawów, w którym zaznaczamy
interesujący nas zestaw i klikamy przycisk Edytuj (oznaczony strzałką na
rysunku nr 11).
rys. nr 11 — Menedżer zestawów
Otworzy się okno Modyfikuj zestaw, gdzie musimy usnąć wiersz
Wszystko, wprowadzone zmiany zatwierdzamy przyciskiem OK (rys. nr 12)
rys. nr 12 — modyfikuj zestaw
Otrzymamy tabelę przestawną z samymi owocami, co jest ważne
wybraliśmy tutaj zestaw Owoce a nie produkt, więc Excel sam podstawi nam ten
zestaw do obszaru etykiet wierszy (rys. nr 13).
rys. nr 13 — Pola tabeli przestawnej z zestawem Owoce2
Analogicznie możemy stworzyć zestaw dla warzyw. Możemy to
zrobić na tej samej tabeli przestawnej, zaznaczamy dowolną komórkę w tabeli,
wybieramy polecenie Utwórz zestaw na podstawie elementów z wiersza z karty
Analiza, ale wyświetli nam się komunikat, Excel nie pozwoli nam stworzyć
zestawu na innym zestawie. Musimy najpierw usunąć istniejący zestaw z tabeli
przestawnej (rys. nr 14).
rys. nr 14 — Komunikat Excela
Najłatwiej skopiować sobie tabele przestawną i w oknie Pola
tabeli przestawnej odznaczyć Zestaw Owoce2, a następnie przeciągnąć pole
Produkt do obszaru etykiet wierszy (jak przy tworzeniu tabeli przestawnej od
nowa). Teraz na podstawie dowolnej komórki w kolumnie Produkt możemy sobie
stworzyć nowy Zestaw. Kolejno karta Analiza, potem Pola, elementy i zestawy, a
następnie Utwórz zestaw na podstawie elementów wiersza (jak na rysunku nr 7)
W oknie Nowy zestaw usuwamy wszystkie owoce z listy i wiersz
Wszystko analogicznie jak na rysunku nr 8. Zmieniamy nazwę zestawu na Warzywa2
i zatwierdzamy przyciskiem OK. Otrzymamy tabelę przestawną z zestawem Warzywa2
(rys. nr 15).
rys. nr 15 — Tabela przestawna z zestawem Warzywa2
Mamy teraz 2 osobne tabele przestawne, górna z zestawem
Owoce2 i dolna z zestawem Warzywa2.
Załóżmy że chcemy tym razem podsumować dane w tabeli dla
województw. Skopiujemy tabelę przestawną z owocami i wyłączymy zestaw owoce w
oknie Pola tabeli przestawnej. Następnie przeciągniemy pole Województwa do
obszaru etykiet wierszy to otrzymamy tabelę przedstawioną na rysunku nr 16.
rys. nr 16 — Tabela przestawna podsumowująca według województw
Teraz chcemy podsumować dane dla województw po sprzedaży
owoców. Dzięki temu że mamy stworzony zestaw Owoce2, to nie musimy przeciągać
pola Produkt a następnie przefiltrowywać tabeli po tych produktach, które nie
są owocami, wystarczy że przeciągniemy sobie do obszaru etykiet wierszy pole
Owoce2. Wtedy automatycznie powstanie tabela przestawna przedstawiająca
sprzedaż owoców w danych województwach (rys. nr 17).
rys. nr 17 — Tabela przestawna dla województw i zestawu Owoce2
Analogicznie dla podsumowania po województwach pod kątem
sprzedaży warzyw. Odznaczamy zestaw Owoce2 i zaznaczamy Warzywa2, Excel
automatycznie stworzy nam odpowiednią tabelę (rys. nr 18).
rys. nr 18 — Tabela przestawna dla województw i zestawu Warzywa2
Podsumowując, jeśli często działamy na tabelach przestawnych,
możemy oszczędzić dużo czasu. Wystarczy stworzyć zestawy danych, których
później możemy używać do podsumowywania danych. Tak jak wspominałem wcześniej
możemy tworzyć zestawy dla 2 pól z obszaru etykiet wierszy, ale wtedy w oknie
Nowy zestaw mamy bardzo dużo elementów (rys. nr 19)
rys. nr 19 — Wiele możliwości stworzenia nowego zestawu
Na koniec ciekawostka, jeśli zależy nam na innej kolejności
elementów w danych zestawach to w oknie Modyfikuj zestaw możemy zmieniać tą
kolejność za pomocą strzałek oznaczonych na rysunku nr 20.
rys. nr 20 — Zmiana kolejności danych w zestawie
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście pokażemy jak rozwiązać zadanie nr 5.1 z
matury 2018 z informatyki. Treść zadania i dane zostały przedstawione na
rysunku nr 1.
rys. nr 1 — Zadanie z matury z informatyki 2018
Zadanie polega na wyznaczeniu roku, w którym zbiornik
retencyjny został zasilony największą ilością wody. Pierwszy etapem pracy jest
pobranie danych do Excela. Dane te są w pliku tekstowym rozdzielone znakiem
tabulacji, przedstawiają daty oraz ilość wody (m3), jaka zasiliła
zbiornik retencyjny w danym dniu. W Excelu 2010 dane te możemy pobrać za pomocą
polecenia Z tekstu z karty Dane, punkt 2 na rysunku nr 2.
rys. nr 2 — Dane zewnętrzne
W nowszym Excelu np. Excelu 365 te polecenia zniknęły ze
wstążki, ponieważ większość miejsca zajmują polecenia związane z Power Query.
Polecenia te są wyłączone, więc musimy je włączyć w Opcjach w menu Plik.
Otworzy nam się okno Opcje programu Excel, gdzie w zakładce Dane musimy
zaznaczyć checboxy przy poleceniach Pokaż kreatory importu starszych danych i
zatwierdzić przyciskiem OK (rys. nr 3).
rys. nr 3 — pokaż kreatory importu starszych danych
Polecenia te są ukryte pod poleceniem Pobierz dane w zakładce
Dane. Wybieramy polecenie Starsze kreatory a następnie Z pliku tekstowego
(starsza wersja) – punkt 4 na rysunku nr 4.
rys. nr 4 — Starsze kreatory
Następnie musimy znaleźć lokalizacje pliku Woda.txt, który
pobraliśmy, zaznaczyć go i zatwierdzić klikając Importuj (rys. nr 5).
rys. nr 5 — import danych z pliku tekstowego
Pokaże nam się okno Kreator importu tekstu, w którym w trzech
krokach zaimportujemy nasze dane. Przede wszystkim musimy zdecydować czy nasz
test będziemy rozdzielać czy ma mieć stałą szerokość (punkt 1 na rysunku nr 6).
Wybieramy tekst rozdzielany, bo mamy rozdzielenie znakiem tabulacji. Następnie
musimy się upewnić czy jest zaznaczone prawidłowe pochodzenie pliku –
środkowoeuropejski (punkt 2). Nasze dane nie mają nagłówków, więc checbox
oznaczony na rysunku punktem 3 ma pozostać niezaznaczony. Następnie klikamy
przycisk Dalej.
rys. nr 6 — Kreator importu tekstu — krok 1
W drugim kroku musimy zaznaczyć checbox przy odpowiednim
ograniczniku – w naszym zadaniu jest to Tabulator, dzięki temu nasze dane
zostaną rozdzielone na dwie kolumny. Następnie klikamy przycisk Dalej (rys. nr 7).
rys. nr 7 — Kreator importu tekstu — krok 2
W trzecim kroku wybieramy format danych w kolumnach, w
pierwszej kolumnie Data (RMD) pokazane na rysunku nr 8, w drugiej kolumnie
wybieramy formatowanie ogólne bo są to liczby całkowite. Na koniec zatwierdzamy
przyciskiem Zakończ.
rys. nr 8 — Kreator importu tekstu — krok 3
Pojawi nam się okienko Importowanie danych, gdzie musimy
zdecydować gdzie nasze dane mają zostać wstawione – wybieramy komórkę A1,
następnie zatwierdzamy klikając przycisk OK (rys. nr 9).
rys. nr 9 — Okno importowanie danych
Nasze dane zostały pobrane z pliku tekstowego do Excela co
widać na rysunku nr 10.
rys. nr 10 — Pobrane dane
Wstawione dane są połączone z plikiem z którego zostały
zaimportowane. Możemy to sprawdzić za pomocą polecenie Zapytania i połączenia w
karcie Dane (punkt nr 2 na rysunku nr 11). Otworzy nam się okienko z
zapytaniami i połączeniami.
rys. nr 11 — Zapytania i połączenia
Naszym danym brakuje nagłówków, możemy je dodać za pomocą
skrótów klawiszowych: pierwszy Shift+Space w celu zaznaczenia wiersza z aktywną
komórką, a następnie Ctrl+Shift+Plus w celu dodania wiersza powyżej. Wpisujemy
nagłówki w wolne komórki.
Wygodniej jest zamienić nasze dane na tabelę w rozumieniu
Excela. Za pomocą skrótu klawiszowego Ctrl+T otwieramy okno Tworzenie tabeli,
gdzie Excel domyślnie zaznacza dane, ale bez nagłówków. Dzieje się tak dlatego
że Excel zaznacza dokładnie te dane, które zostały zaimportowane. My chcemy aby
nagłówki zostały uwzględnione więc ręcznie zamieniamy w zakresie danych numer
komórki z $A$2 na $A$1 (rys. nr 12).
rys. nr 12 — Tworzenie tabeli
Po zatwierdzeniu przyciskiem OK wyświetli nam się komunikat,
że jeśli zamienimy te dane na tabelę to stracą one połączenie z danymi
zewnętrznymi (z plikiem, z którego je zaimportowaliśmy). Potrzebowaliśmy te
dane pobrać raz, więc możemy zatwierdzić ten komunikat (rys. nr 13).
rys. nr 13 — Komunikat Excela
Innym sposobem na zaimportowanie danych jest po prostu
skopiowanie ich, czyli zaznaczamy wszystkie dane w pliku tekstowym za pomocą
skrótu klawiszowego Ctrl+A, a następnie kopiujemy za pomocą Ctrl+C. Następnie wklejamy
w Excelu Ctrl+V i te dane wkleją nam się w osobne komórki. Dane te nie są
połączone z plikiem wyjściowym, inaczej niż w poprzednim przypadku.
Najlepiej importować dane za pomocą Power Query. Microsoft
się z tym zgadza, dlatego właśnie ukrył te starsze polecenia importu
zewnętrznych danych.
Kiedy
mamy już zaimportowane dane, możemy sprawdzić, w którym roku do zbiornika
retencyjnego wpłynęło najwięcej wody. Zrobimy to za pomocą tabel przestawnych. Wybieramy
dowolną komórkę w naszej tabeli, a następnie wybieramy polecenie Tabela
przestawna z karty Wstawianie (rys. nr 14).
rys. nr 14 — Tabela przestawna
Otworzy nam się okno Tworzenie tabeli przestawnej. Zakres
domyślnie zawiera całą tabelę, wybieramy wstawienie w istniejącym arkuszu
(komórka D1) i zatwierdzamy przyciskiem OK. W oknie tabeli przestawnej
przeciągamy Datę do obszaru etykiet wierszy, a następnie klikamy na dowolną
datę prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Grupuj.
W oknie Grupowanie zaznaczamy grupowanie po latach, bo Excel sam domyślnie nie
zgrupuje danych. Zatwierdzamy przyciskiem OK (rys. nr 15).
rys. nr 15 — Grupowanie wg lat
Otrzymamy daty zgrupowane latami (rys. nr 16)
rys. nr 16 — Pogrupowane dane
Kiedy mamy tak pogrupowane dane, wystarczy przeciągnąć pole Woda
do obszaru sumy wartości i ona automatycznie nam się zsumuje dla każdego roku
(rys. nr 17).
rys. nr 17 — Pola tabeli przestawnej
Możemy kliknąć prawym przyciskiem myszy na któryś z tych
podsumowań, aby zmienić Format liczby (rys. nr 18).
rys. nr 18 — Format liczby
W oknie Formatowanie komórek zmieniamy formatowanie na
liczbowe, zero miejsc po przecinku i zaznaczamy checkbox Użyj separatora, żeby
liczby były bardziej czytelne (rys. nr 19).
rys. nr 19 — Formatowanie liczbowe
Aby znaleźć największą wartość możemy posortować nasze dane
od Z do A za pomocą polecenia Sortuj na karcie Dane. Wtedy największy wynik
będzie na początku tabeli (rys. nr 20).
rys. nr 20 — Posortowane dane
Innym sposobem jest założenie filtra. Rozwijamy sobie filtr dla
kolumny Data, wybieramy Filtry wartości, a następnie Pierwsze 10 – punkt 3 na
rysunku nr 21.
rys. nr 21 — Filtry wartości
Otworzy nam się okno Filtr 10 pierwszych wartości, gdzie
wpisujemy w miejscu oznaczonym zieloną strzałką, że interesuje nas tylko jedna
wartość i zatwierdzamy OK (rys. nr 22).
rys. nr 22 — Okno Filtra 10 pierwszych wartości
Otrzymujemy jeden interesujący nas wynik (rys. nr 23).
rys. nr 23 — Największy wynik
Nasza tabela przestawna zawiera sumę końcową, która nie jest
nam tu potrzebna, więc możemy ją wyłączyć za pomocą polecenia Wyłącz dla
wierszy i kolumn (punkt nr 3 na rysunku nr 24) z Sum końcowych na karcie
Projektowanie.
rys. nr 24 — Sumy końcowe
Ostatecznie otrzymujemy jedną datę (rok) i sumę m3
wody, jakie wpłynęły w danym roku do zbiornika retencyjnego (rys. nr 25).
rys. nr 25 — Odpowiedź na zadanie z matury
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście omówimy filtrowanie tabeli przestawnej
po podsumowanych wartościach. Zagadnienie to omówimy na podstawie przykładowych
danych sprzedażowych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Dla podanych danych stworzyliśmy tabelę przestawną (rys. nr
2). W naszym przykładzie chodziło o to by stworzyć tabelę przestawną tylko z tymi miastami, których podsumowanie po polu
(kolumnie) Zysk/strata daje wartość ujemną.
rys. nr 2 — Pola tabeli przestawnej
Rozpatrzyliśmy konkretny przykład, ale możemy to rozwiązanie
przenosić na wiele innych filtrów. Aby stworzyć tabelę przestawną, zaznaczamy
pojedyncza komórkę w danych, następnie wybieramy polecenie Tabela przestawna z
karty Wstawianie (rys. nr 3).
rys. nr 3 — Wstawianie tabeli przestawnej
Otworzy nam się okno Tworzenie tabeli przestawnej. Zaznaczamy tabelę – zakres (punkt nr 1 na rysunku nr 4) i wybieramy gdzie chcemy stworzyć tabelę przestawną – w naszym przykładzie w nowym arkuszu (punkt 2 na rysunku poniżej). Zatwierdzamy klikając OK.
rys. nr 4 — Tworzenie tabeli przestawnej
Przeciągamy Miasto do obszaru etykiet wierszy, natomiast Zysk/
Strata do obszaru podsumowań wartości (rys. nr 5).
rys. nr 5 — Obszary etykiet
Następnie klikamy prawym przyciskiem myszy na pierwszą
wartość i z podręcznego menu wybieramy polecenie Format liczby (rys. nr 6).
rys. nr 6 — Format liczby
Otworzy nam się okno Formatowania. Wybieramy Kategorię
formatowania Walutowe (punkt nr 1 na rysunku poniżej) oraz typ z wartościami
ujemnymi (punkt 2) i ustawiamy brak miejsc po przecinku – patrz punkt 3 na
rysunku nr 7. Zatwierdzamy klikając przycisk OK.
rys. nr 7 — Okno formatowania
W wyniku naszych działań mamy wartości ujemne i dodatnie w
zależności od miast, czyli widzimy w których miastach mieliśmy zysk, a w
których straty (rys. nr 8).
rys. nr 8 — Tabela przestawna
Nas interesują tylko te miasta, które przyniosły straty,
czyli te z wartością ujemną po podsumowaniu w kolumnie Zysk/Strata. Tabela
przestawna nie daje standardowo takich możliwości filtrowania, abyśmy uzyskali
tylko miasta z wartościami ujemnymi, ale możemy przefiltrować tabelę po
miastach. Jeżeli nie chcemy aby w naszej tabeli pojawiły się przykładowo miasta
takie jak Białystok, Bielsko Biała i Częstochowa to wystarczy, że odznaczymy
checboxy obok ich nazw i zatwierdzimy klikając OK (rys. nr 9).
rys. nr 9 — Filtrowanie po miastach
Te miasta nie będą już wyświetlane w tabeli przestawnej,
zostały przefiltrowane na jej poziomie. Nam chodzi o filtr, który zwróci nam
miasta z ujemną wartością. Musimy wyczyścić poprzedni filtr za pomocą polecenie
Wyczyść filtr z „Miasto” (rys. nr 10).
rys. nr 10 — Wyczyść filtr
Sposobu na filtrowanie po wartościach w kolumnie Suma z
Zysk/Strata nauczyłem się od Billa Jelena. Zaznaczamy komórkę obok nagłówka
kolumny Suma z Zysk/Strata, następnie wciskamy skrót klawiszowy Ctrl+Shift+L,
czyli nakładamy filtr z karty Dane (rys. nr 11).
rys. nr 11 — Polecenie Filtruj
Teraz możemy filtrować po kolumnie Suma z Zysk/Strata.
Wybieramy Filtry liczb a następnie Mniejsze lub równe (rys. nr 12).
rys. nr 12 — Filtry liczb
Otworzy nam się okno Autofiltr niestandardowy. Wpisujemy obok
pola jest mniejsze niż lub równe wartość 0, zatwierdzamy klikając przycisk OK
(rys. nr 13).
rys. nr 13 — Autofiltr niestandardowy
Otrzymamy przefiltrowane dane przedstawione na rysunku nr 14.
Co warto zauważyć to nasz filtr nałożyliśmy na cały arkusz a nie na samą tabelę
przestawną, a możemy to rozpoznać przez to, że pokazują nam się kolorowe numery
wierszy, a niektórych wierszy w ogóle brakuje.
rys. nr 14 — Filtr nałożony na arkusz
Możemy sobie nałożyć dodatkowe dane, tzn. do obszaru etykiet
kolumn przeciągnąć Datę. Domyślnie Excel automatycznie nie grupuje po datach,
więc klikamy prawym przyciskiem myszki na jakąś datę, a następnie z podręcznego
menu wybieramy polecenie Grupuj. W oknie Grupowanie, zaznaczamy grupowanie po
miesiącach, zatwierdzamy klikając OK (rys. nr 15).
rys. nr 15 — Grupowanie
Otrzymamy tabelę przestawną przedstawioną na rysunku nr 16.
rys. nr 16 — Tabela przestawna pogrupowana po miesiącach
Musimy wyłączyć poprzedni filtr za pomocą skrótu klawiszowego
Ctrl+Shift+L, ponieważ wychodzi on w środku nowej tabeli. Ponownie ustawiamy
aktywną komórkę obok nagłówka miesiąca tabeli przestawnej i włączamy filtr po
całości za pomocą skrótu klawiszowego Ctrl+Shift+L. Teraz możemy filtrować dane
w tabeli przestawnej po dowolnej kolumnie. Rozpatrzymy filtrowanie dla
Stycznia. Klikamy trójkącik przy styczniu (na rysunku oznaczony zieloną
strzałką), a następnie wybieramy polecenie Filtry liczb i Mniejsze lub równe
(rys. nr 17).
rys. nr 17 — Filtry liczb
Dalej analogicznie jak dla poprzedniego przypadku (patrz rys.
13), w oknie Autofiltr niestandardowy wpisujemy Mniejsze lub równe 0.
Zatwierdzamy OK. Otrzymamy przefiltrowane dane ale tylko w miesiącu styczeń
(rys. 18).
rys. nr 18 — Tabela przestawna z filtrem
Podsumowując nakładanie takich filtrów w tabeli przestawnej
nie jest możliwe. My nakładamy filtr na cały arkusz, dzięki temu możemy
nakładać ten filtr arkusza na tabelę przestawną. Na tabelę przestawną możemy
tylko nałożyć filtry po miastach lub po datach, czyli te filtry działają zanim
te dane zostaną wyświetlone w arkuszu. Możemy rozpoznać, że filtry zostały
nałożone na arkusz a nie bezpośrednio na tabelę przestawną po tym, że Excel
zaczyna kolorować numery wierszy oraz części z nich brakuje.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
