Поиск ошибок в таблице и их исправление с помощью кода VBA Excel. Создание словаря ошибок и автоматическое добавление в него слов с неправильным написанием.
Описание задачи
В одном из столбцов таблицы присутствует список объектов, часть из которых имеет ошибки в написании. Необходимо с помощью кода VBA Excel наименования одинаковых объектов привести к единому написанию.
Для этого нам понадобится словарь ошибочных слов и код VBA, который будет сверять содержимое ячеек заданного диапазона со словарем, исправлять ошибки и добавлять новые позиции в словарь.
Наименованиями объектов могут быть названия товаров, брендов, компьютерных игр, растений, животных, стран, курортов, городов, аэропортов и т.д. Мы будем использовать названия городов.
Создание словаря
Создайте новый лист и переименуйте его ярлык в «Словарь». Словарь может быть размещен в файле с таблицей или в отдельной книге Excel.
Если вам приходится исправлять ошибки в нескольких различных списках объектов, лучше создать отдельный файл со всеми словарями.
Изначально на листе «Словарь» следует дать названия первым двум колонкам. Например, так:
| Наименование с ошибкой | Правильное написание |
|---|
Первый запуск кода VBA Excel добавит все уникальные наименования из указанного диапазона таблицы в первую колонку словаря. В этом столбце будут содержаться и по одному экземпляру правильного наименования.
Если в исходной таблице все наименования одного объекта окажутся ошибочными, то в первый столбец словаря следует вручную добавить правильное написание этого объекта.
Вторая колонка словаря заполняется вручную. Напротив каждого наименования с ошибкой пишем правильное во втором столбце.
Второй запуск программы исправит все ошибки в указанном диапазоне таблицы.
При обработке следующих списков программа также будет добавлять новые наименования в первую колонку словаря. Второй столбец заполняется вручную.
Исправление ошибок
Будем решать задачу с условием, что таблица и словарь расположены в одной книге Excel. Активный лист – лист с таблицей.
Для ускорения кода VBA будем использовать в циклах массивы, а не переменные диапазона (as Range). Словарь загрузим в объект Dictionary.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
Sub IspravleniyeOshibok() Dim colList As Long, strList As Long, strDict As Long, _ i As Long, myList1 As Variant, myList2 As Variant, _ myDict As Object On Error GoTo Instruk 'Указываем номер столбца со списком в таблице colList = CLng(InputBox("Введите номер столбца")) 'colList = 2 'Определяем номер последней строки в таблице strList = Cells(1, colList).CurrentRegion.Rows.Count 'Загружаем список без заголовка в массив myList1 myList1 = Range(Cells(2, colList), Cells(strList, colList)) With Sheets("Словарь") 'Определяем номер последней строки в словаре strDict = .Cells(1, 1).CurrentRegion.Rows.Count 'Загружаем словарь с заголовками в массив myList2 myList2 = .Range(.Cells(1, 1), .Cells(strDict, 2)) End With 'Загружаем словарь в объект Dictionary Set myDict = CreateObject("Scripting.Dictionary") For i = 1 To strDict myDict.Add myList2(i, 1), myList2(i, 2) Next For i = 1 To strList - 1 'Проверяем наличие слова из myList1 в списке 'ключей объекта Dictionary If myDict.Exists(myList1(i, 1)) Then 'Если есть слово в списке ключей, меняем 'слово в myList1 на элемент объекта Dictionary myList1(i, 1) = myDict.Item(myList1(i, 1)) Else 'Если нет слова в списке ключей, 'добавляем новое слово на лист "Словарь" strDict = strDict + 1 Sheets("Словарь").Cells(strDict, 1) = myList1(i, 1) End If Next 'Перезаписываем список в таблице списком из массива myList1 Range(Cells(2, colList), Cells(strList, colList)) = myList1 Instruk: If Err.Description <> "" Then MsgBox "Произошла ошибка: " & Err.Description End If End Sub |
Переменные:
- colList – номер столбца со списком в таблице;
- strList – номер последней строки в таблице;
- strDict – номер последней строки в словаре на листе «Словарь»;
- i – счетчик циклов;
- myList1 – массив для списка из таблицы;
- myList2 – массив для списков из словаря на листе «Словарь»;
- myDict – объект Dictionary (словарь).
Работа кода в картинках
Исходная таблица:
Исходный словарь:
Словарь после первого запуска кода:
Дозаполненный вручную словарь:
Таблица после второго запуска кода:
Лист с таблицей при запуске представленного выше кода VBA Excel должен быть активным.