Как сравнить две таблицы в эксель и найти разницу впр

Формула ВПР в Excel для сравнения двух таблиц — 4 способа

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

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

Итак, вот наши исходные данные.

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

Для этого используем несколько способов.

1. При помощи функции ВПР для каждого товара из прайс-листа №2 будем искать цену в первом прайс-листе и выводить ее рядом с новой. Так легче будет определить различия.
2. Построим сводную таблицу по данным обоих прайс-листов, и там эти отличия увидим.
3. Используем стандартную операцию сравнения.
4. Применим формулу массива.

1. Используем ВПР, чтобы сравнить две таблицы.

Создадим именованный диапазон B4:C19 и назовем его «прайс1». Так нам будет проще ссылаться на первоначальные данные.

Добавим к новым данным еще одну колонку и назовем ее «Цена старая». Для каждого наименования из прайс-листа №2 найдем соответствующую ему цену в №1.

и копируем ее вниз по столбцу.

Видим, что кое-где изменилась цена, и в четырех наименованиях формула ВПР возвратила ошибку #Н/Д. Это означает, что ранее этих товаров не было и цену для них обнаружить не удалось.

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

Для этого используем функцию ЕСЛИОШИБКА и вместо #Н/Д выведем ноль.

Изменим нашу формулу:

Теперь мы можем рассчитать отклонения новой цены от старой.

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

Согласитесь, что такое сравнение выглядит гораздо аккуратнее и нагляднее.

Выглядит сложно и громоздко, но на самом деле все просто. Основа здесь та же, что и ранее: поиск в первой таблице «старой» цены каждого товара из новых данных.

То есть, ключевым является выражение ЕСЛИОШИБКА(ВПР(F4;прайс1;2;0);0).

Если найденное значение равно «новой» цене из ячейки G4, то выводим пустой пробел “”.

Значения смежных ячеек привязаны к этому результату.

Если ячейка J4 пуста, тогда ничего не выводим и в остальных:

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

Но есть один существенный недостаток в таком сравнении таблиц с использованием функции ВПР. Мы сравнили новые значения и старые, нашли изменения и новые товары. Но если какой-то товар ранее существовал, но теперь отсутствует, то этого мы не заметим. Придется повторить весь процесс в обратную сторону, взяв теперь за базу первую таблицу и сопоставляя ее со второй.

То есть, сравнивать придется в двух направлениях.

Согласитесь, не всегда хочется делать двойную работу.

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

Поскольку структура сравниваемых данных одинакова, то мы можем объединить их. Чтобы различить, откуда взяты какие значения, добавьте еще один столбец и укажите там источник данных – прайс1 или прайс2.

Используя наш предыдущий пример, это можно сделать следующим образом:

Теперь через меню Вставка-Сводная таблица создадим свод, можно на этом же листе для наглядности.

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

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

Это еще один пример того, что у функции ВПР есть весьма достойные альтернативные варианты во многих случаях.

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

Следует также отметить, что с большими объемами данных сводные таблицы умеют работать гораздо быстрее, чем ВПР.

3. Стандартное сравнение.

Это самые простой и элементарный способ сравнить два столбца Excel на совпадения. Работать таким образом возможно как с числовыми значениями, так и с текстовыми.

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

Для примера сравним два прайса, записав в столбце I условие совпадения цены

При равенстве мы получим ответ «ИСТИНА», а если совпадения нет, будет «ЛОЖЬ». Копируем из I4 вниз по столбцу.

Этот способ сравнения таблиц – самый элементарный, поэтому останавливаться на нем более не будем.

4. Использование формул массива вместе с ВПР.

Здесь все гораздо сложнее. Вновь вернемся к нашим исходным данным и разместим списки товаров и цен на двух листах рабочей книги: «Прайс1» и «Прайс2».

Создадим из наименований товаров в каждой из таблиц именованный диапазон, как это показано на рисунке.

Назовем их соответственно «прайс_1» и «прайс_2». Так нам легче будет разбираться в формулах.

Результаты сравнения таблиц вынесем также на отдельный лист «Сравнение».

В ячейке A5 запишем формулу

=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(прайс_1; ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;прайс_1);0)); ИНДЕКС(прайс_2;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;прайс_2);0)));»»)

Поскольку это формула массива, то не забудьте завершить ее ввод комбинацией клавиш Ctrl+Shift+Enter.

В результате получим список уникальных (неповторяющихся) значений из всех имеющихся у нас наименований товаров.

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

Функция ПОИСКПОЗ вычисляет номер позиции этого уникального значения и передает его в функцию ИНДЕКС, которая, в свою очередь, по номеру позиции извлекает значение из массива и записывает его в ячейку.

Поскольку это формула массива, то мы последовательно проходим по всему списку от начала до конца, повторяя все эти операции.

Если первая таблица закончилась, то возникает ошибка. ЕСЛИОШИБКА реагирует на это и начинает таким же образом перебирать значения второй таблицы. Когда и там возникает ошибка, то возвращается пустая строка “”.

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

Затем добавим еще два столбца, в которых при помощи функции ВПР запишем результат сравнения двух таблиц по каждому наименованию товара.

Не забудьте, что это тоже формула массива (Ctrl+Shift+Enter).

Можно для наглядности выделить несовпадения цветом, используя условное форматирование.

Напомним, что для этого надо использовать меню Главная – Условное форматирование – Правила выделения ячеек – Текст содержит…

Ну и если значение существует в таблице, то логично было бы его вывести в таблице сравнения.

Заменим в нашей формуле значение «Есть» на функцию ВПР:

В итоге наше формула преобразуется к виду:

Аналогично в С5 :

Напомним, что на листах Прайс1 и Прайс2 находятся наши сравниваемые таблицы.

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

Примеры использования функции ВПР:

Поиск ВПР нескольких значений по нескольким условиям — В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке. При использовании Microsoft…
Функция ЕСЛИОШИБКА – примеры формул — В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального…
Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном…

2 способа извлечь данные из разных таблиц при помощи ВПР. — Задача: Данные, которые нужно найти и извлечь при помощи функции ВПР, находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть, одни и те же столбцы, расположенные в одном…
/> Вычисление номера столбца для извлечения данных в ВПР — Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.…
4 способа, как сделать левый ВПР в Excel. — Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом…
Почему не работает ВПР в Excel? — Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте). Давайте постараемся вместе…
ВПР с несколькими условиями: 5 примеров. — Очень часто наши требования к поиску данных не ограничиваются одним условием. К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т.д. Обычными…
Функция ВПР в Excel: пошаговая инструкция с 5 примерами — ВПР — это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Она поддерживает приблизительное и точное сопоставление, а также подстановочные знаки (* и ?). Значения поиска…
Формула ВПР в Excel — 22 факта, которые нужно знать. — В процессе работы в Excel часто возникает задача извлечения нужных данных из рабочих таблиц. Для этой цели в Excel предусмотрена формула ВПР (VLOOKUP в английском варианте). И хотя ВПР относительно…

Как пользоваться функцией ВПР в Excel: пример с двумя таблицами

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

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

Как работает функция ВПР в Excel: пример

Функция ВПР предназначена для выборки данных из таблицы Excel по определенным критериям поиска. Например, если таблица состоит из двух колонок: «Наименование товара» и «Цена». Рядом находится другая таблица, которая будет искать в первой таблице по наименованию товара и получать значение соответствующей цены.

1. Переходим в ячейку второй таблицы под названием столбца «Цена».
2. Выберите «Формулы»-«Ссылки и массивы»-«ВПР». Ввести функцию ВПР можно и с помощью «мастера функций». Для этого нажмите на кнопку «fx», которая находиться в начале строки формул. Или нажмите комбинацию горячих клавиш SHIFT+F3. В появившимся диалоговом окне на поле категория, выберите из выпадающего списка: «Ссылки и массивы», а потом ниже укажите на функцию.
3. Заполняем аргументы функции.

В поле «Исходное значение» вводим ссылку на ячейку под наименованием товара второй таблицы D3. В поле «Таблица» вводим диапазон всех значений первой таблицы A2:B7. В поле «Номер столбца» вводим значение 2, так как во втором столбце у нас находиться цена, которую мы хотим получить при поиске товара. И нажимаем ОК.

Теперь под заголовком столбца второй таблицы «Товар» введите наименования того товара по котором нам нужно узнать его цену. И нажмите Enter.

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

Функция ВПР в Excel и две таблицы

Немного усложним задание, изменив структуру и увеличив объем данных в таблице. Расширьте объем данных первой таблицы, добавив столбцы: «январь», «февраль», «март». Там запишем суммы продаж в первом квартале как показано на рисунке:

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

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

1. Исходное значение: G3.
2. Таблица: A2:E7. Диапазон нашей таблицы расширен.
3. Номер столбца: <3;4;5>. Нам нужно с помощью функции обращаться одновременно к нескольким столбцам, поэтому значение данного аргумента будет взято в массив фигурными скобками. А номера столбцов следует перечислять через точку с запятой.
4. Интервальный просмотр: ЛОЖЬ.
5. Чтобы значения в выбранных столбцах суммировались, тогда всю функцию нужно поместить внутрь функции СУММ(). Вся формула в целом выглядит следующим образом: =СУММ(ВПР(G3;A3:E6;<3;4;5>;ЛОЖЬ)).
6. После ввода данной формулы следует нажать комбинацию клавиш: CTRL+SHIFT+ENTER. Внимание! Если не нажать комбинацию этих клавиш формула будет работать ошибочно . В Excel иногда приходиться выполнять функции в массиве для этого нужно обязательно использовать клавиши: CTRL+SHIFT+ENTER при вводе функций. Тогда в строке формул все содержимое будет взято в фигурные скобки «<>», что свидетельствует о выполнении формулы в массиве.

Теперь вводите в ячейку G3 наименование товара, в ячейке H3 получаем сумму продаж в первом квартале по данному товару.

Происходит сравнение двух таблиц в Excel функцией ВПР и как только определяется совпадение запрашиваемых данных, сразу подставляется их значения для суммирования функцией СУММ. Весь процесс выполняется циклически благодаря массиву функций о чем свидетельствуют фигурные скобки в строке формул.

Примечание. Если ввести вручную крайние фигурные скобки в строку формул то это не приведет ни ка какому результату. Выполнить функцию циклическим массивом можно только через комбинацию горячих клавиш: CTRL+SHIFT+ENTER.

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

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

Формула ВПР в Excel для сравнения двух таблиц

Сравнение таблиц — довольно распространенное действие в Excel. Например, у нас есть старый прайс-лист и новая версия. Вам необходимо просмотреть цены на какие товары и на сколько изменились.

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

Итак, вот наши необработанные данные.

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

Для этого мы воспользуемся несколькими способами.

1. Использование функции ВПР для каждого продукта в прайс-листе № 2, мы будем искать цену в первом прайс-листе и отображать ее рядом с новым. Это облегчит выявление различий.
2. Давайте построим сводную таблицу на основе данных из обоих прайс-листов и увидим там эти различия.
3. Мы используем стандартную операцию сравнения.
4. Применяем формулу массива.

1. Используем ВПР, чтобы сравнить две таблицы.

Создадим диапазон B4: C19 и назовем его «цена1». Это упростит нам обращение к исходным данным.

Давайте добавим еще один столбец к новым данным и назовем его «Старая цена». Для каждой позиции в прайс-листе нет. 2 найдем соответствующую цену в n. 1.

В 4 вставляем формулу

= ВПР (F4; цена1; 2; 0)

и скопируйте его по столбцу.

Мы видим, что цена тут и там изменилась, и в четырех именах формула ВПР вернула ошибку # N / A. Это означает, что раньше этих товаров не было в наличии и цены на них найти не удалось.

Чтобы сделать результаты сравнения более красивыми и определить степень изменения цены, мы обработаем отображаемые сообщения об ошибках.

Для этого используйте функцию ESLIOSHIBKA и отобразите ноль вместо # N / A.

Изменим нашу формулу:

= SEERROR (ВПР (F4; цена1,2,0); 0)

Теперь мы можем рассчитать отклонение новой цены от старой.

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

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

= ЕСЛИ (SEERROR (VLOOKUP (F4; price1,2,0); 0) = G4; «»; SEERROR (VLOOKUP (F4; price1,2; 0); 0))

Звучит сложно и громоздко, но на самом деле все просто. Основа такая же, как и раньше: посмотрите в первой таблице «старую» цену каждого товара из новых данных.

То есть ключевым выражением является ЕСЛИОШИБКА (ВПР (F4; цена1; 2; 0); 0).

Если найденное значение равно «новой» цене из ячейки G4, выведите пробел “”.

С этим результатом связаны значения соседних ячеек.

Если ячейка J4 пуста, в другом месте ничего не отображается:

В результате остались только строки, в которых произошли изменения цен или появился новый товар, который изначально не был в наличии.

Но у сравнения подобных таблиц с помощью функции ВПР есть существенный недостаток. Мы сравнили новые и старые значения, нашли изменения и новые продукты. Но если какой-либо продукт существовал ранее, а сейчас отсутствует, мы не заметим. Придется повторить всю процедуру в обратном порядке, взяв за основу первую таблицу и сравнив ее со второй.

То есть сравнивать придется по двум направлениям.

Согласитесь, не всегда хочется выполнять двойную работу.

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

Поскольку структура сравниваемых данных одинакова, мы можем их комбинировать. Чтобы различать, откуда берутся значения, добавьте еще один столбец и укажите там источник данных — price1 или price2.

Используя наш предыдущий пример, это можно сделать следующим образом:

Теперь через меню Insert-PivotTable создайте сводку, которую вы можете на том же листе для наглядности.

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

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

Это еще один пример того, как ВПР во многих случаях имеет довольно приличные альтернативы.

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

Также следует отметить, что сводные таблицы могут работать с большими объемами данных намного быстрее, чем VLOOKUP.

3. Стандартное сравнение.

Это самый простой и элементарный способ сравнить два столбца Excel на предмет совпадений. Таким образом можно работать как с числовыми, так и с текстовыми значениями.

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

Например, сравним две цены, отметив условие совпадения цен в столбце I

В случае ничьей мы получим ответ «ИСТИНА», а при отсутствии совпадений — «ЛОЖЬ». Скопируйте из I4 в столбец.

Этот способ сравнения таблиц является наиболее простым, поэтому мы не будем вдаваться в подробности.

4. Использование формул массива вместе с ВПР.

Здесь все намного сложнее. Вернемся к нашим исходным данным и разместим списки товаров и цен на двух листах рабочей книги: «Цена1» и «Цена2».

Создадим ассортимент, названный по названиям товаров в каждой из таблиц, как показано на рисунке.

Назовем их «цена_1» и «цена_2» соответственно. Это упростит понимание формул.

Мы также вынесем результаты сравнения таблиц на отдельный лист «Сравнение».

В ячейке A5 запишите формулу

= ЕСЛИОШИБКА (ЕСЛИОШИБКА (ИНДЕКС (цена_1; ПОИСК (0; СЧЁТЕСЛИ (A $ 4: $ A4; цена_1); 0))), ИНДЕКС (цена_2; ПОИСК (0; СЧЁТЕСЛИ (A $ 4: $ A4; 0)));»»)

Поскольку это формула массива, не забудьте завершить ее комбинацией клавиш Ctrl + Shift + Enter.

В результате мы получим список уникальных (неповторяющихся) значений из всех имеющихся у нас наименований продуктов.

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

Функция ПОИСКПОЗ вычисляет номер позиции этого уникального значения и передает его в функцию ИНДЕКС, которая, в свою очередь, через номер позиции извлекает значение из массива и записывает его в ячейку.

Поскольку это формула массива, мы постоянно просматриваем весь список от начала до конца, повторяя все эти операции.

Если первая таблица завершена, возникает ошибка. ЕСЛИОШИБКА реагирует на это и таким же образом начинает перебирать значения второй таблицы. Когда ошибка возникает и там, возвращается пустая строка “”.

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

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

= ЕСЛИ (UND (ВПР (A5; цена_1; 1; 0)); «Нет»; «Да»)

Не забывайте, что это тоже формула массива (Ctrl + Shift + Enter).

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

Помните, что для этого вам нужно использовать Главное меню — Условное форматирование — Правила выбора ячеек — Текст содержит…

Что ж, если значение существует в таблице, имеет смысл отобразить его в таблице сравнения.

Давайте заменим значение «Да» в нашей формуле на функцию ВПР:

ВПР (A5, Price1! $ B $ 5: $ C $ 20, 2, 0)

В результате наша формула преобразуется в вид:

= ЕСЛИ (UND (ВПР (A5; цена_1; 1; 0)); «Нет»; ВПР (A5; Цена1! $ B $ 5: $ C $ 20, 2, 0))

Аналогично в C5 :

= ЕСЛИ (UND (ВПР (A5; цена_2; 1; 0)); «Нет»; ВПР (A5; Цена2! $ B $ 5: $ C $ 23, 2, 0))

Напомним, что наши сравнительные таблицы можно найти в таблицах Price1 и Price2.

Однако для сравнения двух таблиц вы можете выбрать один из двух методов в соответствии с вашими предпочтениями.

8 способов как сравнить две таблицы в Excel

Добрый день!

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

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

Простой способ, как сравнить две таблицы в Excel

Это самые простой и элементарный способ сравнения двух таблиц. Сравнивать таким способом возможно, как числовые значение, так и текстовые. Для примера сравним два диапазона числовых значений, всего на всего прописав в соседней ячейке формулу их равенства =C2=E2, как результат при равенстве ячеек мы получим ответ «ИСТИНА», а если совпадений нет, будет «ЛОЖЬ». Теперь простым авто копированием копируем на весь диапазон нашу формулу позволяющую сравнить два столбика в Excel и видим разницу.

Быстрое выделение значений, которые отличаются

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».

Сравнить две таблицы в Excel с помощью условного форматирования

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами». В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат». Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

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

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная», пункт меню «Условное форматирование» и в списке жмем пункт «Создать правило…», выбираем правило «Использовать формулу для определения форматируемых ячеек», вписываем формулу =СЧЁТЕСЛИ($C$1:$C$7;C1)=0 и выбираем формат условного форматирования.

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

Как сравнить две таблицы в Excel с помощью функции ВПР

В этом варианте мы будем использовать функцию ВПР, которая позволит нам сравнить две таблицы на предмет совпадений. Для сравнения двух столбиков, введите формулу =ВПР(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула последовательно начинает проверять есть ли повторы значения из столбика А в столбике В, ну и соответственно возвращает значение элемента, если оно было там найдено если же значение не найдено получаем ошибку #Н/Д.

Как сравнить две таблицы в Excel функции ЕСЛИ

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

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));»»;C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Сравнить две таблицы с помощью макроса VBA

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