Применение описательной статистики в Microsoft Excel

How to dou

Решение задач по статистике в Excel. Использование надстройки «Анализ данных»

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

В столбце A на листе показана рекомендуемая розничная цена (SRP). В столбце B рабочий лист показывает единицы, продаваемые каждой книгой, через одну популярную книжную розетку. Вы можете использовать инструмент «Описательная статистика» для суммирования этого набора данных.

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

Нажмите кнопку командной строки Data Data, чтобы сообщить Excel, что вы хотите рассчитать описательную статистику.

Excel отображает диалоговое окно «Анализ данных».

В диалоговом окне «Анализ данных» выделите запись «Описательная статистика» в списке «Инструменты анализа» и нажмите «ОК».

Excel отображает диалоговое окно «Описательная статистика».

В разделе «Ввод» диалогового окна «Описательная статистика» укажите данные, которые вы хотите описать.

Чтобы идентифицировать данные, которые вы хотите описать статистически: Нажмите текстовое поле «Диапазон ввода», а затем введите справочную таблицу диапазона данных для данных. В случае примера рабочего листа входной диапазон составляет $ A $ 1: $ C $ 38. Обратите внимание, что Excel хочет, чтобы адрес диапазона использовал абсолютные ссылки — следовательно, знаки доллара.

Чтобы упростить просмотр или выбор диапазона рабочих листов, нажмите кнопку рабочего листа в правом конце текстового поля «Диапазон ввода». Когда Excel скрывает диалоговое окно «Описательная статистика», выберите диапазон, который вы хотите, перетащив мышь. Затем нажмите кнопку рабочего листа еще раз, чтобы повторно отобразить диалоговое окно «Описательная статистика».

Чтобы определить, упорядочены ли данные в столбцах или строках: Выберите переключатель «Столбцы» или «Строки».

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

В области «Параметры вывода» диалогового окна «Описательная статистика» укажите, где и как Excel должен создавать статистику.

Чтобы указать, где должна располагаться описательная статистика, которую рассчитывают Excel: Выберите один из трех переключателей здесь: диапазон вывода, новый рабочий лист и новая рабочая книга. Как правило, вы размещаете статистику на новом рабочем листе в существующей книге.Для этого просто выберите переключатель «Новый рабочий лист».

Чтобы определить, какие статистические показатели вы хотите вычислить: Установите флажки «Параметры вывода». Установите флажок «Сводная статистика», чтобы сообщить Excel о вычислении статистических показателей, таких как среднее значение, режим и стандартное отклонение. Установите флажок «Уровень доверия для среднего», чтобы указать, что вы хотите получить уровень достоверности для среднего значения выборки.

Примечание: Если вы подсчитаете уровень достоверности для среднего значения выборки, вам нужно ввести процент доверия в текстовое поле. Установите флажки Kth Largest и Kth Smallest, чтобы указать, что вы хотите найти наибольшее или наименьшее значение в наборе данных.

После того, как вы описываете, где находятся данные и как рассчитывать статистику, нажмите «ОК». Вот статистика, которую рассчитывает Excel.

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

Вот новый рабочий лист с рассчитанной описательной статистикой.

Применение описательной статистики в Microsoft Excel

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

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

Использование описательной статистики

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

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

  • Медиана;
  • Мода;
  • Дисперсия;
  • Среднее;
  • Стандартное отклонение;
  • Стандартная ошибка;
  • Асимметричность и др.

Рассмотрим, как работает данный инструмент на примере Excel 2010, хотя данный алгоритм применим также в Excel 2007 и в более поздних версиях данной программы.

Подключение «Пакета анализа»

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

  1. Переходим во вкладку «Файл». Далее производим перемещение в пункт «Параметры».

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

  • Запускается окно стандартных надстроек Excel. Около наименования «Пакет анализа» ставим флажок. Затем жмем на кнопку «OK».
  • После вышеуказанных действий надстройка Пакет анализа будет активирована и станет доступной во вкладке «Данные» Эксель. Теперь мы сможем использовать на практике инструменты описательной статистики.

    Применение инструмента «Описательная статистика»

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

      Переходим во вкладку «Данные» и выполняем щелчок по кнопке «Анализ данных», которая размещена на ленте в блоке инструментов «Анализ».

    Открывается список инструментов, представленных в Пакете анализа. Ищем наименование «Описательная статистика», выделяем его и щелкаем по кнопке «OK».

    После выполнения данных действий непосредственно запускается окно «Описательная статистика».

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

    Читайте также  Создание гистограммы в Microsoft Excel

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

    В первом случае нужно указать конкретный диапазон на текущем листе или его верхнюю левую ячейку, куда будет выводиться обработанная информация. Во втором случае следует указать название конкретного листа данной книги, где будет отображаться результат обработки. Если листа с таким наименованием в данный момент нет, то он будет создан автоматически после того, как вы нажмете на кнопку «OK». В третьем случае никаких дополнительных параметров указывать не нужно, так как данные будут выводиться в отдельном файле Excel (книге). Мы выбираем вывод результатов на новом рабочем листе под названием «Итоги».

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

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

    После того, как все указанные данные внесены, жмем на кнопку «OK».

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

  • После того, как данные «причесаны» можно приступать к их непосредственному анализу. Как видим, при помощи инструмента описательной статистики были рассчитаны следующие показатели:
    • Асимметричность;
    • Интервал;
    • Минимум;
    • Стандартное отклонение;
    • Дисперсия выборки;
    • Максимум;
    • Сумма;
    • Эксцесс;
    • Среднее;
    • Стандартная ошибка;
    • Медиана;
    • Мода;
    • Счет.
  • Если какие-то из вышеуказанных данных для конкретного вида анализа не нужны, то их можно удалить, чтобы они не мешали. Далее производится анализ с учетом статистических закономерностей.

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

    Применение описательной статистики в Microsoft Excel

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

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

    1. Характеристика пакета Excel

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

    Каждая единица информации занимает свою собственную ячей­ку (клетку) в создаваемой рабочей таблице. В каждой рабочей таблице 256 столбцов (из которых в новой рабочей таблице на экране видны, как правило, только первые 10 или 11 (от А до J или К) и 65 536 строк (из которых обычно видны только первые 15-20). Каждая новая рабочая книга содержит три чистых листа рабочих таблиц.

    Вся помещаемая в электронную таблицу информация хранится в от­дельных клетках рабочей таблицы. Но ввести информацию можно только в текущую клетку. С помощью адреса в строке формул и табличного курсора Excel ука­зывает, какая из клеток рабочей таблицы является те­кущей. В основе системы адресации клеток рабочей таблицы лежит комбинация буквы (или букв) столбца и номера строки, например A 2, B 12.

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

    2 Использование специальных функций

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

    Функция СРЗНАЧ вычисляет среднее арифметическое из нескольких массивов (аргументов) чисел. Аргументы число1, число2, . — это от 1 до 30 массивов для которых вычисляется среднее.

    Функция МЕДИАНА позволяет получать медиану заданной выборки. Медиана — это элемент выборки, число элементов выборки со значениями больше которого и меньше которого равно.

    Функция МОДА вычисляет наиболее часто встречающееся значение в выборке.

    Функция ДИСП позволяет оценить дисперсию по выборочным данным.

    Функция СТАНДОТКЛОН вычисляет стандартное отклонение.

    Функция ЭКСЦЕСС вычисляет оценку эксцесса по выборочным данным.

    Функция СКОС позволяет оценить асимметрию выборочного распределения.

    Функция КВАРТИЛЬ вычисляет квартили распределения. Функция имеет формат КВАРТИЛЬ(массив, значение), где массив – интервал ячеек, содержащих значения СВ; значение определяет какая квартиль должна быть найдена (0 – минимальное значение, 1 – нижняя квартиль, 2 – медиана, 3 – верхняя квартиль, 4 – максимальное значение распределения).

    Пример 1. Провести статистический анализ методом описательной статистики доходов населения в регионе 1 и регионе 2.

    Задания для самостоятельной работы

    1. Наблюдение посещаемости четырех внеклассных мероприятий в экспериментальном (20 человек) и контрольном (30 человек) классах дали значения (соответственно): 18, 20, 20, 18 и 15, 23, 10, 28. Требуется найти среднее значение, стандартное отклонение, медиану и квартили этих данных.

    2. Найти среднее значение, медиану, стандартное отклонение и квартили результатов бега на дистанцию 100 м у группы студентов (с): 12,8; 13,2; 13,0; 12,9; 13,5; 13,1.

    3. Определите верхнюю и нижнюю квартиль, выборочную асимметрию и эксцесс для данных измерений роста групп студенток: 164, 160, 157, 166, 162, 160, 161, 159, 160, 163, 170, 171.

    4. Найти наиболее популярный туристический маршрут из четырех реализуемых фирмой, если за неделю последовательно были реализованы следующие маршруты: 1, 3, 3, 2, 1, 1, 4, 4, 2, 4, 1, 3, 2, 4, 1, 4, 4, 3, 1, 2, 3, 4, 1, 1, 3.

    3. Использование инструмента Пакет анализа

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

    Для установки пакета Анализ данных в Excel сделайте следующее:

    — в меню Сервис выберите команду Надстройки;

    — в появившемся списке установите флажок Пакет анализа.

    Для использования статистического пакета анализа данных необходимо:

    • указать курсором мыши на пункт меню Сервис и щелкнуть левой кнопкой мыши;
    • в раскрывающемся списке выбрать команду Анализданных (если команда Анализ данных отсутствует в меню Сервис, то необходимо установить в Excel пакет анализа данных);
    • выбрать строку Описательнаястатистика и нажать кнопку Оk
    • в появившемся диалоговом окне указать входной интервал, то есть ввести ссылки на ячейки, содержащие анализируемые данные;
    • указать выходной интервал, то есть ввести ссылку на ячейку, в которую будут выведены результаты анализа;
    • в разделе Группирование переключатель установить в положение по столбцам или по строкам;
    • установить флажок в поле Итоговая статистика и нажать Ок.

    Задание для самостоятельной работы

    1. В рабочей зоне производились замеры концентрации вредного вещества. Получен ряд значений (в мг./м 3 ): 12, 16, 15, 14, 10, 20, 16, 14, 18, 14, 15, 17, 23, 16. Необходимо определить основные выборочные характеристики.

    Читайте также  Извлечение изображения из документа Microsoft Excel

    Основы анализа данных

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

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

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

    Анализ данных в Microsoft Excel

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

    Обращение к Пакету анализа . Средства, включенные в пакет анализа данных, доступны через команду Анализ данных меню Сервис. Если эта команда отсутствует в меню , в меню Сервис/Надстройки необходимо активировать пункт «Пакет анализа».

    Далее мы рассмотрим некоторые инструменты, включенные в Пакет анализа .

    Описательная статистика

    Описательная статистика (Descriptive statistics ) — техника сбора и суммирования количественных данных, которая используется для превращения массы цифровых данных в форму, удобную для восприятия и обсуждения.

    Цель описательной статистики — обобщить первичные результаты, полученные в результате наблюдений и экспериментов.

    Пусть дан набор данных А, представленный в таблице 8.1.

    Таблица 8.1. Набор данных А

    x y
    3 9
    2 7
    4 12
    5 15
    6 17
    7 19
    8 21
    9 23,4
    10 25,6
    11 27,8

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

    В состав описательной статистики входят такие характеристики: среднее ; стандартная ошибка ; медиана ; мода; стандартное отклонение ; дисперсия выборки; эксцесс ; асимметричность; интервал ; минимум ; максимум ; сумма; счет.

    Отчет » Описательная статистика » для двух переменных их набора данных А приведен в таблице 8.2.

    Таблица 8.2. Описательная статистика для набора данных А

    x y
    Среднее 6,5 17,68
    Стандартная ошибка 0,957427108 2,210922382
    Медиана 6,5 18
    Стандартное отклонение 3,027650354 6,991550456
    Дисперсия выборки 9,166666667 48,88177778
    Эксцесс -1,2 -1,106006058
    Асимметричность -0,128299221
    Интервал 9 20,8
    Минимум 2 7
    Максимум 11 27,8
    Сумма 65 176,8
    Счет 10 10
    Наибольший (1) 11 27,8
    Наименьший (1) 2 7
    Уровень надежности (95,0%) 2,16585224 5,001457714

    Рассмотрим, что же представляют собой характеристики описательной статистики .

    Статистические функции Excel, которые необходимо знать

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

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

    В рамках данной статьи мы не будем затрагивать такие популярные статистические функции Excel, как СЧЕТ и СЧЕТЕСЛИ, для них подготовлен отдельный урок.

    СРЗНАЧ()

    Статистическая функция СРЗНАЧ возвращает среднее арифметическое своих аргументов.

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

    Если в рассчитываемом диапазоне встречаются пустые или содержащие текст ячейки, то они игнорируются. В примере ниже среднее ищется по четырем ячейкам, т.е. (4+15+11+22)/4 = 13

    Если необходимо вычислить среднее, учитывая все ячейки диапазона, то можно воспользоваться статистической функцией СРЗНАЧА. В следующем примере среднее ищется уже по 6 ячейкам, т.е. (4+15+11+22)/6 = 8,6(6).

    Статистическая функция СРЗНАЧ может использовать в качестве своих аргументов математические операторы и различные функции Excel:

    СРЗНАЧЕСЛИ()

    Если необходимо вернуть среднее арифметическое значений, которые удовлетворяют определенному условию, то можно воспользоваться статистической функцией СРЗНАЧЕСЛИ. Следующая формула вычисляет среднее чисел, которые больше нуля:

    В данном примере для подсчета среднего и проверки условия используется один и тот же диапазон, что не всегда удобно. На этот случай у функции СРЗНАЧЕСЛИ существует третий необязательный аргумент, по которому можно вычислять среднее. Т.е. по первому аргументу проверяем условие, по третьему – находим среднее.

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

    Если требуется соблюсти несколько условий, то всегда можно применить статистическую функцию СРЗНАЧЕСЛИМН, которая позволяет считать среднее арифметическое ячеек, удовлетворяющих двум и более критериям.

    Статистическая функция МАКС возвращает наибольшее значение в диапазоне ячеек:

    Статистическая функция МИН возвращает наименьшее значение в диапазоне ячеек:

    НАИБОЛЬШИЙ()

    Возвращает n-ое по величине значение из массива числовых данных. Например, на рисунке ниже мы нашли пятое по величине значение из списка.

    Чтобы убедиться в этом, можно отсортировать числа в порядке возрастания:

    НАИМЕНЬШИЙ()

    Возвращает n-ое наименьшее значение из массива числовых данных. Например, на рисунке ниже мы нашли четвертое наименьшее значение из списка.

    Если отсортировать числа в порядке возрастания, то все станет гораздо очевидней:

    МЕДИАНА()

    Статистическая функция МЕДИАНА возвращает медиану из заданного массива числовых данных. Медианой называют число, которое является серединой числового множества. Если в списке нечетное количество значений, то функция возвращает то, что находится ровно по середине. Если же количество значений четное, то функция возвращает среднее для двух чисел.

    Например, на рисунке ниже формула возвращает медиану для списка, состоящего из 14 чисел.

    Если отсортировать значения в порядке возрастания, то все становится на много понятней:

    Возвращает наиболее часто встречающееся значение в массиве числовых данных.

    Если отсортировать числа в порядке возрастания, то все становится гораздо понятней:

    Статистическая функция МОДА на данный момент устарела, точнее, устарела ее форма записи. Вместо нее теперь используется функция МОДА.ОДН. Форма записи МОДА также поддерживается в Excel для совместимости.

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

    Описательная статистика на базе пакета анализа данных Excel

    Краткая характеристика встроенного пакета анализа данных

    Предполагается, что студент обладает навыками работы в операционной системе Microsoft Windows и Microsoft Excel. Общий вид экрана при работе в Microsoft Excel представлен на рис. 2.2.

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

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

    2. Вариационные ряды и их статистические характеристики

    кнопке Параметры Excel, выбрать Надстройки, в нижней части окна, в поле Управление, выбрать Надстройки Excel, щелкнуть по кнопке Перейти, поставить галочку напротив Пакета анализа (рис. 2.3, я). На вкладке Данные в группе Анализ появится команда Анализ данных (рис. 2.3, б).

    Читайте также  Методы сравнения таблиц в Microsoft Excel

    Рис. 2.2. Общий вид экрана при работе в Microsoft Excel

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

    Дисперсионный анализ. Существует несколько видов дисперсионного анализа. Требуемый вариант выбирается с учетом числа факторов и имеющихся выборок из генеральной совокупности. Однофакторный дисперсионный анализ используется для проверки гипотезы о сходстве средних значений двух или более выборок, принадлежащих одной и той же генеральной совокупности. Этот метод распространяется также на тесты для двух средних (к которым относится, например, /-критерий). Двухфакторный дисперсионный анализ с повторениями представляет собой более сложный вариант однофакторного анализа с несколькими выборками для каждой группы данных. Двухфакторный дисперсионный анализ без повторения представляет собой двухфакторный анализ дисперсии, не включающий более одной выборки на группу. Используется для проверки гипотезы о том, что средние значения двух или нескольких выборок одинаковы (выборки принадлежат одной и той же генеральной совокупности). Этот метод распространяется также на тесты для двух средних, такие как /-критерий.

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

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

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

    Экспоненциальное сглаживание. Применяется для предсказания значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом прогнозе. При анализе используется константа сглаживания а, по величине которой определяется степень влияния на прогнозы погрешностей в предыдущем прогнозе. Для константы сглаживания наиболее подходящими являются значения от 0,2 до 0,3. Эти значения показывают, что ошибка текущего прогноза установлена на уровне от 20 до 30 % ошибки предыдущего прогноза. Более высокие значения константы ускоряют отклик, но могут привести к непредсказуемым выбросам. Низкие значения константы могут привести к большим промежуткам между предсказанными значениями.

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

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

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

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

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

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

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

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

    Т-тест. Этот вид анализа используется для проверки средних для различных типов генеральных совокупностей. Двухвыборочный ?-тест Стьюдента служит для проверки гипотезы о равенстве средних для двух выборок. Эта форма ^-теста предполагает совпадение дисперсий генеральных совокупностей и обычно называется гомоскедастическим ^-тестом. Двухвыборочный Етест Стьюдента используется для проверки гипотезы о равенстве средних для двух выборок данных из разных генеральных совокупностей. Эта форма Етеста предполагает несовпадение дисперсий генеральных совокупностей и обычно называется гетероскедастическим ^-тестом. Если тестируется одна и та же генеральная совокупность, используйте парный тест. Парный двухвыборочный ?-тест Стьюдента используется для проверки гипотезы о различии средних для двух выборок данных. В нем не предполагается равенство дисперсий генеральных совокупностей, из которых выбраны данные. Парный тест используется, когда имеется естественная парность наблюдений в выборках, например, когда генеральная совокупность тестируется дважды — до и после эксперимента. Одним из результатов теста является совокупная дисперсия (совокупная мера распределения данных вокруг среднего значения.

    Z-mecm. Двухвыборочный z-тест для средних с известными дисперсиями. Используется для проверки гипотезы о различии между средними двух генеральных совокупностей. Например, этот тест может использоваться для определения различия между характеристиками двух моделей теодолитов.

    Пример 2.3. На полиметаллическом руднике из 4 забоев взяты 70 проб из каждого забоя. Результаты химических анализов на содержание металла по пробам представлены в табл. 2.7.

    Требуется представить данные опробования в виде упорядоченного и интервального вариационных рядов.