Функция линейн в excel как пользоваться пример • тренд как модель

Как решать задачи на линейную функцию « y = kx + b »

Построить график функции « y = 2x + 3 ». Найти по графику:

Используем правила, по которым мы строили график функции выше. Для построения графика функции « y = 2x + 3 » достаточно найти всего две точки.

Выберем два произвольных числовых значения для « x ». Для удобства расчетов выберем числа « 0 » и « 1 ».

Выполним расчеты и запишем их результаты в таблицу.

Точка	Координата по оси « Оx »	Координата по оси « Оy »
(·)A	y(0) = 2 · 0 + 3 = 3
(·)B	1	y(1) = 2 ·1 + 3 = 5

Отметим полученные точки на прямоугольной системе координат.

Соединим полученные точки прямой. Проведенная прямая будет являться графиком функции « y = 2x + 3 ».

Теперь работаем с построенным графиком функции « y = 2x + 3 ».

Требуется найти значение « y », соответствующее значению « x », которое равно −1; 2; 3; 5 .

Тему «Как получить координаты точки функции» с графика функции мы уже подробно рассматривали в уроке «Как решать задачи на функцию».

В этому уроке для решения задачи выше вспомним только основные моменты.

Запомните!

Чтобы найти значение « y » по известному значению « x » на графике функции необходимо:

1. провести перпендикуляр от оси « Ox » (ось абсцисс) из заданного числового значения « x » до пересечения с графиком функции;
2. из полученной точки пересечения перпендикуляра и графика функции провести еще один перпендикуляр к оси « Oy » (ось ординат);
3. полученное числовое значение на оси « Oy » и будет искомым значением.

По правилам выше найдем на построенном ранее графике функции « y = 2x + 3 » необходимые значения функции « y » для « x » равным −1; 2; 3; 5 .

Заданное значение « x »	Полученное с графика значение « y »
−1	1
2	7
3	9
5	13

Переходим ко второму заданию задачи. Требуется найти значение « x », если значение « y » равно 1; 4; 0; −1 .

Выполним те же действия, что и при решении предыдущего задания. Разница будет лишь в том, что изначально мы будем проводить перпендикуляры от оси « Oy » .

Заданное значение « y »	Полученное с графика значение « x »
−1	−2
−1,5
1	−1
4	0,5

Как проверить, проходит ли график через точку

Не выполняя построения графика функции « y = 2x −

1

3

», выяснить, проходит ли график через точки с координатами (0; −

1

3

) и (1; −2) .

Запомните!

Чтобы проверить принадлежность точки графику функции нет необходимости строить график функции.

Достаточно подставить координаты точки в формулу функции (координату по оси « Ox » вместо « x », а координату по оси « Oy » вместо « y ») и выполнить арифметические расчеты.

Как найти точки пересечения графика с осями

Найти координаты точек пересечения графика функции « y = −1,5x + 3 » с осями координат.

Для начала построим график функции « y = −1,5x + 3 » и на графике отметим точки пересечения с осями.

Выберем два произвольных числовых значения для « x » и рассчитаем значение « y » по формуле функции. Например, для x = 0 и x = 1 .

Точка	Координата по оси « Оx »	Координата по оси « Оy »
(·)A	y(0) = −1,5 · 0 + 3 = 3
(·)B	1	y(1) = −1,5 · 1 + 3 = 1,5

Отметим полученные точки на системе координат и проведем через них прямую. Тем самым мы построим график функции « y = −1,5x + 3 ».

Теперь найдем координаты точек пересечения графика функции с осями по формуле функции.

Запомните!

Чтобы найти координаты точки пересечения графика функции с осью « Oy » (осью ординат) нужно:

• приравнять координату точки по оси « Ox » к нулю (x = 0) ;
• подставить вместо « x » в формулу функции ноль и найти значение « y »;
• записать полученные координаты точки пересечения с осью « Oy » .

Подставим вместо « x » в формулу функции « y = −1,5x + 3 » число ноль.

Запомните!

Чтобы найти координаты точки пересечения графика функции с осью « Ox » (осью абсцисс) нужно:

• приравнять координату точки по оси « Oy » к нулю (y = 0) ;
• подставить вместо « y » в формулу функции ноль и найти значение « x »;
• записать полученные координаты точки пересечения с осью « Oy » .

Подставим вместо « y » в формулу функции « y = −1,5x + 3 » число ноль.

Чтобы было проще запомнить, какую координату точки нужно приравнивать к нулю, запомните «правило противоположности».

Если нужно найти координаты точки пересечения графика с осью « Ox » , то приравниваем « y » к нулю.

И наооборот. Если нужно найти координаты точки пересечениа графика с осью « Oy » , то приравниваем « x » к нулю.

Функция EXCEL ЛИНЕЙН()

history 26 января 2019 г.

Функция ЛИНЕЙН() специально создана для оценки параметров линейной регрессии, а также для вывода регрессионной статистики (коэффициента детерминации, стандартных ошибок, 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 столбца). Как и в более простом режиме, в первой строке возвращаются оценки параметров модели: наклона и сдвига .

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

• выделите диапазон 5х2 ячеек (2 столбца и 5 строк),
• в Строке формул введите формулу ЛИНЕЙН($C$23:$C$83;$B$23:$B$83;;ИСТИНА)
• чтобы ввести формулу нажмите одновременно комбинацию клавиш CTRL + SHIFT + ENTER

Примечание : Чтобы обойтись без формул массива нужно использовать функцию ИНДЕКС() , которая выведет нужное значение. Например, чтобы вывести коэффициент детерминации R 2 введите формулу = ИНДЕКС(ЛИНЕЙН(C23:C83;B23:B83;;ИСТИНА);3;1) . 3 – это номер строки диапазона 5х2, а 1 – это номер столбца. В файле примера на листе Линейный в диапазоне Q 26: R 30 показано как вывести все значения, возвращаемые функцией ЛИНЕЙН() без формул массива .

Итак, установив 4-й аргумент равным ИСТИНА и введя функцию тем или иным способом, функция выведет:

• в строке 1: оценки параметров модели (наклон и сдвиг).
• в строке 2: Стандартные ошибки для наклона и сдвига . Ошибки обозначаются se и seb;
• в строке 3: коэффициент детерминации и стандартную ошибку регрессии . Обозначаются R 2 и SEy;
• в строке 4: значение F-статистики и число степеней свободы . Обозначаются F и df;
• в строке 5: Суммы квадратов SSR, SSE определяющие изменчивость объясненную и необъясненную моделью (см. в статье Простая линейная регрессия разделы про коэффициент детерминации и статью про F-тест ). В справке MS EXCEL SSR, SSE обозначаются как ssreg (Regression Sum of Squares) и ssresid (Residuals Sum of Squares) соответственно.

Примечание : Разобраться в значениях, возвращаемых функцией ЛИНЕЙН() , можно лишь разобравшись в теории линейной регрессии.

В файле примера также приведены формулы, позволяющие сделать расчеты без функции ЛИНЕЙН() – см. диапазон Q 34: R 38 . Альтернативные формулы помогают разобраться в алгоритме расчета вышеуказанных статистических показателей.

Допущение 4: гомоскедастичность

Множественная линейная регрессия предполагает, что остатки имеют постоянную дисперсию в каждой точке линейной модели. Когда это не так, говорят, что остатки страдают от гетероскедастичности .

Когда в регрессионном анализе присутствует гетероскедастичность, результаты регрессионной модели становятся ненадежными.

В частности, гетероскедастичность увеличивает дисперсию оценок коэффициента регрессии, но регрессионная модель этого не учитывает. Это повышает вероятность того, что регрессионная модель объявит термин в модели статистически значимым, хотя на самом деле это не так.

Как определить, выполняется ли это предположение

Самый простой способ определить, выполняется ли это предположение, — построить график стандартизированных остатков по сравнению с прогнозируемыми значениями.

После подбора регрессионной модели к набору данных вы можете создать точечную диаграмму, которая показывает прогнозируемые значения для переменной отклика на оси x и стандартизированные невязки модели на оси y.

Если точки на диаграмме рассеивания имеют закономерность, то присутствует гетероскедастичность.

На следующем графике показан пример регрессионной модели, в которой гетероскедастичность не является проблемой:

Обратите внимание, что стандартизированные остатки разбросаны около нуля без четкой закономерности. На следующем графике показан пример регрессионной модели, в которой гетероскедастичность является проблемой:

На следующем графике показан пример регрессионной модели, в которой гетероскедастичность является проблемой:

Обратите внимание, как стандартизированные остатки становятся более разбросанными по мере того, как прогнозируемые значения становятся больше. Эта форма «конуса» — классический признак гетероскедастичности:

