Niedoceniana funkcja SUMA.ILOCZYNÓW. Jak tworzyć rankingi?

Większość z nas na co dzień używa Excela do podstawowych obliczeń. Dodajemy, wyciągamy średnią, czasem WYSZUKAJ.PIONOWO lub JEŻELI. Schody zaczynają się, gdy musimy wyciągnąć z danych coś więcej – na przykład stworzyć rankingi oparte na konkretnych kategoriach. Zwykle ratujemy się ręcznym sortowaniem oraz filtrowaniem danych albo tworzeniem niekończących się kolumn pomocniczych.

Dziś pokażemy, jak można uprościć sobie życie. Oszczędzamy czas i miejsce w arkuszu, wykorzystując potężną, a często ignorowaną funkcję: SUMA.ILOCZYNÓW.

Zanim przejdziemy do rankingów, zobaczmy mechanikę działania tej funkcji na najprostszym przykładzie.

Wyobraźmy sobie prostą tabelę z zamówieniami: w kolumnie A mamy „Ilość”, w kolumnie B „Cenę”. Standardowo, aby poznać całkowitą wartość, tworzymy kolumnę C („Wartość”), w której mnożymy wartości wiersz po wierszu =A2*B2, a na samym dole sumujemy całość zwykłą funkcją =SUMA(C2:C6).

Dzięki omawianej formule w ogóle nie potrzebujemy kolumny C! Wpisujemy po prostu:

=SUMA.ILOCZYNÓW(A2:A5; B2:B5)

Funkcja sama w tle przemnoży przez siebie odpowiadające wiersze (A2 z B2, A3 z B3 itd.), a następnie zsumuje te wyniki. Błyskawicznie i czysto.

Przejdźmy do naszego głównego problemu. Mamy w arkuszu zestawienie pojazdów. W kolumnie F widnieje „Kolor”, a w kolumnie G „Cena netto”. Naszym zadaniem jest ułożenie samochodów od najtańszego do najdroższego.

Jeśli chcemy po prostu ułożyć wszystko w jednym wielkim worku, używamy standardowej funkcji POZYCJA.NAJW. W kolumnie I wpisujemy:

=POZYCJA.NAJW(G2; $G$2:$G$91; 1)

Excel bierze naszą cenę z komórki G2 i sprawdza, którą pozycję zajmuje w całym zakresie cen od G2 do G91. 3 argument, to informacja o sposobie tworzenia rankingu:

1 – rosnąco

0 – malejąco

Działa to świetnie, ale ma jedną wadę – jest całkowicie „ślepe” na nasze kategorie (kolory).

Tu zaczyna się prawdziwa magia. Klient prosi nas: „Zróbcie mi ranking cenowy, ale osobny dla każdego koloru. Chcę wiedzieć, który biały samochód jest najtańszy spośród białych, a który beżowy spośród beżowych”.

Funkcja POZYCJA.NAJW tutaj kapituluje. Ale my nie! Wykorzystujemy naszą niedocenianą funkcję SUMA.ILOCZYNÓW. W kolumnie J tworzymy ranking po kolorze za pomocą formuły:

=SUMA.ILOCZYNÓW( (F2=$F$2:$F$91) * ($G$2:$G$91 <= G2) )

Co tu się właściwie wydarzyło? Zmusiliśmy Excela do wykonania testu logicznego wewnątrz tablicy:

• Pierwszy nawias (F2=$F$2:$F$91) bierze kolor z naszego bieżącego wiersza i porównuje go z całą tablicą kolorów w wyznaczonym zakresie.

• Drugi nawias ($G$2:$G$91<=G2) sprawdza warunek cenowy – porównuje cenę z naszego wiersza z całą tablicą cen, sprawdzając, które z nich są mniejsze lub równe.

Excel przelicza te warunki na PRAWDA/FAŁSZ (czyli 1 i 0), mnoży je przez siebie i sumuje. W efekcie funkcja po prostu „liczy”, ile jest w naszej tabeli samochodów w tym samym kolorze, które są tańsze od tego konkretnego wiersza. Wynik? Idealny, dynamiczny ranking wewnątrz kategorii!

Czasami w internecie lub w bardziej zaawansowanych arkuszach spotkacie się z innym zapisem tej samej logiki. Możemy użyć takiej formuły:

=SUMA.ILOCZYNÓW( –(F2=$F$2:$F$91); –($G$2:$G$91<=G2) )

To dokładnie to samo! Co robią te dziwne dwa minusy (–)?

–(F2=$F$2:$F$91):  Excel generuje tablicę wartości PRAWDA/FAŁSZ. Problem w tym, że funkcja SUMA.ILOCZYNÓW (SUMPRODUCT) jest zaprojektowana do pracy na liczbach, a nie na wartościach logicznych. Operator podwójnego minusa (tzw. „dwuwymiarowy negator”) wymusza konwersję: PRAWDA staje się 1, a FAŁSZ staje się 0.

–($G$2:$G$91<=G2): Ta sama historia. Tablica logiczna dla ceny jest zamieniana na tablicę jedynek i zer.

Mnożenie wewnątrz funkcji: Teraz SUMA.ILOCZYNÓW otrzymuje dwie tablice numeryczne (np. {1,0,1,0} i {1,1,0,0}). Przemnaża odpowiadające sobie elementy (1*1=1, 0*1=0, 1*0=0, 0*0=0), co daje nam trzecią tablicę: {1,0,0,0}. Jedynki pojawiają się tylko tam, gdzie oba warunki były spełnione.

Finalne sumowanie: Na koniec funkcja sumuje te przemnożone wyniki (1+0+0+0 = 1). I to jest nasza liczba pojazdów spełniających kryteria, czyli nasz ranking.

Dwie wersje formuły SUMA.ILOCZYNÓW osiągają ten sam cel – zliczają pasujące rekordy wewnątrz tablicy. Wersja z * jest bardziej „nowoczesna” i dla wielu bardziej czytelna, ale wersja z — jest klasycznym, bardzo stabilnym rozwiązaniem, które warto znać.

Wyobraźmy sobie, że w kolumnie A mamy dokładną datę produkcji pojazdu, w F jego kolor, a w G cenę. Szef prosi o sumę wartości tylko białych aut wyprodukowanych w 2026 roku.

Zamiast dodawać nową kolumnę z funkcją =ROK(), aby w ogóle móc przefiltrować dane, załatwiamy to jedną, elegancką formułą:

=SUMA.ILOCZYNÓW(  (ROK($A$2:$A$91) = 2026 ) * ( $B$2:$B$91 = „Biały” ) * ($G$2:$G$91) )

Jak to działa? Funkcja „w locie” weryfikuje rok (wirtualnie wyciągając go z pełnej daty) oraz sprawdza kolor. Jeśli oba warunki są spełnione (czyli dają wartość 1), mnoży je przez cenę w danym wierszu. Na koniec sumuje dopasowane wyniki. Szybko, czysto i bez bałaganu w arkuszu!

Migrujemy na te bardziej zaawansowane rozwiązania z kilku prostych powodów. Co na tym zyskujemy w codziennej pracy? Przede wszystkim oszczędzamy mnóstwo miejsca i zachowujemy idealną przejrzystość arkusza, bo całkowicie eliminujemy zbędne kolumny pomocnicze.

Zyskujemy też niesamowitą elastyczność – gdy tylko tego potrzebujemy, po prostu dopisujemy kolejny nawias ze znakiem mnożenia i błyskawicznie dodajemy nowe warunki do rankingu (np. po kolorze oraz dodatkowo po historii pojazdu). No i najważniejsze: unikamy błędów. Im rzadziej bawimy się „ręcznie” w filtrowanie, kopiowanie i wklejanie danych, tym mniejszą mamy szansę, że coś niechcący zepsujemy w naszym raporcie.

Czujesz, że Twój Excel kryje przed Tobą jeszcze więcej takich tajemnic? Gubisz się w gąszczu formuł, a praca z danymi zajmuje Ci zbyt wiele cennego czasu?

Imperium Szkoleniowe jest tu po to, aby Ci pomóc! Nauczymy Cię, jak myśleć analitycznie i automatyzować nudne procesy. Rozwiążemy Twoje palące problemy z raportami i pokażemy, jak pracować mądrzej, a nie ciężej. Nasi trenerzy to praktycy, którzy codziennie mierzą się z takimi samymi wyzwaniami jak Ty.

Skontaktuj się z nami i sprawdź nasze szkolenia z Excela – od podstaw po programowanie VBA czy TypeScript. Zamieńmy frustrację w czystą efektywność!