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

Инструкция

Воспользуйтесь встроенной функцией сравнения ячеек СЧЁТЕСЛИ, если надо сравнить текстовые значения в ячейках колонки таблицы с образцом текста и пересчитать все совпадающие значения. Начните с заполнения колонки текстовыми значениями, а затем в другой колонке щелкните ячейку, в которой хотите видеть результат подсчета, и введите соответствующую формулу . Например, если проверяемые значения находятся в колонке A, а результат надо поместить в первую ячейку колонки C, то ее содержимое должно быть таким:=СЧЁТЕСЛИ($A:$A;"Виноград")Здесь «Виноград» - строковое значение, с которым сравниваются значения всех ячеек колонки A. Можно не указывать его в формуле , а поместить в отдельную ячейку (например - в B1) и вставить в формулу соответствующую ссылку:=СЧЁТЕСЛИ($A:$A;B1)

Используйте опции условного форматирования, если надо визуально выделить в таблице результат сравнения строковых переменных. Например, если надо выделить в колонке A ячейки, текст в которых совпадает с образцом в ячейке B1, то начните с выделения этой колонки - щелкните ее заголовок. Затем щелкните кнопку «Условное форматирование» в группе команд «Стили» закладки «Главная» меню Excel. Перейдите в раздел «Правила выделения ячеек» и выберите строку «Равно». В открывшемся окошке укажите ячейку-образец (щелкните клетку B1) и выберите в выпадающем списке вариант оформления совпадающих строк. Затем нажмите кнопку «OK».

Используйте комбинацию встроенных функций ЕСЛИ и СЦЕПИТЬ при необходимости сравнить с образцом не одну текстовую ячейку, а несколько. Функция СЦЕПИТЬ соединяет указанные ей значения в одну строковую переменную. Например, команда СЦЕПИТЬ(A1;" и ";B1) к строке из ячейки A1 добавит текст « и », а после него поместит строку из ячейки B1. Сконструированную таким способом строку затем можно сравнить с образцом с помощью функции ЕСЛИ. Когда сравнивать надо не одну строку удобнее дать собственное имя ячейке -образцу. Для этого щелкните ее и слева от строки формул вместо обозначения ячейки (например, C1) наберите ее новое имя (например, «образец»). Затем кликните ту ячейку, в которой должен быть результат сравнения, и введите формулу:ЕСЛИ(СЦЕПИТЬ(A1;" и ";B1)=образец;1;0)Здесь единица - это значение, которое будет содержать ячейка с формулой, если сравнение даст положительный результат, а ноль - для отрицательного результата. Размножить эту формулу на все строки таблицы, которые нужно сравнить с образцом очень просто - наведите курсор на правый нижний угол ячейки и, когда курсор изменится (станет черным крестиком), нажмите левую кнопку мыши и растяните эту ячейку вниз до последней сравниваемой строки .

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

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

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

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

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

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

Надстройка позволяет:

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

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

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

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

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

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

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

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

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

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

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

Сравнение столбцов является частным случаем сравнения произвольных диапазонов. В диапазонах №1 и №2 выделяем два столбца, причем выделять можно именно столбцы, а не протягивать мышью рамку выделения по диапазонам с ячейками (для удобства по умолчанию включена опция "Ограничить диапазоны", которая в случае выделения столбцов или строк целиком, ограничивает такие выделения используемым диапазоном), выбираем необходимое действие для поиска либо различий, либо совпадений, выбираем цвет заливки ячеек и запускаем программу. Ниже виден результат поиска совпадающих значений в двух столбцах.

Умение сравнивать два массива данных в 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, которые помогут вам решить некоторые аналитические задачи и упростят работу в поиске повтояющихся (или уникальных) значений.