Lekcja 7. Tabele przestawne
Excel w praktyce
Jednym z najwydajniejszych i najporęczniejszych narzędzi Excela są tabele przestawne służące do zestawiania danych. Tabele przestawne można przetwarzać za pomocą języka VBA lub modyfikować przy użyciu myszki. Dane przechowywane w tabeli przestawnej pochodzą z arkuszowej lub zewnętrznej bazy danych i są umieszczane w specjalnej pamięci podręcznej umożliwiającej wykonywanie obliczeń natychmiast po zmodyfikowaniu zawartości tabeli przestawnej.
Tworzenie tabeli przestawnej
Tabele przestawne są jednym z najcenniejszych narzędzi oferowanych przez Excel, niestety rzadko wykorzystywanym, gdyż wiele osób uważa, że ich używanie jest bardzo trudne. Podstawy używania tabel przestawnych omówimy zatem na kilku prostych przykładach.
Przykład 1.
Dysponujemy raportem w formacie tabeli o trzech kolumnach zawierających: nazwę produktu, kategorię, do której produkt ten należy i jego sprzedaż z ostatniego roku. Tabela zawiera dane dla 500 produktów.
Zostaliśmy poproszeni o obliczenie, jaka jest całkowita sprzedaż dla każdej z 10 kategorii produktów, jaka była średnia sprzedaż na jeden produkt w każdej z kategorii, ile różnych produktów było sprzedawanych w każdej z kategorii i jaka była sprzedaż najlepszego z tych produktów.
Aby utworzyć tabelę przestawną należy ustawić aktywną komórkę na dowolnej komórce tabeli z danymi i wybrać z karty wstążki „Wstawianie” ikonę „Wstaw Tabelę Przestawną” zaznaczoną na poniższym rysunku.
Wyświetlone zostanie poniższe okno. Excel próbuje sam domyślić się, jaki obszar zajmuje nasza tabela z danymi, z reguły jest to sugestia właściwa. Domyślnie ustawiona jest opcja wstawiania tabeli przestawnej w nowym arkuszu – wszystkim początkującym sugeruję przy niej pozostać. Klikamy OK.
Po kliknięciu OK na wstążce pojawiają się „Narzędzia tabel przestawnych” z dwoma nowymi kartami poleceń, „Opcje” i „Projektowanie” (oznaczone 1) Na karcie „Opcje” trzy ostatnie ikony (oznaczone 2) informują nas o tym, jakie elementy tabeli przestawnej mają być wyświetlane. 3 – oznaczona została nowo utworzona tabela przestawna, w tej chwili nie zawiera żadnych kolumn ani wierszy. Numerem 4 oznaczyłem „Listę pól tabeli przestawnej”, która posłuży do utworzenia naszej tabeli. I wreszcie cyfrą 5 oznaczone jest polecenie, które wyświetli okno pozwalające zmieniać opcje tabeli. Od kliknięcia tego polecenia zaczniemy tworzenie naszej tabeli przestawnej.
Istnieje kilka sposobów tworzenia i modyfikowania tabel przestawnych – przeciąganie pól w siatce jest moim zdaniem najbardziej intuicyjnym i najszybszym sposobem. Aby było możliwe w oknie ”Opcje tabeli przestawnej” na karcie „Wyświetlanie” należy zaznaczyć „Układ klasyczny tabeli przestawnej”, opcja ta domyślnie jest wyłączona.
Operację tę musimy przeprowadzić tylko przy pierwszym tworzeniu tabeli przestawnej. Klikamy OK.
Wygląd tabeli zmienił się – powinna teraz wyglądać tak jak jest to pokazane na poniższym rysunku.
Przenosimy pole tabeli przestawnej „kategoria” do obszaru oznaczonego jako „Upuść pola wierszy tutaj”.
Trzykrotnie przenosimy pole „sprzedaż” do obszaru „Upuść elementy danych tutaj”.
Przeciągamy pole „nazwa produktu” do elementów danych (opisy pól są widoczne tylko wówczas, gdy pola te są puste).
Tabela, którą uzyskamy powinna wyglądać tak jak poniżej. Już w tym momencie znamy odpowiedzi na 2 z 4 postawionych na początku tego przykładu pytań. W kolumnie B mamy sumę sprzedaży dla wszystkich kategorii, a w kolumnie E liczbę produktów po kategoriach.
Sformatujemy teraz kolumny w tabeli. Klikamy dowolne pole pierwszej z kolumn z danymi prawym klawiszem i wybieramy opcję „Ustawienia pola wartości...”
Klikamy przycisk „Format Liczby”
Formatujemy liczby tak, jak jest to pokazane poniżej i klikamy OK.
Uzyskamy tabelę, która będzie wyglądać jak poniżej.
Wciąż mamy jednak 3 sumy sprzedaży (w kolumnach B, C i D). Klikamy dowolną komórkę w kolumnie „Suma z sprzedaż 2” prawym klawiszem i wybieramy pole „Ustawienia pola wartości…”. Tym razem, prócz zmiany formatu liczby zmieniamy także opcję „Podsumuj pole wartości według:” na „Średnia”.
A w przypadku kolumny z nagłówkiem „Suma z sprzedaz3” na „Maksimum”, (także odpowiednio formatujemy liczby).
Uzyskujemy tabelę przestawną zawierającą wszystkie poszukiwane przez nas dane.
Ponieważ nagłówek „Suma z sprzedaż” nie brzmi zbyt dobrze, proponuję zmienić ten opis na „Sprzedaż”.
Stajemy w komórce z nagłówkiem i wpisujemy tam „Sprzedaż”, wciskamy „Enter”.
Pojawi się powyższy komunikat, jeśli mimo wszystko chcielibyśmy taką właśnie nazwę nadać możemy dodać spację na końcu słowa Sprzedaż lub dokonać innej drobnej zmiany. Zmieniamy wszystkie nagłówki kolumn uzyskując tabelę taką jak na poniższym rysunku.
Po nabraniu wprawy wszystkie opisane wyżej operacje nie powinny zająć dłużej niż minutę. Gdybyśmy chcieli zrobić to samo bez wykorzystania tabel przestawnych zajęłoby to co najmniej kilkanaście razy więcej.
Proponuję dokonać teraz drobnej zmiany w danych wejściowych (Arkusz: „Tabele przestawne 1”). Zmieńmy sprzedaż dla pierwszego produktu na 2345.
Po powrocie do naszej tabeli odkrywamy, że nic się nie zmieniło, maksimum w kategorii A nadal wynosi 995.
Dane w tabelach przestawnych nie są automatycznie aktualizowane.
Aby tabela przestawna została zaktualizowana, należy z karty „Opcje” wybrać polecenie „Odśwież”.
A nowe wartości zostaną obliczone.
Gdybyśmy chcieli zmienić kolejność pól, np. kolumnę Ilość pokazywać za Sprzedażą, wystarczy złapać za „bok” nagłówka kolumny i przeciągnąć go we właściwe miejsce. To, gdzie pojawi się przeciągana kolumna, pokazuje się na bieżąco podczas przeciągania i jest oznaczone grubą linią.
Na koniec tego przykładu sformatujemy jeszcze tabelę przestawną używając jednego z dostępnych „Stylów tabeli przestawnej”. Po wybraniu stylu po raz pierwszy, nowy styl będzie pokazywany na naszej tabeli automatycznie podczas przemieszczania kursora myszy nad ikonami stylów.
Na skutek ostatniej operacji uzyskaliśmy ostateczny wygląd naszej tabeli w tym przykładzie.
Zadanie
Wprowadź następujące dane do arkusza kalkulacyjnego:
Na podstawie tych danych, spróbuj wykonać podsumowanie w postaci tabeli przestawnej, aby:
Była widoczna ilość sprzedanych towarów każdej kategorii,
Była widoczna ilość sprzedanych towarów każdej kategorii przez każdego sprzedawcę,
Była widoczna łączna ilość sprzedanych towarów przez każdego sprzedawcę, jak i łączna ilość sprzedanych towarów.
Następnie wyświetl tylko informację dla sprzedawcy czwartego.
Osoby zainteresowane sprawdzeniem
poprawności wykonania ćwiczenia
prosimy o wysłanie maila na adres:
szkolenia@cts.com.pl
CTS – Centrum Technik Sieciowych
cts@cts.com.pl
www.cts.com.pl
tel. 22 838-19-08
22 838-52-70
Tabele przestawne są jednym z najcenniejszych narzędzi oferowanych przez Excel, niestety rzadko wykorzystywanym, gdyż wiele osób uważa, że ich używanie jest bardzo trudne. Podstawy używania tabel przestawnych omówimy zatem na kilku prostych przykładach.
Przykład 1.
Dysponujemy raportem w formacie tabeli o trzech kolumnach zawierających: nazwę produktu, kategorię, do której produkt ten należy i jego sprzedaż z ostatniego roku. Tabela zawiera dane dla 500 produktów.
Zostaliśmy poproszeni o obliczenie, jaka jest całkowita sprzedaż dla każdej z 10 kategorii produktów, jaka była średnia sprzedaż na jeden produkt w każdej z kategorii, ile różnych produktów było sprzedawanych w każdej z kategorii i jaka była sprzedaż najlepszego z tych produktów.
Aby utworzyć tabelę przestawną należy ustawić aktywną komórkę na dowolnej komórce tabeli z danymi i wybrać z karty wstążki „Wstawianie” ikonę „Wstaw Tabelę Przestawną” zaznaczoną na poniższym rysunku.
Wyświetlone zostanie poniższe okno. Excel próbuje sam domyślić się, jaki obszar zajmuje nasza tabela z danymi, z reguły jest to sugestia właściwa. Domyślnie ustawiona jest opcja wstawiania tabeli przestawnej w nowym arkuszu – wszystkim początkującym sugeruję przy niej pozostać. Klikamy OK.
Po kliknięciu OK na wstążce pojawiają się „Narzędzia tabel przestawnych” z dwoma nowymi kartami poleceń, „Opcje” i „Projektowanie” (oznaczone 1) Na karcie „Opcje” trzy ostatnie ikony (oznaczone 2) informują nas o tym, jakie elementy tabeli przestawnej mają być wyświetlane. 3 – oznaczona została nowo utworzona tabela przestawna, w tej chwili nie zawiera żadnych kolumn ani wierszy. Numerem 4 oznaczyłem „Listę pól tabeli przestawnej”, która posłuży do utworzenia naszej tabeli. I wreszcie cyfrą 5 oznaczone jest polecenie, które wyświetli okno pozwalające zmieniać opcje tabeli. Od kliknięcia tego polecenia zaczniemy tworzenie naszej tabeli przestawnej.
Istnieje kilka sposobów tworzenia i modyfikowania tabel przestawnych – przeciąganie pól w siatce jest moim zdaniem najbardziej intuicyjnym i najszybszym sposobem. Aby było możliwe w oknie ”Opcje tabeli przestawnej” na karcie „Wyświetlanie” należy zaznaczyć „Układ klasyczny tabeli przestawnej”, opcja ta domyślnie jest wyłączona.
Operację tę musimy przeprowadzić tylko przy pierwszym tworzeniu tabeli przestawnej. Klikamy OK.
Wygląd tabeli zmienił się – powinna teraz wyglądać tak jak jest to pokazane na poniższym rysunku.
Przenosimy pole tabeli przestawnej „kategoria” do obszaru oznaczonego jako „Upuść pola wierszy tutaj”.
Trzykrotnie przenosimy pole „sprzedaż” do obszaru „Upuść elementy danych tutaj”.
Przeciągamy pole „nazwa produktu” do elementów danych (opisy pól są widoczne tylko wówczas, gdy pola te są puste).
Tabela, którą uzyskamy powinna wyglądać tak jak poniżej. Już w tym momencie znamy odpowiedzi na 2 z 4 postawionych na początku tego przykładu pytań. W kolumnie B mamy sumę sprzedaży dla wszystkich kategorii, a w kolumnie E liczbę produktów po kategoriach.
Sformatujemy teraz kolumny w tabeli. Klikamy dowolne pole pierwszej z kolumn z danymi prawym klawiszem i wybieramy opcję „Ustawienia pola wartości...”
Klikamy przycisk „Format Liczby”
Formatujemy liczby tak, jak jest to pokazane poniżej i klikamy OK.
Uzyskamy tabelę, która będzie wyglądać jak poniżej.
Wciąż mamy jednak 3 sumy sprzedaży (w kolumnach B, C i D). Klikamy dowolną komórkę w kolumnie „Suma z sprzedaż 2” prawym klawiszem i wybieramy pole „Ustawienia pola wartości…”. Tym razem, prócz zmiany formatu liczby zmieniamy także opcję „Podsumuj pole wartości według:” na „Średnia”.
A w przypadku kolumny z nagłówkiem „Suma z sprzedaz3” na „Maksimum”, (także odpowiednio formatujemy liczby).
Uzyskujemy tabelę przestawną zawierającą wszystkie poszukiwane przez nas dane.
Ponieważ nagłówek „Suma z sprzedaż” nie brzmi zbyt dobrze, proponuję zmienić ten opis na „Sprzedaż”.
Stajemy w komórce z nagłówkiem i wpisujemy tam „Sprzedaż”, wciskamy „Enter”.
Pojawi się powyższy komunikat, jeśli mimo wszystko chcielibyśmy taką właśnie nazwę nadać możemy dodać spację na końcu słowa Sprzedaż lub dokonać innej drobnej zmiany. Zmieniamy wszystkie nagłówki kolumn uzyskując tabelę taką jak na poniższym rysunku.
Po nabraniu wprawy wszystkie opisane wyżej operacje nie powinny zająć dłużej niż minutę. Gdybyśmy chcieli zrobić to samo bez wykorzystania tabel przestawnych zajęłoby to co najmniej kilkanaście razy więcej.
Proponuję dokonać teraz drobnej zmiany w danych wejściowych (Arkusz: „Tabele przestawne 1”). Zmieńmy sprzedaż dla pierwszego produktu na 2345.
Po powrocie do naszej tabeli odkrywamy, że nic się nie zmieniło, maksimum w kategorii A nadal wynosi 995.
Dane w tabelach przestawnych nie są automatycznie aktualizowane.
Aby tabela przestawna została zaktualizowana, należy z karty „Opcje” wybrać polecenie „Odśwież”.
A nowe wartości zostaną obliczone.
Gdybyśmy chcieli zmienić kolejność pól, np. kolumnę Ilość pokazywać za Sprzedażą, wystarczy złapać za „bok” nagłówka kolumny i przeciągnąć go we właściwe miejsce. To, gdzie pojawi się przeciągana kolumna, pokazuje się na bieżąco podczas przeciągania i jest oznaczone grubą linią.
Na koniec tego przykładu sformatujemy jeszcze tabelę przestawną używając jednego z dostępnych „Stylów tabeli przestawnej”. Po wybraniu stylu po raz pierwszy, nowy styl będzie pokazywany na naszej tabeli automatycznie podczas przemieszczania kursora myszy nad ikonami stylów.
Na skutek ostatniej operacji uzyskaliśmy ostateczny wygląd naszej tabeli w tym przykładzie.
Zadanie
Wprowadź następujące dane do arkusza kalkulacyjnego:
Na podstawie tych danych, spróbuj wykonać podsumowanie w postaci tabeli przestawnej, aby:
Była widoczna ilość sprzedanych towarów każdej kategorii,
Była widoczna ilość sprzedanych towarów każdej kategorii przez każdego sprzedawcę,
Była widoczna łączna ilość sprzedanych towarów przez każdego sprzedawcę, jak i łączna ilość sprzedanych towarów.
Następnie wyświetl tylko informację dla sprzedawcy czwartego.
Osoby zainteresowane sprawdzeniem
poprawności wykonania ćwiczenia
prosimy o wysłanie maila na adres:
szkolenia@cts.com.pl
CTS – Centrum Technik Sieciowych
cts@cts.com.pl
www.cts.com.pl
tel. 22 838-19-08
22 838-52-70
|
nr 7(123)2012  |
|
