Większość arkuszy kalkulacyjnych prawdopodobnie wymaga sumowania kwot z różnych powodów. Czasami jednak musimy policzyć liczbę instancji, a nie sumy.
W tym artykule porównam użycie COUNTIF, COUNTIFS i tabeli przestawnej do zliczania liczby instancji w zestawie danych. Rysunek 1 przedstawia zestaw danych, który zawiera listę wynagrodzeń według regionu, działu i tego, czy są to pracownicy kontraktowi, czy nie.
Załóżmy, że chcemy określić liczbę pracowników według działów. W takim przypadku musimy najpierw uzyskać listę po jednej nazwie każdego działu. Aby to zrobić:
- Kliknij prawym przyciskiem myszy kolumnę C.
- Wybierz Kopiuj z wyświetlonego menu.
- Kliknij prawym przyciskiem myszy dowolną inną kolumnę, na przykład kolumnę H.
- Z wyświetlonego menu wybierz Wklej.
- Aktywuj menu Dane programu Excel.
- Wybierz polecenie Usuń duplikaty.
- Kliknij przycisk OK w oknie dialogowym Usuń duplikaty.
- Kliknij przycisk OK po wyświetleniu monitu informującego o liczbie unikalnych elementów.
Rysunek 1: Duplikaty muszą zostać usunięte przed określeniem liczby pracowników według działów.
Teraz możesz użyć funkcji COUNTIF, aby policzyć liczbę pracowników według działów. Wprowadź tę formułę w komórce I2:
=COUNTIF(C:C,H2)
Zauważ, że w tym przypadku pominąłem numery wierszy w odwołaniu do kolumny C. W ten sposób formuła jest zabezpieczona na przyszłość, więc jeśli później dodam więcej rekordów na dole, funkcja COUNTIF również je uwzględni.
Oznacza to również, że nie trzeba dodawać znaków dolara, aby wskazać odniesienie bezwzględne. Formuła wyglądałaby tak, gdybyśmy odwoływali się dokładnie do tych komórek:
=COUNTIF(C$1:C$42,H2)
Znaki dolara instruują program Excel, aby nie zmieniał numerów wierszy podczas kopiowania formuły w dół. Jak można się domyślić, funkcja COUNTIF ma dwa argumenty:
- zakres - zakres komórek do przeszukania
- kryteria - szukany element w zakresie
Rysunek 2: Funkcja COUNTIF zlicza liczbę pracowników według działów.
Teraz możesz chcieć rozszerzyć tę liczbę pracowników, aby zliczyć pracowników zarówno według działu, jak i regionu. W tej sytuacji użyj funkcji Remove Duplicates, jak pokazano powyżej, aby utworzyć unikalną listę nazw regionów.
Załóżmy, że ukończona lista znajduje się w komórkach od M1 do M5. Możemy przetransponować te wartości, aby stały się nagłówkami kolumn:
- Zaznacz komórki M2:M5.
- Wybierz polecenie Kopiuj (lub naciśnij Ctrl-C).
- Kliknij prawym przyciskiem myszy komórkę I1.
- Wybierz z menu polecenie Transponuj. W programie Excel 2007 należy wybrać polecenie Wklej specjalnie, zaznaczyć pole wyboru Transponuj, a następnie kliknąć przycisk OK.
Możesz teraz usunąć kolumnę M lub użyty zakres tymczasowy.
Rysunek 3: Transponowanie nazw regionów w celu przygotowania danych do użycia funkcji COUNTIFS.
W tym momencie będziemy musieli użyć funkcji COUNTIFS, ponieważ musimy liczyć na podstawie dwóch kryteriów. Wprowadź tę formułę w komórce I2:
=COUNTIFS($A:$A,I$1,$C:$C,$H2)
Zauważ, że w tym przypadku potrzebujemy więcej znaków $, ponieważ kopiujemy formułę w dół i w poprzek. Funkcja COUNTIFS pozwala określić do 128 par kryteriów, co jest kombinacją wyżej wymienionych argumentów zakresu i kryteriów, które omówiłem dla COUNTIF.
Rysunek 4: Użyj funkcji COUNTIFS, aby policzyć zarówno działy, jak i regiony.
Trzecie podejście polega na użyciu tabeli przestawnej do podsumowania tych danych. W tym przypadku Excel poradzi sobie z całym ciężkim zadaniem:
- Kliknij dowolną komórkę na liście, np. komórkę A1.
- Aktywuj menu Wstaw programu Excel.
- Wybierz polecenie Tabela przestawna.
- Kliknij OK, gdy pojawi się polecenie Utwórz tabelę przestawną.
- Kliknij Department na liście PivotTable Fields.
- Przeciągnij Region do sekcji Kolumny na liście Pola tabeli przestawnej.
- Przeciągnij Region do sekcji Wartości na liście Pola tabeli przestawnej.
- Excel natychmiast utworzy podsumowanie, które pokazuje po jednym z każdego działu i regionu.
Rysunek 5: Użyj tabeli przestawnej, aby policzyć dane.
Tabele przestawne zapewniają kilka korzyści w porównaniu do korzystania z funkcji arkusza:
- Nie ma potrzeby usuwania duplikatów, ponieważ Excel automatycznie wyświetla po jednym z każdego elementu.
- Nie trzeba pisać formuł, ponieważ Excel automatycznie podsumowuje dane.
- W razie potrzeby Excel dodaje wiersze i kolumny z sumami całkowitymi, którymi można zarządzać za pomocą polecenia Sumy całkowite, które pojawia się w menu Projekt dostępnym po wybraniu dowolnej komórki w tabeli przestawnej.
