Задачи по эконометрике в Excel с решениями

Обновлено: 21.11.2024

Некоторые функции Excel для статистического анализа

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

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

< td>VAR.P

Функция	Что она вычисляет
СРЕДНЕЕ	Среднее набор чисел
СРЗНАЧЕСЛИ	Среднее значение набора чисел, удовлетворяющих условию
AVERAGEIFS	Среднее значение набора чисел, удовлетворяющих одному или нескольким условиям
HARMEAN	Гармоническое среднее набора положительных чисел
GEOMEAN	Среднее геометрическое набора положительных чисел
MODE.SNGL	Мода набора чисел
МЕДИАНА	Медиана набора чисел
Дисперсия набора чисел, рассматриваемого как совокупность
VAR.S	Дисперсия набор чисел, считающийся выборкой
STDEV.P	Стандартное отклонение набора чисел, считающегося совокупностью
STDEV.S	Стандартное отклонение набора чисел, считающегося образцом
S ТАНДАРТИЗАЦИЯ	Стандартная оценка, основанная на данном среднем значении и стандартном отклонении

Эти удобные функции для определения относительного положения также могут быть очень полезными:

Функция	Что она вычисляет
RANK.EQ	Ранг числа в множестве чисел. Если несколько чисел имеют одинаковый ранг, возвращается наивысший ранг этих чисел.
RANK.AVG	Ранг числа в наборе чисел. Если несколько чисел имеют одинаковый ранг, возвращается их среднее значение.
PERCENTRANK.INC	Ранг числа в наборе чисел, выраженный в процентах от чисел, которым оно больше или равно.
PERCENTRANT.EXC	Ранг числа в наборе чисел, выраженный как процент от чисел, которых он больше.
PERCENTILE.INC	Указанный процентиль в наборе чисел с точки зрения «больше или равно .”
PERCENTILE.EXC	Указанный процентиль в наборе чисел с точки зрения «больше чем».
QUARTILE.INC	1-й, 2-й, 3-й или 4-й квартиль набора чисел с точки зрения «больше или равно».
QUARTILE.EXC	1-й, 2-й, 3-й или 4-й квартиль набора чисел с точки зрения «больше чем».

Эти функции для корреляции и регрессии также полезно знать:

< /tbody>

Функция	Что она вычисляет
CORREL	Коэффициент корреляции между двумя наборами чисел
PEARSON	То же, что и CORREL. (Поймите!)
RSQ	Коэффициент детерминации между двумя наборами чисел (квадрат коэффициента корреляции)
НАКЛОН	Наклон линии регрессии через два набора чисел
ПЕРЕХОД	Пересечение регрессии линия через два набора чисел
STEYX	Стандартная ошибка оценки для линии регрессии через два набора чисел

Функции массива Excel для статистического анализа

Формула массива вычисляет набор значений, а не одно. Вот функции статистического массива Excel. Каждый из них возвращает массив значений в выбранный массив ячеек.

В версии Excel для Microsoft 365 вы выбираете одну ячейку, вводите формулу, нажимаете клавишу ВВОД, и оставшиеся значения переносятся в оставшиеся ячейки массива.

< td width="144">FREQUENCY

Функция	Вычисляет массив …
Частоты значений в наборе значений
MODE.MULT	Состояния набора чисел
ЛИНЕЙН	Статистика регрессии на основе линейной регрессии по двум или более наборам чисел
LOGEST	Статистика регрессии на основе криволинейной регрессия по двум или более наборам чисел
TREND	Числа в линейном тренде на основе известных данных точки
РОСТ	Числа в криволинейном тренде, основанные на известных точках данных

Инструменты анализа данных Excel

Excel Analysis ToolPak – это полезная надстройка, предоставляющая обширный набор инструментов статистического анализа. Вот некоторые из инструментов в ToolPak.Обратите внимание, что последний инструмент, Логистическая регрессия, находится в пакете инструментов анализа XLMiner, а не в Excel.

< tr>

Инструмент	Что он делает
Anova: единый фактор	Дисперсионный анализ для двух или более выборок
Anova: двухфакторный анализ с повторением	Дисперсионный анализ с двумя независимыми переменными и несколькими наблюдениями в каждой комбинации уровней переменных.
Anova: два фактора без повторения	Дисперсионный анализ с двумя независимыми переменными и одним наблюдением в каждой комбинации уровни переменных. Вы можете использовать его для повторных измерений ANOVA.
Корреляция	При наличии более двух измерений в выборке отдельных лиц вычисляет матрицу коэффициентов корреляции для всех возможные пары измерений
Ковариация	При наличии более двух измерений в выборке индивидуумов вычисляет матрицу ковариаций для всех возможных пар измерений
Описательная статистика	Создает отчет о центральной тенденции, изменчивости и других характеристиках значений в выбранном диапазоне ячеек
Экспоненциальное сглаживание	В последовательности значений вычисляет прогноз на основе предыдущего набора значений и предыдущего прогноза для этих значений
F-тест двух выборок для дисперсий	Выполняет F-тест для сравнения двух дисперсий
Гистограмма	Табулирует индивидуальные и совокупные частоты значений в выбранном диапазоне ячеек
Скользящее среднее	В последовательности значений вычисляет прогноз, который представляет собой среднее значение заданного числа предыдущих значений
Генерация случайных чисел	Предоставляет заданное количество случайных чисел, сгенерированных из одного из семи возможных распределений
Ранг и процентиль	Создает таблица, которая показывает порядковый ранг и процентный ранг каждого значения в наборе значений
Регрессия	Создает отчет о статистике регрессии на основе линейная регрессия по набору данных, содержащих одну зависимую переменную и одну или несколько независимых переменных
Выборка	Создает выборку из значений в указанном диапазоне ячейки

Логистическая регрессия (в пакете анализа XLMiner, а не в пакете анализа Excel) создает отчет о статистике регрессии на основе логистической регрессии с помощью набора данных, состоящего из количественной независимой переменной и зависимой переменной, значения которой могут быть только 0 или 1. .

Об этой статье

Эта статья взята из книги:

Об авторе книги:

Джозеф Шмуллер, доктор философии, научный сотрудник Университета Северной Флориды. Он является бывшим членом Американской статистической ассоциации и преподавал статистику на уровне бакалавриата, бакалавриата с отличием и магистратуры.

Пользователи Excel давно и успешно применяют эту программу для решения различного рода задач в разных областях.

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

Решение проблем с оптимизацией данных

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

Следующие команды используются для решения задач оптимизации в Excel:

Менеджер сценариев («ДАННЫЕ» — «Анализ «что, если»» — «Менеджер сценариев») анализирует несколько вариантов исходных значений, создает и оценивает наборы сценариев.
Подбор параметров («ДАННЫЕ» — «Анализ «что, если»» — «Поиск цели») помогает найти значения, обеспечивающие желаемый результат.
Поиск решения (надстройка Microsoft Excel, "ДАННЫЕ" - "Поиск решения") вычисляет оптимальное значение с учетом переменных и ограничений. Перейдите по ссылке и узнайте, как подключить параметр «Поиск решения».

Команда "Подбор параметров" используется для решения простейших задач. Вы используете «Менеджер сценариев» для самых сложных задач. Рассмотрим пример решения оптимизационной задачи с помощью модуля "Решатель".

Условие. Компания производит несколько марок йогурта. Назовем их «1», «2» и «3». Предприятие получает 200 рублей, продав 100 банок йогурта "1". Марка «2» принесла 250 руб. «3» - 300 руб.Продажи хорошо налажены, но количество доступного сырья ограничено. Вам нужно выяснить, какой йогурт и в каком количестве вам нужно сделать, чтобы получить максимальный доход от продаж.

Известные данные (включая норму расхода сырья) будут перечислены в таблице:

На основе этих данных мы составим рабочую таблицу:

Мы до сих пор не знаем количество продуктов. Это переменные.
В столбце "Прибыль" записаны следующие формулы: С11:= 200*В11, С12:= 250*В12, С13:= 300*В13, С14: =СУММ(С11:С13).
Потребление сырья ограничено (это ограничения). В ячейки добавляются следующие формулы:
молоко А18: кефир В18: яйцо С18: сахар D18: То есть мы умножили норму расхода на количество.
Цель состоит в том, чтобы найти максимально возможную прибыль. Это ячейка C14.

Активируйте команду "Решатель" и введите параметры.

Программа выдает свое решение после нажатия кнопки "Решить".

Лучший вариант — сконцентрироваться на приготовлении йогуртов «3» и «1». Кефир "2" производить не стоит.

Решение финансовых задач в Excel

Чаще всего для этой цели используются финансовые функции. Рассмотрим пример.

Условие. Подсчитаем, сколько вложить, чтобы за четыре года образовалось 400 000 рублей. Процентная ставка составляет 20% годовых. Проценты начисляются ежеквартально.

Сделаем таблицу с исходными данными:

Поскольку процентная ставка не меняется в течение всего периода, мы используем функцию =PV (Ставка, Кпер, Pmt, Fv, Тип) к ячейке B7.

Заполнение аргументов:

Ставка. Процентная ставка составляет 20 %/4, поскольку проценты начисляются ежеквартально.
Кпер — общее количество периодов равно 4*4 (общий срок вклада*количество начисляемых периодов в год).
Pmt — Рекуррентные платежи равны 0. Мы ничего не пишем. Депозит не пополняется.
Fv — будущая стоимость — это сумма денег, которую мы хотим получить в конце периода депозита.
Тип – 0.

Инвестор должен внести эти деньги, чтобы результат был отрицательным.

Для проверки правильности решения используем формулу: PV = Fv/(1+Rate)*Nper. Подставляем значения: PV=400 000 / (1 + 0,05) 16 = 183245.

Решение эконометрических задач в Excel

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

Есть два диапазона значений:

Значения X будут играть роль факторной характеристики, Y – результирующей. Задача состоит в том, чтобы найти коэффициент корреляции.

Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1, массив 2).

Решение логических задач в Excel

Табличный процессор имеет встроенные логические функции. Любой из них должен содержать хотя бы один оператор сравнения, который будет определять отношение между элементами (=,>, =, например, Задание. Студенты сдали тест. Каждый из них получил оценку. Тест сдается, если есть более 4 баллов. Если меньше - не сдается.

Поместите курсор в ячейку C1. Нажмите значок функции. Выберите "ЕСЛИ".
Заполните аргументы. Логическим выражением является B1> = 4. Это условие, при котором логическое значение равно TRUE.
Если TRUE - "Тест пройден". FALSE — "Тест не пройден".

Решение математических задач в Excel

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

Условие для обучающей задачи. Найдите обратную матрицу B для матрицы A.

Составляем таблицу со значениями матрицы A.
Выделяем на том же листе область для обратной матрицы.
Перейдите в раздел "ФОРМУЛЫ". Категория - "Математика и триггер". Тип - "МИНВЕРС".
В поле аргумента «Массив» введите диапазон матрицы A.
Одновременно нажмите Shift + Ctrl + Enter, потому что это обязательно для ввода массивов.

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

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

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

Документируйте свои источники данных. Я научился этому на собственном горьком опыте. Я нашел данные, скажем, о ценах на сталь. Прохладный. Некоторое время спустя я хотел обновить данные за последний месяц. Я нашел свою таблицу, но понятия не имею, откуда взялись данные. Я трачу время на гугление. Иногда я никогда не нахожу его. Ой. Итак, теперь, когда я загружаю данные в Excel, я делаю запись, обычно в строке 1, URL-адреса моего источника данных. Было бы неплохо добавить дату доступа, если вы работаете над школьной газетой.

Сохраните необработанные данные. После того, как я загружаю данные, я часто хочу их отформатировать. Возможно, штаты перечислены в алфавитном порядке, и я хочу, чтобы они были отсортированы по численности населения. Вот что я делаю: помещаю данные на лист и называю лист «Необработанные данные». (Внизу дважды щелкните имя листа, например Лист1, и вы сможете ввести новое имя.)

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

Закопайте свой калькулятор

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

Я видел, как высокооплачиваемый консультант опозорился, выступая в качестве свидетеля-эксперта в судебном процессе на миллиард долларов. Он совершил именно эту ошибку, и доверие к нему сильно пошатнулось.

Ссылайтесь, а не копируйте. Я часто сравниваю данные из двух разных источников, таких как рост личных доходов по штатам и продажи компаний по штатам. Я вношу данные о продажах в электронную таблицу. Будьте осторожны в своем следующем шаге. Загрузите данные о доходах на другой лист, но в тот же файл. Теперь у вас есть файл Excel с двумя листами: данные о компании и данные о доходах. Теперь создайте третий лист для анализа. Введите формулу для получения данных о компании. Предположим, что первый штат — Алабама, и это название отображается на листе компании в ячейке A2. На листе анализа в ячейке A2 введите следующую формулу: =+Company!A2. (Простой способ сделать это — перейти в ячейку, ввести знак равенства, чтобы начать формулу, затем щелкнуть имя листа компании, затем щелкнуть ячейку A2 и нажать Enter.) Сделайте то же самое для всех состояний. и для данных о продажах.

Чтобы получить данные о доходах на листе, вам нужно сделать немного по-другому. Данные о доходах могут быть не в том же порядке, что и данные компании. Например, он может быть отсортирован географически, а не в алфавитном порядке. Решением является функция ВПР. Это чрезвычайно мощный инструмент. По сути, он просматривает метку, например «Алабама», а затем находит данные в другом диапазоне с той же меткой и указанным количеством столбцов. Погуглите эту функцию и попробуйте несколько раз. Это огромный. И если ваши метки данных расположены в строке, а не в столбце, используйте HLOOKUP. (V и H обозначают вертикальное и горизонтальное положение.)

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

Найдите способы проверить свои данные

Рассмотрите простой пример: у вас есть строки за январь, февраль и декабрь. Ваши столбцы - Яблоки, Бананы, Вишни. В крайнем правом столбце у вас есть ежемесячные итоги продаж. В самом низу у вас есть годовые продажи по видам фруктов. Чтобы получить общий годовой объем продаж всех видов фруктов, вы можете либо просуммировать ежемесячные итоги продаж, либо суммировать годовые итоги продаж фруктов. Вы должны получить один и тот же ответ в любом случае. Так сделайте оба. Хотите верьте, хотите нет, но иногда они не будут равны друг другу, особенно в сложной электронной таблице. Это простой способ определить проблему. Если вы хотите стать по-настоящему изобретательным, вы можете создать оператор IF, который выводит общую сумму, если два метода совпадают друг с другом, и сообщение об ошибке, если два метода не совпадают друг с другом.

Моделирование с четкими предположениями

Я делаю прогнозы экономики и предприятий, с которыми работаю. Где-то у меня есть предположение, что это критично. В макромодели это может быть что-то вроде предельной склонности к потреблению. В бизнес-модели это может быть цена за единицу проданной продукции. Заманчиво поместить это предположение непосредственно в формулу, например = 0,8 * [ячейка с данными о доходах]. Вместо этого отложите часть листа для предположений. В очень сложной модели у меня есть отдельный лист только для предположений. Пометьте одну ячейку для каждого предположения. Например, одна ячейка может содержать предельную склонность к потреблению. Дайте этой ячейке имя, например MPC.

(Вот как назвать диапазон, который может быть одной ячейкой. Проще всего найти место, где указано местоположение ячейки. В моем макете (который может отличаться в вашей версии Excel, с вашими предпочтениями ), слева от строки формул находится место для расположения ячейки, например A1 или J593. Щелкните это место и введите имя, например MPC. Альтернативный способ — выбрать «Формулы», а затем «Диспетчер имен», «Создать». )

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

Использовать отдельный лист для презентаций

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

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

Узнайте, как преобразовать текст в данные

Я часто копирую данные с веб-страницы или файла PDF, и они передаются в Excel в виде текста. Часто цифры не выровнены по вертикали. Вот что обычно работает: выберите весь лист (элемент управления A), измените шрифт на шрифт с фиксированной шириной, например Courier. Довольно часто ваш текст теперь выглядит аккуратно отформатированным в столбцах. Теперь нажмите «Данные», «Текст в столбцы» и выберите «Фиксированная ширина». В некоторых случаях вам может понадобиться выбрать вариант с разделителями.

Сделайте ФРЕДА своим другом.

Для данных, которые вы будете использовать регулярно, установите надстройку FRED Excel. Например, данные о доходах штата, которые вы можете использовать для сравнения с данными о продажах компании, можно сохранить в электронной таблице, а затем обновить, нажав кнопку надстройки FRED. Это также внесет изменения в данные.

Ищите функцию, которая облегчит жизнь

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

С таким отношением ищите функции Excel, которые сделают вашу жизнь проще. Просмотрите список функций и узнайте о таких вещах, как COUNTIF, сводные таблицы, ISNUMBER,

В каждой сложной электронной таблице ищите способы перепроверить свои цифры.

Изучите сочетания клавиш

Теперь мы переходим от предотвращения и обнаружения ошибок к скорости и эффективности.

Навигация: освойте использование клавиш Shift-End и Shift-Home, чтобы перемещаться вверх или вниз по диапазону ячеек. Используйте стрелку влево и вправо для перемещения влево или вправо в блоке ячеек. Сделайте это с помощью клавиши Shift, чтобы выбрать ячейки.

Копировать и вставлять: Control-C и Control-V значительно ускоряют работу.

Команды клавиатуры. Когда ваши руки находятся на клавиатуре, быстрее удерживать их там, чем перемещать их к мыши. Нажмите клавишу Alt и посмотрите вверх; вы увидите, что разные буквы появляются в разных командах. Введите букву, и тогда вы увидите больше букв над следующим набором команд, открытых для вас. Со временем вы выучите некоторые распространенные последовательности букв, которые позволят вам действовать за одну секунду или меньше — меньше времени, чем потребовалось бы, чтобы поднести руку к мыши.

Инвестируйте в эффективность

Уделите немного времени, чтобы узнать больше об Excel. Это инвестиции: они снижают текущий выпуск, но повышают производительность в будущем. Подумайте о работе, которую вы делаете регулярно. Затем найдите способы повысить свою эффективность. Есть книги и интернет-ресурсы. Я предлагаю комбинацию широкого просмотра, просто чтобы получить идеи. Затем, когда вы столкнетесь с утомительной задачей, поищите инструменты, которые ускорили бы эту конкретную задачу.

Вы не получите повышение только потому, что хорошо разбираетесь в Excel. Но вы можете получить повышение, потому что вы быстро и продуктивно выполняете порученную вам работу. Главное — быть быстрее и продуктивнее.

Этот раздел этой главы здесь в знак признания того, что то, о чем мы сейчас спрашиваем, требует гораздо большего, чем быстрое вычисление отношения или квадратного корня. Действительно, регрессионный анализ почти не использовался до середины прошлого века и не стал широко используемым инструментом, возможно, до конца 1960-х и начала 1970-х годов. Даже тогда вычислительные возможности даже самых больших машин IBM смехотворны по сегодняшним меркам. В первые дни программы разрабатывались исследователями и распространялись. Не было рынка для чего-то под названием "программное обеспечение" и, конечно же, ничего под названием "приложения", а новинка появилась на рынке всего несколько лет назад.

С появлением персональных компьютеров и бурным развитием рынка жизненно важного программного обеспечения у нас есть несколько пакетов регрессионного и статистического анализа на выбор. У каждого есть свои достоинства. Мы выбрали Microsoft Excel из-за его широкой доступности как в кампусах колледжей, так и на рынке выпускников колледжей. Stata является альтернативой и имеет функции, которые будут важны для более продвинутого изучения эконометрики, если вы решите пойти по этому пути. Существуют еще более продвинутые пакеты, но обычно для проведения анализа аналитику требуется значительный объем программирования. Цель этого раздела — продемонстрировать, как использовать Excel для выполнения регрессии, а затем сделать это на примере простой версии кривой спроса.

Первый шаг к выполнению регрессии с помощью Excel — загрузка программы на компьютер. Если у вас есть Excel, у вас есть пакет инструментов анализа, хотя он может быть не активирован. Программа требует значительного объема памяти, поэтому не загружается автоматически.

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

Нажмите «Файл» > «Параметры» > «Надстройки», чтобы открыть меню надстройки «Пакеты инструментов». Выберите «Пакет инструментов анализа» и нажмите «Перейти» рядом с «Управление: надстройки Excel» в нижней части окна. Откроется новое окно, в котором вы нажимаете «Пакет инструментов анализа» (убедитесь, что в поле стоит зеленая галочка), а затем нажимаете «ОК». Теперь в меню данных должна быть вкладка «Анализ». Эти шаги представлены на следующих снимках экрана.

Нажмите «Данные», затем «Анализ данных», затем нажмите «Регрессия» и «ОК». Поздравляем, вы добрались до окна регрессии. Окно запрашивает ваши данные. Щелкнув поле рядом с диапазонами Y и X, вы сможете использовать функцию щелчка и перетаскивания Excel для выбора диапазонов ввода. У Excel есть одна странная особенность: функция «щелкни и брось» требует, чтобы независимые переменные, переменные X, были вместе, что означает, что они образуют единую матрицу. Если ваши данные настроены с помощью переменной Y между двумя столбцами переменных X, Excel не позволит вам использовать щелчок и перетаскивание. Например, скажем, столбец A и столбец C являются независимыми переменными, а столбец B является переменной Y, зависимой переменной. Excel не позволит вам щелкнуть и удалить диапазоны данных. Решение состоит в том, чтобы переместить столбец с переменной Y в столбец A, а затем вы можете щелкнуть и перетащить. Та же проблема возникает снова, если вы хотите запустить регрессию только с некоторыми переменными X. Вам нужно будет настроить матрицу так, чтобы все переменные X, которые вы хотите регрессировать, находились в строго сформированной матрице. Эти шаги представлены на следующих кадрах сцены.

После того, как вы выбрали данные для регрессионного анализа и указали Excel, какая из них является зависимой переменной (Y), а какая независимой ценностью (X), у вас есть несколько вариантов выбора параметров и способа вывода. будет отображаться. См. снимок экрана на рис. 13.22 в разделе «Ввод». Если вы установите флажок «метки», программа поместит запись в первый столбец каждой переменной в качестве ее имени на выходе. Вы можете ввести фактическое имя, такое как цена или доход в анализе спроса, в первой строке электронной таблицы Excel для каждой переменной, и оно будет отображаться в выходных данных.

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

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

После того, как данные введены и сделан выбор, нажмите кнопку "ОК", и результаты по умолчанию будут отправлены на отдельный новый рабочий лист. Выходные данные Excel представлены способом, типичным для других программ регрессионного пакета. Первый блок данных дает общую статистику регрессии: Множественное R, R в квадрате и R в квадрате с поправкой на степени свободы, которые вы хотите сообщить. Вы также получаете стандартную ошибку (оценки) и количество наблюдений в регрессии.

Второй блок данных называется ANOVA (дисперсионный анализ). В этом разделе нас интересует столбец с пометкой F. Это вычисленная F-статистика для нулевой гипотезы о том, что все коэффициенты равны нулю, по сравнению с альтернативой, согласно которой хотя бы один из коэффициентов не равен нулю. Проверка этой гипотезы была представлена в разделе 13.4 в разделе «Насколько хорошо уравнение?» В следующем столбце указано значение p для этого теста под заголовком «Значимость F». Если значение p меньше, скажем, 0,05 (рассчитанная статистика F находится в хвосте), мы можем с уверенностью 90% сказать, что мы не можем принять нулевую гипотезу о том, что все коэффициенты равны нулю. Это хорошо: это означает, что по крайней мере один из коэффициентов значительно отличается от нуля, а значит, влияет на значение Y.

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

Оценка спроса на розы

Вот пример использования программы Excel для выполнения регрессии для конкретного конкретного случая: оценка спроса на розы. Мы пытаемся оценить кривую спроса, которая, исходя из экономической теории, предполагает, что определенные переменные влияют на то, сколько товара мы покупаем. Отношение между ценой товара и величиной спроса называется кривой спроса. Кроме того, у нас есть функция спроса, которая включает другие релевантные переменные: доход человека, цену товаров-заменителей и, возможно, другие переменные, такие как сезон года или цена дополнительных товаров. Объем спроса будет нашей переменной Y, а цена роз, цена гвоздик и доход будут нашими независимыми переменными, переменными X.

Для всех этих переменных теория сообщает нам ожидаемую взаимосвязь. Для цены рассматриваемого товара, розы, теория предсказывает обратную зависимость, кривую спроса с отрицательным наклоном.Теория также предсказывает взаимосвязь между объемом спроса на один товар, в данном случае на розы, и ценой заменителя, в данном примере на гвоздики. Теория предсказывает, что это должна быть положительная или прямая связь; по мере того как цена заменителя падает, мы заменяем розы более дешевым заменителем, гвоздиками. Снижение цены заменителя приводит к снижению спроса на анализируемый товар, здесь розы. Сокращение порождает сокращение — это положительная связь. Для обычных товаров теория также предсказывает положительную связь; по мере роста наших доходов мы покупаем больше товаров, роз. Мы ожидаем таких результатов, потому что это то, что предсказывается столетней экономической теорией и исследованиями. По сути, мы проверяем эти столетние гипотезы. Собранные данные определялись тестируемой моделью. Так должно быть всегда. Вывод статистики не осуществляется путем заброски горы данных в компьютер и запроса у машины теории. Сначала теория, потом тест.

Эти данные представляют собой средние цены по стране, а доход — это личный доход на душу населения в стране. Требуемое количество - это общий годовой объем продаж роз в стране. Это годовые данные временных рядов; мы отслеживаем рынок роз в США с 1984 по 2017 год, 33 наблюдения.

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

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

Результаты регрессии должны автоматически отображаться на новом листе.

Первыми представленными результатами является R-квадрат, мера силы корреляции между Y и X₁, X₂ и X_3. воспринимается как группа. Наш R-квадрат, равный 0,699, с поправкой на степени свободы означает, что 70 % вариации спроса на розы Y можно объяснить вариациями X₁, X_{2< /sub> и X₃, Цена роз, Цена гвоздик и Доход. Не существует статистического теста для определения «значительности» R 2 . Конечно, предпочтительнее более высокий R 2 , но на самом деле именно значение коэффициентов будет определять ценность проверяемой теории и станет частью любой политической дискуссии, если будет продемонстрировано, что они значительно отличаются от нуля.}

Глядя на третью панель вывода, мы можем написать уравнение следующим образом:

где b₀ — отрезок, b₁ — расчетный коэффициент на цену роз, а b₂ — расчетный коэффициент на цена гвоздик, b₃ – расчетный эффект дохода, а – ошибка. Уравнение написано латинскими буквами, указывая на то, что это оценочные значения, а не параметры генеральной совокупности, β.

Наше оценочное уравнение:

Количество проданных роз = 183 475 - 1,76 Цена роз + 1,33 Цена гвоздик + 3,03 Доход Количество проданных роз = 183 475 – 1,76 Цена роз + 1,33 Цена гвоздик + 3,03 Доход

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

Интерпретация коэффициентов может сказать нам о величине влияния изменения каждой переменной на спрос на розы. Именно способность делать это делает регрессионный анализ таким ценным инструментом. Расчетные коэффициенты говорят нам о том, что увеличение цены роз на один доллар приведет к уменьшению количества купленных роз в 1,76 раза. Цена гвоздик, по-видимому, играет важную роль в спросе на розы, поскольку мы видим, что увеличение цены гвоздик на один доллар увеличит спрос на розы на 1.33 единицы, поскольку потребители заменят более дорогие гвоздики. Точно так же увеличение дохода на душу населения на один доллар приведет к увеличению объема закупаемых роз на 3,03 единицы.

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

Однако мы не можем останавливаться на достигнутом. Нам нужно сначала проверить, являются ли наши коэффициенты статистически значимыми от нуля. Мы выдвигаем гипотезу:

для всех трех коэффициентов регрессии. Напомним ранее, что мы не сможем окончательно сказать, что наша оценка b₁ является фактической реальной популяцией β₁, а скорее только с (1-α) % уровень уверенности в том, что мы не можем отвергнуть нулевую гипотезу о том, что наше расчетное значение β₁ значительно отличается от нуля. Аналитик утверждает, что цена роз оказывает влияние на объем спроса. Действительно, каждая из включенных переменных влияет на количество требуемых роз. Таким образом, претензия заключается в альтернативных гипотезах. Потребуется очень большая вероятность, в данном случае 0,95, чтобы ниспровергнуть нулевую гипотезу, статус-кво, что β = 0. Во всех тестах регрессионной гипотезы утверждение заключается в альтернативе, а утверждение состоит в том, что теория нашла переменную это оказывает значительное влияние на переменную Y.

Статистика теста для этой гипотезы следует известной стандартизирующей формуле, которая подсчитывает количество стандартных отклонений t, при которых оценочное значение параметра b₁ отличается от гипотетического значения β. ₀, что в данном случае равно нулю:

Компьютер вычисляет эту тестовую статистику и представляет ее как «t stat». Вы можете найти это значение справа от стандартной ошибки оценки коэффициента. Стандартная ошибка коэффициента для b₁ в формуле равна S_b₁. Чтобы сделать вывод, мы сравниваем эту тестовую статистику с критическим значением t Стьюдента при степенях свободы n-3-1 = 29 и альфа = 0,025 (уровень значимости 5% для двустороннего теста). Наш показатель t для b₁ составляет примерно 5,90, что больше 1,96 (критическое значение, которое мы искали в t-таблице), поэтому мы не можем принять наши нулевые гипотезы об отсутствии эффекта. Мы заключаем, что цена оказывает значительное влияние, потому что вычисленное значение t находится в хвосте. Мы проводим тот же тест для b₂ и b₃. Для каждой переменной мы обнаруживаем, что не можем принять нулевую гипотезу об отсутствии связи, потому что рассчитанная t-статистика находится в хвосте для каждого случая, то есть больше критического значения. Было установлено, что все переменные в этой регрессии оказывают значительное влияние на спрос на розы.

Эти тесты показывают, значительно ли отдельный коэффициент отличается от нуля, но не оценивают общее качество модели. Мы видели, что квадрат R с поправкой на степени свободы указывает на то, что эта модель с этими тремя переменными объясняет 70% вариаций в количестве требуемых роз. Мы также можем провести второй тест модели, взятой в целом. Это F-тест, представленный в разделе 13.4 этой главы. Поскольку это множественная регрессия (более одного X), мы используем F-тест, чтобы определить, влияют ли наши коэффициенты в совокупности на Y. Гипотеза такова:

В разделе вывода ANOVA мы находим вычисленную статистику F для этой гипотезы. Для этого примера F-статистика равна 21,9. Опять же, сравнение вычисленной статистики F с критическим значением с учетом желаемого уровня значимости и степеней свободы позволит нам сделать вывод.

Лучший способ сделать вывод для этого статистического теста – использовать правило сравнения p-значения. Значение p — это площадь в хвосте с учетом рассчитанной F-статистики. По сути, компьютер находит для нас значение F в таблице и вычисляет значение p. В сводном выводе под «значимостью F» указана эта вероятность. Для этого примера вычисляется как 2,6 x 10 -5 , или 2,6, затем сдвигается десятичная дробь на пять знаков влево. (0,000026) Это почти бесконечно малый уровень вероятности и, безусловно, меньше, чем наш альфа-уровень 0,05 для 5-процентного уровня значимости.

Не имея возможности принять нулевую гипотезу, мы заключаем, что эта спецификация этой модели имеет право на существование, поскольку по крайней мере один из оценочных коэффициентов значительно отличается от нуля. Поскольку F-вычисленное больше, чем F-критическое, мы не можем принять H₀, а это означает, что X₁, X₂ и X₃ вместе оказывает существенное влияние на Y.

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

Читайте также: