Zliczanie według kryteriów za pomocą COUNTIF

Funkcja SUMIF sumuje pasujące liczby. Czasami nie chodzi jednak o sumę, lecz tylko o to, aby dowiedzieć się, ile wierszy spełnia warunek. Ile sprzedaży przypadło na region East? Ile wyników było większych niż 90?

Tym zajmuje się funkcja COUNTIF. Przeszukuje ona zakres i zlicza każdą komórkę spełniającą warunek, pomijając pozostałe.

Funkcja COUNTIF jest prostsza niż SUMIF, ponieważ wymaga tylko dwóch elementów:

• range komórki, które należy przejrzeć
• criteria warunek, z którym porównywana jest każda komórka

Nie ma osobnego elementu sum_range, ponieważ COUNTIF zlicza pasujące komórki w tym samym zakresie, który sprawdza.

Załóżmy, że regiony są wymienione w A2:A10. Aby sprawdzić, ile wierszy ma wartość East, przeszukaj ten zakres pod kątem słowa East ujętego w cudzysłowy.

Arkusz odczytuje każdą komórkę i za każdym razem, gdy znajdzie East, zwiększa wynik o jeden. Podobnie jak SUMIF, dopasowywanie tekstu nie uwzględnia wielkości liter.

Wpisanie słowa na stałe jest w porządku, ale odwołanie do komórki sprawia, że formuła jest elastyczna. Jeśli komórka D1 zawiera nazwę regionu, zlicz dopasowania na podstawie D1.

Po zmianie D1 na West wynik zostanie natychmiast zaktualizowany. Dobrze współgra to z funkcją SUMIF w następnej kolumnie, dzięki czemu można wyświetlić zarówno sumę, jak i liczbę wpisów dla tego samego regionu.

COUNTIF działa również na liczbach. Aby zliczyć, ile wyników w B2:B10 jest dokładnie równych 100, użyj samej liczby jako kryterium.

W przypadku zwykłej liczby nie trzeba używać cudzysłowów, choć ich użycie również zadziała. Każda komórka równa 100 zwiększa wynik o jeden.

Prawdziwa siła tej funkcji ujawnia się przy użyciu operatorów porównania wewnątrz cudzysłowów. Aby zliczyć wyniki większe niż 90:

• \">90\" powyżej 90
• \"<60\" poniżej 60
• \">=70\" 70 lub więcej

Ta formuła informuje, ile wpisów przekroczyło próg 90, więc doskonale nadaje się do zliczania zaliczeń i niezaliczeń.

Aby porównać wartość przechowywaną w komórce, połącz operator i komórkę za pomocą ampersandu, dokładnie tak jak w funkcji SUMIF. Jeśli D1 zawiera wynik progowy, zlicz wszystkie wartości równe temu progowi lub od niego większe.

Operator & łączy \">=\" z zawartością D1, tworząc dynamiczny warunek, taki jak \">=70\".

Aby zliczyć komórki, które nie zawierają określonej wartości, użyj operatora różności <>. Ta formuła zlicza każdy region, który nie jest równy East.

Można również zliczać niepuste komórki za pomocą samego \"<>\", które dopasowuje każdą komórkę zawierającą dowolną wartość. To szybki sposób na sprawdzenie, ile wpisów zostało uzupełnionych.

Dwa przydatne wzorce:

• \"\" zlicza naprawdę puste komórki
• \"<>\" zlicza komórki zawierające dowolną wartość

Jest to przydatne przy śledzeniu postępów, na przykład liczby zadań, które nie mają jeszcze statusu. Należy pamiętać, że komórka zawierająca spację wygląda na pustą, ale taką nie jest, dlatego warto oczyścić dane, jeśli wyniki zliczania wydają się nieprawidłowe.

COUNTIF doskonale sprawdza się przy tworzeniu zestawienia częstości. Wpisz każdy unikalny region w komórkach D2, D3 i D4, a następnie napisz jedną funkcję COUNTIF, która odwołuje się do komórki z etykietą, i skopiuj ją w dół.

Każdy wiersz pokaże teraz, ile razy występuje dany region. Dodaj funkcję SUM pod wynikami zliczania — powinna być równa liczbie wierszy danych. To szybka kontrola, czy nic nie zostało pominięte.

Pomysłowe zastosowanie: wykrywanie duplikatów. Jeśli zliczysz, ile razy wartość z A2 występuje w całej kolumnie, a wynik będzie większy niż jeden, oznacza to duplikat.

Owiń tę formułę w IF, aby oznaczyć duplikaty: wynik większy niż 1 oznacza, że wpis się powtarza. To popularny sposób na czyszczenie list przed analizą.

Można już zliczać wiersze spełniające warunek za pomocą funkcji COUNTIF. Należy pamiętać:

• Wzorzec to po prostu range, criteria, bez argumentu sum_range.
• Cudzysłowy ujmują tekst i kryteria oparte na operatorach, takie jak \">90\".
• Operator można połączyć z komórką za pomocą &, jak w wyrażeniu \">=\"&D1.
• Funkcja przydaje się do tworzenia tabel częstości i oznaczania duplikatów.

W następnej części obliczona zostanie średnia tylko z pasujących wartości za pomocą AVERAGEIF.