Сравнение данных двух таблиц в excel на совпадения

Пример работы с функцией ВПР

В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B — товар и цена) нет данных по столбцу B; во второй — заполнены оба столбца (товар и цена). Теперь нужно проверить первые столбцы в обоих таблицах и автоматически, при найденном совпадении, скопировать цену в первую табличку. Вроде, задачка простая.

Две таблицы в Excel – сравниваем первые столбцы

Как это сделать.

Ставим указатель мышки в ячейку B2 — то бишь в первую ячейки столбца, где у нас нет значения и пишем формулу:

A2 — значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

$E$1:$F$7 — полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать)

Обратите внимание на значок “$” — он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;. 2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца

Если бы у нее было 3 столбца – то значение можно было бы копировать из 2-го или 3-го столбца);

2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца – то значение можно было бы копировать из 2-го или 3-го столбца);. ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит)

ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

Какая должна быть формула

Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!

Значение было найдено и подставлено автоматически

Чтобы цена была проставлена и для других наименований товара — просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.

Растягиваем формулу (копируем формулу в другие ячейки)

После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали — будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!

Значения из одной таблицы подставлены в другую

Примечание : должен сказать, что функция ВПР достаточно требовательна к ресурсам компьютера. В некоторых случаях, при чрезмерно большом документе, чтобы сравнить таблицы может понадобиться довольно длительное время. В этих случаях, стоит рассмотреть либо другие формулы, либо совсем иные решения (каждый случай индивидуален).

Ну а у меня на этом пока всё, удачи!

Сравнение 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​… и в появившемся​

excel2.ru>

Пример работы с функцией ВПР

В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B — товар и цена) нет данных по столбцу B; во второй — заполнены оба столбца (товар и цена). Теперь нужно проверить первые столбцы в обоих таблицах и автоматически, при найденном совпадении, скопировать цену в первую табличку. Вроде, задачка простая.

Две таблицы в Excel – сравниваем первые столбцы

Как это сделать.

Ставим указатель мышки в ячейку B2 — то бишь в первую ячейки столбца, где у нас нет значения и пишем формулу:

A2 — значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

$E$1:$F$7 — полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать)

Обратите внимание на значок “$” — он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;. 2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца

Если бы у нее было 3 столбца – то значение можно было бы копировать из 2-го или 3-го столбца);

2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца – то значение можно было бы копировать из 2-го или 3-го столбца);. ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит)

ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

Какая должна быть формула

Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!

Значение было найдено и подставлено автоматически

Чтобы цена была проставлена и для других наименований товара — просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.

Растягиваем формулу (копируем формулу в другие ячейки)

После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали — будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!

Значения из одной таблицы подставлены в другую

Примечание : должен сказать, что функция ВПР достаточно требовательна к ресурсам компьютера. В некоторых случаях, при чрезмерно большом документе, чтобы сравнить таблицы может понадобиться довольно длительное время. В этих случаях, стоит рассмотреть либо другие формулы, либо совсем иные решения (каждый случай индивидуален).

Ну а у меня на этом пока всё, удачи!

Сравнение двух столбцов на совпадения в Excel

Как сделать сравнение значений в Excel двух столбцов? Для решения данной задачи рекомендуем использовать условное форматирование, которое быстро выделить цветом позиции, находящиеся только в одном столбце. Рабочий лист с таблицами:

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

1. Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя».
2. В появившемся окне в поле «Имя:» введите значение – Таблица_1.
3. Левой клавишей мышки сделайте щелчок по полю ввода «Диапазон:» и выделите диапазон: A2:A15. И нажмите ОК.

Для второго списка выполните те же действия только имя присвойте – Таблица_2. А диапазон укажите C2:C15 – соответственно.

Полезный совет! Имена диапазонов можно присваивать быстрее с помощью поля имен. Оно находится левее от строки формул. Просто выделяйте диапазоны ячеек, а в поле имен вводите соответствующее имя для диапазона и нажмите Enter.

Теперь воспользуемся условным форматированием, чтобы выполнить сравнение двух списков в Excel. Нам нужно получить следующий результат:

Позиции, которые есть в Таблице_1, но нет в Таблцие_2 будут отображаться зеленым цветом. В тоже время позиции, находящиеся в Таблице_2, но отсутствующие в Таблице_1, будут подсвечены синим цветом.

