Подстановочное значение Excel, если условие соответствует

Обновлено: 21.11.2024

В учебнике объясняются функции Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ с примерами их использования. Узнайте, как использовать функцию ЗАМЕНИТЬ с текстовыми строками, числами и датами, а также как вложить несколько функций ЗАМЕНИТЬ или ПОДСТАВИТЬ в одну формулу.

На прошлой неделе мы обсуждали различные способы использования функций НАЙТИ и ПОИСК на листах Excel. Сегодня мы более подробно рассмотрим две другие функции для замены текста в ячейке в зависимости от ее местоположения или замены одной текстовой строки другой в зависимости от содержимого. Как вы уже догадались, я говорю о функциях Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ.

Функция Excel ЗАМЕНИТЬ

Функция ЗАМЕНИТЬ в Excel позволяет заменить один или несколько символов в текстовой строке другим символом или набором символов.

Как видите, функция ЗАМЕНА в Excel имеет 4 аргумента, и все они обязательны.

Старый_текст — исходный текст (или ссылка на ячейку с исходным текстом), в котором вы хотите заменить некоторые символы.
Начальный_номер — позиция первого символа в старом_тексте, который вы хотите заменить.
Num_chars — количество символов, которые вы хотите заменить.
Новый_текст – замещающий текст.

Например, чтобы заменить слово "sun" на "son", можно использовать следующую формулу:

=ЗАМЕНИТЬ("солнце", 2, 1, "о")

И если вы поместите исходное слово в какую-либо ячейку, скажем, A2, вы можете указать ссылку на соответствующую ячейку в аргументе old_text:

=ЗАМЕНИТЬ(A2, 2, 1, "o")

Использование функции Excel REPLACE с числовыми значениями

Функция ЗАМЕНИТЬ в Excel предназначена для работы с текстовыми строками. Конечно, вы можете использовать его для замены цифровых символов, являющихся частью текстовой строки, например:

=REPLACE(A2, 7, 4, "2016")

Обратите внимание, что мы заключаем «2016» в двойные кавычки, как обычно делаем с текстовыми значениями.

Аналогичным образом вы можете заменить одну или несколько цифр в числе. Например:

И снова нужно заключить значение замены в двойные кавычки ("6").

Примечание. Формула ЗАМЕНИТЬ в Excel всегда возвращает текстовую строку, а не число. На снимке экрана выше обратите внимание на выравнивание по левому краю возвращаемого текстового значения в ячейке B2 и сравните его с исходным числом, выровненным по правому краю в ячейке A2. А поскольку это текстовое значение, вы не сможете использовать его в других вычислениях, пока не преобразуете его обратно в число, например, умножив на 1 или используя любой другой метод, описанный в разделе Как преобразовать текст в число.

Использование функции Excel REPLACE с датами

Как вы только что видели, функция ЗАМЕНИТЬ прекрасно работает с числами, за исключением того, что она возвращает текстовую строку :) Помня, что во внутренней системе Excel даты хранятся в виде чисел, вы можете попробовать использовать некоторые формулы замены дат. . Результаты будут весьма неловкими.

Например, у вас есть дата в формате A2, скажем, 1-окт-14, и вы хотите изменить "октябрь" на "ноябрь". Итак, вы пишете формулу REPLACE(A2, 4, 3, "Nov"), которая указывает Excel заменить 3 символа в ячейках A2, начиная с 4-го символа… и получили следующий результат:

Почему? Потому что «01-Oct-14» — это только визуальное представление базового серийного номера (41913), представляющего дату. Таким образом, наша формула замены изменяет последние 3 цифры в приведенном выше серийном номере на "Nov" и возвращает текстовую строку "419Nov".

Чтобы заставить функцию Excel ЗАМЕНИТЬ правильно работать с датами, вы можете сначала преобразовать даты в текстовые строки, используя функцию ТЕКСТ или любой другой метод, показанный в разделе Как преобразовать дату в текст в Excel. Кроме того, вы можете встроить функцию ТЕКСТ непосредственно в аргумент old_text функции ЗАМЕНИТЬ:

=ЗАМЕНИТЬ(ТЕКСТ(A2, "дд-ммм-гг"), 4, 3, "ноябрь")

Помните, что результатом приведенной выше формулы является текстовая строка, и поэтому это решение работает, только если вы не планируете использовать измененные даты в дальнейших расчетах. Если вам нужны даты, а не текстовые строки, используйте функцию DATEVALUE, чтобы преобразовать значения, возвращаемые функцией Excel REPLACE, обратно в даты:

=ДАТАЗНАЧ(ЗАМЕНИТЬ(ТЕКСТ(A2, "дд-ммм-гг"), 4, 3, "ноябрь"))

Вложенные функции REPLACE для выполнения нескольких замен в ячейке

Довольно часто может потребоваться выполнить несколько замен в одной и той же ячейке. Конечно, можно было сделать одну замену, вывести промежуточный результат в дополнительный столбец, а затем снова использовать функцию ЗАМЕНИТЬ. Однако лучший и более профессиональный способ — использовать вложенные функции REPLACE, которые позволяют выполнять несколько замен с помощью одной формулы. В этом контексте "вложение" означает помещение одной функции в другую.

Рассмотрите следующий пример. Предположим, у вас есть список телефонных номеров в столбце A, отформатированный как «123456789», и вы хотите сделать их более похожими на телефонные номера, добавив дефисы. Другими словами, ваша цель — превратить "123456789" в "123-456-789".

Вставить первый дефис очень просто. Вы пишете обычную формулу замены Excel, которая заменяет нулевые символы дефисом, т.е. добавляет дефис на 4-й позиции в ячейке:

Результат приведенной выше формулы замены выглядит следующим образом:

Хорошо, а теперь нам нужно вставить еще один дефис в 8-ю позицию. Для этого вы помещаете приведенную выше формулу в другую функцию Excel REPLACE. Точнее, вы вставляете его в аргумент old_text другой функции, чтобы вторая функция REPLACE обрабатывала значение, возвращаемое первой REPLACE, а не значение в ячейке A2:

В результате вы получаете телефонные номера в нужном формате:

Аналогичным образом вы можете использовать вложенные функции REPLACE, чтобы текстовые строки выглядели как даты, добавляя косую черту (/) там, где это необходимо:

=(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2,3,0,"/"),6,0,"/"))

Кроме того, вы можете преобразовать текстовые строки в реальные даты, заключив приведенную выше формулу ЗАМЕНИТЬ в функцию ДАТАЗНАЧ:

И, естественно, вы не ограничены в количестве функций, которые вы можете вложить в одну формулу (современные версии Excel 2010, 2013 и 2016 позволяют использовать до 8192 символов и до 64 вложенных функций в формуле).

Например, вы можете использовать 3 вложенные функции REPLACE, чтобы число в A2 отображалось как дата и время:

=REPLACE(REPLACE(REPLACE(REPLACE(A2,3,0,"/") ,6,0,"/"), 9,0, " "), 12,0, ":")

Замена строки, которая появляется в разных позициях в каждой ячейке

До сих пор во всех примерах мы имели дело со значениями схожего характера и производили замены в одной и той же позиции в каждой ячейке. Но реальные задачи часто бывают сложнее. В ваших рабочих листах заменяемые символы могут не обязательно появляться в одном и том же месте в каждой ячейке, и поэтому вам придется найти позицию первого символа, который необходимо заменить. Следующий пример продемонстрирует то, о чем я говорю.

Предположим, у вас есть список адресов электронной почты в столбце A. И название одной компании изменилось с "ABC" на, скажем, "BCA". Таким образом, вам необходимо соответствующим образом обновить адреса электронной почты всех клиентов.

Но проблема в том, что имена клиентов имеют разную длину, и поэтому вы не можете указать, где именно начинается название компании. Другими словами, вы не знаете, какое значение указать в аргументе start_num функции Excel REPLACE. Чтобы узнать это, используйте функцию Excel НАЙТИ, чтобы определить позицию первого символа в строке «@abc»:

Затем укажите указанную выше функцию НАЙТИ в аргументе start_num формулы ЗАМЕНИТЬ:

=ЗАМЕНИТЬ(A2, НАЙТИ("@abc",A2), 4, "@bca")

Совет. Мы включаем «@» в нашу формулу поиска и замены Excel, чтобы избежать случайных замен в части имени адресов электронной почты.Конечно, вероятность того, что такие совпадения произойдут, очень мала, и все же вы можете перестраховаться.

И мы хотим, чтобы формула возвращала исходный адрес электронной почты вместо ошибки. Итак, давайте заключим нашу формулу НАЙТИ И ЗАМЕНИТЬ в функцию ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ЗАМЕНИТЬ(A2, НАЙТИ("@abc",A2), 4, "@bca"),A2)

И эта улучшенная формула отлично работает, не так ли?

Совет. Если вы хотите произвести замену в исходных данных, проще использовать диалоговое окно Excel НАЙТИ и ЗАМЕНИТЬ.

Функция Excel ПОДСТАВИТЬ

Функция ПОДСТАВИТЬ в Excel заменяет один или несколько экземпляров заданного символа или текстовой строки указанными символами.

Синтаксис функции ПОДСТАВИТЬ в Excel следующий:

Первые три аргумента являются обязательными, а последний необязательным.

Текст — исходный текст, в котором вы хотите заменить символы. Может быть предоставлена в виде тестовой строки, ссылки на ячейку или результата другой формулы.
Old_text — символы, которые вы хотите заменить.
Новый_текст — новые символы для замены старого_текста.
Instance_num – экземпляр old_text, который вы хотите заменить. Если его не указать, каждое вхождение старого текста будет заменено новым текстом.

Например, все приведенные ниже формулы заменяют "1" на "2" в ячейке A2, но возвращают разные результаты в зависимости от того, какое число указано в последнем аргументе:

=SUBSTITUTE(A2, "1", "2", 1) — первое вхождение "1" заменяется на "2".

=SUBSTITUTE(A2, "1", "2", 2) — заменяет второе вхождение "1" на "2".

=SUBSTITUTE(A2, "1", "2") — заменяет все вхождения "1" на "2".

Примечание. Функция ПОДСТАВИТЬ в Excel чувствительна к регистру. Например, следующая формула заменяет все вхождения буквы «X» в верхнем регистре на «Y» в ячейке A2, но не заменяет ни одной буквы «x» в нижнем регистре.

Замена нескольких значений одной формулой (вложенная ПОДСТАВКА)

Как и в случае с функцией ЗАМЕНА Excel, вы можете вложить несколько функций ПОДСТАВИТЬ в одну формулу, чтобы выполнять несколько подстановок одновременно, т. е. заменять несколько символов или подстрок одной формулой.

Предположим, что у вас есть текстовая строка вида "PR1, ML1, T1" в ячейке A2, где "PR" означает "Проект", "ML" означает "Веха", а "T" означает "Задача". Вам нужно заменить три кода полными именами. Для этого вы можете написать 3 разные формулы ПОДСТАВКИ:

=ПОДСТАВИТЬ(A2, "ML", "Веха")

=ПОДСТАВИТЬ(A2, "Т", "Задание")

А затем вложите их друг в друга:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2,"PR","Проект"),"ML","Веха"),"T","Задача")

Обратите внимание, что мы добавили пробел в конце каждого аргумента new_text для удобства чтения.

ЗАМЕНА в Excel и ЗАМЕНА в Excel

Функции Excel REPLACE и SUBSTITUTE очень похожи друг на друга в том смысле, что обе предназначены для замены текстовых строк. Различия между этими двумя функциями заключаются в следующем:

SUBSTITUTE заменяет один или несколько экземпляров заданного символа или текстовой строки. Итак, если вы знаете текст, который нужно заменить, используйте функцию Excel ПОДСТАВИТЬ.
REPLACE изменяет символы в указанной позиции текстовой строки. Итак, если вы знаете положение заменяемых символов, используйте функцию Excel REPLACE.
Функция ПОДСТАВИТЬ в Excel позволяет добавить необязательный параметр (instance_num), указывающий, какое вхождение old_text следует заменить на new_text.

Вот как вы используете функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel. Надеюсь, эти примеры окажутся полезными при решении ваших задач. Я благодарю вас за чтение и надеюсь увидеть в нашем блоге на следующей неделе!

Power Query (M) значительно упростил многие действия по преобразованию данных, и замена значений — одна из них. Вы можете легко щелкнуть правой кнопкой мыши любое нужное значение в Power Query, будь то в Excel или Power BI, или в других компонентах Power Platform в целом, и просто заменить это значение любым желаемым вариантом. Однако замена значений на основе определенных условий поначалу может показаться не такой уж простой. Я видел, как многие разработчики Power Query (M) добавляли новые столбцы для достижения этой цели. Но добавление нового столбца — не всегда хорошая идея, особенно если вы можете сделать это за один простой шаг в Power Query. В этом посте я покажу вам быстрый и простой способ, который может помочь вам справиться со многими различными сценариями замены значений.

Представьте, что у вас есть таблица, как показано ниже, и вам нужно заменить столбец значений [B] значениями столбца [C], если [A] = [B].

Один из способов – добавить новый условный столбец со следующей логикой:

Ну, это прекрасно работает, но подождите, вы добавляете новый столбец, верно? Не лучше ли обработать описанный выше простой сценарий без добавления нового столбца? Если ваш ответ да, продолжайте читать.

Вы можете использовать пользовательский интерфейс редактора запросов для создания замены значения в Power Query, чтобы избежать опечаток, проблем с чувствительностью к регистру и т. д. И, честно говоря, проще использовать пользовательский интерфейс, когда это возможно, чем вводить длинные M-коды. В любом случае… Давайте удалим новый столбец, который мы создали ранее, и рассмотрим второй сценарий.

Щелкните правой кнопкой мыши значение в столбце B и выберите "Заменить значения"

Замените выбранное значение любым желаемым значением. В моем примере я заменил 5 на 1000.

Все, что вам нужно сделать сейчас, это изменить код с правильной логикой. Давайте рассмотрим логику, мы хотим проверить каждое значение столбца [B] в каждом отдельном ряду таблицы и заменить его значением столбца [C], только если [B] = [A ].

Я выделил слово "каждый", так как это важное ключевое слово в Power Query.

Поэтому нам нужно только изменить код Power Query, как показано ниже:

Поэтому окончательный код будет таким:

Приведенный выше код находит значение столбца [A] в [B], если они равны, то заменяет значение столбца [B] значением столбца [C].

Чтобы узнать больше о том, как ссылаться в Power Query, посмотрите мой предыдущий пост здесь.

Представьте, что мы используем power query для очистки данных и должны заменить значения как часть процесса преобразования.

Для каждого значения, которое мы хотим заменить, мы должны создать в запросе шаг «Заменить значения». Если у нас есть десятки или даже сотни значений для замены, это может быть довольно утомительно.

Если у нас уже есть список значений, которые нужно заменить, было бы проще сделать все это за один шаг на основе списка!

В этом посте мы узнаем, как выполнить массовый поиск и замену в мощном запросе на основе списка значений.

Видеоруководство

Настройка

В этом примере у нас есть небольшая таблица с одним столбцом, который называется Должность, и эта таблица называется MyData. Это данные, которые мы собираемся преобразовать с помощью нашей функции поиска и замены.

У нас также есть таблица с двумя столбцами под названием «Найти и заменить», которая называется MyFindReplace. Это таблица пар элементов, которые нужно найти и заменить.

На самом деле столбцы могут называться как угодно, так как мы не будем обращаться к ним по имени. Важно то, что столбец поиска находится слева, а столбец замены — справа.

Затем нам нужно импортировать эти таблицы в power query. Перейдите на вкладку «Данные», затем нажмите команду «Из таблицы/диапазона», чтобы импортировать их в редактор запросов Power.

Использование замены значений в Power Query

В редакторе расширенных запросов мы можем выполнить шаг «Заменить значения» на вкладке «Преобразование» или в контекстном меню.

Выберите столбец, в котором мы хотим заменить значения, перейдите на вкладку «Преобразование» и нажмите команду «Заменить значения».

Мы также можем получить доступ к этой команде, щелкнув правой кнопкой мыши заголовок столбца и выбрав в меню Заменить значения.

Любая из этих опций открывает меню «Заменить значения», после чего мы можем ввести значение для поиска и значение для замены.

Это заменит все экземпляры this во всем столбце.

Если мы посмотрим на сгенерированный из этого М-код, то увидим, что для выполнения замены используется функция запроса мощности Table.ReplaceValue.

Мы также будем использовать эту функцию для нашего решения, но нет простого способа использовать эту функцию на основе списка значений. Для этого нам потребуется создать собственную пользовательскую функцию запроса.

M-код для функции запроса

В предыдущем посте о воспроизведении функции TRIM Excel в запросе мощности мы рассмотрели шаги по созданию функции запроса. Нам нужно будет выполнить те же действия с приведенным выше M-кодом.

Эта функция запроса принимает 3 аргумента.

DataTable — это таблица, содержащая столбцы, в которых мы хотим найти и заменить значения.
FindReplaceTable — это таблица с двумя столбцами. Первый столбец содержит значения для поиска, а второй столбец содержит значения для их замены. Каждая строка в таблице состоит из одной пары значений поиска и замены.
DataTableColumn — это список имен столбцов, в которых мы хотим найти и заменить значения.

Затем функция преобразует нашу FindReplaceTable в список пар поиска и замены, и мы перебираем их и применяем функцию Table.ReplaceValue к каждой паре.

Как использовать эту функцию

То, как мы будем использовать эту функцию запроса, будет немного отличаться от нашего примера с функцией TRIM. В этом случае мы применили функцию к каждой строке столбца, добавив пользовательский столбец.

Чтобы использовать эту функцию, нам нужно применить ее ко всему столбцу.

Нам нужно добавить шаг запроса. В запросе, в котором мы хотим использовать эту функцию, мы можем щелкнуть маленький значок fx справа от строки формул.

Тогда мы можем войти в вышеуказанную функцию.

Первый аргумент будет ссылаться на предыдущий шаг запроса. В нашем случае это был шаг под названием Changed Type.

Второй аргумент будет ссылаться на имя запроса, содержащего таблицу значений поиска и замены. В нашем случае это называлось MyFindReplace.

Третий аргумент будет ссылаться на имя столбца, в котором мы хотим заменить значения. Он должен быть в формате списка с использованием фигурных скобок. В нашем случае это был .

Обратите внимание, что с помощью этого аргумента мы можем применить поиск и замену к нескольким столбцам. Например, размещение третьего аргумента применит поиск и замену как к столбцам Должность, так и к столбцам Описание работы.

Выводы

Проявив некоторую изобретательность, мы можем создать функцию, которая может выполнять массовый поиск и замену шагов на основе списка значений.

Ключевым моментом является создание рекурсивной функции на основе индекса списка значений поиска и замены.

Мы даже можем использовать это решение для одновременного поиска и замены в нескольких столбцах, чтобы сэкономить еще больше времени.

Как найти и заменить значения больше/меньше определенного значения в Excel?

Возможно, на вашем листе есть несколько данных, теперь вы хотите найти все ячейки, содержащие значение больше или меньше определенного числа, а затем заменить их другим значением, которое вам нужно сразу. Обычная функция «Найти и заменить» не применяется для решения этой проблемы. Есть ли у вас другие хорошие предложения по решению этой задачи?

Поиск и замена значений больше/меньше определенного значения с помощью кода VBA

Например, я найду все значения больше 500 и заменю их на 0.

Следующий код VBA может помочь вам заменить значения, которые больше или меньше определенного значения на единицу, сделайте следующее:

<р>1. Удерживая нажатыми клавиши ALT + F11, откроется окно Microsoft Visual Basic для приложений.

<р>2. Нажмите «Вставить» > «Модуль» и вставьте следующий код в окно модуля.

Код VBA: поиск и замена значений больше или меньше определенного значения

<р>3. Затем нажмите клавишу F5, чтобы запустить этот код, и выберите данные диапазона, которые вы хотите заменить, в окне подсказки, см. снимок экрана:

<р>4. Затем нажмите OK, и вы увидите, что все значения больше 500 заменены на 0.

Примечание. В приведенном выше коде вы можете изменить следующие переменные по своему усмотрению.

Найдите и замените значения больше / меньше определенного значения с помощью Kutools for Excel

Если вы не знакомы с кодом, я могу рассказать об удобном инструменте — Kutools for Excel, с его функцией «Выбрать определенные ячейки», вы можете быстро выбрать нужные ячейки, а затем сразу заменить их.

После установки Kutools for Excel выполните следующие действия:

<р>1. Выберите диапазон данных, который вы хотите найти и заменить.

<р>2. Нажмите Kutools > Выбрать > Выбрать определенные ячейки, см. снимок экрана:

<р>3. В диалоговом окне «Выбор определенных ячеек» выберите параметр «Ячейка» в разделе «Тип выбора», выберите «Больше, чем» в раскрывающемся списке «Конкретный тип» и введите значение, которое вы хотите использовать, в текстовом поле, кроме «Больше», см. снимок экрана:

<р>4. Затем нажмите кнопку «ОК», все ячейки больше 500 были выбраны немедленно, затем введите значение, которое нужно заменить, и одновременно нажмите клавиши Ctrl + Enter, все выбранные значения были заменены одновременно. Смотрите скриншоты:

Примечание. В диалоговом окне "Выбор определенных ячеек" можно использовать другие критерии, например "Меньше чем", "Равно", "Содержит" и т. д.

Демонстрация: поиск и замена значений больше / меньше определенного значения с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Скачать и бесплатно попробовать прямо сейчас!

Читайте также: