1. Воспользуемся рабочей таблицей задачи 1 (рис. 4.1). Начнем с функции ТЕНДЕНЦИЯ. Для этого выделяем диапазон ячеек D4:D11, который следует заполнить значениями функции ТЕНДЕНЦИЯ, соответствующими известным данным о прибыли предприятия, вызываем команду Функция из меню Вставка. В появившемся диалоговом окне Мастер функций выделяем функцию ТЕНДЕНЦИЯ из категории Статистические, после чего щелкаем по кнопке <ОК>. Эту же операцию можно осуществить нажатием кнопки <fx> (Вставка функции) стандартной панели инструментов. В появившемся диалоговом окне Аргументы функции вводим в поле Известные_значения_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 = bmx,
с помощью функции ЛГРФПРИБЛ оно выполняется аналогично: в диапазон ячеек 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
Часа