Konwertowanie komórek tabeli przestawnej na formuły arkusza

Tabela przestawna ma kilka układów udostępniających wstępnie zdefiniowaną strukturę raportu, ale nie można ich dostosowywać. Jeśli potrzebujesz większej elastyczności podczas projektowania układu raportu w formie tabeli przestawnej, możesz przekonwertować komórki na formuły arkusza, a następnie zmienić ich układ, wykorzystując w pełni wszystkie funkcje dostępne w arkuszu. Można przekonwertować komórki na formuły korzystające z funkcji modułów lub funkcji WEŹDANETABELI. Konwersja komórek na formuły znacznie upraszcza proces tworzenia, aktualizowania i obsługi tych dostosowanych tabel przestawnych.

Po przekonwertowaniu komórek na formuły te uzyskują dostęp do tych samych danych, co tabela przestawna, i można je odświeżać, aby wyświetlać aktualne wyniki. Jednak może z wyjątkiem filtrów raportów nie masz już dostępu do interakcyjnych funkcji tabeli przestawnej, takich jak filtrowanie, sortowanie czy rozwijanie i zwijanie poziomów.

Informacje na temat typowych scenariuszy konwertowania tabel przestawnych na formuły arkuszy

Poniżej przedstawiono typowe przykłady czynności, jakie można wykonać po przekonwertowaniu komórek tabeli przestawnej na formuły arkusza w celu dostosowania układu przekonwertowanych komórek.

Zmienianie rozmieszczenia komórek i usuwanie ich 

Załóżmy, że masz raport okresowy, który musisz tworzyć co miesiąc dla swojego personelu. Potrzebny jest tylko podzbiór informacji z raportu, przy czym preferowany jest dostosowany układ danych. Wystarczy przenieść i rozmieścić komórki w odpowiednim układzie projektu, usunąć komórki, które nie są potrzebne w miesięcznym raporcie personelu, a następnie sformatować komórki i arkusz zgodnie z preferencjami.

Wstawianie wierszy i kolumn 

Załóżmy, że chcesz wyświetlić informacje dotyczące sprzedaży za ostatnie dwa lata w podziale według regionów i grup produktów oraz że chcesz wstawić rozszerzony komentarz w dodatkowych wierszach. Wystarczy wstawić wiersz i wprowadzić tekst. Ponadto chcesz dodać kolumnę pokazującą sprzedaż według regionów i grup produktów, których nie ma w oryginalnej tabeli przestawnej. Wystarczy wstawić kolumnę, dodać formułę w celu uzyskania odpowiednich wyników, a następnie wypełnić 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 tak, aby wskazywał testową bazę danych w celu porównania tych dwóch wyników.

Używanie odwołań do komórek w celu zróżnicowania danych wprowadzanych przez użytkownika 

Załóżmy, że chcesz, aby cały raport zmieniał się na podstawie danych wprowadzanych przez użytkowników. Można zmienić argumenty formuł modułowych na odwołania do komórek w arkuszu, a następnie wprowadzić inne wartości w tych komórkach w celu uzyskania odmiennych wyników.

Tworzenie niejednolitego układu wierszy lub kolumn (nazywane również raportowaniem asymetrycznym) 

Załóżmy, że chcesz utworzyć raport, który zawiera kolumnę z roku 2008 o nazwie Sprzedaż rzeczywista, kolumnę z roku 2009 o nazwie Sprzedaż planowana, ale nie chcesz używać żadnych innych kolumn. Można utworzyć raport zawierający tylko te kolumny, w przeciwieństwie do tabeli przestawnej, która wymaga raportowania symetrycznego.

Tworzenie własnych formuł modułowych i wyrażeń MDX 

Załóżmy, że chcesz utworzyć raport, aby przedstawić sprzedaż określonego produktu dokonaną przez trzech określonych sprzedawców w lipcu. Użytkownicy posiadający wiedzę na temat wyrażeń MDX i zapytań OLAP mogą samodzielnie wprowadzać formuły modułu. Te formuły mogą być dość złożone, jednak korzystając z funkcji Autouzupełnianie formuł, można uprościć ich tworzenie i zwiększyć dokładność. Aby uzyskać więcej informacji, zobacz Korzystanie z funkcji Autouzupełnianie formuł.

Konwertowanie komórek na formuły używające funkcji modułów

1. Aby zapisać tabelę przestawną do użytku w przyszłości, zalecamy utworzenie kopii skoroszytu przed przekonwertowaniem tabeli przestawnej przez kliknięcie polecenia Zapisz>plik jako. Aby uzyskać więcej informacji, zobacz Zapisywanie pliku.

