Перейти к содержимому

VBA Excel. Ошибки в таблице – поиск и исправление

Поиск ошибок в таблице и их исправление с помощью кода VBA Excel. Создание словаря ошибок и автоматическое добавление в него слов с неправильным написанием.

  1. Описание задачи
  2. Создание словаря
  3. Исправление ошибок
  4. Работа кода в картинках

Описание задачи

В одном из столбцов таблицы присутствует список объектов, часть из которых имеет ошибки в написании. Необходимо с помощью кода VBA Excel наименования одинаковых объектов привести к единому написанию.

Для этого нам понадобится словарь ошибочных слов и код VBA, который будет сверять содержимое ячеек заданного диапазона со словарем, исправлять ошибки и добавлять новые позиции в словарь.

Наименованиями объектов могут быть названия товаров, брендов, компьютерных игр, растений, животных, стран, курортов, городов, аэропортов и т.д. Мы будем использовать названия городов.

Создание словаря

Создайте новый лист и переименуйте его ярлык в «Словарь». Словарь может быть размещен в файле с таблицей или в отдельной книге Excel.

Если вам приходится исправлять ошибки в нескольких различных списках объектов, лучше создать отдельный файл со всеми словарями.

Изначально на листе «Словарь» следует дать названия первым двум колонкам. Например, так:

Наименование с ошибкой Правильное написание

Первый запуск кода VBA Excel добавит все уникальные наименования из указанного диапазона таблицы в первую колонку словаря. В этом столбце будут содержаться и по одному экземпляру правильного наименования.

Если в исходной таблице все наименования одного объекта окажутся ошибочными, то в первый столбец словаря следует вручную добавить правильное написание этого объекта.

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

Второй запуск программы исправит все ошибки в указанном диапазоне таблицы.

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

Исправление ошибок

Будем решать задачу с условием, что таблица и словарь расположены в одной книге Excel. Активный лист – лист с таблицей.

Для ускорения кода VBA будем использовать в циклах массивы, а не переменные диапазона (as Range). Словарь загрузим в объект Dictionary.

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 Instr
'Указываем номер столбца со списком в таблице
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
Instr:
If Err.Description <> "" Then
  MsgBox "Произошла ошибка: " & Err.Description
End If
End Sub

Переменные:

  • colList – номер столбца со списком в таблице;
  • strList – номер последней строки в таблице;
  • strDict – номер последней строки в словаре на листе «Словарь»;
  • i – счетчик циклов;
  • myList1 – массив для списка из таблицы;
  • myList2 – массив для списков из словаря на листе «Словарь»;
  • myDict – объект Dictionary (словарь).

Работа кода в картинках

Исходная таблица:

Исходный словарь:

Словарь после первого запуска кода:

Дозаполненный вручную словарь:

Таблица после второго запуска кода:

Лист с таблицей при запуске представленного выше кода VBA Excel должен быть активным.

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

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

Ваш комментарий будет опубликован после прохождения обязательной модерации. Исходящие ссылки не допускаются. Время модерации составит от нескольких минут до нескольких часов в зависимости от времени суток и занятости модератора. При добавлении в комментарий кода VBA Excel, вставьте перед его началом тег [vb] и по окончании кода - [/vb].