Статистические функции Excel, которые необходимо знать
Автор Антон Андронов На чтение 4 мин Опубликовано
Функции категории Статистические предназначены в первую очередь для анализа диапазонов ячеек в Excel. С помощью данных функций Вы можете вычислить наибольшее, наименьшее или среднее значение, подсчитать количество ячеек, содержащих заданную информацию, и т.д.
Данная категория содержит более 100 самых различных функций Excel, большая часть из которых предназначена исключительно для статистических расчетов и обычному рядовому пользователю покажется темным лесом. Мы же в рамках этого урока рассмотрим самые полезные и распространенные функции данной категории.
В рамках данной статьи мы не будем затрагивать такие популярные статистические функции Excel, как СЧЕТ и СЧЕТЕСЛИ, для них подготовлен отдельный урок.
Содержание
- СРЗНАЧ()
- СРЗНАЧЕСЛИ()
- МАКС()
- МИН()
- НАИБОЛЬШИЙ()
- НАИМЕНЬШИЙ()
- МЕДИАНА()
- МОДА()
СРЗНАЧ()
Статистическая функция СРЗНАЧ возвращает среднее арифметическое своих аргументов.
Данная функция может принимать до 255 аргументов и находить среднее сразу в нескольких несмежных диапазонах и ячейках:
Если в рассчитываемом диапазоне встречаются пустые или содержащие текст ячейки, то они игнорируются. В примере ниже среднее ищется по четырем ячейкам, т.е. (4+15+11+22)/4 = 13
Если необходимо вычислить среднее, учитывая все ячейки диапазона, то можно воспользоваться статистической функцией СРЗНАЧА. В следующем примере среднее ищется уже по 6 ячейкам, т.е. (4+15+11+22)/6 = 8,6(6).
Статистическая функция СРЗНАЧ может использовать в качестве своих аргументов математические операторы и различные функции Excel:
СРЗНАЧЕСЛИ()
Если необходимо вернуть среднее арифметическое значений, которые удовлетворяют определенному условию, то можно воспользоваться статистической функцией СРЗНАЧЕСЛИ.
Следующая формула вычисляет среднее чисел, которые больше нуля:
В данном примере для подсчета среднего и проверки условия используется один и тот же диапазон, что не всегда удобно. На этот случай у функции СРЗНАЧЕСЛИ существует третий необязательный аргумент, по которому можно вычислять среднее. Т.е. по первому аргументу проверяем условие, по третьему – находим среднее.
Допустим, в таблице ниже собрана статистика по стоимости лекарств в городе. В одной аптеке лекарство стоит дороже, в другой дешевле. Чтобы посчитать стоимость анальгина в среднем по городу, воспользуемся следующей формулой:
Если требуется соблюсти несколько условий, то всегда можно применить статистическую функцию СРЗНАЧЕСЛИМН, которая позволяет считать среднее арифметическое ячеек, удовлетворяющих двум и более критериям.
МАКС()
Статистическая функция МАКС возвращает наибольшее значение в диапазоне ячеек:
МИН()
Статистическая функция МИН возвращает наименьшее значение в диапазоне ячеек:
НАИБОЛЬШИЙ()
Возвращает n-ое по величине значение из массива числовых данных.
Например, на рисунке ниже мы нашли пятое по величине значение из списка.
Чтобы убедиться в этом, можно отсортировать числа в порядке возрастания:
НАИМЕНЬШИЙ()
Возвращает n-ое наименьшее значение из массива числовых данных. Например, на рисунке ниже мы нашли четвертое наименьшее значение из списка.
Если отсортировать числа в порядке возрастания, то все станет гораздо очевидней:
МЕДИАНА()
Статистическая функция МЕДИАНА возвращает медиану из заданного массива числовых данных. Медианой называют число, которое является серединой числового множества. Если в списке нечетное количество значений, то функция возвращает то, что находится ровно по середине. Если же количество значений четное, то функция возвращает среднее для двух чисел.
Например, на рисунке ниже формула возвращает медиану для списка, состоящего из 14 чисел.
Если отсортировать значения в порядке возрастания, то все становится на много понятней:
МОДА()
Возвращает наиболее часто встречающееся значение в массиве числовых данных.
Если отсортировать числа в порядке возрастания, то все становится гораздо понятней:
Статистическая функция МОДА на данный момент устарела, точнее, устарела ее форма записи. Вместо нее теперь используется функция МОДА.ОДН. Форма записи МОДА также поддерживается в Excel для совместимости.
Как известно, категория Статистические в Excel содержит более 100 самых разноплановых функций. Но, как показывает практика, львиная доля этих функций практически не применяется, а особенно начинающими пользователями. В этом уроке мы постарались познакомить Вас только с самыми популярными статистическими функциями Excel, которые Вы рано или поздно сможете применить на практике. Надеюсь, что данный урок был для Вас полезен. Удачи Вам и успехов в изучении Excel.
Оцените качество статьи. Нам важно ваше мнение:
Основные статистические функции в Excel: использование, формулы
Зная статистические формулы и приемы можно обработать, проанализировать и упорядочить большое количество информации.
В Эксель инструменты статистики выведены в отдельную категорию функций. Давайте посмотрим, как их найти, а также, какие из них являются наиболее популярными среди пользователей.
- Использование статистических функций
- СРЗНАЧ
- МАКС
- МИН
- СРЗНАЧЕСЛИ
- МЕДИАНА
- НАИБОЛЬШИЙ
- НАИМЕНЬШИЙ
- МОДА.ОДН
- СТАНДОТКЛОН
- СРГЕОМ
- Заключение
Использование статистических функций
Смотрите также: “Основные математические функции в Excel: использование, формулы”
Формулы функций в Excel можно вводить вручную непосредственно в той ячейке, где планируется выполнить соответствующие расчеты.
Это легко применимо к таким простым действиям, как сложение, вычитание, умножение и деление. Но запомнить формулы сложных функций уже непросто, поэтому проще воспользоваться специальным помощником, который встроен в программу.
Итак, чтобы вставить функцию в ячейку, выполняем одно из следующих действий:
- Находясь в любой вкладке программы щелкаем по значку “Вставить функцию” (fx), которая находится с левой стороны от строки формул.
- Переходим во вкладку “Формулы”, где видим в левом углу ленты инструментов кнопку “Вставить функцию”.
- Используем сочетание клавиш Shift+F3.
Независимо от выбранного способа выше перед нами появится окно вставки функций. Щелкаем по текущей категории и из раскрывшегося списка выбираем пункт “Статистические”.
Далее будет предложен на выбор один из статистических операторов. Отмечаем нужный и жмем OK.
На экране отобразится окно с аргументами выбранной функции, которые нужно заполнить.
Примечание: существует еще один способ выбора требуемой функции. Находясь во вкладке “Формулы” в блоке инструментов “Библиотека функций” щелкаем по значку “Другие функции”, затем выбираем пункт “Статистические” и, наконец, в открывшемся перечне (который можно листать вниз) – нужный оператор.
Давайте теперь рассмотрим наиболее популярные функции.
СРЗНАЧ
Смотрите также: “Как посчитать среднее значение в Excel: формула, функции, инструменты”
Оператор вычисляет среднее арифметическое значение из указанных значений (диапазона). Формула функции выглядит таким образом:
=СРЗНАЧ(число1;число2;…)
В качестве аргументов функции можно указать:
- конкретные числа;
- ссылки на ячейки, которые можно указать как вручную (напечатать с помощью клавиатуры), так и находясь в соответствующем поле щелкнуть по нужному элементу в самой таблице;
- диапазон ячеек – указывается вручную или путем выделения в таблице.
- переход к следующему аргументу происходит путем щелчка по соответствующему полю напротив него или просто нажатием клавиши
МАКС
Функция помогает определить максимальное значение из заданных чисел (диапазона). Формула оператора следующая:
=МАКС(число1;число2;…)
В аргументах функции, также, как и в случае с оператором СРЗНАЧ можно указать конкретные числа, ссылки на ячейки или диапазоны ячеек.
МИН
Функция находит минимальное число из указанных значений (диапазона ячеек). В общем виде синтаксис выглядит так:
=МИН(число1;число2;…)
Аргументы функции заполняются так же, как и для оператора МАКС.
СРЗНАЧЕСЛИ
Функция позволяет найти среднее арифметическое значение, но при выполнении заданного условия. Формула оператора:
=СРЗНАЧЕСЛИ(диапазон;условие;диапазон_усреднения)
В аргументах указываются:
- Диапазон ячеек – вручную или с помощью выделения в таблице;
- Условие отбора значений из заданного диапазона (больше, меньше, не равно) – в кавычках;
- Диапазон_усреднения – не является обязательным аргументом для заполнения.
МЕДИАНА
Оператор находит медиану заданного диапазона значений. Синтаксис функции:
=МЕДИАНА(число1;число2;…)
В аргументах указываются: конкретные числа, ссылки на ячейки или диапазоны элементов.
НАИБОЛЬШИЙ
Функция позволяет найти из указанного диапазона значений с заданной позицией (по убыванию). Формула оператора:
=НАИБОЛЬШИЙ(массив;k)
Аргумента функции два: массив и номер позиции – K.
Допустим, имеется ряд чисел 4, 6, 12, 24, 15, 9. Если мы укажем в качестве аргумента “K” число 2, результатом будет значение, равное 15, т.к. оно второе по величине в выбранном диапазоне.
НАИМЕНЬШИЙ
Функция также, как и оператор НАИБОЛЬШИЙ, выполняет поиск из указанного диапазона значений. Правда, в данном случае счет идет по возрастанию. Синтаксис оператора следующий:
=НАИМЕНЬШИЙ(массив;k)
МОДА.ОДН
Функция пришла на замену более старому оператору “МОДА” (теперь находится в категории “Полный алфавитный перечень”).
Позволяет определять число, которое повторяется чаще остальных в выбранном диапазоне. Работает функция по формуле:
=МОДА.ОДН(число1;число2;…)
В значениях аргументов указываются конкретные числовые значения, отдельные ячейки или их диапазоны.
Для вертикальных массивов, также, используется функция МОДА.НСК.
СТАНДОТКЛОН
Функция СТАНДОТКЛОН также устарела (но ее все еще можно найти, выбрав алфавитный перечень) и теперь представлена двумя новыми:
- СТАДНОТКЛОН.В – находит стандартное отклонение выборки
- СТАДНОТКЛОН.Г – определяет стандартное отклонение по генеральной совопкупности
Формулы функций выглядят следующим образом:
- =СТАДНОТКЛОН.В(число1;число2;…)
- =СТАДНОТКЛОН.Г(число1;число2;…)
СРГЕОМ
Оператор находит среднее геометрическое значение для заданного массива или диапазона.
Формула функции:
=СРГЕОМ(число1;число2;…)
Заключение
В программе Excel более 100 статистических функций. Мы лишь рассмотрели те, которые используются пользователями чаще других, а также, где их можно найти и как заполнить аргументы для получения корректного результата.
Смотрите также:
- Почему Эксель не считает формулу: что делать
- Сортировка и фильтрация данных в Excel
Статистика в Excel | 10 статистических функций в Microsoft Excel
Рам Девани — Опубликовано 27 сентября 2020 г. и изменено 27 сентября 2020 г.
Новичок Бизнес-аналитика Excel Статистика
Обзор
- Microsoft Excel — отличный инструмент для изучения и выполнения статистических функций
- Вот 12 статистических функций в Excel, которыми вы должны овладеть для успешной карьеры аналитика
Преуспеем в статистике!
«Статистика — это грамматика науки».
– Карл ПирсонДавайте сделаем это немного более актуальным для нас. Статистика — это грамматика науки о данных. Вы заметите, что почти каждый успешный специалист по науке о данных или аналитик имеет четкое представление о статистике, но означает ли это, что вам нужна степень магистра в этой области?
Абсолютно нет!
Мы подойдем к этой статье, используя знаменитый принцип Парето — 80% последствий исходят из 20% причин. Поэтому мы сосредоточимся на 20% концепций и функций, которые пригодятся в 80% случаев! Теперь вопрос аналитика: с чего начать внедрение статистики?
Конечно, вы можете реализовать и практиковать эти концепции в таких языках программирования, как Python и R. Но придержите лошадей на секунду и подумайте — какой инструмент чаще всего используется в большинстве организаций?
Это Microsoft Excel! Честно говоря, Excel — это швейцарский армейский нож для профессионалов в области аналитики, который помогает вам сосредоточиться на том, что важно (статистика в нашем случае), а остальные расчеты и настройки выполняет сам.
Эта статья предназначена для всех, кто только начинает изучать статистику с помощью Excel. Мы будем использовать 10 ключевых статистических функций в Excel, чтобы ответить на вопросы фиктивной спортивной компании Khelo, просматривая их данные.
Если вы новичок в мире аналитики и Excel, я настоятельно рекомендую пройти эти бесплатные курсы:
- Введение в бизнес-аналитику
- Microsoft Excel: формулы и функции
Как мы подойдем к этим 10 статистическим функциям в Excel
Я разделил эти статистические функции в Excel на две категории:
- Основные статистические функции
- Промежуточные статистические функции
Начнем!
Понимание данных и постановки задачи
Мы будем решать несколько ключевых вопросов о нашей компании по производству манекенов для спортивного инвентаря и одежды – Khelo. Вот такие столбцы у нас есть:
- Оборудование
- Количество проданных товаров
- Стоимость каждого предмета
- Скидка %
- Доход
В ходе этой статьи мы ответим на следующие вопросы:
- Сколько товаров со скидкой?
- Сколько предметов/оборудования продается в магазине?
- Какое количество товаров продано без скидки?
- Продаются ли какие-либо товары, стоимость которых превышает 2000, а ставка дисконтирования превышает 50%?
- Каково среднее количество проданных товаров?
- Каково среднее количество проданных товаров?
- Какой процент скидки чаще всего?
- Каково стандартное отклонение количества проданных товаров?
- Есть ли связь между количеством проданных товаров и процентом скидки?
Основные статистические функции в Excel
MS Excel предоставляет множество полезных статистических функций.
Давайте начнем с некоторых основных, но чрезвычайно мощных функций. Честно говоря, вы обнаружите, что используете основные статистические функции 90% времени, а остальные 10% вашего времени занимают промежуточные и расширенные функции.
Здесь мы будем говорить в основном о различных видах функций счета. Они очень похожи на другие функции, такие как сумма, максимум, минимум, среднее.
1. Функция счета
Мы используем функцию подсчета, когда нам нужно подсчитать количество ячеек, содержащих число. Помните ТОЛЬКО ЦИФРЫ ! Посмотрим на функцию:
- СЧЕТ(значение1, [значение2], …)
Итак, давайте попробуем найти ответ на наш первый вопрос — Сколько товаров было со скидкой?
11 товаров со скидкой.
2. Функция счета
В то время как функция count подсчитывает только числовые значения, функция COUNTA подсчитывает все непустые ячейки диапазона.
Функция полезна для подсчета ячеек, содержащих информацию любого типа, включая значения ошибок и пустой текст.
- СЧЁТ(значение1, [значение2], …)
3. Отсчет пробелов
Функция COUNTBLANK подсчитывает количество пустых ячеек в диапазоне ячеек. Ячейки с формулами, которые возвращают пустой текст, также учитываются здесь, но ячейки с нулевыми значениями не учитываются. Это отличная функция для суммирования пустых ячеек при анализе любых данных.
- СЧИТАТЬ ПРОПУСТЫ (диапазон)
Только 2 товара без скидки.
4. Функция счета
Countifs — одна из наиболее часто используемых статистических функций в Excel. Функция СЧЁТЕСЛИМН применяет одно или несколько условий к ячейкам в заданном диапазоне и возвращает только те ячейки, которые удовлетворяют всем условиям.
- СЧЁТЕСЛИМН(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерии2]…)
Далее давайте посмотрим на промежуточные статистические функции.Промежуточные статистические функции в Excel
Здесь мы обсудим некоторые промежуточные статистические функции в MS Excel, связанные с центральной тенденцией и дисперсией. Эти функции очень полезны в повседневной жизни аналитика.
5. Средняя функция
Самая распространенная функция, которую мы обычно используем в повседневной жизни, — это среднее (или среднее). Функция СРЗНАЧ просто возвращает среднее арифметическое всех ячеек в заданном диапазоне:
.- СРЗНАЧ(число1, [число2], …)
6. Медианная функция
Проблему выбросов можно решить, используя другую функцию для центральной тенденции – медиану.
Медианная функция возвращает среднее значение заданного диапазона ячеек. Синтаксис довольно прост:
- МЕДИАНА(число1, [число2], …)
7. Режим Функция
Для числовых значений обычно достаточно среднего и медианы, но как насчет категориальных значений? Здесь в игру вступает режим. Режим возвращает наиболее частое и повторяющееся значение в заданном диапазоне значений:
.- MODE.SNGL(номер1,[номер2],…)
MODE.SNGL возвращает только одно значение, тогда как MODE.MULT возвращает массив наиболее часто встречающихся значений. Ну, это просто. Найдем наиболее частое значение скидки, предоставляемой спортивным магазином:
Эта величина скидки составляет 10%.8. Функция стандартного отклонения
Стандартное отклонение— один из способов количественной оценки дисперсии. Это мера того, насколько широко значения разбросаны по сравнению со средним значением.
Здесь мы будем использовать функцию STDEV.P, которая используется для расчета стандартного отклонения на основе всей совокупности, заданной в качестве аргументов:
- СТАНДОТКЛОН.П(номер1,[номер2],…)
Ранее мы рассчитали среднее значение и медиану, чтобы получить представление о центральной тенденции. Давайте найдем стандартное отклонение, чтобы увидеть уровень дисперсии: Как и ожидалось, стандартное отклонение проданного количества меньше, а это означает, что дисперсия меньше, тогда как стандартное отклонение для стоимости продуктов высокое.
9. Функции квартилей
Это еще одна функция, находящая широкое применение в отрасли. Это помогает нам разделить население на группы. QUARTILES.INC возвращает квартиль набора данных на основе значений процентиля от 0 до 1 включительно.
Например, вы можете использовать эту функцию, чтобы узнать 25% лучших клиентов вашей клиентской базы.
- КВАРТИЛЬ.ВКЛ(массив, кварт)
10. Функция корреляции
Функция CORREL() — моя любимая. Это дает действительно мощные идеи, которые не очевидны невооруженным глазом. Функция КОРРЕЛ возвращает коэффициент корреляции двух диапазонов ячеек.
Но что это? Это в основном говорит нам, насколько сильна связь между двумя переменными.
Примечание. Не отражает причинно-следственных связей.
- КОРРЕЛ (массив1, массив2)
Диапазон значений корреляции от -1 до 1.
Давайте перейдем к нашему последнему и самому интересному вопросу — есть ли связь между количеством проданных товаров и процентом скидки?
Ну, корреляция получается ~0,8, что довольно много. Кажется, что они положительно связаны — это означает, что чем больше скидка, тем больше количество проданных товаров.
Конечные примечания
В этой статье мы обсудили более 10 статистических функций для начинающих и средних пользователей в MS Excel, от простого count() до расширенного correl(). Статистика — один из самых важных инструментов в наборе аналитика, и вы можете достичь многих своих статистических целей, просто используя Excel.
Я рекомендую вам просмотреть следующие дополнительные ресурсы в Excel:
- 3 Амбициозные диаграммы Excel для расширения вашего портфолио аналитики и визуализации
- 5 полезных приемов Excel, которые помогут стать эффективным аналитиком
В будущем я расскажу о расширенных статистических функциях.
Дайте мне знать некоторые из ваших любимых статистических функций, и я постараюсь включить их в свои следующие статьи.
Excelстатистические функцииstatisticsstatistics excel
Содержание
Джозеф говорит:
Хорошая и интересная статистика, спасибо, что поделились.Лучшие ресурсы
Скачать приложение
Мы используем файлы cookie на веб-сайтах Analytics Vidhya для предоставления наших услуг, анализа веб-трафика и улучшения вашего опыта на сайте. Используя Analytics Vidhya, вы соглашаетесь с нашей Политикой конфиденциальности и Условиями использования. Примите
Политику конфиденциальности и использования файлов cookie
Станьте полноценным специалистом по данным
Будьте впереди в своей карьере AI ML | Предварительные условия не требуютсяЗагрузить брошюру
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Norm) среднее значение генеральной совокупности с использованием нормального распределения (новая возможность в Excel 2010 — заменяет функцию достоверности)
Inc в Excel 2010)
Inc в Excel 2010)
P в Excel 2010)
P в Excel 2010)
Linear в Excel 2016)
(Новое в Excel 2019)
Sngl в Excel 2010)
Inv в Excel 2010) заменяет функцию Betainv)
Функция Dist.Rt в Excel 2010)
Расст в Excel 2010)
ТЕСТ
эксцесс набора данных
INV
PROB