Dynamiczne formuły tablicowe – rozlane formuły

Zwykłe formuły tablicowe mają ogromne możliwości. Dzięki nim możemy w jednej komórce otrzymać wynik jaki normalnie otrzymalibyśmy po zastosowaniu kilku formuł w osobnych komórkach. Mimo to nie są zbyt często stosowane z jednego prostego powodu. Ich użycie jest trudne i wymaga innego podejścia. W zwykłych formułach mamy jeden wynik dla każdej formuły. W tablicowych za to operujemy tablicami danych, więc musimy mieć na uwadze że przekształcamy jednocześnie wiele wartości. Musimy też przewidywać jakich rozmiarów będzie tablica z naszymi wynikami przeliczeń. Na szczęście Excel wyeliminował te problemy wprowadzając dynamiczne formuły tablicowe.

Dynamiczne a zwykłe formuły tablicowe – porównanie

Pierwsza różnica jest taka, że do zatwierdzania dynamicznych formuł tablicowych nie musimy  używać kombinacji Ctrl+Shift+Enter. Wspisujemy je po prostu tak zwykłe formuły. Poprawia to komfort pracy, upraszcza ją oraz eliminuje część błędów. Łatwo można było zapomnieć o zastosowaniu kombinacji Ctrl+Shift+Enter pry wprowadzaniu czy edytowaniu formuł tablicowych. A to przekładało się na błędne wyniki obliczeń.

Druga różnica to zakres wyników formuły. W zwykłych tablicach musieliśmy wprowadzić formułę tablicową na odpowiednim zakresie komórek, stworzyć tablicę wyników. W dynamicznych formułach tablicowych wprowadzamy formułę w jednej komórce, po czym następuje tzw. rozlanie formuły na tyle komórek ile w formule powstało wyników.

Trzecia różnica której wprost nie widzimy jako użytwownicy Excela, a została wprowadzona z dynamicznymi formułami tablicowymi to zmiana silnika obliczeniowego w Excelu. Jest to o tyle ważna rzecz, że teraz Excel sprawdza ile wyników powstaje w efekcie obliczeń dla każdej komórki. Jest to baza do tworzenia zupełnie nowych formuł i wprowadzania dużych usprawnień w wykonywaniu skomplikowanych obliczeń.

Dynamiczne formuły tablicowe – lista obecnie dostępnych formuł

Unikatowe(Tablica;By_col;Exacly_once)

To chyba najbardziej praktyczna i najbardziej oczekiwana formuła przez wielu użytkowników Excela. Stworzenie listy unikatowych wartości wcześniej wymagało albo użycia tabeli przestawnej albo formuł tablicowych. Pierwsze choć stosunkowo proste to wymagało odświeżania po każdej zmianie danych źródłowych. Drugie choć przeliczane na bieżąco to za to trudne w użyciu. Wymagany parametr jest tylko pierwszy, musimy podać z jakiego zakresu danych chcemy wyciągnąć wartości unikatowy. Drugi parametr określa czy unikatowość chcemy sprawdzać po wierszach czy kolumnach. W trzecim parametrze określamy czy chcemy otrzymać listę wszystkich unikatowych wartości czy tylko tych które występują dokładnie raz.

Funkcja UNIKATOWE ma większe możliwości niż by się wydawało na pierwszy rzut oka. Na przykład gdy użyjemy jej na dwóch kolumnach to stworzy nam listę unikatowych wartości z uwzględnieniem łącznie obu kolumn. Możliwości zastosowań jest o wiele więcej ale to już zostanie omówione w innym wpisie.

SORTUJ(Tablica; Indeks_sortowania; Kolejność_sortowania; Według_kolumny)

Druga bardzo przydatna formuła pozwala na automatyczne posortowanie wartości bez przekształceń danych źródłowych. Tutaj podobnie tylko pierwszy parametr jest wymagany do działania funkcji. Drugi parametr określa numer wiersza lub kolumny według, której chcemy sortować gdy funkcji używamy na większym zakresie niż jedna kolumna lub jeden wiersz. Trzeci parametr to sortowanie rosnąco (domyślnie) lub rosnąco. Ostatni parametr określa czy formuła ma działać na wierszach (domyślnie) czy na kolumnach.

Dobrą praktyką jest używanie sortowania na unikatowych wartościach. Dzięki temu otrzymujemy posortowaną listę, która świetnie nadaje się do budowy rozwijanych list czy słowników.

SORTUJ.WEDŁUG(Tablica; Wedłu_tablicy1; Kolejność_sortowania1; Według_tablicy2;…)

