Как применять условное форматирование в google таблицах - компьютер - cправка

ПОПАРНОЕ СРАВНЕНИЕ СТРОК/ СТОЛБЦОВ (ОТНОСИТЕЛЬНЫЕ ССЫЛКИ)

Теперь будем производить попарное сравнение значений в строках 1 и 2.

Задача3 . Сравнить значения ячеек диапазона A 1: D 1 со значениями из ячеек диапазона A 2: D 2 . Для этого будем использовать относительную ссылку.

введем в ячейки диапазона A2:D2 числовые значения (можно считать их критериями);
выделим диапазон A1:D1 ;
применим к выделенному диапазону Условное форматирование на значение Меньше ( Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше )
в левом поле появившегося окна введем относительную ссылку на ячейку A2 (т.е. просто А2 или смешанную ссылку А$2 ). Убедитесь, что знак $ отсутствует перед названием столбца А.

Теперь каждое значение в строке 1 будет сравниваться с соответствующим ему значением из строки 2 в том же столбце! Выделены будут значения 1 и 5, т.к. они меньше соответственно 2 и 6, расположенных в строке 2.

Результат можно увидеть в файле примера на листе Задача3 .

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

Примечание-отступление : О важности фиксирования активной ячейки при создании правил Условного форматирования с относительными ссылками

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

СОВЕТ : Чтобы узнать адрес активной ячейки (она всегда одна на листе) можно посмотреть в поле Имя (находится слева от Строки формул ). В задаче 3, после выделения диапазона A1:D1 (клавиша мыши должна быть отпущена), в поле Имя , там будет отображен адрес активной ячейки A1 или D 1 . Почему возможно 2 вырианта и в чем разница для правил условного форматирования?

Посмотрим внимательно на второй шаг решения предыдущей задачи3 — выделение диапазона A 1: D 1 . Указанный диапазон можно выделить двумя способами: выделить ячейку А1 , затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь вправо к D1 ; либо, выделить ячейку D1 , затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь влево к А1 . Разница между этими двумя способами принципиальная: в первом случае, после завершения выделения диапазона, активной ячейкой будет А1 , а во втором D 1 !

Теперь посмотрим как это влияет на правило условного форматирования с относительной ссылкой.

Если мы выделили диапазон первым способом, то, введя в правило Условного форматирования относительную ссылку на ячейку А2 , мы тем самым сказали EXCEL сравнивать значение активной ячейки А1 со значением в А2 . Т.к. правило распространяется на диапазон A 1: D 1 , то B 1 будет сравниваться с В2 и т.д. Задача будет корректно решена.

Если при создании правила Условного форматирования активной была ячейка D1 , то именно ее значение будет сравниваться со значением ячейки А2 . А значение из A 1 будет теперь сравниваться со значением из ячейки XFB2 (не найдя ячеек левее A 2 , EXCEL выберет самую последнюю ячейку XFD для С1 , затем предпоследнюю для B 1 и, наконец XFB2 для А1 ). Убедиться в этом можно, посмотрев созданное правило:

выделите ячейку A1 ; нажмите Главная/ Стили/ Условное форматирование/ Управление правилами ; теперь видно, что применительно к диапазону $A$1:$D$1 применяется правило Значение ячейки 6 (задан формат: красный фон) и Значение ячейки >7 (задан формат: зеленый фон), см. рисунок выше. Т.к. правило Значение ячейки >6 (задан формат: красный фон) располагается выше, то оно имеет более высокий приоритет, и поэтому ячейка со значением 9 будет иметь красный фон

На Флажок Остановить, если истина можно не обращать внимание, он устанавливается для обеспечения обратной совместимости с предыдущими версиями EXCEL, не поддерживающими одновременное применение нескольких правил условного форматирования. Хотя его можно использовать для отмены одного или нескольких правил при одновременном использовании нескольких правил, установленных для диапазона (когда между правилами нет конфликта)

Подробнее можно ]]>прочитать здесь ]]> .

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

Формат Excel файлов

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

Как установить формат ячеек в Excel разберем на примере сравнения двух столбцов – количества запланированной к выпуску продукции и реально выпущенной.

Отметим красным те строки, где план в 2000 единиц выполнен не был. Для этого необходимо указать критерий для установки стиля. Щелкнем по ячейке C2 – она первая и хранит информацию о выпущенной продукции. Условное форматирование выполняется с помощью одноименного значка на панели инструментов.

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

При выборе откроется окно, в котором можно установить значение и выбрать цвета.

Введем граничный параметр 2000 и выберем красный цвет. Жмем «ОК». Ячейка станет цветной, так как значение в ней меньше заданного.

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

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

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

