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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      Отблагодарите автора, поделитесь статьей в социальных сетях.

      Функции категории Статистические предназначены в первую очередь для анализа диапазонов ячеек в 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 Excel справиться со всем.

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

      Эта функция можем высчитывать большинство критериев, среди которых:

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

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

      Для этого нажмем на вкладку «Файл» — «Параметры».

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

      В новом окне ставим галочку напротив «Пакет анализа» и применяем операцию.

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

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

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

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

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

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