Как в кабинете

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

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

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

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

СРЗНАЧ()

Статистическая функция СРЗНАЧ возвращает среднее арифметическое собственных аргументов.

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

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

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

Статистическая функция СРЗНАЧ может употреблять в качестве собственных аргументов математические операторы и разные функции Excel:

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

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

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

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

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

Статистическая функция МАКС возвращает наибольшее значение в спектре ячеек:

Статистическая функция МИН возвращает меньшее значение в спектре ячеек:

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

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

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

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

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

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

МЕДИАНА()

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

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

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

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

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

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

Интегрированные функции. Статистический анализ. Работа с математическими и статистическими функциями

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

Программка EXCEL содержит наиболее 400 интегрированных функций, которые можно избрать при помощи Мастера функций.

Формулы, содержащие функции, можно вводить конкретно в ячейку, в строчку формул либо создавать при помощи Мастера функций

Для вызова Мастера функций нужно избрать команду Вставить функцию f_x из меню Формулы либо надавить на панели инструментов формула клавишу

Опосля её нажатия покажется окно Мастера функций (рис. 3.1 рис. 3.1).

В открывшемся диалоговом окне изберите категорию и имя функции, а потом в полях с надлежащими подсказками введите аргументы (рис. 3.2 рис. 3.2). Опосля нажатия клавиши ОК, готовая функция покажется в строке формул

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

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

1. числовые константы, к примеру, функция ПРОИЗВЕД(2;3) вычисляет произведение чисел 2 и 3, т.е. 2•3.
2. ссылки на ячейки и блоки ячеек (функция ПРОИЗВЕД (А1;С1:СЗ) вычисляет произведение содержимого ячеек А1,С1,С2 и С3, т.е. А1•С1•С2•СЗ.
3. текстовые константы (заключенные в кавычки).
4. логические значения.
5. массивы.
6. имена ссылок, к примеру, если ячейке А10 присвоить имя СУММА –последовательность установок Формулы Присвоить имя. – рис. 3.3 рис. 3.3), а блоку ячеек В10:Е10 – имя ИТОГИ, то допустима последующая запись: =СУММ(СУММА;ИТОГИ).
7. смешанные аргументы, к примеру, =СРЗНАЧ (Группа;АЗ;5*3)

Пример 1. Вычислить значения функции

Y=e x *sin(x) для

1. Заполним столбец А значениями аргумента функции. Чтоб не вводить их вручную, применим последующий прием. Введите в ячейку А1 изначальное значения аргумента (-1). Во вкладке Основная> Редактирование изберите клавишу Заполнить, потом Прогрессия и в открывшемся диалоговом окне укажите предельное значение (1), шаг(0,2) и направление По столбцам (рис. 3.4 рис. 3.4). Опосля нажатия клавиши ОК в столбце А будут введены все значения аргумента

1. В ячейку В1 введите формулу =exp(А1)*sin(A1). Размножьте эту формулу на другие ячейки столбца B , ухватив левой мышью маркер наполнения (темный квадратик в правом нижнем углу рамки выделенной ячейки B1 ) и протащив маркер до конца конфигурации аргумента. В итоге будут вычислены надлежащие значения функции.

Логические функции

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

Так, функция ЕСЛИ делает проверку условия, задаваемого первым аргументом логич_выр:

=ЕСЛИ(логич_выр; знач_да; знач_нет) и возвращает знач_да, если условие выполнено (ИСТИНА), и знач_нет, в неприятном случае (ЛОЖЬ).

Если значение в ячейке А6<10, то функция возвратит итог 5, а по другому – 10.

Если значение в ячейке B4>80, то в ячейке с приведенной формулой будет записано «Сданы», по другому – «Не сданы».

Если сумма значений в столбце А1:А10 больше 0, то вычислится сумма значений в столбце В1:В10, в неприятном случае итог – 0.

Доп логические функции

разрешают создавать сложные условия, к примеру:

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

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

