Ta wskazówka stanowi rozszerzenie porady dotyczącej programu Excel z zeszłego tygodnia. Możesz użyć polecenia Przejdź do specjalnego, aby zaznaczyć puste komórki z zakresu. To świetna technika programu Excel do czyszczenia danych w tabeli przestawnej.
Tabele przestawne to wspaniałe narzędzia. Ale co, jeśli Twoja tabela przestawna jest tylko pośrednim krokiem w manipulowaniu danymi i chcesz dalej wykorzystywać podsumowane dane w tabeli przestawnej? Kiedyś regularnie otrzymywałem 5000 wierszy danych ze starszego systemu mainframe. Te dane zawierają kolumny dla produktu, miesiąca, regionu, stanu i ilości. Musiałem podsumować dane w tabeli przestawnej z miesiącami na górze, produktem, regionem i stanem w wierszach. Dzięki tabelom przestawnym jest to bardzo proste.
Stamtąd musiałem pobrać dane z tabeli przestawnej i zaimportować je do programu Access. Jak widać, domyślna tabela przestawna nie jest do tego odpowiednia. Program Excel używa stylu konspektu, dzięki czemu każdy region pojawia się tylko raz, z pustymi komórkami po nim, aż do sumy częściowej regionu. Komórki w sekcji danych bez danych są puste, a nie numeryczne. Nie chcę również sum pośrednich w danych.
Począwszy od programu Excel 97, istnieją opcje tabeli przestawnej umożliwiające sterowanie niektórymi z tego zachowania.
W oknie dialogowym Układ tabeli przestawnej kliknij dwukrotnie nazwę pola dla regionu. W sekcji Sumy częściowe kliknij przycisk opcji „Brak”. Pozwoli to pozbyć się niechcianych sum pośrednich.
W programie Excel 2000 przycisk opcji przeniesie Cię do okna dialogowego, w którym możesz określić „Dla pustych komórek, pokaż:” i wypełnić zero, aby tabela nie była pełna pustych komórek.
Aby dalej manipulować tabelą przestawną, musisz sprawić, by nie była to już tabela przestawna. Jeśli spróbujesz zmienić komórki w tabeli przestawnej, program Excel poinformuje Cię, że nie możesz zmienić części tabeli przestawnej. Aby zmienić tabelę przestawną na same wartości, wykonaj następujące kroki:
- Przesuń wskaźnik komórki poza tabelę przestawną.
- Jeśli tabela przestawna zaczyna się w wierszu 1, wstaw nowy wiersz 1.
- Rozpocznij wyróżnianie w nowym pustym wierszu 1 i przeciągnij w dół, aby zaznaczyć całą tabelę przestawną.
- Wykonaj Ctrl + C (lub Edycja - Kopiuj), aby skopiować ten zakres.
- Po zaznaczeniu tego samego obszaru wykonaj Edytuj - Wklej specjalnie - Wartości - OK, aby zmienić tabelę przestawną na wartość statyczną.
Ale oto prawdziwa wskazówka na ten tydzień. Jennifer pisze
Ciągle napotykam problem z wykorzystaniem opcji tabeli przestawnej do podsumowania ryz danych, ale nie wypełnia ona wierszy pod każdą zmianą kategorii wierszy. Czy wiesz, jak wypełnić tabelę przestawną poniżej każdej zmiany w kategorii? Musiałem przeciągać i kopiować każdy kod, aby móc wykonywać więcej tabel przestawnych lub sortować. Próbowałem zmienić opcje w tabeli przestawnej, ale bezskutecznie.
Odpowiedź nie jest łatwa do nauczenia. To nie jest intuicyjne. Ale jeśli nienawidzisz przeciągania tych komórek w dół, pokochasz poświęcenie czasu na naukę tego procesu! Podążaj dalej - wydaje się długi i rozciągnięty, ale naprawdę działa. Gdy go zdobędziesz, możesz to zrobić w 20 sekund.
W rzeczywistości są tutaj 2 lub 3 nowe wskazówki. Załóżmy, że masz 2 kolumny po lewej stronie, które są w formacie konspektu i należy je wypełnić. Podświetl od komórki A3 aż do komórki B999 (lub jakikolwiek inny ostatni wiersz danych).
Sztuczka nr 1. Zaznaczenie wszystkich pustych komórek w tym zakresie.
Naciśnij Ctrl + G, alt = "" + S + K, a następnie Enter. co?
Ctrl + G wywołuje okno dialogowe GoTo
Alt + S wybiorą przycisk „Specjalne” w oknie dialogowym
Okno dialogowe Goto-Special to niesamowita rzecz, o której niewielu wie. Naciśnij „k”, aby wybrać „spacje”. Naciśnij klawisz Enter lub kliknij OK, a teraz zaznaczysz tylko wszystkie puste komórki w kolumnach konspektu tabeli przestawnej. To są wszystkie komórki, które chcesz wypełnić.
Sztuczka nr 2. Nie patrz na ekran, kiedy to robisz - jest to zbyt przerażające i zagmatwane.
Naciśnij klawisz równości. Uderz w strzałkę w górę. Przytrzymaj Ctrl i naciśnij Enter. Trafienie równa się, a strzałka w górę mówi: „Chcę, aby ta komórka była taka sama, jak komórka nade mną”. Przytrzymanie klawisza Ctrl po naciśnięciu klawisza Enter mówi: „Wprowadź tę samą formułę w każdej zaznaczonej komórce, która dzięki sztuczce nr 1 zawiera wszystkie puste komórki, które chcieliśmy wypełnić.
Sztuczka nr 3. Co Jennifer już wie, ale jest tutaj dla kompletności.
Teraz musisz zmienić wszystkie te formuły na wartości. Zaznacz ponownie wszystkie komórki w A3: B999, a nie tylko puste miejsca. Naciśnij Ctrl + C, aby skopiować ten zakres. Hit alt = "" + E, a następnie sv (enter). wkleić wartości specjalne do tych formuł.
Ta-da! Już nigdy nie spędzisz popołudnia na ręcznym usuwaniu nagłówków kolumn w tabeli przestawnej.
