Jest kilka funkcji, które w Excelu są używane nadzwyczaj często. Są to przede wszystkim WYSZUKAJ.PIONOWO, SUMA.JEŻELI, LICZ.JEŻELI. Te funkcje są niezwykle użyteczne i elastyczne. Ale i one czasem zawodzą. Są takie sytuacje, gdy trzeba pokombinować trochę bardziej.
Sprawdź funkcje Excel, które musisz znać!
Chciałbyś/chciałabyś, aby Twoje ulubione funkcje były jeszcze bardziej elastyczne? Chcesz łatwiej rozwiązywać na pozór skomplikowane problemy? Na szczęście jest na to sposób. Poznaj odwołania wieloznaczne oraz symbole wieloznaczne.
Symbole wieloznaczne w Excelu
Są takie trzy znaki w Excelu, które są traktowane przez program jako znaki specjalne.
- Gwiazdka „*” – zastępuje dowolny ciąg znaków przylegający do tekstu. Na przykład tekst „*a” jest rozumiany przez Excela jako dowolny ciąg znaków kończący się literą „a”. Może to być „gruszka„ lub „Ala„.
- Znak zapytania „?” – działa podobnie do znaku „*”, z tą różnicą, że zastępuje tylko jeden znak. Na przykład „?a” może odpowiadać „ma”, „1a” lub „la”.
- Tylda „~” – kasuje efekt działania znaków specjalnych „*” oraz „?”. Dzięki „~” możemy traktować znaki specjalne jako zwykłe znaki w tekście.
Sprawdź konkretne przykłady jak używać odwołania wieloznaczne za pomocą tych trzech magicznych znaków.
Ale najpierw –> <–
Rozpoznawanie imion żeńskich w formule
W tym przykładzie mamy prostą tabelę imion i przypisanych im kwot. Mamy za zadanie wyznaczyć sumę kwot odpowiadającą imionom żeńskim i oddzielnie imionom męskim.
Tabela jest mała, więc z łatwością możesz obliczyć szukaną wartość „na palcach” lub używając prostej funkcji SUMA. Takie rozwiązanie nie będzie jednak elastyczne i sprawdzi się tylko w tym konkretnym przykładzie. Jeśli będziemy chcieli przenieść je na większą tabelę, to pojawi się problem.
Innym sposobem jest dodanie kolumny z oznaczeniem płci K = Kobieta i M = Mężczyzna obok imion. W ten sposób będziesz mógł/mogła użyć standardowej funkcji SUMA.JEŻELI do zsumowania wartości dla konkretnych płci. Jest to dobre rozwiązanie o ile potrafisz szybko i sprawnie dodać oznaczenie płci. W małej tabeli możesz zrobić to „ręcznie”. Jeśli natomiast chciałbyś/chciałabyś użyć do tego formuły, to znów wracamy do punktu wyjścia. Czyli do naszego pierwotnego problemu rozpoznania, które imiona są żeńskie a które męskie.
Porady i przykłady jak używać funkcji SUMA.JEŻELI znajdziesz na naszych kursach on-line Excel Średnio Zaawansowany oraz Excel Zaawansowany. Ale skoro ten poradnik jest o symbolach specjalnych i odwołaniach wieloznacznych, to użyję ich do zbudowania rozwiązania tego problemu 😊
Na początku jednak drobna obserwacja, która pomoże nam zbudować odpowiednią formułę. Polskie imiona żeńskie kończą się literą „a”. Przynajmniej te tradycyjne. Mogą się zdarzyć jakieś wersje imion typu „Nikol” lub temu podobne. Ograniczmy się jednak do stwierdzenia, że polskie imiona żeńskie kończą się literą „a” 😊.
Dzięki temu możemy użyć konstrukcji „*a”, która będzie tłumaczona przez Excel na dowolny ciąg znaków kończący się literą „a”. Więc taka fraza będzie odpowiadała wszystkim imionom żeńskim.
Teraz jesteśmy już gotowi do zbudowania formuły na bazie funkcji SUMA.JEŻELI. Sumujemy wszystkie imiona żeńskie, czyli takie, które kończą się na literę „a”.
=SUMA.JEŻELI(Imiona_1[Imię]; „*a”; Imiona_1[Kwota])
Proste, prawda? Najważniejsze jednak jest to, że formuła jest elastyczna. Dodatkowo nie musimy dodawać żadnych nowych kolumn ani modyfikować naszych danych.
Problem z niepotrzebnymi spacjami
Często dane pobrane z zewnętrznych źródeł nie są najwyższej jakości. Mają wiele błędów i nieprawidłowości, które mogą skutecznie utrudnić stosowanie standardowych formuł. Jednym z takich problemów są zbędne spacje na końcu słowa.
Na pierwszy rzut oka takie dane wyglądają ok. Dlatego, że znaków spacji nie widać. Jeśli jednak przyjrzymy się tym imionom bliżej, to można zauważyć, że na końcu niektórych faktycznie znajduje się jedna lub więcej spacji.
Chcemy policzyć wszystkie wystąpienia imienia Agata oraz imienia Łukasz. Użyjemy oczywiście funkcji LICZ.JEŻELI. Jako kryterium zastosujemy odpowiednie imię.
Jednak słowo „Agata” nie jest tym samym co „Agata „ (z 3 spacjami na końcu). Excel rozpozna jedynie dokładne wystąpienia szukanych słów. Dlatego będzie więc nieprawidłowy.
Rozwiązaniem tego problemu jest dodanie znaku „*” na końcu imienia. W ten sposób zastąpimy wszystkie potencjalne spacje na końcu imienia. Będziemy wyszukiwać
[@Imię]&”*”
To były przykłady z zastosowaniem znaku specjalnego „*” do budowania odwołań wieloznacznych. Przejdźmy teraz do pozostałych dwóch symboli.
Odwołania wieloznaczne w filtrach
Poniższa tabela zawiera proste zestawienie zamówień. Każdy rekord zawiera numer dokumentu sprzedaży oraz kwotę.
Wiemy, że konwencja tworzenia numerów dokumentów jest następująca:
- na początku „PAR” lub „VAT” dla paragonów lub faktur VAT odpowiednio.
- ukośnik „/”
- kombinacja 6 dowolnych znaków „xxxxxx”. Dopuszczalne są cyfry, litery i znaki specjalne.
- ukośnik „/”
- na końcu rok pisany w konwencji czterocyfrowej „rrrr”.
Naszym zadaniem jest zastosowanie filtra, który będzie wyświetlał wszystkie paragony wystawione w 2019 roku. Jak podejść do rozwiązania tego problemu?
Zobaczmy jak wygląda numer każdego z szukanych dokumentów:
PAR/xxxxxx/2019
gdzie „xxxxxx” są zupełnie dowolne.
Na szczęście wiemy, że jeden dowolny znak możemy zapisać używając symbolu specjalnego „?”. Przekładając numer szukanych dokumentów na język rozpoznawany przez odwołania wieloznaczne dostajemy:
PAR/??????/2019
Użyjemy dokładnie takiej frazy w filtrze numeru dokumentu. Teraz spróbuj założyć filtr i wyświetlić inne dokumenty. Potrafisz znaleźć faktury VAT z 2018 roku?
Wyszukiwanie tekstu zawierającego symbol wieloznaczny
W tym przykładzie mamy tabelę zawierającą słowa klucze oraz przypisane do nich kwoty. Tekst-klucz może zawierać również liczby i znaki specjalne. W tym symbole wieloznaczne.
Naszym zadaniem jest wyszukanie kwoty odpowiadającej słowu „Aga*”.
Naturalnie próbujemy użyć standardowej funkcji WYSZUKAJ.PIONOWO. Jednak wtedy Excel potraktuje symbol „*” jako odwołanie wieloznaczne. Dlatego potraktuje słowo „Agata” jako pierwsze na liście spełniające kryteria wyszukiwania „Aga*” i zwróci nieprawidłową wartość.
Jak sobie z tym poradzić? Na szczęście jest symbol „~”, który kasuje wieloznaczność symboli „*” i „?”. Możesz więc dodać na końcu „~*”, aby wymusić, by funkcja szukała słowa z symbolem „*” na końcu.
Trudniejszym przykładem będzie wyszukanie wartości dla słowa „Aga?a”. Wierzę, że sobie poradzisz. W razie problemów lub pytań zaprasza na naszą grupę Facebook. Rozwiązujemy tam takie i podobne zadania.
Elastyczne Znajdź/Zamień a odwołania wieloznaczne
Symbole specjalne mogą Ci też pomóc, jeśli chcesz użyć polecenia Znajdź/Zamień. To bardzo popularne i użyteczne polecenie ma też niestety swoje ograniczenia. W standardowym zastosowaniu wyszukujemy konkretnego ciągu znaków i zastępujemy go innym konkretnym ciągiem znaków. A co zrobić w sytuacji opisanej poniższym przykładem?
Niektórzy ludzie nie do końca wiedzą jak zapisać nazwę marki Turbo Excel. Niektórzy używają łączników takich jak myślnik, kropka, podkreślenie, znak zapytania lub inne. Chcemy szybko i łatwo zamienić wszystkie sposoby pisowni na jeden poprawny Turbo Excel (ze spacją).
W tym celu użyjmy polecenia Znajdź/Zamień (skrót CTRL + H). Ale zamiast zastępować oddzielnie każdy ze znaków „-„, „.”, „_”, „?” spacją zastąpimy je wszystkie za jednym razem.
Wystarczy znaleźć frazę „Turbo?Excel” i zamienić ją na „Turbo Excel”. Wtedy Excel automatycznie potraktuje „?” jako znak specjalny i wstawi w jego miejsce dowolny znak.
Odwołania wieloznaczne w formatowaniu warunkowym
W tym przykładzie zobaczysz jak za pomocą formatowania warunkowego wyróżnić jedynie imiona żeńskie na liście imion.
Funkcjonalność formatowania warunkowego jest szczegółowo omówiona na naszych kursach on-line Excel Średnio Zaawansowany oraz Excel Zaawansowany. Możesz rozbudować lub odświeżyć swoją wiedzę na bazie licznych przykładów i ćwiczeń.
Jak się zapewne domyślasz, w tym przykładzie użyjemy symbolu specjalnego i odwołania wieloznaczne, aby zbudować odpowiednią formułę.
=JEŻELI(LICZ.JEŻELI(B4; „*a”); PRAWDA; FAŁSZ)
Zwróć uwagę, że wewnętrzna funkcja LICZ.JEŻELI(B4; „*a”) zwraca 1 jeśli imię kończy się na „a” oraz 0 w przeciwnym przypadku.
Na koniec wystarczy użyć malarza formatów, aby skopiować formatowanie na całą kolumnę w tabeli.
–> Na koniec pobierz plik z kompletnym rozwiązaniem i przeanalizuj je jeszcze raz. <–
Jeśli szukasz więcej praktycznych przykładów i porad, to zapraszam na kursy online. Szczególnie przydatne będą kurs Excel Średnio Zaawansowany oraz Excel Zaawansowany. To kompleksowe kursy wideo naszpikowane praktycznymi przykładami. Wynieś swoją wiedzę z Excela na zupełnie inny poziom!