Что такое форматирование ячеек: Применение пользовательских форматов

Применение пользовательских форматов

Создание пользовательских форматов

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

Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.

В поле Тип вводится пользовательские форматы, варианты написания которых мы рассмотрим далее.

В поле Тип вы можете задать формат значения ячейки следующей строкой:

[цвет]»любой текст»КодФормата»любой текст»

Посмотрите простые примеры использования форматирования. В столбце А — значение без форматирования, в столбце B — с использованием пользовательского формата (применяемый формат в столбце С)

Какие цвета можно применять 

В квадратных скобках можно указывать один из 8 цветов на выбор:

Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.

Далее рассмотрим коды форматов в зависимости от типа данных. 

Числовые форматы

Символ	Описание применения	Пример формата	До форматирования	После форматирования
#	Символ числа. Незначащие нули в начале или конце число не отображаются	######	001234	1234
0	Символ числа. Обязательное отображение незначащих нулей	000000	1234	001234
,	Используется в качестве разделителя целой и дробной части	####,#	1234,12	1234,1
пробел	Используется в качестве разделителя разрядов	# ###,#0	1234,1	1 234,10

Форматы даты

Формат	Описание применения	Пример отображения
М	Отображает числовое значение месяца	от 1 до 12
ММ	Отображает числовое значение месяца в формате 00	от 01 до 12
МММ	Отображает сокращенное до 3-х букв значение месяца	от Янв до Дек
ММММ	Полное наименование месяца	Январь — Декабрь
МММММ	Отображает первую букву месяца	от Я до Д
Д	Выводит число даты	от 1 до 31
ДД	Выводит число в формате 00	от 01 до 31
ДДД	Выводит день недели	от Пн до Вс
ДДДД	Выводит название недели целиком	Понедельник — Пятница
ГГ	Выводит последние 2 цифры года	от 00 до 99
ГГГГ	Выводит год даты полностью	1900 — 9999

Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат «

ДД.ММ.ГГГГ» отформатирует дату в привычный нам вид 31.12.2017, а формат «ДД МММ» преобразует дату в вид 31 Дек.

Форматы времени

Аналогичные форматы есть и для времени.

Формат	Описание применения	Пример отображения
ч	Отображает часы	от 0 до 23
чч	Отображает часы в формате 00	от 00 до 23
м	Отображает минуты	от 0 до 59
мм	Минуты в формате 00	от 00 до 59
с	Секунды	от 0 до 59
сс	Секунды в формате 00	от 00 до 59
[ч]	Формат истекшего времени в часах	например, [ч]:мм -> 30:15
[мм]	Формат истекшего времени в минутах	например, [мм]:сс -> 65:20
[сс]	Формат истекшего времени в секундах	—
AM/PM	Для вывода времени в 12-ти часовом формате	например, Ч AM/PM -> 3 PM
A/P	Для вывода времени в 12-ти часовом формате	например, чч:мм AM/PM -> 03:26 P
чч:мм:сс.00	Для вывода времени с долями секунд

Текстовые форматы

Текстовый форматов как таковых не существует. Иногда требуется продублировать значение в ячейке и дописать в начало и конец дополнительный текст. Для этих целей используют символ @.

ДО форматирования	ПОСЛЕ форматирования	Примененный формат
Россия	страна — Россия	«страна — «@

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

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

1. Положительные числа
2. Отрицательные числа
3. Нулевые значения
4. Текстовый формат

Для этого мы можем в поле Тип указать следующую конструкцию:

Формат положительных значений ; отрицательных ; нулевых ; текстовых 

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

[цвет]»любой текст»КодФормата»любой текст»

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

Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает «нет данных».

Редактирование и копирование пользовательских форматов

Чтобы отредактировать созданный пользовательский формат необходимо:

1. Выделить ячейки, формат которых вы хотите отредактировать.
2. Открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
3. Изменить строку форматирования в поле Тип.

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

1. Использовать функцию копирования по образцу.
   
2. Выделить ячейки, открыть окно Формат ячеек, на вкладке Число в списке Все форматы выбрать нужный формат и нажать ОК.

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

 

Скачать

Форматирование ячеек в Excel — Информационные технологии

Уроки MS Excel

Часто во время использования табличного редактора возникают такие моменты, когда необходимо, чтобы конкретные колонки

Уроки MS Excel

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

Уроки MS Excel

Часто при работе с табличными документами появляется необходимость установки галочки в конкретном месте рабочей

Уроки MS Excel

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

Уроки MS Excel

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

Уроки MS Excel

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

Уроки MS Excel

Те, кто работал в текстовом редакторе MS Word, видели, как при неправильном написании слов

Уроки MS Excel

Фильтрация данных в Excel необходима для облегчения работы с таблицами и большим объемом информации.

Уроки MS Excel

Чтобы в Microsoft Office Excel найти значения ячейки, находящейся на пересечении столбика и строчки

Уроки MS Excel

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

Уроки MS Excel

Регистр в Microsoft Office Excel — это высота букв, их расположение в ячейках табличного

Уроки MS Excel

При работе с таблицами в Microsoft Office Excel часто требуется вставить строку или несколько

Уроки MS Excel

В Microsoft Office Excel, начиная с версии 2007 года, появилась возможность сортировки и фильтрации

Уроки MS Excel

Таблица с одинаковыми значениями — серьезная проблема для многих пользователей Microsoft Excel. Повторяющуюся информацию

Уроки MS Excel

Примечания в Microsoft Office Excel — это некоторая дополнительная информация, которую пользователь привязывает к

Уроки MS Excel

Составляя таблицы в Microsoft Office Excel, пользователь может увеличить размеры массива, чтобы расширить содержащуюся

Форматирование таблиц в Excel | Блог Александра Воробьева

Опубликовано 21 Окт 2014
Рубрика: Справочник Excel | 16 комментариев

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

Форматирование таблиц в Excel – это:

1. Установка формата чисел.

2. Настройка типа, вида, размера и цвета используемых шрифтов.

3. Установка необходимых размеров строк и столбцов.

4. Объединение (если необходимо) групп ячеек.

5. Добавление соответствующих внешних границ и цветов заливки для диапазонов ячеек.

6. Выполнение выравнивания записей относительно границ ячеек.

7. Защита записей от случайных изменений.

Многие из этих действий (но не все) выполняются при помощи кнопок панели инструментов MS Excel «Форматирование» и знакомы всем, даже начинающим пользователям. У меня, например, панель «Форматирование» выглядит так, как на скриншоте, расположенном ниже.

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

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

Пример форматирования таблицы.

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

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

1. Форматируем заголовок таблицы.

1.1. Выделяем область A1…E1, нажимаем на кнопку «Объединить и поместить в центре» на панели «Форматирование». Ячейки объединились в одну, текст заголовка расположился по центру.

1.2. Выбираем шрифт — «Arial», размер – «16», «Полужирный», «Подчеркнутый», цвет шрифта – «Фиолетовый».

2. Форматируем заголовки столбцов.

2.1. Выделяем ячейки A2 и B2, нажимаем на кнопку «Объединить ячейки».

2.2. Выделяем ячейки A7 и B7, нажимаем на кнопку «Объединить ячейки».

2.3. Выделяем ячейки A2 и B2, нажимаем и удерживаем на клавиатуре кнопку «Ctrl» и выделяем ячейки A7 и B7 – в результате имеем две выделенные объединенные ячейки.

2.4. Выбираем шрифт — «Arial Black», размер – «12», выравнивание – «По левому краю», цвет шрифта – «Лиловый».

2.5. Выделяем диапазон C2…E2, нажимаем и удерживаем на клавиатуре кнопку «Ctrl», выделяем область C7…E7.

2.6. Выбираем шрифт — «Arial Black», размер – «8», выравнивание – «По центру», цвет шрифта – «Лиловый».

2.7. Для уменьшения горизонтального размера таблицы расположим надписи в ячейках C2, C7, E2, E7 в двух строках. Для этого в ячейке C2 в слове «Обозначения» после буквы «а» ставим знак переноса «-» и нажимаем на клавиатуре сочетание клавиш «Alt» и «Enter». Вторая часть слова — «чения» — осталась в той же ячейке, но расположилась на строке ниже. Аналогично поступаем с записями в ячейках C7, E2, E7.

3. Форматируем записи.

3.1. Выделяем записи исходных данных и результата расчетов — диапазон A3…E6 и диапазон A8…E8.

3.2. Выбираем шрифт — «Arial», «11», «Полужирный», «По центру», «Синий».

3.3. Выделяем диапазон B3…B6 и ячейку B8 и выравниваем «По левому краю».

3.4. Выделяем диапазон A8…E8, изменяем цвет шрифта на «Красный».

3.5. Выделяем диапазон D3…D6 и делаем щелчок правой кнопки мыши. В выпавшем контекстном меню выбираем «Формат ячеек…». В открывшемся окне «Формат ячеек» настраиваем числовой формат исходных данных.

3.6. Выделяем ячейку D8 и так же, как в предыдущем пункте через окно «Формат ячеек» настраиваем числовой формат результата расчета, установив три знака после запятой.

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

4. Устанавливаем размеры строк и столбцов.

Устанавливаем курсор мыши на заголовок столбца «A» вблизи с границей столбца «B». Как только курсор мыши примет вид креста состоящего из вертикальной линии и горизонтальной разнонаправленной стрелки делаем двойной щелчок левой кнопкой. Ширина столбца «A» стала по размеру равной максимальной записи в столбце – 15 пиксель.

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

5. Оформляем границы.

5.1. Выделяем область A2…E8 и нажимаем на кнопку панели «Все границы» и следом, не снимая выделения, нажимаем на кнопку «Толстая внешняя граница».

5.2. Выделяем ячейки A2…E2 и нажимаем на кнопку панели «Форматирование» «Толстая внешняя граница». Аналогично обрамляем диапазон A7…E7.

6. Выполняем заливку диапазонов цветом.

6.1. Выделяем область D3…D6, выбираем цвет заливки — «Светло-бирюзовый». В таких ячейках хранятся исходные данные.

6.2. Выделяем ячейку D8, выбираем цвет заливки — «Светло-желтый». В этой ячейке выводится результат расчетов.

7. Выполняем защиту формул от случайных изменений.

7.1. Выделяем ячейку D8, щелкаем правой кнопкой мыши, выбираем в выпавшем окне «Формат ячеек» вкладку «Защита» и устанавливаем галочку напротив надписи «Защищаемая ячейка».

7.2. Переходим в главное меню MS Excel и выполняем: «Сервис» — «Защита» — «Защитить лист». Пароль можно не ставить. Ячейка от случайных изменений защищена. Защита от злоумышленников – это другая тема.

На этом примере мы подробно рассмотрели, как можно выполнить форматирование таблиц в Excel. Результат – на рисунке внизу.

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

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

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

Другие ст

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

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

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

По традиции, пример Вы можете скачать ниже по ссылке.

Перед тем, как перейти к настройке, выделим весь диапазон, к которому мы хотим применить условное форматирование.
В нашем примере выделим столбец С без шапки, то есть C2:C21.
Во вкладке Главная в группе Стили нажмём на Условное форматирование.

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

В выпадающем списке начнём с разбора первого способа выделения, Правила выделения ячеек и выберем Больше… .

Появится всплывающее окно, в котором укажем настройки.
Мы будем форматировать ячейки, которые больше 400.
Чтобы это реализовать, выделим их Зелёной заливкой и тёмно-зелёным текстом.
ОК.

Результат будет выглядеть следующим образом:

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

Разберём следующий способ условного форматирования — Правила отбора первых и последних значений.
И далее Первые 10 элементов… .

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

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

Гистограммы

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

В выпадающем списке Условного форматирования выберем Гистограммы.
Предлагаю взять сплошную оранжевую заливку.
Цвет заливки не принципиален, поэтому можете выбрать свой либо повторять все действия по уроку.

На изображении ниже, можете оценить как работает гистограмма.
Длина окрашенного столбца внутри ячейки зависит от значения ячейки.
Чем больше значение, тем больше величина закрашенного столбца.

Цветовые шкалы

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

Выберем правило Цветовая шкала «Зелёный — Белый».

Самое большее значение имеет ячейка с насыщенным зелёным цветом.
Чем значение меньше, тем цвет светлее.
И так вплоть до белого цвета заливки.

Наборы значков

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

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

Как Вы могли догадаться, зеленый имеет самое большое значение (>=67%).
Желтый — среднее (33%<=x<67%), и красный — самые маленькие значения (>33%).

Создать правило

Microsoft Excel предлагает много готовых инструментов выделения.
Так же, мы можем создать вручную своё правило.
Чтобы это сделать просто в выпадающем списке Условное форматирование выберем Создать правило… .

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

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

Правила выделения ячеек | Очистить правила | Правила сверху / снизу | Условное форматирование с формулами

Условное форматирование в Excel позволяет выделять ячейки определенным цветом в зависимости от значения ячейки.

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

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

1. Выберите диапазон A1: A10.

2. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

3. Щелкните Выделить правила ячеек, больше.

4. Введите значение 80 и выберите стиль форматирования.

5. Щелкните OK.

Результат. Excel выделяет ячейки, размер которых превышает 80.

6.Измените значение ячейки A1 на 81.

Результат. Excel автоматически изменяет формат ячейки A1.

Примечание: вы также можете использовать эту категорию (см. Шаг 3) для выделения ячеек, которые меньше значения, между двумя значениями, равными значению, ячеек, содержащих определенный текст, даты (сегодня, на прошлой неделе, в следующем месяце и т. Д.) .), повторяющиеся или уникальные значения.

Очистить правила

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

1. Выберите диапазон A1: A10.

2. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

3. Щелкните «Очистить правила», «Очистить правила из выбранных ячеек».

Правила сверху / снизу

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

1.Выберите диапазон A1: A10.

2. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

3. Щелкните Правила верхних / нижних, выше среднего.

4. Выберите стиль форматирования.

5. Щелкните OK.

Результат. Excel вычисляет среднее значение (42,5) и форматирует ячейки, превышающие это среднее значение.

Примечание: вы также можете использовать эту категорию (см. Шаг 3), чтобы выделить первые n элементов, верхние n процентов, нижние n элементов, нижние n процентов или ячейки, которые находятся ниже среднего.

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

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

1. Выберите диапазон A1: E5.

2. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

3.Щелкните Новое правило.

4. Выберите «Использовать формулу для определения ячеек для форматирования».

5. Введите формулу = ISODD (A1)

6. Выберите стиль форматирования и нажмите OK.

Результат. Excel выделяет все нечетные числа.

Объяснение: всегда записывайте формулу для верхней левой ячейки в выбранном диапазоне.Excel автоматически копирует формулу в другие ячейки. Таким образом, ячейка A2 содержит формулу = ISODD (A2), ячейка A3 содержит формулу = ISODD (A3) и т. Д.

Вот еще пример.

7. Выберите диапазон A2: D7.

8. Повторите шаги 2–4 выше.

9. Введите формулу = $ C2 = «США»

10. Выберите стиль форматирования и нажмите OK.

Результат.Excel выделяет все заказы в США.

Объяснение: мы исправили ссылку на столбец C, поместив символ $ перед буквой столбца ($ C2). В результате ячейки B2, C2 и ячейка D2 также содержат формулу = $ C2 = «USA», ячейки A3, B3, C3 и D3 содержат формулу = $ C3 = «USA» и т. Д.

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

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

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

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

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

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

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

Как создать правило условного форматирования с помощью формулы

Чтобы настроить правило условного форматирования на основе формулы в Excel 2019, Excel 2016, Excel 2013 и Excel 2010, выполните следующие действия:

1. Выберите ячейки, которые нужно отформатировать. Вы можете выбрать один столбец, несколько столбцов или всю таблицу, если хотите применить условный формат к строкам. Наконечник. Если вы планируете добавить больше данных в будущем и хотите, чтобы правило условного форматирования применялось к новым записям автоматически, вы можете:
   • Преобразование диапазона ячеек в таблицу ( Вставка вкладки> Таблица ). В этом случае условное форматирование будет автоматически применяться ко всем новым строкам.
   • Выберите несколько пустых строк под данными, скажем, 100 пустых строк.
2. На вкладке Главная в группе Стили щелкните Условное форматирование> Новое правило…
   
3. В окне Новое правило форматирования выберите Используйте формулу, чтобы определить, какие ячейки нужно форматировать .
4. Введите формулу в соответствующее поле.
5. Нажмите кнопку Формат… , чтобы выбрать собственный формат.
   
6. Переключение между шрифтом , границей и Заполните вкладки и поиграйте с различными параметрами, такими как стиль шрифта, цвет узора и эффекты заливки, чтобы настроить формат, который лучше всего подходит для вас. Если стандартной палитры недостаточно, щелкните Дополнительные цвета… и выберите любой цвет RGB или HSL по своему вкусу.Когда закончите, нажмите кнопку OK .
   
7. Убедитесь, что в разделе Preview отображается нужный формат, и если это так, нажмите кнопку OK , чтобы сохранить правило. Если вас не очень устраивает предварительный просмотр формата, нажмите кнопку Format… еще раз и внесите изменения.
   

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

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

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

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

Формулы для сравнения значений (числа и текст)

Как вы знаете, Microsoft Excel предоставляет несколько готовых к использованию правил для форматирования ячеек со значениями больше, меньше или равными указанному вами значению ( Условное форматирование> Правила выделения ячеек ). Однако эти правила не работают, если вы хотите условно отформатировать определенные столбцы или целые строки на основе значения ячейки в другом столбце. В данном случае вы используете аналогичные формулы:

Состояние	Пример формулы
Равно	= B2 = 10
Не равно	= B2 <> 10
Больше	= $ B2> 10
Больше или равно	= $ B2> = 10
Менее	= B2 <10
Меньше или равно	= B2 <= 10
Между	= И ($ B2> 5, $ B2 <10)

На снимке экрана ниже показан пример формулы Больше, чем , которая выделяет названия продуктов в столбце A, если количество товаров на складе (столбец C) больше 0.Обратите внимание, что формула применима только к столбцу A ($ A $ 2: $ A $ 8). Но если вы выберете всю таблицу (в нашем случае $ A $ 2: $ E $ 8), это выделит целые строки на основе значения в столбце C.

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

= $ A2 <$ B2 - форматировать ячейки или строки, если значение в столбце A меньше, чем соответствующее значение в столбце B.

= $ A2 = $ B2 - форматировать ячейки или строки, если значения в столбцах A и B совпадают.

= $ A2 <> $ B2 - форматировать ячейки или строки, если значение в столбце A не такое же, как в столбце B.

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

Формулы И и ИЛИ

Если вы хотите отформатировать таблицу Excel на основе 2 или более условий, используйте функцию = И или = ИЛИ:

Состояние	Формула	Описание
Если выполняются оба условия	= И ($ B2 <$ C2, $ C2 <$ D2)	Форматирует ячейки, если значение в столбце B меньше, чем в столбце C, и , если значение в столбце C меньше, чем в столбце D.
При выполнении одного из условий	= ИЛИ ($ B2 <$ C2, $ C2 <$ D2)	Форматирует ячейки, если значение в столбце B меньше, чем в столбце C, или , если значение в столбце C меньше, чем в столбце D.

На скриншоте ниже мы используем формулу = AND ($ C2> 0, $ D2 = "Worldwide") , чтобы изменить цвет фона строк, если количество товаров на складе (столбец C) больше чем 0, и если продукт доставляется по всему миру (столбец D).Обратите внимание, что формула работает с текстовыми значениями , а также с числами .

Естественно, вы можете использовать два, три или более условий в формулах = И и = ИЛИ.

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

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

Я думаю, что все знают, как форматировать пустые, а не пустые ячейки в Excel - вы просто создаете новое правило типа « Форматировать только ячейки, которые содержат» и выбираете либо Пробелы , либо Без пробелов .

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

Формула для пробелов : = $ B2 = "" - форматирование выбранных ячеек / строк, если соответствующая ячейка в столбце B пуста.

Формула для непустых значений : = $ B2 <> "" - форматирование выбранных ячеек / строк, если соответствующая ячейка в столбце B не пуста.

Примечание. Формулы выше будут работать для ячеек, которые «визуально» пусты или не пусты. Если вы используете какую-либо функцию Excel, которая возвращает пустую строку, например = if (false, "OK", "") , и вы не хотите, чтобы такие ячейки обрабатывались как пустые, используйте вместо них следующие формулы = isblank (A1) = true или = isblank (A1) = false для форматирования пустых и непустых ячеек соответственно.

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

• Оранжевые строки (ячейка в столбце B не пуста): = $ B2 <> ""
• Зеленые строки (ячейки в столбце B и столбце C не пусты): = AND ($ B2 <> "", $ C2 <> "")

Еще одна вещь, которую вам нужно сделать, это переместить второе правило вверх и установить флажок Остановить, если истинно рядом с этим правилом:

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

Формулы Excel для работы с текстовыми значениями

Если вы хотите отформатировать определенный столбец (столбцы), когда другая ячейка в той же строке содержит определенное слово, вы можете использовать формулу, описанную в одном из предыдущих примеров (например, = $ D2 = "Worldwide"). Однако это будет работать только для точного совпадения .

Для частичного совпадения необходимо использовать либо ПОИСК (без учета регистра), либо НАЙТИ (с учетом регистра).

Например, для форматирования выбранных ячеек или строк, если соответствующая ячейка в столбце D содержит слово « Worldwide », используйте приведенную ниже формулу. Эта формула найдет все такие ячейки, независимо от того, где в ячейке находится указанный текст, включая « Ships Worldwide », « Worldwide, за исключением… » и т. Д .:

= ПОИСК ("Весь мир", $ D2)> 0

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

= ПОИСК ("Весь мир", $ D2)> 1

Формулы Excel для выделения дубликатов

Если ваша задача состоит в условном форматировании ячеек с повторяющимися значениями, вы можете воспользоваться предопределенным правилом, доступным в разделе Условное форматирование> Правила выделения ячеек> Повторяющиеся значения… В следующей статье содержится подробное руководство по использованию этой функции: Как автоматически выделять дубликаты в Excel.

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

Выделить дубликаты, включая 1 ^st вхождений

= СЧЕТЕСЛИ ($ A $ 2: $ A $ 10, $ A2)> 1 - эта формула находит повторяющиеся значения в указанном диапазоне в столбце A (A2: A10 в нашем случае), включая первые вхождения.

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

Выделить дубликаты без 1 ^st вхождений

Чтобы игнорировать первое вхождение и выделять только последующие повторяющиеся значения, используйте эту формулу: = СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> 1

Выделить последовательные дубликаты в Excel

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

• Выберите столбец, в котором вы хотите выделить дубликаты, без заголовка столбца .
• Создайте правило (я) условного форматирования, используя следующие простые формулы:
  Правило 1 (синий): = $ A1 = $ A2 - выделяет 2 ^{и все последующие вхождения, если таковые имеются.
  Правило 2 (зеленый): = $ A2 = $ A3 - выделяет 1 событие st .}

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

Важно! Для правильной работы формул важно, чтобы Правило 1, выделяющее 2 ^и и все последующие повторяющиеся вхождения, было первым правилом в списке, особенно если вы используете два разных цвета.

Выделить повторяющиеся строки

Если вы хотите применить условный формат, когда повторяющиеся значения встречаются в двух или более столбцах, вам нужно будет добавить в таблицу дополнительный столбец, в котором вы объедините значения из ключевых столбцов, используя простую формулу, подобную этой = A2 & B2 .После этого вы применяете правило, используя любой вариант формулы СЧЁТЕСЛИ для дубликатов (с 1 ^st вхождений или без них). Естественно, вы можете скрыть дополнительный столбец после создания правила.

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

В этом примере, чтобы выделить повторяющиеся строки с первыми вхождениями , создайте правило со следующей формулой:
= COUNTIFS ($ A $ 2: $ A $ 11, $ A2, $ B $ 2: $ B $ 11, $ B2) > 1

Чтобы выделить повторяющиеся строки без первых вхождений , используйте эту формулу:
= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2, $ B $ 2: $ B2, $ B2)> 1

Сравнить 2 столбца на наличие дубликатов

Одна из наиболее частых задач в Excel - проверка двух столбцов на наличие повторяющихся значений - i.е. найти и выделить значения, которые существуют в обоих столбцах. Для этого вам нужно будет создать правило условного форматирования Excel для каждого столбца с комбинацией функций = ISERROR () и = MATCH () :

для столбца A: = ISERROR (MATCH (A1, $ B $ 1: $ B $ 10000,0)) = FALSE

для столбца B: = ISERROR (MATCH (B1, $ A $ 1: $ A $ 10000,0)) = FALSE

Примечание. Для правильной работы таких условных формул очень важно применять правила ко всем столбцам, например.г. = $ A: $ A и = $ B: $ B .

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

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

Формулы для выделения значений выше или ниже среднего

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

Например, вы можете использовать формулу = $ E2 для условного форматирования строк, в которых количество продаж ниже среднего, как показано на снимке экрана ниже. Если вы ищете обратное, то есть, чтобы затенить продукты, эффективность которых выше среднего, замените «<» на «>» в ​​формуле: = $ E2> AVERAGE ($ E $ 2: $ E $ 8) .


Как выделить ближайшее значение в Excel

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

Пример 1. Найдите ближайшее значение, включая точное совпадение

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

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

= МИН (АБС (B2: D13- (0)))

В приведенной выше формуле B2: D13 - это диапазон ячеек, а 0 - это число, для которого вы хотите найти ближайшее соответствие. Например, если вы ищете значение, наиболее близкое к 5, формула изменится на: = MIN (ABS (B2: D13- (5)))

Примечание. Это формула массива , поэтому вам нужно нажать Ctrl + Shift + Enter вместо простого ввода Enter, чтобы завершить его.

А теперь вы создаете правило условного форматирования со следующей формулой, где B3 - это верхняя правая ячейка в вашем диапазоне, а $ C $ 2 в ячейке с приведенной выше формулой массива:

= ИЛИ (B3 = 0- $ C $ 2, B3 = 0 + $ C $ 2)

Обратите внимание на использование абсолютных ссылок в адресе ячейки, содержащей формулу массива ($ C $ 2), поскольку эта ячейка является постоянной.Кроме того, вам нужно заменить 0 на число, для которого вы хотите выделить ближайшее совпадение. Например, если бы мы хотели выделить ближайшее к 5 значение, формула изменится на: = ИЛИ (B3 = 5- $ C $ 2, B3 = 5 + $ C $ 2)

Пример 2. Выделите значение, наиболее близкое к заданному, но НЕ точное соответствие

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

Например, следующая формула массива находит значение, наиболее близкое к 0 в указанном диапазоне, но игнорирует нули, если они есть:

= МИН (АБС (B3: C13- (0)) + (10 ^ 0 * (B3: C13 = 0)))

Не забудьте нажать Ctrl + Shift + Enter после того, как вы закончите вводить формулу массива.

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

= ИЛИ (B3 = 0- $ C $ 2, B3 = 0 + $ C $ 2)

Однако, поскольку наша формула массива в ячейке C2 игнорирует точное совпадение, правило условного форматирования также игнорирует нули и выделяет значение 0,003, которое является наиболее близким совпадением.


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

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

Почему условное форматирование в Excel работает неправильно?

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

1. Правильно используйте абсолютные и относительные адреса ячеек. Очень сложно вывести общее правило, которое будет работать в 100% случаев. Но чаще всего вы будете использовать абсолютный столбец (с $) и относительную строку (без $) в ссылках на ячейки, например = $ A1> 1 .

   Имейте в виду, что формулы = A1 = 1 , = A $ 1 = 1 и = A $ 1 = 1 дадут разные результаты.Если вы не уверены, какой из них правильный в вашем случае, вы можете попробовать все 🙂 Для получения дополнительной информации см. Относительные и абсолютные ссылки на ячейки в условном форматировании Excel.

2. Проверьте примененный диапазон. Проверьте, применяется ли ваше правило условного форматирования к правильному диапазону ячеек. Основное правило таково - выберите все ячейки / строки, которые вы хотите отформатировать, но не включайте заголовки столбцов.
3. Запишите формулу для верхней левой ячейки. В правилах условного форматирования ссылки на ячейки указываются относительно самой левой верхней ячейки в применяемом диапазоне.Поэтому всегда пишите формулу условного форматирования для 1-й строки с данными.

   Например, если ваши данные начинаются со строки 2, вы помещаете = A $ 2 = 10 , чтобы выделить ячейки со значениями, равными 10, в всех строках . Распространенная ошибка - всегда использовать ссылку на первую строку (например, = A $ 1 = 10 ). Помните, что вы ссылаетесь на строку 1 в формуле только в том случае, если ваша таблица не имеет заголовков и ваши данные действительно начинаются в строке 1. Наиболее очевидным признаком этого случая является то, что правило работает, но форматирует значения не в тех строках, которые должны .

4. Проверьте созданное вами правило. Еще раз проверьте правило в диспетчере правил условного форматирования. Иногда без всякой причины Microsoft Excel искажает только что созданное правило. Итак, если правило не работает, перейдите к Условное форматирование> Управление правилами и проверьте формулу и диапазон, к которому она применяется. Если вы скопировали формулу из Интернета или другого внешнего источника, убедитесь, что используются прямые кавычки .
5. Отрегулируйте ссылки на ячейки при копировании правила. Если вы копируете условное форматирование Excel с помощью Format Painter, не забудьте настроить все ссылки на ячейки в формуле.
6. Разбиение сложных формул на простые элементы. Если вы используете сложную формулу Excel, которая включает несколько различных функций, разделите ее на простые элементы и проверьте каждую функцию по отдельности.

И, наконец, если вы испробовали все шаги, но ваше правило условного форматирования все еще работает некорректно, напишите мне в комментариях, и мы попытаемся разобраться в этом вместе 🙂

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

Вас также может заинтересовать

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

• Открыть меню Global Nav Глобальная навигация Закрыть меню
• яблоко
• Сумка для покупок

Поддержка поиска

отменить

• яблоко
• Mac
• iPad
• iPhone
• Часы
• телевидение
• Музыка
• Служба поддержки
• Сумка для покупок

отменить Сообщества Получать поддержку войти в систему

• Войти
• Вход корпоративный

Просматривать Поиск Сообщества

• Войти
• Задайте вопрос
• Просмотр
• Получить поддержку

• Задать вопрос

простых и расширенных вариантов использования форматирования ячеек в Excel

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

Хватит болтовни. Давайте приступим. Если вы хотите продолжить, вы можете скачать файл Excel. (Просто нажмите кнопку «Загрузить» в правом верхнем углу окна браузера. Не спрашивайте меня, зачем Dropbox это прикрепляет. Разработчики…)

Стандартное форматирование чисел

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

Эти ошибки новичков неуместны, потому что Excel поместил несколько параметров форматирования чисел спереди и по центру в разделе «Число» на вкладке «Главная»:

Щелкните, чтобы увеличить изображение.

Для неофитов, вот что означают эти значки слева направо:

Валюта

Конвертировать в валюту.Эта опция выравнивает символ доллара по левому краю и выравнивает число по правому краю. Технически это называется форматированием учета. (Если вы хотите, чтобы символ валюты совпадал с числом, не используйте этот значок; используйте параметр «Валюта» в раскрывающемся меню над этим рядом значков.)

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

%

Преобразует десятичные дроби в проценты.

,

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

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

Увеличить десятичный

Добавьте десятичные знаки. Поскольку я не [чрезмерно] невротик или ученый, я очень редко использую этот вариант.

Уменьшение десятичного

Удалите ненужные десятичные дроби. Я использую это все время. PSA: Пожалуйста - ради всего святого и измеримого - избавьтесь от десятичных знаков на осях диаграммы.

Выберите ось, нажмите Ctrl-1 (Mac: Command-1), чтобы открыть параметры форматирования, и настройте их в разделе «Число». Пожалуйста.

Щелкните, чтобы увеличить изображение.

Щелкните, чтобы увеличить изображение.

Дополнительные параметры ленты

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

(Да, угадайте, кого Microsoft любит больше.)

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

Параметры пользовательского форматирования чисел

Хорошо, вот здесь начинается самое интересное. Рискуя показаться клише, ваши варианты практически безграничны, если вы научитесь использовать опцию Custom.

Pro Совет: Чтобы открыть параметры форматирования, просто выберите ячейки, которые вы хотите отформатировать, и нажмите Ctrl-1 (Mac: Command-1). Это действительно работает для всего в Excel: элементов диаграмм, изображений и т. Д.

Форматирование даты

Excel дает вам довольно много вариантов на выбор в разделе «Число»> «Дата» в диалоговом окне «Формат ячеек» (которое, опять же, вы можете получить, нажав Ctrl-1 или Command-1 на Mac). Но мне нравится формат, который выглядит как 13 августа (без года), чего нет в Excel.Без проблем. Следуя нескольким простым принципам, вы можете создавать свои собственные параметры форматирования.

В приведенных ниже примерах мы будем использовать 3 августа 2012 г .:

м: 8

мм: 08

ммм:

августа

мммм:

августа

д: 3

дд: 03

ддд: пт (потому что он выпал на пятницу)

дддд: пятница

гг: 12

гггг: 2012

Итак, вот несколько примеров того, как можно показать 3 августа 2012 г .:

3 августа 2012 г .: ммм д, глаз

, пятница, 3 августа 2012 г.: дддд, мммм д, гггг

пт.08.03.12: ддд. мм.дд.гг

Форматирование положительных и отрицательных чисел

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

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

Щелкните, чтобы увеличить изображение.

Конечно, в интерфейсе GWT все они красочны, но как только вы загружаете их в Excel, они превращаются в гадкого утенка данных, и ничто не выделяется. Но Excel дает вам возможность диктовать форматирование для положительных, отрицательных чисел и даже 0.

Чтобы отформатировать эти числа, вам просто нужно помнить о следующем формате: форматирование положительного числа; форматирование отрицательного числа; форматирование для 0.

Для использования цветов в Excel есть несколько встроенных:

[ЧЕРНЫЙ]

[ЗЕЛЕНЫЙ]

[КРАСНЫЙ]

[СИНИЙ]

[CYAN]

[ПУРПУРНЫЙ]

[БЕЛЫЙ]

[ЖЕЛТЫЙ]

Примечание: Регистр не учитывается.