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.
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.
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.
Efekt:
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łę.
Więcej na temat efektywnych metod stosowanych w celu automatyzacji pracy w Excelu dowiesz się na kursie – Kurs Excel Zaawansowany.
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).
Przykład 2
Wynikiem poniższego działania będzie 17 w komórce H3. Następnie taką komórkę przeciągamy w dół
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ę.
Natomiast jeśli przed Sortuj wstawimy operator przecięcia pośredniego @ to Excel zwróci pierwszą (najmniejszą) wartość z kolumny.
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.
W przypadku wykorzystania operatora @ można otrzymać konkretny i zamierzony fragment tablicy.
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.
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.
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.
Przykład 2
Na poniższym przykładzie funkcja ILE.NIEPUSTYCH(). Więcej o tej funkcji możesz się dowiedzieć na kursie – Kurs Excel średnio zaawansowany
Jeżeli podobał Ci się ten artykuł to zachęcam do zajrzenia na stronę z naszymi kursami, zwłaszcza Kursy Excel, z których dowiesz się znacznie więcej o Tabelach i technikach przyspieszenia pracy w Excelu.
