Подстановка функции в Excel
Обновлено: 14.05.2024
Функция ПОДСТАВИТЬ была представлена в MS Excel 2007 и доступна в разделе Текстовые функции Excel Функции Список наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает сотни функций, которые важно знать аналитику Excel. Он заменит одну или несколько текстовых строк другой текстовой строкой. Эта функция полезна, когда мы хотим заменить старый текст в строке новой строкой.
Разница между функциями SUBSTITUTE и REPLACE
Функция ПОДСТАВИТЬ очень похожа на функцию ЗАМЕНИТЬ. Однако SUBSTITUTE заменяет один или несколько экземпляров заданной текстовой строки, тогда как REPLACE заменяет текст в указанной позиции предоставленной строки.
Формула
=ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_экземпляра])
Функция ПОДСТАВИТЬ использует следующие аргументы:
- Текст (обязательный аргумент) — это текст или ссылка на ячейку, содержащую текст, для которого мы хотим заменить символы.
- Old_text (обязательный аргумент) — текст, который мы хотим заменить.
- Новый_текст (обязательный аргумент) — текст, которым мы хотим заменить старый_текст.
- Instance_num (необязательный аргумент) — указывает, какое вхождение old_text вы хотите заменить новым_текстом. Если вы укажете instance_num, будет заменен только этот экземпляр old_text. В противном случае каждое вхождение old_text в тексте заменяется на new_text.
Как использовать функцию ПОДСТАВИТЬ в Excel?
Чтобы понять, как использовать функцию ПОДСТАВИТЬ, рассмотрим несколько примеров:
Пример 1
Предположим, что нам даны следующие данные:
Мы использовали приведенные ниже формулы для замены текста в приведенном выше примере.
Экземпляр ограничивает замену SUBSTITUTE одним конкретным экземпляром old_text. Если instance не указан, все экземпляры old_text заменяются на new_text. Мы получаем следующие результаты:
Кроме того, ЗАМЕНА чувствительна к регистру и не поддерживает подстановочные знаки. В приведенном выше примере, если для Lime мы указываем старый текст как «l», то функция не будет выполнять замену.
Пример 2
Если мы хотим преобразовать номер столбца в букву столбца Excel (например, A, B, C и т. д.), мы можем использовать формулу, основанную на функциях АДРЕС и ПОДСТАВИТЬ. Предположим, нам даны следующие данные:
Используется следующая формула:
Мы получаем следующие результаты:
Первым шагом было создание адреса, который будет содержать номер столбца. Мы сделали это с помощью функции ADDRESS, предоставив 1 для номера строки, номер столбца из A1 и 4 для аргумента abs_num (чтобы получить относительную ссылку): ADDRESS(1,B5,4).
С этой информацией ADDRESS возвращает текст «A1». Затем мы использовали ПОДСТАВИТЬ, чтобы убрать цифру 1 следующим образом: =ПОДСТАВИТЬ("A1", "1","").
Дополнительные ресурсы
Спасибо, что прочитали руководство CFI по важным функциям Excel! Потратив время на изучение и освоение этих функций, вы значительно ускорите анализ моделирования оценки. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:
Бесплатное руководство по Excel
Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.
Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel - формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.
Вам нужно заменить определенную часть текста? Вы можете использовать функцию ПОДСТАВИТЬ в Excel, чтобы помочь вам. Вот как!
В Excel функция ПОДСТАВИТЬ находит строку в ячейке и заменяет ее другой строкой. Функция ПОДСТАВИТЬ чувствительна к регистру и ищет экземпляры строки в ячейке.
Вы можете выбрать, будет ли функция ПОДСТАВИТЬ работать с конкретным экземпляром строки или будет воздействовать на все экземпляры. Это заменяет или заменяет все вхождения старой строки новой.
Синтаксис функции SUBSTITUTE
- текст: либо сама строка, либо целевая ячейка, в которой должна выглядеть функция.
- old_text: строка, которую вы хотите заменить.
- new_text: новая строка, которая заменит старую.
- [instance_num]: номер экземпляра старой строки, которую вы хотите заменить. Оставьте пустым, если хотите заменить все экземпляры.
Несмотря на то, что в официальном синтаксисе Excel используется термин текст, а не строка, вы можете использовать эту функцию как для чисел, так и для символов. Если вы хотите удалить символ, вы можете просто заменить его пробелом.
Как использовать функцию ПОДСТАВИТЬ
Нет ничего лучше, чем пример использования функции ПОДСТАВИТЬ. В этом конкретном примере у нас есть номер телефона с неправильным кодом страны (+98). Цель состоит в том, чтобы изменить этот код страны на правильный (+1) с помощью функции ПОДСТАВИТЬ.
- Выберите ячейку, в которой вы хотите отобразить замененную строку. В данном примере это будет A2.
- В строке формул введите приведенную ниже формулу и нажмите Enter. При этом будет просмотрена ячейка A2, найдена строка 98 и заменена на 1. Номер экземпляра 1 в последней части формулы означает, что только следует заменить первые 98.
- Теперь Excel изменит первые 98 на 1, изменив код страны для номера телефона. Вы можете удалить номер экземпляра и посмотреть, как он изменится, так как каждые 98 заменяются на 1.
Вложенные замены
Функция ПОДСТАВИТЬ не может заменить несколько строк в одной ячейке. Однако в одной и той же ячейке можно выполнить три отдельные замены. Хороший способ добиться этого — вложить функции друг в друга.
В этом примере у нас есть ячейка, содержащая три сокращения. Цель состоит в том, чтобы заменить их полными именами, которые они представляют.
- Выберите ячейку, в которой должны отображаться выходные данные. C2 в этом примере.
- В строке формул введите приведенную ниже формулу: Поскольку самая внутренняя функция заглядывает в ячейку A2, две другие тоже заглядывают в ячейку A2.
- Нажмите Enter. Теперь Excel заменит аббревиатуру полными именами.
Теперь вы можете использовать функцию ПОДСТАВИТЬ
Функция ПОДСТАВИТЬ позволяет удобно заменять символы в строках. Вы также можете определить, какое вхождение строки вы хотите заменить.
Эта функция также позволяет заменять строки пробелами или заменять пробелы строками. Вы также можете использовать функцию поиска «Найти и заменить» в Excel, чтобы найти и заменить элементы.
Функция ПОДСТАВИТЬ в Microsoft Excel — это удобный инструмент, который позволяет пользователям заменять один или несколько экземпляров определенного символа или текстовой строки другим символом или текстовой строкой. Чтобы увидеть, как это работает в электронной таблице, мы обсудим синтаксис и четыре аргумента функции ПОДСТАВИТЬ в Excel.
Далее мы попробуем свои силы в некоторых формулах ПОДСТАВКИ на нескольких примерах. ПОДСТАВИТЬ — это встроенная функция рабочего листа, относящаяся к категории строковых/текстовых функций в Excel. Функция ПОДСТАВИТЬ чувствительна к регистру и не поддерживает подстановочные знаки.
Оглавление
Синтаксис
Аргументы:
текст — это обязательный аргумент, в который вы вводите исходную текстовую строку, в которой вы хотите заменить символы, указанные в аргументе old_text. Чтобы указать значение для этого аргумента, вы можете использовать ссылку на ячейку, ввести текстовую строку или использовать возвращаемое значение, предоставленное другой формулой.
old_text — это обязательный аргумент, в который вы будете вводить символы или текстовую строку, которую хотите заменить.
new_text — это обязательный аргумент, в который вы вводите символы или текстовую строку, которой вы хотите заменить содержимое аргумента old_text.
nth_instance — это необязательный аргумент, который позволяет указать Excel, какой экземпляр символа (символов) или текстовой строки в аргументе old_text вы хотите заменить.Если этот параметр опущен, функция заменит все экземпляры символа(ов) или текстовой строки, введенной в аргументе old_text.
Важные характеристики функции ПОДСТАВИТЬ
- Функция ПОДСТАВИТЬ – это полезный инструмент, который позволяет заменить определенные символы или текстовую строку, введя их в аргумент old_text на символы или текстовую строку, введенные в аргумент new_text.
- Необязательный аргумент nth_instance позволяет выбрать конкретный экземпляр символа(ов) или текстовой строки, введенных в old_text. Пропуск этого аргумента приведет к замене всех экземпляров old_text предоставленными символами или текстовой строкой. в аргументе new_text.
- SUBSTITUTE — это функция, чувствительная к регистру и не поддерживающая подстановочные знаки.
Примеры функции ПОДСТАВИТЬ в Excel
Вот несколько основных примеров того, как вы можете включить и использовать функцию ПОДСТАВИТЬ в своем рабочем листе. Чтобы получить четкое представление о работе этих формул, откройте электронную таблицу и попробуйте использовать эти формулы вместе с примерами.
1. Заменить все экземпляры строки или символа
Давайте разберем инструкции формулы для Excel и посмотрим, как это выглядит при использовании слов. По сути, мы просим Excel: просмотреть содержимое ячейки A2 (текстовый аргумент) и найти все «1» (архив old_text) в этой ячейке. Затем замените все экземпляры «1» (поскольку мы не указали аргумент nth_instance) на «3» (аргумент new_text).
Здесь Excel заменит все экземпляры «1» и вернет «Этаж 3, Комната 3».
2. Замена определенного экземпляра строки или символа
Это та же самая формула, которую мы использовали в предыдущем примере, но с аргументом nth_instance, указанным как 2. Аргумент nth_instance ограничит замену только указанным экземпляром, который в нашем примере является вторым экземпляром "1" . Поэтому функция подставит только второй экземпляр и вернет "Этаж 1, Комната 3".
3. Чувствительность к регистру функции ПОДСТАВИТЬ
Обратите внимание, что формула заменяет текстовую строку только при вводе соответствующего регистра. Использование «room» вместо «Room» вообще не приводит к замене.
4. Вложенная формула ПОДСТАВИТЬ
Вложенная формула ПОДСТАВИТЬ позволяет нам заменить несколько текстовых строк с помощью одной формулы. Например, предположим, что мы хотим добавить текстовую строку «Нет». после слов «Этаж» и «Комната». Нам потребуется использовать следующие формулы:
Вместо этого мы объединяем эти формулы и вкладываем одну формулу ПОДСТАВИТЬ в другую, как показано выше. Вложенная формула будет состоять из двух шагов.
- Шаг 1. Сначала он будет работать с "вложенной" формулой, т. е. с той, что находится внутри квадратных скобок первой формулы ПОДСТАВИТЬ. Таким образом, формула сначала вычислит доход от замены текстовой строки «Этаж» на «Номер этажа». На данный момент возврат выглядит следующим образом: "Этаж №1, Комната 1"
- Шаг 2. Затем вторая формула ПОДСТАВИТЬ, т. е. та, что за скобками, ищет текстовую строку "Комната" в "Этаж № 1, Комната 1" (что является результатом шага 1) и заменяет «Комната» с «Комната №» чтобы дать нам желаемый результат: "Этаж №1, Комната №1″
Функция Excel ЗАМЕНИТЬ
Функция Excel REPLACE очень похожа на функцию ПОДСТАВИТЬ, ее можно использовать для замены последовательности символов в строке/тексте другим набором символов. Кратко рассмотрим синтаксис этой функции.
Синтаксис
Аргументы:
old_text — исходное строковое значение работает так же, как и в функции ПОДСТАВИТЬ.
start — второй аргумент — это позиция символов, которые вы хотите заменить.
number_of_chars — третий аргумент — это место, где вы вводите количество символов, которые хотите заменить, начиная с позиции, указанной во втором аргументе.
new_text — четвертый аргумент аналогичен аргументу new_text функции ПОДСТАВИТЬ.Это текстовая строка, которой вы хотите заменить указанные символы.
Пример функции замены Excel:
Единственная разница между функциями ПОДСТАВИТЬ и ЗАМЕНИТЬ заключается в методе определения строк, которые необходимо заменить или заменить. Давайте рассмотрим пример функции ЗАМЕНИТЬ, чтобы увидеть, как она использует другой метод для достижения цели, аналогичной функции ПОДСТАВИТЬ.
Как мы видим, формула возвращает "Этаж 2, Комната 1", так как мы заменили "1" на "2". Обратите внимание, что функция REPLACE не позволяет указать, какой экземпляр конкретного символа или текстовой строки мы хотим заменить. Это интуитивно понятно, потому что мы уже вводим позицию символа, а не сопоставляем указанный символ (символы) или текстовую строку, как с функцией ПОДСТАВИТЬ.
ЗАМЕНИТЬ ИЛИ ЗАМЕНИТЬ — когда что выбрать?
Если вы не знаете, какие символы в текстовой строке хотите заменить, но знаете положение этих символов в текстовой строке, используйте функцию ЗАМЕНИТЬ. Если независимо от позиции вы хотите изменить символ(ы) в текстовой строке, совпав с указанной текстовой строкой, используйте функцию ПОДСТАВИТЬ.
Я надеюсь, что эти примеры дали вам хорошее представление о том, как вы можете использовать функцию ПОДСТАВИТЬ в своих рабочих листах. Потратьте немного времени на эти формулы, и вы быстро станете их чемпионом. Скоро увидимся с другой мощной функцией Excel.
Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!
Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.
Функция ПОДСТАВИТЬ Находит и заменяет существующий текст новой текстовой строкой. С учетом регистра.
Чтобы использовать функцию рабочего листа Excel ПОДСТАВИТЬ, выберите ячейку и введите:
ЗАМЕНА просматривает строку в ячейке C2, находит «быстрый коричневый» и заменяет его на «медленный белый».
Как использовать ЗАМЕНУ
Функция ПОДСТАВИТЬ принимает 3 обязательных аргумента и 1 необязательный:
- Текст: текст, который вы хотите заменить символами.
- Old_text: текст, который вы хотите заменить
- Новый_текст: текст, которым вы хотите заменить Старый_текст.
- Instance_num (необязательно): указывает вхождение Old_text, которое вы хотите заменить на New_text. По умолчанию все вхождения Old_text будут заменены, если экземпляр не указан.
Вложенность SUBSTITUTE
Что, если бы нам пришлось заменить две части одной и той же строки? Давайте посмотрим, как можно объединить две функции ПОДСТАВИТЬ.
Обратите внимание, как результат ПОДСТАВИТЬ(C2,C3,C4) используется в качестве первого аргумента для второго ПОДСТАВИТЬ. Если разбить формулу на части:
=SUBSTITUTE(C2,C3,C4) оценивается как "медленная белая лиса перепрыгивает через ленивую собаку".
Затем ПОДСТАВЬТЕ("Медленный белый лис прыгает через ленивую собаку", C5,C6), что дает результат "Медленный белый лев прыгает через ленивую собаку".
Разница между SUBSTITUTE и REPLACE
Функция, аналогичная функции ЗАМЕНИТЬ, — ЗАМЕНИТЬ. В большинстве случаев их можно использовать взаимозаменяемо, однако применяется следующее эмпирическое правило.
Функция ПОДСТАВИТЬ должна использоваться, когда известна заменяемая строка текста или заменяется целое слово или слова, как в нашем примере с лисой, использованном ранее.
Функция ЗАМЕНА должна использоваться, когда известна позиция заменяемых символов в строке или когда заменяется часть слова или строки. Например, удаление дефиса из строки чисел.
Функция REPLACE также может быть вложенной, как и SUBSTITUTE, однако важно отметить, что относительное положение начального символа может меняться в зависимости от длины заменяемой строки на первом шаге вложенной формулы.
Например, замена "быстро коричневого" на "медленно белого" означает замену 11 символов на 10 символов. Поэтому, если он вложен, начальный символ для второго REPLACE будет на 1 символ от начальных позиций символов.
ЗАМЕНИТЬ советы
Обратите внимание, что число 3030 неверно, так как «20» и «20» в «2020» были заменены на «30».
Заинтересованы в дополнительных текстовых функциях?
Подробнее о ЗАМЕНЕ или о том, как используются другие текстовые функции в Excel, такие как ВЛЕВО, СРЕДН и ВПРАВО, см. в других наших статьях.
ЗАМЕНИТЬ в Google Таблицах
Функция ПОДСТАВИТЬ работает в Google Таблицах точно так же, как и в Excel:
Дополнительные примечания
Функция ЗАМЕНИТЬ чувствительна к регистру!. Вы можете использовать функции LOWER или UPPER для преобразования строк текста в согласованный регистр перед использованием функции SUBSTITUTE. Затем, при желании, вы можете использовать функцию PROPER, чтобы сделать первую букву каждого слова прописной.
Кроме того, вы можете использовать функции ПОИСК и ЗАМЕНА для имитации ЗАМЕНЫ без учета регистра. Используйте функцию ПОИСК, чтобы найти начальную позицию текстовой строки. Функция ПОИСК не чувствительна к регистру. Затем используйте функцию REPLACE, чтобы заменить текст. Вам нужно будет использовать результат функции SEARCH в качестве ввода start_num в REPLACE. Вам также нужно будет определить количество символов для замены ( num_chars ). Вы можете подсчитать их вручную или использовать функцию ДЛСТР для подсчета количества символов. Пример:
Заменить — удалить дефисы
Распространенный вопрос: как удалить дефисы из текста. Хитрость заключается в том, чтобы заменить дефис ничем.
Для этого примера предположим, что в ячейке A1 есть текст с дефисами. Вот как их удалить
<р>1. В пустой вспомогательной ячейке введите =ПОДСТАВИТЬ(2. Нажмите на ячейку, в которую вы хотите заменить символы, или A1 (это автоматически заполнит вашу формулу)
3. Введите (запятая)
4. Введите «-»
5. Введите (запятая)
6. Введите «» и нажмите Enter
Ваш конечный результат во вспомогательной ячейке должен выглядеть следующим образом:
= ПОДСТАВИТЬ(A1, "-","")
Еще 3 примера замены:
Глядя на ячейку B3, мы хотим заменить слово «больной» на «большой». Мы можем использовать следующее выражение:
Если текст, который мы хотим заменить, встречается более одного раза, нам нужно указать экземпляр, который мы имеем в виду. Во втором примере у нас есть два вхождения слова «старый». Итак, если мы хотим изменить второй экземпляр на слово «серый», мы должны использовать:
Обратите внимание: если вхождение не указано, заменяются все вхождения. Итак:
Избавится от всех экземпляров слова "старый" и заменит их словом "серый". Обратите внимание, что если старый текст не может быть найден, строка остается неизменной. Итак, взглянем на последний пример:
Означает, что мы пытаемся заменить слово "черный" словом "серый". Однако, поскольку «черный» не встречается, исходная строка левитра, поставляемая в США, осталась неизменной:
ЗАМЕНИТЬ примеры в VBA
Вы также можете использовать функцию ПОДСТАВИТЬ в VBA. Тип:
application.worksheetfunction.substitute(text,old_text,new_text,instance_num)
Для аргументов функции (текст и т. д.) вы можете либо ввести их непосредственно в функцию, либо определить переменные для использования вместо этого.
При выполнении следующего кода
Содержимое ячейки A1 примет следующий вид:
Итак, слово «использование» было заменено пустой строкой. Последний параметр (1) указывает, какой экземпляр текста следует заменить, если текст присутствует более одного раза. Так, например, если мы выполним следующий оператор
Ячейка A1 будет выглядеть так
Обратите внимание, что первое вхождение "the" было заменено на "an", а второе "the" не было затронуто. Если бы мы опустили последний параметр, оба слова «the» были бы заменены
Функция ПОДСТАВИТЬ может использоваться, когда вы хотите заменить текст новым указанным текстом в строке.
Что он возвращает
Он возвращает текстовую строку, в которой старый текст был заменен новым.
Синтаксис
=ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_экземпляра])
Входные аргументы
- текст – текст, который вы хотите изменить.
- old_text — текстовая строка, которую вы хотите заменить.
- новый_текст — новая текстовая строка, которой вы хотите заменить старый_текст.
- [instance_num] — (необязательно) количество раз, которое вы хотите заменить (в случае нескольких вхождений). Если этот аргумент не указан, подставляются все экземпляры old_text.
Дополнительные примечания
- Если указать номер экземпляра, заменяется только этот экземпляр старого текста. В противном случае каждое вхождение старого текста в текстовую строку заменяется новым текстом.
- Лучше всего использовать, когда нужно заменить определенный текст другим текстом.