Поиск решения Excel: как составить план проверок материалов курса

Сначала договоритесь, что оптимизируете

Представим вымышленную команду курса, которая готовит материалы к редакционной встрече. Есть шесть коротких проверок по 15 минут и четыре длинные по 25 минут. Редактор располагает 120 минутами; как минимум две длинные проверки нужны для предстоящего обсуждения. Требуется выбрать максимальное количество завершённых проверок в пределах этого времени.

Все числа придуманы для учебного расчёта. Предполагается, что внутри каждой группы работы требуют одинакового времени и имеют заранее согласованный критерий завершения. Прерванная проверка не считается готовой. Если задачи существенно различаются по важности, сначала нужно выразить обязательные приоритеты в условиях модели.

Максимум количества удобен не для любого рабочего дня. Он мог бы вытеснить важную длинную работу множеством мелких. Поэтому до расчёта обсудите приоритеты автора курса по матрице Эйзенхауэра, а затем решайте, какие ограничения обязательны именно сегодня.

Подготовьте ячейки и формулы

Создайте лист со столбцами A — вид проверки, B — запланированное количество, C — минуты на одну, D — суммарное время, E — доступное количество. Во второй строке запишите короткие проверки, в третьей — длинные. В B2 и B3 сначала поставьте нули: эти значения впоследствии будет менять расчёт.

Для коротких проверок задайте C2 = 15 и E2 = 6, для длинных C3 = 25 и E3 = 4. В D2 введите формулу =B2*C2, в D3 — =B3*C3. В B5 разместите =B2+B3, а в D5 — =D2+D3. Первая итоговая ячейка считает готовые проверки, вторая — занятые минуты.

Проверьте зависимости вручную: если временно поставить B2 = 1 и B3 = 1, B5 должна показать 2, а D5 — 40. Затем верните исходные нули. Эта простая проба помогает обнаружить неверную ссылку до запуска надстройки. Подпишите итоги рядом, чтобы после перерыва не перепутать количество с продолжительностью.

Включите надстройку и задайте ограничения

Пример рассчитан на настольный Excel для Windows. Если команды нет на вкладке «Данные», откройте «Файл» → «Параметры» → «Надстройки», выберите управление надстройками Excel и включите «Поиск решения». Порядок описан в инструкции Microsoft по загрузке Solver.

В «Поиске решения» укажите целевую ячейку B5 и максимум. Изменяемые ячейки — B2:B3. Добавьте условия:

• D5 ≤ 120: общий запас времени.

• B2 ≤ E2 и B3 ≤ E3: нельзя проверить больше имеющихся работ.

• B3 ≥ 2: обязательные длинные проверки.

• B2:B3 ≥ 0 и B2:B3 — целые: отрицательные и дробные количества запрещены.

Выберите метод Simplex LP для этой линейной модели и запустите решение. Настройка целевой формулы, переменных и ограничений описана в руководстве Microsoft по Solver. Та же документация указывает, что эта надстройка не поддерживается в Excel для веба; для повторения сценария потребуется настольное приложение.

Проверьте оба оптимальных плана

У нашей модели два плана с максимальным количеством шесть. Первый — четыре короткие и две длинные проверки: 4 × 15 + 2 × 25 = 110 минут. Второй — три короткие и три длинные: 3 × 15 + 3 × 25 = 120 минут. Оба соблюдают доступные количества и минимум длинных работ.

Почему нельзя завершить семь? При обязательных двух длинных самый короткий такой набор содержит ещё пять коротких: 2 × 25 + 5 × 15 = 125 минут. Замена короткой проверки длинной только увеличивает время. Значит, семь работ уже не помещаются, и найденные шесть действительно являются максимумом при наших условиях.

Не ожидайте, что надстройка обязательно вернёт первый план: цель не различает два оптимума. Если важен десятиминутный резерв, выберите первый после проверки. Если нужны дополнительные длинные работы, второй может быть полезнее. Чтобы это решение принимала сама модель, придётся явно сформулировать дополнительное правило предпочтения.

Разберите ошибки постановки по результату

Дробное количество проверок означает, что ограничение целочисленности забыто или назначено не тем ячейкам. Итог больше десяти работ при исходном запасе шесть и четыре — повод проверить верхние границы. Нулевая длинная проверка недопустима даже при большом общем количестве: обязательный минимум нельзя заменять пожеланием в примечании.

Если допустимого решения нет, сначала перечитайте условия. Например, требование выполнить все четыре длинные и все шесть коротких проверки потребовало бы 190 минут. Оно несовместимо со 120 минутами. Исправлять нужно рабочую договорённость или запас времени, а не формулу так, чтобы таблица показывала удобный результат.

Не удаляйте ограничения по одному без записи причины. Сохраните исходную постановку и новую версию рядом: что изменили, кто принял решение, какие последствия это имеет для редакционной встречи. Тогда расчёт остаётся объяснимым планом, а не набором случайных настроек.

Сопоставьте расчёт с реальной работой

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

Для пересмотра оценок используйте прошлый опыт против ошибки планирования. Не пытайтесь объяснить любое превышение недостаточной скоростью редактора: могли измениться критерии готовности или состав задачи.

Передавайте в работу список конкретных материалов, соответствующий выбранным количествам. Сохраните рядом дату расчёта, ограничения и резерв. В Umhub размещайте материалы после фактического завершения проверок: математически допустимый план ещё не подтверждает готовность урока к публикации.