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

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

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

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

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

Перед началом работы добавьте «Сопоставить столбцы» в Excel

«Сопоставить столбцы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Начните работу с инструментами XLTools
– пробный период дает 14 дней полного доступа ко всем инструментам.

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

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

  1. Выберите столбцы для сравнения.

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

  3. Отметьте Столбцы содержат заголовки, если это так.

  4. Отметьте Показывать процент соответствия, чтобы степень соответствия отображалась в процентах.

    В противном случае результат отобразится как 1 (полное соответствие) или 0 (нет соответствия).

  5. Укажите, куда следует поместить результат: на новый или на существующий лист.

  6. Нажмите ОК Готово, результат представлен в сводной таблице.

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

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

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

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

  1. Выберите столбцы для сравнения.

    Напр., столбцы с данными прототипов.

  2. Нажмите кнопку Сопоставить столбцы на панели XLTools.

  3. Выберите Сопоставить с диапазоном эталонных столбцов Выберите столбцы эталонных значений.

    Напр., столбцы со стандартами.

  4. Отметьте Столбцы содержат заголовки, если это так.

  5. Отметьте Показывать процент соответствия, чтобы степень соответствия отображалась в процентах.

    В противном случае результат отобразится как 1 (полное соответствие) или 0 (нет соответствия).

  6. Укажите, куда следует поместить результат: на новый или на существующий лист.

  7. Нажмите ОК Готово, результат представлен в сводной таблице.

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

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

Когда надстройка «Сопоставить столбцы» наиболее полезна

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

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

  • Насколько схожи показатели экспериентальных групп
  • Насколько схожи результаты экспериментальных и контрольной групп
  • Насколько схожи/отличны несколько товаров одной категории
  • Насколько показатели KPI сотрудников близки к плановым показателям
  • Насколько схожи между собой показатели нескольких магазинов розничной сети, и т.д.
Any questions or suggestions?
How can we help?