Jak używać funkcji REDUCE w programie Microsoft Excel

VBA był kiedyś jedynym sposobem na obsługę iteracyjnej, wieloetapowej logiki w Microsoft Excel. Już nie. REDUCE przenosi moc programowania funkcyjnego bezpośrednio do Twoich komórek, pozwalając skondensować złożone, nieuporządkowane dane do pojedynczych, czystych wyników.

Niezależnie od tego, czy usuwasz niechciane znaki, czy tworzysz dynamiczną wyszukiwarkę, REDUCE pozwala tworzyć zaawansowane silniki danych, które dostosowują się w miarę rozwoju Twoich arkuszy kalkulacyjnych.

Składnia REDUCE: Analogią do śnieżnej kuli

Funkcja REDUCE może wydawać się onieśmielająca, ponieważ wymaga użycia LAMBDA, ale logika jest w rzeczywistości całkiem intuicyjna, gdy ją sobie wizualizujesz. Oto składnia:

=REDUCE(initial_value,array,LAMBDA(a,v,calculation))

Aby zrozumieć, jak to działa, wyobraź sobie kulę śnieżną toczącą się z góry:

• initial_value (opcjonalnie): To jest Twój punkt wyjścia. To początkowy rozmiar śnieżki, zanim zacznie się toczyć. Jeśli pozostawisz to pole puste, Excel użyje pierwszego elementu w Twojej tablicy jako wartości początkowej.
• tablica: To jest nachylenie. To zakres komórek, przez które funkcja ma się poruszać.
• LAMBDA(a,v,...): To jest silnik, który mówi kulce śnieżnej, co robić na każdym etapie. Akumulator (a) to sama kulka śnieżna, niosąca wynik z poprzedniego wiersza do następnego. Wartość (v) to świeży śnieg — konkretny element w bieżącym wierszu, który funkcja przetwarza.
• obliczenie: To jest logika. Mówisz Excelowi, aby wziął śnieżną kulę (a), dodał nowy śnieg (v) i przekazał wynik do następnego kroku.

Ważne jest, aby pamiętać, że REDUCE ma pamięć. W przeciwieństwie do standardowych funkcji, które analizują każdą komórkę niezależnie, REDUCE dokładnie wie, co wydarzyło się na każdym etapie.

Prosta rozgrzewka: Użycie REDUCE do iteracyjnego sumowania

Zanim wyjaśnię, jak użyć funkcji REDUCE do radzenia sobie z „niemożliwymi” zadaniami, pokażę Ci działanie efektu kuli śnieżnej na przykładzie podstawowej matematyki. Chociaż w poniższym przykładzie normalnie użyłbyś funkcji SUM, użycie REDUCE do dodawania listy liczb pokazuje, jak akumulator (a) i wartość (v) przekazują sobie pałeczkę.

Wyobraź sobie, że masz sformatowaną tabelę Excela o nazwie T_Numbers, zawierającą cyfry od 1 do 5.

Oto wzór na ich sumę:

=REDUCE(0,T_Numbers,LAMBDA(a,v,a+v))

Oto, co się dzieje:

Funkcja kończy listę i zwraca ostateczny wynik 15.

W tym prostym przykładzie, śnieżka była liczbą, która rosła poprzez dodawanie. Jednak prawdziwa moc funkcji REDUCE ujawnia się, gdy uświadomisz sobie, że akumulator (a) może przechowywać dowolny typ danych. W kolejnych przykładach z prawdziwego świata pokażę ci, jak akumulator może przetwarzać ciągi tekstowe lub śledzić stany logiczne.

Przykład z rzeczywistego świata 1: dynamiczna striptizerka wieloosobowa

Załóżmy, że masz listę kodów w tabeli Excel i chcesz je oczyścić, usuwając wszystkie myślniki, nawiasy, kropki, znaki plus i spacje.

Możesz rozważyć zagnieżdżenie funkcji SUBSTITUTE jedna w drugiej, aby to zrobić:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@Code],"-",""),"(",""),")",""),".",""),"+","")," ","")

