Excel таблицы рабочего выходные дни

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

Как составить график рабочих дней в Excel?

Один из самых простых способов составить график рабочих дней без выходных и праздников, основан на использовании функции РАБДЕНЬ.МЕЖД.

Построение графика начнем от указания даты последнего рабочего дня прошлого года. Рядом составим список только праздничных (без выходных), которые являются нерабочими днями на вашей фирме для текущего года. Все значения должны иметь формат ячеек «Дата». Пример начала создания графика рабочих дней показан ниже на рисунке:

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

=РАБДЕНЬ.МЕЖД(B1;1;1;$B$3:$B$16)

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

=РАБДЕНЬ.МЕЖД(A3;1;1;$B$3:$B$16)

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

В результате получился персональный производственный календарь на 2019-й год для конкретной фирмы.

Функция РАБДЕНЬ.МЕЖД возвращает дату рабочего дня вычисленную на основе количества дней, на которое должна быть увеличенная задана дата. У функции имеется 2 обязательных аргумента и 2 опциональных:

=РАБДЕНЬ.МЕЖД(нач_дата;число_дней;выходные;праздники)

1. Нач_дата: с какой даты следует начать выполнять вычисление.
2. Число_дней: количество в днях, которое будет вычисляться от начальной даты.
3. Выходные: по умолчанию если данный опциональный аргумент не заполнен, то функция считает за выходные дни недели субботу и воскресенье. Однако данный аргумент позволяет определить какие именно дни недели считать за выходные. Во время ввода функции вручную в строку формул или в ячейку Excel высверливается интерактивная подсказка с помощью, которой можно выбрать одну из 15-ти доступных вариаций для выходных. Или просто указать в третьем аргументе код вариации, то есть число от 1-го и до 15-ти. Изменение графика рабочего дня в Excel:



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



Как устроен график рабочих дней в Excel?

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

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

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

• в бухгалтерских расчетах (зарплата, стаж, отпуска…)
• в логистике — для корректного определения сроков доставки с учётом выходных и праздников (помните классическое «давай уже после праздников?»)
• в управлении проектами — для правильной оценки сроков с учётом, опять же, рабочих-нерабочих дней
• при любом использовании функций типа РАБДЕНЬ (WORKDAY) или ЧИСТРАБДНИ (NETWORKDAYS), т.к. они требуют в качестве аргумента список праздников
• при использовании функций Time Intelligence (типа TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR и т.д.) в Power Pivot и Power BI
• … и т.д. и т.п. — примеров масса.

Тем, кто работает в корпоративных ERP-системах типа 1С или SAP — проще, в них производственный календарь встроен. Но что делать пользователям Excel?

Можно, конечно, вести такой календарь вручную. Но тогда придется как минимум раз в год (а то и чаще, как в «весёлом» 2020 году) его актуализировать, аккуратно внося туда все выходные, переносы и нерабочие дни, придуманные нашим правительством. А потом повторять эту процедуру каждый следующий год. Скукота.

А как насчёт чуть напрячься и сделать «вечный» производственный календарь в Excel? Такой, чтобы сам обновлялся, брал данные из интернета и формировал всегда актуальный список нерабочих дней для последующего использования в любых расчетах? Заманчиво?

Сделать такое, на самом деле, совсем не сложно.

Источник данных

Главный вопрос — а где брать данные? В поисках подходящего источника я перебрал несколько вариантов:

• Оригинальные указы публикуются на сайте правительства в формате PDF (вот, один из них, например) и отпадают сразу — полезную информацию из них не вытащить.
• Заманчивым вариантом, на первый взгляд, казался «Портал открытых данных РФ», где есть соответствующий набор данных, но, при ближайшем изучении всё оказалось печально. Для импорта в Excel сайт жутко неудобен, техподдержка не отвечает (самоизолировались?), да и сами данные там давно устарели — производственный календарь на 2020 год обновлялся последний раз в ноябре 2019 (позорище!) и, само-собой, не содержит наших «коронавирусных» и «голосовательных» выходных 2020 года, например.

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

И вот в процессе поисков случайно обнаружилось прекрасное — сайт http://xmlcalendar.ru/

Без лишних «рюшечек», простой, легкий и быстрый сайт, заточенный под одну задачу — отдавать всем желающим производственный календарь за нужный год в XML-формате. Отлично!

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

Я, на всякий случай, связался с авторами сайта и они подтвердили, что сайт существует уже 7 лет, данные на нем постоянно актуализируются (у них для этого даже ветка на github есть) и закрывать его они не собираются. И совершенно не против, чтобы мы с вами с него грузили данные для любых наших проектов и расчётов в Excel. Бесплатно. Как же приятно, что есть еще такие люди! Респектище!

Осталось загрузить эти данные в Excel с помощью надстройки Power Query (для версий Excel 2010-2013 её можно бесплатно скачать с сайта Microsoft, а в версиях Excel 2016 и новее — она уже встроена по умолчанию).

Логика действий будет такая:

1. Делаем запрос для загрузки данных с сайта за один любой год
2. Превращаем наш запрос в функцию
3. Применяем эту функцию к списку всех имеющихся лет, начиная с 2013 и до текущего года — и получаем «вечный» производственный календарь с автоматическим обновлением. Вуаля!

Шаг 1. Импортируем календарь за один год

Сначала загрузим производственный календарь за один любой год, например, за 2020. Для этого в Excel идём на вкладку Данные (или Power Query, если вы установили её как отдельную надстройку) и выбираем Из интернета (From Web). В открывшееся окно вставляем ссылку на соответствующий год, скопированную с сайта:

После нажатия на ОК появляется окно предварительного просмотра, в котором нужно нажать кнопку Преобразовать данные (Transform data) или Изменить данные (Edit data) и мы попадем в окно редактора запросов Power Query, где и продолжим работу с данными:

Сразу же можно смело удалить в правой панели Параметры запроса (Query settings) шаг Измененный тип (Changed Type) — он нам не нужен.

Таблица в столбце holidays содержит коды и описания нерабочих дней — увидеть её содержимое можно, дважды «провалившись» в неё щелчком мыши по зелёному слову Table:

Для возврата назад придется удалить в правой панели все появившиеся шаги обратно до Источника (Source).

Вторая таблица, куда можно зайти аналогичным образом, содержит как раз то, что нам нужно — даты всех нерабочих дней:

Останется эту табличку обработать, а именно:

1. Отфильтровать только даты праздников (т.е. единички) по второму столбцу Attribute:t

2. Удалить все столбцы, кроме первого — щелчком правой кнопкой мыши по заголовку первого столбца и выбором команды Удалить другие столбцы (Remove Other Columns):

3. Разделить первый столбец по точке отдельно на месяц и день с помощью команды Разделить столбец — По разделителю на вкладке Преобразование (Transform — Split column — By delimiter):

4. И, наконец, создать вычисляемый столбец с нормальными датами. Для этого на вкладке Добавление столбца жмём на кнопку Настраиваемый столбец (Add Column — Custom Column) и вводим в появившееся окно следующую формулу:

=#date(2020, [#»Attribute:d.1″], [#»Attribute:d.2″])

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

Шаг 2. Превращаем запрос в функцию

Следующая наша задача — преобразовать созданный на 2020 год запрос в универсальную функцию для любого года (номер года будет её аргументом). Для этого делаем следующее:

1. Разворачиваем (если ещё не развернута) панель Запросы (Queries) слева в окне Power Query:

2. После преобразования запроса в функцию возможность видеть шаги, из которых состоит запрос и легко их редактировать, к сожалению, пропадает. Поэтому имеет смысл сделать копию нашего запроса и резвиться уже с ней, а оригинал оставить про запас. Для этого щелкаем правой кнопкой мыши в левой панели по нашему запросу calendar и выбираем команду Дублировать (Duplicate).

Щелкнув еще раз правой по получившейся копии calendar(2) выберем команду Переименовать (Rename) и введём новое имя — пусть будет, например, fxYear:

3. Открываем исходный код запроса на внутреннем языке Power Query (он лаконично назвается «М») с помощью команды Расширенный редактор на вкладке Просмотр (View — Advanced Editor) и вносим туда небольшие правки для превращения нашего запроса в функцию на любой год.

Было:

Стало:

Если интересны подробности, то здесь:

• (year as number)=>  — объявляем, что в нашей функции будет один числовой аргумент — переменная year
• Подклеиваем переменную year в веб-ссылку на шаге Источник. Поскольку Power Query не позволяет склеивать числа и текст, то на лету преобразовываем номер года в текст с помощью функции Number.ToText
• Подставляем переменную year вместо 2020 года на предпоследнем шаге #»Добавлен пользовательский объект«, где мы формировали дату из фрагментов.

После нажатия на Готово наш запрос превращается в функцию:

Шаг 3. Импортируем календари за все года

Осталась самая малость — сделать последний главный запрос, который будет закачивать данные за все имеющиеся года и складывать все полученные даты праздников в одну таблицу. Для этого:

1. Щёлкаем в левой панели запросов в серое пустое место правой кнопкой мыши и выбираем последовательно Новый запрос — Другие источники — Пустой запрос (New Query — From other sources — Blank query):

2. Нам нужно сформировать список всех лет, для которых мы будем запрашивать календари, т.е. 2013, 2014 … 2020. Для этого в строке формул появившегося пустого запроса вводим команду:

Конструкция:

={ЧислоА..ЧислоБ}

… в Power Query формирует список целых чисел от А до Б. Например, выражение

={1..5}

…выдало бы список 1,2,3,4,5.

Ну, а чтобы не привязываться жёстко к 2020 году, мы используем функцию DateTime.LocalNow() — аналог экселевской функции СЕГОДНЯ (TODAY) в Power Query — и извлекаем из неё, в свою очередь, текущий год функцией Date.Year.

3. Полученный набор лет хоть и выглядит вполне адекватно, но для Power Query представляет собой не таблицу, а особый объект — список (List). Но преобразовать его в таблицу — не проблема: достаточно нажать кнопку В таблицу (To Table) в левом верхнем углу:

4. Финишная прямая! Применяем созданную нами ранее функцию fxYear к полученному списку лет. Для этого на вкладке Добавление столбца жмём кнопку Вызвать настраиваемую функцию (Add Column — Invoke Custom Function) и задаём её единственный аргумент — колонку Column1 с годами:

После нажатия на ОК наша функция fxYear по очереди отработает импорт для каждого года и мы получим столбец, где в каждой ячейке будет содержаться таблица с датами нерабочих дней (содержимое таблицы хорошо видно, если щёлкнуть мышью в фон ячейки рядом со словом Table):

Останется развернуть содержимое вложенных таблиц, щёлкнув по значку с двойными стрелками в шапке столбца Даты (галочку Использовать исходное имя столбца как префикс при этом можно снять):

… и после нажатия на ОК мы получим то, что хотели — список всех праздников с 2013 и до текущего года:

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

Сам запрос можно переименовать как-нибудь более осмысленно, чем Запрос1 и выгрузить затем результаты на лист в виде динамической «умной» таблицы с помощью команды Закрыть и загрузить на вкладке Главная (Home — Close & Load):

Обновлять созданный календарь в будущем можно щелчком правой кнопки мыши по таблице или запросу в правой панели через команду Обновить. Или же использовать кнопку Обновить все на вкладке Данные (Date — Refresh All) или сочетание клавиш Ctrl+Alt+F5.

Вот и всё.

Теперь вам больше никогда не нужно тратить время и мыслетопливо на поиски и актуализацию списка праздничных дней — теперь у вас есть «вечный» производственный календарь. Во всяком случае, до тех пор, пока авторы сайта http://xmlcalendar.ru/ поддерживают своё детище, что, я надеюсь, будет ещё очень и очень долго (спасибо им еще раз!).

Ссылки по теме

• Импорт курса биткойна в Excel из интернета через Power Query
• Поиск ближайшего рабочего дня функцией РАБДЕНЬ (WORKDAY)
• Поиск пересечения интервалов дат

Выделить дату, день недели в Excel при условии.

​Смотрите также​​Но работают​ пн по пт,​ днями недели Сб​Полностью с Вами​ А у Вас​А в Условном​ Условном форматировании.​ 1:​
​ сотрудников в ячейках​​ окно написания темы:​ в первой ячейке​​Формула, которую я​ примере уже 2018​Che79​Если дата (ячейка​ такая таблица.​Условное форматирование в Excel​один день​ за исключением сб​ и Вс выкрашивались​ согласен.​​ в ячейке ничего​ форматировании в Формате​У меня самостоятельно​2 вариант какой​ диапазона F13:AJ21;F25:AJ29 тоже​«Условным форматированием выделить​ (А1) стоит прописана​ скопировал с файла​
​ год. Праздники и​:​A2​Выделяем таблицу, заходим​поможет выделить день​
​, т.е. начинают 11-го​ и вс.​ в другой цвет​Никанор​ нет​ ячеек выставить например​ не получается это​ должен быть результат:​ ЕСЛИ выходной день​ Выходные ТЕКСТом в​ дата (01.04.2014) ,​ 1449045.xlsx с Условного​ «рабочие» выходные вынесены​

​Никанор​) попадает на субботу​ в «Условное форматирование»​ недели, рабочие или​​ и заканчивают 11-го,​Например, необходимо выполнить​ при заданном том​
​: boa, – Андрей!​Никанор​ # ##0.00″ СБ.»​
​ сделать.​ Выходные выделяются буквой​ суббота – что​ табеле учёта рабочего​ в ячейке ниже​ форматирования и формула​

​ отдельно в серые​, проверяйте на реальном​ или воскресенье, то​ и выбираем «Создать​
​ выходные дни, дату.​ а если заканчивают​ окраску, начало работ​ или ином месяце​Чудесно Вы придумали​
​: Отличное решение. Уже​Но я понял​Можно Вас попросить​​ «Х». Рисунок 2:​

​ было «СБ» или​ времени и начисления​ прописана формула (=А1+1)​ которую я скопировал​
​ поля. В УФ​​ файле​ нижеследующая формула возвращает​
​ правило».​ Здесь перечислены варианты​

​ 14-го получается уже​ с 28.03.2013 (ЧТ)​ + можно ли​ «автоматизировали» без Условного​ после того как​ что мои условия​ написать эту формулу​pabchek​ «Сб», ЕСЛИ выходной​ заработной платы».​ и т.д. до​ с сайта, в​

​ формула​=(ДЕНЬНЕД($A12;2)>5)*($A12<>»»)​ дату​Выбираем «Сравнить столбцы​ формул настройки ячеек​два дня​ , длительность работы​ еще к этим​ форматирования!​ Вы предложили формулу,​ невыполнимы так как​ в файл и​: Здравствуйте!​ день воскресенье –​Помогите, пожалуйста, в​ конца месяца. Ячейка​ Вашем сообщении от:​

excel-office.ru

Ближайший рабочий день в MS EXCEL

​=(ДЕНЬНЕД($A12;2)>5)*($A12<>»»)+ИЛИ($A12=$P$2:$P$12)-ИЛИ($A12=$Q$2:$Q$12)​Я бы еще​следующего понедельника​ таблицы для определения​ таблицы Excel для​(11 и 14),​ 6 дней, получается,​ выходным привязать праздничные​Действительно теперь править​ а не Условное​Правильно ли я​

​ прикрепить на сайт.​​Я пошел немного​​ что бы было​ прилагаемом файле «Табель​ с «СЕГОДНЯ»шним числом​ Пятницы, 15.12.2017, 18:52​​И формулы в​​ сюда праздники добавил,​
​(см. Файл примера):​

​ форматируемых ячеек». В​

​ выделения их по​​ вот следующий этап​
​ что рабочие дни​

​ дни ( я​ будет меньше.​ форматирование, и я​
​ понял что это​

​P. S. Я разобрался.​ по другому. Возможно,​ «ВС» или «Вс»​
​ учёта рабочего времени​

​ выделяется цветом (светло-коричневым),​
​ | Сообщение №​ календаре немного переделал.​ наверняка Вам понадобятся)​=РАБДЕНЬ(A2-1;1) или​ строке «Формат…» пишем​ условию.​ работ начинают уже​ — ЧТ, ПТ,ПН,​ задаю на весь​Огромное спасибо!​

excel2.ru

Формула условное форматирование выделить выходные в столбце (Формулы/Formulas)

​ открыл Ваш файл​​ невозможно?​
​ Это формула, а​ Вас устроит.​ (не столь важно​ и начисления заработной​ но как сделать,​ 8 ничем не​Никанор​
​Никанор​=ЕСЛИ(ДЕНЬНЕД(A2;2)>5;A2+(8-ДЕНЬНЕД(A2;2));A2)​ формулу. =ЕОШИБКА(B9)​1.​
​ 14-го.​ ВТ,СР, ЧТ, т.е.​​ год праздничные дни,​
​Только у меня​ то я сам​boa​ не формула для​
​Формула для ячейки​ большая или маленькая​
​ платы» нужно Условным​

​ что бы ячейка​​ отличаются.​​: Che79, Большое спасибо​
​: vikttur, Спасибо.​​или дату​Выбираем цвет, которым​Выделить в Excel день​Поэтому, в моём​

​ конец выполнения работ​​ а Excel при​​ сразу не получилось.​​ подумал о таком​: Никанор,​​ Условного форматирования.​
​ G13:​ вторая буква).​ форматированием выделить выходные​

​ с датой (06.04.2014),​​Но я сделал​
​ за файл и​Но я сделал​прошедшей пятницы​ будет окрашиваться ячейка​ недели.​ первом примитивном варианте​ выпадает на 04.04.2013.​​ выставлении того или​
​ Я Вашу формулу​ варианте.​а что бы​У меня похожая​=ЕСЛИ($C13<>0;ТЕКСТ(G$10;»ддд»);»»)​
​2 вариант формулы:​ дни ТЕКСТом с​
​ 7 числа ячейка​

​ следующее: Открыл Ваш​​ формулу. Сейчас уже​ так: выделил весь​
​=ЕСЛИ(ДЕНЬНЕД(A2;2)>5;МИН(A2-(ДЕНЬНЕД(A2;2)-5);A2);A2)​​ с ошибкой.​​Например, выделим субботы​ в ячейке М9​ У меня же​ иного месяца выкрашивает​

​ с Сообщения №​​То есть алгоритм​​ выводило «Х», можно​​ формула стоит в​_Boroda_​
​ ЕСЛИ не ПУСТАЯ​ УСЛОВИЯМИ:​

​ в датой выходного​​ файл → Главная​ нет времени –​ диапазон A12:A42 →​Если дата попадает на​Нажимаем «ОК». Получилось так.​
​ и воскресенья. Как​
​ было написано: К9+L9​ получается, что она​
​ столбец с этим​ 17 Дата: Среда,​
​ – последовательность выполнения​ такую формулу в​

​ диапазоне ячеек F11:AJ11​​: УФ с каким-то​​ ячейка диапазона B13:B21;B25:B29​​ЕСЛИ день недели​ дня выделилась красным​ → Условное форматирование​ посмотрю позже и​ Главная → Условное​ воскресенье, то будет​Ещё вырианты применения​ преобразовать даты в​-1​ считает вместе с​​ праздничным днём).​​ 04.07.2018, 12:02 вставлял​ должны быть такими:​

​ G13​​ для определения дня​ текстом можно сделать​ что бы напротив​ Суббота, что бы​ светом?​ → Управление правилами​
​ сообщу Вам.​ форматирование → создать​ возвращена прошедшая суббота:​ условного форматирования смотрите​ дни недели, смотрите​Vlad999​ СБ и ВС,​

​С подсчетом ночных​​ в ячейку F13​
​1. Открыл файл.​=IF(AND($C13<>0;WEEKDAY(G$10;2)>5);»X»;»»)​ недели.​ только в том​ фамилии, имени, отчества​ было СБ или​ikki​ → Изменить правило​Спасибо за внимание​
​ правило и написал​=ЕСЛИ(ДЕНЬНЕД(A2;2)>6;A2-1;A2)​

​ в статье «Условное​​ в статье «День​: я показал принцип,​ в этом случае​ и вечерних вопрос​ и «протягивал» –​

​2. Удалил все​​Никанор​Я таблицу заполняю​
​ случае, если в​

​ сотрудников в ячейках​​ Сб (не столь​​: вот честное пионерское​​ → ОК.​ к моему вопросу​ формулу​Если дата попадает на​ форматирование в Excel».​ недели по дате​ а там дотачивайте​ конец работы выпадает​ решил.​

​ результат был неправильный.​​ ненужные данные.​:​ вручную. И если​
​ этой ячейке изначально​
​ диапазона F13:AJ21;F25:AJ29 тоже​ важно большая или​ — нифига не​И после того​ и за уделённое​=ДЕНЬНЕД($A$12)>5​ воскресенье, то будет​3.​ Excel». У нас​ как правильно. в​ на 02.04.2013 (ВТ),​
​amfor​А потом эту​3. «Протянул» данные​boa​ в ячейке будет​ уже есть что-то​ ЕСЛИ выходной день​
​ маленькая вторая буква);​ понял​ как я нажал​ время и за​
​$A$12 – это​ возвращен следующий понедельник:​Выделение сегодняшней даты в​ есть строка в​ примере 11.01.2013 не​ что не правильно.​
​: Для Сб.и Вс.​ формулу вставил в​ с заполненными ячейками​
​, – Андрей Здравствуйте!​ формула, а потом​
​ — текст или​ суббота или воскресенье​ЕСЛИ день недели​​Денис​​ ОК все праздники​ то что Вы​ первая ячейка –​=ЕСЛИ(ДЕНЬНЕД(A2;2)>6;A2+1;A2)​ Excel.​ таблице с наименованием​ было.​Сам я не​vikttur​ ячейку G13 и​ Суббота «Сб» и​Большое спасибо за​
​ я буду заполнять​ формула. У Вас​ – что бы​ Воскресенье, что бы​

​: 6 апреля -​​ окрасились.​ ещё больше сделали​

excelworld.ru

Выделение даты по дням недели (Формулы/Formulas)

​ перовое число месяца.​​ВНИМАНИЕ!​У нас такая​ дней недели.​SDK969​ программист, с excel​
​: Праздничные и предпраздничные​ «протянул» вправо до​ Воскресенье «Вс» вниз.​ предложенную формулу.​ вручную таблицу –​ же зеленые ячейки​ было «Х». Выделено​ было ВС или​ воскресенье, т.е. выходной.​Интересно, ведь я​ чем я просил.​ И весь диапазон​Формулы не учитывают​ таблица с датами.​Выделяем эту строку. На​: Да, там я​ работал только на​

​ — эти зависят​​ AJ13 и влево​4. Заполняю таблицу​Только мне бы​

​ то формулы удалятся.​​ пустые, поэтому текст​ буквой «Ха» –​ Вс или НД​ Вот мне надо,​ формулу не изменял,​Никанор​ выделился красным цветом.​ праздничные дни и​

​Нам нужно, чтобы​​ закладке «Главная» в​
​ ошибся, конечно же​

​ уровне таблиц и​​ от того, на​
​ на ячейку F13​

​ данными.​​ хотелось это сделать​
​Мне нужно именно​

​ прилеплять без макросов​​ что бы было​ (По украинскому –​ что бы этот​ она осталась такой​: Che79, Здравствуйте!​

​Что я сделал​​ переносы рабочих дней​ в столбце «Дата​ разделе «Стили» выбираем​ работы с 11-го​ примитивных формул =+-.​ какую ногу в​ и получилось.​Но я и​ Условным форматированием.​

​ Условным форматированием, если​​ не получится.​​ видно, что это​​ Неділя) (не столь​

​ выходной выделился только​​ как и была​

Условным форматированием выделить Выходные ТЕКСТом в табеле (Формулы/Formulas)

​Скажите пожалуйста почему​​ неправильно?​
​ на выходные. В​ окончания обучения» ячейка​ — «Условное форматирование»,​
​ начались)) хотя это​Прикрепляю файл примера.​ этом году встало​1. вопрос: Правильно​ так сейчас это​
​Но Вашу формулу​ это возможно.​Проще зайти с​ выходной день.​ важно большая или​ сегодня 7 апреля.​ – просто сделал​ в файле 1449045.xlsx(20Kb)​
​Прилагаю рисунок.​ статье Ближайший рабочий​ с сегодняшней датой​ нажимаем на функцию​ не суть важно)))​Vlad999​
​ правительство​ я сделал что​ делаю, а мне​ я тоже приму​pabchek​ другой стороны -​То есть сейчас нужно​ маленькая вторая буква).​
​ Сейчас пример на​ как написал выше.​ в этом примере​Никанор​​ день с учетом​​ окрасилась. Выделяем это​ «Правила выделения ячеек»​
​Большое Вам спасибо.​: а по праздникам​Поэтому без таблицы​ формулу вставил в​ бы хотелось автоматизировать​ к сведению.​
​: Это формула не​ ЗАЧЕМ Вам это​ что бы все​Сейчас в правиле​ мыло скину.​Всё работает прилагаю​ 1, 2 и​: Che79, Большое спасибо​ праздников и переносов​ столбец (D2:D6). Заходим​ и, затем -​ Будем пытаться дотачить)​ вы работаете?​
​ этих самых дат​ ячейку G13?​ этот процесс.​
​_Boroda_​ для УФ, а​
​ нужно вообще?​ ячейки, которые в​ Условного форматирования для​ikki​ 3 рисунка для​ 9 мая праздник,​ за быструю помощь!​ учтены праздничные дни​ в «Условное форматирование»​ «Текст содержит…».​Vlad999​SDK969​ не решить никак.​2. вопрос: Как​И я подумал​: Это Вы о​ просто в ячейку.​
​Никанор​ прикреплённом файле залиты​ выделения выходных дней​: не надо!!!​ наглядности.​ а в столбце​Формула:​ и переносы рабочих​ и выбираем «Правила​Теперь пишем наш​: в «нач.» у​: точно, а про​Заливка выходных условным​ изменить Вашу формулу​ что это возможно​
​ чем? И зачем?​_Boroda_​: _Boroda_, – Александр​ зелёным цветом, были​ стоит формула:​лучше на форум.​
​Интересно почему так​ А не раскрашены​=(ДЕНЬНЕД($A12;2)>5)*($A12<>»»)​ дней на выходные.​ выделения ячеек» -​ текст, который должен​ меня в формулу​
​ праздники я и​ форматированием:​ из Сообщения №​ с помощью Условного​Цитата​: 1. Они и​ Здравствуйте!​ с определённым ТЕКСТом.​
​=(МЕСЯЦ($B$1)=МЕСЯЦ(F$10))*(ДЕНЬНЕД(F$10;2)>5)*НЕ(ЕПУСТО($B12))​Russel​ произошло.​ с помощью Условного​
​правильно выделяет (форматирует)​

​Никанор​​ «Дата…». Из списка​ выделять ячейку. В​ не дописан диапазон​ забыл. спасибо)​=ДЕНЬНЕД(G3;2)>5​ 17 Дата: Среда,​
​ форматирования.​Никанор, 04.07.2018 в​ так выделены цветом​1. Для того​Необходимо – что​

​она применяется для​: Формула в УФ​Или если скопировать​ форматирования?​

​ выходные – так​​: Здравствуйте уважаемые помощники!​
​ правил выбираем –​ нашем примере –​ праздников — допишите.​
​Vlad999​Для определения первого​
​ 04.07.2018, 12:02 что​

​Никанор​​ 11:29, в сообщении​2. Для этого​ что бы были​ нужно – какой​ диапазона ячеек =$F$12:$AJ$40.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(И(ДЕНЬНЕД(A1;2)=7;A1=СЕГОДНЯ()-1);1;0)​ эту же формулу​Или так должно​ как я и​Подскажите, пожалуйста, как​ «Сегодня». Получилось так.​ это буквы «Сб».​
​SDK969​: по моему у​ дня у Вас​ были выходные, не​

​: _Boroda_, – Александр!​​ № 10 ()​ можно сделать ругательства.​
​ выделены выходные дни;​ должен быть результат​Эта формула Условного​
​Miphistofel​ с сайта​ быть?​ хотел.​
​ в приложенном файле:​Из списка правил​
​Теперь выбираем цвет​: МММ…Вы имеете ввиду​ вас изначально не​ формула​
​ «Х», а «Сб»​Я всё прекрасно​Цитата​ Тем жеУФ-ом, только​

​2. Что бы​​ в изображено на​ форматирования даёт правильный​
​: Не работает(​=(ДЕНЬНЕД($A12;2)>5)*($A12<>»»)+ИЛИ($A12=$P$2:$P$12)-ИЛИ($A12=$Q$2:$Q$12)​
​Che79​vikttur​ «Условное форматирование выделить​
​ можно выбрать другое​ заливки – это​ кол-во праздничных дней,​ правильно рассчитано в​=ДАТА($C$3;ВПР($B$3;месяцы;2;ЛОЖЬ);1)​ и «Вс»?​ понял.​
​УФ с каким-то​ более другим.​ в выходные дни​
​ рисунках 1 и​ результат – она​Вот пример:​→ Открыть Ваш​
​: Май 2017 -​: 1. «​ выходные. в столбце.xlsx»​ правило. Например, чтобы​
​ цвет, которым будут​ например с 22.01​ столбце «срок».​?​boa​
​Большое спасибо за​ текстом можно сделать​См. файл​ не проставлять отработанные​ 2 в моём​ «работает» правильно и​Russel​
​ файл 1449045.xlsx →​ не красится, а​ссылка без закрепления строки​

​ выделить выходные дни​​ ячейка с датой​ окрашиваться выделенные ячейки.​ по 24.01 ?​

​смотрите ф-цию РАБДЕНЬ​​Первое число месяца​: Никанор,​
​ ответы и объяснения.​ только в том​pabchek​ часы.​
​ сообщении № 2.​

​ выходные дни окрашиваются​​: У меня в​ Главная → Условное​ Май 2018 -​» — это ведь​ в диапазоне ячеек​ окончания срока окрашивалась​

​ Если мы нажмем​​ Не врубился как​
​ (праздники пропишите где​ всегда единица. Просто​ответ 1: Да,​
​pabchek​ случае, если в​: а в чем​3. Вывод: для​P. S. А​
​ зелёной заливкой.​ вашем файле работает.​
​ форматирование → Управление​ красится. Что не​ для Вас написано​ A12:A42.​ за несколько дней​ на стрелку и​
​ их вписать.​ нибудь отдельно на​ в ячейку «1».​ в G13​
​: Копирование формулы один​ этой ячейке изначально​ проблема? перед ручным​
​ удобства заполнения табеля.​ то мне приходится​Сейчас в диапазоне​

​ Вы где в​​ правилами → Изменить​
​ так?​2. Написали слово​В ячейке J1​ раньше, за месяц,​
​ из списка выберем​

​Vlad999​​ листе).​​ Или в Вашем​​ответ 2​
​ раз по сравнению​ уже есть что-то​
​ заполнением во все​Мне бы хотелось​ каждый раз «протягивать»​
​ ячеек F12:AJ12 стоит​ УФ добавили новое​ правило → вставить​

​Никанор​​ «неправильно» с пробелом​ название месяца с​
​ в течение месяца,​​ строку «1,2,3,4,5», то​: у меня было​SDK969​​ в календаре есть​​=IF(AND($C13<>0;WEEKDAY(G$10;2)>5);TEXT(G$10;»ДДД»);8)​ с ручным заполнением​ — текст или​ ячейки скопировать формулу​ это сделать только​ выходные дни вниз​ формула: Код=ЕСЛИ(ДЕНЬНЕД(I$10;2)=6;»СБ»;ЕСЛИ(ДЕНЬНЕД(I$10;2)=7;»ВС»;»»)) Эта​ условие?​ формулу, которую скопировал​: Che79, А у​Никанор​ указанием года. Формат​ т.д. В одних​ выйдет окно, в​ так =РАБДЕНЬ(M9;1) а​: Изначально, при первом​ месяцы с первым​а что бы​ нескольких десятков ячеек​ формула. У Вас​ и поверх нее​ Условным форматированием –​ из формул диапазона​ формула даёт правильный​
​Miphistofel​ с сайта →​ меня не красятся.​: vikttur, Виноват. Извините.​ ячейки текстовый.​ и тех же​

​ котором можно выбрать​​ нужно так =РАБДЕНЬ(M9;1;$AT$7:$AT$20)​ варианте, считалось как​ числом 12?​ и для 12-й​ занимает доли процента​ же зеленые ячейки​ забивать уже восьмерки​ без Макросов.​ F12:AJ12 со строки​
​ результат – она​: Все работает!) НО,​ нажать ОК и​
​Вот прилагаю рисунок.​
​ Невнимательно читал. Вот​В ячейке А12​
​ ячейках может быть​ цвет и шрифта,​$AT$7:$AT$20 диапазон куда​ обычная неделя с​
​Дни недели можно​ строки подходила, то​
​ времени. А заполнять​ пустые,Правильно ли я​Никанор​Если это без​ 12 – «СБ»​
​ «работает» правильно и​ если только сегодня,​ тоже все праздники​Che79​

​ Ваша формула без​​ формула​
​ установлено несколько правил​ другие параметры и​
​ внесены праздники. кстати​ СБ и ВС,​

​ вычислять иначе, не​​ в ячейку F12​ автоматически, тогда уж​ понял что это​: _Boroda_, – Александр!​ Макросов невозможно сделать,​ и «ВС».​ ЕСЛИ день недели​ а завтра уже​ окрасились с помощью​

​:​​ закрепления строки –​
​=ДАТА(ГОД($J$1);МЕСЯЦ($J$1);СТОЛБЕЦ(A1))​ условного форматирования. как​ цвет.​ если он мешает​ но потом понял,​ завязываясь на формулу​ вставьте​ написать макрос и​ невозможно?Я же написал​
​Большое спасибо за​

​ то буду пользоваться​​«Протягиваю» вниз, где​
​ суббота – то​ ячейка 06.04.2014 не​

​ Условного форматирования.​​Никанор​
​ без символа $:​В ячейке А13​ выделить данные по​
​Нажимаем «ОК». Так​ в данном месте​
​ что их нужно​
​ определения дня.​Код=IF(WEEKDAY(F$10;2)>5;TEXT(F$10;»ДДД»);IF($C12=0;»»;8))​ шлепать одну кнопку.​ уже, Вы цитату​ предложенный вариант решения​ так как есть.​ заполнены фамилии сотрудников.​ результат в ячейке​ будет выделена, а​
​Результат будет тот​, а у меня​=ДЕНЬНЕД(A12)>5​ такая же формула​ условию, смотрите в​ же устанавливаем правило​ его можно сделать​
​ исключить.​SDK969​За что мне​boa​
​ привели мою, там​ вопроса.​Никанор​ Хочется автоматизировать этот​ «СБ», а ЕСЛИ​ мне нужно что​ же – формула​ все красится и​

​Эта формула «работает​​ как в ячейке​
​ статье «Как выделить​ с текстом «Вс».​
​ на другом листе.​​При первом варианте​
​: Здравствуйте!​ нравится стиль R1C1,​: Никанор,​ разве что-то можно​Я понял, если​
​: pabchek, – Виктор​
​ процесс.​ день недели воскресенье​ бы все предыдущие​ в Условном форматировании​ в 2007 (см.​ правильно.​

excelworld.ru

Табель выходов: выделение и учет выходных и праздничных дней

​ А12.​​ ячейки в Excel».​
​ Получилось так.​SDK969​ уже рабочий график.​Стоит задача построить​ так это за​можете в моей​ как-то иначе понять?.​ выходные дни заполняются​ здравствуйте!​С уважением.​ – то результат​ воскресные дни были​ будет показывать правильно.​ рисунок) и, тем​Ошибку в слове​Формула, которая ниже​Предположим, что счет за​Заполнять дни недели можно​: Да-да, теперь понятно.​Vlad999​ график производства работ.​ то, что для​
​ формуле пустоту заменить​ Еще раз -​ – то ячейка​

​Большое спасибо за​​Никанор​

​ в ячейке «ВС».​​ выделены всегда.​Никанор​ более в 2013.​ неправильно исправил.​ «не работает».​ продукцию нужно выставлять​
​ копированием. Смотрите статью​ Благодарю.​: смотрите.​
​Для грамотного графика​ любой ячейки всего​
​ на 8-ки и​
​ с помощью ТОЛЬКО​ окрашивается в красный​ ответ.​​: Необходимо – что​​В диапазонах B13:B21;B25:B29​
​Pelena​: Вот добавляю 3​ Проверьте формулу в​Ещё раз большое​Код=(МЕСЯЦ($J$1)=МЕСЯЦ(A$12))*(ДЕНЬНЕД(A$12;2)>5)*НЕ(ЕПУСТО($J1))​ только в рабочие​ «Копирование в Excel».​
​К сожалению решить​SDK969​ необходимо исключить дни​ диапазона формула одинаковаяКод=IF(WEEKDAY(R10C;2)>5;TEXT(R10C;»ДДД»);IF(RC3=0;»»;8))​

planetaexcel.ru

Требуется исключить дни недели сб и вс!

​ размножить ее для​​ Excel и без​
​ цвет.​У меня в​
​ нужно – какой​ Фамилии имя отчества​: Замените в формуле​ рисунок​ УФ. В самом​ Вам спасибо!​vikttur​​ дни, несмотря на​​Так можно выделять​ задачу пока не​: Спасибо. сейчас поизучаю))​ недели сб и​batrak13​
​ всего диапазона F13:AJ21;F25:AJ29,​ использования VBA невозможно​Хороший вариант решения​ файле очень много​ должен быть результат​ сотрудников.​=СЕГОДНЯ()​Денис​ файле ничего не​Che79​:​ дату доставки. Напишем​ ячейки с любым​ могу.​Вот такой момент.​ вс(так как выходные)​: Уважаемые форумчане!​ тогда править будет​
​ — в пустой​ вопроса.​ правил Условного форматирования.​ изображено в этом​Необходимо – что нужно:​
​на​

​: Всем привет! Кто​​ удаляли?​:​

​=ДЕНЬНЕД(ссылка)>5​​ формулу, которая определяет:​ текстом.​Таблица какая-то капризная,​

​Если взять ячейку​​ Т.е. в процессе​Помогите с таким​ еще меньше. Только​ ячейке кроме заливки​
​Мне бы хотелось​Я не знаю​ сообщении на рисунках​ Условным форматированием сделать:​

​Miphistofel​​ подскажет как сделать​Никанор​Никанор​ссылка​ если дата доставки​2.Например, нам нужно​ вечно куда-то скачет,​
​ L9:M9 , то​ подсчёта графика, необходимо​

​ вопросом: Есть график​​ праздничные и сокращенные​

​ и границ форматировать​​ это решить с​
​ какие правила в​
​ 1 и 2.​1 вариант формулы:​: Все работает спасибо!​ выделение даты определенным​: Che79, Отвечаю. Ничего​, посмотрите, вот вариант​
​- адрес верхней​​ попадает на выходной,​​ в большой таблице​ что-то сбивается. Или​ по логике все​ чтобы в столбцах​​ выходом с четырех​​ дни​ нечего​ помощью приблизительной функции:​ файле надо удалить,​
​1 вариант какой​ ЕСЛИ не ПУСТАЯ​Никанор​ цветом, если дата​​ не удалял и​

​ с автораскраской выходных​​ левой ячейки форматируемого​ то дата счета​ найти ячейки с​ конец рабочего дня​ правильно 11.01.2013 +​

​L: M:​​ сменным графиком работы​=IF(AND($C13<>0;WEEKDAY(G$10;2)>5);»X»;8)​»Чтобы купить что-нибудь​ =ПОИСК и что​ на эту формулу​
​ должен быть результат:​ ячейка диапазона B13:B21;B25:B29​

​: Здравствуйте уважаемые помогающие!​​ вычисляется формулой?​ ничего не изменял.​ / праздников /​ диапазона (ссылка без​

​ – следующий понедельник​​ ошибками (#ЗНАЧ!, #ССЫЛКА!,​ сказывается и начинаю​ 1 день =(исключая​число рабочих дней​ на производстве, хотелось​Никанор​

​ ненужное, сначало надо​​ бы искало выходной​ и какой поставить​ Выходные выделяются «СБ»​
​ что бы напротив​Полное название темы, которое​Пример: В столбце​Вот добавляю рисунок.​ «рабочих» выходных. В​ закрепления строки)​

​ (или прошедшая пятница).​​ #Н/Д). У нас​ тупить.​
​ выходные) 14.01.2013.​ учитывалось только с​ бы,чтобы столбцы с​
​: pabchek, – Виктор!​ продать что-нибудь ненужное».​ день.​ формат ячеек в​ и «ВС». Рисунок​ фамилии, имени, отчества​

CyberForum.ru

​ не поместилось в​