Excel: тайна линии тренда

Коллеги не раз жаловались, что Excel всех версий при построении линии тренда для линейной или иной регрессии определяет коэффициенты регрессионной прямой (или кривой) неправильно, даже если корректно указать для графика данные оси 0x (правая кнопка мыши в области данных диаграммы, "Выбрать данные...", кнопка "Изменить" для подписей горизонтальной оси).

Проблема решается просто - для типа диаграммы "График", который все и выбирают, Excel использует при построении регрессии значения 1,2,3,... для горизонтальной оси, а не ваши данные. Выберите тип "Точечная" - "Точечная с прямыми (или гладкими) отрезками и маркерами" и всё заработает безупречно. Для этого типа диаграммы перед созданием нужно выделить, в том числе, подписи оси 0x.

Построим несколькими способами линейную регрессию для ряда данных из 4 точек и приложим соответствующий документ.

Лист 1. Пользуемся готовыми средствами линии тренда (правая кнопка мыши на точке ряда, выбрать "Добавить линию тренда...").

Построение диаграммы

Окно "Формат линии тренда", правая кнопки мыши на ряде данных, "Добавить линию тренда..."

Как видим, коэффициенты прямой равны 0,3333 и 0,0833, если построить по ним прямую, она идеально совпадёт с полученной линией тренда (см. на листе 1 прикреплённого документа).

Лист 2. Применяем функцию ЛИНЕЙН. Её нужно вводить как формулу массива - то есть, выделить две ячейки D2:E2, куда попадут коэффициенты, не снимая выделения ввести или создать с помощью Мастера функций формулу =ЛИНЕЙН(B2:B5;A2:A5;;ЛОЖЬ) и нажать комбинацию клавиш Ctrl+Shift+Enter. Видно, что коэффициенты получились те же самые.

Лист 3. Применяем пару функций НАКЛОН и ОТРЕЗОК для вычисления коэффициентов регрессионной прямой. Получаем те же значения.

Лист 4. Вычисляем коэффициенты МНК первого порядка "вручную". Формула в ячейках G2:G3 также является формулой массива и вводится как на листе 2. Коэффициенты получатся "в обратном порядке", то есть, нужно будет вычислить =$G$3*A2+$G$2 (см. столбец I).

Лист 5. Решаем МНК первого порядка как задачу оптимизации. Если кнопка инструмента "Поиск решения" отсутствует на вкладке "Данные", нужно добавить её (круглая кнопка ии меню Файл, "Параметры" или "Параметры Excel", Надстройки, выбрать в списке "Надстройки Excel", "Перейти...", включить чекбокс "Поиск решения", ОК).

В документе показано окно "Поиск решения" для версии Excel 2007, в старших версиях отличия незначительны. Результаты совпадают с остальными способами.

 Скачать этот документ Excel 2007 и выше в архиве .zip (31 Кб)

12.03.2023, 13:03 [267 просмотров]