Lost your password?


Хитрости »
Основные понятия (25)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (18)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (67)
Разное (42)
Баги и глюки Excel (4)

Массовая замена слов

Предположим, что нам необходимо заменить все "ул." на листе на "улица ". Или русское слово "дом" на английское "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 KiB, 8 381 скачиваний)


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

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

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

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


и в итоге за пару секунд получим следующий результат:
Результат замены
Достаточно выделить столбец со статьями на листе с реализацией и запустить код(либо нажатием кнопки заменить значения, либо нажав 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
Найти в ячейке любое слово из списка
Замена ссылок в формулах на их значения
Как удалить строки по условию?


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 61 комментарий
  1. Антон:

    Отличная статья.
    А возможно ли выполнить замену следующим образом: к примеру есть словосочетание "книга book". Теперь провести замену таким образом чтобы слово книга заменилась на book, а book заменилось на книга. В итоге получился бы результат "book книга"?
    Так сказать массовая параллельная замена, а не последовательная)

    Заранее спасибо!

    • То, что Вы хотите - это не параллельная замена, а нелогичная :) Для данного кода нелогичная.
      Т.к. слова в одной ячейке - то код просто не поймет что из этого надо заменять в данный момент, а что нет. Для таких вещей надо писать отдельный код, учитывая все нюансы(как я понял слов-то может быть больше двух)

  2. Юлия:

    Очень и очень полезно! Давно искал! Скажите как выполнить замену по всей книге, а не просто по выделенному листу?

    • В этом случае необходимо делать дополнительный цикл по листам книги. Но тут надо понимать, что либо по всему диапазону листов будет замена, либо для каждого отдельного листа по выделенному диапазону. Для выделенного как-то так:

      'заменяем
      Dim wsSh as Worksheet
          For lr = 1 To UBound(avArr, 1)
              s = avArr(lr, lToFindCol)
              If Len(s) Then 'если значение для замены не пустое
                  For each wsSh in ThisWorkbook.WorkSheets
                      wsSh.Activate
                      Selection.Replace s, avArr(lr, lToReplaceCol), lLookAt
                  next
              End If
          Next lr
      • Юлия:

        Благодарю за ответ. Вы знаете, я профан в макросах, я просто готовлю словарь, чтобы применять его к документам с большим количеством листов. Вы бы могли, чуть-чуть по-подробнее сказать, как я могу применить макрос ко всему документу? Была бы Вам очень признательна.

  3. Юлия:

    Не отвечайте! Я разобралась с Вашим объяснением! Внесла деньги на развитие ресурса! Благодарю!

  4. Виктор:

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

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

      • Виктор:

        Я попробовал код. У меня ситуация следующая: огромный список ссылок вида домен/страница и есть список доменов, по которому нужно удалить страницы. После включения макроса из ячеек удаляется только домен, а страница остается. Приведу пример: нужно удалить из ячеек всю информацию где содержится домен wikipedia.org, после включения макроса остается: https:///wiki/Операционный_усилитель_с_токовой_обратной_связью, а хочется что бы ячейка была пустой.

        • Итак. Создаете список для удаления, в котором применяете подстановочные знаки(например "*книга*". Вместо слов для замены оставляете пустые ячейки. При запуске кода указываете полное соответствие(1 - т.е. по всей ячейке, как и при стандартной замене).
          Плюс посмотрите статью: Как удалить строки по условию?
          возможно удалять сразу будет удобнее.

  5. Эдуард:

    Дмитрий(Админ), здравствуйте. Скажите а можно такое реализовать? :

    Подправить это место With ThisWorkbook.Sheets(«Соответствия») так чтобы по нажатию кнопки поиск велся по неск. книгам.

    У меня листы в книги следующие: общие соответствия/форма1(лист с кнопкой макроса)/соответствия1/форма2/соответствия2/форма3/соответствия3...
    1) нужно чтобы поиск велся так: нажимаю кнопку в «форма1» поиск идет в листах «соответствия1» и «общие соответствия», нажимаю кнопку в «форма2» поиск идет в листах «соответствия2» и «общие соответствия» и т.д. листов «соответствия» может быть много. Или может чтоб не плодить листы просто создать одну «форму» и в ней сделать несколько кнопок макроса которые так и назвать «форма1», «форма2»… ?

    2) можно ли в коде прописать чтобы макрос учитывал регистр? Т.е. в листах «соответствия» я могу разместить по 2 варианта регистра (строчн. и прописн.) ну и в зависимости от того какой регистр текста на перевод, чтоб такой же регистр макрос и подставлял.
    Денежку сейчас сразу отправлю

  6. Борис:

    Добрый день.

    Помогите внести изменения в ваш макрос:

    1. Запуск макроса нужен автоматически по изменению "таблицы замен" или столбца А листа 3
    2. Обрабатывать нужно не "выделенные области", а конкретный диапазон А13 - А16, надо убрать выбор области.
    3. Класть результат замены надо в соседний столбец (то есть в B13 - B16), оставляя исходные данные в ячейках A13 - A16 неизменными

    Как это реализовать?

  7. А если нужно заменить одно слово на большой кусок текста?

  8. Алексей:

    Как сделать точное совпадения слов?

  9. Ivan:

    Добрый день!
    Макрос работает замечательно для небольшого количества требуемых для замены слов. но когда мне надо заменить в районе 1500 ФИО на их логины в сети, то макрос не работает. Как можно это проблему решить?

    • Иван, а теперь представим, что у меня все работает и с 1500 и с 3000 :) Как проблему решить? Вы вдумайтесь в написанное Вами: что значит не работает? Ошибка появляется, замены не производятся, заменяется не то и т.д. Опишите проблему более детально - я же не сижу у Вас за спиной, когда Вы там свои ФИО заменяете. Спасибо.

  10. Спасибо! вы спасли мою **пу))
    оч. срочно нужно было сделать массовый поиск и замену, ваш файлик оооочень помог!

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2021 Excel для всех   Войти