Сравнение списков в Excel: 3 эффективных метода
Сравнение списков данных — одна из самых частых задач в Excel. Вместо того чтобы создавать множество столбцов с формулами ВПР (VLOOKUP), можно использовать более эффективные и наглядные методы. В этой статье мы разберем три способа сравнения списков, включая использование сводных таблиц, что является оптимальным решением для анализа изменений во времени.
Команды и сочетания клавиш ниже приведены для настольного Excel для Windows. В других версиях расположение команд может отличаться.
Метод 1: Сводная таблица для сравнения нескольких списков
Если вам нужно сравнить несколько версий списка (например, списки участников за понедельник, вторник и среду), сводная таблица — ваш лучший выбор. Этот метод позволяет наглядно увидеть, кто добавился, кто выбыл и какие изменения произошли.
- Объедините все списки в один. Создайте единый список, добавив новый столбец «Источник» (Source). В этом столбце укажите, к какому исходному списку (понедельник, вторник и т.д.) относится каждая запись.
- Создайте сводную таблицу. Выделите объединенный диапазон и вставьте сводную таблицу (Вставка → Сводная таблица).
- Настройте поля сводной таблицы:
- Перетащите поле «Имя» (Name) в область «Строки».
- Перетащите поле «Источник» (Source) в область «Столбцы».
- Для проверки наличия перетащите непустое поле «Имя» также в область «Значения» и выберите «Количество». Для сравнения числовых показателей добавьте соответствующее числовое поле и выберите «Сумма».
- Отключите итоги. В параметрах сводной таблицы отключите строку и столбец общих итогов для более чистого вида.
В результате вы получите компактную таблицу, где по строкам — все уникальные имена, а по столбцам — дни. На пересечении будет видно наличие или значение для каждого дня, что сразу покажет изменения.
Метод 2: Сравнение двух списков с помощью «Перейти к выделенному»
Этот метод сравнивает значения в одной и той же строке. Он не ищет элементы списка в произвольном порядке.
- Выделите диапазон в первом столбце (например,
A2:A9). - Удерживая клавишу
Ctrl, выделите соответствующий диапазон во втором столбце (например,D2:D9). Проверьте, какая ячейка активна: сравнение идёт с её столбцом. При необходимости клавишейTabпереместите активную ячейку в столбец A, не снимая выделения. - Перейдите на вкладку «Главная» → «Найти и выделить» → «Перейти к выделенному».
- В диалоговом окне выберите опцию «Различающиеся по строкам» и нажмите «ОК».
Если активная ячейка находится в столбце A, Excel выделит отличающиеся значения в столбце D. При активной ячейке в D результат будет обратным. Выделенные ячейки можно залить цветом.
Важно: Этот метод работает корректно, только если списки идеально синхронизированы по строкам. Добавление или удаление строки в одном из списков сместит сравнение и даст некорректный результат.
Спасибо Коллин Янг за этот совет.
Метод 3: Выделение совпадений с помощью условного форматирования
Правило «Повторяющиеся значения» ищет повторы во всей выделенной области, в том числе внутри одного списка. Если каждый список содержит только уникальные значения, это позволяет увидеть общие элементы двух списков.
- Выделите диапазон в первом списке (например,
B4:B16). - Удерживая
Ctrl, выделите диапазон во втором списке (например,E4:E16). - На вкладке «Главная» выберите «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения».
- В диалоговом окне оставьте значение «Повторяющиеся» и выберите формат заливки (например, светло-розовый). Нажмите «ОК».
При наличии повторов внутри одного списка такое правило не доказывает совпадение между списками. Для проверки B4:B16 по второму списку создайте правило «Использовать формулу» с формулой =СУММПРОИЗВ(--($E$4:$E$16=B4))>0 (английская: =SUMPRODUCT(--($E$4:$E$16=B4))>0), применяемое к $B$4:$B$16. Для E4:E16 создайте обратное правило =СУММПРОИЗВ(--($B$4:$B$16=E4))>0. Пустые ячейки исключите из диапазонов либо добавьте проверку на непустое значение.
Спасибо Джилл Барнетт за этот совет.
Заключение
Выбор метода сравнения списков в Excel зависит от задачи. Для анализа изменений в нескольких списках во времени идеально подходит сводная таблица. Для быстрой проверки двух столбцов на идентичность используйте «Перейти к выделенному». А для визуального выделения общих элементов в двух диапазонах отлично работает условное форматирование с проверкой второго диапазона. Освоив эти три приема, вы сможете эффективно работать с любыми данными.