Статистические функции в excel с примерами

Функция МАКС

Возвращает максимальное числовое значение из списка аргументов.

Синтаксис: =МАКС(число1; ; …), где число1 является обязательным аргументом, все последующие аргументы (до число255) необязательны. Аргумент может принимать числовые значения, ссылки на диапазоны и массивы. Текстовые и логические значения в диапазонах и массивах игнорируются.

=МАКС() – возвращает результат 5, при этом строка «50» игнорируется, т.к. задана в массиве.=МАКС(1;2;3;4;0;-5;5;»50″) – результатом функции будет 50, т.к. строка явно задана в виде отдельного аргумента и может быть преобразована в число.=МАКС(-2; ИСТИНА) – возвращает 1, т.к. логическое значение задано явно, поэтому не игнорируется и преобразуется в единицу.

Как включить анализ данных в Excel: полное руководство

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

1. Установите Excel

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

2. Откройте Excel и импортируйте данные

После того, как Excel установлен, откройте программу и импортируйте ваши данные. Вы можете сделать это, выбрав пункт «Открыть» в меню «Файл» и выбрав файл с вашими данными.

3. Отформатируйте данные

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

4. Используйте встроенные функции

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

Чтобы использовать функции в Excel, выберите ячейку, в которую хотите вывести результат, и введите формулу, начинающуюся с знака «=». Например, «=AVERAGE(A1:A10)» выведет среднее значение для диапазона ячеек от A1 до A10.

5. Создайте графики и диаграммы

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

6. Примените условное форматирование

Условное форматирование позволяет выделить определенные значения или тренды в вашей таблице, чтобы легче проследить за ними. Вы можете применить условное форматирование, выбрав нужные данные и затем выбрав опцию «Условное форматирование» в меню «Главная». Выберите тип условия, например, «Больше», «Меньше» или «Равно», и задайте значения для форматирования.

7. Используйте анализ данных инструменты

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

8. Постройте сводные таблицы

Сводные таблицы позволяют суммировать и анализировать данные из разных источников и показывать их в удобном формате. Чтобы создать сводную таблицу, выберите данные, которые хотите анализировать, и выберите опцию «Сводная таблица» в меню «Вставка». Затем выберите поля, которые вы хотите добавить в строки, столбцы и значения.

9. Сделайте прогнозирование

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

10. Автоматизируйте анализ данных с помощью макросов

Если вам часто приходится выполнять одни и те же операции с данными, вы можете автоматизировать их с помощью макросов. Макросы в Excel позволяют записывать серию действий и выполнять их одним нажатием кнопки. Чтобы создать макрос, выберите опцию «Разработчик» в меню «Файл» и выберите «Записать макрос».

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

Функция МИН

В статистических подсчетах нередко нужно не только определить среднее значение, среднеквадратическое отклонение и вычислить другие показатели

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

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

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

Функция МАКС

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

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

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

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

Максимальное количество аргументов, которые можно использовать в этой функции – 255. При этом обязательным аргументом является только первое число. В качестве аргументов не могут использоваться текстовые и логические значения. Они просто не учитываются формулой, в которой используется указанный оператор. Основное отличие функции СРЗНАЧА от СРЗНАЧ заключается в том, что текстовые значения и «ЛОЖЬ» считаются нулевыми, а значение «Истина» приравнивается к единице.

Функция РАНГ.СР

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

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

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

Источник

Как рассчитать процент в Excel

Рассчитать проценты в Excel так же просто, как и везде: просто разделите два числа и умножьте на 100. Допустим, мы рассчитываем процент 347 из 521.

Просто разделите 347 на 521, используя синтаксис = 347/521. (Если вы не знакомы с Excel, знак равенства говорит Excel, что вы хотите, чтобы он что-то вычислял. Просто введите уравнение после этого и нажмите Войти запустить расчет.)

Теперь у вас есть десятичное значение (в данном случае 0,67). Чтобы преобразовать его в процент, нажмите Ctrl + Shift + 5 на клавиатуре (это очень полезное сочетание клавиш в Excel

добавить в свой арсенал).

Вы также можете изменить формат ячейки, щелкнув правой кнопкой мыши ячейка, выбирая Формат ячеек, выбор процент, и нажав Хорошо.

Помните, что при изменении формата ячейки выполняется шаг «умножить на 100». Если вы умножите на 100, а затем измените формат на процент, вы получите другое умножение (и неправильное число).

Совет: Узнайте, как создавать выпадающие списки для ячеек Excel

,

СУММЕСЛИМН

