Описательная статистика на базе Пакета анализа данных Excel. Двухфакторный дисперсионный анализ с повторениями

Юлия Перминова

Тренер Учебного центра Softline с 2008 года.

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

Полезное дополнение. Вы также можете создавать сводные диаграммы на основе сводных таблиц, которые будут автоматически обновляться при их изменении. Это полезно, если вам, например, нужно регулярно создавать отчёты по одним и тем же параметрам.

Как работать

Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.

  1. Откройте файл с таблицей, данные которой надо проанализировать.
  2. Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
  3. Должно появиться диалоговое окно «Создание сводной таблицы».
  4. Настройте отображение данных, которые есть у вас в таблице.

Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» - в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц - получим более детальную информацию за конкретный период.

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

Можно её детализировать, например, по странам. Переносим «Страны».

Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.

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

Полезное дополнение. Координаты нигде прописывать не нужно - достаточно лишь корректно указать географическое название в таблице.

Как работать

  1. Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
  2. Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
  3. Выделите диапазон данных для анализа.
  4. На вкладке «Вставка» есть кнопка 3D-карта.

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

Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.

3. Лист прогнозов

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

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

Как работать

  1. Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
  2. Выделите два ряда данных.
  3. На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
  4. В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
  5. Выберите дату окончания прогноза.

В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).

Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.

На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза - график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия - это и есть прогноз.

4. Быстрый анализ

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

Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).

Как работать

  1. Откройте таблицу с данными для анализа.
  2. Выделите нужный для анализа диапазон.
  3. При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.

В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.

Также можно проставить в ячейках разноцветные значки: зелёные - наибольшие значения, красные - наименьшие.

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

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

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

Примечание: Чтобы включить функцию Visual Basic для приложений (VBA) для пакета анализа, вы можете загрузить надстройку " Пакет анализа - VBA " таким же образом, как и при загрузке пакета анализа. В диалоговом окне Доступные надстройки установите флажок Пакет анализа - VBA .

Чтобы загрузить пакет анализа в Excel для Mac, выполните указанные ниже действия.

    В меню Сервис выберите пункт надстройки Excel .

    В окне Доступные надстройки установите флажок Пакет анализа , а затем нажмите кнопку ОК .

    1. Если надстройка Пакет анализа отсутствует в списке поля Доступные надстройки , нажмите кнопку Обзор , чтобы найти ее.

      Если появляется сообщение о том, что пакет анализа не установлен на компьютере, нажмите кнопку Да , чтобы установить его.

      Выйдите из приложения Excel и перезапустите его.

      Теперь на вкладке Данные доступна команда Анализ данных .

Я не могу найти пакет анализа в Excel для Mac 2011

Существуют несколько сторонних надстроек, которые предоставляют функции пакета анализа для Excel 2011.

Вариант 1. Скачайте статистическое программное обеспечение надстройки КСЛСТАТ для Mac и используйте его в Excel 2011. КСЛСТАТ содержит более 200 основных и расширенных статистических средств, включающих все функции пакета анализа.

    Выберите версию КСЛСТАТ, соответствующую операционной системе Mac OS, и загрузите ее.

    Откройте файл Excel, содержащий данные, и щелкните значок КСЛСТАТ, чтобы открыть панель инструментов КСЛСТАТ.

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

Вариант 2. Скачайте Статплус: Mac LE бесплатно из Аналистсофт, а затем используйте Статплус: Mac LE с Excel 2011.

Вы можете использовать Статплус: Mac LE для выполнения многих функций, которые ранее были доступны в пакетах анализа, таких как регрессия, гистограммы, анализ вариации (Двухфакторный дисперсионный обработки) и t-тесты.

    Перейдите на веб-сайт аналистсофт и следуйте инструкциям на странице загрузки.

    После загрузки и установки Статплус: Mac LE откройте книгу, содержащую данные, которые нужно проанализировать.

Microsoft Excel предлагает средства для анализа статистических данных. Такие встроенные функции, как СРЗНАЧ (AVERAGE), МЕДИАНА (MEDIAN) и МОДА (MODE), могут использоваться для проведения анализа данных. Если встроенных статистических функций недостаточно, необходимо обратиться к пакету Анализ данных .

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

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

1. Выберите в меню Сервис команду Анализ данных . При первом выборе этой команды Excel загружает файл с диска. Затем на экране появится окно диалога Анализ данных (рис. 2.19).

Рис. 2.19. Окно диалога Анализ данных

2. Чтобы использовать какой-либо из инструментов анализа, выберите его имя в списке и нажмите кнопку ОК.

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

Если команда Анализ данных отсутствуетв меню Сервис или формула, содержащая функцию из пакета анализа, возвращает ошибочное значение MЯ?(# NAME?), выберите в меню Сервис команду Надстройки , затем Пакет анализа в списке надстроек, после чего нажмите кнопку ОК . Если Пакет анализа отсутствует в списке надстроек, вы должны установить его, запустив программу Setup.

При анализе данных часто возникает необходимость определения различных статистических характеристик или параметров распределения. С помощью Microsoft Excel можно анализировать распределение, используя несколько инструментов: встроенные статистические функции, функции для оценки разброса данных, инструмент Описательная статистика (Descriptive Statistics), который предоставляет удобные сводные таблицы основных параметров распределения, инструменты Гистограмма (Histogram), Ранг и персентиль (Rank and Percentile).

Встроенные статистические функции Microsoft Excel применяются при проведении статистического анализа данных. В данном разделе мы ограничимся обсуждением наиболее часто используемых статистических функций. Кроме них Excel также предлагает более сложные функции ЛИНЕЙН (LINEST), ЛГРФПРИБЛ (LOGEST), ТЕНДЕНЦИЯ (TREND) и РОСТ (GROWTH), которые работают с числовыми массивами.

Описательная статистика (Descriptive Statistics) позволяет создать таблицу основных статистических характеристик для одного или нескольких множеств входных значений. Выходной диапазон содержит таблицу со статистическими характеристиками для каждой переменной входного диапазона: среднее, стандартная ошибка, медиана, мода, стандартное отклонение и дисперсия выборки, коэффициент эксцесса, коэффициент асимметрии, размах, минимальное значение, максимальное значение, сумма, количество значений, k -е наибольшее и наименьшее значения (для любого заданного k ) и доверительный интервал для среднего.

Для использования Описательная статистика в меню Сервис выберите команду Анализ данных , затем в списке Инструменты анализа окна диалога Анализ данных выберите инструмент Описательная статистика и нажмите кнопку ОК . Появится окно диалога, показанное на рис. 2.20.

Рис. 2.20. Окно диалога Описательная статистика

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

Чтобы получить представленную выше таблицу статистических характеристик, установите флажки в области Параметры вывода .

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

Анализ данных с помощью диаграмм

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

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

Можно создать либо внедренную диаграмму, либо лист диаграммы. Внедренная диаграмма – это объект, расположенный на листе и сохраняемый вместе с листом при сохранении книги. Внедренные диаграммы также связаны с данными и обновляются при изменении исходных данных. Лист диаграммы – лист книги, содержащий только диаграмму. Листы диаграммы связаны с данными таблиц и обновляются при изменении данных в таблице.

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

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

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

Ряд данных – группа связанных точек данных диаграммы, отображающая значение строк или столбцов листа. Каждый ряд данных отображается по-своему. На диаграмме может быть отображен один или несколько рядов данных. На круговой диаграмме отображается только один ряд данных.

Чтобы изменить текст легенды или имя ряда данных на листе, выберите ячейку, содержащую изменяемое имя ряда, введите новое имя и нажмите клавишу ENTER.

Чтобы изменить текст легенды или имя ряда данных на диаграмме, выберите нужную диаграмму, а затем выберите команду Диаграмма Исходные данные . На вкладке Ряды выберите изменяемые имена рядов данных. В поле Имя укажите ячейку листа, которую следует использовать как легенду или имя ряда. Также можно просто ввести нужное имя. Если в поле Имя ввести имя, то текст легенды или имя ряда потеряют связь с ячейкой листа.

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

При большом диапазоне изменения значений для разных рядов данных в линейчатой диаграмме или при смещении типов данных (таких, как цена и объем) есть возможность отобразить один или несколько рядов данных на вспомогательной оси. Шкала этой оси соответствует значениям для соответствующих рядов:

– выберите ряды данных, которые нужно отобразить на вспомогательной оси, щелчком мыши;

Формат Ряды – вкладка Ось ;

– установите переключатель в положение По вспомогательной оси .

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

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

Диаграмма Тип диаграммы – на вкладках Стандартные или Нестандартные выберите необходимый тип.

Для использования типов диаграмм конус, цилиндр или пирамида в объемной диаграмме или гистограмме выберите в поле Тип диаграммы в меню Стандартные пункт Цилиндр, Конус или Пирамида, а затем установите значок в поле Применить к .

Процедура изменения цветов, узора, ширины линии или типа рамки для маркеров данных, области диаграммы, области построения, сетки, осей и подписей делений на плоских и объемных диаграммах, линий тренда и планок погрешностей на плоских диаграммах, а также стенки и основания на объемных диаграммах:

– установить указатель на изменяемый элемент диаграммы и дважды нажать кнопку мыши;

– при необходимости выбрать вкладку Узор и указать нужные параметры.

Для указания эффекта заливки необходимо выбрать соответствующую команду, а затем указать нужные параметры на вкладках Градиентная, Текстура и Узор .

Работа с таблицами формата Список

Список – это упорядоченный набор данных, состоящий из строки заголовков (описания данных) и строк данных, которые могут быть числовыми и текстовыми.

Размер списка ограничен размерами одного рабочего листа, т.е. список может иметь не более 256 полей и не более 65 535 записей. Полями принято называть столбцы списка, а записями – строки.

– список обязательно должен содержать строку заголовков;

– в каждом столбце должна содержаться однотипная информация. Например, не следует смешивать в одном столбце даты и обычный текст;

– в списке не должно быть пустых строк;

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

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

Excel обладает мощными средствами для работы со списками. Это:

– пополнение списка с помощью формы;

– фильтрация списка;

– сортировка списка;

– подведение промежуточных итогов;

– создание итоговой сводной таблицы на основе данных списка.

Для того чтобы воспользоваться любым из этих инструментов, нужно установить курсор на одну из ячеек списка.

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

Рис. 2.21. Форма ввода данных

Если вы выбрали первый способ, то используйте команду контекстного меню Выбрать из списка. Excel избавит вас от необходимости много раз набирать один и тот же текст.

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

Индикатор в правом верхнем углу формы показывает номер выбранной записи и общее число записей в форме.

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

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

Форму ввода можно использовать не только для ввода данных. Она позволяет просматривать существующие записи, редактировать их, удалять и выборочно отображать данные по определенному критерию.

Фильтрация списков

В Excel существует два типа фильтров: Автофильтр и Расширенный фильтр .

Перед тем как использовать Автофильтр , выделите любую ячейку списка. Затем выберите команду Данные Фильтр Автофильтр . При включении Автофильтра возле имен полей списка появятся кнопки со стрелками.

При щелчке по любой из этих кнопок раскрывается меню (рис. 2.22), содержащее команды и список значений данного поля. С помощью этого меню можно отобрать все записи с заданным значением поля.

Рис. 2.22. Вид меню, содержащего команды и список значений поля

Обратите внимание на цвет стрелок на кнопках Автофильтра : если Автофильтр включен, кнопки окрашиваются в синий цвет.

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

Если задан сложный критерий, то придется отменять составляющие условия отбора по очереди. Иногда бывает проще отказаться от Автофильтра , выбрав команду Данные Фильтр Автофильтр , а потом установить Автофильтр снова.

Кроме команды Все , в раскрывающемся меню кнопок Автофильтра есть еще одна команда Первые 10 ..., которая используется для полей числового типа или дат. Эта команда покажет «горячую десятку» вашего списка.

Пусть необходимо узнать расходы за последние три дня. Щелкните по кнопке Автофильтра в столбце Дата , выберите в раскрываемся меню команду Первые 10 ..., в диалоговом окне сделайте установки, как на рис. 2.23.

Рис. 2.23. Диалоговое окно установки расходов за последние 3 дня

В окне Наложение условия по списку можно установить любое количество наибольших (или наименьших) элементов, которое хотите отобразить. Если вы хотите оставить процент записей (например, 10% наименьших значений), в третьем окне вместо Элементов списка установите % от количества элементов . При создании сложного условия отбора команда Первые 10 ... всегда применяется ко всему списку.

Иногда стандартных условий Автофильтра оказывается недостаточно. Для создания собственного Автофильтра необходимо:

– для выбранного поля (например, Менеджер ) из раскрывающегося меню кнопки Автофильтра выбрать команду (Условие …);

– в диалоговом окне Пользовательский автофильтр (рис. 2.24) задать условия отбора значений списка.

Рис. 2.24. Окно Пользовательский автофильтр

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

Для полей числового типа или дат используются следующие правила:

И , когда интересует область между двумя числами или датами;

ИЛИ , если интересует область вне интервала, заданного двумя числами или датами.

Расширенный фильтр

Часто для отбора нужной информации из списка бывает вполне достаточно Автофильтра или пользовательского фильтра. Однако для решения сложной задачи приходится прибегать к помощи расширенной фильтрации. Расширенный фильтр гораздо гибче Автофильтра , но чтобы воспользоваться им, придется выполнить подготовительные действия.

С помощью Расширенного фильтра (рис. 2.25) можно:

– определить более сложный критерий фильтрации;

– помещать результат отбора данных на другое место и даже на новый лист рабочей книги;

– устанавливать вычисляемый критерий отбора.

Рис. 2.25. Окно Расширенный фильтр

Чтобы воспользоваться Расширенным фильтром , необходимо задать диапазон критериев.

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

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

Условия отбора следует вносить в пустые ячейки диапазона критериев. Условия отбора, расположенные в ячейках одной строки, соединяются оператором И . Условия, расположенные на разных строках, соединяются оператором ИЛИ . Диапазон критериев может состоять из любого количества строк.

Область ячеек, содержащих критерии, должна отделяться от списка, по крайней мере, одной пустой строкой.

Для того чтобы отключить Расширенный фильтр , используют команду Данные Фильтр Отобразить все.

При использовании вычисляемого критерия отбор производится «по несуществующему полю». При создании формул вычисляемых критериев всегда ссылайтесь на первую строку списка, а не на строку заголовков. Если в формулу будут подставляться значения вне списка, используют абсолютные ссылки.

Если отфильтрованный список должен быть помещен на другой лист рабочей книги, сначала переходят на этот лист и только потом обращаются к команде Данные Фильтр Расширенный фильтр.

ЗАДАНИЕ № 1

Статистический анализ данных в программе MS Excel

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

В соответствии с методическими указаниями к лабораторной работе «Измерение скорости движения автомобилей» (по дисциплине «Изыскание и проектирование автомобильных дорог») обработать экспериментальные данные измерений методами математической статистики в программе Excel. Для чего:

1. Вычислить статистические характеристики, используя встроенные функции: - минимальное значение скорости движения Vмин;

Максимальное значение скорости движения Vмакс; - среднее значение скорости движения Vср;

Стандартное отклонение S;

Стандартное отклонение среднего Sср;

Коэффициент Стьюдента (для определения доверительного интервала) t; - доверительный интервал для Р = 0.95.

2. Получить статистические характеристики, используя инструмент « Описательная статистика » из дополнительного пакета «Анализ данных ».

3. Построить гистограмму распределения скорости движения.

4. Построить кумулятивную кривую (кривую накопленной частости).

5. Построить теоретическую кривую распределения скорости движения.

Для получения достаточного количества исходных данных (результатов измерений скорости) использовать имитационный эксперимент с помощью инструмента «Генерация случайных чисел » дополнения «Анализ данных ».

При выполнении п.п. 3 и 4 подобрать интервал скоростей («карман» – в терминологии Excel), позволяющий получить наиболее симметричную гистограмму, демонстрирующую нормальный закон распределения.

Образец выполнения приведен в прилагаемом файле ОсновыПК1-Студент.xls.

Методические указания

Предположим, что мы проделали серию из 10 опытов, измеряя некоторую величину Х. Таблица 1. Примерный вид листа «Обработка эксперимента»

Записи в колонках D и Е – это подсказки, которые помогут разобраться с тем, какие характеристики мы будем рассчитывать. Колонка F у Вас должна быть пока пустой, в нее будут помещены наши формулы.

Обработку результатов начнем с расчета числа опытов n .

Для определения числа значений используется специальная функция, которая называется СЧЕТ . Для ввода формулы с функциями используется Мастер функций , который запускается командой «Вставка функции» через меню «Вставка» – «Функция» или кнопкой на панели инструментов с обозначением f x .

Щелкнем мышкой по ячейке F6 , где должен находиться результат и запустим Мастер функций.

Первый шаг работы (рисунок 1) служит для выбора нужной функции.

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

Список функций упорядочен по алфавиту, что позволяет без труда найти нужную нам функцию СЧЕТ («Подсчитывает количество чисел в списке аргументов»).

Выделив щелчком эту функцию, нажимаем кнопку Ok и переходим к шагу 2.

Второй шаг (рисунок 2) служит для задания аргументов функции.

Функции СЧЕТ надо указать, какие числа ей надо пересчитывать, или в каких ячейках находятся эти числа. Следующие два этапа обработки серии опытов проводятся аналогично.

В ячейке F7 c помощью функции СРЗНАЧ рассчитывается среднее значение выборки, в ячейке F8 – стандартное отклонение выборки, с помощью функции СТАНДОТКЛОН. .

Аргументами этих функций служит все тот же диапазон ячеек.

Для расчета доверительного интервала необходимо определить коэффициент Стьюдента. Он зависит от вероятности ошибки (при обычно задаваемой надежности 95% вероятность ошибки составляет 5%), и от числа степеней свободы n-1 ).

