PagePublication Analysis of data


Чтобы посмотреть этот PDF файл с форматированием и разметкой, скачайте его и откройте на своем компьютере.
Государственное образовательное учреждение

высшего професси
о
нального образования

«Кузбасский государственный технический университет»

Филиал ГУ КузГТУ в г. Новокузнецке

Муниципальное общеобразовательное учреждение «Лицей №11»




Куклина И.Д.


ИНСТРУМЕНТЫ

АНАЛИЗА ДАННЫХ

В
ЭЛЕКТРОННЫХ ТАБЛИЦАХ

Учебно
-
методическое пособие




Подбор параметра



Таблицы подстановки с о
д
ной и
двумя переменн
ы
ми



Поиск решения



Создание сценариев







Новокузнецк

2009

УДК

ББК








Куклина И.Д.

Инструменты анализа данных в
электр
онных таблицах
: учебно
-
методическое
пособие [текст] / И.Д.

Куклина.
-

Новокузнецк: РИО МОУ ДПО ИПК, 2009.
-

48

с.









В данном пособии рассмотрены основные
возможности та
б
личного процессора
Microsoft Excel
и OpenOffice.org Clc
как инструмента для анали
за данных и по
стро
е
ния
моделей, используемых при выработке управленч
е
ских решений.

Данное руководство рассчитано на пользователей, имеющих базовые знания и
навыки работы в
электронных таблицах
. Д
оступное и наглядное описание основных
идей и количественных

подходов к поиску и принятию решений в задачах управления,
большое количество разобранных задач, позволяет познакомить с возможностями ан
а
литического аппарата
электронных таблиц

как учащихся старших классов, так и ст
у
дентов
гуманитарных специальностей
выс
ших учебных зав
е
дений
.





Содержание


Введение

................................
................................
................................
...............

4

1. Подбор параметра

................................
................................
...........................

5

1.1. Инструмент анализа Подбор параметра

................................
.................

5

1.2. Примеры подбора параметра

................................
................................
...

6

1.3. Косвенное влияние ячеек

................................
................................
........

10

1.4. Нахождение корней уравнения

................................
..............................

11

1.5. Задачи для самостоятельной работы

................................
.....................

13

1.
6. Вопросы для самопроверки

................................
................................
....

14

2. Таблица подстановок

................................
................................
....................

15

2.1. Создание таблицы подстановки с одной переменной

.........................

15

2.2. Создание таблицы подстановки с двумя переменными

......................

17

2.3. Удаление таблицы подстановки данных

................................
..............

20

2.4. Задачи для самостоятельной работы

................................
.....................

21

2.5. Вопросы для самопроверки

................................
................................
....

21

3. Поиск решения

................................
................................
..............................

22

3.1. Надстройка Поиск решения в Microsoft Office

................................
....

22

3.1.1. Постановка задачи и оптимизация модели

................................
.....

22

3.1.2. Изменение способа поиска решения

................................
...............

25

3.1.3. Просмотр промежуточных результатов поиска решения

.............

25

3.1.4. Сохранение и загрузка модели оптимизации

................................
.

26

3.2. Поиск решения в OpenOffice.org Clc

................................
...................

26

3.3. Решение задач оптимизации с
о многими неизвестными

....................

28

3.7. Задания для самостоятельной работы

................................
...................

34

3.8. Вопросы для самопроверки

................................
................................
....

37

4. Создание сценариев для анализов «что
-
если»

................................
...........

38

4.1. Создание сценария

................................
................................
..................

38

4.2. Просмотр с
ценария
................................
................................
..................

39

4.3. Создание итогового отчета по сценариям

................................
............

39

4.4. Пример использования сценариев

................................
.........................

40

4.5. Вопросы для самопроверки

................................
................................
....

42

Литература

................................
................................
................................
.........

43

ОТВЕТЫ

................................
................................
................................
.............

46



4

Введ
ение

В данном пособии
на примере приложений
Microsoft
Excel

и
OpenO
f-
fice
.
org

Calc

рассмотрены основные
возможности табличного процессора
как и
н
струмента для анализа данных и построения
моделей, используемых
при выработке управленческих р
е
шений.

Табличные
процессоры (или электронные таблицы) предназначены
для автоматизации любых расчетов и позволяют обрабатывать большие
таблицы с выводом их на экран и печать. Практически все расчеты, в
ы
полняемые в процессе управления, могут моделироваться с помощью та
б
личны
х процессоров.

Microsoft Excel
и
OpenOffice
.
org

Calc

предлагает несколько инстр
у
ментов

анализа
, относящихся к категории «
ч
то
-
е
сли». К ним относятся
Подбор параметр
а
,
Та
б
лиц
а

подстановки с одной и
ли
двумя переменными
,
Поиск решения
,
Сцен
а
рии
.

В пособии даны

примеры эффективного использования Microsoft
Excel
и
OpenOffice
.
org

Calc

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

Д
анное руководство
рассчитано на
пол
ь
зовател
ей, имеющих
базовы
е

знани
я

и навык
и

работы в пр
иложении Microsoft
Excel

или
OpenOffice
.
org

Calc
.
Д
оступное и наглядное описание основных идей и количестве
н
ных
подходов к поиску и принятию решений в задачах управления, большое
количество разобра
н
ных задач,
позволяет познакомить с возможностями
аналитического аппарата
Microsoft
Excel

или
OpenOffice
.
org

Calc

как уч
а
щихся старших классов, так и студентов

гуманитарных специальностей
высших учебных завед
е
ний
.
Пособие содержит методические указания для
проведения
лаборато
р
ных работ по курсу «Информатика» для студентов
спец
и
альност
ей

061000 «Государственное и муниципальное управление»,
100103 «Социально
-
культурный сервис и туризм» и м
о
жет быть полезно
преподавателю при подготовке к занятиям
.




5

1. Подбор параметра

Т
ермины и определения

Анализ «что
-
если»



процесс изменения значений ячеек и анализа
влияния этих изменений на результат вычисления фо
р
мул на листе.

Подбор параметра



способ поиска определенного знач
е
ния ячейки
путем изменения значения в другой ячейке. Пр
и подборе параметра
пр
и
ложение

изменяет значение в одной конкретной в ячейке до тех пор, пока
формула, зависящая от этой ячейки, не вернет требуемый резул
ь
тат.

Команда
Подбор параметра

находит только одно решение, даже е
с
ли задача имеет несколько решений,

и только для одной ячейки.


Перед применением рассматриваемого инструмента следует решить
задачу средствами
электронных таблиц

с любыми и
с
ходными данными.

1.1. Инструмент
анализа
П
одбор параметра

1.

C
оставить таблицу, отвечающую требованиям:



я
чейка, в которо
й должен быть получен желаемый результат

(
ц
е
левая ячейка
)
,

должна содержать формулу, а значение в ней должно быть
наиболее близким к тому, которое требуется пол
у
чить
;



я
чейка, в которой должно быть выведено искомое значение,
должна прямо или косвенно влиять

на результат в
целевой
ячейке
;
она
не
должна содержать формулы, а только числовое значение, которое является
исходным для формулы, наход
я
щейся в целевой ячейке
.

2.

Применить инструмент
Подбор параметра
.

Microsoft

Office

2007
:

на
ленте

Данные

в группе
Работа

с данными

выбрать
пиктограмму
Анализ
«что
-
если»
, а затем выбрать в списке пункт
Подбор пар
а
метра

(
рис.

1.1)
.

Microsoft

Office

‰97
-
2003
: команд
а

Сервис

\

Подбор п
а
раметра
.

OpenOffice
.
org

Calc
: команд
а

Сервис

\

Подбор п
а
раметра
.


Рис.
1.1.
Работа с данным
и

(
Microsoft

Office

2007)

3.

В
появившемся диалоговом окне
Подбор параметра
заполнить
пустые поля.


6

Microsoft

Office
.

В

поле
Установить в ячейке

ввести ссылку на
ячейку, содержащую формулу

(
рис.

1.2).
В

поле
Значение

в
вести иск
о
мый
результат
.
В поле
Изменяя зн
ачение ячейки

ввести ссылку на ячейку, зн
а
чение которой н
ад
о подобрать.

Формула в ячейке, указанной в поле
Уст
а
новить в ячейке

должна сс
ы
латься на эту ячейку.


Рис.
1.2. Диалоговое окно инструмента
Подбор параметра

OpenOffice
.
org

Calc
.

В

поле
Яч.
с

формул
ой

ввести ссылку на яче
й
ку, содержащую формулу

(
рис.

1.
3
)
.

В

поле
Целевое з
начение

в
вести иск
о
мый результат
.
В поле
Изменя
ема
я ячейк
а

ввести ссылку на ячейку, знач
е
ние которой нужно подобрать.



Рис.
1.
3
. Диалоговое окно инструмента
Подбор параметра

1.2.
Примеры подбора параметра

Задача 1.
1.

Какие должны быть ежемесячные вклады в течение
12

месяцев

при процентной ставке
10,5%

годовых, чтобы по истечении
срока на счету накопилась сумма в
100

000

руб.?

Решение.

1.

Создать таблицу (вариант оформления листа
Micro
soft

Excel для
реш
е
ния задачи приведен на
рис.

1.
4
)
.


Рис.
1.
4
. Вариант оформления листа



в
ячейк
е

В1



подбираемый параметр
,
установить в ячейке
д
е
нежный формат (
Microsoft

Office

2007
:

на
ленте

Гла
в
ная

в группе

7

Число

выбрать в списке пункт
Денежный
;
Micro
soft

O
f
fice

‰97
-
2003
,

OpenOffice
.
org

Calc
:
команда
Формат

\

Ячейки

\

Число:
Денежный
)
;



в ячейках
В
2

3



исходные данные
, в ячейке
В3

установить пр
о
центный формат

(
Microsoft

Office

2007
: на
ленте

Главная

в гру
п
пе
Число

выбрать в списке пункт
Процентный
;
Mi
crosoft

Office

‰97
-
2003
,

OpenOffice
.
org

Calc
: команда
Формат

\

Ячейки

\

Чи
с
ло: Процен
т
ный
);



в ячейке
В4



формула расчета ежемеся
ч
ной выплаты:

=
БС
(B3/12;B2;
-
B1)
1



установить в ячейке денежный формат.

2.

Применить инструмент
Подбор параметра
.
Для этого:

Micro
soft

Office

2007
: на
ленте

Данные

в группе
Работа с данными

выбрать пиктограмму
Анализ «что
-
если»

, а затем выбрать в списке
пункт
Подбор параметра
.

Microsoft

