Задача 3. 1. Воспользуемся рабочей таблицей задачи 1 (рис

1. Воспользуемся рабочей таблицей задачи 1 (рис. 4.1). Начнем с функции ТЕНДЕНЦИЯ. Для этого выделяем диапазон ячеек D4:D11, который следует заполнить значениями функции ТЕНДЕНЦИЯ, соответствующими известным данным о прибыли предприятия, вызываем команду Функция из меню Вставка. В появившемся диалоговом окне Мастер функций выделяем функцию ТЕНДЕНЦИЯ из категории Статистические, после чего щелкаем по кнопке <ОК>. Эту же операцию можно осуществить нажатием кнопки <f_x> (Вставка функции) стандартной панели инструментов. В появившемся диалоговом окне Аргументы функции вводим в поле Известные_значения_y диапазон ячеек C4:C11; в поле Известные_значения_х – диапазон ячеек B4:B11. Чтобы вводимая формула стала формулой массива, при закрытии окна Аргументы функции используем комбинацию клавиш <Ctrl> + <Shift> + <Enter>.

Введенная нами формула в строке формул будет иметь следующий вид:

{=ТЕНДЕНЦИЯ(C4:C11;B4:B11)}

В результате диапазон ячеек D4:D11 заполняется соответствующими значениями функции ТЕНДЕНЦИЯ (см. рис. 4.6).

Рис. 4.6. Заполненная таблица

2. Для составления прогноза о прибыли предприятия на 2003 и 2004 гг. необходимо выполнить ниже приведенные действия.

Выделить диапазон ячеек D12:D13, куда будут заноситься значения, прогнозируемые функцией ТЕНДЕНЦИЯ. Вызвать функцию ТЕНДЕНЦИЯ и в появившемся диалоговом окне Аргументы функции ввести в поле Известные_значения_y – диапазон ячеек C4:C11; в поле Известные_значения_х – диапазон ячеек B4:B11; а в поле Новые_значения_х – диапазон ячеек B12:B13. Превратить эту формулу в формулу массива, используя комбинацию клавиш <Ctrl> + <Shift> + <Enter> при закрытии окна Аргументы функции.

Введенная формула будет иметь следующий вид:

{=ТЕНДЕНЦИЯ(C4:C11;B4:B11;B12:B13)},

а диапазон ячеек D12:D13 заполнится прогнозируемыми значениями функции ТЕНДЕНЦИЯ (см. рис. 4.6).

Аналогично заполняется ряд данных с помощью функции РОСТ, которая используется при анализе нелинейных зависимостей и работает точно так же, как ее линейный аналог ТЕНДЕНЦИЯ. На рис. 4.7 представлена таблица в режиме показа формул.

Рис. 4.7. Таблица в режиме формул

3. Для исходных данных и полученных рядов данных построим диаграмму, изображенную на рис. 4.8.

Рис. 4.8. Диаграмма «Динамика прибыли предприятия за 1995 – 2004 гг.

Задача 4

Отметим, что, в отличие от функций ТЕНДЕНЦИЯ и РОСТ, ни одна из перечисленных выше функций (НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ) не является регрессией. Эти функции играют лишь вспомогательную роль, определяя необходимые параметры регрессии.

Для линейной и экспоненциальной регрессий, построенных с помощью функций НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ, внешний вид их уравнений всегда известен, в отличие от линейной и экспоненциальной регрессий, соответствующих функциям ТЕНДЕНЦИЯ и РОСТ.

1. Построим линейную регрессию, имеющую уравнение:

y = mx+b,

с помощью функций НАКЛОН и ОТРЕЗОК, причем угловой коэффициент регрессии m определяется функцией НАКЛОН, а свободный член b – функцией ОТРЕЗОК.

Для этого осуществляем следующие действия: заносим исходную таблицу в диапазон ячеек A4:B14 (см. рис. 4.9). Значение параметра m будет определяться в ячейке С19. Вызываем команду Функция из меню Вставка. В появившемся диалоговом окне Мастер функций выделяем функцию НАКЛОН из категории Статистические, после чего щелкаем по кнопке <ОК>. Заносим диапазон ячеек B4:B14 в поле Известные_ значения_y и диапазон ячеек А4:А14 в поле Известные_значения_х. В ячейку С19 будет введена формула:

=НАКЛОН(B4:B14;A4:A14).

По аналогичной методике определяется значение параметра b в ячейке D19. И ее содержимое будет иметь следующий вид:

=ОТРЕЗОК(B4:B14;A4:A14)

Таким образом, необходимые для построения линейной регрессии значения параметров m и b будут сохраняться соответственно в ячейках C19 и D19.

Далее заносим в ячейку С4 формулу линейной регрессии в виде:

=$C$19*A4+$D$19

В этой формуле ячейки С19 и D19 записаны с абсолютными ссылками (адрес ячейки не должен меняться при возможном копировании). Знак абсолютной ссылки $ можно набить либо с клавиатуры, либо с помощью клавиши F4, предварительно установив курсор на адресе ячейки. Воспользовавшись маркером заполнения, копируем эту формулу в диапазон ячеек С4:С17. Получаем искомый ряд данных (рис. 4.9). В связи с тем, что количество заявок – целое число, следует установить на вкладке Число окна Формат ячеек (Формат ® Ячейки) числовой формат с числом десятичных знаков 0.

Рис. 4.9. Таблица данных о поступлении заявок в диспетчерскую службу

Теперь построим линейную регрессию, заданную уравнением:

y = mx+b,

с помощью функции ЛИНЕЙН. Для этого, вводим в диапазон ячеек C20:D20 функцию ЛИНЕЙН как формулу массива (при закрытии окна Аргументы функции используем комбинацию клавиш <Ctrl> + <Shift> + <Enter>):

{=ЛИНЕЙН(B4:B14;A4:A14)}

В результате получаем в ячейке C20 значение параметра m, а в ячейке D20 – значение параметра b. Вводим в ячейку D4 следующую формулу:

=$C$20*A4+$D$20,

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

2. Строим экспоненциальную регрессию, имеющую уравнение:

y = bm^x,

с помощью функции ЛГРФПРИБЛ оно выполняется аналогично: в диапазон ячеек C21:D21 вводим функцию ЛГРФПРИБЛ как формулу массива:

{=ЛГРФПРИБЛ(B4:B14;A4:A14)}

При этом в ячейке C21 будет определено значение параметра m, а в ячейке D21 – значение параметра b; в ячейку E4 вводится формула:

=$D$21*$C$21^A4

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

На рис. 4.10 приведена таблица, где видны используемые нами функции с необходимыми диапазонами ячеек, а также формулы.

Рис. 4.10. Таблица с введенными формулами

3. Для исходных данных и полученных рядов данных построена диаграмма, изображенная на рис. 4.11.

Рис. 4.11. Диаграмма «Динамика поступления заявок»

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

1. Что понимается под линейной и полиномиальной линиями тренда.

2. Для задачи №2 составьте прогноз о прибыли предприятия на 2005 и 2006 гг.

3. Для задачи №3, используя функции ТЕНДЕНЦИЯ и РОСТ, составьте прогноз о прибыли предприятия на 2005 и 2006 гг.

4. Объясните понятие линейной и экспоненциальной регрессии.

При выполнении лабораторных работ использовать [1], [3], [4] и [5].

3.5. Лабораторная работа №5

Часа