Расчет параметров линейной модели при аппроксимации опытных данных с помощью функции Excel ЛИНЕЙНЦель работы: изучение методики использования функции рабочего листа Excel ЛИНЕЙН для нахождения параметров линейного уравнения регрессии.
Исходные положения. При вызове Excel на экране появится окно электронной таблицы. В главном меню выбрать указателем мыши значок вызова справочных данных - [? ] или [Справка] и нажать левую кнопку мыши. В появившемся меню выбрать строку Вызов справки или Справка: Microsoft Excel. В диалоговом окне справочной системы выбрать вкладку Предметный указатель и произвести поиск нужной темы по ключевому слову. В текстовое поле ввести с клавиатуры: функция листа ЛИНЕЙН. Выбрать соответствующую строчку с помощью мыши и нажать кнопку Вывести. Другой способ – выбрать вкладку Содержание, затем раскрыть строку Справка по функциям, выбрать Статистические функции и среди этих функций найти функцию ЛИНЕЙН. На экране появится описание функции ЛИНЕЙН. Внимательно изучите и законспектируйте основные положения описания функции: назначение функции, математическую модель, синтаксис, порядок ввода, форму представления результатов и описание получаемых результатов решения. Порядок выполнения работы. Получив у преподавателя исходные данные для расчета, введите их в поле электронной таблицы. Отформатируйте исходные данные с помощью меню Формат и команды Ячейки. Толщину линий рамки и цвет граф введенной таблицы исходных данных подобрать в соответствии со вкусом и чувством меры. С помощью функции ЛИНЕЙН определить параметры уравнения регрессии и коэффициент детерминации для модели множественной регрессии и для всех однофакторных моделей в рамках рассматриваемой задачи: у =b0+b1 х 1+b2 х 2+…+bn х n; у =b0+b1 х 1; у =b0+b2 х 2; у =b0+bn х n. Используя найденные параметры уравнения регрессии, определить расчетные значения зависимой переменной (у расч) и прогнозное значение функции при известных значениях влияющих переменных и найденных параметрах уравнения регрессии. Определить значение коэффициента корреляции (R). Проанализируйте найденные величины коэффициентов детерминации и корреляции. Пример выполнения работы. Консалтинговая фирма провела анализ влияния издержек реализации, включающих закупку, транспортировку, складирование на величину прибыли, получаемой фирмами, реализующими свой товар. Специалисты-консультанты, используя полученные данные как средне-нормативные, смогли определить ориентировочный размер прибыли для новой фирмы, появившейся на рынке:
Рис. 1. Функция ЛИНЕЙН из категории Статистические
Ввести в таблицу исходные данные. Вызвать меню Формат команда Ячейки, вкладка Границы (внешние и внутренние). Выделите на свободном месте таблицы с помощью левой кнопки мыши массив ячеек в пять строк и с количеством столбцов, равным количеству столбцов исходных данных (количеству неизвестных величин в модели b0, b1, b2, b3), например, B18: E22. Вызвать функцию ЛИНЕЙН. Для этого следует нажать левой кнопкой мыши на кнопку [fx]. Или из меню Вставка выбрать команду Функция. На экране появится диалоговое окно Мастера функций. Выберите в списке Категория – Статистические, а в списке Функция – ЛИНЕЙН. Щелкните по кнопке [ОК]. В появившемся диалоговом окне функции ЛИНЕЙН в соответствующие текстовые поля введите аргументы функции: известные_значения_у – адреса Е3: Е12; известные_значения_х – адреса В3: D12; константа (для получения свободного члена уравнения регрессии) – ввести число 1 или слово ИСТИНА; статистика (для получения коэффициента детерминации R 2) – ввести число 1 или слово ИСТИНА. Для ввода функции (как формулы массива) щелкните по формуле в строке формул (или нажмите кнопку F2) и нажмите сочетание клавишей Ctrl+Shift+Enter.
Рис. 2. Ввод аргументов функции ЛИНЕЙН
В выделенном массиве ячеек появляется следующая информация:
Из таблицы следует: свободный член уравнения регрессии b 0 = -305.109 коэффициенты уравнения b 1=0.717071, b 2=0.094195, b 3= -1.55735. Коэффициент детерминации R 2=0.981984. Для расчета коэффициента корреляции выделить свободную ячейку, вызвать в категории Математические функцию с именем КОРЕНЬ. В качестве аргумента ввести адрес ячейки, содержащей значение R 2. Получим R =0.99. Для определения расчетных значений зависимой величины у расч. в ячейку, расположенную рядом с первым значением у факт. ввести формулу: у расч= b0 + b1* х 1 + b2* х 2 + b3* х 3. Для того, чтобы не вводить похожие формулы и во все последующие ячейки, их можно скопировать. Поскольку при копировании формул адреса ячеек в относительной форме (например, Е3 или D15) автоматически изменяются на величину смещения по строкам и столбцам, а адреса ячеек в абсолютной форме (например, $D$15) остаются неизменными, используем это свойство. Введем в ячейку F3 знак =, щелкнем левой кнопкой мыши по ячейке E18 (это адрес свободного члена уравнения b0) и нажмем на клавиатуре кнопку [F4] (для быстрой установки знаков $), введем с клавиатуры знак +, щелкнем по ячейке D18 и нажмем [F4] (это коэффициент b1), введем знак * (умножить), щелкнем по ячейке В3 (это значение х1), введем знак +, щелкнем по ячейке C18 и нажмем [F4] (это коэффициент b2), введем знак * , щелкнем по ячейке С3 (значение х2) и так далее. В результате в ячейке F3 получим формулу: =$E$18+$D$18*B3+$C$18*C3+$B$18*D3. Для ввода формулы нажать клавишу Enter или мышью кнопку [V] в строке формул. Для копирования выделенной формулы установить курсор мыши на маркер заполнения – небольшой черный квадрат в нижнем правом углу выделенной ячейки или диапазона. Попав на маркер заполнения, указатель примет вид черного креста. Чтобы скопировать содержимое выделенного диапазона в соседние ячейки, нажмите левую кнопку мыши и перемещайте мышь в нужном направлении (до ячейки F12). Во всех ячейках диапазона F3: F12 появятся расчетные значения для зависимой величины (у расч.). Если выделить ячейку F12 в строке формул можно увидеть формулу: =$E$18+$D$18*B12+$C$18*C12+$B$18*D12. Как видно, при копировании относительные адреса изменились, а абсолютные остались неизменными. Определим коэффициент детерминации с помощью функции КВПИРСОН. В разделе Статистические выбрать функцию КВПИРСОН и в качестве аргумента известные_значения_у ввести адреса фактических значений зависимой переменной (Е3: Е12), а для аргумента известные_значения_х ввести адреса расчетных значений зависимой переменной (F3: F12). В выделенной ячейке после нажатия кнопки [ОК] появится значение R 2, совпадающее с тем, которое возвращает функция ЛИНЕЙН. Для определения прогнозного значения функции для новых данных выделим ячейку с формулой (например, F12), подведем указатель мыши к границе рамки, нажмем клавишу Ctrl (рядом со стрелкой появится черный крестик) и не отпуская ее, при нажатой левой кнопке мыши перетащим копию формулы в ячейку F14. Отпустив клавиши, в ячейке F14 получим искомую прогнозную величину. Для завершения прогноза скопируйте формулу в ячейки F15 и F16. Для получения параметров уравнения регрессии для однофакторных моделей, вызвать функцию ЛИНЕЙН и в строке известные_значения_х указать в первом случае адреса первого столбца данных (В3: В12), для другой модели вызвать снова функцию ЛИНЕЙН и выделить второй столбец (С3: С12), а для третьей модели – соответственно (D3: D12).
Порядок выполнения работы 1.Получить у преподавателя данные для расчета. 2.Ввести исходные данные в таблицу Excel. 3.Провести на ЭВМ серию расчетов по определению параметров множественной корреляционной зависимости и парных зависимостей для каждой из независимых переменных в отдельности. 4.Определить расчетные значения зависимой переменной, прогнозные значения и коэффициенты корреляции и детерминации. 5.Зафиксировать результаты расчетов в тетради. 6.Сделать выводы по результатам моделирования и записать в тетради. Отчет по работе должен содержать 1.Название и цель работы. 2.Основные теоретические и методические положения. 3.Исходные данные для расчета. 4.Результаты расчета. 5.Выводы по результатам моделирования.
|
