Tabela przestawna zawiera kilka układów, które zapewniają wstępnie zdefiniowaną strukturę raportu, ale nie można dostosować tych układów. Jeśli potrzebujesz większej elastyczności w projektowaniu układu raportu w formie tabeli przestawnej, możesz przekonwertować komórki na formuły arkusza, a następnie zmienić układ tych komórek, w pełni korzystając ze wszystkich funkcji dostępnych w arkuszu. Możesz przekonwertować komórki na formuły używające funkcji modułu lub użyć funkcji WEŹDANETABELI. Konwertowanie komórek na formuły znacznie upraszcza proces tworzenia, aktualizowania i utrzymywania tych dostosowanych tabel przestawnych.
Podczas konwertowania komórek na formuły te formuły uzyskują dostęp do tych samych danych co tabela przestawna i można je odświeżać w celu wyświetlenia aktualnych wyników. Jednak z wyjątkiem filtrów raportów nie masz już dostępu do interakcyjnych funkcji tabeli przestawnej, takich jak filtrowanie, sortowanie lub rozwijanie i zwijanie poziomów.
Uwaga: Po przekonwertowaniu tabeli przestawnej OLAP można nadal odświeżać dane w celu uzyskania aktualnych wartości miar, ale nie można aktualizować rzeczywistych elementów członkowskich wyświetlanych w raporcie.
Informacje o typowych scenariuszach konwertowania tabel przestawnych na formuły arkusza
Poniżej przedstawiono typowe przykłady tego, co można zrobić po przekonwertowania komórek tabeli przestawnej na formuły arkusza w celu dostosowania układu przekonwertowanych komórek.
Zmienianie rozmieszczenia i usuwanie komórek
Załóżmy, że masz okresowy raport, który musisz utworzyć każdego miesiąca dla personelu. Potrzebujesz tylko podzbioru informacji raportu i wolisz dostosować układ danych. Możesz po prostu przenosić i rozmieszczać komórki w odpowiednim układzie projektu, usuwać komórki, które nie są potrzebne w miesięcznym raporcie dla personelu, a następnie sformatować komórki i arkusz zgodnie z twoimi preferencjami.
Wstawianie wierszy i kolumn
Załóżmy, że chcesz, aby informacje o sprzedaży z poprzednich dwóch lat były wyświetlane w podziale na regiony i grupy produktów oraz że chcesz wstawić komentarz rozszerzony w dodatkowych wierszach. Wystarczy wstawić wiersz i wprowadzić tekst. Ponadto chcesz dodać kolumnę pokazującą sprzedaż według regionu i grupy produktów, która nie znajduje się w oryginalnej tabeli przestawnej. Wstaw kolumnę, dodaj formułę, aby uzyskać odpowiednie wyniki, a następnie wypełnij kolumnę w dół, aby uzyskać wyniki dla każdego wiersza.
Korzystanie z wielu źródeł danych
Załóżmy, że chcesz porównać wyniki między produkcyjną i testową bazą danych, aby upewnić się, że testowa baza danych daje oczekiwane wyniki. Możesz łatwo skopiować formuły komórek, a następnie zmienić argument połączenia w celu wskazywania testowej bazy danych w celu porównania tych dwóch wyników.
Używanie odwołań do komórek w celu zmiany danych wejściowych użytkownika
Załóżmy, że chcesz, aby cały raport zmieniał się na podstawie danych wejściowych użytkownika. Można zmienić argumenty formuł modułu na odwołania do komórek w arkuszu, a następnie wprowadzić różne wartości w tych komórkach, aby uzyskać różne wyniki.
Tworzenie nieformularzowego układu wierszy lub kolumn (nazywanego również raportowaniem asymetrycznym)
Załóżmy, że musisz utworzyć raport zawierający kolumnę z 2008 r. o nazwie Sprzedaż rzeczywista, kolumnę z 2009 r. o nazwie Sprzedaż projektowana, ale nie chcesz mieć żadnych innych kolumn. W przeciwieństwie do tabeli przestawnej, który wymaga symetrycznego raportowania, można utworzyć raport zawierający tylko te kolumny.
Tworzenie własnych formuł modułów i wyrażeń MDX
Załóżmy, że chcesz utworzyć raport przedstawiający sprzedaż określonego produktu przez trzech konkretnych sprzedawców w lipcu. Jeśli masz wiedzę na temat wyrażeń MDX i zapytań OLAP, możesz samodzielnie wprowadzić formuły modułu. Chociaż te formuły mogą stać się dość skomplikowane, można uprościć tworzenie i zwiększyć dokładność tych formuł przy użyciu funkcji Autouzupełnianie formuł. Aby uzyskać więcej informacji, zobacz Korzystanie z funkcji Autouzupełnianie formuł.
Uwaga: Korzystając z tej procedury, można przekonwertować tylko tabelę przestawną OLAP.
-
Aby zapisać tabelę przestawną do użycia w przyszłości, zalecamy utworzenie kopii skoroszytu przed przekonwertowaniem tabeli przestawnej przez kliknięcie pozycji Plik > Zapisz jako. Aby uzyskać więcej informacji, zobacz Zapisywanie pliku.
-
Przygotuj tabelę przestawną, aby zminimalizować rozmieszczenie komórek po konwersji, wykonując następujące czynności:
-
Zmień układ na układ najbardziej podobny do odpowiedniego układu.
-
Interakcja z raportem, na przykład filtrowanie, sortowanie i przeprojektowanie raportu, w celu uzyskania odpowiednich wyników.
-
-
Kliknij tabelę przestawną.
-
Na karcie Opcje w grupie Narzędzia kliknij pozycję Narzędzia OLAP, a następnie kliknij pozycję Konwertuj na formuły.
Jeśli nie ma filtrów raportu, operacja konwersji zostanie ukończona. Jeśli istnieje co najmniej jeden filtr raportu, zostanie wyświetlone okno dialogowe Konwertuj na formuły .
-
Zdecyduj, jak chcesz przekonwertować tabelę przestawną:
Konwertowanie całej tabeli przestawnej
-
Zaznacz pole wyboru Konwertuj filtry raportu .
Spowoduje to przekonwertowanie wszystkich komórek na formuły arkusza i usunięcie całej tabeli przestawnej.
Konwertuj tylko etykiety wierszy tabeli przestawnej, etykiety kolumn i obszar wartości, zachowując filtry raportów
-
Upewnij się, że pole wyboru Konwertuj filtry raportu jest wyczyszczone. (Jest to ustawienie domyślne).
Spowoduje to przekonwertowanie wszystkich etykiet wierszy, etykiet kolumn i komórek obszaru wartości na formuły arkusza oraz zachowanie oryginalnej tabeli przestawnej, ale tylko filtrów raportu, dzięki czemu można nadal filtrować przy użyciu filtrów raportu.
Uwaga: Jeśli format tabeli przestawnej to wersja 2000–2003 lub wcześniejsza, możesz przekonwertować tylko całą tabelę przestawną.
-
-
Kliknij przycisk Konwertuj.
Operacja konwersji najpierw odświeża tabelę przestawną, aby upewnić się, że są używane aktualne dane.
Podczas operacji konwersji na pasku stanu jest wyświetlany komunikat. Jeśli operacja trwa długo i wolisz przekonwertować ją w innym czasie, naciśnij klawisz ESC, aby anulować operację.
Uwagi:
-
Nie można konwertować komórek z filtrami zastosowanymi do ukrytych poziomów.
-
Nie można przekonwertować komórek, w których pola mają obliczenia niestandardowe utworzone za pomocą karty Pokaż wartości jako w oknie dialogowym Ustawienia pola wartości . (Na karcie Opcje w grupie Aktywne pole kliknij pozycję Aktywne pole, a następnie kliknij pozycję Ustawienia pola wartości).
-
W przypadku komórek, które są konwertowane, formatowanie komórek jest zachowywane, ale style tabeli przestawnej są usuwane, ponieważ te style mogą być stosowane tylko do tabel przestawnych.
-
Za pomocą funkcji WEŹDANETABELI w formule można konwertować komórki tabeli przestawnej na formuły arkusza, gdy chcesz pracować ze źródłami danych innych niż OLAP, jeśli nie chcesz od razu przeprowadzić uaktualnienia do nowego formatu tabeli przestawnej w wersji 2007 lub aby uniknąć złożoności korzystania z funkcji modułu.
-
Upewnij się, że polecenie Wygeneruj WEŹDANETABELI w grupie Tabela przestawna na karcie Opcje jest włączone.
Uwaga: Polecenie Wygeneruj dane GETPIVOTDATA ustawia lub czyści opcję Użyj funkcji GETPIVOTTABLE dla odwołań do tabel przestawnych w kategorii Formuły w sekcji Praca z formułami w oknie dialogowym Opcje programu Excel .
-
Upewnij się, że w tabeli przestawnej jest widoczna komórka, która ma być używana w każdej formule.
-
W komórce arkusza poza tabelą przestawną wpisz formułę, do której chcesz dołączyć dane z raportu.
-
Kliknij komórkę w tabeli przestawnej, która ma zostać użyta w formule w tabeli przestawnej. Do formuły zostanie dodana funkcja arkusza WEŹDANETABELI, która pobiera dane z tabeli przestawnej. Ta funkcja nadal pobiera poprawne dane, jeśli układ raportu się zmieni lub dane zostaną odświeżone.
-
Zakończ wpisywanie formuły i naciśnij klawisz ENTER.
Uwaga: Jeśli usuniesz z raportu dowolną komórkę, do których odwołuje się formuła WEŹDANETABELI, formuła zwróci #REF!.
