Articles

Jak używać znaków wieloznacznych w Excelu

Czym są znaki wieloznaczne w Excelu?

Znaki wieloznaczne w Excelu to znaki specjalne, które mogą być używane w miejsce znaków w formule. Są one stosowane w formułach Excela w celu uzyskania niekompletnych dopasowań.

Excel obsługuje znaki wieloznaczne w formułach w celu zwrócenia wartości, które mają ten sam wzór. Znaki te są używane do szukania ciągu tekstowego z tymi samymi znanymi wzorcami – znakami początkowymi i końcowymi, a także liczbą znaków w komórce.

Znaki wieloznaczne są przydatne zwłaszcza wtedy, gdy nie jest znana dokładna zawartość komórki. Należy jednak pamiętać, że działają one tylko z tekstem, a nie z liczbami.

Typy znaków wieloznacznych

Istnieją trzy znaki wieloznaczne:

* (gwiazdka)

? (znak zapytania)

~ (tylda)

Znaki wieloznaczne

Asterisk

* (gwiazdka) reprezentuje dowolną liczbę znaków. Na przykład „Aus*” zwraca Australię lub Austrię.

Znak wieloznaczny gwiazdka - lista krajów

Znaki wieloznaczne

Znak zapytania

? (znak zapytania) reprezentuje jeden pojedynczy znak. Na przykład, „p?n” zwraca pan, pen lub pin.

znaki wieloznaczne znak zapytania - litera p

znaki wieloznaczne

Tylda

~ (tylda) jest używana do dosłownego wskazywania znaków gwiazdki i znaku zapytania w ich postaci, jak * lub ?zamiast znaków wieloznacznych w formule. Na przykład, „Aus~*” zwraca Aus*, ale nie Australię lub Austrię, jak powyżej.

znaki wieloznaczne tylda -. lista krajów

Znaki wieloznaczne

Znaki wieloznaczne

Jak używać znaków wieloznacznych

Znaki wieloznaczne są powszechnie używane w niektórych podstawowych formułach programu Excel, i.e., COUNTIF, COUNTIFS, VLOOKUP, FIND AND REPLACE, SEARCH, CONDITIONAL FORMATTING, etc.

Oto kilka przykładów jak to działa:

VLOOKUP

W normalnych okolicznościach, VLOOKUP szuka dokładnej wartości określonej w liście i zwraca odpowiadającą jej wartość w tabeli.

Czasami, dokładna, pełna wartość z listy nie jest dostępna i znana jest tylko jej częściowa zawartość. Wtedy właśnie przydają się znaki wieloznaczne.

W poniższym przykładzie zadaniem jest znalezienie liczby punktów dostawy dla Austin. Jednak zamiast Austin na liście pojawia się Austin, Texas.

Zwykła formuła VLOOKUP nie zadziałałaby w tym scenariuszu, ponieważ wartość lookup – Austin, nie ma dokładnego dopasowania.

Znaki wieloznaczne Aby znaleźć informacje, wpisz:

=VLOOKUP(D3&”*”;A1:B9;2;FALSE)

Wstawiając „*”, mówi Excelowi, aby szukał dowolnego tekstu, który zaczyna się lub kończy wartością lookup w D3 – Austin, i może mieć dowolną liczbę znaków po tekście.

Znajduje dokładne dopasowanie, które w tym przypadku wynosi 5. Znaki wieloznaczne

ZNAJDŹ I ZAMIEŃ

Używanie znaków wieloznacznych w Excelu znajdź i zamień jest przydatne do poprawiania danych i ujednolicania zestawu danych w całej bazie danych.

Ta sytuacja jest szczególnie częsta w przypadku obsługi danych wprowadzonych ręcznie przez pracowników lub klientów, tworząc dodatkowe wartości w bazie danych, co może utrudnić analizę.

Poniższy przykład pokazuje częsty obraz podczas obsługi bazy danych klienta.

Znaki wieloznaczneMiasta pod Waszyngtonem DC mogą być mylące.

Aby to naprawić, użyj prostej funkcji „ZNAJDŹ I REPLIKUJ” w Excelu.

  • Znajdź Waszyngton z literą D po nim i zastąp go jako „Waszyngton DC”

Znaki wieloznaczne

  • Kliknij „Zastąp wszystko” i teraz wszystkie wartości „Waszyngton DC” są wyrównane, co daje czystą bazę danych.

Znaki wieloznaczne

COUNTIF

Włączenie znaków wieloznacznych w ramach funkcji COUNTIF zlicza liczbę komórek w podanym zakresie, które spełniają określoną liczbę znaków.

  • i.e., mamy listę kodów krajów, a zadaniem jest zliczenie liczby krajów, które mają trzy znaki w swoim kodzie kraju.

W tym scenariuszu każdy ? odnosi się do jednego znaku. Znaki wieloznacznePonieważ chcielibyśmy dowiedzieć się, które kraje mają trzy znaki w swoim kodzie kraju, formuła powinna zawierać trzy ???

Aby się tego dowiedzieć, wpisz:

=COUNTIF(A2:A9;”???”)

Wartość zwracana to 2. Znaki wieloznaczne

FORMATOWANIE WARUNKOWE

Oprócz formuł znaki wieloznaczne mogą być również wykorzystywane do tworzenia formatowania warunkowego.

Powiedzmy, że naszym zadaniem jest wyróżnienie krajów, których nazwy zaczynają się na literę G.

formatowanie warunkowe

  1. Przejdź do Formatowanie warunkowe > Nowa reguła > Wybierz opcję „Użyj formuły do określenia, które komórki mają być sformatowane.”
  2. Wprowadź poniższą formułę.

Pomysł jest taki, aby znaleźć kraje, których nazwy zaczynają się na literę G. Zatem * (gwiazdka) jest używana, aby zażądać od Excela zwrócenia wartości, które zaczynają się od G i następuje po nich dowolna liczba znaków.

  1. Wybierz żądane formatowanie – w tym przypadku czcionka w kolorze czerwonym.

formatowanie warunkowe - czerwony

  1. Kliknij przycisk OK, a następnie zastosuj ten sam format do pozostałych komórek w kolumnie A za pomocą narzędzia Malarz formatów.

Wynik jest następujący:

zastosowano formatowanie warunkowe

Nie działa symbol wieloznaczny Excela? Oto dlaczego

Wspomniane wcześniej użycie * (gwiazdka) i ? (znak zapytania) odnoszą się do różnych sytuacji. Nie działają one jednak, jeśli same znaki wieloznaczne są częścią wartości wyszukiwania.

  • Na przykład, aby znaleźć „Aus?”, wymagane jest „Aus~?”.

Podsumowanie

Znaki wieloznaczne najlepiej sprawdzają się w sytuacjach, gdy dane są niekompletne lub częściowo dostępne. Służą one do zastępowania znaków w formule, czy to jako dowolna liczba znaków, czy pojedynczy znak, wyrażony jako * (gwiazdka) i ? (znak zapytania).

Znaki te są zgodne z większością popularnych funkcji – IFS, VLOOKUP, FIND AND REPLACE oraz CONDITIONAL FORMATTING. Pomagają one w wyszukiwaniu, dopasowywaniu i zwracaniu częściowego dopasowania w sytuacjach, gdy wartość wyszukiwania jest niekompletna. Czasami korygują dane poprzez ich synchronizację.

Jakkolwiek potężne by to nie było, należy pamiętać, że działają one tylko z tekstem, a nie z liczbami.

Aby dowiedzieć się więcej o formułach i funkcjach Excela, wypróbuj nasze kursy Excela już dziś.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *