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

Статистическата обработка на данните е събирането, подреждането, обобщаването и анализа на информация, с възможност за определяне на тенденцията и прогнозата за изследваното явление. Excel разполага с огромен брой инструменти, които помагат да се провеждат изследвания в тази област. Последните версии на тази програма по отношение на способностите почти не са по-ниски от специализираните приложения в областта на статистиката. Основните инструменти за извършване на изчисления и анализ са функциите. Нека проучим общите черти на работата с тях, а също така ще разгледаме подробно някои от най-полезните инструменти.

• Статистически функции
  • MAX
  • MIN
  • СРЕДНО
  • AVERAGEIF
  • MODA.ODN
  • MEDIAN
  • СтОткл
  • Най-много
  • МАЛКИ
  • RANG.SR
• Въпроси и отговори

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

Както всяка друга функция в Excel, статистическите функции работят с аргументи, които могат да имат формата на постоянни числа, референтни клетки или масиви.

Експресиите могат да се въвеждат ръчно в определена клетка или във формула, ако знаете синтаксиса на дадена кладенеца. Но е много по-удобно да използвате специален прозорец с аргументи, който съдържа съвети и готови полета за въвеждане на данни. Отидете до прозореца на аргумента на статистическите изрази може да бъде чрез «Function Wizard» или чрез използване на бутоните «Function Libraries» на лентата.

Можете да стартирате съветника за функциите по три начина:

Кликнете върху иконата «Вмъкване на функция» вляво от лентата за формули.

Докато в раздела «Формули» кликнете върху бутона «Вмъкни функция» в лентата с инструменти «Функционална библиотека» .

Когато изпълнявате някоя от горните опции, се отваря прозорецът «Функции на функцията» .

След това кликнете върху полето «Категория» и изберете «Статистически» .

След това ще се отвори списък със статистически изрази. Общо има повече от сто. За да отидете в прозореца с аргументи на който и да е от тях, просто трябва да го изберете и да кликнете върху бутона «OK» .

За да стигнем до елементите, от които се нуждаем, през лентата, преместваме раздела «Формули» . В групата инструменти на лентата «Function Library» кликнете върху бутона «Други функции» . В отворения списък изберете категорията «Статистически» . Ще се отвори списък с наличните елементи от посоката, от която се нуждаем. За да отидете в прозореца с аргументи, просто кликнете върху един от тях.

Операторът MAX е предназначен да определи максималния брой проби. Той има следния синтаксис:

В полетата за аргументи трябва да въведете диапазона от клетки, в които се намира номерът. Най-голямото количество от него, тази формула води до клетката, в която тя сама по себе си.

С името на функцията MIN е ясно, че нейните задачи са директно противоположни на предишната формула — тя търси най-малкото набора от номера и го извежда в дадена клетка. Има следния синтаксис:

СРЕДНО

Функцията AVERAGE търси номер в зададения диапазон, който е най-близо до средната стойност на средната аритметична стойност. Резултатът от това изчисление се показва в отделна клетка, в която се съдържа формулата. Моделът е, както следва:

AVERAGEIF

Функцията AVERAGE има същите задачи като предишната, но е възможно да се определи допълнително условие. Например, повече, по-малко, не е равно на определено число. Тя е посочена в отделно поле за аргумента. Освен това може да се добави диапазон за осредняване като незадължителен аргумент. Синтаксисът е:

MODA.ODN

Формулата MODAODH извежда клетката от номера, която се появява най-често. В по-старите версии на Excel имаше функция MODA, но в по-късните версии тя беше разделена на две: MODAAD (за отделни номера) и MOD.NSC (за масиви). Старата версия обаче също остана в отделна група, в която се събират елементи от предишни версии на програмата, за да се осигури съвместимост на документите.

MEDIAN

Операторът MEDIAN определя средната стойност в диапазона от номера. Това означава, че не определя аритметичната средна стойност, а просто средната стойност между най-големия и най-малкия брой диапазони от стойности. Синтаксисът е:

СтОткл

Формулата STDEV, подобно на MODA, е реликва от старите версии на програмата. Сега се използват модерните си подвидове — STANDOTKLON.V и STANDOTKLON.G. Първата от тях е предназначена за изчисляване на стандартното отклонение на извадката, а втората — за общата популация. Тези функции се използват и за изчисляване на стандартното отклонение. Синтаксисът е:

Най-много

Този оператор показва номера в избраната клетка в низходящ ред на броя на населението. Това означава, че ако имаме набор от 12,97,89,65 и указваме позиционния аргумент от 3, тогава функцията в клетката ще върне третия по големина номер. В този случай това е 65. Синтаксисът на оператора е следният:

В този случай k е номерът на стойността.

МАЛКИ

Тази функция е огледално изображение на предишния оператор. В него и вторият аргумент е серийният номер на номера. Тук само в този случай поръчката се разглежда от по-малките. Синтаксисът е следният:

RANG.SR

Тази функция има противоположен ефект на предишната. В определената клетка тя дава кориндовия номер на конкретно число в извадката чрез условието, посочено в отделен аргумент. Това може да бъде възходящ или низходящ ред. Последното е зададено по подразбиране, ако полето «Поръчка» е оставено празно или е поставено числото 0. Синтаксисът за този израз е както следва:

По-горе бяха описани само най-популярните и заявени статистически функции в Excel. Всъщност те са многократно по-големи. Независимо от това, основният принцип на техните действия е подобен: обработката на множество данни и връщането към посочената клетка на резултата от изчислителните действия.

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

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

• Использование статистических функций
  • СРЗНАЧ
  • МАКС
  • МИН
  • СРЗНАЧЕСЛИ
  • МЕДИАНА
  • НАИБОЛЬШИЙ
  • НАИМЕНЬШИЙ
  • МОДА.ОДН
  • СТАНДОТКЛОН
  • СРГЕОМ
• Заключение

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

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

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

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

Переходим во вкладку “Формулы”, где видим в левом углу ленты инструментов кнопку “Вставить функцию”.

Используем сочетание клавиш Shift+F3.

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

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

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

Примечание: существует еще один способ выбора требуемой функции. Находясь во вкладке “Формулы” в блоке инструментов “Библиотека функций” щелкаем по значку “Другие функции”, затем выбираем пункт “Статистические” и, наконец, в открывшемся перечне (который можно листать вниз) – нужный оператор.

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

СРЗНАЧ

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

=СРЗНАЧ(число1;число2;…)

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

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

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

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

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

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

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

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

СРЗНАЧЕСЛИ

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

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

В аргументах указываются:

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

МЕДИАНА

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

