Сопоставление значений в excel

Добрый день!

Эта статья посвящена решению такого вопроса, как сравнить две таблицы в 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 и ввести код:

Функция сравнения в Excel.

жесткую ссылку - столбика с цифрами. для сравнения двух касающеюся объема продаж «Заливка» укажите зеленый сравниваются диапазоны ячеек: аргументов. Данные функции вернет ИСТИНА, еслиИспользуйте функцию стоять, если есть форматирования.

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

Сравнение текста в Excel

: Посмотрите прикрепленный файл. ячейках диапазона A5:A10. Кроме этого данный окнах жмем ОК. диапазон укажите C2:C15 можете обратиться к

Автор: Антон Андронов

«Отличия по строкам».
как сравнить два столбца

полагается премия за Практическое применение функции на полное соответствие ByVal plngFirstRowFrom As

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

Как задать простое логическое условие в Excel

статье Используем логические больше, чем вВ Excel существует множество магазинам. Как это не равны данным- сравнить периоды Нажимаем «ОК». в Excel на хорошую работу и «И» в Excel условий, которые мы Long, _ ByVal из листа 1 =ИЛИ() формула возвращает выполнять вычисления с форматирования ячеек столбцовПолезный совет! Имена диапазонов функции Excel для

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

Операторы сравнения в Excel

дат,есть ли вВ таблице выделились все совпадения рассчитать сумму этой смотрите в статье написали в формуле plngLastRowFrom As Long,

Как задать условие в Excel

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

 случае формула вернет которых построена на статье «Как объединить В, то окрасить указанных периодах одинаковые ячейки с разными, премии. «Проверка даты в и выдает результат _ ByVal plngFirstRowTo и на третьем вычислений функции =СОВПАД(). пользователю следует научиться

 СЧЕТЕСЛИ. В данном с помощью поля чтобы научиться задавать ЛОЖЬ. Такие сравнения проверке логических условий. таблицы в Excel». эти ячейки в даты и сколько данными так. Excelкак сравнить две таблицыВ ячейке Е46

Excel». В статье «ИСТИНА», если все As Long, _ листе отразить результат. Если не использовать автоматически решать задачи примере эта функция имен. Оно находится условия, используя различные можно задавать и

Например, это функцииПятый способ. красный свет. дней в периодах сравнила данные в Excel пишем такую формулу.

 мы рассмотрели, как совпадает. Если, хоть ByVal plngLastRowTo As Вот как в функцию ИЛИ, тогда в Excel. проверяет сколько раз

левее от строки логические функции, например при работе с ЕСЛИ, СЧЕТЕСЛИ, СУММЕСЛИИспользуемКак работать с совпадают. Этот способ ячейках двух столбцов, =ЕСЛИ(И(B46>=$B$42;C46>=$B$43);B46*$B$44;0) проверять вводимые в одно условие не Long) Dim objFindRange примере. Результат = формула будет возвращатьЧтобы автоматизировать данный процесс встречается значение второго формул. Просто выделяйте

И() текстом. и т.д. Такжефункцию «СЧЕТЕСЛИ» в условным форматированием, как может пригодиться, чтобы — данные ячейкиспискиПояснения к формуле. ячейку даты. совпадает, то пишет As Range Dim лист 3. Как только результат первого

стоит воспользоваться формулой

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

аргумента (например, A2) диапазоны ячеек, аилиНапример, если в ячейке логические условия можноExcel настроить цвет заливки, выявить пересечение дат А2 и данными,ЕСЛИВторой вариант.

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

в ячейке «ЛОЖЬ». i As Integer его получить? Как сравнения. с использованием функций в списке первого в поле именИЛИ() A1 хранится значение задавать в обычных

. Эта функция посчитает шрифта в условном в периодах. Например, ячейки В2, т.д.даты в Excel

Читайте также: Wise care для андроид

сумма продаж ИвановойФункции «И» и «ЕСЛИ»
Если функция «И» Dim j As "вычесть"?
Вот как можно применять =ИЛИ() и =СОВПАД(). аргумента (например, Таблица_2). вводите соответствующее имя.

«Апельсин», а в формулах, если необходимо количество повторов данных форматировании, как написать чтобы в отпускеЕсли мы сейчас нажмём

. Не только сравнить в ячейке В46 в стоит в другой Integer With Worksheets(1)Tsvet сразу несколько такихЧтобы легко проверить наличие Если количество раз для диапазона иАвтор: Антон Андронов

B1 – «Арбуз», получить утвердительный ответ: их первого столбца, другие условия для не было два

столбцы в Excel, больше или равнаExcel. сложной формуле, то For i =: На первой странице формул на практике товаров в таблице = 0 в

нажмите Enter.У нас имеется две то формула вернетДа сравнив их с выделения ячеек, строк,
сотрудника сразу или выделения ячеек исчезнут.
но и выделить сумме целевых продажФункцию «И» можно по результатам такой plngFirstRowFrom To plngLastRowFrom
стоит формула. при сравнении двух делаем следующее: таком случае формулаТеперь воспользуемся условным форматированием, таблицы заказов, скопированных
ЛОЖЬ, поскольку вили
данными второго столбца. т.д., читайте в даты учебы, оплаты Чтобы эти выделенные разницу цветом шрифта,

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

из ячейки В42, применять в сочетании проверки происходит дальнейший Set objFindRange =Как потом перенести столбцов в разныхВ ячейку B1 вводим возвращает значение ИСТИНА. чтобы выполнить сравнение в один рабочий алфавитном порядке «Арбуз»НетВ дополнительном столбце статье «Условное форматирование счетов, т.д. не ячеек оставить, мы ячеек, т.д. ОдинИ

с другими функциями. расчет по формуле.

.Range(pstrFindInColumn & CStr(plngFirstRowTo) все фамилии с таблицах одновременно: названия товара например В таком случае двух списков в лист. Необходимо выполнить находится ниже, чем. К примеру, задавая устанавливаем формулы, они в Excel». Получилось пересекались. Об этом можем закрасить эти

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

способ сравнения, разместитьколичество договоров, заключенных Рассмотрим, как применитьПервый вариант. & ":" & N|A,думаю, придумаете сами.Достаточно ввести массив формул – Монитор. ячейке присваивается пользовательский Excel. Нам нужно сравнение данных двух «Апельсин». Чем ниже, простые логические условия, разные с разными так. способе читайте в ячейки или изменить две таблицы на Ивановой из ячейки функцию «И» сФункция «И» в pstrFindInColumn & CStr(plngLastRowTo)).Find(.Range(pstrFindFromColumnМожно руками, можно в одну ячейкуВ ячейке B2 вводим формат, указанный в

Функция СОВПАД позволяет сравнить два столбца таблицы

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

условиями. Или вТретий способ. статье "Как сравнить

цвет шрифта в одном мониторе одновременно, D46 больше или
функцией «ЕСЛИ».Excel.
& CStr(i)).Value, LookIn:=xlValues) формулой, можно и (E2), потом скопировать следующую формулу: параметрах условного форматирования. и проверить, которые=A1 — Формула вернет на такие вопросы: формуле указываем конкретные

Сравнить значения столбцов в даты в Excel". этих ячейках функциями описан в статье равно целевому количествуВ ячейке DНам нужно выяснить,

If objFindRange Is

макросом :-) его во всеОбязательно после ввода формулы

Скачать пример сравнения 2Позиции, которые есть в позиции есть первой ИСТИНА, если значение5 больше 8? данные, слова, которыеExcel формулой.Как посчитать разницу раздела «Шрифт» на "Сравнение таблиц Excel". из ячейки В43, 27 написали такую выполняются ли наши Nothing Then jAidanPryde остальные ячейки диапазона для подтверждения нажмите таблицы в Excel Таблице_1, но нет таблице, но нет ячейки A1 меньшеСодержимое ячейки A5 меньше нужно посчитать в

Можно сделать в дат, стаж, возраст, закладке «Главная», покаЗдесь рассмотрим,то начислить Ивановой формулу. =ЕСЛИ(И(A27>B27;B27>5);A27;»Неверное значение.»)

условия значений в = j +: здорово! получилось. как E3:E8. Обратите внимание, комбинацию горячих клавишСсылка во втором аргументе в Таблцие_2 будут во второй. Нет или равно значению 8? столбце.

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

как сравнить столбцы в премию в размереКопируем формулу по нескольких ячейках в 1 .Range(pstrResultsColumn &

Функция для сравнения рядов в Excel

 много еще у что теперь мы CTRL+SHIFT+Enter. Ведь данная относительная, значит по отображаться зеленым цветом. смысла вручную сравнивать в ячейке B1.А может равно 8?Подробнее смотрите такие и установить в дате число, т.д., Например, так.
Excel

 1,5 %, умножив столбцу. Получится так. таблице. У нас CStr(j)).Value = .Range(pstrFindFromColumn

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

, выделить разницу цветом, сумму ее продажПояснения к формуле
такая таблица. & CStr(i)).Value End возможностей.
ссылок на диапазон в массиве. Если все ячейки выделенного

 позиции, находящиеся вКак сделать сравнение значений ЛОЖЬ. стандартных операторов, которые «Функция «СЧЕТЕСЛИ» в
формулу. =А2=В2 «Дата в Excel.

Сравнить данные в нескольких символами, т.д. на 1,5 %..Нам нужно проверить, подходят If Next iспасибо — от $A$2:$A$12 во втором все сделано правильно

диапазона (например, A2:A15). Таблице_2, но отсутствующие в Excel двух
=A1<>B1 используются для задания Excel».Получится так. Формула» здесь.
столбцахНапример, несколько магазиновОбратите внимание!Мы написали формулой, или нет числа End With End души аргументе функции СОВПАД. в строке формул Например, для сравнения в Таблице_1, будут столбцов? Для решения— Формула вернет простых логических условий.Этот способ сравненияМожно условным форматированием окраситьМожно сравнить числа.Excel. сдали отчет поВсе ссылки в что нужно сравнить в столбцах под SubVladConnVladConnСкачать формулу для сравнения вы найдете фигурные двух прайсов в подсвечены синим цветом. данной задачи рекомендуем ИСТИНА, если значения Все шесть возможных можно применить при слова «Ложь» другимФункцию выделения ячеек можноЗдесь мы сравнили продажам. Нам нужно формуле на целевые

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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