Wstęp

Co się zmieniło w Excelu w ciągu ostatnich pięciu lat?

Już ponad pięć lat dzieli nas od publikacji trzech moich książek poświęconych Excelowi, wydanych przez wydawnictwo Helion w latach 2019 - 2020. Ukazały się one w serii pod wspólnym tytułem Zrozumieć Excela. W pierwszej części, zatytułowanej Funkcje i wyrażenia, omówiłem zagadnienia ogólne, przeznaczone dla wszystkich użytkowników Excela zainteresowanych poznaniem podstaw jego działania i poszerzeniem umiejętności wykorzystania go do obliczeń. Szczególny nacisk położyłem w tej części na usystematyzowanie sposobów użycia funkcji. Druga część, zatytułowana Obliczenia i wykresy, poświęcona jest bardziej szczegółowym zagadnieniom obliczeniowym w zakresie nauk ścisłych i technicznych, jak też ilustracji tych obliczeń wykresami. Część trzecia wreszcie, która nosi tytuł VBA - makra i funkcje, wprowadza Czytelnika w tematykę Visual Basica for Application. Obejmuje podstawy i liczne przykłady wykorzystania tego języka do wspomagania różnych aspektów obliczeń.

Moim celem było przybliżenie Czytelnikowi, jak działa Excel i jak można wykorzystać jego możliwości do rozwiązywania praktycznych problemów. Przede wszystkim chciałem pokazać sposoby rozwiązywania zagadnień obliczeniowych pojawiających się w naukach ścisłych i technicznych, ale również problemy ogólne, które interesują szersze grono Czytelników, nie tylko inżynierów. W moich książkach uwzględnione zostały proste zagadnienia bazodanowe, wykorzystanie list rozwijanych, operacje na danych tekstowych, tworzenie i analiza różnych zestawień tabelarycznych i obliczenia dotyczące dat i czasu.

Czas jednak nie stoi w miejscu. W ciągu minionych pięciu - sześciu lat Excel intensywnie się rozwijał, pojawiały się nowe funkcje i wbudowane narzędzia, które pozwalają rozwiązywać typowe problemy w sposób bardziej zwięzły i efektywny. Niektóre problemy obliczeniowe, do rozwiązania których niezbędne było użycie narzędzi zewnętrznych, takich jak VBA, można już teraz rozwiązywać w ramach samego Excela. Nadal jednak pozostaje pole dla wspomagających języków programowania takich jak Visual Basic, TypeScript czy Python. Ponadto platforma Power Query, która była pierwotnie tylko dodatkiem, została już praktycznie zintegrowana z programem i służy jako podstawowe narzędzie do pozyskiwania i wstępnej obróbki danych w Excelu. Obecnie Excel jest w coraz większym stopniu integrowany z internetem. Coraz więcej usług i funkcji wykorzystuje połączenia zdalne do udostępniania treści i wykonywania działań takich jak na przykład tłumaczenie tekstu.

W samym programie nastąpiła, można powiedzieć, rewolucyjna zmiana w podejściu do tablic i obliczeń wykonywanych z ich udziałem. O ile pierwotny Excel był "komórkocentryczny", to znaczy główny nacisk był położony na użycie do obliczeń pojedynczych komórek traktowanych odrębnie i tworzenie ciągów obliczeniowych na ich podstawie, to obecnie akcentuje się przede wszystkim przetwarzanie od razu całych tablic danych, obejmujących wiele komórek. Opracowano wiele funkcji ułatwiających operacje na tablicach i przede wszystkim zmieniono podejście do tzw. formuł tablicowych. Pierwotnie były one opcją obliczeniową, teraz stały się podstawowym, domyślnym sposobem interpretacji wyrażeń.

W związku z tymi zmianami część treści moich książek wymagałaby aktualizacji z uwzględnieniem nowych możliwości i nowego podejścia do obliczeń. Oczywiście ogólne metody i koncepcje obliczeniowe pozostają wciąż aktualne, można je jednak realizować za pomocą nowych funkcji w sposób bardziej sprawny i przejrzysty.

Na przestrzeni ostatnich pięciu lat pojawiły się dwie nowe edycje "pudełkowe" 2021 i 2024, ale na rynku dominuje obecnie wersja w postaci platformy sieciowej, która zmieniła nazwę na Microsoft 365. Program jest aktualizowany na bieżąco co miesiąc lub co pół roku, w zależności od umowy. Nadal jest również dostępna i stale rozwijana bezpłatna wersja online, której nazwa ewoluowała przez Excel Web App do obecnej Excel dla sieci Web. Jest ona częścią pakietu Office dla sieci Web na platformie Microsoft 365. Tej wersji sieciowej poświęcony jest rozdział 15. tego opracowania.

Zakres tematyczny opracowania

Czwarty tom cyklu Zrozumieć Excela zatytułowany Zrozumieć Excela. Formuły dynamiczne i nowe funkcje w Microsoft 365 został pomyślany jako uzupełnienie i aktualizacja treści poprzednich tomów. Przedstawiłem w nim dość szczegółowo nową koncepcję obliczeń tablicowych w Excelu i jej wpływ na zapis formuł i organizację obliczeń. Wyjaśniłem różnice między dawnymi formułami tablicowymi a nowymi formułami dynamicznymi. Opisałem rozszerzoną koncepcję nazw definiowanych, które teraz mogą mieć zasięg zawężony do pojedynczej formuły (dzięki użyciu funkcji LET), ale również mogą być nadawane funkcjom definiowanym przez użytkownika (dzięki użyciu metafunkcji LAMBDA). Wprowadzenie funkcji LAMBDA znosi monopol języka Visual Basic for Application w zakresie definiowania funkcji obliczeniowych, jednak nie zastępuje go całkowicie w tej roli. Użytkownik zyskuje nowe narzędzie i możliwość wyboru. Zapewne możliwość wykonywania obliczeń w jednolitym środowisku ma swoje zalety, ale trzeba to nowe narzędzie dobrze poznać i opanować, a nie jest to takie proste dla osoby przyzwyczajonej do używania VBA. Język VBA narzuca podział zadania na dość krótkie i czytelne instrukcje. Funkcja LAMBDA nie narzuca takiej dyscypliny, więc sposób zapisu w dużym stopniu zależy od projektanta obliczeń. Na szczęście powstały narzędzia wspomagające tworzenie rozbudowanych formuł i takie narzędzie również zostało tu uwzględnione. Nie jest ono jednak na razie zintegrowane z Excelem i wymaga osobnej instalacji.

Moje opracowanie opiera się oczywiście przede wszystkim na oficjalnej dokumentacji programu, udostępnionej przez jego twórców, lecz jej dokładniejsza analiza ujawnia liczne braki i niedopowiedzenia, a nawet nieścisłości. Nietrudno dostrzec pośpiech w jej przygotowaniu, przejawiający się między innymi w niekonsekwentnym nazewnictwie i niedokładnym tłumaczeniu. Starałem się, jak można najdokładniej, przetestować działanie wszystkich omawianych funkcji i uzupełnić ich opisy na podstawie własnych obserwacji. Wykorzystywałem również liczne opisy, wyjaśnienia i komentarze dostępne w literaturze i w internecie. Nie sposób jednak przetestować wszystkich możliwych sytuacji, dlatego jeśli w moich opisach pozostały jeszcze jakieś nieścisłości, to z góry przepraszam i proszę o kontakt w razie ich odkrycia. Niektóre błędy są znane producentowi i stopniowo korygowane, zatem część moich uwag krytycznych może z czasem stać się nieaktualna.

Układ tego tomu jest nieco inny niż trzech poprzednich. Zagadnienia i funkcje są tu omawiane w podziale tematycznym, zaczynając od rozwiązań środkami dostępnymi bezpośrednio w arkuszu kalkulacyjnym. Pod koniec każdego rozdziału zamieszczam informacje o metodach i środkach, których można użyć w VBA do uzyskania podobnych wyników. Innych języków programowania dostępnych z poziomu Excela (TypeScript, Python) nie uwzględniałem.

Podobnie jak było w przypadku poprzednich tomów, zakładam, że użytkownik będzie wykonywał obliczenia osobiście i na lokalnym komputerze. Nie omawiam zagadnień związanych z udostępnianiem plików, pracą grupową i pracą zdalną. Nie zajmuję się również kwestią zabezpieczania arkuszy i plików przed nieautoryzowaną modyfikacją lub nieuprawnionym użyciem. Praca grupowa w Excelu nie jest najwygodniejsza, wiele funkcji w tym trybie nie działa, kwestia udostępniania fragmentów arkusza czy pliku poszczególnym użytkownikom jest zagadnieniem dość złożonym i nie będzie omawiana. Podobnie kwestie zabezpieczeń. Excel był pierwotnie przeznaczony do użytku osobistego i zaimplementowane w nim zabezpieczenia są raczej symboliczne i mają za zadanie ochronę przed przypadkową niezamierzoną modyfikacją danych lub formuł, a nie przed zamierzonym działaniem w złej wierze.

Excel był pierwotnie przeznaczony głównie do zastosowań ekonomiczno-biznesowych, stąd są w nim zaimplementowane liczne funkcje służące do typowych operacji związanych z obliczaniem rat kredytów, oprocentowania, amortyzacji itp., jak również wykresy służące do prezentacji danych biznesowych. W poprzednich tomach pomijałem specjalistyczne funkcje i obliczenia finansowe z uwagi na dużą ilość innych zagadnień do omówienia. Obecnie pojawiła się możliwość uzupełnienia tych kwestii. Jest im poświęcony jeden z rozdziałów. Nie omawiam natomiast tabel i wykresów przestawnych ani dodatku Power Query, poza przypadkiem gdy ten dodatek jest niezbędny do uzyskania omawianej funkcjonalności rozszerzonych typów danych. Nie omawiam również zaawansowanych funkcji statystycznych. Tym zagadnieniom poświęcone są odrębne publikacje.

Do kogo adresowana jest ta książka?

Książka uzupełnia treści zawarte w poprzednich trzech tomach, zatem podobnie jak one jest adresowana do hobbystów, którzy chcą poznać głębiej nowe możliwości Excela zarówno w zakresie obliczeń numerycznych, jak i w dziedzinie przetwarzania niezbyt dużych ilości danych. Ponieważ wywodzę się ze środowiska akademickiego, przykłady zawarte w książce nawiązują do moich zainteresowań. Od Czytelnika oczekuję znajomości obsługi arkusza kalkulacyjnego w zakresie omówionym przynajmniej w pierwszym tomie tego opracowania lub w innych podobnych książkach czy samouczkach dostępnych w sieci.

W przeciwieństwie do poprzednich tomów, w których starałem się podawać rozwiązania niezależne od używanej wersji Excela, ewentualnie wskazując warianty odpowiednie dla różnych wersji, w tej książce koncentruję się na nowych rozwiązaniach dostępnych w Excelu 365 lub jego nowszych wersjach "pudełkowych" 2021 i 2024. Użytkownicy starszych wersji programu nie będą niestety mogli z większości z nich skorzystać. Opcją dla nich może być bezpłatna wersja Excel dla sieci Web, która jest aktualizowana na bieżąco i obejmuje również prawie wszystkie nowe funkcje obliczeniowe Excela.

Wskazówka dla użytkowników starszych wersji programu:

Świadczenie przez Microsoft pomocy technicznej dla pakietów Office 2016 i Office 2019 zakończy się 14 października 2025 r. Nie będzie żadnych rozszerzeń ani dodatkowych aktualizacji zabezpieczeń. Wszystkie aplikacje pakietu Office 2016 i Office 2019 będą nadal działać. Możesz jednak narazić się na poważne i potencjalnie szkodliwe zagrożenia bezpieczeństwa.

Zawartość opracowania

Pierwszy rozdział jest poświęcony nowemu podejściu do formuł tablicowych i ich ewolucji w kierunku formuł dynamicznych, które potrafią same określić, ile komórek potrzebują, aby wyświetlić wszystkie wyniki. Potrafią również zareagować na zmianę danych i zaktualizować nie tylko wyświetlane wartości, ale również ich liczbę.

W drugim rozdziale przypominam koncepcję nazw definiowanych i nowe sposoby ich wykorzystania przez funkcje LET i LAMBDA. Te nowe funkcje oferują wyjątkowe możliwości polegające na wielokrotnym wykorzystywaniu raz zdefiniowanych schematów obliczeniowych, również z uwzględnieniem rekurencji, która dotąd nie była w arkuszach Excela możliwa. Również dwa kolejne rozdziały poświęciłem omówieniu zaawansowanych sposobów wykorzystania funkcji LAMBDA i funkcji pomocniczych, ułatwiających prowadzenie obliczeń, których wynikiem są tablice.

Piąty rozdział jest prezentacją pomocniczego programu, który powstał w zespole ściśle powiązanym z twórcami Excela, a który ma ułatwić tworzenie i analizę działania złożonych formuł, w tym formuł z wykorzystaniem funkcji LAMBDA. Ten program podlega dość intensywnej ewolucji i po osiągnięciu swej ostatecznej postaci być może zostanie zintegrowany z programem głównym. W swej obecnej postaci dodatek dobrze współpracuje z lokalnymi wersjami językowymi Excela, natomiast moduł debugera nie obsługuje jeszcze wszystkich funkcji.

Wraz z wprowadzeniem funkcji dynamicznych udostępniono kilka funkcji do wyszukiwania, filtrowania, sortowania i eliminowania duplikatów. Te funkcje niejako zdublowały funkcjonalności, które były wcześniej dostępne jako narzędzia. Teraz są również dostępne jako funkcje, co stwarza dodatkowe możliwości ich użycia, na przykład przez wykonanie sekwencji działań w jednym wyrażeniu. Oczywiście każde z rozwiązań ma swoją specyfikę: narzędzia działają w miejscu, bezpośrednio na danych, ale jednorazowo; funkcje wyświetlają wyniki w innym miejscu, nie modyfikując samych danych, za to przeliczają się automatycznie, gdy dane ulegną zmianie. Specyfika i sposoby użycia tych funkcji stanowią treść rozdziału szóstego.

W rozdziale siódmym zebrałem nowe funkcje przeznaczone do pracy z tekstem. Ta dziedzina była w tradycyjnym Excelu dość zaniedbana. Pierwotnie działania na danych tekstowych były traktowane marginesowo. Z czasem jednak twórcy Excela je docenili i opracowali kilka nowych funkcji, które zdecydowanie ułatwiają typowe operacje polegające na dzieleniu i łączeniu fragmentów tekstu zapisanych w różnych komórkach. Do omówienia tych funkcji dołączyłem również dwie całkiem nowe, służące do tłumaczenia tekstów z jednego języka na inny.

Do pracy z tekstem służą również wyrażenia regularne, które stanowią swoisty język umożliwiający zaawansowaną analizę treści zapisów. Do ich obsługi udostępniono trzy nowe funkcje. Do tej pory wyrażenia regularne można było analizować, używając VBA, obecnie w nowych wersjach Excela można to robić wprost w arkuszu. Ze względu na złożoność tej problematyki wyodrębniłem ją w osobnym rozdziale - dwunastym.

Jak już wspominałem, twórcy nowej odsłony Excela dużo uwagi poświęcili ułatwieniu operacji na tablicach. W tradycyjnym Excelu uwaga była skoncentrowana na pojedynczej komórce i operacje na tablicach wymagały odwołań do każdej komórki składowej. Obecnie udostępniono funkcje, które pozwalają dowolnie łączyć i dzielić tablice, jak również zmieniać ich strukturę (przekształcać tablice dwuwymiarowe w wektory i odwrotnie). Wciąż jednak brakuje funkcji do odwracania kolejności elementów tablicy. Można to oczywiście zrobić, łącząc funkcje, ale nie ma jednej gotowej funkcji służącej do takiej operacji. Wszystkie te zagadnienia związane z przekształcaniem tablic zebrałem w rozdziale ósmym.

Kolejny rozdział (dziewiąty) poświęciłem zupełnie nowemu typowi danych w Excelu. Są to tzw. dane rozszerzone (Rich Data). Za pomocą danych tego typu można umieszczać w komórkach arkusza obrazy, których wielkość dostosowuje się do wielkości komórki, a więc można je łatwo skalować. Udostępniono również narzędzia do przekształcania tych obrazów w obiekty, które można edytować, a następnie umieścić z powrotem w komórce. Nowa funkcja OBRAZ pozwala umieszczać w komórkach obrazy, których źródło znajduje się w sieci.

W rozdziale dziesiątym omawiam dwie bardzo zaawansowane funkcje, które opracowano z myślą o stworzeniu funkcyjnej, a więc niewymagającej odświeżania, alternatywy tabel przestawnych. Funkcje mają imponujące możliwości, ale z powodu licznych parametrów są dość trudne do opanowania. Ich wadą jest też całkowity brak formatowania wyników, które trzeba uzupełniać dość żmudnym formatowaniem warunkowym.

Rozdział jedenasty poświęciłem omówieniu funkcji PRZYTNIJZAKRES i operatorów kropkowych, które skracają zapis, jednak kosztem zmniejszenia jego czytelności. Ta funkcja przydaje się, gdy pracujemy z danymi, których długość jest zmienna, a nie chcemy obciążać formuł pustymi komórkami. Funkcja przyda się również przy sporządzaniu wykresów, które mają pokazywać dane dynamicznie.

O rozdziale dwunastym wspominałem już powyżej, a w trzynastym zebrałem różne funkcjonalności programu, które mają poprawić komfort pracy z nim. Są to bardzo różne narzędzia; ich przydatność pozostawiam ocenie Czytelnika. Z ciekawszych warto wymienić: nowe, powiązane z komórkami kontrolki pola wyboru; możliwość zablokowania niepożądanej konwersji formatu liczb (niestety nie dotyczy to jeszcze dat); lepsze wizualne wyróżnienie komórki aktywnej; przekreślenie nieaktualnych wartości przy ręcznym przeliczaniu formuł; usprawnienie list kontroli poprawności przez oczyszczenie danych źródłowych z pustych komórek i duplikatów oraz aktywne wyszukiwanie pasujących wartości na liście.

W rozdziale czternastym przywołałem inny wariant danych rozszerzonych (pisałem już wcześniej o obrazach jako danych z tej kategorii). Tym razem są to dane połączone - dane specjalistyczne, których źródło jest gdzieś w sieci, a ich dostarczaniem zajmuje się wyspecjalizowana firma współpracująca z Microsoftem. Kategorie tych danych ewoluują w czasie, obecnie (w kwietniu 2025) dostępne są dane dotyczące geografii, kursów akcji giełdowych i walut. Dane, które tego wymagają ze względu na swój charakter, są automatycznie odświeżane co kilka minut. Oczywiście korzystanie z nich wymaga połączenia z internetem. Posługując się dodatkiem Power Query, można teraz również zdefiniować własny typ danych, podobny do danych połączonych dostarczanych przez firmę zewnętrzną, określając samemu, skąd mają być pobierane dane. To źródło może być w internecie, może być to plik zewnętrzny lub zakres komórek w arkuszu Excela.

