Производственный календарь в Excel
HTML-код
- Опубликовано: 8 сен 2024
- Как сделать "вечный" производственный календарь в Excel с автообновлением.
Скачать пример www.planetaexc...
Заходите в гости ↓↓↓
Мои книги planetaexcel.ru...
Мои онлайн-курсы www.planetaexc...
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru...
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Прекрасный преподавательский талант! Как же не хватает таких объяснений порой не в экселе, но и в других областях. Спасибо!
да если б в школе так учили, давно уже на Марсе б были города
@@Pavel_Fire если б в школе учились...вот тогда, может быть
Николай, Вы действительно показываете сложные задачи в понятном и лёгком варианте. Вы большой молодец и реально эксперт!👍👍👍👍👍👍👍
Огромное, огромное спасибо! По правде я не знаю пока, когда я применю эти знания. Но то что Вы делаете... я считаю это просто космос. Высший уровень! Низкий Вам поклон.
Спасибо за труд!
Спасибо! Надеюсь, было полезно :)
Nikolay Pavlov Николай, извините что не по теме, подскажите если не трудно, создал подключение через power query для сводной таблицы, все настроил и все работало, но теперь открываю книгу со сводной таблицей и вкладки power query нет вообще. Создаю новую книгу и вкладка есть, подскажите что можно сделать? Подозреваю что это какие то настройки безопасности.
Настраивал все на рабочем компьютере
@@planetaexcel
Николай, спасибо Вам за ваш труд! Вы не представляете скольким людям Вы помогли и сэкономили времени..
Не подскажете решение проблемы с одной интересной формулой?
=КОВАРИАЦИЯ.В
данная формула работает при выделении ячеек или вставки их через точку с запятой...никак не соображу, что можно добавить, что бы не сортировать постоянно список или протыкивать каждую ячейку отдельно..
@@planetaexcel а где брать пр.календарь с республиканскими праздниками, например Татарстана?
@@user-qs9bc5fb9p У вас Татарстан, а у нас два субъекта сразу! Предприятие функционирует на территории двух субъектов, со своими праздниками!.... Вот, сижу, дуууумаю... А сама работа (видео) просто шикарное!!!!!!!!!!!!
Как же мне не хватало подобных видео в 90-е годы моего учительства! Слушаю и смотрю с огромным удовольствием! Спасибо огромное за Ваш труд!
Охренеть. Я, и не догадывался, что в Excel, так интересно. Спасибо тебе, Николай.
супер, даже не представлял что такое можно творить в эксел. курс просто замечательный.
Как всегда изящно, легко и актуально!
О! Новое видео от Великого Шамана))))) спасибо огромное! Очень нужная вещь..... С нетерпением жду следующий он-лайн курс! Честно, если бы Вы еще по 1С преподавали....йеххх...
В 1С мне скучно - там всё по рельсам :)
Скучно))) хотя самое интересное там это отчеты и пользовательские поля. Как бы так вывернуться, чтобы вытащить то, что нужно, а не то, что предлагает разработчик))). Эксель, конечно, универсальнее...
Тот случай когда календарь производственный, а в душе праздник ! Спасибо Nikolay Pavlov
Спасибо за полезное видео!
А как сделать 3 таблицы:
1. Дата праздника | Его название
2. Рабочие сокращенные дни
3. Рабочий день (Сб/Вс)
Было бы круто если появится такое видео!
Спасибо большое, Николай!
Как всегда высший пилотаж!
Как кто-то ранее сказал, сначала лайк потом просмотр!!! Просто ох...тельно!!!👍🏻👍🏻👍🏻
Спасибо, Николай. Отличная техника!
---
Из моей практики в похожей задаче очень удобно представлять output в виде реального календаря. Для этого к Вашей "плоской" таблице в PQ можно добавить колонки: год, номер месяца, номер недели, день недели. Делаем Load в сводную таблицу (год, месяц, номер недели в Строки, день недели в Столбцы и саму дату в Данные; еще 2 Среза с годами и месяцами). Форматируем Данные как просто номер дня "д" и получаем сводную таблицу-календарь. В нём прикольно фильтровать по Срезу месяц, получая несколько месяцев или один и тот же, но за разные года
Отличная мысль, спасибо, Иван!
Звучит очень круто!
Рад, что идея Вам понравилась. Там много чего можно применить и улучшить…
Одним из плюсов сводной таблицы-календаря также является УФ=Условное Форматирование Данных (собственно дат, форматированных как номер дня). Если на скрытый лист вывести из PQ "умную" таблицу Holidays с датами праздничных дней, то к Данным сводной применяем УФ типа "=COUNTIF(INDIRECT("Holidays[Holiday Date]");E11)>0", где E11 - это адрес ячейки сводной. Так можно сделать, к примеру, праздники красным, независимо от дня недели, переносы - синим и т.д. Даты легко сравнивать с датами и форматировать соответственно…
Добрый день. Иван, можно подсмотреть как Вы сделали? Если можно отправьте на почту k.prytkov@mail.ru
@@user-kl3cv7qm4k ОК, нет проблем. Файл-пример выслан по указанному адресу
Как всегда здорово, спасибо большое!!!
Большое спасибо за видеоурок!
Просто космос какой то! Спасибо!
Тема на следующее видео в продолжение этого: сделать рабочий график для пяти смен по двое, день-ночь-48, с возможностью отмечать отгулы, больничные, отпуск
Спасибо большое, очень наглядно, хотя на мой взгляд жутко сложно.
Прекрасный преподователь
Преогромнейшее СПАСИБО! Здóрово объяснил, обязательно переделаю свой календарь, нужно только время для этого выделить. СПАСИБО за Канал!
Николай снова пользуется магией вне Хогвартса... Отличное видео, благодарю!
Понравилось про "веселый 2020 год"))) реально он очень "веселым" оказался)))
Ну и как всегда спасибо за новые знания.
Привет Вам из будущего, 2022-го. "Веселья" мало не бывает, как оказалось.
У нас предприятие функционирует на территории двух субъектов, со своими праздниками!.... Вот, сижу, дуууумаю... А сама работа (видео) просто шикарное!!!!!!!!!!!! Теперь видимо придется искать на сайтах субъектов и "лепить" к этим данным данные по субъектам...
Значит у вас праздников в два раза больше? Классно! :)
Здравствуйте Николай. Видео как всегда на высоте. Но если честно, я ждал от вас практические примеры
Примеры чего? :)
Отличное видео. Обязательно сделаю такой каледарь.
Интересно. Спасибо.
Реально здорово, спасибо
Спасибо!
Великолепно!!!
Как всегда высший пилотаж👍👍👍💥🔥
Николай, спасибо!
Несмотря на 2020...ждём очных встреч!
А мы вернулись в рабочий режим - вчера провел первый очный тренинг после карантина :)
@@planetaexcel а как туда попасть?
Николай, спасибо Вам за ваш труд! Вы не представляете скольким людям Вы помогли и сэкономили времени..
Не подскажете решение проблемы с одной интересной формулой?
=КОВАРИАЦИЯ.В
данная формула работает при выделении ячеек или вставки их через точку с запятой...никак не соображу, что можно добавить, что бы не сортировать постоянно список или протыкивать каждую ячейку отдельно..
Если у вас Office 365, то можно использовать функцию СОРТ для сортировки на лету. Ну, или формулу массива, если у вас предыдущие версии Excel - см. www.planetaexcel.ru/techniques/2/80/
Спасибо
В качестве спасиба за наводку на сайт, ну и вообще за уроки - номер недели по ISO от меня:
Вставляем в пользовательский столбец следующую формулу:
= Number.RoundDown((Number.From([Дата] - #date(Date.Year([Дата] - #duration([День недели] - 4, 0, 0, 0)), 1, 1)) + Date.DayOfWeek(#date(Date.Year([Дата] - #duration([День недели] - 4, 0, 0, 0)), 1, 4), Day.Monday) + 4) / 7)
где, [Дата] столбец с датами, а [День недели] столбец с номерами дней недели для этих дат (где понедельник = 1)
...пожалста
Спасибо! :)
Вам спасибо! Немного модернизировал: )
= Number.RoundDown( ( Number.From([Дата] - #date(Date.Year([Дата] - #duration([День недели] - 4, 0, 0, 0) ), 1, 4) ) + Date.DayOfWeek(#date(Date.Year([Дата] - #duration([День недели] - 4, 0, 0, 0) ), 1, 4), Day.Monday) ) / 7) + 1
Ну просто здорово!
Николай, здорово, ещё бы такой разбор по Python был) не близка Вам эта тема? 😉
ИМХО, это будет "кино не для всех" :)
Но можно попробовать объяснить основы и написать, например, пару функций, да.
Подумаю, как это лучше реализовать.
@@planetaexcel может будет не для всех, но я заценю. С меня будет подписка! Ах да, я же итак подписан 😁
@@planetaexcel неправда, сейчас очень актуальная тема)
Простите за оффтоп, интересует больше сортировка по одному столбцу, а вывод формулой данных из второго от отсортированного столбца. Необходимо это для учитывания одинаковых значений, но с разными наименованиями.
В новом Excel с динамическими массивами такое можно реализовать функцией СОРТПО (SORTBY)
@@planetaexcel а в старом [2010] есть возможность такое сделать?
@@KeiTaiho ок, спасибо! Будем искать костыли.
Хорошее видео. Много чего интересного. Но вот... идея "вечного" календаря, думаю, всё же, не осуществима. Ну вот, допустим, начнёт водоканал трубы менять, отключит в районе воду недельки на 2 и всё производство станет - таки, понадобится, вручную правки вносить.
Поэтому слово "вечный" в кавычках :)
Добрый день Николай!
Сделайте пожалуйста видео на тему «как войти в Инстаграмм аккаунт через Power Query”
Это очень актуальная тема, так как много людей сейчас выбирают комментарии и подписчиков в экселе.
Огромное спасибо за видео! Но у меня вопрос, в xml нет атрибута для простых выходных. Следовательно запросом с сайта нельзя получить обычные выходные.
спасибо! только подумала о том, что нужна такая таблица в єксель
Николай, добрый день! Подскажите, а у вас есть видео как заполнить даты на месяц в строку с учётом праздников вернее без них?
Николай 🔥🔥🔥
Николай, спасибо Вам за оч полезный материал! Кто-нибудь помогите, пожалуйста: в 2022 году появился новый столбец в таблице праздников, из-за этого функция не работает, пока не пойму, как устранить...
🙄
Как всегда простое решение!!!
Спасибо авторам xmlcalendar.ru - без него было бы сложнее на порядок :)
Очень круто.
Отлично! вообще очень красиво! Один вопрос: на 7:30 мы выбрали только тип 1 - выходные. По-моему, для правильного отображения производственного календаря нужна выборка 3 - перенесённые рабочие дни и соответствующая обработка их в итоговом календаре.
Согласен. Можно продублировать запрос и изменить у него на шаге фильтрации =3, да.
Полезное видео, а как из имеющийся информации составить настоящий производственный календарь с подсвеченными выходными, праздниками, с информацией о рабочих и не рабочих днях, часах?
Гений.👏👏👏
Николай, я для работы приобрел офис 365, по правде я не открыл для себя чего-то совершенно нового. Я действительно тестит динамические масивы, новые формулы, но это баловстов по сравлению с Power Bi и Pivot в которой я работаю с огромным количеством данных. Я буду благодарен, если вы сможете показать реальные преимущества платной версии, если таковые имеются.
Ну, после BI и Pivot вас будет трудно чем-то удивить полюбому :)
Но для большинства обычных пользователей те же динамические массивы - это кардинальное изменение всей логики работы в Excel.
Очень классное видео! Спасибо за труд!
Подскажите пожалуйста, на вкладке "Добавление столбца" нет "Вызвать настраиваемую функцию". Из-за этого последний шаг не могу сделать.
Есть только "добавить пользовательский столбец, добавить столбец индекса, создать дубликат столбца"
Здравствуйте, спасибо за уроки! Очень нужен Ваш совет, есть ли возможность в Excel на основе результатов выбора каких-либо параметров, сгенерировать особую таблицу? Объясняю - нужно создать checklist для разных фабрик, для разных типов проектов и при разных изменениях по сравнению с референсом. Пользователь должен выбрать из этих списков нужное, нажать кнопочку и вуаля, генерируется (или открывается) особая таблица чеклиста (из заранее мной созданных скажем 10-15 вариантов). Или это из разряда фантастики?
Будем внедрять!
а не могли бы сделать видео ролик о том,как сделать подсчёт зарплаты за неделю(штат из 40 человек)и в колонках должно указываться(опоздание,прогул,болезнь за каждый день)
Даже тут правительство недорабатывает
вы хоть что-то без него делать можете?:) Или везде нянька нужна?
На этапе преобразования в таблицу "Даты" возникает "Требуются сведения о конфиденциальности данных", чего нет в данном уроке. После выбора "продолжить" и выбора "анонимного использования" результатом пункта = Table.AddColumn(#"Преобразовано в таблицу", "fxYear", each fxYear([Column1])) получаю таблицу, в которой 2021-2022 года отрабатывают нормально, а года по 2020 включительно выдают ошибку Expression.Error: Столбец "Attribute:f" таблицы не найден. Формат как-то поменялся? Словил ошибку на 2016-ом офисе, на 2019-ом результат тот же...
Запрос про конфиденциальность - это нормально (зависит от версии). Ставите галочку "Игнорировать" и все ОК. Насчет старых годов не скажу - возможно на сайте что-то изменили уже с момента выпуска ролика.
ставь начальный год 21
Николай, добрый день! А есть какие-нибудь таблицы по планированию производства?
Николай, можете ли Вы показать как округлить числа по правилу парной
цифры? Так, при округлении 2,5 должно получится 2, в то время как при
округлении 3,5 получается 4.
Функция ОКРУГЛТ не подойдет?
@@planetaexcel В чистом виде не подходит. Возможно в комбинации с другими функциями, но самостоятельно додуматься до этого не получается.
Округляться цифры должны постепенно справа налево: если последняя значимая цифра меньше или равняется «4», она отбрасывается; если больше или равняется «6» - ближайшая слева от нее значимая цифра увеличивается на единицу. Если последняя значимая цифра «5», ближайшая слева от нее цифра увеличивается на единицу, если она нечетная, а четная остается без изменений (правило четной цифры).
Помогите, пожалуйста, решить эту задачу!)))
По легкому как-то можно поправить запрос, так чтобы к локальной дате прибавлялся 1 год? В декабре 2020 нужно думать уже про 2021 :)
Можно
={2013..Date.Year(DateTime.LocalNow()+1)}
... только нужно будет добавить тогда шаг для фильтрации Error, которые возникнут на годах, где нет еще данных.
Здравствуйте Николай! Очень доступно объясняете! Легко! Спасибо =) Сразу же на вас подписалась! У меня вопрос . Я хочу разделять цифры нажимая на панеле инструментов на "000" , но он мне пишет , что " Финансовый стиль не найден". Мне приходится заходить в Формат ячеек и там менять. Как я могу разделить цифры обычным путем, не делая еще несколько манипуляций?)) п.с. Файл я выгружаю с 1с формат 95г, но потом пересораняю на Эксель 97/2003. Пожалуйста помогите мне с этим вопросом.
Если пишет , что " Финансовый стиль не найден", то это какая-то явная проблема с самим Excel. Он у вас лицензионный или не очень?
так получается только выходные и праздники выгрузились, а как сделать чтоб весь год отображался, с сокращённными днями и чтоб рабочие часы видно было
Прикольно! А напротив даты можно ещё текстовую расшифровку праздника?
Можно :) Посмотрите комменты - уже спрашивали это дело :)
@@planetaexcel Спасибо!
Николай, а как быть с выходными днями, которые считаются рабочими? Как они обрабатываются в данном способе? К тому же стандартные функции Excel, например РАБДЕНЬ, не умеют с ними работать.
Хороший вопрос. Стандартные функции РАБДЕНЬ и ЧИСТРАБДНИ с ними не дружат, но можно сделать справочник из всех выходных и нерабочих дней и использовать РАБДЕНЬ.МЕЖД и ЧИСТРАБДНИ.МЕЖД с указанием, что на неделе нет выходных :)
Учитывает ли календарь перенос рабочих дней? В идеале результат должен содержать все дни и количество рабочих часов 8, 7 или 0.
Этот - не учитывает. Если нужны рабочие часы, то придется добавить шаг с функцией if в Power Query, где каждому типу дня (столбец Attribute:t) сопоставляется кол-во рабочих часов.
Возможно ли добавить описание праздников?
Привет Николай. хотел спросить можешь написать код через макрос , допустим в каждом столбце Написано по имя фамилия и отчество. 1)Иванов 2)сергей 3)Петрович. мне нужно чтобы каждые 3 столбцы находились в одной строке в отдельной ячейки
А вот я взяла 2021 год уже, и у меня не получается преобразовать дату на 2 столбца☹️
Работаю с нерусской версией виндоуса, Office 365, дата изначально английская, те например, 101 (1 января), 1231 (31 декабря). Как это исправить?
И ещё такая просьба. Можно упоминать название всех команд на английском языке, чтобы в нерусской версии знать, как что называется. Ведь порой названия очень сильно отличаются. Заранее огромное спасибо!
Подскажите, а с помощью чего выводятся подсказки формул в редакторе формул в PQ? Или это зависит от версии PQ?
Появилось в последних версиях, да.
мне говорили что это поддерживается только в office 365 с платной подпиской.
А как такое реализовать в гугл-таблицах? Как добавить этот календарь туда?
Николай, здравствуйте! Искала везде и не нашла. Помогите, пожалуйста! Как сделать график дежурств (кто-то дежурит 2/2, кто-то 3/1, кто-то неделя через неделю)?
После применения функции ошибка :[В запросе "" произошла ошибка. Expression.Error: Столбец "Attribute:f" таблицы не найден] за 13-20 год. За 21-23 без ошибки. Если можно помогите.
А как в этом календаре в апрель 2020 обозначен? Целиком выходной?
Да
запрос можно превратить в функцию! а казалось, что знаю уже всё в этой области.
Еще и двумя способами, как минимум. Первый - в этом видео, а второй (в последних версиях PQ) - через добавление в запрос параметров и потом правой кн. мыши - Создать функцию.
Никола, добрый день!
Подскажите, как можно надежно запаролить свой макрос? В интернете видел очень простой способ распаролить доступ к макросам средствами архиватора. Проверил, действительно работает (на версии офиса 16).
Заранее благодарен!
Найдете способо абсолютно надежной защиты - напишите :)
@@planetaexcel да я ж только учусь, в основном по Вашим урокам. Согласен, абсолютной защиты не бывает. А может, эта тема будет в одном из Ваших будущих уроков?))
Вопрос не по теме. Можно ли в эксель картинки под ячейки поместить?
Разметка страницы - Подложка
When you know...
... everything is simple! :)
Я ожидал что будет в итоге будет производственный календарь такого же формата как и например в консультанте
А зачем он нужен такой красивый и бессмысленный? Для формул нужна не красота, а просто список дат :)
@@planetaexcel просто мы на работе постоянно делаем производственный календарь в котором как раз расписываем все эти дни и выделяем цветом, вот я и подумал что вы подобную форму делаете. Просто не понял вас сразу.
Перечитал все комментарии но так и не увидел ответа как добавить расшифорвку к нерабочему дню в календаре?
Для причины нерабочего дня я создал еще одну функцию, а потом объединил запросы. Но есть нюансы. Пример по ссылке sites.google.com/view/calendarforever
У меня одной не получается этот календарь? Я все делала как в видео, но у меня в конце получается ошибка есть только за 2022 и 2021 год эти даты, а вот с 2013 года пишет ошибка. А это что может значит? Я где то ошиблась?
Постоянно себе обещаю заняться вдумчивым изучением excel уроков, но когда начинаю это делать всё время раздражает процесс поиска нужных уроков, в итоге движения в перед нет. Ааааааааа
Рекомендую копнуть в сторону курсов - там логика и последовательность изложения уже выстроены.
Ну, или книжку хорошую.
У меня подписка 365, но выглядит совсем не так и при импорте данных просто вылетает Ексель.
17:43 Ни фига не просто, а где сам производственный календарь? Это, всего лишь список дат. По нему не понятно где праздники, где сокращенные рабочие дни, где количество рабочих часов?
Мне кажется это вы создали эти программы, а не майкрософт.
Долго и ничего не понятно.
Если нет времени вникать - качайте готовый пример в статье (ссылка в описании).
Спасибо!