=МЕДИАНА(число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:

1. МАКС;
2. МИН;
3. НАИБОЛЬШИЙ;
4. НАИМЕНЬШИЙ;
5. РАНГ;
6. СРЗНАЧ;
7. СРЗНАЧА;
8. СРЗНАЧЕСЛИ;
9. СРЗНАЧЕСЛИМН;
10. СЧЁТ;
11. СЧЁТЕСЛИ;
12. СЧЁТЕСЛИМН;
13. СЧЁТЗ;
14. СЧИТАТЬПУСТОТЫ.

Функция МАКС

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

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

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

Функция МИН

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

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

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

Функция НАИБОЛЬШИЙ

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

Синтаксис: =НАИБОЛЬШИЙ(массив; n), где

• массив – диапазон ячеек либо массив элементов, содержащий числовые значения. Текстовые и логические значения игнорируются.
• n – натуральное число (кроме нуля), указывающее позицию элемента в порядке убывания. Если задать дробное число, то оно округляется до целого в большую сторону (дробные числа меньше единицы возвращают ошибку). Если аргумент превышает количество элементов множества, то функция возвращает ошибку.

Массив или диапазон НЕ обязательно должен быть отсортирован.

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

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

Функция НАИМЕНЬШИЙ

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

Синтаксис: =НАИМЕНЬШИЙ(массив; n), где

• массив – диапазон ячеек либо массив элементов, содержащий числовые значения. Текстовые и логические значения игнорируются.
• n – натуральное число (кроме нуля), указывающее позицию элемента в порядке возрастания. Если задать дробное число, то оно округляется до целого в меньшую сторону (дробные числа меньше единицы возвращают ошибку). Если аргумент превышает количество элементов множества, то функция возвращает ошибку.

Массив или диапазон НЕ обязательно должен быть отсортирован.

Функция РАНГ

Возвращает позицию элемента в списке по его значению, относительно значений других элементов. Результатом функции будет не индекс (фактическое расположение) элемента, а число, указывающее, какую позицию занимал бы элемент, если список был отсортирован либо по возрастанию либо по убыванию.
По сути, функция РАНГ выполняет обратное действие функциям НАИБОЛЬШИЙ и НАИМЕНЬШИЙ, т.к. первая находит ранг по значению, а последние находят значение по рангу.
Текстовые и логические значения игнорируются.

Синтаксис: =РАНГ(число; ссылка; [порядок]), где

• число – обязательный аргумент. Числовое значение элемента, позицию которого необходимо найти.
• ссылка – обязательный аргумент, являющийся ссылкой на диапазон со списком элементов, содержащих числовые значения.
• порядок – необязательный аргумент. Логическое значение, отвечающее за тип сортировки:
  • ЛОЖЬ – значение по умолчанию. Функция проверяет значения по убыванию.
  • ИСТИНА – функция проверяет значения по возрастанию.

Если в списке отсутствует элемент с указанным значением, то функцией возвращается ошибка #Н/Д.
Если два элемента имеют одинаковое значение, то возвращается ранг первого обнаруженного.
Функция РАНГ присутствует в версиях Excel, начиная с 2010, только для совместимости с более ранними версиями. Вместо нее внедрены новые функции, обладающие тем же синтаксисом:

• РАНГ.РВ – полная идентичность функции РАНГ. Добавленное окончание «.РВ», сообщает о том, что, в случае обнаружения элементов с равными значениями, возвращается высший ранг, т.е. самого первого обнаруженного;
• РАНГ.СР – окончание «.СР», сообщает о том, что, в случае обнаружения элементов с равными значениями, возвращается их средний ранг.

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

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

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

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

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

Результатом выполнения функции из примера будет значение 4, т.к. логические и текстовые значения будут проигнорированы, а (5 + 7 + 0 + 4)/4 = 4.

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

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

Возвращаемое значение в следующем примере 2,833333, так как текстовые и логические значения принимаются за ноль, а логическое ИСТИНА приравнивается к единице. Следовательно, (5 + 7 + 0 + 0 + 4 + 1)/6 = 2,833333.

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

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

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

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

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

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

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

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

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

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

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

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

Функция СЧЁТ

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

Синтаксис: =СЧЁТ(значение1; [значение2]; …), где значение1 – обязательный аргумент, принимающий значение, ссылку на ячейку, диапазон ячеек или массив. Аргументы от значение2 до значение255 являются необязательными и аналогичными значение1.

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

=СЧЁТ(1; 2; «5») – результат функции 3, т.к. строка «5» конвертируется в число.
=СЧЁТ(<1; 2; "5">) – результатом выполнения функции будет значение 2, так как, в отличие от первого примера, число в виде строки записано в массиве, поэтому не будет преобразовано.
=СЧЁТ(1; 2; ИСТИНА) – результат функции 3. Если бы логическое значение находилось бы в массиве, то оно не засчиталось как число.

Функция СЧЁТЕСЛИ

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

Синтаксис: =СЧЁТЕСЛИ(диапазон; критерий), где

• диапазон – обязательный аргумент. Принимает ссылку на диапазон ячеек для проверки на условие.
• критерий – обязательный аргумент. Критерий проверки, содержащий значение либо условия типа больше, меньше, которые необходимо заключать в кавычки. Для текстовых значений можно использовать подстановочные символы (* и ?).

В данном случае необходимо подсчитать количество человек с окладом свыше 4000 рублей.

Функция СЧЁТЕСЛИМН

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

Синтаксис: =СЧЁТЕСЛИМН(диапазон1; критерий1; [диапазон2]; [критерий2]; …).

На рисунке изображено использование функции СЧЁТЕСЛИМН, где подсчитывается количество человек, имеющих оклад свыше 4000 рублей и проживающих в Москве и Московской области. При этом для последнего условия используется подстановочный символ *.

Функция СЧЁТЗ

Подсчитывает непустые ячейки в указанном диапазоне.

Синтаксис: =СЧЁТЗ(значение1; [значение2]; …), где значение1 является обязательным аргумент, все последующие аргументы до значение255 необязательны. В качестве значения может содержаться ссылка на ячейку или диапазон ячеек.

Ячейки, содержащие пустые строки (=»»), засчитываются как НЕпустые.

Функция возвращает значение 4, т.к. ячейка A3 содержит текстовую функцию, возвращающую пустую строку.

Функция СЧИТАТЬПУСТОТЫ

Подсчитывает пустые ячейки в указанном диапазоне.

Синтаксис: =СЧИТАТЬПУСТОТЫ(диапазон), где единственный аргумент является обязательным и принимает ссылку на диапазон ячеек для проверки.

Пустые строки (=»») засчитываются как пустые.

Функция возвращает значение 2, несмотря на то, что ячейка A3 содержит текстовую функцию, возвращающую пустую строку.

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

Функции категории Статистические предназначены в первую очередь для анализа диапазонов ячеек в 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: НОРМОБР

Обзор

Целью данной статьи является описание функция НОРМОБР в Microsoft Office Excel 2003 и последующих версиях Excel, чтобы показать, как используется функция и сравнить результаты функции в Excel 2003 и более поздних версиях Excel с результатами Функция НОРМОБР, если он используется в более ранних версиях Excel.

Дополнительные сведения

Функция НОРМОБР (p, мю, сигма) возвращает значение x, с вероятностью p, обычный случайная величина с виду среднее и стандартное отклонение сигма принимает значение меньше или равно x.

Синтаксис

где p, «среднее» и «стандартное_откл» являются числовыми значениями. Так как p соответствует вероятности (см. Примечание 1), он должен быть больше 0 и меньше 1. Поскольку «стандартное_откл» представляет собой стандартное отклонение, то оно должно быть больше 0.

Примечание 1 Точнее говоря «поскольку p соответствует интегральную для непрерывного случайная величина, которая может принимать любое значение в диапазоне от минус бесконечности и плюс бесконечность. » Нет никаких конкретных отрицательный z для NORMSDIST(z) = 0 и z не определенные положительные для NORMSDIST(z) = 1 (хотя существуют значения z сколь угодно близко к 0 и 1 соответственно), поэтому мы должны настаивать, для NORMINV(p), 0

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

Функция НОРМРАСП и НОРМОБР, связанных функций. Если функция НОРМРАСП (x, мю, сигма) возвращает p, НОРМОБР (p, мю, сигма) возвращает x. Аналогичным образом функция НОРМСТОБР и НОРМСТРАСП, связанных функций; Если NORMSDIST(z) возвращает p, то NORMSINV(p) возвращает по существу z. Excel преобразует НОРМРАСП (x, мю, сигма) mu + sigma*NORMSDIST((x-mu)/sigma). Аналогичным образом функция НОРМОБР (p, мю, сигма) преобразуется в мю + sigma*NORMSINV(p).

Для демонстрации функции НОРМОБР, создайте пустой лист Excel и скопируйте приведенную ниже таблицу. Выделите ячейку A1 листа Excel и вставьте записи таким образом, таблица заполняет A1:D14 ячеек на листе.

Функция НОРМОБР (p, мю, сигма)

=NORMINV(A4, $B$1, $B$2)

=NORMINV(A5, $B$1, $B$2)

=NORMINV(A6, $B$1, $B$2)

=NORMINV(A7, $B$1, $B$2)

=NORMINV(A8, $B$1, $B$2)

=NORMINV(A9, $B$1, $B$2)

=NORMINV(A10, $B$1, $B$2)

=NORMINV(A11, $B$1, $B$2)

=NORMINV(A12, $B$1, $B$2)

=NORMINV(A13, $B$1, $B$2)

=NORMINV(A14, $B$1, $B$2)

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

В Microsoft Office Excel 2007 перейдите на вкладку Главная , в группе ячейки нажмите кнопку Формат и выберите команду Автоподбор ширины.

В Excel 2003 выберите пункт
В меню Формат , а затем щелкните столбец
Автоподбор.

Можно отформатировать столбцы B, C и D для согласованного читаемости (например 5 десятичных знаков).

A4:B14 ячейки отображает значения НОРМОБР (p, мю, сигма) для конкретных значений p. «среднее» и «стандартное_откл» инициализируются 100 и 10 в ячейки B1 и B2. Сравнение значений в столбцах B и D показывает, что функция НОРМОБР (p, мю, сигма) равно мю + sigma*NORMSINV(p). Как упоминалось ранее, если вызывается функция НОРМОБР (p, мю, сигма), Microsoft Excel вычисляет среднее + sigma*NORMSINV(p). Любые неточности в НОРМОБР возникает из-за неточности в НОРМСТОБР.

В следующих трех разделах рассматриваются неточности в НОРМСТОБР и берутся непосредственно из статьи на НОРМСТОБР. Эта статья также имеет другой пример Excel и учебника обсуждение связи между функцией и ее обратную.

Вы можете поэкспериментировать с изменением значения «среднее» и «стандартное_откл» в ячейках B1 и B2. Измените mu до 200 и обратите внимание, что это просто добавляет 100 все НОРМОБР результаты. Измените mu 100 и измените сигма на 20. Обратите внимание, что поскольку сигма удвоилась, значения, возвращаемые НОРМОБР два раза далеко не mu (например, 100), как и раньше. Конечно можно изучить влияние дополнительные изменения в «среднее» и «стандартное_откл» на свои собственные. Увеличение mu увеличивает все значения НОРМСТОБР на ту же сумму; увеличивает сигма, несколько больше 1, оставив без изменений, mu увеличивает расстояние от всех значений НОРМСТОБР из mu же несколько.

Результаты в более ранних версиях Excel

Точности НОРМСТОБР зависит от двух факторов. Так как функция НОРМСТОБР включает систематическую поиска над значениями НОРМСТРАСП, важно точности НОРМСТРАСП.

Кроме того поиск должен быть достаточно уточнение, что он «домов» на соответствующий ответ. Можно использовать таблицу нормального распределения вероятности учебника по аналогии, записи в таблице должно быть точным и поэтому многие, можно найти соответствующую строку таблицы, которая дает вероятность правильного определенное число десятичных знаков. Конечно с программным обеспечением компьютера один не построить и хранения такой monstrous таблицы; Вместо этого отдельные операции вычисляются по запросу как поиск через выручка «таблица». Но таблица должна быть точным в первую очередь, а поиск продолжить достаточно далеко, что он не останавливается преждевременно ответ, которого соответствующая вероятность (строка таблицы, если вам нравится) находится слишком далеко от пользователя p в вызове NORMSINV(p). Таким образом усовершенствования в НОРМСТОБР состоят из следующие усовершенствования:

Улучшения в точности НОРМСТРАСП

Усовершенствования в процессе поиска для повышения чистоты звука

Функция НОРМСТРАСП была улучшена, но только для Excel 2003 и последующих версиях Excel, но не для более ранних версиях Excel. Улучшенный усовершенствования в процессе поиска впервые появились в Microsoft Excel 2002, но не до этого. Статьи по Knusel (см. Примечание 2) Указывает числовое недостаток в НОРМСТОБР в Microsoft Excel 97. Указанные недостатки сохраняются, как описано, Knusel, ожидая улучшения в процессе поиска в программе Microsoft Excel 2002 результаты лучше. Однако результаты еще не были полностью согласен с его Knusel.

Примечание 2 Knusel, L. на точность статистического распределения в Microsoft Excel 97, вычислительные статистики и анализа данных, 26, 375-377, 1998.

Результаты в Excel 2003 и последующих версиях Excel

Процедура в Excel 2003 и последующих версиях Excel НОРМСТОБР использует преимущества улучшений в НОРМСТРАСП в Excel 2003 и последующих версиях Excel.

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

Статистические функции excel 827369 : функция НОРМСТРАСП

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

Выводы

Для очень больших или очень малые значения p в NORMSINV(p) обычно возникают неточности в более ранних версиях Excel. Более точные значения в Excel 2003 и последующих версиях Excel.

Статья о функции НОРМСТРАСП указывает, что большинство пользователей не должны повлиять на неточности в НОРМСТРАСП в более ранних версиях Excel. Из этого следует, что пользователи Excel 2002 не осуществляемых за НОРМСТОБР поскольку детали процесса поиска были добавлены в программе Microsoft Excel 2002.

Для пользователей Excel 2002 и более ранних версиях Excel нет больше беспокойство неточность НОРМСТОБР, так как функция НОРМСТРАСП и процесс поиска необходимые улучшения в этих версиях.