Сравнить два неодинаковых столбца excel. Как сравнить два столбца в Excel — методы сравнения данных Excel

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

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

Первый список — А2:В31 , этот диапазон называется OldList . Второй список — D2:E31 , диапазон называется NewList . Диапазоны были названы с помощью команды Формулы Определенные имена Присвоить имя . Давать названия диапазонам необязательно, но это облегчает работу с ними.

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

1. Выделите ячейки диапазона OldList .
2. Выберите .
3. В окне Создание правила форматирования выберите пункт под названием Использовать формулу
4. Введите эту формулу в поле окна (рис. 164.2): =СЧЁТЕСЛИ(NewList;A2)=0 .
5. Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно. Лучше всего выбрать различные цвета заливки.
6. Нажмите кнопку ОК.

Ячейки в диапазоне NewList используют подобную формулу условного форматирования.

1. Выделите ячейки диапазона NewList .
2. Выберите Главная Условное форматирование Создать правило для открытия диалогового окна Создание правила форматирования .
3. В окне Создание правила форматирования выберите пункт Использовать формулу для определения форматируемых ячеек.
4. Введите эту формулу в поле окна: =СЧЕТЕСЛИ(OldList;D2)=0 .
5. Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно (другой цвет заливки).
6. Нажмите кнопку ОК.

В результате имена, которые находятся в старом списке, но которых нет в новом, будут выделенными (рис. 164.3). Кроме того, имена в новом списке, которых нет в старом, также выделены, но другим цветом. Имена, появляющиеся в обоих списках, не выделены.

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

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

Надстройка для сравнения значений в двух диапазонах Excel

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

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. находить элементы диапазона №1, которых нет в диапазоне №2;

3. находить элементы диапазона №2, которых нет в диапазоне №1;

4. находить элементы диапазона №1, которые есть в диапазоне №2;

5. находить элементы диапазона №2, которые есть в диапазоне №1;

6. выбирать один из девяти цветов заливки для ячеек с искомыми значениями;

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

8. вместо сравнения числовых значений использовать сравнение текстовых значений при помощи опции «Сравнить числа как текст»;

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

10. сравнивать значения в ячейках диапазона, не учитывая регистр.

Как сравнить два столбца с использованием макроса (надстройки) для Excel?

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

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

Начинаем с функции «Совпад ». К примеру, сравниваемые данные находятся в столбцах, имеющих адреса С3 и В3. Результат же сравнения нужно поместить в клеточку, например, D3. Мы щелкаем мышкой на этой клеточке, входим в директорию меню «формулы», находим строчку «библиотека функций», раскрываем функции, помещенные в ниспадающий список, находим слово «текстовый» и щелкаем на «Совпад».

Через мгновение на дисплее вы увидите новую форму, где будут всего два поля: «текст один», «текст два». В них нужно забить, как раз, адреса сравниваемых столбцов (С3, В3), после щелкнуть на привычную клавишу «ОК». В итоге, вы увидите результат со словами «Истина»/«Ложь». В принципе, ничего особо сложного даже для начинающего юзера! Но это далеко не единственный метод. Давайте разберем функцию «Если».

Если требуется выполнить построчный анализ двух столбиков, в третий столбик помещаем любую функцию, о которых шла речь выше («если», либо «совпад»). Действие ее распространяем на всю высоту заполненных колонок. Далее, выделяем третий столбец, щелкаем вкладку «главная», ищем в появившейся группе слово «стили». Откроются «правила выделения столбцов/ячеек».

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

Эксель: условное форматирование

Форматирование условного типа позволит вам не просто сравнить два разных столбика/ячейки/строчки, а и выделить заданным цветом (красным) разные в них данные. То есть мы ищем не совпадения, а разности. Чтобы это получить, действуем так. Выделяем нужные столбики, не трогая их названий, заходим в директорию меню «главная», в ней отыскиваем подраздел «стили».

HLOOKUP и VLOOKUP

Значение, которое нужно отыскать, обозначаем, как «lookup_value». Колонки для поиска вбиваются, как «table array». Номер столбика следует указать, как «сol_index_num». Причем это тот столбец, значение которого совпало, и которое нужно вернуть/исправить. Команда «range lookup» здесь выступает, как добавочная. Она может указать, нужно значение сделать точным, либо приближенным.

Способ обработки значений-дубликатов

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

Далее мы выделяем эти ячейки, ведь нам нужно было не просто сравнить текст в двух столбцах в 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

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

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

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

После выполнения макроса, результат должен быть следующим:

Использование надстройки Inquire

Итак, мы рассмотрели несколько способов сравнения данных в Excel, которые помогут вам решить некоторые аналитические задачи и упростят работу в поиске повтояющихся (или уникальных) значений.

