Линейн в excel пример

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

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

Это первая статья из серии «Как самостоятельно рассчитать прогноз продаж с учетом роста и сезонности», из которой вы узнаете о 5 способах расчета значений линейного тренда в Excel.

Для того, чтобы легче было научиться прогнозировать продажи с учетом роста и сезонности, я разбил 1 большую статью о расчете прогноза на 3 части:

1. 1. Расчет значений тренда (рассмотрим на примере Линейного тренда в этой статье);
   2. Расчет сезонности;
   3. Расчет прогноза;

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

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

Рассмотрим линейный тренд на примере расчета прогноза продаж в Excel по месяцам.

Временной ряд продажи по месяцам (см. вложенный файл).

В этом временном ряду у нас есть 2 переменных:

Уравнение линейного тренда y(x)=a+bx, где

y — это объёмы продаж

x — номер периода (порядковый номер месяца)

a – точка пересечения с осью y на графике (минимальный уровень);

b – это значение, на которое увеличивается следующее значение временного ряда;

1-й способ расчета значений линейного тренда в Excel с помощью графика

Выделяем анализируемый объём продаж и строим график, где по оси Х — наш временной ряд (1, 2, 3… — январь, февраль, март …), по оси У — объёмы продаж. Добавляем линию тренда и уравнение тренда на график. Получаем уравнение тренда y=135134x+4594044

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

При расчете значений линейного тренде нам будут известны:

1. Время — значение по оси Х;
2. Значение «a» и «b» уравнения линейного тренда y(x)=a+bx;

Рассчитываем значения тренда для каждого периода времени от 1 до 25, а также для будущих периодов с 26 месяца до 36.

Например, для 26 месяца значение тренда рассчитывается по следующей схеме: в уравнение подставляем x=26 и получаем y=135134*26+4594044=8107551

27-го y=135134*27+4594044=8242686

2-й способ расчета значений линейного тренда в Excel — функция ЛИНЕЙН

1. Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel:

=ЛИНЕЙН(известные значения y, известные значения x, константа, статистика)

Для расчета коэффициентов в формулу вводим

известные значения y (объёмы продаж за периоды),

известные значения x (номера периодов),

вместо константы ставим 1,

вместо статистики 0,

Получаем 135135 — значение (b) линейного тренда y=a+bx;

Для того чтобы Excel рассчитал сразу 2 коэффициента (a) и (b) линейного тренда y=a+bx, необходимо

1. установить курсор в ячейку с формулой и выделить соседнюю справа, как на рисунке;
2. нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

Получаем 135135, 4594044 — значение (b) и (a) линейного тренда y=a+bx;

2. Рассчитаем значения линейного тренда с помощью полученных коэффициентов . Подставляем в уравнение y=135134*x+4594044 номера периодов — x, для которых хотим рассчитать значения линейного тренда.

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

3-й способ расчета значений линейного тренда в Excel — функция ТЕНДЕНЦИЯ

Рассчитаем значения линейного тренда с помощью стандартной функции Excel:

=ТЕНДЕНЦИЯ(известные значения y; известные значения x; новые значения x; конста)

Подставляем в формулу

1. известные значения y — это объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);
2. известные значения x — это номера периодов x для известных значений объёмов продаж y;
3. новые значения x — это номера периодов, для которых мы хотим рассчитать значения линейного тренда;
4. константа — ставим 1, необходимо для того, чтобы значения тренда рассчитывались с учетом коэффицента (a) для линейного тренда y=a+bx;

Для того чтобы рассчитать значения тренда для всего временного диапазона, в «новые значения x» вводим диапазон значений X, выделяем диапазон ячеек равный диапазону со значениями X с формулой в первой ячейке и нажимаем клавишу F2, а затем — клавиши CTRL + SHIFT + ВВОД.

4-й способ расчета значений линейного тренда в Excel — функция ПРЕДСКАЗ

Рассчитаем значения линейного тренда с помощью стандартной функции Excel:

=ПРЕДСКАЗ(x; известные значения y; известные значения x)

Вместо X поставляем номер периода, для которого рассчитываем значение тренда.

Вместо «известные значения y» — объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);

«известные значения x» — это номера периодов для каждого выделенного объёма продаж.

3-й и 4-й способ расчета значений линейного тренда быстрее, чем 1 и 2-й, однако с его помощью невозможно управлять коэффициентами тренда, как описано в статье «О линейном тренде».

5-й способ расчета значений линейного тренда в Excel — Forecast4AC PRO

2. Заходим в меню программы и нажимаем «Start_Forecast». Значения линейного тренда рассчитаны.

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

В следующих статье «Как самостоятельно сделать прогноз продаж с учетом роста и сезонности» мы:

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

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

• Novo Forecast Lite — автоматический расчет прогноза в Excel .
• 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

• Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Глава 22. Функция массива ЛИНЕЙН

Это глава из книги: Майкл Гирвин. 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 зависят от двух переменных х₁ и х₂, функция ЛИНЕЙН возвращает 12 статистик (подробнее см. Введение в множественную регрессию и Построение модели множественной регрессии). На рис. 22.8 используются следующие обозначения:

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

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

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

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

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

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

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

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

2 комментария для “Глава 22. Функция массива ЛИНЕЙН”

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

Построение графика в Excel и использование функции ЛИНЕЙН

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

Исследуем характер зависимости в три этапа:

— Построим график зависимости.

— Построим линию тренда (в данном случае это прямая ).

— Получим числовые характеристики коэффициентов этого уравнения.

Решение

Построение графика зависимости.

1. Выделим интервал А1:В25.

2. Вызовем Мастер диаграмм, нажав соответствующую кнопку на панели инструментов.

3. Используя мышь, выделим область для встроенной диаграммы.

4. На 1 шаге в диалоговом окне Мастера диаграмм интервал А1:В25 должен быть указан, если это не так укажите. Нажмите Шаг>.

5. На 2 шаге выберите тип диаграммы XY-точечная.Нажмите Шаг>.

6. На 3 шаге выберите первый тип автоформата. Нажмите Шаг>

7. На 4 шаге укажите следующие параметры:

8. Отвести 1 столбец для данных по оси Х; отвести 1 строку для текста легенды. Нажмите Шаг>.

9. На 5 шаге в окне «Название диаграммы: » введите заголовок «Линейная аппроксимация»; в окне «Категорий [X]:» введите «x»; в окне «Значений [Y]:» введите «y». Нажмите Закончить.

Построение линии тренда

Для построения линии тренда выполним следующую последовательность действий:

Дважды щелкнем по диаграмме. Диаграмма активизируется.

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

Вставляем линию тренда, воспользуемся меню Вставка – Линия тренда.

Появиться диалоговое окно «Линия тренда» выберем на вкладке «Тип» (Рис.2) линейный тип и перейдем к вкладке «Параметры».

На вкладке «Параметры» (Рис.3) потребуем показывать уравнение тренда на диаграмме и показывать значение , поставив их в соответствующие клетки. Нажмем кнопку ОК.

На диаграмме появится линия тренда с соответствующим уравнением. Также изменится легенда. При желании текстовое поле с уравнением и значением , а также название координат x и y, можно оттащить в более удобное место, как это сделано на Рис 4.

Для построения квадратичной аппроксимации на четвертом шаге в диалоговом окне «Линия тренда» выберем на вкладке «Тип» (Рис.2) полиномиальный тип степень 2. Результат представлен на рис.5.

Для построения экспоненциальной аппроксимации на четвертом шаге в диалоговом окне «Линия тренда» выберем на вкладке «Тип» (Рис.2) экспоненциальный тип. Результат представлен на рис.6.

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

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

Получение числовых характеристик зависимости

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

— Выделите область A65:B69.

— Вызовите Мастер функций.

— Выберите функцию Линейн.

— Определим аргументы функции.

— В качестве изв_знач_уукажите В1:В25.

— В качестве изв_знач_хукажите А1:А25.

— Третье поле Константаоставьте пустым.

— В четвертом поле статнаберите истина.

— Нажмите кнопку Закончить.

— Установите курсор в строку формул.

— Нажмите комбинацию клавиш Ctrl+Shift+Enter, это обеспечит ввод табличной формулы!

В результате должны заполниться все ячейки интервала A65:B69(см. табл.9).

Поясним назначение некоторых величин, расположенных в табл.9.

Величины, расположенные в ячейках A67 и B67 характеризуют соответственно наклон и сдвиг.

A69 — коэффициент детерминированности.

A70 — F-наблюдаемое значение.

B68 — число степеней свободы.

A69 — регрессионная сумма квадратов.

B69 — остаточная сумма квадратов.

Рассмотрим назначение функции ЛИНЕЙН.

Эта функция использует метод наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Уравнение для прямой линии имеет следующий вид:

или ,

где зависимое значение y является функцией независимого значения x. Значения m — это коэффициенты, соответствующие каждой независимой переменной x, а b — это постоянная. Заметим, что y, x и m могут быть векторами.

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

Синтаксис

ЛИНЕЙН(известные_значения_y;известные_значения_x;конст; статистика)

Известные_значения_y — это множество значений y, которые уже известны для соотношения .

— Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.

Известные_значения_x — это множество значений x, которые уже известны для соотношения .

— Массив известные_значения_x может содержать одно или несколько множеств переменных.

— Если используется только одна переменная, то известные_значения_y и известные_значения_x могут быть массивами любой формы при условии, что они имеют одинаковую размерность.

— Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец).

Конст— это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

— Если констимеет значение ИСТИНА или опущена, то b вычисляется обычным образом.

— Если констимеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются так, чтобы выполнялось соотношение y = mx .

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

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

. (17)

— Если статистика имеет значение ЛОЖЬ или опущена, то функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

Дополнительная регрессионная статистика приведена в табл. 10

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

ЛИНЕЙН – это статистическая функция 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. Корреляционный анализ

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

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