Excel сам находит дубликаты и ошибки — одна кнопка проверяет всю таблицу через VBA

Excel сам находит дубликаты и ошибки — VBA-макрос для полной проверки таблицы. Подробно разбираем, как в Excel через VBA автоматически находить дубликаты, пустые значения и проблемные строки. Готовый макрос, разбор логики и реальные сценарии для рабочих таблиц.

Есть в Excel одна неприятная правда, которую почти все узнают слишком поздно: пока таблица маленькая, ошибки кажутся редкостью. Но как только файл начинает жить настоящей рабочей жизнью, дубликаты, пропуски и кривые значения лезут в него так же уверенно, как сквозняк в старое окно.

Сначала всё выглядит прилично. Есть список клиентов, есть номера заказов, есть суммы, даты, статусы. Потом файл проходит через несколько рук. Кто-то копирует строки. Кто-то догружает выгрузку из CRM. Кто-то вручную дописывает данные внизу. Кто-то удаляет пару записей и вставляет новые. И в какой-то момент таблица внешне остаётся аккуратной, а внутри уже начинает гнить.

Появляются дубликаты заказов.
Повторяются клиенты.
Одинаковые документы попадают в отчёт дважды.
Где-то пустая дата.
Где-то вместо суммы текст.
Где-то строка выглядит нормальной, но на самом деле в ней уже сидит ошибка, которая потом размажется по итогам, как жирное пятно по белой рубашке.

Самое неприятное здесь не в самой ошибке. А в том, что человек часто замечает её слишком поздно. Уже после отправки отчёта. Уже после сверки. Уже после того, как цифры ушли руководителю, клиенту или бухгалтерии.

Именно поэтому одна из самых полезных автоматизаций в Excel — это кнопка полной проверки таблицы. Не условное форматирование “на всякий случай”. Не ручной просмотр глазами до головной боли. А нормальный VBA-макрос, который проходит по данным, ищет дубликаты, пустые значения, подозрительные записи и сразу показывает, где нужно смотреть.

Это уже не просто “трюк для продвинутых”. Это рабочий инструмент, который реально экономит время, снижает риск ошибок и делает Excel заметно взрослее.

Почему обычная проверка глазами почти всегда проигрывает

Когда таблица состоит из двадцати строк, глаз ещё справляется. Можно быстро пробежаться, увидеть повторы, найти пустые ячейки, заметить, что сумма выглядит странно. Но как только данных становится много, человек начинает работать не вниманием, а инерцией.

Он смотрит на таблицу, но уже не видит её целиком. Мозг начинает угадывать. Достраивать. Доверять привычной картинке. А Excel в это время спокойно хранит внутри всё подряд — дубли, мусор, пустоты, обрывки старых вставок и любые другие сюрпризы.

Проблема здесь не в невнимательности. Просто глаз — плохой инструмент для однотипной проверки больших объёмов. Он быстро устает, а таблицы, наоборот, становятся всё длиннее и наглее.

Можно, конечно, каждый раз использовать фильтры, сортировки, формулы, условное форматирование. Но у этих способов есть слабое место: они редко работают как цельная система. Их нужно настраивать, помнить про них, обновлять диапазоны, следить, чтобы кто-нибудь не снёс правило случайным движением.

Макрос удобнее тем, что он превращает проверку в отдельное действие. Нажал кнопку — получил результат. Не нужно гадать, сработало ли правило в нужном столбце и не осталось ли внизу три неучтённые строки. VBA делает проверку целиком и по заданной логике.

Какие ошибки обычно приходится искать в реальных таблицах

Если говорить честно, почти во всех рабочих файлах повторяется один и тот же набор проблем.

1. Самая очевидная — дубликаты. Один и тот же номер заказа, одна и та же заявка, один и тот же клиент, одна и та же накладная. Иногда это настоящий дубль, иногда результат двойной выгрузки, иногда следствие ручного копирования.
2. Вторая проблема — пустые обязательные поля. Строка вроде есть, а даты нет. Или есть клиент, но нет суммы. Или номер документа пустой. Такие ошибки особенно коварны, потому что таблица визуально продолжает выглядеть заполненной.
3. Третья проблема — нечисловые или нелогичные значения. В столбце суммы внезапно появляется текст. Или сумма становится отрицательной там, где её по смыслу быть не должно. Или вместо даты оказывается набор символов.
4. Четвёртая — частично испорченные строки. Когда половина данных заполнена, а половина нет. Такие записи хуже явных пустых строк, потому что они выглядят правдоподобно.
5. И пятая проблема — скрытая повторяемость по нескольким столбцам. Например, один и тот же клиент в один и тот же день с одной и той же суммой. По отдельности поля не повторяются так уж явно, а в связке это уже почти точный дубль.

Все эти ошибки можно искать вручную. Но это как подметать двор зубной щёткой: технически возможно, но очень быстро надоест.

Что именно мы сделаем в этой статье

Мы создадим VBA-макрос, который:

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

И самое важное — ты сможешь не просто вставить код, а понять его логику. Потому что иначе макрос превращается в странное заклинание: вроде работает, но трогать страшно.

Начнём с базовой задачи: найти дубликаты в одном столбце

Представим простую ситуацию. У нас есть таблица заказов. В столбце A хранится номер заказа. Если номер повторился, это уже повод проверить строку. Значит, задача такая: пройти по столбцу A, найти повторяющиеся значения и подсветить строки.

Вот базовый рабочий макрос:

Sub ПроверкаДубликатов()
Dim Диапазон As Range*
Dim Ячейка As Range*
Dim Словарь As Object*
Set Словарь = CreateObject("Scripting.Dictionary")*
Set Диапазон = Range("A2:A5000")*
For Each Ячейка In Диапазон*
If Trim(Ячейка.Value) <> "" Then*
If Словарь.exists(Ячейка.Value) Then*
Ячейка.EntireRow.Interior.Color = RGB(255, 150, 150)*

Else*
Словарь.Add Ячейка.Value, 1*
End If*
End If*
Next Ячейка*
MsgBox "Проверка завершена", vbInformation, "Excel"*
End Sub

Это уже рабочее решение. Оно простое, но вполне пригодно для реальной таблицы.

Как работает этот макрос по шагам

Первая строка:

Sub ПроверкаДубликатов()

Это начало процедуры. Название можно менять, но лучше сразу давать понятные имена. Не “Macro1”, а что-то человеческое.

Дальше:

Dim Диапазон As Range
Dim Ячейка As Range
Dim Словарь As Object

Здесь мы объявляем переменные. Диапазон — для того столбца, который будем проверять. Ячейка — для прохода по каждой строке. Словарь — ключевая часть решения. Именно он запоминает, какие значения уже встречались.

Строка:

Set Словарь = CreateObject("Scripting.Dictionary")

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

Дальше:

Set Диапазон = Range("A2:A5000")

мы задаём диапазон проверки. Здесь это столбец A, начиная со второй строки. То есть первая строка считается заголовком.

Потом запускается цикл:

For Each Ячейка In Диапазон

Макрос начинает проходить по каждой ячейке диапазона. Условие:

If Trim(Ячейка.Value) <> "" Then

означает: если в ячейке не пусто, работаем дальше. Функция Trim убирает лишние пробелы, что полезно в живых таблицах, где пустая ячейка иногда выглядит непустой просто из-за случайного пробела. Теперь главное:

If Словарь.exists(Ячейка.Value) Then

Если значение уже есть в словаре, значит, оно повторилось. В этом случае:

Ячейка.EntireRow.Interior.Color = RGB(255, 150, 150)

подсвечивается вся строка мягким красным цветом. Если же значение встретилось впервые:

Словарь.Add Ячейка.Value, 1

мы добавляем его в словарь. И в конце:

MsgBox "Проверка завершена", vbInformation, "Excel"

Excel показывает сообщение о завершении проверки.

Ничего лишнего. Всё по делу. И уже этого достаточно, чтобы быстро выловить дубли в таблице.

Почему словарь лучше простых формул

Иногда спрашивают: зачем вообще VBA, если можно поставить формулу или условное форматирование?

Можно. Но словарь в VBA выигрывает в нескольких случаях сразу.

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

Формула хороша, когда задача простая и статичная. Макрос хорош, когда задача становится частью рабочего процесса.

Недостаток базового варианта

