Регрессионная модель в Excel

Регрессионную модель будем строить для прогнозирования и оценке демографии в России. Статистические данные динамики численности населения в России за 20 лет по годам, в период с 1999 по 2019 год возьмём из вики.

Таблица численности населения России с 1999 г. по 2019 г.

X, год Y, млн.чел.
1999 147.5
2000 146.9
2001 146.3
2002 145.2
2003 145
2004 144.2
2005 143.5
2006 142.8
2007 142.2
2008 142
2009 141.9
2010 142.9
2011 142.9
2012 143.1
2013 143.3
2014 143.7
2015 146.3
2016 146.5
2017 146.8
2018 146.9
2019 146.8

В таблице переменная X – год, Y – численность населения в млн.
Таблица численности населения России с 1999-2019
Для того чтобы построить линию тренда и получить уравнение регрессии, переходим на вкладку Вставка, выбираем диаграммуточечная с гладкими кривыми и маркерами
Диаграмма – точечная с гладкими кривыми и маркерами Excel
Затем переходим на область диаграммы и правым кликом мыши вызываем меню и выбираем выбрать данные и выбираем диапазон данных для диаграммы
Выбрать данные Excel
Изменение ряда Excel
Выбор источника данных - Excel
В результате должен получиться следующий график
График динамики численности населения России с 1999 г. по 2019 г
Анализируя полученный график, можно сделать вывод, что этот период характеризуется демографической ямой. С 1999 года по 2009 год численность населения падала в России, а с 2010 по 2018 год наблюдается рост численности населения, а c 2018 по 2019 гг. опять идёт небольшой спад численности населения. Рост численности населения в России, начиная с 2010 годом возможно связан с ведением программы материнского капитала в 2007 году, а также с присоединением Крыма в 2014 году.
Для получения уравнения регрессии для данной линии тренда, жмём плюс (элементы диаграммы) на области графика справа вверху -> линия тренда -> дополнительные параметры.
плюс на графике - Excel
выбор модели в Excel
Здесь выбираем форму линии тренда и ниже ставим галочки — показать уравнение регрессии и показать на диаграмме величину достоверности аппроксимации, также указываем прогноз вперёд на один период, т.е. на 2020 год.
уравнение регрессии и значение достоверности аппроксимации Excel
Выбираем полиномиальную линию тренда четвертой порядка (хотя выше 5 и 6 порядка, но они не всегда верно описывают модель), так как значение величины достоверности аппроксимации высокое по сравнению с линейной, экспоненциальной, логарифмической, степенной и т.д.
Форма линии тренда Полиномиальная Excel
Уравнение регрессии:
y = -0.0005x4 + 4.2586x3 — 12830x2 + 2E+07x — 9E+09
Здесь, значение E означает 10 в какой-либо степени.
Например,
число 2E+07 эквивалентно числу 2*107=-20000000
— 9E+09=-9*109=-90000000000
Величина достоверности аппроксимации равна:
R² = 0.9587
С помощью полученного уравнения регрессии можно спрогнозировать население России на 2020 год.
Прогноз населения России на 2020 год - график
Анализируя график, можно сделать вывод что в 2020 году населения России снизится на 200-300 тыс.чел.
Таким же образом можно построить линию тренда для динамики численности населения России после ВОВ, начиная с 1946 по 2019 г.

X Y
1946 97.5
1947 98.5
1948 99.2
1949 100.2
1950 102.1
1951 103
1952 104.6
1953 106.7
1954 108.4
1955 110.5
1956 112.3
1957 114
1958 115.7
1959 117.5
1960 119
1961 120.8
1962 122.4
1963 123.9
1964 125.2
1965 126.3
1966 127.2
1967 128
1968 128.7
1969 129.4
1970 130.1
1971 130.6
1972 131.3
1973 132.1
1974 132.8
1975 133.6
1976 134.6
1977 135.5
1978 136.5
1979 137.5
1980 138.1
1981 138.9
1982 139.6
1983 140.5
1984 141.6
1985 142.5
1986 143.5
1987 144.8
1988 146
1989 147.4
1990 147.7
1991 148.3
1992 148.5
1993 148.6
1994 148.4
1995 148.5
1996 148.3
1997 148
1998 147.8
1999 147.5
2000 146.9
2001 146.3
2002 145.2
2003 145
2004 144.2
2005 143.5
2006 142.8
2007 142.2
2008 142
2009 141.9
2010 142.9
2011 142.9
2012 143.1
2013 143.3
2014 143.7
2015 146.3
2016 146.5
2017 146.8
2018 146.9
2019 146.8

График динамики численности населения России после Великой Отечественной войны в период с 1946 по 2019 г.
Динамика численности населения России 1946 по 2019

Leave a Reply

Ваш e-mail не будет опубликован.