Файл: 3. 2 Ход работы 3 Ответы на контрольные вопросы и задания.rtf

- средняя величина.

Значит формула Коэффициента вариаций: = (КОРЕНЬ (R18) /R13);

3) В чем заключаются особенности построения гистограммы распределения данных?

Особенность построения гистограммы с распределением данных в том, что для неё необходимы значения статистических данных (среднее значение, дисперсия, медиана и т.п.).

) Напишите логическую формулу, которая выводит текстовое сообщение ”Вычислена сумма" или ”Вычислено произведение” в зависимости от того, что было вычислено на рабочем листе в п.3 задания 1.

Новая логическая формула будет иметь вид:

=ЕСЛИ (ЗНАК (C2) =ЗНАК (C4) =ЗНАК (C6); "Вычисленасумма"; "Вычислено произведение").

6. Задание №4

6.1 Выполнение задания

Вариант задания включает 2 задачи. Для каждой из них необходимо получить:

Составить таблицу исходных данных.

2 Прогноз изменения данных, выполненный с использованием линейной и экспоненциальной регрессии.

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

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

На гистограмме (или графике) исходных данных в задаче 1 отобразить линию тренда, а также соответствующее ей уравнение.

Вариант 3.

. Автомобильный салон имеет данные о количестве проданных автомобилей "Мерседес” и "БМВ" за последние 4 квартала. Учитывая тенденцию изменения объема продаж. Определить, каких автомобилей ("Мерседес” или ”БМВ”) необходимо закупать больше в следующем квартале?

. Известны следующие данные о пяти недавно проданных подержанных автомобилях: стоимость продажи, стоимость аналогичного нового автомобиля, год выпуска, пробег, количество капитальных ремонтов, экспертные заключения о состоянии кузова и техническом состоянии автомобилей (по 10-бальной шкале). Определить, сколько может стоить автомобиль с соответствующими характеристиками: 34 млн. руб., 1990 г., 140000 км, 0, 6, 7.

6.2 Ход работы

6.2.1 Первое задание

Пункт 1.

Составим таблицу начальных значений (рис. 17).

Пункт 2.

Составим прогноз изменения объема продаж в первом квартале нового года, для этого используем функцию ТЕНДЕНЦИЯ для линейной регрессии и функцию РОСТ для экспоненциальной (рис. 18).

=ТЕНДЕНЦИЯ (F4: F7) и =ТЕНДЕНЦИЯ (J4: J7),

=РОСТ (F4: F7) и =РОСТ (J4: J7).

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

Пункт 3.

Коэффициент в уравнении линейной регрессии найдем по формуле: =ЛИНЕЙН (F4: F7) и =ЛИНЕЙН (J4: J7), а для экспоненциальной регрессии - =ЛГРФПРИБЛ (F4: F7) и =ЛГРФПРИБЛ (J4: J7). Результаты для линейной регрессии Мерседес и БМВ равны 10,5 и - 12,2 соответственно, для экспоненциальной Мерседес и БМВ равны 1,0286641 и 0,999369631 соответственно.

Пункт 4.

Погрешности вычислений для линейной регрессии и экспоненциальной от МЕРСЕДЕС и БМВ рассчитаем по формулам =СТОШYX (F4: F7; $E$4: $E$7) и =СТОШYX (G4: G7; $E$4: $E$7) соответственно, а для экспоненциальной =СТОШYX (J4: J7; $I$4: $I$7) и =СТОШYX (K4: K7; $I$4: $I$7) соответственно. Результаты приведены на рис.30. В этом случае для решения данной задачи подходят оба способа решения, как с помощью линейной регрессии, так и с помощью экспоненциальной, так как погрешности практически одинаковы.

Пункт 5.

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

6.2.2 Второе задание

Пункт 1.

Построим таблицу начальных данных для линейной регрессии и экспоненциальной (см. рис. 35).

Пункт 2.

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

=ТЕНДЕНЦИЯ (B3: B7; C3: H7; C8: H8; ЛОЖЬ) - для линейной регрессии и =РОСТ (B13: B17; C13: H17; C18: H18; ЛОЖЬ) - для экспоненциальной регрессии.

Результат на рис. 21.

Пункт 3.

Коэффициенты рассчитаем используя формулы =ЛИНЕЙН (B3: B7; C3: H7;; ЛОЖЬ) для линейной регрессии и =ЛГРФПРИБЛ (B13: B17; C13: H17;; ЛОЖЬ) для экспоненциальной.

Пункт 4. Рассчитаем погрешности вычислений по формулам =СТОШYX (B3: B8; C3: H8) для прямолинейной регрессии и =СТОШYX (B13: B18; C13: H18) для экспоненциальной.

Не требуется выполнять Пункт 5, так как этого нет в задании.

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

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

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

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

Простая регрессия:

Линейная регрессия:

=-281101,8302*x
Экспоненциальная регрессия:=0,672234727^x

Множественная регрессия:

Линейная регрессия:

= 426006,81*x₁+186068,053*x₂+6,4*x₄+2707,94*x₅+0,168793685
Экспоненциальная регрессия:

= (0,495592796^x) * (0,676074258^x) * (1^x) * (0,999983514^x) * (1,011410682^x) * 1,000000288
. Определите погрешность вычислений для каждого коэффициента в уравнении прямой и экспоненциальной кривой.

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

Линейная регрессия: Стандартная ошибка для x 0,00075982

Экспоненциальная регрессия: Стандартная ошибка для x 0,52975662

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

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

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

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

Выбрать подходящую линию тренда из команд, находящихся во вкладке Работа с диаграммами - Макет - Анализ - Линия тренда

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

. Как с помощью линии тренда отобразить прогнозируемые величины?

Вызвать контекстное меню Линии тренда

2 Выбрать