Formuły tablicowe
W jednym z poprzednich postów poruszyłem temat formuł tablicowych. Są one stosunkowo słabo znane wśród użytkowników Excela, a szkoda, ponieważ pozwalają na uproszczenie wielu obliczeń.
Dziś przybliżę Wam takie formuły i pokażę na prostych przykładach do czego można je wykorzystać. Na początek kilka definicji.
Tablica jest zbiorem wartości lub komórek, które możemy przetworzyć grupowo.
Formuła tablicowa jest specjalną formułą obsługującą tablice. Polega to na tym, że formuła tablicowa stosuje zadane działanie/wykonuje operacje na każdej komórce zakresu. Musimy pamiętać, że formuła tablicowa może nam dać w wyniku pojedynczą wartość, lub wiele wartości, z których każda będzie w innej komórce. Przykład formuły tablicowej dającej wiele wartości znajdziecie w poście "Tabliczka mnożenia za pomocą jednej formuły".
Immanentną cechą formuł tablicowych jest sposób wprowadzania: wzór formuły wpisujemy normalnie (zaczynając od znaku "="), natomiast zatwierdzamy ją kombinacją klawiszy CTRL+SHIFT+ENTER. Tak wprowadzoną formułę Excel będzie traktować jako tablicową i umieści ją w nawiasach klamrowych ({}). Uwaga! Nie wprowadzajcie sami takich nawiasów do formuł - spowodują one błąd formuły!
Dzięki formułom tablicowym możemy na każdej komórce danego zakresu wykonywać operacje tak, jak pętla języka programowania przetwarza kolejne elementy tablicy.
Aby lepiej zrozumieć formuły tablicowe prześledźmy poszczególne przykłady. Na początek ilustracja działania. Załóżmy, że mamy dwa zakresy wartości jak na rysunku poniżej:
Chcemy otrzymać w kolumnie C wyniki mnożenia wartości z kolumny A przez wartości z kolumny B - będzie to pięć wyników. Zaznaczamy zakres C1:C5 i wpisujemy:
=A1:A5*B1:B5
i naciskamy klawisze CTRL+SHIFT+ENTER. Wyniki powinny wyglądać następująco:
Niezbyt imponujące ale… załóżmy teraz, że chcemy otrzymać dla danych z powyższego rysunku sumę iloczynów. W tym wypadku nie musimy liczyć poszczególnych iloczynów i dopiero wtedy je sumować (pomijając oczywiście fakt, że taka funkcja arkusza w Excelu już istnieje, ale na potrzeby naszego ćwiczenia możemy na chwilę o tym zapomnieć) - możemy to zrobić za pomocą jednej formuły tablicowej. Wystarczy w komórce np. C1 wpisać:
=SUMA(A1:A5*B1:B5)
i zatwierdzić CTRL+SHIFT+ENTER. Ta formuła tablicowa daje nam tylko jeden wynik. Czy już widzicie jakie możliwości tkwią w formułach tablicowych? A poniżej kilka przykładów, jak można formuły tablicowe wykorzystać.
Aby określić liczbę wartości numerycznych w zakresie wartości:
=SUMA(JEŻELI(CZY.LICZBA(zakres_danych);1;0))
Aby określić liczbę komórek zawierających błąd:
=SUMA(JEŻELI(CZY.BŁ(zakres_danych);1;0))
Aby określić liczbę unikatowych wartości numerycznych:
=SUMA(JEŻELI(CZĘSTOŚĆ(zakres_danych)>0;1;0))
Załóżmy jeszcze, że mamy dane sprzedaży z trzech miast zarejestrowane dla trzech różnych lat jak na rysunku:
Suma wartości komórek zakresu Wartość sprzedanych w Warszawie w 2000 roku:
=SUMA((A2:A10=2000)*(B2:B10="Warszawa")*C2:C10)
Liczba komórek zakresu Wartość, dla których rok jest równy 2000 a miasto to Kraków:
=SUMA((A2:A10=2000)*(B2:B10="Kraków"))
Liczba komórek zakresu Wartość, dla których miasto to Kraków lub Łódź i okres to rok 2001:
=SUMA((A2:A10=2001)*((B2:B10="Kraków")+(B2:B10="Łódź")))
Na zakończenie w ramach podsumowania przedstawiam Wam wady i zalety stosowania formuł tablicowych w miejsce zwykłych formuł zagnieżdżonych czy pośrednich.
Zalety formuły tablicowej
- zajmują mniej pamięci,
- znacznie poprawiają wydajność naszej pracy,
- pozwalają wyeliminować formuły pośrednie
- w połączeniu ze stosowaniem zakresów nazwanych pozwalają na budowanie łatwo rozszerzalnych i czytelnych arkuszy.
- umożliwiają wykonywanie operacji, które w przeciwnym razie byłyby trudne do realizacji lub wręcz niemożliwe.
Wady formuły tablicowej
- wydłużają czas wykonywania obliczeń w arkuszu,
- wymagają nieco doświadczenia aby je w pełni wykorzystać.
Od siebie mogę dodać, że z formułami tablicowymi osobiście mam tak, jak z tabelami przestawnymi. Dopóki ich nie znałem - niczego mi nie brakowało, ale jak je poznałem - do dziś nie rozumiem, jak mogłem sobie bez nich radzić...