Пакет «Анализ данных»
Microsoft Excel 2013 содержит в своем составе ряд средств, которые можно использовать для анализа данных. К таковым можно отнести встроенные функции из категории Статистические, а также средства из надстройки Анализ данных.
Указанные средства позволяют решать целый круг различных задач, включая основные задачи математической статистики:
- • представление экспериментальных данных в упорядоченном удобном для анализа виде;
- • оценка характеристик наблюдаемых случайных величин;
- • проверка статистических гипотез — принятие решения о согласованности результатов оценивания с опытными данными.
Надстройка статистического анализа представляет собой специальную программу, для подключения которой нужно:
- 1) выбрать вкладку Файл;
- 2) щелкнуть Параметры;
- 3) выбрать команду Надстройки;
- 4) в окне Управление выбрать пункт Надстройки Excel и нажать кнопку Перейти;
- 5) в окне Доступные надстройки установить флажок Пакет анализа
и нажать кнопку ОК.
Как использовать Пакет анализа Регрессия в Microsoft Excel
После указанных действий пакет статистического анализа будет доступен через команду Анализ данных в группе Анализ на вкладке Данные ленты.
При выборе команды Анализ данных открывается диалоговое окно (рис. 5.52), в котором можно выбрать требуемый инструмент анализа.
Рис. 5.52. Диалоговое окно Анализ данных 5.9.1. Построение гистограмм
Для решения первой из рассмотренных задач математической статистики и, в частности, для построения гистограмм следует выбрать инструмент Гистограмма.
В этом случае откроется диалоговое окно Гистограмма (рис. 5.53), в котором следует ввести необходимые данные и задать требуемые параметры вывода.
В поле Входной интервал следует ввести адрес диапазона ячеек, в котором содержатся анализируемые данные.
В поле Интервал карманов можно ввести ссылку на диапазон ячеек, который содержит значения верхней (правой) границы отрезков (карманов), указанные в возрастающем порядке. При вычислении числа попаданий
Рис. 5.53. Диалоговое окно Гистограмма
данных на каждый отрезок значения на нижней границе отрезка включаются, а на верхней границе не включаются.
Поле Интервал карманов является необязательным для заполнения. Если диапазон карманов не указан, то карманы будут созданы автоматически.
Выходной интервал задается указанием левой верхней ячейки выходного диапазона. Результаты анализа могут быть выведены на новый лист текущей рабочей книги или на новый лист новой рабочей книги.
Для этого следует установить переключатель в соответствующее положение, а при необходимости вести имя для нового листа в поле Новый рабочий лист.
Расширение аналитических возможностей Excel с помощью надстройки ”Пакет анализа”
Установка флажка Парето (отсортированная диаграмма) определяет порядок вывода данных в порядке убывания частот.
При установке флажка Интегральный процент на гистограмме дополнительно отображается график интегральной функции распределения.
Для автоматического создания гистограммы на листе, содержащем выходной диапазон, следует установить флажок Вывод графика.
Рис. 5.54. Результат анализа данных инструментом Гистограмма
Источник: studme.org
Использование пакета анализа в MS Excel
Общие сведения.Пакет анализа, являющийся надстройкой, содержит коллекцию функций и инструментов, расширяющих встроенные аналитические возможности MS Excel. В частности, пакет анализа можно использовать для создания гистограмм, ранжирования данных, извлечения случайных или периодических выборок из множеств данных, проведения регрессионного анализа, получения основных статистических характеристик для выборки, генерации случайных чисел с различным распределением, а также применять преобразование Фурье и другие преобразования к своим данным.
Функции пакета анализа можно использовать точно так же, как и любые другие функции MS Excel, а чтобы получить доступ к инструментам пакета анализа, необходимо выполнить следующие действия:
1. Выбрать в менюСервис командуАнализ данных. Затем на экране появится окно диалога, содержащее список инструментов анализа.
2. Чтобы использовать инструмент анализа, необходимо выбрать его имя в списке и нажмите кнопкуОК.
3. Заполнить открывшееся окно диалога. В большинстве случаев это означает задание входного диапазона с данными, которые вы собираетесь анализировать, задание выходного диапазона, в который должны быть помещены результаты, и выбор нужных параметров.
Если командаАнализ данных отсутствует в менюСервис или если формула, которая использует функцию пакета анализа, возвращает ошибочное значение #ИМЯ?, выберите в менюСервис командуНадстройки и затем -Пакет анализа -VBA в списке надстроек, после чего наж-мите кнопкуОК. При анализе данных часто возникает необходимость определения различных статистических характеристик или параметров распределения. С помощью MS Excel можно анали-зировать распределение, используя несколько инструментов: встроенные статистические функции, функции для оценки разброса данных, инструментОписательная статистика, который предоставляет удобные сводные таблицы основных параметров распределения, инструменты Гистограмма и Ранг и персентиль.
Задание 1.Рассмотреть на практическом примере применение инструмента Описательная статистика пакета анализа.
1. Создать новый файл, на 1-м листе построить таблицу по приведенной ниже форме.
| A | B | C | D |
| 1 | 102,8396 | ||
| 2 | 102,0065 | ||
| 3 | 101,0474 | ||
| 4 | 98,5871 | ||
| 5 | 100,0231 | ||
| 6 | 102,5894 | ||
| 7 | 104,3369 | ||
| 8 | 101,2785 | ||
| 9 | 103,1233 | ||
| 10 | 97,2110 | ||
| 11 | 98,7534 | ||
| 12 | 100,2587 | ||
| 13 | 109,5468 | ||
| 14 | 105,1034 | ||
| 15 | 102,2587 | ||
| 16 | 101,6512 | ||
| 17 | 106,4527 | ||
| 18 | 102,5584 | ||
| 19 | 99,3213 | ||
| 20 | 100,0203 |
1. ИнструментОписательная статистика предлагает таблицу основных статистических характеристик для одного или нескольких множеств входных значений. Выходной диапазон этого инструмента содержит следующие статистические характеристики для каждой переменной из входного диапазона: среднее, стандартная ошибка, медиана, мода, стандартное отклонение, дисперсия, коэффициент эксцесса, коэффициент асимметрии, интервал, минимальное значение, максимальное значение, сумма, число значений, k-e наибольшее и наименьшее значения (для любого заданного значения k) и уровень значимости для среднего.
2. Для использования инструмента Описательная статистика необходимо выбрать в меню Сервис команду Анализ данных, затем в окне диалога Анализ данных выбрать инструмент Описательная статистика и нажать кнопку ОК.
3. В открывшемся диалоговом окне установить следующие опции: Входной интервал: $A$1:$A$20, Группирование: по столбцам, Уровень надежности: флажок — 95%, К-ый наименьший: флажок – 20, К-ый наибольший – 20, Параметры вывода — Выходной интервал: $C$1, Итоговая статистика: флажок, Нажать кнопку ОК.
4. В результате в таблице будет отражен статистический анализ данного столбца (А1:А20).
5. Сохранить результаты на личном диске в файле под именем stat-2. xls.
Задание 2. Вычисление скользящего среднего.Скользящее среднее — это метод, позволяя-ющий упростить анализ тенденции за счет сглаживания колебаний измерений за некоторый период времени. Эти колебания могу возникать из-за случайного «шума», который часто является побочным эффектом техники измерения. Например, измерения высоты растущих детей будут зависеть и от точности линейки, и от того, насколько прямо стоит ребенок при измерении. Однако вы можете выполнить ряд измерений, затем сгладить их по отрезкам времени и построить окончательную кривую, которая будет более точно отражать фактическую скорость роста.
Колебания измерений и связанное с ними смещение данных могут быть результатом различных временных условий. Месячные продажи, например, могут изменяться в зависимости от количества рабочих дней в месяце или от количества продавцов, находящихся в отпуске.
1. Создать новый файл, на 1-м листе которого построить таблицу и двугодичную кривую спроса, показанные на рис. 1.
2. Для построения менее «шумной» линии тренда для этих данных можно использовать шестимесячное скользящее среднее. Первая точка в кривой скользящего среднего — это среднее за первые шесть месяцев (январь-июнь 2004 г.). Следующая точка — среднее со второго по седьмой месяцы (Февраль-Июль 2005 г.) и т.д. ИнструментСкользящее среднее может выполнить этот анализ вместо вас.
3. Чтобы использовать инструмент Скользящее среднее, выберите команду Анализ данных в меню Сервис, укажите инструмент Скользящее среднее в окне диалогаАнализ данных и наж-мите кнопкуОК. ИнструментСкользящее среднее требует задания трех параметров: диапазона, который содержит анализируемые данные, диапазона для вычисляемых усредненных данных и интервала усреднения. Например, для определения трехмесячного скользящего среднего, задайте интервал 3.
4. На рис. 2. представлено шестимесячное скользящее среднее вместе с исходной кривой спроса из рис. 1. ИнструментСкользящее среднее возвращает выходные данные в столбце С, который использовался для построения сглаженной кривой в диаграмме. Обратите внимание, что первые пять ячеек выходного диапазона содержат ошибочное значение #Н/Д.
Если интервал равен п, вы всегда будете иметь п-1 ошибочное значение #Н/Д в начале выходного диапазона. Включение этих значений в диаграмму не создает трудностей, так как Excel просто отставляет пустой начальную область сглаженной кривой.
| A | B | C | D | E | F | G |
| 1 | Месяц | Спрос (руб.) |
| 2 | январь 2004 | 120325 |
| 3 | февраль 2004 | 125600 |
| 4 | март 2004 | 126000 |
| 5 | апрель 2004 | 125400 |
| 6 | май 2004 | 135200 |
| 7 | июнь 2004 | 142000 |
| 8 | июль 2004 | 145700 |
| 9 | август 2004 | 148100 |
| 10 | сентябрь 2004 | 152000 |
| 11 | октябрь 2004 | 156000 |
| 12 | ноябрь 2004 | 151000 |
| 13 | декабрь 2004 | 152300 |
| 14 | январь 2005 | 144400 |
| 15 | февраль 2005 | 143900 |
| 16 | март 2005 | 156200 |
| 17 | апрель 2005 | 157500 |
| 18 | май 2005 | 162000 |
| 19 | июнь 2005 | 174800 |
| 20 | июль 2005 | 176900 |
| 21 | август 2005 | 184000 |
| 22 | сентябрь 2005 | 197200 |
| 23 | октябрь 2005 | 202600 |
| 24 | ноябрь 2005 | 225000 |
| 25 | декабрь 2005 | 255800 |
Рис. 1.Спрос в месяц
| 1 | Месяц | Спрос (руб.) | Скользящее среднее по 6 месяцам |
| 2 | январь 2004 | 120325 | #Н/Д |
| 3 | февраль 2004 | 125600 | #Н/Д |
| 4 | март 2004 | 126000 | #Н/Д |
| 5 | апрель 2004 | 125400 | #Н/Д |
| 6 | май 2004 | 135200 | #Н/Д |
| 7 | июнь 2004 | 142000 | 129087,5 |
| 8 | июль 2004 | 145700 | 133316,7 |
| 9 | август 2004 | 148100 | 137066,7 |
| 10 | сентябрь 2004 | 152000 | 141400 |
| 11 | октябрь 2004 | 156000 | 146500 |
| 12 | ноябрь 2004 | 151000 | 149133,3 |
| 13 | декабрь 2004 | 152300 | 150850 |
| 14 | январь 2005 | 144400 | 150633,3 |
| 15 | февраль 2005 | 143900 | 149933,3 |
| 16 | март 2005 | 156200 | 150633,3 |
| 17 | апрель 2005 | 157500 | 150883,3 |
| 18 | май 2005 | 162000 | 152716,7 |
| 19 | июнь 2005 | 174800 | 156466,7 |
| 20 | июль 2005 | 176900 | 161883,3 |
| 21 | август 2005 | 184000 | 168566,7 |
| 22 | сентябрь 2005 | 197200 | 175400 |
| 23 | октябрь 2005 | 202600 | 182916,7 |
| 24 | ноябрь 2005 | 225000 | 193416,7 |
| 25 | декабрь 2005 | 255800 | 206916,7 |
Рис. 2.Скользящее среднее по 6 месяцам
Источник: studopedia.ru
Использование пакета анализа
Пакет анализа представляет собой надстройкуExcel– набор средств и инструментов, расширяющих стандартные возможности Excel, связанные с анализом данных. Возможности надстройки Пакет анализа данных: 1. Однофакторный дисперсионный анализ. 2. Двухфакторный дисперсионный анализ с повторениями. 3. Двухфакторный дисперсионный анализ без повторений.
4. Корреляция. 5. Ковариация. 6. Описательная статистика. 7. Экспоненциальное сглаживание. 8.Двухвыборочный F-тест для дисперсий. 9. Анализ Фурье.
10. Гистограмма. 11. Скользящее среднее. 12. Генерация случайных чисел. 13. Ранг и перцентиль. 14.
Регрессия. 15. Выборка. 16. Парный двухвыборочный t-тест для средних. 17.Двухвыборочный t-тест с одинаковыми дисперсиями. 18.Двухвыборочный t-тест с разными дисперсиями. 19.Двухвыборочный z-тест для средних.
Для того, чтобы воспользоваться одним из инструментов Пакета анализа исследуемые данные следует представить в виде таблицы, где столбцами являются соответствующие показатели. При создании таблицы Excel информация вводится в отдельные ячейки. Совокупность ячеек, содержащих анализируемые данные, называется входным интервалом.
Для того, чтобы воспользоваться набором средств и инструментов Пакет анализа необходимо произвести его установку. Необходимо выполнить следующие действия: Открыть панель быстрого доступа. И из меню выбрать Другие команды.
Вызов окна ПараметрыExcel Появится диалоговое окно ПараметрыExcel, в правом окне выбрать функцию Пакет анализаVBA. 
Рис.Настройка Пакета анализа. Щелкнуть по кнопке Перейти и нажать на ОК. Появиться диалоговое окно Надстройки. 
Рис.Диалоговое окно Настройки.
Ставим флажок в поле Пакет анализа и подтверждаем свой выбор нажатием на клавишу ОК. Т.о. установили пакет анализа. Для дальнейшей работы на ленте выбираем вкладку Данные. И выбираем функцию Анализ данных. 
Рис.Вызов функции Анализ данных. Далее выбираем команду Данные→Анализ→Анализ данных.
В списке Инструменты анализа открывшегося окна выберите пункт Описательная статистика
Рис.Работа с функцией Анализ данных Появляется диалоговое окно Описательная статистика. 
Рис.
Диалоговое окно Описательная статистика Заполните окно параметров статистики следующим образом: В поле Входной интервал – диапазон ячеек с данными для статанализа, в поле Выходной интервал – адрес ячейки, куда запишется результат анализа.В параметрах вывода, устанавливаем флажок в поле Итоговая статистика и нажимаем на ОК. 
Рис.
Ввод данных с диалоге Описательная статистика В результате произведенных операций получим рассчитанные показатели описательной статистики. 
Рис. Результаты Описательной статистики.
Диаграммы.
Ограничение
Для продолжения скачивания необходимо пройти капчу:
Источник: studfile.net
Включение блока инструментов «Анализ данных» в Microsoft Excel
Программа Excel – это не просто табличный редактор, но ещё и мощный инструмент для различных математических и статистических вычислений. В приложении имеется огромное число функций, предназначенных для этих задач. Правда, не все эти возможности по умолчанию активированы. Именно к таким скрытым функциям относится набор инструментов «Анализ данных». Давайте выясним, как его можно включить.
Включение блока инструментов
Чтобы воспользоваться возможностями, которые предоставляет функция «Анализ данных», нужно активировать группу инструментов «Пакет анализа», выполнив определенные действия в настройках Microsoft Excel. Алгоритм этих действий практически одинаков для версий программы 2010, 2013 и 2016 года, и имеет лишь незначительные отличия у версии 2007 года.
Активация
- Перейдите во вкладку «Файл». Если вы используете версию Microsoft Excel 2007, то вместо кнопки «Файл» нажмите значок Microsoft Office в верхнем левом углу окна.
После выполнения этих действий указанная функция будет активирована, а её инструментарий доступен на ленте Excel.
Запуск функций группы «Анализ данных»
Теперь мы можем запустить любой из инструментов группы «Анализ данных».
-
Переходим во вкладку «Данные».
Выбираем ту функцию, которой хотим воспользоваться и жмем на кнопку «OK».
Работа в каждой функции имеет свой собственный алгоритм действий. Использование некоторых инструментов группы «Анализ данных» описаны в отдельных уроках.
Как видим, хотя блок инструментов «Пакет анализа» и не активирован по умолчанию, процесс его включения довольно прост. В то же время, без знания четкого алгоритма действий вряд ли у пользователя получится быстро активировать эту очень полезную статистическую функцию.
Мы рады, что смогли помочь Вам в решении проблемы.
Источник: lumpics.ru