Сравнить два массива в excel на совпадения. Как сравнить два столбца в Excel — методы сравнения данных Excel. В решении каких задач поможет надстройка «Сопоставить столбцы»

При сравнении нескольких сопоставимых объектов в Excel таблицах, данные часто организуют по столбцам, чтобы было удобно сравнивать характеристики этих объектов построчно. Например, модели автомобилей, телефоны, экспериментальные и контрольные группы, ряд магазинов торговой сети и др. При большом числе строк визуальный анализ не может быть достоверным. Функции ВПР, ИНДЕКС, ПОИСКПОЗ (VLOOKUP, INDEX, MATCH) удобны для сравнения данных по ячейкам и не дают общей картины. А как выяснить, насколько в целом столбцы схожи между собой? Идентичны ли столбцы?

Способ 2

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

Найти повторяющиеся значения в двух столбцах с помощью формулы

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

Вы можете изменить переменные для того, что вы используете. Примечание. Выберите «Каждая строка» в разделе «Основы». Затем выберите «Одинаковые значения» из раздела «Поиск». Наконец, вы можете выбрать один цвет фона или шрифта, чтобы выделить те же ячейки в разделе «Обработка результатов», как вам нужно.

Надстройка «Сопоставить столбцы» позволяет сопоставить столбцы и увидеть общую картину:

Сравнить два и более столбцов друг с другом
Сравнить столбцы с эталонными значениями
Вычислить точный процент соответствия
Представить результат в наглядной сводной таблице

Демо: быстро найти и выделить повторяющиеся значения в двух столбцах

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

У моих данных есть заголовки.
Выберите целые строки.

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

Язык видео: английский. Субтитры: русский, английский. (Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)

Добавить «Сопоставить столбцы» в Excel 2016, 2013, 2010, 2007

Подходит для: Microsoft Excel 2016 - 2007, desktop Office 365 (32-бит и 64-бит).

Как работать с надстройкой:

Как сравнить два столбца и более друг с другом и вычислить процент соответствия

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

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

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

Нажмите кнопку «Сопоставить столбцы» на панели XLTools > Выберите «Сопоставить столбцы между собой».
Нажмите ОК >

Совет:
Выберите сводную таблицу результата > Кликните по пиктограмме Экспресс-анализа > Примените «Цветовую шкалу».

Человеческий глаз часто сталкивается с трудностями, говоря о различиях между двумя числами и транспонированием чисел, это ошибка ввода данных номер один. Нам действительно нужно только увидеть, если он не найден. Другой способ сделать это - использовать пользовательский формат. Это принимает значение и изменяет его цвет шрифта в соответствии с цветом фона, что бы это ни было!

Консалтинговая карьера Мелиссы охватывает банковское дело, производство, телекоммуникации, энергетику и страхование, что позволяет ей предоставлять реальные примеры и приложения. Она позволила каждому из ученых-ракетологов брокерам по недвижимости поставить «продуктивную» обратно в офисное программное обеспечение для работы. Когда мы используем рабочие листы, нам иногда приходится сравнивать два столбца и находить в них повторяющиеся значения. Существуют ли быстрые методы для выполнения этой задачи?

Прочтение результата: прототипы Тип 1 и Тип 3 практически идентичны, показатель соответствия на 99% говорит о том, что 99% их параметров в строках совпадают. Тип 2 и Тип 4 схожи менее всего — их параметры совпадают только на 30%.

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

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

Использование формулы подстановки ВПР

Было бы неплохо, если бы мы могли просто выделить недостающее значение с помощью цветной маркировки. С условным форматированием на основе формулы, которая идет. Меню для создания нового правила форматирования открывается, когда мы вводим формулу. Затем мы нажимаем «Форматирование», чтобы установить цвета шрифта и фона.

Совет: чтобы было проще интерпретировать результат, примените к нему условное форматирование:
Выберите сводную таблицу результата > Кликните по пиктограмме Экспресс-анализа > Примените «Цветовую шкалу».

Прочтение результата: прототип Тип 2 на 99% соответствует Стандарту 2, т.е. 99% их параметров в строках совпадают. Продукт 5 ближе всего к Стандарту 3 - 96% их параметров идентичны. В то же время Продукт 4 далёк от соответствия какому-либо из трёх стандартов. Теперь можно сделать вывод, насколько каждый из прототипов отклоняется от целевых эталонных значений.

Чтобы сравнить ячейку с другой ячейкой

С помощью кисти формата мы переносим форматирование в остальную часть списка. Здесь результат до строки 26. Метод 2 выглядит лучше. Недопустимые значения выделены. Но что, если мы хотим подчеркнуть значения, которые одинаковы в обоих списках? Для этого варианта мы просто отменяем запрос формулы. Мы снова используем условное форматирование. Например, вы можете определить, являются ли входные данные истинными или если они найдены некорректными после сравнения.

Чтобы сравнить ячейку со всем списком

Для этого воспользуйтесь следующей матричной формулой.

В решении каких задач поможет надстройка «Сопоставить столбцы»

Надстройка построчно сканирует ячейки и вычисляет процент одинаковых значений в столбцах. XLTools «Сопоставить столбцы» не подходит для обычного сравнения значений в ячейках — она не предназначена для поиска дубликатов или уникальных значений.

Надстройка «Сопоставить столбцы» имеет другое назначение. Её главная задача — выяснить, насколько, в целом, наборы данных (столбцы) схожи или отличны. Надстройка помогает с анализом большого объёма данных, когда вам нужно посмотреть шире, на макро-уровне, напр. ответить на такие вопросы:

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

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

Использование условного оператора если

Начало галереи. Он контролирует поведение поиска. 1 или отсутствующее значение: найдите наибольшее значение, меньшее или равное критерию поиска. Это подходит только для чисел. Если вы ищете 40, а наибольшее значение таблицы - 38, то будет названа первая ячейка, содержащая это значение. Если несколько ячеек имеют это значение, вы можете найти только первый из них. 0 - Здесь критерий поиска и найденное значение должны точно совпадать. Если вы ищете не менее 40, вы можете сравнить этот тип сравнения. Это первая ссылка на число, которое имеет хотя бы искомое значение. Важно: значения в матрице поиска должны быть упорядочены в порядке убывания!

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

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

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

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Каждый должен принести по меньшей мере 35 коробок в лагерь. Тот, кто создает больше, получает бонус. Вопрос в том, какой сотрудник сделал 36 или более ящиков? Таким образом, критерием поиска является число 36. Проблема. Такой поиск всегда находит только один результат, даже если все требования удовлетворяют.

Здесь вы можете сравнить, присутствует ли значение или, по крайней мере, почти подходящее значение. К сожалению, упоминается только подходящее значение. Вот условие, значение по умолчанию, а затем изменение форматирования ячейки. Например, вы можете добавить красный фон ко всем ячейкам со значением, составляющим не менее 35. Некоторые условия уже указаны в условном форматировании. Чтобы применить его, выберите ячейки для сравнения с мышью. Затем нажмите «Условное форматирование» в верхней части меню.

Выборки, основанные на одном или нескольких условиях. Ряд функций Excel используют операторы сравнения. Например, СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. Эти функции осуществляют выборки на основе одного или нескольких условий (критериев). Проблема в том, что эти функции могут только складывать, подсчитывать количество, и находить среднее. А если вы хотите наложить условия на поиск, например, максимального значения или стандартного отклонения? В этих случаях, поскольку не существует встроенной функции, вы должны изобрести формулу массива. Нередко это связано с использованием оператора сравнения массивов. Первый пример в этой главе, показывает, как рассчитать минимальное значения при одном условии.

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

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

Воспользуемся функцией ЕСЛИ, чтобы выбрать элементы массива, отвечающие условию. На рис. 4.1 в левой таблице присутствуют столбец с названиями городов и столбец с временем. Требуется найти минимальное время для каждого города и поместить это значение в соответствующую ячейку правой таблицы. Условие для выборки – название города. Если вы используете функцию МИН, то сможете найти минимальное значение столбца В. Но как вы выберите только те числа, что относятся только к Окленду? И как вам скопировать формулы вниз по колонке? Поскольку в Excel нет встроенной функции МИНЕСЛИ, вам необходимо написать оригинальную формулу, совмещающую функции ЕСЛИ и МИН.

При вычислении реальных представлены числом 1, а ложные - числом. Это выглядит очень хорошо при преобразовании истинных и ложных в единицы и нули. Чтобы сделать это преобразование в числа, у нас есть два трюка. Мы умножаем равенство, поэтому мы вынуждаем значение стать числовым.

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

Рис. 4.1. Цель формулы: выбрать минимальное время для каждого города

Скачать заметку в формате или в формате

Как показано на рис. 4.2, вам следует начать ввод формулы в ячейку E3 с функции МИН. Но вы же не можете поместить в аргумент число1 все значения столбца B!? Вы хотите отобрать только те значения, которые относятся к Окленду.

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

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

Как показано на рис. 4.3, на следующем этапе введите функцию ЕСЛИ в качестве аргумента число1 для МИН. Вы вложили ЕСЛИ внутрь МИН.

Разместив курсор в месте введения аргумента лог_выражение функции ЕСЛИ (рис. 4.4), вы выделяете диапазон с названиями городов А3:А8, а затем нажимаете F4, чтобы сделать ссылки на ячейки абсолютными (подробнее см., например, ). Затем вы набираете сравнительный оператор – знак равенства. Наконец, вы выделите ячейку слева от формулы – D3, оставляя ссылку на нее относительной. Сформулированное условие позволит выбрать только Окленды при просмотре диапазона А3:А8.

Сравнение столбцов ячейки на ячейку

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

Сравнение столбцов с нечувствительным к регистру

Сравните несколько столбцов, содержимое которых одинаково для всех строк

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

Рис. 4.4. Создайте оператор массива в аргументе лог_выражение функции ЕСЛИ

Итак, вы создали оператор массива с помощью оператора сравнения. В любой момент обработки массива оператор массива является оператором сравнения, так что результатом его работы будет массив, состоящий из значений ИСТИНА и ЛОЖЬ. Чтобы убедиться в этом, выделите массив (для этого щелкните во всплывающей подсказке на аргумент лог_выражение ) и нажмите F9 (рис. 4.5). Обычно вы используете один аргумент лог_выражение, возвращающее либо ИСТИНУ, либо ЛОЖЬ; здесь же результирующий массив вернет несколько значений ИСТИНЫ и ЛЖИ, так что функция МИН выберет минимальное число только для тех городов, которые соответствуют значению ИСТИНА.

Рис. 4.5. Чтобы увидеть массив, состоящий из значений ИСТИНА и ЛОЖь, щелкните во всплывающей подсказке на аргумент лог_выражение и нажмите F9