Jak zlokalizować i zmienić dane źródłowe tabeli przestawnej programu Excel. W przypadku brakujących danych źródłowych wykonaj kroki, aby spróbować je odtworzyć. Zmień opcje, aby zapisać dane źródłowe w pliku tabeli przestawnej.

UWAGA: W przypadku makr, które pomagają w zarządzaniu danymi źródłowymi, przejdź na stronę makr danych źródłowych tabeli przestawnej. Aby uzyskać makro do cofnięcia przestawienia danych źródłowych, przejdź do strony Unpivot Excel Data Macro

Zlokalizuj i napraw dane źródłowe

W tym filmie zobaczysz, jak zlokalizować tabelę przestawną źródło danych, a następnie sprawdź źródło danych, aby upewnić się, że zawiera wszystkie potrzebne wiersze i kolumny. W razie potrzeby dostosuj źródło danych, aby uwzględnić nowe wiersze lub kolumny.

UWAGA: rozwiązanie terminowe, użyj nazwanej tabeli programu Excel jako źródła danych. Zostanie ona dostosowana automatycznie, jeśli zostaną dodane nowe wiersze.

Pisemne instrukcje znajdują się pod filmem i możesz pobrać przykładowy plik i postępować zgodnie z nimi z wideo.

Zlokalizuj dane źródłowe

Po utworzeniu tabeli przestawnej możesz chcieć dodać nowe rekordy w danych źródłowych lub zmienić istniejące rekordy. dużego skoroszytu, znalezienie dokładnego źródła tabeli przestawnej może być trudne, jeśli istnieje kilka tabel lub list.

UWAGA: Jeśli kupiłeś mój dodatek Pivot Power Premium, kliknij przycisk Tabela przestawna Informacje, a następnie kliknij Przejdź do danych źródłowych.

Aby znaleźć dane źródłowe tabeli przestawnej, wykonaj następujące czynności:

1. Zaznacz dowolną komórkę w tabeli przestawnej.
2. Na Wstążce na karcie Narzędzia tabeli przestawnej kliknij kartę Analiza (w programie Excel 2010 kliknij kartę Opcje).
3. W grupie Dane kliknij górną sekcję polecenia Zmień źródło danych.

Otworzy się okno dialogowe Zmień źródło danych tabeli przestawnej, w którym możesz zobaczyć tabelę źródłową lub zakres w pole Tabela / zakres. Może to być odniesienie do arkusza i zakresu komórek, na przykład

• Zamówienia! 1 $ A $: 9 $ H

lub nazwa tabeli, takie jak

• Sales_East

Za oknem dialogowym można zobaczyć zakres źródłowy w arkuszu, otoczony ruchomą ramką.

Na poniższym zrzucie ekranu źródłowy zakres danych kończy się w wierszu 9, a nowy rekord został dodany w wierszu 10. Rekord ten nie pojawi się w tabeli przestawnej, chyba że zostanie dostosowane źródło danych.

Dostosuj zakres danych źródłowych

Jeśli dane źródłowe tabeli przestawnej to statyczne odniesienie do określonego arkusza i zakresu, nie dostosowuje się automatycznie po dodaniu nowych danych. Na powyższym zrzucie ekranu zakres źródła danych odnosi się do Zamówienia! 1 USD A: 1 USD: 9 USD i można go dostosować ręcznie, aby uwzględnić wiersz 10.

Aby ręcznie dostosować statyczny zakres źródła:

W razie potrzeby możesz dostosować zakres źródła, aby zawierał wszelkie nowe dane, ale lepszym rozwiązaniem jest utworzenie źródła dynamicznego i oprzyj na tym tabelę przestawną. Instrukcje znajdują się poniżej.

Zmień dane źródłowe

Czasami może zajść potrzeba zmiany tabeli przestawnej, aby korzystała z innego źródła danych. W tym przykładzie utworzono nową tabelę zawierającą tylko dane dotyczące wschodniego r egion. Tabela przestawna zostanie zmieniona, aby używać tego źródła, a następnie będzie można ją wysłać do menedżera w regionie wschodnim bez ujawniania danych z regionu zachodniego.

UWAGA: Po zmianie tabeli źródłowej na inną , może być konieczna zmiana nazw niektórych pól lub dodanie innych pól do tabeli przestawnej.

Aby zmienić dane źródłowe tabeli przestawnej programu Excel, wykonaj następujące kroki:

1. Zaznacz dowolną komórkę w tabeli przestawnej.
2. Na Wstążce, na karcie Narzędzia tabeli przestawnej, kliknij kartę Analizuj (w programie Excel 2010 kliknij kartę Opcje).
3. Na karcie Dane kliknij górną sekcję polecenia Zmień źródło danych.
4. Otworzy się okno dialogowe Zmień źródło danych tabeli przestawnej i możesz zobaczyć tabelę źródłową lub zakres w polu Tabela / zakres. Na tym zrzucie ekranu poniżej bieżącym źródłem danych jest tabela Sales_Data, która zawiera dane z obu regionów.

   

5. Usuń istniejące źródło danych i wpisz nową nazwę tabeli lub wybierz nowe źródło danych Zakres. Tabela / zakres zostaje zmieniona na Sales_East, gdzie wymienione są tylko dane dotyczące wschodu.

   

6. Kliknij OK.

Dynamiczne źródło dla tabeli przestawnej

Zamiast używać zakresu statycznego jako źródła danych tabeli przestawnej, lepszym rozwiązaniem jest utworzenie zakresu dynamicznego, który automatycznie dostosuje rozmiar.

Utwórz źródło dynamiczne – tabela programu Excel

Dynamiczne źródło – nazwany zakres

Dynamiczne źródło – dynamiczna tablica

Użyj dynamicznego źródła

Dynamiczne źródło – tabela programu Excel

Zamiast używać statycznego zakresu jako źródła danych tabeli przestawnej, lepszym rozwiązaniem jest utworzenie zakresu dynamicznego na podstawie nazwanej tabeli programu Excel. Tutaj znajdują się pisemne instrukcje lub obejrzyj poniższy film.

Zanotuj nazwę tabeli, aby móc używać jej jako źródła tabeli przestawnej

Dynamiczne źródło – nazwany zakres

Jeśli nie możesz użyć tabeli programu Excel jako danych źródłowych, tworzysz dynamiczny nazwany zakres na podstawie formuły INDEKS lub PRZESUNIĘCIE. Tutaj znajdują się pisemne instrukcje lub obejrzyj wideo poniżej.

Film pokazuje, jak utworzyć zakres dynamiczny z funkcją PRZESUNIĘCIE w programie Excel 2007, a kroki są podobne w późniejszych wersjach.

UWAGA: Zapamiętaj nazwę zakresu, aby można było jej użyć jako źródła tabeli przestawnej.

Źródło dynamiczne – tablica dynamiczna

Jeśli masz wersję programu Excel, która obsługuje nowe funkcje, takie jak SORT lub UNIQUE, możesz użyć tych funkcji do tworzenia tablic dynamicznych. Ta technika wykorzystuje tylko formuły – bez makr .

W sekcji Pobieranie pobierz przykładowy plik przefiltrowanych danych źródłowych. Pokazuje on, jak skonfigurować nazwany zakres zawierający tylko widoczne wiersze z nazwanej tabeli programu Excel.

Oto filtere d dane na innym arkuszu, z tylko 2 powtórzeniami i 3 kategoriami z widocznych wierszy.

Następnie możesz utworzyć tabelę przestawną na podstawie tylko tych przefiltrowanych danych.

Użyj dynamicznego źródła dla tabeli przestawnej

Po utworzeniu dynamicznego źródła zawierającego tabelę przestawną danych, wykonaj następujące kroki, aby użyć tych danych źródłowych:

1. Zaznacz dowolną komórkę w tabeli przestawnej.
2. Na Wstążce, na karcie Narzędzia tabeli przestawnej, kliknij przycisk Analizuj (w programie Excel 2010 kliknij kartę Opcje).
3. W grupie Dane kliknij górną sekcję polecenia Zmień źródło danych.
4. W oknie dialogowym Zmień źródło danych tabeli przestawnej wyczyść pole Tabela / zakres
5. W polu Tabela / zakres wpisz nazwę utworzonej tabeli lub nazwanego zakresu. W tym przykładzie dynamicznym źródłem jest nazwana tabela – tblOrders.
6. Kliknij OK

Zapisz dane źródłowe za pomocą tabeli przestawnej

Podczas tworzenia tabeli przestawnej w skoroszycie rekordy danych źródłowych są zapisywane w specjalnym obszarze pamięci – pamięci podręcznej przestawnej. Następnie po zamknięciu pliku program Excel może zapisać dane źródłowe w tej pamięci podręcznej przestawnej lub wyczyścić tę pamięć.

Obie opcje mają zalety i wady:

Jeśli zapiszesz dane źródłowe:

• Plik będzie większy
• Plik może otwierać się szybciej

Jeśli nie zapiszesz danych źródłowych:

• Wypełnienie będzie mniejsze
• Plik może się otworzyć powoli, podczas gdy pamięć podręczna przestawna jest odbudowywana
• Tabelę przestawną należy odświeżyć po otwarciu pliku

Jak zmienić ustawienie

Aby włączyć opcję Zapisz włączenie lub wyłączenie ustawienia Dane źródłowe:

• Kliknij prawym przyciskiem myszy komórkę w tabeli przestawnej i kliknij Opcje tabeli przestawnej.
• Na karcie Dane w sekcji Dane tabeli przestawnej , dodaj lub usuń zaznaczenie pola wyboru Zapisz dane źródłowe z plikiem
• Kliknij OK.

Odśwież dane podczas otwierania

Jeśli zdecydujesz się włączyć wyłączyć opcję Zapisz dane źródłowe z plikiem, należy włączyć opcję Odśwież dane podczas otwierania pliku. To ustawienie znajduje się tuż pod polem wyboru Zapisz dane źródłowe.

