Potęga funkcji Excela 365

Excel w wersji 365 jest prawdziwym game-changerem! Dlaczego? Bo otrzymujemy dostęp do funkcji, które wynoszą budowę formuł na całkiem inny poziom. Po pierwsze, złożone megaformuły możemy zastąpić krótszymi konstrukcjami. Po drugie, w wielu przypadkach, nie musimy już pisać własnych funkcji VBA (tzw. User-Defined Function). Po trzecie, niektóre funkcje mogą wykonywać operacje na zakresach – są więc alternatywą dla wbudowanych narzędzi. W tym artykule podam przykłady pięciu formuł, które byłyby praktycznie niemożliwe do utworzenia w starszej wersji Excela.

Wyciąganie cyfr z tekstu

Pierwsze zadanie polega na wyciągnięciu cyfr z tekstu. Dla ułatwienia wyróżniłem je na czerwono.

Sposób pierwszy – pojedyncze znaki

Formuła, która sprawdzi się w tym przypadku może wyglądać tak:

=POŁĄCZ.TEKSTY(;;
JEŻELI.BŁĄD(—(FRAGMENT.TEKSTU(B3;SEKWENCJA(DŁ(B3));1));””))

• Najpierw, dzięki funkcjom FRAGMENT.TEKSTU i SEKWENCJA, tworzymy tablicę pojedynczych znaków.
• Za pomocą podwójnego minusa (lub np. funkcji WARTOŚĆ) próbujemy przekonwertować każdy znak na cyfrę. Uda się to tylko w przypadku cyfr (wziętych w cudzysłów) – nie uda się dla liter i innych znaków.
• Dzięki JEŻELI.BŁĄD, wszystkie błędy zamieniamy na puste ciągi tekstowe. W ten sposób otrzymujemy tablicę złożoną z pustych ciągów i cyfr.
• Za pomocą POŁĄCZ.TEKSTY łączymy ze sobą wszystkie cyfry, ignorujemy zaś puste elementy tablicy.

Sposób drugi – wyrażenia regularne

Od niedawna Microsoft pozwala na stosowanie wyrażeń regularnych w formułach, co jest ogromnym ułatwieniem. Poprzednią formułę możemy więc skrócić do takiego zapisu.

=JEŻELI.BŁĄD(POŁĄCZ.TEKSTY(;;REGEXEXTRACT(B3;”[0-9]”;1));””)

• Korzystamy z nowej funkcji REGEXEXTRACT, której zadaniem jest wydobycie części tekstu spełniającego warunki określone we wzorcu.
• Podany wzorzec [0-9], z trzecim argumentem funkcji ustawionym na 1, pozwala utworzyć tablicę wszystkich cyfr.
• Funkcja POŁĄCZ.TEKSTY konwertuje dane z tablicy do ciągu tekstowego.
• Funkcja JEŻELI.BŁĄD jest zabezpieczeniem na wypadek, gdyby w tekście źródłowym nie było cyfr.

Zwrócenie słów z listy

Tym razem potrzebujemy wyciągnąć z ciągu tekstowego, tylko te słowa, które znajdują się na oddzielnie przygotowanej liście. Dla ułatwienia, wyróżniłem je na czerwono.

Wersja prostsza – duplikaty

Poniższa formuła zwraca wszystkie wystąpienia słowa w oryginalnym tekście.

=POŁĄCZ.TEKSTY(” „;;JEŻELI(CZY.LICZBA(PODAJ.POZYCJĘ(PODZIEL.TEKST(B3;” „);$F$3:$F$4;0));PODZIEL.TEKST(B3;” „);””))

• Większość pracy wykonuje tutaj funkcja PODZIEL.TEKST, którą możemy porównać do funkcji Split w VBA.
• Najpierw tworzymy tablicę wszystkich słów z oryginalnego tekstu. Rozdzielnikiem jest spacja.
• Za pomocą funkcji PODAJ.POZYCJĘ próbujemy znaleźć pozycję każdego słowa na naszej liście (F3:F4).
• Jeśli słowo znajduje się na liście – funkcja zwróci liczbę. Jeśli słowa nie ma na liście – funkcja zwróci błąd.
• Za pomocą funkcji JEŻELI sprawdzamy ten warunek. Jeśli mamy liczbę – zwracamy odpowiednie słowo z tablicy. Jeśli mamy błąd – zamieniamy go na pusty ciąg.
• Za pomocą funkcji POŁĄCZ.TEKSTY łączymy ze sobą pojedyncze słowa z tablicy do postaci ciągu tekstowego.

Wersja trudniejsza – unikaty

Poniższa formuła wprowadza jedną zmianę. W wyniku zwraca zawsze unikatowe wystąpienia. Jej działanie widać dobrze na ostatnim przykładzie. Słowo pies występuje tam dwukrotnie, ale w wyniku mamy je tylko raz.

=POŁĄCZ.TEKSTY(” „;;UNIKATOWE(JEŻELI(CZY.LICZBA(PODAJ.POZYCJĘ(PODZIEL.TEKST(B3;” „);$F$3:$F$4;0));PODZIEL.TEKST(B3;” „);””);PRAWDA))

