Przejdź do głównej zawartości

Liczniki z logiką OR dla wielu kryteriów w programie Excel

Zwykle można użyć funkcji LICZ.WARUNKI, aby zliczyć komórki na podstawie jednego lub więcej warunków z logiką ORAZ w programie Excel. Czy kiedykolwiek doświadczyłeś sytuacji, w której musisz policzyć więcej niż jedną wartość z jednej kolumny lub zakresu komórek? Oznacza to liczenie z wieloma warunkami i logiką OR. W takim przypadku można zastosować razem funkcje SUMA i LICZ.WARUNKI lub użyć funkcji SUMA.


Policz komórki z warunkami OR w Excelu

Na przykład mam zakres danych, jak pokazano na poniższym zrzucie ekranu, teraz chcę policzyć numer produktu, którym jest „Ołówek” lub „Linijka”, tutaj omówię dwie formuły rozwiązywania tego zadania w programie Excel.

Policz komórki z warunkami LUB przy użyciu funkcji SUMA i LICZ.WARUNKI

W programie Excel, aby policzyć z wieloma warunkami LUB, możesz użyć funkcji SUMA i LICZ.WARUNKI ze stałą tablicową, ogólna składnia to:

=SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))
  • range: Zakres danych zawiera kryteria, z których liczysz komórki;
  • criterion1, criterion2, criterion3…: warunki, na podstawie których chcesz zliczać komórki.

Aby policzyć liczbę produktów, które są „Ołówek” lub „Linijka”, skopiuj lub wprowadź poniższą formułę do pustej komórki, a następnie naciśnij Wchodzę klucz do uzyskania wyniku:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"}))


Wyjaśnienie wzoru:

=SUMA(LICZ.WARUNKI(B2:B13;{"Ołówek";"Linijka"}))

  • {"Ołówek","Linijka"}: Najpierw powinieneś upakować wszystkie warunki w stałej tablicy w następujący sposób: {"Ołówek","Linijka"}, oddziel elementy przecinkami.
  • LICZ.WARUNKI(B2:B13;{"Ołówek","Linijka"}): Ta funkcja LICZ.WARUNKI uzyska indywidualne liczby dla „Ołówek” i „Linijka”, a wynik będzie następujący: {2,3}.
  • SUMA(LICZ.WARUNKI(B2:B13;{"Ołówek","Linijka"}))=SUMA({2,3}): Na koniec ta funkcja SUMA sumuje wszystkie elementy w tablicy i zwraca wynik: 5.

Tips: Możesz również użyć odwołań do komórek dla kryteriów, zastosuj poniższą formułę tablicową, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać poprawny wynik:

=SUM(COUNTIF(B2:B13,D2:D3))


Policz komórki z warunkami OR za pomocą funkcji SUMPRODUCT

Oto kolejna formuła tworzona przez funkcję SUMPRODUCT, która może również pomóc zliczać komórki za pomocą logiki OR. Ogólna składnia to:

=SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))
  • range: Zakres danych zawiera kryteria, z których liczysz komórki;
  • criterion1, criterion2, criterion3…: warunki, na podstawie których chcesz zliczać komórki.

Skopiuj lub wprowadź następującą formułę do pustej komórki i naciśnij Wchodzę klucz do zwrócenia wyniku:

=SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))


Wyjaśnienie wzoru:

=PROD.SUMA(1*(B2:B13={"Ołówek","Linijka"}))

  • B2:B13={"Ołówek","Linijka"}: To wyrażenie porównuje każde kryterium „Ołówek”, „Linijka” z komórką zakresu B2:B13. Jeśli kryterium jest spełnione, zwraca TRUE, w przeciwnym razie wyświetli się FALSE, otrzymasz następujący wynik: {PRAWDA,FAŁSZ;FAŁSZ,FAŁSZ;FAŁSZ,FAŁSZ;FAŁSZ,PRAWDA;FAŁSZ,FAŁSZ;PRAWDA,FAŁSZ ;FAŁSZ,FAŁSZ;FAŁSZ,PRAWDA;FAŁSZ,FAŁSZ;FAŁSZ,FAŁSZ;FAŁSZ,PRAWDA;FAŁSZ,FAŁSZ}.
  • 1*(B2:B13={"Ołówek","Linijka"}): Mnożenie konwertuje wartości logiczne – TRUE i FALSE na 1 i 0, więc otrzyma wynik taki: {1,0;0,0;0,0;0,1;0,0;1,0;0,0 0,1;0,0;0,0;0,1;0,0;XNUMX}.
  • SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): W końcu funkcja SUMPRODUCT sumuje wszystkie liczby w tablicy, aby uzyskać wynik: 5.

Policz komórki z wieloma zestawami warunków OR w programie Excel

Czasami może być konieczne zliczenie komórek z co najmniej dwoma zestawami warunków LUB, w takim przypadku można użyć opcji SUMA i LICZ.WARUNKI ze stałą tablicową lub SUMAPRODUKT z funkcjami PODAJ.LICZB.

Policz komórki z dwoma zestawami warunków LUB za pomocą funkcji SUMA i LICZ.WARUNKI

Aby poradzić sobie tylko z dwoma zestawami kryteriów LUB, wystarczy dodać kolejną stałą tablicową do formuły LICZ.WARUNKI.

Na przykład mam zakres danych pokazany na poniższym zrzucie ekranu, teraz chcę policzyć osoby, które zamówiły „Ołówek” lub „Linijka”, a kwota wynosi <100 lub >200.

Wprowadź lub skopiuj następującą formułę do komórki, a następnie naciśnij Wchodzę klucz, aby uzyskać wynik, patrz zrzut ekranu:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"},C2:C13,{"<100";">200"}))

Note: W formule należy użyć średnika dla drugiej stałej tablicowej, która tworzy tablicę pionową.


Policz komórki z wieloma zestawami warunków OR za pomocą funkcji SUMPRODUCT

Powyższa formuła działa tylko dla dwóch zestawów kryteriów LUB, jeśli musisz liczyć z większą liczbą kryteriów, może ci pomóc złożona formuła SUMPRODUCT wraz z funkcjami ISNUMBER MATCH.

Weźmy na przykład poniższe dane, aby policzyć osoby, które zamówiły „Ołówek” lub „Linijka”, a Status to „Dostarczono” lub „W transporcie” i podpisano przez „Bob” lub „Eko”, należy zastosować złożoną formułę .

Skopiuj lub wprowadź poniższą formułę do pustej komórki, a następnie naciśnij Wchodzę klucz, aby uzyskać obliczenia, zobacz zrzut ekranu:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))


Wyjaśnienie wzoru:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))

CZY.LICZBA(DOPASUJ(B2:B13;{"Ołówek","Linijka"},0)):

  • PODAJ.POZYCJĘ(B2:B13;{"Ołówek","Linijka"},0): Ta funkcja PODAJ.POZYCJĘ służy do porównywania każdej komórki w zakresie B2:B13 z odpowiednią stałą tablicową. Jeśli dopasowanie zostanie znalezione, zwraca względną pozycję wartości w tablicy, w przeciwnym razie wyświetlana jest wartość błędu. Tak więc otrzymasz następującą listę tablic: {1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;# nie dotyczy}.
  • ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}):Funkcja CZY.LICZBA konwertuje liczby na PRAWDA, a wartości błędów na FAŁSZ w następujący sposób: {PRAWDA;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;PRAWDA;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ;PRAWDA;FAŁSZ}.

Powyższą logikę można również zastosować do drugiego i trzeciego wyrażenia ISNUMBER.

SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):

  • Następnie te trzy tablice są mnożone razem wewnątrz SUMPRODUCT, który automatycznie konwertuje wartości TRUE i FALSE na 1 i 0 w ramach operacji matematycznej w następujący sposób: SUMPRODUCT({1;0;0;1;0;1;0;1; 1;0;1;0}*{1;1;0;0;1;1;0;1;1;0;1;1} *{1;0;0;0;0;0;0; 1;0;0;1;0})=PROD.SUMA({1;0;0;0;0;0;0;1;0;0;1;0}).
  • W końcu funkcja SUMPRODUCT sumuje wszystkie liczby w tablicy, aby uzyskać wynik: 3.

Zastosowana funkcja względna:

  • SUMA:
  • Funkcja Excel SUMA zwraca sumę podanych wartości.
  • LICZ.JEŻELI:
  • Funkcja LICZ.JEŻELI to funkcja statystyczna w programie Excel, która służy do zliczania liczby komórek spełniających kryterium.
  • SUMA PRODUKTU:
  • Funkcja SUMPRODUCT może służyć do mnożenia dwóch lub więcej kolumn lub tablic, a następnie do uzyskania sumy iloczynów.
  • CZY.NUMER:
  • Funkcja Excel CZY.LICZBA zwraca PRAWDA, jeśli komórka zawiera liczbę, a FAŁSZ, jeśli nie.
  • MECZ:
  • Funkcja Microsoft Excel MATCH wyszukuje określoną wartość w zakresie komórek i zwraca względną pozycję tej wartości.

Więcej artykułów:

  • Policz unikalne wartości liczbowe na podstawie kryteriów
  • W arkuszu programu Excel może wystąpić problem polegający na zliczaniu unikalnych wartości liczbowych na podstawie określonego warunku. Na przykład, jak mogę policzyć unikalne wartości ilości produktu „T-shirt” z raportu, jak pokazano na zrzucie ekranu? W tym artykule pokażę kilka formuł, aby osiągnąć to zadanie w Excelu.
  • Policz liczbę wierszy z wieloma kryteriami OR
  • Aby policzyć liczbę wierszy z wieloma kryteriami w różnych kolumnach, za pomocą logiki OR, funkcja SUMPRODUCT może ci pomóc. Na przykład mam raport o produkcie, jak pokazano na poniższym zrzucie ekranu, teraz chcę policzyć wiersze, w których produkt to „T-shirt” lub kolor „Czarny”. Jak poradzić sobie z tym zadaniem w programie Excel?

Najlepsze narzędzia biurowe

Kutools dla programu Excel - pomaga wyróżnić się z tłumu

🤖 Pomocnik AI Kutools: Zrewolucjonizuj analizę danych w oparciu o: Inteligentne wykonanie   |  Wygeneruj kod  |  Twórz niestandardowe formuły  |  Analizuj dane i generuj wykresy  |  Wywołaj funkcje Kutools...
Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty  |  Usuń puste wiersze  |  Łącz kolumny lub komórki bez utraty danych  |  Okrągły bez wzoru ...
Super VLookup: Wiele kryteriów  |  Wiele wartości  |  W wielu arkuszach  |  Wyszukiwanie rozmyte...
Adw. Lista rozwijana: Łatwa lista rozwijana  |  Zależna lista rozwijana  |  Lista rozwijana wielokrotnego wyboru...
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  Porównaj kolumny z Wybierz Te same i różne komórki ...
Polecane funkcje: Fokus siatki  |  Widok projektu  |  Duży pasek formuły  |  Menedżer skoroszytów i arkuszy | Biblioteka zasobów (Automatyczny tekst)  |  Selektor dat  |  Połącz arkusze  |  Szyfruj/odszyfruj komórki  |  Wysyłaj e-maile według listy  |  Super filtr  |  Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi12 Tekst Tools (Dodaj tekst, Usuń znaki ...)  |  50 + Wykres rodzaje (Wykres Gantta ...)  |  40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin ...)  |  19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki ...)  |  12 Konwersja Tools (Liczby na słowa, Przeliczanie walut ...)  |  7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podziel komórki Excela ...)  |  ... i więcej

Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...

Opis


Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)

  • Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
  • Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
  • Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
  • Wprowadza wydajne karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations