Сопоставить 2 таблицы в excel. Как сравнить два столбца в Excel — методы сравнения данных Excel
Умение сравнивать два массива данных в Excel часто пригождается для людей, обрабатывающих большие объемы данных и работающих с огромными таблицами. Например, сравнение может быть использовано в , корректности занесения данных или внесение данных в таблицу в срок. В статье ниже описаны несколько приемов сравнения двух столбцов с данными в Excel.
Использование условного оператора ЕСЛИ
Метод использования условного оператора ЕСЛИ отличается тем, что для сравнения двух столбцов используется только необходимая для сравнения часть, а не весь массив целиком. Ниже описаны шаги по реализации данного метода:
Разместите оба столбца для сравнения в колонках A и B рабочего листа.
В ячейке С2 введите следующую формулу =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));"";A2) и протяните ее до ячейки С11. Данная формула последовательно просматривает наличие каждого элемента из столбца A в столбце B и возвращает значение элемента, если оно было найдено в столбце B.
Использование формулы подстановки ВПР
Принцип работы формулы аналогичен предыдущей методике, отличие заключается в , вместо ПОИСКПОЗ. Отличительной особенностью данного метода также является возможность сравнения двух горизонтальных массивов, используя формулу ГПР.
Чтобы сравнить два столбца с данными, находящимися в столбцах A и B(аналогично предыдущему способу), введите следующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.
Данная формула просматривает каждый элемент из основного массива в сравниваемом массиве и возвращает его значение, если оно было найдено в столбце B. В противном случае Excelвернет ошибку #Н/Д.
Использование макроса VBA
Использование макросов для сравнения двух столбцов позволяет унифицировать процесс и сократить время на подготовку данных. Решение о том, какой результат сравнения необходимо отобразить, полностью зависит от вашей фантазии и навыков владения макросами. Ниже представлена методика, опубликованная на официальном сайте Микрософт.
1 | Sub
Find_Matches() |
В данном коде переменной CompareRange присваивается диапазон со сравниваемым массивом. Затем запускается цикл, который просматривает каждый элемент в выделенном диапазоне и сравнивает его с каждым элементом сравниваемого диапазона. Если были найдены элементы с одинаковыми значениями, макрос заносит значение элемента в столбец С.
Чтобы использовать макрос, вернитесь на рабочий лист, выделите основной диапазон (в нашем случае, это ячейки A1:A11), нажмите сочетание клавиш Alt+F8. В появившемся диалоговом окне выберите макрос Find_ Matches и щелкните кнопку выполнить.
После выполнения макроса, результат должен быть следующим:
Использование надстройки Inquire
Итог
Итак, мы рассмотрели несколько способов сравнения данных в Excel, которые помогут вам решить некоторые аналитические задачи и упростят работу в поиске повтояющихся (или уникальных) значений.
Сегодня расскажу как искать совпадение в столбцах Excel. Разберем все тонкости на примерах.
Задача 1: Есть 6 текстов в 6 ячейках. Необходимо узнать, какие из них уникальные, а какие повторяются.
Использовать будем Условное форматирование .
- Выбираем ячейки, которые необходимо сравнить;
- Во вкладке Главная переходим "Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения";
Выскакивает новое окно и в таблице начинают подсвечиваться ячейки, которые повторяются.
В этом окне вы можете выбрать две настройки: подсвечивать Повторяющиеся или Уникальные ячейки , а также какую подсветку при этом использовать - граница, текст, цвет текста, фон или своя уникальная.
Рассмотрим еще пример. Необходимо сравнить два столбца в Excel на совпадения. Есть таблица, в которой также есть совпадения, но уже числовые.
Выбираем таблицу и заходим в Повторяющиеся значения . Все совпадения будут подсвечены.
Можно, например, найти совпадения в одном столбце. Для этого достаточно перед применением опции выделить только его.
Ну и как я говорил выше, из выпадающего списка вы можете выделять не только повторяющиеся ячейки, но и уникальные.
Искать таким образом можно буквы, слова, символы, тексты и т.д.
В этом уроке расскажу как закрепить шапку на каждой странице Excel. Это может понадобиться в том случае, если вы работаете с большими по размеру таблицами. Обычно при их распечатке выходят документы с большим количеством страниц и для того, чтобы было удобно с ними работать, необходимо сохранить шапку таблицы на каждом листе.
В этом уроке расскажу как сделать разбивку текста по столбцам в Excel. Данный урок подойдет вам в том случае, если вы хотите произвести разбивку текста из одного столбца на несколько. Сейчас приведу пример. Допустим, у вас есть ячейка "A", в которой находится имя, фамилия и отчество. Вам необходимо сделать так, чтобы в первой ячейке "A" была только фамилия, в ячейке "B" - имя, ну и в ячейке "C" отчество.
В этой статье расскажу как удалить дубликаты в Excel . Рассматривать будем самый простой штатный способ, который появился начиная с Excel 2007.
Часто стоит задача сравнить два списка элементов. Делать это вручную слишком утомительно, и к тому же нельзя исключать вероятность ошибок. Excel упрощает эту операцию. В данном совете описывается метод с применением условного форматирования.
На рис. 164.1 приведен пример двух многостолбцовых списков имен. Применение условного форматирования может сделать различия в списках очевидными. Эти примеры списков содержат текст, но рассматриваемый метод работает и с числовыми данными.
Первый список - А2:В31 , этот диапазон называется OldList . Второй список - D2:E31 , диапазон называется NewList . Диапазоны были названы с помощью команды Формулы Определенные имена Присвоить имя . Давать названия диапазонам необязательно, но это облегчает работу с ними.
Начнем с добавления условного форматирования к старому списку.
- Выделите ячейки диапазона OldList .
- Выберите .
- В окне Создание правила форматирования выберите пункт под названием Использовать формулу
- Введите эту формулу в поле окна (рис. 164.2): =СЧЁТЕСЛИ(NewList;A2)=0 .
- Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно. Лучше всего выбрать различные цвета заливки.
- Нажмите кнопку ОК.
Ячейки в диапазоне NewList используют подобную формулу условного форматирования.
- Выделите ячейки диапазона NewList .
- Выберите Главная Условное форматирование Создать правило для открытия диалогового окна Создание правила форматирования .
- В окне Создание правила форматирования выберите пункт Использовать формулу для определения форматируемых ячеек.
- Введите эту формулу в поле окна: =СЧЕТЕСЛИ(OldList;D2)=0 .
- Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно (другой цвет заливки).
- Нажмите кнопку ОК.
В результате имена, которые находятся в старом списке, но которых нет в новом, будут выделенными (рис. 164.3). Кроме того, имена в новом списке, которых нет в старом, также выделены, но другим цветом. Имена, появляющиеся в обоих списках, не выделены.
Обе формулы условного форматирования используют функцию СЧЁТЕСЛИ . Она рассчитывает, какое количество раз определенное значение появляется в диапазоне. Если формула возвращает 0, это означает, что элемент не входит в диапазон. Таким образом, условное форматирование берется за дело, и цвет фона ячейки меняется.
Информацию, оформленную в виде таблиц, гораздо удобнее анализировать и использовать в различных расчетах, но когда необходимо сравнить данные нескольких похожих таблиц, визуально все это сделать очень сложно. Подходящее программное обеспечение всегда может выручить в подобной ситуации, и далее мы рассмотрим, как сравнить две таблицы в Excel, используя для этого разные методы анализа.
Сравнить таблицы в Excel нажатием одной кнопки к сожалению, не получится, и мало того, возможно для сравнения придется еще и некоторым образом подготовить данные, а также написать формулу.
В зависимости от необходимого результата подбирается и способ сравнения данных из таблиц. Самый простой способ, это сравнение двух на первый взгляд одинаковых столбцов для выявления строк, в которых это отличие все же есть. Сравнивать таким образом можно как числовые значения, так и текст.
Сравним два столбца цифровых значений, в которых отличие имеется только в нескольких ячейках. Записав простую формулу в соседнем столбце, условие равенства двух ячеек «=B3=C3» , мы получим результат «ИСТИНА» , если содержимое ячеек одинаковое, и «ЛОЖ» , если содержимое ячеек отличается. Растянув формулу по всей высоте столбца сравниваемых значений очень легко будет найти отличающуюся ячейку.
Если нужно просто удостоверится в наличии или отсутствии отличий в столбцах, можно воспользоваться пунктом меню «Найти и выделить» , на вкладке «Главная» . Для этого нужно предварительно выделить сравниваемые столбцы, после чего выбирать уже необходимый пункт меню. В выпадающем списке необходимо выбрать «Выделить группу ячеек…» , а в появившемся окошке выбрать «отличия по строкам» .
Условное форматирование отличий в упорядоченных значениях
При желании можно применить к отличающимся ячейкам условное форматирование, делая заливку ячейки, меняя цвет текста и т.д. В этом случае нужно выбирать пункт «Условное форматирование»
, в выпадающем списке которого выбираем «Управление правилами»
.
В диспетчере правил выбираем пункт «Создать правило» , а в создании правил выбираем . Теперь мы можем задать формулу «=$B3<>$C3» для определения форматируемой ячейки, и задать для нее формат, нажав на кнопку «Формат» .
Теперь у нас имеется правило отбора ячеек, задано форматирование, и определен диапазон сравниваемый ячеек. После нажатия на кнопку «OK» , заданное нами правило будет применено.
Сравнение и форматирование отличий в неупорядоченных значениях
Сравнение таблиц Эксель не ограничивается сравнением упорядоченных значений. Иногда приходится сравнивать диапазоны перемешанных значений, в которых необходимо определить вхождение одного значения в диапазон других значений.
Например, у нас имеется набор значений, оформленный в виде двух столбцов, и еще один такой же набор значений. В первом наборе у нас имеются все значения от 1 до 20, а во втором некоторые значения отсутствуют и продублированы другими значениями. Наша задача выделить условным форматированием в первом наборе значения, которых нет во втором наборе.
Порядок действий следующий, выделяем первый набор данных, именуемый у нас «Столбец 1» , и в меню «Условное форматирование» выбираем пункт «Создать правило…» . В появившемся окошке выбираем , вписываем необходимую формулу «=СЧЁТЕСЛИ($C$3:$D$12;A3)=0» и выбираем способ форматирования.
В нашей формуле используется функция «СЧЁТЕСЛИ» , которая подсчитывает количество повторений значения из определенной ячейки «A3» в заданном диапазоне «$C$3:$D$12» , которым выступает наш второй столбец. В качестве сравниваемой ячейки необходимо указывать первую ячейку из диапазона значений, к которым будет применяться форматирование.
После применения созданного правила все ячейки с неповторяющимися значениями в другом наборе значений будут выделены указанным цветом.
Конечно же, есть и более сложные варианты сравнения двух таблиц в Excel, как например, сравнение цент товаров в новом и старом прайсах. Допустим, имеется две таблицы с ценами, и рядом с ценами в новой таблице нужно указать и старые цены для каждого товара, причем порядок товаров в списках не соблюдается.
Рядом с ценами в новой таблице в ячейке следующего столбца необходимо записать формулу, которая и будет производить выборку значений. В формуле мы будем применять функция «ВПР» , которая может вернуть значение из любого столбца в строке, в которой выполнилось условие поиска. Чтобы функция работала правильно, необходимо чтобы в столбце в каждой строке находились уникальные значения, по которым будет производится поиск. Если значения будут повторяться, учитываться будет только первое найденное.
Необходимая нам формула будет выглядеть следующим образом: «=ВПР(B18;$B$3:$C$10;2;ЛОЖЬ)» . Первое значение «B18» соответствует первой ячейке искомого наименования товара. Второе значение «$B$3:$C$10» означает постоянный адрес диапазона таблицы старого прайса, значения из которой нам понадобятся. Третье значение «2» означает номер столбца из выделенного диапазона, в ячейке которого мы и будем брать старую цену товара. И последнее значение «ЛОЖЬ» задает поиск только по точному совпадению значений. После протаскивания формулы по всему столбцу новой таблицы мы получим в этом столбце старые значения цен по каждой позиции, имеющейся в новой таблице. Напротив наименования последнего товара формула выводит сообщение ошибки «#Н/Д» , что свидетельствует об отсутствии данного наименования в старом прайсе.
Вариантов сравнения таблиц в Экселе может быть бесчисленное множество, причем некоторые из них можно провести только при помощи надстройки VBA.
Вопрос от пользователя
Здравствуйте!
У меня есть одна задачка, и уже третий день ломаю голову - не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут - скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете (прим. : фотка вырезана цензурой, все-таки личная информация) .
Заранее благодарю. Андрей, Москва.
Доброго дня всем!
То, что вы описали - относится к довольно популярным задачам, которые относительно просто и быстро решать с помощью Excel. Достаточно загнать в программу две ваши таблицы, и воспользоваться функцией ВПР . О ее работе ниже...
Пример работы с функцией ВПР
В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B - товар и цена) нет данных по столбцу B ; во второй - заполнены оба столбца (товар и цена). Теперь нужно проверить первые столбцы в обоих таблицах и автоматически, при найденном совпадении, скопировать цену в первую табличку. Вроде, задачка простая...
Как это сделать...
Ставим указатель мышки в ячейку B2 - то бишь в первую ячейки столбца, где у нас нет значения и пишем формулу:
=ВПР(A2 ;$E$1:$F$7 ;2 ;ЛОЖЬ )
A2 - значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);
$E$1:$F$7 - полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок "$" - он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;
2 - номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца - то значение можно было бы копировать из 2-го или 3-го столбца);
ЛОЖЬ - ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).
Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!
Чтобы цена была проставлена и для других наименований товара - просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.
После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали - будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!
Примечание : должен сказать, что функция ВПР достаточно требовательна к ресурсам компьютера. В некоторых случаях, при чрезмерно большом документе, чтобы сравнить таблицы может понадобиться довольно длительное время. В этих случаях, стоит рассмотреть либо другие формулы, либо совсем иные решения (каждый случай индивидуален).
На этом всё, удачи!