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 stworzyć ranking sprzedawców. W przykładowych danych na rys. nr 1 mamy zestawienie sprzedawców i ich sprzedaż. Z tych danych musimy zrobić podsumowanie sprzedaży poszczególnych sprzedawców, a następnie zrobić ranking, czyli wyciągnąć sprzedawców z największą sprzedażą.
Rys. nr 1 – przykładowe dane
Najpierw
zadanie to wykonamy za pomocą formuł i funkcjonalności Excela, a następnie to
samo zadanie wykonamy z wykorzystaniem tabeli przestawnej.
Pierwszym
krokiem będzie wyciągnięcie unikatowych wartości z kolumny Sprzedawca. Możemy
ręcznie skopiować wszystkich sprzedawców z kolumny B za pomocą skrótu
klawiszowego Ctrl+C, a następnie wkleić do kolumny G za pomocą skrótu Ctrl+V. Zaznaczamy
kolumnę G (Sprzedawca), a następnie wybieramy ikonkę polecenia Usuń
duplikaty z karty Dane (rys. nr 2).
Rys. nr 2 – polecenie Usuń duplikaty
Excel wyświetli nam komunikat Ostrzeżenie o usuwaniu duplikatów, w którym zaznaczamy checkbox przy opcji Pozostawić obecnie zaznaczony obszar, następnie zatwierdzamy komunikat przyciskiem Usuń duplikaty (rys. nr 3).
Rys. nr 3 – Ostrzeżenie o usuwaniu duplikatów
Wyświetli się okno Usuwania duplikatów, które zatwierdzamy przyciskiem OK (rys. nr 4).
Rys. nr 4 – okno Usuwania duplikatów
Wyświetli się kolejny komunikat, tym razem z informacją o liczbie usuniętych duplikatów i unikatowych wartości przedstawiony na rys. nr 5, który zatwierdzamy przyciskiem OK.
Rys. nr 5 – komunikat Excela
Otrzymamy kolumnę z sześcioma unikatowymi wartościami przedstawioną na rys. nr 6.
Rys. nr 6 – kolumna z unikatowymi wartościami (sprzedawcami)
Powyższe dane możemy posortować od A do Z za pomocą ikonki polecenia na karcie Dane (rys. nr 7).
Rys. nr 7 – polecenie sortowania danych od A do Z
Teraz musimy
podsumować sprzedaż poszczególnych sprzedawców. Użyjemy do tego funkcji SUMA.JEŻELI.
Pierwszym argumentem funkcji jest zakres, czyli zakres po którym
będziemy sprawdzać nasze kryterium (aby zaznaczyć całą kolumnę możemy użyć skrótu
klawiszowego Ctrl+Shift+Strzałka w dół. Zaznaczony zakres (B2:B325)
blokujemy bezwzględnie za pomocą klawisza F4. Drugi argument funkcji to kryteria,
czyli nazwa aktualnego sprzedawcy, którego nie blokujemy, ponieważ ma się
przesuwać w dół razem z kopiowanie formuły na wiersze poniżej. Trzeci argument
funkcji to suma_zakres, czyli kolumna, po której chcemy podsumować nasze
dane (E2:E325), zablokowana bezwzględnie. Zapis całej formuły powinien wyglądać
następująco:
=SUMA.JEŻELI($B$2:$B$325;G2;$E$2:$E$325)
Powyższą formułę
zatwierdzamy i kopiujemy w dół. Otrzymamy podsumowanie sprzedaży przedstawione
na rys. nr 8.
Rys. nr 8 – podsumowanie sprzedaży unikatowych sprzedawców
Otrzymaliśmy
sprzedaż sumaryczną wszystkich sprzedawców. Teraz możemy wykorzystać funkcję POZYCJA.NAJW
(pozycja największa), żeby określić pozycję danego wyniku w rankingu. Pierwszym
argumentem funkcji jest liczba, czyli konkretna wartość sprzedaży (H2).
Drugi argument funkcji to odwołanie, czyli zakres, z którego chcemy
zrobić ranking (H2:H7), zablokowany bezwzględnie za pomocą klawisza F4. Zapis
całej formuły powinien wyglądać następująco:
=POZYCJA.NAJW(H2;$H$2:$H$7)
Powyższą
formułę zatwierdzamy i przeciągamy na wiersze poniżej. Otrzymamy ranking pozycję
poszczególnych sprzedawców w rankingu sprzedaży przedstawiony na rys. nr 9.
Rys. nr 9 – ranking sprzedawców według wysokości sprzedaży
Jak widać na
rysunku powyżej mamy przyznane miejsca w rankingu, Cezary jest na pierwszym
miejscu a Paulina na ostatnim – szóstym. Aby ranking dobrze się prezentował to
musimy mieć posortowanych sprzedawców według miejsc, które kolejno zajmują.
Wykorzystamy do tego funkcje INDEKS, która będzie "patrzyła"
na kolumnę G (Sprzedawca), czyli w pierwszym argumencie podajemy zakres
zablokowany bezwzględnie — $G$2:$G$7. Drugi argument to nr_wiersza, czyli
pozycja poszczególnych sprzedawców. Aby uzyskać te pozysje musimy użyć funkcji PODAJ.POZYCJĘ.
Pierwszym argumentem funkcji jest szukana_wartość, czyli numer pozycji,
której szukamy – pozycja 1 (komórka K2). Drugi argument to przeszukiwana_tab,
czyli kolumna z rankingiem zablokowana bezwzględnie ($I$2:$I$7). Trzeci
argument to typ_porównania, wpisujemy wartość 0 – dopasowanie dokładne.
Zapis całej formuły powinien wyglądać następująco:
=INDEKS($G$2:$G$7;PODAJ.POZYCJĘ(K2;$I$2:$I$7;0))
Funkcja PODAJ.POZYCJĘ
zwraca nam numer wiersza z danym numerem w indeksie, a funkcja INDEKS zwraca
nam wartość z tego wiersza. Po zatwierdzeniu powyższej formuły i skopiowaniu
jej w dół, otrzymamy ranking przedstawiony na rys. nr 10.
Rys. nr 10 – ranking sprzedawców
Teraz pokażemy rozwiązanie wykorzystujące tabelę przestawną. Zaznaczamy pojedynczą komórkę w tablicy danych, na podstawie których chcemy wykonać tabelę przestawną. Wybieramy polecenie Tabela przestawna z karty Wstawianie (rys. nr 11).
Rys. nr 11 – polecenie Tabela przestawna
Otworzy nam się okno Tworzenia tabeli przestawnej, w którym wybieramy, gdzie chcemy umieścić raport w postaci tabeli przestawnej (Istniejący arkusz), wskazujemy konkretną komórkę (np. G10). Tak ustawione parametry zatwierdzamy przyciskiem OK.
Rys. nr 12 – okno Tworzenia tabeli przestawnej
Przeciągamy Sprzedawcę do obszaru Etykiet wierszy (punkt 1 na rys. nr 13), następnie przeciągamy Sprzedaż do obszaru Podsumowań wartości (punkt 2).
Rys. nr 13 – tworzenie tabeli przestawnej
Otrzymamy tabelę przedstawioną na rys. nr 14.
Rys. nr 14 – tabela przestawna
Mamy podsumowanie sprzedaży. W kolumnie Suma z Sprzedaż powinniśmy zmienić formatowanie, w tym celu klikamy prawym przyciskiem myszy na dowolną komórkę z tej kolumny, następnie z podręcznego menu wybieramy polecenie Format liczby (rys. nr 15).
Rys. nr 15 – polecenie Format liczby
Otworzy nam się okno Formatowania komórek, gdzie w polu Kategoria wybieramy Walutowe, następnie zatwierdzamy zmianę przyciskiem OK (rys. nr 16).
Rys. nr 16 – zmiana formatowania na walutowe
Otrzymamy sformatowane dane przedstawione na rys. nr 17.
Rys. nr 17 – sformatowane dane
W tabeli przestawnej możemy sobie posortować dane od największej do najmniejszej za pomocą ikony sortowania od Z do A na karcie Dane. Posortowane dane zostały przedstawione na rys. nr 18.
Rys. nr 18 – posortowane dane
Otrzymaliśmy niejako ranking, ale nas mniej interesuje wartość sprzedaży, a bardziej numer pozycji danego sprzedawcy. Pole Sprzedaż jeszcze raz przeciągniemy do obszaru podsumowań wartości jak na rys. nr 13 (punkt 2). W nowej kolumnie klikamy prawym przyciskiem myszy i z podręcznego menu rozwijamy polecenie Pokaż wartości jako, a następnie wybieramy polecenie Porządkuj wartości od największych do najmniejszych (rys. nr 19).
Rys. nr 19 – polecenie Porządkuj wartości od największych do najmniejszych
Otworzy nam się okno Pokazywania wartości jako, gdzie na liście rozwijanej w polu podstawowym wybieramy Sprzedawca. Tak ustawiony parametr zatwierdzamy przyciskiem OK (rys. nr 20).
Rys. nr 20 – okno Pokazywania wartości jako
Otrzymamy ranking sprzedawców w odpowiedniej kolejności, ponieważ wcześniej posortowaliśmy dane. Ponadto możemy zmienić nazwę kolumny Suma z Sprzedaż2 na Ranking (rys. nr 21).
Rys. nr 21 – ranking sprzedawców
Możemy pozbyć się wartości Suma końcowa, ponieważ jest nam zbędna. W tym celu rozwijamy polecenie Sumy końcowe (punkt 2 na rys. nr 22) z karty Projektowanie (punkt 1), a następnie wybieramy polecenie Wyłącz dla wierszy i kolumn (punkt 2).
Rys. nr 22 – ścieżka dostępu do polecenia Wyłącz dla wierszy i kolumn
Otrzymamy tabelę przestawną z rankingiem sprzedawców bez sumy końcowej przedstawioną na rys. nr 23.
Rys. nr 23 – ranking sprzedawców bez sumy końcowej
Podsumowując stworzenie rankingu możemy wykonać na dwa sposoby, za pomocą funkcjonalności klasycznego Excela i tabeli przestawnej. Jak widać powyżej szybszym sposobem jest wykorzystanie tabeli przestawnej.
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 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 załadować ponad milion
wierszy do tabeli przestawnej, czyli ponad 1048576 wierszy, które mieszczą się
w arkuszu Excela. Będziemy używać do tego zadania dodatku do Excela – Power Query.
Dla ułatwienia dane pobierzemy sobie z pliku tekstowego,
czyli wybieramy polecenie Z pliku tekstowego /CSV z karty Dane (rys. nr 1).
Pobieranie danych z pliku tekstowego/ CSV
Otworzy nam się okno, w którym musimy znaleźć lokalizację
pliku (danych bazowych), jaki chcemy zaimportować do Power Query. Wybieramy
plik a następnie nasz wybór zatwierdzamy przyciskiem Importuj (rys. nr 2).
rys. nr 2 — Wybór źródła danych
Otworzy nam się okno z danymi Sprzedaż zagranicznak.csv z
danymi, które będziemy chcieli edytować w Power Query, więc klikamy przycisk
Edytuj (rys. nr 3).
rys. nr 3 — Edycja pliku CSV
Otworzy nam się Edytor zapytań Power Query, w którym chcemy
sprawdzić czy wszystkie dane zostały załadowane poprawnie. Kolumna Kod pocztowy
jest błędna, ponieważ Power Query źle rozpoznaje dane (np. dla kodu z wartością
0 z przodu Power Query pomija tą wartość 0 i otrzymujemy kod 4- cyfrowy).
Wystarczy zmienić formatowanie tej kolumny. Klikamy na ikonkę 123 przy nazwie
kolumny Kod pocztowy, a następnie z podręcznego menu wybrać formatowanie Tekst
(rys. nr 4).
rys. nr 4 — Zmiana formatu danych
Pojawi się komunikat, w którym musimy zatwierdzić zmianę typu
kolumny za pomocą przycisku Zamień bieżącą (rys. nr 5).
rys. nr 5 — Zmień typ kolumny
W kolejnym etapie wybieramy polecenie Zamknij i załaduj do z
karty Narzędzia główne (rys. nr 6).
rys. nr 6 — Zamknij i załaduj do
Otworzy nam się okno Importowania danych. Jeśli posiadasz
subskrypcję Excela 365 w tym okienku znajduje się bezpośrednia opcja, aby
załadować dane jako Raport w formie tabeli przestawnej (rys. nr 7).
rys. nr 7 — Parametry importowania danych
Jeśli posiadasz starszą wersję pakietu Office to wybieramy
polecenie Utwórz tylko połączenie, następnie nasz wybór zatwierdzamy
przyciskiem OK (rys. nr 8).
rys. nr 8 — Utwórz tylko połączenie
W Excelu w bocznym panelu stworzyło się zapytanie Power Query,
tylko w formie połączenia (rys. nr 9).
rys. nr 9 — Zapytanie Power Query w panelu bocznym
Na tym etapie musimy ręcznie wstawić sobie tabelę przestawną.
Klikamy polecenie Tabela przestawna z karty Wstawianie (rys. nr 10).
rys. nr 10 — Tabela przestawna
Otworzy nam się okno Tworzenia tabeli przestawnej. W polu
wybierz dane, które chcesz analizować, zaznaczamy opcję Użyj zewnętrznego
źródła danych, następnie klikamy przycisk Wybierz połączenie (rys. nr 11).
rys. nr 11 — Okno tworzenia tabeli przestawnej
Otworzy nam się okno z Istniejącymi połączeniami. Zaznaczamy
połączenie w tym arkuszu Zapytanie – Sprzedaż Zagranicznak, a następnie klikamy
przycisk Otwórz (rys. nr 12).
rys. nr 12 — Istniejące połączenia
Następnie w oknie Tworzenie tabeli przestawnej z rysunku nr 11,
wybieramy miejsce wstawienia naszej tabeli (Istniejący arkusz i konkretna
komórka $A$1) i tak ustawione parametry zatwierdzamy przyciskiem OK.
Mamy dużą ilość danych, więc musimy trochę poczekać, ponadto
Excel robi sobie kopię zapytania, co dodatkowo wydłuża proces przetwarzania
danych.
Otrzymamy w Excelu pustą tabelę, przestawną którą stworzymy
przeciągając odpowiednie dane do pól etykiet wierszy i kolumn (rys. nr 13).
rys. nr 13 — Pusta tabela przestawna
Przeciągamy pola Miasto i Kraj do obszaru etykiet wierszy, a
następnie przeciągamy pole Przychód do obszaru Sumy wartości (rys. nr 14).
rys. nr 14 — Przeciąganie danych do obszaru etykiet
Otrzymamy tabelę przestawną przedstawioną na rysunku nr 15.
rys. nr 15 — Tabla przestawna
Możemy sobie podejrzeć ilość załadowanych wierszy w panelu
Zapytania i połączenia. W tym arkuszu zostało stworzonych 2 501 580
wierszy (rys. nr 16).
rys. nr 16 — ilość wierszy
Klikamy skrót klawiszowy Ctrl+S, aby zapisać nasze dane.
Skopiowane zapytanie Sprzedaż zagraniczna (2) zostało stworzone tylko pod tą
tabelę przestawną. Połączenie źródłowe możemy dalej wykorzystywać do innych
zadań.
Ciekawostką jest, że plik w Excelu dotyczący tego odcinka
zajmuje niecałe 30 Mb, w porównaniu do 130 Mb danych w pliku źródłowym csv.
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.
