Регрессионный анализ в Excel. Подробная иллюстрированная инструкция

Далее вводим факторы (точнее, содержащие информацию о них ячейки) в поле «Входной интервал X». Как указывалось ранее, перед нами стоит задача понять, как влияет температура воздуха на количество клиентов. Для этого необходимо записать адреса ячеек, входящих в столбик «Температура». Как это сделать? Та точно так же, как и с предыдущим полем: ввести вручную или выделить соответствующий диапазон мышью.

Обзор алгоритмов интеллектуального анализа данных Microsoft Analysis Services

В настоящее время существует определенный пул Business Intelligence и Data Science инструментов, которые позволяют исследовать и визуализировать данные.

Microsoft Analysis Services – часть системы управления базами данных Microsoft SQL Server. Включает в себя набор служб, связанных с бизнес-анализом и исследованием данных. Службы Analysis Services предоставляют алгоритмы для использования в решениях интеллектуального анализа данных.

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

Алгоритмы используются внутри контейнеров моделей Analysis Services, которые в свою очередь создаются на основе структур Analysis Services.

Для работы с алгоритмами нам потребуются:

1. В качестве серверной части:
   • MS SQL Server с установленными службами Analysis Services
2. В качестве клиентской части:
   • MS Excel с активированной надстройкой “Интеллектуальный анализ данных”
   • MS Visual Studio с установленным Microsoft SQL Server Data Tools Business Intelligence
   • Microsoft SharePoint
3. Дополнительно потребуется:
   • Доступ к Analysis Services с разрешением на создание файлов в разделе Analysis Services

В MS Office 2013 и выше данные инструменты встроены в корпоративные версии MS Excel (достаточно активировать надстройку «Интеллектуальный анализ данных» в настройках MS Excel).

В MS Office 2010 надстройку необходимо устанавливать отдельно ссылка.

После активации надстройки в MS Excel появится вкладка «Интеллектуальный анализ данных».

В MS Excel последовательно выбираем — Интеллектуальный анализ данных > Соединение > Создать.

Заполняем настройки подключения со службами Analysis Services, проверяем соединение и выбираем – ОК.

2. Создание структуры интеллектуального анализа.

В MS Excel последовательно выбираем — Интеллектуальный анализ данных > Дополнительно > Создать структуру интеллектуального анализа данных.

В Мастере задачи последовательно выбираем – Внешний источник данных > Имя источника данных.

Заполняем поле «Запрос» и выбираем «Далее».

3. Выбор типов данных.

В нашем случае Наименование должности (поле DOLJ) является дискретной величиной (Discrete), а размер выплаты (поле ZARP) непрерывной (Continuous).

Указываем типы данных и выбираем «Далее».

4. Выбор процента проверочных данных.

Процент проверочных данных устанавливает соотношение качества анализа от качества проверки.

Указываем процент проверочных данных и выбираем «Далее».

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

5. Добавление модели к структуре.

В MS Excel последовательно выбираем — Дополнительно > Добавить модель к структуре.

6. Алгоритм «Дерево принятия решений».

Analysis Services содержит несколько алгоритмов интеллектуального анализа. Выбираем «Алгоритм дерева принятия решений».

6.1 Выбор входных и прогнозируемых полей.

В нашем случае, в таблицу была добавлена зависимость выплат от должности. Поэтому входными данными является «Должность(DOLJ)», а прогнозируемыми «Зарплата (ZARP)» Устанавливаем соответствия столбцов таблицы с их назначением и выбираем «Далее».

6.2 Результат работы алгоритма – «Дерево принятия решений».

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

На данной схеме слева направо отображены наиболее сильные из выявленных зависимостей.

Результат полностью удовлетворяет входным данным. При заполнении поля с выплатами действительно имелось правило, которое добавляло к выплатам для должностей с идентификаторами 13, 17, 15, … около 30%.

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

7. Результат работы алгоритма – «Классификация».

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

8. Результат работы алгоритма — «Оценка».

Данный алгоритм предоставил полезное графическое представление данных. Присутствует корректная группировка данных.

9. Результата работы алгоритма – «Кластеризация».

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

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

10. Результат работы алгоритма – «Прогноз».

Особенностью данного алгоритма является необходимость использования временных меток. В нашем случае используется дата транзакции «INDATE».

Результат работы алгоритма представлен в интерактивном окне, где можно варьировать соотношение исследуемого и прогнозируемого диапазона данных.

Для тестирования работы алгоритма по всему временному диапазону была добавлена постоянная зависимость, увеличивающая на 40% значение каждое 15-е число месяца. Как видно из графика, алгоритм сохранил данную закономерность.

Резюмируем

Алгоритмы интеллектуального анализа данных применимы и могут быть полезны в работе аналитика.

В качестве преимуществ отмечаем:

• Интерактивный графический интерфейс и как следствие низкий порог входа.
• Служба Microsoft Analysis Services не требует отдельной лицензии. Входит в дистрибутив MS SQL server.
• В качестве клиента Microsoft Analysis Services могут выступать широко используемые в банке MS Excel, MS Sharepoint, MS Management Studio, MS Visual Studio.
• Клиент-серверная архитектура Microsoft Analysis Services производит ресурсоемкие аналитические задачи на стороне сервера.

В качестве недостатков отмечаем:

• Малое число моделей (алгоритмов).
• Ограниченное число настроек алгоритмов.
• Часть алгоритмов в качестве вывода результатов предоставляет лишь график без возможности экспорта табличных результатов.

Регрессионный анализ в Excel. Подробная иллюстрированная инструкция

Важно понимать, что Excel – это не только программа для создания баз данных, но и профессиональный статистический инструмент. И в статистике есть множество способов обработки числовых значений. Один из них – регрессионный анализ. Он тесно связан с корреляциями. Перед тем, как разобраться в том, как в Эксель осуществлять его на практике, необходимо сперва понять, что же такое регрессионный анализ и чем он отличается от корреляционного.

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

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

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

Как подключить пакет анализа в программе Excel

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

1. Открыть меню «Файл». Для этого нужно нажать на одноименную кнопку слева от вкладки «Главная».
2. Далее у нас откроется меню настроек файла. Нас интересует вкладка «Параметры».
3. Теперь у нас появляется возможность настроить параметры Excel. Затем переходим в меню надстроек, выставляем надстройки Excel в перечне, который находится внизу и нажимаем на «Перейти».
4. После этого появляется окошко, в котором можно управлять существующими надстройками. Нас интересует опция «Пакет анализа». Нужно поставить галочку возле нее и нажать на «ОК».

Теперь у нас на вкладке «Данные» добавился новый блок инструментов, в котором появилась кнопка «Анализ данных».

А теперь более подробно опишем, какие виды регрессионного анализа бывают и как его осуществлять в Excel.

Какие бывают виды регрессионного анализа

Выделяют несколько видов регрессий:

1. Параболическая.
2. Степенная.
3. Логарифмическая.
4. Экспоненциальная.
5. Показательная.
6. Гиперболическая.
7. Линейная регрессия.

Давайте более подробно рассмотрим последнюю разновидность в программе для построения электронных таблиц Excel.

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

Давайте приведем небольшой пример. Допустим, у нас есть файл с диапазоном данных, содержащим информацию о том, какая средняя температура воздуха за окном в определенный временной период и сколько было покупателей в этот же день. Для этого нужно использовать регрессионный анализ, разобравшись, каким именно способом климатические условия (то есть, температура воздуха) оказывают влияние на то, как это торговое заведение посещается. Для этого нам нужно составить уравнение регрессии, которое выглядит так: У = а0 + а1х1 +…+акхк. Давайте приведем небольшую расшифровку этих данных.

1. Y. Обозначает переменную, которая зависима от определенных факторов. Именно ее нам и нужно проанализировать. В нашем примере в качестве такой переменной выступает количество покупателей.
2. х – это совокупность факторов, которые способны изменить значение переменной. В данном случае ею выступает температура воздуха. Но могут включаться и другие значения, которые могут быть измерены математическими.
3. а – это коэффициент регрессии. Необходим для того, чтобы формула могла определить не только наличие самого фактора, но и степень его влияния на переменную Y.
4. k – это общее число всех факторов, которые имеются на текущий момент.

Чтобы осуществить анализ линейной регрессии, необходимо выполнить следующие шаги:

1. Сделать клик по кнопке «Анализ данных», появившейся после добавления соответствующей надстройки. Она располагается на вкладке «Данные» в группе «Анализ».
2. После этого появится крошечное диалоговое окно. Но несмотря на это, оно содержит достаточное количество информации о том, какие инструменты анализа можно использовать. Нас же интересует регрессия. Соответствующий пункт и нужно выбрать. После того, как он будет выделен, можно нажимать кнопку «ОК».
3. После этого нам нужно настроить регрессию. В соответствующем диалоговом окне необходимо обязательно заполнить входные интервалы X и Y. К оставшимся параметрам, если их не заполнять, будут применены настройки, запрограммированные по умолчанию. В поле с входным интервалом Y записываем тот диапазон, в котором находятся переменные, для которых мы пытаемся установить влияние имеющихся факторов. Простыми словами, общее число покупателей. Есть несколько способов ввода адреса: с клавиатуры или же непосредственное их выделение с помощью мыши. Естественно, проще первый вариант в большинстве случаев, но если человек владеет слепым методом печати и точно помнит адрес диапазона, то вручную ему будет все же проще.

Далее вводим факторы (точнее, содержащие информацию о них ячейки) в поле «Входной интервал X». Как указывалось ранее, перед нами стоит задача понять, как влияет температура воздуха на количество клиентов. Для этого необходимо записать адреса ячеек, входящих в столбик «Температура». Как это сделать? Та точно так же, как и с предыдущим полем: ввести вручную или выделить соответствующий диапазон мышью.

Что касается других настроек, то они дают возможность задать метки, уровень надежности показателей, константу-ноль, а также задать ряд других параметров. Но в подавляющем количестве ситуаций нет необходимости корректировать эти настройки. Единственное, что нужно сделать – так это задать правильный переключатель для опции вывода результатов. По стандарту итоги выводятся на другой лист, но пользователь может, если у него будет такое желание, осуществить вывод на тот же лист, что и таблица с первоначальными данными. Также возможен вывод результатов в отдельную книгу. Наконец, после завершения настроек нужно нажать кнопку «ОК», после чего программа все оставшиеся действия выполнит самостоятельно.

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

Ознакомиться с результатами регрессионного анализа можно в том месте, которое было указано в параметрах. Выглядит он таким приблизительно образом.

Самое главное значение, на которое мы будем ориентироваться – это R-квадрат. В нем записывается качество используемой модели. Чем он выше, тем оно выше. Если оно меньше 0,5, то зависимость считается плохой, если выше – то уже лучше. Чем ближе к 1, тем лучше. Соответственно, максимальный коэффициент – 1.

Также нужно обратить внимание на еще один важный показатель. Его можно найти в ячейке, которая находится на стыке строки Y-пересечение и колонки «Коэффициенты». Здесь можно увидеть значение Y, которое будет равно нулю при определенных условиях. Также можно понять, насколько наша зависимая переменная является зависимой от факторов. Для этого нужно посмотреть, какая цифра стоит на пересечении граф Переменная X1» и «Коэффициенты». Чем коэффициент выше, тем лучше.

Видим, что программа Microsoft Excel открывает широкие возможности для регрессионного анализа. Но конечно, нужна дополнительная подготовка, чтобы читать эти результаты. Но если вы уже разбираетесь в статистике, то будет значительно проще. А теперь давайте приведем некоторые простые примеры, чтобы было более наглядно понятно, как линейная регрессия проводится на практике.

Пример регрессионного анализа №1

А теперь настало время разобрать практические кейсы, как можно использовать линейную регрессию. Допустим, у нас есть набор данных о расходах на ТВ-рекламу, интернет-продвижение и о том, сколько получилось реализовать товара в российской национальной валюте. Все эти данные упакованы в таблицу. Перед нами стоит задача – определить коэффициенты регрессии для независимых переменных (то есть, в нашем случае ими выступают расходы на рекламу по ТВ и в интернете, поскольку оба значения влияют на объем реализуемых товаров). Последовательность действий такая:

1. Открыть рабочий лист и ввести данные.
2. Активировать инструмент регрессия способом, описанным выше.
3. В появившемся диалоговом окне необходимо задать входной интервал X, Y, задать метки
4. Также не стоит забывать ввести выходной интервал. Для выполнения этой задачи необходимо также указать такие параметры, как «График нормальной вероятности» и «График остатков».

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

Пример регрессионного анализа №2

Второй случай, в котором можно проводить регрессионный анализ – это необходимость найти максимальную модель распределения расходов на разные виды рекламы для того, чтобы получить самую большую прибыль. И такую маркетинговую задачу вполне может решить обычный Excel, кто бы мог подумать?

Предположим, максимальный бюджет на рекламу, который может быть потрачен организацией – 170000 рублей. Это ограничение невозможно предусмотреть стандартным средством, описанным выше. Здесь нужно использовать совсем другую надстройку, которая называется «Поиск решения». Есть ее возможность найти в том же разделе, что и описываемую нами. И аналогично пакету анализа, нам необходимо включить эту надстройку в том же самом меню.

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

Точно также, как в случае с пакетом анализа, инструмент поиска решения требует наличия математической модели. В качестве неё и выступает целевая функция. В нашем случае она следующая: Y= 2102438,6 + 6,4004 X1 — 54,068 X2 > max. В качестве используемых ограничений используется следующее выражение: X1 + X2 = 0, X2 >=0.

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

1. Для начала нажать «Параметры Excel», после чего отправиться в категорию «Надстройки».
2. После этого в поле «Управление» найти «Надстройки Excel» и кликнуть по «Перейти».
3. После этого в списке надстроек активировать «Поиск решения».

После нажатия клавиши ОК надстройка успешно активирована. Далее достаточно просто нажимать на соответствующую кнопку на вкладке «Данные» в той же группе, что и пакет анализа и задать подходящие параметры. После этого программа все сделает самостоятельно. Таким образом, использование регрессии в Excel – очень простая штука. Значительно легче, чем может показаться на первый взгляд, поскольку большую часть действий выполняет программа. Достаточно просто вбить правильные настройки, и дальше можно расслабиться. И да, нужно еще интерпретировать результаты правильно. Но это не проблема. Успехов.