Как сравнить списки в excel

Как в excel сравнить два списка на совпадения

​Смотрите также​​ группе команд «Библиотека​ одинаковых, но есть​ формула — =А1=В1​​В итоге получим слияние​​Таблица2​.​​Минусы​​ честнок. ), что-то пропало​​ и не отсортированы​​ЛОЖЬ (FALSE)​​ считаться совпадающими, а​ то формулы в​полностью совпадающими​ С2. =СУММ(ЕСЛИ(A2:A6<>B2:B6;1;0)) Нажимаем​ как прибавить к​Если мы сейчас нажмём​Есть несколько способов,​ функций» и выберите​ записи, которые либо​ результатом будет или​
​ данных из обеих​​, которые получаются по-умолчанию).​Если изменятся цены (но​​тоже есть. Для​ (ежевика, малина. ), у​
​ (элементы идут в​:​ ​ ячейках​, если списки их​
​ «Enter». Копируем формулу​ дате число, т.д.,​ на мышь, то​как сравнить два столбца​ самую первую строку​ в первом списке​ ИСТИНА или ЛОЖЬ​ таблиц:​Загрузите старый прайс в​ ​ не количество товаров!),​
​ поиска добавленных в​ каких-то товаров изменилась​ ​ разном порядке), то​
​Число несовпадений можно посчитать​ и ​ — «ЕСЛИ». Появившаяся​ отсутствуют, либо во​2) Выделение различий​Названия столбцов в шапке​​ Power Query с​ то достаточно просто​ новый прайс товаров​ цена (инжир, дыня. ).​ придется идти другим​ формулой:​ 1> – различными.​=СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0)​
​ и каждое уникальное​ в столбце с​ «Дата в Excel.​ Чтобы эти выделенные​ совпадения​ форма заполнения аргументов​ втором.мне нужно сравнить​Выделите оба столбца​ лучше, конечно, переименовать​ помощью кнопки​ обновить созданную сводную,​ придется делать такую​ Нужно быстро найти​
​ путем.​
​=СУММПРОИЗВ(—(A2:A20<>B2:B20))​Для определения совпадения используем​ ​ вернут 0. Если​
​ значения имеет одинаковое​ разницей будут стоять​ Формула» здесь.​ ячеек оставить, мы​,​ будет содержать три​ их и расхождение​ и нажмите клавишу​ ​ двойным щелчком на​ ​Из таблицы/диапазона (From Table/Range)​
​ щелкнув по ней​ же процедуру в​ и вывести все​Самое простое и быстрое​или в английском варианте​ формулу (см. Файл​ в ячейке​ количество повторов (сортировка​ цифры. Единица будет​Можно сравнить числа.​ можем закрасить эти​
​как сравнить две таблицы​ поля. В первом​ этих списков вывести.​
​ F5, затем в​ более понятные:​​с вкладки​​ правой кнопкой мыши​ обратную сторону, т.е.​ эти изменения.​ решение: включить цветовое​ =SUMPRODUCT(—(A2:A20<>B2:B20))​ примера):​Е1​ может быть любая);​ стоять, если есть​Функцию выделения ячеек можно​ ячейки или изменить​ Excel​ из них -​на листе 1​ открывшемся окне кнопку​А теперь самое интересное.​Данные (Data)​ -​
​ подтягивать с помощью​Для любой задачи в​ выделение отличий, используя​Если в результате получаем​=СУММПРОИЗВ(—(A2:A10<>B2:B10))​не 0, а​2. Списки считаются​
​ различия, а «нуль»​
​ вызвать клавишей F5.​ цвет шрифта в​,​ «Лог_выражение» — сформулируйте​ — один список,​
​ Выделить — Отличия​
​ Идем на вкладку​​или с вкладки​Обновить (Referesh)​ ​ ВПР новые цены​​ Excel почти всегда​ условное форматирование. Выделите​
​ ноль — списки​Формула попарно сравнивает элементы​ в ячейках​частично совпадающими​ — данные в​ В появившемся окне​ этих ячейках функциями​списки​ саму операцию сравнения.​​на листе 2​ по строкам. В​Добавить столбец (Add Column)​Power Query​.​ к старому прайсу.​ есть больше одного​ оба диапазона с​ идентичны. В противном​ каждого списка (A2:A10<>B2:B10).​Е2 F2​
​, если списки их​ ячейках одинаковые. Получится​ «Переход» нажимаем кнопку​ раздела «Шрифт» на​,​ Например, для выяснения​ — другой список​ последних версиях Excel​и жмем на​(в зависимости от​Плюсы​ ​ Если размеры таблиц​
​ решения (обычно 4-5).​ ​ данными и выберите​
​ случае — в​ Если различия не​0, то списки​ уникальных значений совпадают,​ так.​
​ «Выделить…».​
​ закладке «Главная», пока​даты в Excel​ тождественности значений в​и результат вывести​
​ 2007/2010 можно также​ кнопку​ версии Excel). После​: такой подход на​ завтра поменяются, то​ Для нашей проблемы​ на вкладке​ них есть различия.​ обнаружены (везде получено​ являются​ но количество повторов​Четвертый​Второй способ.​
​ эти ячейки выделены.​​. Не только сравнить​​ ячейках B3 и​
​ на 3 лист.​​ воспользоваться кнопкой Найти​​Условный столбец (Conditional Column)​ загрузки вернемся обратно​ порядок быстрее работает​ придется корректировать формулы.​ можно использовать много​Главная — Условное форматирование​ Формулу надо вводить​ значение ЛОЖЬ), то​частично совпадающими​ каждого элемента м.б.​
​с​
​Можно в​​ Например, так.​ ​ столбцы в Excel,​​ C3 напишите B3=C3.​кто сталкивался уже​ и выделить -​. А затем в​ в Excel из​
​ с большими таблицами,​ Ну, и на​ разных подходов:​ — Правила выделения​ как формулу массива,​ сумма этих значений​(их заголовки окрашиваются​ любое;​
​пособ.​Excel сравнить и выделить​Или так.​ но и выделить​
​ В поля «Значение_если_истина»​ с этим?дайте код​ Выделение группы ячеек​ открывшемся окне вводим​ Power Query командой​ чем ВПР.​

Сравнение 2-х списков в MS EXCEL

​ действительно больших таблицах​функцию​

​ ячеек — Повторяющиеся​​ т.е. после ввода​​ =0 (ЛОЖЬ в​ оранжевым цветом) ​

Задача

​3. Списки считаются​

​Можно​ цветом​Сравнить данные в нескольких​

​ разницу цветом шрифта,​​ и «Значение_если_ложь» поместите​​ макроса?.​ на вкладке Главная​ несколько условий проверки​Закрыть и загрузить -​Минусы​ (>100 тыс. строк)​

​ВПР (VLOOKUP)​​ значения (Home -​​ формулы в ячейку​ арифметических значениях заменяется​Если хотябы одна из​не совпадающими​объединить таблицы Excel​

​ячейки с разными данными​​ столбцах​​ ячеек, т.д. Один​ надписи или числа,​Инструкция​3) Подсветка различий​ с соответствующими им​ Закрыть и загрузить​

Решение

​: надо вручную копировать​ все это счастье​- искать названия​ Conditional formatting -​ жать не на​ нулем), и соответственно,​ вышеуказанных формул (ячейки​

​, если списки их​с отчетами. Тогда​с помощью условного​

• ​Excel.​​ способ сравнения, разместить​​ которые должны отображаться​1​ условным форматированием​ значениями на выходе:​ в. (Close &​ данные друг под​ будет прилично тормозить.​
  ​ товаров из нового​
  ​ Highlight cell rules​
  ​Enter​
  ​ списки совпадают.​
• ​Е2 F2​ уникальных значений не​ все одинаковые данные​ форматирования.​Здесь мы сравнили​
• ​ две таблицы на​ в случае положительного​Чтобы выяснить, совпадают​Алексей матевосов (alexm)​Останется нажать на​​ Load — Close​​ друга и добавлять​Скопируем наши таблицы одна​​ прайс-листа в старом​​ — Duplicate Values)​, а на​
• ​То есть, если 2​) возвращают не 0,​ совпадают (значения, которые​ соберутся в одну​Итак, мы выделяем​​ три столбца в​​ одном мониторе одновременно,​ или отрицательного результатов​ ли значения в​​: Еще вариант считать​​ОК​ & Load To. )​​ столбец с названием​​ под другую, добавив​ и выводить старую​​:​​Ctrl+Shift+Enter​ списка состоят из​
• ​ то списки считаются​ есть в одном​​ строку, но можно​​ столбцы с данными​ таблице, предварительно выделив​​ описан в статье​​ сравнения. Для завершения​

Тестируем

​ двух ячейках таблицы,​ сколько раз А1​​и выгрузить получившийся​​:​ прайс-листа. Если размеры​ столбец с названием​ цену рядом с​

​Если выбрать опцию​.​ разных элементов или​​не совпадающими​​ списке, в другом​ будет посмотреть и​ (без названия столбцов).​ все три столбца​ «Сравнение таблиц Excel».​ ввода аргументов функции​ используйте функцию «СОВПАД».​ встречается в В1:В10​ отчет в Excel​. и в появившемся​

Совпадают ли 2 списка в MS EXCEL? (попарное совпадение)

​ таблиц изменяются, то​ прайс-листа, чтобы потом​ новой, а потом​Повторяющиеся​Если с отличающимися ячейками​ из одинаковых элементов,​

​.​ отсутствуют).​​ отдельно данные по​​ На закладке «Главная»​ таблицы. Сравниваются данные​​Здесь рассмотрим,​​ нажмите кнопку OK.​

​ Например, если сравниваемые​Если количество равно​ с помощью все​ затем окне выбрем​ придется делать все​ можно было понять​ ловить отличия​, то Excel выделит​ надо что сделать,​ но списки отсортированы​1. В файле примера​Создадим для удобства 2​

​ магазинам. Как это​ в разделе «Стили»​ в ячейках построчно​
​как сравнить столбцы в​

​3​ величины помещены в​ единице, то ИСТИНА,​ той же кнопки​Только создать подключение (Connection​ заново.​ из какого списка​объединить два списка в​ цветом совпадения в​ то подойдет другой​

​ по-разному, то списки​ (диапазоны (​ Динамических диапазона Список1​ сделать, смотрите в​ нажимаем на кнопку​ (А2, В2, С2,т.д.).​ Excel​Если операцию сравнения​ ячейки с адресами​

​ в противном случае​Закрыть и загрузить (Close​ Only)​Power Query — это​ какая строка:​ один и построить​ наших списках, если​ быстрый способ: выделите​ считаются не совпадающими.​

​А26:B33А36:B41А44:B49​
​ и Список2, которые​ статье «Как объединить​ «Условное форматирование». Из​ Получилось так.​

Поиск отличий в двух списках

​, выделить разницу цветом,​ нужно произвести построчно​ B3 и C3,​ ЛОЖЬ​ & Load)​.​ бесплатная надстройка для​Теперь на основе созданной​ по нему потом​ опцию​

Вариант 1. Синхронные списки

​ оба столбца и​ Поэтому списки перед​) имеется 3 пары​ будут ссылаться на​ таблицы в Excel».​ появившегося списка выбираем​Как сравнить даты в​ символами, т.д.​ для двух колонок​ а результат их​Формула для ячейки​на вкладке​​Повторите то же самое​​ Microsoft Excel, позволяющая​​ таблицы создадим сводную​​ сводную таблицу, где​

​Уникальные​ нажмите клавишу​

​ сравнением нужно отсортировать.​

​ списков каждого типа:​ диапазоны ячеек, содержащие​

​Пятый способ.​ функцию «Создать правило».​Excel.​Например, несколько магазинов​ таблицы, поместите в​ сравнения надо отобразить​ С1​Главная (Home)​ с новым прайс-листом.​ загружать в Excel​​ через​​ наглядно будут видны​​- различия.​​F5​

​Аналогичное сравнение можно осуществить​ полностью совпадающие; частично​ значения в соответствующих​Используем​В строке «Формат…» пишем​Можно сравнить даты.​​ сдали отчет по​​ первую строку третьей​ в клетке D3,​​=СЧЁТЕСЛИ (B$1:B$10;A1)​​:​​Теперь создадим третий запрос,​ данные практически из​​Вставка — Сводная таблица​ отличия​Цветовое выделение, однако, не​​, затем в открывшемся​ без использования формул,​ совпадающие; не совпадающие.​ списках.​​функцию «СЧЕТЕСЛИ» в​​ такую формулу. =$А2<>$В2.​

​ Принцип сравнения дат​ продажам. Нам нужно​ колонки одну из​ кликните ее мышкой​

• ​После ввода формулу​Красота.​
• ​ который будет объединять​​ любых источников и​
• ​ (Insert — Pivot​использовать надстройку Power Query​ всегда удобно, особенно​​ окне кнопку​
• ​ например с помощью​2. Вставляя по очереди​Чтобы сравнить списки сделаем​​Excel​ Этой формулой мы​ тот же –​ сравнить эти отчеты​ описанных выше функций,​
• ​ и перейдите на​

Вариант 2. Перемешанные списки

​ протянуть.​Причем, если в будущем​ и сравнивать данных​ трансформировать потом эти​ Table)​ для Excel​

​ для больших таблиц.​Выделить (Special)​ инструмента Выделение группы​ указанные пары списков​ следующее (см. Файл​. Эта функция посчитает​ говорим Excel, что​​ выделяем столбцы, нажимаем​ и выявить разницу.​ а затем распространите​ вкладку «Формулы» в​Если в столбце​ в прайс-листах произойдут​ из предыдущих двух.​​ данные любым желаемым​

​. Закинем поле​​Давайте разберем их все​​ Также, если внутри​-​ ячеек (см. раздел​ в диапазон​​ примера):​​ количество повторов данных​

​ если данные в​ на кнопку «Найти​У нас такая​ ее на высоту​ меню Excel. В​ С все значения​ любые изменения (добавятся​ Для этого выберем​

​ образом. В Excel​Товар​​ последовательно.​ ​ самих списков элементы​​Отличия по строкам (Row​​ Отличия по строкам)​​A5:B19​Сформируем в столбце​ их первого столбца,​ ячейках столбца А​

​ и выделить». Выбираем​ таблица с данными​ сравниваемых колонок. Это​

​ группе команд «Библиотека​ ИСТИНА, то таблицы​ или удалятся строки,​ в Excel на​ 2016 эта надстройка​

​в область строк,​Если вы совсем не​ могут повторяться, то​

Сравнение двух таблиц

​ differences)​ или с использованием​, результат получим в​D​ сравнив их с​

​ не равны данным​ функцию «Выделение группы​ из магазинов. Как​ можно сделать, например,​ функций» раскройте выпадающий​ совпадают по данным.​ изменятся цены и​ вкладке​ уже встроена по​ поле​

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

• ​ в ячейках столбца​​ ячеек», ставим галочку​​ перенести данные из​ протянув вниз правый​ список «Текстовые» и​Чтобы не просматривать​ т.д.), то достаточно​Данные — Получить данные​ умолчанию на вкладке​
• ​Прайс​ замечательной функцией, то​ подойдет.​ Excel 2007/2010 можно​ дополнительном столбце.​ исходных списков (полностью​
• ​ значений для обоих​В дополнительном столбце​

​ В, то окрасить​ у слов «Отличия​

Способ 1. Сравнение таблиц функцией ВПР (VLOOKUP)

​ разных таблиц в​ нижний угол ячейки​ выберите в нем​ весь столбец, все​ будет лишь обновить​ — Объединить запросы​Данные (Data),​в область столбцов​ загляните сначала сюда​

​В качестве альтернативы можно​ также воспользоваться кнопкой​СОВЕТ:​ совпадающие списки имеют​ списков (см. статью​ устанавливаем формулы, они​ эти ячейки в​ по строкам». Нажимаем​ одну, читайте в​ с формулой -​

​ строку «СОВПАД». В​ ли там ИСТИНА,​ наши запросы сочетанием​ — Объединить (Data​а для Excel​ и поле​ и почитайте или​

​ использовать функцию​​Найти и выделить (Find​Более подробное сравнение​ заголовки зеленого цвета;​ Отбор уникальных значений​ разные с разными​

​ красный свет.​​ «ОК».​ статье «Как связать​ при наведении на​ результате на экране​ можно в С11​ клавиш Ctrl+Alt+F5 или​ — Get Data​ 2010-2013 ее нужно​Ц​ посмотрите видеоурок по​СЧЁТЕСЛИ​ & Select) -​ 2-х списков описано​ частично совпадающие -​ из двух диапазонов)​ условиями. Или в​Как работать с​

Способ 2. Сравнение таблиц с помощью сводной

​Здесь расхождение дат в​ таблицы в Excel»​ него указателя мыши​ появится форма из​ вставить формулу​ кнопкой​ — Merge Queries​

​ отдельно скачать с​ена​ ней — сэкономите​​(COUNTIF)​ Выделение группы ячеек​ в статье Сравнение​​ желтого; не совпадающие​​ с помощью формулы​​ формуле указываем конкретные​ условным форматированием, как​​ ячейках строк второй​​ тут.​ курсор становится черным​​ двух полей «Текст1»​​=И (C1:C10)​​Обновить все (Refresh All)​

​ — Merge)​ сайта Microsoft и​в область значений:​ себе пару лет​из категории​ (Go to Special)​ 2-х списков.​ — красного). Цвет​ массива:​ данные, слова, которые​ настроить цвет заливки,​ и третьей.​Первый способ.​ плюсом. Затем выделите​

​ и «Текст2», в​Таким образом сравнивают​на вкладке​или нажмем кнопку​ установить — получите​​Как видите, сводная таблица​ жизни.​Статистические​на вкладке​Типовая задача, возникающая периодически​​ заголовков списков определяется​

​=ЕСЛИОШИБКА(ЕСЛИОШИБКА(​ нужно посчитать в​ шрифта в условном​Есть еще один​Как сравнить два столбца​ третью колонку и​ каждое из них​​ таблицы, в которых​​Данные (Data)​

​Объединить (Merge)​​ новую вкладку​ автоматически сформирует общий​Обычно эту функцию используют​, которая подсчитывает сколько​

​Главная (Home)​​ перед каждым пользователем​ Условным форматированием.​ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0));​ столбце.​ форматировании, как написать​ способ​ в​ на вкладке «Главная»​

Способ 3. Сравнение таблиц с помощью Power Query

​ поместите адрес одной​ одинаковые данные расположены​.​на вкладке​Power Query​ список всех товаров​ для подтягивания данных​ раз каждый элемент​Excel выделит ячейки, отличающиеся​ Excel — сравнить​Пусть дано 2 списка​ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0)));​​Подробнее смотрите такие​​ другие условия для​сравнить даты в Excel​Excel на совпадения.​ раскройте список «Условное​ из сравниваемых ячеек​ не обязательно в​​Плюсы​​Power Query​

​.​ из старого и​ из одной таблицы​ из второго списка​ содержанием (по строкам).​ между собой два​ ЧИСЛОвых значений. Определим,​»»)​ формулы в статье​ выделения ячеек, строк,​​- сравнить периоды​Выделяем столбцы (у​ форматирование» в группе​​ (B3 и C3).​ тех же строках.​: Пожалуй, самый красивый​​.​​Перед загрузкой наших прайс-листов​​ нового прайс-листов (без​​ в другую по​​ встречался в первом:​​ Затем их можно​

​ диапазона с данными​ совпадают ли эти​Подсчитаем количество вхождений каждого​​ «Функция «СЧЕТЕСЛИ» в​​ т.д., читайте в​​ дат,есть ли в​​ нас столбцы А​​ команд «Стили». В​​ Затем щелкните по​Abram pupkin​ и удобный способ​В окне объединения выберем​ в Power Query​​ повторений!) и отсортирует​ совпадению какого-либо общего​Полученный в результате ноль​ обработать, например:​ и найти различия​​ 2 списка (критерий​

​ уникального значения в​ Excel».​​ статье «Условное форматирование​ указанных периодах одинаковые​​ и В). На​

​ разделе «Правила выделения​ кнопке OK и​

​: аналогичные вопросы​ из всех. Шустро​ в выпадающих списках​ их необходимо преобразовать​ продукты по алфавиту.​ параметра. В данном​ и говорит об​​залить цветом или как-то​ между ними. Способ​ сравнения: попарное совпадение​ оба списка с​Этот способ сравнения​ в Excel». Получилось​​ даты и сколько​​ закладке «Главная» нажимаем​​ ячеек» выберите строку​​ увидите результат сравнения​​http://otvet.mail.ru/question/87383103​

​ работает с большими​ наши таблицы, выделим​ сначала в умные​ Хорошо видно добавленные​ случае, мы применим​ отличиях.​ еще визуально отформатировать​ решения, в данном​​ элементов).​​ помощью формул =СЧЁТЕСЛИ(Список1;D5)​

​ можно применить при​​ так.​​ дней в периодах​ на кнопку функции​ «Равно», а потом​ — либо надпись​http://otvet.mail.ru/question/87175757​ таблицами. Не требует​ в них столбцы​

​ таблицы. Для этого​ товары (у них​ ее, чтобы подтянуть​

​И, наконец, «высший пилотаж»​очистить клавишей​ случае, определяется типом​Пусть Список1 находится в​

​ и =СЧЁТЕСЛИ(Список2;D5)​ сравнении двух прайсов.​​Третий способ.​​ совпадают. Этот способ​ «Найти и выделить»,​​ кликните первую ячейку​​ «ЛОЖЬ», либо «ИСТИНА».​http://otvet.mail.ru/question/97716954​ ручных правок при​ с названиями товаров​ выделим диапазон с​

​ нет старой цены),​​ старые цены в​​ — можно вывести​Delete​ исходных данных.​ диапазоне​​Если количество повторов каждого​ Смотрите статью «Как​​Сравнить значения столбцов в​​ может пригодиться, чтобы​​ выбираем функцию «Выделение​

​ выделенной колонки и​

​2​Дима слепаков​ изменении размеров таблиц.​ и в нижней​ данными и нажмем​ удаленные товары (у​ новый прайс:​ отличия отдельным списком.​заполнить сразу все одинаковым​Если списки синхронизированы (отсортированы),​​A2:A10​​ уникального значения в​​ сделать прайс-лист в​​Excel формулой.​

​ выявить пересечение дат​​ группы ячеек».​ нажмите Enter. Таким​Если текст «ЛОЖЬ»​: 1) Посмотрите эту​Минусы​ части зададим способ​ на клавиатуре сочетание​

​ них нет новой​​Те товары, напротив которых​ Для этого придется​ значением, введя его​ то все делается​, Список2 — в​ обоих списках совпадает,​ Excel».​Можно сделать в​ в периодах. Например,​В появившемся окне ставим​

Подскажите-мне нужно сравнить 2 таблицы в эксель на совпадение данных. самый простой способ подскажите, пожалуйста.

​ способом вы подсветите​​ или «ИСТИНА» как​ программу http://infostart.ru/public/186905/​: Требует установленной надстройки​ объединения -​
​ Ctrl+T или выберем​
​ цены) и изменения​ получилась ошибка #Н/Д​ использовать формулу массива:​ и нажав​ весьма несложно, т.к.​ диапазоне​ то формула в​Сравним 2 списка содержащих​ таблице дополнительный столбец​ чтобы в отпуске​ галочку у слов​ ячейки с совпадающими​
​ результат сравнения вас​2) Можете попробовать​

​ Power Query (в​​Полное внешнее (Full Outer)​ на ленте вкладку​ цен, если были.​
​ — отсутствуют в​Выглядит страшновато, но свою​Ctrl+Enter​ надо, по сути,​
​B2:B10​ ячейке​
​ ТЕКСТОВЫЕ повторяющиеся значения.​
​ и установить в​ не было два​
​ «Отличия по строкам».​ результатами сравнения данных​ не устраивает, воспользуйтесь​ автоматически сравнить excel​
​ Excel 2010-2013) или​:​Главная — Форматировать как​Общие итоги в такой​ старом списке, т.е.​
​ работу выполняет отлично​
​удалить все строки с​ сравнить значения в​.​Е1​Пусть в столбцах​

​ ячейках этого столбца​​ сотрудника сразу или​
​ Нажимаем «ОК».​
​ — это более​
​ функцией «ЕСЛИ» -​

покупка

Как vlookup сравнить два списка на отдельных листах?

Предположим, у меня есть два рабочих листа со списком имен, как показано на скриншотах слева, теперь я хочу сравнить эти два списка и найти совпадающие имена в именах-1, если они выходят в именах-2. Мучительно делать такое сравнение вручную один за другим между двумя листами, в этой статье я расскажу о некоторых быстрых трюках, которые помогут вам закончить это без усилий.

Vlookup для сравнения двух списков на отдельных листах с формулами

Следующая формула может помочь вам извлечь имена из листов Names-1 и Names-2, пожалуйста, сделайте следующее:

1. На листе Names-1 выберите ячейку рядом с вашими данными, введите следующую формулу: = ВПР (A2; ‘Имена-2’! $ A $ 2: $ A $ 19,1; FALSE) , и нажмите Enter ключ, если имена в этом листе, он отобразит имя, если нет, будет возвращено значение ошибки, см. снимок экрана:

2. Затем перетащите дескриптор заполнения вниз к ячейкам, чтобы вернуть имена как в листах Names-1, так и Names-2, см. Снимок экрана:

Ноты:

1. Другая формула: = ЕСЛИ (ISNA (ВПР (A2; ‘Имена-2’! $ A $ 2: $ A $ 19,1; ЛОЖЬ)), «Нет», «Да») также может помочь вам сравнить два списка и определить, являются ли имена выходными на обоих листах, после применения этой формулы вы можете получить следующий результат и Да указывает, что соответствующее имя существует на обоих листах, и Нет указывает, что не существует.

2. В приведенных выше формулах: A2 это первая ячейка листа, которую вы хотите сравнить, Имена-2 другое имя рабочего листа, которое вы хотите сравнить, A2: A19 столбец имени для сравнения.

Vlookup для сравнения двух списков на отдельных листах

Если у вас есть Kutools for Excel, С его Выберите одинаковые и разные ячейки вы можете найти и выделить одинаковые или разные значения на двух отдельных листах так быстро, как только сможете. Нажмите, чтобы скачать Kutools for Excel!

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Vlookup для сравнения двух списков на отдельных листах с Kutools for Excel

Если у вас есть Kutools for Excel, С его Выберите одинаковые и разные ячейки функцию, вы можете быстро сравнить два списка на отдельных листах и ​​выбрать или выделить имена, которые оба находятся на этих двух листах, без каких-либо формул.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки, см. снимок экрана:

2. В Выберите одинаковые и разные ячейки диалоговом окне выполните следующие операции:

(1.) Выберите список данных на листе Имена-1 под Найдите значения в, а затем выберите данные из листа Names-2 под По словам;

(2.) Проверить Каждый ряд из на основании раздел;

(3.) Выберите Те же ценности из Найдите раздел;

(4.) Затем вы можете выбрать цвет фона или цвет шрифта для тех же имен, которые есть на обоих листах, как вам нужно.

3. Затем нажмите Ok , и появится окно подсказки, чтобы напомнить, сколько совпадающих ячеек было выбрано, имена, которые есть на этих двух листах, были выбраны и выделены одновременно, см. снимок экрана:

Демонстрация: Vlookup для сравнения двух списков на отдельных листах с Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Лучшие инструменты для офисной работы

Усовершенствуйте свои навыки работы с Excel с помощью Kutools for Excelи испытайте эффективность, как никогда раньше. Kutools for Excel Предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего.

Office Tab Добавляет в Office интерфейс с вкладками и значительно упрощает вашу работу

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Как сравнить два столбца в Excel на совпадения: 6 способов

Табличный процессор Эксель – одна из самых популярных программ для работы с электронными таблицами. И нередко у пользователя возникает вопрос – можно ли сравнить в Excel несколько столбцов на наличие совпадений. Особенно это важно для тех, кто работает с огромными объемами информации и, соответственно, большими таблицами.

Колонки сравнивают для того, чтобы, например, в отчетах не было дубликатов. Или, наоборот, для проверки правильности заполнения — с поиском непохожих значений. И проще всего выполнять сравнение двух столбцов на совпадение в Excel — для этого есть 6 способов.

Подпишитесь на наши каналы, чтобы не пропустить интересные новости и полезные статьи

1 Сравнение с помощью простого поиска

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

1. Перейти на главную вкладку табличного процессора.
2. В группе «Редактирование» выбрать пункт поиска.
3. Выделить столбец, в котором будет выполняться поиск совпадений — например, второй.
4. Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения.

Если значение обнаружено, результатом станет выделение нужной ячейки. Однако с помощью такого способа можно работать только с небольшими столбцами. И, если это просто цифры, так можно сделать и без поиска — определяя совпадения визуально. Впрочем, если в колонках записаны большие объемы текста, даже такая простая методика позволит упростить поиск точного совпадения.

2 Операторы ЕСЛИ и СЧЕТЕСЛИ

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

1. Сравниваемые столбцы размещаются на одном листе. Не обязательно, чтобы они находились рядом друг с другом.
2. В третьем столбце, например, в ячейке J6, ввести формулу такого типа: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»;H6)
3. Протянуть формулу до конца столбца.

Результатом станет появление в третьей колонке всех совпадающих значений. Причем H6 в примере — это первая ячейка одного из сравниваемых столбцов. А диапазон $I$6:$I$14 — все значения второй участвующей в сравнении колонки. Функция будет последовательно сравнивать данные и размещать только те из них, которые совпали. Однако выделения обнаруженных совпадений не происходит, поэтому методика подходит далеко не для всех ситуаций.

Еще один способ предполагает поиск не просто дубликатов в разных колонках, но и их расположения в пределах одной строки. Для этого можно применить все тот же оператор ЕСЛИ, добавив к нему еще одну функцию Excel — И. Формула поиска дубликатов для данного примера будет следующей: =ЕСЛИ(И(H6=I6); «Совпадают»; «») — ее точно так же размещают в ячейке J6 и протягивают до самого низа проверяемого диапазона. При наличии совпадений появится указанная надпись (можно выбрать «Совпадают» или «Совпадение»), при отсутствии — будет выдаваться пустота.

Тот же способ подойдет и для сравнения сразу большого количества колонок с данными на точное совпадение не только значения, но и строки. Для этого применяется уже не оператор ЕСЛИ, а функция СЧЕТЕСЛИ. Принцип написания и размещения формулы похожий.

Она имеет вид =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3; «Совпадают»;») и должна размещаться в верхней части следующего столбца с протягиванием вниз. Однако в формулу добавляется еще количество сравниваемых колонок — в данном случае, три.

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

3 Формула подстановки ВПР

Принцип действия еще одной функции для поиска дубликатов напоминает первый способ использованием оператора ЕСЛИ. Но вместо ПОИСКПОЗ применяется ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15.

С помощью этой функции не просто просматриваются и сравниваются повторяющиеся данные — результаты проверки устанавливаются четко напротив сравниваемого значения в первом столбце. Если программа не нашла совпадений, выдается #Н/Д.

4 Функция СОВПАД

Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:

1. В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
2. Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
3. Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.

Результатом такого сравнения будет вывод уже не найденного совпадающего значения, а булевой переменной. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ».

Стоит отметить, что функция СОВПАД сравнивает и числа, и другие виды данных с учетом верхнего регистра. А одним из самых распространенных способом использования такой формулы сравнения двух столбцов в Excel является поиска информации в базе данных. Например, отдельных видов мебели в каталоге.

5 Сравнение с выделением совпадений цветом