W rozdziale piętnastym przedstawiłem bezpłatną alternatywę standardowej aplikacji Excela w postaci usługi sieciowej Excel dla sieci Web. Ta wersja programu obejmuje większość funkcjonalności klasycznej aplikacji przy nieco odmiennym, choć podobnym interfejsie użytkownika. Jej użycie wymaga założenia bezpłatnego konta w Microsofcie i dostępu do sieci. W treści rozdziału wymieniam funkcjonalności, których tu brakuje (główna to brak dostępu do VBA), ale również te, które są w tej wersji, a nie ma ich w wersji klasycznej. Jeśli chodzi o funkcje obliczeniowe, to dostępne są prawie wszystkie.

Rozdział szesnasty odbiega charakterem od pozostałych. Są w nim przedstawione funkcje do obliczeń finansowych. Nie są to nowe funkcje - wręcz przeciwnie - ta grupa funkcji jest bardzo stabilna, nic się w nich nie zmieniło od lat. Jednak dotąd o tych funkcjach nie pisałem, a są wśród nich takie, które można z powodzeniem wykorzystać również do celów niezwiązanych z finansami.

Opracowanie zamyka rozdział siedemnasty, który podobnie jak w pierwszym tomie stanowi zestawienie składni funkcji omawianych w tym tomie. Może on służyć jako indeks nowych funkcji, ułatwiając odszukanie tej właśnie potrzebnej.

Konwencje stosowane w tekście

Jeśli chodzi o strukturę tekstu, to tak jak w poprzednich tomach przyjąłem, że rozdziały dzielą się na podrozdziały, a te z kolei na punkty i podpunkty. Odpowiednie tytuły wyróżniane są czcionką o zróżnicowanej wielkości.

Starałem się utrzymać w tekście te same sposoby wyróżniania słów, które stosowałem w poprzednich tomach. Nazwy pozycji menu, treść komunikatów wyświetlanych przez program oraz oznaczenia klawiszy są wyróżnione czcionką pochyłą (kursywą), na przykład Dane/Pobierz dane albo Ctrl+Shift+V. Przy określaniu klawiszy znak + oznacza kombinację naciśnięć, to znaczy pierwszy lub dwa pierwsze klawisze należy nacisnąć i przytrzymać, i dopiero wtedy nacisnąć ostatni klawisz kombinacji. Sekwencję klawiszy (kolejne klawisze są naciskane i zwalniane) określamy, oddzielając ich symbole przecinkiem. Czcionką pochyłą oznaczam również angielskie lub inne obcojęzyczne wyrażenia lub odpowiedniki używanych terminów.

Symbole wielkości fizycznych i zmiennych prostych oraz ogólne symbole funkcji nieużywane jako nazwy w arkuszu są pisane czcionką pochyłą niepogrubioną, na przykład liczba wyrazów n, funkcja f(x), natomiast symbole tablic i innych zmiennych złożonych - czcionką pochyłą pogrubioną.

Czcionką pogrubioną zostały zaznaczone ważne pojęcia, których określenia padają w tekście, oraz słowa w zdaniu, na które warto szczególnie zwrócić uwagę.

Granice przedziałów domkniętych oznaczone są nawiasem kwadratowym, a przedziałów otwartych nawiasem okrągłym, na przykład stwierdzenie, że liczba x należy do przedziału [0, 1), oznacza, że 0 ? x < 1.

W formułach (wyrażeniach) stosowanych w arkuszu kalkulacyjnym nazwy funkcji predefiniowanych pisane są WERSALIKAMI czcionką prostą niepogrubioną, a nazwy obowiązkowych argumentów funkcji - czcionką pogrubioną pochyłą, na przykład DŁ(tekst). Argumenty opcjonalne (które można pominąć) zostały zapisane czcionką pochyłą niepogrubioną, na przykład FILTRUJ(tablica; uwzględnienie; jeśli_puste). W materiałach informacyjnych firmy Microsoft argumenty opcjonalne są zazwyczaj umieszczane w nawiasach kwadratowych. W tej książce przyjąłem inne oznakowanie, gdyż nawiasy kwadratowe występują nierzadko jako faktyczne elementy formuł, co mogłoby prowadzić do niejasności.

Jeżeli formuła nie mieści się w wierszu na stronie, ale w arkuszu nie może być podzielona na wiersze, w treści umieszczałem znak . Znak ten nie jest częścią formuły i nie należy go wpisywać.

Adresy komórek, na przykład C5, nazwy stałych logicznych PRAWDA, FAŁSZ oraz znaczniki błędów, na przykład #ARG!, są pisane WERSALIKAMI.

Znak wielokropka (...) używany w opisie niektórych funkcji oznacza, że poprzedzający go parametr może być powtarzany. Tego znaku oczywiście nie należy wpisywać dosłownie. Przykładowo: zapis SUMA(arg1; arg2; ...) oznacza, że funkcja SUMA musi mieć co najmniej jeden argument, ale może mieć ich również dwa, trzy lub więcej.

W wyrażeniach nazwy zmiennych i formuł definiowane przez użytkownika w VBA są pisane czcionką pogrubioną pochyłą, podobnie jak obowiązkowe argumenty funkcji, natomiast funkcje LAMBDA, stałe liczbowe i tekstowe, operatory działań oraz określenia opisowe niebędące nazwami - czcionką prostą.

Formuły (wyrażenia) w Excelu zapisuje się z reguły bez spacji. Jednakże użycie spacji, jak również znaku przejścia do nowej linii (Alt+Enter) pomiędzy poszczególnymi elementami wyrażenia jest dopuszczalne, a nawet wskazane, gdy wyrażenie jest rozbudowane. Nie wolno tylko wstawiać spacji wewnątrz liczby (poza nielicznymi wyjątkami), wewnątrz adresu lub nazwy, wewnątrz operatorów złożonych z dwu znaków, np. <>, oraz między nazwą funkcji a nawiasem otwierającym. Zasady te omówiłem bardziej szczegółowo w [A2]. Ze względu na przejrzystość i czytelność zapisu w tej książce stosuję znaki spacji po znaku równości, od którego rozpoczyna się wyrażenie, oraz po średnikach rozdzielających argumenty funkcji. Tych spacji nie trzeba wpisywać do arkusza.

Wyrażenia definiujące nazwy (formuły nazwane) są prezentowane w tej książce z reguły w konwencji:

nazwa : = wyrażenie_definiujące

Nazwę należy wpisać w polu Nazwy w skoroszycie, a wyrażenie_definiujące, począwszy od znaku równości, w polu Odwołuje się do. Dwukropek służy tylko do poprawy przejrzystości zapisu i nie powinien być nigdzie wpisywany. Procedura nadawania nazw formułom jest opisana w [A2], a w odniesieniu do funkcji LAMBDA w rozdziale 2., w punkcie "Tworzenie funkcji LAMBDA".

Stosowany czasem, szczególnie w odniesieniu do nazw, zapis w nawiasach trójkątnych należy rozumieć w ten sposób, że tekst wewnątrz nawiasów (wraz z tymi nawiasami) należy zastąpić odpowiednią wartością. Nie jest to element składni Excela, tylko sposób przekazania, jak należy skonstruować wyrażenie. Przykładowo: jeśli pojawi się zapis Obraz <nr>, to należy rozumieć, że taki zapis oznacza np. Obraz 5.

Odnosząc się do kolejnych wersji programu Excel, będę używał na przykład oznaczenia 2021+, gdy opis będzie dotyczył wersji 2021 i następnych (w tym również 365), a oznaczenia 2019- w przypadku wersji 2019 i wcześniejszych.

Odnośniki do literatury są ponumerowane kolejno w kolejności ich przywołania i umieszczone w nawiasach kwadratowych. Odwołania do poprzednich tomów tej serii są skonstruowane w specjalny sposób. W nawiasach umieszczam literę A, B lub C odpowiednio w odniesieniu do tomu 1. - 3., a następnie numer rozdziału. Odwołania do tej książki będą podawane opisowo.

Podziękowania

Przy pracy nad tym opracowaniem korzystałem z uwag i wskazówek Czytelników moich poprzednich książek, którzy zaproponowali uzupełnienia lub uściślenia tekstu. Chciałbym w tym miejscu serdecznie podziękować wszystkim, którzy zechcieli podzielić się ze mną swoimi komentarzami i problemami, jakie można rozwiązywać za pomocą arkusza kalkulacyjnego.

Przez kilkanaście lat czynnie uczestniczę w internetowych forach ExcelForum.pl i elektroda.pl, a od niedawna również w międzynarodowych stackoverflow.com i superuser.com, skąd czerpię inspirację i gdzie pogłębiam swoją wiedzę, a także w miarę swoich możliwości i umiejętności staram się pomagać innym użytkownikom w rozwiązywaniu ich problemów. Przez te fora można się również ze mną kontaktować. W tym miejscu chciałbym złożyć podziękowania wszystkim ekspertom z tych forów, z których wiedzy i pomysłów korzystałem. Jest to grono dość liczne, chciałbym wymienić (alfabetycznie) pseudonimy osób, od których czegoś ważnego się nauczyłem lub które zainspirowały mnie do własnych poszukiwań:

Artik, Bill Szysz, bodek, hurgadion, Kaper, Marecki, Rafał B., Tajan, Waldek, Wormsek.

Dużo ciekawych informacji i pomysłów można znaleźć również w serwisie YouTube. Chciałbym tu wymienić kilka kanałów polskich i zagranicznych, z których treści korzystałem:

Polskie: Malinowy Excel i PMSocho.

Zagraniczne: Access Analytic, Excel Campus - Jon, Excel Macro Mastery, Excel Off The Grid, Leila Gharani, MyOnlineTrainingHub.

Twórcy tych i podobnych kanałów zasługują na moje słowa uznania.

Autor

Rozdział 1. Nowe podejście do obliczeń z udziałem tablic

Koncepcja formuł dynamicznych

Począwszy od września 2018 roku, Microsoft rozpoczął udostępnianie użytkownikom platformy Microsoft 365 (dawniej znanej jako Office 365) nowej postaci formuł tablicowych, którą określa się jako formuły dynamiczne (początkowo używano również terminu formuły rozlane, ale jest on obecnie mniej popularny). Od roku 2020 mogą z nich korzystać wszyscy subskrybenci tej platformy. Dotyczy to również bezpłatnej wersji Online (w ramach usługi OneDrive). Formuły dynamiczne są też obsługiwane przez "pudełkowe" wersje Excela 2021 i 2024. Docelowo powinny one zastąpić używane wcześniej statyczne formuły tablicowe, wprowadzane do arkusza za pomocą kombinacji klawiszy Ctrl+Shift+Enter.

Jest to dość istotna zmiana w koncepcji działania arkusza. Niesie też ze sobą wielorakie konsekwencje. Nic się nie zmieniło, jeśli chodzi o formuły wykonujące obliczenia na pojedynczych danych. Zmiana dotyczy tylko tych przypadków, gdy w formule są zawarte odwołania do danych grupowych (tablic lub zakresów komórek). W takim przypadku formuła może być interpretowana dwojako: pierwszy sposób zakłada, że do obliczeń bierze się tylko jedną wybraną wartość z podanego zakresu lub tablicy (formuła selektywna), drugi sposób przewiduje użycie danych grupowych w całości. W tym drugim przypadku mamy do czynienia z formułą tablicową.

Oba te sposoby funkcjonowały i nadal funkcjonują. Zmiana dotyczy tego, która interpretacja jest przyjmowana domyślnie. Dotychczas jako domyślną uznawano interpretację selektywną. Po naciśnięciu klawisza Enter formuła, która odwoływała się do danych grupowych, była zazwyczaj obliczana tak, że z każdej tablicy była wybierana do obliczeń tylko jedna wartość. W rzeczywistości istniały wyjątki od tej interpretacji i w przypadku złożonych formuł było to dość skomplikowane, ale taka była ogólna zasada. Gdy użytkownik chciał wprowadzić do arkusza formułę tablicową, czyli wykorzystującą wszystkie wartości, musiał najpierw zaznaczyć w arkuszu miejsce (ciągły obszar komórek) na oczekiwany wynik, następnie wprowadzić formułę do pierwszej komórki tego obszaru i wreszcie zatwierdzić klawiszami Ctrl+Shift+Enter. Tak uzyskana formuła zwracała tablicę wyników do zaznaczonego obszaru komórek. Formuła ta była statyczna, to znaczy jej rozmiar był ustalony w momencie zatwierdzania i mógł później podlegać modyfikacji tylko w trybie edycji formuły. Nie ma możliwości wstawiania (czy usuwania) wierszy ani kolumn w obszarze obejmowanym przez taką formułę. Nawet gdy formuła tablicowa zwracała wynik w pojedynczej komórce, ale w trakcie obliczania tego wyniku odwoływała się do danych grupowych, to i tak wymagała zatwierdzenia przez Ctrl+Shift+Enter.

Formuła odwołująca się do grupy danych jest domyślnie formułą tablicową

Według nowej koncepcji każda formuła jest traktowana domyślnie jak tablicowa. Użytkownik wprowadza formułę do jednej komórki (w przypadku formuły zwracającej tablicę wyników będzie to lewa górna komórka tej tablicy) i zatwierdza ją samym klawiszem Enter. Program analizuje formułę, tak jakby (według poprzedniej koncepcji) była zatwierdzana tablicowo. Jeśli formuła zwraca pojedynczy wynik, jest on po prostu wyświetlany w komórce z formułą. Jeśli formuła zwraca tablicę wyników, program Excel sam określa rozmiary tej tablicy i wyprowadza wyniki do sąsiadujących komórek, otaczając cały obszar wynikowy cienką obwódką (jest ona widoczna po zaznaczeniu którejkolwiek komórki obszaru wynikowego i znika po zaznaczeniu komórki spoza tego obszaru). Ilustruje to rysunek 1.1.

Rysunek 1.1. Formuła dynamiczna w komórce E2

Cechy formuł dynamicznych

Edycji może podlegać tylko pierwsza komórka obszaru tablicy, w pozostałych komórkach w podglądzie na pasku formuły wyrażenie jest wyświetlane w kolorze szarym i jest nieedytowalne (w przypadku tradycyjnych formuł tablicowych można edytować dowolną komórkę w obszarze formuły, a wszystkie te formuły są identyczne). Nie ma przeszkód, aby wstawiać lub usuwać komórki, wiersze czy kolumny w obszarze wyników nowej tablicy dynamicznej. W takim przypadku wartości przesuwają się odpowiednio, tak aby tablica zachowała spójność. Można sobie wyobrazić taką tablicę dynamiczną jako obrus, którym przykryty jest rozsuwany stół, a ten obrus jest przyczepiony do stołu w swoim lewym górnym rogu.

Taka tablica jest dynamiczna w tym sensie, że przy wprowadzaniu formuły liczba komórek wynikowych jest określana automatycznie, a jeśli w arkuszu zmienią się wartości komórek, od których zależą rozmiary tablicy zwracanej przez formułę, to przy przeliczaniu arkusza rozmiary te zostaną skorygowane i wyświetli się odpowiednio mniej lub więcej wyników. Tradycyjne formuły tablicowe w arkuszu w takiej sytuacji nie zmieniają rozmiaru i albo wyświetlają tylko część wyników, albo wyświetlają w niektórych komórkach kod błędu braku danych (#N/D). Zachowaniem dynamicznym charakteryzują się natomiast formuły tablicowe, którym nadano nazwę. Takie zachowanie nowych tablic nazwano rozlaniem (ang. spill), a uzyskiwane wyniki określano początkowo mianem tablic rozlanych. Obecnie bardziej popularny jest termin tablica dynamiczna.

Nowe formuły tablicowe są zapisywane z punktu widzenia pierwszej komórki tablicy rozlanej, a rodzaj użytych odwołań (względne, absolutne czy mieszane) nie ma wpływu na działanie formuły. Wszystkie komórki tablicy rozlanej "dziedziczą" tę samą formułę, która została wprowadzona do pierwszej komórki, ale kopiować można tylko pierwszą komórkę lub obszar, który ją zawiera.

Choć "rozlewanie" wyniku formuły odbywa się automatycznie, to jednak komórki, w których mają się znaleźć wyniki formuły, muszą być puste. Jeśli choć jedna z nich ma jakąś zawartość (samo formatowanie nie przeszkadza), to formuła nie może się rozlać, obramowanie obszaru zmienia się na przerywane (widać je tylko, gdy aktywna jest komórka z formułą) i w komórce z formułą (lewy górny róg obszaru) pojawia się komunikat błędu #ROZLANIE! (w wersji angielskiej #SPILL!). Niestety komunikat ten w polskiej wersji jest dość długi i często nie mieści się w komórce o standardowej szerokości, więc może być widoczny jako znaki ######. W takim przypadku trzeba usunąć lub przenieść zawartość komórek, które przeszkadzają, ewentualnie tak zmodyfikować formułę, aby zajmowała mniej komórek. Gdy zniknie przeszkoda, formuła wyświetli prawidłowy wynik (rysunek 1.2).

Rysunek 1.2. Komunikat błędu #ROZLANIE! nie mieści się w komórce

Formuła dynamiczna nie może się prawidłowo rozlać w komórkach scalonych. Jeżeli w obszarze formuły dynamicznej wielokomórkowej znajdzie się choćby jedna komórka scalona, to wystąpi błąd #ROZLANIE! podobnie jak na rysunku 1.2. Nie ma natomiast przeszkód w stosowaniu formatowania Wyrównaj zaznaczenie do środka, które może spełniać funkcję podobną do scalania komórek, ale tylko w odniesieniu do wierszy.

Jeśli nowe formuły dynamiczne odwołują się do danych z innego skoroszytu, skoroszyt ten musi być otwarty. W razie zamknięcia skoroszytu źródłowego wszystkie połączone dynamiczne formuły tablicowe zwrócą błąd #ADR!, gdy tylko zostaną odświeżone.

Formuły dynamicznej zwracającej kilka wyników nie można kopiować przez "przeciąganie" czarnym plusem. Prowadziłoby to do nakładania się wyników formuł i w konsekwencji powodowałoby wyświetlenie błędu #ROZLANIE! Zamiast tego należy odpowiednio zmodyfikować argumenty użytych funkcji, tak aby formuła uwzględniła wszystkie potrzebne wartości.

Jak już pisałem, tablicę dynamiczną można sobie wyobrazić jako obrus pokrywający fragment stołu-arkusza. Wartości są widoczne na powierzchni, ale nie są związane sztywno z komórkami arkusza. Można odwołać się bezpośrednio do komórki zawierającej określoną wartość, na przykład = I5, ale takie odwołanie będzie nietrwałe, jeśli powyżej piątego wiersza wstawimy lub usuniemy wiersze czy komórki. Jeśli formuła dynamiczna jest w komórce H2, skuteczniej odwołamy się do komórki I5 za pomocą funkcji INDEKS:

Takie odwołanie jest niewrażliwe na wstawianie czy usuwanie elementów arkusza. Znaczenie znaku # (operatora zakresu dynamicznego) będzie wyjaśnione dalej.

Przy okazji wprowadzenia tablic dynamicznych poprawiono działanie funkcji INDEKS i innych podobnych funkcji, które wcześniej miały problemy z obsługą argumentów oznaczających indeksy, gdy miały one postać tablic. W starszych wersjach Excela, gdy chcieliśmy z tablicy lub zakresu wydzielić wybrane kolumny lub wiersze, na przykład:

to wynik można było wyświetlić w arkuszu, ale nie można go było wykorzystać bezpośrednio do dalszych obliczeń. Aby to było możliwe, tablice indeksowe musiały być zapisywane w dość skomplikowany sposób. Po wprowadzeniu tablic dynamicznych nie ma już z tym problemu, formuła taka zwraca pełnowartościową tablicę.

Pojawiły się ponadto nowe funkcje, takie jak WYBIERZ.WIERSZE czy WYBIERZ.KOLUMNY, które pozwalają osiągnąć podobny wynik w nieco inny sposób. Piszę o tych funkcjach w rozdziale 8. w odpowiednim podrozdziale.

Interpretacja formuł dynamicznych w starym stylu według zasady rzutu prostokątnego

Wobec wprowadzenia zasady, że naciśnięcie samego Enter spowoduje tablicową interpretację formuły zawierającej dane grupowe, trzeba było określić, w jaki sposób zasygnalizować intencję interpretacji takiej formuły w sposób selektywny (czyli tak, jak domyślnie były interpretowane te formuły do wersji 2019 włącznie). W tym celu niezbędne było wprowadzenie nowego operatora @ zwanego po angielsku implicit intersection operator. Zostało to przetłumaczone w materiałach Microsoftu jako "operator przecięcia pośredniego". Ja jednak sugerowałbym użycie terminu "operator rzutu prostokątnego" lub krócej "operator rzutowania", gdyż wydaje mi się, że pojęcie "rzutu prostokątnego" u większej liczby osób wywoła jakieś skojarzenie, niż wywołałoby określenie "przecięcie pośrednie". A chodzi w sumie o to, aby w nowej rzeczywistości tablic dynamicznych znaleźć określenie dla takiego zachowania formuły, jakie było zachowaniem domyślnym w czasach klasycznych formuł tablicowych.

Działanie operatora @

Gdy tradycyjna formuła tablicowa odwoływała się do zakresów komórek w wyrażeniu złożonym, a wyrażenie było zatwierdzane samym klawiszem Enter (a nie tablicowo przez Ctrl+Shift+Enter), formuła wybierała z podanego zakresu jedną komórkę i wartość z tej jednej komórki przetwarzała dalej na wynik. Wybór tej komórki odbywał się na zasadzie rzutu prostokątnego, to znaczy z komórki zawierającej formułę prowadziliśmy linię prostopadłą do wiersza czy kolumny z zakresem danych i miejsce przecięcia (czyli odpowiednio ten sam wiersz lub ta sama kolumna) wskazywało komórkę, którą należy uwzględnić w obliczeniach.

Na przykład (w starszym Excelu, wersja 2019-) w komórce B5 umieściliśmy formułę:

i zatwierdziliśmy klawiszem Enter. Żeby obliczyć tę formułę, Excel musiał odnaleźć w pionowym zakresie komórek D3:D6 tę komórkę, która leżała w tym samym wierszu co komórka z formułą, czyli w wierszu piątym. Jest to komórka D5. Ona zostałaby wzięta do obliczeń i funkcja zwróciłaby wynik równy D5^0,5. Gdyby zatwierdzić formułę tablicowo (przez Ctrl+Shift+Enter), do obliczeń byłyby wzięte wszystkie cztery komórki z zakresu D3:D6. Takie jest właśnie działanie operatora rzutowania @, tyle że było to zachowanie domyślne, więc Excel działał w ten sposób "w tle" i nie trzeba było używać tego operatora w sposób jawny.

Po wprowadzeniu nowej filozofii formuł tablicowych użycie samego Enter wystarcza, żeby formuła została obliczona jako tablicowa (czyli z wykorzystaniem wszystkich przekazanych danych). Jednak w pewnych przypadkach użytkownik formuły może potrzebować, aby została ona obliczona tak jak w klasycznej wersji Excela, czyli z zastosowaniem zasady rzutu prostokątnego. I do tego właśnie jest potrzebny nowy operator @. Nie jest on taki całkiem nowy, bo pełnił już tę funkcję w odwołaniach strukturalnych stosowanych w tabelach programu Excel dostępnych od wersji 2007 (pojawił się po raz pierwszy w tej roli w wersji 2010). Ale tu zakres jego zastosowania został rozszerzony.

A zatem aby w nowym Excelu, obsługującym tablice dynamiczne, ograniczyć zakres przetwarzanych danych do jednej komórki, należy zapisać powyższe wyrażenie jako:

Operator @ można umieścić przed zakresem komórek i wówczas działa on na zasadzie rzutu prostokątnego, ale można go też umieścić przed tablicą lub w szczególnym przypadku przed stałą tablicową i wtedy wskazanie pada na pierwszy (lewy górny) element tablicy. Operator @ jest operatorem lewostronnym. Jeśli w powyższym zapisie użyć dodatkowej pary nawiasów:

to wówczas działanie operatora @ odnosić się już będzie do tablicy, a nie do zakresu i wyrażenie zwróci jako wynik pierwszy element, czyli D3^0,5. W tradycyjnym Excelu taki efekt nawet trudniej uzyskać, bo trzeba najpierw nadać nazwę i to nie samemu zakresowi, ale dowolnemu wyrażeniu z tym zakresem (wystarczy dopisać + na początku).

Jeśli formuła zawierająca odwołanie do danych grupowych została przygotowana w klasycznej wersji Excela i przy jej zatwierdzaniu nie użyto Ctrl+Shift+Enter, lecz samego Enter, to taka formuła po otwarciu w nowej dynamicznej wersji Excela będzie miała dopisane operatory rzutowania w odpowiednich miejscach. Gdy zobaczymy, że zostały dopisane do formuły (użytkownik nie mógł ich tam umieścić, bo kontrola składni w starszych wersjach Excela tego nie dopuszczała i były one usuwane), oznacza to na pewno, że formuła była zatwierdzana przez Enter, a nie tablicowo. W pewnych przypadkach formuły mogły zwracać taki sam wynik przy zatwierdzeniu tablicowym i zwykłym. W związku z tym użycie operatora @ może czasem wydać się zbędne. Można to zweryfikować i w takich przypadkach operatory te pominąć. Ale jeśli je pozostawimy zgodnie z sugestią programu, wyniki również będą takie jak we wcześniejszej wersji.

Cechy operatora @

Użyty przed odwołaniem do pojedynczej komórki niczego nie zmienia (odwołanie pozostaje odwołaniem, nie ma rzutowania); użyty przed zakresem jednowymiarowym (wektorem, wierszem lub kolumną) wyodrębnia z niego jedną komórkę na zasadzie rzutu prostokątnego i zwraca odwołanie do tej komórki; użyty przed zakresem dwuwymiarowym generuje błąd #ARG!; nie może być używany przed zakresem złożonym; użyty przed tablicą (a także funkcją zwracającą tablicę) zwraca wartość pierwszego elementu tej tablicy; użyty w wyrażeniu złożonym działa na przylegający z jego prawej strony fragment wyrażenia, który można zinterpretować jako odwołanie lub tablicę, przy czym są uwzględniane operatory jednoargumentowe (-, +) stojące przed adresem, a także operator przecięcia (spacja).

To ostatnie zachowanie trudno precyzyjnie opisać, więc zilustruję je przykładami. Wyobraźmy sobie, że w komórce C5 wpisujemy wyrażenie odwołujące się do zakresu A3:A6.

Wyrażenie Znaczenie

= @A3:A6 A5 (odwołanie)

= @+A3:A6 A3 (wartość)

= @A3:A6% A5% (wartość)

= @(A3:A6%) A3% (wartość)

Działanie operatora @ nie jest dokładnie zgodne z działaniem Enter we wcześniejszych wersjach Excela, chociaż taką sugestię można odczytać z materiałów Microsoftu. Działanie tego operatora jest bardziej przewidywalne, natomiast w działaniu poprzednich wersji Excela było więcej wyjątków i sytuacji, w których można było uniknąć używania kombinacji klawiszy Ctrl+Shift+Enter, a mimo to otrzymać wynik uwzględniający wszystkie komórki zakresu przekazanego w odwołaniu.

Weźmy najprostszy przykład funkcji agregującej typu suma. Jeśli funkcji tej przekazujemy odwołanie do zakresu komórek, na przykład

to w starszych wersjach Excela wystarczyło użycie Enter, aby uzyskać sumę 3 komórek. Dopiero gdy przed sumowaniem na tym zakresie chcieliśmy wykonać jakąś dodatkową operację, na przykład

to albo trzeba było użyć kombinacji Ctrl+Shift+Enter, albo po zatwierdzeniu samym Enter otrzymywało się jedną wartość na zasadzie rzutu prostokątnego.

W nowych edycjach Excela obsługujących operator @ użycie go w opisanych wyżej przypadkach wywoła ten sam skutek, każda z formuł:

zwróci wynik zgodnie z zasadą rzutu prostokątnego, a formuła

zwróci PIERWIASTEK(A3).

Natomiast bez operatora @ każda z formuł zwróci sumę wszystkich elementów.

W dokumentacji firmy Microsoft podano listę funkcji, które zwracają zakresy lub tablice i które w związku z tym mogą być poprzedzone operatorem @. Ta lista grupuje funkcje, które ze swej natury zwracają lub mogą zwracać wynik w postaci tablicy. Nie należy jednak zapominać, że wszystkie funkcje zwykłe, które otrzymają jako argument zakres lub tablicę, również zwracają tablicę wyników. Lista Microsoftu obejmuje następujące funkcje:

ADR.POŚR, CZĘSTOŚĆ, CZY.FORMUŁA, FILTERXML, FORMUŁA.TEKST, HIPERŁĄCZE, INDEKS, KOMÓRKA, MACIERZ.ILOCZYN, MACIERZ.JEDNOSTKOWA, MACIERZ.ODW, NR.KOLUMNY, PRZESUNIĘCIE, REGEXPP, REGEXPW, REGLINP, REGLINW, TRANSPONUJ, WIERSZ, WYST.NAJCZĘŚCIEJ.TABL, funkcje UDF zdefiniowane w VBA.

Należy jeszcze zwrócić uwagę na dwa aspekty. Pierwszy dotyczy funkcji zwracających odwołania: ADR.POŚR, INDEKS i PRZESUNIĘCIE oraz JEŻELI i WYBIERZ, o ile odwołania zostaną im przekazane jako argumenty. W tym przypadku operator @ będzie działał na zasadzie rzutu prostokątnego, w pozostałych przypadkach będzie wskazywał pierwszy element tablicy wynikowej. Poza tym w zależności od wartości argumentów funkcje mogą zwracać pojedyncze wartości lub dane grupowe. Jeśli argument jest przekazywany przez odwołanie, to nie wiadomo z góry, jaka to będzie wartość, więc uwzględnia się też inną możliwość.

Drugi aspekt dotyczy funkcji wywodzących się z dodatku Analysis ToolPak. Te funkcje nie przyjmują argumentów w postaci odwołań do zakresów komórek, mogą jednak przyjmować odwołania do tablic. I tak na przykład nie można zapisać formuły

bo otrzymamy błąd #ARG!. Można natomiast użyć wywołania

i w tym przypadku wynik prawidłowo rozleje się na 3 komórki. Jeśli chcielibyśmy tu użyć operatora @, należy użyć wyrażenia

aby zadziałała zasada rzutu prostokątnego, albo

aby wykonać obliczenia dla pierwszego elementu tablicy.

Inny przykład odnoszący się do funkcji INDEKS:

Jeśli D1 zawiera wartość z przedziału 1 - 10, to funkcja INDEKS zwraca pojedynczą wartość i obecność operatora @ niczego nie zmienia. Jeśli jednak komórka D1 zawierałaby wartość 0, to funkcja INDEKS bez operatora @ zwróciłaby kolumnę 10 wyników, a wobec użycia operatora @ zwróciłaby tylko jeden wynik (lub błąd) w zależności od tego, w którym wierszu wpisana była formuła.

Użycie operatora @ w nowych wersjach Excela jest możliwe również w wariancie mieszanym, to znaczy w tym samym wyrażeniu jedno odwołanie może być poprzedzone operatorem @, a drugie nie. Przykład:

Takie wyrażenie jest jednak niekompatybilne ze starszymi wersjami Excela, w których wszystkie odwołania muszą być traktowane jednolicie: albo tablicowo, albo na zasadzie rzutu prostokątnego. Jeśli arkusz z takim odwołaniem będziemy chcieli otworzyć w starszej wersji Excela, pojawi się sygnalizacja nieznanej funkcji _xlfn.SINGLE. Takie oznaczenie otrzymują w starszych wersjach Excela odwołania z operatorem @, których nie da się prawidłowo przekształcić.

Użycie operatora zakresu rozlanego #

Nowa koncepcja formuł tablicowych wiąże się z wprowadzeniem jeszcze jednego nowego operatora #, zwanego operatorem zakresu rozlanego (to jest oficjalna terminologia Microsoftu - zgrabniej byłoby: operatorem zakresu dynamicznego).

Ponieważ nowe formuły są dynamiczne, nie można z góry przewidzieć, do ilu komórek rozleje się wynik formuły. Aby można było odwołać się w innej formule do całego obszaru wynikowego tablicy dynamicznej, należy użyć nowego operatora #, który jest dopisywany do adresu pierwszej komórki tablicy dynamicznej (tej komórki, w której jest wpisana formuła). Jest to operator prawostronny. Zatem zapis E5# oznacza tablicę dynamiczną (a także formułę tablicową starszego typu), której lewy górny narożnik znajduje się w komórce E5.

Jeśli komórka E5 nie zawiera formuły dynamicznej, lecz dowolną zwykłą formułę (a także wynik działania tabeli danych), wówczas odwołanie odnosi się do samej komórki E5. Natomiast jeśli komórka E5 nie zawiera w ogóle formuły, albo zawiera formułę dynamiczną lub tablicową, ale nie jest to pierwsza komórka tej formuły, wówczas takie odwołanie zwraca błąd #ADR!. Należy jeszcze raz podkreślić, że gdy używamy operatora #, musimy wskazać pierwszą, a nie dowolną komórkę z zakresu formuły dynamicznej lub tablicowej.

Jeśli edytujemy formułę i zaznaczymy cały zakres komórek zajmowany przez formułę dynamiczną, Excel automatycznie przekształci odwołanie w dynamiczne, to znaczy np. zamiast A4:B10 wpisze A4#.

Przed operatorem # powinien być adres pojedynczej komórki lub odpowiadająca mu nazwa (nazwa też musi się odnosić do pojedynczej komórki). Może też być wyrażenie zwracające odwołanie do pojedynczej komórki.

Przykładowo zamiast I12# możemy użyć:

Jeśli chcemy utworzyć złożone wyrażenie adresowe, to powinniśmy przyjąć, że operator # ma taki sam priorytet jak dwukropek. A zatem niepoprawne będzie wyrażenie:

Należy je zapisać z nawiasami:

lub tak:

Poniższy przykład ilustruje użycie operatora # w przypadku niestandardowego sortowania danych. Przykład będzie w pełni zrozumiały po przeczytaniu opisu użytych w nim funkcji. Opis ten znajduje się w rozdziale 6. tej książki. (To odnosi się również do dalszych przykładów przytoczonych w tym rozdziale).

W przykładzie chodzi o posortowanie etykiet zapisanych w formacie XXX#??. Każda z etykiet składa się z 3 dowolnych liter na początku, a dalej mogą wystąpić 1 - 3 cyfry. Etykiety należy posortować rosnąco, ale biorąc pod uwagę tylko cyfry i sortując je w porządku liczbowym. Liczba etykiet do posortowania może zmieniać się dynamicznie.

Przykładowe dane i wyniki pokazano na rysunku 1.3.

Rysunek 1.3. Przykład użycia operatora # przy sortowaniu niestandardowym

Dane umieszczone są w kolumnie H od wiersza 12. w dół. Górna granica zakresu może być zmodyfikowana stosownie do potrzeb. W komórce I12 znajduje się dynamiczna formuła filtrująca:

Formuła ta eliminuje puste komórki w zakresie danych, jak również umożliwia późniejsze odwoływanie się do nich w sposób dynamiczny za pomocą operatora #. W tym przypadku można również zastosować funkcję PRZYTNIJZAKRES lub operator kropkowy (opis w rozdziale 11.), wtedy stosowanie operatora # staje się zbędne.

Jeszcze inny sposób, jaki można byłoby tu zastosować, to użycie funkcji UDF podobnej do właściwości Range.CurrentRegion znanej z VBA. Sama ta właściwość nie może być użyta w funkcji UDF, ale można napisać podobną funkcję, która może spełnić to zadanie. Przy końcu rozdziału podałem propozycję takiej funkcji UDF.

Teraz za pomocą odwołania I12# uzyskujemy dynamiczną kolumnę danych, którą możemy poddać sortowaniu. (Jeszcze inny sposób dostępu do danych w sposób dynamiczny to umieszczenie ich w tabeli Excela i ewentualnie nadanie nowej nazwy). Sortowanie przeprowadzimy za pomocą formuły:

Operator zakresu rozlanego nie obsługuje odwołań do skoroszytów zamkniętych. Zamiast tego zwracana jest wartość błędu #ADR!. Otwarcie skoroszytu, do którego występuje odwołanie, przywróci poprawne działanie.

Nowe rodzaje błędów i komunikaty o ich występowaniu

Wprowadzenie nowych funkcji i nowych sposobów działania w programie Excel spowodowało również poszerzenie katalogu błędów arkuszowych zgłaszanych przez program. Do tej pory występowało 7 rodzajów błędów. Ta liczba nie obejmuje błędu ####### występującego, gdy wyświetlana wartość nie mieści się w komórce lub nie może być wyświetlona w żądanym formacie; błąd ten nie ma numeru i nie jest rozpoznawany przez funkcję NR.BŁĘDU. Nie jest tu liczony również błąd odwołania cyklicznego, który nie zawsze jest błędem. Zależy to od ustawienia opcji zgody na obliczenia iteracyjne i może być zamierzone przez użytkownika.

Nowym błędom przypisano kolejne numery od 8 do 14 oraz 19 (i inne numery w VBA, żeby nie było zbyt łatwo). Poniżej w tabeli 1.1 zestawiłem ogólne informacje o tych błędach. Uwzględniłem w niej również błędy stosowane we wcześniejszych wersjach.

Nie sposób przy okazji nie zauważyć, że twórcy polskiej wersji Excela znowu zafundowali nam mało sensowną zmianę oznaczenia błędu #N/D!. W nowym Excelu oznaczenie tego błędu jest pozbawione wykrzyknika (pewnie po to, żeby go upodobnić do wersji angielskiej #N/A, w której wykrzyknika nie było).

Tabela 1.1. Zestawienie standardowych komunikatów błędów Excela i odpowiadających im predefiniowanych stałych VBA oznaczających numery błędów.

Numer błędu w Excelu

Komunikat polski

Komunikat angielski

Stała w VBA

Numer błędu w VBA

Krótki opis błędu

1

#ZERO!

#NULL!

xlErrNull

2000

Obszary nie przecinają się

2

#DZIEL/0!

#DIV/0!

xlErrDiv0

2007

Dzielenie przez 0

3

#ARG!

#VALUE!

xlErrValue

2015

Niewłaściwy typ argumentu

4

#ADR!

#REF!

xlErrRef

2023

Adres nie istnieje

5

#NAZWA?

#NAME?

xlErrName

2029

Niezdefiniowana nazwa

6

#LICZBA!

#NUM!

xlErrNum

2036

Niewykonalna operacja matematyczna

7

#N/D

#N/A

xlErrNA

2042

Brak danych

8

#POBIERANIE_DANYCH

#GETTING_DATA

xlErrGettingData

2043

Oczekiwanie na zakończenie pobierania danych

9

#ROZLANIE!

#SPILL!

xlErrSpill

2045

Obszar rozlania zawiera inne dane

10

#POŁĄCZ!

#CONNECT!

xlErrConnect

2046

Nie można uzyskać żądanych danych. Błąd występuje w związku z rozszerzonymi typami danych.

11

#ZABLOKOWANE!

#BLOCKED!

xlErrBlocked

2047

Wyłączony dostęp do zewnętrznego serwera danych lub funkcjonalności

12

#NIEZNANE!

#UNKNOWN!

xlErrUnknown

2048

Nieznany błąd

13

#POLE!

#FIELD!

xlErrField

2049

Pole nie istnieje w rozszerzonym typie danych

14

#OBL!

#CALC!

xlErrCalc

2050

Błąd tablicowy, najczęściej pusta tablica lub pusty wynik funkcji LAMBDA

19

#PYTHON!

#PYTHON!

     

Zasady zachowania zgodności między starszymi a nowymi wersjami Excela

Ze względu na zgodność wsteczną w nowych wydaniach Excela nadal są obsługiwane tradycyjne formuły tablicowe. Zaleca się jednak ich konwersję do nowych formuł dynamicznych, jeśli nie ma szczególnego powodu zachowania ich tradycyjnej postaci. Aby wykonać taką konwersję, należy skopiować pierwszą (lewą górną) komórkę formuły tablicowej do tymczasowej wolnej komórki, zaznaczyć zakres i usunąć zawartość wszystkich komórek z dotychczasową formułą tablicową, wreszcie skopiować z powrotem formułę z tymczasowej komórki na właściwe miejsce i zatwierdzić klawiszem Enter.

Jeśli arkusz zapisany w nowej wersji Excela, obsługującej tablice dynamiczne, zostanie otwarty w starszej wersji, tablice rozlane będą widoczne jako tradycyjne formuły tablicowe (pod warunkiem że będą używane funkcje dostępne w tej starszej wersji). Z kolei jeśli arkusz utworzony w starszej wersji zawiera formuły tablicowe, to po otwarciu w nowej wersji zostanie automatycznie przekształcony do wersji dynamicznej.

Jeśli przewidujemy, że arkusz utworzony w nowej wersji będzie uruchamiany również we wcześniejszych wersjach, to możemy sprawdzić jego zgodność, posługując się wbudowanym narzędziem, które można otworzyć za pomocą sekwencji poleceń:

Plik/Konto/Informacje/Wyszukaj problemy/Sprawdź zgodność

Ten sposób pozwala wybrać wersję lub wersje Excela, w których chcemy uruchomić nasz arkusz i odszukać komórki z formułami mogącymi sprawiać problemy. Nie jest to narzędzie absolutnie doskonałe, ale raczej zasygnalizuje problemy "na zapas", niż jakiś pominie. W każdym razie daje ono przynajmniej przybliżoną orientację. Nie znam niestety prostego i wiarygodnego sposobu, aby stwierdzić, czy określona formuła, która działa poprawnie w nowym Excelu, będzie wymagać zatwierdzenia tablicowego w starszych wersjach niedynamicznych. Teoretycznie tę odpowiedź powinno dać użycie właściwości .SavedAsArray, o której piszę dalej w punkcie "Właściwości identyfikujące formuły i zakresy dynamiczne", ale sprawdza się to, gdy formuła jest stosunkowo prosta, natomiast w przypadku bardziej złożonych formuł możemy otrzymać wynik True, chociaż formuła nie wymagała zatwierdzenia tablicowego.

Jak pogodzić formuły dynamiczne z tabelami Excela?

Nowe formuły dynamiczne mogą odwoływać się do danych zawartych w tabelach programu Excel i korzystać z odwołań strukturalnych. Ma to tę zaletę, że w razie zmiany rozmiaru tabeli zakres danych formuły zostanie automatycznie dopasowany. Natomiast same formuły dynamiczne muszą być umieszczane poza tabelami programu Excel. W tabelach tych nie można umieszczać wielokomórkowych formuł tablicowych - odnosiło się to do tradycyjnych formuł tablicowych i obowiązuje nadal w przypadku formuł dynamicznych.

Ponieważ jednak obie funkcjonalności mają duże znaczenie dla użytkownika, należy się zastanowić, jak pogodzić wymogi i ograniczenia obu tych form prezentacji danych i wyników.

Najpierw należy sobie uświadomić istotne różnice pomiędzy nimi. Tabele Excela są zorientowane kolumnowo, zarówno cała tabela, jak i każda kolumna mają obowiązkowo własne nazwy, poprzez które można się odwoływać do danych. Nazwy kolumn są umieszczone w pojedynczym wierszu nagłówkowym u góry tabeli. Ponadto tabela może mieć na dole opcjonalny wiersz podsumowania. Dane w komórkach są niezależne, co pozwala stosunkowo łatwo manipulować kolumnami i wierszami (przestawiać miejscami, ukrywać i odkrywać). Wiersze jednak nie mają nazw i nie można się w prosty sposób odwoływać do innego wiersza niż bieżący (to znaczy ten, w którym umieszczono formułę z odwołaniem). Każda komórka może zawierać stałą lub formułę, ale formuła może zwracać tylko pojedynczy wynik. Jeśli kolumna zawiera jednakowe formuły, to są one kopiowane automatycznie po dodaniu danych. Zaletą tabel jest automatyczne dopasowanie ich wielkości do danych. Jeśli jakaś formuła zewnętrzna odwołuje się do kolumny tabeli, to nie musimy się martwić o to, czy odwołanie obejmuje wszystkie dane, ani o to, czy trzeba filtrować puste komórki na końcu danych. Ponadto możemy bezpiecznie wstawiać lub usuwać kolumny, a te, które były wcześniej, zachowują swoje nazwy i nie musimy się obawiać, że zmieniły położenie w arkuszu. Wszystkie odwołania do nich pozostaną aktualne.

Dla odmiany tablice dynamiczne stanowią prostokątne obszary powiązanych ze sobą komórek bez wyróżniania jakiejkolwiek orientacji. Zawartość wszystkich komórek jest opisana jedną formułą dynamiczną, w związku z tym w obszarze formuły nie można umieszczać żadnych innych niezależnych danych (np. stałych). Nie można zmieniać kolejności kolumn czy wierszy ani usuwać lub dodawać czegokolwiek inaczej niż przez zmianę całej formuły. Nie można wstawiać ani usuwać wierszy formuły (można usunąć lub dodać wiersze arkusza "pod spodem" tablicy dynamicznej, wtedy formuła rozleje się na inne wiersze lub kolumny). Sama formuła jest przypisana do jednej komórki (lewej górnej) i usunięcie wiersza lub kolumny z tą komórką usuwa całą formułę. Można natomiast ukryć (a później odkryć) wiersze lub kolumny (wiersze można ukrywać również filtrem) i to wpływa na wygląd tablicy dynamicznej.

Formuły dynamiczne nie generują automatycznie żadnych nagłówków ani podsumowań (chociaż udostępnione niedawno funkcje GRUPUJ.WEDŁUG i PRZESTAWIAJ.WEDŁUG - opis w rozdziale 10. - mogą kopiować nagłówki danych i tworzyć podsumowania, ale to jest specyfika tych funkcji, a nie właściwość tablic dynamicznych). Kolumny i wiersze nie mają nazw, można się do nich odwoływać tylko przez pozycję.

Jeśli chodzi o formatowanie, to należy pamiętać, że formatowanie stałe jest powiązane z konkretną komórką, natomiast formatowanie warunkowe z wartością komórki. Zatem po usunięciu lub wstawieniu wiersza/kolumny arkusza te formatowania mogą się rozejść.

Można wyróżnić kilka sposobów powiązania tablic dynamicznych z tabelami Excela, które zapewniają spójne przetwarzanie zawartych w nich danych.

Metoda tablicy zsynchronizowanej

Metoda tablicy zsynchronizowanej polega na utworzeniu tablicy dynamicznej poza tabelą i połączeniu ich za pomocą formuł w taki sposób, że dane pozostaną powiązane. Aby zaimplementować tę metodę, należy:

W osobnym zakresie utworzyć tablicę dynamiczną, odwołującą się do danych zapisanych w tabeli. Użyć formuł, aby powiązać kolumny tabeli z odpowiednimi komórkami tablicy.

Można to sobie wyobrazić w dwóch wariantach: albo tworzymy osobną tabelę do umieszczenia danych przetworzonych (rysunek 1.4), albo wstawiamy dodatkowe kolumny do już istniejącej tabeli Excela (rysunek 1.5).

Rysunek 1.4. Synchronizowanie danych w tabelach Excela i tablicy dynamicznej (wariant z dwiema tabelami)

Rysunek 1.5. Synchronizowanie danych w tabelach Excela i tablicy dynamicznej (wariant z jedną tabelą)

Rozwiązanie to ilustruje prosty przykład sortowania danych. Sortowanie jest tylko przykładem, w którym łatwo zrozumieć logikę wyników. W rzeczywistych zastosowaniach ta logika może być dużo bardziej złożona.

W tabeli Excela w dwóch kolumnach mamy imiona studentów i ich wyniki punktowe. Poza tabelą (obok) używamy funkcji SORTUJ, aby posortować te dane z użyciem formuły dynamicznej. Formuła może być taka:

Tabela_niesort - to nazwa nadana tabeli z danymi, argument 2 oznacza sortowanie według drugiej kolumny, a argument -1 określa malejącą kolejność sortowania.

Jeśli w tej samej tabeli chcemy mieć dane źródłowe i przetworzone, to należy określić ściśle zakres kolumn, które chcemy sortować, np.:

Funkcja SORTUJ nie uwzględnia nagłówków kolumn, ale można je skopiować ręcznie, aby wyniki były bardziej czytelne. Odwołanie do tabeli przez jej nazwę zapewnia, że w razie dopisania imion do listy lub ich usunięcia wyniki dopasują się automatycznie.

Jeśli chcemy umieścić posortowane wyniki z powrotem w tabeli, to można w tym celu utworzyć odwołania do poszczególnych komórek z wynikami formuły dynamicznej. Jeśli mamy już utworzoną tabelę na wyniki lub chcemy umieścić wyniki w tej samej tabeli co dane źródłowe, to wystarczy utworzyć te odwołania w jednym wierszu, a tabela skopiuje je automatycznie. Można również skopiować wyniki formuły dynamicznej i wkleić je jako łącze (opcja Utwórz łącze przy wklejaniu specjalnym). Jednak łącze możemy wkleić tylko do zakresu, a nie do tabeli, zatem najpierw trzeba wkleić łącze, a później przekształcić zakres w tabelę.

O ile formuła dynamiczna dostosowuje się automatycznie do zmian rozmiaru tabeli, o tyle w drugą stronę nie jest to w pełni zautomatyzowane. Trzeba ręcznie dostosować wymiary tabeli wynikowej do danych. Można zwiększyć tabelę, umieszczając kursor w jej ostatnim polu i naciskając klawisz Tab. Można również zmienić rozmiary tabeli, przeciągając myszą w dół lub do góry znacznik tabeli, który znajduje się w jej prawym dolnym rogu. Zbędne wiersze trzeba oczyścić z pozostałości danych i formatowania.

Metoda wyboru pojedynczych wartości

Inny sposób synchronizacji polega na wyborze pojedynczych wartości z tablicy wyników formuły dynamicznej. W tabeli możemy umieszczać tylko pojedyncze wartości. Mając tablicę wyników, możemy wybrać z niej pojedyncze wartości na przykład za pomocą funkcji INDEKS. Wykorzystując te same dane, możemy zapisać formułę sortowania w następujący sposób:

Zapis jest bardziej zwięzły, bo nie wyświetlamy osobno wyniku tablicy dynamicznej, natomiast obliczeniowo jest mniej efektywny, bo tablica dynamiczna jest generowana w każdej komórce, a wykorzystywana jest tylko jedna wartość.

Ta formuła jest tak zapisana, że numery kolumn są wyliczane, żeby łatwo było kopiować. Ale to powoduje, że kolumn nie możemy przestawiać. Alternatywnie można zapisać stałe numery kolumn. Wtedy można będzie kolumny łatwo przestawić, natomiast kopiowanie formuły z jednej kolumny do drugiej już nie będzie możliwe.

Przy zmianie liczby wierszy w tabeli źródłowej tabela wynikowa musi być częściowo dopasowana ręcznie, jak w poprzedniej metodzie.

Metoda zwiniętego tekstu

Tę metodę możemy stosować, gdy w jednej komórce tabeli powinniśmy wyświetlić kilka wartości. W takim przypadku można te wartości połączyć w jeden ciąg tekstowy, na przykład za pomocą funkcji POŁĄCZ.TEKSTY.

Za ilustrację niech posłuży podobne zestawienie, w którym studenci zdobywają punkty z różnych przedmiotów, a my chcemy to podsumować tak, żeby było widać, jakie przedmioty zaliczała każda z osób. Dane i podsumowanie pokazałem na rysunku 1.6.

Rysunek 1.6. Zestawienie wyników sprawdzianów z zastosowaniem metody zwiniętego tekstu

Tabele mają nazwy Tabela_niesort3 i Tabela_sort3.

W kolumnie [Student] mamy unikatową listę osób posortowaną alfabetycznie. Uzyskano to za pomocą formuły:

W kolumnie [Przedmioty] zestawiono nazwy przedmiotów, z których każda z osób otrzymała punkty. Nazwy przedmiotów skrócono do 3 pierwszych znaków i ułożono alfabetycznie, tworząc z nich listy rozdzielone przecinkami:

W kolumnie [L. punktów] podsumowano punkty poszczególnych osób:

Ta metoda jest szczególnie przydatna, gdy nie ma relacji jeden-do-jednego między danymi a tabelą wynikową. Łącząc wartości w jednej komórce, można wyświetlać złożone dane w formacie tabeli. Takie podejście zapewnia jasną i zwięzłą prezentację zawartości tabeli z danymi.

Tabela utworzona na podstawie fragmentu tablicy dynamicznej

Utworzenie tabeli na podstawie tablicy dynamicznej umożliwia odwoływanie się do elementów za pomocą ich nazw kolumn, na co pozwala elastyczność tabel. Funkcja FILTRUJ odgrywa kluczową rolę w tej metodzie, umożliwiając wyodrębnienie i wyświetlenie w tabeli określonych danych z tablicy dynamicznej. Takie podejście łączy w sobie mocne strony zarówno tabel, jak i tablic, oferując zaawansowane rozwiązanie do manipulowania danymi i ich analizy. Korzystając z funkcji FILTRUJ, można dynamicznie aktualizować tabelę na podstawie zawartości tablicy, zapewniając dokładne i aktualne informacje.

Przykład takiego ujęcia pokazuje rysunek 1.7.

Rysunek 1.7. Tablica dynamiczna pośrednicząca pomiędzy tabelą źródłową a tabelą wynikową

W tym przykładzie dane są takie jak w przykładzie poprzednim. Teraz jednak chcemy wybrać jeden z przedmiotów i dla niego utworzyć listę osób i punktacji. W komórce AY16 jest lista rozwijana, której źródłem jest kolumna Tabela_niesort4[Przedmioty]. Niestety, nie można użyć odwołania strukturalnego wprost w definicji listy. Należy albo zdefiniować nazwę w Menedżerze nazw i w treści tej nazwy podać odwołanie strukturalne, albo podać wprost adres całej kolumny bez nagłówka. W obu tych przypadkach zawartość listy dopasuje się automatycznie do zawartości kolumny w tabeli. Ponadto w Excelu 365 są również automatycznie usuwane duplikaty, więc przedmioty na liście nie będą się powtarzać (we wcześniejszych wersjach Excela nie było tej funkcjonalności). Jeśli źródłem listy ma być wynik formuły dynamicznej, to należy użyć odwołania do pierwszej komórki z wynikami z dopisanym znakiem # (można też takiemu odwołaniu nadać nazwę).

Po dokonaniu wyboru z listy przedmiotów dane z tabeli są filtrowane w tablicy dynamicznej z wykorzystaniem funkcji FILTRUJ. W komórce BA15 jest formuła kopiująca nagłówki tabeli:

A w komórce BA16 właściwa formuła filtrująca:

Wyniki tej formuły są wykorzystywane w tabeli wynikowej do wyświetlenia list osób i odpowiadających im list wyników (w AY23 oraz kopia w AZ23):

Podane rozwiązania należy traktować tylko jako przykłady do twórczego rozwinięcia i dopasowania do swoich potrzeb.

Ukrycie pustych wierszy za pomocą filtra z fragmentatorem

Jeszcze inny sposób dopasowania rozmiarów tabeli polega na ukrywaniu pustych wierszy za pomocą filtra. Pomysł zaczerpnąłem z filmu opublikowanego na YouTubie przez Wyna Hopkinsa z Access Analytic [1].

Jak już wcześniej pisałem, rozmiary tabeli nie dopasowują się automatycznie do danych generowanych przez formuły. Jeśli chcemy sobie ułatwić ręczne dopasowanie, można utworzyć tabelę z zapasowymi wierszami, a następnie ukryć puste wiersze za pomocą filtra, który jest dostępny w każdej tabeli. Niestety, ukrywa się przy tym nie tylko wiersze tabeli, ale całego arkusza, zatem obok naszej tabeli nie można mieć innych istotnych danych.

Jako przykład wykorzystam znowu listę studentów zdobywających punkty za zaliczenie różnych przedmiotów. Tym razem na liście ta sama osoba może pojawić się kilka razy z tym samym lub różnymi przedmiotami. Chcemy teraz uzyskać listę osób - wraz z punktacją (jeśli są duplikaty, można obliczyć średnią lub wynik maksymalny) - które zaliczały wybrany z listy przedmiot.

Dane źródłowe umieszczono w tabeli o nazwie Tabela_dane, która zaczyna się od komórki B2. W wierszach tabeli są imiona studentów, nazwy przedmiotów i liczby punktów. Ta sama osoba może mieć kilka wpisów z tego samego przedmiotu. Tabela jest widoczna tylko częściowo, bo część wierszy arkusza została ukryta.

Widok arkusza pokazuje rysunek 1.8.

Rysunek 1.8. Wykorzystanie fragmentatora do ukrywania pustych wierszy w tabeli wynikowej

W komórce F3 utworzono (za pomocą formuły dynamicznej) listę unikatowych przedmiotów. Użyto formuły:

Ta lista posłużyła jako źródło listy rozwijanej w komórce H3. W tej komórce jako źródło listy wskazano zakres dynamiczny:

Komórce F3 można też nadać nazwę Przedmioty i tę nazwę przywołać w definicji źródła listy:

W komórce H3 dokonujemy wyboru; tej komórce nadałem nazwę Wybór.

Ten sposób definiowania listy różni się od opisanego wcześniej. Są to sposoby równoważne i wybór należy do projektanta obliczeń.

Tabela do prezentacji wyników rozpoczyna się w komórce J5 i obejmuje 3 kolumny. Pierwsza z nich jest kolumną pomocniczą i została ukryta. Tabela nosi nazwę Tabela12 i została zaprojektowana z zapasem, tak aby pomieściła wszystkie wyniki i nie wymagała powiększania.

Jak to działa?

W kolumnie J (ukrytej) w komórce J6 umieszczono formułę:

Formuła ta skopiuje się automatycznie w całej kolumnie. Ta formuła wyświetla tekst "Pokaż" w każdym wierszu, w którym jest wyświetlane imię studenta, a w pustych wierszach wyświetla kropkę. Kropkę wybrałem ze względu na działanie fragmentatora, który w razie pustego wpisu wyświetla słowo "(puste)", a kropka jest mniej widoczna. Można też wykorzystać ZNAK(160), czyli spację nierozdzielającą, która jest niewidoczna i nie generuje słowa "(puste)".

W kolumnie K ([Student]) wyświetlane są imiona studentów przypisanych do wybranego przedmiotu. Formuła zastosowana w komórce K6:

Formuła najpierw filtruje imiona studentów przypisanych do wybranego przedmiotu:

Następnie wybiera z listy imiona unikatowe, wreszcie za pomocą funkcji INDEKS z całej tablicy wyników wybiera po jednym imieniu do wyświetlenia w danej komórce. Numer bieżącego wiersza został tu obliczony przez zliczenie wierszy od nagłówka do wiersza bieżącego i odjęcie 1 (wiersz nagłówkowy).

Gdy zabraknie danych, formuła INDEKS zwraca błąd, który jest zamieniany na pusty tekst przez funkcję JEŻELI.BŁĄD. W ten sposób w kolumnie otrzymujemy listę imion, a następnie puste teksty. Ten wynik jest interpretowany w kolumnie J.

W kolumnie L ([Śr.punktów]) obliczany jest średni wynik każdego studenta z wybranego przedmiotu (alternatywnie ktoś może obliczyć wynik maksymalny). Służy do tego formuła:

Sprawdzane są dwa warunki: imię studenta i wybrany przedmiot, po czym z wyników liczona jest średnia.

Tak zapisane formuły wyświetlają odfiltrowane wyniki, a pod spodem puste wiersze. Jeśli chcemy uniknąć wyświetlania tych pustych wierszy, można je odfiltrować. Aby ułatwić sobie zadanie, można zamiast standardowego przycisku filtra użyć fragmentatora. Zaletą fragmentatora jest to, że jest stale widoczny w arkuszu i wystarczy jedno kliknięcie zamiast dwóch. Pole jest też większe, więc łatwiej w nie trafić myszą. Ale fragmentator wyświetla wszystkie pozycje filtra (nie da się ich pogrupować), dlatego nie byłoby wygodne użycie fragmentatora do kolumny [Student]. W związku z tym użyto pomocniczej kolumny, aby wszystkich studentów połączyć w jedną grupę do wyświetlenia. Fragmentator został wstawiony nad tabelą i powiązany z ukrytą kolumną J ([Pokaż]). Ta kolumna zawiera tylko dwie wartości "Pokaż" i coś, co zastępuje wartość pustą, np. kropkę. Dzięki temu fragmentator wyświetla tylko dwie pozycje (fragmentator jest wyświetlany bez nagłówka). Nas interesuje pozycja "Pokaż", gdyż kliknięcie jej odświeża filtr po zmianie wybranego przedmiotu i pokazuje wszystkie pozycje wymagające wyświetlenia.

Alternatywą użycia fragmentatora mogłoby być makro zdarzeniowe.

Ponieważ filtr nie odświeża się automatycznie, to wprowadzony został jeszcze sygnalizator konieczności odświeżenia. Jeśli tabela wyświetla kilka pustych wierszy, to jest to widoczne, a jeśli uznamy, że nam to przeszkadza, można filtr odświeżyć, klikając przycisk "Pokaż". Jeśli jednak tabela nie wyświetla wszystkich wierszy, które powinny być wyświetlone, to odświeżenie jest konieczne i trzeba jakoś o tym poinformować użytkownika. W tym celu została zdefiniowana komórka M3 z etykietą "Ukryte", która wyświetla liczbę wierszy z niepustą zawartością, które powinny zostać wyświetlone. Ta komórka zawiera formułę:

Formuła ta zlicza wiersze, które w kolumnie Tabela12[Student] mają niepustą zawartość, a równocześnie pozostają w wierszach ukrytych. Jeśli wynik jest większy od zera, komórkę można sformatować warunkowo, tak żeby była lepiej widoczna i skłoniła użytkownika do odświeżenia filtra.

Oczywiście bez użycia fragmentatora i bez makra też można sobie poradzić. Nie jest wtedy potrzebna kolumna pomocnicza. Filtr definiujemy w kolumnie [Student] tak, żeby pokazywał wszystkie niepuste wiersze. Po zmianie wyboru przedmiotu należy kliknąć "lejek" aktywnego filtra i po rozwinięciu menu filtra tylko kliknąć OK lub nacisnąć Enter. Widać, że w tym przypadku uzyskanie nieco lepszego komfortu obsługi wymaga wielu dodatkowych działań. Czy warto je podejmować? Decyzja należy do projektanta i użytkownika.

Użycie formuł dynamicznych z innymi funkcjonalnościami Excela

Oprócz tabel Excela istnieje szereg innych funkcjonalności, które mogą wykorzystywać formuły tablicowe lub odwołania do zakresów dynamicznych. Warto przeanalizować, czy i jak zastąpienie formuł tablicowych przez tablice dynamiczne wpłynie na ich działanie.

Menedżer nazw

Okienko Menedżer nazw obsługuje nazwy nadane przez użytkownika różnego rodzaju obiektom przechowującym lub generującym dane. Wśród tych obiektów wymienić można: (1) zakresy komórek, (2) formuły generujące tablice danych. Obiekty te mogą mieć charakter statyczny lub dynamiczny. W obu przypadkach obsługa jest prawidłowa. Jednak używając nazw zdefiniowanych, użytkownik musi pamiętać, czy nazwa odnosi się do tablicy, czy do zakresu komórek. Nazwy możemy nadawać dowolne, ale używając ich, musimy pamiętać, co sobą reprezentują, gdyż w pewnych sytuacjach (np. jako źródła listy rozwijanej) musimy używać zakresów, a nie tablic.

Na poniższym rysunku (rysunek. 1.9) pokazano przykładowe dane źródłowe w zakresie komórek oraz efekt ich sortowania. Wyniki sortowania zostały wygenerowane za pomocą nazwy, którą nadano formule dynamicznej. Trzeci zakres widoczny na rysunku to również efekt użycia nazwy, ale w tym przypadku nazwa została nadana dynamicznemu zakresowi komórek, który został wygenerowany przez poprzednią formułę. Widok Menedżera nazw, w którym zdefiniowano opisane nazwy, został przedstawiony na rysunku 1.10.

Rysunek 1.9. Użycie nazw do przechowania formuły dynamicznej i zakresu dynamicznego

Rysunek 1.10. Widok Menedżera nazw ze zdefiniowanymi nazwami Wyniki_SWG i Wyniki_ZD

Przykładowe dane zostały posortowane formułą według drugiej kolumny w porządku rosnącym, a następnie według trzeciej kolumny w porządku malejącym.

Formuła dynamiczna otrzymała nazwę Wyniki_SWG. Oczekiwany efekt sortowania można uzyskać za pomocą formuły:

Alternatywnie można użyć dwukrotnie funkcji SORTUJ:

Należy zwrócić uwagę, że w tym drugim przypadku kolejność użycia funkcji SORTUJ musi być odwrócona.

W arkuszu wywołanie nazwy Wyniki_SWG umieszczono w komórce E4. Gdy wyniki formuły dynamicznej zostaną umieszczone w arkuszu, stają się one zakresem dynamicznym, do którego można się odwoływać za pomocą operatora #. Takiemu odwołaniu również można nadać nazwę, przy czym operator # może być objęty nazwą, ale może też pozostać poza nazwą.

W przykładzie użyto nazwy Wyniki_ZD. Jeśli zamierzamy kopiować formułę (lub nazwę) zawierającą odwołanie dynamiczne z operatorem #, należy pamiętać, że formuła musi odwoływać się do pierwszej komórki zakresu dynamicznego, w związku z tym najczęściej adres tej komórki powinien być absolutny (ze znakami $). Samo użycie operatora # nie zmienia interpretacji poprzedzającego go adresu.

Użycie operatora # powoduje, że Excel musi wyliczyć zakres rozlania. W związku z tym te funkcjonalności Excela, które nie wykonują obliczeń, nie będą również umiały zinterpretować tego operatora. Natomiast Menedżer nazw potrafi wykonywać obliczenia, w związku z tym nazwy w nim zdefiniowane są już na ogół zrozumiałe dla innych funkcjonalności.

Wykresy

Wykresy nie wykonują obliczeń przy ustalaniu zakresów danych. Oczekują gotowych dokładnie określonych zakresów lub stałych tablicowych. Nie zmieniło się to w zasadzie (z małym wyjątkiem, o którym za chwilę) wraz z wprowadzeniem tablic dynamicznych; tak było zawsze. Jeśli jeszcze przed wprowadzeniem tablic dynamicznych chcieliśmy użyć funkcji takich jak INDEKS lub PRZESUNIĘCIE do utworzenia zakresu dynamicznego, to musieliśmy zastosować nazwę. Nadal do zdefiniowania wykresu można użyć zarówno tablicy, jak i zakresu dynamicznego. W obu przypadkach jednak pośrednikiem musi być nazwa. Ponadto przy definiowaniu danych do wykresu nazwa ta musi być kwalifikowana nazwą arkusza lub nazwą skoroszytu (w zależności od zasięgu nazwy, z tym że jeśli wskażemy arkusz, a nazwa ma zasięg w całym skoroszycie, to Excel jest w stanie to sam skorygować, ale nie na odwrót). Podanie samej nazwy bez kwalifikatora spowoduje błąd (nawet jeśli nazwa nie odwołuje się w ogóle do danych z arkusza; wystarczy, że jest w nim zdefiniowana).

Stosunkowo niedawno wprowadzono aktualizację w Excelu 365 (nie jest dostępna w programie Excel 2021). Polega ona na tym, że jeśli pełny "zakres danych wykresu" odpowiada zakresowi dynamicznemu, to wykres aktualizuje się automatycznie przy zmianie tego zakresu. Musi to być jeden zakres dynamiczny (nagłówki kolumn mogą być poza tym zakresem dynamicznym). Jego adres jest zapisywany jako adres statyczny, ale aktualizacja działa.

Poniżej na rysunkach 1.11. i 1.12 pokazałem, jak można zdefiniować dane do wykresu. W tym przypadku wykres pokazuje punkty losowe, których liczbę Lpkt definiujemy w arkuszu.

Rysunek 1.11. Przykład definiowania danych do wykresu

Rysunek 1.12. Przykładowy wykres przedstawiający punkty losowe

Obrazy połączone

Obrazy połączone zachowują się podobnie do wykresów, nie próbują niczego obliczać, chcą tylko wyświetlić zakres, który im podano. W związku z tym użycie odwołania z # bezpośrednio w połączonym obrazie nie działa. Musimy ponownie skorzystać z Menedżera nazw.

Poniższa kopia ekranu (rysunek 1.13) przedstawia obraz połączony pokazujący nazwany zakres dynamiczny Szczyt, zdefiniowany jako

Rysunek 1.13. Obraz połączony z zakresem dynamicznym

W komórce E25 znajduje się formuła dynamiczna filtrująca źródłowy zakres danych w celu wyświetlenia najwyższych wyników punktowych, co najmniej o wartości podanej w komórce C24:

Formuła filtruje również nagłówek, wykorzystując właściwość Excela, która przy porównaniu uznaje wszystkie teksty za większe od liczb.

Gdyby jednak należało pokazać wartości poniżej granicy z C24, to trzeba zastosować inny sposób. Nagłówki można wyświetlić w komórce I25 jako zakres statyczny = A3:C3, a formułę w I26 już bez nagłówków:

Aby uzyskać obraz połączony, łączymy te dwa zakresy w jeden, definiując nazwę Dno:

W Excelu 365 i 2024 można ponadto wykorzystać nową funkcję STOS.PION.

Po ponownym obliczeniu nazwanego zakresu liczba wierszy i kolumn może się zwiększyć lub zmniejszyć. Obraz również wydłuża się lub zmniejsza, aby dopasować się do rozmiaru zakresu określonego przez nazwę.

Formatowanie warunkowe

Formatowanie warunkowe nie jest zgodne z tablicami dynamicznymi. Jeśli użyjemy odwołania z operatorem # do określenia zakresu formatu warunkowego, Excel przekonwertuje go na zakres statyczny. Podobnie było już poprzednio z zakresami nazwanymi.

Aby użyć formatowania warunkowego z tablicami dynamicznymi, musimy wybrać zakres statyczny, który jest większy niż nasz przewidywany zakres wynikowy. Będzie on sprawiać wrażenie dynamicznego, ale może wymagać korekty od czasu do czasu, gdy zakres dynamiczny rozszerzy się poza warunkowo sformatowane komórki.

Walidacja danych

Listy sprawdzania poprawności danych wykonują obliczenia, ale działają tylko z zakresami. Nie mogą używać tablic. W związku z tym lista sprawdzania poprawności danych:

Może korzystać bezpośrednio z systemu odwołań z #. Może używać nazwanego zakresu, który jest zdefiniowany z użyciem zakresu dynamicznego (z #). Nie może używać dynamicznej formuły tablicowej ani nazwy odnoszącej się do tablicy.

Dane do listy sprawdzania poprawności muszą mieć odpowiedni kształt, musi to być wektor, czyli pojedynczy wiersz lub kolumna.

Tabele danych

Ponieważ tablic i tabel wszelakich w Excelu dostatek, wyjaśniam, że tu mam na myśli tabele danych z kategorii Analizy warunkowej.

Dane/Analiza warunkowa/Tabela danych

Jest to funkcjonalność z bardzo długą historią w Excelu; jest czymś pośrednim między formułą tablicową a makroinstrukcją. Powtarza obliczenia według tego samego schematu dla różnych danych w dwu możliwych wariantach: z jedną zmienną niezależną (w tym przypadku może być wiele funkcji) lub z dwiema zmiennymi niezależnymi (w tym przypadku tylko jedna funkcja). Dla każdego zestawu danych może być obliczony jeden wynik. W kontekście nowych funkcji Excela można to porównać do działania funkcji MAKEARRAY (opis w rozdziale 4.).

Należy pamiętać, że dane bieżące (robocze) nie mogą być powiązane z danymi wzorcowymi. W razie takich powiązań otrzymamy błędne wyniki (bez komunikatu o błędzie).

Inicjacja tabeli danych polega na zaznaczeniu w arkuszu obszaru obejmującego dane i miejsce na wynik, wypełnieniu okienka dialogowego i zatwierdzeniu przez Enter. Procedura nie jest dynamiczna, jakkolwiek po zmianie wartości danych formuły przeliczają się automatycznie. Jednak po zmianie liczby danych trzeba powtórzyć procedurę inicjacji (kasując wcześniej poprzednie wyniki). Jeśli liczba danych zmienia się na skutek działania formuł dynamicznych, to można sobie zaplanować tabelę z zapasem, przewidując, jaka będzie maksymalna liczba danych, i wyświetlając puste teksty w razie ich braku.

Inny wariant to napisanie w VBA makra zdarzeniowego lub uruchamianego na życzenie, które wykona dla nas procedurę redefinicji tabeli danych. Tu przy okazji można zwrócić uwagę na niestandardowe zachowanie właściwości CurrentArray i HasArray. HasArray zwraca True w obszarze wyników tabeli danych i tylko z pozycji tych komórek można odczytać CurrentArray. Ale zakres CurrentArray obejmuje nie tylko wyniki, ale również przyległe dane tabeli danych.

Tabele przestawne

Mogłoby się wydawać, że przy definiowaniu zakresu źródłowego tabeli przestawnej można użyć zakresu dynamicznego z operatorem #. Jednak to tylko pierwsze wrażenie. Gdy tylko klikniemy OK, Excel konwertuje odwołanie dynamiczne na standardowy zakres statyczny i z niego tworzy tabelę przestawną. Możemy jednak wykorzystać nazwane zakresy odnoszące się do zakresu dynamicznego.

Pojawia się jednak dodatkowa trudność. Tabele przestawne przyjmują założenie, że pierwszy wiersz danych jest wierszem nagłówka. Powoduje to problem, ponieważ odwołania dynamiczne często nie zawierają nagłówka. Z podobnym problemem mieliśmy do czynienia przy omawianiu obrazów połączonych.

W obu tych przypadkach, zależnie od posiadanej wersji Excela, można wykorzystać operator zakresu : do utworzenia połączonego zakresu nagłówka i dynamicznego zakresu danych. Należy pamiętać, że użycie dwukropka powoduje połączenie zakresów w jeden obszar prostokątny, najmniejszy pokrywający zakresy składowe. W przypadku nagłówka wystarczy wskazanie jednej komórki. Definiując nazwę w Menadżerze nazw, możemy wykorzystać na przykład takie odwołania:

albo

pamiętając o kolejności operatorów. Kolejność zakresów jest bez znaczenia.

Użytkownicy Excela 2024 i 365 mają do dyspozycji jeszcze nową funkcję STOS.PION (opis w rozdziale 8.), która umożliwia łączenie kilku tablic (nie tylko dynamicznych) w jedną tablicę. W tym przypadku jednak mamy do czynienia z łączeniem tablic, a nie zakresów, więc musimy wskazać wszystkie dane do połączenia. W brakujące miejsca funkcja wpisze kod błędu #N/D. Zatem w naszym przykładzie, zakładając, że dane i nagłówki obejmują 3 kolumny, należałoby napisać:

W tym przypadku kolejność argumentów (zakresów lub tablic) ma znaczenie.

Obsługa formuł dynamicznych w VBA

Nowe wersje właściwości Range.Formula

Wraz z wprowadzeniem formuł dynamicznych w VBA pojawiły się 4 nowe wersje właściwości .Formula opatrzone dodatkowo cyfrą 2. Docelowo powinny one zastąpić poprzednie 5 wariantów (4 wersje Formula i FormulaArray). Formuły opisane tymi nowymi właściwościami są domyślnie dynamiczne, to znaczy same określają na podstawie swojej definicji, ile wartości i w jakim układzie (ile wierszy i ile kolumn) mają zwrócić jako wynik. Jest to zawsze obszar prostokątny. Formuła jest przypisana do jednej komórki. Jeśli wynik jest tablicą, to komórka z formułą jest w lewym górnym rogu tej tablicy. Komórki arkusza, w których ma być wyświetlony wynik formuły dynamicznej, muszą być puste, inaczej formuła zwraca błąd #ROZLANIE!

Rejestrując makro w nowym Excelu (2021+ lub 365), otrzymamy zapis z wykorzystaniem właściwości .Formula2R1C1. Jeśli formuła zwraca pojedynczy wynik, to właściwości z grupy Formula2 zachowują się tak samo jak odpowiednie właściwości z grupy Formula. Natomiast jeśli formuła zwraca wynik w postaci tablicy i chcemy odczytać właściwości .Formula2 w całym obszarze zajmowanym przez tę tablicę, to wynik niepusty otrzymamy tylko w pierwszej komórce, a w pozostałych wynikiem będzie pusty tekst. Natomiast gdy odczytamy właściwość .Value, to do każdej komórki będzie przypisana ta wartość, którą formuła wyświetla.

Formuły zwracane przez właściwość .Formula2 mogą zawierać dodatkowo (w porównaniu z użyciem .Formula) operator rzutowania (niejawnego przecięcia) @. Pojawia się on wszędzie tam, gdzie w formule użyto zakresu lub tablicy w miejscu, gdzie formuła oczekiwała pojedynczej wartości. Właściwość .Formula stosuje w takim przypadku zasadę rzutowania (ang. implicit intersection), czyli wybiera do obliczeń jeden element z zakresu lub tablicy. Pozostaje to jednak niejawne (to znaczy w formule w żaden sposób nie jest zaznaczone, że wykorzystano tylko jeden element z kilku dostępnych).

Jeśli taką formułę (zdefiniowaną za pomocą .Formula) odczytamy z użyciem .Formula2, to w zapisie pojawi się znak (lub znaki) @ wszędzie tam, gdzie zastosowano zasadę rzutowania. Gdybyśmy formułę zdefiniowali ponownie za pomocą .Formula2 już bez tych znaków @, stanie się ona formułą dynamiczną, obejmującą (jak tradycyjne formuły tablicowe) wszystkie wartości argumentów i wszystkie te wartości zostaną użyte w obliczeniach.

Zilustruję to prostym przykładem tabliczki mnożenia. Załóżmy, że w zakresach A2:A5 oraz C1:F1 mamy jakieś liczby. W komórce D4 definiujemy formułę mnożenia za pomocą właściwości .Formula.

Taka formuła zadziała zgodnie z zasadą rzutowania i z każdego zakresu wybierze jedną wartość, w tym przypadku A4 i D1, i zwróci ich iloczyn jako jeden wynik.

Gdybyśmy odczytali (w trybie bezpośrednim) przypisaną do tej komórki właściwość .Formula2, otrzymalibyśmy:

To samo zobaczymy również na pasku formuły w wersji Excela obsługującej formuły dynamiczne. Znak @ informuje, że z każdego z zakresów A2:A5 i C1:F1 została pobrana do obliczeń jedna wartość.

Jeżeli teraz zdefiniowalibyśmy formułę tak jak poprzednio, ale z wykorzystaniem właściwości .Formula2, to znaczy

w komórce D4 pojawiłby się wynik formuły dynamicznej rozlanej na 4 wiersze i 4 kolumny, przy czym komórka D4 byłaby w lewym górnym rogu tej tablicy. Do obliczeń wykorzystane będą wszystkie wartości z zakresów danych i mnożenie zostanie wykonane na zasadzie "każdy z każdym". Wynikiem będzie tablica złożona z 16 liczb. Należy również zwrócić uwagę na zmianę lokalizacji wyników. Przy zastosowaniu zasady rzutu prostokątnego w komórce D4 był wynik mnożenia A4*D1, obecnie, czyli przy zastosowaniu zasady sekwencji (ang. lifting), w komórce D4 będzie wynik mnożenia pierwszych elementów, tzn. A2*C1, a wynik A4*D1 odnajdziemy w komórce E6.

Czytelnikowi pozostawiam analizę, jak będzie wyglądał wynik, jeżeli z formuły usuniemy jeden operator @, a drugi pozostawimy.

Przy zapisywaniu pliku Excel weryfikuje formuły zdefiniowane za pomocą .Formula2 i zapisuje te formuły, które można tak zapisać, jako formuły zwykłe, a te, gdzie występuje taka potrzeba - jako formuły tablicowe. Dzięki temu plik zapisany w nowej wersji Excela może być również poprawnie otwarty w starszej wersji, bez obsługi formuł dynamicznych. Formuły dynamiczne są tam widoczne jako tablicowe.

Jeśli planujemy używać formuły tylko w nowej wersji Excela, powinno się używać właściwości z grupy .Formula2. Jeśli formuła ma być używana i w starej, i w nowej wersji, należy używać właściwości z grupy .Formula. Nowe wersje Excela obsługują zarówno stary, jak i nowy wariant. Formuły są automatycznie tłumaczone, więc wystarczy, że w programie umieścimy jedną z wersji.

Właściwości identyfikujące formuły i zakresy dynamiczne

Aby ułatwić orientację w charakterze formuł, w nowym Excelu dodano jeszcze kilka właściwości obiektu Range. Pierwsza z nich to .SavedAsArray. Jej składnia:

a = Range(zakres).SavedAsArray

Jeśli wszystkie komórki w zakresie należą do formuły dynamicznej lub tablicowej, właściwość zwraca True, jeśli żadna z komórek nie jest fragmentem takiej formuły - zwraca False, w przypadku mieszanym zwraca Null.

Kolejna właściwość to .HasSpill. Składnia i wynik analogiczne do .SavedAsArray. Jeśli zakres należy w całości do obszaru formuły dynamicznej, zwraca True.

Dwie następne właściwości zwracają obiekty Range. W związku z tym można ich używać z innymi właściwościami obiektu Range. Pierwsza z tej grupy, .SpillParent, zwraca komórkę, która jest źródłem formuły dynamicznej. Przykład użycia w oknie Immediate:

Komórka musi być pojedyncza i powinna znajdować się w obszarze formuły dynamicznej (można to sprawdzić dzięki .HasSpill). W wyniku otrzymujemy odwołanie do komórki, w której zaczyna się formuła dynamiczna.

Gdy już znamy miejsce początku formuły dynamicznej, można odczytać adres całego obszaru zajmowanego przez tę formułę. Służy do tego właściwość .SpillingToRange. Używamy jej podobnie jak .SpillParent, tylko jako komórkę wyjściową musimy podać komórkę, w której wpisana jest formuła dynamiczna (a nie którąkolwiek z jej obszaru). Wynikiem jest odwołanie do całego obszaru formuły.

Funkcja UDF odpowiadająca właściwości Range.CurrentRegion

Właściwość CurrentRegion w VBA służy do określenia obszaru danych przyległego do komórki wskazanej jako argument. Przez obszar przyległy rozumiemy najbliższe otoczenie komórki oddzielone od reszty arkusza co najmniej jednym pustym wierszem i pustą kolumną - jako puste traktuje się komórki bez zawartości (zawartością może być również formuła, nawet gdy zwraca pusty tekst), natomiast komórka pusta może być sformatowana. Granicą może być również granica arkusza. Ta właściwość jest wykorzystywana w sposób niejawny przez różne funkcjonalności w VBA, jeśli wskazujemy pojedynczą komórkę, na przykład przy filtrowaniu lub sortowaniu.

Właściwość ta mogłaby być również przydatna jako funkcja UDF do wykorzystania w arkuszu. Niestety, w arkuszu (przy wywołaniu z funkcji UDF) ta właściwość nie działa: nie zwraca błędu, ale jest "przezroczysta" - zwraca po prostu swój argument (tak samo zachowuje się właściwość SpecialCells).

Zastanawiałem się, dlaczego zablokowano dostęp do takiej ważnej funkcjonalności przez funkcje UDF. Oficjalnego wyjaśnienia nie ma, ale domyślam się, że może tu chodzić o zabezpieczenie przed możliwymi odwołaniami cyklicznymi. Należy pamiętać, aby tej funkcji nie wywoływać z komórki, która należy do obszaru przyległego do swojego argumentu. W takim przypadku mogłyby wystąpić odwołania cykliczne. Dotyczy to również funkcji naśladujących właściwość CurrentRegion.

Ze względu na przydatność tej funkcji pojawiły się pomysły, jakim kodem można ją zastąpić. Jedna z propozycji kodu pochodzi od Petera Schleifa [2]. Po wprowadzeniu drobnych modyfikacji kod funkcji można zapisać tak:

Argumentem funkcji powinna być pojedyncza komórka. Jeśli poda się adres zakresu komórek, wzięta pod uwagę będzie pierwsza komórka tego zakresu. Funkcja obiega komórkę wokół i sprawdza osobno każdą stronę pod kątem obecności pustych komórek. Wykorzystywana jest do tego funkcja WorksheetFunction.CountA. Sprawdzanie jest kontynuowane aż do osiągnięcia krawędzi arkusza lub "otoczki" złożonej z pustych komórek. Zdefiniowałem cztery funkcje pomocnicze, aby łatwiej określić rozmiary i położenie bieżącego zakresu podczas jego tworzenia.

Funkcja CurrentRegionUDF zwraca w wyniku odwołanie do zakresu (a nie adres w formie tekstu), więc wynik może być wprost użyty w wyrażeniach. W pewnym sensie funkcja ta spełnia podobną rolę jak operator #, ale w odniesieniu do dowolnej zawartości komórki, a nie tylko formuły dynamicznej. Poza tym w przypadku operatora # należy użyć adresu pierwszej komórki zakresu formuły dynamicznej, a w przypadku funkcji zdefiniowanej powyżej można podać adres dowolnej komórki. Trzeba jednak mieć świadomość, że w zależności od wyboru komórki początkowej można otrzymać różne wyniki. Szczególnie należy zwrócić uwagę, czy komórka startowa jest pusta, czy wypełniona.

Rozdział 2. Rozszerzona interpretacja pojęcia nazwy

Zasięg nazwy ograniczony do jednej funkcji - funkcja LET

Podstawy użycia nazw definiowanych w Excelu

Nazwy definiowane przez użytkownika pełniły i nadal pełnią ważną rolę, jeśli chodzi o ułatwienie człowiekowi zrozumienia logiki formuł. W językach programowania tę funkcję spełniają zmienne, natomiast w Excelu - właśnie nazwy. Tu dane przechowywane są w komórkach arkuszy, które stanowią rodzaj siatki. Standardowym wyróżnikiem każdej komórki jest jej adres, zapisywany najczęściej w stylu A1, czyli przez wskazanie litery oznaczającej kolumnę i liczby oznaczającej numer wiersza. Adres określa położenie danych w arkuszu, pozwala zatem sprawnie odszukać te dane lub odnaleźć miejsce, w którym mają być zapisane.

Jednak adres nie odnosi się w żaden sposób do znaczenia tych danych. Można powiedzieć, że adres jest wygodny dla komputera, ale nie dla człowieka, który ma go używać. Dla człowieka mniejsze znaczenie ma miejsce, w którym zapisana jest informacja, natomiast ważniejsza jest treść informacji i opis jej znaczenia. Załóżmy, że w komórce B5 wpisane jest nazwisko jakiejś osoby. Chcemy to nazwisko odnaleźć na liście nazwisk. Możemy posługiwać się adresem komórki, ale dla kogoś z zewnątrz, kto zobaczy formułę, nie będzie oczywiste, że B5 oznacza nazwisko. Natomiast jeśli tej komórce przypiszemy nazwę nazwisko i tej nazwy użyjemy w formule zamiast adresu, to dla człowieka zrozumienie sensu formuły będzie łatwiejsze. Ponadto modyfikując arkusz, możemy przenieść komórkę z nazwiskiem w inne miejsce - będzie ona miała teraz inny adres, ale zachowa tę samą nazwę. Powiązanie nazwy z adresem będzie teraz zadaniem dla komputera, a nie dla człowieka.

Taki jest w dużym uproszczeniu sens używania nazw definiowanych w arkuszach Excela. Nazwy możemy nadawać różnym obiektom w skoroszycie, takim jak pojedyncze komórki, zakresy komórek, stałe, formuły, a od niedawna również funkcje, o czym napiszę w dalszej części rozdziału. Również Excel sam generuje nazwy różnych obiektów, takich jak tabele strukturalne, tabele przestawne, obszary wykorzystywane przy filtrowaniu zaawansowanym, obszary wydruku itp. Tu użytkownik ma ograniczone możliwości ingerencji i w każdym przypadku trzeba sprawdzić, co i jak można zmienić, aby taka nazwa nie utraciła swej funkcjonalności.

Przegląd nazw i ich edycja są możliwe za pośrednictwem Menedżera nazw, który stanowi "centrum zarządzania" nazwami zdefiniowanymi w skoroszycie. Każda z nazw zdefiniowanych ma określony zasięg (ang. scope), który w polskiej wersji Excela został niefortunnie przetłumaczony jako "zakres". Znaczenie tych pojęć jest zbliżone, tylko że "zakres" jest używany w polskim Excelu jako odpowiednik angielskiego range, a to jest pojęcie dużo ważniejsze i częściej używane, niemniej są one zupełnie różne i nie powinny dzielić (w języku polskim) tej samej nazwy. Ja w tym opracowaniu będę używał terminu "zasięg".

Zasięg nazw w Menedżerze nazw obejmuje domyślnie cały skoroszyt, jeżeli określona nazwa jest definiowana po raz pierwszy. W momencie definiowania można jednak ograniczyć zasięg nazwy do jednego wskazanego arkusza. Później już raz ustalonego zasięgu nie można zmienić. Jeśli nazwa była już wcześniej zdefiniowana, program zaproponuje ustalenie domyślnego zasięgu w aktualnie aktywnym arkuszu.

Zasięg oznacza miejsce, w którym nazwa może być użyta wprost, bez kwalifikatora. Poza swoim zasięgiem nazwa może być również używana, ale tylko z poprzedzającym kwalifikatorem. W innym skoroszycie będzie to kwalifikator nazwy skoroszytu i ewentualnie arkusza, a w bieżącym skoroszycie kwalifikator nazwy arkusza, na przykład:

jeżeli nazwa nazwisko została zdefiniowana z zasięgiem tylko w Arkuszu2.

Problemem przy używaniu nazw jest konieczność zachowania ich unikatowości. Często posługujemy się uniwersalnymi nazwami symbolicznymi typu x, y oznaczającymi jakieś zmienne, których znaczenie zależy od kontekstu użycia. W arkuszu czy w skoroszycie można zdefiniować tylko jedną nazwę x. Jeśli są nam potrzebne kolejne, musimy już dodawać jakieś wyróżniki, np. xa, xb, pamiętając jednocześnie, aby nie było kolizji nazwy ze standardowym adresem komórki. Częściowym rozwiązaniem tego problemu było wprowadzenie tabel strukturalnych Excela, które mają swoje unikatowe nazwy (nadawane początkowo przez program, ale z możliwością modyfikacji przez użytkownika). W obrębie takiej tabeli można stosować nazwy kolumn, które muszą być unikatowe tylko w ramach jednej tabeli, a pomiędzy tabelami mogą się powtarzać. W ramach tej samej tabeli posługujemy się lokalnymi identyfikatorami kolumn [x], natomiast poza tabelą musimy wskazać nazwę całej tabeli, a następnie identyfikator kolumny, na przykład Tabela1[x].

Udostępnienie funkcji LET

Kolejnym krokiem twórców Excela było wprowadzenie funkcji LET. Jest ona dostępna od wersji 2021, w Microsoft 365 i w Excelu dla sieci Web. Ta funkcja pozwala na definiowanie nazw lokalnych (o zasięgu na poziomie formuły), a następnie używanie ich w wyrażeniu definiującym oczekiwany wynik obliczeń. Nazwa funkcji nawiązuje do angielskiej nazwy polecenia przypisania wartości, znanej z języka Basic. Jednak istotą jej funkcjonalności jest nie samo przypisanie, lecz użycie nazw lokalnych tylko w obrębie jednej formuły w celu uproszczenia zapisu i skrócenia czasu jej obliczania. Dzięki temu bardziej złożone formuły są łatwiejsze do odczytania, bo nie trzeba powtarzać identycznych fragmentów kodu.

Z polską nazwą funkcji były problemy. Przez pewien czas używana była nazwa ZEZWALAJ (wyjątkowo niefortunna), z której ostatecznie zrezygnowano, pozostając przy oryginalnej nazwie angielskiej. Ślady tej tymczasowej nazwy pozostały jednak gdzieniegdzie w dokumentacji i w komunikatach błędów. Główną zaletą nazwy angielskiej jest to, że jest krótka. Niestety też niewiele wyjaśnia.

Formuła funkcji LET przechowuje wyniki pośrednich obliczeń przypisane do lokalnych nazw. Aby użyć tej funkcji w programie Excel, należy zdefiniować pary nazw i skojarzonych z nimi formuł obliczeniowych (co najmniej jedną parę nazwa i wartość, maksymalnie do 126 par) oraz jedną formułę wynikową.

Składnia funkcji

=LET(nazwa1; wartość1; nazwa2; wartość2; ...; wynik)

Argumenty

nazwa1 - nazwa nadawana wartości1, wartość1 - wartość lub obliczenie przypisane do nazwy1, nazwa2; wartość2 - (opcjonalne) druga para nazwa i wartość, wynik - obliczenie lub nazwa wcześniej zdefiniowana i obliczona.

Po określeniu nazwy zmiennej można jej przypisać wartość statyczną lub wartość wynikającą z obliczeń. Formuła może wtedy odwoływać się do zmiennej po nazwie tyle razy, ile potrzeba, podczas gdy wartość zmiennej jest opisana tylko w jednym miejscu i jednorazowo obliczana.

Nazwy zmiennych i przypisane wartości występują w parach oddzielone średnikami (nazwa1; wartość1; nazwa2; wartość2 itd.). LET może obsłużyć do 126 par nazwa i wartość, ale wymagana jest tylko jedna taka para. Funkcja jest obliczana od lewej do prawej, w związku z tym definicje kolejnych nazw mogą zawierać odwołania do nazw zdefiniowanych wcześniej, a formuła wynik - do wszystkich zdefiniowanych nazw. Niedopuszczalne są odwołania rekurencyjne (do nazwy właśnie definiowanej). Poprawnie zdefiniowana funkcja ma nieparzystą liczbę argumentów (co najmniej 3).

Przykłady użycia

Najprostsze przypadki użycia funkcji LET z jedną lub dwiema zmiennymi:

Możliwe jest zainicjowanie zmiennej wartością pustą:

Każda zmienna zdefiniowana w funkcji LET ma zasięg w bieżącej funkcji LET od miejsca zdefiniowania (ściślej: od następnej pary nazwa i wartość) do ostatniego nawiasu. Zatem nazwy zdefiniowane wcześniej mogą być wykorzystane do definiowania następnych nazw:

= LET(x; 2; y; x + 1; x * y) // zwraca 6

Wymogi dotyczące nazw w funkcji LET

Nazwy muszą być podane wprost, bez cudzysłowów, nie mogą być wynikiem formuł. Muszą spełniać ogólne wymagania odnoszące się do nazw zdefiniowanych w Excelu: zaczynać się od litery lub znaku podkreślenia (nieoficjalnie może też być \), wewnątrz nazwy mogą ponadto wystąpić cyfry (oraz nieoficjalnie znak zapytania). Można używać znaków o kodach powyżej 128, choć może to generować kłopoty przy transferze pliku do innej wersji językowej. Są jednak pewne różnice. W funkcji LET nazwy nie mogą zawierać kropek, mogą natomiast mieć postać kolidującą z nieużywanym stylem adresowania (co pozostaje zakazane w przypadku nazw arkuszowych i skoroszytowych). Przykładowo: jeśli w skoroszycie jest używany styl adresowania A1, to wewnątrz funkcji LET można zdefiniować nazwy takie jak w, k, wk, r, c, rc itp.

Jeśli jednak w treści funkcji LET (dotyczy to także funkcji LAMBDA) używamy funkcji o nazwach wieloczłonowych (z kropkami), np. LICZBA.CAŁK, WARTOŚĆ.LICZBOWA, to nie można użyć jako nazwy zmiennej pierwszego członu użytej nazwy funkcji, np. 'liczba' (otrzymamy komunikat, że formuła jest nieprawidłowa). Można natomiast użyć nazwy funkcji, która jest jednoczłonowa, choć nie polecam tego z uwagi na czytelność zapisu. Zilustruję to przykładem. Możemy użyć zapisu z nazwą liczba:

= LET(liczba; 2; 1+ZAOKR(PI(); liczba))

Jeśli jednak użyjemy podobnego zapisu z funkcją LICZBA.CAŁK, to Excel zgłosi błąd składni. Trzeba zmienić nazwę liczba na przykład na licz:

= LET(licz; 2; 1+LICZBA.CAŁK(PI(); licz))

Ponowne użycie tych samych nazw

W obrębie funkcji LET nazwy muszą być unikatowe, ale można wykorzystywać również nazwy zdefiniowane na poziomie skoroszytu lub arkusza, z tym że jeżeli nazwa o takim samym brzmieniu zostanie zdefiniowana ponownie na poziomie funkcji LET, to nowa definicja "zasłoni" poprzednią od miejsca swego wystąpienia. Można jednak zawsze odwołać się do nazwy arkuszowej lub skoroszytowej przez zastosowanie kwalifikatora, np.

Flet.xlsx!x

Na tym tle można jednak zauważyć drobne "niedoróbki". Jeśli mamy zdefiniowaną w skoroszycie nazwę odnoszącą się do zakresu komórek i przy wprowadzaniu formuły zaznaczymy myszą ten zakres, to do formuły wpisana zostanie nazwa bez kwalifikatora, nawet gdyby ta nazwa była już ponownie zdefiniowana lokalnie. Również po podświetleniu na pasku formuły wyrażenia z nazwą program rozpoznaje nazwy zdefiniowane w skoroszycie, a nie lokalne. Dotyczy to też użycia klawisza F9 w trybie edycji.

Samo zadeklarowanie nazwy zmiennej nie inicjuje jej (inaczej niż w przypadku funkcji LAMBDA), to znaczy nie jest poprawna np. taka konstrukcja:

= LET(x; x+1; 2*x)

Wyrażenie zwróci błąd #NAZWA?, chyba że nazwa x była zdefiniowana na poziomie arkusza lub skoroszytu. Można natomiast zainicjować zmienną w jednej funkcji LET, a potem użyć ponownie tej samej nazwy w następnym wywołaniu:

= LET(x; 3; LET(x; 2*x; x+1)) // zwraca 7

Nie polecam tego typu konstrukcji, bo są niezbyt czytelne, ale zwracam uwagę, że są formalnie poprawne.

Lepiej zapisać to tak:

= LET(x; 3; y; 2*x; y+1) // zwraca 7

Wynikiem funkcji LET jest obliczona formuła wynik lub wcześniej obliczona zmienna. Wynik zawsze pojawia się jako ostatni argument funkcji (może być tylko jeden wynik, ale może mieć postać tablicy).

Porada programisty [3]: W czasie debugowania warto nadać nazwę również końcowemu wynikowi, aby można było łatwo zmienić końcowe obliczenie z wynik na x lub dowolny inny wynik pośredni w celu sprawdzenia wartości.

Warto zwrócić uwagę, że wszystkie funkcje z grupy LET, LAMBDA i pomocniczych zwracają wyniki liczbowe w postaci niesformatowanej (w formacie Ogólne), niezależnie od tego, jakie dane są przetwarzane. Należy to mieć na względzie szczególnie przy przetwarzaniu dat i czasu.

Zalety i wady funkcji LET

Funkcja LET dobrze współpracuje z innymi funkcjami Excela, szczególnie z nowymi, generującymi wyniki dynamiczne. Zmienne (nazwy) definiowane przez użytkownika mogą odnosić się zarówno do pojedynczych wartości, jak i do zakresów lub tablic. Jednak w odniesieniu do funkcji, których dotyczą ograniczenia w wartościach lub typie argumentów, sama funkcja LET niczego nie zmienia.

Użycie funkcji LET poprawia czytelność wyrażeń i w pewnych przypadkach umożliwia ich skrócenie (wizualnie nie zawsze, jeśli wybieramy długie opisowe nazwy). Czas wykonania funkcji skraca się, ale tylko w przypadku bardzo złożonych wyrażeń. W przypadku prostszych, z jedną lub dwiema nazwami skrócenie czasu wykonania jest raczej symboliczne, natomiast czytelność jest zwykle lepsza. Niektórzy specjaliści zalecają stosowanie klawiszy Alt+Enter do podziału zapisu formuły na wiersze, tak by każda definicja nazwy była oddzielona od innych.

Dzięki użyciu nazw łatwiej wprowadzać poprawki w formułach i łatwiej zapanować nad złożoną strukturą formuł (np. liczne pary zagnieżdżonych nawiasów).

Funkcja LET nie propaguje błędów bez potrzeby. Jeśli zdefiniowane nazwy dają w wyniku obliczenia błędy, ale te nazwy nie są potrzebne do wyznaczenia końcowego wyniku, to funkcja zwraca poprawny wynik. Nie oznacza to pomijania obliczeń zbędnych argumentów (jak w przypadku funkcji JEŻELI czy WYBIERZ, co nazywa się w języku angielskim lazy evaluation), lecz ignorowanie błędów, które nie mają wpływu na wynik.

Z dydaktycznego punktu widzenia funkcja LET ułatwia zrozumienie warunków użycia adresów względnych w nazwach. Gdy definiowanie nazwy przebiega w Menedżerze nazw i jest oderwane od miejsca użycia formuły, studentom trudno jest zrozumieć, dlaczego w momencie definiowania formuły komórka aktywna musi być tam, gdzie formuła będzie później użyta. W funkcji LET jest to powiązane i dzięki temu łatwiejsze do zrozumienia.

Kluczowe korzyści ze stosowania funkcji LET

W podsumowaniu można wskazać, że funkcja LET zapewnia trzy istotne korzyści:

Przejrzystość - nazywanie zmiennych używanych w formule może znacznie ułatwić czytanie i zrozumienie złożonej formuły. Uproszczenie - nazywanie i definiowanie zmiennych w jednym miejscu pozwala wyeliminować nadmiarowość i błędy wynikające z wpisywania tego samego kodu w kilku miejscach. Wydajność - eliminacja nadmiarowego kodu oznacza skrócenie ogólnego czasu obliczeń, ponieważ jednakowe obliczenia są wykonywane tylko raz.

Definiowanie funkcji obliczeniowych przez użytkownika z możliwością nadawania im własnych nazw - funkcja LAMBDA

Geneza

Funkcja LAMBDA, znana również jako funkcja anonimowa, wchodzi w skład języków programowania od dziesięcioleci. Pojęcie to pochodzi z rachunku lambda, formalnego systemu logiki matematycznej i informatyki. Początek dały prace Alonzo Churcha nad wynalezieniem rachunku lambda w 1936 roku (przed pojawieniem się komputerów elektronicznych). W niektórych językach programowania funkcje anonimowe faktycznie są tworzone przez użycie słowa kluczowego lambda. Funkcje anonimowe wchodzą w skład wielu języków programowania. Po raz pierwszy zostały użyte w języku LISP w 1958 roku.

Udostępnienie funkcji LAMBDA użytkownikom Excela zostało ogłoszone pod koniec roku 2020, a od lutego 2022 są one dostępne dla wszystkich subskrybentów platformy Microsoft 365. Zatem mogą z nich korzystać również posiadacze Excela 2024.

Funkcja LAMBDA - składnia i podstawowe wymogi

Funkcja LAMBDA umożliwia definiowanie własnych niestandardowych funkcji, które po nadaniu nazwy można ponownie wykorzystać w skoroszycie. Nowa funkcja może być dostępna w całym skoroszycie (przy definiowaniu można ograniczyć jej zasięg do jednego arkusza, jak w przypadku zwykłych nazw) i jest wywoływana tak samo jak własne funkcje programu Excel.

Można przekształcić często używane formuły w funkcje, eliminując konieczność ich kopiowania i wklejania (co zwiększa ryzyko błędu), a także dodawać nowe funkcje do biblioteki własnych funkcji programu Excel (niestety trudno je odróżnić, gdyż są oznaczone takimi samymi ikonami; dlatego warto przyjąć jakąś konsekwentną koncepcję nazw). Funkcja LAMBDA nie wymaga języka VBA, makr ani kodu JavaScript, więc teoretycznie mogą z niej skorzystać również osoby, które nie mają doświadczenia w programowaniu. Jednak moim zdaniem zapisanie definicji tych funkcji jest mniej intuicyjne niż w VBA (ale to tylko moje subiektywne zdanie). Natomiast niewątpliwą zaletą jest brak problemów na styku język polski - język angielski. Skoroszyt może być zapisany w standardowym formacie .xlsx.

Składnia funkcji

=LAMBDA(parametr; ...; obliczenie)(argument; ...)

Argumenty

parametr - zmienna symboliczna reprezentująca wartość argumentu funkcji.

W definicji funkcji może być kilka parametrów (maksymalnie 253), ale może też nie być ich wcale. Nazwy parametrów muszą spełniać ogólne wymogi dotyczące nazw stosowanych w Excelu z wyjątkami, jak w przypadku funkcji LET.

obliczenie - formuła obliczeniowa funkcji. Jest tylko jeden taki argument i musi być na ostatnim miejscu listy za parametrami. Funkcja LAMBDA zwraca zawsze tylko jeden wynik (ale może on być tablicą). argument - wartość, która ma być podstawiona w miejscu odpowiadającego mu parametru. Liczba argumentów musi odpowiadać liczbie parametrów, z zastrzeżeniem, że niektóre parametry mogą być zadeklarowane jako opcjonalne, wtedy argumentów może być mniej. Argumentami mogą być zmienne dowolnego typu, również inne LAMBDY.

Podkreślona część formuły może być zastąpiona nazwą przy użyciu Menedżera nazw. Przy definiowaniu nazwy funkcji LAMBDA pomijamy argumenty. Podajemy je dopiero przy wywołaniu.

Specyfika i zalety funkcji LAMBDA

Funkcja LAMBDA poszerza i usprawnia stosowanie formuł nazwanych w programie Excel. Dotychczas nazwa, która odnosiła się do formuły, musiała zwracać jednoznacznie określoną pojedynczą wartość lub tablicę wartości. Teraz, wykorzystując funkcję LAMBDA, możemy modyfikować wartości argumentów przekazywanych do formuły i w ten sposób pośrednio modyfikować zwracaną wartość.

Raz zdefiniowana i nazwana funkcja LAMBDA może być używana wielokrotnie w skoroszycie, podobnie jak inne nazwy. Definiując funkcję LAMBDA, można wykorzystywać operacje i funkcje standardowe dostępne w programie, jak również funkcje niestandardowe, takie jak funkcje UDF (zapisane w języku VBA), czy funkcje makr XLM (funkcje dawnego systemu makr Excela 4.0) oraz inne funkcje LAMBDA.

W definicji LAMBDY można nawet umieścić wywołanie tej samej funkcji LAMBDA (ale powinna mieć wcześniej nadaną nazwę). Będzie to wówczas tak zwane wywołanie rekurencyjne (używane są zamiennie terminy rekurencja albo rekursja, ja w tym tekście będę stosował ten pierwszy). Jest to nowość, gdyż tradycyjne nazwy definiowane nie mogły być wywoływane rekurencyjnie. Możliwe było tylko wykonywanie zamierzonych obliczeń cyklicznych, ale to trochę co innego, choć może być wykorzystane w podobnym celu. Rekurencyjne wywoływanie LAMBD zostanie omówione w następnym rozdziale.

Jedną z istotnych zalet nazwanych funkcji LAMBDA jest to, że wszystkie składowe procesu obliczeniowego są zapisane w jednym miejscu. Oznacza to, że przy wprowadzaniu poprawek lub zmian wystarczy zmodyfikować jedno miejsce w kodzie, a zmiany będą automatycznie uwzględnione we wszystkich wystąpieniach tej funkcji LAMBDA w skoroszycie.

Tworzenie funkcji LAMBDA

W programowaniu komputerowym termin "lambda" oznacza anonimową funkcję lub wyrażenie. Funkcja anonimowa to funkcja zdefiniowana bez nadanej nazwy. Proces definiowania funkcji LAMBDA w Excelu zaczynamy z reguły od utworzenia ogólnej (nienazwanej) formuły.

Następnie, po przetestowaniu, definicja funkcji jest przenoszona do Menedżera nazw w celu przypisania nazwy, którą można później wywoływać w dowolnym miejscu skoroszytu.

Tworzenie i używanie funkcji LAMBDA przebiega w czterech podstawowych fazach:

Sprawdzenie poprawności planowanych obliczeń przy użyciu standardowej formuły. Zapis i przetestowanie anonimowej wersji formuły LAMBDA. Przeniesienie treści definicji formuły LAMBDA (bez argumentów) do Menedżera nazw i nadanie nazwy. Wywołanie nowej funkcji w arkuszu przez podanie jej nazwy i listy argumentów w nawiasach.

Aby zilustrować proces definiowania LAMBDY, zacznijmy od bardzo prostej formuły mnożenia dwóch liczb:

W arkuszu Excela zwykle użylibyśmy odwołań do komórek w następujący sposób:

Temu wyrażeniu można nadać nazwę w Menedżerze nazw, ale ta nazwa odnosiłaby się tylko do mnożenia zawartości konkretnych, wskazanych wcześniej komórek. Można również zdefiniować osobne nazwy x i y, przypisując je do komórek B5 i C5, a następnie użyć tych nazw w formule mnożenia. Ale zawsze musimy mieć najpierw zdefiniowane argumenty, aby móc użyć nazwy odnoszącej się do wykonywanego działania.

Tymczasem LAMBDA pozwoli rozszerzyć zastosowanie formuły na dowolne, wskazane później argumenty. Jeśli stosowaliśmy już funkcję LET, możemy zacząć od zapisania obliczenia za pomocą tej funkcji:

Wyrażeniu z funkcją LET również możemy nadać nazwę, ale to nie różni się istotnie od klasycznego użycia nazwy. Ale teraz połączymy koncepcję nazwy ze zmodyfikowanym zapisem funkcji. W ten sposób przejdziemy do utworzenia w arkuszu formuły LAMBDA.

Zaczynamy od wersji anonimowej. Gdy mamy zapisaną funkcję LET, wystarczy zamienić nazwę LET na LAMBDA, a wartości argumentów przenieść na koniec i umieścić je w dodatkowych nawiasach w takiej samej kolejności.

Jeżeli nie mamy zapisanej funkcji LET, należy najpierw rozważyć, czy formuła wymaga danych wejściowych (argumentów). Jeśli tak, to należy w definicji przewidzieć odpowiednią liczbę parametrów (nazw zmiennych). W tym przypadku formuła wymaga podania wartości dwóch argumentów x i y.

Zapis formuły funkcji zaczynamy od słowa LAMBDA i dodajemy wymagane parametry (nazwy symboliczne), z którymi przy wywołaniu będą skojarzone argumenty podane przez użytkownika:

Następnie musimy dodać formułę obliczenia, x * y:

Gdybyśmy zatwierdzili wzór (naciskając Enter) w tym momencie, otrzymalibyśmy błąd #OBL!. Dzieje się tak, ponieważ formuła nie zawiera jeszcze wartości argumentów (np. odwołań do komórek), które są niezbędne do wykonania obliczenia. Aby przetestować formułę, musimy uzupełnić zapis o wartości argumentów. Służy do tego specjalna składnia z argumentami na końcu definicji funkcji w dodatkowej parze nawiasów:

Ta składnia jest specyficzna dla funkcji LAMBDA. Liczba i kolejność argumentów muszą być zgodne z liczbą parametrów wymienionych w definicji funkcji (nie można przypisywać argumentów przez nazwy parametrów, jak w VBA). Umożliwia to testowanie formuły bezpośrednio w arkuszu przed nadaniem nazwy funkcji LAMBDA.

Jeśli funkcja LAMBDA zostanie zdefiniowana bez parametrów, to i tak przy wywołaniu należy po nazwie funkcji umieścić parę nawiasów (to odróżnia ją od zwykłej nazwy).

Możemy już teraz nazwać funkcję LAMBDA za pomocą Menedżera nazw. Najpierw skopiujemy formułę, bez umieszczonych na końcu argumentów w nawiasach. Następnie otwieramy Menedżera nazw (np. za pomocą skrótu Ctrl+F3) i klikamy Nowy (rysunek 2.1). Można również z karty Formuły wybrać polecenie Definiuj nazwę.

Rysunek 2.1. Definiowanie nazwy funkcji LAMBDA

W oknie dialogowym Nowa nazwa wprowadzamy nazwę RAZY, pozostawiamy Zakres (zasięg) ustawiony na Skoroszyt i wklejamy skopiowaną formułę do obszaru Odwołuje się do.

Wskazówka: do pola Odwołuje się do można przejść, używając klawisza tabulatora.

Opcjonalnie można wypełnić również pole komentarza. Treść tego komentarza będzie widoczna jako podpowiedź przy wywołaniu funkcji.

Formuła musi zaczynać się od znaku równości (=). Teraz gdy formuła LAMBDA ma już nazwę, może być używana w skoroszycie jak każda inna funkcja.

Nowa funkcja niestandardowa zwraca taki sam wynik jak dwie wcześniejsze formuły.

Widok Menedżera nazw po zdefiniowaniu funkcji LAMBDA ilustruje rysunek 2.2.

Rysunek 2.2. Funkcja LAMBDA o nazwie RAZY zdefiniowana w Menedżerze nazw

Choć formuła była pierwotnie przewidziana do użycia do pojedynczych liczb, to typy danych nie są jawnie określone, więc formuły można używać do wszystkich typów danych, dla których określona jest w Excelu operacja mnożenia, na przykład do obszarów komórek czy stałych tablicowych.

Wykorzystane w funkcji LAMBDA rozszerzenie koncepcji nazwy może wydawać się niewielkie, ale ma ono liczne istotne konsekwencje. Do tej pory nazwa mogła być przypisywana tylko do czegoś, co miało już wartość lub miejsce w arkuszu (odwołanie). Teraz nazwę można nadać również fragmentowi formuły, reprezentującemu obliczenie, które jednak samodzielnie nie ma żadnej wartości. Musi jednak odpowiadać wymogom składni LAMBDY, nie może to być zatem dowolny tekst.

Ten sposób jest do pewnego stopnia konkurencyjny w stosunku do budowania formuł z fragmentów tekstu, a następnie przekształcania ich w działające formuły za pomocą funkcji SZACUJ (albo Evaluate w VBA) lub przez przypisanie do właściwości .FormulaLocal komórki.

Niestety wymogi składni powodują przy konstrukcji bardziej złożonych wyrażeń konieczność wielokrotnego powtarzania słów LAMBDA i LET, co zmniejsza czytelność formuł i zwiększa ich długość. Można temu częściowo zaradzić, wprowadzając osobne nazwy fragmentów wyrażeń. Uproszczeniu formuł służy też wprowadzona stosunkowo niedawno koncepcja eta lambd (nieco dalej wyjaśnię, o co tu chodzi).

Przykład 2.1 - policz słowa

W tym przykładzie utworzymy funkcję LAMBDA do liczenia słów. Excel nie ma funkcji do tego celu, ale można policzyć słowa w komórce za pomocą formuły opartej na funkcjach DŁ i PODSTAW w następujący sposób:

Formuła ta porównuje długość wpisu w komórce z długością tego wpisu po usunięciu spacji (zamianie spacji na puste teksty). Do wyniku dodaje 1, gdyż liczba słów jest o 1 większa od liczby rozdzielających je spacji.

Jednak gdy komórka jest pusta (nie ma żadnych słów), formuła podaje błędny wynik 1, gdyż nie odróżnia tego przypadku od obecności jednego słowa. Rozwiążemy ten problem później.

Inny problem może powstać, gdy słowa nie są rozdzielone spacją, lecz innym separatorem, na przykład przecinkiem, średnikiem, kropką, nawiasami itp. Jeśli taka sytuacja jest przewidywana, trzeba wcześniej zamienić wszystkie potencjalne separatory na spacje, usunąć nadmiarowe spacje i dopiero wtedy zastosować naszą funkcję LAMBDA.

Ta formuła wymaga tylko jednego argumentu, czyli tekstu zawierającego słowa. W naszej funkcji LAMBDA nazwiemy ten argument tekst. Oto formuła zapisana w formie anonimowej funkcji LAMBDA:

Zauważmy, że tekst pojawia się jako pierwszy argument, a obliczenia są drugim i zarazem ostatnim argumentem. Aby przetestować w arkuszu poprawność powyższej formuły, należy za nawiasem kończącym definicję funkcji dopisać wartość argumentu lub odwołanie do komórki z tą wartością (w nawiasach):

Wyniki testowej formuły LAMBDA są takie same jak pierwotnej formuły, więc następnym krokiem jest zdefiniowanie i nazwanie nowej formuły za pomocą Menedżera nazw, jak wyjaśniono wcześniej. Nazwiemy tę formułę LiczSłowa. Formuła użyta w Menedżerze nazw jest taka sama jak powyżej (wersja bez argumentu).

Rozwiązanie problemu pustej komórki

Jak wspomniałem powyżej, nasza formuła zwraca nieprawidłowy wynik 1, gdy komórka jest pusta. Ten problem można rozwiązać, zastępując +1 poniższym kodem:

Kod ten sprawdza długość tekstu (bez nadmiarowych spacji) i dodaje 1 tylko wtedy, gdy tekst ma długość większą od zera. Aby zaktualizować istniejącą nazwaną formułę LAMBDA, ponownie musimy użyć Menedżera nazw:

Otwieramy Menedżera nazw. Wybieramy nazwę LiczSłowa i klikamy Edytuj. Zastępujemy formułę w polu Odwołuje się do następującym wzorem:

Formułę można jeszcze ulepszyć, wykorzystując funkcję LET do eliminacji powtórzonego obliczenia:

Po zamknięciu Menedżera nazw funkcja LiczSłowa działa poprawnie również na pustych komórkach, jak pokazano na rysunku 2.3.

Aktualizacja kodu w Menedżerze nazw obejmuje wszystkie wystąpienia formuły LiczSłowa. Jest to istotna zaleta niestandardowych funkcji tworzonych za pomocą LAMBDA - treścią formuł można zarządzać w jednym miejscu.

Rysunek 2.3. Przykład zastosowania funkcji LAMBDA do liczenia słów w komórce

Konwencje nazewnictwa funkcji LAMBDA

Istnieje wiele różnych sposobów nazywania funkcji LAMBDA, w tym (określenia wzięte z języka angielskiego): ALLCAPS, CamelCase, lowerCamelCase i all_lower_case [3]. Inną powszechną praktyką jest rozpoczynanie nazwy funkcji od podkreślenia, np. _FUNCTION. Nie polecam tego, ponieważ nazwy zaczynające się od podkreślenia często odnoszą się do nazw ukrytych.

Funkcje w bibliotece LAMBDA w większości zaczynają się od L_ i używają ALLCAPS, na przykład L_LINSPACE, z dwóch głównych powodów:

Wszystkie wbudowane funkcje w programie Excel są pisane wielkimi literami, a nazwane funkcje w Arkuszach Google muszą być pisane wielkimi literami. Prefiks L_ pomaga odróżnić funkcje niestandardowe od wbudowanych funkcji arkusza kalkulacyjnego. Ponadto po wpisaniu =L_ w komórce program Excel wyświetli całą listę funkcji niestandardowych zaczynających się od litery L.

Warto też pamiętać, że Excel dopuszcza stosowanie nazw funkcji LAMBDA identycznych jak nazwy funkcji wbudowanych. Przy korzystaniu z funkcji autouzupełniania są one wyświetlane obok siebie na liście. Aby odróżnić takie funkcje, można zdefiniować komentarz do nazwy, który będzie wyświetlany obok nazwy funkcji podczas dokonywania wyboru z listy. Jednak w takim przypadku Excel zawsze używa funkcji standardowej, chyba że z kontekstu użycia wynika, że chodzi o lambdę (sama nazwa bez argumentów).

Konwencje stosowane do nazw parametrów

Przy wyborze nazw parametrów staramy się pogodzić dwa sprzeczne wymogi: opisowość i zwięzłość, przy czym ważniejszy wydaje się jednak zrozumiały opis. Oto polecane zasady [3]:

Używaj nazw opisowych, taki kod wymaga mniej komentarzy. Litery tylko małe lub przynajmniej małe na początku: lowerCamelCase. Nazwy parametrów LAMBDA są wyświetlane w podpowiedzi funkcji, więc nazwa powinna być sugestywna. Parametry jednoliterowe są dopuszczalne w przypadku indeksów i typowego użycia funkcji, takich jak (acc, v) dla (akumulator, wartość bieżąca). m, n to zazwyczaj rozmiary (m dla wierszy, n dla kolumn). i, j, k są zazwyczaj indeksami, które mogą być zwiększane. Nieprawidłowe są nazwy parametrów takie jak a1, v1, seq1, bo oznaczają adresy komórek. Zamiast tego użyj podkreśleń lub przeliteruj: tab_1, wektor_1.

Komentarze w nazwanych funkcjach

Chociaż Zaawansowany Edytor Formuł (Advanced Formula Environment) - piszę o nim w rozdziale 5. - umożliwia komentowanie kodu w treści funkcji (przy użyciu // i /* */), jednak te komentarze są usuwane, zanim dodatek utworzy nazwaną funkcję, która jest wyświetlana w Menedżerze nazw.

Istnieją dwa główne sposoby dołączania komentarzy, które nie znikną, gdy nazwane funkcje będą używane w innych skoroszytach.

Metoda 1. Dodanie opisu za pomocą pola Komentarz w Menedżerze nazw. Ten opis pojawi się jako etykietka narzędzia.

W przypadku korzystania z modułu AFE opis jest dodawany automatycznie przy użyciu specjalnej składni komentarza bezpośrednio przed definicją LAMBDY, na przykład:

Metoda 2. Można użyć LET w funkcji LAMBDA i dodać komentarze przy użyciu nazwanych parametrów, takich jak rem_1, rem_2 itp. Funkcje biblioteki LAMBDA używają tej techniki do definiowania adresu URL strony dokumentacji każdej funkcji.

Współdziałanie funkcji LET i LAMBDA

Funkcje LET i LAMBDA można łączyć w jednym wyrażeniu. Parametr obliczenie funkcji LAMBDA może przyjąć postać funkcji LET, jeżeli wymaga złożonych obliczeń. Możliwa jest również sytuacja odwrotna, kiedy nazwa definiowana wewnątrz funkcji LET jest funkcją LAMBDA.

Mogą tu wystąpić dwa przypadki - nazwa może być nadana:

wywołaniu LAMBDY z argumentami, samej funkcji, do której argumenty zostaną dołączone później.

Pokażę to na prostych przykładach. Przypadek pierwszy:

Jest to trochę "zakręcony" wzór na długość obwodu okręgu opisanego na trójkącie prostokątnym, którego przyprostokątne mają długości x i y. Pod symboliczne zmienne a i b w definicji LAMBDY podstawiono wartości x i y zdefiniowane w funkcji LET.

Przypadek drugi:

Tu nazwę fm nadano nie konkretnej wartości, wynikającej z obliczenia, lecz całej funkcji LAMBDA i dopiero dalej, w części obliczeniowej, dołączono do funkcji argumenty, aby sfinalizować obliczenie.

Ten przypadek ma też specjalne zastosowanie przy rekurencyjnym wywołaniu funkcji LAMBDA, co omówię w jednym z następnych podrozdziałów. Tam podam też odpowiedni przykład.

Użycie nazwy zmiennej w deklaracyjnej części funkcji LAMBDA inicjuje ją, co oznacza, że można jej użyć w części obliczeniowej. W tej części można użyć tej samej nazwy zmiennej do jej modyfikacji, na przykład:

Kopiowanie LAMBDY do innych skoroszytów

Nazwane funkcje LAMBDA są zdefiniowane i dostępne w bieżącym skoroszycie. Jak jest w przypadku wszystkich nazw, ich zasięg można ograniczyć do wybranego arkusza. Jeśli chcielibyśmy udostępnić nazwaną funkcję LAMBDA w innym skoroszycie, to można skopiować komórkę zawierającą wywołanie tej funkcji do wybranego skoroszytu. Wówczas skopiuje się również definicja użytej nazwy. Jeśli LAMBDA miała zasięg ograniczony do arkusza, a formułę skopiujemy do innego arkusza, zostanie utworzona analogiczna definicja z zasięgiem w nowym arkuszu. Formuły można później usunąć - nazwy pozostaną.

Można również utworzyć nowy pusty arkusz w skoroszycie, w którym są zapisane nazwy, i skopiować ten arkusz lub przenieść do nowego skoroszytu. Wraz z nim skopiują się również zdefiniowane w nim nazwy (wszystkie - nie tylko funkcje LAMBDA). Sam arkusz można później usunąć. W podobny sposób można utworzyć skoroszyt - szablon, który będzie otwierany automatycznie przy otwieraniu Excela lub tworzeniu nowego skoroszytu (Ctrl+N).

Należy jednak uważać przy kilkukrotnym kopiowaniu nazw w ten sposób. Jeśli nazwy będą się powtarzać, nowe kopie będą miały zasięg tylko w nowym arkuszu i można je łatwo usunąć wraz z tym arkuszem - inaczej zrobimy sobie bałagan w Menedżerze nazw.

Uwaga na adresy kwalifikowane

Przy przenoszeniu definicji LAMBDY z arkusza do Menedżera nazw należy zwrócić uwagę na adresy komórek, jeżeli te występują w definicji. Zazwyczaj w arkuszu używamy adresów bez kwalifikatora arkusza. Odnoszą się one wówczas do arkusza bieżącego. Gdy kopiujemy taką formułę do Menedżera nazw, dopisywany jest automatycznie kwalifikator arkusza aktywnego. Skutkuje to tym, że gdy nazwana funkcja LAMBDA jest wywoływana w innym arkuszu, użyty adres odnosi się do tego konkretnego arkusza, który został wcześniej zapisany. Jeśli intencją autora formuły było, by adres odnosił się do arkusza bieżącego, należy ręcznie skorygować wzór, usuwając nazwę arkusza z odwołania, a pozostawiając tylko wykrzyknik (!). Nie da się tego zrobić wcześniej w arkuszu, dopiero w Menedżerze nazw.

Użycie innej funkcji jako argumentu funkcji LAMBDA

Zasady ogólne

Argumentami funkcji LAMBDA mogą być nie tylko wartości, ale również inne funkcje LAMBDA. Schemat takiego użycia jest dość prosty. Musimy tylko wiedzieć, ile argumentów należy przekazać do wywoływanej funkcji wewnętrznej. Nazwę funkcji należy skojarzyć z argumentem lub listą argumentów umieszczonych w nawiasach. Jeśli byłby to jeden argument, można użyć na przykład zapisu:

Ta LAMBDA ma użyć funkcji f, jeżeli argument x jest dodatni, a funkcji g w przeciwnym razie.

Alternatywnie można zapisać:

Wywołując taką funkcję, trzeba podać 3 argumenty: dwie nazwy funkcji wewnętrznych i wartość argumentu x. Gdyby funkcje f i g wymagały różnej liczby argumentów, to dopuszczalny byłby tylko drugi wariant zapisu z osobno wymienionymi argumentami.

Należy pamiętać, że nazwy oznaczające funkcje mogą występować tylko jako samodzielne argumenty, nie można wykonywać na nich żadnych operacji arytmetycznych ani relacyjnych. Można jedynie tworzyć z nich stosy za pomocą funkcji STOS.PION i STOS.POZ, o czym piszę w rozdziale 8.

Aby jakaś funkcja mogła być użyta jako argument funkcji LAMBDA, sama musi być LAMBDĄ. Każda taka funkcja musi być zadeklarowana z określoną liczbą parametrów (lub bez parametrów). Liczba parametrów nie może być dowolna, nie istnieje coś podobnego do tablicy ParamArray z VBA.

Jeśli chcielibyśmy użyć takiej funkcji (na przykład zdefiniowanej w VBA), należy ją wpierw "opakować" w LAMBDĘ i dopiero w takiej postaci można jej dalej używać. Trochę przypomina to sytuację, gdy chcemy wykorzystać w arkuszu jakąś funkcję wewnętrzną VBA, na przykład Round. Wówczas również musimy najpierw zdefiniować funkcję UDF, której nadajemy własną nazwę, i dopiero wówczas, pośrednio, możemy tę funkcję wykorzystywać.

Gdy mamy na przykład funkcję MojaFunkcja z dwoma argumentami x i y, to możemy ją zadeklarować jako LAMBDĘ:

Wpisujemy to do Menedżera nazw i nadajemy funkcji nazwę, np. MojaLambda.

Gdy to wykonamy, można już traktować tę funkcję jako LAMBDĘ, czyli na przykład możemy używać w wyrażeniach samej nazwy funkcji bez argumentów.

Do niedawna tej procedurze podlegać musiały również funkcje standardowe Excela. Od września 2024 udostępniono tzw. eta lambdy (piszę o nich więcej w następnym podrozdziale), dzięki czemu funkcje standardowe używane w zwykły sposób są od razu domyślnie traktowane jak LAMBDY i nie wymagają indywidualnej deklaracji. Procedura ta dotyczy nadal funkcji używanych w zestawach, funkcji UDF oraz funkcji dawnych makr XLM.

Używanie nazw funkcji standardowych jako LAMBD - eta lambdy

Zaraz po wprowadzeniu do Excela funkcji LAMBDA każda taka funkcja wymagała deklaracji w Menedżerze nazw. Po jakimś czasie twórcy programu postanowili ułatwić życie sobie i użytkownikom i zrezygnować z konieczności deklarowania jako LAMBD funkcji standardowych. Od tej pory wszystkie funkcje standardowe mają już "wbudowany" status LAMBD.

Określa się to angielskim terminem eta reduced lambda albo w skrócie eta lambda. Funkcje te mogą być używane jako argumenty innych funkcji LAMBDA bezpośrednio, bez tego dodatkowego "opakowania". Wprowadzenie eta lambd zbiegło się w czasie z udostępnieniem dwóch ważnych funkcji GRUPUJ.WEDŁUG (ang. nazwa GROUPBY) i PRZESTAWIAJ.WEDŁUG (ang. nazwa PIVOTBY), o których będzie mowa w rozdziale 10. Te funkcje korzystają właśnie z eta lambd jako swoich argumentów. Jest to rozwiązanie dużo bardziej czytelne dla użytkownika od stosowanego wcześniej np. w funkcjach SUMY.CZĘŚCIOWE czy AGREGUJ numerowania funkcji pomocniczych.

Eta lambdy upraszczają zapis formuł funkcji pomocniczych LAMBDA, takich jak BYCOL czy REDUCE, i mogą być wykorzystywane również przy tworzeniu własnych funkcji niestandardowych.

Przeanalizujmy przykład użycia funkcji trygonometrycznych SIN i COS jako funkcji LAMBDA. Przed wprowadzeniem eta lambd należało im nadać nowe własne nazwy za pomocą Menedżera nazw. Nie było formalnie zakazane użycie takiej samej nazwy, ale później na liście podpowiedzi (IntelliSense) byłyby dwie identyczne nazwy. W takim przypadku przy użyciu standardowym NAZWA_FUNKCJI(argumenty) wybrana by była funkcja standardowa, natomiast przy użyciu samej nazwy funkcji bez argumentów - funkcja LAMBDA.

Wygodniej w takim przypadku do każdej definiowanej nazwy funkcji dopisać przedrostek L_. Obecnie wobec istnienia eta lambd nie jest to potrzebne, ale może się przydać, gdy będziemy definiować funkcje niestandardowe na bazie standardowych. Możemy na przykład uprościć wywołanie jakiejś funkcji, która używa wielu argumentów, ustawiając część z nich na stałe wartości.

Jeśli na przykład chcielibyśmy obliczyć wartość wielomianu funkcyjnego, który byłby sumą n składników:

Możemy zdefiniować ogólny wielomian postaci:

Odpowiada mu definicja funkcji LAMBDA:

Po zdefiniowaniu takiej nazwy możemy obliczyć wartość wielomianu dowolnego stopnia dla dowolnej funkcji f i dla dowolnej wartości argumentu x. Na przykład przy obliczaniu wartości wielomianu piątego stopnia z funkcją COS dla wartości x = 0,35 wywołanie funkcji LW może wyglądać tak:

Gdybyśmy chcieli użyć jakiejś funkcji niestandardowej, na przykład sumy SIN + COS, to można również zdefiniować ją ad hoc przy wywołaniu funkcji LW: