Эксель выделение цветом дата

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

Как вы справляетесь с дедлайнами?

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

Использование условного форматирования и дат - дополнительные примеры

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

Как выделить даты выходных в Excel

2. Программное обеспечение
3. Microsoft Office
4. Excel
5. Как выделить даты выходных в Excel

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

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

1. Выберите ячейки данных в целевом диапазоне (ячейки B3: B18 в этом примере), щелкните вкладку «Главная» на ленте Excel, а затем выберите «Условное форматирование» → «Новое правило».

   Откроется диалоговое окно «Новое правило форматирования».

   

2. В списке вверху диалогового окна щелкните параметр «Использовать формулу для определения ячеек для форматирования».

   При выборе этого варианта значения оцениваются на основе указанной вами формулы. Если определенное значение оценивается как ИСТИНА, к этой ячейке применяется условное форматирование.

3. В поле ввода формулы введите формулу, показанную на этом шаге.

   Обратите внимание, что вы используете функцию WEEKDAY для оценки номера дня недели целевой ячейки (B3).Если целевая ячейка возвращается как день недели 1 или 7, это означает, что дата в B3 - это выходной день. В этом случае будет применяться условное форматирование.

    = ИЛИ (ДЕНЬ НЕДЕЛИ (B3) = 1, ДЕНЬ НЕДЕЛИ (B3) = 7) 

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

4. Нажмите кнопку «Форматировать».

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

5. Вернувшись в диалоговое окно «Новое правило форматирования», нажмите кнопку «ОК», чтобы подтвердить правило форматирования.

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

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

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

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

Если у вас есть базовые знания о формулах Excel, то вы, скорее всего, знакомы с некоторыми функциями даты и времени, такими как СЕЙЧАС, СЕГОДНЯ, ДАТА, ДЕНЬ НЕДЕЛИ и т. Д. В этом руководстве мы собираемся расширить эту функциональность. to условно отформатируйте даты Excel по своему усмотрению.

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

Условное форматирование дат в Excel (встроенные правила)

Microsoft Excel предоставляет 10 вариантов форматирования выбранных ячеек на основе текущей даты.

1. Чтобы применить форматирование, просто перейдите на вкладку Home > Условное форматирование > Выделите правила для ячеек и выберите A Date Occurring .
   
2. Выберите один из вариантов даты из раскрывающегося списка в левой части окна, начиная с прошлого месяца до следующего месяца.
3. Наконец, выберите один из предустановленных форматов или настройте свой собственный формат, выбрав различные параметры на вкладках Font , Border и Fill .Если стандартной палитры Excel недостаточно, вы всегда можете нажать кнопку Дополнительные цвета… .
   
4. Нажмите ОК и наслаждайтесь результатом! :)
   

Однако этот быстрый и простой способ имеет два существенных ограничения: 1) он работает только для выбранных ячеек и 2) условный формат всегда применяется на основе текущей даты.

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

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

Как выделить выходные в Excel

К сожалению, Microsoft Excel не имеет встроенного календаря, аналогичного Outlook. Что ж, давайте посмотрим, как можно без особых усилий создать свой собственный автоматический календарь.

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

Снимок экрана ниже демонстрирует функцию ДАТА в действии. Я использовал формулу = ДАТА ($ B $ 2, $ B $ 1, B $ 4) , которая копируется в строку 5.

Наконечник. Если вы хотите отображать только дни недели, как показано на изображении выше, выберите ячейки с формулой (строка 5 в нашем случае), щелкните правой кнопкой мыши и выберите Формат ячеек…> Число> Пользовательский .В раскрывающемся списке под Тип выберите dddd или ddd , чтобы отображать полные или сокращенные названия дней соответственно.

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

1. Вы начинаете с выбора календаря Excel, в котором вы хотите добавить тени к выходным.В нашем случае это диапазон $ B $ 4: $ AE $ 10. Обязательно начинайте выбор с 1 ^st столбца даты - столбец B в этом примере.
2. На вкладке Домашняя страница щелкните меню Условное форматирование> Новое правило .
3. Создайте новое правило форматирования на основе формулы, как описано в разделе Создание правил условного форматирования Excel с помощью формулы.
4. В поле « Format values ​​where this formula is true» введите следующую формулу WEEKDAY, которая определит, какие ячейки являются субботой и воскресеньей: = WEEKDAY (5,2 B $)> 5
5. Нажмите кнопку Format… и настройте свой собственный формат, переключаясь между вкладками Font , Border и Fill и играя с различными параметрами форматирования.Когда закончите, нажмите кнопку OK , чтобы просмотреть правило.
   

