Pobierz przykładowy skoroszyt
Ten samouczek pokazuje, jak używać Funkcja Excel SUMPRODUCT w Excelu.
Przegląd funkcji SUMPRODUCT
Funkcja SUMPRODUCT Mnoży tablice liczb i sumuje tablicę wynikową.
Aby użyć funkcji arkusza programu Excel SUMPRODUCT, wybierz komórkę i wpisz:
(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)
Funkcja SUMPRODUCT Składnia i dane wejściowe:
| 1 | =PRODUKCJASUMA(tablica1,tablica2,tablica3) |
tablica1 - Tablice liczb.
Co to jest funkcja SUMPRODUCT?
Funkcja SUMPRODUCT jest jedną z bardziej zaawansowanych funkcji programu Excel. To nazwa, może prowadzić do przekonania, że jest przeznaczona tylko do podstawowych obliczeń matematycznych, ale może być używana do znacznie więcej.
Tablice
SUMPRODUCT wymaga danych wejściowych tablic.
Więc po pierwsze, co rozumiemy przez „szyk”? Tablica to po prostu grupa elementów (np. liczb) ułożonych w określonej kolejności, podobnie jak zakres komórek. Jeśli więc masz liczby 1, 2, 3 w komórkach A1:A3, Excel odczytałby to jako tablicę {1,2,3}. W rzeczywistości możesz wpisać {1,2,3} bezpośrednio do formuł Excela, a tablica zostanie rozpoznana.
Poniżej omówimy więcej o tablicach, ale najpierw spójrzmy na prosty przykład.
Podstawowa matematyka
Spójrzmy na podstawowy przykład SUMPRODUCT, używając go do obliczenia całkowitej sprzedaży.
Mamy naszą tabelę produktów i chcemy obliczyć całkowitą sprzedaż. Możesz kusić, aby po prostu dodać nową kolumnę, pobrać sprzedaną ilość * cena, a następnie zsumować nową kolumnę. Zamiast tego możesz po prostu użyć funkcji SUMPRODUCT. Przejdźmy przez formułę:
| 1 | =PROD.SUMA(A2:A4,B2:B4) |
Funkcja załaduje zakresy liczb do tablic, pomnoży je względem siebie, a następnie zsumuje wyniki:
| 1234 | =PRODUKCJA SUMA({100, 50, 10}, {6, 7, 5})=PRODUKCJA SUMA({100 * 6, 50 * 7, 10 * 5})=PRODUKCJA SUMA({600, 350, 50}=1000 |
Funkcja SUMPRODUCT była w stanie pomnożyć dla nas wszystkie liczby ORAZ wykonać sumowanie.
Średnia ważona
Innym przypadkiem, w którym pomocne jest użycie SUMPRODUCT, jest konieczność obliczenia średniej ważonej. Najczęściej dzieje się tak, gdy mamy do czynienia z zadaniami szkolnymi, dlatego rozważmy poniższą tabelę.
Możemy zobaczyć, ile warte są quizy, testy i prace domowe na ogólną ocenę, a także jaka jest aktualna średnia dla każdego konkretnego przedmiotu. Ocenę ogólną możemy następnie obliczyć pisząc
| 1 | =PROD.SUMA(B2:B4, C2:C4) |
Nasza funkcja ponownie mnoży każdy element w tablicach przed zsumowaniem sumy. To działa tak
| 123 | =PROD.SUMA({30%, 50%, 20%}, {73%, 90%, 95%})=PRODUKTOR({22%, 45%, 19%})=86% |
Wiele kolumn
Innym miejscem, w którym możemy użyć SUMPRODUCT, jest jeszcze więcej kolumn, które należy pomnożyć względem siebie. Spójrzmy na przykład, w którym musimy obliczyć objętość w kawałkach drewna.
Zamiast tworzyć kolumnę pomocniczą do obliczania całkowitej sprzedaży dla każdego wiersza, możemy to zrobić za pomocą jednej formuły. Nasza formuła będzie
| 1 | =PROD.SUMA(B2:B5;C2:C5;D2:D5) |
Pierwsze elementy każdej tablicy będą się mnożyć względem siebie (np. 4 * 2 * 1 = 8). Następnie drugi (4 * 2 * 2 = 16) i 3r & D, itd. W sumie da to szereg produktów, które wyglądają jak {8, 16, 16, 32). Wtedy całkowity wolumen byłby sumą tej tablicy, 72.
Jedno kryterium
OK, dodajmy kolejną warstwę złożoności. Widzieliśmy, że SUMPRODUCT może obsługiwać tablice liczb, ale co z tym, jeśli chcemy sprawdzić kryteria? Cóż, możesz również tworzyć tablice dla wartości logicznych (wartości logiczne to wartości PRAWDA lub FAŁSZ).
Na przykład weź podstawową tablicę {1, 2, 3}. Stwórzmy odpowiednią tablicę, która wskazuje, czy każda liczba jest większa niż 1. Ta tablica wyglądałaby jak {FAŁSZ, PRAWDA, PRAWDA}.
Jest to niezwykle pomocne w formułach, ponieważ możemy łatwo przeliczyć PRAWDA/FAŁSZ na 1/0. Spójrzmy na przykład.
Korzystając z poniższej tabeli, chcemy obliczyć „Ile sprzedanych jednostek było czerwonych?”
Możemy to zrobić za pomocą tej formuły:
| 1 | =PROD.SUMA(A2:A4, --(B2:B4="Czerwony")) |
"Trzymać się! Co to za symbol podwójnego minusa? mówisz. Pamiętasz, jak powiedziałem, że możemy przekonwertować z Prawda/Fałsz na 1/0? Robimy to, zmuszając komputer do wykonania operacji matematycznej. W tym przypadku mówimy „weź wartość ujemną, a następnie ponownie weź ujemną”. Pisząc to, nasza tablica zmieni się w ten sposób:
| 123 | {Prawda, prawda, fałsz}{-1, -1, 0}{1, 1, 0} |
Więc wracając do pełnej formuły SUMPRODUCT, załaduje się do naszych tablic, a następnie pomnoży, w ten sposób
| 123 | =PRODUKCJASUMA({100, 50, 10}, {1, 1, 0})=PRODUKCJASUM({100, 50, 0})=150 |
Zwróć uwagę, jak 3r & D item stał się 0, ponieważ wszystko pomnożone przez 0 staje się zerem.
Wiele kryteriów
Do naszej funkcji możemy załadować do 255 tablic, więc z pewnością możemy załadować więcej kryteriów. Spójrzmy na tę większą tabelę, w której dodaliśmy sprzedany miesiąc.
Jeśli chcemy wiedzieć, ile sprzedanych przedmiotów było czerwonych oraz były w lutym, moglibyśmy napisać naszą formułę jak
| 1 | =PROD.SUMA(A2:A4, --(B2:B4="czerwony"), --(C2:C4="lut")) |
Komputer oceni wtedy nasze tablice i pomnoży je. Omówiliśmy już, w jaki sposób tablice True/False są zmieniane na 1/0, więc na razie pominę ten krok.
| 123 | =PROD.SUMA({100, 50, 10}, {1, 1, 0}, {0, 1, 1})=PROD.SUMA({0, 50, 0})=50 |
W naszym przykładzie mieliśmy tylko jeden wiersz, który spełniał wszystkie kryteria, ale przy rzeczywistych danych mogłeś mieć wiele wierszy, które trzeba było dodać razem.
Złożone kryteria
OK, do tego momentu możesz nie być pod wrażeniem, ponieważ wszystkie nasze przykłady można było wykonać przy użyciu innych funkcji, takich jak SUMA.JEŻELI lub LICZ.JEŻELI. Teraz zrobimy coś z tymi innymi funkcjami żargon robić. Wcześniej nasza kolumna Miesiąc zawierała rzeczywiste nazwy miesięcy. A gdyby zamiast tego miał daty?
Nie możemy teraz zrobić SUMIF, ponieważ SUMIF nie może obsłużyć potrzebnych nam kryteriów. SUMPRODUCT może jednak poradzić sobie z manipulowaniem tablicą i przeprowadzaniem głębszego testu. Manipulowaliśmy już tablicami, kiedy przetłumaczyliśmy Prawda/Fałsz na 1/0. Będziemy manipulować tą tablicą za pomocą funkcji MIESIĄC. Oto pełna formuła, której będziemy używać
| 1 | =PROD.SUMA(A2:A4, --(B2:B4="Czerwony"), --(MIESIĄC(C2:C4)=2)) |
Spójrzmy na 3r & D tablica bliżej. Najpierw nasza formuła wyciągnie numer miesiąca z każdej daty w C2:C4. To da nam {1, 2, 2}. Następnie sprawdzamy, czy ta wartość jest równa 2. Teraz nasza tablica wygląda tak: {False, True, True}. Znowu robimy podwójny minus i mamy {0, 1, 1}. Jesteśmy teraz z powrotem w podobnym miejscu, jak w przykładzie 3, a nasza formuła będzie w stanie powiedzieć nam, że w lutym sprzedano 50 sztuk, które były czerwone.
Podwójny minus a mnożenie
Jeśli widziałeś już wcześniej używaną funkcję SUMPRODUCT, być może zauważyłeś nieco inną notację. Zamiast używać podwójnego minusa, możesz napisać
| 1 | =PROD.SUMA(A2:A4*(B2:B4="Czerwony")*(MIESIĄC(C2:C4)=2)) |
Formuła nadal będzie działać w ten sam sposób, po prostu ręcznie mówimy komputerowi, że chcemy pomnożyć tablice. SUMPRODUCT i tak zamierzał to zrobić, więc nie ma zmiany w sposobie działania matematyki. Wykonywanie operacji matematycznej konwertuje nasze Prawda/Fałsz na 1/0 to samo. Skąd więc ta różnica?
W większości przypadków nie ma to większego znaczenia i sprowadza się do preferencji użytkownika. Jest jednak co najmniej jeden przypadek, w którym potrzebne jest pomnożenie.
Gdy używasz SUMPRODUCT, komputer oczekuje, że wszystkie argumenty (tablica1, tablica2 itd.) będą miały ten sam rozmiar. Oznacza to, że mają taką samą liczbę wierszy lub kolumn. Możesz jednak zrobić coś, co nazywamy dwuwymiarowym obliczeniem tablicowym za pomocą SUMPRODUCT, które zobaczymy w następnym przykładzie. Kiedy to zrobisz, tablice mają różne rozmiary, więc musimy pominąć sprawdzanie „wszystkich tego samego rozmiaru”.
Dwa wymiary
Wszystkie poprzednie przykłady miały nasze tablice idące w tym samym kierunku. SUMPRODUCT poradzi sobie w dwóch kierunkach, jak zobaczymy w następnej tabeli.
Oto nasza tabela sprzedanych jednostek, ale dane są uporządkowane tam, gdzie kategorie znajdują się u góry. Jeśli chcemy dowiedzieć się, ile przedmiotów było czerwonych i w kategorii A, możemy napisać
| 1 | =PRODUKCJASUMA((A2:A4="Czerwony")*(B1:C1="A")*B2:C4) |
Co tu się dzieje?? Okazuje się, że będziemy się mnożyć w dwóch różnych kierunkach. Wizualizacja tego jest trudniejsza za pomocą tylko pisemnego zdania, więc mamy kilka obrazów, które mogą nam pomóc. Po pierwsze, nasze kryteria wierszy (czy to czerwone?) będą mnożone przez każdy wiersz w tablicy.
| 1 | =PRODUKCJASUMA((A2:A4="CZERWONY")*B2:C4) |
Następnie kryteria kolumny (czy to kategoria A?) zostaną pomnożone przez każdą kolumnę
| 1 | =PRODUKCJASUMA((A2:A4="Czerwony")*(B1:C1="A")*B2:C4) |
Po tym, jak oba te kryteria spełniły swoją rolę, jedyne niezerowe, które pozostały, to 5 i 10. SUMPRODUCT da nam wtedy całkowitą sumę 15 jako naszą odpowiedź.
Pamiętasz, jak rozmawialiśmy o tym, że tablice muszą mieć ten sam rozmiar, chyba że robisz dwa wymiary? To było częściowo poprawne. Ponownie przegląda tablice, których użyliśmy w naszej formule. ten wzrost dwóch naszych tablic jest taka sama, a szerokość z dwóch naszych tablic są takie same. Tak więc nadal musisz upewnić się, że wszystko ułoży się poprawnie, ale możesz to zrobić w różnych wymiarach.
Dwa wymiary i złożoność
Wiele razy otrzymujemy dane, które nie są w najlepszym układzie odpowiednim dla naszych formuł. Moglibyśmy spróbować to zmienić ręcznie lub możemy być mądrzejsi z naszymi formułami. Rozważmy poniższą tabelę.
Tutaj mamy pomieszane dane dotyczące naszych produktów i sprzedaży dla każdego miesiąca. Jak moglibyśmy się dowiedzieć, ile przedmiotów Bob sprzedał w ciągu całego roku?
W tym celu skorzystamy z dwóch dodatkowych funkcji: SZUKAJ i CZY NUMER. Funkcja SEARCH pozwoli nam wyszukać słowo kluczowe „items” w komórkach nagłówka. Wyjście z tej funkcji będzie albo liczbą, albo błędem (jeśli słowo kluczowe nie zostanie znalezione). Następnie użyjemy ISNUMBER do konwersji że dane wyjściowe do naszych wartości logicznych. Nasza formuła będzie wyglądać jak poniżej.
Powinieneś już być dobrze zaznajomiony z pierwszą tablicą. Stworzy wynik taki jak {0, 1, 0, 1}. Następna tablica kryteriów, o której właśnie mówiliśmy. Spowoduje to utworzenie liczby dla wszystkich komórek zawierających w sobie „Elementy” oraz błędu dla pozostałych {5, #N/A!, 5, #N/A!}. ISNUMBER następnie konwertuje to na Boolean {True, False, True, False}. Następnie, gdy mnożymy, zachowane zostaną tylko wartości z pierwszej i trzeciej kolumny. Po tym, jak wszystkie tablice pomnożą się nawzajem, jedyne niezerowe liczby, jakie będziemy mieć, to te wyróżnione tutaj:
| 1 | =SUMPRODUCT((A2:A5="Bob")*(ISNUMBER(SEARCH("Items",B1:E1))*B2:E5)) |
SUMPRODUCT następnie zsumuje je wszystkie i otrzymamy ostateczny wynik 29.
SUMA PRODUKT Lub
Istnieje wiele sytuacji, w których chcielibyśmy mieć możliwość zsumowania wartości, jeśli nasza kolumna kryteriów ma jedną wartość LUB inną wartość. Możesz to osiągnąć w programie SUMPRODUCT, dodając dwie tablice kryteriów względem siebie.
W tym przykładzie chcemy zsumować sprzedane jednostki zarówno w kolorze czerwonym, jak i niebieskim.
Nasza formuła będzie wyglądać tak
| 1 | =PROD.SUMA(A2:A7; (B2:B7="Czerwony")+(B2:B7="Niebieski")) |
Spójrzmy na tablicę kryteriów czerwonych. Da to tablicę, która wygląda tak: {1, 1, 0, 0, 0, 0}. Tablica kryteriów niebieskich będzie wyglądać tak: {0, 0, 1, 0, 1, 0}. Kiedy je dodasz, nowa tablica będzie wyglądać tak: {1, 1, 1, 0, 1, 0}. Możemy zobaczyć, jak dwie tablice połączyły się w jedną tablicę kryteriów. Funkcja pomnoży to przez naszą pierwszą tablicę i otrzymamy {100, 50, 10, 0, 75, 0}. Zauważ, że wartości Green zostały wyzerowane. Ostatnim krokiem SUMPRODUCT jest zsumowanie wszystkich liczb, aby uzyskać nasze rozwiązanie 235.
Tutaj jedno słowo ostrzeżenia. Uważaj, kiedy tablice kryteriów nie wykluczają się wzajemnie. W naszym przykładzie wartości w kolumnie B mogą być czerwone lub niebieskie, ale wiedzieliśmy, że nigdy nie może to być jedno i drugie. Zastanów się, czy napisaliśmy taką formułę:
| 1 | =PROD.SUMA(A2:A7; (A2:A7>=50)+(B2:B7="Niebieski")) |
Naszym zamiarem jest znalezienie niebieskich przedmiotów, które zostały sprzedane lub były w ilości większej niż 50. Warunki te nie wykluczają się, ponieważ jeden wiersz może mieć ponad 50 w kolumnie A oraz bądź niebieski. Spowoduje to, że pierwsza tablica kryteriów będzie wyglądała jak {1, 1, 0, 1, 1, 0}, a druga tablica kryteriów będzie miała postać {0, 0, 1, 0, 1, 0}. Zsumowanie ich razem dało {1, 1, 1, 1, 2, 0}. Widzisz, jak mamy teraz 2? Pozostawiony sam, SUMPRODUCT podwoiłby wartość w tym wierszu, zmieniając 75 na 150, i otrzymalibyśmy zły wynik. Aby to poprawić, umieszczamy w naszej tablicy zewnętrzne sprawdzenie kryteriów, tak jak poniżej:
| 1 | =PROD.SUMA(A2:A7, --((A2:A7>=50)+(B2:B7="Niebieski")>0)) |
Teraz, po dodaniu dwóch wewnętrznych tablic kryteriów, sprawdzimy, czy wynik jest większy niż 0. To pozbywa się 2, które mieliśmy wcześniej, i zamiast tego otrzymamy tablicę taką jak {1, 1, 1 , 1, 1, 0} co da poprawny wynik.
SUMPRODUCT Dokładne
W większości funkcji programu Excel nie jest rozróżniana wielkość liter, ale czasami musimy mieć możliwość wyszukiwania z uwzględnieniem wielkości liter. Gdy pożądany wynik jest wartością liczbową, możemy to osiągnąć za pomocą polecenia DOKŁADNIE w funkcji SUMA PRODUKT. Rozważ poniższą tabelę:
Chcemy znaleźć wynik dla pozycji „ABC123”. Zwykle funkcja DOKŁADNA porównuje dwa elementy i zwraca wynik logiczny określający, czy te dwa elementy są dokładnie to samo. Ponieważ jednak znajdujemy się w SUMPRODUCT, nasz komputer będzie wiedział, że mamy do czynienia z tablicami i będzie mógł porównać jeden element z każdym elementem w tablicy. Nasza formuła będzie wyglądać tak
| 1 | =PROD.SUMA(--DOKŁADNE("ABC123"; A2:A5); B2:B5) |
Funkcja DOKŁADNIE sprawdzi następnie każdy element w A2:A5, aby sprawdzić, czy pasuje do wartości i wielkości liter. Da to tablicę, która wygląda jak {0, 1, 0, 0}. Po pomnożeniu przez B2:B5 tablica staje się {0, 2, 0, 0}. Po ostatecznym podsumowaniu otrzymujemy nasze rozwiązanie 2.
SUMPRODUCT w Arkuszach Google
Funkcja SUMPRODUCT działa dokładnie tak samo w Arkuszach Google, jak w programie Excel:
SUMPRODUCT Przykłady w VBA
Możesz także użyć funkcji SUMPRODUCT w VBA. Typ: application.worksheetfunction.sumproduct(array1,array2,array3)
Wykonywanie następujących instrukcji VBA
| 1 | Range("B10") = Application.WorksheetFunction.SumProduct(Range("A2:A7"), Range("B2:B7")) |
przyniesie następujące wyniki
W przypadku argumentów funkcji (tablica 1 itd.) można wprowadzić je bezpośrednio do funkcji lub zdefiniować zmienne, które będą używane w zamian.
