Funkcje Tablicowe

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Funkcje Tablicowe.xlsx tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji

Przykłady dla wszystkich lekcji szkolenia Excel 2007: ExcelSzkolenie.pl Cwiczenia Excel 2007.zip

Funkcje Tablicowe są dość rzadko używane w biznesie, a pomocne przede wszystkim w przypadku zaawansowanych obliczeń matematycznych i statystycznych. Lekcja ta ograniczy się tylko do kilku prostych przykładów, osoby zainteresowane tematem i posiadające wysokie zdolności analityczne, z pewnością będą mogły tworzyć znacznie bardziej użyteczne i ciekawe funkcje tego typu.

Przykład 1.

Funkcja TRANSPONUJ

(Arkusz: ‘Funkcje Tablicowe 1’)

Funkcja TRANSPONUJ jest najprostszym przykładem Funkcji Tablicowych.

W tym przykładzie chcielibyśmy dane sprzedaży ustawione pionowo pokazać poziomo tworząc łącza do pionowej tabeli.

Zaznaczamy obszar F3:H3, w którym ma znaleźć się tablica wynikowa.

Wprowadzanie funkcji tablicowej zawsze rozpoczynamy od naciśnięcia przycisku F2.

Wpisujemy =TRANSPONUJ(C3:C5), podczas wpisywania Excel będzie podawał podpowiedź z nazwą funkcji i jej składnią.

Po wprowadzeniu funkcji tablicowej zawsze zakańczamy wprowadzanie wciśnięciem Ctrl+Shift+Enter.

Nie wprowadzamy z klawiatury symboli nawiasów klamrowych, Excel sam je dopisze po wciśnięciu Ctrl+Shift+Enter.

W efekcie powyższych działań w komórkach F3:H3 mamy funkcję tablicową zapisaną w nawiasach klamrowych {}.

Pomimo tego że funkcja ‘wygląda jakby byla wprowadzona w 3 komórkach jest to jedna funkcja i wprowadzenie zmian w którejkolwiek komórce powoduje zmiany we wszystkich komórkach.

Zmiany wprowadzamy także rozpoczynając od wciśnięcia F2, a po ich wprowadzeniu ‘wychodzimy’ z funkcji tablicowej wciskając Ctrl+Shift+Enter.

W przypadku tablic zmiana części tablicy nie jest możliwa, np. w powyższym przykładzie nie będzie możliwe dodanie kolumny pomiędzy ‘F’ i ‘G’.

Nie będzie także możliwe wprowadzenie jakichkolwiek zmian inaczej niż poprzez rozpoczęcie wciskając F2 i zakończenie przez Ctrl+Shift+Enter.

W wypadku wszystkich takich prób wyświetlony zostanie komunikat.

Funkcję tablicową najprościej skasować poprzez zaznaczenie całego zakresu w którym się znajduje i wciśnięcie klawisza ‘Del’.

Przykład 2.

Funkcje SUMA i JEŻELI wykorzystane jako funkcje tablicowe

(Arkusz: ‘Funkcje Tablicowe 2’)

Większość ‘zwykłych’ funkcji może być wykorzystana jako funkcje tablicowe. 

W tym przykładzie chcielibyśmy zsumować wydatki dla ‘Kategorii 3’ w Polsce i Rosji.

Funkcje SUMA oraz funkcja JEŻELI mogą być wykorzystana do utworzenia naszego własnego odpowiednika funkcji SUMA.JEŻELI która będzie sumowała dane z wielu kolumn, czego funkcja SUMA.JEŻELI nie potrafi.

W poniższym przykładzie sumujemy wiele wyników funkcji: JEŻELI, które przyjmują wartości 0 jeśli Kategoria jest inna niż ‘Kategoria 3’ lub wartość wydatków jeśli jest jej równa.

{=SUMA(JEŻELI($B$4:$B$14=$H$4;$D$4:$E$14;0))}

Mówiąc bardziej obrazowo, funkcja JEŻELI zostanie uruchomiona 22 razy ponieważ tyle komórek znajduje się w zakresie D4:E14 i zwróci wartości wydatków dla ‘Kategorii 3’ lub zera, wszystkie te wartości zostaną zsumowane przez funkcję SUMA. 

Przykład 3.

(Arkusz: ‘Funkcje Tablicowe 3’)

W przykładzie 2 połączyliśmy funkcję jeżeli z funkcją suma. Na tej samej zasadzie możemy łączyć funkcję jeżeli z wieloma innymi funkcjami.

Poniżej pokazuję jak możemy utworzyć funkcję tablicową wyszukującą minimum dla produktów z Kategorii 1.

Dzięki odpowiedniemu adresowaniu funkcję tą można skopiować na poniższe komórki i znaleźć minimum dla każdej z kategorii.

Po napisaniu funkcji raz nie będziemy oczywiście tworzyć jej od nowa dla średniej i maksimum, szybciej będzie ją skopiować i użyć polecenia zamień (Ctrl+H) zamieniając MIN odpowiednio na ŚREDNIA i MAX.

W drugiej tabeli sumuję przychody dla produktów, których koszty są powyżej 500. Podobnie jak powyżej warunek jest w innej kolumnie (Koszty) niż kolumna sumowana (Przychody).

Ale warunek może też być w tej samej tabeli. Poniżej przedstawiona formuła sumuje przychody tylko dla produktów, których przychody przekraczają 600.

W tym przykładzie kwota przychodów nie jest wpisana w formule ale znajduje się w innej komórce której adres podajemy w formule.

Wreszcie w ostatnim przykładzie warunkiem jest wynik porównania tablic. Sumowane są przychody tylko dla produktów, dla których przychody przewyższają koszty.

Jak widać więc możliwości jest bardzo wiele, jeżeli porównujemy tablicę z pojedynczą liczbą lub adresem, każdy element tablicy będzie z tą liczbą / adresem porównywany. Jeżeli porównamy 2 tablice oczywiście muszą mieć one tyle samo elementów i każdy z elementów jest porównywany z odpowiednim (będącym w tej samej kolejności) elementem 2 tablicy.

Podobnie jak wcześniej, po napisaniu funkcji na sumę, funkcje tablicowe dla średniej, minimum i maksimum utworzymy poprzez skopiowanie i zmiany w funkcji sumującej.

Przykład 4.

(Arkusz: ‘Funkcje Tablicowe 4’)

W poniższym przykładzie prezentuję funkcję tablicową, która dla rekordów spełniających warunek wylicza średnią ważoną.

Funkcja tablicowa {=SUMA(JEŻELI($C$5:$C$24=$M5;D$5:D$24*H$5:H$24;0))/SUMA(JEŻELI($C$5:$C$24=$M5;H$5:H$24;0))} składa się z 2 elementów:

Pierwszy element SUMA(JEŻELI($C$5:$C$24=$M5;D$5:D$24*H$5:H$24;0)) dla rekordów spełniających warunek $C$5:$C$24=$M5 przemnaża marżę procentową D$5:D$24 przez sprzedaż H$5:H$24 uzyskując w ten sposób marżę kwotową dla rekordów spełniających warunek.

Drugi element to już zwykłe połączenie sumy i jeżeli, które przerabialiśmy także w poprzednich przykładach. SUMA(JEŻELI($C$5:$C$24=$M5;H$5:H$24;0)) Ten element sumuje sprzedaż dla rekordów spełniających warunek, aby podzielić przez nią marżę kwotową wyliczoną przez pierwszy element.

Funkcje tablicowe dają bardzo dużo możliwości, powyżej przedstawiłem tylko krótki wstęp do nich.

Jeszcze jeden przykład wykorzystania funkcji tablicowych znajduje się w lekcji Tabela Danych, zachęcam do zapoznania się z nim.