Excel oferuje tysiące funkcji, ale większość użytkowników ogranicza się do podstawowych, takich jak SUMA i ŚREDNIA. Chociaż funkcje te wystarczają do prostych zadań, istnieją trzy funkcje, które obsługują bardziej złożone scenariusze przy znacznie mniejszym nakładzie pracy. Funkcje SEKWENCJA, LET i LAMBDA nie są tak powszechnie używane, ale rozwiązują konkretne problemy wymagające niewygodnych obejść lub długich formuł, które są trudne w utrzymaniu.
Korzystając z tych funkcji, możesz tworzyć dynamiczne, autonomiczne rozwiązania, które aktualizują się automatycznie, zamiast tworzyć wiele kolumn pomocniczych lub kopiować formuły do dziesiątek komórek. Niezależnie od tego, czy generujesz dane sekwencyjne, zarządzasz złożonymi obliczeniami, czy tworzysz niestandardowe funkcje wielokrotnego użytku, funkcje te należą do najprzydatniejszych. Funkcje programu Excel, które mogą zaoszczędzić Ci mnóstwo pracy.
Szybkie linki
4. Funkcja SEQUENCE: automatyczne generowanie danych
Utwórz dynamiczne sekwencje liczb i dat
Funkcja SEQUENCE tworzy tablice numerów seryjnych bez konieczności ręcznego wpisywania każdej wartości. Niezależnie od tego, czy potrzebujesz listy identyfikatorów pracowników, numerów faktur, czy zakresów dat, ta funkcja bezproblemowo sobie z nimi poradzi.
Zasada jest prosta i przejrzysta:
=SEKWENCJA(wiersze; [kolumny]; [początek]; [krok])
Przeanalizujmy parametry:
- wydziwianie: Określa liczbę liczb, które mają być wyświetlane pionowo.
- kolumny: Kontroluje rozprzestrzenianie poziome - pozostaw puste dla jednej kolumny.
- rozpocząć: Określa numer początkowy, wartością domyślną jest 1.
- krok: Określa odstęp między liczbami, wartością domyślną jest również 1.
Mając zbiór danych sprzedażowych, funkcja SEQUENCE okazuje się przydatna do generowania numerów referencyjnych. Na przykład, poniższa formuła generuje liczby od 1 do 32.
=SEKWENCJA(32)
Podobnie, jeśli chcesz zacząć od 1001, możesz użyć:
=SEKWENCJA(32, 1, 1001)
Funkcja ta przydaje się również w przypadku sekwencji dat. Poniższa formuła wygeneruje dwanaście kolejnych dat, począwszy od 1 stycznia. Jest to lepsze rozwiązanie niż ręczne wprowadzanie dat w raportach miesięcznych lub harmonogramach projektów.
=SEKWENCJA(12; 1; DATA(2025; 1; 1), 1)
Można również utworzyć wyłącznie dni robocze, łącząc funkcje SEKWENCJA i LICZBY. Inne DATA w Excelu, takich jak WORKDAY, w przypadku bardziej zaawansowanych scenariuszy planowania.
Duże tablice SEKWENCJI mogą spowalniać arkusze kalkulacyjne. Unikaj generowania więcej niż 10,000 XNUMX wartości naraz, chyba że jest to absolutnie konieczne. Jeśli potrzebujesz dużych zestawów danych, rozważ podzielenie ich na mniejsze części lub skorzystanie z zewnętrznych źródeł danych.
3. Funkcja LET sprawia, że złożone formuły są łatwiejsze w obsłudze.
Wyeliminuj powtarzające się obliczenia i popraw czytelność.
LET przypisuje nazwy wartościom w formule. Eliminuje to powtarzające się obliczenia i ułatwia pracę. Zamiast wpisywać to samo wyrażenie wielokrotnie, można je zdefiniować raz i odwołać się do niego po nazwie.
Budowa zdania jest zgodna z tym schematem:
=LET(nazwa1, wartość1, [nazwa2, wartość2, ...], obliczenie)
Możesz zdefiniować wiele zmiennych, dodając więcej par nazwa-wartość. Obliczenia ostatecznie wykorzystują te nazwane zmienne do wygenerowania wyniku.
Mając zbiór danych sprzedażowych, załóżmy, że obliczasz prowizję przedstawiciela handlowego wraz z premiami. Bez LET napisałbyś:
=IF(G2*0.05>500, G2*0.05*1.1, G2*0.05)
Obliczenie prowizji B2*0.05 pojawia się dwukrotnie. Z LET jest to jeszcze bardziej przejrzyste:
=LET(prowizja, G2*0.05, JEŻELI(prowizja>500, prowizja*1.1, prowizja))
Wykonuje te same obliczenia, ale „prowizję” ustala się raz na początku. Wystarczy zmienić stawkę prowizji w jednym miejscu.
W przypadku złożonej analizy marży zysku, LET okazuje się bardziej użyteczny. Poniższy przykład jasno definiuje każdy składnik.
=LET(przychód, G2, koszty, L2, marża, (przychód-koszty)/przychód, JEŻELI(marża>0.3, "Wysoka", JEŻELI(marża>0.15, "Średnia", "Niska"))))
Ten wzór oblicza marżę zysku w procentach, a następnie klasyfikuje ją jako wysoką (powyżej 30%), średnią (15–30%) lub niską (poniżej 15%). Każdy składnik ma jasną nazwę, co ułatwia zrozumienie logiki.
Metoda ta pozwala na redukcję złożoności wzoru o połowę. Ułatwia późniejsze korygowanie i modyfikowanie arkuszy kalkulacyjnych.
2. Funkcja LAMBDA tworzy niestandardowe funkcje wielokrotnego użytku.
Twórz funkcje niestandardowe dla powtarzającej się logiki biznesowej
Funkcja LAMBDA pozwala tworzyć funkcje niestandardowe, których można wielokrotnie używać w skoroszycie. Zamiast kopiować formuły wszędzie, można utworzyć jedną funkcję, która przyjmuje dane wejściowe i zwraca obliczone wyniki.
Wzór jest następujący:
=LAMBDA(parametr1, [parametr2, ...], obliczenie)
Parametry pełnią funkcję symboli zastępczych – podczas wywołania funkcji przekazujesz rzeczywiste wartości, które zastępują te symbole zastępcze. Obliczenia wykorzystują te parametry do wygenerowania wyniku.
Załóżmy, że często obliczasz ważone wyniki wydajności. Możesz utworzyć funkcję LAMBDA, taką jak poniżej:
=LAMBDA(sprzedaż, kwota, waga, (sprzedaż/kwota)*waga)
Tworzy funkcję wielokrotnego użytku, która przyjmuje trzy dane wejściowe: rzeczywistą sprzedaż, limit sprzedaży i współczynnik ważenia. Zwraca ważony wynik wydajności, dzieląc sprzedaż przez limit i mnożąc wynik przez wagę. Nazwij tę funkcję „PerformanceScore” za pomocą Menedżera nazw programu Excel.
Aby nadać nazwę funkcji LAMBDA, przejdź do Formuły > Zarządzanie nazwami > Nowy.
Teraz możesz wywołać tę funkcję w dowolnym miejscu skoroszytu.
=Wynik wydajności(B2, C2, 0.7)
Funkcja ta oblicza wynik wydajności na podstawie podanej wartości sprzedaży, udziału i współczynnika ważenia.
Aby analizować regiony, możesz utworzyć funkcję, która klasyfikuje regiony na podstawie przychodów:
=LAMBDA(przychód, JEŻELI(przychód>100000, "Wysoki", JEŻELI(przychód>50000, "Średni", "Niski"))))
Ta funkcja klasyfikuje przychody na trzy poziomy: wysoki dla kwot powyżej 100,000 50,000 USD, średni dla kwot od 100,000 50,000 do XNUMX XNUMX USD oraz niski dla kwot poniżej XNUMX XNUMX USD. Możesz nadać mu nazwę „Przychód” i używać go we wszystkich arkuszach kalkulacyjnych w następujący sposób:
=Przychód(J2)
Funkcja LAMBDA działa również z innymi funkcjami i Umożliwia pisanie formuł w języku ludzkim Stosowanie opisowych nazw zamiast niejednoznacznych odniesień do komórek.
Możesz uporządkować funkcje LAMBDA w Menedżerze nazw, używając prefiksów, takich jak „fn_” dla wszystkich funkcji niestandardowych (na przykład „fn_PerformanceScore”). Ułatwia to ich wyszukiwanie i zapobiega konfliktom ze standardowymi zakresami nazw.
1. Łączę te funkcje, aby tworzyć wydajne rozwiązania.
Tworzenie kompleksowych narzędzi do analizy biznesowej
Użycie instrukcji SEQUENCE, LET i LAMBDA razem pozwala na rozwiązywanie problemów, które w innym przypadku wymagałyby wielu kolumn pomocniczych lub złożonych formuł tablicowych. Taka kombinacja tworzy dynamiczne i łatwe w utrzymaniu rozwiązania.
Rozważmy stworzenie narzędzia do prognozowania sprzedaży z wykorzystaniem danych sprzedażowych. Poniższa formuła oblicza 12-miesięczną prognozę sprzedaży dla pojedynczej kwoty sprzedaży początkowej. Zaczyna się od zdefiniowania dwóch zmiennych kluczowych za pomocą formuły LET. Jako wartość bazową sprzedaży przyjmuje wartość z komórki G2.
=LET(sprzedaż_podstawowa, G2, stopa_wzrostu, L2, ProjectMonthly, LAMBDA(miesiąc, sprzedaż_podstawowa * (1 + stopa_wzrostu)^miesiąc), ProjectMonthly(SEKWENCJA(12)))
Następnie obliczasz miesięczną stopę wzrostu z poziomu L2 wynoszącą 0.04 (4%). Możesz zmieniać tę wartość, aby modelować różne scenariusze. Następnie definiujesz małą, wielokrotnego użytku funkcję o nazwie ProjectMonthly. Funkcja ta oblicza prognozowaną sprzedaż dla danego miesiąca na podstawie sprzedaży bazowej i stopy wzrostu.
Ponadto wywołuje funkcję ProjectMonthly i przekazuje do niej SEQUENCE(12). Generuje to tablicę liczb od 1 do 12, a LAMBDA automatycznie stosuje obliczenia do każdej liczby w tej sekwencji.
Oto przydatny kalkulator nagród, który oblicza nagrody na podstawie osiągniętego celu.
=LAMBDA(sprzedaż, cel, LET(współczynnik, sprzedaż/cel, IF(współczynnik>=1.2, sprzedaż*0.08, IF(współczynnik>=1, sprzedaż*0.05, 0))))
Zacznij od małych rzeczy, a następnie zwiększaj poziom skomplikowania.
Funkcje te działają najlepiej, gdy są starannie łączone. Zacznij od prostych aplikacji — użyj funkcji SEQUENCE do tworzenia danych testowych, funkcji LET do usuwania duplikatów obliczeń i funkcji LAMBDA do często używanych reguł biznesowych. Gdy oswoisz się z każdą funkcją z osobna, znajdziesz naturalne możliwości łączenia ich w bardziej zaawansowane rozwiązania.
Krzywa uczenia się nie jest stroma, ale korzyści są ogromne. Twoje arkusze kalkulacyjne stają się bardziej niezawodne, łatwiejsze do audytu i prostsze do modyfikacji w przypadku zmian wymagań biznesowych. To właśnie sprawia, że te trzy funkcje są szczególnie cenne dla każdego, kto regularnie pracuje z danymi.