Для нахождения коэффициента Стьюдента используется статистическая функция Excel СТЬЮДРАСПОБР (“Стьюдента распределение обратное“). Особенностью этой функции является то, что первый аргумент, число 5% (или 0,05) вводится в соответствующее окно с клавиатуры. Для второго указываем адрес ячейки, где находится значение n , затем дописываем в окне “-1”. Получаем запись “F6-1 ”.

Для нахождения доверительного интервала используется обычная формула умножения. Конечно, вместо букв там должны стоять адреса ячеек, где находятся коэффициент Стьюдента и стандартное отклонение среднего. Как правило, значение доверительного интервала округляется до одной значащей цифры, такой же порядок окружения должен быть и у среднего. Поэтому окончательный результат можно записать так: с 95%-ной надежностью Х = 14,80±0,05 . В заключение посчитаем относительную ошибку определения Х: = ДИ / Х ср (формула: “=F11/F7 ”). Значение относительной ошибки обычно выражают в процентах, у нас 0,3%.

Для выполнения заданий 2 и 3 используется надстройка «Пакет анализа» (из меню Сервис  .Анализ данных  Гистограмма).

Для установки надстройки вызвать меню Сервис  Надстройки и из предлагаемого списка доступных к установке надстроек выбрать «Пакет анализа» (см. Установка надстроек

Excel на компьютере.doc).

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

Чтобы воспользоваться возможностями, которые предоставляет функция «Анализ данных» , нужно активировать группу инструментов «Пакет анализа» , выполнив определенные действия в настройках Microsoft Excel. Алгоритм этих действий практически одинаков для версий программы 2010, 2013 и 2016 года, и имеет лишь незначительные отличия у версии 2007 года.

Активация

  1. Перейдите во вкладку «Файл» . Если вы используете версию Microsoft Excel 2007, то вместо кнопки «Файл» нажмите значок Microsoft Office в верхнем левом углу окна.
  2. Кликаем по одному из пунктов, представленных в левой части открывшегося окна – «Параметры» .
  3. В открывшемся окне параметров Эксель переходим в подраздел «Надстройки» (предпоследний в списке в левой части экрана).
  4. В этом подразделе нас будет интересовать нижняя часть окна. Там представлен параметр «Управление» . Если в выпадающей форме, относящейся к нему, стоит значение отличное от «Надстройки Excel» , то нужно изменить его на указанное. Если же установлен именно этот пункт, то просто кликаем на кнопку «Перейти…» справа от него.
  5. Открывается небольшое окно доступных надстроек. Среди них нужно выбрать пункт «Пакет анализа» и поставить около него галочку. После этого, нажать на кнопку «OK» , расположенную в самом верху правой части окошка.
  6. После выполнения этих действий указанная функция будет активирована, а её инструментарий доступен на ленте Excel.

    Запуск функций группы «Анализ данных»

    Теперь мы можем запустить любой из инструментов группы «Анализ данных» .


    Работа в каждой функции имеет свой собственный алгоритм действий. Использование некоторых инструментов группы «Анализ данных» описаны в отдельных уроках.

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



Есть вопросы?

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам: