Zliczanie według wielu warunków za pomocą COUNTIFS

Funkcja COUNTIFS zlicza wiersze spełniające kilka warunków jednocześnie. Podczas gdy COUNTIF odpowiada na pytanie „ile transakcji sprzedaży było w regionie East?”, COUNTIFS odpowiada na pytanie „ile transakcji sprzedaży w regionie East miało miejsce w styczniu?”.

Funkcja ta niczego nie sumuje ani nie uśrednia — po prostu zlicza wiersze, które spełniają każdy test. Dzięki temu doskonale nadaje się do pytań takich jak „ile zamówień o wartości powyżej 100 USD pochodziło od nowych klientów?”.

Warto poznać jej strukturę i zastosowanie.

COUNTIFS nie ma argumentu sum_range, ponieważ nie sumuje wartości — potrzebuje tylko zakresów do sprawdzenia oraz kryteriów, z którymi należy je porównać.

• criteria_range1, criteria1 — pierwszy test
• criteria_range2, criteria2 — drugi test
• ...i tak dalej, maksymalnie do 127 par

Każdy argument występuje w uporządkowanej parze zakres, kryterium. Wynikiem jest jedna liczba całkowita: liczba wierszy, w których wszystkie warunki są spełnione.

Skorzystajmy z tej samej tabeli sprzedaży — kolumna A to Region, kolumna B to Month, a kolumna C to Amount — i policzmy, ile transakcji sprzedaży dotyczyło regionu East i miesiąca January.

W tej formule nie ma kolumny z kwotami, ponieważ zliczamy wiersze, a nie sumujemy pieniędzy. Każda para dodatkowo zawęża wynik zliczania.

Podobnie jak SUMIFS, funkcja COUNTIFS przyjmuje operatory porównania w cudzysłowie podczas testowania wartości liczbowych.

• ">100" — wartości większe niż 100
• ">=18" — wartości równe 18 lub większe
• "<>" — dowolna niepusta komórka

W tym przykładzie zliczamy wiersze z regionu East, w których kwota jest większa niż 100. Należy zauważyć, że kolumna z kwotami występuje jako criteria_range, a nie sum_range.

Często trzeba zliczyć wartości mieszczące się między dwiema liczbami. Nie można zrobić tego za pomocą jednego kryterium — tę samą kolumnę należy podać dwa razy, określając dolną i górną granicę.

Spowoduje to zliczenie kwot wynoszących co najmniej 50 i co najwyżej 200. Oba warunki odnoszą się do kolumny C, a ponieważ COUNTIFS stosuje logikę AND, zliczone zostaną tylko wiersze znajdujące się w tym przedziale.

Aby granice były dynamiczne, należy przechowywać je w komórkach i łączyć operator z komórką za pomocą &. Niską wartość należy umieścić w komórce F1, a wysoką w komórce F2.

Zapis ">="&F1 tworzy kryterium większe lub równe wartości w komórce F1. Dzięki temu użytkownik może wpisać nowe limity i natychmiast zobaczyć zaktualizowaną liczbę bez edytowania samej formuły.

Dwa specjalne kryteria są przydatne podczas sprawdzania jakości danych:

• "<>" zlicza komórki, które nie są puste
• "=" lub "" zlicza komórki, które są puste

Ta formuła zlicza, ile wierszy regionu East ma wypełnioną wartość Salesperson (kolumna D nie jest pusta) — to szybki sposób na wykrycie brakujących danych.

Kryteria tekstowe obsługują te same symbole wieloznaczne co SUMIFS. Symbolu * należy użyć dla ciągu znaków o dowolnej długości, a ? dla pojedynczego znaku.

Spowoduje to zliczenie każdego wiersza, w którym nazwisko osoby sprzedającej zaczyna się od litery M — pasują Maria, Mike i Mohammed. Symbole wieloznaczne dotyczą wyłącznie tekstu; w przypadku liczb są ignorowane.

Obowiązuje tu ta sama zasada wymiarów co w funkcji SUMIFS: każdy criteria_range musi mieć taki sam kształt. Jeśli jeden zakres to A2:A100, a drugi to B2:B50, funkcja COUNTIFS zwróci błąd #VALUE!.

Odwołania do całych kolumn, takie jak A:A i B:B, automatycznie mają ten sam rozmiar, dlatego są często wybierane jako bezpieczne zakresy kryteriów.

COUNTIFS jest podstawą wielu pulpitów nawigacyjnych. W kolumnie F należy umieścić nazwy regionów, a w wierszu 1 — nazwy miesięcy, a następnie zliczyć każdą kombinację za pomocą odwołań mieszanych, aby formuła wypełniła całą siatkę.

Zablokowanie kolumn za pomocą $A:$A oraz komórek wyszukiwania za pomocą $F2 i G$1 pozwala jednej formule wypełnić całą tabelę przestawną z wartościami zliczeń po jej skopiowaniu.

Należy zachować właściwe rozróżnienie między tymi funkcjami. COUNTIF przyjmuje pojedynczą parę range, criteria, natomiast COUNTIFS przyjmuje jedną lub więcej takich par. W przeciwieństwie do SUMIFS żadna z tych funkcji nie ma osobnego zakresu wynikowego — obie po prostu zliczają.

Jeśli występuje więcej niż jeden warunek, należy użyć funkcji COUNTIFS. Wiele osób korzysta z COUNTIFS nawet przy jednym teście, aby pamiętać tylko jeden spójny schemat dla funkcji SUM, COUNT i AVERAGE.

Można już zliczać wiersze według wielu warunków:

• COUNTIFS(range1, crit1, range2, crit2, ...) — brak argumentu sum_range, tylko pary testów.
• Zakres między dwiema wartościami można zliczyć, powtarzając tę samą kolumnę z dwiema granicami.
• Zapisu "<>" należy używać dla komórek niepustych, a symbolu & do łączenia operatora z ograniczeniami zapisanymi w komórkach.
• Wszystkie zakresy muszą mieć ten sam rozmiar.

Następnie: obliczanie średniej wartości według wielu warunków za pomocą AVERAGEIFS.