- •215 Второй способ решения
- •217 Ироц. Ставка, в ячейку а9 - текст Будущая стоимость. Скопируем интервал «чеек b2:g2 в интервал b7:g7, а интервал b3:g3 в интервал b8:g8.
- •1. Разработать компьютерную модель решения задачи.
- •2. Ввести конкретные значения параметров и решить задачу.
- •Требуется:
- •1. Формулы для расчета выходных показателей имеют вид:
- •Вариант 5
1. Разработать компьютерную модель решения задачи.
2. Ввести конкретные значения параметров и решить задачу.
Первый способ решения Математические основы решения задачи. Рассматриваемая т представляет собой общий случай подобных задач. В общем * имеем следующие выражения для будущей стоимости (см. зад текущей лабораторной работы):
т
|
А |
в....... |
С D Е ,| F G |
|
||||
Вычислительная схема решения специального вида |
|
|||||||
5 |
Исходная сумма |
1000 |
|
|
|
|
|
|
в |
Срок |
|
0 |
1 |
2 |
3 |
4 |
|
7 |
Процентная ставка |
Вклады |
0% |
10% |
20% |
30% |
40% |
|
8 |
0% |
1000 |
1000 |
1100 |
1300 |
1600 |
2000 |
|
9 |
10% |
40 |
|
40 |
48 |
60 |
76 |
|
10 |
20% |
40 |
|
|
40 |
52 |
68 |
|
11 |
30% |
40 |
|
|
|
40 |
56 |
|
12 |
40% |
40 |
|
|
|
|
40 |
|
13 |
50% |
40 |
|
|
|
|
|
|
14 |
Буд. стоимость |
|
1000 |
1140 |
1388 |
1752 |
2240 |
219
Л
(
+ Л
i+Zi
-2
+ ... + Ап
(«0 ^ (-1 J
Непосредственное построение такой формулы в Нйтруднительно, поэтому удобнее пользоваться I ииределением:
рамках Excel рекуррентным
(4)
Построение компьютерной модели и решение задачи. В рамках
| llxcel подготовим модель решения. Пусть N = 5. Разместим исходные шишые задачи в первых четырех строках электронной таблицы (табл. 62): ряд 0 12 3 4 5В интервале ячеек B2:G2, ряд вкладов: 1000 40 50 80 70 80 в интервале B3:G3, ряд процентных ставок: 10% 20% 30% 40% 50%
и интервале B4:G4.
Таблица 62
|
А |
в |
с |
D |
Е |
F |
G |
|
Срок |
0 |
1 |
2 |
3 |
4 |
5 |
Вклады |
1000 |
40 |
50 |
60 |
70 |
80 |
|
|
Процентная ставка |
0% |
10% |
20% |
30% |
40% |
50% |
Объединим ячейки A4:G4 в одну и введем в нее текст Рекуррентная шычислительная схема решения, в ячейку А5 - текст Срок, в ячейку А6 -н-кст Вклады, в ячейку А7 — текст Процентная ставка, в ячейку А8 - текст Ьудущая стоимость. Скопируем исходные данные задачи в формируемую с чему: интервал B2:G2 в интервал B5:G5, интервал B3:G3 в интервал B6:G6, интервал B4.G4 в интервал B7:G7.
Реализуем рекуррентное определение функции (4). В ячейку В8 введем формулу: =В6, в ячейку С8 - формулу: =В8+(СУММ($В$6:В6))*С7+С6.
Последнюю скопируем в интервал D8:G8. Получим следующую модель решения (рис. 89).
'.Ш
Срок
Вклады
Процентная ставка
1000
0%
30
10%
40
20%
50
30%
60
40%
G
5
70
50%
Срок
Рекуррентная вычислительная схема ращения
Вклады
[Процентная ставка
В {Будущая стоимость
1000
0%
40
10% I 20%
50
Б0_
30%
=В6 ^=В8+<СУММ($В«:В6)ГС7+С6|
70
40%
80
50%
Рис. 89. Модель решения задачи
220 Используя Автозаполнение скопируем формулу из ячейки С8 и • D8:G8. Все исходные данные задачи определены, поэтому пои решение (табл. 63).
Таблица
Срок
Рекуррентная вычислительная схема решения
уклады
Пр_оцентная ставка
^Будущая стоимость
Г "| и
0%
1000
40
10%
1140
50
20%
1398
60
30%
1785
70
40%
2315
Второй способ решения ^^^^_
В рамках Excel подготовим модель решения. Обычным о<>|> введем текстовые данные, скопируем ряды 0 1 2 3 4 5, 0% 10% 20% юк, 40% 50% и 40 50 60 70 80. В интервалы А7:А12 и В7:В12 последние дни \ччм вставим с использованием команды Специальная вставка. В ячей г введем формулу: =$В7*(1+СУММ($С$6.С$6)-СУММ($А$7:$А7)).
Завершив формирование модели решения аналои.
рассмотренному выше (см. задача I, второй способ), получим модед)
решения задачи (рис. 90).
__ ^ „ £:■;.,■#»' ч. «■■ р е r s г~х ;
1
2 3
4____
5 Срок
Процент
Последовательность процентных ставок
0%
10%
20%
30%
40%
Вычислительная схема решения специального вида
50%
6 Процентная ставка
7 ( 0%
10%
20% 30%
40%
50%
13 Будущая стоимость
[Вклады
0%
1000
10%
20%
~Э0%
40
50
70
.~ .У|________ЛЛЛЧ\________ШЛИЦ
:tB7'(f+CyMMitC$6: СЩ-СУМЩ$А$7 tA7J)T
40%
=СУММ(С7:С12)
60%
В
] С | D | Е I F |~С
5°!L
Вычислительная схема решения специального вида
--------------1----------------1------- л-----------п----
Рис. 90. Модель решения задачи ^^^^_
Все исходные данные задачи определены, поэтому получим решпнн задачи (табл. 64).
Таблица 64
I В
MI-K
2МИ1
ЦП
ЦП
I II
III-.
пи
.4(10'.
Ppj^e^n>«fljnaBKa
10
0%
10%
20%
30%
40%
50%
13|Будущая1ж>имость
Вклады
1000
40
50
60
70
80
0%
1000] ITOOI 130Q
looq
10%
М
ТЩ
20%| 30%] 40%
4Й
5CJ
1398
1600| 2000[
60
63
бЫ
1785]
7U
8d
84
7Й
231Я
221 Задания
Вариант 1 /. Решить задачу 1 двумя способами при следующих условиях:
р = 5%, N = 5_____________________
Последовательность вкладов
Срок
Вклады
0
3000
1
50
70
90
4
ПО
130
2. Решить задачу 2 двумя способами при следующих условиях:
^ = 50,N=5,/V=2000
---------------- |
Последовательность процентных ставок |
|
||||
Срок |
0 |
1 |
2 |
3 |
4 |
5 |
Процент |
0% |
20% |
30% |
40% |
~~1Ш" |
60% |
3. Решить задачу 3 двумя способами при следующих условиях:
Срок |
0 |
1 |
2 |
3 |
4 |
5 |
Вклады |
4000 |
35 |
45 |
55 |
65 |
75 |
11роцентная ставка |
0% |
10% |
20% |
30% |
40% |
50% |
Вариант 2 /. Решить задачу 1 двумя способами при следующих условиях:
|
|
Рш |
10%,/V = |
5 |
|
|
|
|
Последовательность вкладов |
|
|
||||
Срок |
0 |
1 |
2 |
3 |
4 |
5 |
|
Вклады |
4000 |
40 |
30 |
90 |
120 |
100 |
2. Решить задачу 2 двумя способами при следующих условиях:
_______А =40,^=5, Л) =1000__________________
Последовательность процентных ставок
рок_
I 11роцент
О
0%
1
10%
30%
50%
70%
90%
3. Решить задачу 3 двумя способами при следующих условиях:
Срок |
0 |
1 |
2 |
3 |
4 |
5 |
Вклады |
5000 |
30 |
45 |
60 |
75 |
90 |
Процентная ставка |
0% |
5% |
10% |
15% |
20% |
25% |
222
Вариант 3 /. Решить задачу 1 двумя способами при следующих условиях:
p = 2%,N=5
Последовательность вкладов
2. Решить задачу 2 двумя способами при следующих условиях:
A = 70,N=5,P0= 4000
Срок
Процент
Последовательность процентных ставок
0
0%
6%
12%
18%
24%
3. Решить задачу 3 двумя способами при следующих условиях:
5 30"..
Вариант 4 /. Решить задачу J двумя способами при следующих условиях:
p = 3%,N=5
Последовательность вкладов
2. Решить задачу 2 двумя способами при следующих условиях:
A=55,N=5, P0 = 2200
Срок
Процент
Последовательность процентных ставок
0%
4%
16%
28%
40%
3. Решить задачу 3 двумя способами при следующих условиях
520%
223
Вариант 5 /. Решить задачу 1 двумя способами при следующих условиях:
p=\5%,N=5
|
Последовательность вкладов |
|
|
|||
Срок |
0 |
1 |
2 |
3 |
4 |
5 |
Вклады |
2300 |
13 |
26 |
39 |
52 |
65 |
2. Решить задачу 2 двумя способами при следующих условиях:
А= 150,N=5,P0= 3500
|
Последовательность процентных ставок |
|
||||
Срок |
0 |
1 |
2 |
3 |
4 |
5 |
Процент |
0% |
4% |
8% |
12% |
16% |
20% |
3. Решить задачу 3 двумя способами при следующих условиях:
Срок |
0 |
1 |
2 |
3 |
4 |
5 |
Вклады |
4400 |
33 |
43 |
53 |
63 |
73 |
Процентная ставка |
0% |
1% |
10% |
19% |
28% |
37% |
Вариант 6 /. Решить задачу 1 двумя способами при следующих условиях:
p = 6%,N=5
|
Последовательность вкладов |
|
|
|||
Срок |
0 |
1 |
2 |
3 |
4 |
5 |
Вклады |
2300 |
21 |
36 |
51 |
66 |
81 |
2. Решить задачу 2 двумя способами при следующих условиях: A"55,N=S,Pq= 2600
|
Последовательность процентных ставок |
|
|
|
|||||||||||
Срок |
0 |
1 |
2 |
3 |
4 |
5 |
|
|
|||||||
Процент |
0% |
3% |
8% |
13% |
18% |
23% |
|
|
|||||||
3. Решить задачу 3 двумя способами при следующих условиях: |
|
||||||||||||||
Срок |
0 |
1 |
2 |
3 |
4 |
|
5 |
||||||||
Вклады |
4100 |
15 |
35 |
55 |
75 |
|
95 |
||||||||
Процентная ставка |
0% |
1% |
5% |
9% |
13% |
|
17% |
226
Лабораторная работа № 8
Методы решения задач с использованием средств
"Подбор параметра" и "Поиск решения"
Построение диаграмм и графиков _
Цель работы: изучение технологии построения компьютер моделей с использованием методов "Подбор параметра", "П< решения" и приобретение практических навыков постро. имя вычислительных схем. Изучение технологии построения диаграмм Ц графиков в среде Excel.
Содержание Изучаются вопросы:
1. Метод решения задач "Подбор параметра".
2. Метод решения задач "Поиск решения".
3. Построение различных графиков и диаграмм. Выполняется вариант задания.
Указания ^_
Смотреть п. 2.2.3 "Построение диаграмм и графиков" и п. 2 I
"Использование средства "Подбор параметра" и "Поиск решения".
Задача 1. Предположим, вы решили приобрести машину. Мумии
определить выгодно ли ездить на ней на работу?
Решение ч^иии
Будем считать, что для поездки на работу вы пользуетесь одиИМ видом транспорта: маршрутным такси. Следовательно, ежедпсиии. расходы на дорогу в оба конца составляют 16 рублей. ПримЩ стоимость бензина равной 22 рубля, а расход - 10 литров на 100 М| Следовательно, для вычисления затрат на один километр пути и цену бензина умножить на 0,1. Схема решения задачи представлен.! рис. 91.
1 Стоимость проезда в городском транспорте
2 Километраж в оба конца________......
3 Стоимости бензина______________________
4 Затраты на дорогу при пользовании бензином
"Разница между стоимостью проезда и [затратами на бензин_______
_щ
=ВЭ"р,ГВ2
шщ
'=В1-В*|
Рис. 91. Модель решения задачи Необходимо определить, при каком километраже сравнявши затраты на пользование городским и автомобильным транспортом i этого: выделим ячейку В5, в меню Сервис выберем команду и параметра. Заполним поля диалогового окна (рис. 92), т.е. требу! чтобы разность между затратами на проезд в городском транспорг. .
227 итратами на проезд на автомобиле была равна нулю, посредством изменения километража^
рЩУ[|з[ртрт^ДдЗ^В Установить в ячейке: Значение: Им*еняр знача в«:ячемк>
|
т |
^ |
щ |
|
|
|В5 |
|
-ы |
|||
1» |
|
|
|||
в |$В*2 1 |
Отмена |
^ |
|||
С |
ок |
|
|
|
Рис. 92. Окно Подбора параметра
В результате получится километраж в оба конца, равный 7,2727. 1начит, половина этого будет составлять 3,6364. Получается, что ваша работа должна находиться на расстоянии 3,6 км от вашего дома. При решении данной задачи мы не учли расходы на обслуживание, стоянку и ремонт автомобиля, а также расход бензина при нахождении в "пробке" и т.д.
Решение задачи линейного программирования. Задача линейного программирования формулируется следующим образом: найти такие шачения неизвестных xt, x2, хъ которые максимизируют линейную форму F при заданных линейных ограничениях.
Задача 2. Пусть требуется решить следующую задачу линейного программирования:
F = ЗОх, + 32х2 + 30х3 -> шах
12х, +10.*2+9х3 ^220 15х, + 18*2+ 20*з < 400 б*,+4х2+4*з ^100 хх,х2,хг >0 Решение
Подготовим вычислительную схему решения. Зарезервируем три ячейки для решения задачи (интервал В1:ВЗ) и для их интерпретаций (интервал А1:АЗ). В ячейку В4 введем формулу, определяющую линейную форму, которую требуется максимизировать: ■30*В1+32*В2+30*ВЗ; в ячейку В5 - левую часть первого ограничения: ■12*В1+10В2+9*ВЗ; в ячейку В6 - левую часть второго ограничения: ■15*В1+18*В2+20*ВЗ, в ячейку В7 - левую часть третьего ограничения: ■6*В1+4*В2+4*ВЗ. В ячейки А4:А7 введем соответствующие интерпретации для ограничений системы. Окончательно вычислительная схема решения представлена на рис. 93.
Завершив формирование схемы вычисления, запускаем средство Поиск решения. В появляющемся диалоговом окне (рис. 94) в поле
228 Установить целевую ячейку требуется ввести ссылку на ячейку В4, в рл Равной установить флажок максимальному значению, в поле Изменяя "ЧвН ввести ссылки на ячейки В1.ВЗ (ячейки, зарезервированные для знач неизвестных). ______________
1 Значение Xi
2 Значение хд
3 Значение ха 1 Линейная форма F
5 Первое уравнение
6 Второе уравнение
7 (Третье уравнение.
=30*81 +32*В2+30*ВЗ :12*В1+10В2+9*ВЗ =15*В1-И8*В?+7П*т
1=6*В1+4*В2+4*ВЗ| -f^^^^m
Рис. 93. Модель решения задачи Формирование системы линейных ограничений проводят анаш» ичш изложенному выше. Задав все параметры, требуется нажать и нот Выполнить для того, чтобы запустить его в работ
Рис. 94. Окно средства Поиск решения После завершения работы средства Поиск решения в ячс(1к зарезервированных для значений неизвестных, сформируется pciuci задачи линейного программирования (табл. 65).
Таблице
сред
Задача 3. Определить структуру привлеченных коммерческого банка, используя данные табл. 66. ________