Jak korzystać z funkcji Excel LAMBDA -

Spisie treści

Podsumowanie

Funkcja Excel LAMBDA umożliwia tworzenie niestandardowych funkcji, które mogą być ponownie używane w całym skoroszycie, bez języka VBA i makr.

Cel, powód

Utwórz funkcję niestandardową

Wartość zwracana

Zgodnie z formułą

Składnia

= LAMBDA (parametr,…, obliczenie)

Argumenty

  • parametr - wartość wejściowa funkcji.
  • obliczenie - obliczenie, które ma zostać wykonane jako wynik funkcji. Musi być ostatnim argumentem.

Wersja

Excel 365

Uwagi dotyczące użytkowania

W programowaniu komputerowym LAMBDA odnosi się do anonimowej funkcji lub wyrażenia. Funkcja anonimowa to funkcja zdefiniowana bez nazwy. W programie Excel funkcja LAMBDA umożliwia definiowanie i hermetyzację określonej funkcji formuły, podobnie jak funkcja programu Excel. Po zdefiniowaniu funkcji LAMBDA można nazwać ją i ponownie wykorzystać w innym miejscu w skoroszycie. Innymi słowy, funkcja LAMBDA to sposób na tworzenie funkcji niestandardowych.

Jedną z kluczowych zalet niestandardowej funkcji LAMBDA jest to, że logika zawarta w formule istnieje tylko w jednym miejscu. Oznacza to, że istnieje tylko jedna kopia kodu do zaktualizowania podczas naprawiania problemów lub aktualizacji funkcjonalności, a zmiany będą automatycznie propagowane do wszystkich wystąpień funkcji LAMBDA w skoroszycie. Funkcja LAMBDA nie wymaga języka VBA ani makr.

Przykład 1 | Przykład 2 | Przykład 3

Tworzenie funkcji LAMBDA

Funkcje LAMBDA są zwykle tworzone i debugowane na pasku formuły w arkuszu, a następnie przenoszone do menedżera nazw w celu przypisania nazwy, której można używać w dowolnym miejscu skoroszytu.

Istnieją cztery podstawowe kroki tworzenia i używania niestandardowej formuły opartej na funkcji LAMBDA:

  1. Sprawdź logikę, której będziesz używać ze standardową formułą
  2. Utwórz i przetestuj ogólną (nienazwaną) wersję formuły LAMBDA
  3. Nazwij i zdefiniuj formułę LAMBDA za pomocą menedżera nazw
  4. Przetestuj nową funkcję niestandardową przy użyciu zdefiniowanej nazwy

Poniższe przykłady omawiają te kroki bardziej szczegółowo.

Przykład 1

Aby zilustrować, jak działa LAMBDA, zacznijmy od bardzo prostej formuły:

=x*y // multiple x and y

W programie Excel ta formuła używałaby zwykle odwołań do komórek w następujący sposób:

=B5*C5 // with cell references

Jak widać, formuła działa dobrze, więc jesteśmy gotowi przejść do tworzenia ogólnej formuły LAMBDA (wersja bez nazwy). Pierwszą rzeczą do rozważenia jest to, czy formuła wymaga danych wejściowych (parametrów). W tym przypadku odpowiedź brzmi „tak” - formuła wymaga podania wartości dla x i wartości dla y. Po ustaleniu tego zaczynamy od funkcji LAMBDA i dodajemy wymagane parametry do wprowadzenia danych przez użytkownika:

=LAMBDA(x,y // begin with input parameters

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

=LAMBDA(x,y,x*y)

Jeśli wpiszesz formułę w tym momencie, otrzymasz #CALC! błąd. Dzieje się tak, ponieważ formuła nie ma wartości wejściowych do pracy, ponieważ nie ma już żadnych odwołań do komórek. Aby przetestować formułę, musimy użyć specjalnej składni, takiej jak ta:

=LAMBDA(x,y,x*y)(B5,C5) // testing syntax

Ta składnia, w której parametry są podawane na końcu funkcji LAMBDA w oddzielnym zestawie nawiasów, jest unikalna dla funkcji LAMBDA. Pozwala to na przetestowanie formuły bezpośrednio w arkuszu, przed nadaniem nazwy LAMBDA. Na poniższym ekranie widać, że ogólna funkcja LAMBDA w F5 zwraca dokładnie ten sam wynik, co oryginalna formuła w E5:

Jesteśmy teraz gotowi do nazwania funkcji LAMBDA za pomocą Menedżera nazw. Najpierw wybierz formułę, * nie uwzględniając * parametrów testowych na końcu. Następnie otwórz Menedżera nazw za pomocą skrótu Control + F3 i kliknij Nowy.

W oknie dialogowym Nowa nazwa wprowadź nazwę „XBYY”, pozostaw zakres ustawiony na skoroszyt i wklej skopiowaną formułę do obszaru wprowadzania „Odwołuje się do”.

Upewnij się, że formuła 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. Na ekranie poniżej wzór w G5, skopiowany w dół, to:

Nowa funkcja niestandardowa zwraca ten sam wynik, co pozostałe dwie formuły.

Przykład 2

W tym przykładzie przekonwertujemy wzór do obliczania objętości kuli na niestandardową funkcję LAMBDA. Ogólny wzór Excela do obliczania objętości kuli to:

=4/3*PI()*A1^3 // volume of sphere

gdzie A1 reprezentuje promień. Poniższy ekran przedstawia tę formułę w akcji:

Zauważ, że ta formuła wymaga tylko jednego wejścia (promienia) do obliczenia objętości, więc nasza funkcja LAMBDA będzie potrzebować tylko jednego parametru (r), który pojawi się jako pierwszy argument. Oto wzór przekonwertowany na LAMBDA:

=LAMBDA(r,4/3*PI()*r^3) // generic lambda

W arkuszu roboczym zastąpiliśmy oryginalną formułę ogólną wersją LAMBDA. Zauważ, że używamy składni testowej, która pozwala nam podłączyć B5 do promienia:

Wyniki z ogólnej formuły LAMBDA są dokładnie takie same jak w oryginalnej formule, więc następnym krokiem jest zdefiniowanie i nazwanie tej formuły LAMBDA za pomocą Menedżera nazw, jak wyjaśniono powyżej. Nazwa używana dla funkcji LAMBDA może być dowolną poprawną nazwą programu Excel. W tym przypadku nazwiemy formułę „SphereVolume”.

W arkuszu roboczym zastąpiliśmy ogólną (nienazwaną) formułę LAMBDA nazwaną wersją LAMBDA i wprowadziliśmy B5 zamiast r. Zwróć uwagę, że wyniki zwrócone przez niestandardową funkcję SphereVolume są dokładnie takie same, jak poprzednie wyniki.

Przykład 3

W tym przykładzie utworzymy funkcję LAMBDA do liczenia słów. Excel nie ma funkcji do tego celu, ale możesz liczyć słowa za pomocą komórki z niestandardową formułą opartą na funkcjach LEN i SUBSTITUTE:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

Przeczytaj szczegółowe wyjaśnienie tutaj. Oto formuła w działaniu w arkuszu:

Zwróć uwagę, że otrzymujemy nieprawidłową liczbę 1, gdy formuła ma pustą komórkę (B10). Poniżej zajmiemy się tym problemem.

Ta formuła wymaga tylko jednego elementu wejściowego, którym jest tekst zawierający słowa. W naszej funkcji LAMBDA nazwiemy ten argument tekstem. Oto wzór przekonwertowany na LAMBDA:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)

Zauważ, że „tekst” pojawia się jako pierwszy argument, a obliczenie jest drugim i ostatnim argumentem. Na poniższym ekranie zastąpiliśmy oryginalną formułę ogólną wersją LAMBDA. Zauważ, że używamy składni testowej, która pozwala nam podłączyć B5 do tekstu:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)(B5)

Wyniki z ogólnej formuły LAMBDA są takie same jak w oryginalnej formule, więc następnym krokiem jest zdefiniowanie i nazwanie tej formuły LAMBDA za pomocą Menedżera nazw, jak wyjaśniono wcześniej. Nazwijmy tę formułę „CountWords”.

Poniżej zastąpiliśmy ogólną (nienazwaną) formułę LAMBDA nazwaną wersją LAMBDA i wprowadziliśmy B5 dla tekstu. Zauważ, że otrzymujemy dokładnie takie same wyniki.

Formuła używana w Menedżerze nazw do definiowania słów kluczowych jest taka sama, jak powyżej, bez składni testowej:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)

Naprawianie problemu pustej komórki

Jak wspomniano powyżej, powyższa formuła zwraca nieprawidłową liczbę 1, gdy komórka jest pusta. Ten problem można rozwiązać, zastępując +1 poniższym kodem:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)

Pełne wyjaśnienie tutaj. Aby zaktualizować istniejącą nazwaną formułę LAMDA, ponownie musimy użyć Menedżera nazw:

  1. Otwórz Menedżera nazw
  2. Wybierz nazwę „CountWords” i kliknij „Edytuj”
  3. Zastąp kod „Odwołuje się do” następującą formułą:
=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+(LEN(TRIM(text))>0))

Po zamknięciu Menedżera nazw CountWords działa poprawnie na pustych komórkach, jak widać poniżej:

Uwaga: po jednokrotnej aktualizacji kodu w Menedżerze nazw wszystkie wystąpienia formuły CountWords są aktualizowane jednocześnie. Jest to kluczowa zaleta funkcji niestandardowych utworzonych za pomocą aktualizacji formuły LAMBDA, którą można zarządzać w jednym miejscu.

Interesujące artykuły...