Przepis na

Wprowadzenie do formuł macierzowych

• Autor: Mirosław Zalewski
• 30 kwietnia 2013
• komentarzy 5

Współcześnie formuły macierzowe (tablicowe) mają stosunkowo niewielkie znaczenie. Nadal jednak istnieją sytuacje, w których ich wykorzystanie jest najprostszym lub najbardziej efektywnym rozwiązaniem. Artykuł przedstawia podstawy pracy z nimi: czym są, jak je wprowadzać i modyfikować oraz jakie są ich wady i zalety. Nie zabraknie także kilku przykładów ich zastosowania w praktyce.

Podstawy teoretyczne

Ujmując rzecz w bardzo dużym uproszczeniu, macierz to zbiór (lista, kolekcja) elementów. Elementami tymi mogą być zarówno liczby, jak i ciągi znaków. W arkuszu kalkulacyjnym macierz jest reprezentowana przez zakres komórek. Rozróżniamy macierze jednowymiarowe (składające się tylko z kolumny lub tylko z wiersza) oraz dwuwymiarowe (składające się zarówno z kolumn, jak i z wierszy).

Formuła macierzowa jest taką formułą, która dokonuje obliczeń na elementach macierzy, a której wynikiem również jest macierz. Efekt jej działania może zajmować wiele komórek w arkuszu. Ponieważ program Calc nie potrafi automatycznie rozpoznawać formuł macierzowych, użytkownik musi je oznaczyć jako takie podczas wprowadzania.

Formuły macierzowe mogą pracować jednocześnie na dwóch lub więcej macierzach. W takim wypadku dana operacja jest wykonywana na odpowiadających sobie elementach macierzy, a efektem jest macierz zawierająca wyniki obliczeń. Należy dbać o to, aby wszystkie macierze miały takie same rozmiary (tyle samo wierszy i kolumn), gdyż w przeciwnym wypadku wyniki bywają nieprzewidywalne.

Tak samo jak w zwyczajnej pracy z arkuszem kalkulacyjnym, odróżnić możemy formuły macierzowe od funkcji macierzowych. Żeby jednak nie było zbyt łatwo, wiele funkcji obsługuje jako argumenty zarówno macierze, jak i pojedyncze wartości. Co więcej, niektóre formuły mogą dawać różne wyniki w zależności od tego, czy zostaną wprowadzone w trybie macierzowym, czy też nie. W rezultacie rozróżnienie to nie jest zbyt ścisłe i nie będę z niego korzystał; zaznaczam tylko, że można się z nim spotkać.

Wreszcie warto odnotować, że formuły macierzowe zwane są też formułami tablicowymi lub — rzadziej — formułami obszarowymi. W języku angielskim używa się zwrotów array formulas, matrix formulas lub CSE formulas (od kombinacji klawiszy służącej do ich wprowadzenia). Wszystkie te wyrażenia oznaczają to samo i można ich używać zamiennie.

Wprowadzanie formuł macierzowych

Jak wspomniałem powyżej, podczas wstawiania formuły macierzowej trzeba zadeklarować, że ma ona być traktowana inaczej niż zwykłe formuły. Istnieją dwa sposoby, aby to zrobić. Obu przyjrzymy się na przykładzie poniższej bazy danych.

W kolumnie B umieszczona jest cena za jeden przedmiot, w kolumnie C zaś — ilość zakupionych sztuk. Załóżmy, że chcielibyśmy umieścić w kolumnie D łączną kwotę wydaną na przedmioty danego typu.

W tym celu umieszczamy kursor w komórce D2 i otwieramy kreator funkcji. Jako formułę wpisujemy:

=B2:B11*C2:C11

Koniecznie zaznaczamy także Macierz w lewym dolnym rogu.

W efekcie cała kolumna D została wypełniona danymi. Mówiąc ściśle, wynikiem działania naszej formuły tablicowej jest macierz, której kolejne elementy są iloczynami odpowiadających sobie elementów macierzy podanych jako argumenty.

Zauważmy przy tym, że sama formuła została otoczona nawiasami klamrowymi ({}). W ten sposób Calc informuje, że formuła w danej komórce jest formułą macierzową.

Przyjmijmy, że w dalszej kolejności chcielibyśmy policzyć łączną kwotę wydaną na wszystkie przedmioty. Oczywiście teraz, gdy już iloczyny znajdują się w kolumnie D, wystarczy do tego zwykła funkcja SUMA. Jednak dzięki formule macierzowej ten sam efekt możemy uzyskać wykorzystując zaledwie jedną komórkę (czyli nawet wtedy, gdyby kolumna D nie była wypełniona).

Umieszczamy więc kursor w komórce D12 i wpisujemy formułę:

=SUMA(B2:B11*C2:C11)

Zatwierdzamy ją kombinacją klawiszy Ctrl + Shift + Enter.

Tak jak poprzednim razem, wynikiem działania funkcji jest tablica. Tym razem jednak jest ona od razu przekazywana jako argument do funkcji SUMA, która z kolei zwraca tylko jedną liczbę. W rezultacie otrzymaliśmy sumę wszystkich iloczynów odpowiadających sobie elementów obu macierzy.

Zauważmy przy tym, że w pasku formuły ponownie znajdują się klamry, chociaż ich nie wprowadzaliśmy. Pełnią one wyłącznie funkcję informacyjną i nawet kiedy wpisujemy formułę ręcznie, pomijamy je.

Trzeba mieć ciągle na uwadze fakt, że formuły macierzowe wstawiamy za pomocą odmiennej kombinacji klawiszy. Istnieje wiele formuł, które pokazują odmienne wyniki w zależności od tego, czy zostaną wprowadzone jako macierzowe, czy też nie.

Na zakończenie warto jeszcze dodać, że w obu opisanych wyżej przypadkach można sobie poradzić bez formuł tablicowych. W pierwszym wystarczy umieścić formułę iloczynu w komórce D2 i „przeciągnąć” ją do końca kolumny. Drugą możemy zastąpić funkcją SUMA.ILOCZYNÓW.

Modyfikowanie formuł macierzowych

Dopóki wynik działania formuły macierzowej jest pojedynczą wartością, dopóty jej zmiana niczym nie różni się od zmiany zwykłych formuł. Jeżeli jednak wynik zajmuje kilka komórek (tak jak w pierwszym opisanym wyżej przykładzie), próba modyfikacji lub usunięcia jednej z nich zakończy się wyświetleniem komunikatu błędu.

Aby temu zaradzić, należy zaznaczyć wszystkie elementy macierzy wynikowej. Dopiero wtedy możemy rozpocząć jej edycję. Należy przy tym pamiętać, aby zmiany zatwierdzić kombinacją Ctrl + Shift + Enter.

Sprawa komplikuje się, gdy formuła po modyfikacji zwraca tablicę liczącą więcej elementów niż przedtem. W takim wypadku zanim rozpoczniemy edycję powinniśmy zaznaczyć tyle komórek, ile będzie zajmowała zwracana macierz. Osobiście uważam, że prościej jest usunąć starą macierz i wprowadzić zmodyfikowaną od nowa.

Macierze wprowadzone bezpośrednio do formuły

Dotychczas zajmowaliśmy się wyłącznie dwoma typami macierzy: tymi, które już znajdują się w arkuszu oraz tymi, które są wynikiem działania formuły macierzowej. Istnieje jeszcze trzeci rodzaj, macierze wpisane bezpośrednio do formuły. Czasami nazywa się je „stałymi” (ang. constant array).

Macierze takie zapisujemy wewnątrz nawiasów klamrowych; jest to jedyna sytuacja, gdy wprowadzamy także klamry. Do oddzielania kolumn służy kropka (.), do oddzielania wierszy zaś średnik (;). Wypełnianie macierzy zaczynamy od lewego górnego rogu i postępujemy od lewej do prawej, z góry na dół.

Aby zobaczyć, jak to działa, wystarczy skopiować poniższą formułę i wkleić ją do komórki Calca (tak jak wszystkie formuły macierzowe, zatwierdzamy ją kombinacją Ctrl + Shift + Enter):

={1.2.3;4.5.6;7.8.9}

Przy okazji zauważmy, że w pasku formuły pojawiła się dodatkowa para nawiasów klamrowych, otaczająca całą formułę. Klamry te, jak już wspominałem, służą jedynie do poinformowania nas, że mamy do czynienia z formułą macierzową; sami ich nie wpisujemy.

Macierz wprowadzona ręcznie nie może zawierać pustych elementów. Dla przykładu poniższa macierz nie jest poprawna:

={1..2;;3..4}

Tablica stała może zawierać zarówno liczby, jak i tekst. Tekst należy ująć w cudzysłowy ("o tak"). Macierze nie obsługują innych typów danych (co wynika z tego, że dla arkusza kalkulacyjnego wszystkie dane ostatecznie są bądź liczbą, bądź tekstem).

Zalety formuł macierzowych

Uzupełniając podane wyżej informacje, wyróżnić można trzy główne zalety formuł tablicowych:

• Pomagają zaoszczędzić miejsce — operacje wymagające wielu obliczeń mieszczą się w zaledwie jednej komórce.
• Nawet gdy wynik zajmuje wiele komórek, wszystkie one mają w pasku formuły tę samą zawartość. W przypadku danych złożonych z bardzo wielu wierszy pozwala to szybko zorientować się, na jakim zakresie została wykonana dana operacja.
• Można dzięki nim wykonać operacje, które nie są możliwe innymi środkami. Czasami są więc po prostu konieczne.

Poza tym ich użycie zmniejsza rozmiar skoroszytu arkusza kalkulacyjnego (w końcu formuła, która może być powtarzana nawet na kilku tysiącach komórek, zapisywana jest tylko raz). Jednak w dobie pojemnych dysków twardych i szybkich łącz internetowych oszczędność kilkunastu czy kilkudziesięciu kB wydaje mi się mało istotna.

Wady formuł macierzowych

Niestety, formuły tablicowe nie są pozbawione wad. Do najważniejszych można zaliczyć:

• Bardzo ogólna dokumentacja. Przy opisie poszczególnych funkcji w Pomocy zazwyczaj pomija się ich zachowywanie, gdy jednym z argumentów jest macierz. O ile mi wiadomo, nie istnieje także żaden spis funkcji przyjmujących macierze jako argumenty. Odkrycie wielu szczegółów musi odbywać się metodą prób i błędów.
• Po części z powyższego wynika, że stosunkowo niewielu użytkowników w ogóle wie o ich istnieniu. Gdy nad arkuszem pracuje kilka osób, ich wykorzystanie może utrudniać pracę innym.
• Operacje tablicowe na bardzo dużych zbiorach danych mogą znacznie spowolnić zarówno obliczenia, jak i zapisywanie oraz wczytywanie pliku.

Przykłady użycia

Wyżej wspomniałem, że istnieją problemy, których jedynym rozwiązaniem jest użycie formuł tablicowych. Poniżej znajduje się kilka z nich oraz przykładowe rozwiązania. Ponieważ formuły te nie są głównym tematem artykułu, nie będę szczegółowo wyjaśniał ich działania.

Jak obliczyć średnią liczb w zakresie, który może zawierać błędy?

=ŚREDNIA(JEŻELI(CZY.BŁĄD(A1:A10);"";A1:A10))

Jak obliczyć sumę liczb wyodrębnionych z bazy danych na podstawie kilku kryteriów? (Zagadnienie to zostało szerzej opisane w artykule Funkcja SUMA.JEŻELI i kilka warunków).

=SUMA((A1:A1="warunek1")*(B1:B10="warunek2")*C1:C10)

Jak obliczyć średnią z liczb do pierwszego kwartyla w danym zakresie?

=ŚREDNIA(MIN.K(A1:A50;WIERSZ(ADR.POŚR("A1:A"& ZAOKR.GÓRA(LICZBA.WIERSZY(A1:A50)/4)))))

Iloma elementami różnią się dwie serie danych?

=SUMA(1*(F17:G19<>I17:J19))

Podsumowanie

Na koniec warto powtórzyć najważniejsze informacje dotyczące formuł tablicowych.

• Nigdy nie wpisujemy nawiasów klamrowych otaczających formułę (nie dotyczy to nawiasów, które otaczają tablicę wprowadzoną w obrębie formuły).
• Formułę macierzową wprowadzamy kombinacją klawiszy Ctrl + Shift + Enter.
• Wiele operacji, które kiedyś były możliwe tylko dzięki formułom tablicowym, dzisiaj można wykonać za pomocą dedykowanych funkcji. Jeżeli nie jest to absolutnie konieczne, lepiej jest formuł macierzowych nie używać.