Теперь позвольте мне вкратце пояснить формулу WEEKDAY (serial_number, [return_type]) , чтобы вы могли быстро настроить ее для своих собственных таблиц.

• Параметр serial_number представляет дату, которую вы пытаетесь найти. Вы вводите ссылку на свою первую ячейку с датой, в нашем случае 5 B $.
• Параметр [return_type] определяет тип недели (квадратные скобки подразумевают, что это необязательно).Вы вводите 2 в качестве типа возврата за неделю, начиная с понедельника (1) по воскресенье (7). Вы можете найти полный список доступных типов возврата здесь.
• Наконец, вы пишете> 5, чтобы выделить только субботу (6) и воскресенье (7).

На скриншоте ниже показан результат в Excel 2013 - выходные выделены красноватым цветом.

Советы:

• Если у вас в компании нестандартные выходные, напримерПо пятницам и субботам вам нужно будет настроить формулу, чтобы она начинала отсчет с воскресенья (1) и выделяла дни 6 (пятница) и 7 (суббота) - WEEKDAY (5,1 B $)> 5 .
• Если вы создаете горизонтальный (альбомный) календарь, используйте относительный столбец (без $) и абсолютную строку (с $) в ссылке на ячейку, потому что вы должны заблокировать ссылку на строку - в приведенном выше примере это строка 5, поэтому мы ввели 5 бразильских долларов. Но если вы разрабатываете календарь в вертикальной ориентации, вам следует сделать наоборот, т.е.е. используйте абсолютный столбец и относительную строку, например $ B5, как вы можете видеть на скриншоте ниже:

Как выделить праздники в Excel

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

Например, я добавил следующие праздники в столбец A (14 австралийских долларов: 17 австралийских долларов). Конечно, не все из них - настоящие государственные праздники, но в демонстрационных целях подойдут :)

Снова вы открываете Условное форматирование > Новое правило .В случае праздников вы собираетесь использовать функцию MATCH или COUNTIF :

• = СЧЁТЕСЛИ (14 австралийских долларов: 17 австралийских долларов, 5 млрд рублей)> 0
• = ПОИСКПОЗ (5 B $, 14 A $: 17,0 A $)

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

На следующем изображении показан результат в Excel 2013:

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

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

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

1. Прежде всего, вам необходимо определить код формата вашей даты. Вот несколько примеров:
   • D1: дд-ммм-гг или д-ммм-гг
   • D2: дд-ммм или д-ммм
   • D3: ммм-гг
   • D4: мм / дд / гг или м / д / гг или м / д / гг ч: мм

   Полный список кодов дат можно найти в этой статье.

2. Выберите столбец, в котором вы хотите изменить цвет ячеек или всей таблицы, если вы хотите выделить строки.
3. А теперь создайте правило условного форматирования, используя формулу, подобную этой: = ЯЧЕЙКА ("формат", $ A2) = "D1" . В формуле A - это столбец с датами, а D1 - это формат даты.

   Если ваша таблица содержит даты в 2 или более форматах, используйте оператор OR, например = ИЛИ (ячейка («формат», $ A2) = «D1», ячейка («формат», $ A2) = «D2», ячейка («формат», $ A2) = «D3»)

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

   

Как выделить строки по определенной дате в определенном столбце

Предположим, у вас есть большая электронная таблица Excel, содержащая два столбца даты (B и C). Вы хотите выделить каждую строку с определенной датой, например 13 мая-14, в столбце C.

Чтобы применить условное форматирование Excel к определенной дате, вам нужно сначала найти ее числовое значение . Как вы, наверное, знаете, Microsoft Excel хранит даты как последовательные порядковые номера, начиная с 1 января 1900 года.Итак, 1 января 1900 хранится как 1, 2 января 1900 хранится как 2… и 13 мая 14 как 41772.

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

На самом деле это была основная часть работы, и теперь вам нужно только создать правило условного форматирования для всей таблицы с помощью этой очень простой формулы: = $ C2 = 41772 .Формула подразумевает, что ваша таблица имеет заголовки, а строка 2 - это ваша первая строка с данными.

Альтернативный способ - использовать формулу ДАТА ЗНАЧ , которая преобразует дату в числовой формат, в котором она хранится, например = $ C2 = ДАТА ЗНАЧЕНИЕ ("13.05.2014")

Какую бы формулу вы ни использовали, она будет иметь одинаковый эффект:

Условное форматирование дат в Excel на основе текущей даты

Как вы, наверное, знаете, Microsoft Excel предоставляет функции СЕГОДНЯ () и для различных вычислений на основе текущей даты.Вот лишь несколько примеров того, как вы можете использовать его для условного форматирования дат в Excel.

Пример 1. Выделите даты, равные, больше или меньше сегодняшнего дня

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

Равно сегодня: = $ B2 = СЕГОДНЯ ()

Больше, чем сегодня: = $ B2> СЕГОДНЯ ()

Меньше, чем сегодня: = $ B2 <СЕГОДНЯ ()

Снимок экрана ниже демонстрирует приведенные выше правила в действии.Обратите внимание, на момент написания СЕГОДНЯ было 12 июня 2014 года.

Пример 2. Условное форматирование дат в Excel на основе нескольких условий

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

Для этой задачи вам понадобится дополнительный столбец со следующей формулой (где E - ваш столбец Delivery , а F - столбец Invoice ):

= ЕСЛИ (E2> = СЕГОДНЯ (), ЕСЛИ (F2 = "", 1, 0), 0)

Если дата доставки больше или равна текущей дате и в столбце «Счет-фактура» нет числа, формула возвращает 1, в противном случае - 0.

После этого вы создаете простое правило условного форматирования для столбца Invoice с формулой = $ G2 = 1 , где G - ваш дополнительный столбец.Конечно, позже вы сможете скрыть этот столбец.

Пример 3. Выделите предстоящие даты и задержки

Предположим, у вас есть расписание проекта в Excel, в котором перечислены задачи, их даты начала и продолжительность. Вы хотите, чтобы дата окончания для каждой задачи рассчитывалась автоматически. Дополнительная проблема заключается в том, что формула также должна учитывать выходные. Например, если дата начала - 13 июня 2014 года, а количество рабочих дней (Продолжительность) - 2, дата окончания должна быть 17 июня 2014 года, поскольку 14 июня и 15 июня - суббота и воскресенье. .

Для этого воспользуемся функцией РАБДЕНЬ.INTL (начальная_дата, дни, [выходные], [праздники]) , точнее = РАБДЕНЬ.INTL (B2, C2,1) .

В формуле мы вводим 1 в качестве параметра 3 ^rd , поскольку он указывает субботу и воскресенье как праздничные дни. Вы можете использовать другое значение, если у вас разные выходные, например, пт и сб. Полный список значений выходных дней доступен здесь. При желании вы также можете использовать 4-й параметр [праздники], который представляет собой набор дат (диапазон ячеек), которые следует исключить из календаря рабочих дней.

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

• = AND ($ D2-TODAY ()> = 0, $ D2-TODAY () <= 7) - выделите все строки, где дата окончания (столбец D) находится в пределах следующих 7 дней . Эта формула действительно удобна, когда дело доходит до отслеживания предстоящих дат истечения срока действия или платежей.
• = И (СЕГОДНЯ () - $ D2> = 0, СЕГОДНЯ () - $ D2 <= 7) - выделите все строки, где дата окончания (столбец D) находится в пределах за последние 7 дней .Вы можете использовать эту формулу для отслеживания последних просроченных платежей и других задержек.

Вот еще несколько примеров формул, которые можно применить к таблице выше:

= $ D2 <СЕГОДНЯ () - выделяет все прошедшие даты (то есть даты меньше текущей даты). Может использоваться для форматирования истекших подписок, просроченных платежей и т. Д.

= $ D2> СЕГОДНЯ () - выделяет все будущие даты (то есть даты, которые больше текущей даты). Вы можете использовать его для выделения предстоящих событий.

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

= $ D2-TODAY ()> = 6 - выделяет даты, которые происходят через 6 или более дней.

= $ D2 = СЕГОДНЯ () - 14 - выделяет даты, наступившие ровно две недели назад.

Как выделить даты в диапазоне дат

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

Вы можете выполнить эту задачу снова, используя функцию СЕГОДНЯ (). Вам просто нужно будет построить немного более сложные формулы, как показано в примерах ниже.

Формулы для выделения прошедших дат

• Более 30 дней назад: = СЕГОДНЯ () - $ A2> 30
• С 30 по 15 дней назад включительно: = И (СЕГОДНЯ () - $ A2> = 15, СЕГОДНЯ () - $ A2 <= 30)
• Менее 15 дней назад: = И (СЕГОДНЯ () - $ A2> = 1, СЕГОДНЯ () - $ A2 <15)

