Параметров моделей линейных и нелинейных зависимостей
Цель работы: изучение методики использования функций рабочего листа Excel ТЕНДЕНЦИЯ, ПРЕДСКАЗ, КОРРЕЛ, ПИРСОН, ЛГРФПРИБЛ, РОСТ для прогнозирования линейных и нелинейных процессов.
Исходные положения. В главном меню Excel вызвать справочную систему с помощью кнопки [Справка] из главного меню. Вывести на экран описание функций листа ТЕНДЕНЦИЯ, ПРЕДСКАЗ, КОРРЕЛ, ПИРСОН, ЛГРФПРИБЛ, РОСТ. Внимательно изучите и законспектируйте основные положения из описания этих функций: назначение функций, математические модели, синтаксис, порядок ввода, форму представления результатов и описание расшифровки получаемых результатов. Порядок выполнения работы. Получив у преподавателя исходные данные для расчета, введите их в поле электронной таблицы. С помощью функции ТЕНДЕНЦИЯ определить прогнозные значения зависимой переменной у. С помощью функции ПРЕДСКАЗ определить прогнозные значения функции у отдельно для каждой из независимых переменных. С помощью функции КОРРЕЛ и ПИРСОН определить парные коэффициенты корреляции между всеми независимыми переменными, а также между независимыми и зависимой переменной. С помощью функции ЛГРФПРИБЛ определить параметры множественного уравнения регрессии для нелинейной зависимости. С помощью функции РОСТ определить прогнозные значения зависимой величины для нелинейной зависимости. Модели, используемые в функциях: ТЕНДЕНЦИЯ у=b0+b1* х 1+b2* х 2+…+bn* x n ПРЕДСКАЗ у=b0+b1* х ЛГРФПРИБЛ и РОСТ у=b0*b1 х 1* b2 х 2*…* bn х n КОРРЕЛ
ПИРСОН
Пример выполнения работы. Используя исходные данные из предыдущей работы, найдем расчетные значения прогнозируемой величины прибыли. Для этого выделим массив ячеек, соответствующий неизвестным значениям величины прибыли для Продуктов 11, 12 и 13 – это ячейки Е14: Е16. Вызвать функцию ТЕНДЕНЦИЯ из категории Статистические. В появившемся диалоговом окне в соответствующие текстовые поля ввести аргументы функции: известные_значения_у – адреса Е3: Е12; известные_значения_х – адреса В3: D12; новые_значения_х – адреса B14: D16; константа – ввести число 1 (или слово ИСТИНА). Для ввода функции (как формулы массива) щелкните по формуле в строке формул и нажмите сочетание клавишей Ctrl+Shift+Enter. В выделенном массиве ячеек появятся прогнозируемые значения зависимой переменной. Для определения расчетных значений зависимой переменной, используем функцию ТЕНДЕНЦИЯ. Для этого выделим ряд свободных ячеек, аналогичный по величине ряду фактических значений зависимой величины (например, ячейки F3: F12). Вызвать функцию ТЕНДЕНЦИЯ, в текстовое поле известные_значения_у ввести адреса Е3: Е12; в поле известные_значения_х – адреса В3: D12, остальные поля можно оставить незаполненными. В результате ввода функции как формулы массива, в выделенных ячейках получим искомые расчетные значения функции. Найдем расчетные значения прогнозируемой величины прибыли в отдельности от каждой из независимых величин. Для этого в свободную ячейку введем функцию ПРЕДСКАЗ из категории Статистические. В появившемся диалоговом окне в текстовые поля ввести аргументы функции: значение независимой величины х, для которого предсказывается величина зависимой переменной – ввести адрес ячейки В14; известные_значения_у – адреса Е3: Е12; известные_значения_х – адреса В3: В12. После ввода функции в выделенной ячейке появится значение прогнозируемой величины. Аналогичные действия повторяются и для определения прогноза при рассмотрении зависимости функции от других независимых переменных, расположенных в столбцах С и D. Эти действия можно упростить, если использовать операцию копирования функции. Для этого при вводе функции ПРЕДСКАЗ (например, в ячейку G14), необходимо для значений зависимой величины указать абсолютные (неизменяемые при копировании) адреса, то есть при вводе адресов известные_значения_у, следует нажать клавишу F4. В результате адреса примут вид $Е$3: $Е$12. Скопируем функцию вправо на две ячейки, потянув за маркер заполнения. В ячейках H14 и I14 окажутся прогнозные значения от величины второго и третьего факторов. Найдем расчетные значения парных коэффициентов корреляции. В свободную ячейку введем функцию КОРРЕЛ из категории Статистические. В появившемся диалоговом окне в текстовые поля введем в качестве массива1 – адреса значений переменной х 1 (В3: В12), в качестве массива2 – адреса значений переменной х 2 (С3: С12). После ввода функции в выделенной ячейке появится значение коэффициента корреляции r 12. Аналогичные действия повторить для определения коэффициентов корреляции для всех сочетаний независимых и зависимой переменной и представить их в виде корреляционной матрицы:
Оставшиеся свободными ячейки заполнить значениями парных коэффициентов корреляции, рассчитанными с помощью функции ПИРСОН из категории Статистические. В соответствующую ячейку введите функцию и в качестве аргументов укажите адреса двух рядов данных, например для расчета коэффициента r 21 в качестве массива1 ввести адреса переменной х 2 (С3: С12), а в качестве массива2 – адреса переменной х 1 (В3: В12). Найдем расчетные значения параметров нелинейного уравнения регрессии и коэффициент детерминации. Выделим массив ячеек в пять строк и с количеством столбцов, соответствующим количеству искомых параметров уравнения регрессии (в нашем примере – четыре столбца). Вызвать функцию ЛГРФПРИБЛ из категории Статистические. В появившемся диалоговом окне в соответствующие текстовые поля ввести аргументы функции: известные_значения_у – адреса Е3: Е12, известные_знечения_х – адреса В3: D12, константа – ввести число 1 (или слово ИСТИНА), статистика – ввести число 1 (или слово ИСТИНА). Для ввода функции (как формулы массива) щелкните по формуле в строке формул и нажмите сочетание клавишей Ctrl+Shift+Enter. В выделенном массиве ячеек появятся параметры нелинейного уравнения регрессии и коэффициент детерминации. Расположение результатов расчета аналогичное функции ЛИНЕЙН. Найдем расчетные значения прогнозируемой величины прибыли для нелинейной зависимости. Выделить массив ячеек, соответствующий неизвестным значениям прибыли для продуктов 11, 12 и 13 – это ячейки Н14: Н16. Вызвать функцию РОСТ из категории Статистические. В появившемся диалоговом окне в соответствующие тестовые поля ввести аргументы функции: известные_значения_у – адреса Е3: Е12, известные_значения_х – адреса В3: D12, новые_значения_х – адреса В14: D16, константа – можно ввести число 1 (или слово ИСТИНА). Для ввода функции (как формулы массива) щелкните по формуле в строке формул и нажмите сочетание клавишей Ctrl+Shift+Enter. В выделенном массиве ячеек появятся прогнозируемые значения зависимой величины – прибыли.
Порядок выполнения работы
1.Получить у преподавателя данные для расчета. 2.Ввести исходные данные в таблицу Excel. 3.Провести на ЭВМ серию расчетов по определению прогнозных значений зависимой переменной с использованием модели множественной корреляционной линейной и нелинейной зависимости, определению парных коэффициентов корреляции. 4.Определить параметры нелинейного множественного уравнения регрессии, расчетные значения зависимой переменной, прогнозные значения и коэффициенты корреляции и детерминации. 5.Зафиксировать результаты расчетов в тетради. 6.Сделать выводы по результатам моделирования и записать в тетради.
Отчет по работе должен содержать
1.Название и цель работы. 2.Основные теоретические и методические положения. 3.Исходные данные для расчета. 4.Результаты расчета. 5.Выводы по результатам моделирования.
|
; 
; 
