Funkcja SUMA.ILOCZYNÓW mnoży i sumuje ze sobą wartości z dwóch lub większej ilości tablic. Jednak za pomocą tej funkcji możemy również wyliczać wartość dwóch i więcej kolumn w oparciu o zdefiniowane kryterium.
Na filmie widzimy zastosowanie funkcji SUMA.ILOCZYNÓW w typowym przypadku. Jako argumenty posłużyły nam dwie tabele: z ilością sprzedanych produktów (C2:C13) oraz z cenami jednostkowymi (D2:D13). W wyniku otrzymaliśmy łączną wartość wszystkich produktów oraz wszystkich klientów. W jaki sposób możemy otrzymać wartość sprzedaży dla poszczególnych produktów (SKU)?
Dzięki zastosowaniu konwersji w postaci podwójnego minusa w tle funkcja SUMA.ILOCZYNÓW wyliczy wartość sprzedaży dla poszczególnych produktów.
Zapis formuły:
Linia 1. znak równości SUMA kropka ILOCZYNÓW otwórz nawias okrągły $C$2 dwukropek $C$13 średnik $D$2 dwukropek $D$13 średnik minus minus otwórz nawias okrągły $B$2 dwukropek $B$13 znak równości H2 zamknij nawias okrągły zamknij nawias okrągły.
Dwa pierwsze argumenty funkcji są takie same, jak w przypadku funkcji wpisanej w komórce D14, gdzie wyliczana była wartość dla wszystkich produktów. Z małym wyjątkiem – tablice zostały zablokowane za pomocą znaków $. Zapis ten zablokuje zmienianie się zakresów podczas kopiowania formuł. Aby zablokować zakresy, możemy ręcznie wstawić znak dolara we właściwych miejscach lub zaznaczyć zakresy, które mają być zablokowane i wybrać z klawiatury klawisz funkcyjny [F4], a program sam wstawi znaki dolara przed numerem wiersza i literą kolumny.
Zauważmy trzeci argument funkcji. Ponieważ chcemy sprawdzić wartości sprzedaży dla wszystkich SKU i ręczne wpisywanie wartości byłoby czasochłonne, szczególnie w przypadku dużych list, za pomocą zapisu =H2 odwołujemy się do wartości ze wskazanej komórki. W tym wypadku jest to SKU o numerze 1000033. W wyniku następującego zapisu $B$2:$B$13=H2Excel będzie sprawdzał wartości z tablicy w kolumnie B, która przechowuje identyfikatory produktów. Ponieważ znak przyrównania (=) zalicza się do funkcji logicznych, Excel przy każdym napotkaniu wartości w tablicy równej naszemu SKU zwróci wartość PRAWDA.
Na filmie widać, które wartości zwraca zapis trzeciego argumentu. Zauważmy, że tylko wartość pierwsza i ostatnia w powyższym zapisie spełniają warunek (PRAWDA), ponieważ tylko pierwszy i ostatni element tablicy w kolumnie B jest zgodny z identyfikatorem produktu w komórce H2. W trzecim argumencie jest jeszcze jeden ważny element, a mianowicie podwójny minus przed warunkiem ($B$2:$B$13=H2). Jak widać na filmie funkcja zwraca wartości typu PRAWDA lub FAŁSZ, z którymi Excel nie potrafi sobie poradzić, mnożąc tablice i później je sumując. Dlatego, aby zamienić te wartości na wartości liczbowe zera i jedynki, używamy zapisu podwójnego minusa. Dzięki temu program zamieni wartości logiczne na liczby i będzie w stanie prawidłowo wykonać funkcję SUMA.ILOCZYNÓW.
R1e0eOahEoIg4
Nagranie filmowe dotyczące zastosowania funkcji suma iloczynów.
Nagranie filmowe dotyczące zastosowania funkcji suma iloczynów.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Wujek poprosił cię o pomoc w jego firmie zajmującej się produkcją pięciu różnych materiałów. Tygodniowo przeprowadzają wiele transakcji. Wszyscy klienci mają swoje unikatowe numery, do których przypisywana jest ilość danego materiału, jaką zakupili oraz jego cena. Ma być ona pobierana z oddzielnej tabeli składającej się z nazwy materiału oraz jego kosztu za sztukę. Bywa, że niektórzy klienci wielokrotnie zaopatrują się w materiały. Oblicz, jaką całkowitą kwotę wydał każdy z nabywców w tym miesiącu.
Przykładowe rozwiązanie zadania:
R1N5ijappo21s
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B, C, F, G, I oraz J wprowadzono dane dotyczące zakupu materiałów przez konkretnych klientów wraz z wydanymi przez nich kwotami. W arkuszu kolejno dodano opisy: w komórce A1 numer klienta, w komórce B1 Materiał, w komórce C1 Liczba sztuk, w komórce F1 Materiał, w komórce G1 Cena za sztukę, w komórce I Numer klienta, w komórce J1 Łączna kwota transakcji. W kolumnie A w komórkach od A2 do A20 wpisano numery klientów. W kolumnie B w komórkach od B2 do B20 wpisano wartości liczbowe. W kolumnie C w komórkach od C2 do C20 wpisano liczbę sztuk. W kolumnie F w komórkach od F2 do F6 wpisano liczbę materiałów. W kolumnie G w komórkach od G2 do G6 wpisano kwoty za sztukę. W kolumnie I w komórkach do I2 do I13 wpisano numery klientów. W kolumnie J w komórkach od J2 do J13 wpisano kwoty transakcji. Dodatkowo zaprezentowano sposób obliczania ceny materiału. Formułę obliczania należy wpisać w komórce D2. Brzmi ona następująco: =WYSZUKAJ.PIONOWO(B2;$F$2:$G$6;2;FAŁSZ).
Rdk2x5ZDsZX8F
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B, C, D, F, G oraz J wprowadzono dane dotyczące zakupu materiałów przez konkretnych klientów wraz z wydanymi przez nich kwotami. W arkuszu kolejno dodano opisy: w komórce A1 numer klienta, w komórce B1 Materiał, w komórce C1 Liczba sztuk, w komórce D1 Cena materiału, w komórce F1 Materiał, w komórce G1 Cena za sztukę, w komórce J1 Łączna kwota transakcji. W kolumnie A w komórkach od A2 do A20 wpisano numery klientów. W kolumnie B w komórkach od B2 do B20 wpisano wartości liczbowe materiałów. W kolumnie C w komórkach od C2 do C20 wpisano liczbę sztuk. W kolumnie D w komórkach od D2 do D20 wpisano ceny materiałów. W kolumnie F w komórkach od F2 do F6 wpisano liczbę materiałów. W kolumnie G w komórkach od G2 do G6 wpisano kwoty za sztukę. W kolumnie J w komórkach od J2 do J13 wpisano kwoty transakcji. Dodatkowo zaprezentowano sposób obliczania numeru klienta. Formułę obliczania należy wpisać w komórce I2. Brzmi ona następująco: =UNIKATOWE(A2:A25).
RTGsuWdI0YpJs
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B, C, D, F, G oraz I wprowadzono dane dotyczące zakupu materiałów przez konkretnych klientów wraz z wydanymi przez nich kwotami. W arkuszu kolejno dodano opisy: w komórce A1 Numer klienta, w komórce B1 Materiał, w komórce C1 Liczba sztuk, w komórce D1 Cena materiału, w komórce F1 Materiał, w komórce G1 Cena za sztukę, w komórce I1 Numer klienta. W kolumnie A w komórkach od A2 do A20 wpisano numery klientów. W kolumnie B w komórkach od B2 do B20 wpisano wartości liczbowe materiałów. W kolumnie C w komórkach od C2 do C20 wpisano liczbę sztuk. W kolumnie D w komórkach od D2 do D20 wpisano ceny materiałów. W kolumnie F w komórkach od F2 do F6 wpisano liczbę materiałów. W kolumnie G w komórkach od G2 do G6 wpisano kwoty za sztukę. W kolumnie I w komórkach od I2 do I13 wpisano numery klientów. Dodatkowo zaprezentowano sposób obliczania łącznej kwoty transakcji. Formułę obliczania należy wpisać w komórce J2. Brzmi ona następująco: =SUMA.ILOCZYNÓW($C$2:$C$25;$D$2:$D$25;--($A$2:$A$25=I2)).
Ćwiczenie 2
Korzystając z arkusza wykonanego do poprzedniego zadania, oblicz ile sprzedano sztuk każdego materiału i ile wynosiła łączna kwota transakcji zakupu tych materiałów.
Przykładowe rozwiązanie zadania:
R125r1PJeeiRQ
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B, C, D, F, G, I oraz J wprowadzono dane dotyczące zakupu materiałów przez konkretnych klientów wraz z wydanymi przez nich kwotami. W arkuszu kolejno dodano opisy: w komórce A1 numer klienta, w komórce B1 Materiał, w komórce C1 Liczba sztuk, w komórce D1 Cena materiału, w komórce F1 Materiał, w komórce G1 Cena za sztukę, w komórce I Numer klienta, w komórce J1 Łączna kwota transakcji. W kolumnie A w komórkach od A2 do A25 wpisano numery klientów. W kolumnie B w komórkach od B2 do B25 wpisano wartości liczbowe. W kolumnie C w komórkach od C2 do C25 wpisano liczbę sztuk. W kolumnie D w komórkach od D2 do D25 wpisano ceny materiału. W kolumnie F w komórkach od F2 do F6 wpisano liczbę materiałów. W kolumnie G w komórkach od G2 do G6 wpisano kwoty za sztukę. W kolumnie I w komórkach do I2 do I13 wpisano numery klientów. W kolumnie J w komórkach od J2 do J13 wpisano kwoty transakcji. Dodatkowo kolumnę I15 zatytułowano Materiał, w komórkach od I16 do I20 wpisano liczby. Kolumnę K15 zatytułowano Łączna kwota transakcji, w komórkach od K16 do K20. Dodatkowo zaprezentowano sposób obliczania liczby sztuk. Formułę obliczania należy wpisać w komórce J16. Brzmi ona następująco: =SUMA.JEŻELI($B$2:$C$25;I16;$C$2:$C$25).
RRbZkXAtzhf6k
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B, C, D, F, G, I oraz J wprowadzono dane dotyczące zakupu materiałów przez konkretnych klientów wraz z wydanymi przez nich kwotami. W arkuszu kolejno dodano opisy: w komórce A1 numer klienta, w komórce B1 Materiał, w komórce C1 Liczba sztuk, w komórce D1 Cena materiału, w komórce F1 Materiał, w komórce G1 Cena za sztukę, w komórce I Numer klienta, w komórce J1 Łączna kwota transakcji. W kolumnie A w komórkach od A2 do A25 wpisano numery klientów. W kolumnie B w komórkach od B2 do B25 wpisano wartości liczbowe. W kolumnie C w komórkach od C2 do C25 wpisano liczbę sztuk. W kolumnie D w komórkach od D2 do D25 wpisano ceny materiału. W kolumnie F w komórkach od F2 do F6 wpisano liczbę materiałów. W kolumnie G w komórkach od G2 do G6 wpisano kwoty za sztukę. W kolumnie I w komórkach do I2 do I13 wpisano numery klientów. W kolumnie J w komórkach od J2 do J13 wpisano kwoty transakcji. Dodatkowo kolumnę I15 zatytułowano Materiał, w komórkach od I16 do I20 wpisano liczby. Komórkę J15 zatytułowano Liczba sztuk, w komórkach od J16 do J20 wpisano liczby. Dodatkowo zaprezentowano sposób obliczania łącznej kwoty transakcji. Formułę obliczania należy wpisać w komórce K16. Brzmi ona następująco: =SUMA.ILOCZYNÓW($C$2:$C$25;$D$2:$D$25;--($B$2:$B$25=I16)).
