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

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

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

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

Если вы решили делать это вручную – не советую. Придёт новый месяц и форматы придется переделывать. Поэтому, создаем автоматизированный табель. Используем для этого Условное форматирование:

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

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

Как работает формула, которую мы записали в качестве условия? Логика такова: функция ДЕНЬНЕД определяет порядковый номер дня недели по западному стандарту (1-й день — воскресенье, 7-й — суббота). Мы сравниваем результат работы функции с единицей и семёркой — номерами выходных дней.

Далее функция ИЛИ проверит, если хотя бы одна из этих проверок дала ИСТИНУ (подтвердилась) — он так же вернёт ИСТИНУ. Тем самым, давая команду условному форматированию сработать.

Обратите внимание, как мы ссылаемся на ячейку с датой. Для самой первой ячейки мы указали B$2. Это значит, что при копировании этой ссылки для других ячеек, координаты столбцов будут изменяться, а строк — нет (перед номером строки стоит знак заморозки $). То есть, формула всегда будет ссылаться на ячейку с датой — в соответствующем столбце и второй строке. Это важный момент, без которого формула работать не будет. Подробнее о заморозке координат читайте здесь.

А у меня сегодня всё. Пишите Ваши вопросы в комментариях, если что-то неясно, или не получается!

Источник: officelegko.com

Создание календаря в Excel на год

Готовый результат

Для тех, кто просто хочет скачать календарь в Excel на текущий год, на картинке ниже показан как он выглядит. Красным отмечены праздничные дни, розовым – выходные. Текущий день, также автоматически выделяется заливкой.

Добавил календарь на 2017 год с праздниками и выходными в соответствии с постановлением правительства.

Пошаговая инструкция создания календаря в Excel

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

Делаем заголовки

В ячейку A1 вбиваем следующую формулу: =”Календарь на ” & ГОД(СЕГОДНЯ()) & ” год”. Функция СЕГОДНЯ() возвращает текущую дату (мы часто будем ей пользоваться). Соответственно связка функций ГОД(СЕГОДНЯ()) возвратит нам текущий год.

Теперь создадим заголовки месяцев. В ячейку где стоит Январь запишем следующую формулу =ДАТА(ГОД(СЕГОДНЯ());1;1). Эта функция возвращает первое января текущего года (01.01.2015 в моем случае). Применим к этой ячейке такой формат, чтобы отображалось только название месяца. Для этого откроем формат ячеек сочетанием клавиш Ctrl + 1 и выберем соответствующий формат (см. рисунок).

Для создания заголовков последующих месяцев будем использовать еще одну очень полезную функцию =ДАТАМЕС(B3;1). Эта функция возвращает дату отстоящую от указанной на заданное число месяцев (в нашем случае на один). Таким образом, у нас получается следующая картина (дополнительно я добавил немного форматирования):

Проставляем числа

Для автоматического проставления чисел в календаре мы будем использовать всего одну формулу – формулу массива. На вид звучит страшновато, но если разобраться – все легко и просто. Выделяем ячейки B5:H10 и вводим следующую формулу (не спешим жать Enter):
=ДАТА(ГОД(B3);МЕСЯЦ(B3);1-1)-(ДЕНЬНЕД(ДАТА(ГОД(B3);МЕСЯЦ(B3);1-1))-1)+<0:1:2:3:4:5:6>*7+

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

Аналогичную операцию проводим с другими месяцами или просто копируем диапазон дат января в следующие месяца. Получаем следующую картину:

Читайте также:  Как в excel найти значение в таблице

Форматируем

Отформатируем ячейки таким образом, чтобы они содержали только числа (а не полностью даты). Выделяем даты, открываем формат ячеек (Ctrl+1) и вводим вручную следующий формат:

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

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

Сейчас в январе календарь содержит даты 29, 30, 31 декабря, а также часть дат февраля. Давайте немного скроем эти числа. Для этого будем использовать условное форматирование. Выделяем ячейки B5:H10 и создаем правило условного форматирования. Форматироваться будут ячейки, для которых следующая формула будет являться ИСТИНОЙ:
=И(МЕСЯЦ(B5)<>1+3*(ЧАСТНОЕ(СТРОКА(B5)-5;9))+ЧАСТНОЕ(СТОЛБЕЦ(B5);9))

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

Автоматически выделяем текущую дату в календаре

Для этого создаем еще одно правило условного форматирования. Ячейка будет форматироваться только в том случае, если ее значение будет равно текущей дате (=СЕГОДНЯ()):

Выделим выходные дни в календаре Excel

На этот раз обойдемся обычной заливкой ячеек. Выбирайте на свой вкус. У меня получилось так:

Добавляем праздничные дни

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

Вернемся в наш календарь и создадим еще одно правило условного форматирования. Формула условного форматирования будет проверять является ли дата праздником. И в случае если формула вернет ИСТИНА ячейка будет форматироваться. Вот эта формула:
=НЕ(ЕОШИБКА(ПОИСКПОЗ(B5;ДВССЫЛ(“Праздники[Праздничные дни]”);0))). Стоит отметить, что при добавлении или изменении праздничных дней, календарь будет автоматически форматироваться.

Осталось скопировать ячейки B5:H10 в другие месяца и наш автоматический календарь готов и может служить нам не один год.

Изменение цветовой гаммы календаря Excel

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

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

Источник: micro-solution.ru

Пример формулы выделения цветом выходных дней по дате в Excel

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

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

Ниже на рисунке приведен пример автоматического выделения цветом дат выходных дней:

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

  1. Выделите исходных диапазон ячеек (в данном примере это A2:A17) и выбреете инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В результате чего появится окно как показано ниже на рисунке:
  2. В появившемся окне выберите опцию: «Использовать формулу для определения форматируемых ячеек». Теперь у нас есть возможность ввести формулу для определения собственных правил и условий выделения ячеек цветом в предварительно выделенном исходном диапазоне.
  3. В поле ввода введите логическое выражение формулы представленное на данном этапе. Обратите внимание на то, что в формуле используются только относительные ссылки на ячейки. Для определения дня недели по значению ячейки используется функция ДЕНЬНЕД. Если функция при определенно заданных аргумента будет возвращать значение 6 или 7, это значит, что в текущей ячейке (например, A4) записана дата выходного дня. После чего итоговый результат вычисления целой формулы будет возвращать логическое значение ИСТИНА. В таком случае для этой ячейки будет применено условное форматирование, предварительно заданное пользователем в настройках данного инструмента (описано на следующем этапе).

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

    Источник: exceltable.com

    Выделяем дни недели цветом в EXCEL

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

    Пусть в столбце А содержится перечень дат. Выделим ячейки , содержащие дни недели Суббота и Воскресенье, красным цветом (см. файл примера ).

    Читайте также:  Как в excel преобразовать в число

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

    В ячейке содержится Дата

    В столбце А содержатся значения в формате дата, например в ячейке А6 – 13.08.2012.

    Чтобы настроить правило Условного форматирования нужно:

    • выделить диапазон А6:А14
    • выбрать пункт меню Главная/ Стили/ Условное форматирование/ Создать правило.
    • выбрать тип правила Использовать формулу для определения форматируемых ячеек
    • в поле Форматировать значения, для которых следующая формула является истинной ввести формулу =ДЕНЬНЕД(A6;2)>5
    • задать формат, например красную заливку фона ячейки.

    Даты в столбце А , у которых номер дня недели >5, будут выделены красным.

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

    Пусть в столбцах B и С также содержатся значения в формате дата, например в ячейке B6 введем формулу =A6 .

    Для ячеек в диапазоне B6:B14 настроим пользовательский формат ДДД (для диапазона С6:С14 ДДДД).

    Не смотря на то, что теперь в ячейках отображаются Пн и Понедельник, значения в ячейках продолжают оставаться в формате Дата. В этом можно убедиться скопировав значение ( CTRL+V ), например, из ячейки С6 , и вставив его Как значение ( Главная/ Буфер обмена/ Вставить / Вставить значение ). В Строке формул будет отображаться значение 13.08.2012, а не слово понедельник . Поэтому настраивать правило Условного форматирования нужно так как показано в предыдущем разделе.

    В ячейке содержится Число

    Пусть в столбце D содержится номер дня недели, например в ячейке D6 введем формулу =ДЕНЬНЕД(A6;2) .

    В этом случае правило Условного форматирования настраивается аналогично случаю когда в ячейке содержится Дата, только несколько упрощается формула =$D6>5

    В ячейке содержится Текстовое значение

    Пусть в столбце E содержится день недели в текстовом формате, например слово Понедельник.

    В этом случае правило Условного форматирования настраивается с помощью формулы =ИЛИ($E6=”суббота”;$E6=”воскресенье”)

    Источник: excel2.ru

    Определение выходных дней в табеле

    Для определения выходных дней (суббот и воскресений) воспользуйтесь функцией ДЕНЬНЕД, которая устанавливает номер дня недели. Эту функцию введите в ячейку С28:

    В следующую ячейку – С29 – введите функцию ЕСЛИ:

    Функция ЕСЛИ действует по описанной ниже схеме:

    • День недели, определенный в ячейке С28, – суббота?
    • Если да, возвращается значение ИСТИНА.
    • Если нет, переходим к следующему ЕСЛИ.
    • Если днем недели является воскресенье, возвращается значение ИСТИНА.
    • В противном случае возвращается значение ЛОЖЬ.

    Учет выходных и праздничных дней

    На следующем этапе нам предстоит создать формулу, которая при соблюдении одного из условий (праздничный день, выходной день или праздничный день, выпадающий на выходной) возвращает значение ИСТИНА. Мы предполагаем разместить эту формулу в ячейке СЗО.

    Для решения задачи воспользуемся логической функцией ИЛИ. Эта функция возвращает значение ИСТИНА, если хотя бы один из ее аргументов имеет значение ИСТИНА, и значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

    Функция ИЛИ имеет следующий синтаксис:

    Здесь логииеское_значение1, логическое_значение2, . – это от 1 до 30 проверяемых условий, которые могут иметь либо значение ИСТИНА, либо значение ЛОЖЬ.

    Рис. 7.12. Панель функции ИЛИ

    В первом аргументе нам необходимо указать адрес ячейки С29, в которой определяется день недели, а во втором аргументе – адрес ячейки С27, где устанавливаются даты праздников.

    Формула в ячейке СЗО должна быть такой:

    Произведем вложение функций:

    • Формулу из ячейки С25 вложим в формулу из ячейки С26:
    • – Полученную формулу вложим в формулу из ячейки С27:

    Формулу из ячейки С28 вложим в формулу из ячейки С29:

    Полученную формулу вложим в формулу из ячейки СЗО:

    Перенос выходных дней

    На практике нередки случаи переноса выходных дней. В нашем табеле реакция на такие переносы не обеспечена. Сейчас мы исправим этот недостаток. Даты дней, которые вследствие переноса стали рабочими, будем вводить в область G22:G24 листа Праздники. Этой области присвоим имя ДатаПеренос (рис. 7.13).

    Даты дней, которые вследствие переноса стали выходными, добавим в список праздников, находящийся в диапазоне A15:G16 листа Праздники (рис. 7.5).

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

    Рис. 7.13. Список с перемещенными датами

    Скопируйте ее из строки формул, вставьте в ячейку С31, замените имя Да-таПраздн именем ДатаПеренос и удалите функцию НЕ.

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

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

    Теперь мы должны проанализировать значения ячеек С3О и С31. Первая содержит логическое значение, которое указывает на то, является ли день выходным либо праздничным. Логическое значение во второй ячейке отражает факт изменения статуса дня (то есть не стал ли он вследствие переноса выходных рабочим днем).

    Формула для анализа должна работать так. День является выходным, если его статус не изменился (С31 содержит значение ИСТИНА), и он является праздничным, субботним или воскресным (СЗО содержит значение ИСТИНА). Во всех остальных случаях день будет рабочим.

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

    Здесь логическое_значение1, логическое_значение2, . – это от I до 30 проверяемых условий, которые могут иметь либо значение ИСТИНА, либо значение ЛОЖЬ.

    Введите в ячейку С32 такую функцию:

    В данную функцию надлежит вложить формулы из ячеек СЗО и С31. Ниже показано, как выглядит результирующая формула в ячейке С32:

    Скопируйте формулы из ячеек С27 и С32 в ячейки С10 и С11.

    Источник: www.taurion.ru

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

    Как формулами настроить заполнение табеля учета рабочего времени? Шаблон такого табеля можно скачать в этом уроке. Уровень сложности: Excel-III: Формулы и встроенные функции в Excel (8 ак.ч.)

    Очередной EXCEL hand-made из серии: если я решила не работать – меня не остановить. Сохранилась модель табеля УРВ, с которой я в своё время работала – пока нам не наладили ведение табелей в учётной системе.

    Потребовалось всего 4 функции, чтобы настроить такой шаблон – это СУММ, СЧЁТ, ЕСЛИ, СЧЁТЕСЛИ. Работает шаблон по следующим правилам (скачать можно у меня на сайте ExcelForum.ru в разделе ПРАКТИКУМ):

    • В серой ячейке ввести первое число месяца, за который необходимо сделать табель.
    • На листе «Исключения» уже есть списки праздничных и сокращённых дней на 2019 г., оформленные как умные таблицы – чтобы в случае увеличения числа строк нам не пришлось бы корректировать привязанные к ним формулы с листа «Табель» [Главная – Форматировать как таблицу (правее центра)]. На 2020 г. списки нужно обновить.
    • На листе «Табель» в жёлтых ячейках формулы сами заполняют нужные даты; последние три ячейки с более интенсивной окраской содержат хитрые формулы, чтобы оставить ячейки пустыми, если дней в месяце меньше 31-го. С помощью окна [Формат ячеек] я настроила вывод на экран только номера дня – можете на любой жёлтой ячейке подсмотреть, как реализована эта настройка.
    • В голубых ячйках самая сложная формула, которая последовательно проверяет, записана ли текущая дата в списке праздников и если это так, ставит букву «В». Далее формула проверяет список сокращённых дней и ставить цифру 7, если находит текущую дату в этом справочнике. И только в последнуюю очередь выясняет номер дня недели и если номер от 1 до 5 (от пн до пт), то ставит в ячейку 8 часов, либо букву «В» для оставшихся дней.
    • В розовых ячейках трудятся простейшие функции СЧЁТ (считает числовые ячейки, а это и есть количество рабочих дней) и СУММ (считает общее кол-во отработанных часов, пропуская текстовые ячейки).
    • В зелёных ячейках приведён пример подсчёта выходных и дней отпуска с помощью СЧЁТЕСЛИ.

    Уфф, кажется, всё… СТОП! Про условное форматирование сообщить забыла – если вместо рабочих часов формула (или вручную) проставляет буквы, то шрифт автоматически становится красным. Подсмотреть настройку можно выделив любую голубую ячейку и заглянув в «Диспетчер правил условного форматирования» [Главная – Условное форматирование (правее центра) – Управление правилами (внизу списка)].

    Лист «Табель» сделан как базовый шаблон, а общий табель по всем сотрудникам смоделирован на листе «ОБЩИЙ». Там все данные по дням заполнены с помощью прямых ссылок на базовый шаблон. Их можно нарушить и вручную проставить ОТ, например. Только сначала сделайте копию листа или копию файла.

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

    Источник: excelforum.ru