Obejrzyj wideo na temat znaków wieloznacznych programu Excel
Istnieją tylko 3 znaki wieloznaczne Excela (gwiazdka, znak zapytania i tylda) i wiele można z nich zrobić.
W tym samouczku pokażę cztery przykłady, w których te symbole wieloznaczne Excela są absolutnymi ratownikami.
Znaki wieloznaczne programu Excel - wprowadzenie
Symbole wieloznaczne to znaki specjalne, które mogą zajmować dowolne miejsce dowolnego znaku (stąd nazwa - symbol wieloznaczny).
W programie Excel są trzy znaki wieloznaczne:
- * (gwiazdka) - Reprezentuje dowolną liczbę znaków. Na przykład Ex* może oznaczać Excel, Excels, Example, Expert itp.
- ? (znak zapytania) - Reprezentuje jedną postać. Na przykład Tr?mp może oznaczać Trump lub Tramp.
- ~ (tylda) - Służy do identyfikacji znaku wieloznacznego (~, *, ?) w tekście. Załóżmy na przykład, że chcesz znaleźć dokładną frazę Excel* na liście. Jeśli użyjesz Excel* jako ciągu wyszukiwania, otrzymasz dowolne słowo, które ma Excel na początku, a po nim dowolną liczbę znaków (takich jak Excel, Excels, Excellent). Aby konkretnie szukać Excela*, musimy użyć ~. Więc naszym ciągiem wyszukiwania będzie excel~*. W tym przypadku obecność znaku ~ zapewnia, że program Excel odczyta następujący znak w takiej postaci, w jakiej jest, a nie jako symbol wieloznaczny.
Uwaga: nie spotkałem się z wieloma sytuacjami, w których trzeba użyć ~. Niemniej jednak dobrze jest wiedzieć.
Przejdźmy teraz przez cztery niesamowite przykłady, w których postacie wieloznaczne wykonują wszystkie ciężkie zadania.
Znaki wieloznaczne programu Excel - przykłady
Przyjrzyjmy się teraz czterem praktycznym przykładom, w których znaki wieloznaczne Excela mogą być bardzo przydatne:
- Filtrowanie danych przy użyciu znaku wieloznacznego.
- Wyszukiwanie częściowe przy użyciu znaku wieloznacznego i WYSZUKAJ.PIONOWO.
- Znajdź i zamień częściowe dopasowania.
- Policz niepuste komórki zawierające tekst.
#1 Filtruj dane za pomocą znaków wieloznacznych Excel
Znaki wieloznaczne programu Excel przydają się, gdy masz duże zestawy danych i chcesz filtrować dane na podstawie warunku.
Załóżmy, że masz zbiór danych, jak pokazano poniżej:
Możesz użyć symbolu wieloznacznego gwiazdki (*) w filtrze danych, aby uzyskać listę firm zaczynających się na literę A.
Oto jak to zrobić:
- Zaznacz komórki, które chcesz filtrować.
- Przejdź do Dane -> Sortuj i filtruj -> Filtruj (Skrót klawiaturowy - Control + Shift + L).
- Kliknij ikonę filtra w komórce nagłówka
- W polu (pod opcją Filtr tekstu) wpisz A*
- Kliknij OK.
Spowoduje to natychmiastowe przefiltrowanie wyników i wyświetlenie 3 nazw - ABC Ltd., Amazon.com i Apple Stores.
Jak to działa? - Gdy dodasz gwiazdkę (*) po A, program Excel odfiltruje wszystko, co zaczyna się od A. Dzieje się tak, ponieważ gwiazdka (będąca symbolem wieloznacznym programu Excel) może reprezentować dowolną liczbę znaków.
Teraz, korzystając z tej samej metodologii, możesz używać różnych kryteriów do filtrowania wyników.
Na przykład, jeśli chcesz odfiltrować firmy, które zaczynają się od alfabetu A i zawierają w sobie alfabet C, użyj ciągu Klimatyzacja. To da tylko 2 wyniki - ABC Ltd. i Amazon.com.
Jeśli użyjesz A?C? zamiast tego otrzymasz jako wynik tylko ABC Ltd (ponieważ dozwolony jest tylko jeden znak między „a” i „c”)
Notatka: Ta sama koncepcja może być również zastosowana podczas korzystania z zaawansowanych filtrów programu Excel.
#2 Częściowe wyszukiwanie za pomocą symboli wieloznacznych i WYSZUKAJ.PIONOWO
Częściowe wyszukiwanie jest potrzebne, gdy musisz wyszukać wartość na liście, a nie ma dokładnego dopasowania.
Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej, i chcesz wyszukać firmę ABC na liście, ale lista zawiera ABC Ltd zamiast ABC.
W tym przypadku nie można użyć zwykłej funkcji WYSZUKAJ.PIONOWO, ponieważ wartość wyszukiwania nie ma dokładnego dopasowania.
Jeśli użyjesz funkcji WYSZUKAJ.PIONOWO z przybliżonym dopasowaniem, otrzymasz błędne wyniki.
Możesz jednak użyć symbolu wieloznacznego w funkcji WYSZUKAJ.PIONOWO, aby uzyskać prawidłowe wyniki:
Wprowadź następującą formułę w komórce D2 i przeciągnij ją do innych komórek:
=WYSZUKAJ.PIONOWO("*"&C2&"*",$A$2:$A$8,1,FAŁSZ)
Jak działa ta formuła?
W powyższym wzorze, zamiast używać wartości wyszukiwania w takiej postaci, jest ona otoczona po obu stronach gwiazdką ze znakiem wieloznacznym Excel (*) - „*”&C2&”*”
To mówi programowi Excel, że musi szukać dowolnego tekstu zawierającego słowo w C2. Może mieć dowolną liczbę znaków przed lub po tekście w C2.
W związku z tym formuła szuka dopasowania i gdy tylko otrzyma dopasowanie, zwraca tę wartość.
3. Znajdź i zamień częściowe dopasowania
Znaki wieloznaczne Excela są dość wszechstronne.
Możesz go używać w złożonej formule, a także w podstawowych funkcjach, takich jak Znajdź i zamień.
Załóżmy, że masz dane, jak pokazano poniżej:
W powyższych danych region został wprowadzony na różne sposoby (np. Północny zachód, Północny zachód, Północny zachód).
Często tak jest w przypadku danych sprzedażowych.
Aby wyczyścić te dane i zapewnić ich spójność, możemy użyć znaków wieloznacznych Znajdź i zamień za pomocą Excela.
Oto jak to zrobić:
- Wybierz dane, w których chcesz znaleźć i zamienić tekst.
- Przejdź do strony głównej -> Znajdź i wybierz -> Przejdź do. Spowoduje to otwarcie okna dialogowego Znajdź i zamień. (Możesz także użyć skrótu klawiaturowego - Control + H).
- Wpisz następujący tekst w oknie dialogowym Znajdź i zamień:
- Znajdź co: Północ*W*
- Zamienić: Północny zachód
- Kliknij Zamień wszystko.
To natychmiast zmieni wszystkie różne formaty i sprawi, że będą spójne z północno-zachodnim.
Jak to działa?
W polu Znajdź użyliśmy Północ*W* który znajdzie każdy tekst, który zawiera słowo Północ i zawiera alfabet „W” w dowolnym miejscu po nim.
W związku z tym obejmuje wszystkie scenariusze (północny zachód, północny zachód i północny zachód).
Znajdź i zamień znajduje wszystkie te wystąpienia i zmienia je na północno-zachodnie i zapewnia spójność.
4. Policz niepuste komórki zawierające tekst
Wiem, że jesteś sprytny i myślisz, że Excel ma już wbudowaną funkcję do tego.
Masz całkowitą rację!!
Można to zrobić za pomocą funkcji ILE.NIEPUSTYCH.
ALE… Jest z tym jeden mały problem.
Wiele razy, gdy importujesz dane lub korzystasz z arkusza roboczego innych osób, zauważysz, że są puste komórki, chociaż może tak nie być.
Te komórki wyglądają na puste, ale mają w sobie =””. Problem w tym, że
Kłopot polega na tym, że funkcja ILE.NIEPUSTYCH nie traktuje tej komórki jako pustej komórki (liczy ją jako tekst).
Zobacz przykład poniżej:
W powyższym przykładzie używam funkcji ILE.NIEPUSTYCH, aby znaleźć komórki, które nie są puste i zwraca 11, a nie 10 (ale wyraźnie widać, że tylko 10 komórek ma tekst).
Powodem, jak wspomniałem, jest to, że nie uważa A11 za puste (a powinno).
Ale tak działa Excel.
Rozwiązaniem jest użycie w formule znaku wieloznacznego programu Excel.
Poniżej znajduje się formuła wykorzystująca funkcję LICZ.JEŻELI, która zlicza tylko komórki zawierające tekst:
=LICZ.JEŻELI(A1:A11;"?*")
Ta formuła mówi programowi Excel, aby liczył tylko wtedy, gdy komórka ma co najmniej jeden znak.
w ?* kombinacja:
- ? (znak zapytania) zapewnia obecność co najmniej jednego znaku.
- * (gwiazdka) zapewnia miejsce na dowolną liczbę dodatkowych znaków.
Notatka: Powyższa formuła działa, gdy w komórkach znajdują się tylko wartości tekstowe. Jeśli masz listę zawierającą zarówno tekst, jak i liczby, użyj następującej formuły:
=LICZBA(A1:A11)-LICZBA PUSTE(A1:A11)
Podobnie możesz używać symboli wieloznacznych w wielu innych funkcjach programu Excel, takich jak JEŻELI(), SUMA.JEŻELI(), ŚREDNIAJEŻELI() i PODAJ.POZYCJĘ().
Warto również zauważyć, że chociaż możesz używać znaków wieloznacznych w funkcji SZUKAJ, nie możesz ich użyć w funkcji ZNAJDŹ.
Mam nadzieję, że te przykłady pokażą Ci wszechstronność i moc znaków wieloznacznych programu Excel.
Jeśli masz inny innowacyjny sposób na jego wykorzystanie, podziel się nim ze mną w sekcji komentarzy.
Przydatne mogą być następujące samouczki programu Excel:
- Korzystanie z funkcji LICZ.JEŻELI i LICZ.JEŻELI z wieloma kryteriami.
- Tworzenie listy rozwijanej w programie Excel.
- Przecięcie operatora w programie Excel
