Рабочие полезности Excel с моей работы (Полезность 1)⁠⁠

Вчера пришёл Генеральный и говорит: "Нужно создать простой и понятный инструмент для составления графика работы водителя на предприятии".

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

Итак, решаем поэтапно задачу.

1. Сначала нужен общий вид этой визуализации.

Был придуман и разработан такой вид:

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

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

Из прикольного:

• от изменения месяца меняется количество отображаемых дней;

• от установленного интервала меняется отображение шапки таблицы;

• от изменения времени начала и окончания рабочего дня также изменяется отображение;

• всё оформлено в удобном виде с использованием выпадающих списков;

• "заливка" календаря сформирована условным форматированием;

Т.е. заполняя постоянно таблицу с событиями в любой месяц можно получить отображение.

3. Оформляем таблицу для занесения информации.

У неё очень просто и понятный вид:

Умная таблица, которая легко продляется вниз.

4. "Всхлапываем" обе таблицы.

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

Формулу, конечно, я переработал и получил такой её вид:

=ЕСЛИ(И($J3<>"";K$2<>"");--(ЕСЛИ(ИЛИ($J3="";K$1="");"";СУММПРОИЗВ(($J3=Таблица2[[Дата]:[Дата]])*((--ЛЕВСИМВ(K$1;5)>=Таблица2[[Время окончания]:[Время окончания]])+(--ПРАВСИМВ(K$1;5)<=Таблица2[[Время начала]:[Время начала]])=0)))>0);"")

Надо разобрать формулу, чтобы понять, что сделали ребята и как получили нужный результат. А что за результат? В календаре в задействованную дату и время выставляется 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 приведёт к образованию массива вида:

Графическая интерпретация такова:

7. Поскольку в столбце "↓↓ ДНИ ↓↓" на рисунке "Пример отображения" на самом деле находятся даты, то операция вида ($J3=Таблица2[[Дата]:[Дата]]) выдаст массив подходящих дат для даты 01.09.2023 такого вида:

8. После перемножения результата проверки даты с результатов проверки времени, если получат ИСТИНА, то всё, мы говорим, что это время задействовано.

9. СУММПРОИЗВ необходимо, чтобы проверить везде и по всем сочетаниям событий и проверяемой даты. Сравнение с ">0" просто приведёт к постановке исключительно "1" в ячейку, т.е. не будет учитываться количество раз, если пересечение есть несколько раз.

Решение гениально и просто. Приложение полностью рабочее!

Вот готовый файл!

P.S. Если Вам было полезно, то рассчитываем на благодарность (автор формулы не останется неудел)!