Letysite.ru

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

Как убрать условное форматирование в excel

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

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

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

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

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

Удаление значений в ячейках с ручным форматированием

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

Удаление значений в ячейках с условным форматированием

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

Видео по работе с надстройкой

Гайд по использованию условного форматирования в Excel

Что такое «Условное форматирование» и для чего оно нужно?

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

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

Как создать правило? ​

Студенты сдают тест по теме «Рыночная экономика», оценка за тест ставится в формате зачет/незачет. При этом «зачет» ставится, если набрано не менее 80 баллов. Необходимо выделить оранжевым цветом строки со студентами, которые провалили тестирование.

Рассмотрим, какими правилами можно воспользоваться для решения данной задачи.

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

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

В данном примере у нас есть числовое значение – количество баллов. Давайте выделим цветом те ячейки, где количество баллов не дотягивает до зачета.

Для этого выделяем диапазон значений, для которого будем применять правило, и выбираем «Правила выделения ячеек» – «Меньше».

После этого видим открывшееся окошко для ввода данных. Вводим количество баллов, необходимое для зачета – 80.

Теперь осталось выбрать формат.

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

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

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

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

В итоге мы имеем подсвеченные ячейки с нужной отметкой.

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

Для того, чтобы выделить строку целиком, зайдём в раздел «Управление правилами».

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

Читать еще:  Как сделать ссылку в excel

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

Форматировать все ячейки на основании их значений ​

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

Двухцветная шкала – от минимального значения в выделенном диапазоне к максимальному. Цветовую схему шкалы при необходимости можно изменить.

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

Гистограмма тоже вполне наглядна. Берет максимальное значение диапазона за 100% и пропорционально заполняет ячейку цветом (цвет также можно изменить).

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

Главное не забывайте указывайте диапазон, для которого данное правило будет применяться (это касается любого правила).

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

Примечание: о том, как правильно и продуктивно работать с правилами фильтрации, читайте в нашей статье «Правила фильтрации в MS Excel».

Форматировать только ячейки, которые содержат

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

Форматировать только первые или последние значения ​

Это правило не так часто применяется, но если Вам нужно выделить, например, 5 ячеек с наивысшим результатом (значения, которые относятся к первым 5), или, наоборот, 10 ячеек с наименьшим результатом (значения, которые относятся к последним 10), то используйте его.

Форматировать только значения, которые находятся выше или ниже среднего ​

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

Форматировать только уникальные или повторяющиеся значения ​

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

Использовать формулу для определения форматируемых ячеек ​

Ну вот и добрались до последнего пункта в этом меню, и, на наш взгляд – самого универсального. С помощью этого правила мы и выполним условие поставленной задачи.

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

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

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

Примечание: Знак $ закрепляет столбец или строку, в зависимости от того, перед буквой (столбец) или цифрой (строка) он стоит. Написание $D$5 показывает, что в формуле будет использоваться только конкретная ячейка.

Так как нам необходимо форматировать всю таблицу, т.е. использовать в формуле весь столбец D, перед строкой символ $ убираем (перед столбцом убирать не нужно). В итоге остается $D5.

Примечание: Сразу убирать этот знак не стоит, т.к. после применения правила диапазон сдвинется по строкам. Самое оптимальное – применить, потом убрать его, затем применить снова.

И теперь мы видим результат: оранжевым цветом выделены строки со студентами, у которых оценка за тест – незачет. Задача выполнена!

Как изменить или удалить правило? ​

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

По кнопке «Изменить правило» откроется меню, в котором можно отредактировать формулу, изменить параметры форматирования и т.д.

Кнопка «Удалить правило» удалит то, на которым в данный момент стоит выделение.

Также правила можно менять местами, нажимая на стрелочки в этом же меню «вверх» или «вниз». Выполняются правила снизу-вверх, т.е. то, которое сверху, перекрывает нижние (выполняется последним).

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

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

Трюк №22. Как в Excel включить и выключить условное форматирование и проверку данных при помощи флажков

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

Обычно, чтобы разрешить пользователям вводить данные, которые могли бы быть расценены как неправильные, из ячеек удаляется условное форматирование или проверка данных. Однако есть более простой путь: можно совместить простой флажок с панели инструментов Формы (Forms) с проверкой данных.

Для этого примера мы применим условное форматирование к диапазону ячеек, чтобы любые данные, встречающиеся более одного раза, выделялись для упрощения идентификации. Предположим, что в таблице данные расположены в диапазоне $А$1:$Н$100 . Условное форматирование этого диапазона данных для упрощения идентификации повторяющихся данных требует выполнить несколько шагов.

Читать еще:  Как задать среднее значение в excel

Выделите ячейку К1 и присвойте ей имя CheckBoxLink, введя его в поле имени слева от строки формул и нажав клавишу Enter. Если панель инструментов Формы (Forms) не видна, правой кнопкой мыши щелкните любую панель инструментов и выберите команду Формы (Forms), а затем щелкните значок флажка. Теперь щелкните лист где-либо за пределами диапазона $А$1:$Н$100 , чтобы добавить флажок.

Правой кнопкой мыши щелкните флажок и в контекстном меню выберите команду Формат объекта > Элемент управления (Format Control > Control). В поле Связь с ячейкой (Cell Link) введите имя CheckBoxLink и щелкните кнопку ОК. Выберите ячейку А1, а затем перетащите указатель, чтобы выделить диапазон ячеек до Н100.

Важно, чтобы ячейка А1 была при выделении активной. Выберите команду Формат > Условное форматирование (Format → Conditional Formatting) и в поле с параметром Значение (Value Is) выберите вариант Формула (Formula Is). В поле справа введите следующую формулу (рис. 2.7): =AND(COUNTIF($A$l:$H$100;Al)>1;CheckboxL1nk) , в русской версии Excel: =И(СЧЕТЕСЛИ($A$l:$H$100;Al)>1;CheckboxLink) . Щелкните кнопку Формат (Format), перейдите на вкладку Вид (Patterns) и выберите цвет, которым будут выделяться дублирующиеся данные. Щелкните кнопку ОК, затем еще раз щелкните кнопку ОК.

Рис. 2.7. Диалоговое окно с формулой условного форматирования диапазона и выделения повторяющихся данных

Когда флажок, который вы добавили на лист, будет установлен, связь в ячейке К1 (CheckBoxLink) возвратит значение ИСТИНА (TRUE), и все дублирующиеся значения в диапазоне $А$1:$А$100 будут подсвечены. Когда вы сбросите флажок, связь в ячейке возвратит значение ЛОЖЬ (FALSE) и дубликаты подсвечены не будут.

Этот флажок представляет собой выключатель, при помощи которого вы можете включать и выключать условное форматирование в таблице, не переходя в диалоговое окно Условное форматирование (Conditional Formatting). Тот же принцип можно применять при проверке данных, используя формулы.

Этот способ работает благодаря функции И (AND). Она требует, чтобы произошло два события: функция СЧЁТЕСЛИ($А$1:$Н$100;А1)>1 (COUNTIF($A$1:$H$100;A1)>1) должна вернуть значение ИСТИНА (TRUE), и связь с флажком в ячейке (CheckBoxLink) также должна вернуть значение ИСТИНА (TRUE). Другими словами, чтобы функция И (AND) вернула значение ИСТИНА (TRUE), оба условия должны быть истинными.

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Условное форматирование в Excel

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

Условное форматирование – это простой способ определить ячейки с ошибочными записями или значениями определённого типа. Вы можете использовать формат (например, красная заливка), чтобы легко идентифицировать определенные ячейки.

Виды условного форматирования

Когда вы нажимаете на кнопку Условное форматирование, которая находится в группе Стили вкладки Главная, вы увидите выпадающее меню со следующими опциями:

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

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

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

Цветовые шкалы позволяет задавать двух- и трехцветовые шкалы для цвета фона ячейки на основе ее значения относительно других ячеек в диапазоне

Наборы значков отображает значок в ячейке. Какой именно значок отображается, зависит от значения ячейки относительно других ячеек. Excel 2013 предоставляет 20 наборов значков на выбор (при этом вы можете смешивать и сочетать значки из разных наборов). Количество значков в наборах колеблется от трех до пяти.

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

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

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

Графическое условное форматирование

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

На рисунке изображено применение двух различных правил для форматирования для диапазона от 6 до 1 и наоборот. В первом случае применялись Цветовые шкалы, где мы видим, как изменяется формат при изменении значения от 6 до 1, во втором – 3 цветные стрелки.

Определение конкретных значений в диапазоне ячеек

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

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

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

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

Выделяем диапазон ячеек, к которому мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Правила выделения ячеек -> Текст содержит.

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

Читать еще:  Использование функции впр в excel примеры

Щелкаем ОК, чтобы наше правило вступило в силу.

Несколько условных форматирований для одного диапазона

Скажем, вы хотите применить три различных условных форматирования к одному и тому же диапазону ячеек: первый тип формата, когда ячейка содержит целевое значение, второй – когда больше цели и третий – когда меньше. Ниже описаны шаги по заданию формата Желая заливка с темно-желтым текстом для ячеек содержащих значение 95, Зеленая заливка с темно-зеленым текстом для ячеек со значениями больше 95 и Светло-красная заливка и темно-красным текстом для ячеек меньше 95.

Выделяем диапазон ячеек, к которому мы хотим применить три различных правила условного форматирования. Начнем с создания правила для ячеек, содержащих значение равное 95. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Правила выделения ячеек -> Равно. Excel откроет диалоговое окно Равно, где в левом текстовом поле необходимо указать условие 95, а в правом выпадающем списке выбрать формат для этого условия Желая заливка с темно-желтым текстом.

Далее задаем условное форматирование для значений больше 95. Из меню Условное форматирование -> Правила выделения ячеек выбираем Больше, в появившемся диалоговом окне Больше указываем значение, выше которого ячейка будет закрашиваться в зеленый цвет, и сам формат.

Аналогичную операцию проделываем для ячеек со значениями меньше 95. На этот раз из списка правил необходимо выбрать Меньше и задать формат с красной заливкой.

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

Формулы в условном форматировании

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

Выделяем таблицу с данными, к которой мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Создать правило. В появившемся диалоговом окне Создание правила форматирования в поле Выберите тип правила выбираем Использовать формулу для определения форматируемых ячеек.

В поле Измените описание правила задаем условия и формат для нашего правила. В нашем случае, условием будет формула =ИЛИ(ДЕНЬНЕД($A2;2)=6;ДЕНЬНЕД($A2;2)=7). В качестве формата я выбрал темно красную заливку.

Послесловие

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

Вам также могут быть интересны следующие статьи

31 комментарий

А можно ли сделать так чтобы значение в ячейке менялось в зависимости от цвета другой ячейки? Например если ячейка залита красным цветом то 0, если зеленым цветом то 1.

Эльнур, такую штуку можно реализовать с помощью создания пользовательской функции, например, такой:

Замена Условного форматирования обычным

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

На данный момент возможно конвертировать следующие форматы:

  • заливка ячеек;
  • цвет шрифта ячеек.
  • стиль шрифта(Жирный, Наклонный, Зачеркнутый ).
  • границы ячейки. Можно варьировать заменяемые границы по отдельности — левая граница, правая граница, нижняя граница и верхняя граница.

Каждое условие может быть заменено независимо от другого. Однако стоит помнить, что если изначально в ячейке была заливка какого-либо цвета, то после применения данной команды прежняя заливка будет заменена на заливку, определенную условием УФ. Если в условии УФ заливка отсутствует — она также будет удалена из ячейки после применения команды. Это относится ко всем условиям, заливка лишь пример.

Примечание: Мной тестировались файлы с различными условиями УФ, среди которых были: формулы с именованными диапазонами, формулы сложной вложенности, условия со ссылками на другие листы и естественно различные варианты простых условий. Но это не означает, что надстройка 100% корректно обработает именно Ваш файл, т.к. все возможные условия я протестировать просто не в состоянии. Просьба сообщать обо всех случаях некорректной работы надстройки(с описанием примененной формулы).

Вы так же можете удалить все Условия УФ после преобразования форматов. Пригодится, если форматов на листе много и удалять вручную достаточно времязатратно. Доступно два режима удаления:

  • Удалить УФ только с активного листа. Будут удалены все условия УФ, применённые к активному листу;
  • Удалить УФ со всех листов книги. Будут удалены все условия УФ, созданные в активной книге.

Стоит внимательно и аккуратно применять данную команду, т.к. после её выполнения Вы не сможете отменить удаление и все условия УФ будут потеряны.

The_Prist_FCtoReal.zip (97,6 KiB, 4 512 скачиваний)

В архиве расположена сама надстройка и файл помощи. Как установить надстройку: Установка надстроек

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

Ссылка на основную публикацию
Adblock
detector