Jak korzystać z funkcji SUMPRODUCT programu Excel -

Spisie treści

Podsumowanie

Funkcja SUMPRODUCT mnoży razem zakresy lub tablice i zwraca sumę iloczynów. Brzmi to nudno, ale SUMPRODUCT to niezwykle wszechstronna funkcja, której można używać do liczenia i sumowania, takich jak COUNTIFS lub SUMIFS, ale z większą elastycznością. W SUMPRODUCT można z łatwością korzystać z innych funkcji, aby jeszcze bardziej rozszerzyć funkcjonalność.

Cel, powód

Pomnóż, a następnie zsumuj tablice

Wartość zwracana

Wynik tablic mnożonych i sumowanych

Składnia

= SUMA.RODUKTU (tablica1; (tablica2);…)

Argumenty

  • tablica1 - pierwsza tablica lub zakres do pomnożenia, a następnie dodanie.
  • tablica2 - (opcjonalne) Druga tablica lub zakres do pomnożenia, a następnie dodaj.

Wersja

Excel 2003

Uwagi dotyczące użytkowania

Funkcja SUMPRODUCT działa z tablicami, ale nie wymaga normalnej składni tablicy (Ctrl + Shift + Enter) do wejścia. Celem funkcji SUMPRODUCT jest pomnożenie, a następnie sumowanie tablic. Jeśli podano tylko jedną tablicę, SUMPRODUCT po prostu zsumuje pozycje w tablicy. Można dostarczyć do 30 macierzy.

Kiedy po raz pierwszy napotkasz SUMPRODUCT, może wydawać się nudne, złożone, a nawet bezcelowe. Ale SUMPRODUCT to niezwykle wszechstronna funkcja o wielu zastosowaniach. Ponieważ radzi sobie z tablicami z wdziękiem i bez narzekania, możesz go używać do przetwarzania zakresów komórek w sprytny, elegancki sposób (zobacz linki do przykładów formuł na tej stronie).

Aby zilustrować, jak działa SUMPRODUCT, oto kilka typowych przykładów.

SUMPRODUCT dla sum warunkowych i zliczeń

Załóżmy, że masz dane zamówień w komórce A2: B6, ze stanem w kolumnie A i sprzedażą w kolumnie B:

ZA b
1 Stan Obroty
2 UT 75
3 WSPÓŁ 100
4 TX 125
5 WSPÓŁ 125
6 TX 150

Używając SUMPRODUCT, możesz policzyć łączną sprzedaż w Teksasie („TX”) za pomocą następującego wzoru:

=SUMPRODUCT(--(A2:A6="TX"))

Możesz podsumować całkowitą sprzedaż w Teksasie („TX”) za pomocą następującego wzoru:

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

Uwaga: nie daj się zmylić podwójnie ujemnym. Jest to powszechna sztuczka używana w bardziej zaawansowanych formułach programu Excel do przekształcania wartości PRAWDA i FAŁSZ na 1 i 0. Zobacz więcej poniżej…

Dla powyższego przykładu sumy, oto wirtualna reprezentacja dwóch tablic przetworzonych po raz pierwszy przez SUMPRODUCT:

tablica1 tablica2
FAŁSZYWY 75
FAŁSZYWY 100
PRAWDZIWE 125
FAŁSZYWY 125
PRAWDZIWE 150

Każda tablica zawiera 5 elementów. Pierwsza tablica zawiera wartości PRAWDA / FAŁSZ, które wynikają z wyrażenia A2: A6 = "TX", a druga tablica to zawartość B2: B6. Każdy element w pierwszej tablicy zostanie pomnożony przez odpowiadający mu element w drugiej tablicy. Jednak w obecnym stanie wynik SUMPRODUCT będzie równy zero, ponieważ wartości TRUE i FALSE będą traktowane jako zero. Potrzebujemy, aby pozycje w tablicy1 były numeryczne - muszą być „wymuszone” na jedynki i zera. Tutaj pojawia się podwójny minus.

Używając podwójnego ujemnego - (podwójny jednoargumentowy, dla typów technicznych) jesteśmy w stanie przekształcić PRAWDA / FAŁSZ na wartości numeryczne jeden i zero, jak pokazano na wirtualnej reprezentacji poniżej. Ostatnia kolumna „Produkt” przedstawia wynik pomnożenia dwóch tablic. Zsumowany wynik, 275, jest wartością zwracaną przez SUMPRODUCT.

tablica1 tablica2 Produkt
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Suma 275

Używając składni nawiasów klamrowych dla tablic, przykład wygląda następująco:

=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150))

i tak po pomnożeniu:

=SUMPRODUCT((0,0,125,0,150))

W tym przykładzie szczegółowo omówiono powyższe pomysły.

SUMPRODUCT z innymi funkcjami

SUMPRODUCT może bezpośrednio używać innych funkcji. Możesz zobaczyć SUMPRODUCT używane z funkcją LEN do zliczania całkowitej liczby znaków w zakresie lub z funkcjami takimi jak ISBLANK, ISTEXT itp. Zwykle nie są to funkcje tablicowe, ale gdy otrzymają zakres, tworzą „tablicę wyników”. Ponieważ SUMPRODUCT jest zbudowany do pracy z tablicami, może bezpośrednio wykonywać obliczenia na tablicach. Może to być dobry sposób na zaoszczędzenie miejsca w arkuszu, poprzez wyeliminowanie kolumny „pomocniczej”.

Na przykład załóżmy, że masz 10 różnych wartości tekstowych w A1: A10 i chcesz policzyć wszystkie znaki dla wszystkich 10 wartości. Możesz dodać kolumnę pomocniczą w kolumnie B, która używa tej formuły: LEN (A1) do obliczania znaków w każdej komórce. Następnie możesz użyć SUMA, aby zsumować wszystkie 10 liczb. Jednak używając SUMPRODUCT, możesz napisać taką formułę:

=SUMPRODUCT(LEN(A1:A10))

W przypadku użycia z zakresem takim jak A1: A10 funkcja LEN zwróci tablicę 10 wartości. Następnie SUMPRODUCT po prostu zsumuje wszystkie wartości i zwróci wynik, bez potrzeby stosowania kolumny pomocniczej.

Zobacz przykłady poniżej, wiele innych sposobów korzystania z SUMPRODUCT.

Uwagi:

  1. SUMPRODUCT traktuje elementy nieliczbowe w tablicach jako zera.
  2. Argumenty tablicy muszą mieć ten sam rozmiar. W przeciwnym razie SUMPRODUCT wygeneruje #VALUE! wartość błędu.
  3. Testy logiczne wewnątrz tablic utworzą wartości PRAWDA i FAŁSZ. W większości przypadków będziesz chciał wymusić je na 1 i 0.
  4. SUMPRODUCT często może bezpośrednio używać wyników innych funkcji (zobacz przykłady formuł poniżej)

Interesujące artykuły...