Как выполнить логистическую регрессию в Excel
Логистическая регрессия — это метод, который мы используем для подбора регрессионной модели, когда переменная ответа является бинарной.
В этом руководстве объясняется, как выполнить логистическую регрессию в Excel.
Пример: логистическая регрессия в Excel
Используйте следующие шаги, чтобы выполнить логистическую регрессию в Excel для набора данных, который показывает, были ли баскетболисты колледжей выбраны в НБА (драфт: 0 = нет, 1 = да) на основе их среднего количества очков, подборов и передач в предыдущем время года.
Шаг 1: Введите данные.
Сначала введите следующие данные:
Шаг 2: Введите ячейки для коэффициентов регрессии.
Поскольку в модели у нас есть три объясняющие переменные (pts, rebs, ast), мы создадим ячейки для трех коэффициентов регрессии плюс один для точки пересечения в модели. Мы установим значения для каждого из них на 0,001, но мы оптимизируем их позже.
Далее нам нужно будет создать несколько новых столбцов, которые мы будем использовать для оптимизации этих коэффициентов регрессии, включая логит, e логит , вероятность и логарифмическую вероятность.
Шаг 3: Создайте значения для логита.
Далее мы создадим столбец logit, используя следующую формулу:
Шаг 4: Создайте значения для e logit .
Далее мы создадим значения для e logit , используя следующую формулу:
Шаг 5: Создайте значения для вероятности.
Далее мы создадим значения вероятности, используя следующую формулу:
Шаг 6: Создайте значения для логарифмической вероятности.
Далее мы создадим значения для логарифмической вероятности, используя следующую формулу:
Логарифмическая вероятность = LN (вероятность)
Шаг 7: Найдите сумму логарифмических вероятностей.
Наконец, мы найдем сумму логарифмических правдоподобий, то есть число, которое мы попытаемся максимизировать, чтобы найти коэффициенты регрессии.
Шаг 8: Используйте Решатель, чтобы найти коэффициенты регрессии.
Если вы еще не установили Solver в Excel, выполните следующие действия:
- Щелкните Файл .
- Щелкните Параметры .
- Щелкните Надстройки .
- Нажмите Надстройка «Поиск решения» , затем нажмите «Перейти» .
- В новом всплывающем окне установите флажок рядом с Solver Add-In , затем нажмите «Перейти» .
После установки Солвера перейдите в группу Анализ на вкладке Данные и нажмите Солвер.Введите следующую информацию:
- Установите цель: выберите ячейку H14, содержащую сумму логарифмических вероятностей.
- Путем изменения ячеек переменных: выберите диапазон ячеек B15:B18, который содержит коэффициенты регрессии.
- Сделать неограниченные переменные неотрицательными: снимите этот флажок.
- Выберите метод решения: выберите GRG Nonlinear.
Затем нажмите «Решить» .
Решатель автоматически вычисляет оценки коэффициента регрессии:
По умолчанию коэффициенты регрессии можно использовать для определения вероятности того, что осадка = 0. Однако обычно в логистической регрессии нас интересует вероятность того, что переменная отклика = 1. Таким образом, мы можем просто поменять знаки на каждом из коэффициенты регрессии:
Теперь эти коэффициенты регрессии можно использовать для определения вероятности того, что осадка = 1.
Например, предположим, что игрок набирает в среднем 14 очков за игру, 4 подбора за игру и 5 передач за игру. Вероятность того, что этот игрок будет выбран в НБА, можно рассчитать как:
P(draft = 1) = e 3,681193 + 0,112827*(14) -0,39568*(4) – 0,67954*(5) / (1+e 3,681193 + 0,112827*(14) -0,39568*(4) – 0,67954*(5) ) ) = 0,57 .
Поскольку эта вероятность больше 0,5, мы прогнозируем, что этот игрокпопасть в НБА.
Типы линейчатых диаграмм в Excel
Создавая линейчатую диаграмму в Excel, можно выбрать один из следующих подтипов:
Линейчатая с группировкой
Линейчатая диаграмма с группировкой (2-D или 3-D) сравнивает значения в категориях данных. В линейчатой диаграмме с группировкой категории обычно построены по вертикальной оси (ось Y), а значения – по горизонтальной (ось X). 3-D линейчатая диаграмма с группировкой не отображает третью ось, а просто делает полосы графика объёмными.
Линейчатая с накоплением
Линейчатая диаграмма с накоплением показывает пропорции отдельных элементов в отношении к целому. Как и линейчатая диаграмма с группировкой, она может быть плоской (2-D) или объёмной (3-D):
Нормированная линейчатая с накоплением
Этот тип линейчатой диаграммы похож на предыдущий, но показывает долю в процентах каждого элемента относительно целого для каждой категории данных.
Цилиндры, конусы и пирамиды
Кроме стандартных прямоугольников, для построения всех перечисленных подтипов линейчатой диаграммы можно использовать цилиндры, конусы или пирамиды. Разница только в форме фигуры, которая отображает исходные данные.
В Excel 2010 и более ранних версиях диаграмму с цилиндрами, конусами или пирамидами можно было построить, выбрав соответствующий тип графика на вкладке Вставка (Insert) в разделе Диаграммы (Charts).
На Ленте меню Excel 2013 и Excel 2016 не предлагается использовать цилиндры, конусы или пирамиды. Как сообщает Microsoft, эти типы диаграмм были убраны, поскольку слишком большое разнообразие типов диаграмм в ранних версиях Excel затрудняло пользователю выбор нужного типа. Тем не менее, возможность использовать цилиндр, конус или пирамиду доступна и в современных версиях Excel, правда для этого потребуется сделать несколько дополнительных действий.
Как в Excel 2013 и 2016 при построении диаграммы использовать цилиндр, конус или пирамиду
Чтобы в Excel 2013 и 2016 при построении диаграммы использовать цилиндр, конус или пирамиду, создайте объёмную линейчатую диаграмму нужного типа (, или ) и затем измените фигуры, использованные для построения рядов:
- Выделите все полосы на диаграмме, кликните по ним правой кнопкой мыши и в контекстном меню нажмите Формат ряда данных (Format Data Series), либо просто дважды кликните по полосе графика.
- В появившейся панели в разделе Параметры ряда (Series Options) выберите подходящую Фигуру (Column shape).
Замечание: Если на диаграмме построено несколько рядов данных, то описанную процедуру необходимо повторить для каждого ряда в отдельности.
Линейчатая диаграмма
В Excel 2007 имеется такой тип. По сути, это столбчатая гистограмма, где столбики повернуты горизонтально. Столбчатая гистограмма и линейчатая диаграмма взаимозаменяемы. Они применяются для анализа изменений показателя за определенный промежуток времени.
Каждый столбик (прямоугольник) показывает величину уровня в анализируемом статистическом ряду. Сравниваемые данные имеют одинаковые единицы измерения. Поэтому удается проанализировать рассматриваемый процесс.
На основе уже имеющихся данных по численности населения построим линейчатую диаграмму.
Обратите внимание: так как столбики имеют горизонтальное направление, диаграмма как будто ложится на бок
Теперь годы – это подписи вертикальной оси, а численность – горизонтальной. Но при выборе источника данных для диаграммы нужно придерживаться прежнего порядка:
Значения для категорий и рядов должны остаться теми же.
Как построить целевую линию с разными значениями
В ситуациях, когда вы хотите сравнить фактические значения с оценочными или целевыми значениями, разными для каждой строки, описанный выше метод не очень эффективен. Линия не позволяет точно указать целевые значения, в результате чего вы можете неверно интерпретировать информацию на графике:
Чтобы более четко визуализировать целевые значения, вы можете отобразить их следующим образом:
Чтобы добиться этого эффекта, добавьте линию на диаграмму, как описано в предыдущих примерах, а затем выполните следующие настройки:
- На графике дважды щелкните целевую линию. Это выделит строку и откроет Серия данных формата панель в правой части окна Excel.
- На Серия данных формата панель, перейти к Заполнение и линия вкладка > Линия и выберите Нет линии.
- Переключиться на Маркер раздел, развернуть Параметры маркераизмените его на Встроенный, выберите горизонтальную полосу в Тип поле и установите Размер соответствует ширине ваших полос (24 в нашем примере):
- Установите маркер Заливка на Сплошная заливка или же Заливка узором и выберите нужный цвет.
- Установите маркер границы на Сплошная линия а также выбрать нужный цвет.
На скриншоте ниже показаны мои настройки:
Создание линейного графика — короткая версия
Шаги по добавлению базового линейного графика или линейной диаграммы в рабочий лист Excel:
- Выделите данные, которые должны быть включены в график — включите заголовки строк и столбцов, но не заголовок таблицы данных.
- Нажми на Вставить вкладку ленты.
- В разделе «Графики» ленты нажмите Вставить линейную диаграмму , чтобы открыть раскрывающийся список доступных типов диаграмм / диаграмм.
- Наведите указатель мыши на тип диаграммы, чтобы прочитать описание диаграммы / графика.
- Нажмите на нужный граф.
На текущий лист будет добавлен простой, неформатированный граф — тот, который отображает только строки, представляющие выбранную серию данных, название графика по умолчанию, легенду и значения осей.
Диаграмма Венна
Это визуализация данных с помощью пересекающихся кругов. Так можно показать несколько множеств и их пересечения.
Диаграмма показывает, из чего складывается лидерствоИзображение: Wepik
Пересечения показывают, сколько общего есть у этих множеств. Так можно оформить результаты какого-нибудь опроса или показать, какой будет работа, выполненная быстро и дёшево. Можно показывать не только пересечения, но и полную принадлежность одного множества к другому: помещая один маленький круг в более крупный.
Диаграмма показывает государства Британских острововИзображение: ConceptDraw
Простейший график изменений
График нужен тогда, когда необходимо показать изменения данных. Начнем с простейшей диаграммы для демонстрации событий в разные промежутки времени.
Допустим, у нас есть данные по чистой прибыли предприятия за 5 лет:
| Год | Чистая прибыль* |
| 2010 | 13742 |
| 2011 | 11786 |
| 2012 | 6045 |
| 2013 | 7234 |
| 2014 | 15605 |
* Цифры условные, для учебных целей.
Заходим во вкладку «Вставка». Предлагается несколько типов диаграмм:
Выбираем «График». Во всплывающем окне – его вид. Когда наводишь курсор на тот или иной тип диаграммы, показывается подсказка: где лучше использовать этот график, для каких данных.
Выбрали – скопировали таблицу с данными – вставили в область диаграммы. Получается вот такой вариант:
Прямая горизонтальная (синяя) не нужна. Просто выделяем ее и удаляем. Так как у нас одна кривая – легенду (справа от графика) тоже убираем. Чтобы уточнить информацию, подписываем маркеры. На вкладке «Подписи данных» определяем местоположение цифр. В примере – справа.
Улучшим изображение – подпишем оси. «Макет» – «Название осей» – «Название основной горизонтальной (вертикальной) оси»:
Заголовок можно убрать, переместить в область графика, над ним. Изменить стиль, сделать заливку и т.д. Все манипуляции – на вкладке «Название диаграммы».
Вместо порядкового номера отчетного года нам нужен именно год. Выделяем значения горизонтальной оси. Правой кнопкой мыши – «Выбрать данные» — «Изменить подписи горизонтальной оси». В открывшейся вкладке выбрать диапазон. В таблице с данными – первый столбец. Как показано ниже на рисунке:
Можем оставить график в таком виде. А можем сделать заливку, поменять шрифт, переместить диаграмму на другой лист («Конструктор» — «Переместить диаграмму»).
Вычисление значений функции
Нужно вычислить значения функции в данных точках. Для этого в ячейке В2 создадим формулу, соответствующую заданной функции, только вместо x будем вводить значение переменной х, находящееся в ячейке слева (-5).
Важно: для возведения в степень используется знак ^, который можно получить с помощью комбинации клавиш Shift+6 на английской раскладке клавиатуры. Обязательно между коэффициентами и переменной нужно ставить знак умножения * (Shift+8)
Ввод формулы завершаем нажатием клавиши Enter. Мы получим значение функции в точке x=-5. Скопируем полученную формулу вниз.
Мы получили последовательность значений функции в точках на промежутке с шагом 1.
Как в офисе…
Использование диаграмм Excel — хороший способ отображения графиков математических и тригонометрических функций. В этой статье описываются два метода построения графика функции: с одной переменной с помощью точечной диаграммы и с двумя переменными с помощью 3D-диаграммы.
Построение графиков математических функций с одной переменной
Точечная диаграмма (известная как диаграмма XY в предыдущих версиях Excel) отображает точку (маркер) для каждой пары значений. Например, на рис. 140.1 показан график функции SIN. На диаграмму наносятся рассчитанные значения у для значений х (в радианах) от −5 до 5 с инкрементом (приращением) 0,5. Каждая пара значений х и у выступает в качестве точки данных в диаграмме, и эти точки связаны линиями.
Функция выражается в таком виде: у = SIN(x).
Соответствующая формула в ячейке В2 (которая копируется в ячейки, расположенные ниже) будет следующей: =SIN(A2).
Чтобы создать эту диаграмму, выполните следующие действия.
- Выделите диапазон А1:В22.
- Выберите Вставка ► Диаграммы ► Точечная ► Точечная с прямыми отрезками и маркерами.
- Выберите макет диаграммы, который вам нравится, а затем настройте его.
Измените значения в столбце А для построения графика функции при различных значениях х. И, конечно, вы можете использовать любую формулу с одной переменной в столбце В. Вот несколько примеров, которые приводят к построению интересных графиков: =SIN(ПИ()*A2)*(ПИ()*A2) =SIN(A2)/A2 =SIN(A2^3)*COS(A2^2) =НОРМ.РАСП(A2;0;1;ЛОЖЬ)
Чтобы получить более точную диаграмму, увеличьте количество значений для построения графика и сделайте приращение в столбце А меньше.
Вы можете использовать онлайн наш файл примера графиков математических функций с одной переменной, расположенной в Excel Web Apps при помощи Skydrive, и внести свои данные (изменения не будут сохраняться) или скачать себе на компьютер, для чего необходимо кликнуть по иконке Excel в правом нижнем углу. Это бесплатно ?
Построение графиков математических функций с двумя переменными
Вы также можете строить графики функций, которые используют две переменные. Например, следующая функция рассчитывает z для различных значений двух переменных (х и у): =SIN($A2)*COS($B1)
На рис. 140.2 приведена поверхностная диаграмма, которая рассчитывает значение z для 21 значения х в диапазоне от −3 до 0 и для 21 значения у в диапазоне от 2 до 5. Для х и у используется приращение 0,15.
Значения х находятся в диапазоне А2:А22, а значения у — в диапазоне B1:V1.
Формула в ячейке В2 копируется в другие ячейки таблицы и имеет следующий вид: =SIN($A2)*C0S(B$1).
Чтобы создать диаграмму, выполните приведенные ниже действия.
- Выделите диапазон A1:V22.
- Выберите Вставка ► Диаграммы ► Другие ► Поверхность.
- Выберите макет диаграммы, который вам нравится, а затем настройте его.
Пока значения х и у имеют равные приращения, вы можете задавать любую формулу с двумя переменными. Вам, возможно, потребуется настроить начальные значения и значение приращения для х и у. Для увеличения сглаживания используйте больше значений х и у при меньшем приращении. Вот другие формулы, которые вы можете попробовать: =SIN(КОРЕНЬ($A2^2+B$1^2)) =SIN($A2)*COS($A2*B$1) =COS($A2*B$1)
Гистограммы
Гистограммы подходят для данных, которые меняются с течением времени или группируются в категории — например, по отраслям или типам продуктов. Сделать гистограмму более понятной помогут следующие советы.
- Столбцы лучше расположить в хронологическом порядке.
- Используйте одну ось для обозначения времени, а вторую — для обозначения количества.
- Не упорядочивайте данные от большего к меньшему или наоборот: зрителю будет удобнее смотреть на изменения в хронологическом порядке.
В гистограммах с несколькими видами информации можно создавать отдельные столбцы для каждого из них или накладывать их друг на друга. Так сделано в примере ниже, изображающем динамику поставок устройств с поддержкой Bluetooth по всему миру (в млрд).
Если данные сгруппированы в несколько категорий и не связаны по времени, стоит упорядочить столбцы от большего к меньшему или наоборот. Этот способ организации помогает зрителям быстро делать выводы. Но если все цифры суммируются — например, как прибыль от продажи разных товаров — гистограмма этого не покажет. Для этого лучше использовать круговую диаграмму, подробнее о которой будет рассказано позже.
Сервисы, которые упростят работу
Как правило, графики никто не рисует руками — для этого используют специальные сервисы:
- плагины в Figma: Chart Maker, Charts или NB Charts;
- сервис Datawrapper: в него можно импортировать свои данные и строить даже сложные диаграммы — а потом скачивать их в PNG и использовать в своём дизайне;
- Raw Graphs — аналогичный инструмент: можно создавать диаграммы и скачивать в разных форматах;
- ChartBlocks — онлайн-сервис для визуализации данных, где чуть меньше видов диаграмм, зато больше возможностей для кастомизации их дизайна;
- Google Sheets и Excel — помогут вам сделать график из данных прямо в вашей таблице;
- инструменты для создания презентаций — у нас есть отдельная подборка.
Не забывайте про существование обычных таблиц и схем. Ими часто можно заменить график, а бывает, что и вовсе достаточно абзаца текста с нужными цифрами. Диаграммы хороши, чтобы наглядно показать данные, но простые решения иногда работают не хуже.
Больше интересного про дизайн в нашем телеграм-канале. Подписывайтесь!
Вычисление значений функции
Нужно вычислить значения функции в данных точках. Для этого в ячейке В2 создадим формулу, соответствующую заданной функции, только вместо x будем вводить значение переменной х, находящееся в ячейке слева (-5).
Важно: для возведения в степень используется знак ^, который можно получить с помощью комбинации клавиш Shift+6 на английской раскладке клавиатуры. Обязательно между коэффициентами и переменной нужно ставить знак умножения * (Shift+8)
Ввод формулы завершаем нажатием клавиши Enter. Мы получим значение функции в точке x=-5. Скопируем полученную формулу вниз.
Мы получили последовательность значений функции в точках на промежутке с шагом 1.
