 |
Использование статистических функций
|
|
|
|
| Задание 3. | Исследовать линейную зависимость между стоимостью и площадью жилого объекта с помощью статистических функций. |
1. Откройте файл Простая регрессия.xls.
2. Скопируйте данные с Листа1 (диапазон A1:B16) на Лист3 в такой же диапазон.
3. В ячейку F1 введите Значение, а в ячейку H1 введите Функция.
4. Выделите ячейку H2, вызовите Мастер функций и в списке статистических функций выберите функцию ОТРЕЗОК. В диалоговом окне Аргументы функции задайте соответствующие диапазоны переменных Y (Цена) и X (Площадь), не включая меток. Щелкните на кнопке ОК. В ячейке H2 будет вычислен постоянный член уравнения регрессии b 0= 18,78947.
5. Выделите ячейку H3, вызовите Мастер функций и в списке статистических функций выберите функцию НАКЛОН. В диалоговом окне Аргументы функции задайте соответствующие диапазоны переменных Y (Цена) и X (Площадь), не включая меток. Щелкните на кнопке ОК. В ячейке H3 будет вычислен коэффициент регрессии b 1= 0,02101.
6. Выделите ячейку H4, вызовите Мастер функций и в списке статистических функций выберите функцию СТОШYX. В диалоговом окне Аргументы функции задайте соответствующие диапазоны переменных Y (Цена) и X (Площадь), не включая меток. Щелкните на кнопке ОК. В ячейке H4 будет вычислена стандартная ошибка оценки 3,2377744.
7. Как будет выглядеть уравнение регрессии? Сравните с результатами, полученными с помощью инструмента анализа Регрессия.
8. Скопируйте значения из столбца H в столбец F. Для этого выделите ячейки с вычисленными значениями (H2:H4) и выполните команду: Правка®Копировать, а затем выделите ячейку F2 и выполните команду: Правка®Специальная вставка... В диалоговом окне включите опцию значения и щелкните на кнопке ОК.
|
|
|
9. Чтобы формулы были видны в столбце H, выберите команду Сервис®Параметры... и на вкладке Вид в области Параметры окна включите опцию Формулы. Увеличьте ширину столбца H, чтобы формулы были видны полностью.
Для предсказания цены используется функция ПРЕДСКАЗ. Допустим, мы хотим знать цену для объекта с жилой площадью в 1000 квадратных метров.
10. В ячейку D8 введите Площадь, а в ячейку F8 введите Предсказанная цена, в ячейку H8 введите Функция. В ячейке D9 задайте значение 1000.
11. Выделите ячейку H9 и вызовите Мастер функций. В списке статистических функций выберите функцию ПРЕДСКАЗ. В диалоговом окне Аргументы функции задайте значение X, выделив ячейку D9, а также соответствующие диапазоны переменных Y (Цена) и X (Площадь), не включая меток. Щелкните на кнопке ОК. В ячейке H9 будет выведена формула.
12. Выделите ячейку с формулой H9 и выполните команду: Правка®Копировать, а затем выделите ячейку F9 и выполните команду: Правка®Специальная вставка... В диалоговом окне включите опцию значения и щелкните на кнопке ОК. Какую стоимость будет иметь объект с жилой площадью в 1000 квадратных метров?
Контрольные вопросы
1. В чем разница между корреляционным анализом и регрессионным анализом?
2. Какова основная задача регрессионного анализа?
3. Какой вид анализа (корреляционный или регрессионный) применяется в каждой из описанных ниже ситуаций?
а) Выяснение наличия какой-либо взаимосвязи между расходами на рекламу и объемом продаж.
б) Разработка системы прогнозирования эффективности портфеля ценных бумаг, основанной на изменениях одного из ведущих индексов фондовой биржи.
в) Создание инструмента формирования сметы, позволяющего выражать затраты в терминах количества произведенных изделий.
г) Анализ данных с целью определения силы взаимосвязи между моральным состоянием работников и их производительностью.
4. Какой вид имеет выборочная функция линейной регрессии?
|
|
|
5. С какой целью используется МНК? Чем характеризуется этот метод?
6. Чем отличается линия наименьших квадратов от других линий на диаграмме рассеяния?
7. Как интерпретируются коэффициенты линейной регрессии b 0, b 1.
8. Что показывает коэффициент детерминации R 2? Какое значение R 2 лучше, более низкое или более высокое?
9. Как интерпретируется стандартная ошибка оценки для линии регрессии? Какое значение стандартной ошибки лучше, более низкое или более высокое?
10. Какие характеристики используются при оценке значимости коэффициента регрессии?
11. Каким образом осуществляется прогнозирование в построенной линейной модели регрессии?
Контрольные задания
Добавьте Лист4 и выполните задание с использованием базы данных служащих (файл База данных служащих.xls находится в папке Мои документы).
| Вариант | Задание |
| Рассматривая годовую зарплату служащих как переменную Y, а стаж работы – как переменную X, постройте линию тренда для линейной регрессии. Проанализируйте полученное уравнение и коэффициент детерминации. | |
| Рассматривая годовую зарплату служащих как переменную Y, а стаж работы – как переменную X, найдите стандартную ошибку оценки. О чем свидетельствуют это значение? | |
| Рассматривая годовую зарплату служащих как переменную Y, а стаж работы – как переменную X, найдите стандартную ошибку коэффициента регрессии. О чем свидетельствует это значение? | |
| Рассматривая годовую зарплату служащих как переменную Y, а стаж работы – как переменную X, постройте График остатков для линейной регрессии. Можно ли считать удовлетворительным полученное линейное приближение? | |
| Рассматривая годовую зарплату служащих как переменную Y, а стаж работы – как переменную X, с помощью статистических функций получите уравнение регрессии. Какую заработную плату можно ожидать для служащего со стажем работы 8 лет? | |
| Рассматривая годовую зарплату служащих как переменную Y, а стаж работы – как переменную X, с помощью статистических функций получите уравнение регрессии. На какую заработную плату может рассчитывать служащий с нулевым стажем работы? | |
| Рассматривая годовую зарплату служащих как переменную Y, а возраст – как переменную X, постройте линию тренда для линейной регрессии. Проанализируйте полученное уравнение и коэффициент детерминации. | |
| Рассматривая годовую зарплату служащих как переменную Y, а возраст – как переменную X, найдите стандартную ошибку оценки. О чем свидетельствуют это значение? | |
| Рассматривая годовую зарплату служащих как переменную Y, а возраст – как переменную X, найдите стандартную ошибку коэффициента регрессии. О чем свидетельствует это значение? | |
| Рассматривая годовую зарплату служащих как переменную Y, а возраст – как переменную X, постройте График остатков для линейной регрессии. Можно ли считать удовлетворительным полученное линейное приближение? | |
| Рассматривая годовую зарплату служащих как переменную Y, а возраст – как переменную X, с помощью статистических функций получите уравнение регрессии. Какую заработную плату можно ожидать для служащего в возрасте 42 лет? | |
| Рассматривая стаж работы как переменную Y, а возраст – как переменную X, постройте линию тренда для линейной регрессии. Проанализируйте полученное уравнение и коэффициент детерминации. | |
| Рассматривая стаж работы как переменную Y, а возраст – как переменную X, постройте График остатков для линейной регрессии. Можно ли считать удовлетворительным полученное линейное приближение? | |
| Рассматривая стаж работы как переменную Y, а возраст – как переменную X, с помощью статистических функций получите уравнение регрессии. Наличие какого стажа можно ожидать для служащего в возрасте 50 лет? |
|
|
|
|
|
|
