В этом видео мы рассмотрим, как выделить примерные совпадения с помощью условного форматирования.
Здесь у нас есть простая справочная таблица, в которой показаны затраты на материалы для различной высоты и ширины. Формула в K8 использует функции ИНДЕКС и ПОИСКПОЗ для получения правильной стоимости на основе значений ширины и высоты, введенных в K6 и K7.
Обратите внимание, что поиск основан на приблизительном совпадении. Поскольку значения расположены в порядке возрастания, ПОИСКПОЗ проверяет значения, пока не будет достигнуто большее значение, а затем отступает и возвращает предыдущую позицию.
Давайте создадим правило условного форматирования, чтобы выделить совпадающую строку и столбец.
Как всегда с более сложным условным форматированием, я рекомендую вам сначала поработать с фиктивными формулами, а затем перенести рабочую формулу непосредственно в правило условного форматирования. Таким образом, вы можете использовать все инструменты Excel при отладке формулы, что сэкономит вам много времени.
Сначала я установлю формулу для ширины. Нам нужно вернуть TRUE для каждой ячейки в строке 7, где согласованная ширина равна 200.
Это означает, что мы начинаем нашу формулу с $ B5 =, и нам нужно заблокировать столбец.
= $ B5 =
Теперь мы не можем искать 275 в столбце ширины, потому что его там нет. Вместо этого нам нужно приблизительное совпадение, которое находит 200, точно так же, как наша формула поиска.
Самый простой способ сделать это - использовать функцию ПРОСМОТР. ПРОСМОТР автоматически выполняет приблизительное совпадение, и вместо возврата позиции, такой как ПОИСКПОЗ, ПРОСМОТР возвращает фактическое значение совпадения. Итак, мы можем написать:
$ B5 = ПРОСМОТР ($ K $ 6, $ B $ 6: $ B $ 12)
С нашей входной шириной для поискового значения и всей шириной в таблице для результирующего вектора.
Если я использую F9, вы увидите, что возвращается значение LOOKUP.
Теперь, когда я ввожу формулу в таблицу, мы получаем ИСТИНА для каждой ячейки в строке шириной 200.
Теперь нам нужно расширить формулу, чтобы она соответствовала столбцу высоты. Для этого я добавлю функцию ИЛИ, а затем вторую формулу для соответствия высоте.
Мы начнем формулу таким же образом, но на этот раз нам нужно заблокировать строку:
= 5 млрд долларов
Затем мы снова используем функцию ПРОСМОТР с высотой для поиска и всеми высотами в таблице в качестве вектора результатов.
= ИЛИ ($ B5 = ПРОСМОТР ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = ПРОСМОТР ($ K $ 7, $ C $ 5: $ H $ 5))
Когда я копирую формулу по таблице, мы получаем ИСТИНА для каждой ячейки в сопоставленном столбце и для каждой ячейки в сопоставленной строке - именно то, что нам нужно для условного форматирования.
Я могу просто точно скопировать формулу в левую верхнюю ячейку и создать новое правило.
Теперь, если я изменю ширину или высоту, выделение будет работать должным образом.
Наконец, если вы хотите выделить только само значение поиска, это простое изменение. Просто отредактируйте формулу и замените функцию ИЛИ на функцию И.
= И ($ B5 = ПРОСМОТР ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = ПРОСМОТР ($ K $ 7, $ C $ 5: $ H $ 5))
Курс
Условное форматированиеСвязанные ярлыки
Введите одни и те же данные в несколько ячеек Ctrl + Enter ⌃ + Return Отображение диалогового окна Специальная вставка Ctrl + Alt + V ⌘ + ⌃ + V Переключение абсолютных и относительных ссылок F4 ⌘ + T