Office

‰97
-
2003
,

OpenOffice
.
org

Calc
:

в
ыполнить команду
Сервис

\

Подбор п
а
раметра
.

3.

В появившемся

диалоговом окне задать параметры
.


Microsoft

Office

(
рис.

1.
5
)
:



в

поле
Установить в ячейке

ввести ссылку на ячейку, содерж
а
щую необходимую фо
р
мулу (
$
B
$4
);



в поле
Знач
е
ние

в
вести искомый результат (
100000
);



в

поле
Изменяя значение ячейки

ввести ссылку на я
чейку, знач
е
ние которой ну
ж
но подобрать (
$
B
$
1
).


Рис.
1.
5
. Диалоговое окно
Подбор параметра

OpenOffice
.
org

Calc
:



в поле
Яч. с формулой

ввести ссылку на фо
р
мулу (
$
B
$4
);



в поле
Целевое з
начение

ввести искомый результат (
100000
);



в поле
Изменя
ема
я ячейк
а

вве
сти ссылку на ячейку, значение к
о
торой ну
ж
но подобрать (
$
B
$1
).

4.

Результат вычислений на
рис.

1.
6
.





1

Здесь и далее аналог функции в программе
OpenOffice
.
org

Calc

см. в Приложении


8


Рис.
1.
6
. Результат вычислений

Ответ: ежемесячные вклады должны быть по
7939,86 р.

Задача
1.
2
.

Решить
линейное уравнение
:
ax
+
b
=0
.

Решение.

1.

Создать таблицу

(
вариант оформления листа на
рис.

1.
7
)
:


Рис.
1.
7
. Вариант построения листа



в ячейках
А3
:
В3



исходные данные;



в ячейке
В6



подбираемое значение переменной
х
;



в ячейке
А6



формула
:


А3*В6В3

2.

Применить инструмент
Подбор параметра
.

Microsoft

Office

(
рис.

1.
8
).


Рис.
1.
8
. Поиск решения линейного уравнения

OpenOffice
.
org

Calc
:

Яч. с формулой
:
$
А
$
6
;

Целевое з
начение
:0
;

И
з
меня
ема
я ячейк
а
:
$
B
$
6
.

Ответ:

х
-
1,5
.

Задача
1.
3
.

Тело брошено вертикально вверх со скоростью
v
=10

м/с
с высоты
h
=5

м
. Известно, что его п
оложение относ
и
тельно поверхности
земли
у (в метрах)

в зависимости от времени
t

(в секундах)

описывается
функцией
y
=
h
+
v
t
-
4,9
t
2
. Определить момент вр
е
мени, когда тело упадет на
землю.


9

Решение.

1.

Создать таблицу (вариант оформления листа на
рис.

1.
9
)
:



в ячейка
х
В1
:
В
2



исходные данные;



в ячейке
В
3



подбираемое значение
момента времени
;



в ячейке
В4



формула
, описывающая положение тела относ
и
тельно земли
:


=
В2В1

3
-
4,9*
В3
^2


Рис.
1.
9
. Вариант построения листа

2.

Применить инструмент
Подбор параметра
,

учитывая,
что к
огда
тело упадет на землю, значение
у0
,

следовательно в

ячейке
В4

число
0
.

Microsoft

Office

(
рис.

1.
10
).


Рис.
1.
10
.
О
кно
Подбор параметра

OpenOffice
.
org

Calc
:
Яч. с формулой
:
$
В
$
4
;

Целевое з
начение
:0
;

И
з
меня
ема
я ячейк
а
:
$
B
$
3
.

3.

Результат вычислений
на
рис.

1.
1
1
. Данный результат вызывает
большие сомнения (отрицательное значение времени). Поэтому надо в
ы
брать кнопку
Отмена
.


Рис.
1.
1
1
. Результат вычислений при начальном значении
t
=0

4.

В

ячейке
В3,

у
с
тановить значение таким образом, чтобы в ячейке
В4

по
явилось значение близкое к искомому


0
. Таким значением может
быть
, например,
число
2.


5.

П
овторно применить инструмент
Подбор параметра
. Результат
вычислений на
рис.


1.
1
2
.



10


Рис.
1.1
2
. Результат вычислений при начальном значении
t
=2

Ответ:
тело упадет на

землю примерно через
2,5

секунды
.

1.3. Косвенное влияние ячеек

В
приведенных
примерах формула непосредственно зависела от и
з
меняемого параметра.
Рассмотрим случай косвенного влияния

ячеек
.

Задача
1.
4.

(РАДИАНЫ, SIN, COS, ЕСЛИ, И). Траектория снаряда,
выле
тающего из орудия под углом
α

c начальной скоростью
v
0

м/с
, задае
т
ся ура
в
нениями:


, где
t



время в секундах.

При
v
0
=30

м/с

и
α45º

определить, на какой высоте будет снаряд

на
расстоянии
50

м

от орудия (
х50
)
?

Решение.

1.

Создать таблицу (вариант оформлен
ия листа на
рис.

1.
1
2
):



в ячейках
В1
:
В2

-

исходные данные;



в ячейке
В3



расстояние от орудия

через время
t
:


B2*B5*COS(РАДИАНЫ(B1))



в ячейке
В4



высота снаряда через время
t
:


B2*B5*SIN(РАДИАНЫ(B1))
-
4,9*B5^2

Примечание
. При написании формул необходимо
учесть, что по
у
с
ловию задачи угол дан в градусах, а аргументы функций
SIN
,
COS

в
M
i-
cr
o
soft

Excel задаются в радианах.
Для

перевода
значения

из одной един
и
цы изм
е
рения в другую
используют
функци
ю

РАДИАНЫ
.


Рис.
1.1
2
. Вариант построения листа

2.

Применить инс
трумент
Подбор параметра
.

Microsoft

Office
:
Установить в ячейке
:
$
В
$
4
;

З
начение
:0
;

Изменяя
значение
ячейк
и
:
$
B
$
3
.


11

OpenOffice
.
org

Calc
:
Яч. с формулой
:
$
В
$
4
;

Целевое з
начение
:0
;

И
з
меня
е
ма
я ячейк
а
:
$
B
$
3
.

3.

Результат вычислений на
рис.

1.
1
3
.


Рис.
1.1
3
. Резул
ьтат вычислений

Ответ: тело
будет примерно на высоте

23

м
.

1.
4
.
Нахождение корней уравнения

Нахождение корней полинома в среде
Microsoft

Excel

выполняе
т
ся в
два этапа:



приближенное определение корней графическим мет
о
дом;



получение точных значений с помощью

инструмента
Подбор п
а
раметра
.

Задача 1.5.
Найти корни полинома третьей степени
x
3
-
x
2
-
x
+
0,5
=0
.

Решение.

1.

Подготовить лист для построения графика функции

y
=

x
3
-
x
2
-
x
+0,5

на промежутке [
х
нач
, х
кон
]
для
10

точек
(вариант оформления
ли
с
та на
рис.

1.1
4
)
:



в ячейк
ах
В
2
:
В
3



промежуток построения графика
, н
а
пример
,


[
-
1,
2
]

(по
д
бирается опытным путем)
;




в ячейке
В4


количество точек для построения графика
, напр
и
мер
10

(также по
д
бирается опытным путем);



шаг вычисляется по формуле, зависящей от промежутка постро
е
ния и

количества точек:


(В
3
-
В
2
)/(В
4
-
1)



в ячейке
E
3



ссылка на начальное значение
х
:


В
2



в ячейку
E
4

вве
сти

формулу вычисления следующего значения
х

и ра
змножить (скопировать)
в
ячейки
диапазон
а

E
5
:
E
12
:


Е3$В$
5



в ячейку
F
3
вве
сти

формулу вычисления значени
я
y

и ра
змн
о
жить (скопировать)
в
ячейки
диапазон
а

F
4:
F
12
:


Е3^3
-
Е3^2
-
E
3+0,5


12


Рис.
1.1
4
. Вариант построения листа

2.

По данным диапазона
E
3:
F
1
2

построи
ть

точечную диагра
м
му

(
рис.

1.1
5
).


Рис.
1.1
5
. График функции
y
=

x
3
-
x
2
-
x
+0,5

на промежутке [
-
1,2
]

3.

Полином т
ретьей степени должен иметь три корня, следовател
ь
но, график должен трижды пересечь ось
Ох
.
Построе
н
ный график имеет
три точки перехода через ось
Ох
. Следовательно, мы нашли все три пр
и
ближенных значения корней ура
в
нения.

Если количество точек перехода
не
отвечает требованию, необход
и
мо изменить интервал нахождения корней,
а возможно и количество точек
для построения графика
.

4.

Добав
ить

на лист таблицу нахождения точных значений корней
уравнения

(
рис.

1.1
6
):



в ячейках
В
10
:
В
12



приближенные значения
х
, при ко
торых гр
а
фик пересекает ось
Ох
, т.е.
y
=0
; на графике (
рис.

1.1
5
) видно, что
это могут быть значения:
-
1; 0,5 и

1,5;



в ячейку
С1
0
вве
сти

формулу вычисления значения
y

и ра
з
мн
о
жить (скопировать)
в
ячейки
диапазон
а

С
11:
С
12
:


В10^3
-

В10^2
-

В100,5



13


Рис.
1.1
6
. Поиск корней

уравнения

5.

Для поиска более точного значения
х
1

применить инструмент
Подбор параме
т
ра
.

Microsoft

Office
:
Установить в ячейке
:
$
С
$
10
;

З
начение
:0
;

Изменяя
значение
ячейк
и
:
$
B
$
10
.

OpenOffice
.
org

Calc
:
Яч. с формулой
:
$
С
$
10
;

Целевое з
начение
:0
;

Изменя
ема
я ячейк
а
:
$
B
$
10
.

6.

Аналогично
определить значение
двух др
у
гих корней.

Ответ:
х
1

-
0,85485;

х
2
≈ 0,40304;

х
3
≈ 1,45170.

1.5. Задачи

для самостоятельной работы

Задача
1.6
.

(ПЛТ)
По сумме займа (
5
00

000

$
) и сроку за
й
ма (
10

лет
)
определить процентную

ставку, при которой ежемесячная плата сост
а
вит

5
0
00

$
.

Задача
1.7
.

(БС) На отпуск за год (учитывать только рабочие мес
я
цы, т.е.
11
) надо накопить определенную сумму денег (
60

000

р.
). Опред
е
лить

величину
ежемесячны
х

вклад
ов

на счет в банк под
9,8%

год
о
вы
х.

Задача
1.8
.

(
ПЛТ
) Какую максимальную ссуду на год можно взять,
если вы вынуждены ограничить ежемесячные выплаты
определенной
су
м
мой

(например,
5000

р.
)
, а процентная ставка

в размере
13
%

начисляется в
ко
н
це каждого месяца?

Задача
1.9
.

Тело брошено верт
икально вверх со скоростью
v

м/с

с

высоты
h

м
. Известно, что его положение относительно поверхности
земли
у (в метрах)

в зависимости от времени
t

(в секундах)

описывается
функц
и
ей
y
=
h
+
v
t
-
4,9
t
2
.

a)

Скорость

v
=10

м/с

и
высот
а

h=5

м
.
О
пределить момент врем
е
ни,
к
о
гда тело будет на высоте
8

м
.


14

b)

С какой скоростью нужно бросить тело с высоты
h=5

м
, чтобы
через
2

сек
унды

оно достигло высоты
h=
100

м
?

c)

С какой высоты нужно бросить тело со скоростью
v
=5

м/с
, чтобы
через
2 секунды

оно упало на повер
х
ность земли?

Задача
1.1
0
.

(РАДИАНЫ,
SIN, COS, ЕСЛИ, И
).
Траект
о
рия снаряда,
вылетающего из орудия под углом
α

c начальной скоростью
v
0

м/с
, задае
т
ся ура
в
нениями:


, где
t



время в секундах.

При
v
0
=30

м/с

и
α45º

определить:

a)

Через какое время после
выстре
ла снаряд упадет на зе
м
лю.

b)

Попадет ли снаряд в мишень, расположе
н
ную на расстоянии
85

м

от орудия. Высота мишени


2

м
, расстояние от ее нижней части до земли


5

м
. Другими словами, надо определить положение снар
я
да

(
у
) при
х85

и в отдельной ячейке сравн
ить полученное значение с высотой м
и
шени.

Задача 1.11.
Найти корни полиномов:

a)

2
x
3
-
0,1
x
2
-
0,7
x
+0,1=0

b)

-
x
3
+0,2
x
2
+0,6
x
=0

c)

3
x
2
+2,5
x
-
1=0

d)

-
5
x
2
+2
x
+5=0

Примечание.

При поиске корней, обратите внимание на степень п
о
линома.

1.6.
Вопросы для самопроверки

1.

Какие задачи мо
жно решить с помощью инструмента
Подбор п
а
раметра
?

2.

Как работает средство
По
д
бор параметра
?

3.

Каким образом должен быть подготовлен лист
электронных та
б
лиц
перед пр
и
менением средства
Подбор параметра
?

4.

Как

вызвать инструмент
Подбор пар
а
метра
?

5.

Какая ячейка назы
вается целевой?

6.

Может ли ячейка, влияющая на целевую
,
содержать фо
р
мулу?

7.

Сколько решений находит команда
Подбор параме
т
ра
?


15

2
. Таблица подстановок

Термины и определения

Таблица данных, таблица подстановок

-

диапазон ячеек, содерж
а
щий результаты подстановки

различных значений в о
д
ну или несколько
формул.
Таблицы данных предоставляют способ быстрого вычисления н
е
скольких версий в рамках одной операции, просмотра и сравнения резул
ь
татов всех различных вариантов на о
д
ном листе.

Ячейка ввода

-

ячейка, в которую
подставляются все значения из
таблицы данных. Хотя ячейка ввода не обязана входить в таблицу данных,
формулы в таблице данных должны ссылаться на ячейку ввода.

Массив

-

объект, используемый для получения нескольких значений
в результате вычисления одной фо
рмулы или для работы с набором арг
у
ментов, расположенных в различных ячейках и сгруппированных по стр
о
кам или столбцам.

Существует два типа таблиц данных: таблицы с одной п
е
ременной и
таблицы с дв
у
мя переменными.

2.1.
Создание таблицы подстановки с одной
переменной

1.

Сформировать таблицу: в

отдельный столбец

или

строку ввести
список значений, которые следует подставлять в
ячейку вв
о
да
.

2.

Выполнить одно из следующих действий.



е
сли значения в таблице
данных

ориентированы по столбцу

(
рис.

2.1)
, ввести формулу
в ячейку, расположенную на одну строку выше
и на одну ячейку правее первого значения
;

п
равее первой
формулы в той
же строке
ввести другие форм
у
лы
;



Рис.
2.1. Таблица подстановки, ориентированная по столбцу



е
сли значения в таблице
данных

ориентированы по
строке

(
рис.

2.
2
)
, ввести формулу в ячейку, расположенную на один столбец л
е
вее
и на одну строку ниже первого значения
,
ниже ввести др
у
гие формулы.


16



Рис.
2.2. Таблица подстановки, ориентированная по строкам

3.

Выделить диапазон ячеек, содержащий формулы и з
начения по
д
становки.

4.

Microsoft

Office

2007
:

н
а
ленте

Данные

в группе
Работа с да
н
ными

выбрать пиктограмму
Анализ «что
-
если»

, а затем в списке
в
ы
брать
пункт
Таблица
данных
.

Microsoft

Office

‰97
-
2003
:

к
оманд
а

Данные

\

Таблица по
д
становки
.

OpenOffice
.
org

Calc
:
команда
Данные

\

Совмещенные операции.

5.

В появившемся диалоговом окне
Таблица подстановок

заполните
пустые поля.



если значения в таблице расположены
в одном с
толбц
е

(
рис.

2.1)
:

Microsoft

Office
: ввести ссы
л
ку на ячейку ввода в поле
Подставлять
значени
я по строкам в
;

(рис. 2.3);

OpenOffice
.
org

Calc
:
ввести ссылку на ячейку
ввода

в поле

Столбец
ввода
;



если значения в таблице расположены
в одной
строк
е

(
рис.

2.
2
)
:

Microsoft

Office
: ввести ссылку на ячейку в поле
Подставлять знач
е
ния по
столбцам

в
;

(рис. 2
.3);

OpenOffice
.
org

Calc
:
ввести ссылку на ячейку в поле

Ст
рока
ввода
.


Рис.
2.3. Диалоговое окно
Таблица подстановки

Задача
2.
1.
(ПЛТ)
Для покупки недвижимости необходимо взять
ссуду. Банки предоставляют ссуду под определенный процент.
По
смо
т
реть, как р
азличные процентные ста
в
ки
(
12
%
,

13
%
,

14
%
)
влияют на размер
ежемесячных выплат по закладной

(
3

000

0
00

руб.
)

на данный
срок

(
10

лет
)
.

Решение
.

1.

Создать таблицу (вариант оформления листа на
рис.

2.
4
, обратите
особое внимание на формат ячеек):



В
4


ячейка вв
ода;


17



B
2

3



исходные данные;



В5



формула для расчета:


ПЛТ(B4/12;B2;
-
B3)



В4
:
D
4



список значений, которые подставляются в ячейку вв
о
да

В
4
;

на ячейки диапазона установить процентный формат;




В4:
D
5



таблица подстан
о
вок.


Рис.
2.
4
. Вариант оформления л
иста

2.

Выделить диапазон
В4:
D
5
.

3.

Microsoft

Office

2007
: на
ленте

Данные

в группе
Работа с да
н
ными

выбрать пиктограмму
Анализ «что
-
если»

, а затем выбрать в
сп
и
ске пункт
Таблица
данных
.

Microsoft

Office

‰97
-
2003
: команд
а

Данные

\

Таблица по
д
становки
.


OpenOf
fice
.
org

Calc
: команда
Данные

\

Совмещенные операции.

4.

В появившемся окне
заполнить поля.

Microsoft

Office
: в поле
Подставлять значения по
столбцам

в
;

ввести
значение
$В$4
.

OpenOffice
.
org

Calc
. В

поле

Ст
рока
ввода

ввести значение
$В$4
.

5.

Нажать кнопку
ОК
.

6.

Рез
ультат вычислений на
рис.

2.
5
.


Рис.
2.
5
. Результат вычислений

2.2.
Создание таблицы подстановки с двумя переменн
ы
ми

Таблицы подстановки с двумя переменными используют одну фо
р
мулу с двумя наборами значений. В ячейку листа ввести формулу, которая
ссылаетс
я на две ячейки ввода

(
рис.


2.
6
)
. В том же столбце ниже форм
у
лы
ввести значения подстановки для первой переменной.
В той же строке
справа от формулы в
ве
сти

значения по
д
становки для второй переменной.


18


Рис.
2.
6
. Вариант построения таблицы
данных

с двумя
п
е
ременными

1.

Выделить диапазон ячеек, содержащий формулу и оба набора
данных подстановки.

2.

Microsoft

Office

2007
: н
а
ленте

Данные

в группе
Работа с да
н
ными

выбрать пиктограмму
Анализ «что
-
если»
, а затем в
ы
брать в списке
пункт
Таблица
данных
.

Microsoft

Offi
ce

‰97
-
2003
:
к
оманд
а

Данные

\

Таблица по
д
становки
.

OpenOffice
.
org

Calc
: команда
Данные

\

Совмещенные операции.

3.

В появившемся окне
заполнить поля.

Microsoft

Office
.
В поле
Подставлять значения по столбцам в

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

ввести ссылку на ячейку ввода для
значений, расположе
н
ных в столбце.

OpenOffice
.
org

Calc
.
В поле
Строка ввода

в

ввести ссылку на яче
й
ку
ввода для значений, расположенных в строке.
В поле
Столбец в
вода

вв
е
сти ссылку на ячейку ввода для значений, расположенных в столбце.

4.

Наж
ать
кнопку
OK
.

Задача
2.2
.
Составить таблицу умножения чисел от
1

до
5
.

Нетрудно заметить, что т
аблица умножения представляет собой та
б
лицу данных с двумя переменными.

Решение.

1.

Настроить лист
(вариант оформления листа на
рис.

2.
7
).


Рис.
2.
7
. Вариант оформления листа


19



А1:В1



ячейки ввода с начальными значениями
;



В
3
:F
3
-


значения для подстановки в ячейку ввода
А1
;



А4:А8



значения для подстановки в ячейку ввода
В1
;



А3



формула д
ля расчета, зависящая от ячеек ввода
:


А1*В1

2.

Выделить диапазон
A
3:
F
8
.

3.

Вызвать инструмент
Таблица
данных

(
Совмещенные операции
).

4.

В
появившемся диалоговом окне
заполнит
ь

пустые поля.

Microsoft

Office

(
рис.

2.
8
)
.

В

поле
Подставлять значения по стол
б
цам в

вве
сти
ссылку
A1
; в

поле
Подставлять значения по строкам в

вв
е
сти
ссы
л
ку
В
1
.

OpenOffice
.
org

Calc
.
В поле
Строка ввода

в

ввести ссылку
A1
; в

п
о
ле
Столбец ввода

ввести ссылку
В1
.

5.

Нажать кнопку
OK
.


Рис.
2.
8
. Диалоговое окно
Таблица подстановки

Задача
2.3
.

(ПЛТ)

Проанализировать
влияние
различных процен
т
ных ставок

(
12%
,
13%
,
14%
)
и сроков займа
(
10
,
20
,
30

лет
)
на размер
ежемесячных выплат по ссуде

в
3

000

000 руб.

Решение.

1.

Настроить лист (вариант оформления листа на
рис.

2.
9
, обратить

внимание на формат яче
ек
)
.


Рис.
2.
9
. Вариант оформления листа



В
3

4



ячейки ввода с начальными значениями;



В
5



исходные данные;


20



В8:D8


значения для подстановки
по столбцам
в яче
й
ку

ввода

B
4
;



А9:А11


значения для подстановки

по строкам

в яче
й
ку

ввода

В
3
;



А8



формула для р
асчета, зависящая от ячеек ввода
:


ПЛТ(B3/12;B4;
-
B5)

2.

Выделить диапазон
:

А8:
D
11
.

3.

Вызвать инструмент
Таблица данных

(
Совмещенные операции
).

4.

В появившемся диалоговом окне
заполнить пустые п
о
ля.

Microsoft

Office
. В поле

Подставлять значения по столбцам в

вв
е
с
ти
ссылку
В
4
, а в

поле
Подставлять значения по стр
о
кам

в

ввести

В3
.

OpenOffice
.
org

Calc
.
В поле
Строка ввода

в

ввести ссылку
В4
; в

п
о
ле
Столбец ввода

ввести ссылку
В
3
.

5.

Нажать кнопку
OK
.
Результат вычислений на
рис.

2.1
0
.


Рис.
2.1
0
. Результат вычислений

2.
3
. Удаление таблицы подстановки данных

Удаление всей таблицы

1.

Выделить всю таблицу данных, включая все формулы, значения
подстановки, рассчитанные значения, примеч
а
ния.

2.

Microsoft

Office

2007
:

н
а
ленте

Главная

в группе
Редактиров
а
ние

выбрать команду
Очи
стить
, а затем выбрать в списке команду
Оч
и
стить все

(или нажать клавишу
).

Microsoft

Office

‰97
-
2003
:

к
оманд
а

Правка

\

Очистить

\

Все
.

Op
e
nOffice
.
org

Calc
: к
оманд
а

Правка

\

Удалить ячейки
.

Удаление рассчитанных значений из таблицы подст
а
новки

Поско
льку рассчитанные значения находятся в массиве, то можно
очистить все значения без удаления структуры таблицы подстановк
и да
н
ных
.
Для этого:

1.

Выделить в таблице данных все рассчитанные знач
е
ния. Формулы
и значения подстановки не выд
е
лять!

2.

Microsoft

Office

2
007
:

н
а
ленте

Главная

в группе
Редактиров
а
ние

выбрать команду
Очистить
, а затем выбрать в списке команду
Оч
и
стить с
одержимое

(или нажать клавишу
D
e
).

Microsoft

Office

‰97
-
2003
:

к
оманд
а

Правка

\

Очистить

\

Содерж
и
мое

(или нажать клавишу
D
e
).

Op
e
nOf
fice
.
org

Calc
: к
оманд
а

Правка

\

Удалить содержимое
.


21

2.
4
. Зада
чи

для самостоятельной работы

Создание т
аблицы подстановки с одной переменной

Зада
ча
2.4
.
(БС) Планируется в течение
1

года

ежемесячно в банк
класть некоторую сумму под проценты (например, по
100
0

руб.
). Сост
а
вить таблицу данных, которая показывает влияние различных
годовых
процентных ставок (
9%
;
9,5%
;
10%
;
10,5%
;

11%
) на размер нако
п
лений.

Задача

2.5
.
(БС) Планируется в течение
1

года

ежемесячно в банк
класть некоторую сумму под
11,2%

процентов
годовых. С
о
ставить таблицу
данных, которая показывает влияние размера ежемесячных вкладов (по
1000

руб
.
,
2500 руб
.,
5000 руб
.,
7500 руб
.) на размер накопл
е
ний.

Задача
2.6
.
(ПЛТ)
Проанализировать, каков будет размер ежемеся
ч
ных выплат по закладной в

900

00
0 руб.

при процентной ставке
12%
и
сроках
10
, 20

и
25

лет
.

Создание таблицы подстановки с двумя переменными

Задача
2.7
.

(
ПЛТ
)
Проанализировать
влияние
различных процен
т
ных ставок (
12
%
;
12
,5%

и
13
%

годовых) и сроков за
й
ма (
5,
10
,
20

и
30 лет
)
на размер ежем
есячных выплат по ссуде (н
а
пример,
800

000 руб.
)
.

Задача
2.8
.

При оформлении заказа на изготовление визитных ка
р
точек существует система
накопительных
скидок.
Проанализировать вли
я
ние разли
ч
ных процентных скидок (
5%
,

8%

и
10%
) и размера тиража (
24
,
120

и
2
40 штук
) на стоимость одной визитной карточки

при заданной
н
а
чальной цене (например,
8

руб.
)
.

2.
5
. Вопросы для самопроверки

1.

Какие задачи можно решить с помощью инструмента
Таблица
подстановок
?

2.

Каким образом должен быть подготовлен лист
электронных та
б
лиц
п
еред примен
е
нием средства
Таблица подстановок
?

3.

Как вызвать инструмент
Таблица подст
а
новок
?


22

3. Поиск решения

Для численного решения уравнений со многими неизвестными и
о
г
раничениями в
электронных таблицах

предусмотрен инструмент анализа
Поиск решения
.

Терм
ины и определения

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


Процедуру поиска решения можно использовать для определения
значен
ия влияющей ячейки, которое соответствует экстремуму (максим
у
му или минимуму).

3.1.
Н
адстройк
а

Поиск решения

в Microsoft Office

Microsoft

Office

2007
:

1.

Щелкнуть по кнопке
Microsoft Office
, выбрать
Пар
а
метры Excel
,
а затем выбрать катег
о
рию
Надстройки
.

2.

В по
ле
Управлен
ие выбрать
Надстройки Excel

и нажать
Перейти
.

3.

В поле
Доступные надстройки

установить флажок рядом с пун
к
том
Поиск реш
е
ния

и нажать
ОК
.

Microsoft

Office

‰97
-
2003
:

1.

Выбрать команду главного меню
Сервис

\

Надстройки
.

2.

Нажать кнопку
Обзор
, чтобы най
ти надстройку
,

которой нет в
о
к
не
Список на
д
строек
.

3.

Установить в окне
Список надстроек

флажок той надстройки, к
о
торую необход
и
мо загрузить

(
Поиск решения
)
.

4.

Следовать инструкциям программы установки, если они им
е
ются.

3.
1
.
1.

Постановка задачи и оптимизац
ия модели

1.

Microsoft

Office

2007
:

н
а
ленте

Данные

в группе
Анализ

щел
к
ните
Поиск решения
.

Microsoft

Office

‰97
-
2003
:

команд
а

Сервис

\

П
о
иск решения
.

2.

В

появившемся диалоговом окне
(рис. 3.1) в

поле
Установить ц
е
левую ячейку

ввести ссылку на ячейку или имя к
онечной ячейки, которая
должна содержать формулу.


23


Рис.
3.
1
. Диалоговое окно инструмента
Поиск решения

3.

В поле
Равной

установить переключатель в одно из полож
е
ний:



максимальному значению



чтобы максимизировать значение к
о
нечной ячейки путем изменения зн
ачений влияющих ячеек;



минимальному значению



чтобы минимизировать

значение к
о
нечной ячейки путем изменения значений влия
ю
щих ячеек
;



значению



чтобы установить значение в конечной ячейке ра
в
ным числу,
которое задается
в соответствующ
ем

п
о
ле.

4.

В поле
Из
меняя ячейки

ввести имена или ссылки на изменяемые
ячейки, разделяя их
точкой с
запят
ой
. Изменяемые яче
й
ки должны быть
прямо или косвенно связаны с
целевой

ячейкой. Допускается задание
до

200 изменяемых ячеек.

Примечание.

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

5.

В поле
Ограничения

ввести все ограничения, накладываемые на
поиск решения.


Рис.
3.
2
. Диалоговое окно
Добавление ограничения

Примечание.



Ограничение состоит из трех составных частей

(
рис
.

3.
2
)
: в поле
Ссылка на ячейку

ввести адрес или имя ячейки, на значение кот
о
рой накладываются ограничения; выбрать из раскрывающегося

24

списка условный оператор (
=
,
=
,
�=
,
цел

или
двоич

); в поле
О
г
раничение

ввести число, ссылку на ячейку или ее имя либо
ф
о
р
мулу, если было выбрано
цел
, то в поле
Ограничение

появи
т
ся «целое», а если выбрано
двоич



«дв
о
ичное».



Чтобы принять ограничение и приступить к вводу нового, надо
нажать кнопку
Добавить
.



Чтобы принять ограничение и вернуться в диалоговое окно
Поиск
решения
, надо н
а
жать
OK
.

6.

Перейти в диалоговое окно
Параметры

(
рис.

3.
3
)
.

Здесь можно
установить ф
лажк
и

Линейная модель

и
Неотрицательные знач
е
ния
.


7.

Выбрать кнопку
ОК
.


Рис.
3.
3
. Диалоговое окно
Параметры

поиска решения

8.

В диалоговом окне
П
оиск решени
я

(
рис.

3.
1
) н
ажать кнопку
В
ы
полнить
.

В
появившемся
диалоговом окне
Результаты поиска решения

(рис.

3.4)
выбрать
либо
Сохранить найденное решение,

чтобы установить
найденное решение на листе
, либо
Восстановить исходные значения
, ч
т
о
бы в
ернуть
исходные да
н
н
ые.


Рис.
3.
4
. Диалоговое окно
Резул
ь
таты поиска решения


25



В поле
Тип отчета
выбрать тип отчета (
Результаты
,
Устойч
и
вость
,
Пределы
), чтобы создать отчет, основа
н
ный на найденном
решении (если решение не найдено,
данные
параметры будут
н
е
доступны), а затем н
ажать кнопку
ОК
.



