Tricki

odwołania wieloznaczne w Excel

Symbole i odwołania wieloznaczne

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.

znaki specjalne w Excel

  • 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 Excel

 

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.

funkcja suma Excel

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.

suma.jeżeli Excel

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])

 

Suma.jeżeli z odwołaniem wieloznacznym w Excel

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.

tabela Excel

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.

niepotrzebne spacje Excel

Chcemy policzyć wszystkie wystąpienia imienia Agata oraz imienia Łukasz. Użyjemy oczywiście funkcji LICZ.JEŻELI. Jako kryterium zastosujemy odpowiednie imię.

Licz.jeżeli w Excel

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ę]&”*”

licz.jeżeli z odwołaniem wieloznacznym w Excel

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ę.

tabela Excel

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.

odwołania wieloznaczne w filtrze Excel

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*”.

tabela Excel

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ść.

wyszukaj pionowo w Excel

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.

wyszukaj pionowo z odwołaniem wieloznacznym w Excel

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ą).

TurboExcel

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.

Znajdź i zamień z odwołaniem wieloznacznym w Excel

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.

formatowanie warunkowe w Excel

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.

formatowanie warunkowe z odwołaniem wieloznacznym w Excel

Na koniec wystarczy użyć malarza formatów, aby skopiować formatowanie na całą kolumnę w tabeli.

malarz formatów Excel

–> 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!

automatyzacja Excel z VBA

5 sposobów na automatyzację Excela z VBA

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.

własna funkcja w Excel

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? 😊

własna funkcja w VBA

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.

rejestrator makr w Excel

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.

rejestrator makr Excel

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.

rejestrator makr Excel

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

uruchom makro Excel

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ś).

przypisz makro do przycisku Excel

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.

tworzenie formularzy w Excel

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.

tworzenie spisu treści w Excel

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!

 

błędy w formułach Excel

5 funkcji, dzięki którym pozbędziesz się błędów w formułach

Codzienna praca z Excelem to przede wszystkim formuły. W swoich arkuszach używasz różnego rodzaju formuł: obliczeniowych, informacyjnych, wyszukiwania. Excel bez formuł byłby kolejną wersją notatnika do przechowywania danych w formie trochę bardziej czytelnej niż tekstowa.

Może się zdarzyć, że Twoja formuła, nad którą pracowałeś/aś tak mozolnie przez dłuższy czas nie działa tak jak powinna. Jeszcze gorzej jeśli zwraca błąd. Co wtedy zrobić? Jak sobie z tym poradzić?

Najlepiej zacząć systematycznie od odpowiedniego przygotowania danych, z którymi będziemy pracować. Kilka wskazówek odnośnie poprawnego przygotowania danych możesz znaleźć na naszej grupie Facebook. Jeśli jeszcze nie dołączyłeś/aś, to zrób to teraz.

Poznaj zasady poprawnego przygotowania danych w kursie Excel Podstawowy oraz Excel Średnio Zaawansowany.

Jednak czasem pomimo wielu starań i przygotowania danych formuły wciąż zwracają błędy. Może to być dzielenie przez zero, brak wyszukiwanej wartości w przeszukiwanym zakresie lub wiele innych. Na szczęście jest kilka funkcji, które pozwolą nam zapanować nad błędami w formułach Excela.

=CZY.BŁĄD

Jest to jedna z podstawowych funkcji informacyjnych do pracy z formułami. Bardzo przydatna szczególnie, gdy wykonujemy szereg formuł w kolejnych komórkach/kolumnach. Jeśli dana formuła korzysta z wyniku innej możesz użyć funkcji CZY.BŁĄD(wartość) w połączeniu z funkcją JEŻELI. W ten sposób możesz zapewnić, że formuła nie będzie wykonana w przypadku błędu.

Przykład użycia funkcji maximum Excel

=CZY.LICZBA

Zdecydowanie najczęściej budujemy formuły obliczeniowe.

Sprawdź 15 funkcji Excel, które po prostu musisz znać.

W przypadku takich formuł chcemy mieć pewność, że argumenty są liczbami. Z pomocą przychodzi nam kolejna funkcja informacyjna CZY.LICZBA(wartość). Funkcja zwraca wartość logiczną PRAWDA lub FAŁSZ w zależności od tego czy argument jest liczbą czy nie.

Funkcja jest szczególnie przydatna w przypadku, gdy pobieramy dane z zewnętrznego źródła. Często w takich sytuacjach liczby są przechowywane jako tekst. Może się też pojawić konflikt znaku separatora dziesiętnego kropka czy przecinek.

Przykład użycia funkcji minimum Excel

 

Pamiętaj, że funkcja CZY.LICZBA nie dokonuje konwersji liczb przechowywanych jako tekst na liczbę. To znaczy, że CZY.LICZBA(”123”) zwróci fałsz ponieważ argumentem jest tekst.

Sprawdź jak poradzić sobie, gdy liczba jest przechowywana jako tekst w kursie Excel Podstawowy.

=JEŻELI.BŁĄD

Najpotężniejsza i chyba najczęściej używana funkcja z całej listy. Przede wszystkim dlatego, że funkcja JEŻELI.BŁĄD(wartość; wartość_jeżeli_błąd) jest kompaktowa. Pozwala w łatwy sposób sprawdzić czy formuła zwraca błąd i jednocześnie wykonać inną czynność jeśli tak jest.

Przykład użycia funkcji data Excel

=CZY.BŁ

Funkcja CZY.BŁ(wartość) jest analogiczna w działaniu do funkcji CZY.BŁĄD(wartość). Z tym, że CZY.BŁ sprawdza jedynie czy wystąpił błąd #N/D (wartość niedostępna). Taki błąd może się pojawić przy wyszukiwaniu liczby lub tekstu, który nie występuje w przeszukiwanej tabeli.

=CZY.PUSTA

Kolejną przydatną funkcją jest CZY.PUSTA(wartość). Jak się pewnie domyślasz, działa ona analogicznie do innych funkcji z rodziny CZY.*. Funkcja CZY.PUSTA sprawdza czy komórka, do której się odwołujemy jest pusta.

Zachęcamy do używania funkcji z tej listy w formułach. Z pewnością pomogą Ci one uporać się z wieloma niespodziewanymi błędami generowanymi przez formuły. Dobrze napisana formuła, która nie zwraca błędów, znacznie poprawia komfort pracy w Excelu.

Jeśli szukasz więcej przykładów, to zapraszamy na kurs Excel Średnio Zaawansowany lub Excel Zaawansowany.