MS EXCEL предоставляет широкие способности для анализа статистических данных. Для решения обычных задач можно употреблять интегрированные функции. Разглядим некие из их.

1. Вычисление среднего арифметического последовательности чисел:

=СРЗНАЧ (числа).

1. Нахождение наибольшего (малого) значения:

1. Вычисление медианы (числа, являющегося серединой огромного количества):

=МЕДИАНА(числа).

1. Вычисление моды (более нередко встречающегося значения в огромном количестве):

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

1. Дисперсия:

1. Обычное отклонение:

=СТАНДОТКЛОН( числа).

Статистический анализ при помощи Пакета анализа

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

Чтоб пользоваться инструментами анализа, сделайте последующие деяния.

1. В меню Данные > Анализ изберите команду Анализ данных.

1. Изберите из перечня заглавие подходящего инструмента анализа и нажмите клавишу ОК.

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

Инструмент Описательная статистика сформировывает таблицу статистических данных, ускоряя и упрощая этот процесс по сопоставлению с внедрением формул 1- 6 (рис. 3.6 рис. 3.6).

Инструмент Генерация случайных чисел дает возможность получать равномерное и неравномерное распределение.

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

Пример 2. Пусть дана таблица с данными о температуре воздуха в Краснодаре в летнюю пору 2014г. Интервал конфигурации температуры от 18 до 38 градуса по Цельсию (его можно найти при помощи функций МАКС() и МИН()).

1. Разобьем этот интервал на подинтервалы – кармашки шириной, к примеру, 2 градуса по Цельсию (ширина кармашков не непременно обязана быть равной).
2. Воспользуемся командой Заполнить из меню Основная в группе Редактирование для резвого наполнения столбца кармашков (значения в столбце будут поменяются от 18 до 38 градусов по Цельсию с шагом 2 градуса).
3. Выполним команду Анализ данных из меню Данные. В открывшемся диалоговом окне зададим входной интервал (это ячейки с данными о температуре), интервал кармашков, выходной интервал (нужно указать лишь верхнюю, левую ячейку для вывода результатов) и установим флаг Вывод графика.
4. Опосля нажатия клавиши ОК на экран будет выведена гистограмма, а рядом со столбцом кармашков покажется столбец частот, показывающий, сколько дней в летнюю пору в Краснодаре имели температуру, попадающую в любой интервал.

ЗАДАНИЕ

Любой вариант состоит из 2-ух заданий. Для выполнения первого задания нужно:

1. На рабочем листе № 4 выстроить таблицу значений функции согласно варианта задания и ее график.
2. Обусловьте среднее, малое и наибольшее значение функции и вывести эти данные на графике.
3. Используя логическую формулу, вычислить сумму значений функций, если среднее, малое и наибольшее значения имеют схожие знаки и произведение в неприятном случае.
4. Случайной ячейке присвоить имя и сгенерировать в ней случайное число. В таблице значений функции добавить очередной столбец, приобретенный умножением у на случайное число. Добавить на графике функции 2-ой график, соответственный приобретенному столбцу данных.

Начальными данными для второго задания являются варианты заданий к лабораторной работе № 1. Нужно:

Гид по статистическому пакету Excel

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

Если вы ищете работу и она хоть как-то связана с арифметикой/экономикой/деньгами, то вы весьма нередко будете встречать такие требования к кандидату:
— Хорошее познание статистики;
— Познание и умение Python/R, чтоб эту статистику использовать.

Но что созодать, если никаких познаний по языкам программирования у вас нет, а встречаться со статистическими моделями так либо по другому придется? А работу-то отыскать необходимо срочно…

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

Для начала побеседуем, где все эти формулы отыскать. Как обычно, перебегаем на вкладку «Формулы» на главной панели, избираем «Остальные функции» и пакет «Статистические». Перед Вами полный список статистических способностей Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

Замечание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

К примеру, необходимо отыскать 5-ое по величине число. Вводим:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

Создатель: Андрон Алексанян, СОО «Аптека-Центр», эксперт SF Education

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