Как определить сколько уроков необходимо ожидать учителю до определенного урока

Пользователь

Сообщений: 380 Регистрация: 22.07.2017

21.02.2024 13:57:33

Собственно, вопрос
Лист Расписание_уч - расписание учителей. На листе ПОИСК D5:D18 - это то, на какой урок нужна замена. На листе ФОРМУЛА - список свободных учителей на день и на номер урока. Вопрос по столбцу Е. Как определить сколько учителю ждать?
Например, возьмем Алееву
Согласно листу поиск нужно заменить уроки 1,2,3,4,5,6. Она свободна, согласно листу формула на уроках 1,2,3,4,5. Теперь ее расписание. Она занята на уроках 6,7,8. Значит, если нужно: заместить 1 (первый) урок, то ждать 5 уроков, заместить 2 (второй) урок, то ждать 4 урока, заместить 3 (третий) урок, то ждать 4 урока. Сложность в том, что если уроки не 1,2,3,4,5,6 были бы, а, скажем, что надо заместить 9,10,11. Как же это уместить в формуле, чтобы расчет был верный? Желательно в динамической т.к. я стараюсь для оптимизации их максимально использовать.
Если кто знает, пожалуйста, подскажите

Пример исправлен

Прикрепленные файлы

Вопрос по формуле.xlsx (63.38 КБ)

Изменено: extrafant - 21.02.2024 18:25:22

Vik_tor Пользователь Сообщений: 2360 Регистрация: 19.09.2014	#2 21.02.2024 17:35:38 отредактируйте пример. уберите ссылки . руками заполните что должно получиться. тут "ПОИСК D5:D18 - это то, на какой урок нужна замена" у вас нет данных Изменено: Vik_tor - 21.02.2024 17:36:43

R091n Пользователь Сообщений: 404 Регистрация: 17.10.2023	#3 21.02.2024 20:40:27 Здравствуйте. Не уверен что правильно вас понял. Собрал что-то сумбурное. (формулы в зеленых ячейках Excel 2021+) Прикрепленные файлы Вопрос по формуле_212024.xlsx (65.38 КБ)

extrafant

Пользователь

Сообщений: 380 Регистрация: 22.07.2017

22.02.2024 08:05:21

Добрый день! R091n!

Да, вроде, цифры я увидел правильные, но, если честно, у меня эксель чуть не полег от расчетов!
Можно ли оптимизировать формулу?
Я так понимаю (сейчас осознал), что столбец С можно вовсе убрать из расчетов. Он никакую функцию не несет
Самое главное, возможно ли сделать функцию динамический? Ведь на самом деле в A2 у меня динамическая функция и список этот меняется. Я в пример вставил обычную просто для примера

R091n

Пользователь

Сообщений: 404 Регистрация: 17.10.2023

22.02.2024 09:12:31

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

Код

=МАКС(0;
      МИНЕСЛИ([Урок];[Учитель];[@Учитель];[День число];[@[День число]];[Предмет];"<>")-[@Урок];
      [@Урок]-МАКСЕСЛИ([Урок];[Учитель];[@Учитель];[День число];[@[День число]];[Предмет];"<>")
      )-1

(0-13) - это ожидание урока, (-1) - урок занят.

extrafant Пользователь Сообщений: 380 Регистрация: 22.07.2017	#6 22.02.2024 09:16:53 Я думаю, что сортировку в базовой входящей таблице можно сделать. Она берется из запроса PQ Итак, нужна сортировка по дню, затем по учителю, потом по № уроку. Верно?

R091n

Пользователь

Сообщений: 404 Регистрация: 17.10.2023

22.02.2024 09:22:33

Цитата
extrafant написал: дню, затем по учителю, потом по № уроку. Верно?

Так будет проще придумать что-то более производительное. Ну и попробуйте вариант с доп. столбцом без сортировки, насколько тормозит.

extrafant Пользователь Сообщений: 380 Регистрация: 22.07.2017	#8 22.02.2024 09:59:07 Лучше-производительное! лишние столбцы зачастую зло Пример исправлен Теперь сортировка по дню, затем по учителю, потом по № уроку Прикрепленные файлы Вопрос по формуле (1).xlsx (123.79 КБ)

R091n

Пользователь

Сообщений: 404 Регистрация: 17.10.2023

22.02.2024 18:10:30

Посмотрите вариант. Формула гораздо быстрее прошлого варианта (не думаю, что это предел совершенства, но точно предел моей фантазии на сегодня). Не динамический массив. Значения: (0-13) - часы ожидания, (-1) значит попадание в свободный урок между занятиями, (урок занят) - попадание в уже поставленный урок, (свободный день) - в этот день нет уроков. Обязательно должны быть включены итоги на таблице 2 (так как для "облегчения" последнюю ячейку определял по ним).

Можно попробовать сделать всю эту таблицу на PQ на основе запроса для Таблицы 2.

Прикрепленные файлы

Вопрос по формуле_220224.xlsx (172.21 КБ)

Изменено: R091n - 22.02.2024 18:11:40

extrafant

Пользователь

Сообщений: 380 Регистрация: 22.07.2017

#10

26.02.2024 12:11:58

R091n спасибо за вариант!
Через PQ не получится т.к. работаю из он-лайн. Да, элементы PQ есть у меня, но только тогда, когда происходит обновление расписания. Далее работа только формулами. Все же можно было бы сделать это формулу динамической? Недостаточно ли будет просто сослаться на динамические диапазоны? Или так просто оно не сработает?

И еще самое главное! Почему-то на листе Определение замены строка 2 пишет, что урок занят, но согласно листу Учителя строке 10 - учитель свободен

П.с. фактически на листе Определение замены вы видите только свободных учителей. Там формула с применением фильтра стоит. Поэтому, определение свободен ли учитель или нет - избыточно

Хочу дополнить, что я, не понимая сложности задачи, сейчас очень удивлен насколько сложна формула для нахождения ближайшего этого числа "от ближайшего" урока по расписанию

Прикрепленные файлы

Вопрос по формуле_260224.xlsb (238.22 КБ)

Изменено: extrafant - 26.02.2024 12:16:32

R091n

Пользователь

Сообщений: 404 Регистрация: 17.10.2023

#11

26.02.2024 18:35:50

Цитата
extrafant написал: пишет, что урок занят

Диапазон был сдвинут на 1. Протестируйте такой вариант (в файле).

Как я понял, у вас Office 365. Там возможностей побольше. А может кто еще что подскажет.
Формула для 365 (динамический массив полностью вся ваша табличка):

Скрытый текст

Код

