Funkcja COUNTIFS, jak korzystać z funkcji liczenia komórek według wielu warunków w programie Excel

Funkcja COUNTIFS w Excelu ma wiele praktycznych zastosowań. Oto, co musisz wiedzieć o korzystaniu z funkcji LICZBY w programie Excel .

Microsoft Excel to znany na świecie program do obsługi arkuszy kalkulacyjnych z wieloma przydatnymi funkcjami. Pojawienie się oprogramowania sprawia, że ​​wprowadzanie danych i obliczanie serii liczb jest prostsze niż kiedykolwiek. A korzystając z Excela na pewno trzeba znać funkcję LICZ .

Funkcja COUNTIFS w programie Excel służy do zliczania komórek spełniających wiele zadanych warunków. Funkcja LICZ.JEŻELI to jedna z najczęściej używanych statystycznych funkcji programu Excel w programie Excel.Jest to zaawansowana funkcja funkcji LICZ.JEŻELI , która zlicza tylko komórki spełniające zadany warunek. Pracując z funkcją COUNTIFS, użytkownicy mogą łatwo znaleźć komórki wynikowe spełniające warunki podane w żądaniu. Warunki mogą być liczbami, datami, tekstem lub komórkami zawierającymi dane. W poniższym artykule dowiesz się, jak korzystać z funkcji LICZBY w programie Excel.

• Jak porównać dane w 2 kolumnach Excela
• Jak używać funkcji DSUM do obliczania sum ze złożonymi warunkami w programie Excel
• Jak używać funkcji SUMIF do obliczania sum warunkowych w programie Excel
• Użycie funkcji WYSZUKAJ.PIONOWO i konkretne przykłady

Instrukcja korzystania z funkcji LICZ. w Excelu

Składnia funkcji COUNTIFS to =COUNTIFS(zakres_kryteriów1, kryteria1, [zakres_kryteriów2, kryteria2],…) .

Tam:

• Kryteria_zakres1 to pierwszy zakres selekcji, który wymaga statystyki, czyli wymaganej wartości.
• Kryteria1 to warunek zastosowany do zakresu_kryteriów wyboru1, wymaganą wartością może być komórka, wyrażenie lub tekst.
• [zakres_kryteriów2, kryteria2] to dodatkowe pary selekcji i warunków, umożliwiające utworzenie do 127 par selekcji i warunków.

Uwaga dotycząca korzystania z funkcji COUNTIFS:

• Dodatkowe selekcje muszą mieć taką samą liczbę wierszy i kolumn jak obszar kryteria_zakres1 i mogą być od siebie oddzielone.
• Jeśli warunek wyboru odnosi się do pustej komórki, funkcja COUNTIFS automatycznie uznaje wartość za 0.
• Można używać znaków ?. Aby zastąpić określony znak, znak * zastępuje cały ciąg znaków. Jeśli chcesz znaleźć znak? lub prawdziwy znak *, wpisz ~ przed tym znakiem.
• Należy pamiętać, że w funkcji COUNTIFS nie jest rozróżniana wielkość liter.
• Ogólnie rzecz biorąc, wartości tekstowe muszą być ujęte w cudzysłów, ale liczby nie. Jeśli jednak do liczby dołączony jest operator logiczny, liczbę i operator należy ująć w cudzysłów, jak pokazano poniżej:

=COUNTIFS(A1:A10,100) // count equal to 100
=COUNTIFS(A1:A10,">50") // count greater than 50
=COUNTIFS(A1:A10,"jim") // count equal to "jim"

Uwaga : Dodatkowe warunki muszą spełniać te same zasady.

• Jeśli w warunku używana jest wartość z innej komórki, odwołanie do komórki musi zostać połączone z operatorem. W poniższym przykładzie COUNTIFS zliczy wartości w A1:A10, które są mniejsze niż wartość w komórce B1. Zwróć uwagę, że operator mniej niż (który jest tekstem) jest ujęty w cudzysłów, ale odwołanie do komórki nie jest potrzebne:

=COUNTIFS(A1:A10,"<"&b1) count="" cells="" less="" than="">

Uwaga : COUNTIFS to jedna z kilku funkcji dzielących warunki na dwie części: Zakres + kryteria. Powoduje to pewne niespójności z innymi formułami i funkcjami.

• COUNTIFS może zliczać puste i niepuste komórki. Poniższe formuły zliczają puste i niepuste komórki w zakresie A1:A10:

=COUNTIFS(A1:A10,"<>") // not blank
=COUNTIFS(A1:A10,"") // blank

• Najprostszym sposobem użycia funkcji COUNTIFS z datami jest odwołanie się do prawidłowej daty w innej komórce, która zawiera odwołanie do komórki. Na przykład, aby policzyć komórki w komórce A1:A10, które zawierają datę większą niż data w komórce B1, możesz użyć następującej formuły:

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1

Przykład użycia funkcji COUNTIFS w programie Excel

1. Karta katalogowa nr 1

Poniżej znajduje się tabela danych, w której można przesłać kilka żądań do tabeli.

Przykład 1: Oblicz liczbę pracowników płci męskiej przy 25 dniach roboczych.

W komórce, w której musimy wpisać wynik, wprowadź formułę =COUNTIFS(C2:C7,"Male",D2:D7,25) i naciśnij Enter.

Tam:

• C2:C7 to obowiązkowy obszar zliczany 1 z płcią pracownika.
• Południe jest warunkiem obszaru zliczania 1.
• C2:C7 to drugi obszar zliczania z dniem pracy pracownika.
• 25 to stan regionu nr 2.

W rezultacie mamy 2 pracowników, Nam, z 25 dniami roboczymi.

Przykład 2: Oblicz liczbę pracowników płci męskiej, których dzień wolny wynosi 0.

W polu wprowadzania formuły wprowadź =COUNTIFS(C2:C7,"Nam",E2:E7,0) i naciśnij klawisz Enter.

Wyniki pokazują, że 1 pracownik płci męskiej ma 0 dni wolnych.

Przykład 3: Oblicz liczbę pracowników płci męskiej, którzy mają 25 dni pracy i mają dni wolne krótsze lub równe 2 dni.

Wprowadzamy formułę =COUNTIFS(C2:C7,"Male",D2:D7,25,E2:E7,"<> i następnie wciskamy Enter.

W rezultacie jest 2 pracowników płci męskiej, którzy spełniają wymogi posiadania dni wolnych krótszych lub równych 2 dniom.

2. Karta katalogowa nr 2

Przykład 1: Oblicz łączną liczbę tajwańskich produktów o cenie jednostkowej mniejszej niż 200 000 VND.

W polu wprowadzania wyników wprowadzamy formułę obliczeniową =COUNTIFS(B2:B7,”*Taiwan”,C2:C7,”<> i następnie wciskamy Enter.

W rezultacie powstały 2 tajwańskie produkty, które spełniły wymagania.

Przykład 2: Oblicz łączną liczbę tajwańskich produktów o cenach jednostkowych od 100 000 do 150 000 VND.

W polu wprowadzania wyników wprowadź formułę =COUNTIFS(B2:B7,”*Taiwan”,C2:C7,”>100000”,C2:C7,”<> i naciśnij Enter.

W rezultacie dostępne będą 2 tajwańskie produkty w cenie od 100 000 do 150 000 VND.

Jak korzystać z funkcji COUNTIFS w połączeniu z SUMA w Excelu

Funkcja COUNTIFS jest zasadniczo bardziej złożoną wersją funkcji COUNTIFS. Główną przewagą funkcji COUNTIFS nad funkcją COUNTIF jest to, że obsługuje wiele warunków i zakresów.

Można jednak także zdefiniować zakres i pojedynczy warunek dla funkcji LICZ.JEŻELI, podobnie jak w przypadku funkcji LICZ.JEŻELI.

Przed użyciem funkcji COUNTIFS należy zrozumieć jedną ważną rzecz: służy ona do czegoś więcej niż tylko sumowania wyników komórek spełniających kryteria dla każdego zakresu komórek.

W efekcie, jeśli masz dwa warunki dla dwóch zakresów, komórki w pierwszym zakresie są filtrowane dwukrotnie: raz przez pierwszy warunek, a następnie przez drugi warunek. Oznacza to, że funkcja COUTNIFS zwróci tylko wartości spełniające oba warunki, w ramach ich podanego zakresu.

Funkcjonalność funkcji COUNTIFS można lepiej zrozumieć, zapoznając się z poniższym przykładem.

Przykład funkcji COUNITFS w Excelu

Oprócz koloru jabłek znajduje się także kolumna opisująca ich wielkość. Ostatecznym celem w tym przykładzie jest policzenie dużych czerwonych jabłek.

1. Wybierz komórkę, w której chcesz wyświetlić wyniki. (W tym przykładzie artykuł wyświetli dużą liczbę czerwonych jabłek w komórce E3).

2. Przejdź do paska formuły i wprowadź poniższą formułę:

=COUNTIFS(A3:A11, "Red", B3:B11, "Big")

3. Za pomocą tego polecenia formuła sprawdza komórki od A3 do A11 pod kątem warunku „Czerwony”. Komórki, które przeszły ten test, są następnie ponownie testowane w zakresie B3 do B11 pod kątem warunku „Duży” .

4. Naciśnij przycisk Enter.

5. Teraz Excel policzy liczbę dużych czerwonych jabłek.

Przykładowe wyniki funkcji Excel COUNTIFS

Obserwuj, jak formuła zlicza komórki, które mają zarówno atrybut czerwony , jak i duży. Formuła pobiera komórki od A3 do A11 i testuje je, aby znaleźć czerwony wynik. Komórki, które spełniają ten warunek, są następnie ponownie testowane pod kątem następnego warunku z drugiego zakresu, w tym przypadku dużego warunku.

Podsumowując, zakresy i warunki po pierwszym zakresie i warunku coraz bardziej zawężają filtr zliczający i nie są od siebie niezależne. Tak więc efektem końcowym przepisu są czerwone i duże jabłka. Możesz policzyć liczbę czerwonych lub dużych jabłek, łącząc funkcję LICZ.JEŻELI z funkcją SUMA .

1. Wybierz komórkę, w której chcesz wyświetlić wyniki formuły. (W tym przykładzie w artykule zostanie użyta komórka E6).

2. Wprowadź poniższy wzór:

=SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big"))

3. Ta formuła policzy komórki zawierające czerwone jabłka, następnie liczbę komórek zawierających duże jabłka, a na koniec zsumuje te dwie liczby.

4. Naciśnij przycisk Enter.

5. Excel będzie teraz liczyć i wyświetlać liczbę dużych lub czerwonych jabłek.

Połącz funkcję LICZ.JEŻELI z funkcją SUMA

Powyżej znajdują się 2 tabele danych z wykorzystaniem funkcji COUNTIFS w Excelu. Użytkownicy muszą wpisać warunki towarzyszące obszarowi danych warunku, aby funkcja COUNTIFS mogła je poprawnie określić.

Jak naprawić typowe błędy podczas korzystania z funkcji LICZ. w programie Excel

Funkcja COUNTIFS nie działa podczas zliczania wartości tekstowych

Licząc ciągi tekstowe, należy je umieścić w cudzysłowie. W przeciwnym razie funkcja COUNTIFS nie będzie w stanie policzyć ciągu tekstowego i zwróci wartość 0. Na poniższym przykładowym obrazie ciąg tekstowy nie został umieszczony w cudzysłowie. Dlatego ta formuła zwraca wartość 0.

Aby naprawić ten błąd, wystarczy dokładnie przepisać formułę: =COUNTIFS(E5:E12, „Car”)

COUNTIFS nie działa, ponieważ odwołanie do zakresu jest nieprawidłowe

W przypadku użycia więcej niż jednego kryterium w funkcji COUNTIFS zakres komórek dla pozostałych kryteriów musi obejmować tę samą liczbę komórek. W przeciwnym razie funkcja COUNTIFS nie będzie działać.

Załóżmy, że w tym przykładzie chcemy policzyć sprzedaż samochodów w Austin. Wprowadzona formuła to = LICZBY (E5:E12, „Samochód”, D5: D11, „Austin”). Przyglądając się bliżej formule, zobaczysz, że pierwszy zakres kryteriów to E5:E12, ale drugi zakres kryteriów to D5:D11. Liczba komórek w zakresie dla tego kryterium nie jest taka sama.

Teraz naciśnij Enter , formuła zwróci #WARTOŚĆ! .

Przepisz formułę poprawnie w następujący sposób: = LICZBY (E5:E12, „Samochód”, D5: D12, „Austin”)

Funkcja COUNTIFS nie działa z powodu błędu w formule

Jeżeli formuła nie zostanie wstawiona poprawnie, funkcja LICZ. W przypadku użycia dowolnego operatora matematycznego, takiego jak większy niż (>), mniejszy niż (<), równy="" (=")" i "not="" równa się=""> <> ), zarówno operator, jak i kryterium numeryczne należy wpisać w tym samym równaniu. Na przykład, jeśli chcesz znaleźć sprzedaż większą niż 100 000 USD, wstaw następującą formułę:

=COUNTIFS(F5:F12,">" 100000)

Tutaj do równania wstawiany jest tylko operator, nie ma kryterium numerycznego.

Jeśli naciśniesz klawisz Enter, zostanie wyświetlone okno dialogowe programu Microsoft Excel z komunikatem: „Wystąpił problem z tą formułą”.

Naprawić problem. Wpisz dokładną formułę:

=COUNTIFS(F5:F12,">100000")

Teraz wprowadziliśmy zarówno operator, jak i kryteria w nawiasach. Teraz ta formuła zwróci wymaganą ilość.

Naciśnij enter.

W rezultacie otrzymasz kwotę sprzedaży większą niż 100 000 USD.

Życzę powodzenia!