Как копировать условные формулы между разделами рабочего листа?

Question

У меня есть данные, размещенные в мини-таблицах на одном рабочем листе. Смотрите картинку

Мне нужно условно отформатировать всю строку (внутри мини-таблицы) на основе значения ячейки внутри строки.

Например, для таблицы U я бы выделил A4:E4 на основе значения B.

Проблема в том, что когда я копирую условное форматирование в таблицу T, оно все еще ссылается на столбец B, а не на столбец G, как мне хотелось бы. Мне приходится делать это много раз (более 50), поэтому исправление вручную займет неоправданно много времени (7 условных форматов на мини-таблицу, более 50 таблиц). Есть идеи?

Answer 1

Предполагая, что все ваши мини-таблицы имеют одинаковую ширину и между ними нет зазоров (или есть только фиксированный зазор), вы можете использовать единую формулу условного форматирования, подобную этой, примененную к ячейкам $A$4:$E$4:

=OFFSET($A4, 0, 5 * QUOTIENT(COLUMN(A4)-1, 5) + 1) = "TWL W/G"

Затем вы сможете использовать инструмент форматирования для копирования условного формата вниз по оставшейся части первой таблицы и по всем остальным мини-таблицам. Вам не понадобится никакого дальнейшего редактирования.

Это работает путем вычисления того, какая мини-таблица форматируется (из столбца оцениваемой ячейки), и, следовательно, с какой ячейкой следует сравнивать тестовую строку.

$A4— начальная точка смещения. Это абсолютная ссылка на столбец A, но имеет относительную ссылку на строку, поэтому при копировании формата в другие строки они будут отформатированы отдельно.

Эта QUOTIENT(COLUMN(A4)-1, 5)часть вычисляет, в какой таблице находится оцениваемая ячейка, предполагая, что каждая таблица имеет ширину в 5 столбцов. A4— это полностью относительная ссылка, поэтому она заменяется для каждой ячейки, в которую вы копируете формат.

Часть 5 * QUOTIENT() + 1выбирает 2-й столбец правильной таблицы (предполагая, что все они имеют ширину в 5 столбцов). Измените 5, если ваши таблицы используют другое количество столбцов, или , + 1если вам нужно протестировать другой столбец в таблице.

Answer 2

Следуй этим шагам:

Первый подход:

1. Выберите необходимый диапазон данных в таблице U.
2. Нажмите Условное форматирование, Новое правило, Использовать формулу, чтобы определить, какую ячейку следует форматировать.
3. В поле «Редактировать описание правила» напишите эту формулу=B4 = B4.
4. Примените желаемый формат и нажмите «ОК».
5. Выберите диапазон условного форматирования в таблице U и скопируйте.
6. Выберите целевой диапазон в таблице T и нажмите «Специальная вставка».
7. В категории «Параметры вставки» нажмите «Форматы», затем «ОК».

Второй подход:

1. Скопируйте ячейку из таблицы U в пустую ячейку.
2. Выберите скопированную ячейку, нажмите «Условное форматирование», выберите параметр «Управление правилами».
3. В поле «Показать правила форматирования для» выберите «Этот рабочий лист».
4. Для правила в таблице U измените диапазон данных «Применяется для соответствия».
5. Выберите диапазон данных в таблице T, чтобы применить формат.
6. Нажмите ОК.

Примечание:

После копирования условного формата проверьте Manage Rule для Table T, теперь вы видите, что Formula — =G4 = G4. Здесь Excel выравнивает «Копировать формат», а также корректирует целевой диапазон данных.

Но помните, что при использовании этого метода Excel также отформатирует те строки, которые не соответствуют значению B4.