Formuły tablicowe w Excelu

Suma wszystkich przychodów

Zacznijmy od prostego przykładu – mamy tabelkę sprzedaży, gdzie widzimy odpowiednio kolumny Produkt, Cenę i Ilość (rys. 1). Dodatkowo mamy kolumnę z obliczonym przychodem, czyli po prostu przemnożoną wartością z kolumny Cena razy wartość z kolumny Ilość.

Nas nie interesuje pojedyncza komórka przychodu, ale przychód całkowity. Ponieważ przychód już jest obliczony dla każdej transakcji sprzedaży, to możemy po prostu je zsumować (=SUMA(D2:D10)).

W tym podejściu istnieje drobna niedogodność – musimy obliczyć przychód każdej transakcji, żeby uzyskać wynik końcowy, który nas interesuje. Powoduje to przede wszystkim konieczność wstawienia dodatkowej kolumny obliczeniowej, a tym samym zwiększenie objętości pliku i dodatkowe obciążenie procesora. Żeby uniknąć zbędnych obliczeń, możemy obliczyć całkowity przychód w jednej formule (formule tablicowej).

Możemy skorzystać z funkcji SUMA, w której zamieścimy wyniki poszczególnych iloczynów – {=SUMA(B2:B10*C2:C10)} (rys. 2).

Istotne jest dla nas, jak dokładnie Excel liczy powyższy zapis mnożenia dwóch tablic (B2:B10*C2:C10). Excel mnoży przez siebie elementy poszczególnych tablic, czyli pierwszy element z pierwszej tablicy (B2) mnoży przez pierwszy element drugiej tablicy (C2) itd. W ten sposób uzyskujemy przychód z wszystkich transakcji wewnątrz funkcji SUMA ({400;20;20;700;4473;2000;60;1750;150}) i w ten sposób możemy uzyskać interesujący nas wynik za pomocą jednej formuły, ale musimy pamiętać o jednej istotnej rzeczy – musimy zatwierdzić formułę za pomocą kombinacji klawiszy Ctrl + Shift + Enter, ponieważ funkcja SUMA, nie radzi sobie z obliczeniami na tablicach w normalnym trybie i bez tej kombinacji klawiszy przy jej zatwierdzaniu zwróci niepoprawny wynik.

To, że formuła została zatwierdzona jako formuła tablicowa, możemy poznać po tym, że w pasku formuły jest ona otoczona nawiasami klamrowymi { }. Te nawiasy pełnią rolę informacyjną i nie ma ich faktycznie w formule.

Funkcja SUMA nie radzi sobie z obliczeniami na tablicach, ale istnieją funkcje, dla których nie jest to problemem. Jedną z takich funkcji jest funkcja SUMA.ILOCZYNÓW. Nawet jej poszczególne argumenty są opisane jako tablice (rys. 3).

Funkcja SUMA.ILOCZYNÓW mnoży przez siebie kolejne elementy wszystkich podanych tablic/zakresów, a na koniec sumuje uzyskane wyniki. Ponieważ funkcja SUMA.ILOCZYNÓW radzi sobie z operacjami na tablicach, nie musimy jej zatwierdzać za pomocą kombinacji klawiszy Ctrl + Shift + Enter. Właśnie zapominanie o tym sposobie zatwierdzania jest najczęstszym problemem związanym z formułami tablicowymi, dlatego jeśli tylko możemy (i nie spowoduje to większego obciążenia procesora), staramy się pisać takie formuły tablicowe, które nie wymagają zatwierdzania tą specjalną kombinacją klawiszy.

Uwaga 1.

Na początku 2019 roku Microsoft planuje wprowadzić dla subskrybentów Office 365 nowy sposób obliczania formuł tablicowych, który m.in. nie będzie wymagał zatwierdzania żadnej formuły za pomocą skrótu Ctrl + Shift + Enter. Więcej na ten temat pod koniec artykułu.

Uwaga 2.

Warto tutaj jeszcze zwrócić uwagę na to, że wiele funkcji Excela działa na zakresach danych (np.: B2:B10), które wyglądają jak tablice, ale ze względu na to, że obliczenia na nich wykonywane są na innej zasadzie niż na tablicach (chociażby w VBA), to nie są w stanie sobie poradzić z obliczeniami na tablicach, natomiast w drugą stronę nie ma takich problemów, czyli np.: funkcja SUMA.ILOCZYNÓW może pracować zarówno z tablicami danych, jak i zakresami danych.

MIN.JEŻELI i MAX.JEŻELI

Kolejnym przykładem, jaki omówimy, jest obliczanie minimalnej/maksymalnej wartości (np.: produkcji/sprzedaży) po warunku, czyli przykładowo chcemy obliczyć minimalną wartość produkcji dla poszczególnych pracowników (rys. 4), czyli po warunku.

Podobne funkcjonalności realizuje np.: funkcja SUMA.JEŻELI, a od wersji Excel 2019 zostały wprowadzone funkcje MIN.WARUNKÓW i MAKS.WARUNKÓW, ale jeśli mamy wcześniejszą wersję Excela, musimy sami sobie stworzyć formułę, która wykonuje obliczenia w analogiczny sposób.

Z naszego zapisu widzimy, jakich funkcji potrzebujemy użyć: MIN i JEŻELI. Zaczniemy od funkcji JEŻELI, za pomocą której sprawdzimy warunek, a tym samym ograniczymy zwracane z zakresu liczby (sprawimy, że zostaną one pominięte przy obliczeniach w funkcji MIN).