=LET(teacher_substitution; CHOOSECOLS(FILTER(Учителя[[Учитель]:[Нагрузка в неделю]];
                                             (Учителя[Учитель]<>ПОИСК!$G$1)
                                             *(Учителя[День число]=ПОИСК!$B$2)
                                             *(Учителя[Класс]="")*(Учителя[Занятость]="")
                                             *(ISNUMBER(MATCH(Учителя[Урок];ПОИСК!$D$5:$D$18;0)));"");1;3;8;12;13);
     days_lst; CHOOSECOLS(teacher_substitution;3);
     teachers_lst;CHOOSECOLS(teacher_substitution;1);
     lesson_numb_lst; CHOOSECOLS(teacher_substitution;2);
     lesson_busy_lst; MAP(days_lst;teachers_lst;lesson_numb_lst;
                          LAMBDA(day;name;numb_l;
                                 LET(day_number;day;
                                     teacher_name;name;
                                     lesson_number;numb_l;
                                     day_shift;MATCH(day_number;Учителя[День число];0);
                                     teacher_range; INDEX(Учителя[Учитель];day_shift;):Учителя[[#Totals];[Учитель]];
                                     start_cell; MATCH(teacher_name; teacher_range;0)+day_shift-1;
                                     end_cell; start_cell+13;
                                     subject_list; INDEX(Учителя[Предмет]; start_cell):INDEX(Учителя[Предмет]; end_cell);
                                     busy_classes; IF(subject_list<>0;SEQUENCE(14));
                                     first_lesson; MIN(busy_classes);
                                     end_lesson;MAX(busy_classes);
                                     min_comparison_list;first_lesson-SEQUENCE(14)-1;
                                     max_comparison_list;SEQUENCE(14)-end_lesson-1;
                                     waiting_lesson; MAP(min_comparison_list; max_comparison_list; busy_classes; LAMBDA(min_l;max_l;busy_l; IF(NOT(busy_l); MAX(0; min_l; max_l);"занят")));
                                     wl_nuber; INDEX(waiting_lesson; lesson_number);
                                     wl_nuber)));
       HSTACK(teacher_substitution; lesson_busy_lst))

Интересный опыт, так как в 365 не работал, практически. В этой формуле нет проверок пустого дня и если пустой урок попадает в зазор между уже имеющимися уроками, то выводится 0. Потестируйте.

Прикрепленные файлы

Вопрос по формуле_260224_1.xlsb (238.02 КБ)

Изменено: R091n - 26.02.2024 21:22:56

extrafant

Пользователь

Сообщений: 380 Регистрация: 22.07.2017

#12

27.02.2024 10:18:02

Уважаемый R091n
Я попробую адаптировать в свою таблицу формулу с LET. А нет ли на русском этой формулы? Я понимаю, что LET, Lambda - они такие, но вот CHOOSECOLS, FILTER, INDEX и др...а то я, боюсь, вообще не смогу ее адаптировать. Выглядит, конечно, очень круто

R091n

Пользователь

Сообщений: 404 Регистрация: 17.10.2023

#13

27.02.2024 10:32:19

Прогнал через транслятор:

Скрытый текст

Код

=LET(teacher_substitution; CHOOSECOLS(ФИЛЬТР(Учителя[[Учитель]:[Нагрузка в неделю]];
                                             (Учителя[Учитель]<>ПОИСК!$G$1)
                                             *(Учителя[День число]=ПОИСК!$B$2)
                                             *(Учителя[Класс]="")*(Учителя[Занятость]="")
                                             *(ЕЧИСЛО(ПОИСКПОЗ(Учителя[Урок];ПОИСК!$D$5:$D$18;0)));"");1;3;8;12;13);
     days_lst; CHOOSECOLS(teacher_substitution;3);
     teachers_lst;CHOOSECOLS(teacher_substitution;1);
     lesson_numb_lst; CHOOSECOLS(teacher_substitution;2);
     lesson_busy_lst; MAP(days_lst;teachers_lst;lesson_numb_lst;
                          LAMBDA(day;name;numb_l;
                                 LET(day_number;day;
                                     teacher_name;name;
                                     lesson_number;numb_l;
                                     day_shift;ПОИСКПОЗ(day_number;Учителя[День число];0);
                                     teacher_range; ИНДЕКС(Учителя[Учитель];day_shift;):Учителя[[#Итоги];[Учитель]];
                                     start_cell; ПОИСКПОЗ(teacher_name; teacher_range;0)+day_shift-1;
                                     end_cell; start_cell+13;
                                     subject_list; ИНДЕКС(Учителя[Предмет]; start_cell):ИНДЕКС(Учителя[Предмет]; end_cell);
                                     busy_classes; ЕСЛИ(subject_list<>0;ПОСЛЕД(14));
                                     first_lesson; МИН(busy_classes);
                                     end_lesson;МАКС(busy_classes);
                                     min_comparison_list;first_lesson-ПОСЛЕД(14)-1;
                                     max_comparison_list;ПОСЛЕД(14)-end_lesson-1;
                                     waiting_lesson; MAP(min_comparison_list; max_comparison_list; busy_classes; LAMBDA(min_l;max_l;busy_l; ЕСЛИ(НЕ(busy_l); МАКС(0; min_l; max_l);"занят")));
                                     wl_nuber; ИНДЕКС(waiting_lesson; lesson_number);
                                     wl_nuber)));
       HSTACK(teacher_substitution; lesson_busy_lst))

extrafant Пользователь Сообщений: 380 Регистрация: 22.07.2017	#14 27.02.2024 11:22:09 Через транслятор частично перевелись. А именованные диапазоны как можно понять к какой части таблицы их отнести?

R091n Пользователь Сообщений: 404 Регистрация: 17.10.2023	#15 27.02.2024 11:36:38 Приложил файл. Динамический массив в зеленных ячейках. Прикрепленные файлы Вопрос по формуле_270224.xlsb (234.24 КБ)

extrafant Пользователь Сообщений: 380 Регистрация: 22.07.2017	#16 27.02.2024 11:38:31 Огромное спасибо! Ушел пробовать

extrafant

Пользователь

Сообщений: 380 Регистрация: 22.07.2017

#17

28.02.2024 12:15:24

2R091n
Функция на зеленом фоне - высший пилотаж! Я даже поверить не могу, что эксель может так быстро работать, но в ней что-то не так.
В строке 2 (столбец F) результате -1 (ждать -1 урок (что правильно), а на зеленом фоне в результате 0 (что не правильно)
И еще ко всему этому ABS можно будет добавить? А то ждать -1 как-то неправильно. Должно быть просто 1

R091n

Пользователь

Сообщений: 404 Регистрация: 17.10.2023

#18

28.02.2024 13:31:07

Цитата
extrafant написал: результате -1

Если вы о том же примере, там у учителя 1-8 и 10 уроки. То есть 9 урок попадает между ними. (Что кстати означает ожидание 0 фактически а не 1) В первой моей функции для таких уроков проставлялось -1, а во второй(зеленое поле) 0, я об этом писал. Предложите способ как это должно считаться, посмотрю можно ли его реализовать. Для примера, у учителя 4 урока - 1,2 и 13,14. Как должно считаться ожидание для 5 и 9 уроков?

extrafant Пользователь Сообщений: 380 Регистрация: 22.07.2017	#19 28.02.2024 14:12:18 Пожалуй, 0 это хороший выход. Правильный. Но, тогда, как показать тех, у кого нет уроков в этот день? Может, тогда цифра условная? 99?

R091n

Пользователь

Сообщений: 404 Регистрация: 17.10.2023

#20

28.02.2024 14:23:01

Цитата
extrafant написал: цифра условная? 99?

Можно и так.