SUMPRODUCT

SUMPRODUCT to funkcja arkusza kalkulacyjnego zwracająca sumę iloczynów odpowiadających sobie elementów tablic.

Jak funkcja SUMPRODUCT łączy mnożenie tablic z sumowaniem

SUMPRODUCT to funkcja arkusza kalkulacyjnego, która mnoży odpowiadające sobie elementy jednej lub kilku tablic, a następnie dodaje otrzymane iloczyny. Dla tablic zawierających wartości 2, 3 i 4 oraz 10, 20 i 30 wynik można zapisać jako 2×10 + 3×20 + 4×30, czyli 200. Funkcja pozwala więc obliczyć sumę ważoną bez tworzenia pomocniczej kolumny z pojedynczymi iloczynami. W praktyce może służyć do podsumowania wartości sprzedaży, kosztów, liczby godzin, punktów, stanów magazynowych lub innych danych zapisanych w odpowiadających sobie wierszach.

Argumentami SUMPRODUCT są tablice lub zakresy komórek. W typowym zastosowaniu wszystkie zakresy powinny mieć taki sam rozmiar i układ, ponieważ element z pierwszego wiersza jednego zakresu jest mnożony przez element z pierwszego wiersza drugiego zakresu, a następnie analogicznie przetwarzane są kolejne pozycje. Jeżeli zakresy mają różną liczbę wierszy albo kolumn, wynik może być błędny lub arkusz może zwrócić komunikat o błędzie, zależnie od programu i sposobu zapisania formuły. W Excelu składnia funkcji ma postać SUMPRODUCT(tablica1; [tablica2]; ...), przy czym separator argumentów może zależeć od ustawień regionalnych.

Warunki logiczne zapisane jako wartości w SUMPRODUCT

Jedną z ważnych cech SUMPRODUCT jest możliwość wykorzystania wyrażeń logicznych do filtrowania danych bez osobnego użycia funkcji SUMIFS. Porównanie zakresu z wartością, na przykład A2:A100="Warszawa", tworzy tablicę wyników PRAWDA i FAŁSZ. Aby użyć tych wyników w działaniu arytmetycznym, często przekształca się je na 1 i 0 operatorem podwójnego minusa: --(A2:A100="Warszawa"). Wtedy wiersze spełniające warunek otrzymują mnożnik 1, a pozostałe 0. Przykładowa formuła =SUMPRODUCT(--(A2:A100="Warszawa");C2:C100;D2:D100) sumuje iloczyny z kolumn C i D tylko dla wierszy, w których kolumna A zawiera tekst „Warszawa”.

Warunki można łączyć przez mnożenie tablic logicznych. Formuła =SUMPRODUCT(--(A2:A100="Warszawa");--(B2:B100="Laptop");C2:C100) uwzględnia wyłącznie wiersze spełniające oba kryteria. Zastosowanie znaku plus może odwzorować logiczne „lub”, ale wymaga ostrożności, ponieważ wiersz spełniający oba warunki może otrzymać wartość 2 i zostać policzony podwójnie. Jeżeli potrzebne jest zwykłe uwzględnienie wiersza tylko raz, warunki można sprowadzić do wartości 0 lub 1 odpowiednią konstrukcją logiczną. Warto również pamiętać, że tekst i puste komórki nie zachowują się tak samo jak liczby, dlatego zakresy przeznaczone do mnożenia powinny być sprawdzone pod kątem błędów, zapisanych jako tekst liczb oraz nieoczekiwanych wartości.

SUMPRODUCT w obliczeniach ważonych i analizie zakresów

Funkcja jest szczególnie użyteczna przy obliczaniu średniej ważonej. Jeżeli w zakresie B2:B20 znajdują się wartości, a w zakresie C2:C20 odpowiadające im wagi, licznik można obliczyć jako SUMPRODUCT(B2:B20;C2:C20). Aby otrzymać średnią ważoną, wynik należy podzielić przez sumę wag, na przykład =SUMPRODUCT(B2:B20;C2:C20)/SUM(C2:C20). Dzielenie przez zero jest możliwe, gdy wszystkie wagi są puste lub równe zero, dlatego w arkuszu używanym operacyjnie warto uwzględnić sprawdzenie tego przypadku, na przykład za pomocą JEŻELI albo IF, zależnie od wersji językowej programu.

SUMPRODUCT może też zliczać rekordy spełniające warunki. Konstrukcja =SUMPRODUCT(--(A2:A100="Tak")) zwraca liczbę komórek zawierających dokładnie tekst „Tak”, a połączenie kilku warunków pozwala policzyć tylko rekordy należące do określonej kategorii i przedziału liczbowego. Przy dużych arkuszach zakresy powinny być ograniczone do rzeczywiście używanych wierszy. Odwołania do całych kolumn, takie jak A:A lub C:C, mogą powodować przeliczanie bardzo dużej liczby komórek i pogarszać wydajność skoroszytu, szczególnie gdy wiele formuł SUMPRODUCT działa równocześnie. Nie należy także bezpośrednio mieszać zakresów poziomych i pionowych ani zakresów przesuniętych względem siebie, ponieważ funkcja opiera obliczenie na pozycji elementów.

Kontrola formuł SUMPRODUCT w pliku arkusza

Nieprawidłowy wynik SUMPRODUCT często wynika nie z samej funkcji, lecz z niespójnych danych wejściowych. Podczas sprawdzania formuły należy porównać początkowe i końcowe adresy wszystkich zakresów, zweryfikować typ danych oraz sprawdzić, czy komórki nie zawierają błędów takich jak #N/D, #ARG! lub #DZIEL/0!. Warto również rozłożyć formułę na części i tymczasowo wyświetlić wynik pojedynczego warunku logicznego albo pojedynczej tablicy. Ułatwia to ustalenie, czy problem dotyczy kryterium, mnożonych wartości, separatora argumentów, czy lokalnego formatu liczb.

Warszawskie Pogotowie Komputerowe zajmuje się profesjonalną naprawą laptopów, komputerów PC i ich podzespołów, dlatego przy problemach z działaniem arkusza można zlecić sprawdzenie komputera, systemu oraz oprogramowania używanego do pracy z plikiem. Sama funkcja SUMPRODUCT nie naprawia uszkodzonego skoroszytu ani sprzętu: wykonuje obliczenie na podstawie przekazanych zakresów. Jeżeli arkusz zamyka się, zawiesza albo wyświetla błędne wyniki mimo poprawnej składni, diagnostyka może objąć stan systemu, pamięci operacyjnej, nośnika danych oraz instalacji programu arkuszowego. Przed przekazaniem pliku do analizy warto zachować jego kopię i zanotować przykładowe dane, dla których rezultat jest niezgodny z oczekiwaniem.