Ниже создайте таблицу «Компоненты автомобильного бензина». В строках укажите данные по каждой компоненте и единицы измерения, а в столбцах номера компонент, введите исходные значения и оформите таблицу как показано на рисунке.
Ниже создайте таблицу «Решение». В строках введите названия параметров «Количество компонента в смеси», «Доля октанового числа», «Доля серы», «Стоимость» и их единицы измерения.
В ячейках таблицы для каждой компоненты введите формулы для вычисления доли октанового числа, доли серы и стоимости. Доля октанового числа вычисляется по формуле =(Количество компонента в смеси)/(Требуемое количество)*(Октановое число), например для компоненты №1 это =D14/D4*D8. Аналогично необходимо занести формулы для вычисления доли октанового числа для других компонент. Доля серы вычисляется по формуле =(Количество компонента в смеси)/(Требуемое количество)*(Содержание серы), например для компоненты №1 это =D14/D4*D9. Стоимость каждой компоненты, необходимой для производства заданного количества смеси вычисляется по формуле (Количество компонента в смеси)*(Себестоимость). Внесите необходимые формулы в ячейки таблицы. Обратите внимание, что на рисунке приведено готовое решение, Вам необходимо в строке «Количество компонента в смеси» указать нулевые значения.
Правее таблицы «Решение» создайте таблицу «Суммарные показатели». В первой ячейке просуммируйте количество всех компонент в смеси из таблицы «Решение». Во второй ячейке просуммируйте все доли октанового числа из таблицы «Решение». В третьей ячейке просуммируйте все доли серы из таблицы «Решение». В четвертой ячейке просуммируйте себестоимость из таблицы «Решение».
Наши таблицы готовы для
задания условий подбора решения.
Итак, ограничения, наложенные на решение задачи:
- Количество каждого из компонентов (изменяемые данные в таблице «Решение») не может быть больше, чем имеющееся количество этих компонентов (исходные данные таблица «Компоненты автомобильного бензина»), (Количество компонента в смеси) <= (Имеющиеся ресурсы) для каждого компонента;
- Используемое количество каждого компонента должно быть целым числом, (Количество компонента в смеси) = целое;
- Количество смеси (сумма количеств используемых компонент в таблице «Суммарные показатели») должна быть равна заданному (требуемому) количеству смеси, (Сумма количеств используемых компонент)=(Требуемое количество);
- Октановое число смеси, в таблице «Суммарные показатели», должно быть не меньше заданного (может быть больше), (Октановое число смеси)>=(Октановое число);
- Доля серы, в таблице «Суммарные показатели», не может быть больше допустимого количества (может быть меньше), (Доля серы)<=(Содержание серы).
Для подбора решения необходимо воспользоваться пунктом меню Сервис Поиск решения. В окне задание параметров решения необходимо указать - Целевая ячейка это итоговая стоимость смеси бензина (I17) (ищется минимальное значение), Изменяемые ячейки это количество компонентов в смеси (D14:G14). Далее необходимо задать ограничения исходя из вышеприведенных условий.
Задав все параметры, выполните подбор решения.
Сохраните рабочую книгу.
Выделите рабочий Лист1 и скопируйте его на рабочий Лист2. Измените октановое число компонента №2 на 46. Выполните подбор решения для новых условий.
Объясните полученный результат.
Сохраните рабочую книгу.
Выделите рабочий Лист1 и скопируйте его на рабочий Лист3. Измените октановое число компонента №2 на 46 и уменьшите общее количество смеси до 1000. Выполните подбор решения для новых условий.
Объясните полученный результат. Дополните условия, наложенные на задачу так, чтобы результат не противоречил здравому смыслу.
Создайте новый лист, на котором создайте диаграммы для всех вариантов, отражающие объемные доли компонентов смеси в результирующем бензине.
Сохраните рабочую книгу Бензин_ВашаФамилия.xsl
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.