Предположим, что нам необходимо заменить все "ул." на листе на "улица ". Или русское слово "дом" на английское "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 - по всему тексту" - данные из столбца А будут заменять только в том случае, если ячейка в выделенном для замены диапазоне полностью совпадает со значением из столбца А листа "Соответствия". Например, в любой из выделенных ячеек записано "На столе книга", а на листе "Соответствия" в столбце А есть только слово "книга". Замена не будет произведена, т.к. необходимо, чтобы в столбце А было так же "На столе книга".
- если указать "2 - по части ячейки" - данные из столбца А будут заменять в случае, если ячейка в выделенном для замены диапазоне содержит любое слово из столбца А листа "Соответствия". На том же примере - "На столе книга". Если выбрать 2, то в тексте "На столе книга" слово книга будет заменено на слово из столбца В - "book".
И еще один практический пример чуть модифицированного кода. Предположим, имеется таблица выручки по реализации продукции:
Как видно, здесь присутствую только номера статей, но нет их расшифровки. Зато расшифровка есть в отдельном листе "Справочник":
Как видно, в справочнике присутствуют нужные номера статей и можно было бы применить ту же ВПР(VLOOKUP) для замен. Если бы не одно но: в таблице по реализации помимо номеров статьей есть еще лишний текст "Статья затрат:". Конечно, можно сначала заменить этот текст, потом в отдельном столбце применить ВПР, заменить формулу значениями и вернуть в исходный столбец. Если при этом надо еще оставить текст "Статья затрат:", то надо будет сделать еще доп.манипуляции либо при составлении формулы, либо после. В любом случае - слишком много лишних телодвижений. А значит бОльшие времязатраты.
Приложенный ниже файл поможет сделать это в разы быстрее:
Массовая замена слов - статьи.xls (91,5 КиБ, 2 937 скачиваний)
и в итоге за пару секунд получим следующий результат:

Достаточно выделить столбец со статьями на листе с реализацией и запустить код(либо нажатием кнопки заменить значения, либо нажав Alt+F8 и выбрав из списка макросов макрос
После нажатия на кнопку будут запрошены следующие параметры:
- указать номер столбца значений в листе "Справочник", в котором искать соответствия номерам статей(в нашем случае это столбец 1(А))
- указать номер столбца, значениями которого заменять найденные в таблице реализации значения(это может быть один из трех столбцов справочника: Группа затрат, Статья затрат, Подстатьи затрат). Логичнее всего указать столбец 4, т.к. он наиболее детализирован и конкретнее указывает расшифровку статьи
- далее будет предложено указать точность поиска:
- если указать "
1 - по всему тексту " - данные будут заменены только в том случае, если значение ячейки в выделенном для замены диапазоне полностью совпадает со значением из столбца А листа "Справочник". Т.е. если бы у нас в таблице реализации был бы записан только номер статьи(1.01), тогда можно было бы указать именно 1 - если указать "
2 - по части ячейки " - данные будут заменены только в том случае, если значение ячейки в выделенном для замены диапазоне содержит любое значение из столбца А листа "Справочник". Это больше подходит к описанному случаю, т.к. нам необходимо заменить исключительно номер статьей на их расшифровку, оставив при этом текст "Статья затрат: "
- если указать "
Если все указано корректно, то на листе будут произведены все необходимые замены.
Возможные ошибки, которые предусмотрены кодом и о которых будет сообщено соответствующим сообщением(код прервется, замены не будут произведены):
- на листе Справочник нет значений
- в качестве столбца для поиска значений и для замены значений на листе Справочник указано одно и то же число
- в качестве столбца значений для замены указано число, превышающее общее количество столбцов на листе Справочник
Особое внимание хочу уделить случаю, когда выбирается замена по части ячейки. В этом случае лучше список на листе Справочник отсортировать по длине текста по тому столбцу, в котором будут значения для поиска. Зачем это надо: т.к. значение по части ячейки будет заменять не полное соответствие, то есть вероятность неверных замен. Например, есть текст
Чтобы получить длину строки текста можно использовать функцию
В отличие от кода, приведенного в начале статьи, код во втором файле позволяет производить замену не только на основании двух столбцов, но и ориентируясь на таблицу данных, как видно из реализации. Можно выбрать любой столбец Справочника для поиска значений и так же любой для замены, что предоставляет большую гибкость по замене значений.
Так же см.:
Замена значений по списку в PowerQuery
Найти в ячейке любое слово из списка
Замена ссылок в формулах на их значения
Как удалить строки по условию?
Есть ли возможность в коде макроса учесть символ символ начала / конца строки?
Для ситуаций типа:
"[Начало строки]НИ " -> "Настольная игра ", но чтобы только_такие НИ заменялись.
Или типа ",[конец строки]" -> "" (удал лишнюю запятую в конце)
Здравствуйте. Скажите пожалуйста, а можно ли как-то заменить сразу несколько похожих слов на одно правильное? Например заменить в столбце А слова: Б-ца №2, Боль-ца 2, Б-ЦА 2, Гор. больн. №2 и.т. д. на Больница №2?
Геннадий, вроде логичный ответ напрашивается: в списке для замены в качестве искомого записываете поочредно все нужные значения("Гор. больн. №2","Боль-ца 2","Б-ца №2") и напротив каждого записываете одно итоговое "Больница №2"
Дмитрий, спасибо за код, вот только с моей задачей справляется не всегда. Точнее корректирует по нескольку раз. Мне нужно вносить коррекции в Г-код для чпу станка. и нужно чтоб распознавание заменяемого слова происходило от пробела до пробела, либо чтоб в той строке где прошли исправления уже коррективы не вносились. Например нужно чтоб менялось только S1, а меняется и если S11 и S15 и так далее.
Александр, для Вашей задачи, судя по описанию, надо применять иной подход. Код из статьи здесь не совсем подходит.
Здравствуйте, а можно ли как то увеличить время замены? Просто очень много данных нужно поменять, и всегда появляеться эта ошибкаhttp://joxi.ru/v29RxWlHz6EzvA
Олександр, а причем тут время замены? На какой строке ошибка появляется? Может у Вас в ячейке просто значение ошибки записано, вот код и спотыкается на этой строке.
Использую ваш макрос уже долго, но всегда возникает такая проблема, повторный перевод.
Например,
Дано:
37
50
Соответствия:
37 = 50
50 = 1
В результате:
1
1
А нужно:
50
1
Что нужно поменять или дописать, чтобы замена происходила только 1 раз на весь текст?
Олег, не поверите - но надо поменять местами...Логику-то попытайтесь понять: сначала Вы заменяете 37 на 50, а потом у Вас другое условие: 50 на 1. Если сначала будете менять 50 на 1, то все должно быть без проблем.
Ого, а так можно было... Спасибо
Здравствуйте!
При запуске первого скрипта, который "Массовая замена слов" он ищет в первой графе (А) первую строку с совпадением на листе Справочника, а как его заставить спрашивать с какой строки начать поиск совпадения?
Александр, если честно - не совсем понятно, что именно запрашивать. Если первую строку на листе Справочника, то просто укажите нужную строку здесь:
например, если надо искать с третьей строки, то будет выглядеть так:
Можете это оформить и через InputBox, так же, как и с направлением перевода:
Dim lrow as long
Добрый день. Макрос хороший. Но вопрос нужно выделить цветом, то что заменено. Как это сделать?
Алексей, если надо отметить те ячейки, в которых были заменены какие-либо замены сделаны, то можно так:
после строки
записываете такие строки:
а строку замены:
дополняете так же заменой формата:
Selection.Replace s, avArr(lr, lToReplaceCol), lLookAt, ReplaceFormat:=True
если же хотите видеть именно те слова, которые были заменены - то здесь надо весь код переписывать кардинально.
Есть такие данные в ячейках
школу ГРЕМЯЧЕВскую школу №2
школу ГРЕМЯЧЕВскую школу №1
школу ШИРОКОВскую школу
школу СЕМЁНОВскую школу
школу УЗУНОВскую школу
Надо сделать замену так, чтобы получилось
ГРЕМЯЧЕВскую школу №2
ГРЕМЯЧЕВскую школу №1
ШИРОКОВскую школу
СЕМЁНОВскую школу
УЗУНОВскую школу
Как найти нашёл в ячейку поиска пишем "школу*школу". А вот что вставить в поле замены, чтобы получилось то, что нужно?
В данном случае нет массовой замены и никакие коды не нужны. Нужно просто подключить смекалку :)
Обычной заменой сначала заменяете " школу " на "_школу_", затем "школу " на ""(пусто). Затем обратно "_школу_" на " школу ". При необходимости добавить так же замену " школу" на "_школу_"(если не везде после второго слова "школу" есть пробел).
Смысл понял, но забыл сказать, что есть вот такие строки
школу №6
школу №16 Д. КУЛИШИ
школу №20 ИМЕНИ И.И. НАЙЗАРА
школу С. ЛЕНИНСКИЕ ЗОРИ
и у них трогать первую "школу" нельзя. Т.е. надо уничтожить первое вхождение "школу" там где "школу блаблабла школу".
Думаю, что додумал. "школу №" заменю на что-то. Спасибо!