Допустим, ваше правило условного форматирования только в строке А2:С2. Чтобы скопировать его на ячейки ниже, выделите строчку А2:С2, выберите “копировать”

Далее выделите строки, на которые нужно перенести правило

И вставьте, кликнув на выбранных ячейках правой кнопкой, используя “специальная вставка”-> “вставить только условное форматирование” (либо вставьте через панель меню, эти же пункты). Значения не поменяются, на выделенные ячейки перенесется только правило.

Либо альтернативный вариант: откройте правила условного форматирования и исправьте диапазон (допустимы и несколько разрозненных диапазонов)

Как менять цвет ячейки по цвету другой ячейки

 если заданный текстЧтобы упростить контроль выполнения и выбранный цвет rule). ввод даты и от внешних факторов,Dim vValueнапример надо выделить ячейку к примеру столбцам) используем вложенную

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

 (в нашем случае заказа, мы можем устраивает, то жмёмВ появившемся диалоговом окне есть событие, на т.е. не известно,Private Sub Workbook_SheetChange(ByVal число «12».

 красного цвета, в формулу МЕСЯЦ(СЕГОДНЯ()), Которая заказа. Для этого: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». ГОД и СЕГОНЯ. вычислять в отдельном но нам потребуется Если Вы ищите нескольких правил форматирования это «Due in»)

 выделить в нашейОК

Создание правила форматирования которое можно запускать какие ячейки окрасятся

 Sh As Object,выделяете ячейку или противном случае окрашиваем в соответствии с

Выделите диапазон ячеек A2:A15 А в появившемся Во втором аргументе диапазоне суммы для

 только это ее для своих данных можно использовать функции будет найден. таблице различными цветами, чтобы увидеть созданное(New Formatting Rule) макрос. завтра, послезавтра ByVal Target As диапазон. ее в синий условиями возвращает номер (то есть список

 окне «Создание правила указан номер месяца каждого цвета (реализовать свойство).

 что-то другое, дайтеИПодсказка: строки заказов с правило в действии.Теперь, выбираем вариант

Этот макрос окраситПодробно: Range)топнули правой мышкой. цвет. текущего месяца в номеров клиентов) и форматирования» выберите опцию: (-1). Отрицательное число не сложно, ноПримечание:

 нам знать, и(AND),Если в формуле разным статусом доставки, если значение вИспользовать формулу для определения и эту ячейку

Имеются две одинаковыеIf Target.Interior.Color =Формат ячеек =>EA_

 текущем году. От выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное

 «Использовать формулу для значит, что нас у каждого пользователяМакрофункции — это набор вместе мы обязательноИЛИ используется условие « информация о котором столбце форматируемых ячеек в зеленый цвет таблицы 10 столбцов 65535 Then MsgBox Число => все: Формат — условное вычисленного формулой номера форматирование»-«Создать правило». А определения форматируемых ячеек» интересует какой был свои требования: ячейки функций к EXCEL что-нибудь придумаем.(OR) и объединить>0 содержится в столбцеQty.(Use a formula и другую. 2000 строк. В «жёлтый» форматы =>основной форматирование месяца отнимаем число в появившемся окне

В поле ввода введите месяц в прошлом с суммами должны 4-й версии, которыеУрок подготовлен для Вас таким образом нескольких«, то строка будетDeliveryбольше to determine whichНо скорее всего каждом столбце отмечаетсяEnd SubОчищаем окошко «Тип»Guest 4, то есть «Создание правила форматирования» формулу: времени. Пример условий быть в определенном нельзя напрямую использовать командой сайта office-guru.ru условий в одном выделена цветом в:4 cells to format), подойдет условное форматирование. определенный вид работы.

на скорую руку и записываем: Он не совсем в случаи Ноября выберите опцию: «ИспользоватьЩелкните на кнопку «Формат» для второго аргумента месте, необходимо учесть

 цветом будут выделены месяц (январь) в пр.). в качестве Именованной формулы.Просуммируем значения ячеек в течение 1 и текст, вне зависимостиDue in X DaysКак видите, изменять в следующая формула является надо получить. зеленым цветом иИзменение цвета не: Через функцию ЕСЛИ во втором число – только на поле ввода введите ячейки актуального месяца. году указанном вДопустим, что одним из

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

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

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

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

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

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

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

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

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

Копировать формат по образцу

Условное форматирование это всего лишь обычный формат, поэтому может быть скопирован так же.

На вкладке «Главная» в самом начале ленты расположена группа «Буфер обмена». В ней Вы найдете пиктограмму кисти – формат по образцу (в разных версия выглядит по-разному, но называется одинаково). Клик по ней копирует не только формат выделенных ячеек, но и условия для него, если таковые имеются. Следующим действием необходимо выделить те ячейки, в которые данное форматирование необходимо перенести.

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

Копирование через вставку

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

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

Также можно выбрать пункт «Специальная вставка…». Тогда приложение Excel отобразит окно, где потребуется установить переключатель на радиокнопке «форматы», после нажать «OK».

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

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

Перенести условия формата можно через диспетчер правил условного форматирования.

Пройдите по следующему пути: Вкладка «Главная» -> Группа «Стили» -> «Условное форматирование» -> «Управление правилами…». Последний пункт отобразит окно:

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

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

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

Примеры

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

=ЕСЛИ(D2>89;»A»;ЕСЛИ(D2>79;»B»;ЕСЛИ(D2>69;»C»;ЕСЛИ(D2>59;»D»;»F»))))

Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике:

Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A.
Если тестовых баллов больше 79, учащийся получает оценку B.
Если тестовых баллов больше 69, учащийся получает оценку C.
Если тестовых баллов больше 59, учащийся получает оценку D.
В противном случае учащийся получает оценку F.

Этот частный пример относительно безопасен, поскольку взаимосвязь между тестовыми баллами и буквенными оценками вряд ли будет меняться, так что дополнительных изменений не потребуется. Но что если вам потребуется разделить оценки на A+, A и A– (и т. д.)? Теперь ваши четыре условных оператора ЕСЛИ нужно переписать с учетом 12 условий! Вот так будет выглядеть ваша формула:

=ЕСЛИ(B2>97;»A+»;ЕСЛИ(B2>93;»A»;ЕСЛИ(B2>89;»A-«;ЕСЛИ(B2>87;»B+»;ЕСЛИ(B2>83;»B»;ЕСЛИ(B2>79;»B-«; ЕСЛИ(B2>77;»C+»;ЕСЛИ(B2>73;»C»;ЕСЛИ(B2>69;»C-«;ЕСЛИ(B2>57;»D+»;ЕСЛИ(B2>53;»D»;ЕСЛИ(B2>49;»D-«;»F»))))))))))))

Она по-прежнему работает правильно и работает правильно, но на написание и проверку нужно много времени, чтобы убедиться, что она работает правильно. Еще одна наиболее взглялая проблема в том, что вам приходилось вручную вводить оценки и эквивалентные буквы оценок. Какова вероятность случайного опечатки? Теперь представьте, что вы пытаетесь сделать это 64 раза с более сложными условиями! Конечно, это возможно, но действительно ли вы хотите обучебиться с такого рода усилиями и возможными ошибками, которые будет трудно обнаружить?

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

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

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» — «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» — «Правила выделения ячеек» — «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

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

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

Выделите первую ячейку диапазона с условным форматированим.
Откройте меню инструмента, нажмите «Управление правилами».

В открывшемся окне видно, какое правило и к какому диапазону применяется.

Как применить условное форматирование в Excel 2016, 2013, 2010, 2007?

Все нужные нам составляющие находятся в разделе меню «Условное форматирование» в категории «Стили» на вкладке «Главная».

Вот какие опции тут доступны:

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

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

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

Итак, первое правило: если значение ячейки меньше нуля, ее фон должен окрашиваться в красный. Реализуем это на практике. Выбираем на ленте опцию «Условное форматирование» -> «Правила выделения ячеек» -> «Меньше».

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

Создадим еще одно правило: если значение ячейки больше 0, окрашиваем ее в желтый цвет. На этот раз выберем правило «Больше».

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

Теперь вводим числа в указанный диапазон и созерцаем результат.

СРАВНЕНИЕ СО ЗНАЧЕНИЕМ В ЯЧЕЙКЕ (АБСОЛЮТНАЯ ССЫЛКА)

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

Задача2 . Сравним значения из диапазона A1:D1 с числом из ячейки А2 .

введем в ячейку А2 число 4;
выделим диапазон A1:D1 ;
применим к выделенному диапазону Условное форматирование на значение Меньше ( Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше );
в левом поле появившегося окна введем ссылку на ячейку A2 нажав на кнопочку, расположенную в правой части окна (EXCEL по умолчанию использует абсолютную ссылку $А$2 ).

В результате, все значения из выделенного диапазона A 1: D 1 будут сравниваться с одной ячейкой $А$2 . Те значения из A 1: D 1 , которые меньше A 2 будут выделены заливкой фона ячейки.

Результат можно увидеть в файле примера на листе Задача2 .

Чтобы увидеть как настроено правило форматирования, которое Вы только что создали, нажмите Главная/ Стили/ Условное форматирование/ Управление правилами ; затем дважды кликните на правиле или нажмите кнопку Изменить правило . В результате увидите диалоговое окно, показанное ниже.