Сравнить две таблицы Excel онлайн — найти новые, удалённые и изменённые строки
Загрузите две версии прайса, выгрузки или списка: Формулник сопоставит строки по ключу и покажет различия.
Формулник сам поймёт, что нужно: формула, таблица, документ или правка файла — и проверит результат.
Как сравнить две таблицы Excel и найти, что изменилось
Две версии таблицы сравнивают по ключу — артикулу, ИНН, номеру договора или табельному номеру, — а не по номеру строки: тогда сортировка и вставленные строки не мешают. Инструмент берёт файлы «Было» и «Стало», находит новые строки, исчезнувшие строки и изменённые ячейки и выдаёт новую книгу с листами «Сводка», «Различия», «Новые» и «Удалённые». Для чисел в «Различиях» указан процент изменения, а на странице сразу видны итоги числовых столбцов «было — стало — разница»: сколько стала стоить вся поставка или на сколько изменился фонд оплаты.
Исходные файлы не меняются. Вручную в Excel то же делают через поиск ключа в другой таблице: =ЕСЛИОШИБКА(ПОИСКПОЗ(A2;Было!A:A;0);"новая").
По шагам
- Проверьте, что у обеих таблиц одна строка заголовков и одинаковые названия столбцов. Титулы над таблицей вроде «Период: октябрь» из отчётов 1С пропускаются сами.
- Убедитесь, что ключ уникален: один артикул — одна строка. Если ключ повторяется, сравнится первая строка, а число повторов попадёт в «Сводку».
- Загрузите старую версию в поле «Было», новую — в «Стало».
- В списке «Сопоставлять строки по столбцу» выберите ключ или оставьте «найти само»: инструмент возьмёт столбец с неповторяющимися значениями — код, артикул, ИНН — и напишет под результатом, какой столбец выбран.
- Нажмите «Запустить» и посмотрите счётчики: новых, удалённых, изменённых и без изменений. Ниже — таблица итогов по каждому числовому столбцу: сумма в «Было», в «Стало» и разница.
- Скачайте книгу: на «Различиях» новые значения подсвечены жёлтым, на «Новых» — зелёным, на «Удалённых» — розовым.
Новый прайс поставщика
Ключ — артикул. На листе «Различия» видно, у каких позиций выросла цена и на сколько процентов, на «Новых» — что появилось в ассортименте, на «Удалённых» — что поставщик больше не возит. Отсортируйте «Различия» по столбцу «Изменение, %» по убыванию — сверху окажутся самые сильные подорожания. Итог по столбцу «Цена» на странице покажет, насколько подорожал прайс в целом.
Два списка сотрудников, клиентов или учеников
ФИО — плохой ключ: «Ёлкина» и «Елкина», двойные фамилии и тёзки дают ложные различия. Берите табельный номер, номер договора, СНИЛС или ИНН. Если ключа нет, сделайте его в обоих файлах до загрузки: =СЦЕП(A2;"|";B2) — например, фамилия плюс дата рождения.
Остатки до и после инвентаризации
Выгрузите остатки из 1С до и после пересчёта. Ключ — код номенклатуры. Изменённые количества с процентами на листе «Различия» — это и есть расхождения, которые нужно объяснить.
Что лежит на листах результата
| Лист | Что на нём | Подсветка |
|---|---|---|
| Сводка | Имена файлов, ключ, число новых, удалённых, изменённых строк и повторов ключа | — |
| Различия | Ключ, столбец, было, стало, изменение в процентах | Новое значение — жёлтым |
| Новые | Строки, которых не было в старой версии | Зелёным |
| Удалённые | Строки, которых нет в новой версии | Розовым |
Частые ошибки
- Столбец называется «Цена» в одном файле и «Цена, руб.» в другом — Сравниваются только столбцы с одинаковыми названиями (регистр и лишние пробелы не важны). Переименуйте заголовок до загрузки, иначе цена не попадёт в сравнение.
- Сравнивают по номеру строки, а не по ключу — После сортировки или вставки одной строки все строки ниже «изменятся». Инструмент сопоставляет только по ключу, поэтому порядок строк не важен.
- В файле формулы, сохранённые программой без значений — Читаются сохранённые значения ячеек, а не сами формулы. Если файл собран скриптом и не открывался в Excel, откройте и сохраните его — значения запишутся.
Вопросы и ответы
Как сравнить две таблицы Excel на совпадения?
Вручную — функцией ПОИСКПОЗ или ВПР по ключевому столбцу: ошибка #Н/Д значит, что строки нет в другой таблице. Быстрее загрузить обе версии в инструмент выше: он сразу разложит строки на новые, удалённые и изменённые.
Как сравнить два столбца в Excel и найти отличия?
Для двух столбцов одной длины подойдёт =СОВПАД(A2;B2) — ЛОЖЬ означает различие с учётом регистра. Для значений, которые стоят в разном порядке, используйте =ЕНД(ПОИСКПОЗ(A2;B:B;0)): ИСТИНА — значения из A нет в B.
Можно ли сравнить таблицы, если строки идут в разном порядке?
Да, если есть ключ. Сравнение идёт по совпадению ключа, порядок строк и их число в версиях могут быть любыми.
Считаются ли «1 240,50» и 1240,5 разными значениями?
Нет. Число, записанное текстом с пробелами и запятой, приводится к числу, а в тексте не учитываются регистр и лишние пробелы. Поэтому выгрузка из 1С и та же таблица из Excel не дают ложных различий.
Какие файлы можно сравнить?
Книги .xlsx и .xlsm и файлы CSV до 5 МБ каждый, до 100 000 строк и 100 столбцов. Из книги берётся первый лист — нужную таблицу перенесите на первый лист или сохраните отдельно.
Что будет с моими файлами?
Исходники только читаются и не изменяются; результат — новая книга Формулника, файлы хранятся 24 часа на серверах в России. Без входа доступно до 30 запусков инструментов в сутки.
- Справка Microsoft: функции ПОИСКПОЗ и ВПР
- Справка Microsoft: средство сравнения электронных таблиц (Spreadsheet Compare) в Office профессиональный плюс