Как убрать условное форматирование таблицы в excel
Форматирование ячеек на листе позволяет выделить нужные сведения, чтобы их было легко заметить, но при этом форматирование неиспользуемых ячеек (особенно целых строк и столбцов) может привести к быстрому росту размера файла рабочей книги. Если у вас открыт избыточно отформатированный лист, то это может замедлить работу не только Excel, но и снизить общую производительность компьютера. Также из-за лишнего форматирования в Excel могут возникать проблемы при печати.
Используйте команду Clean Excess Cell Formatting (Удалить лишнее форматирование ячеек), которая доступна в Excel на вкладке Inquire (Запрос) в Microsoft Office 365 и Office профессиональный плюс 2013. Если вкладка Inquire (Запрос) в Excel недоступна, вот как можно включить ее:
Щелкните Файл > Параметры > Надстройки.
Выберите в списке Управление пункт Надстройки COM и нажмите кнопку Перейти.
В поле Надстройки COM установите флажок Inquire (Запрос) и нажмите кнопку ОК.
Теперь вкладка Inquire (Запрос) должна отображаться на ленте.
Важно: Перед очисткой лишнего форматирования ячейки мы рекомендуем создать резервную копию файла, так как иногда это может привести к увеличению размера файла, а отменить эти изменения невозможно.
Чтобы удалить лишнее форматирование на текущем листе:
На вкладке Inquire (Запрос) выберите команду Clean Excess Cell Formatting (Удалить лишнее форматирование ячеек).
Выберите очистку только открытого листа или очистку всех листов. После выполнения операции очистки нажмите кнопку Да для сохранения изменений на листах или кнопку Нет, чтобы отменить сохранение.
Влияние операции очистки на условное форматирование
При очистке лишнего форматирования из листа удаляются ячейки, расположенные после последней непустой ячейки. Например, если вы применили условное форматирование к целой строке, но ваши данные располагаются только до столбца V, условное форматирование может быть удалено из столбцов, расположенных за столбцом V.
Дополнительные информацию о том, как устранить другие проблемы, вызванные лишним форматированием, см. в статье службы поддержки Майкрософт Сброс последней ячейки в Excel.
Как в excel отменить условное форматирование
=СЧЕТЕСЛИ($A$1:$C$10;A3)=3 и т.д. New Rule).(Duplicate Values). выбранный стиль форматирования..(Home) нажмитеЗаполнение формул в таблицахвыберите команду автоматически отформатированный текст можно оперативнее обеспечивать
Очистить формат
Если вы хотите очистить: Может просто стоит
TaatshiVlad999 
Правописание > Параметры это минус -Обратите внимание, что мы
Нажмите наОпределите стиль форматирования иПримечание:На вкладкеУсловное форматирование
для создания вычисляемыхПараметры
и нажмите появившуюся вас актуальными справочными. условное форматирование на пересмотреть формулу условного
: Если устраивает текстовый, то ли лыжи автозамены.
Поиск и удаление одинакового условного форматирования на листе
не даст ввести создали абсолютную ссылкуИспользовать формулу для определения нажмите
Таким же образомГлавная(Conditional Formatting) > столбцов. кнопку материалами на вашемЩелкните ячейку с условным
листе, следуйте приведенным форматирования таким образом формат изначально на
не едут, то2. Откройте вкладку текст с тире, – форматируемых ячеекОК можно выделить(Home) нажмите
Правила выделения ячеек : одну формулуВ диалоговом окнеПараметры автозамены языке. Эта страница форматированием, которое вы ниже инструкциям. чтобы была зависимость всех листах, то ли я. не
Отмена автоматического форматирования
Автоформат при вводе. хоть ты его$A$1:$C$10(Use a formula.Первые 10 элементовУсловное форматирование(Highlight Cells Rules) применяется ко всемПараметры Excel. Эта кнопка очень переведена автоматически, поэтому хотите удалить соНа всем от состояния какой можно изменить стиль всегда срабатывает.3. Снимите галочки снеси. Временами получается. to determine whichРезультат: Excel выделил повторяющиеся
(Top 10 items),(Conditional Formatting) > > ячейкам в столбцевыберите категорию маленькая, поэтому будьте ее текст может всего листа.листе нибуть ячейки (и Обычный.pashulka с необходимых пунктов. как-то отвоевать инфуПримечание: cells to format).
имена.Первые 10%Удалить правилаБольше таблицы Excel.Правописание внимательны, передвигая курсор. содержать неточности и
На вкладкеНа вкладке в свою очередь,На вкладке Главная, спасибо. Вроде получается.Taatshi для каких-то конкретныхВы можете использоватьВведите следующую формулу:Примечание:
(Top 10%) и(Clear Rules) >(Greater Than).Примечание:и нажмите кнопкуЧтобы удалить форматирование только грамматические ошибки. ДляГлавнаяГлавная например, от элемента — кнопка Стили Хоть так.
Одновременная установка параметров автоматического форматирования
: ячеек, но это любую формулу, которая=COUNTIF($A$1:$C$10,A1)=3Если в первом так далее.
Удалить правила из выделенныхВведите значение Если вы хотите задатьПараметры автозамены для выделенного текста,
нас важно, чтобыщелкните стрелку рядомщелкните управления). ячеек (или группаpashulkazewsua
весьма утомительно( вам нравится. Например,=СЧЕТЕСЛИ($A$1:$C$10;A1)=3 выпадающем списке ВыУрок подготовлен для Вас
ячеек80 способ отображения чисел. выберите команду эта статья была
с кнопкойУсловное форматированиеНапример: Стили, если широкий:, это не помогает(Оно меня достало. чтобы выделить значения,Выберите стиль форматирования и выберите вместо командой сайта office-guru.ru(Clear Rules fromи выберите стиль и дат, вы,На вкладкеОтменить
вам полезна. ПросимНайти и выделить>ячейка А1 = экран) — правойTaatshi Там все отключено,
Существует ли какой-то встречающиеся более 3-х нажмитеПовторяющиесяИсточник: http://www.excel-easy.com/data-analysis/conditional-formatting.html Selected Cells). форматирования. на вкладкеАвтоформат при вводе. Например, если Excel вас уделить пару
и выберите команду
Условное форматирование в Excel
Удалить правила ИСТИНА кнопкой по стилю, Вы можете попробовать кроме ссылок. способ РАЗ И раз, используйте этуОК(Duplicate) пункт
Правила выделения ячеек
Перевел: Антон АндроновЧтобы выделить ячейки, значениеНажмитеГлавная
- установите флажки для автоматически создает гиперссылку секунд и сообщить,
- Выделить группу ячеек>ячейка А2 = Обычный — Изменить вариантГуглю третий день, НАВСЕГДА отключить ЛЮБОЕ формулу:.Уникальные
- Правила перепечатки которых выше среднегоОКв группе «
- необходимых параметров автоматического и ее необходимо помогла ли она.Удалить правила со всего
- 2 — Формат —Vlad999 результаты неутешительны. Похоже, АВТО ФОРМАТИРОВАНИЕ либо=COUNTIF($A$1:$C$10,A1)>3Результат: Excel выделил значения,(Unique), то Excel
★ Еще больше в выбранном диапазоне,.Результат: Excel выделяет ячейки,число форматирования. удалить, выберите команду вам, с помощьюВыберите параметр
Удалить правила
листаячейка В2 = Текстовый — ОК
- , только программно, например, мое желание невыполнимо. для определенной книги,
- =СЧЕТЕСЛИ($A$1:$C$10;A1)>3 встречающиеся трижды. выделит только уникальные уроков по Microsoft выполните следующие шаги: в которых содержится». Она неАдреса Интернета и сетевыеОтменить гиперссылку кнопок внизу страницы.Условные форматы
Правила отбора первых и последних значений
. 2 — ОК событие для конкретного
- Vlad999 либо вообще дляУрок подготовлен для Вас
- Пояснение: имена. ExcelВыделите диапазон значение больше 80. является частью автоматическое пути гиперссылками. Для удобства также.
- В диапазоне ячеек
- на ячейке В2надо попробовать, тоже листа (соответственно код: по моему проще всех без исключения? командой сайта office-guru.ruВыражение СЧЕТЕСЛИ($A$1:$C$10;A1) подсчитывает количествоКак видите, Excel выделяетАвтор: Антон Андронов
A1:A10Измените значение ячейки форматирование. : заменяет введенныйЧтобы прекратить применять этот приводим ссылку наЧтобы выделить все ячейкиВыделите ячейки, содержащие условное
-> Формат -> страдаю от умничанья
должен находиться строго
заранее установить формат
Я хочу указывать
Источник: http://www.excel-easy.com/examples/find-duplicates.html значений в диапазоне дубликаты (Juliet, Delta),
Этот пример научит вас
Поиск дубликатов в Excel с помощью условного форматирования
.A1К началу страницы URL-адреса, сетевые пути конкретный тип форматирования оригинал (на английском с одинаковыми правилами
- форматирование. Условное форматирование -> excel-я
- в модуле этого ячейки «текст» в форматы времени, дат,Перевел: Антон АндроновA1:C10 значения, встречающиеся трижды находить дубликаты вНа вкладкенаУсловное форматирование в Excel и адреса электронной
- в книге, выберите языке) . условного форматирования, установитеНажмите кнопку
выбрать ФОРМУЛА иPelena
листа) ячейках где будете цифр, вводить формулыАвтор: Антон Андронов, которые равны значению (Sierra), четырежды (если Excel с помощьюГлавная81 позволяет выделять ячейки
почты с гиперссылками. командуИногда при вводе сведений переключательЭкспресс-анализ в поле ввода, круто! Господи, неужелиPrivate Sub Worksheet_SelectionChange(ByVal вводить текст.
- — всегда толькоTaatshi
- в ячейке A1. есть) и т.д. условного форматирования. Перейдите
- (Home) нажмите.Результат: Excel автоматически изменяет различными цветами вВключить новые строки иОстановить в лист Excelпроверка данных, которая отображается
- =ЕСЛИ($A$1;A2=B2;ЛОЖЬ)Я, правда, пошла Target As Range)pashulka по моему решению: MS Office Excel
- Если СЧЕТЕСЛИ($A$1:$C$10;A1)=3, Excel форматирует
Следуйте инструкции ниже,
по этой ссылке,
- внизу справа оти нажав на дальше — создала Target.NumberFormat = «@»:
- с обязательным указанием 2010
- ячейку. чтобы выделить только чтобы узнать, как(Conditional Formatting) >A1 содержимого. В данном : при вводе автоматически создает гиперссылку так, как вамэтих же выделенных данных. кнопочку Формат, задать пользовательский стиль дабы End SubТолько нужно
Правила отбора первых и. уроке рассмотрим как данных ниже или и необходимо это не хотелось бы..Примечания:
что делать если
не гробить дефолтный.
продумать где нужно, Если ввод данных
zewsua
ни делала, как
Отменить автоматическое форматирование информации в ячейках
встречающиеся трижды:Выделите диапазон последних значений
Примечание: пошагово использовать правила рядом с таблица прекратить для остальной Например, введенный веб-адресНа вкладке Кнопка А2=В2. Теперь все в
устанавливать текстовый формат, начинать с апострофа: бы ни отменяла,Условное форматированиеСперва удалите предыдущее правилоA1:C10(Top/Bottom Rules) >Таким же образом для форматирования значений Excel, он расширяет части листа, выберите Еxcel преобразовывает вГлавнаяЭкспресс-анализ
ячейка В2 поменяет
шоколаде а где нет.’Taatshi ничего не помогает.(Conditional Formatting), мы условного форматирования.
.Выше среднего можно выделить ячейки, в ячейках. таблицы, чтобы включитьОтключить автоматическое создание гиперссылок гиперссылку. Это может
нажмите кнопкуне отображается в свой формат.babken76
Возможно вместо события,то Excel не, добрый день, Ни выставление формата выбрали диапазонВыделите диапазон
На вкладке(Above Average).
которые меньше заданногоЧтобы выделить ячейки, в новые данные. Например.
быть очень полезным,Условное форматирование
следующих случаях:Если А1 =
: Требуется не удалить имеет смысл использовать проявит свою интеллектуальность.Чтобы включить или ячеек текстовым, ниA1:C10
A1:C10ГлавнаяВыберите стиль форматирования.
значения, в интервале которых содержится значение если у васЕсли необходимо изменить все но иногда это, выберите пункт
все ячейки в выделенном ЛОЖЬ, то формат условное форматирование ячеек, макрос, который будет Правда все введённые отключить автоматическое форматирование нажатие крестика на, Excel автоматически скопирует.(Home) нажмитеНажмите между двумя заданными большее, чем заданное, есть таблицы в настройки автоматического форматирования
совсем не нужно.Удалить правила диапазоне пусты; ячейки В2 будет а временно отключить, осуществлять аналогичное действие, таким способом данные,
определенных элементов, просто панели формул, ни формулы в остальные
На вкладкеУсловное форматированиеОК значениями и так сделайте вот что: столбцах A и одновременно, это можно В таком случае, а затем —значение содержится только в отменен. а затем включить
а вызывать этот Excel будет воспринимать установите или снимите манипуляции с настройками. ячейки. Таким образом,Главная>
. Результат: Excel рассчитывает далее.Выделите диапазон B и ввести сделать в диалоговом автоматическое форматирование можноУдалить правила из выделенных левой верхней ячейке
Если таким образом его, желательно для макрос можно будет
как текст (вне флажки на вкладкеЕсли оно решило, ячейка(Home) выберите командуПравила выделения ячеек
среднее арифметическое дляЧтобы удалить созданные правилаA1:A10 данные в столбце окне отключить для одной ячеек выделенного диапазона, а переправить формулы для всей книги сразу.
с помощью горячих зависимости от формата), Автоформат при вводе. что то, чтоA2
Условное форматирование(Conditional Formatting > выделенного диапазона (42.5) условного форматирования, выполните. C, Excel автоматически
Программное отключение условного форматирования ячеек Excel
Автозамена ячейки или для. все остальные ячейки всех ячеек, то Возможно ли это клавиш. со всеми вытекающимиПопробуйте следовать следующей
я ввожу -
содержит формулу:=СЧЕТЕСЛИ($A$1:$C$10;A2)=3,ячейка> Highlight Cells Rules) и ко всем следующие шаги:На вкладке форматировать столбца CНа вкладке всей книги.Примечание:
пусты.
их состояние будет сделать?
Taatshi последствиями.
инструкции: время, форматирует под
A3Создать правило и выберите
ячейкам, значение которыхВыделите диапазонГлавная
в рамках таблицы.ФайлНаведите курсор мышки наМы стараемся как
Нажмите кнопку зависить от А1.
С уважением,: Спасибо, поэкспериментирую -Taatshi1. Выберите Файл
время. Если они:(Conditional Formatting >Повторяющиеся значения превышает среднее, применяет
Как удалить условное форматирование в Excel
Если вам нужно удалить некоторое условное форматирование или все одновременно, то это можно сделать очень быстро.
Выборочное удаление правил условного форматирования
- Выделите диапазон ячеек с условным форматированием C2:C8.
- Выберите инструмент: «Главная»-«Стили»-«Условное форматирование»-«Управление правилами».
- В появившемся окне «Диспетчер правил условного форматирования» выберите условие с критериями, которое нужно убрать.
- Нажмите на кнопку «Удалить правило» и закройте окно кнопкой «ОК».
Быстрое удаление условного форматирования
Естественно условия форматирования можно удалять другим, для многих более удобным способом. После выделения соответственного диапазона ячеек выберите инструмент: «Главная»-«Стили»-«Условное форматирование»-«Удалить правила»-«Удалить правила из выделенных ячеек» или «Удалить правила со всего листа».
Как вы понимаете, единственный недостаток данного способа – это отсутствие контроля над удалением только отдельного правила. Ведь один и тот же выделенный диапазон ячеек может содержать несколько правил. Иногда возникает необходимость удалять только одно из них, а для этого используйте первый способ.
Условное форматирование предоставляет возможность быстро экспонировать важную информацию на листе с большим количеством данных в Excel. Благодаря этой функции даже самые сложные структуры данных можно сделать легко читабельными. Пользователи Excel высоко оценили возможности условного форматирования, а принцип его использования стал интуитивно понятным и простым. Начиная от 2007 версии Excel, условное форматирование стало обладать широкими возможностями. Большинство операций, которые раньше требовали использование формул для реализации расширенных условий, теперь можно выполнить в несколько кликов мышки.
Как сделать условное форматирование в Excel? Инструкции с примерами.
В этой статье вы найдете множество быстрых способов как сделать условное форматирование строк, столбцов и отдельных ячеек в MS Excel 2016, 2013 и 2010. Мы рассмотрим, как можно применить различное оформление к данным, которые соответствуют определенным критериям. Это может помочь указать на наиболее важную информацию в ваших электронных таблицах.
Всем известно, что изменить фон ячейки легко. Это можно совершить, просто нажав кнопку «Цвет заливки». Но что, если вы хотите изменить оформление вашей таблицы при выполнении какого-то условия? Более того, что, если вам нужно, чтобы он изменялся автоматически при внесении изменений в таблицу? Условное форматирование для этого является действительно мощной и полезной функцией. Далее в этой статье вы найдете ответы на эти вопросы и прочтете несколько полезных советов, которые помогут выбрать правильный метод условного форматирования для каждой конкретной задачи.
В то же время изменение внешнего вида в связи с содержанием текущей либо какой-то иной ячейки либо от иных условий часто считается одной из самых сложных и непонятных функций, особенно для новичков. Если вас тоже пугает эта функция, не бойтесь! На самом деле, она очень удобна и проста в использовании, и вы убедитесь в этом всего за 5 минут после прочтения этого руководства. А теперь взгляните, сколько всего мы можем сделать!
Кроме того, если вы будете использовать форматирование по условию, то имейте в виду, что оно имеет более высокий приоритет по сравнению с обычным оформлением вручную, которое вы можете сделать через меню Главная – Формат.
Вы можете применить условное форматирование к одной или нескольким позициям, строкам, столбцам или всей таблице на основе их содержимого или при выполнении какого-то другого условия. Это делается путем создания правил (условий), в которых вы определяете, когда и как следует изменить вид выбранных клеток таблицы.
Где находится форматирование по условию в Excel?
Это очень просто: на вкладке «Главная», а в более старых версиях — группа «Стили».
Эта функция включает в себя стандартный набор заранее определенных правил и инструментов. Но главное, у пользователя есть возможность самому придумать и настроить необходимый алгоритм закраски и выделения, используя свои формулы.
Чтобы по-настоящему использовать возможности условного формата в Excel, вы должны научиться создавать различные типы правил.
Правила условного форматирования определяют 2 ключевых момента:
- К каким ячейкам должно применяться условное форматирование,
- Какие условия должны быть выполнены.
Я покажу вам, как применить условное форматирование в Excel 2016, потому что это, кажется, самая популярная версия в наши дни. Однако оно практически не отличается от форматирования в версиях 2007, 2013 и 2010. Поэтому у вас не возникнет проблем с выделением цветом нужной информации независимо от того, какая версия установлена на вашем компьютере.
Задача: у вас есть таблица или диапазон данных, и вы хотите изменить фон ячеек на основе их содержания. Кроме того, вы хотите, чтобы он менялся динамически, отражая изменения данных.
Решение: Предположим, у вас в таблице — данные о продажах шоколада различным покупателям. Необходимо в таблице Excel закрасить цветом клетки с количеством следующим образом: менее 100 единиц товара – красным, 100 и более – зелёным.
Итак, вот что вы делаете шаг за шагом:
Способ 1 — Используем стандартные возможности.
Самый простой способ — воспользоваться стандартными правилами выделения ячеек. Эти заготовки включают в себя самые простые и распространенные случаи. Но сначала выберите таблицу или диапазон, где вы хотите изменить фон ячеек. Мы взяли $D$2:$D$21.
Перейдите на вкладку «Главная» и выберите
(1) > «Правила выделения ячеек» (2) > «Меньше» (3). В более ранних версиях программы нужное нам меню располагается в группе «Стили».
В диалоговом окне укажите, что числа должны быть меньше 100, также выберите вариант выделения.
В первом поле задается условие, а во втором указывают, каким образом отформатировать полученный результат. Обратите внимание, выбрать можно цвет фона и текста из предложенных в списке. Но если хочется применить иные оттенки – сделать это можно, перейдя в «Пользовательский формат».
В результате клетки таблицы с количеством меньше 100 окрасились в красный цвет.
Приступаем к созданию второго правила. С этой же областью таблицы проделайте те же операции, только выберите на третьем шаге пункт «Больше».
В результате получим нужную нам раскраску.
Это самый простой вариант заливки ячеек.
С помощью использованных нами «Правил выделения ячеек»:
- находят в таблице числа, которые больше определенного;
- выбирают те, которые меньше определенного;
- указывают на числа, находящиеся в пределах нужного интервала;
- определяют равные какому-то числу;
- помечают в выбранных текстовых полях только те, которые необходимы;
- отмечают столбцы и числа за нужную дату;
- находят повторяющиеся текст или числа;
- придумывают прочие правила.
Способ 2 — Как самому создать правило форматирования?
Тот же результат мы можем получить и чуть иначе. Если ни одно из готовых правил форматирования не отвечает вашим потребностям, вы можете создать новое с нуля. Для этого вновь перейдите на вкладку «Главная» и выберите (1 на рисунке) > «Создать правило» (2).
Затем выберите пункт «Форматировать только ячейки, которые содержат» (3). Чуть ниже укажите, что число должно быть меньше (4) цифры «100» (5).
И далее укажите, как это все должно выглядеть. Нажмите кнопку «Формат» (6).
Выберите «красный» на открывшейся вкладке «Заливка».
При создании правила в окне « Формат ячеек» переключайтесь между вкладками « Шрифт» , « Граница» и « Заливка», чтобы выбрать стиль шрифта, стиль рамки и цвет фона соответственно. На вкладках Шрифт и Заливка вы сразу увидите предварительный просмотр вашего пользовательского формата.
Когда закончите, нажмите кнопку ОК в нижней части окна.
Подсказка:
Если вам нужно больше цветов фона или шрифта, чем предусмотрено в стандартной палитре, нажмите кнопку «Другие цвета…» на вкладках «Заливка» и «Шрифт»..
Если вы хотите применить градиент цвета фона , нажмите кнопку «Способы заливки» и выберите нужные параметры.
Нажмите кнопку ОК, чтобы закрыть окно и проверить, правильно ли применяется условное форматирование к вашим данным.
Повторите все то же самое еще раз, только измените условие: цифра должна быть больше или равна 100. И новый цвет условного форматирования, конечно же, выберите сейчас зеленый.
Способ 3 — Применяем собственную формулу в правиле условного форматирования.
И, наконец, третий способ – самый сложный, но зато самый универсальный и с большими возможностями. Чуть ранее мы создали правила форматирования, указав определенные числа, дату либо текст. Однако в некоторых случаях имеет смысл основывать условие на значении определенной ячейки. Преимущество этого подхода состоит в том, что в зависимости от того, как значение этой ячейки изменится в будущем, ваше условное форматирование будет корректироваться автоматически и отражать изменение данных.
Вновь перейдите на вкладку «Главная», (в старых версиях программы — в группу «Стили») и выберите (1) > «Создать правило» (2).
Затем выберите пункт «Использовать формулу для определения форматируемых ячеек» (3). Теперь нужно указать диапазон, в котором мы хотим что-то выделить. Для этого нажмите на пиктограмму со стрелкой вверх (4) и укажите мышкой начало диапазона – D2. Следите за тем, чтобы ссылка не была абсолютной (можно для этого использовать F4). И в конце просто допишите условие: “<100” (5), как это показано на рисунке.
Осталось только определить новые правила форматирования. Нажмите кнопку «Формат» (6).
Выберите красный на вкладке «Заливка».
Повторите создание условия еще раз, только выражение запишите D2>=100 и выберите зеленый.
Вы спросите: «А зачем все так сложно, если есть более простой вариант?» Дело в том, что использование формулы – более универсальный подход, который мы в дальнейшем будем еще неоднократно применять.
Итак, цель достигнута: фон выбранных ячеек изменяется от их наполнения.
Совет: вы можете использовать тот же метод не только для закраски, но и для изменения оформления шрифта. Для этого просто перейдите на вкладку «Шрифт» в диалоговом окне «Формат», которое мы обсуждали на шаге 6, и выберите предпочитаемый вариант оформления.
Условное форматирование Excel по значению ячейки.
В обоих предыдущих примерах мы создали правила форматирования, прямо указав числа — ограничения. Но чаще всего следует создавать критерий форматирования на основе значений ячеек. Как это сделать? Предположим, в таблице записаны ежемесячные продажи нескольких товаров. Нужно выделить цветом те цифры в декабре, которые были больше январских, в начале года.
Выделяем область для применения условного форматирования М2:М16 и затем выбираем пункт «Создать правило». В описании правила запишем выражение:
Обратите внимание, что здесь используются относительные ссылки, чтобы программа могла последовательно перебрать все ячейки указанной ей области, и при этом каждой ячейке из столбца М соответствовала ячейка из столбца В, расположенная в той же строке и относящаяся к тому же самому товару.
Отображение выделенных ячеек настройте так же, как мы это рассматривали ранее.
Как видно на рисунке, созданное нами правило условного форматирования работает правильно и выделяет декабрьские продажи тех товаров, которые выросли по сравнению с январём.
Использование абсолютных и относительных ссылок в правилах.
Для того, чтобы было проще изменять условия выделения определенных значений в таблице Эксель, запишем некоторые параметры отбора в специально отведённые для этого ячейки.
Задача: выделить в таблице заказы с количеством менее 50 и более 100 ед.
Наши ограничения записываем в D1 и D2. Далее создаем первое правило условного форматирования для диапазона E5:E24.
Абсолютная ссылка на D2 означает, что каждая из ячеек нашего диапазона сравнения должна сравниваться именно с D2. А относительная ссылка на первую ячейку нашей выделенной области E5 предписывает программе начать именно с этой позиции и последовательно двигаться вниз по столбцу, сравнивая количество с пороговым значением 100.
Как обычно, выбираем цвет заливки в случае выполнения условия.
Аналогичным образом для E5:E24 создаем второе правило
В результате часть столбца окрасится зелёным, часть — жёлтым, а количество между 50 и 100 останется неокрашенным.
А теперь давайте усложним задачу — закрасим цветом не отдельные ячейки, а строки таблицы целиком. Для этого нам всего лишь понадобится изменить несколько ссылок в наших правилах.
Прежде всего, заново обозначим диапазон условного форматирования. Теперь это будет $A$5:$G$24.
В правило форматирования внесем небольшое изменение:
Как видите, у нас появилась абсолютная ссылка на столбец E. А на строку ссылка осталась относительной, без знака $. Для программы это означает, что нужно использовать данные строки целиком, и окрасить ее тоже всю, а не отдельную ячейку.
Аналогично второе условие мы меняем с E5<$D$1 на $E5<$D$1.
В то же время ссылка на D2 так и остается абсолютной, поскольку условие записано именно в этой ячейке. В результате получаем «полосатую» таблицу, где цветом выделены уже целые строки. И вся хитрость заключается в грамотном использовании абсолютных ссылок в правилах.
Вывод. Давайте постараемся запомнить несложные принципы использования ссылок в правилах:
- если сравниваются попарно 2 столбца, то используют относительные ссылки (M2>B2).
- если значения в столбце сопоставляются с определённой ячейкой, то на нее обязательно должна быть абсолютная ссылка ($D$1).
- когда нужно закрасить по условию строку целиком, то ссылка на эту строку должна быть относительной ($E5)
- когда нужно закрасить столбец целиком, то ссылка на него должна быть относительной (E$5)
Как использовать в правилах ссылку на соседние листы?
В последних версиях начиная с 2010 года, в формулах условия вы можете спокойно использовать ссылки на данные с других листов. Делается это точно так же, как и в обычных формулах.
В более ранних версиях программы – 2007 и 2003, это ограничение можно легко обойти, использовав именованные диапазоны. Вы просто присваиваете определенные имена диапазонам на текущем или на соседних листах, а затем используете эти имена в функциях.
В частности, вместо
можно работать по формуле
Как вы понимаете, диапазон ‘Formatting (Лист2)’!$E$2:$E$21 получил имя «продажи» и теперь к нему можно обратиться из любого места вашей рабочей книги.
Приоритет выполнения правил — это важно!
При использовании условного форматирования в Excel вы не ограничены только одним правилом на ячейку. Вы можете применять столько правил, сколько требует логика вашего проекта. В том случае, если в вашей таблице используется несколько правил, то важно, в каком порядке они выполняются.
Если выбрать меню «Управление правилами» и указать там «Текущий лист», то вы увидите список имеющихся правил.
В этой таблице мы хотим выделить желтым цветом предстоящие в недалёком будущем отгрузки, а вот те из них, которые должны произойти сегодня или завтра, обозначить красным. Ведь к ним должно быть повышенное внимание и их нужно срочно выполнить.
Сначала создадим первое условие:
Как видим, сюда попадают все строки, в которых дата отгрузки больше текущей даты, записанной в ячейке C2.
Затем создаем второе условие, которое как бы будет являться подмножеством первого. Выделяем только ячейки, в которых ИЛИ дата отгрузки равна текущей $E$5=$C$2, ИЛИ дата отгрузки больше текущей на 1 день $E5-$C$2=1. Если хотя бы одно из этих требований выполняется, то строка будет закрашена красным.
Важно! Правила, расположенные выше в списке, имеют более высокий приоритет (1 и 2 на рисунке вверху). Новые правила всегда добавляются в начало списка и по этой причине имеют более высокий приоритет. Результат их работы не может быть изменен действием предшествующих правил, расположенных ниже.
Однако, порядок выполнения всегда можно изменить в этом же окне при помощи стрелок «Вверх» и «Вниз» (3).
Как редактировать условное форматирование?
Для того, чтобы изменить ранее созданное условие, нужно в первую очередь посмотреть, какие условия мы применяем к таблице и далее просто выбрать нужное правило. Последовательность действий та же, что мы рассмотрели чуть выше. Но на всякий случай еще раз повторю ее на скриншоте: нам нужен раздел «Управление правилами», затем указать, что рассматриваем текущий лист.
При нажатии иконки «Изменить. » мы попадаем в уже знакомое нам меню создания правила. Только все поля там уже заполнены текущими значениями. Остается только изменить то, что необходимо, и нажать «Ок».
А если забыл, где какие правила создавал?
В виду того, что этот способ имеет приоритет над обычным оформлением, вы можете получить внешний вид таблицы не совсем таким, как ожидали. Особенно, если забудете, где и какие правила создавали. Итак, как нам быстро найти в таблице все ячейки с условным форматированием?
Один их простых способов обнаружить такие нестандартные места таблицы – использовать меню Главная – Найти и выделить – . в последних версиях Excel. Или же Главная – Редактирование – Найти и выделить – . в более ранних версиях.
Но в результате вы просто увидите те области таблицы, в которых применено условное форматирование. И не более того. Какие именно там условия изменения оформления — пока неизвестно. В любом случае вам, скорее всего, придется копать глубже и разбираться, какие же условия там применены.
Поэтому лучше всего просто выберите раздел «Управление правилами» — текущий лист. Этот процесс мы уже дважды описывали в предыдущих разделах, поэтому, думаю, проблем здесь не возникнет.
Вы увидите все созданные вами правила, а также приоритет их выполнения. Напомним, что наивысший приоритет имеют правила, находящиеся в начале списка: чем выше, тем важнее. Также указаны области, к которым применяются созданные форматы. Думаю, здесь разобраться будет совершенно несложно.
Как можно скопировать условное форматирование?
Вот несколько способов для копирования правил.
Копировать формат по образцу
Можно скопировать так же, как и обычный формат.
На вкладке «Главная» в самом начале ленты расположена группа «Буфер обмена». В ней вы видите пиктограмму кисти – формат по образцу (в разных версия выглядит по-разному, но называется одинаково). Клик по ней копирует не только формат выделенных ячеек, но и условия для него, если таковые имеются. Следующим действием необходимо выделить те ячейки, в которые данное оформление необходимо перенести.
Имейте в виду, что описанный способ перенесет абсолютно все форматы, в том числе и установленные вручную.
Копирование через вставку.
Альтернативным вариантом дублировать формат является специальный способ вставки.
Скопируйте ячейки с нужным условным форматом любым привычным для вас способом. Выделите диапазон, на который требуется перенести формат (можете выделить и не смежные, зажав клавишу CTRL), а затем по щелчку правой кнопки мыши выберите пункт «Специальная вставка…». Тогда программа отобразит окно, где потребуется установить переключатель на точке «форматы», после чего нажать «OK».
Управление правилами.
Можно воспользоваться диспетчером правил.
Пройдите по следующему пути: -> «Управление правилами…».
Из раскрывающего списка «Показать правила. » выберите пункт «Этот лист». Вы сможете увидеть все правила, которые действуют на текущем листе.
В столбце списка правил «Применяется к» указаны диапазоны, на которые распространяется каждое правило. Допишите в это поле через точку с запятой нужные адреса ячеек, чтобы применить и к ним ранее созданные условия.
Данный способ более трудоемкий, чем предыдущие два. Но его прелесть в том, что он позволяет распространять только нужные правила. Это особенно полезно тогда, когда к копируемым ячейкам применяется несколько условий одновременно, а скопировать нужно только одно из них.
Как убрать условное форматирование?
Эта операция такая же несложная, как и создание правила. Выберите , и затем – «Удалить правила». Вам будет предложено либо удаление из выделенного диапазона данных, либо вовсе всех правил на листе. Но имейте в виду, что при этом вы удалите всё, что было ранее создано. А ведь, возможно, что-то вы хотели бы сохранить.
Поэтому существует и более тонкий инструмент, которым мы рекомендовали бы пользоваться и для редактирования, и для их удаления.
Используйте последний пункт выпадающего меню: «Управление правилами».
Здесь вы видите все правила на текущем листе, к каким диапазонам они относятся и что делают. Поэтому гораздо проще выбрать определенное правило и удалить его.
Либо изменить, если в этом есть необходимость.
Почему не работает?
Если вы не получаете ожидаемого результата, то в первую очередь следует убедиться, верно ли работает созданное вами правило условного форматирования. Для этого вы можете скопировать формулу из правила в любую пустую ячейку и посмотреть, какой результат будет получен. Если вы форматируете по условию целый столбец цифр, то выберите пустое место справа от вашей таблицы.
Если результатом выполнения формулы-условия будет ИСТИНА, значит, должно быть применено условное форматирование. Естественно, если ЛОЖЬ, то — нет. Давайте вернемся в одной из наших задач и выполним такую отладку правил форматирования.
В столбец I скопируем формулу первого условия, в K — второго. Зацепите мышкой правый нижний уголок ячейки с формулой и протащите ее вниз на всю высоту таблицы. Получим полную картину для каждой из ячеек нашего диапазона. Как видите, ИСТИНА и ЛОЖЬ точно соответствуют закраске столбца K, который мы, собственно, и проверяли. В I2 мы получили ИСТИНА, поэтому цвет — зелёный. В J9 ответ также положительный, поэтому цвет — желтый. И так далее.
Если формула сложная, можно разбить ее на части и применить тот же метод отладки.
Надеемся, что вы нашли ответы на интересующие вас вопросы по условному форматированию в нашей инструкции.
Тем не менее, если всё же что-то не получается или не работает – пишите в комментариях ниже. Мы постараемся вам ответить либо даже сделаем отдельный материал, посвященный вашей проблеме.