Генерация данных для простой линейной регрессии в MS EXCEL

Сгенерируем массив данных для целей простой линейной регрессии. Линейный тренд зададим уравнением: Y=k*X+m.

Для академических целей бывает полезно сгенерировать в MS EXCEL значения двух переменных, которые демонстрируют приблизительно линейную взаимосвязь (см. статью про линейную регрессию).

Для этого нам понадобятся следующие исходные данные:

  • наклон k и сдвиг m для задания линии тренда: Y=k*X+m (не путать их с оценками параметров регрессионной модели);
  • начальное значение Х и шаг изменения Х (шаг будет равномерный);
  • величина разброса (в процентах от среднего значения Y).

Сначала сгенерируем массив значений Х и Y=k*X+m (см. файл примера столбцы B и D).

Линию тренда построим на диаграмме типа Точечная по 2-м точкам, т.к. в этом случае линия выглядит на диаграмме ровнее, по сравнению с тем, если ее строить по всем точкам.

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

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

=ЕСЛИ(И($B$15>0;СРЗНАЧ($D$23:$D$83));

НОРМ.ОБР(СЛЧИС();D23;$B$15*ABS(СРЗНАЧ($D$23:$D$83)));

D23)

В качестве среднего значения (второй аргумент функции НОРМ.ОБР()) будем брать текущее значение Y тренда (D23). В качестве стандартного отклонения используем среднее значение Y, умноженное на заданный % отклонения. Подробнее о генерации случайных значений по нормальному закону см. в статье про случайные значения, распределенные по нормальному закону.

Для наглядности также построим линию регрессии (с помощью инструмента Линия тренда и с помощью формул (столбец F), предварительно вычислив параметры модели). 

Обе линии (регрессии и тренда) не обязательно будут совпадать.


Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)