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

Содержание:

Расширенное форматирование списка

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

Создание столбца ИСТИНА/ЛОЖЬ

Для этого нужно использовать колонку справа от полей с флажками. Флажок будет возвращать ИСТИНА (если галочка установлена) или ЛОЖЬ (если она снята). Таким образом вы сможете увидеть, все ли флажки сняты.

Правой кнопкой мыши нажмите на первое поле флажка и выберите «Формат объекта».

На вкладке «Элемент управления» в окне «Формат объекта» нажмите на кнопку выбора ячейки с правой стороны поля «Связь с ячейкой».

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

Общее число предметов и подсчет отмеченных предметов в списке

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

или

в ячейку справа от ячейки «Упакованные предметы» и нажмите Enter. Как показано в примере ниже, функция подсчитает число ячеек в колонке С (с С2 по С8), имеющих значение ИСТИНА или TRUE.

Скрыть столбец ИСТИНА/ЛОЖЬ

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

Как проверить, все ли галочки сняты

Для этого выберите ячейку «Я готов» и введите

или

Если число в ячейке В10 совпадет со значением подсчитанных флажков в ячейке В11, в ней автоматически отобразится «Да», в противном случае появится «Нет».

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

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

Создайте правило, открыв «Главная» → «Условное форматирование» → «Создать правило» → «Диспетчер правил условного форматирования» → «Использовать формулу для определения форматируемых ячеек». Введите

в поле «Форматировать значения, для которых следующая формула является истинной:». Замените значения В10 и В11 адресами ячеек «Общее количество предметов» и «Упакованные предметы», если это не одни и те же ячейки.

Создайте еще одно правило, но в поле «Форматировать значения, для которых следующая формула является истинной:» введите формулу

Затем нажмите «Формат», выберите цвет и нажмите «ОК».

В окне «Диспетчер правил условного форматирования» введите адреса ячеек, которые должны быть заполнены цветом, в поле «Применяется к». Введите те же адреса для обоих правил. В нашем случае это =$B$13. Нажмите «ОК».

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

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

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

Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов – зеленым цветом, если выше 20 градусов – желтый, если выше 30 градусов – красным.

Для применения нескольких условий к одной ячейке выполним следующие действия:

Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

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

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

  1. Выделите ячейки, по которым требуется выполнить проверку. В нашем случае это диапазон B2:E9.
  2. На вкладке Главная нажмите команду Условное форматирование. Появится выпадающее меню.
  3. Выберите необходимое правило условного форматирования. Мы хотим выделить ячейки, значение которых Больше $4000.
  4. Появится диалоговое окно. Введите необходимое значение. В нашем случае это 4000.
  5. Укажите стиль форматирования в раскрывающемся списке. Мы выберем Зеленую заливку и темно-зеленый текст. Затем нажмите OK.
  6. Условное форматирование будет применено к выделенным ячейкам. Теперь без особого труда можно увидеть, кто из продавцов выполнил месячный план в $4000.

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

Гистограммы

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

  • градиентная;
  • сплошная.

Рассмотрим каждый из предложенных вариантов.

Градиентная заливка

  1. Первым делом необходимо выделить нужные строки и столбцы. Затем кликнуть на иконку «Условное форматирование». После этого перейти в раздел «Гистограммы» и выбрать любую из предложенных заливок.

К значениям по умолчанию относятся:

  • зеленая;
  • красная;
  • оранжевая;
  • голубая;
  • фиолетовая.

При наведении на каждый из вариантов вам будет доступен предварительный просмотр.

Сплошная заливка

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

Цвета используются те же самые.

Другие правила

Здесь вы сможете настроить:

стиль;

минимальное и максимальное значение;

внешний вид столбца.

Образец того, что вы настроили, можно увидеть в правом нижнем углу.

Создание правил

Выше мы говорили о правилах, которые уже установлены в программе Excel, и пользователь может просто выбрать любое из них. Но, кроме того, при желании, пользователь может создавать свои правила.

Для этого, нужно нажать в любом подразделе меню условного форматирования на пункт «Другие правила…», расположенный в самом низу списка». Или же кликнуть по пункту «Создать правило…», который расположен в нижней части основного меню условного форматирования.

Открывается окно, где нужно выбрать один из шести типов правил:

  1. Форматировать все ячейки на основании их значений;
  2. Форматировать только ячейки, которые содержат;
  3. Форматировать только первые и последние значения;
  4. Форматировать только значения, которые находятся выше или ниже среднего;
  5. Форматировать только уникальные или повторяющиеся значения;
  6. Использовать формулу для определения форматируемых ячеек.

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

Ограничения Таблиц Excel

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

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

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

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Множество других секретов Excel вы найдете в онлайн курсе.

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

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

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

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

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

Правила в условном форматировании. Написание формул

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

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

Начнем с простой формулы. Перейдем в Условное форматирование — Управление правилами

В открывшемся окне жмем Создать правило, затем находим самый нижний пункт Использовать формулу для…

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

Окно изменения правил в условном форматировании

Жмем ОК и возвращаемся в Диспетчер правил. Здесь уже мы видим список созданных условий:

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

Кстати, формулу можно написать и проверить в Excel заранее. Например, так:

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

Зачастую необходимо изменить форматирование по правилу во всей строке таблицы.

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

Важно добавить

— Как вы заметили по моим правилам, я не использую абсолютные ссылки на диапазон при условии. Если вы делаете условие в диапазоне построчно, то номера строк нельзя делать абсолютными, т.е. ставить знак $

— В написании правил нельзя ссылаться на другие листы.

— Не забудьте, что если работаете с датами и временем, то они воспринимаются Excel’ем как число.

Применить условное форматирование для каждой строки в Excel

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

Метод A Изменение применимости к в диспетчере правил условного форматирования

Например, здесь вы хотите применить цвет фона заливки к ячейкам, если A2> B2, A3> B3,…, An> Bn с условным форматированием.

1. Во-первых, примените условное форматирование к A2: B2. Выберите A2: B2, затем щелкните Главная > Условное форматирование > Новое правило. Если в строке уже есть правила, просто переходите к шагу 4.

2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. от Выберите тип правила раздел, затем введите = $ A2> $ B2 в текстовое поле под Формат значений, где эта формула истинна.

3. Нажмите Формат кнопку, чтобы перейти к Формат ячеек диалоговое окно, а затем вы можете выбрать нужный тип форматирования. Например, заливка цветом фона. Нажмите OK > OK закрыть диалоги.

Теперь к строке A2: B2 применяется условное форматирование.

4. Не снимая выделения с A2: B2, нажмите Главная > Условное форматирование > Управление правилами.

5. в Диспетчер правил условного форматирования найдите правило, которое вы применили к A2: B2, измените диапазон, как вам нужно, в Относится к раздел и нажмите OK.

Затем это правило условного форматирования будет применяться к каждой строке в новом диапазоне.

Метод B Перетащите маркер автозаполнения

Если вы используете Excel 2013 или более поздние версии, вы можете использовать дескриптор автозаполнения, чтобы применить условное правило к соседним строкам.

Предположим, в строке A2: B2 применено два условного форматирования, если A2> B2, заливка красным цветом фона, если A2

Теперь вы хотите применить эти два правила к A3: B9.

1. Во-первых, вам нужно применить правила условного форматирования к строке A2: B2. Если в строке есть правила, просто перейдите к шагу 4. Выберите диапазон A2: B2, щелкните Главная > Условное форматирование > Новое правило. Если в строке уже есть правила, просто переходите к шагу 4.

2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки нужно отформатировать, из Выберите тип правила раздел, затем введите = $ A2> $ B2 в текстовое поле под Формат значений, где эта формула истинна. Затем нажмите Формат кнопку в Формат ячеек диалога под Заполнять на вкладке выберите красный цвет. Нажмите OK > OK.

3. Не снимая выделения с строки A2: B2, включите Диалоговое окно «Новое правило форматирования» снова выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. от Выберите тип правила раздел, затем введите = $ A2 <$ B2 в текстовое поле под Формат значений, где эта формула истинна. Затем нажмите Формат кнопку в Формат ячеек диалога под Заполнять на вкладке выберите зеленый цвет. Нажмите OK > OK.

4. Затем перетащите Автозаполнение дескриптор соседних строк, к которым вы хотите применить условное правило, затем выберите Только форматирование заливки из Параметры автозаполнения.

Другие операции (статьи), связанные с форматированием Conditioanl

Подсчет / суммирование ячеек по цветам с условным форматированием в ExcelТеперь это руководство расскажет вам о некоторых удобных и простых методах быстрого подсчета или суммирования ячеек по цвету с условным форматированием в Excel.

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

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

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

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

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

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

В данном примере будут выделены все ячейки в диапазоне B2:B5, числа в которых больше, чем число, записанное в ячейке C1. То есть вы сможете оперативно поменять число в C1, если решите изменить критерий выделения ячеек, и не придется менять само правило условного форматирования.

Как удалить лишние условия условного форматирования из нашего листа?

Для начала их нужно отобразить:
● откройте лист, с которым будете работать
● выделите все ячейки, выберите любую ячейку и нажмите сочетание клавиш CTRL+A (иногда CTRL+A потребуется нажать несколько раз), либо просто нажмите в левый верхний угол листа

● ячейки выделены, теперь откройте правила условного форматирования, либо правой кнопкой мыши на любой ячейке, пункт “условное форматирование” либо “формат” -> “условное форматирование” в панеле меню.

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

Для удаления — просто кликните на корзину.

Условное форматирование для сравнения двух столбцов.

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

Как найти и закрасить совпадающие ячейки в столбцах.

Можно использовать специальный пункт вкладки «Условное форматирование» — «Повторяющиеся значения».

На рисунке вы видите, что дубликаты выделены зеленым. Думаю, здесь все довольно просто.

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

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

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

Выберите, в каких ячейках вы будете отмечать совпадения – в первой или во второй таблице. Я выделил B3:B25. То есть, в первой таблице мы закрасим ячейки, которые дублируются во второй таблице.

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

Как найти и закрасить совпадения в нескольких столбцах.

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

Давайте закрасим цветом те ячейки в столбце B, которые хотя бы однажды встречаются в G,H и I.

Диапазон форматирования – B3:B25. Выделяем его и в меню – «Создать правило» выбираем «Использовать формулу…»

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

Последовательно двигаемся сверху вниз и сравниваем каждую ячейку колонки B с находящимися в той же горизонтали значениями в G,H и I.

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

Но если столбцов будет не 3, а, предположим, 10? Формула станет слишком громоздкой. Ведь придется указать 10 критериев совпадения.

Есть более простой способ. Изменим правило форматирования и используем функцию СЧЁТЕСЛИ:

СЧЁТЕСЛИ определяет, как часто определенное значение встречается в диапазоне. Считаем, сколько раз значение из B3 встречается в G,H и I таблицы, то есть в $G3:$I3. Если будет более одного совпадения, то срабатывает правило.Функция возвращает 1. А 1 в логическом выражении соответствует ИСТИНА, 0 — ЛОЖЬ. То есть, если счет равен нулю, то в текущей позиции нашего столбца содержится уникальное значение, которое больше нигде в диапазоне поиска не встречается. Согласитесь, так гораздо удобнее, чем писать множество однотипных критериев.

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

Вот это новое правило:

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

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

Тогда все будет работать.

Правила отбора первых и последних значений

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

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

Рассмотрим каждый из них.

Первые 10 элементов

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

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

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

Первые 10%

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

Если указать число 10 (оно используется по умолчанию), то вы увидите следующее.

Последние 10 элементов

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

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

Последние 10%

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

Выше среднего

Данный инструмент весьма удобен, когда нужно отсортировать информацию по отношению к ней самой. То есть редактор Excel сам посчитает среднее число среди выделенной информации и пометит всё то, что выше этой величины. Всё происходит автоматически.

Ниже среднего

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

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

Дополнительные возможности

Собственные формулы

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

  1. Откройте файл в Google Таблицах на компьютере.
  2. Выделите нужные ячейки.
  3. Нажмите Формат Условное форматирование.
  4. В раскрывающемся меню раздела «Форматирование ячеек» выберите Ваша формула. Если правило уже существует, выберите его или нажмите Добавить правило Ваша формула.
  5. Выберите Значение или формула, а затем добавьте формулу и правила.
  6. Нажмите Готово.

Примечание. Если формула должна ссылаться на текущий лист, используйте стандартный формат «(=’sheetname’!cell)». Чтобы формула указывала на ячейки с другого листа, примените функцию ДВССЫЛ (INDIRECT).

Пример 1

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

  1. Откройте файл в Google Таблицах на компьютере.
  2. Выберите диапазон, например ячейки с A1 по A100.
  3. Нажмите Формат Условное форматирование.
  4. В раскрывающемся меню раздела «Форматирование ячеек» выберите Ваша формула. Если правило уже существует, выберите его или нажмите Добавить правило Ваша формула.
  5. Укажите правило для первой строки. В данном случае оно будет выглядеть так: =СЧЁТЕСЛИ($A$1:$A$100;A1)>1.
  6. Выберите другие параметры форматирования.
  7. Нажмите Готово.

Пример 2

Чтобы отформатировать всю строку на основании значения в одной из ее ячеек:

  1. Откройте файл в Google Таблицах на компьютере.
  2. Выберите диапазон, например столбцы от A до E.
  3. Нажмите Формат Условное форматирование.
  4. В раскрывающемся меню раздела «Форматирование ячеек» выберите Ваша формула. Если правило уже существует, выберите его или нажмите Добавить правило Ваша формула.
  5. Укажите правило для первой строки. Например, вы можете выделить всю строку зеленым цветом, если в ячейках столбца B есть текст «Да». Для этого введите формулу =$B1=»Да».
  6. Выберите другие параметры форматирования.
  7. Нажмите Готово.

Абсолютные и относительные ссылки

Чтобы использовать абсолютные ссылки вместо относительных, добавляйте знаки доллара ($) перед буквами и числами в формулах.

Подстановочные знаки

Правила с подстановочными знаками позволяют охватить сразу несколько выражений. Такие знаки используются в полях «Текст содержит» и «Текст не содержит» раздела «Форматирование ячеек».

  • Вопросительный знак (?) заменяет любой одиночный символ. Например, по правилу «к?т» будут отформатированы ячейки, содержащие текст «кот», «кит» и т. д. Ячейки с текстом «кт» или «килт» будут пропущены.
  • Звездочка (*) заменяет один, два или несколько символов (может не заменять ни одного символа). Например, по правилу «к*т» будут отформатированы ячейки, содержащие текст «кот», «кт» и «килт». Ячейки с текстом «ко» или «тк» будут пропущены.
  • Если вам нужно отформатировать текст, содержащий вопросительный знак или звездочку, поставьте перед ними тильду (~). После этого нужный вам знак перестанет считаться подстановочным. Например, по правилу «к~?т» будут отформатированы ячейки, содержащие текст «к?т». Ячейки с текстом «кот» или «к~?т» будут пропущены.

Примечания

  • Чтобы убрать правило, нажмите на значок «Удалить» .
  • Если правил несколько, они применяются в порядке следования. Это означает, что первое выполняемое правило определит формат ячейки или диапазона. Чтобы изменить порядок следования, перетащите правило на нужную позицию.
  • Копируя данные из ячеек с условным форматированием, вы также копируете правила форматирования.
Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Adblock
detector