Извините, не мог пройти мимо! Меня прям конкретно задело какими же сложными формулами вы решаете эту задачу.
Раз уж это "Лига помощи Excel", выкладываю своё решение. Оно более динамичное, масштабируемое, с огромным потенциалом доработки. Сделано на PowerQuery + сводные таблицы.
Решение можно легко расширять до безграничности: прикрутить водителей с ФИО, статистику сколько кто проехал в месяц, и т.п.
С 2016 года формулы в Экселе в привычном понимании стали практически не нужны. Тем более такие сложные условия, как у автора поста.
Всем, кто читает этот пост, рекомендую изучать Эксель в такой последовательности: "умные" таблицы, сводные таблицы, условное форматирование, Power Query.
rick1177, если нужна доработка точно как у вас в примере (с полной сеткой дней), я доделаю.
Вчера пришёл Генеральный и говорит: "Нужно создать простой и понятный инструмент для составления графика работы водителя на предприятии".
Постановка задачи: водитель указывает в простой форме куда он едет, указывает дату, время начала и конца занятости. Его график должен выглядеть наглядно и быть понятный для всех, кому нужен водитель.
Итак, решаем поэтапно задачу.
1. Сначала нужен общий вид этой визуализации.
Был придуман и разработан такой вид:
Пример отображения
2. Определяем настройки графика, которые нам нужны для отображения.
Вот такой набор настроек предлагается: (Дополнительная ценность заключается в том, что можно выбрать интервал отображения - выпадающий список, месяц отображения - выпадающий список, указать год, время начала рабочего дня и его окончания. Графическое отображение графика изменится)
Настройки календаря для отображения информации
Из прикольного:
от изменения месяца меняется количество отображаемых дней;
от установленного интервала меняется отображение шапки таблицы;
от изменения времени начала и окончания рабочего дня также изменяется отображение;
всё оформлено в удобном виде с использованием выпадающих списков;
Надо разобрать формулу, чтобы понять, что сделали ребята и как получили нужный результат. А что за результат? В календаре в задействованную дату и время выставляется 1 и на этой основе выстраивается условное форматирование.
Давайте разбирать поэтапно:
1. --ЛЕВСИМВ(K$1;5) - получает из строки "09:00 - 09:15" начальное_время в формате числовом (0,375) (если не значете, что это за число, то это 1/24/60*(9*60);
2. --ПРАВСИМВ(K$1;5) - - получает из строки "09:00 - 09:15" конечное_время в формате числовом (0,385416667) (если не значете, что это за число, то это 1/24/60*(9*60+15), где 15 - заданный временной интервал для отображения таблицы;
3. Соответственно, конструкция вида --ЛЕВСИМВ(K$1;5)>=Таблица2[[Время окончания]:[Время окончания]] возьмёт массив времени окончания и каждое значение сравнит с временем начала. Результатом такого действа для времени 09: 00 станет массив такого вида:
Что проверяется с помощью данного куска? Есть ли среди времени окончания такое, которое меньше для начала проверяемого интервала. Если нет, то формула выдаёт ЛОЖЬ
4. Аналогичным образом для начального времени операция вида --ПРАВСИМВ(K$1;5)<=Таблица2[[Время начала]:[Время начала]] выдаст результат:
Вид массива для времени окончания
5. Таким образом, операция вида (--ЛЕВСИМВ(K$1;5)>=Таблица2[[Время окончания]:[Время окончания]])+(--ПРАВСИМВ(K$1;5)<=Таблица2[[Время начала]:[Время начала]]) позволит получить массив такого вида:
Результат сложения массивов
6. Дальнее сравнение с 0 операции (--ЛЕВСИМВ(K$1;5)>=Таблица2[[Время окончания]:[Время окончания]])+(--ПРАВСИМВ(K$1;5)<=Таблица2[[Время начала]:[Время начала]])=0 приведёт к образованию массива вида:
Результат сравнения массива на шаге 5 с 0
Графическая интерпретация такова:
Графическая интерпретация выражения
7. Поскольку в столбце "↓↓ ДНИ ↓↓" на рисунке "Пример отображения" на самом деле находятся даты, то операция вида ($J3=Таблица2[[Дата]:[Дата]]) выдаст массив подходящих дат для даты 01.09.2023 такого вида:
Пример массива подходящих дат
8. После перемножения результата проверки даты с результатов проверки времени, если получат ИСТИНА, то всё, мы говорим, что это время задействовано.
9. СУММПРОИЗВ необходимо, чтобы проверить везде и по всем сочетаниям событий и проверяемой даты. Сравнение с ">0" просто приведёт к постановке исключительно "1" в ячейку, т.е. не будет учитываться количество раз, если пересечение есть несколько раз.
Решение гениально и просто. Приложение полностью рабочее!
Бомбануло у меня с "фишек" и их, мягко говоря, непорядочного админства!
Как многие из Вас знают, мы с женой занимаемся своим кулинарным каналом. Снимаем видеорецепты и несём их Вам, нашим читателям и зрителям :) Так вот, до вчерашнего дня, мы показывали наши рецепты и на "фишках". Но вчера без объявления войны, наш профиль там заблокировали и занизили рейтинг. Это итог.
А началось всё больше года назад.
Когда мы решили, что на "фишках" наши рецепты будут интересны, мы сперва написали админу письмо - подходит ли "фишкам" наш формат, стОит ли, можно ли и так далее. На что очень быстро получили ответ в духе "да, конечно, это не совсем наш формат, но давайте попробуем и если пользователям будет интересно - будете постить и дальше" (это не дословная цитата).
Начали размещать - набрали приличный рейтинг, обзавелись своими поклонниками и хейтерами, в общем, всё было хорошо. Но, с месяц назад, у нашего аккаунта начал таять рейтинг. По ночам. Пока никто не видит. Просто так. На ровном месте. По 200 единиц за ночь. Без связи с оценками наших постов пользователями. Написал админу. Он обещал поправить рейтинг (со второго раза; в первом письме он просто не ответил конкретно на этот вопрос). Рейтинг он не исправил, но на некоторое время падение рейтинга остановилось. Ладно, нам подходит. Ничего - рейтинга же еще много! Потом появилось новое правило подсчёта рейтинга на основе лайков/дизлайков. Причём такое ощущение, что только для нашего аккаунта. Суть в том, что первые 8 лайков пользователей не прибавляют нам рейтинга. Последующие работают как обычно. Ладно. Рейтинга еще много, да и подавляющее большинство наших постов в приличном плюсе.
Но вчера рейтинг опять начал пикировать и завалился существенно ниже отметки, которая еще позволяет размещать посты с фото и видео. И за одно, чтобы наверняка, наш аккаунт заблокировали. Писать бесполезно (хотя я, разумеется, написал), так как админ последний месяц просто игнорирует мои письма. Идти выше и связываться с кем-то из руководства, из групп в соцсетях? Да ну его... Дело не в том, что аккаунт забанили, а в хамском отношении, о котором и хотелось рассказать. До этого я с подобным не сталкивался, хотя я "житель сети" с приличным стажем.
Обычно просто пишут - "Простите, но мы решили, что это не наш формат, и мы Вас блокируем" Или просто банят, но с пояснением, с письмом в электронку. Это по-человечески. Наши посты не нарушают ни одного правила "фишек", за которые они банят. Да и других правил не нарушают.
А на "фишках", увы, всё в соответствии с их логотипом-талисманом =) Вот такие дела.
Поддерживайте пост - пусть побольше людей узнают о фишкином админстве :)
Для всех поклонников футбола Hisense подготовил крутой конкурс в соцсетях. Попытайте удачу, чтобы получить классный мерч и технику от глобального партнера чемпионата.
А если не любите полагаться на случай и сразу отправляетесь за техникой Hisense, не прячьте далеко чек. Загрузите на сайт и получите подписку на Wink на 3 месяца в подарок.