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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Источник:
    http://lumpics.ru/descriptive-statistics-in-excel/

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

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

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

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

    Откройте вкладку Файл, нажмите кнопку Параметры и выберите категорию Надстройки.

    Если вы используете Excel 2007, нажмите кнопку Microsoft Office и выберите пункт Параметры Excel .

    В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

    Если вы используете Excel для Mac, в строке меню откройте вкладку Средства и в раскрывающемся списке выберите пункт Надстройки для Excel.

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

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

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

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

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

    Однофакторный дисперсионный анализ

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

    Двухфакторный дисперсионный анализ с повторениями

    Этот инструмент анализа применяется, если данные можно систематизировать по двум параметрам. Например, в эксперименте по измерению высоты растений последние обрабатывали удобрениями от различных изготовителей (например, A, B, C) и содержали при различной температуре (например, низкой и высокой). Таким образом, для каждой из 6 возможных пар условий <удобрение, температура>, имеется одинаковый набор наблюдений за ростом растений. С помощью этого дисперсионного анализа можно проверить следующие гипотезы:

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

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

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

    Читайте также  Транспонирование матриц в Excel

    Двухфакторный дисперсионный анализ без повторений

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

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

    Коэффициент корреляции, например Ковариация, — это мера экстента, в котором одновременно различаются две переменные измерения. В отличие от ковариации коэффициент корреляции масштабируется таким образом, чтобы его значение не зависело от единиц, в которых выражаются две переменные измерения. (Например, если две переменные измерения являются весом и высотой, значение коэффициента корреляции не меняется, если вес конвертируется из килограммов в килограммы). Значение любого коэффициента корреляции должно находиться в диапазоне от-1 до + 1 включительно.

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

    Вы можете использовать инструменты корреляции и ковариации в одном и том же параметре, если у вас есть N различных переменных измерения, которые потратили на набор отдельных пользователей. Средства корреляции и ковариации предоставляют выходную таблицу, матрицу, которая показывает коэффициент корреляции или ковариацию соответственно между каждой парой переменных измерения. Разница заключается в том, что коэффициенты корреляции масштабируются в зависимости от-1 и + 1 включительно. Соответствующие ковариации не масштабируются. Как коэффициент корреляции, так и ковариация — это величины экстентов, в которых две переменные различны друг от друга.

    Инструмент Ковариация вычисляет значение функции КОвариация на листе. P для каждой пары переменных измерения. (Прямое использование ковариации. Функция P вместо средства Ковариация является разумной альтернативой, если есть только две переменные измерения, т. е. N = 2.) Запись по диагонали в выходной таблице инструмента ковариации в строке i — это Ковариация переменной измерения i-ой. Это всего лишь дисперсия Генеральной совокупности для этой переменной, вычисленная функцией на листе var.P.

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

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

    Инструмент анализа «Экспоненциальное сглаживание» применяется для предсказания значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом прогнозе. При анализе используется константа сглаживания a, величина которой определяет степень влияния на прогнозы погрешностей в предыдущем прогнозе.

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

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

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

    С помощью этого инструмента вычисляется значение f F-статистики (или F-коэффициент). Значение f, близкое к 1, показывает, что дисперсии генеральной совокупности равны. В таблице результатов, если f 1, «P(F

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

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

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

    Совет: В Excel 2016 теперь можно создавать гистограммы и диаграммы Парето.

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

    N — число предшествующих периодов, входящих в скользящее среднее;

    A j — фактическое значение в момент времени j;

    F j — прогнозируемое значение в момент времени j.

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

    Инструмент «ранжирование и персентиль» формирует таблицу, содержащую порядковый и процентный ранги для каждого значения в наборе данных. Вы можете проанализировать относительные значения в наборе данных. Это средство использует функции ранжирования на листе. EQ и ПРОЦЕНТРАНГ. INC. Если вы хотите учитывать привязанные значения, используйте ранг. EQ , который обрабатывает привязанные значения в соответствии с одинаковым рангом или использует ранг.Функция AVG , возвращающая среднее значение ранга для привязанных значений.

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

    Средство регрессия использует функцию листа ЛИНЕЙН.

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

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

    Для всех трех средств, перечисленных ниже, значение t вычисляется и отображается как «t-статистика» в выводимой таблице. В зависимости от данных это значение t может быть отрицательным или неотрицательным. Если предположить, что средние генеральной совокупности равны, при t =0 «P(T

    Двухвыборочный z-тест для средних — это Двухвыборочный z-тест для средних и известных отклонений. Это средство используется для проверки гипотезы на то, что в двух или двусторонних вариантах есть различия между двумя единицами заполнения. Если вариативность неизвестна, то функция листа Z.Вместо этого следует использовать проверку .

    При использовании этого инструмента следует внимательно просматривать результат. «P(Z = ABS(z)), вероятность z-значения, удаленного от 0 в том же направлении, что и наблюдаемое z-значение при одинаковых средних значениях генеральной совокупности. «P(Z = ABS(z) или Z

    Дополнительные сведения

    Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

    Источник:
    http://support.microsoft.com/ru-ru/office/%D0%B8%D1%81%D0%BF%D0%BE%D0%BB%D1%8C%D0%B7%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5-%D0%BF%D0%B0%D0%BA%D0%B5%D1%82%D0%B0-%D0%B0%D0%BD%D0%B0%D0%BB%D0%B8%D0%B7%D0%B0-6c67ccf0-f4a9-487c-8dec-bdb5a2cefab6

    Обзор встроенных средств Excel для статистического анализа данных

    Основными средствами анализа статистических данных в Excel являются статистические процедуры надстройки Пакет анализа (Analysis ToolРак) и статистические функции библиотеки встроенных функций. Основные сведения обо всех этих средствах имеются в электронной справочной системе Excel.

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

    Статистические процедуры Пакета анализа

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

    Читайте также  Мгновенный переход на нужный раздел листа по кнопке - Пользовательский интерфейс - Excel - Каталог статей - Perfect Excel

    В Пакет анализа входят следующие статистические процедуры:

    1. генерация случайных чисел (Random number generation);
    2. выборка (Sampling);
    3. гистограмма (Histogram);
    4. описательная статистика (Descriptive statistics);
    5. ранги персентиль (Rank and percentile);
    6. двухвыборочный z-тест для средних (z-Test: Two Sample for Means);
    7. двухвыборочный t-тест для средних с одинаковыми дисперсиями (t-Test: Two-Sample Assuming Equal Variances);
    8. двухвыборочный t-тест для средних с различными дисперсиями (t-Test: Two-Sample Assuming Unequal Variances);
    9. парный двухвыборочный t-тест для средних (t-Test: Paired Two Sample for Means);
    10. двухвыборочный F-тест да я дисперсий (F-Test: Two Sample for Variances);
    11. коварнация (Covariance);
    12. корреляция (Correlation);
    13. рецессия (Regression);
    14. однофакторный дисперсионный анализ (ANOVA: Single Factor);
    15. двухфакторный дисперсионный анализ без повторений (ANOVA: Two Factor Without Replication);
    16. двухфакторный дисперсионный анализ с повторениями (ANOVA: Two Factor With Replication);
    17. скользящее среднее (Moving Average);
    18. экспоненциальное сглаживание (Exponential Smoothing);
    19. анализ Фурье (Fourier Analysis).

    Для доступа к процедурам Пакета анализа необходимо в меню Сервис (Tools) щелкнуть указателем мыши на строке Анализ данных (Data Analysis). Откроется диалоговое окно с соответствующим названием, в котором перечислены процедуры статистического анализа данных (рис. 1).

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

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

    Рис.2. Диалоговое окно процедуры Описательная статистика

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

    К числу общих для большинства процедур элементов управления относятся:

    • поле ввода Входной интервал (Input Range). В это поле вводится ссылка на диапазон, содержащий статистические данные, подлежащие обработке. Входной диапазон может быть столбцом пли группой столбцов (строкой или группой строк);
    • переключатель Группирование (Grouped By). В том случае, когда входной диапазон представляет собой столбец или группу столбцов, переключатель устанавливается в положение по столбцам (Columns). Если же входной диапазон представляет собой строку или группу строк, то переключатель устанавливается в положение по строкам (Rows). Более точным названием этого переключателя было бы название Расположение;
    • флажок Метки (Labels in First Row). Флажок устанавливается в тех случаях, когда первая строка (первый столбец) входного диапазона содержит заголовки. Если такие заголовки отсутствуют, флажок Метки не устанавливают. При этом Excel автоматически создает и выводит на экран стандартные названия для данных выходного диапазона (Столбец1, Столбец2,… или Строка 1. Строка2,…);
    • переключатели Выходной интервал/Новый рабочий лист/Новая книга (Output Range/New Worksheet/New Workbook). Эти переключатели определяют место вывода таблицы, содержащей результаты реализации статистической процедуры. В группе может быть выбран только одни переключатель.

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

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

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

    Эффективным и очень удобным в использовании средством парного регрессионного анализа и анализа временных рядов является процедура Добавить линию тренда (Add Trendline), входящая в комплекс графических средств Excel.

    Статистические функции библиотеки встроенных функций Excel

    Табличный процессор Excel имеет библиотеку встроенных функции рабочего листа (Worksheet function). Одним из разделов этой библиотеки является раздел Статистические функции. В этот раздел входят 83 функции, предназначенные для решения некоторых наиболее востребованных задач теории вероятностей и математической статистики.

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

    Когда в качестве какого-либо аргумента встроенной статистической функции введен текст, функция выдает сообщение об ошибке #ЗНАЧ! (#VALUE!). Если в качестве аргумента, который по определению должен быть целым числом, введено число не целое, Excel использует в качестве аргумента целую часть этот числа. Никакие сообщения об этом «несанкционированном округлении» на экран не выводятся.

    Источник:
    http://excel2010.ru/obzor-vstroennyx-sredstv-excel-dlya-statisticheskogo-analiza-dannyx.html

    Как включить анализ данных в Excel 2010, 2007, 2013

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

    При выполнении сложных аналитических задач по статистике (к примеру, корреляционного и дисперсионного анализа, расчетов по алгоритму Фурье, создания прогностической модели) пользователи часто интересуются, как добавить анализ данных в Excel. Обозначенный пакет функций предоставляет разносторонний аналитический инструментарий, полезный в ряде профессиональных сфер. Но он не относится к инструментам, включенным в Эксель по умолчанию и отображающимся на ленте. Выясним, как включить анализ данных в Excel 2007, 2010, 2013.

    Для Excel 2010, 2013

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

    Включение блока инструментов

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

    1. зайдите во вкладку «Файл», расположенную в верхней части ленты интерфейса;
    2. с левой стороны открывающегося меню найдите раздел «Параметры Эксель» и кликните по нему;
    3. просмотрите левую часть окошка, откройте категорию надстроек (вторая снизу в списке), выберите соответствующий пункт;
    4. в выпавшем диалоговом меню найдите пункт «Управление», кликните по нему мышью;
    5. клик вызовет на экран диалоговое окно, выберите раздел надстроек, если выставлено значение, отличное от «Надстройки Excel», поменяйте его на обозначенное;
    6. нажмите на экранную кнопку «Перейти» в разделе надстроек. В правой части выпадет список надстроек, которые устанавливает программа.

    Поиск пакета в надстройках Excel

    Рассмотрим, как активировать аналитические функции, предоставляемые надстройкой пакета:

    1. В перечне надстроек, выпавшем после последовательного выполнения предыдущих операций, пользователю надлежит поставить знак птички напротив раздела «Пакет анализа».
    2. Выбрав активацию пакета, необходимо нажать клавишу «Ок», расположенную в верхней правой части диалогового окна.
    3. После нажатия кнопки пакет появляется на ленте функций. Для получения доступа к нему в интерфейсе программы выбирается вкладка «Данные». В правой части меню «Раздел анализа». Там пользователь найдет иконку опции «Анализ данных».

    Выбор нужной надстройки

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

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

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

    Расположение функции «Анализ данных» на вкладке «Данные»

    Чтобы применить ту или иную опцию, действуют по нижеприведенному алгоритму:

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

    Как включить анализ данных в Excel 2010

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

    Для Excel 2007

    Алгоритм, как включить анализ данных в Excel 2007, отличается от остальных тем, что в самом начале (для выхода на параметры Excel) вместо кнопки «Файл» пользователь нажимает четырехцветный символ Microsoft Office. В остальном же последовательность операций идентична приведенной для других версий.

    Анализ данных в Excel 2007: как включить?

    Источник:
    http://freesoft.ru/blog/kak-vklyuchit-analiz-dannykh-v-excel-2010-2007-2013

    Обзор встроенных средств Excel для статистического анализа данных

    Основными средствами анализа статистических данных в Excel являются статистические процедуры надстройки Пакет анализа (Analysis ToolРак) и статистические функции библиотеки встроенных функций. Основные сведения обо всех этих средствах имеются в электронной справочной системе Excel.

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

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

    Статистические процедуры Пакета анализа

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

    В Пакет анализа входят следующие статистические процедуры:

    1. генерация случайных чисел (Random number generation);
    2. выборка (Sampling);
    3. гистограмма (Histogram);
    4. описательная статистика (Descriptive statistics);
    5. ранги персентиль (Rank and percentile);
    6. двухвыборочный z-тест для средних (z-Test: Two Sample for Means);
    7. двухвыборочный t-тест для средних с одинаковыми дисперсиями (t-Test: Two-Sample Assuming Equal Variances);
    8. двухвыборочный t-тест для средних с различными дисперсиями (t-Test: Two-Sample Assuming Unequal Variances);
    9. парный двухвыборочный t-тест для средних (t-Test: Paired Two Sample for Means);
    10. двухвыборочный F-тест да я дисперсий (F-Test: Two Sample for Variances);
    11. коварнация (Covariance);
    12. корреляция (Correlation);
    13. рецессия (Regression);
    14. однофакторный дисперсионный анализ (ANOVA: Single Factor);
    15. двухфакторный дисперсионный анализ без повторений (ANOVA: Two Factor Without Replication);
    16. двухфакторный дисперсионный анализ с повторениями (ANOVA: Two Factor With Replication);
    17. скользящее среднее (Moving Average);
    18. экспоненциальное сглаживание (Exponential Smoothing);
    19. анализ Фурье (Fourier Analysis).

    Для доступа к процедурам Пакета анализа необходимо в меню Сервис (Tools) щелкнуть указателем мыши на строке Анализ данных (Data Analysis). Откроется диалоговое окно с соответствующим названием, в котором перечислены процедуры статистического анализа данных (рис. 1).

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

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

    Рис.2. Диалоговое окно процедуры Описательная статистика

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

    К числу общих для большинства процедур элементов управления относятся:

    • поле ввода Входной интервал (Input Range). В это поле вводится ссылка на диапазон, содержащий статистические данные, подлежащие обработке. Входной диапазон может быть столбцом пли группой столбцов (строкой или группой строк);
    • переключатель Группирование (Grouped By). В том случае, когда входной диапазон представляет собой столбец или группу столбцов, переключатель устанавливается в положение по столбцам (Columns). Если же входной диапазон представляет собой строку или группу строк, то переключатель устанавливается в положение по строкам (Rows). Более точным названием этого переключателя было бы название Расположение;
    • флажок Метки (Labels in First Row). Флажок устанавливается в тех случаях, когда первая строка (первый столбец) входного диапазона содержит заголовки. Если такие заголовки отсутствуют, флажок Метки не устанавливают. При этом Excel автоматически создает и выводит на экран стандартные названия для данных выходного диапазона (Столбец1, Столбец2,… или Строка 1. Строка2,…);
    • переключатели Выходной интервал/Новый рабочий лист/Новая книга (Output Range/New Worksheet/New Workbook). Эти переключатели определяют место вывода таблицы, содержащей результаты реализации статистической процедуры. В группе может быть выбран только одни переключатель.

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

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

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

    Эффективным и очень удобным в использовании средством парного регрессионного анализа и анализа временных рядов является процедура Добавить линию тренда (Add Trendline), входящая в комплекс графических средств Excel.

    Статистические функции библиотеки встроенных функций Excel

    Табличный процессор Excel имеет библиотеку встроенных функции рабочего листа (Worksheet function). Одним из разделов этой библиотеки является раздел Статистические функции. В этот раздел входят 83 функции, предназначенные для решения некоторых наиболее востребованных задач теории вероятностей и математической статистики.

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

    Когда в качестве какого-либо аргумента встроенной статистической функции введен текст, функция выдает сообщение об ошибке #ЗНАЧ! (#VALUE!). Если в качестве аргумента, который по определению должен быть целым числом, введено число не целое, Excel использует в качестве аргумента целую часть этот числа. Никакие сообщения об этом «несанкционированном округлении» на экран не выводятся.

    Источник:
    http://excel2010.ru/obzor-vstroennyx-sredstv-excel-dlya-statisticheskogo-analiza-dannyx.html

    Вычисление описательных статистик в Excel

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

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

    После ввода исходных данных (столбцы A и B на рис. 1.4), вычисляем в столбце C выборочную среднюю : =СУММ(B2:B11)/10. Далее в столбцах D и E находим соответственно квадрат и куб отклонений вариант от выборочного среднего . В связи с тем, что количество данных мало, будем находить выборочное СКО через исправленную выборочную дисперсию , которая является несмещенной оценкой генеральной дисперсии. Таким образом, в столбце F имеем: =10*СУММ(D2:D11)/9, после чего в столбце G находим интересующий нас показатель исправленного выборочного СКО : =КОРЕНЬ(F2). В столбцах H и I вычисляем центральный момент 3-го порядка : =СУММ(E2:E11)/10и показатель асимметрии, который с учётом поправки на несмещенность вычисляется как : =100*H2/(72*G2^3).

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

    Б) Вычисление описательных статистик с помощью статистических функций.MS Excel предоставляет широкие возможности для анализа статистических данных. Для решения простых задач можно использовать встроенные статистические функции СЧЕТ, МОДА, МЕДИАНА, СРЗНАЧ, ДИСПР, ДИСП, СТАНДОТКЛОНП, СКОС и ЭКСЦЕСС, которые позволяют определить для выборки объём, моду, медиану, выборочное среднее, выборочную дисперсию, исправленную выборочную дисперсию, выборочное стандартное отклонение, асимметрию и эксцесс соответственно. Для ознакомления со всеми статистическими функциями в Excel воспользуйтесь переходом: ФормулыБиблиотека функцийДругие функции(рис. 1.5),

    далее из выпадающего списка выберите Статистические(рис. 1.6).

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

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

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

    Если данная надстройка ещё не подключена, последовательно переходим: Параметры ExcelНадстройкиПерейти Далее среди доступных надстроек выбираем Пакет анализа и нажимаем OK. Работа с установленной надстройкой происходит следующим образом: Данные → Анализ данных → Описательная статистика → ОК (рис. 1.7).

    Появляется диалоговое окно, которое требуется заполнить (рис. 1.8).

    В этом окне указываем, как сгруппированы данные, ставим «галочку» рядом со словами Итоговая статистика, нажимаем ОК. Появляется итоговое окно. Для рассмотренной выше выборки итоговый результат представлен на рис. 1.9.

    Источник:
    http://zdamsam.ru/a50989.html