У базового макроса есть одна важная слабость: он проверяет жёстко заданный диапазон. То есть до строки 5000. Сегодня этого хватает, завтра уже нет. Или наоборот — в таблице всего 300 строк, а макрос зачем-то идёт до пяти тысяч.

Это не смертельно, но не очень аккуратно. Поэтому следующий шаг — сделать диапазон динамическим.

Улучшаем макрос: пусть Excel сам ищет последнюю строку

Вот более взрослый вариант:

Sub ПроверкаДубликатов()
Dim Диапазон As Range*
Dim Ячейка As Range*
Dim Словарь As Object*
Dim LastRow As Long*
LastRow = Cells(Rows.Count, 1).End(xlUp).Row*
Set Словарь = CreateObject("Scripting.Dictionary")*
Set Диапазон = Range("A2:A" & LastRow)*
For Each Ячейка In Диапазон*
If Trim(Ячейка.Value) <> "" Then*
If Словарь.exists(Ячейка.Value) Then*
Ячейка.EntireRow.Interior.Color = RGB(255, 150, 150)*
Else*
Словарь.Add Ячейка.Value, 1*
End If*
End If*
Next Ячейка*
MsgBox "Проверка завершена", vbInformation, "Excel"*
End Sub

Теперь Excel сам определяет последнюю заполненную строку в столбце A. Это уже гораздо ближе к нормальной рабочей автоматизации. Таблица может расти или уменьшаться — макрос всё равно будет проверять реальный диапазон.

Именно такие мелочи отличают игрушечный код от полезного.

Как убрать старую подсветку перед новой проверкой

Есть ещё одна важная деталь. Если запускать макрос несколько раз, старые подсветки останутся. В результате через время таблица начнёт выглядеть как поле после офисной грозы — и уже неясно, что найдено сейчас, а что было вчера.

Значит, перед новой проверкой лучше очищать фон. Вот версия с очисткой:

Sub ПроверкаДубликатов()
Dim Диапазон As Range*
Dim Ячейка As Range*
Dim Словарь As Object*
Dim LastRow As Long*
LastRow = Cells(Rows.Count, 1).End(xlUp).Row*
Rows("2:" & LastRow).Interior.ColorIndex = xlNone*
Set Словарь = CreateObject("Scripting.Dictionary")*
Set Диапазон = Range("A2:A" & LastRow)*
For Each Ячейка In Диапазон*
If Trim(Ячейка.Value) <> "" Then*
If Словарь.exists(Ячейка.Value) Then*
Ячейка.EntireRow.Interior.Color = RGB(255, 150, 150)*
Else*
Словарь.Add Ячейка.Value, 1*
End If*
End If*
Next Ячейка*
MsgBox "Проверка завершена", vbInformation, "Excel"*
End Sub

Теперь перед запуском макрос очищает заливку строк со второй до последней. Это делает проверку чистой и актуальной.

Проверка по двум столбцам сразу

Теперь перейдём к более жизненной задаче.

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

• клиент + дата
  номер документа + сумма
  артикул + склад
  сотрудник + период

Значит, нужен составной ключ. Например, если в столбце A хранится клиент, а в столбце B дата, можно объединить их в одну строку и сравнивать уже её. Вот пример:

Sub ПроверкаДубликатовПоДвумСтолбцам()
Dim LastRow As Long*
Dim i As Long*
Dim Ключ As String*
Dim Словарь As Object*
LastRow = Cells(Rows.Count, 1).End(xlUp).Row*
Rows("2:" & LastRow).Interior.ColorIndex = xlNone*
Set Словарь = CreateObject("Scripting.Dictionary")*
For i = 2 To LastRow*
Ключ = Cells(i, 1).Value & "|" & Cells(i, 2).Value*
If Trim(Cells(i, 1).Value) <> "" And Trim(Cells(i, 2).Value) <> "" Then*
If Словарь.exists(Ключ) Then*
Rows(i).Interior.Color = RGB(255, 150, 150)*
Else*
Словарь.Add Ключ, 1*
End If*
End If*
Next i*
MsgBox "Проверка завершена", vbInformation, "Excel"*
End Sub

Здесь в переменную Ключ склеиваются значения из двух столбцов. Разделитель “|” нужен для того, чтобы строка “Иван12” не слилась случайно с другой комбинацией символов.

