Слабая; 0,3-0,7 –средняя; 0,7-1,0 – сильная.
Тема № 9 СТАТИСТИЧЕСКИЙ АНАЛИЗ ДАННЫХ Цель занятия – изучение возможности Excel по проведению статистического анализа данных, овладение навыками работы с дополнением «Пакет анализа». Перечень рассматриваемых вопросов: Основные теоретические сведения Запуск Пакета анализа Знакомство с инструментами анализа; операции, выполняемые с помощью пакета анализа
Результат выполнения работы
По завершении занятия пользователь должен освоить обладать следующими освоенными компетенциями:
знать:
- как запустить Пакет анализа;
- как получить числовые статистические характеристики.
способен:
- выполнять дисперсионный и регрессионный анализ и производить анализ полученных результатов.
Тезаурус:пакет анализа, статистические числовые характеристики,дисперсионный анализ, регрессионный анализ.
КРАТКИЕ ТЕОРЕТИЧЕСКИЕ И УЧЕБНО-МЕТОДИЧЕСКИЕ МАТЕРИАЛЫ
Основные теоретические сведения
Microsoft Excel располагает широким набором средств для анализа статистических данных. Для проведения несложного анализа могут быть использованы встроенные статистические функции. Целую коллекцию функций и инструментов, предназначенных для решения сложных статистических и инженерных задач и расширяющих встроенные аналитические возможности Excel, содержит надстройка Пакет анализа.
Для работы с инструментами анализа статистические данные следует представить в виде строк или столбцов рабочего листа. Совокупность ячеек, содержащих анализируемые данные, называется входным диапазоном. Для проведения анализа данных следует указать входной и выходной диапазоны для размещения результатов анализа. Выходной диапазон задается ссылкой на левую верхнюю ячейку. Результаты анализа могут быть представлены в графическом виде.
|
|
|
Запуск Пакета анализа
При обработке массовой экономической информации возникает необходимость вычисления статистических характеристик, отражающих наиболее существенные особенности рядов распределения. Значения статистических функций в Excel удобно вычислять с помощью мастера функций. Для выполнения статистического анализа хорошо использовать также дополнение «Пакет анализа». Это дополнение позволяет выполнять дисперсионный (одно- и двухфакторный), корреляционный и регрессионный анализ, проверку статистических гипотез о равенстве дисперсий и средних и т.д.
Excel имеет средства для проведения анализа рядов динамики (построения линий трендов, расчета прогнозных значений наблюдаемых показателей и т.д.).
Для запуска пакета анализа:
1. Выберите в пункте меню СервискомандуАнализ данных.
|
|
|
2. При отсутствии в меню этой команды следует в меню Сервис выбрать команду Надстройки и в появившемся диалоговом окне Надстройки включить переключатель Пакет анализа
3. Нжать ОК.
Знакомство с инструментами анализа; операции, выполняемые с помощью пакета анализа
После выполнения команды Анализ данных открывается окно диалога Анализ данных, в котором отображается список инструментов пакета анализа.
ИнструментОднофакторный дисперсионный анализ используется для проверки гипотезы о сходстве средних значений двух или более выборок, принадлежащих одной и той же генеральной совокупности.
ИнструментДвухфакторный дисперсионный анализ с повторениями. Представляет собой более сложный вариант однофакторного анализа с несколькими выборками для каждой группы данных.
ИнструментДвухфакторный дисперсионный анализ без повторения. Представляет собой двухфакторный анализ дисперсии, не включающий более одной выборки на группу. Используется для проверки гипотезы о том, что средние значения двух или нескольких выборок одинаковы (выборки принадлежат одной и той же генеральной совокупности).
ИнструментКорреляция применяется для количественной оценки взаимосвязи двух наборов данных, представленных в безразмерном виде. Корреляционный анализ дает возможность установить, ассоциированы ли наборы данных по величине. То есть, большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция), или, наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная корреляция), или данные двух диапазонов никак не связаны (нулевая корреляция).
|
|
|
ИнструментКовариация является мерой связи между двумя диапазонами данных. Используется для вычисления среднего произведения отклонений точек данных от относительных средних. Ковариационный анализ дает возможность установить, ассоциированы ли наборы данных по величине. То есть, большие значения из одного набора данных связаны с большими значениями другого набора (положительная ковариация), или, наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная ковариация), или данные двух диапазонов никак не связаны (ковариация близка к нулю).
Инструмент Описательная статистика строит одномерный статистический отчет, содержащий информацию о центральной тенденции и изменчивости или вариации входных данных. Статистические данные из этого отчета могут указать, какие тесты являются наиболее подходящими для дальнейшего анализа.
|
|
|
Инструмент Регрессия заключается в подборе графика для набора наблюдений с помощью метода наименьших квадратов. Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или более независимых переменных. Основная особенность регрессионного анализа: при его помощи можно получить конкретные сведения о том, какую форму и характер имеет зависимость между исследуемыми переменными.
Инструмент Генерация случайных чисел заполняет интервал независимыми случайными числами. Закон распределения этих чисел задается параметром Распределение.
Инструмент Гистограмма вычисляет частоту появления значений. Числовой промежуток между наименьшим и наибольшим значением делится на интервалы равной длины. Под частотой понимается количество чисел, попавших в такой интервал.
С помощью пакета анализа можно выполнить следующие операции:
- 
Вычислить корреляцию, ковариацию и скользящее среднее;
- Провести экспоненциальное сглаживание данных;
- Построить гистограммы;
- Найти ранг и перцентиль;
- Исследовать регрессию;
- Осуществить статистическое моделирование с использованием механизма генерации случайных чисел и создания выборки.
Во многих инструментах статистического анализа присутствуют одинаковые параметры (см. рис. выше).
К ним относятся:
Входной интервал. Необходимо ввести ссылку на интервал данных рабочего листа, подлежащих анализу. Если во входной интервал включены метки, необходимо установить флажок Метки, в противном случае Excel выведет на экран соответствующее сообщение.
Метки. Если входной интервал не включает меток, снимите флажок Метки. Excel генерирует соответствующие метки данных для выходной таблицы (Строка 1, Строка 2, Строка 3 или Столбец 1, Столбец 2, Столбец 3 и т.д.).
Выходной интервал. Введите ссылку для верхней левой ячейки интервала, в который вы предполагаете вывести результирующую таблицу.
Новый рабочий лист. Этот параметр вставляет новый лист в текущую рабочую книгу и выводит результаты анализа на новый лист, начиная с ячейки А1. Используйте поле ввода для задания имени нового листа.
Новая рабочая книга. Этот параметр создает новую рабочую книгу и выводит в ней результаты на новый рабочий лист, начиная с ячейки А1.
ПРАКТИЧЕСКАЯ ЧАСТЬ
Задание 21
Используя инструменты Описательная статистика пакета анализа выполнить статистическую обработку данных по производству хлебобулочной продукции в отдельных цехах и в целом по предприятию за период от 11.01. по 24.01.2009 г. Исходные данные представлены в следующей таблице.
Для этого.
1. В меню Сервис выбрать команду Анализ данных.
2. В окне диалога Анализ данных выбрать инструмент анализа Описательная статистика.
3. В окне диалога Описательная статистика в поле ввода Входной интервал указать интервал В3:D17.
4. Для параметра Группирование установить флажок По столбцам.
5. Установить флажок Метки в первой строке.
6. В поле ввода Выходной интервал указать адрес верхней левой ячейки выходного интервала G3.
7. Установить флажок Итоговая статистика и в поле Уровень надежности введите значение 95%.
8. В поле К-ый наименьший введите 1.
9. В поле К-ый наибольший введите 1.
10. Параметры окна диалога Описательная статистика установлены
11. Нажать кнопку ОК.
12. На экране появится таблица описательной статистики для трех выборок.
В состав описательной статистики входят такие характеристики: среднее; стандартная ошибка; медиана; мода; стандартное отклонение; дисперсия выборки; эксцесс; асимметричность; интервал; минимум; максимум; сумма; счет и др.
Среднее значение рассчитывается как среднее арифметическое набора данных: сумма всех значений выборки, деленная на объем выборки. При помощи среднего мы получаем возможность сравнивать несколько наборов данных или их частей.
Мода − значение моды массива данных, т.е. наиболее часто встречающееся значение.
Медиана - это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана.
Минимум - наименьшее значение из списка аргументов
Максимум - максимальное значение из списка аргументов
Размах - разница между наибольшим и наименьшим значениями выборки.
Дисперсия - среднее арифметическое квадратов отклонений значений от их среднего.
Счёт − количество чисел в списке аргументов, игнорируя пустые ячейки и ячейки с текстом.
Счётз − количество значений в списке аргументов, т.е. число непустых ячеек.
Стандартное отклонение - квадратный корень из дисперсии выборки - мера того, насколько широко разбросаны точки данных относительно их среднего.
Эксцесс показывает "остроту пика" распределения, характеризует относительную остроконечность или сглаженность распределения по сравнению с нормальным распределением. Положительный эксцесс обозначает относительно остроконечное распределение (пик заострен). Отрицательный эксцесс обозначает относительно сглаженное распределение (пик закруглен).
Если эксцесс существенно отличается от нуля, то распределение имеет или более закругленный пик, чем нормальное, или, напротив, имеет более острый пик (возможно, имеется несколько пиков). Эксцесс нормального распределения равен нулю.
Асимметрия или асимметричность показывает отклонение распределения от симметричного. Если асимметрия существенно отличается от нуля, то распределение несимметрично, нормальное распределение абсолютно симметрично. Если распределение имеет длинный правый хвост, асимметрия положительна; если длинный левый хвост - отрицательна.
Задание 22
По данным исходной таблицы задания 21 выполнить регрессионный анализ, используя инструмент Регрессия пакета анализа. В качестве входного интервала Y указать столбец значений производство продукции в целом по предприятию (столбец Всего), а в качестве входного интервала Х – столбец содержащий номера периодов (столбец Дата). Результаты анализа вывести на текущий рабочий лист. Определить коэффициенты регрессии.
1. В меню Сервис выбрать команду Анализ данных.
2. В окне диалога Анализ данных выбрать инструмент анализа Регрессия.
3. Установить параметры окна диалога Регрессия, приведенные ниже.
4. После установки параметров нажать ОК.
5. Результаты анализа приведены ниже.
Анализ полученных данных.
Величина R-квадрат, называемая также мерой определенности, характеризует качество полученной регрессионной прямой. Это качество выражается степенью соответствия между исходными данными и регрессионной моделью (расчетными данными). Мера определенности всегда находится в пределах интервала [0;1].
Если значение R-квадрата близко к единице, это означает, что построенная модель объясняет почти всю изменчивость соответствующих переменных. И наоборот, значение R-квадрата, близкое к нулю, означает плохое качество построенной модели.
Связь между признаками (по шкале Чеддока) может быть сильной, средней и слабой. Тесноту связи определяют по величине коэффициента корреляции, который может принимать значения от -1 до +1 включительно. Критерии оценки тесноты связи при значениях:
слабая; 0,3-0,7 –средняя; 0,7-1,0 – сильная.
В нашем примере мера определенности равна 0,8252, что говорит об очень хорошей подгонке регрессионной прямой к исходным данным.
Множественный R - коэффициент множественной корреляции R - выражает степень зависимости независимых переменных (X) и зависимой переменной (Y). Эта величина принимает значения в интервале от нуля до единицы. В нашем примере связь между переменными x и y является весьма высокой, так как множественный R равен 0,90841944
Коэффициент регрессии b (68,74505512) и смещение по оси ординат, т.е. константа a (-2736142,275).
Исходя из расчетов, можем записать уравнение регрессии таким образом:
Y= 68,74505512 * Х - 2736142,275Направление связи между переменными определяется на основании знаков (отрицательный или положительный) коэффициентов регрессии (коэффициента b).
Если знак при коэффициенте регрессии - положительный, связь зависимой переменной с независимой будет положительной. В нашем случае знак коэффициента регрессии положительный, следовательно, связь также является положительной.
Если знак при коэффициенте регрессии - отрицательный, связь зависимой переменной с независимой является отрицательной (обратной).
Ниже представлены результаты вывода остатков.
При помощи этой части отчета мы можем видеть отклонения каждой точки от построенной линии регрессии. Наибольшее абсолютное значение остатка в нашем случае -+1,22703631, наименьшее –
-1,15134277.
Задание 23
Получить числовые статистические характеристики по данным приведенным в следующей таблице
Урожайность с.-х. культур в хозяйствах района
| Номер хозяйства | Урожайность ц с га | ||
| картофель | семена подсолнечника | оз. пшеница | |
| 1 | 78 | 9,7 | 22,5 |
| 2 | 99 | 13,1 | 24,5 |
| 3 | 70 | 11,1 | 21,0 |
| 4 | 101 | 9,4 | 21,0 |
| 5 | 103 | 7,7 | 18,0 |
| 6 | 84 | 13,8 | 19,0 |
| 7 | 99 | 13,3 | 19,6 |
| 8 | 84 | 11,8 | 20,0 |
| 9 | 73 | 13,0 | 22,0 |
| 10 | 87 | 8,1 | 20,1 |
| 11 | 91 | 6,8 | 28,6 |
| 12 | 130 | 10,3 | 27,4 |
| 13 | 108 | 11,2 | 26,5 |
Определите:
1. Медиану урожайности картофеля, подсолнечника, оз. пшеницы.
2. Дисперсию урожайности картофеля и оз. пшеницы.
3. Размах вариации урожайностей картофеля, оз. пшеницы, семян подсолнечника.
4. Результаты расчетов записать в тетрадь.
Задание 24
Выяснить наличие влияния денежного стимулирования на производительность труда шести однородным группам из пяти человек каждая. Каждой группе предложены задачи одинаковой трудности. Задачи предлагались каждому испытуемому независимо от всех остальных. Величина вознаграждения (от меньшей к большей)
Данные приведены в следующей таблице.
| Группа 1 | Группа 2 | Группа 3 | Группа 4 | Группа 5 | Группа 6 |
| 10 | 8 | 12 | 12 | 24 | 19 |
| 11 | 10 | 17 | 15 | 16 | 18 |
| 9 | 16 | 14 | 16 | 22 | 27 |
| 13 | 13 | 9 | 16 | 18 | 25 |
| 7 | 12 | 16 | 19 | 20 | 24 |
| 12 | 15 | 13 | 18 | 25 | 21 |
Задание 25
С помощью процедуры регрессионного анализа определите, есть ли зависимость между доходом и потребление молока за месяц и найдите уравнение линейной регрессии на основании данных приведенных в следующей таблице.
Доход и потребление молока за месяц (на одного члена семьи)
| № | Доход (Х), руб. | Потребление молока (У), л |
| 1. | 1200 | 2 |
| 2. | 1400 | 2,5 |
| 3. | 1500 | 3,5 |
| 4. | 1600 | 3,8 |
| 5. | 1700 | 4 |
| 6. | 1800 | 4,5 |
| 7. | 1900 | 4,5 |
| 8. | 2000 | 5 |
| 9. | 2100 | 5 |
| 10. | 2200 | 6 |
| 11. | 3000 | 6 |
| 12. | 3200 | 6,5 |
| 13. | 3400 | 7 |
| 14. | 3600 | 7 |
| 15. | 3800 | 8 |
| 16. | 4000 | 8 |
| 17. | 4500 | 9 |
| 18. | 5000 | 10 |
Самостоятельная работа
Задание 1
По 20 предприятиям региона (таблица 1) изучется зависимость выработки продукции на одного работника У (тыс. руб.) от ввода в действие новых основных фондов Х1 (% от стоимости фондов на конец года) и от удельного веса рабочих высокой квалификации в общей численности рабочих Х2 (%).
Таблица 1
| № предприятия | У | Х1 | Х2 |
| 1 | 7,0 | 3,9 | 10,0 |
| 2 | 7,0 | 3,9 | 14,0 |
| 3 | 7,0 | 3,7 | 15,0 |
| 4 | 7,0 | 4,0 | 16,0 |
| 5 | 7,0 | 3,8 | 17,0 |
| 6 | 7,0 | 4,8 | 19,0 |
| 7 | 8,0 | 5,4 | 19,0 |
| 8 | 8,0 | 4,4 | 20,0 |
| 9 | 8,0 | 5,3 | 20,0 |
| 10 | 10,0 | 6,8 | 20,0 |
| 11 | 9,0 | 6,0 | 21,0 |
| 12 | 11,0 | 6,4 | 22,0 |
| 13 | 9,0 | 6,8 | 22,0 |
| 14 | 11,0 | 7,2 | 25,0 |
| 15 | 12,0 | 8,0 | 28,0 |
| 16 | 12,0 | 8,2 | 29,0 |
| 17 | 12,0 | 8,1 | 30,0 |
| 18 | 12,0 | 8,5 | 31,0 |
| 19 | 14,0 | 9,6 | 32,0 |
| 20 | 14,0 | 9,0 | 36,0 |
Требуется:
Вычислить параметры линейного уравнения множественной регрессии.
Оцените статистическую значимость параметров регрессионной модели с помощью t-критерия, нулевую гипотезу о значимости уравнения и показателей тесноты связи.
Рассчитайте прогнозное значение результата, если прогнозные значения составляют 90% от их значений.
Линейное уравнение множественной регрессии У от Х1 и Х2 имеет вид У=а+b1∙x1+b2∙x2
Эта операция проводится с помощью инструмента анализа данных Регрессия. Она аналогична расчету параметров парной линейной регрессии, только в отличие от неё в окне диалога при заполнении параметра входной интервал Х следует указать не один столбец, а все столбцы, содержащие значения факторных признаков.
Задание №2
Имеются данные о деятельности крупнейших компаний США
в 2009 году
| № п/п | Чистый доход, млрд. долл. США, у | Оборот капитала, млрд. долл.США, х1 | Использованный капитал, млрд. долл. США, х2 | Численность служащих, тыс. чел., х3 |
| 1 | 6,6 | 6,9 | 83,6 | 222,0 |
| 2 | 3,0 | 18,0 | 6,5 | 32,0 |
| 3 | 6,5 | 107,9 | 50,4 | 82,0 |
| 4 | 3,3 | 16,7 | 15,4 | 45,2 |
| 5 | 0,1 | 79,6 | 29,6 | 299,3 |
| 6 | 3,6 | 16,2 | 13,3 | 41,6 |
| 7 | 1,5 | 5,9 | 5,9 | 17,8 |
| 8 | 5,5 | 53,1 | 27,1 | 151,0 |
| 9 | 2,4 | 18,8 | 11,2 | 82,3 |
| 10 | 3,0 | 35,3 | 16,4 | 103,0 |
| 11 | 4,2 | 71,9 | 32,5 | 225,4 |
| 12 | 2,7 | 93,6 | 25,4 | 675,0 |
| 13 | 1,6 | 10,0 | 6,4 | 43,8 |
| 14 | 2,4 | 31,5 | 12,5 | 102,3 |
| 15 | 3,3 | 36,7 | 14,3 | 105,0 |
| 16 | 1,8 | 13,8 | 6,5 | 49,1 |
| 17 | 2,4 | 64,8 | 22,7 | 50,4 |
| 18 | 1,6 | 30,4 | 15,8 | 480,0 |
| 19 | 1,4 | 12,1 | 9,3 | 71,0 |
| 20 | 0,9 | 31,3 | 18,9 | 43,0 |
Требуется:
1. Вычислить параметры линейного уравнения множественной регрессии.
2. Оцените статистическую значимость параметров регрессионной модели с помощью t-критерия, нулевую гипотезу о значимости уравнения и показателей тесноты связи.
3. Рассчитайте прогнозное значение результата, если прогнозные значения составляют 80% от их максимальных значений.
Контрольные вопросы
1. Для каких целей используется дополнение Пакет анализа?
2. Как загрузить дополнение Пакет анализа? Что надо предпринять, если в меню Excel Сервис отсутствует команда Анализ данных?
3. Какие инструменты анализа вы знаете?
4. Что такое входной диапазон?
5. Что такое выходной диапазон?
6. Что такое метки?
7. Как следует подготовить исходные данные для проведения регрессионного анализа средствами дополнения Пакет анализа?
Дата добавления: 2018-05-12; просмотров: 189; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!