Инструмент Подбор параметра в меню Сервис позволяет найти значение аргумента, удовлетворяющее желаемому значению функции. С его помощью можно получить результаты, которые трудно или невозможно получать непосредственно.
Пример. Пусть мы владелец фирмы и договорились с некоторым исполнителем (Петром) о выполнении разовой работы за 5000 руб. «чистыми» и с другим исполнителем (Олегом) – за 300 руб. Пусть зарплата в фирме вычисляется по формулам таблицы
Налогом облагается не вся зарплата. Согласно законодательству, сумма обложения меньше заработка на одну минимальную зарплату. Здесь учитываются вычеты в медицинскую страховку и пенсионный фонд (1% и 2% соответственно). Чтобы избежать отрицательности суммы обложения, в клетке D4 используется функция МАКС (), которая обеспечивает равенство нулю суммы обложения, если она становится отрицательной.
В верхней части таблицы представлены формулы, в нижней – результат для одного штатного работника организации Ивана. Для него расчет ведется в обычном порядке – вводится заработок (1000 руб.) и автоматически вычисляется результат (886 руб.)
Сейчас нужно определить исходную зарплату Петра и Олега по известной сумме «на руки».
Рис.3
Сначала для Петра. Скопируем в строки 5 и 6 формулы из строки 4 и запустим инструмент Подбор параметра, где введем данные, изображенные на рис.2. Аналогично поступим с Олегом (рис.3).
Рис.4 Рис.5
Видим, что Петру нужно выписать 5854 рублей «грязными». В случае с Олегом (маленькая зарплата и много детей) начисленная сумма и сумма на руки (300 руб.) совпадают. Полученные результаты показаны на рис.4.
Рис.6
Можно легко убедиться в правильности вычислений, если запустить процесс Подбор параметра для Ивана, задав ему сумму на руки, равную 886 руб. Тогда увидим, что вычисленное исходное значение будет, как и ранее, равно 1000.
Поиск решения. Инструмент Поиск решения из меню Сервис предоставляет пользователю гораздо более мощное аналитическое средство. Здесь можно искать решение систем уравнений, которые к тому же могут содержать ограничения. К таким задачам относятся важные для планирования коммерческой деятельности задачи линейного и нелинейного программирования.
Пример. Транспортная задача.
Очень часто возникает проблема оптимальной доставки некоторого объема груза из нескольких исходных пунктов (например, складов) в несколько пунктов доставки (например, магазинов). При этом обычно подразумевается, что объем имеющего груза равен объему запрошенного. Получим решение такой задачи средствами Excel.
Пример. Пусть с трех складов требуется развести грузы в объемах 50, 30 и 40 тонн потребителям в 2 пункта доставки в объеме 40 и 80 тонн (рис.5). Известна цена перевозки единицы груза с каждого склада в каждый пункт доставки (столбцы С и Е). Наша задача заключается в следующем: определить такие объемы перевозок со складов в пункты доставки, чтобы стоимость транспортировки была минимальной. На рис.5 искомые значения уже вычислены и обведены жирной рамкой. Видим, что стоимость всего «мероприятия» составляет 1300 руб. Рассмотрим, как были получены эти значения. Прежде всего, в ячейку С8 заносим целевую функцию. Здесь это стоимость всех «элементарных» перевозок, вычисляемая как сумма произведений цены на объем груза
С8=С3*D3+E3*F3+C4*D4+E4*F4+C5*D5+E5*F5.
Рис.7
Для решения используем инструмент Поиск решения, где введем следующие параметры:
Установить целевую ячейку С8 равной минимальному значению.
Изменяя ячейки: D3:D5; F3:F5.
Ограничения:
B3=D3+F3 стоимость вызова с 1-го склада
B4=D4+F4 стоимость вызова с 2-го склада
B5=D5+F5 стоимость вызова с 3-го склада
D6=D3+D4+D5 стоимость доставки в 1-й пункт
F6=F3+F4+F5 стоимость доставки в 2-й пункт
F3:F5>=0 условие положительности стоимости
D3:D5>=0 условие положительности стоимости
В данном примере предполагалась перевозка груза, измеряемого в весовых единицах, расфасовка которого по транспортным средствам безразлична. Если же имеется ввиду перевозка чего-то крупного и неделимого, то следует ввести дополнительные ограничения на целочисленность перевозимых объемов:
D3:D5 = целое и F3:F5 = целое
Результаты в этом случае будут одинаковы: в первый пункт доставки направляются грузы в объеме 7т, 30т, 3т с каждого из трех складов соответственно, во второй – 43т, 0т, 37т. Значение целевой функции осталось как и прежде – 1300 руб.
Анализ данных на примере транспортной задачи
Дано: четыре фабрики и их производственные возможности в условных
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.