• W tym przypadku dodaliśmy jedynie funkcję UNIKATOWE z drugim argumentem ustawionym na PRAWDA ponieważ słowa mamy w kolumnach, a nie w wierszach.
• Funkcję UNIKATOWE dodaliśmy przed JEŻELI, aby przekazać do POŁĄCZ.TEKSTY tablicę unikatowych słów.

Sortowanie według listy niestandardowej

Tym razem krótka tabelka (B2:C16), w której mamy słowną nazwę miesiąca i informację dotyczącą sprzedaży.

Chcemy posortować te dane według miesięcy, ale nie alfabetycznie tylko chronologicznie. W przypadku, gdy w danym miesiącu wystąpiło kilka transakcji, chcemy mieć najpierw najwyższe kwoty.

W kolumnie pomocniczej (E) znajduje się posortowana czasowo lista miesięcy.

Formuła, która sprawdzi się w tym przypadku może wyglądać tak:

=SORTUJ.WEDŁUG(B3:C16;PODAJ.POZYCJĘ(B3:B16;$E$3:$E$14;0);1;C3:C16;-1)

• W pierwszym argumencie funkcji SORTUJ.WEDŁUG wskazujemy całą tabelę źródłową (bez nagłówków).
• Następnie tworzymy dwa poziomy sortowania – każdy poziom składa się z dwóch elementów: tablicy (kolumny) i kolejności. Ogromnym plusem jest tu fakt, że tablica może być wirtualna (utworzona w pamięci Excela) – nie musi być ona częścią tabeli.
• Tworzymy wirtualną tablicę, która określa pozycje wszystkich nazw miesięcy na liście. Przykładowo Styczeń będzie miał 1, Luty 2 itd.
• W kolejności sortowania wpisujemy 1, ponieważ chcemy tabelę posortować rosnąco.
• Tworzymy drugi poziom sortowania i tu jest łatwiej. Kolumną będą dane z zakresu C3:C16 (Sprzedaż), zaś kolejnością będzie -1, ponieważ chcemy tabelę posortować malejąco.

Grupowanie wg nazwy miesiąca

Tym razem chcemy zgrupować dane wg miesiąca, obliczyć wartość środkową (medianę) dla sprzedaży i posortować dane malejąco.

Pomoże nam w tym taka oto formuła:

=SORTUJ(GRUPUJ.WEDŁUG(B3:B16;C3:C16;MEDIANA;;0);2;-1)

• W pierwszym argumencie funkcji GRUPUJ.WEDŁUG wskazujemy kolumnę (bez nagłówka), wg której chcemy grupować – jest to Miesiąc.
• W drugim argumencie podajemy kolumnę z wartościami – jest to Sprzedaż.
• W trzecim argumencie wpisujemy słownie MEDIANA, ponieważ interesuje nas wartość środkowa.
• Czwarty argument dotyczący nagłówków możemy pominąć, zaś w argumencie piątym wpisujemy 0, ponieważ nie chcemy, aby tabela w wyniku zwracała jakiekolwiek sumy częściowe.
• Mając już zgrupowaną tabelę, możemy ją posortować. Ponieważ wystarczy nam tylko jeden poziom, możemy użyć funkcji SORTUJ. Pierwszym argumentem będzie nowa tabela utworzona „w locie” dzięki funkcji GRUPUJ.WEDŁUG, sortować będziemy zaś wg mediany malejąco.

Walidacja adresu e-mail

Nowe funkcje obsługujące wyrażenia regularne to kolejny krok milowy w tworzeniu formuł. Możemy w prosty sposób wydobyć interesujące nas dane lub sprawdzić ich poprawność. Co więcej, nie musimy wiedzieć jak tworzyć reguły – wystarczy zapytać o to Copilota.

W tym zadaniu potrzebuję sprawdzić poprawność adresu e-mail. Chcę aby formuła zwracała w wyniku wartość zerojedynkową PRAWDA lub FAŁSZ.

Pomoże nam w tym taka oto formuła:

=REGEXTEST(B3;”^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$”)

• W pierwszym argumencie REGEXTEST podajemy odwołanie do komórki.
• W drugim argumencie wklejamy wzorzec utworzony przez asystenta AI.

Podsumowanie

Excel 365 zmienił w dużym stopniu sposób w jaki patrzyliśmy na funkcje. W klasycznym rozumieniu, wbudowana funkcja Excelowa dokonywała pewnych obliczeń, aby zwrócić w wyniku pojedynczą wartość. Korzystanie z formuł tablicowych (Ctrl + Shift + Enter) należało raczej do rzadkości.

Wprowadzając kilka lat temu mechanizm rozlania formuły, Microsoft zmienił sposób rozumienia funkcji Excelowych. Od tej pory potrafią one w łatwy sposób zwracać całe tablice danych, mogą dokonywać złożonych operacji filtrowania czy sortowania, ale także być alternatywą dla tak potężnych narzędzi jak np. tabele przestawne.

Plik do pobrania