Функция линейн в экселе

Функция линейн в экселе

Функция ЛИНЕЙН() специально создана для оценки параметров линейной регрессии, а также для вывода регрессионной статистики (коэффициента детерминации, стандартных ошибок, F-статистики и др.).

Функция ЛИНЕЙН() может использоваться для простой регрессии (в этом случае прогнозируемая переменная Y зависит от одной контролируемой переменной Х) и для множественной регрессии (Y зависит от нескольких Х).

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

Функция ЛИНЕЙН() возвращает несколько значений, поэтому для вывода результатов потребуется несколько ячеек. Часто функцию вводят как формулу массива: нажатием клавиш CTRL+SHIFT+ENTER, но, как будет показано ниже, для вывода результатов вычислений это не обязательно.

Функция работает в 2-х режимах. В простейшем случае, когда 4-й аргумент функции опущен или установлен ЛОЖЬ, функция возвращает только 2 значения — это оценки параметров модели: наклона a и сдвига b.

Для того, чтобы вычислить оценки:

  • выделите 2 ячейки в одной строке,
  • в Строке формул введите, например, = ЛИНЕЙН(C23:C83;B23:B83)
  • нажмите CTRL+SHIFT+ENTER.

В левой ячейке будет рассчитано значение наклона, в правой – сдвига.

Примечание: В справке MS EXCEL результат функции ЛИНЕЙН() соответствующий наклону обозначается буквой m, а сдвиг – буквой b.

Примечание: Без формул массива можно обойтись. Для этого нужно использовать функцию ИНДЕКС() , которая выведет нужное значение. Например, чтобы вывести величину сдвига линии регрессии введите формулу = ИНДЕКС(ЛИНЕЙН(C23:C83;B23:B83);1;2) . Если 4-й аргумент функции опущен или установлен ЛОЖЬ, то функция ЛИНЕЙН() в возвращает массив значений вида 1х2 (т.е. 2 ячейки, расположенные в одной строке). Поэтому, для вывода величины сдвига прямой линии регрессии, первый аргумент функции ИНДЕКС() , который является номером строки, должен быть равен 1, а второй аргумент, номер столбца, должен быть равен 2. Чтобы вывести значение наклона линии регрессии формулу =ЛИНЕЙН(C23:C83;B23:B83) достаточно ввести просто как обычную формулу и нажать ENTER. Конечно, можно использовать и формулу =ИНДЕКС(ЛИНЕЙН(C23:C83;B23:B83);1;1) .

Теперь о втором, более сложном режиме функции. Этот режим нужно использовать, если требуется вывести дополнительную статистику (4-й аргумент функции должен быть установлен ИСТИНА). В этом случае функция ЛИНЕЙН() возвращает 10 значений в диапазоне 5х2 ячеек (5 строк и 2 столбца). Как и в более простом режиме, в первой строке возвращаются оценки параметров модели: наклона и сдвига.

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую. Функция ЛИНЕЙН может также возвращать дополнительную регрессионную статистику (подробнее см. справку MS Excel).

Рис. 22.1. Четыре аргумента функции ЛИНЕЙН

Скачать заметку в формате Word или pdf, примеры в формате Excel

Линейная регрессия

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

  1. Выделите диапазон D2:Е2. Функция ЛИНЕЙН возвращает массив из двух значений, расположенных по горизонтали, но не по вертикали.
  2. Введите известные значения y. Это – баллы, которые студенты заработали на последнем тестировании.
  3. Введите известные значения х. Это количество часов, которые студенты потратили на подготовку к тестам.
  4. Опустите аргумент [конст].
  5. Опустите аргумент [статистика].
  6. Введите формулу с помощью Ctrl+Shift+Enter.

Рис. 22.2. Функция ЛИНЕЙН возвращает наклон и отрезок, если массив расположен в горизонтальном диапазоне

Рис. 22.3. Функция массива ЛИНЕЙН заменяет две отдельные функции – НАКЛОН и ОТРЕЗОК

