Расчет параметров линейной модели при аппроксимации опытных данных с помощью функции Excel ЛИНЕЙН

Цель работы: изучение методики использования функции рабочего листа Excel ЛИНЕЙН для нахождения параметров линейного уравнения регрессии.

 

Исходные положения. При вызове Excel на экране появится окно электронной таблицы. В главном меню выбрать указателем мыши значок вызова справочных данных - [? ] или [Справка] и нажать левую кнопку мыши. В появившемся меню выбрать строку Вызов справки или Справка: Microsoft Excel. В диалоговом окне справочной системы выбрать вкладку Предметный указатель и произвести поиск нужной темы по ключевому слову. В текстовое поле ввести с клавиатуры: функция листа ЛИНЕЙН. Выбрать соответствующую строчку с помощью мыши и нажать кнопку Вывести. Другой способ – выбрать вкладку Содержание, затем раскрыть строку Справка по функциям, выбрать Статистические функции и среди этих функций найти функцию ЛИНЕЙН. На экране появится описание функции ЛИНЕЙН. Внимательно изучите и законспектируйте основные положения описания функции: назначение функции, математическую модель, синтаксис, порядок ввода, форму представления результатов и описание получаемых результатов решения.

Порядок выполнения работы. Получив у преподавателя исходные данные для расчета, введите их в поле электронной таблицы. Отформатируйте исходные данные с помощью меню Формат и команды Ячейки. Толщину линий рамки и цвет граф введенной таблицы исходных данных подобрать в соответствии со вкусом и чувством меры.

С помощью функции ЛИНЕЙН определить параметры уравнения регрессии и коэффициент детерминации для модели множественной регрессии и для всех однофакторных моделей в рамках рассматриваемой задачи:

у =b₀+b₁ х ₁+b₂ х ₂+…+b_n х _n;

у =b₀+b₁ х ₁; у =b₀+b₂ х ₂; у =b₀+b_n х _n.

Используя найденные параметры уравнения регрессии, определить расчетные значения зависимой переменной (у _расч) и прогнозное значение функции при известных значениях влияющих переменных и найденных параметрах уравнения регрессии.

Определить значение коэффициента корреляции (R). Проанализируйте найденные величины коэффициентов детерминации и корреляции.

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

 

	A	B	C	D	E
		ИЗДЕРЖКИ РЕАЛИЗАЦИИ
	Товар	Закупка	Транспорт	Склады	Прибыль
	Продукт 1	24782.60	8522.85	2346.19	15048.37
	Продукт 2	333.10	239.61	69.16	148.13
	Продукт 3	7674.20	4639.26	1886.79	2799.74
	Продукт 4	61.80	30.69	9.49	43.02
	Продукт 5	4308.00	1889.78	307.93	2594.29
	Продукт 6	1025.25	1025.25	399.32	1013.83
	Продукт 7	864.70	345.09	53.27	76.94
	Продукт 8	1363.60	765.14	210.69	540.57
	Продукт 9	95.40	37.65	14.55	49.41
	Продукт 10	7381.80	3797.41	811.70	2409.09
	Данные для расчета прибыли
	Продукт 11	3786.60	2980.75	363.59
	Продукт 12	3547.20	2024.51	291.42
	Продукт 13	201.70	122.70	27.15

Рис. 1. Функция ЛИНЕЙН из категории Статистические

 

Ввести в таблицу исходные данные. Вызвать меню Формат команда Ячейки, вкладка Границы (внешние и внутренние).

Выделите на свободном месте таблицы с помощью левой кнопки мыши массив ячеек в пять строк и с количеством столбцов, равным количеству столбцов исходных данных (количеству неизвестных величин в модели b₀, b₁, b₂, b₃), например, B18: E22. Вызвать функцию ЛИНЕЙН. Для этого следует нажать левой кнопкой мыши на кнопку [f_x]. Или из меню Вставка выбрать команду Функция. На экране появится диалоговое окно Мастера функций. Выберите в списке Категория – Статистические, а в списке Функция – ЛИНЕЙН. Щелкните по кнопке [ОК]. В появившемся диалоговом окне функции ЛИНЕЙН в соответствующие текстовые поля введите аргументы функции: известные_значения_у – адреса Е3: Е12; известные_значения_х – адреса В3: D12; константа (для получения свободного члена уравнения регрессии) – ввести число 1 или слово ИСТИНА; статистика (для получения коэффициента детерминации R ²) – ввести число 1 или слово ИСТИНА. Для ввода функции (как формулы массива) щелкните по формуле в строке формул (или нажмите кнопку F2) и нажмите сочетание клавишей Ctrl+Shift+Enter.

Рис. 2. Ввод аргументов функции ЛИНЕЙН

 

В выделенном массиве ячеек появляется следующая информация:

 

	B	C	D	E
	-1.55735	0.094195	0.717071	-305.109
	0.719314	0.080959	0.074878	319.9863
	0.981984	749.448	#Н/Д	#Н/Д
	109.01		#Н/Д	#Н/Д
	1.84E+08		#Н/Д	#Н/Д

 

Из таблицы следует: свободный член уравнения регрессии b ₀ = -305.109 коэффициенты уравнения b ₁=0.717071, b ₂=0.094195, b ₃= -1.55735. Коэффициент детерминации R ²=0.981984. Для расчета коэффициента корреляции выделить свободную ячейку, вызвать в категории Математические функцию с именем КОРЕНЬ. В качестве аргумента ввести адрес ячейки, содержащей значение R ². Получим R =0.99.

Для определения расчетных значений зависимой величины у _расч. в ячейку, расположенную рядом с первым значением у _факт. ввести формулу:

у _расч= b₀ + b_1* х ₁ + b_2* х ₂ + b_3* х _3.

Для того, чтобы не вводить похожие формулы и во все последующие ячейки, их можно скопировать. Поскольку при копировании формул адреса ячеек в относительной форме (например, Е3 или D15) автоматически изменяются на величину смещения по строкам и столбцам, а адреса ячеек в абсолютной форме (например, $D$15) остаются неизменными, используем это свойство. Введем в ячейку F3 знак =, щелкнем левой кнопкой мыши по ячейке E18 (это адрес свободного члена уравнения b₀) и нажмем на клавиатуре кнопку [F4] (для быстрой установки знаков $), введем с клавиатуры знак +, щелкнем по ячейке D18 и нажмем [F4] (это коэффициент b₁), введем знак _* (умножить), щелкнем по ячейке В3 (это значение х₁), введем знак +, щелкнем по ячейке C18 и нажмем [F4] (это коэффициент b₂), введем знак _* , щелкнем по ячейке С3 (значение х₂) и так далее. В результате в ячейке 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 ², совпадающее с тем, которое возвращает функция ЛИНЕЙН.

Для определения прогнозного значения функции для новых данных выделим ячейку с формулой (например, F12), подведем указатель мыши к границе рамки, нажмем клавишу Ctrl (рядом со стрелкой появится черный крестик) и не отпуская ее, при нажатой левой кнопке мыши перетащим копию формулы в ячейку F14. Отпустив клавиши, в ячейке F14 получим искомую прогнозную величину. Для завершения прогноза скопируйте формулу в ячейки F15 и F16.

Для получения параметров уравнения регрессии для однофакторных моделей, вызвать функцию ЛИНЕЙН и в строке известные_значения_х указать в первом случае адреса первого столбца данных (В3: В12), для другой модели вызвать снова функцию ЛИНЕЙН и выделить второй столбец (С3: С12), а для третьей модели – соответственно (D3: D12).

 

Порядок выполнения работы

1.Получить у преподавателя данные для расчета.

2.Ввести исходные данные в таблицу Excel.

3.Провести на ЭВМ серию расчетов по определению параметров множественной корреляционной зависимости и парных зависимостей для каждой из независимых переменных в отдельности.

4.Определить расчетные значения зависимой переменной, прогнозные значения и коэффициенты корреляции и детерминации.

5.Зафиксировать результаты расчетов в тетради.

6.Сделать выводы по результатам моделирования и записать в тетради.

Отчет по работе должен содержать

1.Название и цель работы.

2.Основные теоретические и методические положения.

3.Исходные данные для расчета.

4.Результаты расчета.

5.Выводы по результатам моделирования.