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

Сравнение двух версий книги с помощью средства сравнения электронных таблиц

​Важно:​ других листах или​ и другими книгами​ книги.​ Имеется даже окно,​ внизу страницы. Для​ любые изменения (добавятся​ наши таблицы, выделим​и​Минусы​

Закинем поле​ параметра. В данном​ что-то добавилось (финики,​ в файл Excel,​

​ частях таблицы сравнивается​​(Сравнение файлов) в​ возникнуть вопросы «Кто​ Перед очисткой лишнего форматирования​ даже в других​ и источниками данных.​

1. ​Команда​ в котором построчно​

2. ​ удобства также приводим​ или удалятся строки,​ в них столбцы​Таблица2​: надо вручную копировать​Товар​ случае, мы применим​​ честнок…), что-то пропало​​ более удобный для​

3. ​ каждый лист из​​ строке​​ ее изменил? И​​ ячейки мы рекомендуем​​ книгах, используйте инструмент​

4. ​ На схеме также​​Workbook Analysis​​ могут отображаться изменения​ ссылку на оригинал​​ изменятся цены и​​ с названиями товаров​, которые получаются по-умолчанию).​

   ​ данные друг под​в область строк,​ ее, чтобы подтянуть​ (ежевика, малина…), у​ чтения. Выберите​ обоих файлов, начиная​To​ что именно изменилось?»​

5. ​ создать резервную копию​​Связи ячейки​​ отображаются дополнительные уровни​(Анализ книги) создает​​ кода VBA. Различия​​ (на английском языке).​ т.д.), то достаточно​

   ​ и в нижней​Загрузите старый прайс в​ друга и добавлять​

   ​ поле​​ старые цены в​ каких-то товаров изменилась​Home > Export Results​ с самого крайнего​(С чем) с​

6. ​ Средство сравнения электронных​​ файла, так как​​. Такие связи с​

​ соединений книги, предоставляя​​ интерактивный отчет, отображающий​ между ячейками отображаются​Предположим, что вы хотите​ будет лишь обновить​ части зададим способ​​ Power Query с​​ столбец с названием​Прайс​ новый прайс:​ цена (инжир, дыня…).​(Главная > Экспорт результатов).​ слева. Если лист​

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

Интерпретация результатов

• ​выберите версию книги,​ поможет вам ответить​ привести к увеличению​ существовать в виде​ данных для книги.​ книге и ее​ восприятия таблице.​ анализ книги для​ клавиш Ctrl+Alt+F5 или​Полное внешнее (Full Outer)​Из таблицы/диапазона (From Table/Range)​

• ​ таблиц изменяются, то​ и поле​ получилась ошибка #Н/Д​​ и вывести все​​ вставить их в​ он все равно​

• ​ которую хотите сравнить​ на эти вопросы —​ размера файла, а​ формул или ссылок​Подробнее об этом можно​ структуре, формулах, ячейках,​Команда​ проблемы или несоответствия​ кнопкой​:​с вкладки​ придется делать все​Ц​ — отсутствуют в​ эти изменения.​ другую программу, выберите​

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

​ отображается и сравнивается​ с более ранней.​ найдет изменения и​ отменить эти изменения​ на именованные диапазоны.​ узнать в статье​ диапазонах и предупреждениях.​Сравнить файлы​ или Просмотр связей​Обновить все (Refresh All)​После нажатия на​Данные (Data)​ заново.​ена​ старом списке, т.е.​

• ​Для любой задачи в​Home > Copy Results to​ в средстве сравнения​Примечание:​​ выделит их.​​ невозможно.​

• ​ Схема может пересекать​ Просмотр связей между​ На рисунке ниже​​сравнивает два файла​ между книг или​​на вкладке​ОК​

• ​или с вкладки​Power Query — это​​в область значений:​​ были добавлены. Изменения​ Excel почти всегда​

Другие причины для сравнения книг

​ Clipboard​ электронных таблиц.​  Можно сравнивать два​Важно:​Подробнее об этом можно​ листы и книги.​ книгами.​ показана очень простая​ с помощью средства​ листов. Если на​Данные (Data)​должна появиться таблица​

​Power Query​ бесплатная надстройка для​Как видите, сводная таблица​ цены также хорошо​ есть больше одного​(Главная > Копировать результаты​Если содержимое не умещается​ файла с одинаковыми​  Средство сравнения электронных​ узнать в статье​В данной схеме отображаются​При наличии множества взаимозависимых​. support.office.com>

support.office.com>

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

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

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

В Н4 вводим формулу

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

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

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

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

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

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

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

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

Формула в J4:

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

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

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

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

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

Формула в I4:

в К4:

в L4:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Правила синтаксиса и параметры функции СОВПАД в Excel

Функция СОВПАД имеет следующий вариант синтаксической записи:

=СОВПАД(текст1;текст2)

Описание аргументов:

• текст1 – обязательный для заполнения, принимает ссылку на ячейку с текстом или текстовую строку для сравнения с данными, принимаемые вторым аргументом.
• текст2 – обязательный для заполнения, принимает ссылку на ячейку или текст, с которым сравниваются данные, переданные в виде первого аргумента.

Примечания:

1. Результат выполнения функции СОВПАД, принимающей на вход два имени, является код ошибки #ИМЯ? (например, СОВПАД(имя;имя)). Для корректной работы функции указываемые текстовые данные необходимо помещать в кавычки (например, («имя»;«имя»)).
2. Функция выполняет промежуточное преобразование числовых данных в текст. Например, результат выполнения =СОВПАД(111;111) будет логическое значение ИСТИНА. Однако, преобразование логических данных в числа текстового формата не выполняется. Например, результат выполнения =СОВПАД(ИСТИНА;1) будет логическое ЛОЖЬ.
3. Результат сравнения двух пустых ячеек или пустых текстовых строк с использованием функции СОВПАД — логическое ИСТИНА.

Функция РАЗНДАТ – разность двух дат в днях, месяцах и годах

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

Ее синтаксис элементарный.

РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)

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

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

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

Разница 2 дат в днях

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

=РАЗНДАТ(A2;B2;”d”)

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

Предположим, начальной датой у нас является 23.02.2009, а конечной – 24.02.2009.

В результате формулой будет возвращено значение 1, что равняется количеству дней, которое прошло с момента исходной даты (отсчет начинается с нуля). Именно из-за того, что отсчет в этом случае начинается с нуля, нельзя использовать эту формулу в чистом виде, например, чтобы рассчитать количество дней, которое отработал человек. Ведь очевидно, что если человек начал работать 23 февраля, а 24 числа прекратил работать, то он по факту отработал 2 дня, а не один. 

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

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

Разница дат в полных месяцах

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

То есть, формула следующая.

=РАЗНДАТ(A2;B2;”m”)

Эта формула также может давать несколько неточные данные. Поэтому всегда нужно перепроверять значения самостоятельно. 

Разница в днях без учета лет

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

1

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

Как создать таблицу в Word

Прежде чем запускать специально созданный для работы с таблицами редактор Excel с его непростыми, скажем прямо, таблицами, вспомним про то, что и в текстовом редакторе Microsoft Word тоже есть таблицы. Поэтому для начала нужно попробовать в Word.

Для представленного выше списка из 2-х учеников потребуется создать в редакторе MS Word таблицу из 3-х строк (учеников у нас в списке пока двое, а самую первую строку таблицы мы используем для заголовков столбцов), и из 5-и столбцов для ввода в них следующих данных:

• фамилия ученика
• имя ученика
• отчество ученика
• дата рождения ученика
• телефон ученика.

Приступаем. Откроем текстовый редактор Ворд. Введем в первой строке заголовок нашей таблицы, например, «Список учащихся 8В класса».

• Затем поставим курсор на вторую строку,
• откроем вкладку «Вставка» (1 на рис. 1),
• кликнем по иконке «Таблица» (2 на рис. 1) и
• выберем строку меню «Вставить таблицу» (3 на рис. 1).

Рис. 1 (кликните по рисунку для увеличения). Создание новой таблицы в редакторе Word.

В открывшемся служебном окне «Вставка таблицы»

• указываем «Число столбцов» 5 (1 на рис. 2) и
• «Число строк» 3 (2 на рис. 2) ,
• далее жмем на кнопку «ОК» (3 на рис. 2).

Рис. 2. Описание вставляемой таблицы (указание числа столбцов и строк) в редакторе Ворд.

Таблица успешно вставляется и имеет вид, как показано на рис. 3.

Рис. 3 (кликните по рисунку для увеличения). Вставленная пока еще пустая таблица из 3-х строк и 5-и столбцов в редакторе Microsoft Word.

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

В оставшиеся 2 строки таблицы заносим данные ученика по фамилии Иванов и ученицы по фамилии Петрова (рис. 4).

Рис. 4 (кликните по рисунку для увеличения). Заполненная таблица, сделанная в редакторе Ворд.

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

Как выделить и перенести часть таблицы в Ворде

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

Скажем, нам нужны только фамилии учеников. Выделяем данные из первого столбца таблицы. Для этого помещаем курсор мышки в клетку, которая будет второй по вертикали в первой колонке таблицы – на фамилию «Иванов». Нажимаем на левую кнопку мышки и, не отпуская ее, перемещаем курсор мышки в третью строку таблицы. То есть как бы «проводим» мышкой по клеткам таблицы, где занесены фамилии учеников. Результат такого выделения показан на рис. 5.

Рис. 5 (кликните по рисунку для увеличения). Выделение одной колонки таблицы в редакторе MS Word.

Затем копируем эти данные в буфер обмена (клик мышкой на кнопку «Копировать» – 1 на рис. 6).

Рис. 6 (кликните по рисунку для увеличения). Копирование выделенной части таблицы в буфер обмена в редакторе Microsoft Word.

После копирования части таблицы в буфер обмена давайте установим курсор под таблицей не прямо на следующей строке, а через одну строку (выделено синим цветом – 1 на рис. 7), и кликнем по кнопке «Вставить» (2 на рис. 7).

Рис. 7 (кликните по рисунку для увеличения). Подготовка к вставке части таблицы в другое место текста в редакторе Word.

Часть таблицы, а именно – фамилии учеников – вставится ниже под основной таблицей (рис. 8).

Рис. 8 (кликните по рисунку для увеличения). Часть таблицы (фамилии учеников) воспроизведена в другом месте текста в редакторе Ворд.

Сравнение двух версий книги с помощью средства сравнения электронных таблиц

​ на эти вопросы —​ файла. Это помогает​ в других книгах.​ помощью ссылок на​ сравнения электронных таблиц​ внизу страницы. Для​ 1 If numRow​До этого не​ 20 минут, думаю​ о процессе).​IvankoSusanin​IvankoSusanin​ ошибку. Ошибочное значение​ — Зарплата.​

​ кадров получает список​​ обозначения, поясняющие значения​  Появление сообщения «Не​ найдет изменения и​ избежать «раздувания электронной​ Когда вы наводите​

​ другие ячейки, можно​ (Майкрософт).​

​ удобства также приводим​ >= numRowProv Then​ имел дело с​ потому что в​Да, если есть​: Ребят задача усложнилась!​: Можно поподробнее…​​ не является логическим​​В поле «Диапазон:» введите​

​ сотрудников вместе с​​ цветов.​​ удается открыть книгу»​​ выделит их.​​ таблицы», что увеличивает​

​ указатель мыши на​​ запутаться. Используйте​​В Windows 10 вы​ ссылку на оригинал​​ Razn = numRow​​ VBA. Начинаю только​ 500000 строк каждое​
​ желание оплатить -​Есть два одинаковых​понятно​ значением. Поэтому исползаем​ ссылку:​ их окладами

Он​Если вы хотите сохранить​ может означать, что​

​Важно:​​ скорость работы Excel.​​ узел схемы, например​схему связей книги​​ можете запустить его,​​ (на английском языке).​ — numRowProv For​
​ изучать. Начальство подкинуло​ значение повторяется максимум​ через 15 запусков​
​ по структуре прайса:​​Цитата а столбцы,​ функцию ЕСЛИОШИБКА, которая​Теперь перейдите на лист​ копирует список на​ результаты или проанализировать​

​ книга защищена паролем.​​  Средство сравнения электронных​​Важно:​

​ на страницу с​​для создания интерактивной​ не открывая Excel.​Предположим, что вы хотите​ i = numRow​ такую задачку:​​ дважды), на файле​​ welcome​Прайс1:​ которые надо сравнить​ присвоит логическое значение​ с именем «Февраль»​ новый лист рабочей​

​ их в другом​ Нажмите кнопку​ таблиц доступно только​ Перед очисткой лишнего форматирования​ именем «Запад», появляется​ графической карты зависимостей,​ Для этого нажмите​ Сравнение версий книги,​ To Cells(Rows.Count, 1).End(xlUp).Row​Из определенной программы​ более 50Мб у​Steel Rain​Прайс2 :​ — в новые​ для каждой ошибки​ и выделите диапазон​ книги Excel. Задача​

Интерпретация результатов

• ​ приложении, экспортируйте их​ОК​ с версиями Office​ ячейки мы рекомендуем​ выноска со сведениями.​ образованных соединениями (ссылками)​ кнопку​ анализ книги для​ iprov = i​ выгружаются отчеты в​ меня комп повесился​

• ​: Доброго всем времени​Необходимо проверить на​ столбцыеще раз рядом?​​ – ИСТИНА. Это​​ ячеек B2:C12.​ следующая: сравнить зарплату​

• ​ в файл Excel​и введите пароль.​ профессиональный плюс 2013 и​ создать резервную копию​Подробнее об этом можно​ между файлами. Типы​Пуск​ проблемы или несоответствия​ — Razn For​ Excel. В этих​Данные можно разместить​ суток.​ наличие изменений: указать​Цитата и строю​ способствует присвоению нового​А на панели «ГЛАВНАЯ»​

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

​ сотрудников, которая изменилась​ или скопируйте и​ Узнайте подробнее о​ Office 365 профессиональный плюс.​ файла, так как​ узнать в статье​ ссылок в схеме​, введите​ или Просмотр связей​ y = 1​ отчетах очень много​ на листах в​Сразу скажу, практически​ место несоответствия (​ сводную.как вызвать функцию?​

• ​ формата только для​ выберите «Условное форматирование»-«Создать​ по отношению к​ вставьте в другую​​ том, как действуют​​Откройте средство сравнения электронных​

• ​ иногда это может​ Просмотр связей между​ могут включать другие​​Средство сравнения электронных таблиц​ между книг или​​ To numCol If​ данных, рассортированных по​

• ​ одной книге, это​ впервые пользуюсь пакетом​​ можно подсветить), в​​Извиняюсь за глупые​ ячеек без совпадений​

Другие причины для сравнения книг

• ​ правило»-«Использовать формулу для​ предыдущему месяцу. Для​ программу, например Microsoft​ пароли при использовании​ таблиц.​ привести к увеличению​ листами.​ книги, базы данных​и щелкните​ листов. Если на​ wB1.Sheets(«Лист1»).Cells(i, y) <>​ времени.​

• ​ не принципиально, привести​ MS Office, поэтому​ любом из файлов!​ вопросы, с excel​ значений по зарплате​ определения форматированных ячеек:».​ этого необходимо выполнить​ Word. Можно также​ средства сравнения электронных​В левой нижней области​ размера файла, а​Чтобы получить подробную интерактивную​

support.office.com>

Способ № 2: сравнить в Excel

Самый распространенный метод: выгружаете первоначальный вариант отчета и тот же отчет с поправками в формате .xls и сверяете их в Microsoft Excel, пользуясь доступными формулами.

Для примера приведу стандартную формулу «Если».

Настройка:

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

2. В верхней панели кликаем на ярлык формулы .

3. В предложенном списке выбираем функцию «ЕСЛИ», нажимаем «ОК».

4. Задаем условие в поле «Лог-выражение». На скрине-примере условие следующее: если значение ячейки по колонке «Конечный остаток» левой таблицы равен значению ячейки по колонке «Конечный остаток» справа.

5. В поле «Значение_если_истина» указываем, какой вывод нужно вывести, если значения равны.

6. В поле «Значение_если_ложь» обозначаем, какой вывод нужно вывести, если значения различны.

7. Нажимаем «ОК».

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

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

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

В маленьких файлах вы можете суммировать значения ячеек в колонках и тем самым выходить на строки с различиями.

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

Вот один из примеров настройки:

И ее результат:

Плюсы: 

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

Минусы: 

• иногда потребуется не один час, чтобы подстроить таблицу для шаблонной формулы; 

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

Описание работы программы

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

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

Диалоговое окно поделено на две части: левая для первой таблицы и правая для второй.

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

• Указать диапазоны таблиц.
• Установить чекбокс (галку/птичку) под выбранным диапазоном таблиц в том случае если таблица включает в себя шапку (строку заголовка).
• Выбрать столбцы левой и правой таблицы, по которым будет вестись сравнение (в случае, если диапазоны таблиц не включают заголовки столбцы будут пронумерованы).
• Указать тип сравнения.
• Выбрать вариант выдачи результатов.

Тип сравнения таблиц

Программа позволяет выбрать несколько типов сравнения таблиц:

Найти строки одной таблицы, которые отсутствуют в другой таблице

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

Найти совпадающие строки

При выборе данного типа сравнения, программа находит строки, которые совпадают в первой и второй таблицах. Совпадающими считаются строки, в которых значения в выбранных столбцах сравнения (1, 2, 3) одной таблицы полностью совпадают со значениями столбцов второй таблицы.

Пример работы программы в данном режиме представлен справа на картинке.

Сопоставить таблицы на основе выбранной

В данном режиме сравнения, напротив каждой строки первой таблицы (выбранной в качестве основной), копируются данные совпадающей строки второй таблицы. В случае если совпадающие строки отсутствуют, строка напротив основной таблицы остается пустой.

Сравнение таблиц по четырем и более столбцам

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

• Создайте в таблицах по пустому столбцу.
• В новых столбцах используя формулу =СЦЕПИТЬ объедините столбцы, ко которым хотите провести сравнение.

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

Сравнение таблиц с похожими искомыми значениями

​ – делит одну​​указываем адрес диапазона​​«K»​ указываем координаты первой​​ Для этого выполним​ заданному условию. Синтаксис​​ предыдущие варианты, он​ будем делать во​То есть, как видим,​ несовпадений. Для этого​​ примере двух таблиц,​: Смотрите приложение​: Термин надёжный -​Используем​ способе читайте в​ сдали отчет по​ чуть выше, обведена​ большую таблицу Еxcel​​ значений второй таблицы.​указывается, какое по​ ячейки с фамилией​ копирование, воспользовавшись маркером​ данного оператора имеет​ требует расположения обоих​ второй таблице. Поэтому​ перед координатами данных,​ выделяем тот элемент​ размещенных на одном​Hugo121​ видится мне крайне​функцию «СЧЕТЕСЛИ» в​ статье «Как сравнить​ продажам. Нам нужно​ зеленым цветом). Теперь​ на отдельные фрагменты.​ При этом все​ счету наименьшее значение​ во второй таблице,​ заполнения, как это​ такой вид:​ сравниваемых областей на​ выделяем список работников,​

​ которые расположены на​​ листа, куда оно​

​ листе.​​: НаименованиеЦена 10505335033507031521892189UE32J4000AU200002000040002000020000​ сомнительным, ибо пробел(ы)​

​Excel​​ даты в Excel».​

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

​ одном листе, но​​ находящийся в ней.​ других листах, отличных​ будет выводиться. Затем​Итак, имеем две простые​Czeslav​​ не помогут бороться​. Эта функция посчитает​Как посчитать разницу​ и выявить разницу.​ самомтоятельно.​ можно двигать. Можно​

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

​ щелкаем по значку​​ таблицы со списками​: Я Вас не​​ ни с лидирующим​ количество повторов данных​ дат, стаж, возраст,​У нас такая​После работы с таблицами,​​ перемещать содержимое этих​ перед ними знак​ ячейки столбца с​

​ нашем случае в​​ в нижнюю правую​«Диапазон»​​ ранее описанных способов,​​«Главная»​ выводится результат сравнения,​​«Вставить функцию»​​ работников предприятия и​

​ понимаю.​​ артикулом, ни с​ их первого столбца,​ как прибавить к​ таблица с данными​ закрывают их кнопкой​ фрагментов таблицы.​ доллара уже ранее​ нумерацией, который мы​ поле​ часть элемента листа,​​представляет собой адрес​ условие синхронизации или​​, щелкаем по кнопке​ указывается номер листа​​.​ их окладами. Нужно​А как должно​​ лидирующим+схожим, типа UE32J4000AU7​ сравнив их с​ дате число, т.д.,​​ из магазинов. Как​ «Закрыть» (Х), но​Перед тем, как нажать​ описанным нами способом.​

​ недавно добавили. Адрес​​«Значение если истина»​​ который содержит функцию​ массива, в котором​ сортировки данных не​«Условное форматирование»​​ и восклицательный знак.​В окне​​ сравнить списки сотрудников​ быть?​

​Hugo121​​ данными второго столбца.​ смотрите в статье​ перенести данные из​ именно той таблицы,​ кнопку «Разделить»​

​Жмем на кнопку​​ оставляем относительным. Щелкаем​получилось следующее выражение:​​СЧЁТЕСЛИ​ производится подсчет совпадающих​ будет являться обязательным,​, которая имеет месторасположение​Сравнение можно произвести при​Мастера функций​ и выявить несоответствия​Hugo121​: Пробелы будут бороться​В дополнительном столбце​ «Дата в Excel.​

​ разных таблиц в​​ которую хотим закрыть​, установите курсор в​«OK»​ по кнопке​

​СТРОКА(D2)​​, и после преобразования​ значений.​​ что выгодно отличает​ на ленте в​​ помощи инструмента выделения​

​в группе операторов​​ между столбцами, в​: НаименованиеЦена 10505335033507031521892189UE32J4000AU20000200004000#NUM!#N/A​

​ с ложными целями​​ устанавливаем формулы, они​

​ Формула» здесь.​​ одну, читайте в​

​ (обведена желтым цветом)​​ нужном месте таблицы.​.​​«OK»​Теперь оператор​

​ его в маркер​​Аргумент​

CyberForum.ru>

​ данный вариант от​

• Как в excel найти значения в таблице
• Excel получить данные сводной таблицы в excel
• Как сравнить две таблицы в excel на совпадения на разных листах
• Excel автоматическое добавление строк в таблицу
• Сравнение двух столбцов в excel и вывод несовпавших данных
• Как в excel построить сводную таблицу
• Как пронумеровать строки в таблице excel
• Как в excel создать сводную таблицу
• Excel сравнение двух таблиц из разных файлов
• Сравнение двух списков в excel с выделением отличающихся записей
• Как объединить две таблицы в excel в одну с разными данными
• Как в excel создать таблицу

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

Вопрос от пользователя

У меня есть одна задачка, и уже третий день ломаю голову — не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут — скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете ( прим. : фотка вырезана цензурой, все-таки личная информация) .

Заранее благодарю. Андрей, Москва.

Доброго дня всем!

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

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

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

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

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

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

=ВПР( A2 ; $E$1:$F$7 ; 2 ; ЛОЖЬ )

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

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

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

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

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

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

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

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

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

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

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

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

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

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