Funkcje Tablicowe

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

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

Przykład 1.

(Arkusz: ‘Funkcje Tablicowe 1’)

TRANSPONUJ

Funkcja TRANSPONUJ jest prostym przykładem Funkcji Tablicowych, których Excel oferuje dość dużo. Ponieważ są one dość rzadko używane w biznesie a są pomocne przede wszystkim w przypadku zaawansowanych obliczeń matematycznych i w statystyce, pozostałe funkcje tablicowe nie będą omawiane w ramach tego kursu.

Zaznaczamy obszar F3:H3, w którym ma znaleźć się tablica wynikowa, wprowadzanie funkcji tablicowej zawsze zaczynamy od naciśnięcia klawisza F2.

Wprowadzamy funkcję tablicową =TRANSPONUJ(C3:C5) po wprowadzeniu funkcji tablicowej zawsze zakańczamy wprowadzanie wciśnięciem Ctrl+Shift+Enter.

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

Pomimo tego że funkcja ‘wygląda jakby była 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 tym przypadku zamiast funkcji tablicowej można skopiować zakres ‘B3:B5’, z menu: ‘Wklej specjalne’ wybrać ‘Wklej linki’, dodać znaki $ do linków, po czym skopiować linki i ponownie z menu ‘Wklej specjalne’ wybrać Transpozycja. To rozwiązanie ma tą przewagę, że w przypadku tablic zmiana części tablicy nie jest możliwa (np. w powyższym przykładzie dodanie kolumny pomiędzy ‘F’ i ‘G’).

Przykład 2.

(Arkusz: ‘Funkcje Tablicowe 2’)

SUMA.JEŻELI wykorzystanie jako funkcji tablicowej

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.

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))}

Wprowadzanie funkcji tablicowej zawsze rozpoczynamy od naciśnięcia przycisku ‘F2’ a kończymy naciskając Ctrl+Shift+Enter, nie wprowadzamy z klawiatury symboli nawiasów klamrowych, Excel je sam dopisze po wciśnięciu Ctrl+Shift+Enter.

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 rekoró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 w 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.

Szkolenie Excel 2003 Szkolenie Excel 2007