[expert_bq id=»1570″]Текстовые значения отсортированны в разном порядке, нужно чтобы в столбце С1 С20 указывались те значения которых нет в столбце А1 А20, но есть столбце В1 В20. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E1500:$E$7;0));»»;C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Функция для сравнения двух столбцов в excel. Как сравнить два столбца в Excel — методы сравнения данных Excel

1. Выделите ячейки диапазона OldList .
2. Выберите .
3. В окне Создание правила форматирования выберите пункт под названием Использовать формулу
4. Введите эту формулу в поле окна (рис. 164.2): =СЧЁТЕСЛИ(NewList;A2)=0 .
5. Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно. Лучше всего выбрать различные цвета заливки.
6. Нажмите кнопку ОК.

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

Сравнить листы в Excel на совпадения

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

Существует довольно много способов сравнения табличных областей в Excel, но все их можно разделить на три большие группы:

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

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

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

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

1. Для этого нам понадобится дополнительный столбец на листе. Вписываем туда знак «=». Затем кликаем по первому наименованию, которое нужно сравнить в первом списке. Опять ставим символ «=» с клавиатуры. Далее кликаем по первой ячейке колонки, которую мы сравниваем, во второй таблице. Получилось выражение следующего типа:

Хотя, конечно, в каждом конкретном случае координаты будут отличаться, но суть останется одинаковой.

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

Всего в качестве аргументов можно использовать адреса до 255 массивов. Но в нашем случае мы будем использовать всего два массива, к тому же, как один аргумент.

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

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

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

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

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

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

Кроме того, в нужное нам окно выделения группы ячеек можно попасть и другим способом. Данный вариант особенно будет полезен тем пользователям, у которых установлена версия программы ранее Excel 2007, так как метод через кнопку «Найти и выделить» эти приложения не поддерживают. Выделяем массивы, которые желаем сравнить, и жмем на клавишу F5.

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

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

Данное выражение мы и записываем в вышеуказанное поле. После этого щёлкаем по кнопке «Формат…».

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

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

Таким образом, будут выделены именно те показатели, которые не совпадают.

Способ 4: комплексная формула

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

Оператор СЧЁТЕСЛИ относится к статистической группе функций. Его задачей является подсчет количества ячеек, значения в которых удовлетворяют заданному условию. Синтаксис данного оператора имеет такой вид:

Аргумент «Диапазон» представляет собой адрес массива, в котором производится подсчет совпадающих значений.

Аргумент «Критерий» задает условие совпадения. В нашем случае он будет представлять собой координаты конкретных ячеек первой табличной области.

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

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

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

Затем переходим к полю «Критерий», установив туда курсор. Щелкаем по первому элементу с фамилиями в первом табличном диапазоне. В данном случае оставляем ссылку относительной. После того, как она отобразилась в поле, можно щелкать по кнопке «OK».

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

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

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

После этого переходим к полю «Значение если истина». Тут мы воспользуемся ещё одной вложенной функцией – СТРОКА. Вписываем слово «СТРОКА» без кавычек, далее открываем скобки и указываем координаты первой ячейки с фамилией во второй таблице, после чего закрываем скобки. Конкретно в нашем случае в поле «Значение если истина» получилось следующее выражение:

Теперь оператор СТРОКА будет сообщать функции ЕСЛИ номер строки, в которой расположена конкретная фамилия, и в случае, когда условие, заданное в первом поле, будет выполняться, функция ЕСЛИ будет выводить этот номер в ячейку. Жмем на кнопку «OK».

В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений», который мы ранее преобразовали с помощью функции ЕСЛИ. Делаем все ссылки абсолютными.

В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Щелкаем по кнопке «OK».

Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ. От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.

В поле «Массив» указываем адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть, ставим перед ними знак доллара уже ранее описанным нами способом.

Способ 5: сравнение массивов в разных книгах

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

[expert_bq id=»1570″]Данная формула последовательно просматривает наличие каждого элемента из столбца A в столбце B и возвращает значение элемента, если оно было найдено в столбце B. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Полезный совет! Имена диапазонов можно присваивать быстрее с помощью поля имен. Оно находится левее от строки формул. Просто выделяйте диапазоны ячеек, а в поле имен вводите соответствующее имя для диапазона и нажмите Enter.

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

1. Для этого нам понадобится дополнительный столбец на листе. Вписываем туда знак «=». Затем кликаем по первому наименованию, которое нужно сравнить в первом списке. Опять ставим символ «=» с клавиатуры. Далее кликаем по первой ячейке колонки, которую мы сравниваем, во второй таблице. Получилось выражение следующего типа:

Николай добрый вечер! подскажите пожалуйста! Сравниваю два списка,прайс,ассортимент с помощью варианта 2, выделил уникальные,редактировал нужный список,необходимо скопировать.но он вставляется в условном форматировании в цвете а как это все отменить чтобы нормально копировалось? спасибо! Ссылка