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

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

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

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

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

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

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

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

Где:

1. Σ означает сумму значений;
2. n — размер выборки или количество наблюдений;
3. Yi – наблюдаемые значения, а;
4. Ŷi — прогнозируемые значения.

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

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

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

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

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

Вычислите MSE с помощью функции SUMSQ

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

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

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

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

Вычислите MSE, используя функцию СРЗНАЧ

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

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

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

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

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

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