Jednak ta formuła jest trudna do odczytania, podatna na literówki i niezwykle krucha. Co więcej, jeśli później zdecydujesz się również usunąć z kodów dwukropki i hashtagi, będziesz musiał dodać jeszcze więcej zagnieżdżonych funkcji SUBSTITUTE i nawiasów.

Dzięki funkcji REDUCE możesz użyć argumentu array, aby określić znaki, które chcesz usunąć. To mówi Excelowi, aby przechodził przez listę symboli i oczyszczał tekst znak po znaku.

Najpierw utwórz tabelę o nazwie T_Chars i wypisz każdy symbol, który chcesz usunąć. Szósty wiersz zawiera spację.

Następnie, w głównej tabeli, wpisz następującą formułę i naciśnij Enter:

=REDUCE([@Code],T_Chars[Characters],LAMBDA(a,v,SUBSTITUTE(a,v,"")))

Oto jak to działa:

1. Początkowa wartość (a): Zaczynasz od nieuporządkowanego kodu w bieżącym wierszu ([@Code]). To jest kula śnieżna w jej pierwotnym stanie.
2. Tablica (v): To jest twoja kolumna T_Chars[Characters]. Excel sprawdza każdy pojedynczy symbol na liście, jeden po drugim.
3. Pętla: W rundzie 1, REDUCE bierze kod i zastępuje pierwszy znak w kolumnie T_Chars[Characters] (myślnik) niczym („”). Następnie w rundzie 2, bierze już częściowo oczyszczony kod i usuwa kolejny znak (otwierający nawias). Funkcja kontynuuje ten cykl, aż wyczerpie wszystkie wiersze w kolumnie T_Chars[Characters].

Jeśli później zdecydujesz, że chcesz również usunąć dwukropki i hashtagi, po prostu dodaj te znaki na dole kolumny T_Chars[Characters]. Podobnie, jeśli zdecydujesz, że chcesz zachować wszystkie nawiasy, usuń te wiersze z tabeli.

Przykład z prawdziwego świata 2: Wyszukiwarka wielokategorii

Wyobraź sobie, że zarządzasz listą inwentarza i musisz oznaczyć każdy przedmiot, którego opis zawiera słowa „Niebezpieczny”, „Łatwopalny”, „Kruchy”, „Korozyjny” lub „Toksyczny”.

Aby to zrobić za pomocą zagnieżdżonego wyrażenia OR, należy skonstruować złożoną formułę w ten sposób:

=OR(ISNUMBER(SEARCH("Hazardous",[@Description])),ISNUMBER(SEARCH("Flammable",[@Description])),ISNUMBER(SEARCH("Fragile",[@Description])))

Chociaż to działa w obecnej formie, formuła jest niemal nie do zniesienia ze względu na swoją długość, a za każdym razem, gdy trzeba dodać nowe słowo kluczowe, trzeba ręcznie otworzyć formułę, dodać nowy ciąg ISNUMBER(SEARCH()) i wyrównać nawiasy.

Zamiast tego, korzystając z REDUCE, możesz utworzyć listę słów kluczowych dla argumentu array. Utwórz więc tabelę o nazwie T_Watchlist z jedną kolumną Keyword, a w swojej tabeli produktów wpisz tę formułę:

=REDUCE(FALSE,T_Watchlist[Keyword],LAMBDA(a,v,a+ISNUMBER(SEARCH(v,[@Description]))>0))

Oto, co się dzieje:

1. Wartość początkowa (a): Zaczynasz od FALSE — produkt jest uznawany za bezpieczny, dopóki pętla nie udowodni inaczej.
2. Tablica (v): To jest kolumna T_Watchlist[Keyword]. Excel sprawdza każde pojedyncze słowo na liście, jedno po drugim.
3. Logika: ISNUMBER(SEARCH(v,[@Description])) zwraca 1 (PRAWDA), jeśli zostanie znalezione dopasowanie, lub 0 (FAŁSZ), jeśli nie zostanie. Następnie formuła dodaje tę 1 lub 0 do akumulatora (a). Ponieważ kończy się na >0, w momencie gdy pętla znajdzie dopasowanie i akumulator osiągnie 1, wynik pozostaje PRAWDA przez resztę procesu.

