Zaawansowane formuły arkuszowe w analizie danych

SUMA.ILOCZYNÓW()

Załóżmy, że mamy sytuację taką, jak pokazana jest na Rysunku 1.

Mamy podane informacje o cenie jednostkowej sprzedawanych towarów oraz o liczbie każdego sprzedanego towaru. Na tej podstawie w komórce E2 chcemy obliczyć całkowitą wartość wszystkich sprzedanych towarów. Oczywiście można by to zrobić w ten sposób, że w kolumnie D obliczymy za pomocą prostego mnożenia wartość sprzedaży dla każdego towaru osobno, a następnie w odpowiedniej komórce użyjemy funkcji SUMA(). Jednak może się zdarzyć, że dodatkowa kolumna nie będzie nam potrzebna, albo wręcz z jakiegoś powodu nie będziemy mogli jej stworzyć. W tym celu właśnie możemy wykorzystać funkcję SUMA.ILOCZYNÓW() (w angielskiej wersji Excela jest to funkcja SUMPRODUCT()).

Jest to funkcja, w której jako kolejne argumenty podajemy zakresy, których poszczególne elementy zostaną przez siebie pomnożone. Ważne jest jednak to, aby wszystkie podawane zakresy miały te same wymiary. W naszym przypadku w komórce E2 możemy więc wprowadzić następującą formułę:

=SUMA.ILOCZYNÓW(B2:B8;C2:C8)

Tak wprowadzona formuła będzie działać w ten sposób, że wartość z pierwszej komórki z pierwszego zakresu pomnoży przez wartość z pierwszej komórki z drugiego zakresu i do tego doda drugą wartość z pierwszego zakresu pomnożoną przez drugą wartość z drugiego zakresu itd. Innymi słowy, funkcja SUMA.ILOCZYNÓW() w naszym przypadku będzie wykonywać następujące działanie:

B2*C2+B3*C3+B4*C4+....B8*C8

Wynik działania funkcji SUMA.ILOCZYNÓW() jest pokazany na Rysunku 2.

Dzięki tej funkcji można więc uniknąć wstawiania dodatkowej kolumny i wyliczania wartości cząstkowych do sumy.

INDEKS()

Drugą bardzo przydatną funkcją przy analizie danych może okazać się funkcja INDEKS() (w angielskiej wersji Excela jest to funkcja INDEX()). Dzięki niej możemy znaleźć w pewnym zakresie danych wartość z komórki leżącej na przecięciu konkretnego wiersza i konkretnej kolumny. Załóżmy więc, że mamy sytuację taką, jak pokazano na Rysunku. 3.

Podane są różne towary i parametry ich dotyczące. W komórce K2 chcemy teraz znaleźć wartość Parametru 3 dla towaru o kodzie podanym w komórce I2. Pierwsza, niejako ręczna wersja funkcji INDEKS() wprowadzona do komórki K2 będzie wyglądać następująco:

=INDEKS(A1:G7;4;5)

Funkcja ma taką postać, ponieważ w pierwszej kolejności podajemy zakres danych, który ma być przeszukiwany i w którym są dane, które mają być zwrócone. Następnie podajemy numer wiersza, z którego dane mają być zwrócone, a następnie numer kolumny. Zwrócona zostanie wartość z komórki leżącej na przecięciu wiersza o podanym numerze i kolumny o podanym numerze. W naszym przypadku będzie to wartość z komórki E4, czyli 92.