Microsoft Excel: tabele przestawne
Tabele podsumowań programu Excel umożliwiają użytkownikom w jednym miejscu zgrupowanie znacznej ilości informacji zawartych w kłopotliwych tabelach oraz kompilowanie złożonych raportów. W takim przypadku wartości tabel podsumowań są aktualizowane automatycznie po zmianie wartości dowolnej powiązanej tabeli. Zobaczmy, jak utworzyć tabelę przestawną w programie Microsoft Excel.
Spis treści
Utwórz tabelę przestawną w zwykły sposób
Chociaż rozważymy proces tworzenia tabeli przestawnej na przykładzie Microsoft Excel 2010, ale ten algorytm można zastosować do innych nowoczesnych wersji tej aplikacji.
Na podstawie podamy tabelę wypłat wynagrodzeń pracownikom przedsiębiorstwa. Wskazuje nazwiska pracowników, płeć, kategorię, datę płatności i kwotę płatności. Oznacza to, że każdy odcinek płatności dla pojedynczego pracownika odpowiada osobnej linii tabeli. Pogrupujemy chaotycznie zlokalizowane dane w tej tabeli w jedną tabelę podsumowującą. W takim przypadku dane będą pobierane tylko za trzeci kwartał 2016 r. Zobaczmy, jak to zrobić na konkretnym przykładzie.
Najpierw konwertujemy oryginalną tabelę na dynamiczną tabelę. Jest to konieczne, aby po dodaniu wierszy i innych danych były one automatycznie pobierane do tabeli przestawnej. Aby to zrobić, stajemy się kursorem na dowolną komórkę stołu. Następnie w bloku "Style" na wstążce kliknij przycisk "Formatuj jako tabelę". Wybierz dowolny styl, który Ci się podoba.
Następnie otwiera się okno dialogowe, które monituje nas o podanie współrzędnych położenia tabeli. Jednak domyślnie współrzędne, które oferuje program, pokrywają więc całą tabelę. Więc możemy się tylko zgodzić i kliknąć przycisk "OK". Ale użytkownicy powinni wiedzieć, że jeśli chcą, mogą zmienić parametry pokrycia obszaru tabeli.
Następnie tabela zmienia się w dynamiczną i automatyczną zmianę. Otrzymuje również nazwę, która, jeśli jest to pożądane, użytkownik może zmienić to, co jest dla niego wygodne. Możesz wyświetlić lub zmienić nazwę tabeli na karcie "Projektant".
Aby rozpocząć tworzenie tabeli przestawnej bezpośrednio, przejdź do zakładki "Wstaw". Kliknij pierwszy przycisk na wstążce, który nazywa się "Tabelą przestawną". Następnie otwiera się menu, w którym należy wybrać, co chcemy stworzyć, tabelę lub wykres. Kliknij przycisk "Tabela podsumowująca".
Otworzy się okno, w którym ponownie musimy wybrać zakres lub nazwę tabeli. Jak widać, program zaostrzył już nazwę naszego stołu, więc nie ma już nic więcej do roboty. W dolnej części okna dialogowego możesz wybrać miejsce utworzenia tabeli podsumowania: na nowym arkuszu (domyślnie) lub na tym samym arkuszu. Oczywiście w większości przypadków wygodniej jest korzystać z tabeli zbiorczej na oddzielnym arkuszu. Jest to jednak indywidualna sprawa dla każdego użytkownika, która zależy od jego preferencji i zadań. Po prostu klikamy przycisk "OK".
Następnie formularz tworzenia tabeli przestawnej otwiera się na nowym arkuszu.
Jak widać, w prawej części okna znajduje się lista pól tabel i cztery obszary:
- Nazwy linii;
- Nazwy kolumn;
- Wartości;
- Filtruj raport.
Po prostu przeciągamy myszą pola tabeli, których potrzebujemy w obszarach odpowiadających naszym potrzebom. Nie istnieje żadna jasna reguła, które pola powinny zostać przeniesione, ponieważ wszystko zależy od tabeli źródłowej i od konkretnych zadań, które mogą ulec zmianie.
W tym konkretnym przypadku przenieśliśmy pola "Płeć" i "Data" do pola "Filtr raportu", pole "Kategoria personelu" w polu "Nazwy kolumn", pole "Nazwa" w polu "Nazwy linii", pole "Kwota" płace "do obszaru" Wartości ". Należy zauważyć, że wszystkie obliczenia arytmetyczne danych pochodzących z innej tabeli są możliwe tylko w ostatnim obszarze. Jak widać, podczas wykonywania tych manipulacji z przeniesieniem pól w obszarze, sama tabela w lewej części okna również uległa zmianie.
To jest tabela podsumowująca. Nad tabelą wyświetlane są filtry według płci i daty.
Konfigurowanie tabeli przestawnej
Ale, jak pamiętamy, w tabeli powinny pozostać dane tylko za trzeci kwartał. Tymczasem wyświetlane są dane dla całego okresu. Aby doprowadzić tabelę do pożądanego kształtu, kliknij przycisk obok filtru "Data". W wyświetlonym oknie zaznacz pole naprzeciwko napisu "Wybierz kilka elementów". Następnie usuń zaznaczenie ze wszystkich dat, które nie pasują do trzeciego kwartału. W naszym przypadku jest to tylko jedna data. Kliknij przycisk "OK".
W ten sam sposób możemy użyć filtra na podłodze i wybrać dla raportu, na przykład, tylko niektórych mężczyzn.
Następnie tabela podsumowująca uzyskała ten formularz.
Aby pokazać, że możesz zarządzać danymi w tabeli, jak chcesz, ponownie otwieramy formularz listy pól. Aby to zrobić, przejdź do zakładki "Parametry" i kliknij przycisk "Lista pól". Następnie przenieś pole "Data" z obszaru "Filtr raportu" do obszaru "Nazwa linii" oraz między polami "Kategoria personelu" i "Płeć", wymieniając obszary. Wszystkie operacje są wykonywane po prostu przeciągając elementy.
Teraz stół ma zupełnie inny wygląd. Kolumny są podzielone według płci, linie zostały podzielone według miesięcy, a tabela może być teraz filtrowana według kategorii personelu.
Jeśli przeniesiesz nazwę wiersza na listę pól i ustawisz datę wyższą niż nazwa, to daty wypłat będą podzielone na nazwiska pracowników.
Można również wyświetlić wartości liczbowe tabeli jako histogram. Aby to zrobić, wybierz komórkę z wartością liczbową w tabeli, przejdź do zakładki "Strona główna", kliknij przycisk "Formatowanie warunkowe", przejdź do pozycji "Histogram" i wybierz rodzaj histogramu, który Ci odpowiada.
Jak widać, histogram pojawia się tylko w jednej komórce. Aby zastosować regułę histogramu do wszystkich komórek w tabeli, klikamy przycisk, który pojawił się obok histogramu, i w otwartym oknie przełączamy przełącznik na pozycję "Do wszystkich komórek".
Teraz nasza tabela podsumowań uzyskała reprezentacyjny wygląd.
Utwórz tabelę przestawną za pomocą Kreatora tabeli przestawnej
Możesz utworzyć tabelę przestawną, korzystając z Kreatora tabeli przestawnej. Ale do tego wystarczy, że przyniesiesz to narzędzie do paska narzędzi szybkiego dostępu. Przejdź do elementu menu "Plik" i kliknij przycisk "Opcje".
W otwartym oknie parametrów przejdź do "Pasek narzędzi szybkiego dostępu". Wybierz polecenia z poleceń na taśmie. Na liście elementów szukaj "Kreatora tabeli przestawnej i kreatora wykresu kołowego". Wybierz go, kliknij przycisk "Dodaj", a następnie kliknij przycisk "OK" w prawym dolnym rogu okna.
Jak widać, po naszych działaniach na pasku narzędzi szybkiego dostępu pojawiła się nowa ikona. Klikamy na niego.
Następnie otworzy się Kreator tabeli przestawnej. Jak widać, mamy cztery warianty źródła danych, z których zostanie utworzona tabela podsumowująca:
- na liście lub w bazie danych Microsoft Excel;
- w zewnętrznym źródle danych (inny plik);
- w kilku zakresach konsolidacji;
- w innej tabeli podsumowania lub na wykresie podsumowującym.
Poniżej wybierz, co będziemy tworzyć, tabelę podsumowującą lub wykres. Dokonaj wyboru i kliknij przycisk "Dalej".
Następnie pojawi się okno z zakresem tabeli z danymi, które możesz zmienić, jeśli chcesz, ale nie musimy tego robić. Po prostu kliknij przycisk "Dalej".
Następnie Kreator tabeli przestawnej umożliwia wybranie miejsca, w którym nowy stół zostanie umieszczony na tym samym arkuszu lub na nowym. Dokonaj wyboru i kliknij przycisk "Gotowe".
Następnie otwiera się nowy arkusz o dokładnie tym samym kształcie, co zwykły sposób tworzenia tabeli przestawnej. Dlatego nie ma sensu zatrzymywać się na nim osobno.
Wszystkie dalsze działania są wykonywane zgodnie z tym samym algorytmem, jak opisano powyżej.
Jak widać, można utworzyć tabelę przestawną w programie Microsoft Excel na dwa sposoby: w zwykły sposób za pomocą przycisku na taśmie i przy użyciu Kreatora tabeli przestawnej. Druga metoda zapewnia więcej dodatkowych opcji, ale w większości przypadków funkcjonalność pierwszej opcji jest wystarczająca do wykonania zadań. Tabele podsumowań mogą generować dane w raportach dotyczących prawie wszystkich kryteriów określonych przez użytkownika w ustawieniach.