Как в Экселе выделить цветом выходные дни
Привет друзья. Цветовое мелирование почему-то всегда было очень актуально. В этой статье я покажу вам, как быстро форматировать даты выходных.
Как всегда, воспользуемся реальным примером в Excel. Есть простой табель, нужно заполнить цветом те столбцы, которые выпадают на выходные:
Если вы решили сделать это вручную, я не рекомендую. Наступит новый месяц и форматы придется переделывать. Поэтому мы создаем автоматизированный табель учета рабочего времени. Воспользуемся для этого условным форматированием:
- Выделите область ячейки с датой и временем операции. Нравится:
- Щелкните ленту Главная — Стили — Условное форматирование — Создать правило
- В открывшемся окне выберите «Использовать формулу для определения форматированных ячеек »
- Обратите внимание на формулу внизу этого окна: = ИЛИ (ДЕНЬ НЕДЕЛИ (2 B $) = 1; ДЕНЬ НЕДЕЛИ (2 B $) = 7)
- Нажмите кнопку «Форматировать» и выберите желаемый вид для ячеек, соответствующих выходным данным. В соответствующих вкладках задайте формат чисел, шрифты, границы ячеек и их заливку:
- Нажмите ОК, чтобы применить условное форматирование. Теперь выходные будут украшены так, как мы просили
Выходные теперь выделены темным цветом. При запуске следующего расписания условное форматирование будет перерисовано по мере обновления дат. То есть вам не нужно снова делать что-то новое. Вводите только новые даты и соответствующие данные.
Как работает формула, которую мы пишем как условие? Логика следующая: функция WEEKDAY определяет порядковый номер дня недели по западному стандарту (1-й день — воскресенье, 7-е — суббота). Сравним результат функции с единицей и семью — номерами свободных дней.
Затем функция ИЛИ проверит, дала ли хотя бы одна из этих проверок значение ИСТИНА (подтверждено) — она также вернет ИСТИНА. Итак, даем команду условному форматированию работать.
Обратите внимание, как мы ссылаемся на ячейку даты. Для самой первой ячейки мы указали B $ 2. Это означает, что при копировании этой ссылки для других ячеек координаты столбцов изменятся, а строки — нет (перед номером строки стоит знак блока $). То есть формула всегда будет ссылаться на ячейку с датой в соответствующем столбце и во второй строке. Это важный момент, без которого формула не будет работать.
Создание календаря в Excel на год
Готовый результат:
Для тех, кто просто хочет скачать в Excel календарь на текущий год, на изображении ниже показано, как он выглядит. Праздники отмечены красным, а выходные — розовым. Текущий день также автоматически выделяется заливкой.
Добавлен календарь на 2017 год с праздниками и выходными в соответствии с постановлением правительства.
Пошаговая инструкция создания календаря в Excel
Теперь создадим автоматический календарь, который будет полезен не только на текущий год, но и на все последующие годы. А для этого нам не нужны макросы и программирование. Все, что я покажу, сделано с использованием стандартных функций Excel.
Делаем заголовки
Введите следующую формулу в ячейку A1: = «Календарь на» & ГОД (СЕГОДНЯ ()) & «год». Функция СЕГОДНЯ () возвращает текущую дату (мы будем часто ее использовать). В результате группа функций ГОД (СЕГОДНЯ ()) вернет нам текущий год.
Теперь создадим заголовки месяцев. В ячейке, где находится январь, напишите следующую формулу = ДАТА (ГОД (СЕГОДНЯ ()); 1; 1). Эта функция возвращает 1 января текущего года (в моем случае 01.01.2015). Отформатируйте эту ячейку так, чтобы отображалось только название месяца. Для этого откройте формат ячейки, нажав Ctrl + 1, и выберите соответствующий формат (см. Рисунок).
Для создания заголовков следующих месяцев мы будем использовать еще одну очень полезную функцию = ДАТА (B3; 1). Эта функция возвращает дату, равную указанному количеству месяцев (в нашем случае — одному) от указанной даты. Итак, мы получаем следующее изображение (к тому же я добавил немного форматирования):
Проставляем числа
Для автоматического добавления чисел в календарь мы будем использовать только одну формулу — формулу массива. Звучит устрашающе, но если разобраться, все легко и просто. Выделите ячейки B5: H10 и введите следующую формулу (не спешите нажимать Enter):
= ДАТА (ГОД (B3); МЕСЯЦ (B3); 1–1) — (ДЕНЬ НЕДЕЛИ (ДАТА (ГОД (B3), МЕСЯЦ (B3), 1–1)) — 1) + * 7+
Вместо Enter нажмите комбинацию клавиш Ctrl + Shift + Enter, чтобы ввести формулу массива. В результате в выбранном диапазоне мы получаем дату, указанную в ячейке B3 месяца.
Мы выполняем аналогичную операцию с другими месяцами или просто копируем диапазон дат января в следующие месяцы. Получаем такую картинку:
Форматируем
Мы форматируем ячейки так, чтобы они содержали только числа (а не полностью даты). Выберите даты, откройте формат ячейки (Ctrl + 1) и вручную введите следующий формат:
Календарь становится все более привычным.
Добавляем условного форматирования
Теперь в январе календарь содержит даты 29, 30, 31 декабря и часть февраля. Давайте немного спрячем эти цифры. Для этого воспользуемся условным форматированием. Выделите ячейки B5: H10 и создайте правило условного форматирования. Ячейки, для которых следующая формула истинна, будут отформатированы:
= И (МЕСЯЦ (B5) 1 + 3 * (ЧАСТНАЯ (СТРОКА (B5) -5.9)) + ЧАСТНАЯ (СТОЛБЦА (B5); 9))
Я выбрал в качестве формата текст серого шрифта и получил следующее изображение:
Автоматически выделяем текущую дату в календаре
Для этого создадим еще одно правило условного форматирования. Ячейка будет отформатирована только в том случае, если ее значение равно текущей дате (= СЕГОДНЯ()):
Выделим выходные дни в календаре Excel
На этот раз мы сделаем это с обычным заполнением ячеек. Выбирал на свой вкус Я взял вот так:
Добавляем праздничные дни
Создайте новый лист Excel (Праздники) и добавьте умную таблицу со столбцом Праздник. Мы будем вводить все праздники в этот столбец. Я уже сделал это, вы можете скопировать и безопасно использовать.
Вернемся к нашему календарю и создадим еще одно правило условного форматирования. Формула условного форматирования проверяет, является ли дата праздником. И если формула вернет ИСТИНА, ячейка будет отформатирована. Вот формула:
= NOT (ISERROR (SEARCH (B5, INDIRECT («Праздники [Праздники]»), 0)))). Стоит отметить, что когда вы добавляете или редактируете праздники, ваш календарь будет отформатирован автоматически.
Осталось скопировать ячейки B5: H10 в другие месяцы и наш автоматический календарь готов и может служить нам больше года.
Изменение цветовой гаммы календаря Excel
Если вы используете цвета темы при создании календаря, вы можете изменить их простым движением. Просто изменив цвета темы на вкладке «Макет страницы». Выбирайте то, что вам нравится.
Помимо своего календаря, я добавлю в архив для скачивания календари других пользователей. Вы можете поблагодарить их в комментариях ниже. Если вам тоже есть чем поделиться, дайте ссылку на вашу версию календаря в комментариях, я с радостью добавлю ее.
Пример формулы выделения цветом выходных дней по дате в Excel
При работе с диаграммами и календарями всегда есть очень полезная возможность выделить даты выходных (суббота и воскресенье) цветом. Формула условного форматирования позволяет выделить все выходные дни в списке расписания или календаре в Excel.
Как выделить цветом только выходные дни по дате в Excel
На следующем рисунке показан пример автоматического выделения дат выходных:
Чтобы создать такое правило условного форматирования, выполните следующие шаги шаг за шагом:
- Выделите исходный диапазон ячеек (в данном примере это A2: A17) и выберите инструмент: «ГЛАВНАЯ» — «Условное форматирование» — «Создать правило». В результате появится окно, как показано на следующем рисунке:
- В появившемся окне выберите вариант: «Использовать формулу для определения форматированных ячеек». Теперь у нас есть возможность ввести формулу для определения наших правил и условий для выделения ячеек цветом в ранее выбранном исходном диапазоне.
- В поле ввода введите логическое выражение формулы, представленной на этом шаге. Обратите внимание, что в формуле используются только относительные ссылки на ячейки. Чтобы определить день недели по значению ячейки, используйте функцию WEEKDAY. Если функция возвращает значение 6 или 7 для определенного аргумента, это означает, что дата выходных записана в текущей ячейке (например, A4). Впоследствии окончательный результат вычисления всей формулы вернет логическое значение ИСТИНА. В этом случае к этой ячейке будет применено условное форматирование, которое предопределено пользователем в настройках этого инструмента (описано в следующем шаге).
- Нажмите кнопку «Форматировать», и появится знакомое окно для моделирования отображения ячейки и ее значения — «Формат ячеек», как показано на следующем рисунке. В этом окне вы можете указать цвета заливки, шрифтов и границ ячейки. И он также назначает другие свойства оформлению макета. После ввода всех настроек подтвердите их, нажав кнопку ОК во всех открытых окнах.
В результате в столбце дат выделялись только даты с выходными.
Выделяем дни недели цветом в EXCEL
Используя условное форматирование, выделите красным фон ячеек, содержащих дни недели, субботу и воскресенье.
Пусть столбец A содержит список дат. Выделите красным цветом ячейки, содержащие дни недели, субботу и воскресенье (см. Файл примера).
Мы будем выбирать ячейки, используя правила условного форматирования. В зависимости от содержимого ячейки (дата, текст или число) правила будут разными.
В ячейке содержится Дата
Столбец A содержит значения в формате даты, например, в ячейке A6 — 13.08.2012.
Чтобы настроить правило условного форматирования, вам необходимо:
- диапазон яркости A6: A14
- выберите пункт меню Главная / Стили / Условное форматирование / Создать правило.
- выберите тип правила Используйте формулу для определения отформатированных ячеек
- в поле Формат значений, для которого действительна следующая формула, введите формулу = ДЕНЬ НЕДЕЛИ (A6; 2)> 5
- задать формат, например, красную заливку фона ячейки.
Даты в столбце A с днем недели> 5 будут выделены красным.
В ячейке с пользовательским форматом ДДД или ДДДД содержится Дата
Пусть столбцы B и C также содержат значения в формате даты, например, в ячейку B6 введите формулу = A6 .
Для ячеек в диапазоне B6: B14 установите настраиваемый формат DDD (для диапазона C6: C14 DDDD).
Хотя теперь в ячейках отображаются понедельник и понедельник, значения в ячейках по-прежнему имеют формат даты. Вы можете проверить это, скопировав значение (CTRL + V), например, из ячейки C6 и вставив его как значение (Home / Clipboard / Paste / Paste value). В строке формул будет отображаться 13 августа 2012 г., а не понедельник. Следовательно, вам необходимо настроить правило условного форматирования, как показано в предыдущем разделе.
В ячейке содержится Число
Пусть столбец D содержит номер дня недели, например, в ячейку D6 введите формулу = ДЕНЬ НЕДЕЛИ (A6; 2) .
В этом случае правило условного форматирования настраивается аналогично случаю, когда ячейка содержит дату, только формула несколько упрощена = $ D6> 5
В ячейке содержится Текстовое значение
Пусть столбец E содержит день недели в текстовом формате, например слово «понедельник.
В этом случае правило условного форматирования настраивается по формуле = ИЛИ ($ E6 = «Суббота»; $ E6 = «Воскресенье”)
Определение выходных дней в табеле
Чтобы определить выходные (суббота и воскресенье), используйте функцию WEEKDAY, которая устанавливает номер дня недели. Введите эту функцию в ячейку C28:
В следующей ячейке — C29 — введите функцию ЕСЛИ:
Функция SE работает по схеме, описанной ниже:
- Указан ли день недели в ячейке C28 Суббота?
- Если да, возвращается ИСТИНА.
- Если нет, перейдите к следующему IF.
- Возвращает ИСТИНА, если день недели — воскресенье.
- В противном случае возвращается ЛОЖЬ.
Учет выходных и праздничных дней
На следующем шаге нам нужно создать формулу, которая при выполнении одного из условий (отпуск, выходной или отпуск, приходящийся на выходные), возвращает значение ИСТИНА. Вводим эту формулу в ячейку СЗО.
Для решения проблемы воспользуемся функцией логического ИЛИ. Эта функция возвращает ИСТИНА, если хотя бы один из ее аргументов ИСТИНА, и ЛОЖЬ, если все аргументы ЛОЖЬ.
Функция ИЛИ имеет следующий синтаксис:
Здесь логическое_значение1, логическое_значение2,. Необходимо проверить от 1 до 30 условий, которые могут быть ИСТИННЫМИ или ЛОЖНЫМИ.
Рис. 7.12. ИЛИ функциональная панель
В первом аргументе необходимо указать адрес ячейки C29, где определяется день недели, а во втором аргументе — адрес ячейки C27, где задаются даты праздников.
Формула в ячейке SZO должна выглядеть так:
Давайте вложим функции:
- Вставляем формулу из ячейки C25 в формулу из ячейки C26:
- — Вставляем получившуюся формулу в формулу из ячейки C27:
Вставляем формулу из ячейки C28 в формулу из ячейки C29:
Вставляем получившуюся формулу в формулу из ячейки СЗО:
Перенос выходных дней
На практике часты трансферы по выходным. Наша табель успеваемости не дает ответа на такие переводы. Сейчас мы исправим этот дефект. Даты дней, которые стали рабочими в результате переноса, будут введены в область G22: G24 листа «Праздники». Назовем эту область DateMoving (рис. 7.13).
Даты дней, ставших выходными в результате переноса, будут добавлены в список праздничных дней, который входит в диапазон A15: D16 листа «Праздники» (рис. 7.5).
Теперь в расписании нам нужна формула, которая определит, были ли перенесены данные выходные. Чтобы получить его, мы изменим формулу из ячейки C27 расписания.
Рис. 7.13. Список со сдвинутыми датами
Скопируйте его из строки формул, вставьте в ячейку C31, замените имя Yes-taFollow на имя DateMoving и удалите функцию NOT.
Итоговая формула в ячейке C31 должна выглядеть так:
Формула возвращает ЛОЖЬ, если она находит указанную дату, или ИСТИНУ, если нет. Следовательно, эта ячейка будет содержать значение FALSE, если день стал рабочим днем из-за переноса. Если ячейка содержит значение ИСТИНА, статус дня не изменился.
Теперь нам нужно проанализировать значения ячеек C3O и C31. Первый содержит логическое значение, которое указывает, является ли день выходным или государственным праздником. Логическое значение во второй ячейке отражает тот факт, что статус дня изменился (то есть он стал рабочим из-за переноса выходных дней).
Формула анализа должна работать так. День считается свободным днем, если его статус не изменился (C31 содержит значение ИСТИНА), и является государственным праздником, субботой или воскресеньем (SZO содержит значение ИСТИНА). Во всех остальных случаях это будет рабочий день.
вы можете построить такую формулу, используя функцию И. Возвращает ИСТИНА, если все аргументы ИСТИНА, и ЛОЖЬ, если хотя бы один аргумент ЛОЖЬ. Эта функция принадлежит к категории Boolean и имеет следующий синтаксис:
Здесь boolean1, boolean2,. Он варьируется от 1 до 30 проверяемых условий, которые могут быть ИСТИННЫМИ или ЛОЖНЫМИ.
Введите следующую функцию в ячейку C32:
Эта функция должна включать формулы ячеек SZO и C31. Ниже показано, как выглядит полученная формула в ячейке C32:
Скопируйте формулы из ячеек C27 и C32 в ячейки C10 и C11.
Как в excel выделить выходные дни
Как настроить заполнение расписания формулами? Вы можете скачать шаблон для этого расписания в этом руководстве. Уровень сложности: Excel-III: формулы и функции, интегрированные в Excel (8 академических часов.)
Еще один ручной EXCEL из серии: если я решу не работать, меня не остановить. Шаблон табеля успеваемости URV, с которым я когда-то работал, сохранился до тех пор, пока мы не установили ведение табелей учета рабочего времени в системе бухгалтерского учета.
Для создания такого паттерна потребовалось всего 4 функции: СУММ, СЧЁТ, ЕСЛИ, СЧЁТЕСЛИ. Шаблон работает по следующим правилам (вы можете скачать его у меня на сайте ExcelForum.ru в разделе ПРАКТИКА):
- В серой ячейке введите первый день месяца, для которого вы хотите создать табель учета рабочего времени.
- На листе «Исключения» уже есть списки праздников и сокращенных дней на 2019 год, представленные в виде умных таблиц, чтобы в случае увеличения количества строк нам не пришлось исправлять присоединенные к ним формулы из таблицы Лист «Табель учета рабочего времени» [На главную — Форматировать как таблицу (в центре справа)]. На 2020 год списки нужно обновить.
- На «Табель учета рабочего времени» в желтых ячейках сами формулы заполняют требуемые даты; последние три ячейки с более интенсивным цветом содержат сложные формулы для оставления ячеек пустыми, если в месяце меньше 31 дня. В окне [Формат ячеек] я настроил отображение только номера дня — вы можете увидеть, как этот параметр реализован на любой желтой ячейке.
- В синих ячейках указана более сложная формула, которая последовательно проверяет, записана ли текущая дата в список праздников, и, если да, вводится буква «B». Затем формула проверяет список сокращенных дней и вводит число 7, если находит текущую дату в этом каталоге. И только в последнюю смену узнает номер дня недели, а если номер от 1 до 5 (с пн по пт), то в ячейку ставит 8 часов, или букву «Б» для оставшиеся дни.
- В розовых ячейках работают более простые функции COUNT (подсчитать количество ячеек, а это количество рабочих дней) и SUM (подсчитать общее количество отработанных часов, пропуская текстовые ячейки).
- Зеленые ячейки показывают пример подсчета выходных и отпускных дней с использованием СЧЁТЕСЛИ.
Уфф, кажется, все… СТОП! Забыл сообщить об условном форматировании: если вместо рабочего времени в формуле (или вручную) вводятся буквы, то символ автоматически становится красным. Вы можете просмотреть настройку, выбрав любую синюю ячейку и заглянув в «Диспетчер правил условного форматирования» [Главное — Условное форматирование (справа от центра) — Диспетчер правил (внизу списка)].
«Табель учета рабочего времени» представляет собой базовый шаблон, а общее расписание для всех сотрудников моделируется на основе «ОБЩИЙ» лист. Там все ежедневные данные собраны с прямыми ссылками на базовую модель. Их можно взломать и установить вручную ОТ, например. Просто сначала сделайте копию листа или копию файла.
