W tym artykule dowiemy się, jak używać funkcji JEŻELI zamiast funkcji SUMPRODUCT i SUMIFS w programie Excel.
Scenariusz:
W prostych słowach, pracując z długimi rozproszonymi zbiorami danych, czasami musimy znaleźć sumę liczb z pewnymi kryteriami. Na przykład, znalezienie sumy wynagrodzeń w określonym dziale lub posiadanie wielu kryteriów dla daty, nazwisk, działu lub nawet liczb danych, takich jak pensje poniżej wartości lub ilość powyżej wartości. W tym celu zwykle używasz funkcji SUMA PRODUKT lub SUMA. Ale nie uwierzyłbyś, że wykonujesz tę samą funkcję z podstawową funkcją Excela funkcja JEŻELI.
Jak rozwiązać problem?
Musisz pomyśleć, jak to jest możliwe, aby wykonywać operacje logiczne na tablicach tabel przy użyciu funkcji IF. Funkcja IF w Excelu jest bardzo przydatna, przeprowadzi Cię przez trudne zadania w Excelu lub innych językach kodowania. Funkcja IF testuje warunki na tablicy odpowiadającej wymaganym wartościom i zwraca wynik jako tablicę odpowiadającą warunkom True jako 1 i False jako 0.
W przypadku tego problemu użyjemy następujących funkcji:
- Funkcja SUMA
- JEŚLI funkcja
Będziemy wymagać tych powyższych funkcji i podstawowego poczucia obsługi danych. warunki logiczne na tablicach można stosować za pomocą operatorów logicznych. Te operatory logiczne działają zarówno na tekście, jak i na liczbach. Poniżej znajduje się ogólna formuła. { } nawiasy klamrowe to magiczne narzędzie do wykonywania formuł tablicowych z funkcją JEŻELI.
Wzór ogólny:
| { = SUMA ( JEŻELI ( (logiczne_1) * (logiczne_2) *… * (logiczne_n) , suma_tablica ) ) } |
Uwaga: w przypadku nawiasów klamrowych ( { } ) Posługiwać się Ctrl + Shift + Enter podczas pracy z tablicami lub zakresami w programie Excel. Spowoduje to domyślnie generowanie nawiasów klamrowych we wzorze. NIE próbuj na stałe kodować znaków nawiasów klamrowych.
Logiczne 1 : testuje warunek 1 na tablicy 1
Logiczne 2 : testuje warunek 2 na tablicy 2 i tak dalej
sum_array : tablica, wykonywana jest suma operacji
Przykład :
Wszystko to może być trudne do zrozumienia. Przetestujmy więc tę formułę, uruchamiając ją na poniższym przykładzie. Tutaj mamy dane o dostarczonych produktach do różnych miast wraz z odpowiednimi polami kategorii i ilościami. Tutaj mamy dane i musimy znaleźć ilość ciasteczek wysyłanych do Bostonu, gdzie ilość jest większa niż 40.
Tabela danych i tabela kryteriów są pokazane na powyższym obrazku. Dla zrozumienia celu użyliśmy nazwanych zakresów dla używanych tablic. Nazwane zakresy są wymienione poniżej.
Tutaj :
Miasto zdefiniowane dla tablicy A2:A17.
Kategoria zdefiniowana dla tablicy B2:A17.
Ilość zdefiniowana dla szyku C2:C17.
Teraz jesteś gotowy, aby uzyskać pożądany wynik, korzystając z poniższej formuły.
Użyj wzoru:
| { = SUMA ( JEŻELI ( (Miasto="Boston") * (Kategoria="Pliki cookie") * (Ilość>40) , Ilość)) } |
Wyjaśnienie :
- Miasto ="Boston" : sprawdza wartości w zakresie miast, aby pasowały do "Boston".
- Category="Cookies" : sprawdza wartości w zakresie kategorii w celu dopasowania do "Cookies".
- Ilość > 40 : sprawdza wartości w zakresie ilości do ma
- Ilość w tablicy, w której wymagana jest suma.
- Funkcja JEŻELI sprawdza wszystkie kryteria, a znak gwiazdki (*) mnoży wszystkie wyniki tablicy.
= SUMA (JEŻELI ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}; {33; 87; 58; 38; 54; 51; 28 ; 36 ; 28 ; 44 ; 23 ; 27 ; 43 ; 42 ; 33 ; 30 } ) )
- Teraz funkcja JEŻELI zwraca tylko ilości odpowiadające jedynkom, a reszta jest ignorowana.
- Funkcja SUMA zwraca SUMA.
Teraz ilość odpowiadająca 1 sumuje się tylko w celu uzyskania wyniku.
Jak widać, zwracana jest ilość 43, ale do „Bostonu” dostarczane są trzy zamówienia ciasteczek o ilości 38, 36 i 43. Potrzebowaliśmy sumy ilości, gdy ilość przekracza 40. Zatem formuła zwraca tylko 43. Teraz użyj innych kryteriów, aby uzyskać sumę ilościową dla miasta: „Los Angeles” i kategorię: „Bary” i ilość mniejszą niż 50.
Użyj formuły
| { = SUMA ( JEŻELI ( ( Miasto = "Los Angeles") * (Kategoria="Bary") * (Ilość < 50), Ilość ) ) } |
Jak widać, formuła zwraca w rezultacie wartości 86. Jest to suma 2 zamówień spełniających warunki o ilości 44 i 42. W tym artykule przedstawiono sposób zastępowania zagnieżdżonej formuły JEŻELI pojedynczym elementem JEŻELI w formule tablicowej. Można to wykorzystać do zmniejszenia złożoności złożonych formuł. Jednak ten konkretny problem można łatwo rozwiązać za pomocą funkcji SUMIFS lub SUMPRODUCT.
Użycie funkcji SUMA PRODUKT:
Funkcja SUMPRODUCT zwraca sumę odpowiednich wartości w tablicy. Tak więc otrzymamy tablice, które zwrócą jedynki jako wartości instrukcji True, a zer jako wartości instrukcji False. Tak więc ostatnia suma będzie odpowiadać tam, gdzie wszystkie stwierdzenia są prawdziwe.
Użyj wzoru:
| = SUMPRODUCT ( -- (Miasto = "Boston") , -- (Kategoria = "Cookies") , -- (Ilość > 40) , Ilość ) |
-- : operacja używana do konwersji wszystkich TRUE na 1s i False na 0.
Funkcja SUMPRODUCT ponownie sprawdza SUMA ilości zwróconych przez opisane powyżej funkcje SUMA i JEŻELI.
Podobnie dla drugiego przykładu wynik jest taki sam.
Jak widać funkcja SUMPRODUCT może wykonać to samo zadanie.
Oto wszystkie uwagi obserwacyjne dotyczące stosowania wzoru.
Uwagi:
- Sum_array w formule działa tylko z liczbami.
- Jeśli formuła zwraca błąd #ARG, sprawdź, czy w formule muszą znajdować się nawiasy klamrowe, jak pokazano w przykładach w artykule.
- Znak negacji (--) zmienia wartości, TRUE lub 1 na FAŁSZ lub 0 i FAŁSZ lub 0 na PRAWDA lub 1 .
- Operacje takie jak równa się ( = ), mniejszy niż równy ( <= ), Lepszy niż ( > ) lub nie równa się ( ) można wykonać w ramach zastosowanej formuły, wyłącznie z liczbami.
Mam nadzieję, że ten artykuł o tym, jak używać funkcji JEŻELI zamiast funkcji SUMPRODUCT i SUMIFS w programie Excel, jest objaśniający. Więcej artykułów na temat formuł sumowania znajdziesz 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 stronie e-mail
Jak korzystać z funkcji SUMA PRODUKT w programie Excel: Zwraca SUMA po pomnożeniu wartości w wielu tablicach w programie Excel.
SUMA, jeśli data jest między : Zwraca SUMA wartości między podanymi datami lub okresem w programie Excel.
Suma, jeśli data jest większa niż podana: Zwraca SUMA wartości po podanej dacie lub okresie w programie Excel.
2 sposoby sumowania według miesiąca w Excelu: Zwraca SUMA wartości w danym miesiącu w programie Excel.
Jak sumować wiele kolumn z warunkiem: Zwraca SUMA wartości w wielu kolumnach mających warunek w programie Excel.
Popularne artykuły :
50 skrótów Excela, aby zwiększyć produktywność : Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak używać tFunkcja 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 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.
Jak korzystać z funkcji SUMIF w programie Excel? : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.