Пример 1. Решение уравнений

Cанкт-Петербург

СОДЕРЖАНИЕ

Лабораторная работа 1: Анализ данных с помощью сценариев “ЧТО-ЕСЛИ”. 4

Лабораторная работа 2: Расчет характеристик марковских процессов. 10

Лабораторная работа 3: Решение задач линейной оптимизации. 14

Лабораторная работа 4: Изучение законов распределения случайных величин. 18

Лабораторная работа 5: Генерация случайных чисел и анализ выборки данных. 23

Лабораторная работа 6: Анализ временных рядов. 29

Лабораторная работа 7: Регрессионный анализ. 34

Лабораторная работа 1: Анализ данных с помощью сценариев “ЧТО-ЕСЛИ”

Цель работы

Изучить средства программы Microsoft Excel для анализа данных с помощью сценариев и таблиц подстановки.

Теоретические основы

Во многих случаях возникает необходимость просматривать различные варианты значений в ячейках на листе. Для выполнения анализа “что-если” Excel предоставляет следующие возможности:

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

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

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

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

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

В Excel 2003: выполните команду Сервис (Tools) – Сценарии (Scenarios).

В Excel 2007: вкладка Данные, группа Работа с данными, кнопка Анализ “что-если”, команда Диспетчер сценариев…

В результате откроется окно диалога (рис.1.1):

Рис.1.1 Диспетчер сценариев

В окне Диспетчер сценариев (Scenario Manager) следует нажать кнопку Добавить (Add). Отобразится окно Добавление сценария (Add Scenario), изображенное на рис. 1.2.

Рис. 1.2 Добавление сценариев

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

Таблицы подстановки (данных)помогают создавать сценарии типа «что-если». Например, изменяя значения одного или двух параметров, можно проследить их влияние на результирующее значение.

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

В обоих случаях используется одно диалоговое окно (см. рис. 1.3), для открытия которого следует выполнить следующие действия:

В Excel 2003: выполните команду меню Данные (Data) – Таблица подстановки… (Table)

В Excel 2007: вкладка Данные, группа Работа с данными, кнопка Анализ “что-если”, команда Таблица данных…

Рис.1.3 Окно диалога для применения таблиц подстановки

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

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

Содержание работы

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

Порядок выполнения

Упражнение 1. Создание и просмотр сценария

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

2. Скопируйте лист Товарный чек в новую книгу, сохраните ее под именем Мой_Сценарий. Книгу ArrayFormulas.xls можно закрыть.

3. Подсчитайте общую сумму заказа (ячейка C7).

4. Перед созданием сценария рекомендуется выделить ячейки, данные в которых могут изменяться. В таблице могут изменяться значения в ячейках В2:C5, выделите этот диапазон.

5. В Excel 2003: выполните команду Сервис (Tools) – Сценарии (Scenarios).

В Excel 2007: вкладка Данные, группа Работа с данными, кнопка Анализ “что-если”, команда Диспетчер сценариев…

6. В окне Диспетчер сценариев (Scenario Manager) нажмите кнопку Добавить (Add). Отобразится окно Добавление сценария (Add Scenario).

7. В поле Название сценария (Scenario name) введите название создаваемого сценария – «Вариант исходный».

8. Проверьте, что изменяемые ячейки, выделенные до начала создания сценария автоматически указаны в поле Изменяемые ячейки (Changing cells).

9. В поле Примечание (Comment) просмотрите автоматически вставляемые имя автора сценария и дата создания. При желании дополните примечание необходимой информацией (или очистите поле). Содержание примечания будет отображаться при выборе сценария в поле Примечание окна Диспетчер сценариев (Scenario Manager).

10. Нажмите кнопку ОК, в результате чего отобразится окно Значения ячеек сценария (ScenarioValues).

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

12. Для создания первого сценария, отражающего текущие (исходные) значения и перехода к созданию нового сценария нажмите кнопку Добавить (Add). Снова отобразится окно Добавление сценария (Add Scenario).

