Jak wspomniałem na wielu moich blogach, SUMPRODUCT jest bardzo wszechstronną funkcją i może być używany do wielu celów. W tym artykule zobaczymy, jak możemy użyć tej funkcji do zliczania wartości z wieloma kryteriami OR.
Ogólna formuła SUMPRODUCT do liczenia z wieloma kryteriami
| =PRODUKCJASUM(--(((kryteria1)+(kryteria2)+… )>0) |
Kryteria1: Jest to dowolne kryterium, które zwraca tablicę TRUE i FALSE.
Kryteria2: To kolejne kryteria, które chcesz sprawdzić. Podobnie możesz mieć tyle kryteriów, ile chcesz.
Powyższa ogólna formuła jest często modyfikowana w celu dostosowania do wymagań dotyczących liczenia z wieloma kryteriami OR. Ale podstawowa formuła jest taka. Najpierw zobaczymy, jak to działa na przykładzie, a następnie omówimy inne scenariusze, w których trzeba będzie trochę zmodyfikować tę formułę.
Przykład: Policz użytkowników, jeśli kod dealera lub rokmecze Korzystanie z SUMPRODUCT
Mamy więc zestaw danych sprzedawców. Dane zawierają wiele kolumn. Musimy policzyć liczbę użytkowników, którzy mają kod „INKA” lub rok to „2016”. Upewnij się, że jeśli ktoś ma oba (kod jako "inka" i rok 2016) to powinno być liczone jako 1.
Tak więc mamy tutaj dwa kryteria. Używamy wyżej wymienionej formuły SUMPRODUCT:
| =SUMPRODUCT(--(((Kod=I3)+(Rok=K3))>0)) |
Tutaj kod i rok są nazwanymi zakresami.
To zwraca 7.
W danych mamy 5 rekordów kodu INKA i 4 rekordy roku 2016. Ale 2 rekordy mają odpowiednio "INKA" i 2016 jako kod i rok. Te rekordy są liczone jako 1. I tak otrzymujemy 7.
Jak to działa?
Przyjrzyjmy się więc krok po kroku, jak formuła jest rozwiązywana, a następnie omówię, jak to działa.
| =SUMA PRODUKT(--(((Kod=I3)+(Rok=K3))>0)) |
| 1=>SUMA PRODUKT(--(({PRAWDA;FAŁSZ;PRAWDA;PRAWDA;PRAWDA;PRAWDA;… }+{FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;…})>0)) |
| 2=>SUMA PRODUKT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SUMA PRODUKT(--({PRAWDA;FAŁSZ;PRAWDA;PRAWDA;PRAWDA;PRAWDA;PRAWDA;… }) |
| 4=>SUMA PRODUKT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
W pierwszym kroku porównywana jest wartość I3 („INKA”) z każdą komórką z zakresu kodu. Zwraca tablicę TRUE i FALSE. TRUE dla każdego meczu. Aby zaoszczędzić miejsce, nie pokazałem wszystkich PRAWDA-FAŁSZ. Podobnie wartość K3 (2016) jest dopasowywana do każdej komórki w zakresie lat.
W następnym kroku dodamy te dwie tablice, co spowoduje powstanie nowej tablicy wartości liczbowych. Jak być może wiesz, PRAWDA jest traktowane jako 1, a FAŁSZ jako 0 w Excelu. Więc kiedy dodamy PRAWDA i PRAWDA, otrzymamy 2 i resztę można zrozumieć.
W kolejnym kroku sprawdzamy, która wartość w tablicy jest większa od 0. To ponownie przekształca tablicę w prawdziwą tablicę fałszywą. Dla każdej otrzymanej wartości 0, False i reszta są konwertowane jako prawdziwe. Teraz naszą odpowiedzią jest liczba wartości TRUE w tablicy. Ale jak je liczymy? Oto jak.
Podwójne znaki ujemne (--) są używane do konwersji wartości logicznych na jedynki i zera. Tak więc każda wartość TRUE w tablicy jest konwertowana na 1, a FALSE na 0.
W ostatnim kroku SUMPRODUCT sumuje tę tablicę i otrzymujemy odpowiedź jako 7.
Dodawanie większej liczby kryteriów do zliczania za pomocą SUMPRODUCT
Więc jeśli potrzebujesz dodać więcej lub kryteria do zliczania, możesz po prostu dodać kryteria za pomocą znaku + do funkcji.
Na przykład, jeśli chcesz dodać kolejne kryteria do powyższej formuły, aby dodać liczbę pracowników, którzy sprzedali więcej niż 5 produktów. Formuła SUMPRODUCT będzie wyglądać po prostu tak:
| =SUMPRODUCT(--(((Kod=I3)+(Rok=K3)+(Sprzedaż>5))>0)) |
Prosty! prawda?
Ale powiedzmy, że chcesz mieć dwa kryteria od Kod zasięg. Powiedzmy, że chcesz policzyć „INKB”. Więc jak to robisz? Jedną z metod jest użycie powyższej techniki, ale byłoby to powtarzalne. Powiedzmy, że chcę dodać 10 dodatkowych kryteriów z tego samego zakresu. W takich przypadkach ta technika nie jest tak sprytna do liczenia za pomocą SUMPRODUCT.
Powiedzmy, że mamy dane ułożone w ten sposób.
Kody kryteriów znajdują się w jednym wierszu I2:J2. Ważny jest tutaj układ danych. Formuła SUMPRODUCT dla ustawień liczby kryteriów 3 LUB będzie następująca:
| =SUMPRODUCT(--(((Kod=I2:J2)+(Rok=I3:J3))>0)) |
Jest to formuła SUMPRODUCT liczona z wieloma kryteriami, gdy wiele kryteriów z jednego zakresu jest zapisanych w jednym wierszu.
Zwraca to poprawną odpowiedź, która wynosi 10.
Jeśli wpiszesz dowolny rok w J3, formuła również doda tę liczbę.
Jest to używane, gdy kryteria znajdują się w jednym wierszu. Czy zadziała, gdy kryteria znajdują się w jednej kolumnie dla tego samego zakresu? Nie. Nie będzie.
W tym przykładzie mamy do zliczenia wiele kodów, ale te typy kodów są zapisane w jednej kolumnie. Kiedy używamy powyższej formuły SUMPRODUCT, otrzymujemy błąd #N/A. Nie dowiemy się, jak doszło do tego błędu, ponieważ spowoduje to, że ten artykuł będzie zbyt długi. Zobaczmy, jak możemy sprawić, by to zadziałało.
Aby ta formuła działała, musisz opakować kryteria kodu w funkcję TRANSPONUJ. Dzięki temu formuła będzie działać.
| =SUMPRODUCT(--(((Kod=PRZEKAŻ(H3:H4))+(Rok=PRZEKAŻ(I3:I4)))>0)) |
Jest to wzór na zliczanie z wieloma warunkami lub warunkami w tym samym zakresie, gdy kryteria są wymienione w kolumnie.
Więc tak kolego, mam nadzieję, że byłem wystarczająco jasny i to miało sens. Mam nadzieję, że służy to twojemu celowi bycia tutaj. Jeśli ta formuła nie rozwiązała Twojego problemu, daj mi znać swoje wymagania w sekcji komentarzy poniżej. Z chęcią pomogę Ci w jakikolwiek sposób. Możesz wspomnieć o wszelkich wątpliwościach związanych z Excelem/VBA. Do tego czasu ucz się dalej, doskonal się.
Jak korzystać z funkcji SUMA PRODUKT w programie Excel: Zwraca SUMA po pomnożeniu wartości w wielu tablicach w programie Excel. Ta funkcja może być używana do wykonywania wielu zadań. To jedna z najbardziej wszechstronnych funkcji.
LICZ.IFS z zakresem kryteriów dynamicznych : Aby policzyć z dynamicznym zakresem kryteriów, po prostu używamy funkcji ADR.POŚR. Ta funkcja może
LICZNIKI Z LUB dla wielu kryteriów : Policz komórki, które mają wiele kryteriów zgodnych za pomocą funkcji LUB. Aby umieścić logikę LUB w funkcji LICZ.WARUNKI, nie trzeba używać funkcji LUB.
Używanie funkcji JEŻELI z funkcjami AND / LUB w programie Microsoft Excel : Te funkcje logiczne są używane do wykonywania obliczeń wielokryterialnych. Z IF funkcje OR i AND służą do uwzględniania lub wykluczania dopasowań.
Jak korzystać z funkcji OR w programie Microsoft Excel : Funkcja służy do uwzględnienia wszystkich wartości TRUE w wielu kryteriach.
Jak liczyć komórki zawierające to lub to w programie Excel w programie Excel? :Do komórek, które zawierają to lub tamto, możemy użyć funkcji SUMA PRODUKT. Oto jak wykonujesz te obliczenia.
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO programu Excel| Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z Excela LICZ.JEŻELI Funkcja| Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.
