Как найти дубликаты и пропущенные значения в больших таблицах Excel

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

1. Выделите диапазон с данными, например A2.
2. Откройте вкладку «Главная».
3. Выберите «Условное форматирование».
4. Перейдите в раздел «Правила выделения ячеек».
5. Нажмите «Повторяющиеся значения».
6. Выберите формат выделения и подтвердите действие.

Excel подсветит значения, которые встречаются в диапазоне более одного раза.

Когда использовать: для проверки номеров счетов, ИНН, табельных номеров, кодов товаров и других идентификаторов.

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

Если необходимо не только выделить повторы, но и присвоить каждой записи статус, можно использовать функцию СЧЁТЕСЛИ.

Предположим, в столбце A указаны номера документов. В ячейке B2 введите:
=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$10000;A2)>1;"Дубликат";"Уникальное значение")

Протяните формулу вниз по столбцу
Excel подсчитает, сколько раз значение из A2 встречается в заданном диапазоне. Если больше одного раза — появится статус «Дубликат».

Если требуется выделить только второе и последующие появления значения, а первое оставить без отметки, используйте:
=ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A2)>1;"Повтор";"Первое вхождение")

Такой подход удобен, если таблицу нужно дополнительно фильтровать, сортировать или передавать коллегам для проверки.

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

Допустим, в столбце A находится дата операции, в B — номер документа, а в C — сумма.

Чтобы найти записи, у которых совпадают все три поля, можно использовать формулу:
=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$10000;A2;$B$2:$B$10000;B2;$C$2:$C$10000;C2)>1;"Возможный дубликат";"Проверка пройдена")

Формула отмечает записи с одинаковой датой, номером документа и суммой.

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

Пропущенные значения — это пустые ячейки в полях, которые должны быть заполнены.

Например, в реестре платежей может отсутствовать дата операции, а в кадровой таблице — должность или табельный номер сотрудника.

Если нужно быстро проверить большой диапазон:

1. Выделите таблицу или нужный столбец.
2. Нажмите Ctrl + G или F5.
3. Выберите «Выделить».
4. Укажите «Пустые ячейки».
5. Подтвердите выбор.

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

Этот метод помогает быстро обнаружить пропуски, но не определяет, являются ли они ошибками. Например, отсутствие даты увольнения у действующего сотрудника может быть нормальным.

Предположим, в столбце A находятся номера документов. Чтобы проверить наличие пропусков, в B2 введите:
=ЕСЛИ(A2="";"Не заполнено";"Заполнено")

После копирования формулы вниз каждая строка получит соответствующий статус.

Для более точной проверки ячейки на отсутствие значения можно использовать функцию ЕПУСТО:
=ЕСЛИ(ЕПУСТО(A2);"Не заполнено";"Заполнено")

Разница заключается в том, что ЕПУСТО проверяет, действительно ли ячейка пустая. Если в ней находится формула, возвращающая пустую строку, например ="", функция ЕПУСТО вернёт ложь, хотя визуально ячейка будет выглядеть пустой.

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

Если дата находится в A, номер документа — в B, сумма — в C, можно использовать:
=ЕСЛИ(ИЛИ(A2="";B2="";C2="");"Проверить запись";"Заполнено")

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

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

Фильтры позволяют быстро отобрать проблемные строки и проверить их без изменения исходных данных.

Для поиска пустых значений:

1. Выделите таблицу с заголовками.
2. Откройте вкладку «Данные» и включите «Фильтр».
3. Нажмите стрелку в заголовке нужного столбца.
4. Выберите пункт «Пустые» или соответствующий вариант фильтрации, доступный в вашей версии Excel.

Для проверки повторов сначала создайте вспомогательный столбец с формулой СЧЁТЕСЛИ, а затем отфильтруйте строки со статусом «Дубликат».

Этот способ особенно полезен, когда в таблице тысячи строк и необходимо быстро сформировать список записей для дополнительной проверки.

Если данные регулярно поступают из нескольких файлов, ручная проверка может занимать много времени. В таких случаях стоит использовать Power Query — инструмент Excel для импорта, преобразования и объединения данных.

С его помощью можно:

• удалять дубликаты по выбранным столбцам;
• фильтровать пустые значения;
• проверять корректность типов данных;
• объединять таблицы из разных файлов;
• настраивать повторяемую последовательность действий;
• обновлять результаты после поступления новых данных.

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

При поступлении новых файлов запрос можно обновить, чтобы повторить сохранённые преобразования.

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

Предположим, бухгалтер получил таблицу из 8 000 строк. В ней находятся дата операции, номер документа, контрагент и сумма.

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


Такой алгоритм помогает системно проверять данные и снижает риск ошибок при подготовке отчётности.

При этом обнаружение дубликата не означает, что запись обязательно нужно удалить. Сначала необходимо выяснить причину повторения и проверить исходные документы.

Даже при использовании автоматических инструментов некоторые проблемы остаются незаметными.

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

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

Лишние пробелы мешают сопоставлению. Значения «ООО Альфа» и «ООО Альфа » могут обрабатываться как разные строки. Перед проверкой текстовых данных полезно удалить лишние пробелы.

Числа и текст смешиваются. Один и тот же идентификатор может храниться в разных форматах. Это способно затруднить поиск повторов и сопоставление таблиц.

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

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

Учебный центр «Точка Роста» предлагает обучение Excel и профессиональные курсы по бухгалтерскому учёту. Практическая направленность обучения помогает развивать навыки, которые применяются при работе с документами, реестрами и отчётностью.

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

Excel предлагает несколько способов обнаружения дубликатов и пропущенных значений: условное форматирование, формулы СЧЁТЕСЛИ и СЧЁТЕСЛИМН, поиск пустых ячеек, фильтры и Power Query.

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

Главное — правильно определить критерии дубликатов, проверить обязательные поля и не удалять записи без предварительного анализа.