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