(Excel) Условное форматирование на основе значения смежной ячейки

Я пытаюсь применить условное форматирование в Excel к диапазону ячеек на основе значения соседней ячейки, чтобы добиться чего-то вроде этого:

Цель состоит в том, чтобы выделить значения в столбце B (Фактические расходы) красным цветом, если значение больше, чем соседнее значение в столбце C ( Ожидаемый расход). Я следил за рядом руководств, в которых говорилось о применении условного форматирования, выбрав Условное форматирование> Новые правила> Использовать формулу для определения, какие ячейки форматировать , а затем применив правило = $ B4> $ C4 который отформатировал бы «Фактическую» ячейку красным, если бы она была больше, чем «Ожидаемая» ячейка. Это отлично работает для одной ячейки, но не так хорошо, когда я пытаюсь применить это к диапазону ячеек. Я использовал средство рисования формата, а также редактировал правило, применяемое к диапазону ячеек (например, $ B $ 2: $ B $ 5), как показано ниже, но он основывает форматирование только на исходной формуле (если B4 больше, чем C4) и все становится нежелательно красным.

Я бы предпочел не устанавливать отдельные правила для каждой строки. Есть ли способ объединить это в одно правило?


Вам нужно убрать знаки $ перед номерами строк в формуле … и номером строки, используемым в формула должна соответствовать первой строке данных, поэтому, если вы применяете ее к диапазону («применяется к») $ B $ 2: $ B $ 5, это должна быть эта формула

= $ B2> $ C2

, используя эту «относительную» версию, а не вашу «абсолютную». Excel (неявно) корректирует формула для каждой строки в диапазоне, как если бы вы копировали формулу вниз


Я не знаю если, возможно, это разница в версии Excel, но этому вопросу 6 лет, и принятый ответ мне не помог, поэтому я понял следующее:

В разделе «Условное форматирование»> «Правила управления»:

  1. Создайте новое правило с помощью «Используйте формулу, чтобы определить, какие ячейки форматировать»
  2. Создайте свое правило, но поставьте знак доллара только перед буква: $A2
  3. Под «Ap plies to «, выберите второй столбец вручную (у меня не сработало бы, если бы я изменил значение в поле, он просто продолжал возвращаться к тому, что уже было там), поэтому он выглядит как $ B $ 2: $ B $ 100 (при условии, что у вас 100 строк)

У меня это сработало в Excel 2016.



Когда Excel решает переместить текст в соседние ячейки или обрезать их по границе?

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

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

Есть идеи? Спасибо.


Чтобы текст выливался за край ячейки, должны выполняться следующие условия:

  • в ячейке не включен «Перенос текста»
  • Ячейка не является объединенной ячейкой
  • Ячейка содержит значение, превышающее ширину ячейки
  • Соседняя ячейка пуста * и не является объединенной ячейкой
  • Ячейка имеет любое из следующих горизонтальных выравниваний:

    • Общие
    • Влево (отступ)
    • По центру
    • Справа (отступ)
    • Центр по выделению
      (справа перекрывает ячейку слева; центр перекрывается с обеих сторон.)
  • Содержимое ячейки не повернуты (т.е. ориентация установлена ​​на 0 °) (только Excel 2010?)

  • ячейка содержит текстовое значение. Числовые значения и значения даты преобразуются в #### или в экспоненциальную нотацию вместо перекрытия соседних пустых ячеек.
  • На листе нет «Показать формулы» «включен

Я считаю, что это все необходимые условия. Если я что-то пропустил, не стесняйтесь редактировать этот ответ.

* При определенных обстоятельствах может отображаться соседняя ячейка быть пустым, но не быть, и в этом случае текст не будет переполняться в эту ячейку, потому что она на самом деле не пуста. Например, если соседняя ячейка содержит формулу, разрешающуюся в "" , то она не пуста.


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

Чтобы исправить это, мне пришлось предварительно отформатировать ячейки в текст в Excel, а затем вставить данные в текстовые ячейки с помощью специальной вставки — значений.

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

0


В дополнение к случаям, указанным в других ответах, следует отметить что Excel всегда будет обрезать переполнение текста, если на листе активен параметр «Показать формулы» ( Ctrl + `). Если вы регулярно используете сочетание клавиш Ctrl + 1 для доступа к диалоговому окну «Формат ячеек», очень легко случайно активировать «Показать формулы», и вы можете не заметить, что это произошло. Это особенно верно, если у вас фактически нет формул на активном листе.

0


Много версий назад это зависело исключительно от того, была ли соседняя ячейка пустой или нет. Если кто-то думает, что это уже не так, оставьте комментарий ниже.

2


Я не рекомендую использовать clear all (если вы не хотите потерять свою работу!). Excel (по крайней мере, 2013 г. — не уверен в более ранних версиях — хотя я почти уверен, что 2000 г. не действовал таким образом) распознает пустую строку (т.е. «») как значение и поэтому, даже если у вас есть «пустая» соседняя ячейка , ваши данные не будут переполняться в эту ячейку.

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

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


Я ТАК СЧАСТЛИВЫ, ЧТО НАШЕЛ СРЕДСТВО!! С некоторого времени я научился с этим жить. Текст из одного столбца не попадет в следующий столбец … он будет обрезан. Я попробовал «очистить все» среди многих других вариантов. Наконец … Формат — Ячейки — и снят флажок Объединить ячейки. Теперь это кажется таким простым.

1


Он перетекает в следующую ячейку если в соседней ячейке (например, в столбце A, столбце B) нет какого-либо содержимого.

Если вы удалите содержимое столбца B, содержимое столбца A больше не будет переполнение. Если вы вернетесь в столбец B и выполните «очистку содержимого», тогда A снова выйдет за пределы размера ячейки.


Щелкните нужную ячейку и отключите «перенос текста». В Excel 2013 это находится на вкладке «Главная» примерно посередине экрана.


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

Это не сработает, если вы попытаетесь изменить формат после ввода текста.

Дэйв


Это меня расстраивает, поэтому я этим и занимаюсь. все приведенные выше ответы хороши, и текст только вторгается в следующую ячейку, если в ячейке нет данных, поэтому я делаю следующее: выберите столбец, с которым вы хотите работать, и на вкладке Главная выберите раскрывающийся список Найти и выбрать , выберите «Заменить». В поле «Найти» оставьте поле пустым, а в поле «Заменить на» поставьте точку или пробел и используйте параметр «Заменить все» внизу. Это поместит что-то в пустые поля и остановит проникновение данных из поля перед ним.


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

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

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


У меня был особый случай, когда текст перетекал в следующую ячейку, но я не мог его видеть.

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

Белый текст был перенесен в следующую ячейку, но не на фон, поэтому он был невидим на белом фоне по умолчанию.

Выделение второй ячейки и окрашивание ее в красный цвет устранили проблему.


Данные будут переполняться, только если столбец справа будет пустым. Итак, отфильтруйте и выберите (Пробелы). Выберите первую ячейку в верхней части столбца и введите пробел. Скопируйте и вставьте первую ячейку в конец столбца. Очистите фильтр, и вы увидите, что данные переполнения теперь исчезли.

Вуаля ..

Оцените статью
clickpad.ru
Добавить комментарий