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

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

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

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

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

  1. Находясь в любой вкладке программы щелкаем по значку “Вставить функцию” (fx), которая находится с левой стороны от строки формул.Вставка функции в ячейку таблицы Эксель
  2. Переходим во вкладку “Формулы”, где видим в левом углу ленты инструментов кнопку “Вставить функцию”.Вставка функции в ячейку таблицы Excel
  3. Используем сочетание клавиш Shift+F3.

Независимо от выбранного способа выше перед нами появится окно вставки функций. Щелкаем по текущей категории и из раскрывшегося списка выбираем пункт “Статистические”.

Выбор категории функций в Эксель

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

Выбор статистической функции для вставки в ячейку таблицы Excel

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

Аргументы функции СРЗНАЧ в Excel

Примечание: существует еще один способ выбора требуемой функции. Находясь во вкладке “Формулы” в блоке инструментов “Библиотека функций” щелкаем по значку “Другие функции”, затем выбираем пункт “Статистические” и, наконец, в открывшемся перечне (который можно листать вниз) – нужный оператор.

Выбор функции для вставки в ячейку таблицы Эксель во вкладке Формулы

Давайте теперь рассмотрим наиболее популярные функции.

СРЗНАЧ

Оператор вычисляет среднее арифметическое значение из указанных значений (диапазона). Формула функции выглядит таким образом:

=СРЗНАЧ(число1;число2;…)

В качестве аргументов функции можно указать:

  1. конкретные числа;
  2. ссылки на ячейки, которые можно указать как вручную (напечатать с помощью клавиатуры), так и находясь в соответствующем поле щелкнуть по нужному элементу в самой таблице;
  3. диапазон ячеек – указывается вручную или путем выделения в таблице.
  4. переход к следующему аргументу происходит путем щелчка по соответствующему полю напротив него или просто нажатием клавиши Tab.
    Заполнение аргументов функции СРЗНАЧ в Excel

МАКС

Функция помогает определить максимальное значение из заданных чисел (диапазона). Формула оператора следующая:

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

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

Заполнение аргументов функции МАКС в Excel

МИН

Функция находит минимальное число из указанных значений (диапазона ячеек). В общем виде синтаксис выглядит так:

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

Аргументы функции заполняются так же, как и для оператора МАКС.

Заполнение аргументов функции МИН в Excel

СРЗНАЧЕСЛИ

Функция позволяет найти среднее арифметическое значение, но при выполнении заданного условия. Формула оператора:

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

В аргументах указываются:

  1. Диапазон ячеек – вручную или с помощью выделения в таблице;
  2. Условие отбора значений из заданного диапазона (больше, меньше, не равно) – в кавычках;
  3. Диапазон_усреднения – не является обязательным аргументом для заполнения.Заполнение аргументов функции СРЗНАЧЕСЛИ в Excel

МЕДИАНА

Оператор находит медиану заданного диапазона значений. Синтаксис функции:

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

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

Заполнение аргументов функции МЕДИАНА в Excel

НАИБОЛЬШИЙ

Функция позволяет найти из указанного диапазона значений с заданной позицией (по убыванию). Формула оператора:

=НАИБОЛЬШИЙ(массив;k)

Аргумента функции два: массив и номер позиции – K.

Заполнение аргументов функции НАИБОЛЬШИЙ в Excel

Допустим, имеется ряд чисел 4, 6, 12, 24, 15, 9. Если мы укажем в качестве аргумента “K” число 2, результатом будет значение, равное 15, т.к. оно второе по величине в выбранном диапазоне.

НАИМЕНЬШИЙ

Функция также, как и оператор НАИБОЛЬШИЙ, выполняет поиск из указанного диапазона значений. Правда, в данном случае счет идет по возрастанию. Синтаксис оператора следующий:

=НАИМЕНЬШИЙ(массив;k)

Заполнение аргументов функции НАИМЕНЬШИЙ в Excel

МОДА.ОДН

Функция пришла на замену более старому оператору “МОДА” (теперь находится в категории “Полный алфавитный перечень”). Позволяет определять число, которое повторяется чаще остальных в выбранном диапазоне. Работает функция по формуле:

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

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

Заполнение аргументов функции МОДА в Excel

Для вертикальных массивов, также, используется функция МОДА.НСК.

СТАНДОТКЛОН

Функция СТАНДОТКЛОН также устарела (но ее все еще можно найти, выбрав алфавитный перечень) и теперь представлена двумя новыми:

  • СТАДНОТКЛОН.В – находит стандартное отклонение выборки
  • СТАДНОТКЛОН.Г – определяет стандартное отклонение по генеральной совопкупности

Формулы функций выглядят следующим образом:

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

Заполнение аргументов функции СТАНДОТКЛОН в Excel

СРГЕОМ

Оператор находит среднее геометрическое значение для заданного массива или диапазона. Формула функции:

=СРГЕОМ(число1;число2;…)

Заполнение аргументов функции СРГЕОМ в Excel 

Спасибо за сообщение

Ваше сообщение было получено и отправлено администратору.

Close

FРАСП

Синтаксис:

FРАСП(х,степени_свободы1,степени_свободы2)

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

Аргументы:

  • х – значение, для которого вычисляется функция;
  • степени_свободы1 – числитель степеней свободы;
  • степени_свободы2 – знаменатель степеней свободы.

ДИСП

Синтаксис:

ДИСП(число1, число2, …)

Результат: Дисперсия выборки. Аргументы рассматриваются как выборка из генеральной совокупности.

Аргументы:

  • число1,число2,… – не более 30 аргументов; текстовые, логические и пустые поля приводят к ошибке.

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

Очевидно, что функция СРЗНАЧЕСЛИ() возвращает среднее тех значений, который удовлетворяют каким-то условиям. Помимо этого, условия можно накладывать не только на сами значения, но и на другие ячейки. Проиллюстрируем.

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

Применение функции СРЗНАЧЕСЛИ() с условием на аргумент

Мы выделили диапазон А1:С3 и наложили на него условие – «>0». А можно сделать по-другому.

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

Формула записывается так:

=СРЗНАЧЕСЛИ(Диапазон_на_который_накладываем_условия; “Условие”; Диапазон_по_которому_считаем_среднее_значение)

В нашем случае это примет вид:

Применение функции СРЗНАЧЕСЛИ() с условием на другой диапазон

Кстати говоря, условия можно комбинировать с помощью функции СРЗНАЧЕСЛИМН().

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

Тогда, чтобы усреднить цену всех Анальгинов в аптеке Зеленый Крест, нужно просто использовать формулу:

=СРЗНАЧЕСЛИМН(С2:С13; A2:A13; “зеленый крест”;B2:B13; “анальгин”)

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

ДОВЕРИТ

Синтаксис:

ДОВЕРИТ(альфа, станд_откл, размер)

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

Аргументы:

  • альфа – уровень значимости, используемый для вычисления уровня надежности (уровень надежности равен 100*(1 – альфа)% другими словами, значение альфа, равное 0,05, означает уровень надежности, равный 95%);
  • станд_откл – стандартное отклонение генеральной совокупности для интервала данных (предполагается известным);
  • размер – размер выборки.

МИН()/МАКС() и НАИБОЛЬШИЙ()/НАИМЕНЬШИЙ()

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

Функция МИН() просто принимает массив аргументов и находит самое маленькое число. МАКС() – самое большое. Все просто.

Функция НАИМЕНЬШИЙ() же находит n-ое наименьшее число в массиве. НАИБОЛЬШИЙ(), наоборот, находит n-ое наибольшее число.

Например, нужно найти пятое по величине число. Вводим:

=НАИБОЛЬШИЙ(диапазон; 5).

Фактически, получается, что результат работы НАИБОЛЬШИЙ(массив;1) и МАКС(массив) – одно и то же. Аналогичная ситуация с НАИМЕНЬШИЙ(массив;1) и МИН(массив).

Рекомендуем записаться на наш открытый онлайн-курс «Аналитика в Excel», если вы хотите научиться выполнять рутинную работу быстрее.

ЛГРФПРИБЛ

Синтаксис:

ЛГРФПРИБЛ(известные_значения_у, известные_значения_х, конст, статистика)

Результат: Возвращает матрицу, описывающую экспоненциальную кривую (у = bm/х), которая была рассчитана из заданных значений: первое значение результирующей матрицы есть основание экспоненты (т), второе значение – коэффициент (Ь).

Аргументы:

  • известные_значения_у – множество значений у (если массив известные_значения_у имеет один столбец, то каждый столбец массива известные_значения_х интерпретируется как отдельная переменная; если массив извест-ные_значения_у имеет одну строку, то каждая строка массива известные_значения_х интерпретируется как отдельная переменная);
  • известные_значения_х – необязательное множество значений х, которые уже известны для соотношения у = mх + b (массив известиые_знанения_х может содержать одно или несколько множеств переменных; если используется только одна переменная, то аргументы известные_значения_у известные_значения_х могут быть массивами любой формы при условии, что они имеют одинаковую размерность; если используется более одной переменной, то аргумент извест-ные_значения_у должен быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец); если аргумент известные_значения_х опущен, то предполагается, что это массив {1;2;3;…} такого же размера, как и массив известные_значе-ния_у);
  • конст – логическое значение; если аргумент отсутствует или имеет значение ИСТИНА, то b вычисляется обычным способом; если аргумент имеет значение ЛОЖЬ, то Ь полагается равным 1 и знамения т подбираются так, чтобы выполнялось соотношение у = m/х;
  • статистика – логическое значение, которое указывает, требуется ли возвращать дополнительную статистику по регрессии (если аргумент имеет значение ИСТИНА, то функция ЛГРФПРИБЛ возвращает дополнительную регрессионную статистику, так что возвращаемый массив будет иметь вид: {mn;mn-1;…;m1;b:sen;sen-1;…;se1; seb:r2;sey:F;df:ssreg;ssresid}; если аргумент имеет значение ЛОЖЬ или опущен, то функция ЛГРФПРИБЛ возвращает только коэффициенты т и постоянную Ь).

МЕДИАНА() и МОДА()

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

Напомним, что медианой называется «середина» числового множества.

Например, если есть массив чисел от одного до десяти, то медианой будет число 5,5 (хотя оно само в массив не входит). Это из-за того, что количество элементов в массиве – четно и выбрать «центральное» просто невозможно.

Поиск медианы для «четного» массива

Вот если бы выборка начиналась не с единицы, а с двойки, то ответ был бы ровно 6.

Поиск медианы для «нечетного» массива

Теперь перейдем к моде. Мода – самое часто встречающееся число в выборке.

У функции нахождения моды есть целых три модификации в Excel старшее версии 2010 года: МОДА(), МОДА.ОДН() и МОДА.НСК().

Функция МОДА() оставлена для совместимости – ей, в целом, можно пользоваться: она работает совершенно аналогично функции МОДА.ОДН().

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

Применение функций МОДА() и МОДА.ОДН()

Для подсчета всех мод в выборке нужно использовать функцию МОДА.НСК().

Работает МОДА.НСК() следующим образом: выделяем побольше ячеек (если заранее не знаем, сколько мод у нас получится), в строке формул прописываем =МОДА.НСК(диапазон) и нажимаем Ctrl+Shift+Enter. Получили все моды в столбик.

«Слепой» метод применения функции МОДА.НСК()

Значения #Н/Д появляются, просто потому что мод у нас всего 2. Такой метод поиска мод называется «слепым» — мы просто берем побольше ячеек, чтобы наверняка хватило.

Если Вы не любите подобный «мусор» и Вам нравится, когда все красиво, можно сначала оценить: а сколько же у нас вообще будет мод? А потом просто выделить нужное количество ячеек.

Делается это так: сначала применяем функцию СЧЁТ() к нашей МОДА.НСК() – получили количество мод. А теперь выделяем только две ячейки и делаем все также, как написано выше.

Модификация применения МОДА.НСК()

Функция СЧЁТ

Подсчитывает количество числовых значений в диапазоне.

Синтаксис: =СЧЁТ(значение1; [значение2]; …), где значение1 – обязательный аргумент, принимающий значение, ссылку на ячейку, диапазон ячеек или массив. Аргументы от значение2 до значение255 являются необязательными и аналогичными значение1.

Логические значения в диапазонах и массивах игнорируются. Если такое значение задано явно в аргументе, то оно учитывается как число.

Пример использования:

=СЧЁТ(1; 2; “5”) – результат функции 3, т.к. строка «5» конвертируется в число.
=СЧЁТ({1; 2; “5”}) – результатом выполнения функции будет значение 2, так как, в отличие от первого примера, число в виде строки записано в массиве, поэтому не будет преобразовано.
=СЧЁТ(1; 2; ИСТИНА) – результат функции 3. Если бы логическое значение находилось бы в массиве, то оно не засчиталось как число.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...