Wprowadzenie do formuł macierzowych

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.

Okno arkusza kalkulacyjnego pokazujące przykładową bazę danych. W kolumnie A umieszczone są nazwy przedmiotów, w kolumnie B ich cena za jedną sztukę, w kolumnie C — ilość zakupionych sztuk

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.

Podczas wprowadzania formuły, konieczne jest zaznaczenie pola „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ą.

Okno arkusza kalkulacyjnego pokazujące przykładową bazę danych. W kolumnie D znajduje się formuła macierzowa, która zajmuje 10 komórek

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ć.

Podobał Ci się ten artykuł? Zapisz się na listę subskrybentów i otrzymuj informacje o następnych

Komentarzy: 5

  • r. pisze:

    Witam
    Wpisy na blogu coraz ciekawsze, ambitniejsze, jest super, na prawdę!

    Czy można poprosić o jakieś wprowadzenie lub cykl o makrach?

    Wyrazy szacunku!

    • Przybliżenie Czytelnikom zagadnień związanych z tworzeniem własnych makr jest bardzo kuszące i od dawna wiem, że chciałbym kiedyś pójść w tym kierunku. Problem jednak w tym, że nie jestem programistą i znam tylko najbardziej podstawowe podstawy tego zagadnienia. Zanim więc cokolwiek napiszę, sam muszę się bardzo dużo nauczyć. (Nawiasem mówiąc, podobnie wygląda sprawa z Base).
      Póki co, mogę jedynie odesłać do artykułu wprowadzającego w zarządzanie makrami, na którego końcu znajdują się odnośniki do stron opisujących, jak samodzielnie je tworzyć. Wszystkie są jednak w języku angielskim.
      Dziękuję także za miłe słowa 🙂 .

  • Zygmunt pisze:

    Cytat:
    „Współcześnie formuły macierzowe (tablicowe) mają stosunkowo niewielkie znaczenie”.

    No nie …
    Stanowczy protest!

    Zależy kto i do czego wykorzystuje arkusz kalkulacyjny.
    Jeżeli to zapisywania cen ciastek to faktycznie macierzy nie potrzebuje.
    Ale jeżeli np. przyswaja wiedzę z fizyki w której pełno wzorów typu suma po i razy suma po y itp. podobne – to operacje na macierzach są bardzo potrzebne, bo autor książki nie wysilił się jeżeli chodzi o przykłady i trzeba samemu kombinować.
    A rozwiązywanie układów liniowych? Przecież to nic prostszego niż:
    zastosowanie MINVERSE i MMULT.
    Arkusz kalkulacyjny przeważnie jest „pod ręką” w odróżnieniu od innych programów CAS – choćby (wx)Maxima.
    Mało tego, powiedziałbym, że w arkuszach kalkulacyjnych jest mało funkcji na macierzach, bo tylko:
    MDETERM, MINVERSE, MMULT, MUNIT, TRANSPOSE, a np. iloczyn wektorowy?

    A propos.
    Cena końcowa za np. 5 rodzajów ciastek w różnych cenach i różnych ilościach to przecież iloczyn skalarny – mnożenie macierzowe macierzy jednowierszowej przez jednokolumnową!

    Pozdrawiam.

    PS.
    Powyższy tekst napisałem w obawie, że ktoś mógłby wpaść na pomysł wywalenia funkcji macierzowych z arkusza kalkulacyjnego 🙂

    • Zgadzam się, że wszystko zależy od tego, do czego jest wykorzystywany arkusz. Artykuł jest skierowany do osób, które z formułami macierzowymi — czy macierzami w ogóle — nie mają do czynienia. Dla nich funkcja ta (obsługa macierzy w arkuszu) ma raczej status ciekawostki. Jest to coś, o czym warto wiedzieć, bo czasem się przydaje, ale bez czego można żyć. Stąd właśnie teza, że mają one „stosunkowo niewielkie znaczenie”. W żadnym wypadku nie uważam, aby zdanie to miało charakter uniwersalny.

      Osoby, które arkusza używają niemal wyłącznie do obliczeń na macierzach, mają zupełnie inną perspektywę. Tylko czy osoby takie potrzebują tego artykułu? Nie ma w nim chyba niczego, czego już by nie wiedziały.

      Zaś co do obaw — myślę, że nie są one uzasadnione. Aż takiego wpływu na rozwój LibreOffice to ja nie mam 😉 . Pakiety biurowe zazwyczaj są „ogólnego przeznaczenia”, w związku z czym nigdy nie wiadomo, do czego będą używane. Dlatego jak jakaś funkcja się już w nich znajdzie, to bardzo rzadko zostaje usunięta (a kiedy przestaje działać, zazwyczaj jest to uznawane za błąd). O samo istnienie formuł macierzowych raczej bym się nie obawiał.

  • tancredi pisze:

    Witam,
    Czy istnieje możliwość wykorzystania funkcji SUMY. CZĘŚCIOWE razem z jakimś zakresem macierzowym tak, aby po przefitrowaniu wynik się uaktualniał.
    Chodziłoby mi o coś takiego:
    SUMY.CZĘSCIOWE(9;{A1:A3*B1:B3})
    Na ten moment mogę to samo zrobić z dodatkową kolumną, w której jest wynik funkcji macierzowej, a na dole sobie podsumować częściowo. Chciałbym uzyskać taki efekt bez tworzenia kolumny z wynikami, a jedynie pola z podsumowaniem podobnie jak jest opisane w artykule na temat sumowania wyników macierzy.

Dodaj komentarz