Если вам всё же нужно вывести результаты функции ЛИНЕЙН в вертикальный массив, воспользуйтесь ухищрением (рис. 22.4).

Читайте также:  Как настроить каналы на телевизоре самсунг видео

Рис. 22.4. Формулы для вывода результатов в вертикальный массив

Если вы хотите отобразить не только наклон и отрезок, но и дополнительные статистики, выделите диапазон на один столбец больше, чем столбцов с переменными х, и высотой 5 строк. Как показано на рис. 22.5, поскольку у вас лишь одна переменная х, выделите диапазон Е2:F6 (2 столбца по 5 строк). Третьему и четвертому аргументам присвойте значения ИСТИНА: вы хотите, чтобы b считалось обычным образом, и хотите вывести дополнительные статистики. После ввода формулы нажатием Ctrl+Shift+Enter, результат должен соответствовать рис. 22.6 (подробнее о десяти статистиках см. Простая линейная регрессия).

Рис. 22.5. Когда требуется дополнительная статистика для одной переменной, выделите диапазон 2*5; функция ЛИНЕЙН вернет 10 значений; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Рис. 22.6. Функция ЛИНЕЙН возвращает 10 статистик

В главе 8 было показано, как с помощью формулы преобразовать таблицу в столбец. На рис. 22.7 приведена формула, позволяющая представить результаты работы функции ЛИНЕЙН (которые она возвращает в диапазон 2*5) в вертикальном столбце.

Следующие элементы являются аргументами функции ИНДЕКС:

  • аргумент массив: функция ЛИНЕЙН($B$2:$B$12;$A$2:$A$12;ИСТИНА;ИСТИНА) возвращает диапазон из пяти строк и двух столбцов.
  • аргумент номер_строки: ОСТАТ(ЧСТРОК(E$1:E1)-1;5)+1 возвращает следующие значения 1,2,3,4,5,1,2,3,4,5 при копировании формулы вдоль столбца от Е1 до Е10.
  • аргумент номер_столбца: ЦЕЛОЕ((ЧСТРОК(E$1:E1)-1)/5)+1 возвращает 1,1,1,1,1,2,2,2,2,2 при копировании формулы вдоль столбца от Е1 до Е10.

Рис. 22.7. Преобразование диапазона вывода формулы ЛИНЕЙН из 2*5 в вертикальный

Формула в Е1 не требует ввода с помощью Ctrl+Shift+Enter.

Множественная регрессия

В случае множественной регресии, когда значения y зависят от двух переменных х1 и х2, функция ЛИНЕЙН возвращает 12 статистик (подробнее см. Введение в множественную регрессию и Построение модели множественной регрессии). На рис. 22.8 используются следующие обозначения:

  • y = зависимая переменная
  • x1 = независимая переменная 1 = баллы за домашнее задание
  • x2 = независимая переменная 2 = часов изучал последний столбец тест = гр.

Чтобы выполнить множественную регрессию:

  • Выделите диапазон В3:D7 (число столобцов = число переменных +1; число строк всегда равно 5).
  • Наберите формулу <=ЛИНЕЙН(D13:D23;B13:C23;ИСТИНА;ИСТИНА)>. Для аргумента известные_значения_х, выделите оба столбца значений x из диапазона В13:С23.
  • Введите функцию с помощью клавиш Ctrl+Shift+Enter.
  • Обратите внимание, что несмотря на то, что значения х1 указаны в диапазоне В13:С23 до значений х2, наклон сначала указан для х2.

Рис. 22.8. Для двух переменных x1 и х2 функция ЛИНЕЙН выполняет множественную регрессию

Если вас раздражают знаяения ошибки #Н/Д дополните вашу формулу функцией ЕСЛИОШИБКА (рис. 22.9).

Рис. 22.9. Вы можете избавиться от ошибок #Н/Д «обернув» ЛИНЕЙН функцией ЕСЛИОШИБКА

Пример с тремя переменными не должен вызвать затруднений (рис. 22.10).

Рис. 22.10. Множественная регрессия для трех независимых переменных

Комментарии: 2 комментария

Добрый день!
У меня следующая ситуация: значения двух независимых переменных x1 и x2 содержаться на разных листах. Перенести их на один лист не получается, потому что наборов данных несколько сотен и делать для каждого набора отдельную вкладку — не вариант. Можно ли как-то обойти требование что x1 и x2 должны содержаться в едином диапазоне?

ЛИНЕЙН – это статистическая функция Microsoft Excel, позволяющая определять параметры уравнения линейной регрессии . Технология решения задачи следующая.

1.Введите исходные данные в соответствии с рис. 63.

2. Рассчитайте параметры уравнения линейной регрессии .

2.1. Выделите ячейки В24:С28 (5 строк и 2 столбца).

2.2. Щелкните левой кнопкой мыши на панели инструментов на кнопке или выполните командуВставка,fx Функция, щелкнув поочередно левой кнопкой мыши.

2.3. В диалоговом окне Мастер функций — шаг 1 из 2 с помощью левой кнопки мыши установите: Категория ® , Выберете функцию ® (рис. 73).

2.4. Щелкните левой кнопкой мыши на кнопке .

2.5. На вкладке ЛИНЕЙН установите параметры в соответствии с рис. 74.

Читайте также:  Phpexcel выравнивание в ячейке

2.6. Щелкните левой кнопкой мыши на кнопке .

2.7. Нажмите на клавишу , а затем на комбинацию клавиш + + .

Результаты решения выводятся на экран дисплея в следующем виде (рис. 75).

В табл. 15 приведено название показателей, значение которых дано в ячейках Е2:F6 на рис. 75.

Т а б л и ц а 15

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

Столбец Строка Е F
Значение коэффициента а1 Значение коэффициента а
Среднеквадратическое отклонение а1 Среднеквадратическое отклонение а
Коэффициент детерминации r 2 Среднеквадратическое отклонение у
F-критерий Число степеней свободы вариации
Регрессионная сумма квадратов Остаточная сумма квадратов

В Microsoft Excel также используется статистическая функция ЛГРФПРИБЛ для вычисления параметров уравнения экспоненциальной регрессии . Технология решения аналогична применению функции ЛИНЕЙН.

Решение с помощью надстройки Анализ данных

Анализ данных – это надстройка Microsoft Excel, предназначенная для статистических расчетов. Технология решения следующая.

1.Введите исходные данные в соответствии с рис. 63.

2. Рассчитайте параметры уравнения линейной регрессии .

2.1. Выполните командуСервис,Анализ данных, щелкнув поочередно левой кнопкой мыши.

2.2. В диалоговом окне Анализ данных с помощью левой кнопки мыши установите: Инструменты анализа ® (рис. 76).

2.3. Щелкните левой кнопкой мыши на кнопке .

2.4. На вкладке Регрессия установите параметры в соответствии с рис. 77.

2.5. Щелкните левой кнопкой мыши на кнопке .

Результаты решения выводятся на экран дисплея в следующем виде (рис. 78).

Пояснения к названию отдельных показателей на рис. 78 приведены в табл. 16.

Т а б л и ц а 16

Название показателей, выводимых с помощью надстройки

Анализ данных

Наименование в Microsoft Excel Принятые наименования
Множественный R Коэффициент (индекс) множественной корреляции (в рассмотренном примере парной корреляции)
R-квадрат Коэффициент (индекс) детерминации
df Число степеней свободы вариации
SS Сумма квадратов
MS Дисперсия
F F-критерий
t-статистика t-критерий

III. Задание к расчетно-графической работе по математической статистике на тему «Статистический анализ вариационных рядов распределения (на примере настрига шерсти овец и длины волоса шерсти)»

По 25 овцам имеются данные о настриге шерсти и длины волоса шерсти (табл. 17 и 18).

Т а б л и ц а 17

Настриг шерсти, кг (y)

Номер овцы Предпоследняя цифра номера зачетной книжки
3,9 4,2 4,0 4,1 4,2 4,2 4,1 4,1 4,2 4,2
5,0 4,9 5,2 5,1 5,1 5,1 4,9 5,0 5,1 4,8
4,6 4,8 4,5 4,7 4,8 4,6 4,8 4,8 4,7 4,9
4,9 4,8 4,8 4,8 4,7 4,6 4,6 4,6 4,8 4,7
5,2 5,4 5,1 5,3 5,4 5,1 5,4 5,3 5,2 5,3
4,8 4,6 4,8 4,8 4,7 4,5 4,8 4,7 4,5 4,7
4,5 4,6 4,7 4,8 4,5 4,7 4,5 4,6 4,8 4,6
4,2 4,5 4,1 4,1 4,4 4,4 4,2 4,2 4,3 4,5
5,1 5,2 5,2 5,2 5,2 5,4 5,3 5,4 5,4 5,2
5,4 5,3 5,1 5,3 5,5 5,1 5,4 5,3 5,2 5,4
4,9 4,7 4,7 4,6 4,8 4,6 4,7 4,8 4,9 4,6
4,6 4,8 4,8 4,8 4,5 4,6 4,8 4,9 4,5 4,8
4,6 4,7 4,9 4,8 4,7 4,8 4,7 4,9 4,9 4,7
4,5 4,5 4,3 4,1 4,2 4,1 4,5 4,1 4,2 4,5
4,8 4,8 4,8 4,9 4,6 4,6 4,6 4,7 4,9 4,8
4,9 5,1 5,0 5,0 4,8 4,9 4,9 5,0 4,9 4,8
5,0 4,9 4,9 5,0 4,9 5,0 4,8 5,0 4,8 5,0
4,4 4,4 4,2 4,6 4,6 4,4 4,5 4,4 4,4 4,2
4,8 5,2 4,9 5,1 5,1 5,2 5,1 5,0 5,1 5,2
3,9 4,2 4,0 4,1 3,9 3,9 4,0 4,1 4,0 3,9
5,1 5,1 4,9 5,0 5,1 4,9 5,1 5,0 5,0 5,2
4,4 4,5 4,4 4,2 4,5 4,3 4,1 4,5 4,2 4,4
4,5 4,6 4,6 4,8 4,9 4,6 4,7 4,8 4,8 4,7
4,5 4,3 4,3 4,5 4,3 4,5 4,6 4,4 4,1 4,4
4,1 4,2 4,6 4,3 4,2 4,1 4,5 4,3 4,3 4,5
Читайте также:  Авито рязань вход в личный кабинет

Т а б л и ц а 18

Длина волоса шерсти, см (x)

Номер овцы Последняя цифра номера зачетной книжки
11,3 11,9 12,1 12,1 11,9 13,4 12,9 13,4 13,7 12,4
20,8 21,0 21,2 20,6 20,4 22,1 22,4 19,8 21,3 22,3
17,9 18,9 17,6 18,2 17,1 18,8 16,9 17,0 18,8 19,5
18,7 17,7 18,5 18,4 18,3 18,9 17,0 17,4 17,5 16,8
22,5 22,9 23,0 24,7 22,6 24,5 23,5 24,8 23,4 24,2
19,0 17,2 17,9 19,2 19,1 17,6 17,3 18,8 18,1 17,4
17,1 19,2 18,0 16,8 16,8 18,8 19,1 17,6 18,3 17,3
14,0 15,6 16,4 14,0 15,5 16,4 15,8 15,8 15,0 14,5
21,8 20,2 20,4 19,6 20,9 20,0 22,0 22,1 20,8 21,3
22,8 24,7 22,9 22,7 24,4 24,6 22,8 23,9 24,4 22,7
18,7 19,6 19,2 16,9 18,7 17,7 16,9 18,9 19,0 16,8
19,8 22,1 20,1 19,8 22,0 19,5 19,5 22,3 19,8 20,6
17,5 18,8 18,6 17,3 18,0 18,3 16,7 17,4 18,2 17,4
14,5 16,5 16,1 14,1 15,0 15,7 15,6 14,1 14,9 14,9
18,5 18,1 17,7 17,0 18,7 18,6 18,1 19,2 17,3 16,9
21,7 21,4 21,5 21,6 20,3 22,3 22,0 22,2 22,2 22,5
19,7 22,3 21,2 22,5 20,6 19,6 20,7 21,4 22,2 20,8
16,7 19,2 16,7 19,6 19,0 17,6 16,7 17,5 17,7 18,0
22,2 20,9 22,2 21,4 21,3 19,8 21,3 19,9 21,9 20,0
11,8 13,8 11,5 12,8 13,2 11,5 13,4 12,7 12,1 13,0
20,4 20,5 20,2 20,8 22,3 20,0 20,5 20,9 21,5 21,3
14,1 16,5 15,4 15,9 15,0 15,1 15,4 14,2 15,5 15,4
19,5 19,9 20,9 20,4 22,3 21,1 22,4 20,6 20,7 21,2
15,3 15,7 16,1 16,0 16,0 15,4 16,8 14,2 16,7 14,4
13,3 12,4 11,4 12,6 13,9 11,5 13,7 13,0 11,9 13,8

С помощью втабличного процессора Microsoft Excel,выполните следующие задания.

1. Постройте интервальные ряды распределения настрига и длины волоса шерсти, отобразите их графически в виде гистограмм, полигонов и кумулят. Для этого используйте надстройку Анализ данных, Мастер функций и Мастер диаграмм.

2. Для анализа рядов распределения рассчитайте средние величины (среднюю арифметическую, моду, медиану), выборочные показатели вариации (дисперсию, среднее квадратическое отклонение, коэффициент вариации) и показатели распределения (коэффициенты асимметрии и эксцесса). Для этого используйте Мастер функций и инструмент Описательная статистика надстройки Анализ данных.

3. С помощью выборочного метода рассчитайте для каждого ряда распределения предельные ошибки выборочной средней, найдите доверительные пределы генеральной средней при уровне вероятности суждения 0,95

4. Проверьте гипотезу соответствия рядов распределения настрига и длины волоса шерсти нормальному закону распределения. Для этого используйте Мастер функций.

5. Используя данные интервального ряда распределения настрига шерсти с помощью дисперсионного анализа рассчитайте достоверность разницы в настриге шерстив зависимости от длины волоса шерсти. Для этого используйте надстройку Анализ данных.

6. С помощью корреляционного анализа определите влияние длины волоса шерсти на настриг шерсти. Для этого постройте линейное уравнение регрессии, рассчитайте коэффициент корреляции и оцените его достоверность с помощью t-критерия Стьюдента и F-критерия Фишера. Для этого используйте надстройку Анализ данных.

По каждому заданию сделайте соответствующие выводы.

План работы

1. Вариационные ряды распределения. Графическое представление данных.

2. Статистические оценки параметров распределения.

3. Интервальные оценки. Доверительные интервалы. Ошибки выбороч- ной средней.

4. Статистические гипотезы

5. Дисперсионный анализ

6. Корреляционный анализ

Образец оформления

Первичные данные по настригу (кг) и длине волоса шерсти (см) овец

Последнее изменение этой страницы: 2016-04-21; Нарушение авторского права страницы

Ссылка на основную публикацию
Фотоаппарат сони dsc h50
Название объектива : Carl Zeiss Vario-Tessar Количество групп оптических элементов : 8 Количество оптических элементов : 13 Цифровой Zoom :...
Файл cms что это
Файлы формата CMS открываются специальными программами. Существует 2 типа форматов CMS, каждый из которых открывается разными программами. Чтобы открыть нужный...
Файл менеджер для windows 10 на русском
Менеджер файлов осуществляет просмотр, копирование, управление медиафайлами и папками на персональном компьютере. Он предоставляет функцию быстрого перемещения объектов для ускорения...
Фотоаппараты до 10000 рублей рейтинг
На российском рынке представлено настолько много фотоаппаратов и камер, что найдется модель на любой вкус. В том числе есть действительно...
Adblock detector