Excel - wprowadzenie do formuł tablicowych

Formuły tablicowe to niezwykle potężne narzędzie, za pomocą którego możesz wykonać obliczenia niemożliwe do zrealizowania przy użyciu klasycznych formuł. Jest to narzędzie, które po prostu musisz znać jeśli na poważnie myślisz o pracy w Excelu.

Kurs Excel - zaawansowane funkcje i formuły

4.9

(122 ocen)

Poznaj obszerny zestaw przydatnych funkcji i praktyczne przykłady użycia formuł... Dowiedz się więcej

Uwaga

Formuły tablicowe nie są odrębna funkcją Excela. Jest to to raczej inny, bardziej elastyczny sposób wykorzystania istniejących funkcji programu.

Przejdźmy do prostego przykładu, który powinien dobrze wyjaśnić podstawowe reguły tworzenia formuły tablicowej oraz zalety jakie płyną z jej użycia.

Definiowanie problemu

Popatrz na tę prostą tabelę, która posłuży jako podstawa naszego przykładu.

Od lewej do prawej zawiera ona następujące dane: miesiąc, ilość zamówień oraz łączną wartość tych zamówień. Ja chciałbym teraz sprawdzić jaka jest średnia wartość zamówienia, ale dla całego roku – średnia dla poszczególnych miesięcy w tym momencie mnie nie interesuje. Problem polega na tym, że nie mogę wyliczyć średniej wartości zamówienia dla roku jeśli wcześniej nie dodam pośrednich obliczeń dla każdego miesiąca. Jeśli będę korzystał z tradycyjnych rozwiązań (bez formuł tablicowych) to powinienem otrzymać coś takiego:

Najpierw w kolumnie E dodałem średnią wartość zamówienia dla każdego miesiąca (wartość z kolumny C podzielona przez wartość z kolumny B). Następnie na dole za pomocą funkcji ŚREDNIA wyliczyłem średnią wartość dla całego roku. Czyli dla obliczenia tej jednej wynikowej wartości musiałem użyć aż 12 formuł pośrednich. Nie tylko zajmuje to trochę czasu, ale również komplikuje znacznie strukturę arkusza. Warto więc rozważyć alternatywne rozwiązanie.

Kolejne podejście

Spróbuję teraz wszystkie formuły niejako wprowadzić do jednej komórki. W komórce C16 umieszczę następującą formułę:

=ŚREDNIA(C3:C14/B3:B14)

Próbuję tutaj wyliczyć średnią wartość z wyników dzielenia C przez B (czyli dzielę zakres komórek przez inny zakres). Taka formuła już na pierwszy rzut oka wygląda podejrzanie. I nie powinno to dziwić. Jeśli zatwierdzę formułę naciskając klawisz Enter to otrzymam błąd #ARG!. Tego się po prostu nie da zrobić przy użyciu tradycyjnych metod. Na szczęście nasz problem można bardzo łatwo rozwiązać stosując formuły tablicowe. Co ważne, nie muszę wiele zmieniać w mojej obecnej formule.

Formuła tablicowa

Jedyne co muszę zrobić to zaznaczyć komórkę z błędną formułą i nacisnąć klawisz F2, aby przejść do trybu edycji. Teraz bardzo ważna rzecz – zatwierdzam formułę ponownie, ale teraz używam kombinacji trzech klawiszy Ctrl+Shift+Enter. Moja formuła działa teraz prawidłowo i zwraca jak najbardziej prawidłowy wynik!

Co się stało? Zatwierdzając formułę za pomocą kombinacji tych trzech klawiszy nakazałem Excelowi traktować ją właśnie jako formułą tablicową. Warto teraz zaznaczyć komórkę z formułą ponownie i popatrzeć na pasek formuł powyżej adresu:

Zauważ, że formuła otoczona jest nawiasami klamrowymi. To właśnie po tych nawiasach możemy rozpoznać formułę tablicową. Tutaj ważna uwaga – te nawiasy są dodawane automatycznie przez Excela i nie powinno się ich do formuły dodawać ręcznie.

Jak to działa?

Teraz oczywiście pojawia się pytanie: jak to wszystko działa i czym się to różni od tradycyjnej formuły? Zrozumienie naszego przykładu nie powinno być trudne jeśli formułę tablicową przeanalizujemy od środka.

{=ŚREDNIA(C3:C14/B3:B14)}

Na początku wartości z kolumny C są dzielone przez wartości z kolumny B. Wszystko jest wykonywane po kolei wierszami, czyli najpierw C3 dzielone przez B3, potem C4 dzielone przez B4 itd. Uzyskujemy w ten sposób tablicę z 12 wynikami (bo tyle mam wierszy w zakresie). Teraz na tej tablicy program wykonuje operację umieszczoną na zewnątrz nawiasu, czyli liczy średnią z tej tablicy zawierającej 12 wartości. I ten właśnie sposób otrzymałem prawidłowy rezultat. Można by powiedzieć, że w formule tablicowej obliczenia wykonywane są od środka na zewnątrz.

Kurs Excel - zaawansowane techniki

Zobacz kurs

Kurs Excel - makra i VBA dla początkujących

Zobacz kurs

Kurs Excel 2019 od podstaw

Zobacz kurs

Drugi przykład

Myślę, ze warto omówić jeszcze jeden przykład, tak aby w pełni poznać główne idee formuł tablicowych. Popatrz na drugą tabelkę z danymi:

Mam tutaj po kolei: identyfikator produktu, ilość sztuk w magazynie oraz wartość jednej sztuki. I teraz mam pytanie: jaka jest łączna wartość wszystkich produktów w magazynie? Jeśli wyliczę to w sposób tradycyjny to oczywiście pojawi się problem, o którym już pisałem wcześniej. Muszę najpierw pomnożyć ilość i cenę dla każdego produktu i dopiero potem mogę te wyniki zsumować. Czyli mamy tutaj doskonałego kandydata do użycia formuły tablicowej. A formuła będzie wyglądać następująco:

=SUMA(I3:I12*J3:J12)

Trzeba oczywiście użyć kombinacji Ctrl+Shift+Enter do zatwierdzenia takiej formuły. Działa ona na prostej zasadzie. Najpierw wartości z kolumny I oraz J są mnożone aż do końca zakresu (i3J3,I4J4 itd.). Potem na tablicy zawierającej wyniki mnożenia jest wywoływana suma, które te wyniki do siebie dodaje. Czyli prosto i szybko, przy użyciu jednej formuły możemy obliczyć poszukiwany przez nas wynik.

Podsumowanie

Te dwa proste przykłady powinny dobrze wyjaśnić podstawowe zasada tworzenia formuł tablicowych. Jak widzisz ich użycie ma pewne bardzo istotne zalety. Można w wielu wypadkach uprościć znacznie obliczenia pomijając formuły pośrednie. Nie mówiąc już o tym, że są problemy, których nie da się inaczej rozwiązać jak przy użyciu formuł tablicowych.
Nie zapominaj o tym, że jest to temat dość złożony, a jego solidne opanowanie zajmie trochę czasu.