Общая формула
=ИЛИ($B5=ПРОСМОТР(ширина;ширины);B$5=ПРОСМОТР(высота;высоты))
=OR($B5=LOOKUP(width,widths),B$5=LOOKUP(height,heights))
Объяснение
Чтобы выделить строки и столбцы, связанные с приблизительным соответствием, вы можете использовать условное форматирование с формулой, основанной на функции ПРОСМОТР, вместе с логической функцией, такой как ИЛИ или И.
В показанном примере формула, используемая для применения условного форматирования:
=ИЛИ($B5=ПРОСМОТР(ширина;ширины);B$5=ПРОСМОТР(высота;высоты))
=OR($B5=LOOKUP(width,widths),B$5=LOOKUP(height,heights))
Как работает формула
Эта формула использует 4 именованных диапазона, определяемых следующим образом:
- ширина=K6
- высота=K7
- ширины=B6:B11
- высоты=C5:H5
Условное форматирование вычисляется относительно каждой ячейки, к которой она применяется, начиная с активной ячейки в выборе, которая является ячейка B5 в этом случае.
Чтобы выделить подходящую строку, мы используем это логическое выражение:
$B5=ПРОСМОТР(ширина;ширины)
$B5=LOOKUP(width,widths)
Ссылка на B5 смешанная с фиксированным столбцом и свободной строки, так что только значения в столбце B (ширины) сравниваются со значением в K6 (ширина). В показанном примере это логическое выражение вернет ИСТИНА для каждой ячейки в строке, где ширина равна 200, на основе приблизительного соответствия значения в K6 (ширина 275) для всех значений в K6:B11 (ширины). Это выполняется с помощью функции ПРОСМОТР:
ПРОСМОТР(ширина;ширины)
LOOKUP(width,widths)
Подобно функции ПОИСКПОЗ, ПРОСМОТР будет работать с отсортированными значениями до тех пор, пока не будет найдено большее значение, а затем вернется к предыдущему значению, которое в этом случае равно 200.
Чтобы выделить соответствующий столбец, мы используем это логическое выражение:
B$5=ПРОСМОТР(ширина;ширины)
B$5=LOOKUP(height,heights)
Ссылка на B5 смешанная с относительным столбцом и абсолютной строкой, так что только значения в строке 5 (высоты) сравниваются со значением в K7 (высота). В показанном примере это логическое выражение вернет ИСТИНА для каждой ячейки в строке, где высота равна 300, на основе приблизительного соответствия значения в K7 (высота, 325) для всех значений в C5: H5 (высоты). Это выполняется с помощью функции ПРОСМОТР:
ПРОСМОТР(ширина;ширины)
LOOKUP(height,heights)
Как и выше, ПРОСМОТР будет работать с отсортированными значениями до тех пор, пока не будет найдено большее значение, а затем вернется к предыдущему значению, которое в этом случае равно 300.
Выделить только пересечение
Чтобы выделить только пересечение, просто замените функцию ИЛИ функцией И:
=И($B5=ПРОСМОТР(ширина;ширины);B$5=ПРОСМОТР(высота;высоты))
=AND($B5=LOOKUP(width,widths),B$5=LOOKUP(height,heights))
