Регрессионная модель в 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 года по 2009 год численность населения падала в России, а с 2010 по 2018 год наблюдается рост численности населения, а c 2018 по 2019 гг. опять идёт небольшой спад численности населения. Рост численности населения в России, начиная с 2010 годом возможно связан с ведением программы материнского капитала в 2007 году, а также с присоединением Крыма в 2014 году.
Для получения уравнения регрессии для данной линии тренда, жмём плюс (элементы диаграммы) на области графика справа вверху -> линия тренда -> дополнительные параметры.


Здесь выбираем форму линии тренда и ниже ставим галочки — показать уравнение регрессии и показать на диаграмме величину достоверности аппроксимации, также указываем прогноз вперёд на один период, т.е. на 2020 год.

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

Уравнение регрессии:
y = -0.0005x⁴ + 4.2586x³ — 12830x² + 2E+07x — 9E+09
Здесь, значение E означает 10 в какой-либо степени.
Например,
число 2E+07 эквивалентно числу 2*10⁷=-20000000
— 9E+09=-9*10⁹=-90000000000
Величина достоверности аппроксимации равна:
R² = 0.9587
С помощью полученного уравнения регрессии можно спрогнозировать население России на 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 г.