Applies ToExcel dla Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

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.

Twórca nieformatowego układu wiersza lub kolumny (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.

Twórca własnych formuł modułowych 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.

  1. 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.

  2. 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.

  3. Kliknij tabelę przestawną.

  4. 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 .

  5. 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ą.

  6. 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.

  1. 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 .

  2. Upewnij się, że w tabeli przestawnej jest widoczna komórka, która ma być używana w każdej formule.

  3. W komórce arkusza poza tabelą przestawną wpisz formułę, do której chcesz dołączyć dane z raportu.

  4. 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.

  5. 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!.

Problem: nie można konwertować komórek tabeli przestawnej na formuły arkusza

Potrzebujesz dalszej pomocy?

Chcesz uzyskać więcej opcji?

Poznaj korzyści z subskrypcji, przeglądaj kursy szkoleniowe, dowiedz się, jak zabezpieczyć urządzenie i nie tylko.

Społeczności pomagają zadawać i odpowiadać na pytania, przekazywać opinie i słuchać ekspertów z bogatą wiedzą.