Типичная задача, которая периодически возникает перед каждым пользователем Excel — сравнить два диапазона с данными и найти различия между ними. Метод решения в этом случае определяется типом исходных данных.
Вариант 1. Синхронные списки
Если списки синхронизированы (отсортированы), то все делается очень просто, поскольку необходимо, по сути, сравнивать значения в соседних ячейках каждой строки. В качестве самого простого варианта мы используем формулу сравнения значений, которая на выходе выдаёт логические значения ИСТИНА (ИСТИННЫЙ) or ЛЕЖА (ЛОЖНЫЙ):
Количество несовпадений можно рассчитать по формуле:
=СУММПРОИЗВ(—(A2:A20<>B2:B20))
или по-английски =СУММПРОИЗВ(—(A2:A20<>B2:B20))
Если результат равен нулю, списки идентичны. В остальном у них есть различия. Формулу необходимо вводить как формулу массива, т.е. после ввода формулы в ячейку не нажимайте Enter, А также Shift + Ctrl + Enter.
Если вам нужно что-то сделать с разными ячейками, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопка Выделите (Особый) - Различия в линиях (Различия в строках). В последних версиях Excel 2007/2010 также можно использовать кнопку Найти и выбрать (Найти и выбрать) – Выбор группы ячеек (Перейти в раздел Специальные) таб Главная (Главная)
Excel будет выделять ячейки, которые различаются по содержимому (по строкам). Затем их можно обработать, например:
- заполнить цветом или как-то визуально отформатировать
- очистить с помощью ключа Удалить
- заполните все сразу одним и тем же значением, введя его и нажав Ctrl + Enter
- удалить все строки с выделенными ячейками с помощью команды Главная — Удалить — Удалить строки с листа (Главная страница — Удалить — Удалить строки)
- и так далее
Вариант 2: Перетасованные списки
Если списки разного размера и не отсортированы (элементы расположены в другом порядке), то придется идти другим путем.
Самое простое и быстрое решение — включить цветовое выделение различий с помощью условного форматирования. Выберите оба диапазона с данными и выберите на вкладке Главная – Условное форматирование – Правила выделения ячеек – Повторяющиеся значения:
Если вы выберете опцию Повторяющиеся, то Excel выделит совпадения в наших списках, если опция Уникальные – различия.
Однако цветовая подсветка не всегда удобна, особенно для больших таблиц. Также, если элементы могут повторяться внутри самих списков, то этот метод не сработает.
В качестве альтернативы вы можете использовать функцию COUNTIF (СЧЁТЕСЛИ) из категории Статистический, который подсчитывает, сколько раз каждый элемент из второго списка встречается в первом:
Полученный ноль указывает на различия.
И, наконец, «высший пилотаж» — различия можно вывести отдельным списком. Для этого вам придется использовать формулу массива:
Выглядит устрашающе, но со своей задачей справляется отлично 😉
- Выделить дубликаты в списке цветом
- Сравнение двух диапазонов с дополнением PLEX
- Запрет ввода повторяющихся значений