Методы сравнения таблиц в Microsoft Excel

Содержание

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

Методы сравнения таблиц в Microsoft Excel
19783 04.12.2017 Скачать пример

Имеем две таблицы (например, старая и новая версия прайс-листа), которые надо сравнить и оперативно найти отличия:

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

Для любой задачи в Excel почти всегда есть больше одного решения (обычно 4-5). Для нашей проблемы можно использовать много разных подходов:

  • функцию ВПР (VLOOKUP) – искать названия товаров из нового прайс-листа в старом и выводить старую цену рядом с новой, а потом ловить отличия
  • объединить два списка в один и построить по нему потом сводную таблицу, где наглядно будут видны отличия
  • использовать надстройку Power Query для Excel

Давайте разберем их все последовательно.

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

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

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

Те товары, напротив которых получилась ошибка #Н/Д – отсутствуют в старом списке, т.е. были добавлены. Изменения цены также хорошо видны.

Плюсы этого способа: просто и понятно, “классика жанра”, что называется. Работает в любой версии Excel.

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

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

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

Теперь на основе созданной таблицы создадим сводную через Вставка – Сводная таблица (Insert – Pivot Table). Закинем поле Товар в область строк, поле Прайс в область столбцов и поле Цена в область значений:

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

Общие итоги в такой таблице смысла не имеют, и их можно отключить на вкладке Конструктор – Общие итоги – Отключить для строк и столбцов (Design – Grand Totals).

Если изменятся цены (но не количество товаров!), то достаточно просто обновить созданную сводную, щелкнув по ней правой кнопкой мыши – Обновить (Referesh).

Плюсы: такой подход на порядок быстрее работает с большими таблицами, чем ВПР. 

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

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

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

В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке Данные (Data), а для Excel 2010-2013 ее нужно отдельно скачать с сайта Microsoft и установить – получите новую вкладку Power Query.

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

Для этого выделим диапазон с данными и нажмем на клавиатуре сочетание Ctrl+T или выберем на ленте вкладку – Форматировать как таблицу (Home – Format as Table).

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

Загрузите старый прайс в Power Query с помощью кнопки Из таблицы/диапазона (From Table/Range) с вкладки Данные (Data) или с вкладки Power Query (в зависимости от версии Excel). После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить – Закрыть и загрузить в… (Close & Load – Close & Load To…):

… и в появившемся затем окне выбрем Только создать подключение (Connection Only).

Повторите то же самое с новым прайс-листом. 

Теперь создадим третий запрос, который будет объединять и сравнивать данных из предыдущих двух. Для этого выберем в Excel на вкладке Данные – Получить данные – Объединить запросы – Объединить (Data – Get Data – Merge Queries – Merge) или нажмем кнопку Объединить (Merge) на вкладке Power Query.

В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части зададим способ объединения – Полное внешнее (Full Outer):

После нажатия на ОК должна появиться таблица из трех столбцов, где в третьем столбце нужно развернуть содержимое вложенных таблиц с помощью двойной стрелки в шапке:

В итоге получим слияние данных из обеих таблиц:

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

А теперь самое интересное. Идем на вкладку Добавить столбец (Add Column) и жмем на кнопку Условный столбец (Conditional Column). А затем в открывшемся окне вводим несколько условий проверки с соответствующими им значениями на выходе:

Останется нажать на ОК и выгрузить получившийся отчет в Excel с помощью все той же кнопки Закрыть и загрузить (Close & Load) на вкладке (Home):

Красота.

Причем, если в будущем в прайс-листах произойдут любые изменения (добавятся или удалятся строки, изменятся цены и т.д.), то достаточно будет лишь обновить наши запросы сочетанием клавиш Ctrl+Alt+F5 или кнопкой Обновить все (Refresh All) на вкладке Данные (Data).

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

Минусы: Требует установленной надстройки Power Query (в Excel 2010-2013) или Excel 2016. Имена столбцов в исходных данных не должны меняться, иначе получим ошибку “Столбец такой-то не найден!” при попытке обновить запрос.

Ссылки по теме

Источник: //www.planetaexcel.ru/techniques/8/4352/

Сравнение таблиц excel

Методы сравнения таблиц в Microsoft Excel

​Смотрите также​ 2003 как раз​ появлялось и я​ правило»- «Использовать формулу​ лишнее форматирование. Если​ паролем. Нажмите кнопку​ же книги, которые​ верхнем окне.

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

​ вариантов вы не​«не равно»​ выражение следующего типа:​Довольно часто перед пользователями​ не более 3-х​ мог формулу скопировать​ для определения форматированных​ в Excel открыты​ОК​ вы хотите сравнить.​ если мы нажмем​ того, как именно​.​

​ условиям оператора​ каждую ячейку первой​

​ заданному условию. Синтаксис​После автоматического перемещения в​ избрали, запускается окно​

Способы сравнения

​(​=A2=D2​ Excel стоит задача​ страниц). Для такого​ в другую таблицу,​ ячеек:».​

  • ​ две книги, которые​и введите пароль​
  • ​ А может быть,​ на нижнее окно​
  • ​ пользователь желает, чтобы​После этого открывается небольшое​

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

​ нужно сравнить, можно​ для книги. Узнайте​ вам нужно найти​ (сделаем активным нижнюю​ это сравнение выводилось​ окошко, в котором​

Способ 1: простая формула

​.

То есть, первая​ которые расположены во​ такой вид:​«Диспетчера правил»​ Устанавливаем переключатель в​) и выделяем сравниваемый​ конкретном случае координаты​ или списков для​ формулу? Пока мои​ сравнения? Название файлов​ формулу:​ запустить средство сравнения​ подробнее о том,​ потенциальные проблемы, такие​ таблицу), то полоса​ на экран.​ нужно определить, ссылочный​ фамилия присутствует в​ втором табличном диапазоне.​=СЧЁТЕСЛИ(диапазон;критерий)​щелкаем по кнопке​ позицию​ диапазон второй области.​ будут отличаться, но​ выявления в них​ попытки приводят к​ должны быть, как​

​Щелкните по кнопке «Формат»​ электронных таблиц с​ как действуют пароли​ как введенные вручную​ прорутки появится в​ Максим Тютюшев​ вид должна иметь​ обоих списках.​

  1. ​ В четырех случаях​Аргумент​«OK»​«Выделить по строкам»​​ Далее обворачиваем полученное​​ суть останется одинаковой.​ отличий или недостающих​ ошибке. Например, пытался​ у вас, или​ и на вкладке​​ помощью команды “Сравнить​​ при использовании средства​ (а не вычисляемые)​ нижнем (активном) окне.​Когда нужно​ функция​С помощью маркера заполнения,​

    ​ результат вышел​

    ​«Диапазон»​и в нем.​. Жмем по кнопке​ выражение скобками, перед​

  2. ​Щелкаем по клавише​​ элементов. Каждый юзер​​ провести сравнение в​ произвольные тоже можно?​ «Заливка» укажите зеленый​ файлы”.​ сравнения электронных таблиц.​​ итоги или формулы​​В активном окне​сравнить две таблицы Excel​
  3. ​ИНДЕКС​ уже привычным способом​«1»​представляет собой адрес​Теперь во второй таблице​«OK»​ которыми ставим два​Enter​ справляется с этой​ двух аналогичных таблицах​ Я вижу, что​ цвет. На всех​Если вкладка “Inquire” (Запрос)​

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

  4. ​ у вас есть​ все функции строки​ с большими таблицами,​ работы с массивами.​ЕСЛИ​ случаях –​ производится подсчет совпадающих​ данные, несовпадающие с​Как видим, после этого​«-»​ сравнения. Как видим,​​ но чаще всего​​ (А-С), но более​ начинается с А3,​Выделите диапазон первого списка:​ Excel 2013, см. статью​​ из двух частей.​​ Microsoft Office профессиональный​
  5. ​ меню.​ сложно держать в​ Нам нужен второй​на весь столбец.​«0»​ значений.​ соответствующими значениями первой​ несовпадающие значения строк​​. В нашем случае​​ при сравнении первых​
  6. ​ на решение указанного​​ массивных – где​​ тогда как надо​​ C2:C15 и снова​​ Включение средства диагностики​​ Книга в левой​​ плюс 2013, вы​​Сейчас эти полосы прокрутки​​ уме все цифры​
  7. ​ вариант. Он установлен​​ Как видим, по​​. То есть, программа​Аргумент​ табличной области, будут​ будут подсвечены отличающимся​ получилось такое выражение:​ ячеек обоих списков​ вопроса тратится довольно​ 65536 строк. Но​

    ​ с А2. Кстати,​

    ​ выберите инструмент: «ГЛАВНАЯ»-«Условное​ электронных таблиц. Подробнее​ части соответствует файлу,​ можете с помощью​ передвигают таблицы синхронно.​ из разных ячеек​ по умолчанию, так​ двум позициям, которые​ не смогла отыскать​

    ​«Критерий»​​ выделены выбранным цветом.​​ оттенком.

    Кроме того,​–(A2:A7D2:D7)​ программа указала показатель​ большое количество времени,​ Эксель “наполнил штаны”​ у вас по-моему​​ форматирование»-«Создать правило»- «Использовать​​ об инструментах этой​​ указанному в поле​​ средства сравнения электронных​Чтобы​ таблицы. Для того,​ что в данном​ присутствуют во второй​ во второй таблице​​задает условие совпадения.​​Существует ещё один способ​ как можно судить​

    ​Щелкаем по кнопке​

    ​«ИСТИНА»​​ так как далеко​​ и выдал ошибку.​

  8. ​ сравниваются лишь 3​ формулу для определения​ надстройки см. в​ “Compare” (Сравнить) (обычно​ таблиц от корпорации​​таблицы Excel перемещать независимо​​ чтобы облегчить этот​ окошке просто щелкаем​ таблице, но отсутствуют​ два значения, которые​ В нашем случае​ применения условного форматирования​ из содержимого строки​«OK»​​, что означает совпадение​​ не все подходы​

​Pelena​ столбца (А-С), а​ форматированных ячеек:».​ статье Возможности средства​ это более старый​ Майкрософт создать отчет​ друг от друга​ процесс существуют специальные​ по кнопке​ в первой, формула​ имеются в первом​ он будет представлять​ для выполнения поставленной​ формул, программа сделает​.​ данных.​ к данной проблеме​:​

​ мне надо от​

​В поле ввода введите​ диагностики электронных таблиц.​ файл), а книга​ о найденных различиях.​, нажимаем кнопку «Сихронная​ функции.​«OK»​ выдает номера строк.​

Способ 2: выделение групп ячеек

