Контроль: В результате подбора предполагаемая ставка в ячейке С5 получится 15,48 %.

6. Закройте MS Excel, сохранив файл в своей папке под именем Upr25.

Упражнение.

Задание:
Вклад в сумме 100 000 руб. размещается в банк на 3 года с ежемесячным начислением сложных процентов. Предполагаемый уровень инфляции 1,5% в месяц. Определите с помощью подбора параметра какая годовая ставка по вкладам обеспечит доход от вклада 10 000 руб. с точки зрения покупательной способности.

Образец

Метод расчета:

J=(1+t)n;

;

R=S-Начальный вклад.

Ход выполнения:

1. Откройте файл «Upr23» из своей папки.

2. Заполните таблицу новыми данными.

3. Откорректируйте формулу в ячейке В6.

4.С помощью Подбора параметра определите годовуюставку, которая обеспечит реальныйдоход от вклада 10 000 руб.

Контроль: В результате в ячейке В2 подберется годоваяставка 21,23%, которая обеспечит реальныйдоход от вклада 10 000 руб.

9. Закройте MS Excel, не сохраняя изменений в файле.

УРОК 30.

Тема урока:
Использование Поиска решения для определения оптимального плана производства.

Упражнение.

Задание:
Необходимо найти оптимальное соотношение объемов выпуска различных сортов конфет, так чтобы общая прибыль от реализации была максимальной. Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены: сахара 1000 кг, какао 700 кг, наполнителя 400 кг, ароматизатора 600 кг, сухого молока 250 кг. Каждому сорту конфет соответствует своя норма прибыли: от 1 кг конфет «Старт» 37 рублей, «Ириса» - 40 рублей, «Му-Му» - 52 рубля, «Ария» - 72 рубля, «Фея» - 65 рублей. Нормы расхода сырья на производство 1 кг конфет каждого вида приведены в таблице:

  Расход сырья по сортам (на 1 кг конфет)
  Сахар Какао Наполнитель Ароматизатор Сухое молоко
Старт 0,360 г. 0,120 г. 0,120 г. 0,250 г. 0,000 г.
Ирис 0,490 г. 0,000 г. 0,000 г. 0,250 г. 0,200 г.
Му-му 0,350 г. 0,000 г. 0,200 г. 0,350 г. 0,120 г.
Ария 0,370 г. 0,450 г. 0,210 г. 0,200 г. 0,150 г.
Фея 0,340 г. 0,320 г. 0,230 г. 0,210 г. 0,100 г.

Образец

Метод расчета:

Прибыль считается по формуле:

,

где с1 стоимость 1 кг конфет Старт, с2 ‑ 1 кг конфет Ирис, с3 ‑ 1 кг конфет Му‑му, с4 ‑ 1 кг конфет Ария, с5 ‑ 1 кг конфет Фея.

х1 план производства конфет Старт, х2 ‑ конфет Ирис, х3 ‑ конфет Му‑му, х4 ‑ конфет Ария, х5 ‑ конфет Фея.

При этом должны обеспечиваться ограничения по сырью:

где аij расход i‑го типа сырья на 1 кг конфет j‑го вида(например: а11 расход сахара на 1 кг конфет Старт, а12 ‑ на 1 кг конфет Ирис, а13 ‑ на 1 кг конфет Му‑му, а14 ‑ на 1 кг конфет Ария, а15 ‑ на 1 кг конфет Фея, а21 расход какао на 1 кг конфет Старт, а22 ‑ на 1 кг конфет Ирис, а23 ‑ на 1 кг конфет Му‑му, а24 ‑ на 1 кг конфет Ария, а25 ‑ на 1 кг конфет Фея, а31 расход наполнителя на 1 кг конфет Старт и т.д.)

b1 запас сахара, b2 запас какао, b3 ‑ наполнителя, b4 ‑ ароматизатора, b5 ‑ сухого молока.

По смыслу задачи:

Ход выполнения:

1.Откройте файл «Upr16» из папки Primer.

2. Заполните таблицу данными по образцу:

Подсказка: Ячейки выделенные фоном содержат формулы, в остальные введите числа или текст

2.1.В ячейки А1:Н2, А3:А7, А8:В10 наберите соответствующие текстовые комментарии.

2.2.Ячейки В3:G7, C8:G9 заполните числами.

3.Организуйте в таблице формулы:

3.1.Общий расход каждого сырья на производство всех конфет складывается из суммы произведений плана производства конфет, соответствующего вида на расход данного сырья:

· Вызовите функцию СУММПРОИЗВиз категорииматематических в ячейку Н3;

· Определите ее аргументы;

· Для использования полученной формулы маркером заполнения один из диапазонов в окне Аргумента функции переведите в абсолютный вид (с помощью клавиши F4);

· Ок.

Наши рекомендации