Сохранить сценарий
, чтобы сохранить значения изменяющейся
ячейки в качестве сценария, который можно будет отобразить
позже. В появившемся диалоговом окне в поле
Название сцен
а
рия

ввести имя для этого сцен
а
рия.


Чтобы просмотреть, изменить

или в
ыполнить сценарий, надо:

Microsoft

Office

2007
:

н
а
ленте

Данные

в группе
Работа с да
н
ными

выберите команду
Анализ «что
-
если»
, а затем выбр
ать

в
списке пункт
Диспе
т
чер сценариев
.


Microsoft

Office

‰97
-
2003
:

к
оманд
а

Се
р
вис

/

Сценарии
.

3.
1
.
2.

Изменение
способа поиска решения

1.

В диалоговом окне
Параметры поиска решения

(
рис.

3.3
)
задать
один или несколько параме
т
ров:



в поле
Максимальное врем
я

задается
время в секундах,
отведе
н
ное

на
поиск решения;



в поле
Предельное число итераций

задается максимальное кол
и
чество
итераций (
повторений
)
, отводимое на достижение коне
ч
ного результата;



в поле
Относительная погрешность

задается необходимая п
о
грешность
-

чем меньше
значение
, тем выше точность р
е
зультат
а
;



в поле
Допустимое отклонение

задается
значение допустимого
от
клонения в процентах
;



в поле
Сходимость

задается значение относительного изменения,
при достижении которого в последних пяти итерациях поиск р
е
шения прекращается
-

чем меньше значение, тем выше то
ч
ность.

2.

Наж
ать

ОК
.

3.

В диалоговом окне
Поиск решения

наж
ать

кн
опку
Выпо
л
нить

или
Закрыть
.

Примечание.

Чтобы восстановить параметры по умолчанию в ди
а
логовом окне
Поиск решения

выбрать кнопку
Восстан
о
вить
.

3.
1.3
. Просмотр промежуточных результатов поиска р
е
шения

1.

Настроить лист
электронных таблиц

для решения задачи.

2.

В
ызвать инструмент
Поиск решения
.

Microsoft

Office

2007
: на
ленте

Данные

в группе
Анализ

щелкните
Поиск реш
е
ния
.

Microsoft

Office

‰97
-
2003
:
к
оманд
а

Сервис

\

П
о
иск решения
.


26

3.

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

(
рис.

3.
3
)
установить флажок
П
о
казывать результаты итераций
, а затем нажать кнопку
ОК
.

4.

В диалоговом окне
Поиск решения

нажать кнопку
Выпо
л
нить
.

5.

В диалоговом окне
Текущее состояние поиска решения

нажать
одну из кнопок:

Стоп
, чтобы остановить пои
ск решения и вывести на
э
к
ран диалоговое окно
Результаты пои
с
ка решения
;

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

3.
1.4.

Сохранение и загрузка модели оптимизации

Значения элементов управления диалогового окна
Поиск решени
я

записываются на лист при
сохранении.
Чтобы использовать на листе н
е
сколько моделей оптимизации, нужно сохр
а
нить их с помощью
команды
Сохранить модель
.

Чтобы загрузить модель, необходимо
сначала
сохр
а
нить хотя бы одну м
о
дель.

1.

Настроить лист
Microsoft

Exce
l

для решения задачи.

2.

Вызвать инструмент
Поиск решения
.

Microsoft

Office

2007
: на
ленте

Данные

в группе
Анализ

щелкните
Поиск реш
е
ния
.

Microsoft

Office

‰97
-
2003
:
к
оманд
а

Сервис

\

П
о
иск решения
.

3.

В

диалоговом окне
Поиск решения

нажать кнопку
П
а
раметры
.

4.

В д
иалоговом окне
Параметры поиска решения

в

зав
и
симости от
ситуации выполнить одно из д
ействий
:

для сохранения нажать кнопку
С
о
хранить модель

и в
вести ссы
л
ку на верхнюю ячейку столбца, в котором
следует разместить модель оптимизации;

для загрузки ранее созда
нной
модели, нажать кнопку
Загрузить модель

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

3.
2
.
Поиск решения

в
OpenOffice.org Calc

1.

К
оманд
а

Сервис

\

П
о
иск решения
.

2.

В появившемся диалоговом окне
Решатель

(рис. 3.
5
)
в

поле
Ц
е
лев
ая

ячейк
а

ввести сс
ылку на ячейку, содержа
щую
форм
у
лу.

3.

В поле
Оптимизация результата

установить переключатель в
одно из положений:



М
аксиму
м



чтобы максимизировать значение конечной ячейки
путем изменения значений влияющих ячеек;



М
иниму
м



чтобы минимизировать значение ко
нечной ячейки
путем изменения значений влия
ю
щих ячеек;



З
начени
е



чтобы установить значение в конечной ячейке ра
в
ным
числу,
которое задается
в соответствующ
ем

п
о
ле.


27

9.

В поле
Путем и
змен
ени
я яче
ек

ввести имена или ссылки на изм
е
няемые ячейки. Изменяемые яче
йки должны быть прямо или косвенно св
я
заны с целевой ячейкой.

10.

В
разделе
Огранич
ительные условия

ввести все ограничения,
н
а
кладываемые на поиск решения.

Примечание.
Ограничение состоит из трех составных частей: в поле
Ссылка на ячейку

ввести адрес или имя

ячейки, на значение которой
н
а
кладываются огран
и
чения;
в поле
Операция

выбрать из раскрывающег
о
ся списка условный оператор (
=
,
=
,
�=
,
цел
ое

или
двоич
ное
); в поле
Зн
а
чение

ввести число, ссылку на ячейку или ее имя либо формулу.


Рис.
3.
5
. Диалоговое окно

инструмента
Поиск решения

11.

Перейти в диалоговое окно
Параметры
(
рис.

3.
6
).
Здесь можно
установить ф
лажк
и

Принять переменные как неотрицательные

и

Пр
и
нять переменные как целочисленные
.
Выбрать кнопку
ОК
.


Рис.

3.6.
Параметры поиска решения


28

12.

В диалоговом окн
е
П
оиск решения

(
рис.

3.
5
) нажать кнопку
Р
е
шить
.

В
появившемся
диалоговом окне выбрать
либо
Сохранить
резул
ь
тат
,

чтобы установить найденное решение на листе
, либо
Восстан
о
вить
предыдущий
, ч
тобы в
ернуть
исходные да
н
ные.

3.
3
. Решение задач оптимизации со мно
гими неизвестными

Если найденное решение задачи существенно отличается от ожида
е
мого результата или не найдено, можно попробовать запустить проц
е
дуру
поиска решения с другими начальными зн
а
чениями влияющих ячеек. Если
задать такие значения влияющих ячеек,
которые расположены близко к
экстремальной точке целевой функции, можно значительно сократить вр
е
мя поиска реш
е
ния.

Н
е каждая решаемая задача имеет однозначное реш
е
ние.

Чтобы прервать поиск, надо нажать клавишу
ESC
. Лист будет пер
е
считан с учетом последних

найденных значений для влияющих яч
е
ек.

Задача
3.
1.

Частная столярная

мастерская
производит две модели
сборных книжных полок


модель

А

и
модель

В
.

Их производство огран
и
чено наличием сырья и временем о
б
работки:



для каждого изделии
модели

А

требуется
3

кв.
м

досок, а изделия
модели

В



4

кв.м
;



мастерская
может получать от своих поставщиков до
1
0
0

кв.м

д
о
сок в неделю;



для каждого изделия
модели

А

требуется

1

ч
. машинного врем
е
ни, а для изделия
м
о
дели В



2,5

ч
.;



в неделю можно использовать
4
0 ч
.

машинного вр
ем
е
ни.

Сколько изделий каждой модели следует выпускать
мастерской
в
неделю, если каждое изделие модели
А

приносит
600

руб.

прибыли, а ка
ж
дое изделие модели
В


9
0
0 руб.

прибыли?

Решение.

Мат
е
матическая модель.

Обозначим:

x



количество деталей
модели А
,
выпускаемых в течение недели;

y



количество деталей
модели В
.

Тогда прибыль будет
600
х
900
у

(
руб.
)
.
Ее

надо максимализировать.
Функцию, для которой ищется экстремум (максимум или минимум) наз
ы
вают
цел
е
вой

функцией.

Беспредельному увеличению количества из
делий препятствуют
о
г
раничения:



о
граничено количество материала для полок:
3х4у<1700



о
граничено машинное время на изготовление полок
:
х
2
,5у<
4
0



к
роме того, количество изделий


неотрицательное ц
е
лое число,
поэтому
х

0
,
у

0
.


29

Формально задача оптимизации
записывается следующим образом:


Построение таблицы

1.

Создать таблицу (вариант оформления на
рис.

3.
7
):



в

ячейках
С3:Е4



исходные данные;



в ячейках
С10:
С
11



ограничения
;



в

ячейках
В7
,
В10
,
В11

записаны формулы аналогичные форм
у
лам системы: (1), (2), (3) с
оответственно, где вместо
х
,
у

и факт
и
ческих да
н
ных


ссылки на ячейки.


Рис.
3.
7
. Вариант построения листа
Microsoft

Excel

2.

Вызвать инструмент анализа
Поиск решения.

Microsoft

Office

2007
: на
ленте

Данные

в группе
Анализ

щелкните
Поиск решения
.

Microsoft

Office

‰97
-
2003
:
к
оманд
а

Сервис

\

П
о
иск решения
.

OpenOffice.org Calc
:
к
оманд
а

Сервис

\

П
о
иск решения
.

3.

В
появившемся
диалоговом окне

заполнить поля.

Microsoft

Office
:



Установить целевую яче
й
ку
:
$В$
7



Равной
:
максимальному знач
е
нию



Изменяя ячейки
:
$В$3:$В$4



Ограничения
:

$В$10<$С$10
,



$В$11<$С$11



$B$3:$B$4
=

ц
елое

OpenOffice.org Calc
:



Ц
елев
ая

яче
й
к
а
:
$В$
7



Оптимизация результата
:
М
аксиму
м


30



Путем и
зме
нени
я яче
е
к
:
$В$3:$В$4



Огранич
ительные условия
:

$В$10<$С$10
,



$В$11<$С$11



$B$3:$B$4  целое

4.

Добавить

параметры поиска решения.

Microsoft

Office
:

установить два флажка:
Линейная модель

(так как
ограничения и целевая функция являются линейными по переме
н
ным
х

и
у
)

и
Неотрицательные значения

(условие (4) в системе).


OpenOffice
.
org

Calc
:

установить флаж
о
к

П
ринять переменные как
неотрицательные.

5.

Вернуться в окно п
оиск решения

и запустить на выполнение.

6.

В
диалогово
м

окн
е

Результаты поиска решения

выберите
либо
Сохранить найденное решение
,
либо
Восстановить исходные знач
е
ния.

Ответ: изделие
А



25

шт.
,
В



6

ш
т.
; прибыль
20
400 руб
.

Задача 3.2.
Задача о назначениях
.
Имеется
n

рабочих и
n

видов р
а
бот (
n=4
). Стоимость
С
i,j

выпо
л
нения
i
-
м

рабочим,
j
-
й

работы приведена в
таблице (
рис.

3.
7
, ячейки
В4
:
Е7
). Составить план работ так, чтобы все р
а
боты были выполнены, ка
ж
дый рабочий был занят только на одной работе,
а суммарная стоимость выполнения всех работ была минимал
ь
ной.


Рис.
3.
8
. Задача о назначениях

Построение таблицы

1.

Создать таблицу (вариант оформления на
рис.

3.
8
):



в ячейках
B
4
:Е7



исходные данные;



в ячейке
В1
0



целевая функция;


31



в ячейках
B
14:Е17



выходные данные


1
, если работник выпо
л
няет данную работу и
0
, если нет;



в ячейках
F
1
4:
F
1
7
,
B
18:
E
18



контрольные суммы.

2.

В

ячейках диапазона
B
14:Е17

установит
ь

пользовательский фо
р
мат таким образом, чтобы вместо
1

выводился «х»
.

Д
ля этого
:

Microsoft

Office
:

выбр
ать

команд
у

Формат

\

Ячейки
;

в

появившемся
окне
Формат ячеек

выбрать
(все форматы)

и в поле
Тип

зап
и
сать:




[1]" ";[=1]"x";

OpenOffice
.
org

Calc
:

выбрать команду
Формат

\

Ячейки
; в п
ояви
в
шемся окне
Формат ячеек

выбрать
Особый

и в поле
Код формата

записать: [<1]" ";[1]"x";

3.

В

ячейк
у

В
10

с целевой функцию записать формулу
Ф1


расчет
общей
стоимости работ

при выбранном распределении н
а
грузки
:



СУММПРОИЗВ
(B
4
:Е7;B14:Е17)


4.

В ячейку
В18

записать формулу
Ф2
:



СУММ(В14:В17)


а затем размножить (скопировать) ее в ячейки
18
-
й

строки.

5.

В ячейку
F
14

записать формулу
Ф3
:



СУММ(В14:
Е14
)


а затем размножить (скопировать) ее

в ячейки сто
лбца
F
.


6.

Вызвать инструмент
Поиск решения.

7.

В диалоговом окне
заполнить поля
:

Microsoft

Office
:



Установить целевую яче
й
ку
:
$В$10



Равной
:
минимальному знач
е
нию



Изменяя ячейки
:
$В$14:$Е$17



Ограничения
:

$В$14:$Е$17двоичное
,



$
F
$14:$
F
$17
=
1
,



$
B
$18:$
E
$18=1

OpenOffice.org Calc
:



Ц
елев
ая

яче
й
к
а
:
$В$1
0



Оптимизация результата
:
М
и
ни
му
м



Путем и
зме
нени
я яче
е
к
:
$В$14:$Е$17



Огранич
ительные условия
:

$В$14:$Е$17двоичное
,



$
F
$14:$
F
$17
=
1
,



$
B
$18:$
E
$18=1

8.

Добавить параметры поиска решения
-

Линейная модель

и

Нео
т
рицательные значения
.

9.

Нажать кнопку
ОК
, а затем
Выполнить
.

Выбрать

либо
Сохр
а
нить найденное решение

либо
Восстан
о
вить исходные значения.

Ответ:
минимальная стоимость выполнения работ

15
.


32

Задача 3.3. Транспортная задача
.
Имеется
n

пунктов производства

и
m

пунктов распределения пр
о
дукции (
n=m=4
). Стоимость
С
i,j

перевозки
единицы продукции с
i
-
го

пун
к
та производства в
j
-
й

центр распределения
приведена в таблице (
рис.

3.
8
, ячейки
В4:Е7
). В столбце
F

в
i
-
й

строке ук
а
зан объем производства в
i
-
м

пункте прои
зводства, а в
10
-
й

строке в
j
-
м

столбце указан спрос в
j
-
м

центре распределения.

Составить план перев
о
зок по доставке требуемой продукции в пун
к
ты распределения, так чтобы
суммарные транспортные расходы были м
и
нимальными.


Рис.
3.
9
. Транспортная задача

П
остроение таблицы

1.

Создать таблицу (вариант оформления на
рис.

3.
9
):



в ячейках
B
4:
F
8



исходные данные;



в ячейках
B
15:Е18



выходные данные;



в ячейке
В11



целевая функция;



в ячейках
F
15:
F
18
,
B
19:
E
19



контрольные суммы.

2.

В ячейку
В11

с целевой функцию запи
сать формулу
Ф1


расчет
общей стоимости перевозок:



СУММПРОИЗВ(B4:Е7;B15:Е18)

3.

В ячейку
В19

записать формулу
Ф2
:



СУММ(В15:В18)


а затем размножить (скопировать) ее в ячейки
19
-
й

строки.


33

4.

В ячейку
F
15

записать ф
ормулу
Ф3
:



СУММ(В15:Е15)


а затем размножить (скопировать) ее в ячейки столбца
F
.


5.

Вызвать инструмент
Поиск решения

и

за
полнить поля формы.

Microsoft

Office
:



Установить целевую яче
й
ку
:
$В$11



Равной
:
минимальному знач
е
нию



Изменяя яче
йки
:
$В$15:$Е$18



Ограничения
:

$В$15:$Е$18целое
,



$
B
$19:$
E
$19=$
B
$8:$
E
$8,

$
F
$15:$
F
$18=$
F
$4:$
F
$
7

OpenOffice.org Calc
:



Ц
елев
ая

яче
й
к
а
:
$В$1
1



Оптимизация результата
:
М
и
ни
му
м



Путем и
зме
нени
я яче
е
к
:
$В$1
5
:$Е$1
8



Огранич
ительные условия
:

$В$15:$Е$18целое
,



$
B
$19:$
E
$19=$
B
$8:$
E
$8,

$
F
$15:$
F
$18=$
F
$4:$
F
$
7

10.

Добавить параметры поиска решения
-

Линейная модель
и

Нео
т
рицательные зн
а
чения
.

11.

Нажать кнопку
ОК
, а затем
Выполнить
.

Выбрать либо
Сохр
а
нить найденное решение

либо
Восстан
о
вить исходные значения.

Ответ: минималь
ная стоимость
перевозок
5
60
.

Задание 3.4.
Предприятие планирует изготовление
2000

единиц пр
о
дукции за смену. Для этого ему необходимо закупить оборудование, кот
о
рое предлагают различные фирмы. Известны стоимость, производител
ь
ность и надежность оборудовани
я каждой фирмы (
рис.

3.
10
). Сколько
единиц оборудования надо закупить у разных фирм, чтобы с минимальн
ы
ми затратами обеспечить выпуск продукции с надежн
о
стью

75%
?


Рис.
3.
10
. Оформление рабочего листа

Построение таблицы

1.

Создать таблицу (вариант оформления

на
рис.

3.
10
):



в ячейках
B
3:Е5



исходные данные;



в ячейках
B
6

6



выходные данные



количество единиц обор
у
дования
;


34



в ячейках
G
3:
G
4



ограничения;



в ячейке
F
5



целевая функция;



в ячейках
F
3
,

F
4



формулы для расчета общего количества дет
а
лей и надежно
сть (отношение потенциально качественной пр
о
дукции к общему количеству выпуще
н
ных деталей)
.

2.

В ячейках
B
4:
F
4

установить процентный формат.

3.

В ячейку
F
3

записать формулу
Ф1
:





СУММПРОИЗВ(B3:E3;B6:E6)

4.

В ячейку
F
4

записать формулу
Ф2
:






СУММПРОИЗВ(B6:E6;B4:E4;B3:E3)/СУММПРОИЗВ(B6:E6;B3:E3)

5.

В ячейку
F
5

записать формулу
Ф
3
:





СУММПРОИЗВ(B6:E6;B5:E5)

6.

Вызвать инструмент
Поиск решения

и

за
полнить поля формы.

Microsoft

Office
:



Установить целевую яче
й
ку
:
$
F
$
5



Равной
:
минимальному знач
е
нию



Изменяя ячейки
:
$В$
6
:$Е$
6



Ограничения
:

$В$6:$Е$6целое
,



$
F
$3=$
G
$3
,


$
F
$4�=$
G
$4

OpenOffice.org Calc
:



Ц
елев
ая

яче
й
к
а
:
$
F
$
5



Оптимизация результата
:
М
и
ни
му
м



Путем и
зме
нени
я яче
е
к
:
$В$
6
:$Е$
6



Огранич
ительные условия
:

$В$6:$Е$6целое
,



$
F
$3=$
G
$3
,


$
F
$4�=$
G
$4

12.

Добавить параметр

Неотрицательные знач
е
ния
.

13.

Нажать кнопку
ОК
, а затем
Выполнить
.

Выбрать либо
Сохр
а
нить найденное решение

либо
Восстан
о
вить исходные значения.

Ответ: минимальн
ые затраты (
6710

у.е.
) достига
ю
тся, если у «Арх
и
меда» и «
Ньютона» купить по
одному

станку, у «Планеты»



33
, а у «О
р
биты»



49
.

3.
7
. Задания для самостоятельной работы

Задание
3.
5
.

Имеются деньги достоинством в
1
,
5
,
10
,
50
,
100
,
500

и
1000

руб
. Определить наименьшее количество купюр разного достоинства
для выда
чи заданной суммы денег (напр
и
мер,
9999
).

Задание
3
.
6
.

Фирма производит три вида продукции (
А
,
В

и
С
) для
выпуска каждого требуется определенное время обработки на всех чет
ы
рех устройствах
I
,
II
,
III

и
IV

(см.

рис.

3.
1
1
).
Макс
и
мальное время работы
на устр
ойствах в неделю
84
,
42
,
21

и
42

часа

соответственно.

Каждое изд
е

35

лие вида
А

приносит прибыль
90

руб.
, вида
В



180

руб
., вида
С



120

руб
.

Определить, какую продукцию и в каких количествах
надо
произв
о
дить
для максимализации прибыли (рынок сбыта для каждог
о продукта неогр
а
ничен).


