fbpx

Dynamiczne formuły tablicowe (formuły rozlewające się) w Excelu

Wstęp

Dynamiczne formuły tablicowe (formuły rozlewające się) to takie formuły gdzie wprowadzamy odpowiednią funkcję tylko do jednej komórki, a efekt jest widoczny dla całego zakresu. W trzecim kwartale 2018 Microsoft zaprezentował dynamiczne formuły tablicowe dla wersji Excela 365. Najbardziej popularne funkcje dynamiczne dostępne w wersji 365 to SORTUJ(), FILTRUJ(), LOSOWA.TABLICA(), SORTUJ.WEDŁUG(), X.WYSZUKAJ(), SEKWENCJA(), UNIKATOWE(). Wyeliminowano w nich potrzeby starszych wersji – użycia klawiszy Ctrl + Shift + Enter zatwierdzających formuły tablicowe. Teraz wystarczy formułę tablicową zatwierdzać enterem.

Omówienie funkcji rozlewających

Poniżej zaprezentowano kilka przykładów funkcji rozlewających się. Zasada tworzenia takich funkcji jest zawsze taka sama.

Funkcja SORTUJ()

W przykładzie poniżej dane w kolumnie B zostały posortowana w Kolumnie E przy pomocy funkcji Sortuj(). Funkcja została wykonana w komórce E2 i została „rozlana” dla wszystkich wierszy z danymi.

Dynamiczne formuły tablicowe
Dynamiczne formuły tablicowe

SORTUJ.WEDŁUG()

W tym zadaniu należało posortować tabelkę względem kolumny Sprzedaż od największych do najmniejszych. Funkcję należy wprowadzić do komórki G2. Po zatwierdzeniu funkcja „rozleje się” uzupełniając resztę danych.

Dynamiczne formuły tablicowe

Efekt:

Dynamiczne formuły tablicowe

Co ciekawe jeżeli zajrzymy do każdej innej komórki w naszym nowopowstałym zakresie to zauważymy, że funkcja w pasku formuły jest wyszarzana i nie można jej ani usunąć ani edytować. Możemy to zrobić tylko z poziomu komórki G2 – komórki, w której budowaliśmy formułę.

Dynamiczne formuły tablicowe

Symbole # oraz @

Symbole w Excelu istnieją od dawna. Ich zastosowanie może bardzo pomóc w efektach graficznych w codziennej pracy. Możemy wstawiać takie symbole jak potęgowanie, ułamki, znaki niewystępujące w naszej wersji językowej (np. niemieckie umlauty lub cyrylicę), strzałki, znaczki, a także znacznie więcej. Znajdziemy je na karcie Wstawianie w sekcji Symbole po prawej stronie. Takie symbole nie stanowią niczego więcej niż efektów graficznych.

Natomiast dużo ciekawsze jest zastosowanie takich symboli jak # lub @.

Są to tak zwane operatory przecięcia pośredniego. Służą do obsługi tablic dynamicznych. Przecięcie pośrednie służy do zwrócenia określonej wartości przez Excela w przypadku gdy użytkownik zaznacza zakres komórek. W przypadku gdy zakresem jest tylko jedna komórka i użyjemy @ to wynikiem będzie ten sam element. Jeżeli wartość jest zakresem, wtedy wynikiem będzie komórka z tego samego wiersza lub kolumny co formuła.

Przykład 1

Wynikiem poniższego działania będzie 2 dlatego że leży na przecięciu wskazanej przez nas kolumny (B6:B9) i wiersza, w którym wprowadziliśmy działanie (wiersz 7).

Dynamiczne formuły tablicowe

Przykład 2

Wynikiem poniższego działania będzie 17 w komórce H3. Następnie taką komórkę przeciągamy w dół

Dynamiczne formuły tablicowe

Przykład 3

Jeżeli użyjemy funkcji sortuj względem tablicy to Excel zwróci tę tablice posortowaną względem kolumn zaczynając od komórki w której wprowadziliśmy funkcję.

Dynamiczne formuły tablicowe
Dynamiczne formuły tablicowe

Natomiast jeśli przed Sortuj wstawimy operator przecięcia pośredniego @ to Excel zwróci pierwszą (najmniejszą) wartość z kolumny.

Dynamiczne formuły tablicowe

Operator @ w formułach rozlewających

Operator @ to tak zwany operator przecięcia pośredniego związany z formułami rozlewającymi. Operator @ można wykorzystać stosując formuły tablicowe w momencie gdy nie chcemy uzyskać całej tablicy. Poniżej wykorzystano funkcję SORTUJ.WEDŁUG() względem kolumny Sprzedaż malejąco.

Dynamiczne formuły tablicowe
Dynamiczne formuły tablicowe

W przypadku wykorzystania operatora @ można otrzymać konkretny i zamierzony fragment tablicy.

Dynamiczne formuły tablicowe

Idąc w drugą stronę – co się stanie jeśli z funkcji usuniemy symbol @?

Gdy funkcja zwraca pojedynczą wartość, usunięcie @ nic nie zmieni

Gdy funkcja zwraca zakres, usunięcie @ wywoła efekt rozlania na sąsiadujące komórki.

Operator # w formułach rozlewających

Gdy korzystamy z funkcji rozlewających możemy w kolejnych funkcjach wykorzystać te symbole do zautomatyzowania pracy. Przykładowo poniżej wykorzystano funkcję LOSOWA.TABLICA() aby zwrócić odpowiedni zakres przy użyciu efektu rozlania, a obok użyto funkcji SUMA() do zsumowania komórek, ale wraz z symbolem #. Ten symbol działa tylko przy zastosowaniu funkcji dynamicznych.

Dynamiczne formuły tablicowe

Czy można stosować operator # przy użyciu funkcji nie dynamicznej?

Tak, pod warunkiem, że zakres, który wykorzystujemy odnosi się do funkcji dynamicznej.

Przykład 1

Poniżej zaprezentowano użycie operatora # w funkcji dynamicznej Filtruj() tak aby uzyskać wartości powyżej 2000.

Dynamiczne formuły tablicowe

Przykład 2

Na poniższym przykładzie funkcja ILE.NIEPUSTYCH()

Dynamiczne formuły tablicowe

Zobacz również

Szkolenia

udostępnij:
Gratulacje!
Wniosek został wysłany prawidłowo.
Dziękujemy za przesłanie zapytania. Skontaktujemy się z Tobą najszybciej jak to możliwe.