13. Создайте еще два сценария с именами «Вариант один» и «Вариант два». Значения ячеек, влияние которых необходимо просмотреть укажите по своему усмотрению.

14. Нажмите кнопку ОК. Отобразится окно Диспетчер сценариев (Scenario Manager),в котором будет показан список доступных сценариев.

15. Выберите сценарий «Вариант один» и нажмите кнопку Вывести (Show).

16. Просмотрите сценарий «Вариант два».

17. Нажмите кнопку Закрыть (Close).

В дальнейшем для отображения требуемого сценария необходимо в окне Диспетчер сценариев (Scenario Manager) выбрать нужный сценарий и нажать кнопку Вывести (Show).

Любой сценарий можно изменить или дополнить. Для этого в окне Диспетчер сценариев (Scenario Manager) следует выбрать нужный сценарий и нажать кнопку Изменить (Edit), после чего отобразится окно Изменение сценария (Edit Scenario).После внесенных изменений следует нажать кнопку ОК.

Упражнение 2. Объединение сценариев

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

1. Создайте новую книгу и скопируйте в нее данные листа Товарный чек.

2. Создайте новый сценарий с именем «Вариант Омега». Изменяемые ячейки те же, значения их укажите по своему усмотрению.

3. Откройте исходную книгу Мой_Сценарий.xls лист Товарный чек.

4. В Excel 2003: для объединения исходных сценариев с новым выберите Сервис (Tools) – Сценарии (Scenarios)и нажмите кнопкуОбъединить (Merge).

В Excel 2007: вкладкаДанные,группаРабота с данными,кнопкаАнализ “что-если”,командаДиспетчер сценариев…

5. В окнеОбъединение сценариев (Merge Scenarios)в поле Книга (Book) выберите книгу, в поле Лист (Sheet) укажите лист с новым сценарием. Нажмите OK.

6. В окне Диспетчер сценариев (Scenario Manager) выберите новый сценарий и выведите его на экран, нажав кнопку Вывести (Show).

Упражнение 3. Создание отчета по сценарию

После завершения построения сценариев можно создать отчет.

Для создания отчета выполните следующие действия:

1. Откройте книгу Мой_Сценарий.xls лист Товарный чек.

2. В Excel 2003: выполните команду Сервис(Tools) – Сценарии(Scenarios),
в Excel 2007: вкладка Данные, группа Работа с данными, кнопка Анализ “что-если”, команда Диспетчер сценариев…)

3. В окне Диспетчер сценариев (Scenario Manager) нажмите кнопку Отчет (Summary).

4. Проверьте ячейку результата (C7) и нажмите OK.

5. В открывшемся новом листе Структура сценария (Scenario Summary) просмотрите отчет.

Упражнение 4. Создание таблицы подстановки с одним входом

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

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

1. Откройте книгу Таблицы_подстановки.xls лист 1.

2. Изучите функцию ПЛТ(PMT) в ячейке B7, рассчитывающую сумму периодического платежа в зависимости от общего числа периодов выплат (ячейка B6), годовой процентной ставки (ячейка B5) и общей суммы (B4).

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

1. В отдельную строку (в ячейки с E6 по I6) введите значения, которые следует подставлять в ячейку ввода – 12, 24, 36, 48, 60.
2. Проверьте, что введена формула в ячейку, расположенную на один столбец левее и на одну строку ниже первого значения (ячейка D7).
3. Выделите диапазон ячеек, содержащий формулы и значения подстановки (D6:I7).
4. В Excel 2003: выполните команду меню Данные (Data) – Таблица подстановки… (Table)

В Excel 2007: вкладка Данные, группа Работа с данными, кнопка Анализ “что-если”, команда Таблица данных…

1. Введите в поле Подставлять значения по столбцам в: (Row input cell) ссылку на ячейку ввода значения периода – B6. Второе поле оставьте пустым. Нажмите OK.

