Bill zadał w tym tygodniu pytanie o zbędne dane w Excelu.
Miesięczną listę transakcji buduję w Excelu. Pod koniec miesiąca muszę wyeliminować zbędne dane i podać sumę według kodu konta. Każdy kod konta może wystąpić wielokrotnie. Następnie Bill opisał swoją obecną metodologię programu Excel, która jest podobna do metody 1 poniżej, aby stworzyć unikalną listę kodów kont, z planami wykorzystania macierzy formuł CSE w celu uzyskania sum. Pyta, czy istnieje łatwiejszy sposób uzyskania unikalnej listy kodów kont z sumami dla każdego konta?
To idealne pytanie na wakacje. Będąc użytkownikiem Lotus od 15 lat, uważam metodę Billa za klasyczną metodę „szybkiej i brudnej” manipulacji danymi z dobrych starych czasów Lotus 2.1. To czas, w którym liczy się nasze błogosławieństwa. Kiedy zastanawiasz się nad tym pytaniem, zdajesz sobie sprawę, że ludzie w Microsoft naprawdę obdarowali nas przez lata wieloma narzędziami. Jeśli używasz programu Excel 97, istnieje co najmniej pięć metod wykonania tego zadania, z których wszystkie są znacznie łatwiejsze niż klasyczna metoda opisana przez Billa. W tym tygodniu przedstawię samouczek na temat pięciu metod.
Mój uproszczony zestaw danych zawiera numery rachunków w kolumnie A i kwoty w kolumnie B. Dane zaczynają się od A2: B100. Na początku nie jest to sortowane.
Metoda 1
Użyj kreatywnych instrukcji If w połączeniu z funkcją Wklej wartości specjalne, aby znaleźć odpowiedź.
Biorąc pod uwagę nowsze narzędzia oferowane przez program Excel, nie polecam już tej metody. Używałem tego często, zanim pojawiły się lepsze rzeczy i nadal są sytuacje, w których się przydaje. Moja alternatywna nazwa to metoda „Lotus-123-When-Were-In-The-In-The-Mood-To-Use- @ DSUM”. Oto kroki.
- Posortuj dane według kolumny A.
- Wymyśl formułę w kolumnie C, która zachowa sumę bieżącą według konta. Komórka C2 jest
=IF(A2=A1,C1+B2,B2). - Wymyśl wzór w D, który pozwoli zidentyfikować ostatni wpis dla określonego konta. Komórka D2 jest
=IF(A2=A3,FALSE,TRUE). - Skopiuj C2: D2 do wszystkich swoich wierszy.
- Skopiuj C2: D100. Wykonaj Edit - PasteSpecial - Values z powrotem na C2: D100, aby zmienić formuły na wartości.
- Sortuj malejąco według kolumny D.
- W przypadku wierszy, które mają wartość PRAWDA w kolumnie D, masz unikalną listę numerów rachunków w A i końcową sumę bieżącą w C.
Plusy: To jest szybkie. Wszystko, czego potrzebujesz, to wyczucie pisania instrukcji IF.
Wady: są lepsze sposoby.
Metoda 2
Użyj filtru danych - filtr zaawansowany, aby uzyskać listę unikalnych kont.
Pytanie Billa naprawdę brzmiało, jak uzyskać unikalną listę numerów kont, aby mógł użyć formuł CSE do uzyskania sumy. Jest to metoda uzyskiwania listy unikatowych numerów kont.
- Wyróżnij A1: A100
- Z menu wybierz Dane, Filtr, Filtr zaawansowany
- Kliknij przycisk opcji „Kopiuj do innej lokalizacji”.
- Kliknij pole wyboru „Tylko unikatowe rekordy”.
- Wybierz pustą sekcję arkusza, w której ma się pojawić unikatowa lista. Wpisz to w polu „Kopiuj do”. (Zwróć uwagę, że to pole jest wyszarzone, dopóki nie wybierzesz opcji „Kopiuj do innej lokalizacji”.
- Kliknij OK. Unikalne numery kont pojawią się w F1.
- Wprowadź dowolne manipulacje w dół, formuły tablicowe itp., Aby uzyskać wyniki.
Zalety: Szybsza niż metoda 1. Nie jest wymagane sortowanie.
Wady: Formuły CSE wymagane po tym przyprawią Cię o zawrót głowy.
Metoda 3
Użyj konsolidacji danych.
Jakość mojego życia poprawiła się, gdy program Excel zaoferował konsolidację danych. To było WIELKIE! Konfiguracja zajmuje 30 sekund, ale oznaczała śmierć dla DSUM i innych metod. Twój numer konta musi znajdować się po lewej stronie pól numerycznych, które chcesz zsumować. Musisz mieć nagłówki nad każdą kolumną. Musisz przypisać nazwę zakresu do prostokątnego bloku komórek, które zawierają numery kont w lewej kolumnie i nagłówki u góry. W tym przypadku zakres ten to A1: B100.
- Podświetl A1: B100
- Przypisz nazwę zakresu do tego obszaru, klikając pole nazwy (po lewej stronie paska formuły) i wpisując nazwę, na przykład „RazemMnie”. (Alternatywnie użyj Wstaw - Nazwa).
- Umieść wskaźnik komórki w pustej sekcji arkusza.
- Wybierz dane - konsoliduj
- W polu odwołania wpisz nazwę zakresu (TotalMe).
- W sekcji Użyj etykiet w zaznacz górny wiersz i lewą kolumnę.
- Kliknij OK
Plusy: to moja ulubiona metoda. Nie jest wymagane sortowanie. Skrót to alt-D N (rangename) alt-T alt-L enter. Jest łatwo skalowalny. Jeśli Twój zakres obejmuje 12 miesięcznych kolumn, odpowiedź będzie zawierała sumy z każdego miesiąca.
Wady: Jeśli wykonasz kolejną konsolidację danych w tym samym arkuszu, musisz usunąć starą nazwę zakresu z pola Wszystkie odwołania za pomocą przycisku Usuń. Numer konta musi znajdować się po lewej stronie danych liczbowych. Jest nieco wolniejszy niż tabele przestawne, co staje się zauważalne w przypadku zestawów danych zawierających ponad 10 000 rekordów.
Metoda 4
Użyj podsumowań danych.
To fajna funkcja. Ponieważ praca z wynikowymi danymi jest dziwna, używam ich rzadziej niż Konsolidacja danych.
- Sortuj według kolumny A rosnąco.
- Wybierz dowolną komórkę w zakresie danych.
- Wybierz z menu Dane - Sumy częściowe.
- Domyślnie program Excel oferuje sumę częściową ostatniej kolumny danych. To działa w tym przykładzie, ale często trzeba przewijać listę „Dodaj sumę częściową do:”, aby wybrać odpowiednie pola.
- Kliknij OK. Excel wstawi nowy wiersz przy każdej zmianie numeru konta z sumą.
Po dodaniu sum pośrednich zobaczysz małe 123 pojawiające się poniżej pola nazwy. Kliknij 2, aby zobaczyć tylko jedną linię na konto z sumami. Przeczytaj sekcję Kopiuj podsumy Excela, aby uzyskać wyjaśnienie specjalnych kroków potrzebnych do skopiowania ich do nowej lokalizacji. Kliknij 3, aby zobaczyć wszystkie linie. Plusy: Cool Feature. Doskonały do drukowania raportów z sumami i podziałami stron po każdej sekcji.
Wady: dane należy najpierw posortować. Powolny dla wielu danych. Musisz użyć Goto-Special-VisbileCellsOnly, aby uzyskać sumy w innym miejscu. Aby wrócić do oryginalnych danych, musisz użyć opcji Data-Subtotals-RemoveAll.
Metoda 5
Użyj tabeli przestawnej.
Tabele przestawne są najbardziej wszechstronne ze wszystkich. Twoje dane nie muszą być sortowane. Kolumny liczbowe mogą znajdować się po lewej lub po prawej stronie numeru konta. Możesz łatwo przenieść numery kont w dół lub w poprzek strony.
- Wybierz dowolną komórkę w zakresie danych.
- Z menu wybierz Dane - Tabela przestawna.
- Zaakceptuj wartości domyślne w kroku 1
- Upewnij się, że zakres danych w kroku 2 jest poprawny (zwykle jest)
- Jeśli korzystasz z programu Excel 2000, kliknij przycisk Układ w kroku 3. Użytkownicy programu Excel 95 i 97 automatycznie przechodzą do układu w kroku 3.
- W oknie dialogowym układu przeciągnij przycisk Konto z prawej strony okna i upuść go w obszarze Wiersz.
- Przeciągnij przycisk Kwota z prawej strony okna dialogowego i upuść go w obszarze Dane.
- Użytkownicy programu Excel 2000 klikają OK, użytkownicy programu Excel 95/97 klikają przycisk Dalej.
- Określ, czy chcesz, aby wyniki były w nowym arkuszu, czy w określonej sekcji istniejącego arkusza. Przeczytaj więcej o tabelach przestawnych w sekcji Zaawansowane sztuczki tabel przestawnych programu Excel.
- Stoły przestawne oferują niesamowitą funkcjonalność i sprawiają, że to zadanie jest bardzo proste. Aby skopiować wyniki tabeli przestawnej, musisz wykonać polecenie Edycja-Wklej Wartości Specjalne, w przeciwnym razie Excel nie pozwoli wstawiać wierszy itp.
Zalety: szybki, elastyczny, potężny. Szybko, nawet w przypadku dużej ilości danych.
Wady: nieco onieśmielające.
Bill ma teraz cztery nowe metody eliminacji zbędnych danych. Chociaż metody te nie były dostępne od zarania dziejów, zarówno Lotus, jak i Excel były wielkimi innowatorami, oferując nam szybsze sposoby wykonywania tego przyziemnego zadania.