Jeśli dodasz 10 nowych słów kluczowych do kolumny T_Watchlist[Keyword], REDUCE automatycznie wykryje nowe wiersze, uruchomi dodatkowe pętle i natychmiast zaktualizuje Twoje flagi. W rezultacie przechodzisz od zagnieżdżonej formuły OR, która wymaga stałej kontroli, do formuły REDUCE, która zmienia się w miarę zmiany kryteriów.

Dlaczego używać REDUCE zamiast VBA?

Możesz się zastanawiać, dlaczego nie użyć po prostu makra VBA lub Power Query do tych zadań. Chociaż te narzędzia są potężne, REDUCE oferuje trzy zalety:

• Natywne wsparcie w chmurze: Makra VBA nie działają w Excelu w przeglądarce ani w aplikacjach mobilnych i tabletowych Excela. Jeśli udostępnisz skoroszyt z makrami komuś, kto otworzy go w przeglądarce, Twoje rozwiązanie przestanie działać. REDUCE to natywna funkcja, która działa we wszystkich najnowszych wersjach Excela, niezależnie od urządzenia czy platformy.
• Zero problemów z bezpieczeństwem: Wiele działów IT blokuje pliki XLSM (z makrami) ze względu na ryzyko związane z bezpieczeństwem. Ponieważ REDUCE działa w standardowym pliku XLSX, możesz być pewny, że nie zostanie zablokowany przez zapory ani nie wywoła żadnych ostrzeżeń bezpieczeństwa.
• Błyskawiczne przeliczanie: Power Query jest świetny do obsługi ogromnych zbiorów danych, ale wymaga ręcznego odświeżenia, aby zobaczyć zmiany. REDUCE jest jednak reaktywny. W momencie, gdy dodasz nowe słowo kluczowe do swojej listy obserwowanych lub poprawisz literówkę w kodzie produktu, wyniki aktualizują się natychmiast.

REDUCE nie zawsze jest rozwiązaniem

Chociaż REDUCE jest niezwykle potężne, nie jest uniwersalnym rozwiązaniem dla każdej sytuacji. Są dwa scenariusze, w których warto rozważyć użycie innych narzędzi:

• Ogromne zestawy danych: Ponieważ funkcja REDUCE wykonuje pętlę obliczeniową dla każdej komórki, może znacznie spowolnić działanie arkusza kalkulacyjnego, jeśli uruchamiasz szczególnie skomplikowane pętle lub pracujesz z bardzo dużymi zestawami danych. W takich przypadkach zwykle najlepszym rozwiązaniem jest Power Query.
• Złożone debugowanie: W przeciwieństwie do VBA, gdzie można przechodzić przez kod linia po linii, REDUCE nie posiada specjalistycznych narzędzi do debugowania krok po kroku. Chociaż można testować małe fragmenty formuły, znacznie trudniej jest dokładnie ustalić, który wiersz spowodował błąd w długiej pętli.

Przechodząc od zagnieżdżonych ciągów znaków do czystej logiki REDUCE, sprawiasz, że twoje formuły są krótsze i mądrzejsze. W rzeczywistości REDUCE jest częścią tria potężnych funkcji LAMBDA zaprojektowanych do obsługi tablic w obrębie arkusza. MAP działa jak linia montażowa — używaj jej, gdy chcesz zastosować tę samą logikę do każdej pojedynczej komórki w zakresie i zwrócić nową listę o tym samym rozmiarze co oryginał. SCAN jest bardziej jak raport postępów — to idealna funkcja do tworzenia bieżących sum lub krok po kroku historii obliczeń.