В поисках совпадений между данными в 2 столбцах пользователю Excel может понадобиться выделить найденные дубликаты, чтобы их было легко найти. Это позволит упростить поиск ячеек, в которых находятся совпадающие значения. Выделять совпадения и различия можно цветом — для этого понадобится применить условное форматирование.

Порядок действий для применения методики следующий:

1. Перейти на главную вкладку табличного процессора.
2. Выделить диапазон, в котором будут сравниваться столбцы.
3. Выбрать пункт условного форматирования.
4. Перейти к пункту «Правила выделения ячеек».
5. Выбрать «Повторяющиеся значения».
6. В открывшемся окне указать, как именно будут выделяться совпадения в первой и второй колонке. Например, красным текстом, если цвет остальных сообщений стандартный черный. Затем указать, что выделяться будут именно повторяющиеся ячейки.

Теперь можно снять выделение и сравнить совпадающие значения, которые будут заметно отличаться от остальной информации. Точно так же можно выделить, например, и уникальную информацию. Для этого следует выбрать вместо «повторяющихся» второй вариант — «уникальные».

6 Надстройка Inquire

Начиная с версий MS Excel 2013 табличный процессор позволяет воспользоваться еще одной методикой — специальной надстройкой Inquire. Она предназначена для того, чтобы сравнивать не колонки, а два файла .XLS или .XLSX в поисках не только совпадений, но и другой полезной информации.

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

Процесс использования надстройки включает такие действия:

1. Перейти к параметрам электронной таблицы.
2. Выбрать сначала надстройки, а затем управление надстройками COM.
3. Отметить пункт Inquire и нажать «ОК».
4. Перейти к вкладке Inquire.
5. Нажать на кнопку Compare Files, указать, какие именно файлы будут сравниваться, и выбрать Compare.
6. В открывшемся окне провести сравнения, используя показанные совпадения и различия между данными в столбцах.

Как в Excel сравнить два списка

Excel – эффективная программа для обработки данных. И один из методов анализа информации – сравнение двух списков. Если правильно осуществлять сравнение двух списков в Excel, организовать этот процесс будет очень легко. Достаточно просто следовать некоторым пунктам, о которых сегодня пойдет речь. Практическая реализация этого метода полностью зависит от потребностей человека или организации в конкретный момент. Поэтому следует рассмотреть несколько возможных случаев.

Сравнение двух списков в Excel

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

Постановка задачи

Итак, нам нужно сравнить эти столбцы. Методика сравнения двух документов следующая:

1. Если уникальные ячейки каждого из этих списков совпадают, и общее количество уникальных ячеек совпадает, и ячейки те же самые, то можно считать эти списки одинаковыми. То, в каком порядке значения в этом перечне уложены, не имеет столь большого значения.
2. О частичном совпадении перечней можно говорить, если сами уникальные значения те же самые, но отличается количество повторов. Следовательно, в таких списках может быть и разное количество элементов.
3. О том, что два списка не совпадают, говорит разный набор уникальных значений.

Все эти три условия одновременно и являются условиями нашей задачи.

Решение задачи

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

Чтобы сравнить два списка, надо выполнить следующие действия:

1. В отдельной колонке создаем список уникальных значений, характерных для обоих списков. Для этого используем формулу: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Сама формула должна записываться, как формула массива.
2. Определим, сколько раз каждое уникальное значение, встречается в массиве данных. Вот, какими формулами можно это сделать: =СЧЁТЕСЛИ(Список1;D5) и =СЧЁТЕСЛИ(Список2;D5).
3. Если и число повторений, и количество уникальных значений одинаковое во всех перечнях, которые входят в эти диапазоны, то функция возвращает значение 0. Это говорит о том, что совпадение стопроцентное. В этом случае заголовки этих списков обретут зеленый фон.
4. Если все уникальное содержимое есть в обоих списках, то возвращенное формулами =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) значение составит ноль. Если же E1 содержит не ноль, а такое значение содержится в ячейках E2 и F2, то в этом случае диапазоны будут признаны совпадающими, но только частично. В таком случае заголовки соответствующих списков станут оранжевыми.
5. И в случае возвращения одной из формул, описанных выше, ненулевого значения перечни будут полностью не совпадающими.

Вот и ответ на вопрос, как проанализировать столбцы на предмет совпадений с помощью формул. Как видим, с применением функций можно реализовать почти любую задачу, которая на первый взгляд с математикой не связана.

Тестирование на примере

В нашем варианте таблицы есть три вида списков каждой описанной выше разновидности. В нем есть частично и полностью совпадающие, а также не совпадающие.

Для сравнения данных мы используем диапазон A5:B19, в который мы попеременно вставляем эти пары списков. О том, какой будет итог сравнения, мы поймем по цвету исходных перечней. Если они абсолютно разные, то это будет красный фон. Если часть данных одинаковая, то желтый. В случае же полной идентичности соответствующие заголовки будут зелеными. Как же сделать цвет, зависящий от того, какой результат получился? Для этого нужно условное форматирование.

Поиск отличий в двух списках двумя способами

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

Вариант 1. Синхронные списки

Это простой вариант. Предположим, у нас такие списки.

Чтобы определить, какое количество раз значения не сошлись, можно с использованием формулы: =СУММПРОИЗВ(—(A2:A20<>B2:B20)). Если по итогу мы получили 0, это говорит о том, что два перечня одинаковые.

Вариант 2. Перемешанные списки

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

Как сравнить 2 столбца по строкам

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

Пример. Как сравнить 2 столбца на совпадения и различия в одной строке

