Funkcja COUNTIFS programu Excel służy do uzyskiwania liczby komórek w jednym lub kilku zakresach, które spełniają zestaw określonych warunków lub kryteriów.
Czasami funkcja COUNTIFS nie działa zgodnie z oczekiwaniami i zwraca wartość #VALUE ! 0 , komunikat wskazujący na błąd w formule lub inną nieoczekiwaną wartość.
Oto kilka powodów, dla których funkcja nie działa zgodnie z oczekiwaniami w programie Microsoft Excel:
- Gdy formuła odwołuje się do zakresu w skoroszycie, który jest zamknięty.
- Użycie wartości z innej komórki bez konkatenacji z użytym operatorem logicznym.
- Nie należy ujmować kryteriów tekstowych w cudzysłowy (” „).
- Kolejne zakresy nie mają takiej samej liczby kolumn i wierszy jak pierwszy zakres kryteriów.
- Kryteria liczbowe i operator logiczny nie są ujęte w cudzysłów (” „).
- Spróbuj uzyskać liczbę przy użyciu logiki OR.
- Późniejsze zmiany w zestawie danych.
- Błędy w formule.
W tym samouczku wyjaśniamy, w jaki sposób można rozwiązać te przyczyny, aby funkcja zwracała poprawną liczbę, której szukamy.
1. Formuła odwołuje się do zakresu w innym skoroszycie, który jest zamknięty.
Gdy funkcja COUNTIFS odwołuje się do zakresu w innym zamkniętym skoroszycie, zwraca błąd #VALUE! zamiast oczekiwanej liczby komórek.
W poniższym przykładzie zamiast formuły zwracającej liczbę czerwonych produktów został wygenerowany błąd #WARTOŚĆ!, ponieważ formuła odwołuje się do zakresu w innym zamkniętym skoroszycie o nazwie Kolory produktów, który jest zamknięty.
Rozwiązanie
Rozwiązaniem niedziałających kont w tym scenariuszu jest otwarcie zamkniętego skoroszytu i naciśnięcie klawisza F9 na klawiaturze, aby formuła ponownie obliczyła prawidłowe parametry. Uzyskamy wtedy prawidłowe konto, jak pokazano w poniższym przykładzie.
2. Używanie wartości innej komórki bez konkatenacji odwołania do komórki z operatorem logicznym
Jeśli użyjesz operatora logicznego i odwołania do komórki jako kryteriów dla funkcji COUNTIFS w formule i nie połączysz odwołania do komórki z operatorem logicznym, wstawiając & przed odwołaniem do komórki, funkcja zwróci 0 zamiast oczekiwanej liczby.
Ilustruje to poniższy przykład, w którym użyto formuły =COUNTIFS(G2:G6,”>M2″).
Rozwiązanie
W poniższym przykładzie musimy wprowadzić poprawną formułę =COUNTIFS(G2:G6,”> ” &M2) w komórce I3. Po naciśnięciu ENTER otrzymamy poprawną liczbę 4.
W poprawnej formule tylko operator logiczny większy niż (>) jest ujęty w cudzysłów (” „), a operator konkatenacji & jest umieszczony przed odwołaniem do komórki M2.
3. Nie umieszczaj cudzysłowów ( ” „) wokół kryteriów tekstowych
Jeśli kryteria tekstowe nie są ujęte w cudzysłowy, funkcja COUNTIFS zwróci wartość 0 zamiast prawidłowej liczby, której oczekujemy, jak pokazano w poniższym przykładzie.
Formuła w komórce J2 ma postać =COUNTIFS(C2:C6,Red). Formuła zwraca 0, ponieważ kryterium Red nie zostało ujęte w cudzysłów (” „).
Rozwiązanie problemu niedziałających kont
Musimy ująć kryteria tekstowe w formule w cudzysłów, jak pokazano w poniższym przykładzie, a wtedy otrzymamy poprawną liczbę 2.
4. Następny zestaw kryteriów nie ma takiego samego kształtu jak pierwszy zestaw kryteriów.
Gdy używamy kilku kryteriów w funkcji COUNTIFS, a dodatkowe opcjonalne zakresy kryteriów nie odpowiadają pierwszemu zakresowi kryteriów, generowany jest błąd #VALUE.
W poniższym przykładzie formuła =COUNTIFS(B2:B13, „Red”,C2:C10, „Microsoft”) została wprowadzona do komórki G3 w celu uzyskania liczby czerwonych produktów dostarczonych przez Microsoft.
Po naciśnięciu klawisza Enter pojawia się błąd #VALUE! jak pokazano poniżej.
Dzieje się tak, ponieważ drugi zakres kryteriów C2:C10 nie ma takiej samej liczby komórek jak pierwszy zakres kryteriów B2:B13.
Rozwiązanie
Aby rozwiązać ten problem, musimy upewnić się, że drugi zakres kryteriów ma taki sam rozmiar jak pierwszy zakres kryteriów, jak pokazano poniżej:
Po naciśnięciu klawisza ENTER otrzymamy prawidłową liczbę 2, jak pokazano poniżej:
5. Nie umieszczaj kryteriów numerycznych i operatorów logicznych w cudzysłowach (” „).
Kiedy używamy operatorów logicznych, takich jak równy (=), większy niż (>), mniejszy niż (<) i nie równy (<>) w formule i nie wstawimy kryteriów liczbowych i operatora logicznego między tymi samymi cudzysłowami, Excel wysyła nam komunikat wskazujący, że formuła zawiera błąd.
W poniższym przykładzie nie umieściliśmy operatora logicznego i kryterium liczbowego między cudzysłowami. Tylko operator logiczny jest ujęty w cudzysłów.
Po naciśnięciu klawisza ENTER otrzymamy następujący komunikat:
Rozwiązanie
Musimy upewnić się, że zarówno operator logiczny, jak i kryterium liczbowe znajdują się w tym samym cudzysłowie, jak pokazano poniżej:
Po naciśnięciu klawisza ENTER otrzymamy poprawną liczbę 5, jak pokazano poniżej:
6. Próba uzyskania liczby przy użyciu logiki OR
Chociaż funkcja COUNTIFS może generować prawidłowe zliczenia przy użyciu logiki AND, generuje tylko wartość 0, gdy próbujemy użyć jej do obliczeń przy użyciu logiki OR.
W poniższym przykładzie próbujemy uzyskać liczbę czerwonych i niebieskich kategorii produktów przy użyciu logiki OR.
Po naciśnięciu klawisza ENTER otrzymujemy nieoczekiwaną wartość 0, jak pokazano poniżej. Dzieje się tak, ponieważ funkcja COUNTIFS nie może wykonywać obliczeń przy użyciu logiki OR.
Rozwiązanie
Możemy obejść ten problem logiki OR, używając funkcji COUNTIFS i SUMA razem.
W poniższym przykładzie wprowadziliśmy formułę =SUMA(COUNTIFS(B2:B13,{„Czerwony”, „Niebieski”})) w komórce G3.
Po naciśnięciu klawisza ENTER otrzymamy poprawną wartość 6, jak pokazano poniżej. Dzieje się tak, ponieważ funkcja COUNTIFS zwróciła 3 liczby czerwonych i 3 liczby niebieskich z zakresu kryteriów B2:B13, a funkcja SUMA dodała te wartości, aby uzyskać 6.
7. Późniejsze zmiany w zbiorze danych
Zmiany w zestawie danych mogą spowodować, że funkcja COUNTIFS nie wygeneruje oczekiwanych wartości. Na przykład w poniższym zestawie danych funkcja COUNTIF działa poprawnie i zwraca prawidłową liczbę niebieskich produktów.
Jeśli jednak nastąpią zmiany w danych, na przykład w tym przypadku dodamy słowo kolor do Niebieski, funkcja COUNTIFS wyświetli teraz nieprawidłową wartość 0.
Rozwiązanie
Sytuacji tej można zaradzić, używając symboli wieloznacznych z funkcją COUNTIF.
W poniższym przykładzie użyliśmy znaku gwiazdki (*) w kryteriach, co pozwala nam uzyskać prawidłową liczbę 4 produktów. Funkcja COUNTIF może teraz wyszukiwać częściowe dopasowania w zakresie kryteriów.
8. Błędy w formule
Czasami funkcja COUNTIF nie działa z powodu błędu w formule, na przykład zapomnienia przecinka oddzielającego zakres kryteriów od operatora logicznego.
W poniższym przykładzie nie ma przecinka między zakresem kryteriów D2:D13 a operatorem logicznym większy niż (>).
Po naciśnięciu klawisza ENTER pojawi się komunikat informujący o błędzie w formule.
Rozwiązanie
Podczas pisania formuł należy zachować szczególną ostrożność, aby upewnić się, że składnia jest poprawna. W poniższym przykładzie wstawienie przecinka pomiędzy zakres kryteriów a operator większy niż (>) rozwiązuje problem i otrzymujemy poprawną liczbę 5.
Podsumowanie
COUNTIFS to bardzo potężna funkcja, która może być używana do generowania liczby komórek w zakresach danych, które spełniają określone warunki.
W tym samouczku przyjrzeliśmy się różnym powodom, dla których funkcja ta nie zawsze działa zgodnie z oczekiwaniami i sposobom ich rozwiązania.