38 324 subscribers

Как заставить Эксель думать за тебя

1,4k full reads

Находим оптимальное решение с любыми параметрами

Как заставить Эксель думать за тебя

В прошлый раз мы закончили на том, что научили Эксель подбирать один параметр, чтобы найти решение. Сегодня пойдём дальше: покажем ему все наши условия и ограничения и попросим решить задачу самому. То есть, по сути, заставим Эксель думать за нас.

В чём идея

Представим, что мы решили запустить свой бизнес: доставку и упаковку подарочных наборов из Китая:

  1. Заказываем товары на определённую сумму.
  2. Упаковываем красиво в подарочную бумагу и пакеты с бантами.
  3. Продаём дороже.
  4. Получаем разницу в цене — это прибыль.

Упаковщик у нас один, и работает он по 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 секунд и выдаст решение:

Как заставить Эксель думать за тебя

Получилось любопытно: у нас даже остался запас по бюджету, но полностью выбрано время на упаковку товаров.

Скачать файл с примером.

Что дальше

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