Как рассчитать среднеквадратическую ошибку (MSE) в Microsoft Excel

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

В этом руководстве мы более подробно объясним, что такое MSE, а затем покажем, как рассчитать MSE в Excel.

Что такое среднеквадратическая ошибка?

Среднеквадратическая ошибка (MSE) – это расчет, измеряющий среднеквадратическую разницу между расчетными и фактическими значениями в наборе данных. Другими словами, он оценивает величину ошибки в статистической модели. Для любителей статистики: он вычисляет, насколько близко линия регрессии соответствует набору точек данных.

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

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

Формула MSE выглядит следующим образом:

Где:

Σозначает сумму значений;

n— размер выборки или количество наблюдений;

Yi— наблюдаемые значения;

Ŷi— прогнозируемые значения.

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

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

Как рассчитать среднеквадратическую ошибку в Microsoft Excel

Существует два основных способа расчета MSE в Microsoft Excel: функция SUMSQ, СРЗНАЧ функция и формула MSE. Мы будем использовать пример ниже, чтобы показать вам, как рассчитать MSE, используя каждую из этих функций:.

В этом упрощенном примере мы рассмотрим вымышленные объемы продаж за каждый месяц (столбец A). Оценочные значения представлены в столбце B, а фактические значения — в столбце C.

Рассчитать MSE с помощью функции SUMSQ

Функция СУМСК в Excel используется для вычисления суммы квадратов чисел в диапазоне. Вот как его можно использовать для расчета MSE:

Создайте новый столбец в столбце D под названием «Разница». Это будет представлять собой разницу между ожидаемыми и фактическими значениями.

Используйте формулу «C2-B2», чтобы вычислить разницу между точками данных в каждой строке.

Выберите новую ячейку и введите следующий синтаксис: «=SUMSQ(D2:D13/COUNT(D2:D13)». Замените ячейки в формуле правильными ячейками из электронной таблицы Excel. Окончательный результат в этой ячейке. ваша MSE.

Рассчитать MSE с помощью функции СРЗНАЧ

Функция СРЗНАЧ в MS Excel используется для расчета среднего значения диапазона чисел. Этот метод возвращает тот же результат, что и функция SUMSQ, но каждый шаг выполняется вручную.

Выполните шаги 1 и 2, описанные в методе SUMSQ. Это вернет разницу между прогнозируемыми значениями и фактическими данными.

Назовите столбец E «Квадрат различий», затем используйте формулу «=D2^2», чтобы вычислить квадрат разницы каждого значения в столбце D.

Выберите новую ячейку и вычислите среднее значение столбца E. Это вернет среднее значение квадратов, которые вы вычислили ранее. Для этого введите «=СРЗНАЧ(E2:E12)» и нажмите Enter. Обязательно замените ячейки на ячейки, которые вы использовали в своем рабочий лист. Полученное значение данных и есть ваш MSE.

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

Microsoft Excel уже давно является одним из самых мощных инструментов для анализа данных. Надеемся, что благодаря этому руководству вы теперь сможете легко использовать Excel для расчета MSE. А поскольку они очень похожи, вы сможете выполнить те же вычисления в Google Таблицы..