Spis treści
W tej chwili Microsoft Excel ma prawie pięćset funkcji arkusza roboczego dostępnych w oknie Kreatora funkcji – przycisk fx na pasku formuły. To bardzo przyzwoity zestaw, ale mimo to prawie każdy użytkownik prędzej czy później spotyka się z sytuacją, w której ta lista nie zawiera potrzebnej mu funkcji – po prostu dlatego, że nie ma jej w Excelu.
Do tej pory jedynym sposobem rozwiązania tego problemu były makra, czyli napisanie własnej funkcji zdefiniowanej przez użytkownika (UDF = User Defined Function) w Visual Basic, co wymaga odpowiednich umiejętności programistycznych i czasami wcale nie jest łatwe. Jednak wraz z najnowszymi aktualizacjami Office 365 sytuacja zmieniła się na lepsze – do Excela dodano specjalną funkcję „wrappera” LAMBDA. Z jego pomocą zadanie tworzenia własnych funkcji jest teraz rozwiązywane łatwo i pięknie.
Przyjrzyjmy się zasadzie jego użycia w poniższym przykładzie.
Jak najprawdopodobniej wiesz, Excel ma kilka funkcji analizy dat, które pozwalają określić liczbę dnia, miesiąca, tygodnia i roku dla danej daty. Ale z jakiegoś powodu nie ma funkcji określającej numer ćwiartki, która też jest często potrzebna, prawda? Naprawmy to niedociągnięcie i twórzmy za pomocą LAMBDA własną nową funkcję, aby rozwiązać ten problem.
Krok 1. Napisz formułę
Zacznijmy od tego, że ręcznie w zwykły sposób napiszemy w komórce arkusza formułę, która obliczy to, czego potrzebujemy. W przypadku numeru ćwiartki można to zrobić np. tak:
Krok 2. Zawijanie w LAMBDA i testowanie
Teraz nadszedł czas, aby zastosować nową funkcję LAMBDA i otoczyć nią naszą formułę. Składnia funkcji jest następująca:
=LAMBDA(Zmienna1; Zmienna2; … ZmiennaN ; Wyrażenie)
gdzie nazwy co najmniej jednej zmiennej są wymienione jako pierwsze, a ostatnim argumentem jest zawsze formuła lub wyrażenie obliczeniowe, które ich używa. Nazwy zmiennych nie powinny wyglądać jak adresy komórek i nie powinny zawierać kropek.
W naszym przypadku będzie tylko jedna zmienna – data, dla której obliczamy numer kwartału. Nazwijmy dla niego zmienną, powiedzmy d. Następnie zawijanie naszej formuły w funkcję LAMBDA i zastępując adres oryginalnej komórki A2 fikcyjną nazwą zmiennej, otrzymujemy:
Należy pamiętać, że po takiej transformacji nasza formuła (w rzeczywistości poprawna!) zaczęła generować błąd, ponieważ teraz oryginalna data z komórki A2 nie jest do niej przenoszona. Aby przetestować i uzyskać pewność, możesz przekazać do niego argumenty, dodając je po funkcji LAMBDA w nawiasach:
Krok 3. Utwórz nazwę
Teraz czas na łatwą i przyjemną część. Otwieramy Menedżer nazw Zakładka formuła (Formuły — Menedżer nazw) i utwórz nową nazwę za pomocą przycisku Stwórz (Tworzyć). Wymyśl i wprowadź nazwę dla naszej przyszłej funkcji (na przykład Nomkwartala) i w terenie Połączyć (Odniesienie) dokładnie skopiuj z paska formuły i wklej naszą funkcję LAMBDA, tylko bez ostatniego argumentu (A2):
Wszystko. Po kliknięciu OK utworzonej funkcji można użyć w dowolnej komórce na dowolnym arkuszu tego skoroszytu:
Użyj w innych książkach
LAMBDA i tablice dynamiczne
Funkcje niestandardowe utworzone za pomocą funkcji LAMBDA skutecznie wspierają pracę z nowymi tablicami dynamicznymi i ich funkcjami (FILTER, UNIK, GRADE) dodane do programu Microsoft Excel w 2020 r.
Załóżmy, że chcemy utworzyć nową funkcję zdefiniowaną przez użytkownika, która porównuje dwie listy i zwraca różnicę między nimi – te elementy z pierwszej listy, których nie ma na drugiej. Praca życia, prawda? Wcześniej używali do tego funkcji a la VPR (WYSZUKAJ.PIONOWO)lub tabel przestawnych lub zapytań dodatku Power Query. Teraz możesz zrobić z jedną formułą:
W wersji angielskiej będzie to:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Tutaj funkcja COUNTIF zlicza liczbę wystąpień każdego elementu pierwszej listy w drugiej, a następnie funkcji FILTER wybiera tylko tych z nich, którzy nie mieli tych zdarzeń. Owijając tę strukturę w LAMBDA i tworzenie na jego podstawie nazwanego zakresu z nazwą np. WYSZUKAJ DYSTRYBUCJĘ – dostaniemy wygodną funkcję, która zwraca wynik porównania dwóch list w postaci tablicy dynamicznej:
Jeśli dane źródłowe nie są zwykłymi, ale „inteligentnymi” tabelami, nasza funkcja również poradzi sobie bez problemów:
Innym przykładem jest dynamiczne dzielenie tekstu poprzez konwersję go do formatu XML, a następnie parsowanie go komórka po komórce przy użyciu funkcji FILTER.XML, którą ostatnio analizowaliśmy. Aby nie za każdym razem ręcznie odtwarzać tej skomplikowanej formuły, łatwiej będzie zawinąć ją w LAMBDA i na jej podstawie stworzyć zakres dynamiczny, czyli nową kompaktową i wygodną funkcję, nazywając ją np. RAZDTEXT:
Pierwszym argumentem tej funkcji będzie komórka z tekstem źródłowym, a drugim znakiem separatora, a wynik zwróci w postaci poziomej tablicy dynamicznej. Kod funkcji będzie następujący:
=LAMBDA(t;d; TRANSPONUJ(FILTR.XML("
Lista przykładów jest nieskończona – w każdej sytuacji, w której często trzeba wpisywać ten sam długi i nieporęczny wzór, funkcja LAMBDA znacznie ułatwi życie.
Rekurencyjne wyliczanie znaków
Wszystkie poprzednie przykłady pokazywały tylko jedną, najbardziej oczywistą stronę funkcji LAMBDA – jej użycie jako „opakowania” do pakowania w nią długich formuł i upraszczania ich wprowadzania. W rzeczywistości LAMBDA ma inną, znacznie głębszą stronę, która zamienia ją w prawie pełnoprawny język programowania.
Faktem jest, że fundamentalnie ważną cechą funkcji LAMBDA jest możliwość ich implementacji w rekurencja – logika obliczeń, gdy w procesie obliczania funkcja wywołuje samą siebie. Z przyzwyczajenia może to zabrzmieć przerażająco, ale w programowaniu rekurencja jest powszechną rzeczą. Nawet w makrach w Visual Basic można to zaimplementować, a teraz, jak widać, trafiło do Excela. Spróbujmy zrozumieć tę technikę na praktycznym przykładzie.
Załóżmy, że chcemy utworzyć funkcję zdefiniowaną przez użytkownika, która usunie wszystkie podane znaki z tekstu źródłowego. Przydatności takiej funkcji, jak sądzę, nie trzeba udowadniać – bardzo wygodnie byłoby z jej pomocą wyczyścić zaśmiecone dane wejściowe, prawda?
Jednak w porównaniu z poprzednimi, nierekurencyjnymi przykładami czekają nas dwie trudności.
- Będziemy musieli wymyślić nazwę naszej funkcji, zanim zaczniemy pisać jej kod, ponieważ w niej ta nazwa będzie już używana do wywołania samej funkcji.
- Wpisanie takiej funkcji rekurencyjnej do komórki i debugowanie jej przez podanie argumentów w nawiasach po LAMBDA (tak jak to zrobiliśmy wcześniej) nie zadziała. Będziesz musiał stworzyć funkcję natychmiast „od zera” w Menedżer nazw (Nazwisko menedżera).
Nazwijmy naszą funkcję powiedzmy CLEAN i chcielibyśmy, aby miała dwa argumenty – tekst do oczyszczenia oraz listę wykluczonych znaków jako ciąg tekstowy:
Stwórzmy, tak jak wcześniej, na karcie formuła в Menedżer nazw nazwany zakres, nazwij go WYCZYŚĆ i wpisz w polu łodzie następująca konstrukcja:
=LAMBDA(t;d;JEŻELI(d=””;t;KASUJ(ZAMIANA(t;LEWO(d);””));MID(d;2;255))))
Tutaj zmienna t jest oryginalnym tekstem do wyczyszczenia, a d jest listą znaków do usunięcia.
Wszystko działa tak:
iteracja 1
Fragment SUBSTITUTE(t;LEFT(d);””), jak można się domyślić, zastępuje pierwszy znak z lewego znaku ze zbioru d do usunięcia w tekście źródłowym t pustym ciągiem tekstowym, czyli usuwa znak „ A". Jako wynik pośredni otrzymujemy:
Vsh zkz n 125 rubli.
iteracja 2
Następnie funkcja wywołuje siebie i jako wejście (pierwszy argument) otrzymuje to, co pozostało po wyczyszczeniu w poprzednim kroku, a drugim argumentem jest ciąg wykluczonych znaków rozpoczynający się nie od pierwszego, ale od drugiego znaku, tj. „BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ”, bez początkowego „A” – wykonuje to funkcja MID. Tak jak poprzednio, funkcja pobiera pierwszy znak od lewej z pozostałych (B) i zastępuje go w przekazanym jej tekście (Zkz n 125 rubli) pustym ciągiem – otrzymujemy jako wynik pośredni:
125 ru.
iteracja 3
Funkcja wywołuje się ponownie, otrzymując jako pierwszy argument to, co pozostało z tekstu do wyczyszczenia w poprzedniej iteracji (Bsh zkz n 125 ru.), A jako drugi argument zestaw wykluczonych znaków skrócony o jeszcze jeden znak do po lewej, tj. „VGDEEGZIKLMNOPRSTUFHTSCHSHSHSCHHYYYYUYA.” bez początkowego „B”. Następnie ponownie pobiera pierwszy znak od lewej (B) z tego zbioru i usuwa go z tekstu – otrzymujemy:
sh zkz n 125 ru.
I tak dalej – mam nadzieję, że wpadłeś na pomysł. Przy każdej iteracji lista znaków do usunięcia zostanie obcięta po lewej stronie, a następny znak z zestawu będziemy wyszukiwać i zastępować pustką.
Gdy skończą się wszystkie znaki, będziemy musieli wyjść z pętli – tę rolę pełni po prostu funkcja IF (JEŚLI), w którą opakowany jest nasz projekt. Jeśli nie ma już znaków do usunięcia (d=""), to funkcja nie powinna już wywoływać samej siebie, ale po prostu zwracać tekst do wyczyszczenia (zmienna t) w jego ostatecznej postaci.
Rekurencyjna iteracja komórek
Podobnie można zaimplementować cykliczne wyliczanie komórek w danym zakresie. Załóżmy, że chcemy utworzyć funkcję lambda o nazwie LISTA ZAMIENNIKÓW hurtowej wymiany fragmentów w tekście źródłowym według podanej listy referencyjnej. Wynik powinien wyglądać tak:
Tych. na naszej funkcji LISTA ZAMIENNIKÓW będą trzy argumenty:
- komórka z tekstem do przetworzenia (adres źródłowy)
- pierwsza komórka kolumny z wartościami do wyszukania z wyszukiwania
- pierwsza komórka kolumny z wartościami zastępczymi z wyszukiwania
Funkcja powinna iść od góry do dołu w katalogu i kolejno zastępować wszystkie opcje z lewej kolumny Znaleźć do odpowiednich opcji z prawej kolumny namiastka. Możesz to zaimplementować za pomocą następującej rekurencyjnej funkcji lambda:
Przesunięcie w dół w każdej iteracji jest realizowane przez standardową funkcję Excela SPRZEDAŻ (ZRÓWNOWAŻYĆ), który w tym przypadku ma trzy argumenty – pierwotny zakres, przesunięcie wiersza (1) i przesunięcie kolumny (0).
Otóż, gdy tylko dojdziemy do końca katalogu (n = „”), musimy zakończyć rekurencję – przestajemy się wywoływać i wyświetlamy, co nagromadziło się po wszystkich zamianach w zmiennej tekstu źródłowego t.
To wszystko. Żadnych skomplikowanych makr ani zapytań Power Query — całe zadanie rozwiązuje jedna funkcja.
- Jak korzystać z nowych dynamicznych funkcji tablicowych programu Excel: FILTER, SORT, UNIC
- Zastępowanie i czyszczenie tekstu za pomocą funkcji SUBSTITUTE
- Tworzenie makr i funkcji zdefiniowanych przez użytkownika (UDF) w VBA