Niektóre mało prawdopodobne zastosowania funkcji SUMPRODUCT programu Excel

O ile możesz nie być świadomy funkcji SUMPRODUCT Excela, ci, którzy wiedzą, często używają jej do określonego celu: pomnożenie dwóch zakresów razem, a następnie zsumowanie całości.

Jak pokazano na rysunku 1, pojedynczy wzór wykorzystujący SUMPRODUCT może zastąpić osiem wzorów wymienionych w komórkach D2 do D9. Funkcja SUMPRODUCT mnoży wartości w kolumnie B przez wartości w kolumnie C w układzie wiersz po wierszu, a następnie sumuje całość.

Chociaż jest to pomocne, możesz nie zdawać sobie sprawy, że SUMPRODUKT może również działać jak VLOOKUP lub SUMIF na sterydach.

Rysunek 1: SUMPRODUCT mnoży wartości razem i sumuje produkty, ale może również zrobić więcej.

Funkcja VLOOKUP Excela pozwala na wyszukiwanie danych z tabeli w oparciu o określone kryteria i posiada cztery argumenty:

• lookup_value – Są to dane, których chcesz szukać w pierwszej kolumnie tablicy.

–

• tablica tablicowa – Tablica tablicowa jest zakresem dwóch lub więcej kolumn.

– Tablica

• col_index_num – Ten argument umożliwia określenie kolumny w tablicy, dla której mają zostać zwrócone dane.

– Ten argument pozwala określić kolumnę w tablicy, dla której chcesz zwrócić dane.

• range_lookup – Na tej pozycji zaznaczasz FALSE, jeśli szukasz dokładnego dopasowania do lookup_value , lub TRUE, jeśli chcesz uzyskać przybliżone dopasowanie. Na przykład, użyłbyś FALSE’a do sprawdzenia ceny towaru lub TRUE’a, jeśli ustalasz, do którego przedziału podatkowego należy poziom dochodu.

Rysunek 2: VLOOKUP umożliwia wyszukiwanie pojedynczej wartości na podstawie jednego kryterium.

Jak pokazano na rysunku 2, VLOOKUP zwraca $85,106 z kolumny E dla pierwszej instancji Jabłek, która pojawia się w kolumnie D. Dodatkowe instancje Jabłek są ignorowane. Jeśli trzeba zsumować wiele wartości w oparciu o określone kryteria, lepszym wyborem jest SUMIF. Funkcja ta posiada trzy argumenty:

• zakres – Jest to kolumna lub wiersz, w którym chcesz szukać określonych kryteriów.
• kryteria – Jest to ta sama wartość, co wartość lookup_value dla VLOOKUP.
• sum_range – Jest to kolumna lub wiersz, z którego chcesz dodać liczby za każdym razem, gdy znajdziesz kryteria w zakresie.

Rysunek 3: PODSUMOWANIE pozwala na sumowanie wielu pozycji, ale jest również ograniczone do jednego kryterium.

Rysunek 4: SUMPRODUCT może zwrócić te same wyniki co SUMIF.

Jak pokazano na rysunku 3, SUMIF zwraca $396,495. W tym przypadku, za każdym razem, gdy znalazł dopasowanie w kolumnie D na wyrazie Jabłka, dodawał odpowiednią wartość w kolumnie E. Podobnie jak w przypadku VLOOKUP, można wyszukiwać tylko na podstawie jednego kryterium. Na szczęście, SUMPRODUCT pozwala na sumowanie wartości w oparciu o wiele kryteriów.

Jednak najpierw użyjmy SUMPRODUKTU do dopasowania w oparciu o jedno kryterium, jak pokazano na rysunku 4. W tym przypadku zwraca on ten sam wynik co SUMIF, ponieważ podaliśmy tylko jedno kryterium.

I odwrotnie, na rysunku 5, SUMPRODUCT zwraca 272 584 USD, ponieważ określiliśmy, że chcemy sprzedawać tylko jabłka sprzedawane w Georgii Północnej.

Rysunek 5: DOSTAWA może również zwracać wyniki oparte na wielu kryteriach.

Jak pokazano na rysunku 5, wzór wygląda na Komórki od A2 do A19 dla słów North GA, a na Komórki od D2 do D19 dla słowa Apples. Gdy oba kryteria są spełnione, SUMPRODUCT sumuje odpowiednie wartości z Komórek E2 do E19.

Rysunek 6: SUMPRODUCT może również zwrócić liczbę dopasowań, które spełniają określone przez Ciebie kryteria.

Na koniec, jak pokazano na rysunku 6, jeśli określisz tylko kryteria i pominiesz zakres do zsumowania, wówczas SUMPRODUCT zwróci liczbę pozycji, które odpowiadają określonym przez Ciebie kryteriom.

O autorze:

David H. Ringstrom, CPA kieruje firmą Accounting Advisors, Inc. z siedzibą w Atlancie, świadczącą usługi szkoleniowe i doradcze w zakresie baz danych w całym kraju. Skontaktuj się z Davidem pod adresem [chronione pocztą elektroniczną] lub śledź go na Twitter . David mówi na konferencjach o programie Microsoft Excel i prezentuje webcasty dla kilku dostawców CPE, w tym dla partnera AccountingWEB .

Artykuły pokrewne:

• Trzy triki do formatowania wierszy w programie Excel
• Trzy menu Excela, które mogłeś pominąć