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

Question

Я обнаружил, что формулы и диапазоны условного форматирования будут автоматически корректироваться при копировании, удалении или перемещении данных в электронной таблице. Хотя это хорошая идея, она имеет тенденцию ломать вещи для меня довольно странными способами.

Чтобы избежать этого, я попытался написать правила, которые применялись ко всей электронной таблице и отсеивали заголовки столбцов, чтобы выделить данные, которые я хотел проверить.

Пример: =AND(A$1="Check This Column For Blanks),ISBLANK(A1)) применяется к =$1:$1048576

Однако даже при том, что правило явно применялось ко всему листу, оно все равно автоматически корректировалось (и таким образом ломалось странным образом), как я работал на листе.

Как я могу избежать этого?

Answer 1

Когда мне нужен диапазон, который не должен изменяться ни при каких обстоятельствах, включая перемещение, вставку и удаление ячеек, я использовал именованный диапазон и функцию INDIRECT .

Например, если я хочу, чтобы диапазон всегда применялся к ячейкам A1:A50 , я определил именованный диапазон с помощью диспетчера имен:

В менеджере имен добавьте новый диапазон (нажмите «Создать»), а в поле «Относится к:» используйте функцию INDIRECT, чтобы указать необходимый диапазон ячеек, например =INDIRECT("A1:A50") или =INDIRECT("Sheet!A1:A50") . Поскольку диапазон технически является просто текстовым аргументом, никакое количество перестановок ячеек не заставит Excel обновить его.

Кроме того, это работает как минимум в Excel 2010 и Excel 2013. Хотя мой скриншот с 2013 года, я использовал эту точную технику в 2010 году в прошлом.

Предостережения

1. Имейте в виду, что эта неизменность также может сбить вас с толку. Например, если вы измените имя листа, именованный диапазон будет нарушен.

2. Я заметил незначительное снижение производительности при использовании этой стратегии на значительном количестве ячеек. Модель, которую я использую на работе, использует эту технику с именованными диапазонами, которые охватывают несколько тысяч разнородных диапазонов ячеек, и Excel обновляет немного вялый, когда я обновляю ячейки в этих диапазонах. Это может быть моим воображением, или это может быть тот факт, что Excel выполняет дополнительные вызовы функций для опосредованного.

Answer 2

Я обнаружил, что правила очень легко нарушить, но есть кое-что, что вы можете попробовать, но, кажется, не нарушаете никаких правил.

Вы можете изменить текст внутри ячеек. Если вам нужно добавить строку, добавьте свои данные в конец таблицы и пересортируйте ее. Если вам нужно удалить строку, удалите только текст / цифры, а затем пересортируйте таблицу.

Это работает для меня, когда у меня есть условное форматирование, которое применяется к столбцам, и я обычно устанавливаю форматирование для всего столбца, например. $ F:$ F. Он все равно должен работать, если вы форматируете для частичного диапазона, просто убедитесь, что после добавления / удаления и применения всех данных, которые вы хотите отформатировать, все еще в пределах ваших исходных параметров диапазона.

Это очень большое разочарование для меня.

Надеюсь, это поможет.

Answer 3

Я обнаружил , что с помощью INDIRECT функции и функции ROW в правиле Условного форматирования устраняет проблему Excel создавать новые правила и изменение диапазона.

Например, я хотел добавить строку между строками в моей электронной таблице регистра чековой книжки, когда месяц менялся с одной строки на другую. Итак, моя формула в правиле CF:

=MONTH(INDIRECT("C"&ROW()))<>MONTH(INDIRECT("C"&ROW()-1))

где столбец C в моей электронной таблице содержит дату. Мне не нужно было делать ничего особенного для диапазона (не нужно было определять имя диапазона и т.д.).

Поэтому в примере исходного плаката вместо "A1" или «A $ 1» в правиле CF используйте:

INDIRECT("A"&ROW())

Answer 4

Я столкнулся с очень похожей проблемой. Я сделал несколько макросов, чтобы добавить строки и скопировать формулы, а затем настроить столбцы и размеры строк, чтобы отформатировать внешний вид листа. Я обнаружил, что эта проблема возникает в одном из двух случаев.

1) Когда что-то из INSIDE "относится к" вырезается / вставляется за пределы этого диапазона.

2) Когда есть объединенные ячейки внутри "относится к", и любые строки или столбцы корректируются.

Во время выпуска объединенной ячейки появляется, что Excel должен все разложить, пересчитать его условное приложение, настроить все ячейки (добавить или удалить строки или что-то еще), а затем снова объединить их. Это невидимо для нас, но, похоже, именно так оно и применяется.

Мысль, которая может помочь нам найти решение по этому вопросу.

-T

Answer 5

Я не ТАК уверен, и часто сталкиваюсь с одной и той же проблемой.

Я бы сказал, что поле «Применить к» на панели условного форматирования (CF) ВСЕГДА будет работать динамически. Таким образом, он ВСЕГДА преобразует любые ссылки в формат =$A$1:$A$50 .

Это боль.

Answer 6

В формуле условного форматирования, используйте R1C1 обозначение с INDIRECT функцией:
Пример 1:

If same row column A = 1 ...

=IF($A1=1,1,0) becomes 
=IF(INDIRECT("RC1",FALSE)=1,1,0)

Пример 2:
If same row column A = 2 AND next row column A = 3 ...

=IF(AND(($A1=2),($A2=3)),1,0) becomes
=IF(AND((INDIRECT("RC1",FALSE)=2),(INDIRECT("R[1]C1",FALSE)=3)),1,0)

Answer 7

У меня есть простое решение для этого.

Просто переместитесь в ячейку, находящуюся в пределах диапазона (ячейку, которая не выходит за пределы диапазона), нажмите «Редактор формата», а затем вставьте весь столбец. Опять же, он покажет, где он сломался, вам просто нужно сделать «Форматировщик» для ячейки, которая нарушила диапазон. Теперь это тоже может показаться немного длинным, вы можете просто создать для этого простой макрос.

Answer 8

Если вариант использования заключается в создании электронной таблицы отчета, то это должно работать хорошо:

---

Вы можете обойти чрезмерную полезность Excel с помощью пары одноразовых шагов настройки, если вы также хотите вручную выполнить простое обновление окончательных данных после выполнения всего ввода данных.

Предполагая, что ваши данные расположены нормально (в строках):

1. Введите все свои данные в один лист. Для этого урока я назову лист Input . Не применяйте к нему условное форматирование. Не стесняйтесь перемещать значения к содержанию вашего сердца (например, удалить / вставить / копировать / вставить)
2. Создайте еще один пустой лист и назовите его как хотите (например, « Output). Вручную скопируйте глобальное форматирование, например ширину столбцов и формат заголовка (не содержимое заголовков, а только форматирование).
3. Замените $A$1 в следующей формуле расположением начала строки заголовка, затем скопируйте все столбцы и строки, которые вы хотите вывести: =IF(LEN(OFFSET(Input!$A$1,ROW()-1,COLUMN()-1))>0,OFFSET(Input!$A$1,ROW()-1,COLUMN()-1),"") _{(оператор IF(LEN(...)>0,...,"") необходим, поскольку Excel также помогает определить типы данных для вас и использует 0 для пустых ячеек в противном случае, и члены -1 приходят от смещения против порядковой семантики)}
4. Примените условное форматирование к Output листу.

Это копирует ваш лист данных Input лист Output без каких-либо ссылок на ячейки, которые Excel будет автоматически вырезать, чтобы вы могли с уверенностью определить свое условное форматирование на листе Output .

Вам просто нужно вручную расширить диапазон, к которому применяется формула на листе Output при изменении размера ваших Input данных.

_{Я предлагаю вам избегать слияния любых клеток.}

---

PS Этот вопрос задавался много раз (он также применяется в Google Sheets, который является клоном Excel), так что вы можете найти лучшее решение для своего варианта использования, просматривая их:

1. Фрагментация условного форматирования Excel
2. (этот) Как я могу предотвратить автоматическое изменение формул и диапазонов условного форматирования?
3. Сохранить условное форматирование на вырезанную пасту
4. Excel: как навсегда применить условное форматирование ко всей электронной таблице?
5. Почему Excel убивает мое условное форматирование?