W przeciwnym razie podczas próby filtrowania danych lub wprowadzania jakichkolwiek innych zmian układu pojawi się komunikat.

• „Raport w formie tabeli przestawnej został zapisany bez danych bazowych. Użyj polecenia Odśwież dane, aby zaktualizować raport. ”

Jeśli zobaczysz ten komunikat, kliknij OK, a następnie ręcznie odśwież tabelę przestawną.

Aby uniknąć irytującego wiadomości, włącz opcję Odśwież dane podczas otwierania pliku.

Odtwórz ponownie dane źródłowe tabeli przestawnej

Jeśli przypadkowo usuniesz arkusz, który zawiera dane źródłowe tabeli przestawnej lub jeśli otrzymałeś plik bez danych tabeli przestawnej, możesz użyć funkcji Pokaż szczegóły tabeli przestawnej, aby go odtworzyć.

UWAGA: To nie zadziała dla wszystkich tabel przestawnych, ale jest warto spróbować odzyskać dane źródłowe.

Aby spróbować odtworzyć dane źródłowe tabeli przestawnej, wykonaj następujące kroki, aby użyć funkcji Pokaż szczegóły:

1. Upewnij się, że że żaden z elementów w polach tabeli przestawnej nie jest ukryty – wyczyść wszystkie filtry i fragmentatory, które zostały zastosowane.
2. Tabela przestawna nie musi zawierać wszystkich pól – po prostu upewnij się, że jest co najmniej jednym polem w Val obszar ues.
3. Pokaż sumy końcowe dla wierszy i kolumn. Jeśli sumy nie są widoczne, wybierz komórkę w tabeli przestawnej i na Wstążce w obszarze Narzędzia tabeli przestawnej kliknij kartę Analiza. W grupie Układ kliknij opcję Sumy całkowite, a następnie kliknij opcję Włącz dla wierszy i kolumn.
4. Kliknij dwukrotnie komórkę suma końcowa w prawym dolnym rogu tabeli przestawnej. Powinno to spowodować utworzenie nowego arkusza z powiązanymi rekordami z oryginalnych danych źródłowych.

Napraw wyodrębnione Dane

W przypadku odzyskiwania danych źródłowych może być konieczne wprowadzenie dodatkowych zmian w celu przywrócenia ich do pierwotnego stanu.

• Zmień nazwę tabeli, używając nazwy oryginalnych danych źródłowych, jeśli jest znana.
• Jeśli oryginalne dane źródłowe zawierały formuły, będziesz musiał je ponownie utworzyć, ponieważ funkcja Pokaż szczegóły eksportuje tylko dane.
• Kolumny w wyodrębnionych danych będą w tej samej kolejności, w jakiej były w oryginalnych danych źródłowych.
• Wyodrębnione dane będą sformatowany przy użyciu domyślnego formatu tabeli skoroszytu. Możesz zastosować inny format tabeli lub zastosować własne formatowanie.
• Uwaga: jeśli wprowadziłeś zmiany w danych źródłowych i nie zaktualizowałeś tabeli przestawnej, te zmiany nie pojawią się w wyodrębnionych danych.

Połącz się z wyodrębnionymi danymi

Jeśli zmienisz nazwę tabeli, która została utworzona podczas procesu Pokaż szczegóły i użyjesz tej samej nazwy, co tabela, która pierwotnie zawierała źródło danych, tabela przestawna może automatycznie połączyć się z nowymi danymi źródłowymi.

Jeśli nie, możesz połączyć się z odtworzonymi danymi źródłowymi – wykonaj czynności opisane w sekcji powyżej: Użyj dynamicznego źródła dla tabeli przestawnej – użyj nazwy tabeli, którą nadałeś nowej tabeli.

Pobierz przykładowy plik

Pivot Source Przykład: Aby postępować zgodnie z samouczkami, możesz pobrać plik DateAmt.zip. Skompresowany plik jest w formacie xlsx i nie zawiera makr.

Przykład przefiltrowanych danych źródłowych: ten przykład dotyczy wersji programu Excel, które mają nowe funkcje, takie jak SORT i UNIQUE. Dow Załaduj ten przykładowy plik, aby zobaczyć, jak tabela przestawna jest tworzona tylko z widocznych wierszy w przefiltrowanej tabeli programu Excel. Skompresowany plik jest w formacie xlsx i nie zawiera makr.

Narzędzia tabel przestawnych

Aby zaoszczędzić czas podczas budowania, formatowania i modyfikowania tabel przestawnych, użyj narzędzi w moim Dodatek Power Premium. Za pomocą kilku kliknięć możesz:

• skopiować formatowanie z jednej tabeli przestawnej i zastosować je do innej tabeli przestawnej.
• zmień wszystkie wartości z Count na Sum
• usuń „Sumę” ze wszystkich nagłówków

i wiele więcej!

Nie przegap naszych wskazówek dotyczących programu Excel