Szybki raport z wykorzystaniem formuł tablicowych

Przyjmijmy, że w arkuszu znajduje się lista towarów sprzedanych w pewnym okresie. Na jej podstawie potrzebujemy szybko sporządzić raport zawierający średnią liczbę sprzedanych sztuk, a także najmniejszą i największą. Wyniki mają dotyczyć każdego towaru.

Przykładowe dane sprzedażowe przedstawia rysunek 1.

Rysunek 1. Dane źródłowe do raportu

W pierwszej kolejności przygotujmy tabelę pomocniczą, w której zostaną zwrócone wyniki. W tym celu:

1. Wpiszmy nagłówki kolumn zgodnie z rysunkiem 2.

2. W kolumnie Towar wprowadźmy nazwy wszystkich towarów znajdujących się na właściwej liście.

Rysunek 2. Tabela pomocnicza

Tabela pomocnicza jest gotowa, można więc wprowadzić formuły. W tym celu:

1. Zaznaczmy komórkę F2 i wpiszmy do niej następującą formułę:

=ŚREDNIA(JEŻELI($B$2:$B$35=$E2;$C$2:$C$35;""))

i koniecznie zatwierdźmy ją kombinacją klawiszy [Ctrl]+[Shift]+[Enter], ponieważ jest to formuła tablicowa.

2. Skopiujmy ją do komórek poniżej.

3. Do komórki G2 wpiszmy formułę zwracającą zakup najmniejszej liczby sztuk danego towaru.

=MIN(JEŻELI($B$2:$B$35=$E2;$C$2:$C$35;""))

4. Zatwierdźmy ją wspomnianą kombinacją klawiszy i także skopiujmy poniżej.

5. W komórce H2 powinna znaleźć się formuła zwracająca największe zamówienia. Jak się pewnie domyślasz, będzie wyglądała bardzo podobnie do poprzednich:

=MAX(JEŻELI($B$2:$B$35=$E2;$C$2:$C$35;""))

Rysunek 3. Gotowy raport

Wyjaśnienie działania formuł:

Funkcja JEŻELI przeszukuje wszystkie komórki z zakresu B2:B35 w poszukiwaniu nazwy zgodnej ze znajdującą się w komórce E2. Na tej podstawie tworzona jest tablica wartości zawierająca liczby sztuk, które odpowiadają Towarowi 1. Na nich są przeprowadzane dalsze obliczenia za pomocą funkcji ŚREDNIA, MAX lub MIN.