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

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

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

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

В чём идея

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

  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 секунд и выдаст решение:

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

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

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

Что дальше

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

Текст:

Михаил Полянин

Редактор:

Максим Ильяхов

Художник:

Даня Берковский

Корректор:

Ирина Михеева

Вёрстка:

Кирилл Климентьев

Соцсети:

Олег Вешкурцев

Получите ИТ-профессию
В «Яндекс Практикуме» можно стать разработчиком, тестировщиком, аналитиком и менеджером цифровых продуктов. Первая часть обучения всегда бесплатная, чтобы попробовать и найти то, что вам по душе. Дальше — программы трудоустройства.
Начать карьеру в ИТ
Получите ИТ-профессию Получите ИТ-профессию Получите ИТ-профессию Получите ИТ-профессию
Еще по теме
Головоломки для ИТ-собеседований
Головоломки для ИТ-собеседований

Четыре метода, которые помогают с решением.

easy
Находчивый инженер в кафе

Как логика побеждает разгильдяйство.

medium
Решаем кодом задачу про наноботов

Вся программа — 9 строк.

easy
Задача про начальника транспортного цеха

Что быстрее — вспомнить формулы за 7 класс или написать программу для решения?

easy
Задачка — Мама против вирусов
Мама против вирусов

Коронавирусная задачка на математику.

easy
Задача Эйнштейна

Учёный утверждал, что только 2% людей могут решить в уме эту задачу (так говорят в Википедии).

hard
Как взорвать ракету одной переменной
Как взорвать ракету одной переменной

Краткий мастер-класс по правильному объявлению типов данных.

easy
Задача о деньгах

Пропало 300 рублей, но никто этого не заметил.

medium
Тревелблогеры в пустыне

Математика против дешевых инстаграмных амбиций.

easy
Сложная задача на логику
Сложная задача на логику

Про поезд-убийцу и быстро бегающих людей.

hard
medium