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 –> Pobierz plik Excel i spróbuj wykonać wszystkie ćwiczenia samodzielnie <–

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!

Pracujesz w biurze i co dzień, co tydzień lub co miesiąc powtarzasz te same czynności? Kopiuj-wklej dane. Załóż, zdejmij filtr. Przeciągnij dobrze znaną formułę po raz setny lub nawet tysięczny. Znasz to z własnych doświadczeń?

Czasy, gdy wszystko trzeba było robić ręcznie już dawno minęły. Dzisiaj czas jest na wagę złota. Dlatego każdy szuka sposobów, aby swoją codzienną pracę wykonywać sprawniej, szybciej i efektywniej. Również tą pracę w Excelu.

Narzędzie MS Excel oferuje mnóstwo funkcjonalności i opcji do automatyzacji Twojej pracy. Wystarczy, że wyciągniesz po nie rękę i zaoszczędzisz trochę swojego cennego czasu. Tym razem pokażę Ci kilka sposobów na taką automatyzację przy pomocy VBA.

1. Tworzenie własnych funkcji

Korzystasz często z kombinacji tych samych funkcji? Napisanie prostej funkcji w VBA to chyba jeden z najprostszych sposobów na ułatwienie swojej pracy w Excelu. Spójrz na przykład jednej z najczęściej używanej kombinacji funkcji INDEKS i PODAJ.POZYCJĘ. Już w najprostszym zastosowaniu formuła może przysparzać o ciarki. Jest długa i skomplikowana.

Sprawdź najbardziej popularne funkcje w Excel, które musisz znać.

Dlatego polecam zamienić ją na własną funkcję. Teraz wygląda to dużo prościej, prawda? 😊

Najprostszy kod VBA tej funkcji znajdziejsz poniżej.

Function IdxMatch(tabela As Range, idx_wierszy, idx_kolumn)
   Dim wartosc, pozycja_w As Integer, pozycja_k As Integer
   pozycja_w = Application.WorksheetFunction.Match(idx_wierszy, tabela.Columns(1), 0)  'indeks wiersza
   pozycja_k = Application.WorksheetFunction.Match(idx_kolumn, tabela.Rows(1), 0)      'indeks kolumny
   wartosc = Application.WorksheetFunction.Index(tabela, pozycja_w, pozycja_k)         'wynik funkcji indeks
   IdxMatch = wartosc
End Function

Pamiętaj, że to tylko jeden z wielu przykładów na ułatwienie sobie życia za pomocą własnej funkcji w VBA. Masz jakieś swoje?

2. Rejestrowanie powtarzalnych procesów

Czas na kolejny przykład, który aż się prosi o automatyzację. Wyobraź sobie, że raz na miesiąc lub kwartał dostajesz zestawienie danych. Jakiś standardowy zrzut z systemu (sprzedażowego, raportowego, …). Twoim pierwszym zadaniem jest założenie filtra, lub kilku filtrów na te dane i wybranie błędnych rekordów. Trafiają one do innej osoby do wyjaśnienia.

Niby nic specjalnego. Zadanie jak każde. Jednak jeśli takich zadań nazbiera się 10 lub 20, to stracisz kilka godzin na bezsensowne czynności, które można łatwo zautomatyzować.

– Ale ja nigdy nie napisałem żadnego makra w VBA! Jak ma to zrobić?

Okazuje się, że do automatyzacji takich czynności wcale nie potrzebujesz znajomości języka VBA. Wystarczy, że skorzystasz z rejestratora makr.

Wybierz polecenie Zarejestruj makro i wymyśl nazwę dla swojego makra. Od teraz każda Twoja czynność jest rejestrowana. Po wykonaniu wszystkich kroków wybierz Zatrzymaj rejestrowanie.

Następnym razem jak będziesz musiał powtórzyć te same czynności wystarczy, że uruchomisz swoje makro.

Zaoszczędzony czas możesz przeznaczyć na coś dużo ciekawszego niż przeklejanie danych 😊

Dodawanie przycisków i skrótów klawiszowych

Napisałeś(aś) lub zarejestrowałeś(aś) swoje makro. Teraz wystarczy, że przejdziesz na listę makr, odnajdziesz to właściwe i uruchomisz je. Proste, prawda? Ale może być jeszcze prościej 😊

Dodaj przycisk i przypisz do niego wybrane makro. Dzięki temu nie będziesz musiał szukać go na liście (jeśli takich makr masz kilka). Nie będziesz musiał(a) nawet pamiętać jak je nazwałeś(aś).

Tworzenie inteligentnych formularzy

Wchodzimy w opcję dla trochę bardziej zaawansowanych. Ale spokojnie, w końcu po to uczyć się Excela, prawda? 😉

Rozwiązanie oparte na tworzeniu własnych inteligentnych formularzy jest skierowane głównie do tych, którzy chcą mieć większą kontrolę nad skoroszytem. Dzięki takim formularzom można ograniczyć użytkownikom dostęp do konkretnych komórek minimalizując jednocześnie ryzyko błędu lub niepożądanej zmiany. Zamiast tego, użytkownik pliku wprowadza swoje parametry w formularzu, który jest mu wyświetlany.

Poszczególne kontrolki (przyciski, pola tekstowe, …) trzeba odpowiednio zaprogramować. Jednak jest to na tyle proste, że poradzisz sobie z tym nawet z minimalną znajomością VBA.

Dodawanie treści, które same się odświeżają

Bardzo przydatne w pracy z dużymi plikami są spisy treści. Dzięki nim wiemy jaka jest zawartość każdego arkusza. Jeśli dodatkowo mamy do dyspozycji hiper linki, to z łatwością możemy poruszać się po pliku.

Stworzenie takiego spisu treści jest stosunkowo proste. Trudności zaczynają się, jeśli chcemy, aby taki spis aktualizował się za każdym razem, gdy wyświetlamy ten arkusz.

Wtedy niezbędne jest napisanie procedury, która tworzy taki spis treści. Następnie dodanie prywatnej procedury w tym konkretnym arkuszu, która będzie wywoływać procedurą tworzącą 😊

Private Sub Worksheet_Activate()
'Procedura uruchamia procedurę tworzenia spisu treści przy każdorazowym aktywowaniu tego arkusza

Call SpisTresci

End Sub

Pamiętaj. Jeśli masz dość powtarzalnych czynności w Excelu i chcesz zaoszczędzić trochę czasu, to próbuj automatyzować. Przecież nie chodzi o to, aby się na pracować. Tylko o to, aby zadanie zostało wykonane. Dlatego pracuj mądrze.

Jeśli szukasz więcej przykładów, 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!