fbpx
Minuty czytania: 3

Formuły tablicowe – gdy standardowa funkcja nie wystarczy

Formuły tablicowe wykorzystasz do rzeczy pozornie niemożliwych. Spróbuj za pomocą standardowej funkcji policzyć medianę wynagrodzeń dla handlowców, pracujących w regionie północ, od co najmniej 3 lat. Nie pomoże nawet tabela przestawna. A to tylko prosty przykład.

Formuły tablicowe pomogą Ci w tym i wielu innych złożonych obliczeniach. Co ciekawe, możesz zaangażować do pracy w ten sposób (tablicowy) wiele „standardowych” funkcji, np. ŚREDNIĄ, MEDIANĘ, JEŻELI etc.

Formuła tablicowa, czasami nazywana też CSE od CTRL + SHIFT + ENTER, które kończy jej wprowadzanie to sposób działania, a nie jakaś pojedyncza, tajemnicza funkcja. W artykule wyjaśnię Ci praktyczne działanie na przykładach, ale wykorzystanie formuł tablicowych to prawdziwy „temat rzeka”.

Ręka pisząca kredą na tablicy
Źrócło: Canva.com

Formuła tablicowa: podstawowe reguły

Używając na przykład funkcji JEŻELI, wprowadzasz ją do pojedynczej komórki, a sama funkcja pobiera argumenty również z pojedynczych komórek.

Formuła tablicowa tym różni się od standardowej funkcji, że może:

  • dokonywać „w tle” wielu obliczeń, zwracając wynik do pojedynczej komórki,
  • zwracać wynik swoich obliczeń do więcej niż jednej komórki jednocześnie.

W artykule skupimy się na tym pierwszym przypadku. Zanim przejdziemy do konkretnego przykładu, musisz pamiętać o kluczowej zasadzie – każdą formułę tablicową zatwierdzasz kombinacją klawiszy CTRL + SHIFT + ENTER. Bez tego formuła nie zadziała. W wyniku tego, na końcu i na początku formuły pojawiają się nawiasy klamrowe { }, na przykład:

{=SUMA(JEŻELI(A:A="Północ";JEŻELI(B:B="Klient X";C:C)))}

Formuły tablicowe: przykład z użyciem ŚREDNIEJ i JEŻELI

Aby zrozumieć działanie formuł tablicowych posłużmy się prostym przykładem. Będzie łatwiej, jeżeli przed dalszą lekturą pobierzesz ten plik. Masz 10 przypadków sprzedaży dla różnych handlowców, w różnych miastach. Twoim zadaniem jest policzyć średnią sprzedaż dla Marcina z Łodzi. Oczywiście możesz to samo zrobić np. w tabeli przestawnej, ale wykorzystamy ten uproszczony przykład, żeby zobrazować działanie formuł tablicowych.

Formuły tablicowe - tabela z danymi

Gdy wprowadzisz całą formułę do pojedynczej komórki i zatwierdzisz klawiszami CSE, „w tle” będą się działy pewne operacje 🙂 Przede wszystkim, formuła korzystając z właściwości funkcji JEŻELI, sprawdzi:

  • wszystkie komórki w kolumnie Miasto i jeżeli napotka tam Łódź – wirtualnie zaznaczy tam 1 (czyli prawda),
  • wszystkie komórki w kolumnie Handlowiec i napotykając Marcina – podobnie postawi 1,
  • sprzedaż pozostaje bez zmian, bo nie mamy tam żadnego warunku.
Formuły tablicowe - warunki

Jak widzisz w tabelce powyżej, są 3 wiersze w których warunek z miastem i handlowcem jest spełniony jednocześnie. Podczas moich szkoleń tłumaczę to jako mnożenie przez siebie każdej komórki w danym wierszu. I tak na przykład: w wierszu 1, mamy 0 x 1 x 26900. Wynik, rzecz jasna to 0, więc formuła nie weźmie tej wartości sprzedaży w końcowym wyniku. Z kolei, w wierszu 5, mamy 1 x 1 x 50680 – co daje wynik 50680. I oczywiście ta wartość sprzedaży zostanie uwzględniona przy wyliczaniu średniej.

No właśnie…, a gdzie tu średnia zapytasz? Bo do tej pory to tylko sprawdzamy warunki i mówimy PRAWDA lub FAŁSZ – jak w przypadku funkcji JEŻELI. Aby policzyć średnią, na wybranych w powyższy sposób wartościach trzeba użyć (niespodzianka!) funkcji ŚREDNIA 🙂

Formuły tablicowe - wybrane wartości, na podstawie spełnionych warunków

To co musisz zrozumieć, to fakt że ŚREDNIA zadziała na końcu (po wybraniu odpowiednich wartości spełniających warunki) ale w zapisie formuły umieścimy ją na początku. Generalnie w formułach Excela, funkcje umieszczone najbardziej wewnątrz działają jako pierwsze, a te na zewnątrz (od lewej) jako ostatnie. Jeżeli wiesz dlaczego tak się dzieje, podpowiedz innym w komentarzu.

{=ŚREDNIA(JEŻELI(Q15:Q24=Q10;JEŻELI(R15:R24=R10;S15:S24)))}

Opisane powyżej kroki dzieją się w ułamku sekundy. Chyba, że danych jest bardzo dużo i używasz wielu formuł tablicowych. W takich przypadkach odczujesz spowolnienie, w działaniu arkusza.

Formuły tablicowe: przykłady z użyciem MEDIANY i KWARTYLI

Powyższy przykład to tylko wierzchołek góry lodowej. Formuły tablicowe to naprawdę potężne narzędzie i potrafią znacznie więcej. Pokażę Ci jeszcze dwa przykłady, które mam nadzieję będą dla Ciebie użyteczne.

Liczenie średnich z wynagrodzeń jest kiepskim pomysłem. Ja częściej używam MEDIANY, KWARTYLI, DECYLI i DOMINANTY (w Excelu odpowiednia funkcja to WYST.NAJCZĘŚCIEJ.WART)

Trudność pojawia się, gdy trzeba policzyć medianę dla kilku różnych typów stanowisk, lokalizacji, czy kategorii zaszeregowania. Na poniższym przykładzie zobaczysz jak można sobie z tym poradzić, dzięki formułom tablicowym.

<

Chcesz lepiej poznać Excela? Zapisz się na Excelness News

.

Podsumowanie

Formuły tablicowe to „level master” jeżeli, chodzi o umiejętność posługiwania się funkcjami Excela. Jednak zrozumienie podstawowej koncepcji działania nie powinno sprawić dużych kłopotów. Mam nadzieję, że tym artykułem pomogłem Ci osiągnąć właśnie ten cel.

Życzę Ci owocnego wykorzystania „tablicówek” do wszelkich obliczeń, gdzie zawiedzie standardowa funkcja czy tabela przestawna.


Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.


0 Comments

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *