Конспект урока по информатике на тему «статистические функции»

Функция МАКС

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

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

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

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

Синтаксис и особенности функции

Сначала рассмотрим аргументы функции:

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

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

В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.

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

  • Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
  • Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
  • Функция не учитывает регистр текстовых значений.
  • При формулировании условия подсчета можно использовать подстановочные знаки. «?» — любой символ. «*» — любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (~).
  • Для нормального функционирования формулы в ячейках с текстовыми значениями не должно пробелов или непечатаемых знаков.

10 популярных статистических функций в Microsoft Excel

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

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

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

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

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

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

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

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

Функция СЧЕТЕСЛИ в Excel: примеры

Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.

У нас есть такая таблица:

Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:

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

Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.

Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:

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

Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).

Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:

Формула посчитала «кровати» и «банкетки».

Используем в функции СЧЕТЕСЛИ условие поиска «не равно».

Формула: =СЧЁТЕСЛИ(A1:A11;»»&»стулья»). Оператор «» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».

При применении ссылки формула будет выглядеть так:

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

  1. Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».
  2. Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция ищет в ячейке А1. Текст «стулья» — на базе критерия в ячейке А2.
  3. Посчитаем число ячеек в диапазоне В1:В11 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).
  4. Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это возможно, если диапазоны являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения по двум критериям сразу в двух столбцах. Если диапазоны несмежные, то применяется функция СЧЕТЕСЛИМН.
  5. Когда в качестве критерия указывается ссылка на диапазон ячеек с условиями, функция возвращает массив. Для ввода формулы нужно выделить такое количество ячеек, как в диапазоне с критериями. После введения аргументов нажать одновременно сочетание клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.

СЧЕТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому продвинутому пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.

Примеры использования функции СРЗНАЧ в Excel

Функция СРЗНАЧ относится к группе «Статистические». Поэтому для вызова данной функции в Excel необходимо выбрать инструмент: «Формулы»-«Библиотека функций»-«Другие функции»-«Статистические»-«СРЗНАЧ». Или вызвать диалоговое окно «Вставка функции» (SHIFT+F3) и выбрать из выпадающего списка «Категория:» опцию «Статистические». После чего в поле «Выберите функцию:» будет доступен список категории со статистическими функциями где и находится СРЗНАЧ, а также СРЗНАЧА.

Если есть диапазон ячеек B2:B8 с числами, то формула =СРЗНАЧ(B2:B8) будет возвращать среднее значение заданных чисел в данном диапазоне:

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

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

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

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

Результат выполнения функции в примере на картинке ниже – это число 4, т.к. логические и текстовые объекты игнорируются. Поэтому:

(5 + 7 + 0 + 4) / 4 = 4

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

Результаты еще 4-х задач сведены в таблицу ниже:

Как видно на примере, в ячейке A9 функция СРЗНАЧ имеет 2 аргумента: 1 — диапазон ячеек, 2 – дополнительное число 5. Так же могут быть указаны в аргументах и дополнительные диапазоны ячеек с числами. Например, как в ячейке A11.

Число

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

  • числовой;
  • денежный;
  • финансовый;
  • процентный;
  • дробный;
  • экспоненциальный.

Формат ячейки можно задать двумя способами:

  1. Во вкладке “Главная” в группе инструментов “Число” нажимаем по стрелке рядом с текущим значением и в раскрывшемся списке выбираем нужный вариант.
  2. В окне форматирования (вкладка “Число”), в которое можно попасть через контекстное меню ячейки.

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

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

Также можно поступить наоборот – сначала ввести значение в нужной ячейке, а формат поменять после.

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются

Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2

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

В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку

Прогнозирование производства продукции на графике Excel

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

Исходная таблица:

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

=ТЕНДЕНЦИЯ(B3:B7;A3:A7;A8:A10)

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

Введем в ячейке C8 формулу =193,5*A8+2060,5. В результате получим:

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

Данный пример наглядно демонстрирует принцип работы функции ТЕНДЕНЦИЯ.

Определение среднего значения по условию

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

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

  1. Встаем в результирующую ячейку и жмем кнопку “Вставить функцию” (fx) слева от строки формул.
  2. В Мастере функций выбираем категорию “Статистические”, кликаем по оператору “СРЗНАЧЕСЛИ” и жмем ОК.
  3. Откроются аргументы функции, после заполнения которых кликаем OK:
    • в значении аргумента “Диапазон” указываем (вручную или выделив с помощью левой кнопки мыши в самой таблице) требуемую область ячеек;
    • в значении аргумента “Условие”, соответственно, задаем наше условие попадания ячеек из отмеченного диапазона в общий расчет. В нашем случае, это выражение “>0”. Вместо конкретного числа, в случае необходимости, в условии можно указать адрес ячейки, содержащей числовое значение.
    • поле аргумента “Диапазон_усреднения” можно оставить пустим, так как его обязательное заполнение требуется только при работе с текстовыми данными.
  4. Среднее значение с учетом заданного нами условия отбора ячеек отобразилось в выдранной ячейке.

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

​ в указанном диапазоне,​Запустить Мастер функций можно​ХИ2ОБР​Оценивает стандартное отклонение по​

СРЗНАЧ

​60347​​ списка аргументов, включая​​ совокупности.​Подкатегория​Функция​ мы нашли пятое​Данная функция может принимать​ t-распределения Стьюдента как​

СРЗНАЧЕСЛИ

​ 1, исключая границы).​Возвращает среднее гармоническое.​КОВАРИАЦИЯ.Г​​ версии 2013 означает,​​ ячейку вернет третье​ которое ближе всего​ тремя способами:​CHIINV​ выборке, включая числа,​​-​​ числа, текст и​ДИСПРА​

​Описание​​СРЗНАЧ​​ по величине значение​​ до 255 аргументов​ функцию вероятности и​​ПРОЦЕНТРАНГ.ВКЛ​​ГИПЕРГЕОМ.РАСП​Возвращает ковариацию, среднее произведений​​ что данная функция​​ по величине число.​

МЕДИАНА

​ находится к среднему​​Кликнуть по пиктограмме​​60323​ текст и логические​Находит количество перестановок для​

​ логические значения.​

​VARPA​​FРАСП​​(AVERAGE) используется для​ из списка.​ и находить среднее​

Стандартное отклонение

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

МИН

​ доступна в выпуске​​ В данном случае,​​ арифметическому значению. Результат​«Вставить функцию»​Обратного распределения​

​ значения.​​ заданного числа объектов.​​МЕДИАНА​-​FDIST​

НАИБОЛЬШИЙ

​ вычисления среднего арифметического​Чтобы убедиться в этом,​​ сразу в нескольких​​СТЬЮДЕНТ.ОБР.2Х​ в наборе данных.​ОТРЕЗОК​

​КОВАРИАЦИЯ.В​

НАИМЕНЬШИЙ

​ Excel 2013 и всех​ это 65. Синтаксис​ этого расчета выводится​​слева от строки​​Вычисляет обратное значение односторонней​

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

​ПЕРСЕНТИЛЬ​MEDIAN​​Вычисления дисперсии и отклонения​​Вычисления распределения​

​ значения. Аргументы могут​

office-guru.ru>

Формулы с примерами использования функции СРЗНАЧА

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

Результат выполнения функции возвращает число в примере 2,833333, так как текстовые и логические значения приняты за нуль, а логическое ИСТИНА приравнено к единице. Следовательно:

(5 + 7 + 0 + 0 + 4 + 1) / 6 = 2,83

Синтаксис:

=СРЗНАЧА(значение1;;…)

Аргументы функции СРЗНАЧА подчинены следующим свойствам:

  1. «Значение1» является обязательным, а «значение2» и все значения, которые следуют за ним необязательными. Общее количество диапазонов ячеек или их значений может быть от 1 до 255 ячеек.
  2. Аргумент может быть числом, именем, массивом или ссылкой, содержащей число, а также текстовым представлением числа или логическим значением, например, «истина» или «ложь».
  3. Логическое значение и текстовое представление числа, введенного в список аргументов, учитывается.
  4. Аргумент, содержащий значение «истина», интерпретируется как 1. Аргумент, содержащий значение «ложь», интерпретируется как 0 (ноль).
  5. Текст, содержащийся в массивах и ссылках, интерпретируется как 0 (ноль). Пустой текст («») интерпретирован тоже как 0 (ноль).
  6. Если аргумент массив или ссылка, то используются только значения, входящие в этот массив или ссылку. Пустые ячейки и текст в массиве и ссылке — игнорируется.
  7. Аргументы, которые являются значениями ошибки или текстом, не преобразуемым в числа, вызывают ошибки.

Результаты особенности функции СРЗНАЧА сведены в таблицу ниже:

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

Для установки флажка на вкладке «Файл» надо выбрать команду «Параметры», перейти в категорию «Дополнительно», где найди раздел «Показать параметры для следующего листа» и там установить флаг.

Формулы

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

Формула будет отображаться в соответствующе строке формул, а результат по ней – в содержащей ее ячейке.

Чтобы Excel “видел” выражение как формулу, обязательно нужно в самом начале поставить знак “равно”.

В формулах используются как конкретные числа, так и ссылки – относительные и абсолютные.

Подробнее об этом читайте в нашей статье – “Ссылки в Excel: абсолютные, относительные, смешанные”.

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

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

Чтобы попасть в окно Мастера функций, сначала выбираем нужную ячейку, затем щелкаем по кнопке “Вставка функции” рядом со строкой формул. Затем находим нужный оператор и жмем кнопку OK.

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

Синтаксис функции ПРЕДСКАЗ

Аргументы:

  1. Значение х. Заданный числовой аргумент, для которого необходимо предсказать значение y.
  2. Диапазон значений y. Известные числа, на основании которых будет проводиться вычисление.
  3. Диапазон значений x. Известные числа, на основании которых будет проводиться вычисление.

Когда Excel выдает ошибку:

  1. Заданный аргумент x не является числом (появляется ошибка #ЗНАЧ!).
  2. Массивы данных х и у пусты (ошибка #Н/Д).
  3. Количество точек х не совпадает с количеством точек у (ошибка #Н/Д).
  4. Дисперсия аргумента «диапазон значений x» равняется 0 (ошибка #ДЕЛ/О!).

Уравнение для функции – a + bx, где a = , b = .

Где x и y – средние значения данных в соответствующих диапазонах (точек x и точек y).

Анализ прогноза спроса продукции в Excel по функции ПРЕДСКАЗ

Пример 2. Компания недавно представила новый продукт. С момента вывода на рынок ежедневно ведется учет количества клиентов, купивших этот продукт. Предположить, каким будет спрос на протяжении 5 последующих дней.

Вид исходной таблицы данных:

Пример 2.» src=»https://exceltable.com/funkcii-excel/images/funkcii-excel145-6.png» class=»screen»>

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

Рассчитаем значения логарифмического тренда с помощью функции ПРЕДСКАЗ следующим способом:

Как видно, в качестве первого аргумента представлен массив натуральных логарифмов последующих номеров дней. Таким образом получаем функцию логарифмического тренда, которая записывается как y=aln(x)+b.

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

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

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

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

​ значений диапазона, где​​ГАММАНЛОГ.ТОЧН​​Подсчитывает количество значений в​ оригинал (на английском​=СТАНДОТКЛОН.Г(число1;число2;…)​ сама.​ специальным окном аргументов,​Вычисления ковариации и корреляции​ отклонений точек данных​60340​ распределение.​ДИСПА​

​ для проведения статистического​ сможете применить на​Статистическая функция​ затрагивать такие популярные​ каждого значения x​ k — число​Возвращает натуральный логарифм гамма-функции,​ списке аргументов.​ языке) .​Урок:​По названию функции МИН​ которое содержит подсказки​Находит преобразование Фишера.​

​ от среднего.​Вычисления распределения​МАКС​VARA​ анализа числовых данных.​​ практике. Надеюсь, что​​МАКС​​ статистические функции Excel,​​ в регрессии.​ от 0 и​

СРЗНАЧ()

​ Γ(x).​​СЧИТАТЬПУСТОТЫ​​Чтобы просмотреть более подробные​Формула среднего квадратичного отклонения​

​ понятно, что её​ и уже готовые​ФИШЕРОБР​СТАНДОТКЛОН​Находит отрицательное биномиальное распределение.​MAX​

​-​ Однако эта категория​ данный урок был​возвращает наибольшее значение​ как​СТЬЮДРАСП​ 1 (не включая​​ГАУСС​

​Подсчитывает количество пустых ячеек​ сведения о функции,​ в Excel​ задачи прямо противоположны​​ поля для ввода​​FISHERINV​STDEV​ОТРЕЗОК​60055​​Вычисления дисперсии и отклонения​​ содержит также функции,​

​ для Вас полезен.​​ в диапазоне ячеек:​​СЧЕТ​Возвращает процентные точки (вероятность)​ эти числа).​Возвращает значение на 0,5​

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

​ в диапазоне.​ щелкните ее название​Данный оператор показывает в​ предыдущей формуле –​ данных. Перейти в​​60332​​60060​INTERCEPT​Определения экстремумов​

​Оценивает дисперсию по выборке,​ которые можно отнести​ Удачи Вам и​Статистическая функция​и​ для t-распределения Стьюдента.​ПРОЦЕНТИЛЬ.ВКЛ​ меньше стандартного нормального​​СЧЁТЕСЛИ​​ в первом столбце.​ выбранной ячейке указанное​ она ищет из​ окно аргумента статистических​Вычисления ковариации и корреляции​Вычисления дисперсии и отклонения​60359​

​Определяет максимальное значение из​ включая числа, текст​ к категории «Математические»​ успехов в изучении​МИН​СЧЕТЕСЛИ​СТЬЮДЕНТ.РАСП.2Х​Возвращает k-ю процентиль для​ распределения.​Подсчитывает количество ячеек в​

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

​ и «Информационные».​​ Excel.​​возвращает наименьшее значение​, для них подготовлен​

МИН()

​Возвращает процентные точки (вероятность)​​ значений диапазона.​​СРГЕОМ​ диапазоне, удовлетворяющих заданному​

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

​ Маркер версии обозначает версию​ число из совокупности.​ и выводит его​«Мастер функций»​ФТЕСТ​ выборке.​Находит отрезок, отсекаемый на​

​МАКСА​ДИСПР​Список математических функций:​

​ условию.​ Excel, в которой​ То есть, если​ в заданную ячейку.​

МЕДИАНА()

​или с помощью​​FTEST​​СТАНДОТКЛОНА​ оси линией линейной​MAXA​VARP​60329​В этом разделе даётся​Возвращает n-ое по величине​Статистическая функция​СТЬЮДЕНТ.РАСП.ПХ​Возвращает ранг значения в​РОСТ​СЧЁТЕСЛИМН​ она впервые появилась.​ мы имеем совокупность​

​ Имеет такой синтаксис:​ кнопок​60358​STDEVA​

​ регрессии.​-​60242​Функция​

МОДА()

​ обзор некоторых наиболее​ значение из массива​СРЗНАЧ​

​Возвращает t-распределение Стьюдента.​ наборе данных как​Возвращает значения в соответствии​Подсчитывает количество ячеек внутри​

​ В более ранних​​ 12,97,89,65, а аргументом​​=МИН(число1;число2;…)​«Библиотеки функций»​Проверки статистических критериев​-​ПЕРЕСТ​​Определения экстремумов​​Вычисления дисперсии и отклонения​​Function​​ полезных статистических функций​ числовых данных. Например,​

​возвращает среднее арифметическое​​СТЬЮДЕНТ.ОБР​​ процентную долю набора​ с экспоненциальным трендом.​ диапазона, удовлетворяющих нескольким​ версиях эта функция​ позиции укажем 3,​Функция СРЗНАЧ ищет число​на ленте.​Определяет результат F-теста.​Вычисления дисперсии и отклонения​PERMUT​Определяет максимальное значение из​Вычисляет дисперсию для генеральной​id​ Excel.​ на рисунке ниже​ своих аргументов.​Возвращает значение t для​ (от 0 до​СРГАРМ​ условиям.​ отсутствует. Например, маркер​

​ то функция в​

office-guru.ru>

7 основных статистических функций в Excel

Доброго времени суток уважаемый читатель!

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

Итак, какие же статистические функции в Excel, нам помогут со статистическими данными:

  1. Функция МИН (MIN) – позволит определить в диапазоне значений самое минимальное;
  2. Функция МАКС (MAX) – эта функция найдет максимальное значение в наборе чисел;
  3. Функция СРЗНАЧ (AVERAGE) – используется для нахождения арифметического среднего значения в диапазоне чисел;
  4. Функция СРЗНАЧЕСЛИ (AVERAGEIF) – это весь функционал предыдущей функции, плюс возможность указать критерий отбора: = СРЗНАЧЕСЛИ(А1:Н1;«»), где знак «» — не равно.
  5. Функция МОДА (MODE) – эта функция указывает на самое часто встречающееся число в указываемом диапазоне чисел: =МОДА(А1:Н1);
  6. Функция МЕДИАНА (MEDIAN) – определяет середину (медиану) в диапазоне чисел.
  7. Функция СТАНДОТКЛОН (STDEV) – позволит вам найти и вычислить стандартное отклонение.

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

Составлять сбалансированный бюджет — все равно что защищать свою добродетель: нужно научиться говорить «нет». Рональд Рейган

Функция ЕСЛИ в Excel

Функция имеет следующий синтаксис.

ЕСЛИ(лог_выражение; значение_если_истина; )

лог_выражение – это проверяемое условие. Например, A2

значение_если_истина – значение или формула, которое возвращается при наступлении указанного в первом параметре события.

значение_если_ложь – это альтернативное значение или формула, которая возвращается при невыполнении условия. Данное поле не обязательно заполнять. В этом случае при наступлении альтернативного события функция вернет значение ЛОЖЬ.

Очень простой пример. Нужно проверить, превышают ли продажи отдельных товаров 30 шт. или нет. Если превышают, то формула должна вернуть «Ок», в противном случае – «Удалить». Ниже показан расчет с результатом.

Продажи первого товара равны 75, т.е. условие о том, что оно больше 30, выполняется. Следовательно, функция возвращает то, что указано в следующем поле – «Ок». Продажи второго товара менее 30, поэтому условие (>30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

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

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

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

Функция ТЕНДЕНЦИЯ в Excel и особенности ее использования

Функция ТЕНДЕНЦИЯ используется наряду с прочими функциями прогноза в Excel (ПРЕДСКАЗ, РОСТ) и имеет следующий синтаксис:

= ТЕНДЕНЦИЯ(известные_значения_y; ; ; )

Описание аргументов:

  • известные_значения_y – обязательный аргумент, характеризующий диапазон исследуемых известных значений зависимой переменной y из уравнения y=ax+b.
  • – необязательный для заполнения аргумент, характеризующий диапазон известных значений независимой переменной x из уравнения y=ax+b.
  • – необязательный аргумент, характеризующий одно значение или диапазон данных, для которых необходимо определить соответствующие значения зависимой переменной y.
  • – необязательный аргумент, принимающий на вход логические значения:
  1. ИСТИНА (значение по умолчанию, если явно не указано обратное) – функция ТЕНДЕНЦИЯ выполняет расчет коэффициента b из уравнения y=ax+b обычным методом.
  2. ЛОЖЬ – функция ТЕНДЕНЦИЯ использует упрощенный вариант уравнения – y=ax (коэффициент b = 0).

Примечания:

  1. Рассматриваемая функция интерпретирует каждый столбец или каждую строку из диапазона известных значений x в качестве отдельной переменной, если аргументом известное_y является диапазон ячеек из только одного столбца или только одной строки соответственно.
  2. Аргументы и должны содержать одинаковое количество строк либо столбцов соответственно. Если новые значения независимой переменной явно не указаны, функция ТЕНДЕНЦИЯ выполняет расчет с условием, что аргументы и принимают одинаковые значения. Если оба эти аргумента явно не указаны, рассматриваемая функция использует массивы {1;2;3;…;n} с размерностью, соответствующей размерности известное_y.
  3. Данная функция может быть использована для аппроксимации полиномиальных кривых.
  4. ТЕНДЕНЦИЯ является формулой массива. Для определения нескольких последующих значений необходимо выделить диапазон соответствующего количества ячеек и для отображения результата использовать комбинацию клавиш Ctrl+Shift+Enter.
  5. В качестве аргумента могут быть переданы:
  • Только одна переменная, при этом два первых аргумента функции ТЕНДЕНЦИЯ могут являться диапазонами любой формы, но обязательным условием является одинаковая размерность (количество элементов).
  • Несколько переменных, при этом в качестве аргумента известное_y должен быть передан вектор значений (диапазон из только одной строки или только одного столбца).

Анализ формул в Excel

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

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

Теперь проследим все этапы вычисления и содержимое суммирующей формулы:

Перейдите в ячейку B5, в которой содержится формула.
Выберите инструмент: «Формулы»-«Зависимости формул»-«Вычислить формулу». Появиться диалоговое окно «Вычисление».

В данном окне периодически нажимайте на кнопку «Вычислить», наблюдая за течением расчета в области окна «Вычисление:»

Для анализа следующего инструмента воспользуемся простейшим кредитным калькулятором Excel в качестве примера:

Чтобы узнать, как мы получили результат вычисления ежемесячного платежа, перейдите на ячейку B4 и выберите инструмент: «Формулы»-«Зависимости формул»-«Влияющие ячейки».

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

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

Примечание. Чтобы очистить схему нужно выбрать инструмент «Убрать стрелки».

Полезный совет. Если нажать комбинацию клавиш CTRL+«`» (апостроф над клавишей Tab) или выберите инструмент : «Показать формулы». Тогда мы увидим, что для вычисления ежемесячного платежа мы используем 3 формулы в данном калькуляторе. Они находиться в ячейках: B4, C2, D2.

Такой подход тоже существенно помогает проследить цепочку вычислений. Снова перейдите в обычный режим работы, повторно нажав CTRL+«`».

Ссылка на основную публикацию