Сравнить два столбца в Excel
Найти, что есть в одном списке и нет в другом: сверка выгрузок, клиентов, номенклатуры. Основной инструмент — СЧЁТЕСЛИ, готовый файл помечает совпадения и выводит расхождения сам.
Коротко
Задача сверки одна и та же, как её ни назови: есть два списка — контрагенты, артикулы, две выгрузки за разные дни — и надо понять, что в них общего, а что разошлось. Кто отвалился, кто добавился, где задвоение. Самый надёжный способ — СЧЁТЕСЛИ: он для каждого значения одного списка считает, сколько раз оно встречается во втором. Ноль — значит такого во втором списке нет.
Разложим по столбцам. Список 1 лежит в B5:B44, Список 2 — в D5:D44. Рядом с каждым списком — столбец с пометкой: напротив значения из первого списка формула ставит «да», если оно нашлось во втором, и «НЕТ», если не нашлось. Отдельный столбец собирает подряд, без пустот, всё, что помечено «НЕТ», — это и есть «только в первом списке».
На примере двух списков контрагентов видно сразу. В первом: ООО «Ромашка», ИП Петров, ООО «Восход», ООО «Заря», ЗАО «Луч». Во втором: ООО «Ромашка», ООО «Восход», ООО «Ветер», ИП Сидоров, ООО «Заря». Общие — «Ромашка», «Восход», «Заря»; только в первом — ИП Петров и ЗАО «Луч»; только во втором — ООО «Ветер» и ИП Сидоров. Всё это лежит в файле: меняете списки в жёлтых ячейках — пометки и расхождения пересчитываются.
То же самое формулой в Excel
=ЕСЛИ($B5="";"";ЕСЛИ(СЧЁТЕСЛИ($D$5:$D$44;$B5)>0;"да";"НЕТ")) — есть ли значение из первого списка во втором; НЕТ — оно только в первом=ЕСЛИ($D5="";"";ЕСЛИ(СЧЁТЕСЛИ($B$5:$B$44;$D5)>0;"да";"НЕТ")) — то же для второго списка: ищем каждое его значение в первом=ЕСЛИОШИБКА(ИНДЕКС($B$5:$B$44;НАИМЕНЬШИЙ(ЕСЛИ($C$5:$C$44="НЕТ";СТРОКА($B$5:$B$44)-4);СТРОКА()-4));"") — формула массива (Ctrl+Shift+Enter): собирает в отдельный столбец всё, что помечено НЕТ=СЧЁТЕСЛИ($C$5:$C$44;"НЕТ") — сколько значений первого списка не нашлось во втором — счётчик расхожденийСверка двух списков: помечает совпадения и выводит разницу
Песочница на два списка. Вставляете первый в один столбец, второй — в другой, и файл сам помечает «да/НЕТ» напротив каждой строки, отдельными столбцами собирает «только в первом» и «только во втором» и считает, сколько всего разошлось. СЧЁТЕСЛИ без ВПР и без #Н/Д — работает в Excel 2016 и в Google Таблицах.
Файл
sravnit-dva-spiska.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Положите списки в два столбца рядом. В примере первый список —
B5:B44, второй —D5:D44. - В свободную ячейку напротив первой строки первого списка (в примере
C5) впишите=ЕСЛИ(СЧЁТЕСЛИ($D$5:$D$44;$B5)>0;"да";"НЕТ"). СЧЁТЕСЛИ считает, сколько раз значение изB5встречается во втором списке; больше нуля — «да», ноль — «НЕТ». - Диапазон второго списка закрепите знаком
$(клавиша F4), а ссылку на проверяемую ячейку — нет: тогда формулу можно протянуть вниз, и она поедет по строкам первого списка, но будет искать в том же втором. - Потяните за уголок ячейки вниз до конца списка. Напротив каждого значения появится «да» или «НЕТ».
- Чтобы сверить в обратную сторону, поставьте такую же формулу напротив второго списка, поменяв диапазоны местами:
=ЕСЛИ(СЧЁТЕСЛИ($B$5:$B$44;$D5)>0;"да";"НЕТ"). - Отфильтруйте столбец с пометками по значению «НЕТ» — останутся строки, которых в другом списке нет. Если фильтровать не хочется, «только в первом списке» соберёт в отдельный столбец формула массива из блока выше.
Где обычно ломается
Три вещи, из-за которых сверка врёт чаще всего.
- СЧЁТЕСЛИ надёжнее ВПР для этой задачи. ВПР для проверки «есть или нет» приходится оборачивать в ЕСЛИОШИБКА, иначе он сыплет
#Н/Д. СЧЁТЕСЛИ ошибку не выдаёт вообще: он просто считает совпадения и сразу отвечает «да» или «НЕТ». Для сверки двух списков берите его. - Столбец «только в списке» — это формула массива. В Excel 2016 её вводят не Enter, а сочетанием Ctrl+Shift+Enter — вокруг формулы появятся фигурные скобки
{ }, ставить их руками не нужно. В Google Таблицах и Excel 365 та же формула работает обычным Enter: там массивы разворачиваются сами. Введёте её в Excel 2016 простым Enter — получите одно значение вместо списка. - Регистр и лишние пробелы. «ООО Ромашка» и «ООО Ромашка» с двойным пробелом СЧЁТЕСЛИ сочтёт разными значениями и пометит «НЕТ» на пустом месте. Букв он, наоборот, регистром не различает: «ромашка» и «Ромашка» для него одно и то же — а вот пробелы различает. Перед сверкой прогоните оба списка через
СЖПРОБЕЛЫ: она убирает двойные и крайние пробелы, оставляя по одному между словами.