Добрый день.
Подскажите пожалуйста, какую формулу использовать для суммирования ячеек по определённому цвету.
=subtotal(), после применения фильтра по цвету
Прошу прощения за невежество)))))) А как сделать фильтр по цвету? ))))
В LO 7.2 появился фильтр по цвету символов или фона
Большое Вам, человеческое Спасибо )))))
Доброго дня, Добрый Человек)))))
К сожалению моих знаний пока не хватает для понимания Твоих объяснений. Постараюсь изложить кратко, что я хочу.
Есть график работы сотрудников. Зелёным цветом отмечаются "планируемые рабочие дни", жёлтым - "отпуск". Требуется посчитать сколько планируемых рабочих дней и сколько дней отпуска у каждого сотрудника.
Так же если подскажешь какую литературу по LibreOffice, с задачами, решениями , буду очень признателен.))))))
До субботы доступен только телефон, поэтому посмотреть ваш файл не смогу. Но раз разговор идёт о графике работы, то предполагаю, что подсчет нужен по строке, а фильтр применяется к столбцам. Поэтому ранее предложенный вариант вам не поможет.
Совершенно верно ))))). Буду очень рад, если сможете помочь, когда будет возможность. )))))
Посмотрел приложенный файлик, не увидел ни зеленых ни желтых ячеек
Прошу прощения)))))
Цитата: Любопытный от 9 октября 2021, 09:33Прошу прощения
И вам спасибо за картинку
Как вариант: создайте 6 стилей для каждого дня. Цвет фона и шрифта совпадают. Примените к диапазону дней условное форматирование.
Тип дня Имя стиля (Описание)
1: 1Р (рабочий день)
2: 2В (выходной)
3: 3З (замена)
4: 4П (праздник)
5: 5Б (больничный)
6: 6О (отпуск)
Подсчёт дней ведите в соответствии со значением (1..6) в ячейке.
Прим. В вашем примере отсутствует поле "Кол-во замен".
Хотелось бы в виде ods/xls файла. Но сразу скажу, что цветовая дифференциация штанов ячеек не очень хорошая идея. Была несколько лет назад тема, там тоже считали ячейки по цветам, но затем попались на неотличимости для глаза ячейки без заливки и залитой белым цветом. Обычно в графиках на одного человека 2 строки, в одной часы, в другой код события
Bigor, Огромное Вам спасибо. Обязательно попробую. Что-то мне идея решить это через условное форматирование даже не пришла ))))))
Цитата: Любопытный от 9 октября 2021, 10:26решить это через условное форматирование
это @eeigor предложил :)
Цитата: Bigor от 9 октября 2021, 10:28это @eeigor предложил
Ну, мы очень похожи
))) Прошу прощения, Уважаемый eeigor. Я недавний пользователь этого форума, на имя даже не посмотрел. Думал мы тут вдвоём с Bigor-ом))))). Большое Вам спасибо и дай Бог Вам здоровья )))))) Я обязательно попробую Ваш вариант, очень интересное решение, на мой взгляд. Особенно если всё получится ))))))))))
Рабочий пример на ваших данных
UPD 1:
Макет графика в части календарных дней (1..31) изменять не нужно. Добавьте строки по количеству работников. Уточните (проверьте) формулы условного форматирования. Данный пример следует сохранить в качестве образца настройки формул.
Совет. Как восстанавливать условное форматирование для ячеек?
Установите указатель мыши на первый день (левый верхний угол диапазона), скопируйте формат (кисточка) и проведите мышью по всем дням, начиная с первого.
UPD 2: Обновил пример
Замечание. После копирования ячейки с кодом типа дня в строке "Условные обозначения" её надо правильно вставлять, чтобы вместе со значением не переносить ненужный формат в область диапазона с условным форматированием.
Специальная вставка (Paste Special): Ctrl+Shift+V → Values Only (скриншот). Если это непонятно, то вводите числовой код с клавиатуры, а потом можно скопировать эту ячейку (а не из области условных обозначений) и вставить, куда требуется. Условное форматирование при этом "не страдает".
В моём примере диапазоны условного форматирования имеют связный вид (без разрывов). Это хорошо.
У вопрошающего есть вопросы? Тоже любопытно :)
UPD 3:
И какая тут формула самая "сложная"? Наверное вот эта:
AE3: =IF(AD$3<DAY(EOMONTH(DATE($B$2;$A$3;1);0));AD$3+1;"")
которая вычисляет, есть ли 29 число в заданном месяце. Например, в 2021 году в феврале месяце только 28 дней. Этот расчёт задан только для трёх последних дней: 29-31. Если день отсутствует, ячейка очищается и заливается бордовым цветом (скриншот).
Соответственно, для трёх последних ячеек день недели вычисляется с учётом возможной ошибки (отсутствия дня в календаре):
AE4: =IFERROR(TEXT(DATE($B$2;$A$3;0)+AE$3;"NN");"")
Тогда поясню и формулу выше: она не очевидна.
Что возвращает часть формулы
DATE($B$2;$A$3;0), где B2: =2021; A3: =7
с нулём вместо дня? Правильно: последний день, но предыдущего месяца (в примере это будет 30.06.2021), однако руководство (https://help.libreoffice.org/latest/si/text/scalc/01/func_date.html?&DbPAR=WRITER&System=UNIX) о таком использовании этой функции умалчивает. А потом мы к этой дате прибавляем дни, что в строке выше, начиная с 1 (это переводит нас сразу к первому дню текущего месяца) и так до конца графика. И получаем даты текущего месяца, только отображаем день недели ("NN"). Вот и всё! "Живой" календарь готов.
Значение номера месяца в ячейке A3 можно скрыть, задав белый цвет шрифта или изменив формат на точку с запятой ;
То есть ничего не отображать для положительных чисел.
UPD 4:
Добавлена защита листа для безопасности работы. Срока 4 и столбец C заморожены. Добавлено чередование фона строк работников для удобства зрительного совмещения строк левой и правой частей графика.
Ради интереса провёл маленькое исследование с подсчётом рабочих дней по месяцам года.
Производственный календарь (http://www.consultant.ru/law/ref/calendar/proizvodstvennye/2021/) в помощь.
Количество рабочих дней по месяцам года можно подсчитать по формуле:
=NETWORKDAYS(StartDate;EndDate[;[Holidays][;Workdays]]) (https://help.libreoffice.org/latest/ru/text/scalc/01/func_networkdays.html)
однако из-за переноса выходных процесс подсчёта усложняется.
Здесь имеется в виду то, что Сб вполне может быть рабочей (например 20.02.2021). Поэтому приходится все Сб и Вс считать рабочими днями, то есть обнулять массив Workdays*:={0;0;0;0;0;0;0}, и указывать конкретно, какие из них являются выходным днём в массиве Holidays, то есть вместе с праздниками перечислять также все Сб и Вс, за исключением тех, которые стали рабочими днями в результате переноса выходных. Это не очень удобно. Или я не прав, и можно поступить как-то иначе, чтобы сократить размер массива Holidays?
Какие будет соображения? Впрочем, Вс в массиве Workdays можно оставить нерабочим днём, тогда массив Holidays сократится. У нас когда-нибудь было так, чтобы Вс делали рабочим из-за переноса выходных?
_____
* Этот список начинается с воскресенья, рабочие дни обозначаются нулевым значением, а нерабочие дни - ненулевым значением (обычно 1).
Workdays:={1;0;0;0;0;0;1} для нашей 5-дневки (значение по умолчанию).
Пример прилагается.
Сделал все Вс выходным днём, и массив Holidays значительно уменьшился. Workdays:={1;0;0;0;0;0;0}.
При этом "улетел" День Победы, приходящийся на Вс.
Пример 2
Ну, в общем, это творческий процесс... Прописать все государственные праздники и добавить к ним недостающие нерабочие Сб расчётного года, глядя в производственный календарь. Такой график будет работать весь год. Механизм должен быть ясен.
UPD:
Для задачи автора нет необходимости вычислять количество рабочих дней по формуле. Надо просто жёстко вбить количество рабочих дней (диапазон B28:C39) согласно производственному календарю.
Количество рабочих дней по месяцам (2021)
Январь 5
Февраль 19
Март 22
Апрель 22
Май 19
Июнь 21
Июль 22
Август 22
Сентябрь 22
Октябрь 21
Ноябрь 20
Декабрь 22
Пример 3. Окончательный вариант
Хотя тема и не подходит...
Я когда-то написал в Excel "универсальный" генератор для производственных календарей для регионов РФ (региональные праздники разные) и "развитых" стран мира. Увлекательное занятие - узнаешь, например, о формулах Гаусса (https://ru.wikipedia.org/wiki/%D0%90%D0%BB%D0%B3%D0%BE%D1%80%D0%B8%D1%82%D0%BC_%D0%93%D0%B0%D1%83%D1%81%D1%81%D0%B0_%D0%B2%D1%8B%D1%87%D0%B8%D1%81%D0%BB%D0%B5%D0%BD%D0%B8%D1%8F_%D0%B4%D0%B0%D1%82%D1%8B_%D0%9F%D0%B0%D1%81%D1%85%D0%B8) для вычисления даты Пасхи и т.п.
Самый информированный сайт (https://www.timeanddate.com/calendar/?country=12) о календарях.
Любой такой универсальный календарь требует поддержки. Меняются праздничные дни (например, СССР -> РФ), в некоторых странах практикуется перенос выходных дней по решению правительства, даты мусульманских праздников традиционно объявляются ежегодно и т.д.
@sokol92, но я посмотрел на этот год для России (https://www.timeanddate.com/calendar/?year=2021&country=20): всё верно. Кто-то ведь поддерживает всё это многообразие!
Небольшая команда (https://www.timeanddate.com/company/team)с центром в Норвегии.
Уважаемый eeigor, Огромное Вам спасибо. Вопросов пока нет, возникнут, я с Вашего позволения, обращусь))))))). А пока буду изучать материал. Ещё раз благодарю )))))))))
Да, конечно.
Я ещё доработал макет, добавив один именованный диапазон «Год» и одну именованную формулу «Месяц» (возвращает порядковый номер выбранного месяца). Читать формулы станет легче. Посмотрите.
Я заменил пример 3 в ответе #17: график-цвет (3).ods
C "заменой/тренингом" в вашей классификации (ответ #9) немного непонятно и с полями "Осталось".
Попробуйте адаптировать пример сами.
UPD: Экспорт файла в формат Excel.
Открыл файл в Excel 2016. Ошибки.
=TEXT(DATE(Год;Месяц;0)+C$3;"NN")
1) Именованная формула "Месяц" не работает.
=SWITCH($График.$B$3;"Январь";1;"Февраль";2;"Март";3;"Апрель";4;"Май";5;"Июнь";6;"Июль";7;"Август";8;"Сентябрь";9;"Октябрь";10;"Ноябрь";11;"Декабрь";12)
В Excel формула ПЕРЕКЛЮЧ (SWITCH) появилась только в Excel 2019. В рассматриваемом примере можно заменить функцией ПОИСКПОЗ (MATCH) для поиска номера месяца в диапазоне названий месяцев.
2) Формат для дня недели. "NN" надо заменить на "DDD" (Excel понимает только второе значение, а Calc оба).
После этих изменений пример работает в Excel.
Обе программы неплохо совмещаются на уровне формул.
В файл (прикреплен ниже) внесены указанные выше изменения (и ряд других).
При решении этой задачи ни одного сбоя (вылета) не произошло. Calc справляется :)