Определитель матрицы ▦ Производная функции dydx График 3D Упростить выражение Графический метод решения задач нелинейного программирования ⇲ …
Примеры решений Коэффициент Спирмена Коэффициент Фехнера Множественная регрессия Нелинейная регрессия Уравнение регрессии Автокорреляция Расчет параметров тренда Ошибка аппроксимации

Регрессионный анализ в Excel

Данный онлайн калькулятор предназначен для построения уравнений регрессии и нахождения лучшего из них. Полученный отчет сохраняется в файле Word и в Excel. Аналогичный калькулятор существует и для множественной регрессии Excel.
Вид регрессии
Линейная y=a+bx
Параболическая y=a+bx+cx2
Экспоненциальная y=a·exp(bx)
Степенная y=a·x^b
Гиперболическая y=b/x+a
Логарифмическая y=b·ln(x)+a
Показательная y=a·b^x
Здесь будет отображаться решение.

В современных версиях MS Excel (2016, 2019, 2021, Microsoft 365) команда «Сервис → Анализ данных → Регрессия» имеется в надстройке «Анализ данных».

Как включить «Анализ данных» и получить регрессию в новом Excel

  1. Включите надстройку «Пакет анализа».
    1. Откройте Excel.
    2. Перейдите на вкладку «Файл» (File).
    3. Выберите «Параметры» (Options).
    4. В левом меню выберите «Надстройки» (Add-ins).
    5. Внизу окна в поле «Управление» выберите «Надстройки Excel» → нажмите «Перейти» (Go).
    6. В появившемся окне поставьте галочку напротив: «Пакет анализа» (Analysis ToolPak)
    7. Нажмите «ОК».
    💡 Если вы видите сообщение «Пакет анализа не установлен», нажмите «Да», чтобы установить его.
  2. После включения:
    • Перейдите на вкладку «Данные» (Data).
    • В группе «Анализ» вы увидите кнопку «Анализ данных».
    • Нажмите её → в списке выберите «Регрессия» → нажмите «ОК».
    • Задайте входные диапазоны (Y и X), настройте параметры (уровень достоверности, метки, остатки и т.д.).

Формула для вычислений

Функция EXCEL или инструмент Анализа данных Результат вычислений
Оценка параметров модели парной регрессии


ЛИНЕЙН(изв_знач_у; изв_знач_х; константа; стат)
Смысл аргументов функции
изв_знач_у – диапазон значений у;
изв_знач_х – диапазон значений х;
константа - устанавливается на 0, если заранее известно, что свободный член равен 0 и на 1 в противном случае;
стат– устанавливается на 0, если не нужен вывод дополнительных сведений регрессионного анализа и на 1 в противном случае.
Возвращает следующую информацию
Значение коэффициента b1 Значение коэффициента b0
Среднеквадратическое отклонение b1 Среднеквадратическое отклонение b0
Коэффициент детерминации R2 Среднеквадратическое отклонение у

F-статистика

Число степеней свободы
Регрессионная сумма квадратов Остаточная сумма квадратов
Оценка параметров модели парной и множественной линейной регрессии. Сервис / Анализ данных
Для вычисления параметров уравнения регрессии следует воспользоваться инструментом Регрессия
Возвращает подробную информацию о параметрах модели, качестве модели, расчетных значениях и остатках в виде четырех таблиц: Регрессионная статистика, Дисперсионный анализ, Коэффициенты, ВЫВОД ОСТАТКА.
Так же может быть получен график подбора.
Оценка значимости параметров модели линейной регрессии с использованием t - критерия Стьюдента.
,
Вычисленное по этой формуле значение сравнивается с критическим значением t-критерия, которое берется из таблицы значений t Стьюдента с учетом заданного уровня значимости и числа степеней свободы (n-k-1), где k количество факторов в модели.
СТЬЮДРАСПОБР(вероятность; степени_свободы)
Вероятность — вероятность, соответствующая двустороннему распределению Стьюдента.
Степени_свободы — число степеней свободы, характеризующее распределение.
Возвращает t-значение распределения Стьюдента как функцию вероятности и числа степеней свободы.

Проверка значимости модели регрессии с использованием
F-критерий Фишера
FРАСПОБР(вероятность; степени_свободы1; степени_свободы2)
Вероятность — это вероятность, связанная с F-распределением.
Степени_свободы 1 — это числитель степеней свободы-n1 = k.
Степени_свободы 2 — это знаменатель степеней свободы-.n2 = (n - k - 1),
где k – количество факторов, включенных в модель, 		Возвращает обратное значение для F-распределения вероятностей.
FРАСПОБР можно использовать, чтобы определить критические значения F-распределения.
Чтобы определить критическое значение F, нужно использовать уровень значимости α как аргумент вероятность для FРАСПОБР.

Корреляционный анализ в EXCEL

Формула для вычислений Функция EXCEL или инструмент Анализа данных Результат вычислений

Коэффициент корреляции

КОРРЕЛ(массив1;массив2)
Массив1 — это ячейка интервала значений.
Массив2 — это второй интервал ячеек со значениями
Возвращает коэффициент корреляции меду интервалами ячеек массив1 и массив2.

Оценка значимости коэффициента парной корреляции с использованием t - критерия Стьюдента.
Вычисленное по этой формуле значение tнаблсравнивается с критическим значением t-критерия, которое берется из таблицы значений t Стьюдента с учетом заданного уровня значимости и числа степеней свободы (n-2).

СТЬЮДРАСПОБР (вероятность; степени_свободы)
Вероятность — вероятность, соответствующая двустороннему распределению Стьюдента.
Степени_свободы — число степеней свободы, характеризующее распределение.

Возвращает t-значение распределения Стьюдента как функцию вероятности и числа степеней свободы.
Матрица коэффициентов парной корреляции

Обращение к средствам анализа данных. Они доступны через команду Анализ данныхменю Сервис.
Для вычисления матрицы коэффициентов парной корреляции R следует воспользоваться инструментом Корреляция.
Инструмент Корреляция применяется, если имеется более двух переменных измерений для каждого объекта. В результате выдается таблица, корреляционная матрица, показывающая значение функции КОРРЕЛ для каждой возможной пары переменных измерений. Любое значение коэффициента корреляции должно находиться в диапазоне от -1 до +1 включительно.