Значение из нескольких слов необходимо поместить внутри: Использование функции ВПР (VLOOKUP) для подстановки значений

Содержание

3 способа склеить текст из нескольких ячеек

18025 28.10.2012 Скачать пример

Надпись на заборе: «Катя + Миша + Семён + Юра + Дмитрий Васильевич +
товарищ Никитин + рыжий сантехник + Витенька + телемастер Жора +
сволочь Редулов + не вспомнить имени, длинноволосый такой +
ещё 19 мужиков + муж = любовь!»

Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ

В категории Текстовые есть функция СЦЕПИТЬ (CONCATENATE), которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом. Например, вот так:

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

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

СЦЕПИТЬ пришла ее более совершенная версия с похожим названием и тем же синтаксисом — функция СЦЕП (CONCAT). Ее принципиальное отличие в том, что теперь в качестве аргументов можно задавать не одиночные ячейки, а целые диапазоны — текст из всех ячеек всех диапазонов будет объединен в одно целое:

Для массового объединения также удобно использовать новую функцию ОБЪЕДИНИТЬ (TEXTJOIN), появившуюся начиная с Excel 2016. У нее следующий синтаксис:

=ОБЪЕДИНИТЬ(Разделитель; Пропускать_ли_пустые_ячейки; Диапазон1; Диапазон2 … )

где

Разделитель — символ, который будет вставлен между фрагментами
Второй аргумент отвечает за то, нужно ли игнорировать пустые ячейки (ИСТИНА или ЛОЖЬ)
Диапазон 1, 2, 3 … — диапазоны ячеек, содержимое которых хотим склеить

Например:

Способ 2.

Символ для склеивания текста (&)

Это универсальный и компактный способ сцепки, работающий абсолютно во всех версиях Excel.

Для суммирования содержимого нескольких ячеек используют знак плюс «+«, а для склеивания содержимого ячеек используют знак «&» (расположен на большинстве клавиатур на цифре «7»). При его использовании необходимо помнить, что:

Этот символ надо ставить в каждой точке соединения, т.е. на всех «стыках» текстовых строк также, как вы ставите несколько плюсов при сложении нескольких чисел (2+8+6+4+8)

Если нужно приклеить произвольный текст (даже если это всего лишь точка или пробел, не говоря уж о целом слове), то этот текст надо заключать в кавычки. В предыдущем примере с функцией СЦЕПИТЬ о кавычках заботится сам Excel — в этом же случае их надо ставить вручную.

Вот, например, как можно собрать ФИО в одну ячейку из трех с добавлением пробелов:

Если сочетать это с функцией извлечения из текста первых букв — ЛЕВСИМВ (LEFT), то можно получить фамилию с инициалами одной формулой:

Способ 3.

Макрос для объединения ячеек без потери текста.

Имеем текст в нескольких ячейках и желание — объединить эти ячейки в одну, слив туда же их текст. Проблема в одном — кнопка Объединить и поместить в центре (Merge and Center) в Excel объединять-то ячейки умеет, а вот с текстом сложность — в живых остается только текст из верхней левой ячейки.

Чтобы объединение ячеек происходило с объединением текста (как в таблицах Word) придется использовать макрос. Для этого откройте редактор Visual Basic на вкладке Разработчик — Visual Basic (Developer — Visual Basic) или сочетанием клавиш Alt+F11, вставим в нашу книгу новый программный модуль (меню

Insert — Module) и скопируем туда текст такого простого макроса:

Sub MergeToOneCell()
    Const sDELIM As String = " "     'символ-разделитель
    Dim rCell As Range
    Dim sMergeStr As String
    If TypeName(Selection) <> "Range" Then Exit Sub   'если выделены не ячейки - выходим
    With Selection
        For Each rCell In . Cells
            sMergeStr = sMergeStr & sDELIM & rCell.Text  'собираем текст из ячеек
        Next rCell
        Application.DisplayAlerts = False   'отключаем стандартное предупреждение о потере текста
        .Merge Across:=False                'объединяем ячейки
        Application.DisplayAlerts = True
        .Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM))    'добавляем к объед.ячейке суммарный текст
    End With
End Sub

Теперь, если выделить несколько ячеек и запустить этот макрос с помощью сочетания клавиш Alt+F8 или кнопкой Макросы на вкладке Разработчик (Developer — Macros), то Excel объединит выделенные ячейки в одну, слив туда же и текст через пробелы.

Ссылки по теме

Делим текст на куски
Объединение нескольких ячеек в одну с сохранением текста с помощью надстройки PLEX
Что такое макросы, как их использовать, куда вставлять код макроса на VBA

Функция СЦЕПИТЬ — Служба поддержки Майкрософт

Excel

Формулы и функции

Другие функции

Функция СЦЕПИТЬ

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. ..Меньше

Используйте функцию

СЦЕПИТЬ, относящуюся к текстовым функциям, для объединения двух или нескольких текстовых строк в одну.

Важно: В Excel 2016, Excel Mobile и Excel в Интернете эта функция была заменена функцией СКАЙТ. Функция СЦЕПИТЬ пока еще доступна для обратной совместимости, однако рекомендуем использовать вместо нее функцию СЦЕП. В последующих версиях Excel функция СЦЕПИТЬ может быть недоступна.

Синтаксис: СЦЕПИТЬ(текст1;[текст2];…)

Ниже приведены примеры.

Имя аргумента	Описание
текст1 (обязательный)	Первый элемент для объединения. Это может быть текстовое значение, число или ссылка на ячейку.
текст2,… (необязательные)	Дополнительные текстовые элементы для объединения. Можно указать до 255 элементов и до 8 192 символов.

Имя аргумента

Описание

текст1 (обязательный)

Первый элемент для объединения. Это может быть текстовое значение, число или ссылка на ячейку.

текст2,… (необязательные)

Дополнительные текстовые элементы для объединения. Можно указать до 255 элементов и до 8 192 символов.

Примеры

Чтобы использовать эти примеры в Excel, скопируйте данные из приведенной ниже таблицы и вставьте их на новый лист в ячейку A1.

Данные
вида	Виталий	Токарев
речная форель	Fourth	Pine
32
Формула	Описание
=СЦЕПИТЬ(«Популяция рек для «;A2;» «;A3;» составляет «;A4;» на километр. «)	Создает предложение, объединяя данные в столбце А с остальным текстом. Результат: «Популяция рек для вида речная форель составляет 32 на километр».
=СЦЕПИТЬ(В2;» «;C2)	Объединяет строку в ячейке В2, пробел и значение в ячейке С2. Результат: «Виталий Токарев».
=СЦЕПИТЬ(С2;», «;В2)	Объединяет текст в ячейке C2, строку, состоящую из запятой и пробела, и значение в ячейке B2. Результат: «Виталий Токарев».
=СЦЕПИТЬ(B3;» & «;C3)	Объединяет строку в ячейке B3, строку, состоящую из пробела, амперсанда и еще одного пробела, и значение в ячейке C3. Результат: Fourth & Pine.
=B3 & » & » & C3	Объединяет те же элементы, что и в предыдущем примере, но с помощью оператора & (амперсанд) вместо функции СЦЕПИТЬ. Результат: Fourth & Pine.

Распространенные неполадки

Проблема	Описание
Кавычки отображаются в строке результата.	Используйте точку с запятой для разделения текстовых элементов. Например: Excel отобразит результат функции =СЦЕПИТЬ («Привет, «»мир!») как «Привет,»мир!» с лишней кавычкой, так как пропущена точка с запятой между текстовыми аргументами. Числа не нужно заключать в кавычки.
Слова расположены впритык друг к другу.	Если не поместить пробелы между текстовыми элементами, в строке результата они будут отображаться слитно. Добавьте пробелы в формулу с функцией СЦЕПИТЬ. Это можно сделать двумя способами. Добавьте двойные кавычки с пробелом между ними (» «). Например: =СЦЕПИТЬ(«Привет,»; » «; «мир!»). Добавьте пробел после *текстового* аргумента. Например: =СЦЕПИТЬ(«Привет, «; «мир!»). К строке «Привет,» добавлен пробел.
Ошибка #ИМЯ? отображается вместо ожидаемого результата.	Ошибка #ИМЯ? обычно означает, что для *текстового* аргумента не хватает кавычек.

Необходимые действия	Описание
Вместо функции СЦЕПИТЬ используйте знак & (амперсанд).	С помощью оператора & можно объединять текстовые элементы, не прибегая к функции. Например, =A1 & B1 возвращает то же значение, что и =С CONCATENATE(A1;B1). Во многих случаях воспользоваться оператором амперсанда проще и быстрее, чем создавать строки с помощью функции СЦЕПИТЬ. См. дополнительные сведения об использовании операторов вычислений.
Для объединения и форматирования строк используйте функцию ТЕКСТ.	Функция ТЕКСТ преобразует числовое значение в текст и объединяет числа с текстом или символами. Предположим, что ячейка A1 содержит число 23,5. Чтобы отформатировать число как сумму в рублях, можно использовать следующую формулу: =ТЕКСТ(A1;»0,00 ₽») Результат: 23,5 ₽

Дополнительные сведения

Не удалось прочитать значение аргумента командной строки из нескольких слов в bash

спросил 1 год, 11 месяцев назад

Изменено 1 год, 11 месяцев назад

Просмотрено 145 раз

Я работаю над написанием сценария оболочки bash, который имеет 4 аргумента командной строки. 3/4 аргумента — это одно слово, а 1/4 — это несколько слов. Мне удалось получить значения для 2 из них, но я не могу получить правильные значения для 3-го и четвертого. Однако, если я уберу аргумент о нескольких работах, это сработает.

 options=$(getopt -l "help,env:,site:,cluster:,cluster-group:" -o "he:s:c:cg:" -a -- "$@")
набор eval -- "$options"
пока правда
делать
    случае $ 1 в
        -h|--помощь)
            показатьПомощь
            выход 0
            ;;
        -e|--env)
            сдвиг
            экспортная среда = $ 1
            ;;
        -s|--сайт)
            сдвиг
            сайт экспорта = $1
            ;;
        -c|--кластер)
            сдвиг
            экспортный кластер = $1
            ;;
        -cg|--кластерная-группа)
            сдвиг
            экспортировать cluster_group=$1
            ;;
        --)
            сдвиг
            перерыв;;
    эсак
    сдвиг
сделанный
эхо $среда
эхо $ сайт
эхо $кластер
эхо $cluster_group

При запуске sh b. sh -s S1 -e E1 -c C1 -cg CG1 вывод:

 E1
S1
г

Что я делаю не так?

bash
командная строка
аргументы командной строки

В соответствии с man getopt(1) : -o распознает только односимвольных опций.

 -o, --options короткие опции
              Короткие (односимвольные) варианты, которые необходимо распознать. Если
              эта опция не найдена, первый параметр getopt
              который не начинается с "-" (и не вариант
              аргумент) используется в качестве короткой строки параметров. Каждый короткий
              после символа опции в ярлыках может следовать одно двоеточие
              чтобы указать, что у него есть обязательный аргумент, и двумя двоеточиями
              чтобы указать, что у него есть необязательный аргумент. Первый
              характер коротких опций может быть «+» или «-», чтобы влиять на
              как анализируются параметры и генерируется вывод (см. 
              раздел РЕЖИМЫ СКАНИРОВАНИЯ).

Итак, в вашем случае вы можете указать только одну букву, например: g после опции -o для кластерной группы .

 options=$(getopt -l "help:,env:,site:,cluster:,cluster-group:" -o "h:e:s:c:g:" -a -- "$@")

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

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

найти и заменить сразу несколько значений

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

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

Найти и заменить несколько значений вложенной ПОДСТАВКОЙ

Самый простой способ найти и заменить несколько записей в Excel — использовать функцию ПОДСТАВИТЬ.

Логика формулы очень проста: вы пишете несколько отдельных функций, чтобы заменить старое значение новым. А затем вы вкладываете эти функции одну в другую, так что каждая последующая ПОДСТАВКА использует вывод предыдущей ПОДСТАВКИ для поиска следующего значения.

ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ( текст , старый_текст1 , новый_текст1 ), старый_текст2 , новый_текст2 ), старый_текст3 , новый_текст3 )

Предположим, вы хотите заменить сокращенные названия стран (например, FR , UK и USA ) в списке местоположений в A2:A10 полными названиями.

Для этого введите старые значения в D2:D4 и новые значения в E2:E4, как показано на скриншоте ниже. Затем поместите приведенную ниже формулу в ячейку B2 и нажмите Enter:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2:A10, D2, E2), D3, E3), D4, E4)

… и все замены будут сделаны сразу:

Обратите внимание, описанный выше подход работает только в Excel 365 , который поддерживает динамические массивы.

В преддинамических версиях Excel 2019, Excel 2016 и более ранних версиях формулу необходимо записать для самой верхней ячейки (B2), а затем скопировать в следующие ячейки:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2, $D$2, $E$2), $D$3, $E$3), $D$4, $E$4)

Обратите внимание, что в этом случае мы блокируем значения замены абсолютными ссылками на ячейки, чтобы они не сместились при копировании формулы вниз.

Примечание. Функция ПОДСТАВИТЬ имеет значение с учетом регистра , что означает, что вы должны вводить старые значения ( old_text ) в том же регистре букв, что и в исходных данных.

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

Преимущества : простота реализации; поддерживается во всех версиях Excel

Недостатки : лучше всего использовать для ограниченного числа значений поиска/замены

Поиск и замена нескольких записей с XLOOKUP

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

Допустим, у вас есть список стран в столбце A, и вы хотите заменить все сокращения соответствующими полными названиями. Как и в предыдущем примере, вы начинаете с ввода элементов «Найти» и «Заменить» в отдельных столбцах (D и E соответственно), а затем вводите эту формулу в B2:

=XLOOKUP(A2, $D$2:$D$4, $E$2:$E$4, A2)

В переводе с языка Excel на человеческий язык вот что делает формула:

Поиск значения A2 (lookup_value) в D2:D4 (lookup_array) и возврат совпадения из E2:E4 (return_array). Если не найдено, извлеките исходное значение из A2.

Дважды щелкните маркер заполнения, чтобы скопировать формулу в ячейки ниже, и результат не заставит себя ждать:

Поскольку функция XLOOKUP доступна только в Excel 365, приведенная выше формула не будет работать в более ранних версиях. Однако вы можете легко имитировать это поведение с помощью комбинации IFERROR или IFNA и VLOOKUP:

=ЕСЛИНА(ВПР(A2, $D$2:$E$4, 2, ЛОЖЬ), A2)

Примечание. В отличие от ПОДСТАВКИ, функции XLOOKUP и VLOOKUP не чувствительны к регистру , что означает, что они ищут значения поиска, игнорируя регистр букв. Например, наша формула заменит FR и fr на France .

Преимущества : необычное использование обычных функций; работает во всех версиях Excel

Недостатки : работает на уровне ячейки, не может заменить часть содержимого ячейки

Множественная замена с использованием рекурсивной функции LAMBDA

Для подписчиков Microsoft 365 в Excel предусмотрена специальная функция, позволяющая создавать пользовательские функции с использованием традиционного языка формул. Да, я говорю о LAMBDA. Прелесть этого метода в том, что он может преобразовать очень длинную и сложную формулу в очень компактную и простую. Более того, он позволяет создавать собственные функции, которых нет в Excel, что раньше было возможно только в VBA.

Подробную информацию о создании и использовании пользовательских функций LAMBDA см. в этом руководстве: Как писать функции LAMBDA в Excel. Здесь мы обсудим пару практических примеров.

Преимущества : результатом является элегантная и удивительно простая в использовании функция, независимо от количества заменяемых пар

Недостатки : доступно только в Excel 365; зависит от рабочей книги и не может использоваться повторно в разных рабочих книгах

Пример 1. Поиск и замена сразу нескольких слов/строк

Чтобы заменить несколько слов или текста за один раз, мы создали пользовательскую функцию LAMBDA с именем MultiReplace , которая может принимать одну из следующих форм:

=LAMBDA(текст, старый, новый, ЕСЛИ(старый<>"", Множественная замена(ПОДСТАВИТЬ(текст, старый, новый), СМЕЩ(старый, 1, 0), СМЕЩ(новый, 1, 0)), текст ))

или

=LAMBDA(текст, старый, новый, ЕСЛИ(старый="", текст, Множественная замена(ПОДСТАВИТЬ(текст, старый, новый), СМЕЩ(старый, 1, 0), СМЕЩ(новый, 1, 0))) )

Обе являются рекурсивными функциями, которые вызывают сами себя. Разница лишь в том, как устанавливается точка выхода.

В первой формуле функция ЕСЛИ проверяет, не пуст ли список старых (старые<>«»). Если TRUE, вызывается функция MultiReplace . Если FALSE, функция возвращает текст в своей текущей форме и завершает работу.

Вторая формула использует обратную логику: если old пусто (old=»»), то вернуть текст и выход; в противном случае звоните MultiReplace .

Самое сложное сделано! Что вам осталось сделать, так это назвать функцию MultiReplace в диспетчере имен, как показано на снимке экрана ниже. Подробные рекомендации см. в разделе Как назвать функцию LAMBDA.

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

Какой бы из двух вариантов формулы вы ни выбрали, с точки зрения конечного пользователя синтаксис будет таким же простым:

MultiReplace(текст, старый, новый)

Где:

Текст — исходные данные
Старый — значения для поиска
Новый — значения для замены на

Продолжая предыдущий пример, давайте заменим не только аббревиатуры стран, но и аббревиатуры штатов. Для этого введите аббревиатуры ( старых значений ) в столбце D, начиная с D2, и полные имена (новые значения ) в столбце E, начиная с E2.

В B2 введите функцию MultiReplace:

= Множественная замена(A2:A10, D2, E2)

Нажмите Enter и наслаждайтесь результатами 🙂

Как работает эта формула

Ключом к пониманию формулы является понимание рекурсии. Это может показаться сложным, но принцип довольно прост. С каждой итерацией рекурсивная функция решает один маленький экземпляр более крупной проблемы. В нашем случае Функция MultiReplace перебирает старые и новые значения и в каждом цикле выполняет одну замену:

MultiReplace (ЗАМЕНИТЬ(текст, старый, новый), OFFSET(старый, 1, 0), OFFSET(новый, 1, 0))

Как и во вложенных функциях SUBSTITUTE, результат предыдущей SUBSTITUTE становится текстовым параметром для следующей SUBSTITUTE. Другими словами, при каждом последующем вызове MultiReplace функция ПОДСТАВИТЬ обрабатывает не исходную текстовую строку, а вывод предыдущего вызова.

Чтобы обработать все элементы в списке old , мы начинаем с самой верхней ячейки и используем функцию OFFSET для перемещения на 1 строку вниз при каждом взаимодействии:

СМЕЩЕНИЕ(старое, 1, 0)

То же самое делается для нового списка :

СМЕЩЕНИЕ(новое, 1, 0)

Крайне важно обеспечить точку выхода , чтобы предотвратить бесконечное выполнение рекурсивных вызовов. Делается это с помощью функции ЕСЛИ — если старая ячейка пуста, функция возвращает текст в его нынешнем виде и завершает работу:

=LAMBDA(текст, старый, новый, ЕСЛИ(старый="", текст, Множественная замена(…)))

или

=LAMBDA(текст, старый, новый, ЕСЛИ(старый<>"", Множественная замена(…), текст))

Пример 2. Замена нескольких символов в Excel

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

Если вы предпочитаете вводить старые символы в одну ячейку и новые символы в другую или вводить их непосредственно в формулу, вы можете создать другую пользовательскую функцию с именем ReplaceChars , используя одну из следующих формул:

=LAMBDA(текст, старые_символы, новые_символы, ЕСЛИ(старые_символы<>"", Заменитьсимволы(ПОДСТАВИТЬ(текст, ЛЕВЫЙ(старые_символы), ЛЕВЫЙ(новые_символы)), ПРАВЫЙ(старые_символы, ДЛСТР(старые_символы)-1), ПРАВЫЙ( new_chars, LEN(new_chars)-1)), текст))

или

=LAMBDA(текст, старые_символы, новые_символы, ЕСЛИ(старые_символы="", текст, Заменитьсимволы(ПОДСТАВИТЬ(текст, ЛЕВЫЙ(старые_символы), ЛЕВЫЙ(новые_символы)), ПРАВЫЙ(старые_символы, ДЛСТР(старые_символы)-1), ПРАВЫЙ (new_chars, LEN(new_chars)-1))))

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

И ваша новая пользовательская функция готова к использованию:

ReplaceChars(текст, старые_символы, новые_символы)

Где:

Текст — исходные строки
Старый — символы для поиска
Новый — символы заменить на

Чтобы проверить это в полевых условиях, давайте сделаем то, что часто выполняется с импортированными данными — заменим умные кавычки и умные апострофы прямыми кавычками и прямыми апострофами.

Сначала мы вводим умные кавычки и умный апостроф в D2, прямые кавычки и прямой апостроф в E2, разделяя символы пробелами для лучшей читаемости. (Поскольку мы используем один и тот же разделитель в обеих ячейках, это не повлияет на результат — Excel просто заменит пробел пробелом.)

После этого вводим в ячейку B2 эту формулу:

= Заменить символы (A2: A4, D2, E2)

И получить именно те результаты, которые мы искали:

Можно также вводить символы непосредственно в формулу. В нашем случае просто не забудьте «дублировать» прямые кавычки, например:

=ReplaceChars(A2:A4, "" '", """ "" '")

Как работает эта формула

Функция ReplaceChars циклически проходит через old_chars и new_chars строк и выполняет одну замену за раз, начиная с первого символа слева. Эта часть выполняется функцией ПОДСТАВИТЬ:

ПОДСТАВИТЬ(текст, ВЛЕВО(старые_знаки), ВЛЕВО(новые_символы))

На каждой итерации функция RIGHT удаляет один символ слева от строк old_chars и new_chars , чтобы LEFT могла получить следующую пару символов для замены:

ReplaceChars(ПОДСТАВИТЬ(текст, ЛЕВЫЙ(старые_символы), ЛЕВЫЙ(новые_символы)), ПРАВЫЙ(старые_символы, ДЛСТР(старые_символы)-1), ПРАВЫЙ(новые_символы, ДЛСТР(новые_символы)-1))

Перед каждым рекурсивным вызовом функция ЕСЛИ оценивает строку old_chars . Если он не пуст, функция вызывает сама себя. Как только последний символ был заменен, итерационный процесс завершается, формула возвращает текст в ее нынешнем виде и завершается.

Примечание. Поскольку функция ПОДСТАВИТЬ, используемая в наших основных формулах, равна с учетом регистра , обе лямбды ( MultiReplace и ReplaceChars ) обрабатывают прописные и строчные буквы как разные символы.

Массовый поиск и замена на UDF

Если функция LAMBDA недоступна в вашем Excel, вы можете написать определяемую пользователем функцию для множественной замены традиционным способом с помощью VBA.

Чтобы отличить UDF от определенной в LAMBDA функции MultiReplace , мы назовем ее по-другому, скажем, 9.0079 Массовая замена . Код функции следующий:

Функция MassReplace(InputRng As Range, FindRng As Range, ReplaceRng As Range) As Variant() Dim arRes() As Variant ‘массив для хранения результатов Dim arSearchReplace(), sTmp As String ‘массив, где хранить пары поиска/замены, временная строка Dim iFindCurRow, cntFindRows As Long ‘индекс текущей строки массива SearchReplace, количество строк Dim iInputCurRow, iInputCurCol, cntInputRows, cntInputCols As Long ‘индекс текущей строки в исходном диапазоне, индекс текущего столбца в исходном диапазоне, количество строк, количество столбцов cntInputRows = InputRng. Rows.Count cntInputCols = InputRng.Columns.Count cntFindRows = FindRng.Rows.Count ReDim arRes (1 в cntInputRows, 1 в cntInputCols) ReDim arSearchReplace(1 To cntFindRows, 1 To 2) ‘подготовка массива пар поиска/замены Для iFindCurRow = 1 Для cntFindRows arSearchReplace(iFindCurRow, 1) = FindRng.Cells(iFindCurRow, 1).Value arSearchReplace(iFindCurRow, 2) = ReplaceRng.Cells(iFindCurRow, 1).Value Следующий ‘Поиск и замена в исходном диапазоне Для iInputCurRow = 1 Для cntInputRows Для iInputCurCol = 1 Для cntInputCols sTmp = InputRng.Cells(iInputCurRow, iInputCurCol).Value ‘Замена всех пар поиска/замены в каждой ячейке Для iFindCurRow = 1 Для cntFindRows sTmp = Заменить(sTmp, arSearchReplace(iFindCurRow, 1), arSearchReplace(iFindCurRow, 2)) Следующий arRes(iInputCurRow, iInputCurCol) = sTmp Следующий Следующий MassReplace = arRes Конечная функция

Подобно функциям, определенным в LAMBDA, пользовательские функции охватывают рабочую книгу . Это означает, что функция MassReplace будет работать только в той рабочей книге, в которую вы вставили код. Если вы не знаете, как это сделать правильно, выполните действия, описанные в разделе Как вставить код VBA в Excel.

После добавления кода в книгу функция появится в формуле intellisense — только имя функции, а не аргументы! Хотя я считаю, что запомнить синтаксис не так уж и сложно:

MassReplace(входной_диапазон, найти_диапазон, заменить_диапазон)

Где:

Input_range — исходный диапазон, где вы хотите заменить значения.
Диапазон поиска — символы, строки или слова для поиска.
Replace_range — символы, строки или слова для замены.

В Excel 365, благодаря поддержке динамических массивов, это работает как обычная формула, которую нужно ввести только в верхнюю ячейку (B2):

= Массовая замена (A2: A10, D2: D4, E2: E4)

В предварительно динамическом Excel это работает как формула массива CSE в старом стиле: вы выбираете весь исходный диапазон (B2:B10), вводите формулу и одновременно нажимаете клавиши Ctrl + Shift + Enter, чтобы завершить ее.

Преимущества : достойная альтернатива пользовательской функции LAMBDA в Excel 2019, Excel 2016 и более ранних версиях

Недостатки : рабочая книга должна быть сохранена в виде файла .xlsm с поддержкой макросов

Массовая замена в Excel макросом VBA

Если вам нравится автоматизировать общие задачи с помощью макросов, вы можете использовать следующий код VBA для поиска и замены нескольких значений в диапазоне.

Sub BulkReplace() Dim Rng как диапазон, SourceRng как диапазон, ReplaceRng как диапазон При ошибке Возобновить Далее Установить SourceRng = Application.InputBox(«Исходные данные:», «Массовая замена», Application.Selection.Address, Тип:=8) Очистить Если не SourceRng ничего, тогда Установите ReplaceRng = Application.InputBox («Заменить диапазон:», «Массовая замена», Тип: = 8) Очистить Если Not ReplaceRng ничего не значит, тогда Application.ScreenUpdating = Ложь Для каждого Rng в ReplaceRng. Columns(1).Cells SourceRng.Replace what:=Rng.Value, replace:=Rng.Offset(0, 1).Value Следующий Application.ScreenUpdating = Истина Конец, если Конец, если Конец сабвуфера

Чтобы сразу же использовать макрос, вы можете загрузить нашу рабочую тетрадь, содержащую код. Или вы можете вставить код в свою книгу.

Как использовать макрос

Перед запуском макроса введите старое и новое значения в два соседних столбца, как показано на изображении ниже (C2:D4).

Затем выберите исходные данные, нажмите Alt + F8, выберите макрос BulkReplace и нажмите Запустить .

Как источник ярости 9Предварительно выбран 0125, просто проверьте ссылку и нажмите OK:

После этого выберите заменить диапазон и нажмите OK:

Готово!

Преимущества : установка один раз, повторное использование в любое время

Недостатки : макрос необходимо запускать при каждом изменении данных

Множественный поиск и замена в Excel с помощью инструмента Substring

В самом первом примере я упомянул, что вложенная ПОДСТАВКА — самый простой способ заменить несколько значений в Excel. Я признаю, что ошибался. Наш Ultimate Suite делает вещи еще проще!

Чтобы выполнить массовую замену на листе, перейдите на вкладку Ablebits Data и нажмите Инструменты подстроки > Заменить подстроки .

Появится диалоговое окно Заменить подстроки с предложением определить диапазон Источник и диапазон Подстроки .

Выбрав два диапазона, нажмите кнопку Заменить и найдите результаты в новом столбце, вставленном справа от исходных данных. Да, это так просто!

Совет. Прежде чем нажимать Заменить , обратите внимание на одну важную вещь — поле с учетом регистра. Обязательно выберите его, если вы хотите обрабатывать прописные и строчные буквы как разные символы. В этом примере мы отмечаем эту опцию, потому что мы хотим заменить только строки с заглавными буквами и оставить нетронутыми подстроки, такие как «fr», «uk» или «ak», в других словах.

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

3 способа склеить текст из нескольких ячеек

Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ

Способ 2.

Способ 3.

Ссылки по теме

Функция СЦЕПИТЬ — Служба поддержки Майкрософт

Примеры

Распространенные неполадки

Рекомендации

Дополнительные сведения

Не удалось прочитать значение аргумента командной строки из нескольких слов в bash

Зарегистрируйтесь или войдите в систему

Опубликовать как гость

Опубликовать как гость

найти и заменить сразу несколько значений

Найти и заменить несколько значений вложенной ПОДСТАВКОЙ

Поиск и замена нескольких записей с XLOOKUP

Множественная замена с использованием рекурсивной функции LAMBDA

Пример 1. Поиск и замена сразу нескольких слов/строк

Пример 2. Замена нескольких символов в Excel

Массовый поиск и замена на UDF

Массовая замена в Excel макросом VBA

Как использовать макрос

Множественный поиск и замена в Excel с помощью инструмента Substring

Добавить комментарий Отменить ответ