Рис.
3.1
1
. Исходные данные и оформление рабочего листа

Задание 3.
7
.
Фирма производит деревянные изделия двух видов
-

модель

А
и
модель

В
.

Их производство ограничено наличием сырья и временем обработки:



для каждого изделия
модели

А

требуется
3

кв.м

досок, а изделия
модели В



4

кв.м
;



фирма может получать от своих поставщиков до
1700

кв.м.

досок
в неделю;



для каждого изделия
модели А

требуется
12

мин.

(
0,2

ч.
) маши
н
ного времени, а для изделия
модели В



30

мин.

(
0,5

ч.
);



в неделю
можно использовать
160

ч.

машинного врем
е
ни.

Сколько изделий каждой модели следует выпускать фирме в неделю,
если каждое изделие
модели А

приносит
120 руб.

прибыли, а каждое изд
е
лие
модели В



240 руб
. прибыли?

Задание
3.8
.
Предприятие электронной промышл
енности выпускает
две модели радиоприемников, причем каждая модель производится на о
т
дельной технологической линии.

Суточный объем производства первой
линии


60

изделий, второй линии


75

изделий. На радиоприемник первой
модели расходуются
10

однотипных э
лементов электронных схем, а на р
а
диоприемник второй модели


8

таких же элементов. Максимальный с
у
точный запас используемых элементов равен
800

единицам. Прибыль от
реализации одного радиопр
и
емника первой и второй моделей равна
30$

и
20$

соответственно.

О
пределить оптимальный суточный объем произво
д
ства первой и второй мод
е
ли.


36

Задание
3.
9
.
В швейной мастерской изготовляют
куртки

трех
фас
о
нов
из четырех разных тканей. Расход ткани указан на
рис.

3.1
2
.
Сто
и
мость
пошива
куртки
первого типа

равна
100

у.е.
,
вто
рого



1
2
0

у.е.
,
третьего



110

у.е.

Дневной запас тканей в
мастерской
:
первой
ткани



50

м
,
второй



80

м
,
третьей



25

м
,
четвертой



60

м
. Сколько к
урток
к
а
ждого типа надо
шить
в день, чтобы получить максимальную
стоимость прои
з
водства
?


Рис.
3.1
2
. Вар
иант оформления рабочего листа

Задание
3.1
0
. Задача об оптимизации тургрупп
.
Туристическая
фирма «Экватор» ежедневно отправляет в три отеля Анталии, Кемера и
Мармариса (Турция) соответственно
30
,
20

и
16

чел
о
век. Экскурсионная
программа каждой группы состо
ит из рафтинга (спуск по горной реке на
плоту), яхт
-
тура вдоль побережья и путешествия джип
-
сафари в турецкую
глубинку. Стоимость экскурсий с трансфером на человека для отелей ра
з
ных городов приведена на
рис.

3.1
3
. При этом сущес
т
вуют ограничения на
количе
ство человек в экскурсии: рафтинг


25

чел
овек
, яхт
-
тур


20 чел
о
век
, джип
-
сафари


30 чел
овек
. От каждого отеля на каждую экскурсию
должно быть послано не менее

5

чел
овек
.

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


Рис.
3.1
3
. Исходные данные

Задание 3.
11
.
Фирма для улучшения спроса на свою продукцию р
е
шила организовать рекламную кампанию на радио и телевидении. В бю
д
жете фирмы заложены затраты на

рекламу не более
1000

у.е
. Каждая мин
у
та рекламы на радио обходится в
5

у.е.
, а на телевидении


в
100

у.е
. Опыт
прошлых лет показал, что объем сбыта продукции, которой обесп
е
чивает
минута телерекламы, в
25 раз

больше сбыта, обеспечиваемого одной м
и
нутой
радиорекламы. Однако фирма хотела бы использовать радио, по
крайне мере, в два раза чаще, чем телевидении.

Определить оптимал
ь
ное
распределение средств между радио
-

и т
е
лерекламой.


37

Задание
3.12
.
На складе имеются 6 разновидностей игр
у
шек, каждая
из которых

характеризуется ценой и количеством в наличии (
рис.

3.1
4
).
Определить, какие игрушки и в каком количестве надо взять, чтобы кол
и
чество подарков было максимал
ь
ным.

Примечание.

В ограничения добавить, что количество п
о
дарков, во
-
первых, всегда число целое,
а во
-
вторых, не может превышать значение,
указанное в таблице исходных данных (н
а
пример, клоунов не может быть
больше числа, ук
а
занного в
В2
).


Рис.
3.1
4
.
Ва
риант построения табл
и
цы

Задание 3.13.
Фирма имеет два товарных склада и трех оптовых п
о
купателей.

Известны данные о загруженности каждого из складов, спрос
каждого покупателя и стоимость перевозки (
рис.

3.1
5
), а также, что о
б
щий
объем продукции на складах составляет
300 тысяч единиц

и совпад
а
ет с
общим объемом заказов покупателей. Определит
ь

объем пос
тавок со скл
а
дов так, чтобы транспортные расходы были минимальными.


Рис.
3.1
5
. Вариант построения табл
и
цы

3.8. Вопросы для самопроверки

1.

Как работает средство
Поиск решения
?

2.

Что такое целевая функция?


3.

Какие значения может прин
и
мать целевая функция?

4.

Каки
е ограничения накладываются на данные и зачем?

5.

Какие параметры влияют на
результативность
Поиска решения
?


38

4. Создание сценариев для анализов «что
-
если»

При моделировании задач, имеющих более двух переме
н
ных можно
использовать инструмент
Сценарии
, который т
акже является частью блока
задач «что
-
если».

Термины и определения

Сценарий

-

набор значений, которые сохраняются
программой
и м
о
гут автоматически по
д
ставляться на лист.

Сценарии можно использовать для прогноза результатов моделей
расчетов листа. Существ
ует возможность создать и сохранить в листе ра
з
личные группы значений, а затем переключаться на любой из этих сцен
а
риев, чтобы просматривать разли
ч
ные результаты.

Изменяемые ячейки
в модели


это ячейки, содержащие значения,
которые требуется использовать
в качестве переме
н
ных.

4.1. Создание сценария

1.

Для создания и применения
Сценариев

следует на раб
о
чем листе
смоделировать решение задачи при некотором наборе входных данных.

2.

Microsoft

Office

2007
: на
ленте

Данные

в группе
Работа

с да
н
ными

выбрать команду
Ан
ализ «что
-
если»
, а затем выбрать в сп
и
ске пункт
Диспетчер сценариев
.

Microsoft

Office

‰97
-
2003
:
к
оманд
а

Сервис

\

Сценарии
.

OpenOffice
.
org

Calc
:
к
оманд
а

Сервис

\

Сценарии
.


Рис.
4.1. Диалоговое окно
Диспетчера сценариев

3.

В диалоговом окне
Диспетчер сценарие
в

(
рис.

4.1)

н
ажать кно
п
ку
Доб
а
вить

(
рис.

4
.2).


39


Рис.
4
.2. Диалоговое окно
Добавление сценария

4.

В поле
Название сценария

задать имя сценария.

5.

В поле ячейки
Изменяемые ячейки

ввести ссылки на ячейки, к
о
торые требуется изменить

(
п
ри указани
и

несмежных диапа
зонов, необх
о
димо удерж
и
вать клавишу
Ctrl
).

6.

В группе
Защита

установить необходимые флажки. Нажать
ОК
.

7.

В появившемся диалоговом окне
Значения ячеек сценария
вв
е
сти
необходимые значения

и
нажать кно
п
ку
ОК
.

8.

Если требуется создать дополнительные сценарии, над
о повт
о
рить шаги 2
-
7
. После завершения создания сценариев в диалоговом окне
Диспетчер сцен
а
риев
нажать кнопку
Закрыть

4
.2. Просмотр сценария

1.

Microsoft

Office

2007
: на
ленте

Данные

в группе
Работа с да
н
ными

выбрать команду
Анализ «что
-
если»
, а затем выбрат
ь в сп
и
ске пункт
Диспетчер сценариев
.

Microsoft

Office

‰97
-
2003
: команд
а

Сервис

\

Сценарии
.

OpenOffice
.
org

Calc
:
к
оманд
а

Вид

\

Навигатор

\

Сценарии
.

2.

В
ыбрать имя сценария

и н
ажать кнопку
Вывести
.

4
.3. Создание итогового отчета по сценариям

Чтобы сравнить н
есколько сценариев, можно создать
обобщающий
отчет

или
сводн
ую

табл
и
ц
у
.

1.

Microsoft

Office

2007
: на
ленте

Данные

в группе
Работа с да
н
ными

выбрать команду
Анализ «что
-
если»
, а затем выбрать в сп
и
ске пункт
Диспетчер сценариев
.


40

Microsoft

Office

‰97
-
2003
: коман
д
а

Сервис

\

Сценарии
.

2.

В диалоговом окне
Диспетчер сценариев
выбрать кнопку
О
т
чет
.

3.

В диалоговом окне
Отчет по сценарию

(рис.

4.
3
)

в
ыбрать
Тип
отчета

-

Стру
к
тура

(
поле
Ячейки

результата

можно не заполнять)
или
Сводная таблица

(
в

поле
Ячейки

результата

ввест
и
через
точк
у

с запятой

ссылки на ячейки, значения которых изменяются с помощью сценариев
)
.


Рис.
4
.
3
. Диалоговое окно
Отчет по сценарию

4
.4. Пример использования сценариев

Задача.

Имеется
6

разных фруктов, каждый из которых характериз
у
ется весом и ценой

(данные приведены в таблице на
рис.

4.4
). Выбрать т
а
кие фрукты, чтобы их общий вес колебался в пределах
11
-
12

кг, а сумма
р
ная цена была наимен
ь
шей.


Рис.
4.
4
. Вариант оформления рабочего листа

Решение
.

Создание сценариев

1.

Создать таблицу (вариант оформлен
ия на
рис.

4.
4
):



в ячейках
B
2
:
С
7



исходные данные;



в ячейках
D
2
:
D
7



выходные данные


1

(да)
, если да
н
ный
товар
берем весь целиком
и
0

(нет)
, е
с
ли не

берем
;


41



в ячейке
В10



целевая функция;



в ячейк
е

B
1
3



общий вес набранных фруктов.

2.

Записать
формул
ы

в я
чейки
В10

и
В13
, для этой цели
лучше вс
е
го
подойдет функция
СУММПРОИЗВ
.

3.

В

ячейк
ах

D
2:
D
7

установить
пользовательский формат, чтобы
вместо

двоичных чисел

0

и
1

выводились слова «
нет
» и «
да
»

соотве
т
с
т
венно
. Для этого
:



выполнить
команд
у

Формат

\

Ячейки
;



в поя
вившемся окне
Формат ячеек

(
рис.

4.6
) выбрать
(все фо
р
маты)

и в поле
Тип

записать: [0]"нет";[1]"да";


Рис.
4.6. Пользовательский формат

4.

Вызвать

средство
Поиск решения
, где
выбрать

минимализ
и
ровать
$В$10

по переменным
Наличие

(
$
D
$2:$
D
$7
) при ограниче
ниях
$В$13>$С$13

и
$В$13<$
D
$13
,
Наличие



двои
ч
ное (0 или 1)
.

5.

Нажать кнопку
Выполнить
.

6.

После выполнения расчетов появится диалоговое окно
Резул
ь
таты поиска решения
. Выбрать
Сохранить сценарий
, указать имя сцен
а
рия
Минимум
. Вновь появится окно
Результат
ы поиска решения

устан
о
вить переключатель
Восстановить исходные значения

и з
а
крыть окно.

7.

Решить предыдущую задачу так, чтобы общий вес отобранных
фруктов колебался в пределах 11
-
12 кг, а суммарная цена была наибол
ь
шей. Сохранить сценарий под именем
Макс
и
му
м.

Вычисление по сценарию

8.

Microsoft

Office

2007
: на
ленте

Данные

в группе
Работа с да
н
ными

в
ы
брать команду
Анализ «что
-
если»
, а затем выбрать в списке пункт
Диспетчер сценариев
.

Microsoft

Office

‰97
-
2003
: команд
а

Се
р
вис

\

Сценарии
.

9.

Появится диалоговое окно

Диспетчер сценариев
, в котором пер
е
числены сцен
а
рии текущего рабочего листа.

10.

Выбрать сценарий
Минимум
и нажать

кнопку

Вывести,
анал
о
гично

выбрать сценарий
Максимум.


42

Отчет по сценариям

11.

Microsoft

Office

2007
: на
ленте

Данные

в группе
Работа с да
н
ными

выбра
ть команду
Анализ «что
-
если»
, а затем выбрать в сп
и
ске пункт
Диспетчер сценариев
.

Microsoft

Office

‰97
-
2003
: выполнить команду
Сервис

\

Сценарии
.

12.

В диалоговом окне
Диспетчер сценариев
выбрать кно
п
ку
Отчет
.

13.

В

диалоговом окне
Отчет по сценарию
выбрать тип о
тчета
Структура
, в

поле
Ячейки результата

задать
адреса
$В$10; $В$13
;

14.

Н
ажать кнопку
ОК
.

15.

Аналогично создать отчет в виде сводной таблицы (
рис.

4.8).


Рис.
4.7. Структура сценария


Рис.
4.8. Сводная таблица

4.
5
. Вопросы для самопроверки

1.

Для чего надо созда
вать сценарии решения?

2.

Какие отчеты создаются в результате работы сценариев?


43

Литература


1.

Кошелев В. Excel 2007. Эффективное использование.
-

М.: Б
и
ном.
Лаборатория знаний, 2008
-

544 с.

2.

Лапчик М.П., Семакин И.Г., Хеннер Е.К.Методика преподавания
информати
ки.


4
-
е изд.


М.: Академия, 2007.


624 стр.

3.

Орлов А.И. Теория принятия решений: Учеб. пособие.
-

М.: Изд
а
тельство "Март", 2004.
-

656 с.

4.

Отставнов М. Прикладные свободные программы в школе.


М.: И
з
дательство "Медиа Технолоджи сервис", 2003.


96 с.: и
л.

5.

Слетова Л. Excel 2007
-

М.: "ЭКСМО", 2007
-

336 стр.

6.

Сурядный А., Глушаков С. Microsoft Excel 2007: Самоуч
и
тель.


2
-
е
изд.


М.: АСТ, 2008
-

416 стр.

7.

Шихин Е.В., Чхартишвили А.Г. Математические методы и модели в
управлении: Учеб. пособие.


3
-
е изд.


М.: Дело, 2004.


440 с.


(Сер. «Классический университетский учебнк»).



44

ПРИЛОЖЕНИЕ

Некоторые с
тандартные функции

Математические функции

СУММ

(аргумент1; аргумент2; …)


суммирует указа
н
ные числа; в
качестве аргументов можно указывать данные различных т
ипов, но в по
д
счете участвуют только числа. Как правило, аргументами являются диап
а
зоны яч
е
ек.

OpenOffice.org Calc:
функция
SUM

СУММПРОИЗВ

(массив1;массив2;массив3; ...)


перемножает соо
т
ветствующие элементы заданных массивов (диапазонов) и возвращает
сум
му произведений.

Массив1
,
массив2
,
массив3
, ...



от 2 до 30 массивов (диапазонов),
чьи компоненты нужно перемножить, а з
а
тем сложить; они должны иметь
одинаковые размерности..

OpenOffice.org Calc:

функция
SUMPRODUCT

РАДИАНЫ

(угол)



преобразует
величину
угла из градусах в ради
а
ны.

OpenOffice.org Calc:

функция

RADIANS

Финансовые функции

Аргументы:

Ставка

-

процентная ставка по ссуде.

Кпер



количество выплат по ссуде.

Пс



значение ссуды или общая (полная, приведенная) стоимость.

Бс


-

требуемое значение б
удущей стоимости, или остатка средств
после последней выплаты. Если аргумент
Бс

опущен, то он полагается
равным нулю, т.е. для займа, например,
Бс
=0.

Плт

-

это выплата, производимая в каждый период; это зн
а
чение не
может меняться в течение всего п
е
риода вы
плат. Если аргумент опущен,
должно быть указано значение арг
у
мента
Пс
.

Тип



число 0или 1 обозначающее, когда должна производиться в
ы
плата (0 или опущен
-

в конце периода; 1
-

в нач
а
ле)

ПЛТ

(ставка;кпер;пс;бс;тип)
-

возвращает размер периодического
платежа
, необходимого для погашения ссуды за определенный период
времени.


OpenOffice
.
org

Calc
:

функция
PPMT
(Ставка; Период; Кпер; пс;бс;
тип)


45

Пример

1
.

Определить размер ежемесячных платежей при ссуде в
$10000 на срок в 3 года под 9% год
о
вых.

Ответ:
ПЛТ(9%/12;3
*12;10000)


=PPMT (9%/12; 1;3*12;10000)

БС

(ставка;кпер;плт;пс;тип)
-

возвращает будущую стоимость инв
е
стиции на основе периодических постоянных (равных по величине сумм)
платежей и постоянной процен
т
ной ставки.

OpenOffice
.
org

Calc
:

функция
FV

Пример

2
.

С
колько будет на счету, если в течение 30 лет вклад
ы
вать по 2000 в месяц при 10% год
о
вых.

Ответ:
БС(10%/12;30*12;
-
2000;;1)



=

FV
10%/12;30*12;
-
2000;;1)


ПС

(ставка;кпер;плт;бс;тип)
-

возвращает приведенную (к текущему
моменту) стоимость инвестиции. Приве
денная (нынешняя) стоимость
представляет собой общую сумму, которая на настоящий момент равн
о
ценна ряду будущих в
ы
плат.

OpenOffice.org Calc:

функция

Р
V

Пример

4
.

Условия страховки: 500 руб. платится в конце каждого
месяца в течении 20 лет при 8% годовых..
Определить, общую стоимость
выплат.

Ответ:
ПС(8%/12;20*12;500;;0)



=

Р
V
(8%/12;20*12;500;;0)

Логические функции

ЕСЛИ
(лог_выражение;знач_если_истина;знач_если_ложь)



во
з
вращает одно из двух значений:

Знач_если_истина
,

если
лог_выражение
имеет зн
а
чение
ИС
ТИНА
.

Знач_если_ложь
,

если

лог_выражение

имеет зн
а
чение
ЛОЖЬ
.

OpenOffice
.
org

Calc
:

функция

IF

Пример 4.

Определить, является ли значение в ячейке
В1

полож
и
тельным.

Ответ:
=
ЕСЛИ
(
В
1
�0
; "
да
";"нет")


46

ОТВЕТЫ


Подбор параметра

Задача 1.6
.
Процентная ставка

3,7
4%

Задача 1.7
.
Е
жемесячный вклад

5235,44

Задача 1.8
.
С
суда

55

980,21

р.

Задача 1.9
.
b
)

Скорость
57,3 м/с;
c
)

Высота
9,6 м

Задача 1.10
.
a
)

Время

4,33 сек.;
b
)


6,33 сек.; да, попадет

Задача 1.11

a
)

х
1


-
0,631; х
2


0,149; х
3


0,531

b
)

х
1


-
0,681; х
2


0,001; х
3

0,881

с)

х
1


-
1,129; х
2


0,295

d
)

х
1


-
0,820; х
2


1,220

Таблицы подстановок

Задача
2
.
4


Задача 2.5


Задача 2.6


Задача 2.7



47

Задача 2.8


Поиск решения

Задача 3.
5
.
1 руб.


4 шт., 5 руб.


1 шт., 10 руб.


4 шт., 50 руб.


1

шт.,
100 руб.


4 шт., 500 руб.


1 шт., 1000 руб.


9 шт.

Задача 3.
6
.
А


12 шт.; В


3 шт.; С



0 шт
.
; пр
и
быль 1620 руб.

Задача 3.
7.

И
зделие А


300 шт., В


200 шт.; прибыль 84000

руб.

Задача 3.8
.
И
зделие А (1 линия)


60 шт., изделие В (2

линия)


25 шт.,
прибыль 2300$.

Задача 3.
9
.
К
уртка 1


13

шт., куртка 2


12 шт., куртка 3


12 шт.; общая
сто
и
мость производства


4060

у.е.

Задача 3.1
0
.


Задача
3
.
11
.
Р
адио


75 мин., телевидение


37 мин.

Задача 3.12
.
О
дно из возможных решений: 3 кло
уна, 3 конструкт
о
ра, всѐ
остальное по 1 шт.; всего 10 подарков на су
м
му 999 руб.

Задача 3.13
.
П
ервому покупателю 20

тыс.

ед. со склада 1 и 120

тыс.

ед.
со

склада

2; второму покупателю 90

тыс.

ед. со склада

1; третьему покуп
а
телю 70

тыс.

ед. со склада

1.


Приложенные файлы

  • pdf 24135234
    Размер файла: 913 kB Загрузок: 0

Добавить комментарий