Ten artykuł został zaczerpnięty z witryny MrExcel.com.

Załóżmy, że budujesz miesięczną listę transakcji, na której każdy kod konta może występować wiele razy. Na koniec miesiąca należy uporządkować i podsumować dane według kodów kont.

Zadanie to można wykonać na co najmniej pięć sposobów. Poniżej przedstawiono samouczek opisujący te pięć metod.

Uwaga  Zbiór danych przykładowych zawiera numery kont w kolumnie A i kwoty w kolumnie B. Dane znajdują się w zakresie A2:B100 i nie są obecnie posortowane.

Metoda 1. Sprytnie obmyślone instrukcje JEŻELI w połączeniu z poleceniem Wklej specjalnie

Oto procedura:

1. Posortuj dane według kont (kolumny A).
2. Wymyśl formułę w kolumnie C, która będzie liczyła sumę bieżącą dla każdego konta. Na przykład w komórce C2 wpisz:

   =JEŻELI(A2=A1;C1+B2;B2)

3. Wymyśl formułę w kolumnie D, która będzie identyfikować ostatnią pozycję danego konta. Na przykład w komórce D2 wpisz:

   =JEŻELI(A2=A3;FAŁSZ;PRAWDA)

4. Skopiuj formuły z zakresu C2:D2 w dół do wszystkich swoich wierszy.
5. Skopiuj zakres C2:D100 do schowka. Mając zaznaczony ten zakres, kliknij polecenie Wklej specjalnie w menu Edycja, kliknij polecenie Wartości, a następnie kliknij przycisk OK, aby zastąpić formuły z zakresu C2:D100 wartościami.
6. Posortuj dane malejąco według kolumny D.
7. Wierszom zawierającym wartość PRAWDA w kolumnie D odpowiada lista unikatowych numerów kont w kolumnie A oraz końcowych sum częściowych tej kolumny w kolumnie C.

Za  Jest to metoda szybka. Wystarczy tylko zamiłowanie do pisania instrukcji JEŻELI.

Przeciw  Są lepsze sposoby.

Metoda 2. Uzyskiwanie listy unikatowych kont za pomocą filtru zaawansowanego

Oto metoda uzyskiwania listy unikatowych numerów kont:

1. Zaznacz zakres komórek A1:A100.
2. W menu Dane wskaż polecenie Filtr, a następnie kliknij polecenie Filtr zaawansowany.
3. Kliknij przycisk radiowy Kopiuj w inne miejsce.
4. Zaznacz pole wyboru Tylko unikatowe rekordy.
5. Wyznacz w arkuszu puste miejsce, w którym ma się pojawić lista unikatowych wartości. Wprowadź wybraną lokalizację w polu Kopiuj do.

   Uwaga  Pole Kopiuj do jest niedostępne, dopóki nie zostanie kliknięty przycisk radiowy Kopiuj w inne miejsce.

6. Kliknij przycisk OK. We wskazanym miejscu pojawią się unikatowe wartości.
7. Wprowadź dowolne inne manipulacje, formuły tablicowe itd., które są potrzebne do uzyskania odpowiednich wyników.

Za  Jest to metoda szybsza niż pierwsza. Nie trzeba sortować danych.

Przeciw  Formuły tablicowe wprowadzane po zastosowaniu tej metody przyprawią każdego o zawrót głowy.

Metoda 3. Użycie polecenia Konsoliduj

W tej metodzie zastosowano polecenie Konsoliduj, które ma kilka wymagań. Numer konta musi się znajdować z lewej strony pól liczbowych przeznaczonych do sumowania. Nad każdą kolumną muszą znajdować się nagłówki. Trzeba przypisać nazwę do prostokątnego zakresu komórek zawierającego numery kont w lewej kolumnie i nagłówki w pierwszym wierszu. W tym przypadku będzie to zakres komórek A1:B100.

1. Zaznacz zakres komórek A1:B100.
2. Przypisz nazwę do tego zakresu, klikając nazwę pola (z lewej strony paska formuły) i wpisując nazwę typu SumujMnie (ewentualnie w menu Wstaw kliknij polecenie Nazwa).
3. Umieść wskaźnik komórki w pustej części arkusza.
4. W menu Dane kliknij polecenie Konsoliduj.
5. W polu Odwołanie wpisz nazwę zakresu (SumujMnie).
6. W sekcji Użyj etykiet w zaznacz oba pola wyboru Górny wiersz i Lewa kolumna.
7. Kliknij przycisk OK.

