Znaki wieloznaczne w Excelu – Co to jest i jak ich najlepiej używać

Zobacz film o znakach wieloznacznych w Excelu

W Excelu są tylko 3 znaki wieloznaczne (gwiazdka, znak zapytania i tylda) i wiele można z nimi zrobić.

W tym poradniku pokażę Ci cztery przykłady, w których te znaki wieloznaczne w Excelu są absolutnym ratunkiem.

Znaki wieloznaczne w Excelu – wprowadzenie

Znaki wieloznaczne to znaki specjalne, które mogą zająć dowolne miejsce dowolnego znaku (stąd nazwa – wildcard).

W programie Excel występują trzy znaki wieloznaczne:

1. * (gwiazdka) – reprezentuje dowolną liczbę znaków. Na przykład Ex* może oznaczać Excel, Excels, Example, Expert itd.
2. ? (znak zapytania) – Reprezentuje jeden pojedynczy znak. Na przykład, Tr?mp może oznaczać Trump lub Tramp.
3. ~ (tylda) – Jest używany do identyfikacji znaku wieloznacznego (~, *, ?) w tekście. Na przykład, powiedzmy, że chcesz znaleźć dokładną frazę Excel* na liście. Jeśli użyjesz Excel* jako ciągu wyszukiwania, otrzymasz każde słowo, które ma Excel na początku, a następnie dowolną liczbę znaków (takich jak Excel, Excels, Excellent). Aby konkretnie szukać Excela*, musimy użyć ~. Tak więc nasz ciąg wyszukiwania będzie brzmiał excel~*. Tutaj, obecność ~ zapewnia, że Excel czyta następujący znak jako taki, a nie jako symbol wieloznaczny.

Uwaga: Nie spotkałem się z wieloma sytuacjami, w których trzeba użyć ~. Niemniej jednak, warto o tym wiedzieć.

Przejrzyjmy teraz cztery niesamowite przykłady, w których znaki wieloznaczne wykonują całą ciężką pracę.

Znaki wieloznaczne w Excelu – przykłady

Przyjrzyjrzyjmy się teraz czterem praktycznym przykładom, w których znaki wieloznaczne w Excelu mogą być bardzo przydatne:

1. Filtrowanie danych przy użyciu znaków wieloznacznych.
2. Wyszukiwanie częściowe przy użyciu znaku wieloznacznego i VLOOKUP.
3. Znajdowanie i zastępowanie dopasowań częściowych.
4. Liczenie niepustych komórek zawierających tekst.

#1 Filtrowanie danych za pomocą znaków wieloznacznych w Excelu

Znaki wieloznaczne w Excelu przydają się, gdy masz ogromne zbiory danych i chcesz filtrować dane na podstawie warunku.

Załóżmy, że masz zbiór danych, jak pokazano poniżej:

Możesz użyć znaku wieloznacznego gwiazdki (*) w filtrze danych, aby uzyskać listę firm, które zaczynają się od litery A.

Oto jak to zrobić:

• Zaznacz komórki, które chcesz filtrować.
• Przejdź do zakładki Dane -> Sortuj i filtruj -> Filtruj (skrót klawiaturowy – Control + Shift + L).
• Kliknij na ikonę filtra w komórce nagłówkowej
• W polu (poniżej opcji Filtr tekstu) wpisz A*
• Kliknij OK.

To natychmiast przefiltruje wyniki i da Ci 3 nazwy – ABC Ltd., Amazon.com, oraz Apple Stores.

Jak to działa? – Kiedy dodasz gwiazdkę (*) po literze A, Excel przefiltruje wszystko, co zaczyna się od A. Dzieje się tak, ponieważ gwiazdka (będąca znakiem wieloznacznym Excela) może reprezentować dowolną liczbę znaków.

Teraz, korzystając z tej samej metodologii, możesz użyć różnych kryteriów do filtrowania wyników.

Na przykład, jeśli chcesz filtrować firmy, które zaczynają się od alfabetu A i zawierają alfabet C, użyj ciągu A*C. To da Ci tylko 2 wyniki – ABC Ltd. i Amazon.com.

Jeśli użyjesz A?C zamiast tego, otrzymasz tylko ABC Ltd jako wynik (ponieważ tylko jeden znak jest dozwolony między 'a' i 'c')

Note: The same concept can also be applied when using Excel Advanced Filters.

#2 Partial Lookup Using Wildcard Characters & VLOOKUP

Partial look-up jest potrzebny, gdy musisz szukać wartości na liście i nie ma dokładnego dopasowania.

Na przykład, załóżmy, że masz zestaw danych jak poniżej i chcesz wyszukać firmę ABC na liście, ale lista ma ABC Ltd zamiast ABC.

Nie możesz użyć zwykłej funkcji VLOOKUP w tym przypadku, ponieważ wartość wyszukiwania nie ma dokładnego dopasowania.

Jeśli użyjesz funkcji VLOOKUP z dopasowaniem przybliżonym, da ona błędne wyniki.

Możesz jednak użyć znaku wieloznacznego w funkcji VLOOKUP, aby uzyskać właściwe wyniki:

Wprowadź następującą formułę w komórce D2 i przeciągnij ją do innych komórek:

=VLOOKUP("*"&C2&"*",$A$2:$A$8,1,FALSE)

Jak działa ta formuła?

W powyższej formule, zamiast używać wartości lookup jako takiej, jest ona otoczona po obu stronach znakiem wieloznacznym Excela – gwiazdką (*) – „*”&C2&”*”

To mówi Excelowi, że musi szukać każdego tekstu, który zawiera słowo w C2. Może to być dowolna liczba znaków przed lub po tekście w C2.

Więc formuła szuka dopasowania, a gdy tylko je uzyska, zwraca tę wartość.

3. Znajdź i zamień częściowe dopasowania

Znaki wieloznaczne Excela są dość uniwersalne.

Możesz ich używać zarówno w złożonych formułach, jak i w podstawowych funkcjach, takich jak Znajdź i zamień.

Załóżmy, że masz dane jak poniżej:

W powyższych danych region został wpisany na różne sposoby (np. Północny Zachód, Północny Zachód, Północny Zachód).

Taka sytuacja często ma miejsce w przypadku danych sprzedażowych.

Aby wyczyścić te dane i nadać im spójność, możemy użyć funkcji Znajdź i zamień ze znakami wieloznacznymi w Excelu.

Oto jak to zrobić:

• Zaznacz dane, w których chcesz znaleźć i zamienić tekst.
• Go to Home -> Find & Select -> Go To. Spowoduje to otwarcie okna dialogowego Znajdź i zamień. (Możesz również użyć skrótu klawiaturowego – Control + H).
• Wprowadź następujący tekst w oknie dialogowym Znajdź i zamień:
  • Znajdź co: North*W*
  • Zastąp przez: North-West
• Kliknij na Zamień wszystko.

To natychmiast zmieni wszystkie różne formaty i uczyni je spójnymi z North-West.

Jak to działa?

W polu Znajdź, użyliśmy North*W*, które znajdzie każdy tekst, który ma słowo North i zawiera alfabet 'W' w dowolnym miejscu po nim.

Więc, obejmuje to wszystkie scenariusze (NorthWest, North West, i North-West).

Znajdź i zamień znajduje wszystkie te przypadki i zmienia je na North-West i czyni je spójnymi.

4. Policz komórki niepuste, które zawierają tekst

Wiem, że jesteś inteligentny i myślisz, że Excel ma już wbudowaną funkcję, aby to zrobić.

Masz całkowitą rację!!!

Można to zrobić za pomocą funkcji COUNTA.

ALE… Jest z tym jeden mały problem.

Wiele razy, gdy importujesz dane lub używasz arkusza innych osób, zauważysz, że są w nim puste komórki, podczas gdy może tak nie być.

Komórki te wyglądają na puste, ale mają w sobie =””. Problem polega na tym, że funkcja COUNTA nie traktuje tego jako pustej komórki (liczy ją jako tekst).

Patrz przykład poniżej:

W powyższym przykładzie używam funkcji COUNTA do znalezienia komórek, które nie są puste i zwraca ona 11, a nie 10 (ale widać wyraźnie, że tylko 10 komórek ma tekst).

Powodem, jak już wspomniałem, jest to, że nie uważa A11 za puste (podczas gdy powinien).

Ale tak właśnie działa Excel.

Poprawka polega na użyciu znaku wieloznacznego Excela wewnątrz formuły.

Poniżej znajduje się formuła wykorzystująca funkcję COUNTIF, która liczy tylko komórki, w których znajduje się tekst:

=COUNTIF(A1:A11,"?*")

Ta formuła mówi Excelowi, aby liczył tylko wtedy, gdy komórka ma co najmniej jeden znak.

W kombi ?*:

• ? (znak zapytania) zapewnia, że przynajmniej jeden znak jest obecny.
• * (gwiazdka) robi miejsce dla dowolnej liczby dodatkowych znaków.

Uwaga: Powyższa formuła działa, gdy w komórkach znajdują się tylko wartości tekstowe. Jeśli masz listę, która zawiera zarówno tekst, jak i liczby, użyj następującej formuły:

=COUNTA(A1:A11)-COUNTBLANK(A1:A11)

Podobnie, możesz używać symboli wieloznacznych w wielu innych funkcjach Excela, takich jak JEŻELI(), SUMIF(), AVERAGEIF() i MATCH().

Warto również zauważyć, że chociaż można używać symboli wieloznacznych w funkcji SZUKAJ, nie można ich używać w funkcji ZNAJDŹ.

Mam nadzieję, że te przykłady pozwolą Ci poznać wszechstronność i moc symboli wieloznacznych w Excelu.