Excel vba заменить содержимое конечных ячеек
Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.
В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:
Удобнее обращаться к ячейке через свойство рабочего листа Cells(номер строки, номер столбца), так как вместо номеров строк и столбцов можно использовать переменные. Обратите внимание, что при обращении к любой рабочей книге, она должна быть открыта, иначе произойдет ошибка. Закрытую книгу перед обращением к ней необходимо открыть.
Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:
Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере – это ячейка «A1».
Если мы обращаемся к ячейке на неактивном листе активной рабочей книги, тогда необходимо указать этот лист:
Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках – имя ярлыка.
Обращение к ячейке по индексу
К ячейке на рабочем листе можно обращаться по ее индексу (порядковому номеру), который считается по расположению ячейки на листе слева-направо и сверху-вниз.
Например, индекс ячеек в первой строке равен номеру столбца. Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе, зависящему от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее.
Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.
По индексу можно обращаться к ячейке не только на всем рабочем листе, но и в отдельном диапазоне. Нумерация ячеек осуществляется в пределах заданного диапазона по тому же правилу: слева-направо и сверху-вниз. Вот индексы ячеек диапазона Range(«A1:C3»):
Обращение к ячейке Range("A1:C3").Cells(5) соответствует выражению Range("B2") .
Обращение к ячейке по имени
Если ячейке на рабочем листе Excel присвоено имя (Формулы –> Присвоить имя), то обращаться к ней можно по присвоенному имени.
Допустим одной из ячеек присвоено имя – «Итого», тогда обратиться к ней можно – Range("Итого") .
Запись информации в ячейку
Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:
Хорошо видно, что один и тот же город или компания тут присутствуют в разношёрстных вариантах, что, очевидно, будет создавать массу проблем при работе с этими таблицами в будущем. И если чуть подумать, то можно найти массу примеров похожих задач из других областей.
А теперь представьте, что к вам такие кривые данные попадают регулярно, т.е. это не единичная история из серии "исправил вручную, забыл", а проблема на регулярной основе и в большом количестве ячеек.
Что же делать? Не заменять же вручную 100500 раз кривой текст на правильный через окошко "Найти и заменить" или нажимая Ctrl + H ?
Первое, что приходит в голову в подобной ситуации - произвести массовую замену по составленному заранее справочнику соответствия неправильных и правильных вариантов - вроде такого:
К сожалению, при очевидной распространенности подобной задачи, в Microsoft Excel не существует простых встроенных способов для её решения. Для начала, давайте разберёмся, как это делать формулами, без привлечения "тяжелой артиллерии" в виде макросов на VBA или Power Query.
Случай 1. Массовая полная замена
Начнём с относительно простого случая - ситуации, когда нужно заменить старый кривой текст на новый полностью.
Предположим, что у нас есть две таблицы:
В первой - исходные разномастные названия компаний. Во второй - справочник соответствия. Если находим в названии компании в первой таблице любое слово из столбца Найти, то нужно полностью заменить это кривое название на правильное - из столбца Заменить второй таблицы-справочника.
- Обе таблицы преобразованы в динамические ("умные") с помощью сочетания клавиш Ctrl + T или командой Вставка - Таблица (Insert - Table) .
- На появившейся вкладке Конструктор (Design) первой таблице присвоено имя Данные , а второй таблице-справочнику - Замены .
Чтобы объяснить логику формулы зайдём чуть издалека.
Взяв в качестве примера первую компанию из ячейки A2 и забыв временно про остальные компании, попробуем определить какой именно вариант из столбца Найти там встречается. Для этого выделим любую пустую ячейку в свободной части листа и введём туда функцию НАЙТИ (FIND) :
Эта функция определяет входит ли заданная подстрока (первый аргумент - все значения из столбца Найти) в исходный текст (первая компания из таблицы данных) и должна вывести на выходе либо порядковый номер символа, начиная с которого текст был найден, либо ошибку если подстрока не обнаружена.
Хитрость тут в том, что поскольку первым аргументом мы указали не одно, а несколько значений - эта функция будет возвращать в качестве результата тоже не одно значение, а массив из 3 элементов. Если у вас не последняя версия Office 365 с поддержкой динамических массивов, то после ввода этой формулы и нажатия на Enter вы этот массив увидите прямо на листе:
Пугаться не стоит :) На самом деле наша формула работает и увидеть весь массив результатов всё равно можно, если выделить введённую функцию в строке формул и нажать клавишу F9 (только не забудьте потом нажать Esc , чтобы вернуться обратно к формуле):
Полученный массив результатов означает, что в исходном кривом названии компании (ГК Морозко ОАО) из всех значений в столбце Найти нашлось только второе (Морозко) , причём начиная с 4-го по счёту символа.
Теперь добавим к нашей формуле функцию ПРОСМОТР (LOOKUP) :
У этой функции три аргумента:
- Искомое значение - можно использовать любое достаточно большое число (главное, чтобы оно превышало длину любого текста в исходных данных)
- Просматриваемый_вектор - тот диапазон или массив, где мы ищем искомое значение. Здесь это введённая ранее функция НАЙТИ, возвращающая массив
- Вектор_результатов - диапазон, откуда мы хотим вернуть значение, если искомое значение найдено в соответствующей ячейке. Здесь это правильные названия из столбца Заменить нашей таблицы-справочника.
Главная и неочевидная фишка тут в том, что функция ПРОСМОТР при отсутствии точного совпадения всегда ищет ближайшее наименьшее (предыдущее) значение. Поэтому, указав в качестве искомого значения любое здоровенное число (например 9999), мы заставим ПРОСМОТР находить ячейку с ближайшим наименьшим числом (4) в массиве и выдавать соответствующее ей значение из вектора результатов, т.е. правильное название компании из столбца Заменить.
Второй нюанс заключается в том, что, технически, наша формула является формулой массива, т.к. функция НАЙТИ возвращает в качестве результатов не одно, а массив из трёх значений. Но поскольку функция ПРОСМОТР поддерживает массивы "из коробки", то нам не придётся вводить эту формулу как классическую формулу массива - с помощью сочетания клавиш Ctrl + Shift + Enter . Достаточно будет простого Enter .
Вот и всё. Надеюсь вы ухватили логику.
Осталось перенести готовую формулу первую ячейку B2 столбца Исправлено - и наша задача решена!
Само-собой, с обычными (не умными) таблицами эта формула тоже замечательно работает (только не забудьте про клавишу F4 и закрепление соответствующих ссылок):
Случай 2. Массовая частичная замена
Этот случай чуть похитрее. Снова имеем две "умных" таблицы:
Принципиальное отличие тут в том, что нужно заменять только фрагмент исходных данных - например, в первом адресе неправильный "С-Петербург" на правильный "Санкт-Петербург" , оставив остальную часть адреса (индекс, улицу, дом) в исходном виде.
Готовая формула будет выглядеть так (для удобства восприятия я разделил её на насколько строк с помощью Alt + Enter ):
Основную работу здесь выполняет стандартная Excel'евская текстовая функция ПОДСТАВИТЬ (SUBSTITUTE) , у которой 3 аргумента:
- Исходный текст - первый кривой адрес из столбца Адрес
- Что ищем - тут мы используем трюк с функцией ПРОСМОТР(LOOKUP) из предыдущего способа, чтобы вытащить значение из столбца Найти, которое входит как фрагмент в кривой адрес.
- На что заменить - аналогичным образом находим соответствующее ему правильное значение из столбца Заменить.
Вводить эту формулу с Ctrl + Shift + Enter здесь тоже не нужно, хотя она и является, по-сути, формулой массива.
-
Функция ПОДСТАВИТЬ является регистрочувствительной, поэтому "Спб" в предпоследней строке так и не нашлось в таблице замен. Для решения этой проблемы можно либо использовать функцию ЗАМЕНИТЬ (REPLACE) , либо предварительно привести обе таблицы к одному регистру.
Не идеально и, местами, громоздко, но гораздо лучше, чем однообразная замена вручную, правда? :)
В следующей статье разберёмся, как реализовать подобную массовую подстановку с помощью макросов и Power Query.
Подскажите пожалуйста, у меня есть файл. на листе 1 есть список элементов в столбце B, на листе 2 в столбце B тот же список и в столбце С краткие наименования этих элементов. Мне необходимо создать скрипт, который просматривает столбец B в листе 1 файла Excel, ищет соответствие в столбце B листа 2 и заменяет это значение в листе 1 на соответствующее из столбца С листа 2. Я впервые работаю с VBA. Что мне необходимо сделать?
Спасибо большое за любую помощь!
Добавлено через 3 часа 17 минут
Начала писать скрипт, есть вопросы:
Не могу разобраться как правильно записать "конец строк" и "условие, которое сравнивает Лист1.Cells(i, 1).Value = Лист2.Cells(j, 1).Value"
Макрос, который увеличивает значение ячейки А на 1 при изменении ячейки В
Добрый день. Я написал макрос, который увеличивает значение ячейки А на 1 при изменении ячейки.
Как оптимизировать скрипт, который меняет ширину блока
Вот скрипт, который меняет ширину блока. Есть две ссылки, которые выполняют по клику функции.
Создать скрипт, который одно из запущенных приложений сделает активным, а другое закроет
необходимо создать скрипт который одно из запущенных приложений сделает активным а другое закроет.
Скрипт меняет значение поля только с задержкой
Есть скрипт который должен после того как динамически сформирует поле input сразу менять его.
(if a = b) - это и есть условие. В Вашем случае, "если ячейка такая-то равна ячейке такой-то . ".
"Конец строк" можно ловить так:
Спасибо за помощь!
Dim j As Integer
Dim i As Integer
For i = 1 To 125
For j = 1 To 2301
If Лист1.Cells(i, 2).Value = Лист2.Cells(j, 3).Value Then
Лист1.Cells(i, 2).Value = Лист2.Cells(j, 6).Value
Попробовала данный код на приложенном файле . Почему-то не заменились ячейки - не понятно почему (
Попробуйте вручную. Например, Лист1, строка 3, столбец 2 = "Предоставление сведений об административных правонарушениях в области дорожного движения". Ищем это значение в Листе 2, столбец 3. На строке 267 Бинго! Смотрим в столбец 6 Листа 2, а он брат близнец 3-му столбцу. Т.е. учитывая тот факт, что значения идентичны, то и замены не видно. Это если смотреть на Ваш код. А если прочесть первый пост, то код должен выглядеть так:
На листе 2 в столбце 3 есть короткие названия (меньше 255) - они заменяются сами на себя, поэтому идентичны.
А есть больше 255 - они заменяются на другие. Поэтому "вручной метод" сработал на идентичный вариант.
Добавлено через 8 минут
Попробуйте даже заменить 6 столбец на другой например на 4 - там цифры. Все равно ничего не происходит
Dim j As Integer
Dim i As Integer
For i = 1 To 122
For j = 1 To 2301
If Лист1.Cells(i, 2).Value = Лист2.Cells(j, 3).Value Then
Лист1.Cells(i, 2).Value = Лист2.Cells(j, 4).Value
End If
Я же на другом форуме написал - не то сравниваете. Внимательно! Выше уже тоже указали на ошибку = Лист2.Cells(j, 2).Value Then
Определить, какой скрипт меняет значение поля в форме
На странице есть форма, в ней в том числе несколько скрытых полей. Значения приходят вместе с.
Переключатель (флажок) в форме запроса, который меняет значение поля
Помогите люди добрые, мы в Access не местные. --Есть 2 связанные таблицы, есть запрос, который.
Джава скрипт, который передает значение скрытого поля с одной страницы на другую
мне нужно передать код из четырех цифр с одной страницы, где они находятся в тэге <p> другой.
Получить значение ячейки, зная, что у другой ячейки в этой строке - максимальное значение
Да, может заголовок не очень, но я не знаю как лучше вкратце описать проблему. В общем, у меня.
Range.Replace – это метод, который находит по шаблону подстроку в содержимом ячеек указанного диапазона, заменяет ее на другую подстроку и возвращает значение типа Boolean.
Метод имеет некоторые особенности, которые заключаются в следующем:
- при присвоении булева значения, возвращаемого методом Range.Replace, переменной, необходимо список параметров (аргументов) метода заключать в круглые скобки;
- если метод используется без присвоения возвращаемого значения переменной, параметры должны быть указаны без заключения их в круглые скобки.
Синтаксис и параметры метода
Синтаксис
Синтаксис при замене подстроки и присвоении переменной возвращаемого значения типа Boolean:
variable = expression.Replace(What, Replacement, [LookAt], [SearchOrder], [MatchCase], [MatchByte], [SearchFormat], [ReplaceFormat])
Синтаксис при замене подстроки без присвоения переменной возвращаемого значения:
expression.Replace What, Replacement, [LookAt], [SearchOrder], [MatchCase], [MatchByte], [SearchFormat], [ReplaceFormat]
- variable – переменная (тип данных — Boolean);
- expression – выражение, возвращающее объект Range.
Параметры
Параметр | Описание |
---|---|
What | Искомая подстрока или шаблон*, по которому ищется подстрока в диапазоне ячеек. Обязательный параметр. |
Replacement | Подстрока, заменяющая искомую подстроку. Обязательный параметр. |
LookAt | Указывает правило поиска по полному или частичному вхождению искомой подстроки в текст ячейки: 1 (xlWhole) – поиск полного вхождения искомого текста; 2 (xlPart) – поиск частичного вхождения искомого текста. Необязательный параметр. |
SearchOrder | Задает построчный или постолбцовый поиск: 1 (xlByRows) – построчный поиск; 2 (xlByColumns) – постолбцовый поиск. Необязательный параметр. |
MatchCase | Поиск с учетом или без учета регистра: 0 (False) – поиск без учета регистра; 1 (True) – поиск с учетом регистра. Необязательный параметр. |
MatchByte | Способы сравнения двухбайтовых символов: 0 (False) – двухбайтовые символы сопоставляются с однобайтовыми эквивалентами; 1 (True) – двухбайтовые символы сопоставляются только с двухбайтовым символами. Необязательный параметр. |
SearchFormat | Формат поиска. Необязательный параметр. |
ReplaceFormat | Формат замены. Необязательный параметр. |
* Смотрите знаки подстановки для шаблонов, которые можно использовать в параметре What.
Работа метода в VBA Excel
Исходная таблица для всех примеров:
Пример 1
Примеры записи строк кода с методом Range.Replace и поиском по частичному совпадению подстроки с содержимым ячейки:
В первых двух параграфах описано нетрадиционное использование процедуры Function, поэтому не применяйте его в серьезных проектах. Тестирование проводилось в Excel 2016.
Функция с методом Range.Replace
Но, как ни странно, внутри процедуры Function работает метод Range.Replace, которым мы воспользуемся для изменения значений других ячеек из пользовательской функции.
Пример 1
Эта функция заменяет значение ячейки Cell1 на значение ячейки Cell2 увеличенное на 100. Сама функция размещается в третьей ячейке, чтобы не возникла циклическая ссылка.
В этом примере мы не присваиваем пользовательской функции значение, поэтому отображается значение по умолчанию – 0. Если объявить эту функцию как строковую: Function Primer1(Cell1 As Range, Cell2 As Range) as String , будет возвращена пустая строка.
Изменение значения ячейки C1 (Cell2) приведет к пересчету значения ячейки B1 (Cell1).
Попробуйте очистить или перезаписать ячейку B1 (Cell1), ничего не получится, так как функция Primer1 вновь перезапишет ее значением C1 (Cell2) + 100.
Метод Application.Volatile
Application.Volatile – это метод, который запускает пересчет функции при изменении значения любой ячейки рабочего листа, а не только той, которая присвоена объявленной в функции переменной. Метод Application.Volatile используется только в функциях.
Рассмотрим пересчет функции на следующем примере:
Пример 2
Эта функция будет пересчитываться только при изменении значений ячеек B1 и C1, присвоенных переменным Cell1 и Cell2. При изменении значения ячейки C2, значение ячейки B2 не изменится, так как не будет запущен пересчет функции Primer2.
Функция Primer2 начнет вести себя по-другому, если добавить в нее оператор Application.Volatile (переименуем ее в Primer3):
Пример 3
Теперь при смене значения в ячейке C2, значение ячейки B2 тоже изменится.
В предыдущих примерах функциям не присваивалось конечное значение, чтобы показать, что такое возможно. При присвоении значения пользовательской функции следует следить за тем, чтобы не возникало циклических ссылок между ячейками с изменяемыми функцией значениями, в том числе и той, в которой размещена функция.
Безопасное использование функции
Если вам все-таки хочется в важном проекте VBA Excel с помощью одной пользовательской функции изменять значения нескольких ячеек, есть полностью безопасный вариант.
Он заключается в добавлении дополнительного аргумента в пользовательскую функцию, с помощью которого для каждой ячейки можно вывести отдельный результат. Функция размещается в каждой изменяемой ячейке.
В простых случаях для выбора можно использовать функцию Choose или, в более сложных, оператор If…Then…Else и оператор Select Case.
Пример 4
Используем функцию Choose для выбора способа вычисления пользовательской функции в зависимости от значения дополнительного аргумента:
В функцию Primer4 добавлен дополнительный аргумент a, от которого зависит, какое действие будет произведено со значениями ячеек B1 и C1:
На следующем скриншоте представлены результаты вычисления функции в зависимости от значения аргумента a:
- В ячейке A1 вычисляется сумма значений ячеек B1 и C1 – аргумент a=1.
- В ячейке A2 вычисляется разность значений ячеек B2 и C2 – аргумент a=2.
- В ячейке A3 вычисляется произведение значений ячеек B3 и C3 – аргумент a=3.
Пример 5
Используем оператор If…Then…Else в сокращенном виде (If…Then…) для выбора способа вычисления функции в зависимости от значения дополнительного аргумента:
Читайте также: