Как построить регрессионную модель в excel

современные информационные технологии / развитие инновационной экономики / возможности программы MS Excel / регрессионный анализ / подготовка специалистов. / modern information technologies / the development of an innovative economy / the capabilities of the MS Excel program / regression analysis / training of specialists.

Аннотация научной статьи по экономике и бизнесу, автор научной работы — Дилфуза Рустамовна Акабирходжаева

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

i Надоели баннеры? Вы всегда можете отключить рекламу.

Похожие темы научных работ по экономике и бизнесу , автор научной работы — Дилфуза Рустамовна Акабирходжаева

ОСОБЕННОСТИ ПОСТРОЕНИЯ СТОХАСТИЧЕСКОЙ МОДЕЛИ ОЦЕНКИ ПАРАМЕТРА НЕПРОИЗВОДИТЕЛЬНОГО ПОТЕРЯ

Получение регрессионных моделей в Microsoft Excel

СОВРЕМЕННОЕ СОСТОЯНИЕ И ПУТИ СОВЕРШЕНСТВОВАНИЯ СРЕДНЕГО СПЕЦИАЛЬНОГО И ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ В РЕСПУБЛИКИ УЗБЕКИСТАН

ФОРМИРОВАНИЕ ИНТЕЛЛЕКТУАЛЬНЫХ СПОСОБНОСТЕЙ УЧАЩИХСЯ ПРИ ПОДГОТОВКЕ ВСПОМОГАТЕЛЬНОГО ПЕРСОНАЛА ПО ХИМИИ

ОСОБЕННОСТИ ФУНКЦИОНИРОВАНИЯ ЭЛЕКТРОННЫХ ПЛАТЕЖЕЙ В БАНКОВСКОЙ СФЕРЕ РЕСПУБЛИКИ УЗБЕКИСТАН
ФУТБОЛЧИЛАРНИ ТАЙЁРЛАШ ЖАРАЁНИДАГИ МУСОБАҚАЛАР ФАОЛИЯТИ КЎРСАТКИЧЛАРИНИ ТАХЛИЛ ҚИЛИШНИНГ АХАМИЯТИ
i Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.
i Надоели баннеры?

Вы всегда можете отключить рекламу.

REGRESSION ANALYSIS IN MS EXCEL BY EXAMPLE

This article is devoted to the disclosure of the importance of the ability to apply the capabilities of the MS Excel program for a modern specialist in the field of economics, indicates the need to pay sufficient attention to this program in the system of Higher education. This article also provides a detailed regression analysis in MS Excel for a specific economic problem.

Текст научной работы на тему «РЕГРЕССИОННЫЙ АНАЛИЗ В MS EXCEL НА ПРИМЕРЕ»

Academic Research in Educational Sciences VOLUME 2 | ISSUE 11 | 2021

Scientific Journal Impact Factor (SJIF) 2021: 5.723 Directory Indexing of International Research Journals-CiteFactor 2020-21: 0.89

РЕГРЕССИОННЫЙ АНАЛИЗ В MS EXCEL НА ПРИМЕРЕ

Дилфуза Рустамовна Акабирходжаева

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

Ключевые слова: современные информационные технологии, развитие инновационной экономики, возможности программы MS Excel, регрессионный анализ, подготовка специалистов.

Парная регрессия: линейная зависимость

REGRESSION ANALYSIS IN MS EXCEL BY EXAMPLE

This article is devoted to the disclosure of the importance of the ability to apply the capabilities of the MS Excel program for a modern specialist in the field of economics, indicates the need to pay sufficient attention to this program in the system of Higher education. This article also provides a detailed regression analysis in MS Excel for a specific economic problem.

Keywords: modern information technologies, the development of an innovative economy, the capabilities of the MS Excel program, regression analysis, training of specialists.

Развитие инновационной экономики немыслимо без эффективного применения современных информационно-коммуникационных технологий (ИКТ).

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

Academic Research in Educational Sciences VOLUME 2 | ISSUE 11 | 2021

Scientific Journal Impact Factor (SJIF) 2021: 5.723 Directory Indexing of International Research Journals-CiteFactor 2020-21: 0.89

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

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

Опережающее развитие сектора ИКТ является одним из приоритетов экономической политики Узбекистана.

ЛИТЕРАТУРА И МЕТОДОЛОГИЯ

Опережающее развитие сектора ИКТ является одним из приоритетов экономической политики Узбекистана. В указе Президента Республики Узбекистан Ш.М. Мирзиёева «Стратегия действий по пяти приоритетным направлениям развития Республики Узбекистан в 2017-2021 года» в третьем приоритетном направлении развития и либерализации экономики отмечается, что необходимо широкое «внедрение информационно-коммуникационных технологий в экономику, социальную сферу, системы управления» [2].

В этой связи, Президент Республики Узбекистан Шавкат Миромонович Мирзиёев в своем Указе «О мерах по дальнейшему совершенствованию сферы информационных технологий и коммуникаций» отметил: «В стране проводится последовательная работа по развитию современных информационных технологий и коммуникаций, созданию целостной системы оказания электронных государственных услуг, внедрению новых механизмов диалога государственных органов с населением» [3].

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

Academic Research in Educational Sciences VOLUME 2 | ISSUE 11 | 2021

Scientific Journal Impact Factor (SJIF) 2021: 5.723 Directory Indexing of International Research Journals-CiteFactor 2020-21: 0.89

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

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

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

Кроме того, отфильтрованную информацию можно представить в виде таблицы, структуру которой определит пользователь, а при необходимости ее (структуру) можно видоизменить в любой момент. Для такой процедуры используется команда «сводная таблица».

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

Academic Research in Educational Sciences VOLUME 2 | ISSUE 11 | 2021

Scientific Journal Impact Factor (SJIF) 2021: 5.723 Directory Indexing of International Research Journals-CiteFactor 2020-21: 0.89

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

В настоящее время немаловажным является приведение большого объема информации в надлежащий вид, выделение из этого потока только необходимого. MS Excel помогает пользователю осуществить все необходимые операции и посмотреть любую информацию в нужном аспекте [1].

Рассмотрим более подробно возможности MS Excel и их применение на практике при решении экономических задач.

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

Результат анализа позволяет выделять приоритеты, и, основываясь на главных факторах, результат анализа позволяет прогнозировать, планировать развитие приоритетных направлений, принимать управленческие решения [4].

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

• линейной (у = а + bx);

• параболической (y = a + bx + cx );

• экспоненциальной (y = a * exp(bx));

• степенной (y = a*xAb);

• гиперболической (y = b/x + a);

• логарифмической (y = b * 1n(x) + a);

• показательной (y = a * bAx).

Рассмотрим на примере построение регрессионной модели в MS Excel и интерпретацию результатов. Возьмем линейный тип регрессии.

Google Scholar Scientific Library of Uzbekistan

Academic Research, Uzbekistan 1162 www.ares.uz

Academic Research in Educational Sciences VOLUME 2 | ISSUE 11 | 2021

Scientific Journal Impact Factor (SJIF) 2021: 5.723 Directory Indexing of International Research Journals-CiteFactor 2020-21: 0.89

Пример. На 6 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты (Рис. 1).

1 уволенных плата

Рис. 1 Исходные данные

Модель линейной регрессии имеет следующий вид:

где at — коэффициенты регрессии, xt — влияющие переменные, к — число факторов.

В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор — заработная плата (х).

В MS Excel существуют встроенные функции, с помощью которых можно рассчитать параметры модели линейной регрессии. Но быстрее это сделает надстройка «Пакет анализа».

1. Открываем меню инструмента «Анализ данных» (Рис. 2).

IHLDnUC — l_A.L. CI

ДАННЫЕ Зчистить Повторить Дополнительно столбцам ьтр

Мгновенноезаполнение 0*0 Удал ить дубликаты Проверка данных т

Работа с данными

нализ что если ошения

И Группировать т □1 Разгруппировать т Щ Промежуточный итог Структура

Поиск решения ^^т^Анализ даннызГ^

Рис. 2 Команда Анализ данных

2. Выбираем «Регрессия» (Рис. 3).

Academic Research in Educational Sciences VOLUME 2 | ISSUE 11 | 2021

Scientific Journal Impact Factor (SJIF) 2021: 5.723 Directory Indexing of International Research Journals-CiteFactor 2020-21: 0.89

Рис. 3 Диалоговое окно Анализ данных

3. Откроется меню для выбора входных значений и параметров вывода (где отобразить результат). В полях для исходных данных указываем диапазон описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять (Рис. 4).

i Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.

Рис. 4 Диалоговое окно Регрессия

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

В первую очередь обращаем внимание на Я-квадрат и коэффициенты (Рис. 5).

Я-квадрат — коэффициент детерминации. Коэффициент детерминации (Я-квадрат) оценивает «силу влияния» переменной х на у.

Я-квадрат > 0,95 — высокая точность аппроксимации;

В нашем примере — 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. В нашем примере мы получили удовлетворительный результат.

Academic Research in Educational Sciences VOLUME 2 | ISSUE 11 | 2021

Scientific Journal Impact Factor (SJIF) 2021: 5.723 Directory Indexing of International Research Journals-CiteFactor 2020-21: 0.89

Множественный В О,ВЙВ73О мерах по дальнейшему совершенствованию сферы информационных технологий и коммуникаций»// Народное слово. Ташкент, 2018 г., 20 февраля

4. Каплан А.В., Каплан В.Е., Мащенко М.В., Овечкин Е.В. Решение экономических задач на компьютере — М.: ДМК Пресс: СПб.Питер, 2004.- 594с

Источник: cyberleninka.ru

Как добавить линейную регрессию в графики Excel

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

Добавление линии тренда линейной регрессии на график

Сначала откройте пустую электронную таблицу Excel, выберите ячейку D3 и введите «Месяц» в качестве заголовка столбца, который будет переменной x. Затем щелкните ячейку E3 и введите «Значение Y» в качестве заголовка столбца переменной y. Это в основном таблица с записанным рядом значений данных за месяцы январь-май. Поэтому введите месяцы в ячейки от D4 до D8 и значения данных для них в ячейки от E4 до E8, как показано на снимке непосредственно ниже.

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

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Теперь вы можете настроить график рассеяния для этой таблицы. Выделите все ячейки в таблице с помощью курсора. Нажмите вкладку Вставка и выберите рассеивать > Скаттер только с маркерами добавить график в электронную таблицу, как показано ниже. Кроме того, вы можете нажать горячую клавишу Alt + F1, чтобы вставить гистограмму. Затем вы должны щелкнуть правой кнопкой мыши по графику и выбрать Изменить тип диаграммы > X Y (разброс) > Скаттер только с маркерами,

линейная регрессия2

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

линейная регрессия3

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

линейная регрессия4

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

Форматирование линии тренда линейной регрессии

Чтобы отформатировать линию тренда, щелкните ее правой кнопкой мыши и выберите Формат Trendline, Это снова откроет окно Format Trendline, из которого вы можете нажать Цвет линии, Выбрать Сплошная линия и нажмите цвет поле, чтобы открыть палитру, из которой вы можете выбрать альтернативный цвет для линии тренда.

Чтобы настроить стиль линии, перейдите на вкладку «Стиль линии». Затем вы можете настроить ширину стрелки и настроить параметры стрелки. нажмите Настройки стрелки кнопки для добавления стрелок в линию.

линейная регрессия5

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

линейная регрессия6

Прогнозирование значений с помощью линейной регрессии

После того, как вы отформатировали линию тренда, вы также можете прогнозировать будущие значения с ней. Например, предположим, вам нужно спрогнозировать значение данных через три месяца после мая за август, которое не включено в нашу таблицу. Затем вы можете щелкнуть «Параметры линии тренда» и ввести «3» в текстовом поле «Вперед». Линия тренда линейной регрессии подчеркивает, что значение августа, вероятно, будет чуть выше 3500, как показано ниже.

линейная регрессия7

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

Чтобы добавить значение квадрата r на график, щелкните Отображение значения R-квадрата на графике флажок Это добавляет г квадрат на график чуть ниже уравнения, как на снимке ниже. Вы можете перетащить поле уравнения и корреляции, чтобы изменить его положение на графике рассеяния.

линейная регрессия8

Функции линейной регрессии

Excel также включает функции линейной регрессии, с помощью которых вы можете найти значения наклона, точки пересечения и r квадрата для массивов данных y и x. Выберите ячейку электронной таблицы, чтобы добавить одну из этих функций, а затем нажмите Вставить функцию кнопка. Функции линейной регрессии являются статистическими, поэтому выберите статистическая из выпадающего меню категории. Затем вы можете выбрать RSQ, SLOPE или INTERCEPT чтобы открыть их окна функций, как показано ниже.

линейная регрессия9

Окна RSQ, SLOPE и INTERCEPT практически одинаковы. Они включают в себя поля Known_y и Known_x, которые можно выбрать для добавления значений переменных y и x из вашей таблицы. Обратите внимание, что ячейки должны содержать только цифры, поэтому замените месяцы в таблице соответствующими цифрами, такими как 1 для января, 2 для февраля и т. Д. Затем нажмите Хорошо закрыть окно и добавить функцию в электронную таблицу.

линейная регрессия10

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

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Источник: autotak.ru

Прогнозирование с помощью линейной регрессии в Microsoft Excel — inc. Сезонность и рост

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

Рейтинг
( Пока оценок нет )
Загрузка ...
Заработок в интернете или как начать работать дома