Funckja ta działa podobnie jak funckaj SORTUJ ale możemy tutaj ustalić kolejność sortowania według wielu kolumn. Inaczej mówiąc jest to formuła, która pozwala na to samo co sortowanie zaawansowane ale nie ingeruje w dane źródłowe. Najpierw podajemy zakres danych jakie mają być posortowanie. Ile kolumn zaznaczymy tyle otrzymamy wyników. Następnie podajemy kolumnę według której ma odbyc się sortowanie oraz określamy rodzaj sortowania tj. rosnąco czy malejąco. Dalej możemy ustalać kolejne kolumny według których będzie przeprowadzane sortowanie.

Dla przykładu mamy listę osób z ich wiekiem i wzrostem. Dzięki formule SORTUJ.WEDŁUG łatwo ustawimy że chcemy otrzymać listę osób posortowaną od najmłodszych do najstarszych a w drugiej kolejności od najwyższych do najniższych.

FILTRUJ(Tablica; Uwzględnienie; Jeśli_puste)

Kolejna przydatna funkcja, która ma bardzo wiele zastosowań. Dzięki tej funkcji otrzymamy tablicę wyników spełniających odpowiednie kryteria. Pierwszy parametr to zakres tablicy z której chcemy otrzymać wyniki. Drugi to zestaw kryteriów. Ostatni parametr określa wartość w przypadku gdy żaden rekord danych nie spełnia warunków filtrowania. Dla przykładu wyszukajmy osoby które mają wzrostu nie więcej niż 170cm. W tym przypadku chcemy otrzymać tylko listę imion.

Jak napisałem drugi parametr to określenie warunków filtrowania. Jak zatem wprowadzić tam kilka warunków? Uwzględnieni to tak naprawdę tablica wartości logicznych podanych warunków. Tablicę spełnienia kilku warunków otrzymujemy przez ich przemnożenie – iloczyn macierzy. I tak jeśli chcemy otrzymać listę osób które mają wzrostu nie więcej niż 170cm oraz mają więcej niż 26 lat to wprowadzamy formułę jak poniżej.

Funkcja filtruj ma ogromne możliwości, może zastąpić fragmentatory czy też obsłużyć przypadki gdy chcemy wyszukiwać poziomo według dwóch kryteriów.

SEKWENCJA(Wiersze; Kolumny; Początek; Krok)

To formuła która służy do wygenerowania zestawu wartości dla określonej liczby wierszy i kolumn. W swojej najprostszej postaci wygeneruje nam ciąg kolejnych liczb dla pierwszego wiersza, następnie dla drugiego itd. dla tylu kolumn ile podamy w drugim parametrze. Funkcja ta działa podobnie jak podwójna pętla w VBA.

Jakie może być zastosowanie tej formuły? Każde gdzie możemy wygenerować w określony sposób listę wartości. Na przykład możemy stworzyć formułę która wygeneruje nam listę dat na najbliższe X dni. Naszym X może być np. liczba dni płatności za fakturę dla danego klienta a listę wygenerowanych dat wykorzystamy do listy rozwijanej tak aby użytkownik na pewno wybrał odpowiednią datę.

LOSOWA.TABLICA(Wiersze; Kolumny; Minimum; Maksimum; Całkowite)

To rozbudowana funkcja LOS(). Jeśli podamy tylko liczbę wierszy i kolumn otrzymamy standardowe wartości losowe od 0 do 1 dla określonego zakresu komórek.

Kolejne trzy opcjonalne parametry to ogromne ułatwienie dla osób, które nie wiedziały jak wygenerować liczby z odpowiedniego przedziału wartości. Parametry te określają przedział losowanych wartości oraz czy chcemy otrzymać tylko wartości całkowite. Oczywiście łatwo można było to osiągnąć przy odpowiednich przekształceniach matematycznych funkcji LOS(). Dodanie liczby do wartości LOS() określa minimum zakresu, a przemnożenie funkcji LOS() określało szerokość zakresu wartości. Dzięki funkcji LOSOWA.TABLICA() wygenerujemy odpowiednią listę wartości w kilka sekund. Dla przykładu lista 8 wartości z zakresu 10-65 dla wartości całkowitych.

Dynamiczne formuły tablicowe to kolejna rewolucja w Excelu!

Jak widać dynamiczne formuły tablicowe mają ogromny potencjał oraz bardzo wiele zastosowań. Z jednej strony są bardzo proste w używaniu. Ich składnia jest prosta, nie wymaga łączenia wielu formuł tak jak to się ma przy zwykłych formułach tablicowych. Z drugiej strony działają bardzo szybko. Możliwości ich stosowania są większe niż nam się wydaje z tego względu, że musimy się do nich przyzwyczaić i o nich pamiętać w codziennej pracy. Dzięki temu z każdym tygodniem znajdziemy dla nich kolejne zastosowania, a nasze praca znacznie się usprawni.