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

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

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

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

Функции для расчета средних значений

AVERAGE

функция AVERAGE — одна из статистических функций Excel. Функция возвращает среднее числовых значений, введенных в функцию. Говоря простыми словами, она складывает все значения, указанные в функции, затем делит их на количество и возвращает результат.

синтаксис

= AVERAGE(number1,number2,…)

темы

• numero1 : первое число, которое вы хотите использовать для расчета среднего значения.
• [numero2] : второе число, которое вы хотите использовать для усреднения.

пример

Чтобы увидеть, как работает функция AVERAGE давайте посмотрим пример:

В первом примере мы вставили аргументы непосредственно в функцию.

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

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

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

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

AVERAGEA

функция AVERAGEA Excel указан в категории «Статистические функции Microsoft Excel». Возвращает среднее значение указанных чисел по функции, но в отличие от AVERAGE, обрабатывает логические значения и числа, отформатированные как текст.

синтаксис

=AVERAGEA(valore1,valore2,…)

темы

• value1 : значение, представляющее собой число, логическое значение или число, хранящееся в виде текста.
• [valore2] : значение, представляющее собой число, логическое значение или число, хранящееся в виде текста.

пример

Чтобы понять функцию AVERAGEA нам нужно увидеть пример:

Значение, возвращаемое функцией, равно 10,17, что равно «(0+0+1+10+20+30)/6».

AVERAGEIF

функция AVERAGEIF Excel указан в категории «Статистические функции Microsoft Excel». Возвращает среднее число чисел, удовлетворяющих нескольким заданным условиям. . 

синтаксис

= AVERAGEIF( range, criteria, [average_range] )

темы

• range:  массив значений (или диапазон ячеек, содержащих значения) для проверки на соответствие предоставленным критериям.
• criteria:  Условие, которое необходимо проверить на соответствие каждому из значений в указанном диапазоне.
• [average_range]:  Необязательный массив числовых значений (или ячеек, содержащих числа), который следует усреднить, если соответствующее значение в диапазоне соответствует предоставленным критериям.

Если тема [average_range] опущено, среднее значение рассчитывается для значений в исходном предоставленном диапазоне.

Предоставленными критериями могут быть:

числовое значение (включая целые, десятичные числа, даты, время и логические значения) (например, 10, 01, TRUE)
O
текстовая строка (например, «Текст», «Четверг») — ДОЛЖНА быть указана в кавычках.
O
выражение (например, «>12», «<>0») — ДОЛЖНО быть заключено в кавычки.
Также обратите внимание, что функция AVERAGEIF Excel не чувствителен к регистру. Так, например, текстовые строки «TEXT"А"text» будут оцениваться как равные.

пример

Чтобы понять функцию AVERAGEIF нам нужно попробовать это на примере.

Клетки A16-A20 в следующей таблице показаны пять примеров функции AVERAGEIF Excel.

Для каждого вызова функции AVERAGEIF Excel, тема range (будет проверено на criteria) — диапазон ячеек A1-A14 и тема [average_range] (содержащий значения, подлежащие усреднению) — диапазон ячеек B1-B14.

Обратите внимание, что в ячейках A16, A18 и A20 приведенной выше таблицы текстовое значение «Четверг» и выражения «>2» и «<>TRUE» заключены в кавычки. Это важно для всех текстов или выражений.

AVERAGEIFS

функция AVERAGEIFS Excel указан в категории «Статистические функции Microsoft Excel». Возвращает среднее число чисел, удовлетворяющих нескольким заданным условиям. . В отличие от AVERAGEIF, вы можете задать несколько условий и рассчитать среднее значение только для чисел, соответствующих всем условиям.

синтаксис

= AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

темы

• average_range:  Массив числовых значений (или ячеек, содержащих числа), которые подлежат усреднению.
• criteria_range1, [criteria_range2], …: массивы значений (или диапазоны ячеек, содержащих значения) для проверки друг друга criteria1, criteria2, … (Массивы criteria_range все поставляемые должны иметь одинаковую длину).
• criteria1, [criteria2], …: Условия, подлежащие проверке относительно значений в criteria_range1, [criteria_range2], …

пример

Теперь рассмотрим пример функции AVERAGEIFS:

В следующем примере мы использовали функцию AVERAGEIFS для расчета среднего количества, проданного продавцом «Пьетро» и для товара «Б». Мы ввели критерии непосредственно в функцию и имеем две записи о продаже Питером продукта B.

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

В приведенных ниже данных мы видим два фрукта, отвечающих этим критериям.

MEDIAN

функция MEDIAN Excel возвращает статистическую медиану (среднее значение) списка предоставленных чисел.

синтаксис

= MEDIAN( number1, [number2], ... )

темы

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

Обратите внимание, что:

• Если в данном наборе данных четное количество значений, возвращается среднее из двух средних значений;
• Если предоставленный массив содержит пустые ячейки, текст или логические значения, эти значения игнорируются при вычислении медианы.
• В текущих версиях Excel (Excel 2007 и более поздних версиях) вы можете передать до 255 числовых аргументов функции Медиана, но в Excel 2003 функция может принимать только до 30 числовых аргументов. Однако каждый из числовых аргументов может представлять собой массив из множества значений.

пример

В следующей таблице показаны три примера функции Median:

Учтите, что в предыдущих примерах:

• Пример в ячейке B2 получает четное количество значений и поэтому медиана рассчитывается как среднее двух средних значений, 8 и 9;
• Пример в ячейке B3 включает пустую ячейку A8. Эта ячейка игнорируется при вычислении медианы.

Для получения более подробной информации о функции MEDIAN Excel, см. Веб-сайт Microsoft Office .

MODE

функция MODE Excel возвращает MODE статистика (наиболее частое значение) списка предоставленных чисел. Если в предоставленных данных есть 2 или более повторяющихся значений, функция возвращает наименьшее значение среди них.

синтаксис

= MODE( number1, [number2], ... )

темы

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

Примечание:

• В текущих версиях Excel (Excel 2007 и более поздних версиях) вы можете передать функции до 255 числовых аргументов. MODE, но в Excel 2003 функция может принимать только до 30 числовых аргументов.
• Текстовые и логические значения внутри предоставленного массива чисел игнорируются функцией. Mode.

Примеры функций MODE

Пример 1

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

Пример 2

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

Обратите внимание, что в этом случае есть два mode в данных.

В приведенном выше случае, когда данные в столбце A предыдущей таблицы имеют два MODE статистика (3 и 4), функция MODE возвращает меньшее из этих двух значений.

Для получения более подробной информации и примеров функции MODE Excel, см. Веб-сайт Microsoft Office .

MODE.SNGL

функция MODE.SNGL Excel возвращает MODE статистика (наиболее частое значение) списка предоставленных чисел. Если в предоставленных данных есть 2 или более повторяющихся значений, функция возвращает наименьшее значение среди них.

функция Mode.Sngl является новым в Excel 2010 и поэтому недоступен в более ранних версиях Excel. Однако функция представляет собой просто переименованную версию функции. MODE доступно в более ранних версиях Excel.

синтаксис

= MODE.SNGL( number1, [number2], ... )

темы

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

Примеры функций MODE.SNGL

Пример 1

В следующей таблице показана функция MODE.SNGL Excel, используемый для расчета статистического РЕЖИМА набора значений в ячейках. A1-A6.

Пример 2

В следующей таблице показана функция MODE.SNGL, используется для расчета статистического режима набора значений в ячейках A1-A10.

Обратите внимание, что в этом случае есть два mode в данных.

В приведенном выше случае, когда данные в столбце A предыдущей таблицы имеют два MODE статистика (3 и 4), функция MODE.SNGL возвращает меньшее из этих двух значений.

Для получения более подробной информации и примеров функции MODE.SNGL Excel, см. Веб-сайт Microsoft Office .

GEOMEAN

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

Среднее геометрическое набора значений, y ₁ , y  ₂ ,…, Y _n он рассчитывается по формуле:

Обратите внимание, что среднее геометрическое всегда меньше или равно среднему арифметическому.

функция Geomean Excel вычисляет среднее геометрическое заданного набора значений.

синтаксис

= GEOMEAN( number1, [number2], ... )

темы

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

В текущих версиях Excel (Excel 2007 и более поздних версиях) функция может принимать до 255 числовых аргументов, но в Excel 2003 функция может принимать только до 30 числовых аргументов. Однако каждый аргумент может представлять собой массив значений или диапазон ячеек, каждая из которых может содержать множество значений.

пример

Клетка B1 таблицы показывает простой пример функции geomean в Excel используется для вычисления среднего геометрического значений ячеек А1-А5.

В этом примере функция Geomean возвращает значение 1.622671112 .

HARMEAN

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

Таким образом, среднее гармоническое набора значений y1, y2, ..., yn определяется по формуле:

среднее гармоническое всегда меньше или равно среднему геометрическому, а среднее геометрическое всегда меньше или равно среднему арифметическому.

функция Harmean Excel вычисляет среднее гармоническое значение заданного набора значений.

синтаксис

= HARMEAN( number1, [number2], ... )

темы

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

В текущих версиях Excel (Excel 2007 и более поздних версиях) функция может принимать до 255 числовых аргументов, но в Excel 2003 функция может принимать только до 30 числовых аргументов. Однако каждый аргумент может представлять собой массив значений или диапазон ячеек, каждая из которых может содержать множество значений.

пример

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

В этом примере функция Harmean возвращает значение 1.229508197.

TRIMMEAN

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

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

синтаксис

= TRIMMEAN( array, percent )

темы

• массив — Массив числовых значений, для которых требуется вычислить усеченное среднее значение.
• процент – Процент значений, из которых вы хотите удалитьarray при условии.

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

Следует также отметить, что когда Excel вычисляет, сколько значений удалено изarray из предоставленных значений рассчитанный процент округляется до ближайшего числа, кратного 2. Например, если вы хотите вычислить усеченное среднее array из 10 значений, следовательно:

• Процент 15% соответствует значению 1,5, которое будет округлено до 0 (т.е. никакие значения не будут отброшены изarray перед вычислением среднего);
• Процент 20 % соответствует 2 значениям, поэтому по одному значению с каждого конца диапазона будет отброшено перед усреднением остальных значений;
• Процент 25 % соответствует 2,5 значениям, которые будут округлены до 2 (то есть по 1 значению с каждого конца диапазона будет отброшено перед усреднением остальных значений).

пример

Клетки B1-B3 в таблице ниже показаны 3 примера функции trimmean в Excel все используется для расчета усеченного среднего значений в ячейках A1-A10, для разных процентных значений.

Имейте в виду, что в клетке B1 В приведенной выше таблице процентный аргумент равен 15%. Поскольку вarray при наличии 10 значений количество игнорируемых значений равно 1,5, округленному до ближайшего числа, кратного 2, которое равно нулю.

Функции для расчета перестановок

PERMUT

Количество перестановок для данного количества объектов — это количество комбинаций в любом возможном порядке.

Перестановки отличаются от комбинаций тем, что для перестановки имеет значение порядок объектов, а для комбинации порядок не имеет значения.

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

голубь k — количество выбранных объектов e n количество возможных объектов.

Функция Excel Permut вычисляет количество перестановок указанного количества объектов из набора объектов.

синтаксис

= PERMUT( number, number_chosen )

темы

• number: общее количество доступных предметов.
• number_chosen: количество объектов в каждой перестановке (т.е. количество объектов, выбранных из набора).

Обратите внимание: если какие-либо аргументы заданы в виде десятичных значений, они будут усечены до целых чисел функцией Permut.

пример

В следующей таблице Excel Permut используется для расчета количества перестановок шести объектов, выбранных из наборов разного размера:

PERMUTATIONA

Функции Excel обмен и Permutationa вычисляют количество перестановок выбранных объектов из набора.

Однако эти две функции отличаются тем, что Перестановочная функция не считает повторения, в то время как функция Permutationa считает повторения.

Например, в наборе из 3 предметов, a , b , c , сколько существует перестановок двух объектов?

• La Перестановочная функция возвращает результат 6 (перестановки: ab , ac , ba , bc , ca , cb );
• Функция Permutationa возвращает результат 9 (перестановки: aa , ab , ac , ba , bb , bc , ca , cb , cc ).

Функция Excel Permutationa вычисляет количество перестановок указанного количества объектов из набора объектов.

синтаксис

= PERMUTATIONA( number, number_chosen )

темы

• number: общее количество объектов в наборе (должно быть ≥ 0).
• number_chosen: количество объектов, выбранных из набора (должно быть ≥ 0).

Обратите внимание: если какие-либо аргументы заданы в виде десятичных значений, они будут усечены до целых чисел функцией PERMUTATIONA.

пример

В следующей таблице Excel PERMUTATIONA используется для расчета количества перестановок шести объектов, выбранных из наборов разного размера:

Функции для расчета доверительных интервалов

CONFIDENCE

В Excel 2010 функция CONFIDENCE была заменена функцией Confidence.Norm.

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

Однако функция Confidence может быть недоступно в будущих версиях Excel, поэтому мы рекомендуем использовать эту функцию Confidence.Normесли возможно.

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

синтаксис

= CONFIDENCE( alpha, standard_dev, size )

темы

• alfa: Уровень значимости (= 1 – уровень достоверности). (Например, уровень значимости 0,05 соответствует уровню достоверности 95%).
• standard_dev: Стандартное отклонение генеральной совокупности.
• size: Размер выборки населения.

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

Confidence Interval =   x   ±   CONFIDENCE

пример

В приведенной ниже таблице доверительная функция Excel используется для расчета доверительного интервала со значимостью 0,05 (т. е. уровень достоверности 95%) для среднего значения выборки из 100 мужских ростов. Среднее значение выборки составляет 1,8 метра, а стандартное отклонение — 0,07 метра.

Предыдущая функция возвращает доверительное значение 0,013719748.

Следовательно, доверительный интервал составляет 1,8 ± 0,013719748, что эквивалентно диапазону между 1,786280252 и 1,813719748.

CONFIDENCE.NORM

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

Например. Для данной совокупности и вероятности 95 % доверительный интервал представляет собой диапазон, в котором параметр совокупности с вероятностью 95 % упадет.

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

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

синтаксис

= CONFIDENCE.NORM( alpha, standard_dev, size )

темы

• alfa: Уровень значимости (= 1 – уровень достоверности). (Например, уровень значимости 0,05 соответствует уровню достоверности 95%).
• standard_dev: Стандартное отклонение генеральной совокупности.
• size: Размер выборки населения.

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

Confidence Interval =   x   ±   CONFIDENCE

пример

В приведенной ниже таблице доверительная функция Excel используется для расчета доверительного интервала со значимостью 0,05 (т. е. уровень достоверности 95%) для среднего значения выборки из 100 мужских ростов. Среднее значение выборки составляет 1,8 метра, а стандартное отклонение — 0,07 метра.

Предыдущая функция возвращает доверительное значение 0,013719748.

Следовательно, доверительный интервал составляет 1,8 ± 0,013719748, что эквивалентно диапазону между 1,786280252 и 1,813719748.

Ercole Palmeri