SUBSTITUTE w Excelu (spis treści)
- SUBSTITUTE w programie Excel
- SUBSTITUTE Formula w Excelu
- Jak korzystać z SUBSTITUTE w Excelu?
SUBSTITUTE w programie Excel
- Jest to rodzaj funkcji arkusza, funkcja zastępcza to wbudowana funkcja, która jest sklasyfikowana jako funkcja ciągów lub funkcji tekstowych
- Zastępuje nowy tekst zamiast starego tekstu lub istniejącego tekstu w ciągu tekstowym
- Funkcja zastępowania jest przydatna, gdy chcesz zastąpić określone słowo lub tekst w ciągu tekstowym
- Najczęściej jest używany do zastępowania ciągów tekstowych z importowanych danych
- Funkcja zastępowania jest używana, gdy chcesz zastąpić tekst na podstawie jego zawartości, a nie pozycji
- Jest przydatny w usuwaniu linii danych w komórce, w wyniku czego powstaje prawidłowy ciąg znaków (wyjaśniono w przykładzie: 4)
- Pozwala to zachować spójność w całym arkuszu programu Excel lub książce
Definicja
SUBSTITUTE Funkcja zastępuje stary tekst nowym tekstem w odwołaniu do komórki lub ciągu tekstowym. Zastąpi wszystkie wystąpienia starego tekstu, chyba że zostanie wybrany konkretny numer wystąpienia
SUBSTITUTE Formula w Excelu
Poniżej znajduje się formuła SUBSTITUTE:
Funkcja SUBSTITUTE ma następujące argumenty:
Tekst: Jest to tekst lub odwołanie do komórki zawierającej tekst, dla którego chcesz zastąpić znaki
Stary tekst: tekst, który chcesz zastąpić lub usunąć
Uwaga: Jeśli używasz ciągu tekstowego, musisz zawsze wymieniać ten argument w „podwójnych cudzysłowach”. Rozróżniana jest wielkość liter
Nowy_tekst: Jest to nowy tekst zastępujący stary tekst lub Podciąg, którego chcesz użyć jako zamiennika
Uwaga: Jeśli używasz ciągu tekstowego, musisz zawsze wymieniać ten argument w „podwójnych cudzysłowach”. Rozróżniana jest wielkość liter
Instance_num: Jest to parametr opcjonalny, określa, które wystąpienie starego tekstu chcesz zastąpić nowym tekstem, jeśli ten parametr zostanie pominięty lub nie zostanie dodany, każde wystąpienie starego tekstu zostanie zastąpione nowym tekstem.
Możesz użyć tego argumentu, aby wskazać, które wystąpienie chcesz zastąpić
Jak korzystać z funkcji SUBSTITUTE w programie Excel?
Ta funkcja SUBSTITUTE jest bardzo łatwa w użyciu. Zobaczmy teraz, jak korzystać z funkcji SUBSTITUTE w programie Excel za pomocą kilku przykładów.
Możesz pobrać tę funkcję SUBSTITUTE w szablonie Excel tutaj - funkcję SUBSTITUTE w szablonie ExcelPrzykład 1
Aby zamienić stary ciąg na nowy za pomocą funkcji SUBSTITUTE.
w tym scenariuszu zastąpi stary ciąg (stare wino) nowym ciągiem (nowe wino) za pomocą funkcji zastępczej
Argumenty funkcji zastępczej:
Tekst: Który tekst chcemy zmienić? Jest to komórka referencyjna o numerze kontaktowym tj. B6 LUB STARE WINA, tj. = SUBSTITUTE (B6,
Stary tekst: Tutaj chcemy zmienić istniejący stary ciąg tekstowy, tj. STARY WINA, więc wpisz go z podwójnymi cudzysłowami „STARY WINA” lub można podać odwołanie do komórki, tj. = SUBSTITUTE (B6, C6,
Nowy_tekst: Chcemy zmienić go na NOWE WINA, więc wpisz go z podwójnymi cudzysłowami, tzn. „NOWE WINA” lub odwołanie do komórki można wspomnieć, tj. = SUBSTITUTE (B6, C6, D6
Instance_num: Wskazuje, w której konkretnej instancji zamierzamy zastąpić?
Tutaj zmieniamy cały ciąg w komórce, dlatego należy go pozostawić pusty lub pominąć
Tutaj używamy tej formuły SUBSTITUTE (B6, C6, D6):
Dane wyjściowe będą:
Przykład nr 2
Aby zastąpić określone słowo w ciągu tekstowym nowym słowem za pomocą funkcji SUBSTITUTE w programie Excel
Załóżmy, że chcę zastąpić pierwszą literę „ g” w słowie google słowem „d”
Zwraca różne wyniki w zależności od numeru instancji podanego w ostatnim argumencie
Przypadek 1: Załóżmy, że jeśli pomijam argument Instance_num w funkcji SUBSTITUTE, każde następne wystąpienie starego tekstu zostanie zastąpione nowym tekstem, tzn. Wszystkie wystąpienia zostaną zastąpione
Funkcja SUBSTITUTE bez argumentu liczba_instancji, tj. = SUBSTITUTE (B12, C12, D12)
wyjście będzie:
Przypadek 2: Załóżmy, że jeśli wymienię 1 w argumencie Instance_num w funkcji SUBSTITUTE, zastąpi on tylko pierwsze wystąpienie starego tekstu podanym nowym tekstem lub zastąpi tekst tylko w pierwszej instancji
Funkcja SUBSTITUTE z argumentem Instance_num tj. = SUBSTITUTE (B13, C13, D13, 1),
wyjście będzie:
Przykład nr 3
Zastępowanie znaku specjalnego za pomocą funkcji zastępczej w programie Excel
Zamiana „-” w numerze kontaktowym 778-423-9684 na „*”
Argumenty funkcji zastępczej:
Tekst: Który tekst chcemy zmienić? Jest to komórka referencyjna o numerze kontaktowym tj. 778–423–9684
tj. = SUBSTITUTE („778–423–9684”,
old_text: Chcemy zmienić myślnik (-), więc wpisz go z podwójnymi cudzysłowami „-”
tj. = SUBSTITUTE („778-423-9684”, „-”,
Nowy_tekst: Chcemy zmienić go na znak gwiazdki „*”, więc wpisz go z podwójnymi cudzysłowami
tj. = SUBSTITUTE („778-423-9684”, „-„, „*”
Instance_num: Wskazuje, w której konkretnej instancji zamierzamy zastąpić?
Tutaj musimy go wymienić dwa razy, dlatego należy pozostawić puste
tj. = SUBSTITUTE („778-423-9684”, „-„, ”*”) lub = SUBSTITUTE (K6, ”-„, ”*”)
Dane wyjściowe będą:
Przykład 4
Usuwanie przesunięć linii w komórce za pomocą funkcji SUBSTITUTE.
Zastępuje znak przesunięcia wiersza (ANSI CODE 10) spacją, zwraca wartość wyjściową właściwego ciągu bez żadnych dodatkowych spacji
Tworzenie ciągu tekstowego zawierającego podziały wierszy lub źródła w komórce
Początkowo podział linii można wstawić do komórki za pomocą formuły SUBSTITUTE.
Zawartość w komórkach G13, H13 i I13 jest sklejana za pomocą znaku handlowego i (&), jest to operator konkatenacji w programie Excel, wraz ze znakiem handlowym, znak CHAR (10) służy do utworzenia podziału linii
ZNAK (10): Jest to kod znakowy do podziału wiersza w programie Excel
= G13 i CHAR (10) oraz H13 i CHAR (10) i I13
Wynikiem jest tekst wyjściowy z podziałem wiersza:
Uwaga: Przed zastosowaniem formuły musimy upewnić się, że zawijanie tekstu jest włączone w komórce, która zwraca wartość wyjściową. Tylko wtedy powstaje tekst zawierający podziały linii
Po zakończeniu ciąg tekstowy zawierający podziały wierszy lub kanały w komórce jest kopiowany do innej komórki i stosowana jest funkcja zastępcza
= SUBSTITUTE (L13, CHAR (10), ”„)
Dane wyjściowe będą:
Zastępuje znak przesunięcia wiersza (CHAR (10) lub kod ANSI 10) spacją, co daje prawidłowy ciąg
Przykład 5
NESTED SUBSTITUTE, tj. Zastępując wiele znaków jedną formułą
W poniższym scenariuszu komórka „B18” zawiera ciąg tekstowy taki jak „PM1, DT1, T1”
gdzie „PM” oznacza „Project manager”, „DT” oznacza „Diabetes”, a „T” oznacza „Team”.
Chcę rozszerzyć lub zastąpić te trzy kody pełnymi nazwami w innej komórce
Można to osiągnąć, stosując w Excelu wspomnianą poniżej trzy formułę zastępczą
= SUBSTITUTE ( B18, „PM”, „Project manager”)
= SUBSTITUTE ( B18, „DT”, „Diabetes”)
= SUBSTITUTE ( B18, „T”, „Team”)
A następnie zagnieżdż je w sobie:
= SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (B18, „PM”, „Project Manager”), „DT”, „Diabetes”), „T”, „Team”)
Dane wyjściowe będą:
O czym należy pamiętać o funkcji SUBSTITUTE
- SUBSTITUTE Funkcja nie obsługuje symboli wieloznacznych
- Jeśli nie podano numeru instancji, wszystkie instancje zostaną zastąpione (wyjaśnione w EG 2, Przypadek 1)
- Numer instancji nie może być ujemny ani zero
- SUBSTITUTE działa również dla cyfr i znaków specjalnych
- W funkcji SUBSTITUTE argumenty old_text i new_text należy zawsze wymieniać w „podwójnych cudzysłowach”
- Rozróżniana jest wielkość liter
- Za pomocą funkcji SUBSTITUTE można usunąć jednocześnie dwa różne znaki. możemy zagnieździć kilka funkcji SUBSTITUTE w ramach jednej formuły, aby wykonać kilka zmian jednocześnie. (Wyjaśnione w przykładzie 5: NESTED SUBSTITUTE)
Polecane artykuły
To był przewodnik po funkcji SUBSTITUTE. Tutaj omawiamy formułę SUBSTITUTE i sposób korzystania z funkcji SUBSTITUTE w programie Excel wraz z praktycznymi przykładami i szablonami Excela do pobrania. Możesz także przejrzeć nasze inne sugerowane artykuły -
- Jak korzystać z TRANSPOSE w Excelu?
- Jak korzystać z funkcji AND w programie Excel?
- Jak korzystać z WYSZUKAJ w MS Excel?
- Funkcja IPMT w programie Excel