Предположим, что нам необходимо заменить все "ул." на листе на "улица ". Или русское слово "дом" на английское "house". Или еще интереснее: все английские буквы на русские. Например, английская "а" должна быть заменена на русскую "a", английская "c" на русскую "с", английская "H" на русскую "Н" и т.д. А такое тоже нередко бывает и доставляет проблемы. Ведь если в одной таблице будут русские буквы, а в другой английские - то применение большинства встроенных функций поиска(та же ВПР) просто не найдут соответствия.
Если подобную замену надо сделать для одного сочетания, то все просто: жмем Ctrl+H и указываем что заменить и на что. Но если таких замен надо сделать 20? Или 120? Это надо будет 120 раз нажать и ввести что заменять и на что. А если это надо сделать еще и не в одном документе - то...Думаю сами справитесь с умножением количества замен на количество файлов, в которых это надо сделать. И вроде бы простая операция превращается в ваш личный ад на работе.
Недавно на форуме участнику потребовалось автоматизировать именно такую штуку. Т.к. код несложный - решил написать и чуть дополнив выложить для всех кому код может потребоваться:

Option Explicit
Sub Replace_Mass()
    Dim s As String
    Dim lCol As Long
    Dim avArr, lr As Long
    Dim lLastR As Long
    Dim lToFindCol As Long, lToReplaceCol As Long, lLookAt As Long
 
    'запрашиваем направление перевода - с русского на англ. или наоборот
    lCol = Val(InputBox("Укажите направление перевода:" & vbNewLine & _
                    "   1 - ru-en" & vbNewLine & _
                    "   2 - en-ru", "Запрос", 1))
    If lCol = 0 Then Exit Sub
    'запрашиваем по части ячейки искать или по всему тексту
    'по умолчанию - по части
    lLookAt = Val(InputBox("Искать соответствие по части ячейки или по всему тексту:" & vbNewLine & _
                    "   1 - по всему тексту" & vbNewLine & _
                    "   2 - по части ячейки", "Запрос", 2))
    If lLookAt = 0 Then Exit Sub
 
    Select Case lCol
    Case 1
        lToFindCol = 1
        lToReplaceCol = 2
    Case 2
        lToFindCol = 2
        lToReplaceCol = 1
    End Select
 
    Application.ScreenUpdating = 0
    'Получаем с листа Соответствия значения, которые надо заменить в выделенном диапазоне
    With ThisWorkbook.Sheets("Соответствия")
        lLastR = .Cells(.Rows.Count, 1).End(xlUp).Row
        avArr = .Cells(1, 1).Resize(lLastR, 2)
    End With
    'заменяем
    For lr = 1 To UBound(avArr, 1)
        s = avArr(lr, lToFindCol)
        If Len(s) Then 'если значение для замены не пустое
            Selection.Replace s, avArr(lr, lToReplaceCol), lLookAt
        End If
    Next lr
    Application.ScreenUpdating = 1
End Sub

Как это работает. В книге есть специальный лист с именем "Соответствия". На нем в столбце А записаны слова, которые необходимо заменить, а в столбце В - на что эти слова заменить. Если в столбце А пусто - то замена не будет произведена. Если в столбце В пусто - то значение из столбца А будет просто удалено.
Замены производятся исключительно в выделенных на листе ячейках. Ячейки могут быть несмежными.

Итак, необходимо сделать много замен. Скачиваете файл:

  Массовая замена слов (54,5 КиБ, 10 564 скачиваний)


Примечание: Я сделал файл как переводчик. Т.е. в первом столбце русские слова, во втором английские. Но в столбцах может быть что угодно - хоть слова, хоть символы, хоть числа.
На лист "Соответствия" записываете в столбец А - что заменять, в столбец В - на что заменять. Переходите на лист книги, в котором необходимо произвести замену. Выделяете ячейки, значения в которых надо найти и заменить. После чего жмете Alt+F8 и выбираете макрос "Tips_Macro_ReplaceMASS.xls!Replace_Mass"(или просто "Replace_Mass", если код в той же книге, что и ячейки для поиска и замены).
Первым появится окно с запросом направления перевода. По умолчанию 1(ru-en). Т.е. будет браться слово из столбца А и заменяться словом из столбца В. Но если указать 2 - то будет браться слово из столбца В и заменяться словом из столбца А. Т.е. аналог переводчика - с рус. на англ. и наоборот. Либо из А в В, либо из В в А.
Вторым появится запрос на метод просмотра данных:

  • если указать "1 - по всему тексту" - данные из столбца А будут заменять только в том случае, если ячейка в выделенном для замены диапазоне полностью совпадает со значением из столбца А листа "Соответствия". Например, в любой из выделенных ячеек записано "На столе книга", а на листе "Соответствия" в столбце А есть только слово "книга". Замена не будет произведена, т.к. необходимо, чтобы в столбце А было так же "На столе книга".
  • если указать "2 - по части ячейки" - данные из столбца А будут заменять в случае, если ячейка в выделенном для замены диапазоне содержит любое слово из столбца А листа "Соответствия". На том же примере - "На столе книга". Если выбрать 2, то в тексте "На столе книга" слово книга будет заменено на слово из столбца В - "book".

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

  Массовая замена слов - статьи.xls (91,5 КиБ, 2 937 скачиваний)


и в итоге за пару секунд получим следующий результат:
Результат замены
Достаточно выделить столбец со статьями на листе с реализацией и запустить код(либо нажатием кнопки заменить значения, либо нажав Alt+F8 и выбрав из списка макросов макрос Replace_Mass).
После нажатия на кнопку будут запрошены следующие параметры:

  1. указать номер столбца значений в листе "Справочник", в котором искать соответствия номерам статей(в нашем случае это столбец 1(А))
  2. указать номер столбца, значениями которого заменять найденные в таблице реализации значения(это может быть один из трех столбцов справочника: Группа затрат, Статья затрат, Подстатьи затрат). Логичнее всего указать столбец 4, т.к. он наиболее детализирован и конкретнее указывает расшифровку статьи
  3. далее будет предложено указать точность поиска:
    • если указать "1 - по всему тексту" - данные будут заменены только в том случае, если значение ячейки в выделенном для замены диапазоне полностью совпадает со значением из столбца А листа "Справочник". Т.е. если бы у нас в таблице реализации был бы записан только номер статьи(1.01), тогда можно было бы указать именно 1
    • если указать "2 - по части ячейки" - данные будут заменены только в том случае, если значение ячейки в выделенном для замены диапазоне содержит любое значение из столбца А листа "Справочник". Это больше подходит к описанному случаю, т.к. нам необходимо заменить исключительно номер статьей на их расшифровку, оставив при этом текст "Статья затрат: "

Если все указано корректно, то на листе будут произведены все необходимые замены.
Возможные ошибки, которые предусмотрены кодом и о которых будет сообщено соответствующим сообщением(код прервется, замены не будут произведены):

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

Особое внимание хочу уделить случаю, когда выбирается замена по части ячейки. В этом случае лучше список на листе Справочник отсортировать по длине текста по тому столбцу, в котором будут значения для поиска. Зачем это надо: т.к. значение по части ячейки будет заменять не полное соответствие, то есть вероятность неверных замен. Например, есть текст "Статья затрат: 1.011". В то же время на листе Справочник есть статьи "1.01" и "1.011". Т.к. "1.01" идет раньше в большинстве случаев, то текст будет заменен некорректно: "Статья затрат: ТВ1".
Чтобы получить длину строки текста можно использовать функцию ДЛСТР(LEN):
=ДЛСТР(A2)
=LEN(A2)

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

Так же см.:
Замена значений по списку в PowerQuery
Найти в ячейке любое слово из списка
Замена ссылок в формулах на их значения
Как удалить строки по условию?

Loading

69 комментариев

  1. Есть ли возможность в коде макроса учесть символ символ начала / конца строки?

    Для ситуаций типа:
    "[Начало строки]НИ " -> "Настольная игра ", но чтобы только_такие НИ заменялись.
    Или типа ",[конец строки]" -> "" (удал лишнюю запятую в конце)

  2. Здравствуйте. Скажите пожалуйста, а можно ли как-то заменить сразу несколько похожих слов на одно правильное? Например заменить в столбце А слова: Б-ца №2, Боль-ца 2, Б-ЦА 2, Гор. больн. №2 и.т. д. на Больница №2?

    1. Геннадий, вроде логичный ответ напрашивается: в списке для замены в качестве искомого записываете поочредно все нужные значения("Гор. больн. №2","Боль-ца 2","Б-ца №2") и напротив каждого записываете одно итоговое "Больница №2"

  3. Дмитрий, спасибо за код, вот только с моей задачей справляется не всегда. Точнее корректирует по нескольку раз. Мне нужно вносить коррекции в Г-код для чпу станка. и нужно чтоб распознавание заменяемого слова происходило от пробела до пробела, либо чтоб в той строке где прошли исправления уже коррективы не вносились. Например нужно чтоб менялось только S1, а меняется и если S11 и S15 и так далее.

  4. Использую ваш макрос уже долго, но всегда возникает такая проблема, повторный перевод.
    Например,
    Дано:
    37
    50

    Соответствия:
    37 = 50
    50 = 1

    В результате:
    1
    1

    А нужно:
    50
    1

    Что нужно поменять или дописать, чтобы замена происходила только 1 раз на весь текст?

    1. Олег, не поверите - но надо поменять местами...Логику-то попытайтесь понять: сначала Вы заменяете 37 на 50, а потом у Вас другое условие: 50 на 1. Если сначала будете менять 50 на 1, то все должно быть без проблем.

  5. Здравствуйте!
    При запуске первого скрипта, который "Массовая замена слов" он ищет в первой графе (А) первую строку с совпадением на листе Справочника, а как его заставить спрашивать с какой строки начать поиск совпадения?

    1. Александр, если честно - не совсем понятно, что именно запрашивать. Если первую строку на листе Справочника, то просто укажите нужную строку здесь:

      avArr = .Cells(1, 1).Resize(lLastR, 2)

      например, если надо искать с третьей строки, то будет выглядеть так:

      avArr = .Cells(3, 1).Resize(lLastR, 2)

      Можете это оформить и через InputBox, так же, как и с направлением перевода:
      Dim lrow as long

      lrow = Val(InputBox("Укажите номер первой строки:", "Запрос", 1))
      'а далее
      avArr = .Cells(lrow, 1).Resize(lLastR, 2)
    1. Алексей, если надо отметить те ячейки, в которых были заменены какие-либо замены сделаны, то можно так:
      после строки

      Application.ScreenUpdating = 0

      записываете такие строки:

      'обнуляем параметры замены
      Application.ReplaceFormat.Clear
      'назначаем цвет выделения для замененных ячеек - желтый
      Application.ReplaceFormat.Interior.Color = 65535

      а строку замены:

      Selection.Replace s, avArr(lr, lToReplaceCol), lLookAt

      дополняете так же заменой формата:

      Selection.Replace s, avArr(lr, lToReplaceCol), lLookAt, ReplaceFormat:=True

      если же хотите видеть именно те слова, которые были заменены - то здесь надо весь код переписывать кардинально.

  6. Есть такие данные в ячейках
    школу ГРЕМЯЧЕВскую школу №2
    школу ГРЕМЯЧЕВскую школу №1
    школу ШИРОКОВскую школу
    школу СЕМЁНОВскую школу
    школу УЗУНОВскую школу

    Надо сделать замену так, чтобы получилось
    ГРЕМЯЧЕВскую школу №2
    ГРЕМЯЧЕВскую школу №1
    ШИРОКОВскую школу
    СЕМЁНОВскую школу
    УЗУНОВскую школу

    Как найти нашёл в ячейку поиска пишем "школу*школу". А вот что вставить в поле замены, чтобы получилось то, что нужно?

    1. В данном случае нет массовой замены и никакие коды не нужны. Нужно просто подключить смекалку :)
      Обычной заменой сначала заменяете " школу " на "_школу_", затем "школу " на ""(пусто). Затем обратно "_школу_" на " школу ". При необходимости добавить так же замену " школу" на "_школу_"(если не везде после второго слова "школу" есть пробел).

      1. Смысл понял, но забыл сказать, что есть вот такие строки
        школу №6
        школу №16 Д. КУЛИШИ
        школу №20 ИМЕНИ И.И. НАЙЗАРА
        школу С. ЛЕНИНСКИЕ ЗОРИ

        и у них трогать первую "школу" нельзя. Т.е. надо уничтожить первое вхождение "школу" там где "школу блаблабла школу".

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

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.