Статистические функции в Excel

Статистические функции

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

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

Запустить мастер можно тремя способами:

1. Щелкните значок «Вставить функцию» слева от строки формул.



2. На вкладке «Формулы» нажмите кнопку «Вставить функцию» на ленте панели инструментов «Библиотека функций».



3. Введите сочетание клавиш Shift + F3.

Запуск одного из вышеперечисленных вариантов откроет окно «Мастера».

Затем вам нужно нажать на поле «Категория» и выбрать значение «Статистика».

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

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

МАКС

Оператор MAX предназначен для определения максимального числа из выборки. Он имеет следующий синтаксис:

= МАКС (число1; число2;…)

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

МИН

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

= МИН (число1; число2;…)

СРЗНАЧ

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

= СРЕДНИЙ (число1; число2;…)

СРЗНАЧЕСЛИ

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

= СРЗНАЧЕСЛИ (число1; число2;…; условие; средний_диапазон)

МОДА.ОДН

Формула FASHION.One отображает номер коллекции, которая чаще всего встречается в ячейке. В более ранних версиях Excel была функция MODA, но в более поздних версиях она была разделена на две: MODA.ON (для отдельных чисел) и MODA.NSC (для массивов). Однако старая версия также осталась в отдельной группе, которая содержит элементы из предыдущих версий программы для обеспечения совместимости документов.

= FASHION.ONE (число1; число2;…)

= MODA.NSK (число1; число2;…)

МЕДИАНА

Оператор MEDIAN определяет среднее значение по диапазону чисел. То есть он не устанавливает среднее арифметическое, а просто среднее значение между наибольшим и наименьшим числом в диапазоне. Синтаксис выглядит так:

= МЕДИАНА (число1; число2;…)

СТАНДОТКЛОН

Формула STDEV, как и MODA, является пережитком старых версий программы. Сейчас используются его современные подвиды: СТАНДОТКЛОН и СТАНДОТКЛОН. Первый предназначен для расчета стандартного отклонения выборки, а второй — для генеральной совокупности. Эти функции также используются для расчета стандартного отклонения. Их синтаксис следующий:

= СТАНДОТКЛОН.V (число1; число2;…)

= СТАНДОТКЛОН (число1; число2;…)

НАИБОЛЬШИЙ

Этот оператор отображает указанное число в порядке убывания от совокупности в выбранной ячейке. То есть, если у нас есть коллекция 12,97,89,65 и указано 3 в качестве аргумента позиции, функция вернет третье по величине число в ячейке. В данном случае это 65. Синтаксис оператора следующий:

= НАИБОЛЬШИЙ (матрица; k)

В этом случае k — порядковый номер значения.

НАИМЕНЬШИЙ

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

= МАЛЕНЬКИЙ (матрица; k)

Пример работы с функцией МОДА.ОДН в Excel

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

Примечание: в отличие от среднего арифметического (в данном примере около 41), мод определяет наиболее частое событие в диапазоне событий. Его рационально использовать для решения статистических задач, связанных с анализом нормально распределенных данных.

Замечания

• Аргументы могут быть числами или числами, содержащими имена, массивы или ссылки.

• Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, эти значения игнорируются; подсчитываются ячейки, содержащие нулевые значения.

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

• Если набор данных не содержит тех же данных, РЕЖИМ возвращает значение ошибки # Н / Д.

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

• Среднее арифметическое — это среднее арифметическое, которое вычисляется путем сложения набора чисел и деления полученной суммы на их количество. Например, среднее значение чисел 2, 3, 3, 5, 7 и 10 равно 5, что является результатом деления их суммы 30 на их количество 6.

• Медиана — это число, которое находится в центре набора чисел, т.е половина чисел больше медианы, а половина чисел меньше медианы. Например, медиана для чисел 2, 3, 3, 5, 7 и 10 будет равна 4.

• Мода — это число, которое чаще всего встречается в данном наборе чисел. Например, мода на числа 2, 3, 3, 5, 7 и 10 будет 3.

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

Функция МИН

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

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

Есть много других ситуаций, в которых можно использовать функцию MIN. В самом общем виде это выглядит так: = MIN (число1; число2;…). Принцип заполнения аргументов этой функции аналогичен функции MAX.

Функция МАКС

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

Функции СРЗНАЧ и СРЗНАЧА

Стандартная функция AVERAGE определяет среднее арифметическое числовой выборки. Общий вид формулы такой же, как и для любой другой выборки значений. Сначала пишется название функции, после чего в скобках указываются числа и диапазоны, которые необходимо обработать с помощью этой функции. То есть общая форма формулы следующая: = СРЕДНЕЕ (число1; число2;…).

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

Максимальное количество аргументов, которые можно использовать в этой функции, равно 255. Однако требуется только первое число. Текстовые и логические значения нельзя использовать в качестве аргументов. Они просто не учитываются по формуле, в которой используется указанный оператор. Основное различие между функцией AVERAGE и функцией AVERAGE заключается в том, что значения TEXT и FALSE считаются нулевыми, а значение True равно единице.

Синтаксис

= МОДА ((число1; число2;…)

Аргументы

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

Замечания

• Аргументы могут быть числами или числами, содержащими имена, массивы или ссылки.
• Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, эти значения игнорируются; однако подсчитываются ячейки, содержащие нулевые значения.
• Аргументы, которые являются значениями ошибок или текстом, который не может быть преобразован в числа, вызывают ошибку.
• Если набор данных не содержит тех же данных, РЕЖИМ возвращает значение ошибки # Н / Д.
• Среднее арифметическое — это среднее арифметическое, которое вычисляется путем сложения набора чисел и деления полученной суммы на их количество. Например, среднее значение чисел 2, 3, 3, 5, 7 и 10 равно 5, что является результатом деления их суммы 30 на их количество 6.
• Медиана — это число, которое находится в центре набора чисел, т.е половина чисел больше медианы, а половина чисел меньше медианы. Например, медиана для чисел 2, 3, 3, 5, 7 и 10 будет равна 4.
• Мода — это число, которое чаще всего встречается в данном наборе чисел. Например, мода на числа 2, 3, 3, 5, 7 и 10 будет 3.

Расчет моды в Excel

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

МОДА — это наследие старых редакций Excel. Функция оставлена ​​для обратной совместимости.

FASHION.One — рассчитывает режим по заданным значениям. Здесь все просто. Вставили функцию, указали диапазон данных и «Ок».

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

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

Пример использования функции МОДА.НСК в Excel

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

Заполните столбец «Случайные числа» с помощью функции RANDBETWEEN (1; 100), то есть случайными числами в диапазоне от 1 до 100:

Примечание. Функция СЛУЧМЕЖДУ пересчитывает полученные случайные значения каждый раз, когда новое значение вводится в любую ячейку, поэтому значения в столбце A2 могут отличаться от одного изображения к другому.

Выберите диапазон ячеек B2: B23 и введите формулу:

Чтобы ввести формулу, используйте комбинацию клавиш Ctrl + Shift + Enter, чтобы функция запускалась в массиве. В результате получаем:

Указанный диапазон случайных чисел повторяет значения 48, 47 и 53. Поскольку остальные числа уникальны, для ячеек B5: B23 был сгенерирован код ошибки # N / A (то есть формула не найдена требуемое значение).

Перечень статистических функций

Перейдем непосредственно к рассмотрению статистических функций.

Функция СРГЕОМ

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

Формула очень проста: = СРГЕОМ (число1; число2;…). Помимо чисел, вы также можете указать диапазон значений, которые учитываются этой функцией. Что такое среднее геометрическое? Это число, которое может заменить любое из чисел в последовательности, так что произведение этих значений не изменится. Другой часто используемый термин — пропорциональное среднее. Это синоним среднего геометрического. Этот второй термин используется потому, что среднее геометрическое пропорционально первому и второму числам.

Функция СТАНДОТКЛОН

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

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

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

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

Функция МОДА.ОДН

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

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

Эта функция подходит для горизонтальных массивов. Если вам нужно определить режим выборки для вертикального массива, вы можете использовать аналогичную функцию MODA.NSK. Общий вид функции следующий: = MODE.ONE (аргумент 1, аргумент 2; аргумент …).

Функция НАИМЕНЬШИЙ

Назначение этой функции — поиск из набора значений, заданных пользователем. Принцип его работы такой же, как и у следующего, только поиск ведется по направлению снизу вверх, от наименьшего числа к наибольшему. Синтаксис этой функции предельно прост: = МАЛЕНЬКИЙ (массив; k).

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

Функция НАИБОЛЬШИЙ

Функция НАИБОЛЬШИЙ аналогична, за исключением того, что она ведет обратный отсчет от наибольшего значения. После присвоения ему коэффициента он ищет в порядковой строке от наибольшего до наименьшего числа, занимающего соответствующее место, и возвращает его. Обе функции работают одинаково. Допустим, у нас есть ряд чисел. Если вы укажете 2 для числа k в нем, результатом будет число 15, поскольку оно является вторым по величине в диапазоне, указанном в первом аргументе.

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

Функция МЕДИАНА

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

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

Функция СРЗНАЧЕСЛИ

Это улучшенная версия функции AVERAGE, задача которой — найти среднее арифметическое, но только при соблюдении определенного условия. Эта функция уже немного сложнее, чем приведенные выше: = СРЗНАЧЕСЛИ (диапазон; условие; средний_ диапазон). Давайте подробнее рассмотрим каждую тему:

1. Диапазон. Это ячейки, которые проверяются на соответствие определенному условию.
2. Состояние. Это критерий, по которому проверяется диапазон.
3. Средний диапазон. Это диапазон, из которого будет взято среднее арифметическое. Этот аргумент является необязательным, поскольку диапазон ячеек и средний диапазон могут совпадать.