Вопрос или проблема
Я ломаю голову над этим. Я не знаю, возможно ли то, что я пытаюсь сделать в Excel (версии 2021), без VAB.
С одной стороны, у меня есть таблица, называемая “темарио”: вставьте описание изображения здесь
С другой стороны, у меня есть календа́рь, не в формате таблицы. Каждый “3A-16” – это код предмета, ячейка является функцией FILTER.
вставьте описание изображения здесь
Наконец, у меня есть ячейки в колонке AF, которые я буду использовать в качестве вспомогательной колонки.
Я хочу отформатировать эти ячейки на основе следующей формулы:
=ISNUMBER(SEARCH(XLOOKUP([calendar_cell];temario![range calendar_cell];[range cells used for formatting]);[aux for conditional formatting]))
Например,
=ISNUMBER(SEARCH(XLOOKUP(I7;temario!$D$2:$D$178;temario!$G$2:$G$178);$AF$4))
Столбец G содержит текст, совпадающий с любым из значений в колонке AF. Я пробовал эту формулу без условного форматирования, и она работает идеально.
В чем же тогда проблема?
Когда я применяю условное форматирование, оно работает только в ячейке, которую я установил в первую очередь, например, в ячейке “I7”. Если я перетаскиваю его в ячейку J7, оно продолжает ссылаться на I7 в моей формуле.
Тогда я не знаю, как сделать так, чтобы моя формула поиска динамически изменялась на активную ячейку.
Заранее спасибо.
Ответ или решение
Условия форматирования в Excel: Динамическое применение формулы XLOOKUP
Ваша задача состоит в том, чтобы настроить условное форматирование на основе динамически изменяющейся формулы, использующей XLOOKUP и SEARCH. Основная проблема, с которой вы столкнулись, заключается в том, что формула в условном форматировании не обновляется для других ячеек, когда вы пытались ее перетянуть, и ссылка на ячейку остается фиксированной.
Почему это происходит?
В Excel все формулы, используемые в условиях форматирования, трактуются как абсолютные. Когда вы применяете условное форматирование к одной ячейке и пытаетесь перетянуть его на соседние, Excel не изменяет ссылки на них, если вы не используете относительные ссылки.
Решение проблемы: Правильные относительные ссылки
Для достижения желаемого результата необходимо обернуть вашу формулу в условном форматировании так, чтобы Excel понимал, что ссылки нужно менять в зависимости от текущей ячейки. Используем относительные ссылки, чтобы формула таким образом адаптировалась:
-
Измените формулы:
Вместо использования фиксированной ссылкиI7, используйте A1-ссылки, где I7 – это относительная позиция ячейки. Таким образом, ваша формула в условном форматировании должна выглядеть следующим образом:=ISNUMBER(SEARCH(XLOOKUP(A1; temario!$D$2:$D$178; temario!$G$2:$G$178); $AF$4))Здесь
A1будет относиться к ячейке, в которую вы применяете условное форматирование. -
Настройка условного форматирования:
- Выберите диапазон ячеек (например, I7:J10), к которому вы хотите применить условное форматирование.
- Перейдите в раздел "Условное форматирование" → "Создать правило" → "Использовать формулу для определения формата".
- Введите вашу изменённую формулу как описано выше.
-
Проверка и сохранение:
После настройки вы увидите, что форматирование применено ко всем ячейкам диапазона динамически в зависимости от значений, найденных в каждой из них.
Заключение
Использование относительных ссылок в вашей формуле XLOOKUP и настройка условного форматирования гарантирует, что оно будет автоматически обновляться для всех ячеек в выбранном диапазоне. Если у вас возникнут дополнительные вопросы или потребуется помощь в других аспектах Excel, не стесняйтесь обращаться за поддержкой. Мы рады помочь вам оптимизировать вашу работу!