В прошлый раз мы закончили на том, что научили Эксель подбирать один параметр, чтобы найти решение. Сегодня пойдём дальше: покажем ему все наши условия и ограничения и попросим решить задачу самому. То есть, по сути, заставим Эксель думать за нас.
В чём идея
Представим, что мы решили запустить свой бизнес: доставку и упаковку подарочных наборов из Китая:
- Заказываем товары на определённую сумму.
- Упаковываем красиво в подарочную бумагу и пакеты с бантами.
- Продаём дороже.
- Получаем разницу в цене — это прибыль.
Упаковщик у нас один, и работает он по 8 часов в день с понедельника по пятницу — в месяц получается в среднем 22 рабочих дня. Это нам тоже нужно будет учесть в расчётах, потому что на упаковку каждого товара нужно время.
Давайте посмотрим, что нужно заказать и в каком количестве, чтобы получить максимальную прибыль.
Готовим таблицу
Мы нашли 5 китайских товаров, которыми нам было бы интересно заняться:
Сразу добавим колонку с количеством заказов и учтём, что у каждого поставщика есть минимальный и максимальный размер партии, которую он готов нам отгрузить:
Теперь добавим колонки с временем на упаковку: каждого товара в отдельности и всего по каждой позиции. Указывать будем в минутах. Чтобы получить общее время на упаковку по каждой позиции, используем формулу:
Время на упаковку всей позиции = время упаковки одного товара × количество товаров
На языке Экселя это выглядит для первой строки так:
=E2*B2
Протянем мышкой эту ячейку вниз на 5 клеток, чтобы Эксель сам пересчитал формулы для соответствующих товаров. У нас пока нет ни одного заказанного товара, поэтому везде по нулям:
Добавим колонки с закупочной ценой и ценой, по которой мы будем продавать готовые подарки. Сразу же добавим последнюю колонку «Доход» — в ней мы посчитаем наш доход с каждой позиции:
Доход = (цена продажи − закупочная цена) × количество товаров
На языке формул это будет выглядеть так:
=(H2-G2)*B2
Тоже протягиваем ячейку с формулой вниз, чтобы всё пересчиталось на каждой строчке:
Финальный штрих: добавим внизу строку «Итого», чтобы в ней посчитать наш бюджет и будущий доход. Смысл в том, что мы не можем превысить наш закупочный бюджет, потому что в долг нам никто ничего не пришлёт.
Чтобы посчитать сумму на закупки и сумму дохода, используем функцию СУММ():
=СУММ(I2:I6) ← считаем общую сумму дохода
А для подсчёта бюджета мы умножаем количество каждого заказанного товара на его стоимость и складываем так всё по каждой позиции:
=B2*G2+B3*G3+B4*G4+B5*G5+B6*G6
Напоследок посчитаем общие затраты времени на упаковку: без этого мы не сможем понять, успеет наш упаковщик всё сделать или нет:
=СУММ(F2:F6)
Запрягаем Эксель
Мы используем встроенную функцию «Данные» → «Поиск решения» (в некоторых версиях эта функция находится в разделе «Данные» → «Анализ „Что если“» → «Поиск решения»):
Перед нами появится окно с параметрами поиска решения — в нём мы зададим все условия, а Эксель сам подберёт всё остальное.
Оптимизировать целевую функцию — указываем ячейку с общим доходом. Ставим параметр «Макс» — так Эксель будет стараться подобрать остальные значения так, чтобы значение в целевой ячейке стало максимальным. То что нам нужно.
Изменяя ячейки переменных — тут указываем колонку «Сколько заказываем». Значения в этой колонке и будет подбирать Эксель, чтобы дать нам максимальный доход.
В соответствии с ограничениями — тут нам нужно указать все ограничения, которые у нас есть. Остановимся подробнее.
Первое ограничение — время работы упаковщика. 22 рабочих дня по 8 часов — это 22 × 8 × 60 = 10560 минут. Добавим это условие:
Бюджет тоже не безграничный — допустим, миллион в месяц на закупки:
И теперь добавляем ограничения на размер минимальной и максимальной партии для каждого товара:
Дополнительно указываем, что нам нужны для заказа целые числа — мы не можем заказать пять с половиной клавиатур, например:
Так указываем все ограничения, которые у нас есть (можно сравнивать диапазоны между собой, чтобы было быстрее), и нажимаем «Найти решение»:
Эксель подумает 10–20 секунд и выдаст решение:
Получилось любопытно: у нас даже остался запас по бюджету, но полностью выбрано время на упаковку товаров.
Что дальше
Наш пример — самый простой из тех, что может считать Эксель самостоятельно и находить наилучшие решения. При желании можно в Экселе построить систему управления предприятием, оптимизации затрат или схему оптимального запуска своего дела в условиях ограничений и неопределённости. Попробуем что-нибудь из этого в следующий раз.
