Note: The other languages of the website are Google-translated. Back to English

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.

Wskazówki: 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"}))

Uwagi: 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

Chcesz szybko i perfekcyjnie zakończyć swoją codzienną pracę? Kutools dla programu Excel oferuje 300 zaawansowanych zaawansowanych funkcji (Łącz skoroszyty, sumuj według koloru, dziel zawartość komórek, konwertuj daty itd.) i oszczędzaj 80% czasu.

  • Zaprojektowany dla 1500 scenariuszy pracy, pomaga rozwiązać 80% problemów z Excelem.
  • Zmniejsz tysiące kliknięć klawiatury i myszy każdego dnia, odciąż zmęczone oczy i dłonie.
  • Zostań ekspertem Excela w 3 minuty. Nie musisz już pamiętać żadnych bolesnych formuł i kodów VBA.
  • 30-dniowy nieograniczony bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy. Bezpłatna aktualizacja i wsparcie przez 2 lata.
Wstążka programu Excel (z zainstalowanym Kutools dla programu Excel)

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 zakładki do pakietu Office (w tym Excel), podobnie jak Chrome, Firefox i nowy Internet Explorer.
Zrzut ekranu programu Excel (z zainstalowaną kartą Office)
Sortuj komentarze według
Komentarze (0)
Brak ocen. Oceń jako pierwszy!
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL