Извините, не мог пройти мимо! Меня прям конкретно задело какими же сложными формулами вы решаете эту задачу.
Раз уж это "Лига помощи 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" в ячейку, т.е. не будет учитываться количество раз, если пересечение есть несколько раз.
Решение гениально и просто. Приложение полностью рабочее!
Есть 2 листа. В одном листе есть столбец, куда вбиваю цифры от 1 до 440. На втором листе прописаны все эти 440 цифр в 4 столбца. Не знаю как сделать: Если я на первом листе в столбец вбиваю цифру от 1 до 440, то эта цифра находится автоматом на втором листе и окрашивается в зеленый цвет. Если нет такой цифры в столбце на первом листе, то на втором листе она окрашена в красный.
Чтобы копать, нужна лопата. А чтобы делать хорошие отчёты, нужны инструменты аналитики. Рассказываем про самые актуальные и полезные из них, которые пригодятся как аналитикам, так и вообще всем, кому по работе приходится делать отчеты и таблицы.
Excel
Самый распространённый помощник для аналитики. Ведь он прост в использовании, подходит для решения разных задач, а облачный аналог (Google-таблицы) ещё и бесплатный.
Excel нужен и бухгалтеру, и менеджеру, и экономисту. В программе без труда можно быстро посчитать расходы, построить финансовую модель или визуализировать данные.
Помните, какой ажиотаж вызывает тот человек, который знает формулы Excel? Им можете стать и вы на курсе «Excel и Google-таблицы: от новичка до эксперта». За две недели вы изучите все нужные горячие клавиши и ходовые формулы, и ежеквартальные отчёты перестанут быть задачей на весь рабочий день. Больше никаких мучений — только польза от функционала.
SQL
По мере развития компании данных становится больше, из Экселя она «вырастает» и переходит на СУБД (систему управления базами данных). Если и ваши задачи стали выходить за рамки сводных таблиц в Excel, то вам пригодится SQL — язык запросов к базам данных. Его используют как для внесения информации, так и извлечения нужных данных из сотен и тысяч строк.
Научиться составлять запросы на SQL и вывести свою аналитику на новый уровень можно на курсе «SQL с нуля для анализа данных». Обучение займёт 3–4 недели, и вы сможете стартовать в карьере аналитика , ведь именно этот язык используется в базах данных, на которых работает большинство компаний.
Хотите прокачать себя как аналитика данных и освоить все необходимые инструменты: Excel, SQL, Power BI и даже Python? Специально для таких амбициозных аналитиков в Академии Eduson есть курс «Инструменты аналитики данных», где вы в комфортном темпе освоите всё необходимое за 4 месяца. И сможете не только составлять простые отчёты в Excel лучше всех коллег, но и работать с большими данными как профессионал.
А тех, кто хочет сделать аналитику своей основной деятельностью, приглашаем на курс «Аналитик данных».
Power BI
Представьте, что специалист покончил со сбором и обработкой данных, которые просил подготовить начальник. Получилась таблица в сотни строк и десятки столбцов. Специалист пойдёт с ней к руководителю?
Надеемся, что нет — эти данные нужно наглядно визуализировать. И с этим поможет Power BI — инструмент, который забирает данные из платформ аналитики (MySQL и других источников) и преобразует их в графики и диаграммы. Овладеть Power BI можно на курсе от Eduson и использовать визуализацию в очётах, презентациях и докладах, чтобы нагляднее представлять результаты работы начальству.
Python
Python для работы с данными в основном используют айтишники. Например, специалисты по Data Science, чтобы работать с большими данными и строить прогнозы. Дата-сайентист на основе информации в БД может анализировать поведение клиентов компании и предугадывать их дальнейшие действия. Звучит как магия, но это — аналитика 80 уровня.
Если пользоваться библиотеками, то есть шаблонами с готовыми кусками кода, то получить результаты глубокой аналитики будет гораздо проще.
Научиться использовать Python не только для аналитики, но и для разработки ПО, можно на курсе «Python-разработчик».
Все эти и многие другие навыки можно освоить в Академии Eduson— №1 в дополнительном образовании по версии Smart Rankings. Наши курсы позволят с нуля погрузиться в IT, финансы, бухгалтерию, маркетинг, HR и многие другие востребованные профессии.
Еще один прием, который можно реализовать с помощью проверки данных - контроль общей суммы значений в заданном диапазоне. Например, у нас есть общий бюджет, который нельзя превышать. И есть таблица со статьями затрат. Можно настроить проверку так, что при вводе сумм затрат программа не даст ввести значение, которое при добавлении к уже имеющимся превысит общий бюджет.
Разумеется, можно просто формулой подсчитывать сумму в отдельной ячейке и контролировать результат по ней. Но иногда бывает удобнее явно запретить пользователю "накосячить". Будьте внимательны, к сожалению, как и любая проверка данных, такая фишка сломается при копировании ячеек в проверяемый диапазон из другого места.
Сортировать в Excel можно не только строки, но и столбцы. Иногда это бывает очень полезно. Правда, настройка такой сортировки находится довольно глубоко. Придется открыть окно "Сортировка" с настройками этой операции, а в нем отыскать кнопку "Параметры" и установить соответствующий переключатель. В остальном всё делается по тем же принципам, что и при сортировке строк таблицы.