Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

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

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

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

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

Форматирование части строки в Excel

Пример условного форматирования части строки листа Excel в зависимости от содержимого одной или двух ячеек в этой строке.

Условие примера

  1. Залейте строку желтым фоном, если третья ячейка (столбец «C») в строке содержит «Да».
  2. Заполните строку серым фоном, если четвертая ячейка (столбец «D») в строке содержит «Нет». Залейте строку красным фоном, если третья ячейка (столбец «C») в этой строке содержит «Да», а четвертая ячейка (столбец «D») содержит «Нет».
  3. Заливка применяется к первым 5 ячейкам любого ряда.

Решение примера

Выделите первые 5 столбцов, чтобы определить диапазон, к которому будут применены созданные вами правила условного форматирования:

Выделение первых пяти столбцов на листе Excel

2. Создайте первое правило: условие =$C1=»Да». и цвет заливки — желтый:

Пример создания правил форматирования №3

3. Второе правило — условие =$D1=»Нет», цвет заливки — серый:

Пример создания правил форматирования №4

4. Создайте третье правило: условие =I($C1=»Да»;$D1=»Нет»), цвет заливки — красный:

Пример создания правил форматирования №5

5. Проверьте созданные правила в «Менеджере правил условного форматирования». Здесь отображаются диапазоны, к которым правила применяются правильно:

Диспетчер правил условного форматирования с тремя правилами

6. Вы можете увидеть результаты условного форматирования части строки, заполнив ячейки столбцов «C» и «D» словами «Да» и «Нет»:

Пример условного форматирования части строки на листе Excel

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

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

Читайте так же:
Как преобразовать серийный номер на сегодняшний день в Excel?

Скачайте файл Excel с примерами. Первый рабочий лист реализует условное форматирование для всей строки, второй — для части строки.

Правила выделения ячеек ​

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

У нас есть числовое значение — количество баллов. Ячейки, в которых количество баллов меньше, чем количество очков, будут выделены.

Для этого выделите диапазон значений, для которых вы хотите применить правило, и выберите ‘Правила выделения ячеек’ — ‘Меньшее’.

Затем вам будет предложено ввести свои данные. Введите количество баллов, необходимое для зачета: 80.

Теперь осталось определить формат.

Из списка предложенных вариантов, либо вы можете сами выбрать «Пользовательский формат». В первом открывшемся окне необходимо изменить параметры, в нашем случае для заливки выбран оранжевый цвет. При необходимости здесь же можно изменить формат текста, шрифт (цвет, начертание и т.д.), границы (цвет, тип линии).

Нажмите OK, и вы увидите результат: ячейки, значение которых было меньше 80, выделены оранжевым.

Аналогичным образом можно повторить и текстовые значения, в данном случае значения прохода/непрохода. Для этого нужно выделить новый диапазон, вернуться в «Правила выделения ячеек» и выбрать «Содержит текст».

В открывшемся окне введите текст, который вы хотите выделить — слово «suspense» — и установите нужное форматирование так же, как и раньше.

Наши ячейки выделены нужным знаком.

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

Чтобы выбрать строку целиком, выберите опцию «Управление правилами».

Выберите «Этот лист» из выпадающего списка (чтобы увидеть правила, которые вы применили ко всему листу, а не только к выбранной ячейке), затем нажмите «Создать правило».

Здесь же мы видим список правил, которые нам предлагается применять.

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

Читайте так же:
Как преобразовать единицы температуры между градусами Цельсия, Кельвина и Фаренгейта в Excel?

Выберите первую ячейку в первой строке, которую нужно отформатировать, нажмите кнопку Условное форматирование в разделе Стили на вкладке Главная и выберите в раскрывающемся меню пункт Управление правилами.

В окне «Менеджер правил условного форматирования» нажмите кнопку «Новое правило».

» В окне «Новое правило форматирования» выберите «Использовать формулу для определения ячеек для форматирования». Это самая сложная часть. Формулы должны иметь значение «True» для применения правил, и они должны быть достаточно гибкими, чтобы их можно было использовать во всей таблице. Здесь мы используем формулу:

Во всей формуле я включил адрес ячейки, которую я хочу исследовать, в part = $ D4. Моя текущая строка — 4, а столбец — D (с датой выхода фильма). Обратите внимание на знак доллара перед D . Он будет проверять E5 при применении условного форматирования к следующей ячейке, если вы не включите этот символ. Эта формула будет применяться к нескольким строкам, поэтому необходимо указать «фиксированный» столбец ( $ D ) и «гибкую» строку ( 4 ).

Часть формулы — это условие, которое должно быть выполнено. В этом случае мы выберем простое условие: число в столбце даты выхода должно быть меньше 1980. Конечно, при необходимости вы можете использовать гораздо более сложные формулы.

Таким образом, наша формула верна всегда, когда значение в столбце D текущей строки меньше 1980.

Следующим шагом будет определение форматирования, которое произойдет, если формула верна. В том же окне «Новое правило форматирования» нажмите кнопку «Формат».

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

Вернувшись в окно «Новое правило форматирования», вы можете увидеть предварительный просмотр вашей ячейки. Если вы довольны тем, как все выглядит, нажмите кнопку «ОК».

Теперь вы должны вернуться в окно Менеджер правил условного форматирования. Немного сдвиньте окно, чтобы за ним была видна ваша таблица, а затем нажмите кнопку Применить. Изменение формата выделенной ячейки указывает на то, что ваша формула верна. В этом случае вам нужно сделать несколько шагов назад и подкорректировать формулу, пока она не заработает. Очевидно, что наша формула сработала, и мы смогли заполнить выделенную ячейку зеленым цветом.

Читайте так же:
Как преобразовать научную запись в текст или число в Excel?

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

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

Когда вы закончите, нажмите стрелку вниз справа от поля адреса, чтобы вернуться в окно «Менеджер правил условного форматирования».

В поле «Применить к» теперь включен диапазон ячеек, а не только один адрес. После повторного нажатия кнопки «Применить» вся таблица будет отформатирована в соответствии с вашим правилом. В нашем примере видно, что строки, содержащие фильмы, снятые до 1980 года, залиты зеленым цветом.

Вот и все! Можно создавать дополнительные формулы, если они сложны. Более того, ваши формулы на самом деле могут быть очень сложными по сравнению с примером, который мы использовали здесь. Также можно использовать условное форматирование между различными электронными таблицами, чтобы ячейки или строки в одной таблице форматировались по-разному в зависимости от данных в другой. Вы можете создавать сложные электронные таблицы с данными, которые появляются непосредственно на экране, экспериментируя с рассмотренными нами методами.

Ад Условного Форматирования

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

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

Исходная таблица

Для наглядности в таблицу добавлены три правила условного форматирования:

Правила условного форматирования

Первое правило делает синие гистограммы на столбце с суммами транзакций. Она создается с помощью Главная — Условное форматирование — Гистограммы (Главная — Условное форматирование — Полосы данных).

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

Третий — сделать нижний край всей строки красным, если день меняется на следующий, то есть дата на текущей строке не равна дате на следующей.

Читайте так же:
Как распечатать длинную колонку на одной странице в Excel?

Второе и третье правила создаются с помощью Главная — Условное форматирование — Создать правило — Использовать формулу для определения ячеек для форматирования (Главная — Условное форматирование — Создать правило — Использовать формулу для определения ячеек для форматирования), ввода соответствующей формулы (2) и задания формата ячеек (3):

Создание правила условного форматирования с формулой

Пока что, надеюсь, всё просто и понятно. Таблица хранит данные, а условное форматирование наглядно подсвечивает негодяев-менеджеров, разделяет даты и визуализирует стоимость.

Путь к катастрофе

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

После удаления строки из середины таблицы

Красная линия между 2 и 3 марта почему-то исчезла, а наше правило условного форматирования, разделяющее даты, разделилось на две части, одна из которых имеет ошибку #CCLOCK (то есть не работает), а другая применяется к двум непересекающимся интервалам A2:E8 и A10:E29 (не ко всей таблице!).

В приведенном ниже сценарии Кирилл Краснов повторяет свою сделку с магазином «Лента» в Тольятти (строка 25), и вы должны внести эту информацию в таблицу.

Что вы собираетесь делать?

Скорее всего, как и любой нормальный человек, вы скопируете строку 25 и вставите ее в конец таблицы, верно?

Копируем строчку в конец таблицы

Также вы наследуете все эти правила условного форматирования:

Продублированные правила для добавленной строки

По какой-то причине Excel продублировал те же правила для добавленной строки, вместо того чтобы просто растянуть диапазон в поле Applied to (Применить к).

Ну, а на десерт мы можем попробовать что-нибудь еще более невинное — например, вставить пустую строку в середину таблицы между строками 4 и 5:

Вставляем пустую строку

В списке правил условного форматирования это приведет к созданию еще одного дубликата и разделит диапазон существующего 5-го правила на несколько фрагментов:

Еще больше проблем с условным форматированием

Я могу продолжать еще долго, но, думаю, вы уже уловили идею или вспомнили, как сталкивались с этой проблемой раньше (эта проблема существует в Excel с 2007 года). Вставлять строки, удалять строки, копировать, вырезать и переносить данные в таблице совершенно безобидно и естественно.

  • Появление бесчисленных дубликатов одних и тех же правил
  • Фрагментация областей применения этих правил
  • Появление нефункциональных правил с ошибками #Предупреждение!

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

На форумах по Excel в Интернете это изображение часто называют «Кошмар условного форматирования» («Conditional Formatting Nightmare» или «Условное форматирование Hell»).

Читайте так же:
Как разрешить ввод только буквенно-цифровых символов в Excel?

Очень скоро наступит замедление всего этого нарастающего хаоса. С точки зрения потребления ресурсов, условное форматирование очень ресурсоемко, поскольку Excel пересчитывает ультрафиолетовые правила гораздо чаще, чем ту же формулу. Аналогично, если таких правил десятки, даже самый быстрый ПК начнет тормозить.

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

Так как же нам решить эту проблему?

Способ 1. Вручную

Несмотря на кажущееся пренебрежение, исправить весь этот беспорядок довольно просто. Идея заключается в том, что правила УФ «ломаются», обычно для строк ниже первой строки. Первая строка, с другой стороны, в большинстве случаев остается в порядке. Поэтому нам нужно скопировать все правила в первой строке в остальную часть таблицы, чтобы устранить проблему.

Для этого выполните следующие шаги:

  1. Выберите все строки нашей таблицы, кроме первой.
  2. Удалите все правила условного форматирования из выбранных ячеек с помощью Главная — Условное форматирование — Очистить правила — Очистить правила — Очистить правила из выбранных ячеек.
  3. Выделите первую строку, нажмите кнопку Форматирование по формулам в Home, выделите все остальные строки и скопируйте в них форматирование из первой строки.

Способ 2. Макросом

Если есть ощущение, что подобную процедуру вам придётся проделывать ещё не раз, то имеет смысл автоматизировать весь процесс с помощью макроса. Для этого:

  1. Нажмите Alt + F11 или нажмите Visual Basic (Developer — Visual Basic) на вкладке Developer.
  2. В открытом окне редактора макросов мы добавляем новый модуль в нашу книгу через меню Вставка — Модуль.
  3. Вставьте наш макрос в пустой модуль, который вы создали:

Теперь достаточно выделить все строки таблицы (кроме заголовка) и запустить макрос с помощью команды Разработчик — Макросы или сочетания клавиш Alt + F8.

И всё будет хорошо 🙂

И не забудьте сохранить файл в формате, пригодном для макросов (xlsm).

Если вы хотите применить этот макрос к другим файлам, лучше поместить его в папку Personal Macro.

Немного улучшенная версия этого макроса уже интегрирована в последнюю версию моего PLEX дополнения 😉

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector