Excel Условная фромация: Я не могу сохранить его в диапазоне "$A:$A", как это происходит?
Это хорошо известная проблема с Excel, на которую жаловались десятилетиями
Вы можете добавить свой голос к оттенку и поплакать, ну, похоже, они сбрасывают все старые жалобы и делают что-то, черт возьми, новое ... Вы можете прочитать это для (некоторых маленьких) подробностей:https://support.microsoft.com/en-us/office/uservoice-pages-430e1a78-e016-472a-a10f-dc2a3df3450a
Итак, что вы можете сделать, чтобы улучшить ситуацию после кричащей части?
(Имейте в виду, что вы НЕ МОЖЕТЕ это исправить, поэтому вы можете оказать только паллиативную помощь себе и пользователям.)
Хорошим решением будет написать макрос VBA, который выполняет работу функции условного форматирования (CF). Это выполнимо, хотя я читал, что ТЯЖЕЛО, ОЧЕНЬ ТЯЖЕЛО писать условное форматирование в VBA, и его нелегко поддерживать. Но это абсолютно РЕШИТ эту проблему для вас, если вы готовы к этому или найдете кого-то, кто готов. Я не такой человек, поэтому я перейду к тому, что я делаю.
Проблема в том, что эти диапазоны, которые использует CF, становятся «балканизированными» и обычно довольно быстро. Указание пользователям не вырезать и вставлять, не перетаскивать, не вставлять строки, не... ну, вы поняли... ваши пользователи. Рано или поздно, даже с самыми лучшими намерениями, человек будет делать все, что угодно, потому что его развитое «принятие» того, чему его учили, состоит в том, что он может делать это, просто не сохраняя результаты. И тогда он спас, не подумав достаточно быстро. Или он был на вершине этого, но не понимал, как его действия разрушили логику в электронной таблице, и что, хотя он делал то же самое раньше, он никогда не рассчитывал на биты, которые принимали удар, но на этот раз. .. на этот раз это имело значение и обойдется боссу в пару тысяч долларов или потеряет клиента.
И будьте честны (что также означает «реалистично»), вы сами сделали именно это. Вы редактировали электронную таблицу, выполняли некоторую экспериментальную работу, которую НЕ собирались оставлять, не беспокоились о том, как она работает, поскольку вам нужно было только посмотреть, может ли она делать то, что было, затем вы закрывали, снова открывали и делайте это по-настоящему... но сэкономьте... Тренировки - это не решение проблемы. Это помощь, и если люди правы, это БОЛЬШАЯ помощь, но она не покрывает всего и не делает ничего, чтобы исправить несчастные вещи, которые происходят.
Итак, что я делаю, так это:
Для любого диапазона, который я намереваюсь использовать в CF, я создаю для него именованный диапазон. Итак, А1:А10? Называется "Лошадка".
Сделайте диапазон более инклюзивным. Это должно быть A1:A10? Сделайте его A1:A11. Когда люди вставляют строку под строкой 10, они фактически увеличивают размер диапазона до A1:A12, потому что строка 11 была ВНУТРИ используемого диапазона, а Excel обрабатывал вставку так, как вам хотелось бы.
Когда я настраиваю электронную таблицу, использующую CF, я сразу после завершения основной работы использую
Macro Recorder, чтобы настроить макрос, записывающий, как я перехожу к каждому правилу и редактирую диапазон, к которому оно применяется, чтобы он был именованным диапазоном, который я определил. для этого.
Итак, скажем, это должен был быть A34:A23008. Я делаю все возможное, чтобы расположить вещи так, чтобы я мог использовать строку выше и строку ниже, поэтому A33: A23009. Если я не могу, то я не могу, но я стараюсь. В любом случае я определяю для него именованный диапазон, что бы в итоге у меня не получилось. В этом простом примере только одно правило, поэтому нужно беспокоиться только об одном диапазоне, затем я запускаю Macro Recorder и вызываю правило CF (убедившись, что я изменил селектор «Показать правила для» с «Текущий выбор» на «Рабочий лист — легко сделать, так как он открывается с выбранным, и мне просто нужно нажать Down Arrow), введите =Horsey (или любое другое имя) в поле Applies To : (удалив все, что там есть), сохраните правило и выйдите из Macro Recorder.
Итак, что это дало?
У меня есть бесплатный макрос. Excel сделал всю работу за меня. И поскольку это не макрос, для которого я выделил некоторые строки, НИ ОДИН из них не требует редактирования, чтобы обобщить его. Это готовый продукт.
Во-вторых, если я зайду в CF, Manage Rules и посмотрю на поле
Applies to, я увижу красивый, правильный диапазон.
Поэтому, если я запущу его снова после того, как люди что-то сделают, я снова получу диапазон, определенный в именованном диапазоне, удалив все странные вещи. Ура!
Хотя вы, вероятно, думаете... * "Гм, тут по крайней мере две проблемы, мужик... Во-первых, когда я прочитал то, что вы написали, вы сказали, что видите определенный диапазон, а не Именованный диапазон. Звучит подозрительно, уловка язык вроде языка ласки.Вторая мысль: что насчет изменений, чувак?Если они добавили строку, мне не нужен исходный диапазон, я хочу диапазон, который включает ту строку, которую они вставили, так что... что насчет этого? И теперь я смотрю на поле «Применимо к», в нем вообще нет именованного диапазона, вы ПЫТАЛИСЬ обмануть меня!»
Нисколько. Во-первых, вторая забота. Все действия пользователей, которые разрушают ваш приятный Applies to, НЕ вредят определенному именованному диапазону. Вставить строку, а затем удалить три несмежных строки? Excel корректирует определение именованного диапазона, чтобы включить все эти действия. Поэтому, когда они вставляли строку, именованный диапазон увеличивался на эту строку. Когда вы используете его в поле Applies to правила CF, он применяет скорректированный диапазон, а не исходный диапазон.
Во-вторых, это означает, что первая забота — это только хлопоты, а не проблемы. Когда я запускаю макрос, он перезаписывается именованным диапазоном (скорректированным Excel по мере необходимости). Затем функциональность CF немедленно (сразу после сохранения) изменяет его с фактического имени именованного диапазона на диапазон, на который он ссылается. Так что да, это не остается Именованным диапазоном. Он ДЕЙСТВИТЕЛЬНО меняется на буквальный диапазон. Но обратите внимание, что это ПРАВИЛЬНЫЙ литеральный диапазон: он был сохранен в Excel с помощью функции именованного диапазона, и в тот момент, когда CF меняет его на буквальный диапазон, он восстанавливает его из грязного, неправильного и откровенно пугающего обратно к простому, ясному. диапазон, в котором должны быть указаны изменения, внесенные пользователями.
И тогда они, вероятно, сразу же снова начнут все портить, но это будет рассмотрено через мгновение.
Итак, теперь у меня есть макрос (записанный для меня, не кропотливо и плохо написанный), который делает это всякий раз, когда я его запускаю. Если вы хотите использовать немного более интересный фрагмент VBA, вы можете легко и быстро узнать, как добавить пару строк в макрос, чтобы он запускался каждый раз при открытии файла (восстанавливая вещи непосредственно перед использованием), прямо перед его сохранением. (то же самое), или оба. Следующий шаг в хитрости — добавление кнопки, которая запускает макрос каждый раз, когда пользователь считает, что должен это сделать. Если у вас уже есть навыки или вы хотите научиться, вы даже можете перейти к настройке поиска различных событий, таких как вставка или удаление строк, ячеек и т. д., и вам не нужно выяснять, что делать для каждого из них. пять, затем 12, затем, в конце концов, 15, 21, 30 различных событий, которые вы испытываете, но, скорее, какое бы событие в списке ни произошло, просто запустите макрос прямо сейчас, чтобы эффект на ваши правила CF был зафиксирован в ту же секунду.
Итак, пользователь вставляет строку в интересующий диапазон? В тот момент, когда вставка завершается и Excel фиксирует диапазон в именованном диапазоне, ваш макрос срабатывает и повторно применяет именованные диапазоны к каждому правилу, поэтому правила по-прежнему работают должным образом.
Таким образом, речь идет о том, чтобы чего-то хотеть (бесплатный макрос), о медленной ходьбе (кнопка, которую пользователи могут нажать в любое время, когда они думают, что должны), о ходьбе немного быстрее (макрос срабатывает при открытии или сохранении, или и о том, и о другом), о заметно быстрой ходьбе. (автоматическое срабатывание при возникновении определенных событий), к чему-то близкому к мощной ходьбе (добавление 10-20 событий с течением времени к этому биту автоматического срабатывания).
Я больше не использую CF, но это избавило меня от тонны работы, а пару других — от беспокойства о том, что они получают неверные выводы из данных, которые я им предоставлял на протяжении многих лет.
Однако не всем разрешено использовать макросы в своих электронных таблицах. И это тут же убьет весь подход, а? Тем не менее, стоит поспорить, если CF важен для вас, и ваши пользователи не слишком заинтересованы в том, как это их вина, а не ваша, поэтому вы должны быть огнем, а не они. Или вы не должны получать прибавку к зарплате, потому что ваши электронные таблицы едва ли можно использовать... Для меня это были боссы, которые только на словах отдавали должное моим увещеваниям, но, по сути, регулярно топтали все вокруг, потому что, ну, они были боссами. Так или иначе я должен был сделать лучшую электронную таблицу.
Итак, я надеюсь, что вы можете использовать его. Кстати, помните, что вы записываете макрос, выполняя каждый из них за один раз, чтобы закончить прогрессию. Не 25 отдельных макросов. Но если вы сделаете это или добавите несколько правил, поэтому вам нужно добавить некоторые макросы, вы можете копировать и вставлять части вместе. Вы записали макрос для 13 правил CF и добавили три новых правила? Запустите рекордер, чтобы сделать те же обновления для трех новых правил CF, затем перейдите в VBA и скопируйте строки, которые выполняют в нем работу, перейдите к текущему макросу и вставьте их. Удалите новый макрос (тот, который вы записали). для трех новых правил CF НЕ ОБНОВЛЕННЫЙ (в отличие от «нового») макрос, в котором есть все.
VBA не заботится о порядке обновления правил, и нет никакого замедления от того, что он делает 20 изменений «волей-неволей», а не в каком-то тщательно выбранном «логическом» порядке. Так что просто вставка их в конце прекрасно работает.
Если вам вообще разрешено использовать макросы.