2. Przygotuj tabelę przestawną w celu zminimalizowania ponownego rozmieszczenia komórek po konwersji, wykonując następujące czynności:

   • Należy zmienić układ na układ najbardziej zbliżony do żądanego.
   • Interakcje z raportem, takie jak filtrowanie, sortowanie i ponowne projektowanie raportu, w celu uzyskania żądanych wyników.

3. Kliknij tabelę przestawną.

4. Na karcie Opcje w grupie Narzędzia kliknij przycisk Narzędzia OLAP, a następnie kliknij polecenie Konwertuj na formuły.
   Jeśli nie ma żadnych filtrów raportów, operacja konwersji zostaje zakończona. Jeśli istnieje co najmniej jeden filtr raportu, zostanie wyświetlone okno dialogowe Konwertowanie na formuły .

5. Zdecyduj, jak chcesz przekonwertować tabelę przestawną:
   Konwertowanie całej tabeli przestawnej 

   • Zaznacz pole wyboru Konwertuj filtry raportów .
     Spowoduje to przekonwertowanie wszystkich komórek na formuły arkusza i usunięcie całej tabeli przestawnej.
     Konwertowanie tylko etykiet wierszy, etykiet kolumn i obszaru wartości tabeli przestawnej z zachowaniem filtrów raportów 

   • Upewnij się, że pole wyboru Konwertuj filtry raportów nie jest zaznaczone. Jest to ustawienie domyślne.
     Spowoduje to przekonwertowanie wszystkich etykiet wierszy, etykiet kolumn i komórek obszaru wartości na formuły arkusza i zachowanie oryginalnej tabeli przestawnej, ale tylko z filtrami raportów, dzięki czemu będzie można kontynuować filtrowanie przy użyciu filtrów raportów.

     Uwaga

     Jeśli tabela przestawna jest w formacie 2000–2003 lub starszym, można przekonwertować tylko całą tabelę przestawną.

6. Kliknij przycisk Konwertuj.
   Operacja konwersji polega najpierw na odświeżeniu tabeli przestawnej w celu upewnienia się, że są używane aktualne dane.
   Podczas operacji konwersji wyświetlany jest komunikat na pasku stanu. Jeśli operacja trwa długo, a chcesz przekonwertować ją innym razem, naciśnij klawisz ESC, aby ją anulować.

   Uwaga

   • Nie można konwertować komórek, w których zastosowano filtr, do poziomów, które są ukryte.
   • Nie można konwertować komórek, w których zastosowano obliczenia niestandardowe utworzone za pomocą karty Pokaż wartości jako w oknie dialogowym Ustawienia pola wartości . (Na karcie Opcje w grupie Pole aktywne kliknij pozycję Pole aktywne, a następnie kliknij pozycję Ustawienia pola wartości).
   • W przypadku komórek konwertowanych formatowanie komórek jest zachowywane, ale style tabel przestawnych są usuwane, ponieważ można je stosować tylko do tabel przestawnych.

Konwertowanie komórek przy użyciu funkcji WEŹDANETABELI

Za pomocą funkcji WEŹDANETABELI w formule można przekonwertować komórki tabeli przestawnej na formuły arkusza, gdy chce się pracować ze źródłami danych innymi niż OLAP, zrezygnować z natychmiastowego uaktualnienia do nowego formatu tabeli przestawnej w wersji 2007 lub uniknąć złożoności wynikającej z używania funkcji modułu.

1. Upewnij się, że polecenie Generuj funkcję WEŹDANETABELI w grupie Tabela przestawna na karcie Opcje jest włączone.

   Uwaga

   Polecenie Generuj WEŹDANETABELI ustawia lub czyści pole wyboru Użyj funkcji WEŹTABELA_przestawna w odwołaniach do tabeli przestawnej 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órej chcesz użyć w każdej formule.

3. W komórce arkusza poza tabelą przestawną wpisz odpowiednią formułę do momentu, w którym chcesz uwzględnić dane z raportu.

4. Kliknij komórkę w tabeli przestawnej, której chcesz użyć w formule tabeli przestawnej. Do formuły dodawana jest funkcja arkusza WEŹDANETABELI, która pobiera dane z tabeli przestawnej. Ta funkcja będzie nadal pobierać właściwe dane, nawet jeśli układ raportu ulegnie zmianie lub dane zostaną odświeżone.

5. Zakończ wpisywanie formuły i naciśnij klawisz ENTER.

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