Как сравнить два массива данных в excel

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

Допустим вы работаете с таблицей созданной сотрудником, который в неупорядоченный способ заполняет информацию, касающеюся объема продаж по определенным товарам. Одной из ваших задач будет – сравнение. Следует проверить содержит ли столбец таблицы конкретное значение или нет. Конечно можно воспользоваться инструментом: «ГЛАВНАЯ»-«Редактирование»-«Найти» (комбинация горячих клавиш CTRL+F). Однако при регулярной необходимости выполнения поиска по таблице данный способ оказывается весьма неудобным. Кроме этого данный инструмент не позволяет выполнять вычисления с найденным результатом. Каждому пользователю следует научиться автоматически решать задачи в Excel.

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

Чтобы автоматизировать данный процесс стоит воспользоваться формулой с использованием функций =ИЛИ() и =СОВПАД().

Чтобы легко проверить наличие товаров в таблице делаем следующее:

1. В ячейку B1 вводим названия товара например – Монитор.
2. В ячейке B2 вводим следующую формулу:
3. Обязательно после ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter. Ведь данная формула должна выполняться в массиве. Если все сделано правильно в строке формул вы найдете фигурные скобки.

В результате формула будет возвращать логическое значение ИСТИНА или ЛОЖЬ. В зависимости от того содержит ли таблица исходное значение или нет.

Разбор принципа действия формулы для сравнения двух столбцов разных таблиц:

Функция =СОВПАД() сравнивает (с учетом верхнего регистра), являются ли два значения идентичными или нет. Если да, возвращается логическое значение ИСТИНА. Учитывая тот факт что формула выполняется в массиве функция СОВПАД сравнивает значение в ячейке B1 с каждым значением во всех ячейках диапазона A5:A10. А благодаря функции =ИЛИ() формула возвращает по отдельности результат вычислений функции =СОВПАД(). Если не использовать функцию ИЛИ, тогда формула будет возвращать только результат первого сравнения.

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

Достаточно ввести массив формул в одну ячейку (E2), потом скопировать его во все остальные ячейки диапазона E3:E8. Обратите внимание, что теперь мы используем абсолютные адреса ссылок на диапазон $A$2:$A$12 во втором аргументе функции СОВПАД.

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как сравнить два массива данных в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Трюк №62. Как в Excel 2010 сравнить два диапазона данных

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

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

Для следующих примеров мы заранее скопировали новые данные на тот же лист, где находятся исходные. На рис. 6.1 показано, как данные выглядят в этих двух диапазонах. Обратите внимание, что для упрощения восприятия мы выделили ячейки таблицы 2, отличающиеся от соответствующих ячеек в таблице 1, жирным шрифтом.

Рис. 6.1. Диапазоны для сравнения

Способ 1. Использование значений Истина и Ложь

В первом способе нужно ввести простую формулу в другой диапазон того же размера и формы. Удобно то, что можно добавить формулу за один шаг, не копируя и не вставляя данные. Чтобы сравнить диапазоны, показанные на рис. 6.1, выделите диапазон E1:G7, начиная с ячейки Е1. Это гарантирует, что ячейка Е1 будет активной ячейкой выделения. Выделив диапазон, щелкните строку формул и введите следующую формулу: =А1=А9
[stextbox формулу, нажмите сочетание клавиш Ctrl+Enter. Так вы введете формулу с относительными ссылками в каждую ячейку выделенного диапазона. Это стандартный способ ввода формулы в массив ячеек с соответствующим изменением ссылок.[/stextbox]
Диапазон E1:G7 будет заполнен значениями Истина (True) — такое же значение, и Ложь (False) — значение отличается.

Если два набора данных находятся на разных рабочих листах, для хранения значений Истина/Ложь (True/False) можно использовать третий рабочий лист, введя формулу способом для массивов ячеек. Например, предполагая, что вторая таблица данных находится на листе Sheet2 и начинается с ячейки А9, а исходная таблица данных хранится на листе Sheetl и начинается с.ячейки А1, на третьем рабочем листе введите следующую формулу массива: =Sheet1!A1=Sheet2!A9. При работе с большими объемами данных бывает полезно уменьшить масштаб листа.
[stextbox удалить формулу, введенную способом для массивов, необходимо выделить и удалить весь диапазон. Часть его удалить невозможно.[/stextbox]

Способ 2. Условное форматирование

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

Снова предполагая, что мы сравниваем два предыдущих диапазона, выделите диапазон А1:С7, начиная с ячейки А1. Это гарантирует, что ячейка А1 будет активной ячейкой выделения. Выделив диапазон, выберите команду Формат → Условное форматирование (Format → Conditional Formatting). Выберите пункт Формула (Formula Is) и введите следующую формулу: =NOT(A1=A9), в русской версии Excel =НЕ(А1=А9). Щелкните кнопку Формат (Format) (рис. 6.2) и выберите форматирование, которым будут выделены отличающиеся данные.

Рис. 6.2. Диалоговое окно условного форматирования

Щелкните на кнопке ОК, и все различия будут отформатированы согласно выбранному вами формату. Если вы внесете какие-либо изменения в данные, то при одинаковом содержимом ячеек в обеих таблицах будет восстановлено обычное форматирование.

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

Опубликовано 29.04.2014 Автор Ренат Лотфуллин

Умение сравнивать два массива данных в 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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Sub Find_Matches()
Dim CompareRange As Variant , x As Variant , y As Variant
‘ Установка переменной CompareRangeравной сравниваемому диапазону
Set CompareRange = Range( «B1:B11» )
‘ Если сравниваемый диапазон находится на другом листе или книге,
‘ используйте следующий синтаксис
‘ Set CompareRange = Workbooks(«Книга2»). _
‘ Worksheets(«Лист2»).Range(«B1:B11»)
‘
‘ Сравнение каждого элемента в выделенном диапазоне с каждым элементом
‘ переменной CompareRange
For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, 2) = x
Next y
Next x
End Sub

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

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

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

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

С запуском версии 2013, Excel обзавелась интересной надстройкой под названием Inquire, которая позволяет сравнивать и анализировать два файла Excel. Если вам необходимо сравнить две версии одного и того же файла, например, после создания книги, ваши коллеги внесли кое-какие изменения, и вам нужно определить что именно они изменили, воспользуйтесь инструментом WorkbookCompare надстройки Inquire. Подробнее о том, как устанавливать, запускать и использовать надстройку читайте в моей предыдущей статье про надстройку Inquire.

Итог

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

Вам также могут быть интересны следующие статьи

• Что если отобразить скрытые строки в Excel не работает
• Печать таблицы в Excel — настройка области и границ печати Excel
• Формулы таблиц Excel
• Создание связи между таблицами Excel
• Функция СЖПРОБЕЛЫ в Excel с примерами использования
• Четыре способа использования ВПР с несколькими условиями
• Седьмой урок обучающего курса — Основы Excel — Управление несколькими рабочими листами
• Пятый урок курса по основам Excel — Печать в программе
• Шестой урок онлайн курса по основам Excel — Управление рабочим листом
• Четвертый урок курса по основам Excel — Изменение ячеек

Рубрика: Основы | Метки: Анализ, основы, Формулы | 53 комментария | Permalink

53 комментария

Спасибо, у вас очень понятно и красиво оформлено, глаз радует
для меня трудность- понять работу ПОИСКОЗ. Если не трудно сделайте пост с пояснениями по данной формуле.