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.
Ogólna formuła SUMPRODUCT do liczenia z wieloma kryteriami
| =PROD.SUMA(--(((kryterium1)+(kryterium2)+… )>0) |
Kryteria1: Jest to dowolne kryterium, które zwraca tablicę TRUE i FALSE.
Kryteria2: To jest kolejne kryterium, 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łę.
Problem:
Cenę w komórce należy dokładnie podzielić na 5 podobnych płatności. Dzieląc po prostu cenę 88,04 USD przez 5 i zaokrąglając wynik do dwóch miejsc po przecinku, otrzymujemy indywidualne płatności w wysokości 17,61 USD. To jednak sumuje się do całkowitej płatności w wysokości 88,05 USD, co nie jest dokładną kwotą, od której zaczynamy. Dlatego chcemy obliczyć 5 podobnych liczb, które sumują się do całkowitej kwoty.
Rozwiązanie:
Najpierw oblicz pierwszą płatność dzieląc cenę przez 5 i zaokrąglając wynik.
Użyj funkcji ZAOKR w następującej formule:
=OKRĄG(B1/5,2)
Krok 2:
Oblicz każdą pozostałą płatność.
Użyj funkcji ZAOKR, SUMA i WIERSZ w następującej formule:
=OKRĄG(($B$1-SUMA($B$2:B2))/(5-WIERSZ()+WIERSZ($B$2)),2)
Przykład:
Policz użytkowników, jeśli kod dealera pasuje lub rok za pomocą 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 jedno i drugie (kod jako „inka” i rok 2016) powinien być liczony jako 1.
Posłuchaj więc, że mamy 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. A te rekordy są liczone jako 1. I tak otrzymujemy 7.
Zobaczmy, 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.
-
- =SUMPROD.(--(((Kod=I3)+(Rok=K3))>0))
- SUMA PRODUKT(--(({PRAWDA;FAŁSZ;PRAWDA;PRAWDA;PRAWDA;PRAWDA;… }+{FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;…})>0))
- SUMA PRODUKT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMA PRODUKT(--({PRAWDA;FAŁSZ;PRAWDA;PRAWDA;PRAWDA;PRAWDA;PRAWDA;… })
- SUMA PRODUKT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
Znaki podwójnego minusa (--) służą 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.
Dodawanie większej liczby kryteriów do zliczania za pomocą SUMPRODUCT, więc jeśli chcesz dodać więcej lub kryteria do zliczenia, 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 z zakresu Code. 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 ustawienia liczby kryteriów 3 LUB będzie następująca:
| =SUMPRODUCT(--(((Kod=I2:J2)+(Rok=I3:J3))>0)) |
Jest to formuła SUMPRODUCT, która jest liczona z wieloma kryteriami, gdy wiele kryteriów z jednego zakresu jest zapisanych w wierszu.
Powyższa formuła zwraca 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 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 pojawia się ten błąd, ponieważ powoduje to, że ten artykuł jest 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.
Oto wszystkie uwagi obserwacyjne dotyczące stosowania wzoru.
Uwagi:
- Formuła działa zarówno dla tekstu, jak i liczb.
- Wartości wyszukiwania można podać jako odwołanie do komórki lub bezpośrednio za pomocą symbolu cudzysłowu ( " ) w formule jako argumentów.
Mam nadzieję, że rozumiesz, jak podzielić kwotę na równe płatności, unikając błędów podziału / zaokrąglania w programie Excel. Zapoznaj się z dodatkowymi artykułami na temat wartości wyszukiwania programu Excel i funkcji programu Excel 2019 tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.
Użyj INDEKSU i DOPASUJ, aby wyszukać wartość : Funkcja INDEX & MATCH do wyszukiwania wartości zgodnie z wymaganiami.
Zakres SUMA z INDEKSEM w Excelu : Użyj funkcji INDEX, aby znaleźć SUMA wartości zgodnie z wymaganiami.
Jak korzystać z funkcji INDEKS w programie Excel? : Znajdź indeks tablicy za pomocą funkcji INDEX wyjaśnionej na przykładzie.
Jak korzystać z funkcji PODAJ.POZYCJĘ w programie Excel : Znajdź pozycję PODAJ.POZYCJĘ w tablicy przy użyciu wartości INDEX wewnątrz funkcji PODAJ.POZYCJĘ wyjaśnionej na przykładzie.
Jak korzystać z funkcji WYSZUKAJ w programie Excel : Znajdź wartość wyszukiwania w tablicy za pomocą funkcji WYSZUKAJ wyjaśnionej na przykładzie.
Popularne artykuły :
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie 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 funkcji SUMIF w programie Excel? : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : 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.