Функция СУММЕСЛИМН позволяет суммировать значения по определенным условиям. Условий может быть несколько. В Excel также есть функция СУММЕСЛИ, которая позволяет суммировать по одному критерию. Призываю вас использовать более универсальную формулу.

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

Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город

Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва. Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город.

Более подробно о функциях СУММЕСЛИМН и СУММЕСЛИ рассказано в статье «СУММЕСЛИ и СУММЕСЛИМН в Excel».

Арифметические формулы

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

Одной из самых простых арифметических формул является сложение. Для выполнения сложения в Excel используется знак «+». Например, формула «=A1+B1» сложит значения, находящиеся в ячейках A1 и B1.

Вычитание в Excel выполняется с использованием знака «-«. Например, формула «=A1-B1» вычтет значение ячейки B1 из значения ячейки A1.

Умножение в Excel выполняется с использованием знака «*». Например, формула «=A1*B1» умножит значения ячеек A1 и B1.

Деление в Excel выполняется с использованием знака «/». Например, формула «=A1/B1» разделит значение ячейки A1 на значение ячейки B1.

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

Использование арифметических формул в 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}; если аргумент имеет значение ЛОЖЬ или опущен, то функция ЛГРФПРИБЛ возвращает только коэффициенты т и постоянную Ь).

Финансовые функции Excel

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

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

1. Выберите мышкой ячейку, в которой будет находиться значение этой функции. Найдите в списке финансовых функций ДОХОД и выберите ее.
2. В окне аргументов щелкните мышкой в поле первого аргумента, в данном случае это будет аргумент «Дата_согл», затем щелкните мышкой по ячейке со значением его. В поле аргумента появится значение этой ячейки. Аналогичным образом заполните остальные поля.
3. После нажатия кнопки «ОК» в ячейке «Доход» появится рассчитанное значение данной функции.

Наиболее часто используемые финансовые функции:

• БС. Находит будущую стоимость инвестиций.
• ВСД. Внутренняя ставка доходности.
• МВСД. Модифицированная внутренняя ставка доходности.
• ПРПЛТ. Процентные платежи за указанный период.
• ПС. Приведенная стоимость инвестиций.
• СТАВКА. Вычисляет ставку процентов по аннуитету.
• ЭФФЕКТ. Фактическая процентная ставка.

Округлить до определенного числа цифр: ОКРУГЛ.

Если на листе есть десятичные числа, которые вы хотите округлить в большую или меньшую сторону, используйте функцию ОКРУГЛ в Excel.

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

Например, чтобы округлить число 2,25 до одного десятичного знака, введите следующее и нажмите Enter:

= ОКРУГЛ (2,25,1)

И у вас есть свои результаты. Если вы хотите округлить в меньшую сторону, просто используйте отрицательное число в качестве аргумента цифр.

Последние мысли

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

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

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

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

Чувствуете, что хотите выйти на новый уровень? Ознакомьтесь с нашей шпаргалкой по расширенным формулам Excel.

Функция ВПР (). Вертикальный поиск результата в Excel (англ. VLOOKUP)

Наверняка многие из вас слышали выражение “свэпээрить данные” или “подтянуть данные”. Все это можно сделать с помощью функции ВПР. 

ВПР ищет значение по заданному критерию в левом столбце одной таблицы и возвращает его в другую таблицу или просто в ячейку.

Синтаксис функции:

=ВПР(Искомое значение; Диапазон для поиска; Номер столбца, из которого берутся данные;0 или 1)

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

На практике чаще всего используется точный поиск, т.е. нужно указать 0.

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

Принцип действия функции проще понять на примере:

Необходимо узнать, сколько дней в сентябре. В ячейке H5 находится искомое значение: сентябрь. В качестве Диапазона для поиска выделяем диапазон ячеек E2:F14. Номер столбца указываем 2, т.к. нужное нам значение — число дней — находится во втором столбце. Интервальный просмотр указываем 0 — точный поиск.

У функции ВПР много особенностей, которые подробно и с примерами рассмотрены в статье:

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

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

Синтаксис: =СРЗНАЧЕСЛИМН(диапазон_усреднения; диапазон_условия1; условие1; ; ; …), где

• диапазон_усреднения – обязательный аргумент. Ссылка на ячейки с числовыми значениями для определения среднего арифметического.
• диапазон_условия1 – обязательный аргумент. Диапазон ячеек для проверки.
• условие1 – обязательный аргумент. Значение либо условие проверки. Для текстовых значений могут быть использованы подстановочные символы (* и ?). Условия типа больше, меньше заключаются в кавычки.

Все последующие аргументы от диапазон_условия2 и условие2 до диапазон_условия127 и условие127 являются необязательными.

Используем таблицу из примера предыдущей функции с добавлением городов для сотрудников. Выведем среднюю заработную плату для электриков в городе Москва.Результат выполнения функции 25 000.Функция принимает в расчет только те значения, которые подходят под все условия.

Уровень надежности

Уровень надежности – означает вероятность того, что доверительный интервал содержит истинное значение оцениваемого параметра распределения.

Вместо термина Уровень надежности часто используется термин Уровень доверия . Про Уровень надежности (Confidence Level for Mean) читайте статью Уровень значимости и уровень надежности в MS EXCEL .

Задав значение Уровня надежности в окне надстройки Пакет анализа , MS EXCEL вычислит половину ширины доверительного интервала для оценки среднего (дисперсия неизвестна) .

Тот же результат можно получить по формуле (см. файл примера ): =ДОВЕРИТ.СТЬЮДЕНТ(1-0,95;s;n) s – стандартное отклонение выборки , n – объем выборки .

Подробнее см. статью про построение доверительного интервала для оценки среднего (дисперсия неизвестна) .

Как включить анализ данных в Excel 2010, 2007, 2013

При выполнении сложных аналитических задач по статистике (к примеру, корреляционного и дисперсионног…

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

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

Выясним, как включить анализ данных в Excel 2007, 2010, 2013.

Для Excel 2010, 2013

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

Включение блока инструментов

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

1. зайдите во вкладку «Файл», расположенную в верхней части ленты интерфейса;
2. с левой стороны открывающегося меню найдите раздел «Параметры Эксель» и кликните по нему;
3. просмотрите левую часть окошка, откройте категорию надстроек (вторая снизу в списке), выберите соответствующий пункт;
4. в выпавшем диалоговом меню найдите пункт «Управление», кликните по нему мышью;
5. клик вызовет на экран диалоговое окно, выберите раздел надстроек, если выставлено значение, отличное от «Надстройки Excel», поменяйте его на обозначенное;
6. нажмите на экранную кнопку «Перейти» в разделе надстроек. В правой части выпадет список надстроек, которые устанавливает программа.

Поиск пакета в надстройках Excel

Активация

Рассмотрим, как активировать аналитические функции, предоставляемые надстройкой пакета:

1. В перечне надстроек, выпавшем после последовательного выполнения предыдущих операций, пользователю надлежит поставить знак птички напротив раздела «Пакет анализа».
2. Выбрав активацию пакета, необходимо нажать клавишу «Ок», расположенную в верхней правой части диалогового окна.
3. После нажатия кнопки пакет появляется на ленте функций. Для получения доступа к нему в интерфейсе программы выбирается вкладка «Данные». В правой части меню «Раздел анализа». Там пользователь найдет иконку опции «Анализ данных».

Выбор нужной надстройки

Запуск функций группы «Анализ данных»

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

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

Расположение функции «Анализ данных» на вкладке «Данные»

Чтобы применить ту или иную опцию, действуют по нижеприведенному алгоритму:

1. Нажать на кнопку анализа на ленте.
2. Кликнуть по названию необходимой пользователю функции.
3. Нажать клавишу «Ок», находящуюся рядом с правым верхним углом окошка.
4. В диалоговом окне указать массивы данных, используемые для решения текущей задачи.

Как включить анализ данных в Excel 2010

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

Для Excel 2007

Алгоритм, как включить анализ данных в Excel 2007, отличается от остальных тем, что в самом начале (для выхода на параметры Excel) вместо кнопки «Файл» пользователь нажимает четырехцветный символ Microsoft Office. В остальном же последовательность операций идентична приведенной для других версий.

Анализ данных в Excel 2007: как включить?

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

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

• СРЗНАЧ. Функция предназначена для вычисления среднего значения в диапазонах ячеек. Причем, диапазонов может быть несколько. В расчетах могут использоваться и несмежные диапазоны и ячейки.
• СРЗНАЧЕСЛИ. Функция похожа на предыдущую, но значения для расчета среднего выбираются по указанному условию.
• МАКС. Находит максимальное значение в указанном диапазоне ячеек.
• НАИБОЛЬШИЙ. Находит указанное по величине значение из диапазона ячеек. Например, можно найти второе по величине значение или десятое по величине значение из списка.
• МОДА. Эта функция находит в указанном массиве данных максимально часто встречающееся значение.

СРЗНАЧ() и СРЗНАЧА()

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

Наверно, особо не стоит останавливаться на правилах использования формулы: функция СРЗНАЧ() принимает на вход массив аргументов и дает на выходе среднее значение по всем ячейкам, содержащим числа(!). Это очень важный момент, который далеко не все знают. Поясним на примере.

Пусть дан диапазон А1:С2 и мы ищем среднее значение по всем 6 ячейкам диапазона:

Применение функции СРЗНАЧ()

Однако, результат функции СРЗНАЧ(А1:С2) будет не 8,7, а 13. Почему? (4+15+11+22)/6 = 8,7 ведь?

Да, это правильно, но функция СРЗНАЧ() берет в расчет только те ячейки, где «встречает» числа. Текстовая информация и пустые ячейки просто игнорируются. Поэтому в данном примере СРЗНАЧ() усредняет по 4 ячейкам и выдает правильный ответ – 13.

А вот если нужно произвести усреднение по всему диапазону, вне зависимости от типа данных, нужно использовать функцию СРЗНАЧА().

Принцип работы такой же, как и у СРЗНАЧ(), только на вход будут поступать абсолютно все ячейки. Результат в нашем примере будет уже ожидаемый – 8,7.

Применение функции СРЗНАЧА()

Замечание

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

Например, менеджеру нужно узнать среднедневную выручку за месяц на основании продаж за каждый день. Допустим, за несколько дней ячейки оставлены пустыми. Есть два варианта, почему так произошло:

1. В эти дни не было ни одной продажи. Тогда эти дни должны принимать участие в расчете среднего значения и менеджеру нужно использовать СРЗНАЧА() – так он исключит игнорирование пустых ячеек.

2. Эти дни были выходными. Тогда пропуски сами по себе никакой информации не несут и их надо игнорировать: фактически, эти дни не принимают участие в статистической выборке и функция СРЗНАЧ() поможет их пропустить.

 

КУРС

EXCEL ACADEMY

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Промежуточные математические формулы

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

• ABS() возвращает абсолютное значение числа.

• SQRT() возвращает квадратный корень числа

• RAND() предоставляет случайное число от 0 до 1

Условная логика

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

• СУММЕСЛИМН(диапазон_суммы, диапазон_критериев1, критерий1, )

• СЧЁТЕСЛИМН(диапазон_критериев1, диапазон_критериев1, , …)

• СРЗНАЧЕСЛИМН(диапазон_критериев1, диапазон_критериев1, , …)

Функции оценивают предоставленные критерии и выдают логическое значение ИСТИНА или ЛОЖЬ.

Предположим, у вас есть следующие данные о продажах в столбцах A, B и C:

Чтобы рассчитать сумму продаж красных футболок, используйте формулу СУММЕСЛИМН:

=СУММЕСЛИМН(C2:C6, A2:A6, «Футболка», B2:B6, «Красный»)

Чтобы подсчитать количество рядов с красными футболками, используйте формулу СЧЁТЕСЛИМН:

=СЧЁТЕСЛИМН(C2:C6, A2:A6, «Футболка», B2:B6, «Красный»)

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

=СРЗНАЧЕСЛИ(C2:C6, A2:A6, «Футболка», B2:B6, «Красный»)

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

Вычисляет среднее арифметическое значение для ячеек, отвечающих заданному условию.

Синтаксис: =СРЗНАЧЕСЛИ(диапазон; условие; ), где

• диапазон – обязательный аргумент. Диапазон ячеек для проверки.
• условие – обязательный аргумент. Значение либо условие проверки. Для текстовых значений могут быть использованы подстановочные символы (* и ?). Условия типа больше, меньше записываются в кавычках.
• диапазон_усреднения – необязательный аргумент. Ссылка на ячейки с числовыми значениями для определения среднего арифметического. Если данный аргумент опущен, то используется аргумент «диапазон».

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

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

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

НАИМЕНЬШИЙ()

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

Если отсортировать числа в порядке возрастания, то все станет гораздо очевидней:

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

Например, на рисунке ниже формула возвращает медиану для списка, состоящего из 14 чисел.

Если отсортировать значения в порядке возрастания, то все становится на много понятней:

Возвращает наиболее часто встречающееся значение в массиве числовых данных.

Если отсортировать числа в порядке возрастания, то все становится гораздо понятней:

Статистическая функция МОДА на данный момент устарела, точнее, устарела ее форма записи. Вместо нее теперь используется функция МОДА.ОДН. Форма записи МОДА также поддерживается в Excel для совместимости.

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