​ табличном массиве.​ собой координаты конкретных​ задачи. Как и​ активной одну из​Оператор производит расчет и​Теперь нам нужно провести​ являются рациональными. В​Eclan​ А до К.​ формулу:​Если в вашей организации​ в правой части —​

  1. ​Важно:​ прокрутка» (на рисунке​​Сначала вспомним, какими​​.​Отступаем от табличной области​​Конечно, данное выражение для​​ ячеек первой табличной​ предыдущие варианты, он​ ячеек, находящуюся в​​ выводит результат. Как​​ аналогичную операцию и​ то же время,​, оформите формулу тегами​​ Совпадения должны быть​​Щелкните по кнопке «Формат»​

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

    ​ требует расположения обоих​ указанных не совпавших​​ видим, в нашем​​ с остальными ячейками​ существует несколько проверенных​ с помощью кнопки​ 100%-тными + уникальные​ и на вкладке​​ книги Excel или​​ поле “To” (С​

  2. ​ таблиц доступно только​ зеленым цветом). Теперь​​ таблицу в Excel.​​ИНДЕКС​ колонку номерами по​
  3. ​ табличные показатели, можно​Выделяем первый элемент дополнительного​ сравниваемых областей на​ строках.​ случае результат равен​ обеих таблиц в​ алгоритмов действий, которые​​ fx в режиме​​ строки (которых нет​​ «Заливка» укажите синий​​ базы данных Access,​
  4. ​ чем) (обычно это​ с версиями Office​ каждое окно прокручиваестя​ Для этого смотрим​. Данный оператор предназначен​ порядку, начиная от​ применять и в​ столбца, в котором​ одном листе, но​Произвести сравнение можно, применив​ числу​

Способ 3: условное форматирование

​ тех колонках, которые​ позволят сравнить списки​ правки поста.​ в Списке2). И​ цвет. На всех​ подумайте об установке​ более новый файл).​ профессиональный плюс 2013 и​ самомтоятельно.​

  1. ​ статью “Как сделать​ для вывода значения,​1​ существующем виде, но​ будет производиться подсчет​ в отличие от​ метод условного форматирования.​«1»​ мы сравниваем. Но​ или табличные массивы​​И излишнее цитирование​​ потом, как вывести​​ окнах жмем ОК.​​ средств управления электронными​ Подробные сведения отображаются​ Office 365 профессиональный плюс.​​После работы с таблицами,​​ таблицу в Excel”.​ которое расположено в​​. Количество номеров должно​​ есть возможность его​
  2. ​ количества совпадений. Далее​ ранее описанных способов,​ Как и в​​, то есть, это​​ можно просто провести​
  3. ​ в довольно сжатые​ – это тоже​​ вторую таблицу для​​При определении условий для​​ таблицами и базами​​ в области под​В Windows 7​ закрывают их кнопкой​Для того, чтобы​ определенном массиве в​​ совпадать с количеством​​ усовершенствовать.​ щелкаем по пиктограмме​ условие синхронизации или​ предыдущем способе, сравниваемые​​ означает, что в​​ копирование формулы, что​ сроки с минимальной​ нарушение Правил форума​ сравнения?​ форматирования ячеек столбцов​ данных, которые предлагает​ двумя частями таблицы.​   : в меню​​ «Закрыть» (Х), но​​разместить одновременно несколько таблиц​ указанной строке.​ строк во второй​Сделаем так, чтобы те​«Вставить функцию»​ сортировки данных не​ области должны находиться​ сравниваемых списках было​

    ​ позволит существенно сэкономить​

    ​ затратой усилий. Давайте​Eclan​Написал расширенную формулу:​ мы использовали функцию​​ корпорация Майкрософт. Сервер​​ Изменения выделяются разными​

  4. ​Пуск​​ именно той таблицы,​​ Excel​​Как видим, поле​​ сравниваемой таблице. Чтобы​ значения, которые имеются​.​ будет являться обязательным,​ на одном рабочем​ найдено одно несовпадение.​ время. Особенно данный​​ подробно рассмотрим данные​​:​
  5. ​=ЕСЛИ(СУММПРОИЗВ(($A2='C:\[Список2.xls]Лист1'!$A$1:$A$15818)*($B2='C:\[Список2.xls]Лист1'!$B$1:$B$15818)*($C2='C:\[Список2.xls]Лист1'!$C$1:$C$15818)*($D2='C:\[Список2.xls]Лист1'!$D$1:$D$15818)*($E2='C:\[Список2.xls]Лист1'!$E$1:$E$15818)*($F2='C:\[Список2.xls]Лист1'!$F$1:$F$15818)*($G2='C:\[Список2.xls]Лист1'!$G$1:$G$15818)*($H2='C:\[Список2.xls]Лист1'!$H$1:$H$15818)*($I2='C:\[Список2.xls]Лист1'!$I$1:$I$15818)*($J2='C:\[Список2.xls]Лист1'!$J$1:$J$15818)*($K2='C:\[Список2.xls]Лист1'!$K$1:$K$15818));”есть”;”нет”)​ СЧЕТЕСЛИ. В данном​ аудита и управления​​ цветами в соответствии​​Windows в разделе​
  6. ​ которую хотим закрыть​или​​«Номер строки»​​ ускорить процедуру нумерации,​​ во второй таблице,​​Происходит запуск​
  7. ​ что выгодно отличает​ листе Excel и​ Если бы списки​ фактор важен при​ варианты.​Perfect2You​

​расположив оба списка​ примере эта функция​ (Майкрософт) предоставляет комплексные​ с их типом.​Средства Office 2013​ (обведена желтым цветом)​части одной большой таблицы​уже заполнено значениями​ можно также воспользоваться​ но отсутствуют в​Мастера функций​ данный вариант от​ быть синхронизированными между​ были полностью идентичными,​ сравнивании списков с​Скачать последнюю версию​

  1. ​, ваша формула (тестировал​ Cписок1 и Список2​
  2. ​ проверяет сколько раз​ возможности управления изменениями​​В двух расположенных рядом​​выберите​ или красной кнопкой​​на мониторе, заходим​​ функции​ маркером заполнения.​​ первой, выводились отдельным​​. Переходим в категорию​ ранее описанных.​​ собой.​​ то результат бы​
  3. ​ большим количеством строк.​ Excel​ ее последние дни)​ в C:\ Однако​ встречается значение второго​ для файлов Excel​ частях таблицы сравнивается​​Средство сравнения электронных таблиц​​ «Закрыть» вверху экрана​ на закладку «Вид»​НАИМЕНЬШИЙ​После этого выделяем первую​ списком.​

Источник: //my-excel.ru/tablicy/sravnenie-tablic-excel.html

Как сделать сравнительную таблицу в excel?

Методы сравнения таблиц в Microsoft Excel

    Добрый день!

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

Да, работать с таблицами удобно и хорошо, но вот когда нужно их сравнение, визуально это сделать достаточно затруднительно.

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

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

      Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

  • простой способ, как сравнить две таблицы в Excel;
  • быстрое выделение значений, которые отличаются;
  • сравнить две таблицы в Excel с помощью условного форматирования;
  • сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил;
  • как сравнить две таблицы в Excel с помощью функции ВПР;
  • как сравнить две таблицы в Excel с помощью функции ЕСЛИ;
  • сравнить две таблицы с помощью макроса VBA;
  • сравнить с помощью надстройки Inquire.

Простой способ, как сравнить две таблицы в 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 и ввести код:

Sub Find_Matches()Dim CompareRange As Variant, x As Variant, y As Variant‘ Установка переменной CompareRange равной сравниваемому диапазонуSet CompareRange = Range(«B1:B11»)‘ Если сравниваемый диапазон находится на другом листе или книге,‘ используйте следующий синтаксис‘ Set CompareRange = Workbooks(«Книга2»). _‘   Worksheets(«Лист2»).Range(«B1:B11»)‘‘ Сравнение каждого элемента в выделенном диапазоне с каждым элементом‘ переменной CompareRangeFor Each x In SelectionFor Each y In CompareRangeIf x = y Then x.Offset(0, 2) = xNext yNext xEnd Sub

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

     Для использования вашего макроса, вы переходите на ваш рабочий лист, выделяете диапазон (с примера видно, это A1:A11) и нажимаем горячее сочетание клавиш Alt+F8. В новом диалоговом окне выбираете ваш макрос Find_similar и выполняете его.

Сравнение с помощью надстройки Inquire

     Этот вариант сравнить стал доступен с релизом 2013 версии Excel, редактору добавили надстройку Inquire, которая позволит проанализировать и сравнить два файла Excel.

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

Вот для определения этих изменений вам необходим инструмент WorkbookCompare в надстройкеInquire.

     Ну вот мы и рассмотрели 8 способов как сравнить две таблицы в Excel, эти варианты помогут вам решить свои аналитические задачи и упростят вашу работу.

     Был рад вам помочь!

Прибыль — это гонорар, который вы получаете за умение пользоваться изменениями

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

Сравнение двух столбцов на совпадения в 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

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

В данном примере эта функция проверяет сколько раз встречается значение второго аргумента (например, A2) в списке первого аргумента (например, Таблица_2).

Если количество раз = 0 в таком случае формула возвращает значение ИСТИНА. В таком случае ячейке присваивается пользовательский формат, указанный в параметрах условного форматирования.

Скачать пример сравнения 2 таблицы в Excel

Ссылка во втором аргументе относительная, значит по очереди будут проверятся все ячейки выделенного диапазона (например, A2:A15). Например, для сравнения двух прайсов в Excel даже на разных листах. Вторая формула действует аналогично. Этот же принцип можно применять для разных подобных задач.

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

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

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

Источник: //word-office.ru/kak-sdelat-sravnitel-nuyu-tablicu-v-excel.html

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

Методы сравнения таблиц в Microsoft Excel

    Добрый день!

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

Да, работать с таблицами удобно и хорошо, но вот когда нужно их сравнение, визуально это сделать достаточно затруднительно.

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

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

      Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

Поделиться:
Нет комментариев

    Добавить комментарий

    Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.