Что делать, если это предположение нарушается

Существует три распространенных способа исправить гетероскедастичность:

1. Преобразуйте переменную ответа. Самый распространенный способ справиться с гетероскедастичностью — преобразовать переменную отклика, взяв логарифмический, квадратный или кубический корень из всех значений переменной отклика. Это часто приводит к исчезновению гетероскедастичности.

2. Переопределите переменную ответа. Один из способов переопределить переменную ответа — использовать скорость , а не необработанное значение. Например, вместо использования численности населения для прогнозирования количества цветочных магазинов в городе мы можем вместо этого использовать численность населения для прогнозирования количества цветочных магазинов на душу населения.

В большинстве случаев это снижает изменчивость, которая естественным образом возникает среди больших групп населения, поскольку мы измеряем количество цветочных магазинов на человека, а не простое количество цветочных магазинов.

3. Используйте взвешенную регрессию. Другой способ исправить гетероскедастичность — использовать взвешенную регрессию, которая присваивает вес каждой точке данных на основе дисперсии ее подобранного значения.

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

Связанный : Как выполнить взвешенную регрессию в R

Допущение 3: Независимость

Множественная линейная регрессия предполагает, что каждое наблюдение в наборе данных является независимым.

Как определить, выполняется ли это предположение

Самый простой способ определить, выполняется ли это предположение, — выполнить тест Дарбина-Ватсона , формальный статистический тест, который сообщает нам, демонстрируют ли остатки (и, следовательно, наблюдения) автокорреляцию.

Что делать, если это предположение нарушается

В зависимости от характера нарушения этого предположения у вас есть несколько вариантов:

• Для положительной последовательной корреляции рассмотрите возможность добавления в модель лагов зависимой и/или независимой переменной.
• Для отрицательной последовательной корреляции убедитесь, что ни одна из ваших переменных не является сверхдифференциальной .
• Для сезонной корреляции рассмотрите возможность добавления в модель сезонных фиктивных переменных .

Предположение 2: отсутствие мультиколлинеарности

Множественная линейная регрессия предполагает, что ни одна из переменных-предикторов не сильно коррелирует друг с другом.

Когда одна или несколько переменных-предикторов сильно коррелированы, регрессионная модель страдает от мультиколлинеарности , из-за чего оценки коэффициентов в модели становятся ненадежными.

Как определить, выполняется ли это предположение

Самый простой способ определить, выполняется ли это предположение, — вычислить значение VIF для каждой переменной-предиктора.

Значения VIF начинаются с 1 и не имеют верхнего предела. Как правило, значения VIF выше 5* указывают на потенциальную мультиколлинеарность.

В следующих руководствах показано, как рассчитать VIF в различных статистических программах:

• Как рассчитать VIF в R
• Как рассчитать VIF в Python
• Как рассчитать VIF в Excel

* Иногда вместо этого исследователи используют значение VIF, равное 10, в зависимости от области исследования.

Что делать, если это предположение нарушается

Если одна или несколько переменных-предикторов имеют значение VIF больше 5, самый простой способ решить эту проблему — просто удалить переменные-предикторы с высокими значениями VIF.

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

Прогнозирование с помощью функции ЛИНЕЙН (множественная линейная регрессия)

Следующая таблица данных взята со страницы ЛИНЕЙН на веб-сайте поддержки Microsoft.

В некоторых случаях существует несколько независимых переменных, которые следует учитывать при создании модели линейной регрессии. Это называется множественной линейной регрессией (т. Е. Множественными независимыми переменными). Если я хочу оценить стоимость офисного здания, такие вещи, как площадь пола, количество входов в здание, возраст здания и количество офисов, будут частью уравнения. Давайте посмотрим на пример.

Набрав формулу ЛИНЕЙН в ячейку G29 и выполнив ее, мы получим:

1	= ЛИНЕЙН (E3: E13; A3: D13; ИСТИНА; ИСТИНА)

Модель представлена ​​в следующем виде:

Помните, что массив результатов ЛИНЕЙН находится в порядке, обратном уравнению. В приведенном выше примере 52 317,8 — это наш перехват, b, а 27,6 — это m.₁ или значение уклона для переменной Жилая площадь, Икс₁.

Используя функцию ЛИНЕЙН с предоставленными данными, наша модель регрессии:

С r2 значение 0,997, что указывает на сильную или сильно коррелированную модель. Используя модель, теперь вы можете предсказать, какая оценочная стоимость офисного здания будет основана на любой комбинации вышеуказанных независимых переменных.

Функция EXCEL ЛИНЕЙН()

history 26 января 2019 г.

Статистический анализ

Функция ЛИНЕЙН() специально создана для оценки параметров линейной регрессии, а также для вывода регрессионной статистики (коэффициента детерминации, стандартных ошибок, 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 столбца). Как и в более простом режиме, в первой строке возвращаются оценки параметров модели: наклона и сдвига .

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

• выделите диапазон 5х2 ячеек (2 столбца и 5 строк),
• в Строке формул введите формулу ЛИНЕЙН($C$23:$C$83;$B$23:$B$83;;ИСТИНА)
• чтобы ввести формулу нажмите одновременно комбинацию клавиш CTRL + SHIFT + ENTER

Примечание : Чтобы обойтись без формул массива нужно использовать функцию ИНДЕКС() , которая выведет нужное значение. Например, чтобы вывести коэффициент детерминации R 2 введите формулу = ИНДЕКС(ЛИНЕЙН(C23:C83;B23:B83;;ИСТИНА);3;1) . 3 – это номер строки диапазона 5х2, а 1 – это номер столбца. В файле примера на листе Линейный в диапазоне Q 26: R 30 показано как вывести все значения, возвращаемые функцией ЛИНЕЙН() без формул массива .

Итак, установив 4-й аргумент равным ИСТИНА и введя функцию тем или иным способом, функция выведет:

• в строке 1: оценки параметров модели (наклон и сдвиг).
• в строке 2: Стандартные ошибки для наклона и сдвига . Ошибки обозначаются se и seb;
• в строке 3: коэффициент детерминации и стандартную ошибку регрессии . Обозначаются R 2 и SEy;
• в строке 4: значение F-статистики и число степеней свободы . Обозначаются F и df;
• в строке 5: Суммы квадратов SSR, SSE определяющие изменчивость объясненную и необъясненную моделью (см. в статье Простая линейная регрессия разделы про коэффициент детерминации и статью про F-тест ). В справке MS EXCEL SSR, SSE обозначаются как ssreg (Regression Sum of Squares) и ssresid (Residuals Sum of Squares) соответственно.

Примечание : Разобраться в значениях, возвращаемых функцией ЛИНЕЙН() , можно лишь разобравшись в теории линейной регрессии.

В файле примера также приведены формулы, позволяющие сделать расчеты без функции ЛИНЕЙН() – см. диапазон Q 34: R 38 . Альтернативные формулы помогают разобраться в алгоритме расчета вышеуказанных статистических показателей.

Что такое ЛИНЕЙН?

Функция ЛИНЕЙН в Excel — это функция, используемая для создания статистики регрессии для модели линейной регрессии. ЛИНЕЙН — это формула массива, которую можно использовать отдельно или с другими функциями для вычисления конкретной статистики о модели.

Линейная регрессия — это метод статистики, используемый для прогнозирования данных, следующих по прямой линии с использованием известных данных. Регрессия используется для прогнозирования таких значений, как рост продаж, потребности в запасах или простые рыночные тенденции.

ЛИНЕЙН похож на ПРОГНОЗ в том смысле, что он дает аналогичный результат, но с гораздо большей информацией о вашей регрессионной модели, а также с возможностью учесть более одной независимой переменной.

Предположим, у меня есть таблица данных с Икс а также у значения, где Икс — независимая переменная и у зависимая переменная:

Я хочу знать, каково уравнение регрессии приведенных выше данных. Используя ЛИНЕЙН:

1	= ЛИНЕЙН (B3: B7; C3: C7; ИСТИНА; ЛОЖЬ)

Значение точки пересечения по оси Y здесь эквивалентно 0 в экспоненциальном представлении.

Уравнение линии y = 2x + 0

Обратите внимание, что функция ЛИНЕЙН возвращает оба наклон и пересечение линии. Чтобы вернуть оба значения, формулу необходимо ввести как формулу массива

Подробнее о формулах массива позже.

Линейная функция y kx b и её график

1. Для того, чтобы изменить тип линии тренда, выделяем её кликом правой кнопки мыши и в раскрывшемся меню выбираем пункт «Формат линии тренда…».
2. После этого запускается уже знакомое нам окно формата. В блоке выбора типа аппроксимации устанавливаем переключатель в положение «Экспоненциальная». Остальные настройки оставим такими же, как и в первом случае. Щелкаем по кнопке «Закрыть».
3. После этого линия тренда будет построена на графике. Как видим, при использовании данного метода она имеет несколько изогнутую форму. При этом уровень достоверности равен 0,9592, что выше, чем при использовании линейной аппроксимации. Экспоненциальный метод лучше всего использовать в том случае, когда сначала значения быстро изменяются, а потом принимают сбалансированную форму.

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

Технические характеристики → Полезное видео → Общие данные → Таблица размеров → Полезные советы → Как правильно рассчитать → Сыпучие продукты → Натяжные потолки и технологии→ Мера жидких продуктов

Пример (как функция рабочего листа)

Рассмотрим несколько примеров функции ЛИНЕЙН, чтобы понять, как использовать Excel функцию ЛИНЕЙН в качестве функции рабочего листа в Microsoft Excel:

В этом первом примере мы ввели значения y в столбец A (ячейки с A2 по A6) и значения x в столбце B (ячейки с B2 по B6). Затем мы ввели следующую функцию ЛИНЕЙН в обе ячейки D2 и E2 следующим образом:

=ЛИНЕЙН(A2A6;B2B6;ИСТИНА;ЛОЖЬ) Результат0.5

Функцию ЛИНЕЙН можно записать в виде формулы массива. При создании формулы массива вам необходимо сделать следующее:

1. Введите формулы в обе ячейки D2 и E2.
2. Выделите ячейки D2 и E2.
3. Нажмите F2 (в Windows) или CONTROL+U (на Mac)
4. Нажмите Ctrl+Shift+Enter

Это создает {} скобки вокруг ваших формул следующим образом:

{=ЛИНЕЙН(A2A6;B2B6;ИСТИНА;ЛОЖЬ)} Результат0.5

Вы можете увидеть, как формула выглядит на изображениях ниже.Значение в ячейке D2 возвращает наклон 0.5, а значение в ячейке E2 возвращает точку пересечения оси Y, равную 0. Эту формулу также можно записать следующим образом (ввод значений вместо ячейки диапазоны):

=ЛИНЕЙН({2;3;5;7;10};{4;6;10;14;20};ИСТИНА;ЛОЖЬ) Результат0.5

Функцию ЛИНЕЙН можно записать в виде формулы массива. При создании формулы массива вам необходимо сделать следующее:

1. Введите формулы в обе ячейки D2 и E2.
2. Выделите ячейки D2 и E2.
3. Нажмите F2 (в Windows) или CONTROL +U (на Mac)
4. Нажмите Ctrl+Shift+Enter

Это создает {} скобки вокруг ваших формул следующим образом:

{=ЛИНЕЙН({2;3;5;7;10};{4;6;10;14;20};ИСТИНА;ЛОЖЬ)} Результат0.5

Решение с помощью функции ЛИНЕЙН

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

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

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

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

2.2. Щелкните левой кнопкой мыши на панели инструментов на кнопке

Вставкаf_xФункция

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

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

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

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

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

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

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

Т а б л и ц а 15

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

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

ЛИНЕЙН

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

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

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

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

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

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

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

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

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

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

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

Т а б л и ц а 16

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

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

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

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

Т а б л и ц а 17

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

Т а б л и ц а 18

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

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

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

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

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

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

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

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

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

План работы

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

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

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

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

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

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

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

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

ЛИНЕЙН советы

1. Убедитесь, что у вас установлена ​​самая последняя версия Microsoft 365 для использования функции ЛИНЕЙН с динамическими массивами. Возможно, вам потребуется включить текущий канал предварительной оценки Office (предварительная версия), чтобы использовать функции динамического массива. На странице учетной записи:
2. Если вы используете версию, отличную от Microsoft 365, вам придется использовать устаревший метод CTRL + SHIFT + ENTER (CSE) для оценки формул массива.
3. При использовании устаревшего метода количество столбцов, выделяемых при вводе функции массива ЛИНЕЙН, всегда равно количеству столбцов. Икс переменные в ваших данных плюс 1. Число строк, которые нужно выбрать для массива, равно 5.
4. Если вы будете делиться своей версией Excel с динамическим массивом с кем-то, кто использует версию, отличную от Microsoft 365, используйте устаревший метод CSE, чтобы избежать проблем с совместимостью.