Текущая дата и любые будущие даты не окрашены.

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

• Произойдет более чем через 30 дней с этого момента: = $ A2-СЕГОДНЯ ()> 30
• Через 30–15 дней включительно: = И ($ A2-СЕГОДНЯ ()> = 15, $ A2-СЕГОДНЯ () <= 30)
• Менее чем за 15 дней: = И ($ A2-СЕГОДНЯ ()> = 1, $ A2-СЕГОДНЯ () <15)

Текущая и прошедшие даты не окрашиваются.

Как заштриховать промежутки и промежутки времени

В этом последнем примере мы собираемся использовать еще одну функцию даты Excel - DATEDIF (start_date, end_date, interval) .Эта функция вычисляет разницу между двумя датами на основе указанного интервала. Он отличается от всех других функций, которые мы обсуждали в этом руководстве, тем, что позволяет игнорировать месяцы или годы и вычислять разницу только между днями или месяцами, в зависимости от того, что вы выберете.

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

Нужная вам формула (где A - столбец Date ):

= РАЗНДАТ (СЕГОДНЯ (), ДАТА ((ГОД (СЕГОДНЯ ()) + 1), МЕСЯЦ ($ A2), ДЕНЬ ($ A2)), «ярд»)

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

Наконечник. Если вы забыли или потеряли эту сложную формулу, вы можете использовать вместо нее эту простую: = 365-РАЗНДАТ ($ A2, СЕГОДНЯ (), «ярд») . Он дает точно такие же результаты, просто не забудьте заменить 365 на 366 в високосные годы :)

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

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

«Дней до следующего дня рождения» Excel Web App

Мы создали это приложение Excel Web App, чтобы показать вам приведенную выше формулу в действии. Просто введите свои события в 1-й столбец и измените соответствующие даты во 2-м столбце, чтобы поэкспериментировать с результатом.

Если вам интересно узнать, как создавать такие интерактивные электронные таблицы Excel, ознакомьтесь с этой статьей - Перемещение электронных таблиц Excel в Интернет.

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

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

Выделить ячейки - Excel

В отличие от других программ Microsoft Office, таких как Word, Excel не имеет кнопки, которую можно использовать для выделения всех или отдельных частей данных в ячейке.

Однако вы можете имитировать выделение ячейки на листе, заполнив ячейки цветом выделения. Чтобы быстро имитировать выделение, вы можете создать собственный стиль ячеек, который можно применить для заливки ячеек цветом выделения. Затем, после применения этого стиля ячейки для выделения ячеек, вы можете быстро скопировать выделение в другие ячейки с помощью Format Painter .

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

Создать стиль ячеек для выделения ячеек

1. Щелкните Home > Новые стили ячеек .

2. В поле Имя стиля введите соответствующее имя для нового стиля ячейки.

   Совет: Например, введите Выделите .

3. Щелкните Формат .

4. В диалоговом окне Формат ячеек на вкладке Заливка выберите цвет, который вы хотите использовать для выделения, а затем нажмите ОК .

5. Щелкните OK , чтобы закрыть диалоговое окно Style .

   Новый стиль будет добавлен в Custom в поле стилей ячеек.

6. На листе выберите ячейки или диапазоны ячеек, которые нужно выделить. Как выделить ячейки?

7. На вкладке Home в группе Styles щелкните созданный вами новый пользовательский стиль ячеек.

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

Используйте Format Painter для выделения других ячеек

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

2. На вкладке Home в группе Clipboard дважды щелкните Format Painter , а затем перетащите указатель мыши на любое количество ячеек или диапазонов ячеек, которые вы хотите выделить.

3. Когда вы закончите, снова нажмите Format Painter или нажмите ESC, чтобы выключить его.

Отображение определенных данных другим цветом или форматом шрифта

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

   Как выбрать данные в ячейке

   Выбрать содержимое ячейки	Сделай это
   В ячейке	Дважды щелкните ячейку и перетащите ее по содержимому ячейки, которую вы хотите выделить.
   В строке формул	Щелкните ячейку и перетащите ее по содержимому ячейки, которую вы хотите выделить в строке формул.
   С помощью клавиатуры	Нажмите F2, чтобы изменить ячейку, используйте клавиши со стрелками, чтобы установить точку вставки, а затем нажмите клавиши SHIFT + СТРЕЛКА, чтобы выбрать содержимое.

2. На вкладке Home в группе Font выполните одно из следующих действий:

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

   • Чтобы применить последний выбранный цвет текста, щелкните Цвет шрифта .

   • Чтобы применить цвет, отличный от доступных цветов темы и стандартных цветов, щелкните Дополнительные цвета , а затем определите цвет, который вы хотите использовать, на вкладке Standard или Custom диалогового окна Colors .

   • Чтобы изменить формат, щелкните Полужирный , Курсив , или Подчеркнутый .

     Сочетание клавиш Вы также можете нажать CTRL + B, CTRL + I или CTRL + U.

Как изменить цвет фона в Excel на основе значения ячейки

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

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

Как динамически изменить цвет ячейки на основе значения в Excel

Цвет фона будет меняться в зависимости от значения ячейки.

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

Решение : необходимо использовать условное форматирование Excel, чтобы выделить значения больше X , меньше Y или между X и Y .

Предположим, у вас есть список цен на бензин в разных штатах, и вы хотите, чтобы цены превышали 3 доллара США.7 должна быть красного цвета и не более 3,45 доллара США быть зеленого цвета.

Примечание : снимки экрана для этого примера были сделаны в Excel 2010, однако кнопки, диалоговые окна и настройки такие же или почти такие же в Excel 2016 и Excel 2013.

Хорошо, вот что вы делаете, шаг за шагом:

1. Выберите таблицу или диапазон, в котором вы хотите изменить цвет фона ячеек. В этом примере мы выбрали $ B $ 2: $ H $ 10 (имена столбцов и первый столбец со списком имен состояний исключены из выбора).
2. Перейдите на вкладку «Главная» в группе «Стили» и выберите Условное форматирование > Новое правило… .
   
3. В диалоговом окне Новое правило форматирования выберите « Форматировать только ячейки, содержащие » в поле « Выберите тип правила » в верхней части диалогового окна.
4. В нижней части диалогового окна в разделе « Форматировать только ячейки с разделом » установите условия правила. Мы выбираем форматировать только ячейки со значением Cell Value - больше чем - 3.7 , как видно на скриншоте ниже.
   

   Затем нажмите кнопку Формат… , чтобы выбрать, какой цвет фона применять при выполнении вышеуказанного условия.

5. В диалоговом окне Формат ячеек переключитесь на вкладку Заливка и выберите цвет по вашему выбору, в нашем случае красноватый цвет, и нажмите ОК .
   
6. Теперь вы вернулись в окно Новое правило форматирования , и предварительный просмотр ваших изменений формата отображается в поле Preview .Если все в порядке, нажмите кнопку ОК .
   

   Результат форматирования будет выглядеть примерно так:
   

   Поскольку нам нужно применить еще одно условие, то есть изменить фон ячеек со значениями, равными или меньшими 3,45, на зеленый цвет, снова нажмите кнопку New Rule и повторите шаги 3-6, задав необходимое условие. Вот предварительный просмотр нашего второго правила условного форматирования:
   

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

   Подсказка : Вы можете использовать тот же метод для изменения цвета шрифта на основе значения ячейки. Для этого просто перейдите на вкладку Font в диалоговом окне Format Cells , которое мы обсуждали на шаге 5, и выберите предпочтительный цвет шрифта.

   

Как навсегда изменить цвет ячейки в зависимости от ее текущего значения

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

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

Решение : найдите все ячейки с определенным значением или значениями с помощью функции Excel Find All или надстройки Select Special Cells , а затем измените формат найденных ячеек с помощью функции Format Cells .

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

Найдите и выделите все ячейки, отвечающие определенному условию

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

Если вам нужно раскрасить ячейки определенным значением, например 50, 100 или 3.4, перейдите на вкладку Home , группу Editing и щелкните Find Select > Find… .

Введите необходимые значения и нажмите кнопку Найти все .

Совет : нажмите кнопку Options в правой части диалогового окна Найти и заменить , чтобы получить ряд расширенных параметров поиска, таких как « Match Case » и « Match all cell content ». ". Вы можете использовать подстановочные знаки, такие как звездочка (*), чтобы найти любую строку символов, или вопросительный знак (?), Чтобы найти любой отдельный символ.

В нашем предыдущем примере, если нам нужно было найти все цены на газ между 3,7 и 3,799, мы должны указать следующие критерии поиска:

Теперь выберите любой из найденных элементов в нижней части диалогового окна Найти и заменить , щелкнув по нему, а затем нажмите Ctrl + A, чтобы выбрать все найденные записи. После этого нажмите кнопку Закрыть .

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

Однако на самом деле нам нужно найти все цены на бензин выше 3,7, и, к сожалению, диалоговое окно Excel «Найти и заменить» не позволяет этого.

К счастью, есть еще один инструмент, который может справиться с такими сложными условиями. Надстройка Select Special Cells позволяет находить все значения в указанном диапазоне, например от -1 до 45, получить максимальное / минимальное значение в столбце, строке или диапазоне, найти ячейки по цвету шрифта, цвету заливки и многому другому.

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

Если вы хотите попробовать надстройку Select Special Cells , вы можете скачать ознакомительную версию здесь.

Изменить цвет фона выбранных ячеек с помощью диалогового окна «Формат ячеек»

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

Откройте диалоговое окно Формат ячеек , нажав Ctrl + 1 (вы также можете щелкнуть правой кнопкой мыши любую из выбранных ячеек и выбрать « Форматировать ячейки… » во всплывающем меню или перейти на вкладку «Главная» > Группа Ячейки > Формат > Формат ячеек… ) и внесите все необходимые изменения формата. На этот раз мы выберем оранжевый цвет фона, просто для разнообразия :)

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

Вот результат наших изменений формата в Excel:

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

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

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

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

Цвет ячейки будет автоматически меняться в зависимости от значения ячейки.

Этот метод предоставляет решение, которое вам, скорее всего, понадобится в 99% случаев, т.е. цвет фона ячеек будет меняться в соответствии с заданными вами условиями.

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

1. На вкладке Home в группе Styles щелкните Conditional Formatting > New Rule… (пошаговые инструкции см. В шаге 2 раздела Как динамически изменять цвет ячейки в зависимости от значения) .
2. В диалоговом окне « Новое правило форматирования » выберите параметр « Использовать формулу, чтобы определить, какие ячейки следует форматировать ». Затем введите одну из следующих формул в поле «Значения формата , где эта формула истинна »:
   • = IsBlank () - для изменения цвета фона пустых ячеек.
   • = IsError () - для изменения цвета фона ячеек с формулами, возвращающими ошибки.

   Поскольку нас интересует изменение цвета пустых ячеек, введите формулу = IsBlank () , затем поместите курсор между круглыми скобками и нажмите кнопку Collapse Dialog в правой части окна, чтобы выбрать диапазон ячеек, или вы можете ввести диапазон вручную, например.г. = IsBlank (B2: h22) .
   

3. Нажмите кнопку Format… и выберите нужный цвет фона на вкладке Fill (подробные инструкции см. В шаге 5 «Как динамически изменять цвет ячейки в зависимости от значения»), а затем нажмите OK .

   Предварительный просмотр вашего правила условного форматирования будет выглядеть примерно так:
   

4. Если вас устраивает цвет, нажмите кнопку OK , и вы увидите, что изменения сразу же применяются к вашей таблице.
   

Статическое изменение цвета фона специальных ячеек

После изменения цвет фона останется прежним, независимо от изменения значений ячеек.

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

1. Выберите таблицу или диапазон и нажмите F5, чтобы открыть диалоговое окно « Перейти к », а затем нажмите кнопку « Special… ».
   
2. В диалоговом окне « Перейти к специальному » установите переключатель Пустые , чтобы выбрать все пустые ячейки .
   

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

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

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

Как максимально эффективно использовать Excel и упрощать сложные задачи

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

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

Именно поэтому команда наших лучших разработчиков Excel разработала и создала более 60 надстроек, которые мы называем Ultimate Suite for Excel. Эти интеллектуальные инструменты справляются с самыми изнурительными, кропотливыми и подверженными ошибкам задачами в Excel и обеспечивают быстрых, точных и безупречных результатов .Ниже приведен краткий список лишь некоторых задач, с которыми могут помочь надстройки:

Просто попробуйте эти надстройки, и вы увидите, что ваша продуктивность Excel вырастет как минимум на 50%!

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

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

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

Смотрите также

• 
  Вместо месячных кровянистые выделения
• 
  Выделения темные при беременности на ранних сроках
• 
  Выделения перед месячными пропали
• 
  Выделения при приеме противозачаточных таблеток в середине цикла
• 
  Выделения при стимуляции гоналом
• 
  Основанием выделения следующих типов учебных занятий
• 
  Выделения после вторых родов
• 
  Выделения кровянистые при беременности на ранних сроках
• 
  Какие могут быть выделения при беременности на ранних сроках до задержки
• 
  Капли в нос от гнойных выделений
• 
  Коричневые выделения на 6 день приема дюфастона