1. Выделите диапазон первой таблицы: A2:A15 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
2. В поле ввода введите формулу:
3. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК.
4. Выделите диапазон первого списка: C2:C15 и снова выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
5. В поле ввода введите формулу:
6. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите синий цвет. На всех окнах жмем ОК.

Сравнение двух списков в 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, в который мы попеременно вставляем эти пары списков. О том, какой будет итог сравнения, мы поймем по цвету исходных перечней. Если они абсолютно разные, то это будет красный фон. Если часть данных одинаковая, то желтый. В случае же полной идентичности соответствующие заголовки будут зелеными. Как же сделать цвет, зависящий от того, какой результат получился? Для этого нужно условное форматирование.

Как исключить значения в одном списке из другого в Excel?

Предположим, у вас есть два списка данных, как показано на скриншоте слева. Теперь вам нужно удалить или исключить имена в столбце A, если имя существует в столбце D. Как этого добиться? А что, если два списка находятся на двух разных листах? Эта статья предлагает вам два метода.

Исключить значения в одном списке из другого с помощью формулы

Для этого вы можете применить следующие формулы. Пожалуйста, сделайте следующее.

1. Выберите пустую ячейку, которая находится рядом с первой ячейкой списка, который вы хотите удалить, затем введите формулу. = СЧЁТЕСЛИ ($ D $ 2: $ D $ 6; A2) в панель формул, а затем нажмите Enter ключ. Смотрите скриншот:

Внимание: В формуле $ D $ 2: $ D $ 6 — это список, на основе которого вы удаляете значения, A2 — это первая ячейка списка, который вы собираетесь удалить. Пожалуйста, измените их по своему усмотрению

2. Продолжая выбирать ячейку результата, перетащите маркер заполнения вниз, пока он не достигнет последней ячейки списка. Смотрите скриншот:

3. Продолжайте выбирать список результатов, затем щелкните Данные > Сортировка от А до Я.

Затем вы можете увидеть, что список отсортирован, как показано на скриншоте ниже.

4. Теперь выберите все строки имен с результатом 1, щелкните правой кнопкой мыши выбранный диапазон и нажмите Удалить чтобы удалить его.

Теперь вы исключили значения из одного списка на основе другого.

Внимание: Если «список для удаления» находится в диапазоне A2: A6 другого листа, такого как Sheet2, примените эту формулу = IF (ISERROR (VLOOKUP (A2; Sheet2! $ A $ 2: $ A $ 6,1; FALSE)), «Сохранить», «Удалить») получить все Сохранить и Удалить результатов, отсортируйте список результатов от A до Z, а затем вручную удалите все строки имен, содержащие результат удаления на текущем листе. Этот раздел будет рекомендовать Выберите одинаковые и разные ячейки полезности Kutools for Excel чтобы решить эту проблему

Пожалуйста, сделайте следующее

Этот раздел будет рекомендовать Выберите одинаковые и разные ячейки полезности Kutools for Excel чтобы решить эту проблему. Пожалуйста, сделайте следующее.

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

1. Нажмите Kutools > Выберите > Выберите одинаковые и разные ячейки. Смотрите скриншот:

2. в Выберите одинаковые и разные ячейки диалоговое окно, вам необходимо:

• 2.1 Выберите список, из которого вы удалите значения в Найдите значения в коробка;
• 2.2 Выберите список, значения которого вы удалите, на основе Согласно информации коробка;
• 2.3 выберите Однокамерная вариант в на основании раздел;
• 2.4 Щелкните значок OK кнопка. Смотрите скриншот:

3. Затем появляется диалоговое окно, в котором указывается, сколько ячеек было выбрано, нажмите OK кнопку.

4. Теперь значения в столбце A выбираются, если они существуют в столбце D. Вы можете нажать кнопку Удалить клавишу, чтобы удалить их вручную.

  Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

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

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

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

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

У нас имеется две таблицы заказов, скопированных в один рабочий лист. Необходимо выполнить сравнение данных двух таблиц в Excel и проверить, которые позиции есть первой таблице, но нет во второй. Нет смысла вручную сравнивать значение каждой ячейки.