To my, jako użytkownicy Excela, sami decydujemy kiedy dana formuła jest „tablicowa” bądź „nietablicowa”. W pierwszym przypadku wystarczy, gdy przy wpisywaniu odpowiedniej formuły zastosujemy kombinację klawiszy Ctrl+ Shift+ Enter. Operacja ta pozwoli na wykorzystanie formuły do obliczeń, jednakże nie na pojedynczych komórkach, ale na tablicach.

Zmiana wykresu za pomocą listy rozwijanej

Zacznijmy od końca – poprzez zastosowanie formuły tablicowej oraz listy rozwijanej Odbiorca może samodzielnie zmieniać zakres wykresu. Przy okazji widzimy, jak efekt ruchu wspiera proces analizy, o czym pisaliśmy już w artykule o nowościach w Excelu 2013. Jak to osiągnąć?

Formuła tablicowa – co to takiego?

Formuły tablicowe umożliwiają obliczenia, których nie można wykonać wykorzystując inne formuły bądź po prostu ułatwiają skomplikowane obliczenia. Za pomocą formuł tablicowych można wykonać poniższe operacje:

• Zliczanie znaków w zakresie komórek;
• Sumowanie liczb spełniających określone kryteria;
• Sumowanie co n-tej wartości z zakresie komórek;

Formuły tablicowe są nazywane także formułami CSE, ponieważ do ich uruchomienia należy nacisnąć klawisze CTRL+SHIFT+ENTER. Formuły tablicowe wykorzystuje się na tablicach, czyli pewnych zbiorach elementów. Elementy mogą znajdować się w jednym wierszu (tablica jednowymiarowa), w kolumnie (pionowa tablica jednowymiarowa) lub w wielu wierszach i kolumnach (tablica dwuwymiarowa). Omawiana formuła może wykonywać wiele obliczeń na jednym lub wielu elementach tablicy. Formuła tablicowa może zwracać jeden (formuła jednokomórkowa) lub wiele wyników (formuła wielokomórkowa). Na poniższym przykładzie zastosujemy formuły tablicowe oraz listy rozwijane w celu automatycznej aktualizacji wykresu– wykorzystana będzie suma.

Jak stosować formuły tablicowe?

Poniższe dane kopiujemy do pustego skoroszytu i wklejamy zaczynając od komórki A1.

Następnie przygotowujemy pod tabelą podsumowanie danych (od komórki A32). Może wyglądać jak poniżej:

W typie, kanale sprzedaży oraz produkcie stosujemy listy rozwijane – analizowanie danych będzie wygodniejsze.

W arkuszu roboczym przygotowujemy zakres danych do poszczególnych list. Jako źródło zaznaczamy odpowiednio dane : dla typu – pierwsza kolumna, dla kanału sprzedaży– środkowa kolumna, dla produktu – ostatnia kolumna. Do wypełnienia tabeli z podsumowaniem można wykorzystać złożone formuły, jednakże w tym przypadku najbardziej efektywną będzie formuła tablicowa.

Następnie trzymając kursor wewnątrz formuły jednocześnie naciśnijmy Ctrl+ Shift+ Enter. Po tej operacji formuła będzie wyglądać jak poniżej:

Formułę przeciągamy do wszystkich pustych komórek w tabeli. Zastosowana formuła uwzględnia pięć tablic : typ, kanał sprzedaży, produkt, lata oraz dane liczbowe i zwraca wynik dla poszczególnych warunków określonych we wskazanych komórkach.

Gdy zmienimy typ, kanał sprzedaży bądź produkt, wartości w tabeli przeliczą się automatycznie zgodnie z danymi źródłowymi.

W celu bardziej efektownej prezentacji danych wstawiamy wykres kolumnowy na podstawie podsumowania powyżej (wraz ze zmianą danych w tabeli, wykres będzie się zmieniał automatycznie – oczywiście, o ile nie mamy wyłączonego w pliku przeliczania automatycznego). Formatujemy wykres.

Wady formuł tablicowych

Formuły tablicowe są niewątpliwie bardzo użyteczne przy wykonywaniu skomplikowanych obliczeń, ale niestety nie są wolne od wad:

• Zdarzą się zapomnieć o konieczności wykorzystania kombinacji Ctrl+Shift+Enter;
• Użytkownicy raportu mogą nie wiedzieć jak korzystać z formuł tablicowych. Najlepiej zapoznać adresatów raportu z tym rodzajem formuł;
• Zbyt duże formuły tablicowe mogą spowolnić wykonywanie obliczeń;

Plik do pobrania

Szczegóły techniczne znaleźć można w pliku: Wykorzystanie formuły tablicowej oraz listy rozwijanej do aktualizacji wykresu.xlsx