Za  Nie trzeba sortować danych. Całą procedurę można wykonać, używając ciągu skrótów klawiaturowych: ALT+D+N (nazwa zakresu), ALT+T, ALT+L, ENTER. Procedura może być stosowana w małej i dużej skali. Jeśli zakres zawiera 12 kolumn z danymi miesięcznymi, w wyniku otrzymamy podsumowania poszczególnych miesięcy.

Przeciw  Aby użyć ponownie polecenia Konsoliduj na tym samym arkuszu, należy usunąć starą nazwę zakresu z pola Wszystkie odwołania, używając klawisza DELETE. Numer konta musi znajdować się z lewej strony danych liczbowych. Jest to metoda odrobinę wolniejsza niż tabela przestawna, co staje się zauważalne w przypadku zestawów danych liczących ponad 10 000 rekordów.

Metoda 4. Użycie polecenia Suma częściowa

Jest to bardzo ciekawa funkcja, ale używana rzadziej niż polecenie Konsoliduj ze względu na nietypowy sposób pracy z danymi wynikowymi.

1. Posortuj dane rosnąco według kolumny A.
2. Zaznacz dowolną komórkę z zakresu danych.
3. W menu Dane kliknij polecenie Sumy częściowe.
4. Domyślnie oferowana jest możliwość podsumowania ostatniej kolumny danych. W przypadku tego przykładu to dobrze, ale często trzeba przewinąć całą listę Dodaj sumę pośrednią do, aby wybrać odpowiednie pola.
5. Kliknij przycisk OK. Przy każdej zmianie numeru konta zostanie wstawiony nowy wiersz z obliczoną sumą częściową.

Po uzyskaniu sum częściowych zobaczysz małe przyciski 1, 2 i 3 pod polem nazwy. Kliknij przycisk 2, aby zobaczyć dla każdego konta tylko jeden wiersz z sumami. Kliknij przycisk 3, aby zobaczyć wszystkie wiersze.

Za  Świetna funkcja. Znakomita do drukowania raportów z podsumowaniami i podziałami stron po każdej części.

Przeciw  Dane muszą być wcześniej posortowane. Funkcja może działać wolno dla większych ilości danych. Aby przenieść sumy w inne miejsce, trzeba użyć polecenia Przejdź do (z menu Edycja), a następnie kliknąć przycisk Specjalnie i wybrać przycisk radiowy Tylko widoczne komórki. Aby powrócić do pierwotnych danych, trzeba użyć polecenia Sumy częściowe (z menu Dane) i kliknąć przycisk Usuń wszystko.

Metoda 5. Użycie tabeli przestawnej

Tabele przestawne są rozwiązaniem najbardziej uniwersalnym ze wszystkich. Dane nie muszą być posortowane. Kolumny liczbowe mogą znajdować się z lewej lub z prawej strony numeru konta. Numery kont mogą być rozlokowane w kolumnach lub wierszach.

1. Zaznacz dowolną komórkę w zakresie danych.
2. W menu Dane kliknij polecenie Raport tabeli przestawnej i wykresu przestawnego.
3. Kliknij przycisk Dalej, aby zaakceptować wartości domyślne w kroku 1.
4. Upewnij się, że obszar danych w kroku 2 jest poprawny (zazwyczaj jest) i kliknij przycisk Dalej.
5. Kliknij przycisk Układ w kroku 3 (użytkownicy programu Excel 97 automatycznie przechodzą do okna Układ w kroku 3).
6. W oknie dialogowym Układ przeciągnij przycisk Konto z prawej strony i upuść go na obszar Wiersz.
7. Przeciągnij przycisk Kwota z prawej strony i upuść go na obszar Dane.
8. Kliknij przycisk OK. (Jeśli używasz programu Excel 97, kliknij przycisk Dalej).
9. Określ, czy wyniki mają zostać umieszczone w nowym czy istniejącym arkuszu, a następnie kliknij przycisk Zakończ.

Za  Jest to metoda uniwersalna i szybka, nawet dla dużych ilości danych.

Przeciw  Może onieśmielać.