4. Просмотрите ячейки E7:I7, в которых создалась формула массива, использующая функцию ТАБЛИЦА(Table) с одним аргументом.

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

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

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

1. Откройте книгу Таблицы_подстановки.xls лист 1.

2. Проверьте, что формула введена в ячейку D10.

3. В том же столбце ниже формулы (D11:D16) введите шесть значений подстановки для величины процентной ставки от 6,0% до 8,5%.

4. В строке правее формулы (E10:I10) введите пять значений периода выплат – 12, 24, 36, 48, 60.

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

6. В Excel 2003: выполните команду меню Данные (Data) – Таблица подстановки (Table…)

В Excel 2007: вкладка Данные, группа Работа с данными, кнопка Анализ “что-если”, команда Таблица данных…

7. Введите в поле Подставлять значения по столбцам в: (Row input cell) ссылку на ячейку ввода значения периода – B6.

8. Введите в поле Подставлять значения по строкам в: (Column input cell) ссылку на ячейку ввода величины процентной ставки – B5.

9. Нажмите OK. Просмотрите ячейки E11:I16, в которых создалась формула массива, использующая функцию ТАБЛИЦА с двумя аргументами.

Отчётность по работе

После выполнения работы обучаемый представляет отчет. Отчёт должен содержать:

1. Название и цель работы.

2. Результаты выполнения упражнений.

3. Выводы по результатам работы.

Лабораторная работа 2: Расчет характеристик марковских процессов

Цель работы

Изучить средства программы Microsoft Excel для расчета основных характеристик марковских процессов.

Теоретические основы

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

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

Многие инженерные задачи сводятся к исследованию функций одной или нескольких переменных вида Y = f(X) или Y = f(x₁,x₂,…x_N).

Исследовать функцию, значит установить область ее существования (значения Х при которых возможно вычислить Y), определить области значений Х, при которых Y принимает положительные, отрицательные и аномально большие значения ("уходит в бесконечность"), найти максимумы, минимумы, иногда точки перегиба графика функции, а также корни уравнения Y = f(x) – значения х, при которых Y обращается в 0 (график функции пересекает ось абсцисс).

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

Один из итерационных методов - табулированиефункции, включающий следующие этапы:

- расчет значений Y при заданных X в большом диапазоне значений Х с большим шагом;

- табулирование с небольшим шагом в наиболее близких диапазонах – вблизи корней, максимумов и минимумов;

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

Пример 1. Решение уравнений

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

Требуется: решить уравнение Y = 0,1∙х² - х -11.

Решение.

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

1.1. Задать область определения (х) от –20 до +20: занести в соседние ячейки (например, А5 и А6) –20 и –19, выделить обе ячейки, поставить курсор на черный квадратик в правом нижнем углу, нажать левую клавишу мыши и потащить вниз до появления числа 20.

1.2. Создать область значений: в ячейку рядом с –20 вставить формулу =0,1*А5^2-А5-11, скопировать ее вниз.

1.3. Выделить область значений и вызвать в меню Вставка команду Диаграмма… (Excel 2003) или на вкладке Вставка в группе Диаграммы выбрать кнопку График(Excel 2007). Обратите внимание, что на оси Х указываются не значения аргумента, а порядковые номера.

2. Найти первый корень уравнения:

2.1. Сделать активной ячейку в диапазоне Y вблизи одного из корней (первое пересечение графика функции с осью абсцисс).

2.2. Вызвать Подбор параметра:

2.2.1. Excel 2003: в менюСервис, выбрать команду Подбор параметра;

2.2.2. Excel 2007: на вкладке Данные, в группе Работа с данными выбрать кнопку Анализ «что-если» и указать команду Подбор параметра.

2.3. В окне Значение установить 0, в нижнем окне Изменяя значение ячейки указать адрес ячейки Х, соответствующей активной ячейки Y, после чего кликнуть по кнопке ОК.

3. Найти второй корень, выбрав значения Y и X вблизи него.