Часть 3. Проверьте таблицу на пустые ячейки до того, как ошибка попадёт в отчёт: VBA сам находит пропуски. Подробный разбор реального VBA-сценария для Excel: как автоматически проверить заполненность таблицы, найти пустые ячейки, подсветить ошибки и не допустить, чтобы недозаполненные данные попали в отчёт, отправку или аналитику. С готовым кодом, пошаговым объяснением и вариантами применения.
Есть одна особенно неприятная офисная ошибка. Не громкая. Не героическая. Не такая, из-за которой сервер падает, а у всех в чате начинается коллективная паника. Она тихая, почти бытовая — в таблице осталась пустая ячейка. Не указан срок. Не заполнен статус. Не вписан ответственный. Нет суммы. Пропущен клиент. И всё это обычно замечают не в момент внесения данных, а позже. Когда уже строят отчёт. Когда файл отправлен. Когда руководитель открыл таблицу. Когда клиент уже получил документ. Когда аналитика пошла криво, и никто сразу не понимает почему.
Вот тут Excel внезапно перестаёт быть просто таблицей и становится чем-то вроде проходной на заводе: если контроль на входе слабый, дальше внутрь заходит всё подряд.
Именно поэтому третья часть серии — одна из самых жизненных. Не самая эффектная на первый взгляд, зато одна из самых полезных в реальной работе. Потому что автоматическая проверка заполненности таблицы — это не «фишка ради красоты». Это защита от мелких дыр, из которых потом вытекают часы, нервы и репутация.
Реальная рабочая ситуация, в которой одна пустая ячейка портит весь день
Представь обычную таблицу. В ней может быть всё что угодно: заявки, оплаты, задачи, клиенты, поставки, смены, документы, отгрузки, интервью, акты, обращения. Кто-то вносит данные утром, кто-то после звонка, кто-то на бегу между двумя задачами, кто-то уже под конец дня, когда голова устала и внимание начинает работать как старый фонарь на ветру — вроде светит, но с перебоями.
В такой среде пустые ячейки появляются постоянно. Не потому что люди плохие или невнимательные. Просто живая работа никогда не бывает стерильной.
Один менеджер забыл указать срок.
Другой проставил клиента, но не написал статус.
Третий оставил пустой комментарий там, где он обязателен.
Четвёртый внёс строку наполовину, потому что хотел вернуться позже — и, конечно, не вернулся.
А потом из этой таблицы строится отчёт. Или запускается сортировка. Или работает макрос из прошлой части, который формирует ежедневную сводку. И внезапно оказывается, что отчёт вроде собрался, но внутри дыры. Где-то пустой статус. Где-то нет ответственного. Где-то пустой срок делает задачу невидимой для контроля. Где-то из-за отсутствующей суммы поехала итоговая аналитика.
Самая злая особенность таких ошибок в том, что они выглядят мелочью ровно до момента, пока не начнут влиять на решение.
Почему ручная проверка работает плохо, даже если кажется простой
На этом месте обычно звучит старая реплика: «Да я и глазами могу посмотреть». Формально — да. Практически — почти никогда не до конца.
Когда строк 10, глазами действительно можно проверить. Когда 40 — уже неприятно. Когда 120 — начинается самообман. Когда 300 — это вообще не проверка, а ритуал надежды. Человек смотрит на таблицу, скользит взглядом по строкам и убеждает себя, что всё в порядке. Но Excel не храм, и надежда в нём работает слабо.
Есть ещё вариант с фильтрами, условным форматированием и ручным поиском пустых значений. Иногда это помогает. Но чаще всего требует дополнительных движений, которые никто не любит делать каждый день. А всё, что требует лишних движений, в живой работе рано или поздно перестают делать.
Значит, нужен сценарий проще: одна кнопка, которая сама проверяет таблицу и показывает, где есть проблемы.
Где такой макрос особенно нужен
Сценарий 1. Продажи, сопровождение, клиентские задачи
Есть таблица заявок: дата, клиент, задача, менеджер, сумма, статус, срок. Если хотя бы одно поле пропущено, дальше начинается путаница. Кто отвечает? Когда дедлайн? Оплачено или нет? VBA-макрос может быстро найти все строки, где не заполнены обязательные поля, и выделить их.
Сценарий 2. Логистика, склад, производство, сервис
Есть журнал заказов, доставок, отгрузок, выездов или ремонтов. Если не заполнен номер заказа, исполнитель, адрес, дата, комментарий или статус, строка становится почти бесполезной. Макрос проверяет таблицу до отчёта и показывает, где провал.
Именно такие сценарии люди чаще всего сохраняют. Не потому что любят код. А потому что в них узнают своё рабочее утро, обед и конец дня.
Что мы хотим получить в итоге
У нас есть рабочий лист с таблицей. Предположим, он называется Данные.
В таблице есть обязательные колонки. Например:
A — Дата
B — Клиент
C — Задача
D — Сумма
E — Ответственный
F — Статус
G — Комментарий
Нас интересует не вся таблица вообще, а именно обязательные поля. Например, мы хотим проверить, что в строке не пусты дата, клиент, задача, ответственный и статус. Комментарий может быть необязательным. Сумма — зависит от процесса.
Макрос должен сделать следующее:
- найти последнюю строку таблицы;
- пройти по всем строкам;
- проверить обязательные ячейки;
- если где-то пусто — подсветить нужные ячейки;
- посчитать количество проблем;
- в конце показать понятное сообщение: всё заполнено или найдено столько-то строк с пропусками.
Это уже не просто автоматизация. Это контроль качества данных в самом понятном, приземлённом виде.
Формулы могут помочь, но не решают всю задачу
Иногда для такой проверки используют формулы. Например, можно добавить служебный столбец и написать:
=ЕСЛИ(ИЛИ(A2="";B2="";C2="";E2="";F2="");"Проверить";"ОК")
Формула рабочая. Но у неё есть ограничения.
- Во-первых, нужен отдельный служебный столбец.
- Во-вторых, кто-то должен помнить, что на него вообще надо смотреть.
- В-третьих, она не подсвечивает проблемные ячейки автоматически так, как это удобно в рабочем сценарии.
- В-четвёртых, если таблицу используют несколько человек, служебные колонки начинают разрастаться, как старый сарай, к которому каждую весну что-то пристраивали, но ни разу не разобрали.
VBA здесь даёт более чистое решение: нажал кнопку, увидел, где пусто, исправил, пошёл дальше.
Готовый VBA-код для автоматической проверки заполненности таблицы
Ниже — полный рабочий макрос. Он проверяет обязательные поля в таблице на листе Данные, снимает старую заливку, подсвечивает пустые ячейки и сообщает итог.
Sub ПроверитьЗаполненностьТаблицы()
Dim ws As Worksheet*
Dim lastRow As Long*
Dim i As Long*
Dim errorCount As Long*
Dim rowHasError As Boolean*
Set ws = ThisWorkbook.Worksheets("Данные")*
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row*
ws.Range("A2:G" & lastRow).Interior.ColorIndex = xlNone*
errorCount = 0*
For i = 2 To lastRow*
rowHasError = False*
If Trim(ws.Cells(i, 1).Value) = "" Then*
ws.Cells(i, 1).Interior.Color = RGB(255, 199, 206)*
rowHasError = True*
End If*
If Trim(ws.Cells(i, 2).Value) = "" Then*
ws.Cells(i, 2).Interior.Color = RGB(255, 199, 206)*
rowHasError = True*
End If*
If Trim(ws.Cells(i, 3).Value) = "" Then*
ws.Cells(i, 3).Interior.Color = RGB(255, 199, 206)*
rowHasError = True*
End If*
If Trim(ws.Cells(i, 5).Value) = "" Then*
ws.Cells(i, 5).Interior.Color = RGB(255, 199, 206)*
rowHasError = True*
End If*
If Trim(ws.Cells(i, 6).Value) = "" Then*
ws.Cells(i, 6).Interior.Color = RGB(255, 199, 206)*
rowHasError = True*
End If*
If rowHasError = True Then*
errorCount = errorCount + 1*
End If*
Next i*
If errorCount = 0 Then*
MsgBox "Пустых обязательных ячеек не найдено.", vbInformation*
Else*
MsgBox "Найдено строк с пропусками: " & errorCount, vbExclamation*
End If*End Sub
Как устроен этот макрос — спокойно и по шагам
Теперь разберём каждую часть так, чтобы читатель не просто скопировал код, а понял логику. Именно такие блоки лучше всего работают на ощущение роста навыка. А для серии 7 это одна из главных задач.
1. Объявляем переменные
Сначала создаём переменные, которые нужны макросу.
ws — лист с таблицей.
lastRow — последняя заполненная строка.
i — счётчик цикла.
errorCount — количество строк, где найдены пропуски.
rowHasError — индикатор, есть ли ошибка в текущей строке.
Здесь всё как в нормальной работе: сначала готовим инструменты, потом начинаем проверку.
2. Указываем лист, который нужно проверять
Set ws = ThisWorkbook.Worksheets("Данные")
Макрос ищет лист Данные. Если твой лист называется иначе — меняешь только это имя.
Например, если лист называется Заявки, строка будет такой:
Set ws = ThisWorkbook.Worksheets("Заявки")
3. Определяем последнюю строку
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Здесь VBA находит последнюю заполненную строку в столбце A. Это стандартный и очень полезный приём, который позволяет работать с таблицей любой длины.
Сегодня у тебя 35 строк. Завтра 180. Код не нужно переписывать, и это уже маленькая победа над рутиной.
4. Убираем старую подсветку
ws.Range("A2:G" & lastRow).Interior.ColorIndex = xlNone
Очень важная строка. Перед новой проверкой макрос снимает старую заливку, чтобы результат был актуальным. Иначе получится классическая офисная каша: часть ошибок уже исправили, а красные ячейки остались, как призраки вчерашней смены.
Здесь диапазон A2:G выбран под нашу структуру таблицы. Если столбцов больше или меньше, это легко меняется.
5. Обнуляем счётчик ошибок
errorCount = 0
С этого момента начинается новая проверка. Если не сбросить счётчик, он будет тащить за собой старые значения, а нам нужна чистая картина.
6. Запускаем цикл по всем строкам
For i = 2 To lastRow
Начинаем со второй строки, потому что первая — заголовки.
7. Сбрасываем флаг ошибки для текущей строки
rowHasError = False
Перед проверкой каждой строки макрос считает, что ошибок в ней нет. Дальше он будет искать пустые ячейки и при необходимости менять этот флаг на True.
8. Проверяем дату
If Trim(ws.Cells(i, 1).Value) = "" Then
ws.Cells(i, 1).Interior.Color = RGB(255, 199, 206)*
rowHasError = True*End If
Если в ячейке A пусто, она подсвечивается светло-красным. Это хороший рабочий цвет: заметный, но не кислотный. И одновременно строка помечается как проблемная.
9. Проверяем клиента
If Trim(ws.Cells(i, 2).Value) = "" Then
ws.Cells(i, 2).Interior.Color = RGB(255, 199, 206)*
rowHasError = True*End If
То же самое для столбца B.
Trim здесь тоже важен. Он убирает пробелы по краям. Иногда ячейка выглядит заполненной, а внутри там один пробел — классика жанра, особенно если кто-то чистил данные наспех. Trim помогает такие штуки не пропускать.
10. Проверяем задачу
If Trim(ws.Cells(i, 3).Value) = "" Then
ws.Cells(i, 3).Interior.Color = RGB(255, 199, 206)*
rowHasError = True*End If
Теперь столбец C.
11. Проверяем ответственного
If Trim(ws.Cells(i, 5).Value) = "" Then
ws.Cells(i, 5).Interior.Color = RGB(255, 199, 206)*
rowHasError = True*End If
Обрати внимание: мы специально пропустили столбец D. Значит, в нашей логике сумма сейчас не обязательна. Это важный момент: макрос не обязан проверять всё подряд. Он должен проверять именно те поля, без которых процесс ломается.
12. Проверяем статус
If Trim(ws.Cells(i, 6).Value) = "" Then
ws.Cells(i, 6).Interior.Color = RGB(255, 199, 206)*
rowHasError = True*End If
Столбец F — статус.
13. Увеличиваем счётчик проблемных строк
If rowHasError = True Then
errorCount = errorCount + 1*End If
Если хотя бы одно обязательное поле пустое, строка считается проблемной. Именно строка, а не количество отдельных ячеек. Это обычно удобнее для работы: пользователь видит не «12 пустых ячеек», а «4 строки требуют внимания».
14. Показываем итоговое сообщение
If errorCount = 0 Then
MsgBox "Пустых обязательных ячеек не найдено.", vbInformation*Else
MsgBox "Найдено строк с пропусками: " & errorCount, vbExclamation*End If
Если всё заполнено — отлично. Если нет — пользователь получает понятный итог и сразу видит проблемные места в таблице.
Почему эта проверка особенно ценна перед отчётом
Вот здесь возникает самое практичное применение. Если ты уже используешь сценарий из второй части — формирование ежедневного отчёта одной кнопкой, — то проверка заполненности должна идти раньше. Сначала проверили таблицу. Потом убрали пропуски. Потом собрали отчёт.
Именно так строится нормальная автоматизация: не просто «сделать быстро», а «сделать быстро и без глупых дыр».
На канале эту статью хорошо перелинковать с материалом про ежедневный отчёт и с первой частью о распределении задач по сотрудникам. Логика простая:
- сначала распределяем задачи;
- потом контролируем заполненность;
- потом формируем отчёт.
Так у читателя складывается не набор разрозненных макросов, а ощущение настоящей рабочей системы. А это уже другая ценность и другой уровень удержания.
Как изменить макрос под свою таблицу
Вот самая полезная часть для тех, кто хочет применить сценарий в работе, а не просто прочитать и пойти дальше.
Если лист называется по-другому
Замени строку:
Set ws = ThisWorkbook.Worksheets("Данные")
на своё имя листа.
Если обязательные столбцы другие
Сейчас проверяются столбцы 1, 2, 3, 5 и 6. То есть A, B, C, E, F.
Если нужно проверять ещё и сумму в столбце D, добавь такой блок:
If Trim(ws.Cells(i, 4).Value) = "" Then
ws.Cells(i, 4).Interior.Color = RGB(255, 199, 206)*
rowHasError = True*End If
Если комментарий в столбце G тоже обязателен, добавь ещё один аналогичный блок для седьмой колонки.
Если нужно не только подсвечивать, но и писать пометку
Можно добавить служебный столбец, например H, и записывать туда текст:
ws.Cells(i, 8).Value = "Есть пропуски"
Это удобно, если потом нужно фильтровать проблемные строки.
Если нужно проверять только конкретный диапазон, а не всю таблицу
Вместо общего диапазона можно ограничить проверку. Например, только строки с 2 по 100:
For i = 2 To 100
Но в живой работе лучше оставлять поиск по последней строке, чтобы макрос рос вместе с таблицей.
Ещё два сценария, где этот макрос особенно полезен
Для HR, рекрутера или администратора
Есть таблица кандидатов: имя, вакансия, дата интервью, статус, комментарий, ответственный. Если не указан статус или дата следующего шага, кандидат очень легко выпадает из процесса. Такой макрос быстро подсвечивает строки, которые провисают.
Для финансов и документооборота
Есть журнал оплат, актов, счетов, контрагентов, сумм и статусов. Пустое поле в такой таблице — уже не просто неудобство, а потенциальная путаница в деньгах или документах. Автоматическая проверка здесь работает как страховка.
Чем хорош этот сценарий — его можно встроить почти в любой процесс, где есть обязательные поля. А таких процессов в Excel, как песка у моря.
Микроистория, которую знают почти все, кто работает с таблицами
Обычно пустая ячейка не выглядит опасной. Пока не приходит момент, когда кто-то спрашивает: «А почему эта задача без срока?» Или: «Почему эта строка не попала в отчёт?» Или: «Кто вообще отвечает за этого клиента?» И тогда начинается археология — люди копаются в таблице, переписке, памяти, чатах, старых файлах, пытаясь восстановить, что же имелось в виду.
А ведь вся эта драма часто начинается с одной маленькой пустоты в одной маленькой ячейке.
Макрос тут работает почти прозаично. Он не спасает мир. Он просто не даёт пустоте пройти дальше по процессу. Но именно такие скучные вещи сильнее всего экономят нервы.
Что логично положить в файл для Telegram к этой статье
К этой части хорошо подходит практический файл, в котором будет:
- лист с примером таблицы;
- готовая кнопка проверки;
- макрос для поиска пустых ячеек;
- отдельный пример, где сумма обязательна;
- отдельный пример, где комментарий обязателен;
- инструкция, какие строки кода менять под свою структуру.
Вот такие файлы особенно хорошо работают на сохранения. Потому что человек видит не просто текст, а инструмент, который можно взять в работу почти без раскачки.
Где ещё использовать этот подход
Механику проверки можно легко расширить. Не только на пустые значения, но и на:
- неверный формат даты;
- отсутствие суммы больше нуля;
- незаполненный статус при наличии клиента;
- отсутствие ответственного при статусе «В работе»;
- пустой комментарий при статусе «Просрочено».
Вот тут VBA особенно хорош: сначала ты делаешь простую проверку, потом чуть дорабатываешь, и обычная таблица превращается в почти умный контрольный лист. Не искусственный интеллект, конечно, но уже и не бумажный сарай с колонками.
Вывод
Автоматизация в Excel полезна не только там, где что-то нужно красиво собрать, разнести или посчитать. Иногда самый ценный сценарий — это проверка на входе. Убедиться, что данные полные. Что в таблице нет дыр. Что пустая ячейка не уедет дальше в отчёт, письмо, аналитику или задачу сотруднику.
Именно поэтому макрос проверки заполненности — один из самых недооценённых и самых рабочих инструментов в серии. Он не производит шоу, но создаёт порядок. А порядок в Excel почти всегда дороже эффектных трюков.
Сохраните статью, если работаете с таблицами, которые заполняют несколько человек или которые потом уходят в отчёты. А файл с готовым примером и макросом забирайте в Telegram — там будет версия с расширенной проверкой и пометкой проблемных строк.
Подписывайтесь на канал, если хотите выстроить не просто набор отдельных макросов, а цельную систему автоматизации.
В следующей части разберём ещё более живой сценарий: как автоматически создавать новые листы по шаблону, чтобы Excel сам подготавливал нужную структуру без ручного копирования и бесконечного «создать лист — переименовать — оформить заново».