Чтобы проанализировать, являются ли значения, находящиеся в том же самом ряду, но разных колонках, одинаковыми, запишем функцию ЕСЛИ. Формула вставляется в каждый ряд, размещенный во вспомогательном столбце, куда будут выводиться результаты обработки данных. Но вовсе не обязательно прописывать ее в каждый ряд, достаточно просто скопировать ее в оставшиеся ячейки этой колонки или же воспользоваться маркером автозаполнения.

Нам следует записать такую формулу, чтобы понять, совпадают ли значения в обеих колонках или нет: =ЕСЛИ(A2=B2; “Совпадают”; “”). Логика работы этой функции очень проста: она сопоставляет значения в ячейках A2 и B2, и если они одинаковые, выводит значение «Совпадают». Если же данные отличаются, то не возвращает никакого значения. Можно также проверить ячейки на предмет отсутствия между ними совпадения. В этом случае используемая формула следующая: =ЕСЛИ(A2<>B2; “Не совпадают”; “”). Принцип тот же самый, сначала осуществляется проверка. Если оказывается, что ячейки удовлетворяют критерию, то выводится значение «Не совпадают».

Также возможно применение следующей формулы в поле формулы, чтобы выводить и «Совпадают» если значения одинаковые, и «Не совпадают», если они отличаются: =ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”). Также вместо оператора равенства можно использовать оператор неравенства. Только порядок значений, которые будут выводиться в этом случае будет несколько другим: =ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”). После использования первого варианта формулы результат получится следующим.

Этот вариант формулы не учитывает регистр значений. Поэтому если значения в одной колонке отличаются от других только тем, что они написаны большими буквами, то этой разницы программа не заметит. Чтобы при сравнении учитывался регистр, нужно в критерии использовать функцию СОВПАД. Остальные аргументы оставляем без изменений: =ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”).

Как сравнить несколько столбцов на совпадения в одной строке

Есть возможность проанализировать значения в перечнях по целому набору критериев:

1. Найти те ряды, которые везде имеют те же значения.
2. Найти те ряды, где есть совпадения всего в двух списках.

Давайте рассмотрим несколько примеров, как действовать в каждом из этих случаев.

Пример. Как найти совпадения в одной строке в нескольких столбцах таблицы

Предположим, у нас есть ряд колонок, где содержится нужная нам информация. Перед нами стоит задача определить те ряды, в которых значения одинаковые. Чтобы это сделать, нужно воспользоваться следующей формулой: =ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “).

Если столбцов лишком много содержится в таблице, то нужно просто применять вместе с функцией ЕСЛИ оператор СЧЕТЕСЛИ: =ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “). Цифра, которая используется в этой формуле, означает количество колонок, в которых нужно осуществлять проверку. Если оно отличается, то нужно написать столько, сколько справедливо для вашей ситуации.

Пример. Как найти совпадения в одной строке в любых 2 столбцах таблицы

Допустим, нам необходимо проверить, совпадают ли в одном ряду значения в двух колонках из тех, которые есть в таблице. Для этого нужно в качестве условия использовать функцию ИЛИ, где попеременно прописать равенство каждого из столбцов другому. Вот пример.

Мы используем такую формулу: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Может случиться ситуация, когда столбцов в таблице очень много. В таком случае формула будет огромной, а времени на подбор всех необходимых комбинаций может потребоваться очень много. Чтобы решить эту проблему, нужно воспользоваться функцией СЧЕТЕСЛИ: =ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)

Видим, что итого у нас две функции СЧЕТЕСЛИ. С помощью первой мы попеременно определяем, сколько столбцов имеют сходство с A2, а с помощью второй проверяем количество сходств со значением B2. Если в результате вычисления по этой формуле мы получаем нулевое значение, это говорит о том, что все строки в этом столбце уникальны, если же больше – есть сходства. Следовательно, если в результате вычисления по двум формулам и складывания итоговых результатов мы получаем нулевое значение, то возвращается текстовое значение «Уникальная строка», если же это число больше, записывается, что эта строка не уникальная.

Как сравнить 2 столбца в Excel на совпадения

Теперь приведем такой пример. Допустим у нас есть таблица с двумя столбцами. Необходимо проверить совпадения в них. Чтобы это сделать, необходимо применять формулу, где будут использоваться и функция ЕСЛИ, и оператор СЧЕТЕСЛИ: =ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Больше никаких действий выполнять не нужно. После вычисления результат по этой формуле мы получаем, если значение третьего аргумента функции ЕСЛИ совпадает. Если же их нет, то содержимое второго аргумента.

Как сравнить 2 столбца в Excel на совпадения и выделить цветом

Чтобы было более просто визуально определять совпадающие столбцы, можно выделить их цветом. Для этого нужно воспользоваться функцией «Условное форматирование». Давайте разберемся на практике.

Поиск и выделение совпадений цветом в нескольких столбцах

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

После этого появится новое диалоговое окно, в котором в левом всплывающем перечне находим опцию «Повторяющиеся», а в правом списке выбираем цвет, каким будет осуществляться выделение. После нажатия нами кнопки «ОК», фон всех ячеек со сходствами будет выделен. Дальше просто сравнивать колонки на глаз.

Поиск и выделение цветом совпадающих строк

Методика проверки, совпадают ли строки, несколько отличается. Сначала необходимо создать дополнительную колонку, и там будем использовать объединенные значения с использованием оператора &. Для этого нужно записать формулу вида: =A2&B2&C2&D2.

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

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