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

Question

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

Мне нужно условно отформатировать всю строку (в пределах minitable) на основе значения ячейки в строке.

Например, для таблицы 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. Примените формат, который вы хотите, и закончите с помощью OK.
5. Выберите условно отформатированный диапазон в Таблице U и Копируйте.
6. Выберите целевой диапазон в Таблице T и нажмите Специальная вставка.
7. В категории «Вставить параметр» нажмите «Форматы», затем «ОК».

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

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

NB:

После того, как вы скопируете условный формат, проверьте Управление правилом для Таблицы T, теперь вы обнаружите, что Формула имеет вид =G4 = G4 . Здесь Excel оправдывает «Формат копирования», а также корректирует целевой диапазон данных.

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