При сравнении нескольких сопоставимых объектов в Excel таблицах, данные часто организуют по столбцам, чтобы было удобно сравнивать характеристики этих объектов построчно. Например, модели автомобилей, телефоны, экспериментальные и контрольные группы, ряд магазинов торговой сети и др. При большом числе строк визуальный анализ не может быть достоверным. Функции ВПР, ИНДЕКС, ПОИСКПОЗ (VLOOKUP, INDEX, MATCH) удобны для сравнения данных по ячейкам и не дают общей картины. А как выяснить, насколько в целом столбцы схожи между собой? Идентичны ли столбцы?
Надстройка «Сопоставить столбцы» позволяет сопоставить столбцы и увидеть общую картину:
- Сравнить два и более столбцов друг с другом
- Сравнить столбцы с эталонными значениями
- Вычислить точный процент соответствия
- Представить результат в наглядной сводной таблице
Язык видео: английский. Субтитры: русский, английский. (Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)
Добавить «Сопоставить столбцы» в Excel 2016, 2013, 2010, 2007
Подходит для: Microsoft Excel 2016 - 2007, desktop Office 365 (32-бит и 64-бит).
Как работать с надстройкой:
Как сравнить два столбца и более друг с другом и вычислить процент соответствия
Рассмотрим пример разработки продукта. Предположим, вам необходимо сравнить между собой несколько готовых прототипов и выяснить, насколько они схожи, отличны и, возможно, даже идентичны.
- Нажмите кнопку «Сопоставить столбцы» на панели XLTools > Выберите «Сопоставить столбцы между собой».
- Нажмите ОК >
Совет:
Выберите сводную таблицу результата > Кликните по пиктограмме Экспресс-анализа > Примените «Цветовую шкалу».
Прочтение результата: прототипы Тип 1 и Тип 3 практически идентичны, показатель соответствия на 99% говорит о том, что 99% их параметров в строках совпадают. Тип 2 и Тип 4 схожи менее всего — их параметры совпадают только на 30%.
Как сравнить столбцы с эталонными значениями и вычислить степень соответствия
Рассмотрим пример разработки продукта. Предположим, вам необходимо сравнить несколько готовых прототипов с неким целевым стандартом, а также рассчитать степень соответствия прототипов этим стандартам.
- Выберите столбцы для сравнения.
Напр., столбцы с данными прототипов. - Нажмите кнопку «Сопоставить столбцы» на панели XLTools.
- Выберите «Сопоставить с диапазоном эталонных столбцов» > Выберите столбцы эталонных значений.
Напр., столбцы со стандартами. - Отметьте «Столбцы содержат заголовки», если это так.
- Отметьте «Показывать процент соответствия», чтобы степень соответствия отображалась в процентах.
В противном случае результат отобразится как 1 (полное соответствие) или 0 (нет соответствия). - Укажите, куда следует поместить результат: на новый или на существующий лист.
- Нажмите ОК > Готово, результат представлен в сводной таблице.
Совет:
чтобы было проще интерпретировать результат, примените к нему условное форматирование:
Выберите сводную таблицу результата > Кликните по пиктограмме Экспресс-анализа > Примените «Цветовую шкалу».
Прочтение результата: прототип Тип 2 на 99% соответствует Стандарту 2, т.е. 99% их параметров в строках совпадают. Продукт 5 ближе всего к Стандарту 3 - 96% их параметров идентичны. В то же время Продукт 4 далёк от соответствия какому-либо из трёх стандартов. Теперь можно сделать вывод, насколько каждый из прототипов отклоняется от целевых эталонных значений.
В решении каких задач поможет надстройка «Сопоставить столбцы»
Надстройка построчно сканирует ячейки и вычисляет процент одинаковых значений в столбцах. XLTools «Сопоставить столбцы» не подходит для обычного сравнения значений в ячейках — она не предназначена для поиска дубликатов или уникальных значений.
Надстройка «Сопоставить столбцы» имеет другое назначение. Её главная задача — выяснить, насколько, в целом, наборы данных (столбцы) схожи или отличны. Надстройка помогает с анализом большого объёма данных, когда вам нужно посмотреть шире, на макро-уровне, напр. ответить на такие вопросы:
- Насколько схожи показатели экспериентальных групп
- Насколько схожи результаты экспериментальных и контрольной групп
- Насколько схожи/отличны несколько товаров одной категории
- Насколько показатели KPI сотрудников близки к плановым показателям
- Насколько схожи между собой показатели нескольких магазинов розничной сети, и т.д.
Инструкция
Воспользуйтесь встроенной функцией сравнения ячеек СЧЁТЕСЛИ, если надо сравнить текстовые значения в ячейках колонки таблицы с образцом текста и пересчитать все совпадающие значения. Начните с заполнения колонки текстовыми значениями, а затем в другой колонке щелкните ячейку, в которой хотите видеть результат подсчета, и введите соответствующую формулу . Например, если проверяемые значения находятся в колонке A, а результат надо поместить в первую ячейку колонки C, то ее содержимое должно быть таким:=СЧЁТЕСЛИ($A:$A;"Виноград")Здесь «Виноград» - строковое значение, с которым сравниваются значения всех ячеек колонки A. Можно не указывать его в формуле , а поместить в отдельную ячейку (например - в B1) и вставить в формулу соответствующую ссылку:=СЧЁТЕСЛИ($A:$A;B1)
Используйте опции условного форматирования, если надо визуально выделить в таблице результат сравнения строковых переменных. Например, если надо выделить в колонке A ячейки, текст в которых совпадает с образцом в ячейке B1, то начните с выделения этой колонки - щелкните ее заголовок. Затем щелкните кнопку «Условное форматирование» в группе команд «Стили» закладки «Главная» меню Excel. Перейдите в раздел «Правила выделения ячеек» и выберите строку «Равно». В открывшемся окошке укажите ячейку-образец (щелкните клетку B1) и выберите в выпадающем списке вариант оформления совпадающих строк. Затем нажмите кнопку «OK».
Используйте комбинацию встроенных функций ЕСЛИ и СЦЕПИТЬ при необходимости сравнить с образцом не одну текстовую ячейку, а несколько. Функция СЦЕПИТЬ соединяет указанные ей значения в одну строковую переменную. Например, команда СЦЕПИТЬ(A1;" и ";B1) к строке из ячейки A1 добавит текст « и », а после него поместит строку из ячейки B1. Сконструированную таким способом строку затем можно сравнить с образцом с помощью функции ЕСЛИ. Когда сравнивать надо не одну строку удобнее дать собственное имя ячейке -образцу. Для этого щелкните ее и слева от строки формул вместо обозначения ячейки (например, C1) наберите ее новое имя (например, «образец»). Затем кликните ту ячейку, в которой должен быть результат сравнения, и введите формулу:ЕСЛИ(СЦЕПИТЬ(A1;" и ";B1)=образец;1;0)Здесь единица - это значение, которое будет содержать ячейка с формулой, если сравнение даст положительный результат, а ноль - для отрицательного результата. Размножить эту формулу на все строки таблицы, которые нужно сравнить с образцом очень просто - наведите курсор на правый нижний угол ячейки и, когда курсор изменится (станет черным крестиком), нажмите левую кнопку мыши и растяните эту ячейку вниз до последней сравниваемой строки .
Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.
Выборки, основанные на одном или нескольких условиях. Ряд функций Excel используют операторы сравнения. Например, СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. Эти функции осуществляют выборки на основе одного или нескольких условий (критериев). Проблема в том, что эти функции могут только складывать, подсчитывать количество, и находить среднее. А если вы хотите наложить условия на поиск, например, максимального значения или стандартного отклонения? В этих случаях, поскольку не существует встроенной функции, вы должны изобрести формулу массива. Нередко это связано с использованием оператора сравнения массивов. Первый пример в этой главе, показывает, как рассчитать минимальное значения при одном условии.
Воспользуемся функцией ЕСЛИ, чтобы выбрать элементы массива, отвечающие условию. На рис. 4.1 в левой таблице присутствуют столбец с названиями городов и столбец с временем. Требуется найти минимальное время для каждого города и поместить это значение в соответствующую ячейку правой таблицы. Условие для выборки – название города. Если вы используете функцию МИН, то сможете найти минимальное значение столбца В. Но как вы выберите только те числа, что относятся только к Окленду? И как вам скопировать формулы вниз по колонке? Поскольку в Excel нет встроенной функции МИНЕСЛИ, вам необходимо написать оригинальную формулу, совмещающую функции ЕСЛИ и МИН.
Рис. 4.1. Цель формулы: выбрать минимальное время для каждого города
Скачать заметку в формате или в формате
Как показано на рис. 4.2, вам следует начать ввод формулы в ячейку E3 с функции МИН. Но вы же не можете поместить в аргумент число1 все значения столбца B!? Вы хотите отобрать только те значения, которые относятся к Окленду.
Как показано на рис. 4.3, на следующем этапе введите функцию ЕСЛИ в качестве аргумента число1 для МИН. Вы вложили ЕСЛИ внутрь МИН.
Разместив курсор в месте введения аргумента лог_выражение функции ЕСЛИ (рис. 4.4), вы выделяете диапазон с названиями городов А3:А8, а затем нажимаете F4, чтобы сделать ссылки на ячейки абсолютными (подробнее см., например, ). Затем вы набираете сравнительный оператор – знак равенства. Наконец, вы выделите ячейку слева от формулы – D3, оставляя ссылку на нее относительной. Сформулированное условие позволит выбрать только Окленды при просмотре диапазона А3:А8.
Рис. 4.4. Создайте оператор массива в аргументе лог_выражение функции ЕСЛИ
Итак, вы создали оператор массива с помощью оператора сравнения. В любой момент обработки массива оператор массива является оператором сравнения, так что результатом его работы будет массив, состоящий из значений ИСТИНА и ЛОЖЬ. Чтобы убедиться в этом, выделите массив (для этого щелкните во всплывающей подсказке на аргумент лог_выражение ) и нажмите F9 (рис. 4.5). Обычно вы используете один аргумент лог_выражение, возвращающее либо ИСТИНУ, либо ЛОЖЬ; здесь же результирующий массив вернет несколько значений ИСТИНЫ и ЛЖИ, так что функция МИН выберет минимальное число только для тех городов, которые соответствуют значению ИСТИНА.
Рис. 4.5. Чтобы увидеть массив, состоящий из значений ИСТИНА и ЛОЖь, щелкните во всплывающей подсказке на аргумент лог_выражение и нажмите F9
У нас имеется две таблицы заказов, скопированных в один рабочий лист. Необходимо выполнить сравнение данных двух таблиц в Excel и проверить, которые позиции есть первой таблице, но нет во второй. Нет смысла вручную сравнивать значение каждой ячейки.
Сравнение двух столбцов на совпадения в Excel
Как сделать сравнение значений в Excel двух столбцов? Для решения данной задачи рекомендуем использовать условное форматирование, которое быстро выделить цветом позиции, находящиеся только в одном столбце. Рабочий лист с таблицами:
В первую очередь необходимо присвоить имена обоим таблицам. Благодаря этому легче понять, какие сравниваются диапазоны ячеек:
- Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя».
- В появившемся окне в поле «Имя:» введите значение – Таблица_1.
- Левой клавишей мышки сделайте щелчок по полю ввода «Диапазон:» и выделите диапазон: A2:A15. И нажмите ОК.
Для второго списка выполните те же действия только имя присвойте – Таблица_2. А диапазон укажите C2:C15 – соответственно.
Полезный совет! Имена диапазонов можно присваивать быстрее с помощью поля имен. Оно находится левее от строки формул. Просто выделяйте диапазоны ячеек, а в поле имен вводите соответствующее имя для диапазона и нажмите Enter.
Теперь воспользуемся условным форматированием, чтобы выполнить сравнение двух списков в Excel. Нам нужно получить следующий результат:
Позиции, которые есть в Таблице_1, но нет в Таблцие_2 будут отображаться зеленым цветом. В тоже время позиции, находящиеся в Таблице_2, но отсутствующие в Таблице_1, будут подсвечены синим цветом.
Принцип сравнения данных двух столбцов в Excel
При определении условий для форматирования ячеек столбцов мы использовали функцию СЧЕТЕСЛИ. В данном примере эта функция проверяет сколько раз встречается значение второго аргумента (например, A2) в списке первого аргумента (например, Таблица_2). Если количество раз = 0 в таком случае формула возвращает значение ИСТИНА. В таком случае ячейке присваивается пользовательский формат, указанный в параметрах условного форматирования. Ссылка во втором аргументе относительная, значит по очереди будут проверятся все ячейки выделенного диапазона (например, A2:A15). Вторая формула действует аналогично. Этот же принцип можно применять для разных подобных задач. Например, для сравнения двух прайсов в Excel даже
