Исследование встроенных функций методом статистического анализа

 5) Вставка гистограммы: см. Задание №2.

 6) Добавление линий тренда: ПК на одном из столбцов => \Добавить линию тренда\ => \Тип\ => \Линейная\ или \Экспоненциальная\ => \Параметры\ => \Вперёд на 1 период\ => \Показывать уравнение на диаграмме\ => \Поместить на диаграмму величину достоверности аппроксимации\ => \Ок\.

 7) В нашем случае величина достоверности для линейной регрессии выше чем для экспоненциальной. 
 
 
 
 
 
 
 
 
 
 
 
 
 

Часть 2.

Исходные  данные:

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

Задача:

 Определить, сколько может стоить однокомнатная  квартира в этом районе без балкона, без телефона, расположенная на первом этаже, общей площадью 28 м², жилой –16 м², с кухней-6 м².

 Выполнение:

 1) Создание таблицы: заполним таблицу => отдельно от нее введем стоимость кв.м, этажа, и т.д. => в ячейку I2 введем формулу «=H2*$B$13+F2*$B$14+G2*$B$15+E2*$B$16» => выполним автозаполнение.

 2) Выполнение множественной  линейной регрессии: \Вставка\ => \Функция\ => \Статистические\ => выбрать из списка «=ЛИНЕЙН» => ввести диапазоны => выделить блок ячеек, соразмерный с данным => \Ctrl\ + \Shift\ + \Enter\.

 3) Запись уравнения:  Y = m₁x₁ + m₂x₂ + … + m_nx_n + b, где m₁, m₂…m_n берутся из 1 строчки получившейся таблицы. В нашем случае: «=СУММ(B20*B12;C20*C12;D20*D12;E20*E12;F20*F12;G20*G12)».

 4) Выполнение множественной  экспоненциальной регрессии: \Вставка\ => \Функция\ => \Статистические\ => выбрать из списка «=ЛГРФПРИБЛ» => ввести диапазоны => выделить блок ячеек, соразмерный с данным => \Ctrl\ + \Shift\ + \Enter\.

 5) Запись уравнения:  Y = b*m₁^x1* m₂^x2*…*m_n^xn, где m₁, m₂…m_n берутся из 1 строчки получившейся таблицы. В нашем случае: «=ПРОИЗВЕД(B32^B12;C32^C12;D32^D12;E32^E12;F32^F12;G32^G12)». 
 
 
 
 
 
 
 
 
 

Контрольные вопросы:

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

 2) На основании результатов вычислений, полученных с помощью функций ЛИНЕЙН() и ЛГРФПРИБЛ(), напишите уравнения прямой и экспоненциальной кривой для простой и множественной регрессии.

 3) Каковы правила ввода и использования табличных формул?

 4) Как на гистограмме исходных данных добавить линию тренда? 

Ответы  на контрольные вопросы: 

 1) Лучше всего использовать «=РОСТ», т.к. погрешность при ее использовании меньше.

    Y = 1,033124226^x1* 0,983670674^x2*0,973285568^x3*1,00393657^x4* 0,994292966^x5*0,998866934^x6

 3) Результат выводиться в виде массива данных, поэтому необходимо указывать выходной диапазон, равный исходному, и заполнять его с помощью \Shift\+\Ctrl\+\Enter\.

 4) Для того чтобы дополнить диаграмму исходных данных линией тренда, необходимо выполнить следующие действия:

• выделить на диаграмме ряд данных, для которого требуется построить линию тренда;
• в меню Вставка выбрать команду Линия тренда;
• в открывшемся окне задать метод интерполяции (линейный, полиномиальный, логарифмический и т. д.), а также другие параметры (например, вывод уравнения интерполяционной кривой, направление и количество периодов для экстраполяции (прогноза) и др.);
• нажать кнопку ОК.

 
 
 
 
 
 
 
 
 
 

Задание  №5

Анализ  “что - если”. Поиск решения.

Исходные  данные: 250 млн руб, 3 вклада ≤ 10% от общего.

Задание:

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

Выполнение:

 1) Создадим таблицу => заполним ее произвольными данными.

 2) Поиск решения:  \Сервис\ => \Поиск решения\ => задать целевую ячейку => \Равной максимальному значению\ => \Изменяя ячейки\ => указать диапазон => \Ограничения\ => \Добавить\ => ввести ограничения => \Ок\ => \Выполнить\ => \Ок\.

 3) Сохранить сценарий.

 4) Список ограничений  конечного сценария для нашего случая:

$B$3 <= ($B$4+$B$5)*2/3

$B$3 >= ($B$6/100)*10

$B$4 >= ($B$6/100)*10

$B$5 >= ($B$6/100)*10

$B$6 <= 250000000

$C$3 <= 20

$C$3 >= 5

$C$4 <= 13

$C$4 >= 5

$C$5 <= 10

$C$5 >= 3

$D$3 <= 20

$D$3 >= 10

$D$4 <= 5

$D$4 >= 2

$D$5 <= 7

$D$5 >= 3 
 
 
 
 
 
 
 
 
 
 
 

Контрольные вопросы и задания: 

1) Для чего используется анализ “что - если”?

2) Что такое сценарий? Как создать и просмотреть сценарий?

3) Как в EXCEL решаются задачи типа ”Найти значение некоторого параметра, при котором значение другого параметра не превысит заданной величины”?

4) В каких случаях используется команда Поиск решения?

5) Изучите диалоговое окно Параметры поиска решения и с помощью справочной системы EXCEL ответьте на вопросы:

   a) Как ограничить  длительность процесса последовательных  приближений к решению?

   б)  Что  задает значение в поле Точность диалогового  окна Параметры поиска решения?

   в) Как просмотреть  промежуточные результаты поиска решения? 

Ответы  на контрольные вопросы: 

1) “Что - если”: изменение значения какой-либо ячейки приводит к пересчету во всех зависящих от нее ячейках.

2) Сценарий - это именованный набор изменяемых значений, представляющих некоторое множество параметров модели “что -если”. Создание сценария: \Сервис\ => \Сценарии\ => \Добавить\ => введите имя сценария => введите изменяемые ячейки => \Ок\ => введите новые значения или формулы => \Ок\ => \Закрыть\.

3) \Сервис\ => \Подбор параметра\ => ввести изменения.

4) Поиск решения используется при необходимости изменения диапазона данных исходя из особых ограничений.

5. 

a)  В поле Предельное  число итераций введите максимальное  количество итераций, отводимое  на достижение конечного результата.

б)  Это  погрешность  — чем меньше введенное число, тем выше точность результатов.

в) Их можно посмотреть вызвав окно отчета Solver. 
 
 
 
 
 
 
 
 
 
 
 

Заключение. 

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

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

 Ввиду этого целесообразно  использовать MS Excel для подготовки пользователя к работе в MS Access.