Как в таблице Excel сравнить два диапазона данных при помощи условного форматирования
Рис. 164.1. Вы можете использовать условное форматирование, чтобы выделить различия в двух диапазонах
Первый список — А2:В31 , этот диапазон называется OldList. Второй список — D2:E31 , диапазон называется NewList. Диапазоны были названы с помощью команды Формулы ► Определенные имена ► Присвоить имя. Давать названия диапазонам необязательно, но это облегчает работу с ними.
Начнем с добавления условного форматирования к старому списку.
- Выделите ячейки диапазона OldList.
- Выберите Главная ► Условное форматирование ► Создать правило для открытия диалогового окна Создание правила форматирования.
- В окне Создание правила форматирования выберите пункт под названием Использовать формулу для определения форматируемых ячеек.
- Введите эту формулу в поле окна (рис. 164.2): =СЧЁТЕСЛИ(NewList;A2)=0 .
- Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно. Лучше всего выбрать различные цвета заливки.
- Нажмите кнопку ОК.
Ячейки в диапазоне NewList используют подобную формулу условного форматирования.
- Выделите ячейки диапазона NewList.
- Выберите Главная ► Условное форматирование ► Создать правило для открытия диалогового окна Создание правила форматирования.
- В окне Создание правила форматирования выберите пункт Использовать формулу для определения форматируемых ячеек.
- Введите эту формулу в поле окна: =СЧЕТЕСЛИ(OldList;D2)=0 .
- Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно (другой цвет заливки).
- Нажмите кнопку ОК.
В результате имена, которые находятся в старом списке, но которых нет в новом, будут выделенными (рис. 164.3). Кроме того, имена в новом списке, которых нет в старом, также выделены, но другим цветом. Имена, появляющиеся в обоих списках, не выделены.
Обе формулы условного форматирования используют функцию СЧЁТЕСЛИ. Она рассчитывает, какое количество раз определенное значение появляется в диапазоне. Если формула возвращает 0, это означает, что элемент не входит в диапазон. Таким образом, условное форматирование берется за дело, и цвет фона ячейки меняется.
Рис. 164.3. Условное форматирование приводит к тому, что различия в двух списках выделяются
Ссылка на ячейку в функции СЧЁТЕСЛИ всегда должна быть в верхней левой ячейке выбранного диапазона.
Расширенное сравнение двух колонок (списков) — Макросы и программы VBA — Excel — Каталог статей — Perfect Excel
Обратите внимание на три варианта в диалоговом окне Параметры объединения . По умолчанию выбран вариант 1. В некоторых случаях требуется добавить в параметры объединения дополнительные строки из одной таблицы. Так как вам нужно найти только совпадающие данные, оставьте для объединения значение 1. Закройте диалоговое окно Параметры объединения , нажав кнопку Отмена .
Какие предложения от excel ?
Как правило, в задачах подобного рода применяется функция ВПР.
Например формула может выглядеть следующим образом:
Логика следующая: берем позицию в Прайсе2 и ищем ее по Прайсу1, выводя значение.
Однако, этот вариант работает не для всех случаев: если в Прайсе2 нет позиции, которая была в Прайсе1, формула не работает —
Она повторяет предыдущую, но уже учитывает значение (количество товара) при поиске.
Но она также бесполезна, если позиция выбыла в Прайсе2:
Для небольшого удобства прайсы разнесены по разным листам одной таблицы, а сама итоговая таблица на третьем листе.
Здесь видно, что формула учитывает моменты, если в Прайсах пропадают или появляются позиции. В таблице они обозначены словом «Нет».
Формула работает. Но, помимо ужасающих размеров, она имеет одно «но», точнее два «но».
Чтобы все работало корректно, необходимо:
- правильно указать диапазоны из Прайсов (выделить их в Прайсах Ctrl+Shift+Enter и перенести в формулу);
- позиции товаров в финальной таблице должны идти с учетом всех выбывших и/или прибывших позиций в обоих Прайсах. Сама формула не будет искать эти позиции в Прайсах и в вставлять в итоговую. Она просто берет наименование в итоговой таблице и ищет его в Прайсах, записывая количество товара и/или его отсутствие.
Выберите сводную таблицу результата Кликните по пиктограмме Экспресс-анализа Примените «Цветовую шкалу».
Сравнить две таблицы excel / Хабр
Рассмотрим пример разработки продукта. Предположим, вам необходимо сравнить несколько готовых прототипов с неким целевым стандартом, а также рассчитать степень соответствия прототипов этим стандартам.
Как сравнить столбцы с эталонными значениями и вычислить степень соответствия
Рассмотрим пример разработки продукта. Предположим, вам необходимо сравнить несколько готовых прототипов с неким целевым стандартом, а также рассчитать степень соответствия прототипов этим стандартам.
Выберите Сопоставить с диапазоном эталонных столбцов Выберите столбцы эталонных значений.
Отметьте Показывать процент соответствия , чтобы степень соответствия отображалась в процентах.
В противном случае результат отобразится как 1 (полное соответствие) или 0 (нет соответствия).
Совет: чтобы было проще интерпретировать результат, примените к нему условное форматирование:
Выберите сводную таблицу результата Кликните по пиктограмме Экспресс-анализа Примените «Цветовую шкалу».
Прочтение результата: прототип Тип 2 на 99% соответствует Стандарту 2, т.е. 99% их параметров в строках совпадают. Продукт 5 ближе всего к Стандарту 3 – 96% их параметров идентичны. В то же время Продукт 4 далёк от соответствия какому-либо из трёх стандартов. Теперь можно сделать вывод, насколько каждый из прототипов отклоняется от целевых эталонных значений.
Сравнить Больше Двух Значений в Excel
Ссылка во втором аргументе относительная, значит по очереди будут проверятся все ячейки выделенного диапазона (например, A2:A15). Например, для сравнения двух прайсов в Excel даже на разных листах . Вторая формула действует аналогично. Этот же принцип можно применять для разных подобных задач.