Введение в алгоритмы машинного обучения: линейная регрессия

Подготовка данных к линейной регрессии

Линейная регрессия изучается уже давно, и есть много литературы о том, как ваши данные должны быть структурированы, чтобы наилучшим образом использовать модель МНК или Градиентного спуска.

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

Используя эти эвристики и посмотреть, что лучше всего работает для вашей проблемы:

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

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

Это наиболее важно для переменной вывода, и, по возможности, необходимо удалить выбросы в переменной вывода (y).

Удалите коллинеарность. Линейная регрессия будет чрезмерно соответствовать вашим данным, когда у вас есть сильно коррелированные входные переменные

Рассмотрим расчет парных корреляций для входных данных и удаление наиболее коррелированных данных.

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

Нормализованные входные данные: Линейная регрессия часто делает более надежные прогнозы, если отмасштабировать входные переменные с помощью стандартизации или нормализации.

Разве линейная регрессия происходит не от статистики?

Прежде чем мы погрузимся в детали линейной регрессии, вы можете спросить себя, почему мы смотрим на этот алгоритм.

Разве это не техника из статистики?

Машинное обучение, в частности, область прогнозного моделирования в первую очередь связана с минимизацией ошибки модели или созданием наиболее точных прогнозов, за счет объяснимости. При применении машинного обучения мы будем заимствовать, повторно использовать и «воровать» алгоритмы из разных областей, включая статистику, и использовать их в этих целях.

Таким образом, линейная регрессия была разработана в области статистики и изучается в качестве модели для понимания взаимосвязи между входными и выходными числовыми переменными, но заимствована машинным обучением. Это одного и статистический алгоритм, и алгоритм машинного обучения.

Далее рассмотрим некоторые из общих имен, используемых для обозначения модели линейной регрессии.

Произвольный базис

$inline$f_i$inline$

$$display$$ f = \sum_i w_i f_i, $$display$$

$inline$f_i$inline$$inline$f_0(\mathbf{x}) = 1$inline$$inline$f_i$inline$$inline$\{(x_1,y_1),\cdots,(x_N,y_N)\}$inline$$inline$(1, x, x^2)$inline$

$$display$$ \Phi = \begin{pmatrix} — & \boldsymbol{f}^{(1)\top} & — \\ \cdots & \cdots & \cdots\\ — & \boldsymbol{f}^{(N)\top} & — \end{pmatrix} = \begin{pmatrix} {f}_{0}\left(\mathbf{x}^{(1)}\right) & {f}_{1}\left(\mathbf{x}^{(1)}\right) & \cdots & {f}_{n}\left(\mathbf{x}^{(1)}\right) \\ \cdots & \cdots & \cdots & \cdots\\ {f}_{0}\left(\mathbf{x}^{(1)}\right) & {f}_{1}\left(\mathbf{x}^{(N)}\right) & \cdots & {f}_{n}\left(\mathbf{x}^{(N)}\right) \end{pmatrix}, $$display$$

$$display$$ E(\mathbf{w})={\|{\boldsymbol{\epsilon}}(\mathbf{w})\|}^2={\|\mathbf{y}-\Phi \, \mathbf{w}\|}^2 $$display$$

$$display$$ \hat{\mathbf{w}} = \text{argmin}_\mathbf{w} \,E(\mathbf{w}) = (\Phi^{\top}\Phi)^{-1}\Phi^{\top}\mathbf{y}=\Phi^{+}\mathbf{y}, $$display$$

График экспоненциального сглаживания

Ниже на рисунке изображен график спроса, среднее скользящие и прогноз методом экспоненциального сглаживания, который построен на основе исходных значений:

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

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

Экстраполяция

— это метод научного исследования, который основан на распространении прошлых
и настоящих тенденций, закономерностей, связей на будущее развитие объекта прогнозирования. К методам экстраполяции
относятся

метод скользящей средней, метод экспоненциального сглаживания, метод наименьших квадратов.

Метод экспоненциального сглаживания

наиболее эффективен при разработке среднесрочных прогнозов.
Он приемлем при прогнозировании только на один период вперед. Его основные достоинства простота процедуры вычислений и возможность
учета весов исходной информации. Рабочая формула метода экспоненциального сглаживания:

При прогнозировании данным методом возникает два затруднения:

• выбор значения параметра сглаживания α;
• определение начального значения Uo.

От величины α зависит

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

Таким образом, если есть уверенность, что начальные условия, на основании которых разрабатывается прогноз, достоверны, следует
использовать небольшую величину параметра сглаживания (α→0). Когда параметр сглаживания мал, то исследуемая функция ведет себя как
средняя из большого числа прошлых уровней. Если нет достаточной уверенности в начальных условиях прогнозирования, то следует использовать
большую величину α, что приведет к учету при прогнозе в основном влияния последних наблюдений.

Точного метода для выбора оптимальной величины параметра сглаживания α нет. В отдельных случаях автор данного метода профессор Браун
предлагал определять величину α, исходя из длины интервала сглаживания. При этом α вычисляется по формуле:

где n – число наблюдений, входящих в интервал сглаживания.

Задача выбора Uo

(экспоненциально взвешенного среднего начального) решается следующими
способами:

• если есть данные о развитии явления в прошлом, то можно воспользоваться средней арифметической и приравнять к ней Uo;
• если таких сведений нет, то в качестве Uo используют исходное первое значение базы прогноза У1.

Также можно воспользоваться экспертными оценками.

Отметим, что при изучении экономических временных рядов и прогнозировании экономических процессов метод экспоненциального сглаживания
не всегда «срабатывает». Это обусловлено тем, что экономические временные ряды бывают слишком короткими (15-20 наблюдений), и в случае,
когда темпы роста и прироста велики, данный метод не «успевает» отразить все изменения.

Уравнение линии тренда в Excel

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

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

Линейная аппроксимация

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

Рассмотрим условное количество заключенных менеджером контрактов на протяжении 10 месяцев:

Разбираемся с трендами в MS Excel

Большой ошибкой со стороны владельца сайта будет воспринимать диаграмму как есть. Да, невооруженным взглядом видно, что синий и оранжевый столбики «осени» выросли по сравнению с «весной» и тем более «летом». Однако важны не только цифры и величина столбиков, но и зависимость между ними. То есть в идеале, при общем росте, «оранжевые» столбики просмотров должны расти намного сильнее «синих», что означало бы то, что сайт не только привлекает больше читателей, но и становится больше и интереснее.

Что же мы видим на графике? Оранжевые столбики «осени» как минимум ни чем не больше «весенних», а то и меньше. Это свидетельствует не об успехе, а скорее наоборот — посетители прибывают, но читают в среднем меньше и на сайте не задерживаются!

Самое время бить тревогу и… знакомится с такой штукой как линия тренда
.

Прогнозируем

Чтобы рассчитать продажи за 10-ый месяц, подставляем в функцию тренда 10 вместо x. То есть,

Получаем 153664 продажи в следующем месяце. Если добавим новую точку на график, то сразу видим, что R^2 улучшился.

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

Повысить точность модели можно добавлением сезонности к функции тренда, что мы и сделаем в следующей статье.

Екатерина Шипова

Магистр прикладной математики и информатики, веб-аналитик. Сертифицированный специалист Google Аnalytics и Яндекс.Метрика.

• Прогнозирование продаж в Excel с учетом сезонности — 27.06.2018
• Построение функции тренда в Excel. Быстрый прогноз без учета сезонности — 05.06.2018
• Когортный анализ. Сколько пользователей к вам вернулось? — 24.05.2018

Глава 22. Функция массива ЛИНЕЙН

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую. Функция ЛИНЕЙН может также возвращать дополнительную регрессионную статистику (подробнее см. справку MS Excel).

Рис. 22.1. Четыре аргумента функции ЛИНЕЙН

Скачать заметку в формате Word или pdf, примеры в формате Excel

Линейная регрессия

На рис. 22.2 показан набор данных (он уже анализировался в главе 9, когда мы обсуждали функции НАКЛОН, ОТРЕЗОК, ПРЕДСКАЗ и ТЕНДЕНЦИЯ). Поскольку ЛИНЕЙН является функцией массива и вы хотите, чтобы она вернула два значения, выполните следующие действия:

1. Выделите диапазон D2:Е2. Функция ЛИНЕЙН возвращает массив из двух значений, расположенных по горизонтали, но не по вертикали.
2. Введите известные значения y. Это – баллы, которые студенты заработали на последнем тестировании.
3. Введите известные значения х. Это количество часов, которые студенты потратили на подготовку к тестам.
4. Опустите аргумент .
5. Опустите аргумент .
6. Введите формулу с помощью Ctrl+Shift+Enter.

Рис. 22.2. Функция ЛИНЕЙН возвращает наклон и отрезок, если массив расположен в горизонтальном диапазоне

Рис. 22.3. Функция массива ЛИНЕЙН заменяет две отдельные функции – НАКЛОН и ОТРЕЗОК

Если вам всё же нужно вывести результаты функции ЛИНЕЙН в вертикальный массив, воспользуйтесь ухищрением (рис. 22.4).

Рис. 22.4. Формулы для вывода результатов в вертикальный массив

Если вы хотите отобразить не только наклон и отрезок, но и дополнительные статистики, выделите диапазон на один столбец больше, чем столбцов с переменными х, и высотой 5 строк. Как показано на рис. 22.5, поскольку у вас лишь одна переменная х, выделите диапазон Е2:F6 (2 столбца по 5 строк). Третьему и четвертому аргументам присвойте значения ИСТИНА: вы хотите, чтобы b считалось обычным образом, и хотите вывести дополнительные статистики. После ввода формулы нажатием Ctrl+Shift+Enter, результат должен соответствовать рис. 22.6 (подробнее о десяти статистиках см. Простая линейная регрессия).

Рис. 22.5. Когда требуется дополнительная статистика для одной переменной, выделите диапазон 2*5; функция ЛИНЕЙН вернет 10 значений; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Рис. 22.6. Функция ЛИНЕЙН возвращает 10 статистик

В главе 8 было показано, как с помощью формулы преобразовать таблицу в столбец. На рис. 22.7 приведена формула, позволяющая представить результаты работы функции ЛИНЕЙН (которые она возвращает в диапазон 2*5) в вертикальном столбце.

Следующие элементы являются аргументами функции ИНДЕКС:

• аргумент массив: функция ЛИНЕЙН($B$2:$B$12;$A$2:$A$12;ИСТИНА;ИСТИНА) возвращает диапазон из пяти строк и двух столбцов.
• аргумент номер_строки: ОСТАТ(ЧСТРОК(E$1:E1)-1;5)+1 возвращает следующие значения 1,2,3,4,5,1,2,3,4,5 при копировании формулы вдоль столбца от Е1 до Е10.
• аргумент номер_столбца: ЦЕЛОЕ((ЧСТРОК(E$1:E1)-1)/5)+1 возвращает 1,1,1,1,1,2,2,2,2,2 при копировании формулы вдоль столбца от Е1 до Е10.

Рис. 22.7. Преобразование диапазона вывода формулы ЛИНЕЙН из 2*5 в вертикальный

Формула в Е1 не требует ввода с помощью Ctrl+Shift+Enter.

Множественная регрессия

В случае множественной регресии, когда значения y зависят от двух переменных х₁ и х₂, функция ЛИНЕЙН возвращает 12 статистик (подробнее см. Введение в множественную регрессию и Построение модели множественной регрессии). На рис. 22.8 используются следующие обозначения:

• y = зависимая переменная
• x₁ = независимая переменная 1 = баллы за домашнее задание
• x₂ = независимая переменная 2 = часов изучал последний столбец тест = гр.

Чтобы выполнить множественную регрессию:

Выделите диапазон В3:D7 (число столобцов = число переменных +1; число строк всегда равно 5).
Наберите формулу

Для аргумента известные_значения_х, выделите оба столбца значений x из диапазона В13:С23.
Введите функцию с помощью клавиш Ctrl+Shift+Enter.
Обратите внимание, что несмотря на то, что значения х1 указаны в диапазоне В13:С23 до значений х2, наклон сначала указан для х2.

Рис. 22.8. Для двух переменных x₁ и х₂ функция ЛИНЕЙН выполняет множественную регрессию

Если вас раздражают знаяения ошибки #Н/Д дополните вашу формулу функцией ЕСЛИОШИБКА (рис. 22.9).

Рис. 22.9. Вы можете избавиться от ошибок #Н/Д «обернув» ЛИНЕЙН функцией ЕСЛИОШИБКА

Пример с тремя переменными не должен вызвать затруднений (рис. 22.10).

Рис. 22.10. Множественная регрессия для трех независимых переменных

Регрессионный анализ

Вначале несколько теоретических замечаний.

Генеральная совокупность и выборка

Как мы , множество всех имеющихся наблюдений принято считать генеральной совокупностью (population). И эти наблюдения, если в них есть взаимосвязи, можно теоретически аппроксимировать, например, линией регрессии

При этом важно понимать, что это некоторая идеальная модель, которую мы никогда не сможем построить

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

Отклонение прогнозного значения от фактического для «идеальной» линии принято называть ошибкой (error или true error).

$$ \varepsilon = y-\hat{y} $$

Отклонение прогноза от факта для выборочной модели (которую мы и строим) называют остатками (residuals или residual error).

$$ \varepsilon = y-f(x) $$

В этом смысле (mean squared error, MSE) корректнее называть средними квадратичными остатками (mean squared residuals).

На практике ошибку и остатки нередко используют как взаимозаменяемые термины.

Уравнение множественной линейной регрессии

Посмотрим на уравнение множественной линейной регрессии.

$$ y = \theta_0 + \theta_1x_1 + \theta_2x_2 + … + \theta_jx_j + \varepsilon $$

В отличие от простой линейной регрессии в данном случае у нас несколько признаков x (независимых переменных) и несколько коэффициентов $ \theta $ («тета»).

Интерпретация результатов модели

Коэффициент $ \theta_0 $ задает некоторый базовый уровень (baseline) при условии, что остальные коэффициенты равны нулю и зачастую не имеет смысла с точки зрения интерпретации модели (нужен лишь для того, что поднять линию на нужный уровень).

Параметры $ \theta_1, \theta_2, …, \theta_n $ показывают изменение зависимой переменной при условии «неподвижности» остальных коэффициентов. Например, каждая дополнительная комната может увеличивать цену дома в 1.3 раза.

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

Категориальные признаки

Модель линейной регрессии может включать категориальные признаки. Продолжая пример с квартирой, предположим, что мы строим модель, в которой цена зависит от того, находится ли квартира в центре города или в спальном районе.

Перед этим переменную необходимо закодировать, создав, например, через Label Encoder признак «центр», который примет значение 1, если квартира в центре, и 0, если она находится в спальном районе.

В модели, представленной выше, если квартира находится в центре (переменная «центр» равна единице), ее стоимость составит 10,1 миллиона рублей, если на окраине (переменная «центр» равна нулю) — лишь восемь.

Для категориального признака с множеством классов можно использовать one-hot encoding, если между классами признака отсутствует иерархия,

или, например, ordinal encoding в случае наличия иерархии классов в признаке

Выбросы в линейной регрессии

Как и , модель линейной регрессии чувствительна к выбросам (outliers), то есть наблюдениям, серьезно выпадающим из общей совокупности. Сравните рисунки ниже.

При наличии выброса (слева), линия регрессии имеет наклон и может использоваться для построения прогноза. Удалив это наблюдение (справа), линия регрессии становится горизонтальной и построение прогноза теряет смысл.

При этом различают два типа выбросов:

• горизонтальные выбросы или влиятельные точки (leverage points) — они сильно отклоняются от среднего по оси x; и
• вертикальные выбросы или просто выбросы (influential points) — отклоняются от среднего по оси y

Ключевое отличие заключается в том, что вертикальные выбросы влияют на наклон модели (изменяют ее коэффициенты), а горизонтальные — нет.

Сравним два графика.

На левом графике черная точка (leverage point) сильно отличается от остальных наблюдений, но наклон прямой линии регрессии с ее появлением не изменился. На правом графике, напротив, появление выброса (influential point) существенно изменяет наклон прямой.

На практике нас конечно больше интересуют influential points, потому что именно они существенно влияют на качество модели.

Если в простой линейной регрессии мы можем оценить leverage и influence наблюдения графически⧉, в многомерной модели это сделать сложнее. Можно использовать график остатков (об этом ниже) или применить один из уже известных нам методов выявления выбросов.

Про выявление leverage и infuential points можно почитать здесь⧉.

Анализ прогноза спроса продукции в Excel по функции ПРЕДСКАЗ

Пример 2. Компания недавно представила новый продукт. С момента вывода на рынок ежедневно ведется учет количества клиентов, купивших этот продукт. Предположить, каким будет спрос на протяжении 5 последующих дней.

Вид исходной таблицы данных:

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

Рассчитаем значения логарифмического тренда с помощью функции ПРЕДСКАЗ следующим способом:

Как видно, в качестве первого аргумента представлен массив натуральных логарифмов последующих номеров дней. Таким образом получаем функцию логарифмического тренда, которая записывается как y=aln(x)+b.

Результат расчетов:

Для сравнения, произведем расчет с использованием функции линейного тренда:

И для визуального сравнительного анализа построим простой график.

Полученные результаты:

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

Как построить график в Excel по уравнению

Как предоставить информацию, чтобы она лучше воспринималась. Используйте графики. Это особенно актуально в аналитике. Рассмотрим, как построить график в Excel по уравнению.

Что это такое

График показывает, как одни величины зависят от других. Информация легче воспринимается. Посмотрите визуально, как отображается динамика изменения данных.

А нужно ли это

Графический способ отображения информации востребован в учебных или научных работах, исследованиях, при создании деловых планов, отчетов, презентаций, формул. Разработчики для построения графиков добавили способы визуального представления: диаграммы, пиктограммы.

Как построить график уравнения регрессии в Excel

Регрессионный анализ — статистический метод исследования. Устанавливает, как независимые величины влияют на зависимую переменную. Редактор предлагает инструменты для такого анализа.

Подготовительные работы

Перед использованием функции активируйте Пакет анализа. Перейдите: Выберите раздел: Далее: Прокрутите окно вниз, выберите: Отметьте пункт: Открыв раздел «Данные», появится кнопка «Анализ».

Как пользоваться

Рассмотрим на примере. В таблице указана температура воздуха и число покупателей. Данные выводятся за рабочий день. Как температура влияет на посещаемость. Перейдите: Выберите: Отобразится окно настроек, где входной интервал:

1. Y. Ячейки с данными влияние факторов на которые нужно установить. Это число покупателей. Адрес пропишите вручную или выделите соответствующий столбец;
2. Х. Данные, влияние на которые нужно установить. В примере, нужно узнать, как температура влияет на количество покупателей. Поэтому выделяем ячейки в столбце «Температура».

Анализ

Нажав кнопку «ОК», отобразится результат. Основной показатель — R-квадрат. Обозначает качество. Он равен 0,825 (82,5%). Что это означает? Зависимости, где показатель меньше 0,5 считается плохим. Поэтому в примере это хороший показатель. Y-пересечение. Число покупателей, если другие показатели равны нулю. 62,02 высокий показатель.

Как построить график квадратного уравнения в Excel

График функции имеет вид: y=ax2+bx+c. Рассмотрим диапазон значений: .

1. Составьте таблицу как на скриншоте;
2. В третьей строке указываем коэффициенты и их значения;
3. Пятая — диапазон значений;
4. В ячейку B6 вписываем формулу =$B3*B5*B5+$D3*B5+$F3;

Копируем её на весь диапазон значений аргумента вправо. При вычислении формулы прописывается знак «$». Используется чтобы ссылка была постоянной. Подробнее смотрите в статье: «Как зафиксировать ячейку». Выделите диапазон значений по ним будем строить график. Перейдите: Поместите график в свободное место на листе.

Как построить график линейного уравнения

Функция имеет вид: y=kx+b. Построим в интервале .

1. В таблицу прописываем значение постоянных величин. Строка три;
2. Строка 5. Вводим диапазон значений;
3. Ячейка В6. Прописываем формулу.

Выделите диапазон ячеек A5:J6. Далее: График — прямая линия.

Мы рассмотрели, как построить график в Экселе (Excel) по уравнению. Главное — правильно выбрать параметры и диаграмму. Тогда график точно отобразит данные.

Часть 10. Подбор формул по графику. Линия тренда

Для рассмотренных выше задач удавалось построить уравнение или систему уравнений.

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

Процесс подбора эмпирической формулы P(x)
для опытной зависимости F(x)
называется аппроксимацией
(сглаживанием). Для зависимостей с одним неизвестным в Excel используются графики, а для зависимостей со многими неизвестными – пары функций из группы Статистические
ЛИНЕЙН и ТЕНДЕНЦИЯ, ЛГРФПРИБЛ и РОСТ.

В настоящем разделе рассматривается аппроксимация экспериментальных данных с помощью графиков Excel: на основе данных стоится график, к нему подбирается линия тренда

, т.е. аппроксимирующая функция, которая с максимальной степенью близости приближается к опытной зависимости.

Степень близости подбираемой функции оценивается коэффициентом детерминации R2

. Если нет других теоретических соображений, то выбирают функцию с коэффициентом R2
, стремящимся к 1. Отметим, что подбор формул с использованием линии тренда позволяет установить как вид эмпирической формулы, так и определить численные значения неизвестных параметров.

Excel предоставляет 5 видов аппроксимирующих функций:

1. Линейная – y=cx+b
. Это простейшая функция, отражающая рост и убывание данных с постоянной скоростью.

2. Полиномиальная – y=c0+c1x+c2x2+…+c6x6
. Функция описывает попеременно возрастающие и убывающие данные. Полином 2-ой степени может иметь один экстремум (min или max), 3-ей степени – до 2-х экстремумов, 4-ой степени – до 3-х и т.д.

3. Логарифмическая – y=c
lnx+b
. Эта функция описывает быстро возрастающие (убывающие) данные, которые затем стабилизируются.

4. Степенная – y=cxb
, (х
>0и y
>0). Функция отражает данные с постоянно увеличивающейся (убывающей) скоростью роста.

5. Экспоненциальная – y=cebx
, (e
– основание натурального логарифма). Функция описывает быстро растущие (убывающие) данные, которые затем стабилизируются.

Для всех 5-ти видов функций используется аппроксимация данных по методу наименьших квадратов (см. справку по F1 «линия тренда»).

В качестве примера
рассмотрим зависимость продаж от рекламы, заданную следующими статистическими данными по некоторой фирме:

(тыс. руб.)	1,5	2,5	3,5	4,5	5,5
Продажи (тыс. руб.)

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

Приступим к решению
. В первую очередь введите эти данные в Excel и постройте график, как на рис. 38. Как видно, график построен на основании диапазона B2:J2. Далее, щелкнув правой кнопкой мыши по графику, добавьте линию тренда, как показано на рис. 38.

Чтобы подписать ось Х соответствующими значениями рекламы (как на рис. 38), следует в ниспадающем меню (рис. 38) выбрать пункт И
сходные данные
. В открывшемся одноименном окне, в закладке Ряд
, в поле П
одписи оси Х
, укажите диапазон ячеек, где записаны значения Х (здесь $B$1:$K$1):

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

После нажатия ОК
Вы получите результат, как на рис. 40. Коэффициент детерминации R2=
0.9846, что является неплохой степенью близости. Для подтверждения правильности выбранной функции (поскольку других теоретических соображений нет) спрогнозируйте развитие продаж на 10 периодов вперед. Для этого щелкните правой кнопкой по линии тренда – измените формат – после этого в поле Прогноз: вперед на:
установите 10 (рис.

После установки прогноза Вы увидите изменение кривой графика на 10 периодов наблюдения вперед, как на рис. 42. Он с большой долей вероятности отражает дальнейшее увеличение продаж с увеличением рекламных вложений.

Вычисление по полученной формуле =237,96*LN(6)+5,9606 в Excel дает значение 432 тыс. руб.

В Excel имеется функция ПРЕДСКАЗ(), которая вычисляет будущее значение Y по существующим парам значений X и Y значениям с использованием линейной регрессии. Функция Y по возможности должна быть линейной, т.е. описываться уравнением типа c+bx
.

Функция предсказания для нашего примера запишется так: =ПРЕДСКАЗ(K1;B2:J2;B1:J1). Запишите – должно получится значение 643,6 тыс. руб.

Часть11. Контрольные задания

Предыдущая12345678910111213141516Следующая