Этап. Решение задачи средствами табличного процессора Microsoft Excel

1. Запустим табличный процессор Microsoft Excel. Для этого выполним последовательность команд Пуск Þ Программы Þ Microsoft Excel.

2. Введем в ячейки столбца A данные, как указано на рис. 1

рис. 1

3. В столбец B введем формулы для расчета целевой функции и ограничений как указано на рис. 2.

 

рис.2

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

· В поле Установить целевую ячейку – укажем адрес ячейки, значение которой должно быть изменено (максимизировано, минимизировано или приравнено к какому-либо определенному указанному значению). В нашем случае это адрес ячейки Прибыль (т.е. B6).

· В поле Равной: - установим переключатель на максимальное значение (т.к. по условию задачи мы максимизируем прибыль).

· В поле Изменяя ячейки – установим ссылку на ячейки, которые будут изменены. В нашем случае это диапазон ячеек $B$2:$B$3.

· В поле Ограничения – щелкнем на кнопке Добавить. В появившемся диалоговом окне:

ü В поле Ссылка на ячейку – укажем адрес ячейки, для которой должно действовать ограничения. В нашем случае это адрес ячейки Материал (т.е. В9).

ü В поле операторов выберем знак соотношения. В данном случае знак <=.

ü В поле Ограничение - укажем границу ограничения. В случае материала это число 1700.

ü Щелкнем OK

· Аналогичным образом, добавим ограничения на время изготовления и ограничения на x и y.

· Таким образом, диалоговое окно Поиск решения должно быть настроено следующим образом:

· Щелкнем по кнопке Параметры и установим флажок Линейная модель. Щелкнем по OK.

· Щелкнем по кнопке Выполнить.

5. Прочтем сообщение в окне Результаты поиска решения. Щелкнем по OK. Результаты решения видны в ячейках таблицы.

6. Сохраните результат в файле под именем оптимизация.xls

Пример решения задачи из раздела III «Регрессия»

Задача. Ниже приводиться урожайность зерновых (y) в СССP в 1922-1934 гг. (x).

x
y, ц/га	7,6	7,2	6,2	8,3	8,2	7,6	7,9	7,5	8,5	8,7	7,0	8,8	8,5

Требуется найти коэффициенты a и b зависимости y от x вдоль прямой f(x) = ax+b, пользуясь методом наименьших квадратов, для того чтобы значения f наилучшим образом приближали значения y. Построить диаграмму с исходными данными и приближающим их линейным графиком.

Ход решения

1. Запустим табличный процессор Microsoft Excel. Для этого выполним последовательность команд Пуск Þ Программы Þ Microsoft Excel.
2. Введем в ячейки данные задачи как указано на рис. 1. 3. В столбце C рассчитаем значение f(x) = ax+b. Для этого в ячейку C2 введем формулу: = $B$16*A2+$B$17 Введем формулу методом автозаполнения по столбцу. 4. В столбце D рассчитаем остатки. Для этого в ячейку D2 введем формулу: = B2-C2 Введем формулу методом автозаполнения по столбцу. 5. В ячейке D16 подсчитаем сумму квадратов остатков. Для этого в ячейку D16 введем формулу: = СУММКВ(D2:D14)	рис. 1
6. Далее решим задачу оптимизации. Целевая функция – ячейка D16, которую следует минимизировать путем изменений a и b без ограничений. Решение задачи оптимизации см. на рис. 2. Целевая ячейка D16 – стремится к минимуму. Изменяемые ячейки В16 и В17 (коэффициенты а и b). Ограничений нет.   Функция прямой будет иметь вид: f(x)=ax+b=0,1x-185,207	рис. 2

7. Построим диаграмму с исходными данными и приближающим их линейным графиком f(x) = ax+b. Для этого выполним нижеуказанную последовательность шагов.

Нажмем кнопку Мастер диаграмм .

Шаг 1. Выбор типа и вида диаграммы.

ü Во вкладке Стандартные выберем Тип – Точечная, вид – Точечная диаграмма со значениями, соединенными сглаживающими линиями без маркеров.

ü Прочтем описание выбранного вида диаграммы.

ü Нажмем кнопку Далее>.

Шаг 2. Источник данных диаграммы.

ü Во вкладке Диапазон данных активизируем опцию Ряды в столбцах.

ü Переключимся на вкладку Ряд.

ü Щелкнем по кнопке Добавить.

ü В поле Имя введем текст - Реальные данные.

ü В поле Значения X нажмем кнопку для возврата в таблицу и выделим там диапазон с годами, т.е. диапазон A2:A14. Нажмем кнопку для возврата в диалоговое окно Мастер диаграмм.

ü В поле Значения Y нажмем кнопку для возврата в таблицу и выделим там диапазон B2:B14. Нажмем кнопку для возврата в диалоговое окно Мастер диаграмм.

ü Щелкнем по кнопке Добавить для добавления второго ряда данных.

ü В поле Имя введем текст - Расчетные данные.

ü В поле Значения X укажем диапазон A2:A14.

ü В поле Значения Y диапазон С2:С14.

ü Нажмем кнопку Далее>.

Шаг 3. Параметры диаграммы.

ü Введем с клавиатуры Название диаграммы – Урожайность зерновых в СССР в 1922-1934 гг. ü Введем с клавиатуры Ось X (категорий) – год ü Введите с клавиатуры Ось Y (значений) – ц/га ü Переключимся на вкладку Линии сетки и активизируем опции, как указано на рис. 3. Переключимся на вкладку Легенда и активизируем опции, как указано на рис. 4. ü Нажмем кнопку Далее>. Шаг 4. Размещение диаграммы. ü Выберем размещение диаграммы на отдельном листе и укажем имя листа Диаграмма. ü Щелкнем по кнопке Готово. Результат см. на рис. 5.

рис. 3 рис. 4

рис. 5

10. Сохраните результат в файле под именем регрессия.xls.

 

Пример решения задачи из раздела IV «Финансовые вычисления»

Задача. Выдан кредит в сумме 1 млн. долл. с 15.01.93 по 15.03.93 под 120% годовых. Рассчитать сумму погасительного платежа.

Ход решения

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

1. Запустим табличный процессор Microsoft Excel. Для этого выполним последовательность команд Пуск Þ Программы Þ Microsoft Excel.

2. Введем исходные данные как указано на рис. 1.

рис.1

3. Настроим формат ячеек. Например, настроим формат ячейки Сумма кредита. Для этого:

· поставим курсор в ячейку B4;

· выполним последовательность команд: Формат Þ Ячейки;

· из предложенных форматов выберем Денежный;

· укажем число десятичных знаков 2;

· укажем обозначение $Английский (США);

· нажмем OK.

4. Введем формулу для подсчета Срока кредита в ячейку B6 как разность между Дата_возврата_кредита и Дата_выдачи_кредита:

= B3-B2

Настроим формат ячейки как числовой для отображения количества дней.

5. Введем формулу для подсчета Срока кредита в годах в ячейку B7 как частное Срока_кредита на 365 дней в году:

= B6/365

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

6. Введем формулу для подсчета Ставки периода в ячейку B8 как произведение Годовая_ставка на Срок_кредита_в_годах:

= B1*B7

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

7. Рассчитаем Сумму возврата в ячейке B9. Воспользуемся финансовой функцией БЗ.