Основные статистические функции в Excel: использование, формулы

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

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

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

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

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

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

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

Примечание. Есть другой способ выбрать нужную функцию. На вкладке «Формулы» панели инструментов «Библиотека функций» щелкните значок «Другие функции», затем выберите пункт «Статистика» и, наконец, в открывшемся списке (который можно прокручивать) — оператор.

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

СРЗНАЧ

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

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

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

1. конкретные числа;
2. ссылки на ячейки, которые можно указать вручную (набрать с клавиатуры) или, находясь в соответствующем поле, щелкнуть нужный элемент в самой таблице;
3. диапазон ячеек — указывается вручную или путем выбора в таблице.
4. переход к следующей теме происходит, щелкнув соответствующее поле перед ним или просто нажав клавишу Tab.

   

МАКС

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

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

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

МИН

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

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

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

СРЗНАЧЕСЛИ

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

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

Аргументы указывают:

1. Диапазон ячеек: вручную или с помощью выделения в таблице;
2. Условие выбора значений из указанного диапазона (плюс, минус, не равно) — в кавычках;
3. Средний_ диапазон не является обязательным.
   

МЕДИАНА

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

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

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

НАИБОЛЬШИЙ

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

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

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

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

НАИМЕНЬШИЙ

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

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

МОДА.ОДН

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

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

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

Функция MODA.NSK также используется для вертикальных массивов.

СТАНДОТКЛОН

Функция STDEV также устарела (но ее все еще можно найти, выбрав алфавитный список), и теперь представлены два новых:

• СТАНДОТКЛОН.B — Находит стандартное отклонение выборки
• STDEV.G — определяет стандартное отклонение на основе общей согласованности

Функциональные формулы выглядят так:

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

СРОТКЛ

Синтаксис:

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

Результат: Среднее абсолютных значений отклонений точек данных от среднего. Функция AVEDEV — это мера дисперсии набора данных.

Предметы:

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

РОСТ

Синтаксис:

РОСТ (известный_y, известный_x, новый_x, const)

Результат: Подгоняет экспериментальную кривую к известному_y и известному_x и возвращает значения этой кривой, соответствующие значениям x, указанным в new_x.

Предметы:

• known_y — это набор значений y, которые уже известны для отношения y — b * m / x (если массив known_y имеет столбец, то каждый столбец массива known_y интерпретируется как отдельная переменная; если массив known_y имеет один row, поэтому каждая строка в массиве known_x интерпретируется как отдельная переменная; если любое число в known_y равно 0 или отрицательно, РОСТ возвращает значение ошибки # ЧИСЛО!);
• известные_x — это необязательный набор уже известных значений x для отношения y = b * m / x (массив известные_x может содержать один или несколько наборов переменных; если используется только одна переменная, известные_y и известные_x могут иметь любую форму, при условии, что они имеют одинаковый размер; если используется более одной переменной, обратите внимание: _y должен быть вектором (т.е одна строка в высоту или один столбец в ширину); если известное_x опущено, предполагается, что это массив {1; 2 ; 3;…} того же размера, что и размер known_y);
• new_x — это новые значения x, для которых РОСТ возвращает соответствующие значения y (new_x должен содержать столбец (или строку) для каждой независимой переменной, как и known_x , ​​поэтому, если known_y является одним столбцом, то known_x и excluded_x должны иметь одинаковое количество столбцов; если known_y — одна строка, known_x и new_x должны иметь одинаковое количество строк; если new_x опущен, предполагается, что он равен известному_x, если опущены как known_x, так и new_x, предполагая, что это массив { 1; 2; 3;…} того же размера, что и известные значения y);
• const — логическое значение; если аргумент const отсутствует или ИСТИНА, то обычно вычисляется b; если аргумент const имеет значение FALSE, тогда b устанавливается равным 1, а значения m выбираются так, чтобы соблюдалось соотношение y = m / x.

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

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

МОДА

Синтаксис:

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

Результат: наиболее частое значение в массиве или диапазоне данных. Как и функция МЕДИАНА, функция РЕЖИМ является мерой относительного положения значений.

Предметы:

• число1, число2,… — от 1 до 30 аргументов, для которых рассчитывается функция РЕЖИМ; в функции MODE вы можете использовать массив или ссылку на массив вместо аргументов.

ДОВЕРИТ

Синтаксис:

ДОВЕРИЕ (альфа; std_dev; размер)

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

Предметы:

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

ЛИНЕЙН

Синтаксис:

ЛИНЕЙН (известный_y, известный_x, константа, статистика)

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

y = m1 * 1 + m2 * 2 +… + b или y = mx + b

где зависимое значение y является функцией независимого значения x, t — это матрица значений наклона результирующей линии, а b — абсцисса точки пересечения прямой с осью Y. ЛИНЕЙН также может возвращать дополнительную статистику регрессии.

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

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

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

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

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

Агрегатная функция MAX возвращает наибольшее значение в диапазоне ячеек:

Агрегатная функция MIN возвращает наименьшее значение в диапазоне ячеек:

ВЕРОЯТНОСТЬ

Синтаксис:

ВЕРОЯТНОСТЬ (диапазон_кс, диапазон_возможностей, нижний_ предел, верхний предел)

Результат: значение вероятности того, что значение интервала находится в указанных пределах. Если upper_limit не указан, возвращается вероятность того, что значения в x_interval равны значению lower_limit.

Предметы:

• x_interval — диапазон числовых значений x;
• interval_probabilities — множество вероятностей появления значений, входящих в аргумент x_interval;
• lower_limit — нижний предел значения, для которого рассчитывается вероятность;
• upper_limit — необязательный верхний предел значения, для которого вы хотите рассчитать вероятность.

НАИБОЛЬШИЙ()

Возвращает n-е по величине значение из массива числовых данных. Например, на изображении ниже мы нашли пятое по величине значение из списка.

Чтобы проверить это, вы можете отсортировать числа в порядке возрастания:

КОВАР

Синтаксис:

КОВАР (матрица1, матрица2)

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

Предметы:

• array1 — первый массив или диапазон данных;
• array2 — второй массив или диапазон данных.

РАНГ

Синтаксис:

РАНГ (номер, ссылка, заказ)

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

Предметы:

• число — число, по которому определяется степень;
• ссылка: массив или ссылка на список чисел (нечисловые значения в ссылке игнорируются);
• порядок — это число, определяющее метод сортировки (если порядок равен 0 или опущен, Excel определяет ранг числа, как если бы ссылка была списком, отсортированным в порядке убывания; если порядок — ненулевое число, Excel определяет ранг номера, как если бы ссылка была списком, отсортированным в порядке возрастания).

ПРИМЕЧАНИЕ

Одинаковые числа получают одинаковый ранг в списке.

МАКС

Синтаксис:

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

Результат: наибольшее значение в списке аргументов.

Предметы:

• число1, число2,… — от 1 до 30 чисел, среди которых ищется максимальное значение. Вы можете указать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел; аргументы, которые являются значениями ошибок или текстами, которые не могут быть преобразованы в числа, генерируют значения ошибок. Если аргумент является массивом или ссылкой, учитываются только числа. Пустые ячейки, логические значения, тексты или значения ошибок в массиве или ссылке игнорируются. Если аргументы не содержат чисел, функция МАКС возвращает 0.

НОРМРАСП

Синтаксис:

НОРМ.РАСП (x; среднее; стандартное_откл; совокупное)

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

Предметы:

• x — значение, для которого строится распределение;
• среднее — среднее арифметическое распределения;
• standard_dev — стандартное отклонение распределения;
• кумулятивное — логическое значение, определяющее форму функции (если кумулятивное значение ИСТИНА, НОРМРАСП возвращает кумулятивную функцию распределения; если этот аргумент имеет значение ЛОЖЬ, возвращается функция плотности распределения).

СЧЕТ

Синтаксис:

СЧЁТ (значение1; значение2,…)

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

Предметы:

• значение1, значение2,… — не более 30 аргументов; если аргумент является матрицей или адресной ссылкой, при вычислении учитываются только числа, в других случаях учитываются пустые поля, числовые поля, логические значения и текстовые представления чисел (но не значения ошибка или неконвертируемый и текст).

КОРЕЛ

Синтаксис:

COREL (массив1; массив2)

Результат: коэффициент корреляции между диапазонами ячеек аргументов array1 и array2. Коэффициент корреляции используется для определения наличия взаимосвязи между двумя свойствами. Например, можно установить зависимость между средней температурой в помещении и наличием кондиционера.

Предметы:

• array1 — первый массив диапазона данных;
• array2 — второй массив диапазона данных.

КВАДРОТКЛ

Синтаксис:

КВАДРОТКЛ (число1; число2,…)

Результат: сумма квадратов отклонений точек данных от их среднего значения.

Предметы:

• число1, число2,… — от 1 до 30 аргументов, для которых вычисляется сумма квадратов отклонений; в функции SQUARE вместо аргументов вы можете использовать массив или ссылку на массив.

Функция СЧЁТ

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

Синтаксис: = COUNT (значение1; значение2;…), где значение1 — обязательный аргумент, который принимает значение, ссылку на ячейку, диапазон ячеек или массив. Аргументы от значения2 до значения255 необязательны и такие же, как для значения1.

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

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

= COUNT (1; 2; «5») — результат функции 3, так как строка «5» преобразуется в число.
= COUNT ({1; 2; «5»}) — результатом выполнения функции будет значение 2, поскольку, в отличие от первого примера, число в виде строки записывается в массив, поэтому оно не будет преобразовано.
= COUNT (1; 2; TRUE) — результат функции 3. Если бы логическое значение было в массиве, оно не было бы засчитано как число.

СТАНДОТКЛОНА

Синтаксис:

СТАНДОТКЛОН (значение1; значение2,…)

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

Предметы:

• значение1, значение2,… — от 1 до 30 аргументов, соответствующих выборке из генеральной совокупности. Вы можете использовать массив или ссылку на массив вместо аргументов, разделенных запятыми. Стандартное отклонение рассчитывается по той же формуле, что и в функции СТАНДОТКЛОН. Однако значения аргументов могут быть не только числовыми, но также текстовыми и логическими значениями. Аргумент, содержащий ИСТИНА, оценивается с помощью 1, а аргумент, содержащий ЛОЖЬ или текст, заменяется на 0.

КВПИРСОН

Синтаксис:

KWPIRSON (известный_y, известный_x)

Результат: возведенный в квадрат коэффициент корреляции Пирсона для заданных точек в известных_y и известных_x. Значение r-квадрат можно интерпретировать как отношение дисперсии y к дисперсии x.

Предметы:

• известные_y — это массив или диапазон точек данных;
• известные_x — это массив или диапазон точек данных.

ЧАСТОТА

Синтаксис:

ЧАСТОТА (массив_данных, карманный_массив)

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

Предметы:

• data_array — это массив или ссылка на набор данных, для которых вычисляются частоты; если аргумент data_array не содержит значений, функция ЧАСТОТА возвращает массив нулей;
• pocket_array — это массив или ссылка на набор диапазонов, в которых сгруппированы значения аргумента data_array Если аргумент pocket_array не содержит значений, функция ЧАСТОТА возвращает количество элементов в аргументе data_array.

ПРИМЕЧАНИЕ

ЧАСТОТА не учитывает пустой текст или ячейки.

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

Использование статистических функций облегчает статистический анализ данных пользователем. Количество статистических функций, доступных в седьмой версии программы, увеличилось, и можно утверждать, что с точки зрения набора доступных функций сегодня Excel почти так же хорош, как специальные программы для обработки статистических данных. Для использования всех статистических функций необходимо загрузить надстройку Analysis Pack.Основой статистического анализа является изучение популяций и выборок. Выборка — это подмножество населения. Изучая выборки, вычисляя отклонения и отслеживая отношения с генеральной совокупностью, вы можете увидеть, насколько репрезентативна выборка. Многочисленные статистические функции в Excel предназначены для вероятностного анализа. Некоторые из наиболее распространенных функций описаны ниже. Пользователь может найти информацию о других функциях в подсистеме помощи.

В СРЕДНЕМ

Синтаксис: AVERAGE (число1; число2, .)

Результат: среднее арифметическое аргументов.

Аргументы: число1, число2,. — числа или имена, массивы или адресные ссылки на диапазон ячеек, содержащих ссылки. Функция СРЗНАЧ позволяет указать от 1 до 30 аргументов;

УЧЕТНАЯ ЗАПИСЬ

Синтаксис: COUNT (значение1; значение2, .)

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

Аргументы: значение1, значение2,. — не более 30 тем; пустые поля игнорируются в массивах и адресных диапазонах.

СЧЁТЕСЛИ

Синтаксис: COUNTIF (число; критерий)

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

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

Примечание. В этом аргументе можно использовать подстановочные знаки вопросительного знака (?) И звездочки (*). Знак вопроса соответствует любому символу, звездочка соответствует любой последовательности символов. Для поиска вопросительного знака или самой звездочки поставьте перед ними знак

СЕБЯ

Результат: возвращает одно значение, если указанное условие возвращает ИСТИНА, и другое значение, если ЛОЖЬ. Функция ЕСЛИ используется для проверки условий значений и формул.

Аргументы: Log_expression — это любое значение или выражение, которое принимает значение ИСТИНА или ЛОЖЬ. Например, A10 = 100 — логическое выражение; если значение в ячейке A10 равно 100, это выражение возвращает ИСТИНА, в противном случае — ЛОЖЬ. Этот аргумент можно использовать в любом операторе сравнения. Value_if_true — это значение, возвращаемое, если логический_тест имеет значение TRUE. Например, если указанный аргумент — «В пределах бюджета», а аргумент «log_test» — ИСТИНА, функция ЕСЛИ отображает текст «В пределах бюджета». Если boolean_test равен TRUE, а value_if_true не указан, возвращаемое значение — 0 (ноль). Чтобы отобразить слово ИСТИНА, для этого аргумента необходимо использовать логическое значение ИСТИНА. Value_if_true может быть формулой. Value_if_False — это значение, возвращаемое, если logical_test имеет значение FALSE. Например, если указанный аргумент является строкой «Превышение бюджета», а аргумент «log_test» имеет значение ЛОЖЬ, функция ЕСЛИ отображает текст «Превышение бюджета». Если boolean_test равен FALSE, а value_if_false опущен (то есть после value_if_true нет точек с запятой), логическое значение FALSE. Если boolean_test равен FALSE и value_if_false пуст (то есть после value_if_true стоит точка с запятой, за которой следует закрывающая скобка), возвращается 0 (ноль). Value_if_false может быть формулой.

Примечание. Вы можете использовать до 64 вложенных функций ЕСЛИ в качестве значений аргументов true_value и false_value для создания более сложных элементов управления. После оценки аргументов true_value и false_value функция IF возвращает результирующее значение. Если один из аргументов функции ЕСЛИ является массивом (Array. Объект, используемый для извлечения нескольких значений из одной формулы или для работы с набором аргументов, расположенных в разных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет группу констант, используемых в качестве аргументов.) функция ЕСЛИ оценивает все элементы массива. Excel содержит дополнительные функции, которые можно использовать для анализа данных с использованием условий. Например, чтобы подсчитать количество появлений текстовой строки или числа в диапазоне ячеек, используйте функции СЧЁТЕСЛИ и СЧЁТЕСЛИ.

SRGEOM

Синтаксис: SRGEOM (число1; число2, .)

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

Темы: номер1, номер2,. — от 1 до 30 тем, для которых рассчитывается среднее геометрическое; в функции SRGEOM вместо аргументов можно использовать массив или ссылку на массив.

МАКСИМУМ

Синтаксис: MAX (число1; число2, .)

Результат: наибольшее значение в списке аргументов.

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

MIN

Синтаксис: MIN (число1; число2, .)

Результат: наименьшее значение в списке аргументов.

Темы: номер1, номер2,. — не более 30 тем; игнорируются только значения ошибок и текст, которые нельзя преобразовать в числа; если ни один из аргументов не содержит чисел, функция MIN возвращает 0.

В НАЛИЧИИ

Синтаксис: VAR (число1; число2, .)

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

Темы: номер1, номер2. — не более 30 тем; текстовые, логические и пустые поля вызывают ошибку.

СРГЕОМ

Синтаксис:

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

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

Предметы:

• число1, число2,… — от 1 до 30 аргументов, для которых вычисляется среднее геометрическое; в функции SRGEOM вместо аргументов можно использовать массив или ссылку на массив.

НОРМАЛИЗАЦИЯ

Синтаксис:

НОРМАЛИЗАЦИЯ (x, среднее, стандартное_откл)

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

Предметы:

• x — нормализованное значение;
• среднее — среднее арифметическое распределения;
• standard_dev — стандартное отклонение распределения.

ПРИМЕЧАНИЕ

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

ЛОГНОРМОБР

Синтаксис:

LOGNORMINV (вероятность; среднее; стандартное_отклонение)

Результат: функция, обратная логарифмически нормальному распределению x, где 1 / (x) имеет нормальное распределение со средним и стандартными параметрами> _dev. Если p = LOGININ (x,…), а затем LOGININV (p,…) = x, для анализа преобразованных данных журнала используется обычная доставка журнала.

Предметы:

• вероятность — вероятность, связанная с нормальным логарифмическим распределением;
• среднее — среднее ln (x);
• стандартное_отклонение — стандартное отклонение ln (x).

СРЗНАЧ

Синтаксис:

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

Результат: среднее арифметическое аргументов.

Предметы:

• число1, число2,… — числа или имена, массивы или адресные ссылки на диапазон ячеек, содержащих ссылки. Функция СРЗНАЧ позволяет указать от 1 до 30 аргументов.

ПРЕДСКАЗ

Синтаксис:

ПРОГНОЗ (x; известные_y, известные_x)

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

Предметы:

• x — точка данных, для которой ожидается значение;
• known_y’s — зависимый массив или диапазон данных;
• известные_x — это независимый массив или диапазон данных.

СТАНДОТКЛОНП

Синтаксис:

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

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

Предметы:

• число1, число2,… — от 1 до 30 числовых аргументов, соответствующих генеральной совокупности; вы можете использовать массив или ссылку на массив вместо аргументов, разделенных точкой с запятой.

ПРИМЕЧАНИЕ

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

МЕДИАНА

Синтаксис:

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

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

Предметы:

• число1, число2,… — числа или имена, массивы или адресные ссылки на диапазон ячеек, содержащих ссылки.

СТАНДОТКЛОН

Синтаксис:

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

Результат: расчетное стандартное отклонение выборки. Стандартное отклонение — это мера ширины разброса точек данных относительно их среднего значения.

Предметы:

• число1, число2,… — от 1 до 30 числовых аргументов, соответствующих выборке генеральной совокупности.

ПРИМЕЧАНИЕ

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

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

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

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

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

Формула очень проста: = СРГЕОМ (число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. Средний диапазон. Это диапазон, из которого будет взято среднее арифметическое. Этот аргумент является необязательным, поскольку диапазон ячеек и средний диапазон могут совпадать.

Статистические функции Excel, которые необходимо знать

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

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

В этой статье мы не будем касаться таких популярных статистических функций Excel, как СЧЁТ и СЧЁТЕСЛИ, для них подготовлен отдельный урок.

Агрегатная функция AVERAGE возвращает среднее арифметическое своих аргументов.

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

Если вычисляемый диапазон содержит пустые ячейки или ячейки, содержащие текст, они игнорируются. В приведенном ниже примере поиск среднего значения выполняется для четырех ячеек, например (4 + 15 + 11 + 22) / 4 = 13

Если вам нужно рассчитать среднее значение, учитывая все ячейки в диапазоне, вы можете использовать статистическую функцию СРЕДНИЙ. В следующем примере среднее значение уже ищется для 6 ячеек, например (4 + 15 + 11 + 22) / 6 = 8,6 (6).

Агрегатная функция AVERAGE может использовать математические операторы и различные функции Excel в качестве аргументов:

ЛГРФПРИБЛ

Синтаксис:

LGRFPRIBL (известные_y, известные_x, константа, статистика)

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

Предметы:

• known_y’s — это набор значений y (если в массиве known_y есть столбец, то каждый столбец в массиве known_x интерпретируется как отдельная переменная; если в массиве known_y’s есть строка, то интерпретируется каждая строка в массиве known_x как отдельная переменная);
• известные_x — это необязательный набор уже известных значений x для отношения y = mx + b (массив known_x может содержать один или несколько наборов переменных; если используется только одна переменная, то аргументы known_y для known_y могут быть массивами любой формы , при условии, что они имеют одинаковый размер; если используется более одной переменной, то известное_y должно быть вектором (т е одна строка в высоту или один столбец в ширину); если известное_x опущено, предполагается, что это массив {1; 2; 3; ..} имеет тот же размер, что и массив known_y);
• const — логическое значение; если аргумент отсутствует или ИСТИНА, то b вычисляется обычным образом; если аргумент имеет значение FALSE, то b устанавливается равным 1 и знаки m выбираются так, чтобы удовлетворялось соотношение y = m / x;
• statistics — логическое значение, указывающее, следует ли возвращать дополнительную статистику регрессии (если аргумент имеет значение ИСТИНА, функция LGRFPRIBL возвращает дополнительную статистику регрессии, чтобы возвращаемый массив выглядел так: {mn; mn-1;…; m1; b: sen; sen-1;…; se1; seb: r2; sey: F; df: ssreg; ssresid}; если аргумент FALSE или опущен, функция LGRFPRIBL возвращает только m коэффициентов константа b).

ДИСП

Синтаксис:

DISP (число1; число2,…)

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

Предметы:

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

МИН

Синтаксис:

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

Результат: наименьшее значение в списке аргументов.

Предметы:

• число1, число2,… — не более 30 аргументов; игнорируются только значения ошибок и текст, которые нельзя преобразовать в числа; если ни один из аргументов не содержит чисел, функция MIN возвращает 0.