Такой подход очень полезен для реальных баз, где по одному полю дубликат может быть нормой, а по сочетанию — уже проблемой.

Проверка пустых обязательных полей

Дубликаты — это только половина работы. Вторая типичная проблема — пустые обязательные поля. Например, в строке обязательно должны быть:

• номер документа
• дата
• сумма
• ответственный

Если хотя бы одно поле пустое, строка уже требует внимания. Вот макрос, который подсвечивает строки с пропусками:

Sub ПроверкаПустыхПолей()
Dim LastRow As Long*
Dim i As Long*
LastRow = Cells(Rows.Count, 1).End(xlUp).Row*
Rows("2:" & LastRow).Interior.ColorIndex = xlNone*
For i = 2 To LastRow*
If Trim(Cells(i, 1).Value) = "" Or Trim(Cells(i, 2).Value) = "" Or Trim(Cells(i, 3).Value) = "" Then*
Rows(i).Interior.Color = RGB(255, 230, 153)*
End If*

Next i*
MsgBox "Проверка завершена", vbInformation, "Excel"*
End Sub

Здесь мы проверяем первые три столбца. Если хотя бы одно из них пустое, строка подсвечивается жёлтым. Такой макрос очень полезен перед отправкой отчётов или загрузкой данных в систему.

Полноценная проверка: дубликаты плюс пустые поля

Теперь соберём всё в один макрос. Это уже будет реальный рабочий инструмент: если строка повторяется — красим её красным, если в ней есть пропуски — жёлтым.

Sub ПолнаяПроверкаТаблицы()
Dim LastRow As Long*
Dim i As Long*
Dim Ключ As String*
Dim Словарь As Object*
LastRow = Cells(Rows.Count, 1).End(xlUp).Row*
Rows("2:" & LastRow).Interior.ColorIndex = xlNone*
Set Словарь = CreateObject("Scripting.Dictionary")*
For i = 2 To LastRow*
If Trim(Cells(i, 1).Value) = "" Or Trim(Cells(i, 2).Value) = "" Or Trim(Cells(i, 3).Value) = "" Then*
Rows(i).Interior.Color = RGB(255, 230, 153)*

End If*
Ключ = Cells(i, 1).Value & "|" & Cells(i, 2).Value*
If Trim(Cells(i, 1).Value) <> "" And Trim(Cells(i, 2).Value) <> "" Then*
If Словарь.exists(Ключ) Then*
Rows(i).Interior.Color = RGB(255, 150, 150)*
Else*
Словарь.Add Ключ, 1*
End If*
End If*
Next i*
MsgBox "Полная проверка завершена", vbInformation, "Excel"*
End Sub

Вот это уже то, что можно смело встраивать в рабочую книгу. Нажал кнопку — и Excel сразу показывает, где пустота, а где повтор.

А если нужно не просто подсветить, а собрать ошибки на отдельный лист

Это уже следующий уровень полезности. Иногда подсветка хороша, когда файл остаётся у вас. Но если нужно передать коллегам, что именно нужно исправить, удобнее собирать проблемные строки на отдельный лист. Например, создать лист Ошибки и переносить туда все строки, которые:

• пустые
• повторяются
• подозрительны

Такой подход полезен, когда один человек собирает отчёт, а другой исправляет данные. Логика будет примерно такой:

• макрос находит ошибку
• копирует строку
• вставляет её на лист “Ошибки”
• помечает причину

Это уже похоже на полноценный контроль качества. Вот пример:

Sub ВыгрузитьОшибкиНаОтдельныйЛист()
Dim LastRow As Long*
Dim i As Long*
Dim NextRow As Long*
Dim Ключ As String*
Dim Словарь As Object*
Dim wsErr As Worksheet*
On Error Resume Next*
Set wsErr = Worksheets("Ошибки")*
On Error GoTo 0*
If wsErr Is Nothing Then*
Set wsErr = Worksheets.Add*
wsErr.Name = "Ошибки"*
Else*
wsErr.Cells.Clear*

End If*
LastRow = Cells(Rows.Count, 1).End(xlUp).Row*
Set Словарь = CreateObject("Scripting.Dictionary")*
Rows(1).Copy Destination:=wsErr.Rows(1)*
wsErr.Cells(1, 10).Value = "Причина"*
NextRow = 2*
For i = 2 To LastRow*
If Trim(Cells(i, 1).Value) = "" Or Trim(Cells(i, 2).Value) = "" Or Trim(Cells(i, 3).Value) = "" Then*
Rows(i).Copy Destination:=wsErr.Rows(NextRow)*
wsErr.Cells(NextRow, 10).Value = "Пустые обязательные поля"*
NextRow = NextRow + 1*
Else*
Ключ = Cells(i, 1).Value & "|" & Cells(i, 2).Value*
If Словарь.exists(Ключ) Then*
Rows(i).Copy Destination:=wsErr.Rows(NextRow)*
wsErr.Cells(NextRow, 10).Value = "Дубликат"*
NextRow = NextRow + 1*
Else*
Словарь.Add Ключ, 1*
End If*
End If
Next i*
MsgBox "Ошибки выгружены на отдельный лист", vbInformation, "Excel"*
End Sub

Такой макрос уже не просто “красит” строки, а создаёт отдельный рабочий список проблем. Для больших файлов это очень удобно.

Где этот приём особенно полезен

Не только в учебных примерах. Как раз наоборот — больше всего он нужен в живой работе.

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

Во всех этих случаях одна кнопка проверки очень быстро окупает себя. Потому что она не просто ускоряет работу. Она делает её спокойнее.

Частые ошибки при создании таких макросов

1. Первая — слишком узкий диапазон.
   Люди задают A2:A100 и забывают, что таблица давно выросла. Потом радуются, что макрос “работает”, хотя половину данных он вообще не видел.
2. Вторая — отсутствие очистки старой подсветки.
   В результате после нескольких запусков таблица выглядит как карта боевых действий, и понять, что найдено сейчас, уже невозможно.
3. Третья — сравнение без Trim.
   Пробелы в ячейках — это старый офисный демон. Он любит делать вид, будто данные есть, хотя по сути там пустота.
4. Четвёртая — попытка искать дубликаты по одному полю там, где нужен составной ключ.
   Например, если один и тот же клиент может встречаться много раз, но не в одну и ту же дату по одному и тому же документу.
5. Пятая — отсутствие отдельного листа ошибок, когда таблица очень большая.
   Подсветка хороша до определённого объёма. Потом удобнее работать уже через отдельный список проблем.

Почему этот макрос особенно хорошо работает в серии

Потому что он не выглядит как абстрактная “функция ради функции”. Это реальный этап рабочего процесса.

• Сначала вы собираете данные.
• Потом очищаете лишнее.
• Потом проверяете структуру.
• Потом ищете ошибки и дубликаты.

И уже после этого формируете отчёт, PDF или отправку.

Именно так Excel превращается из обычной книги в маленькую систему. Не в игрушку, не в набор случайных макросов, а в нормальный инструмент, который берёт на себя повторяющуюся часть работы.

Если вам близка такая логика, посмотрите и другие материалы канала про автоматическую нумерацию строк, сохранение отчёта в папку и создание PDF одной кнопкой. В связке эти инструменты работают особенно хорошо: один готовит данные, второй проверяет, третий формирует итог.

Что в итоге даёт автоматический поиск дубликатов и ошибок

• Если совсем честно — он даёт ощущение контроля.
• Вы больше не гадаете, проскочило ли что-то внизу таблицы.
• Не перечитываете строки по три раза.
• Не надеетесь, что глаз заметит повтор на 1837-й записи.
• Не ловите потом странные расхождения в итогах, когда уже поздно.

Одна кнопка превращает муторную ручную проверку в понятную операцию. А это именно то, ради чего VBA вообще и стоит подключать к Excel.

Если вы работаете с большими таблицами каждый день, напишите в комментариях, что бесит вас сильнее всего: дубликаты, пустые поля, случайные повторы сумм или кривые даты. Следующей статьёй логично разобрать ещё один полезный сценарий — как автоматически проверять не только дубликаты, но и логические ошибки в строке: отрицательные суммы, пустые даты, сломанные статусы и неверные форматы значений.

Подписывайтесь на канал и забирайте файл с примером в Telegram.

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