Срзначеслимн в excel примеры

Простой расчет

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

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

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

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

Синтаксис

Аргументы функции СРЗНАЧЕСЛИМН указаны ниже.

Диапазон_усреднения: обязательный. Одна или несколько ячеек для вычисления среднего с числами или именами, массивами или ссылками, содержащими числа.

Диапазон_условий1, диапазон_условий2, … параметр «диапазон_условий1» — обязательный, остальные диапазоны условий — нет. От 1 до 127 интервалов, в которых проверяется соответствующее условие.

Условие1, условие2, … Параметр «условие1» является обязательным, остальные условия — нет. От 1 до 127 условий в форме числа, выражения, ссылки на ячейку или текста, определяющих ячейки, для которых будет вычисляться среднее. Например, условие может быть выражено следующим образом: 32, «32», «>32», «яблоки» или B4.

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

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

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

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

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

Свойства средней арифметической (математического ожидания)

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

Матожидание в русскоязычной литературе обычно обозначают как M(X), в иностранных учебниках можно увидеть E(X). Встречается обозначение греческой буквой μ (читается «мю»). Для удобства предлагаю вариант M(X).

Итак, свойство 1. Если имеются переменные X, Y, Z, то математическое ожидание их суммы равно сумме их математических ожиданий.

M(X+Y+Z) = M(X) + M(Y) + M(Z)

Допустим, среднее время, затрачиваемое на мойку автомобиля M(X) равно 20 минут, а на подкачку колес M(Y) – 5 минут. Тогда общее среднее арифметическое время на мойку и подкачку составит M(X+Y) = M(X) + M(Y) = 20 + 5 = 25 минут.

Свойство 2. Если переменную (т.е. каждое значение переменной) умножить на постоянную величину (a), то математическое ожидание такой величины равно произведению матожидания переменной и этой константы.

M(aX) = aM(X)

К примеру, среднее время мойки одной машины M(X) 20 минут. Тогда среднее время мойки двух машин составит M(aX) = aM(X) = 2*20 = 40 минут.

Свойство 3. Математическое ожидание постоянной величины (а) есть сама эта величина (а).

M(a) = a

Если установленная стоимость мойки легкового автомобиля равна 100 рублей, то средняя стоимость мойки нескольких автомобилей также равна 100 рублей.

Свойство 4. Математическое ожидание произведения независимых случайных величин равно произведению их математических ожиданий.

M(XY) = M(X)M(Y)

Автомойка за день в среднем обслуживает 50 автомобилей (X). Средний чек – 100 рублей (Y). Тогда средняя выручка автомойки в день M(XY) равна произведению среднего количества M(X) на средний тариф M(Y), т.е. 50*100 = 500 рублей.

Использование функции СРЗНАЧ

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

1. Встаем в ячейку, куда планируем выводить результат. Кликаем по значку “Вставить функци” (fx) слева от строки формул.
2. В открывшемся окне Мастера функций выбираем категорию “Статистические”, в предлагаемом перечне кликаем по строке “СРЗНАЧ”, после чего нажимаем OK.
3.  На экране отобразится окно с аргументами функции (их максимальное количество – 255). Указываем в качестве значения аргумента “Число1” координаты нужного диапазона. Сделать это можно вручную, напечатав с клавиатуры адреса ячеек. Либо можно сначала кликнуть внутри поля для ввода информации и затем с помощью зажатой левой кнопки мыши выделить требуемый диапазон в таблице. При необходимости (если нужно отметить ячейки и диапазоны ячеек в другом месте таблицы) переходим к заполнению аргумента “Число2” и т.д. По готовности щелкаем OK.
4. Получаем результат в выбранной ячейке.
5. Среднее значение не всегда может быть “красивым” за счет большого количества знаков после запятой. Если нам такая детализация не нужна, ее всегда можно настроить. Для этого правой кнопкой мыши щелкаем по результирующей ячейке. В открывшемся контекстном меню выбираем пункт “Формат ячеек”.
6. Находясь во вкладке “Число” выбираем формат “Числовой” и с правой стороны окна указываем количество десятичных знаков после запятой. В большинстве случаев, двух цифр более, чем достаточно. Также при работе с большими числами можно поставить галочку “Разделитель групп разрядов”. После внесение изменений жмем кнопку OK.
7. Все готово. Теперь результат выглядит намного привлекательнее.

Использование формул

Определить день недели также можно с помощью функции ТЕКСТ:

• Значение(обязательный аргумент) — числовое значение;
• Формат(обязательный аргумент) — численный формат в виде текста в кавычках (например, «ДДД» или «ДДДД»).

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

Определение дня недели с помощью функции ТЕКСТ

• Дата в числовом формате(обязательный аргумент) — числовое значение;
• Тип(необязательный аргумент) — тип возвращаемого значения.

• 1 — неделя от воскресенья (число 1) до субботу (число 7);
• 2 — неделя от понедельника (число 1) до воскресенья (число 7);
• 3 — неделя от понедельника (число 0) до воскресенья (число 6).

В нашем случае в качестве даты в числовом формате вводим ячейку с датой A2 и задаем тип как 2, в результате получаем значение 2 (т.е. вторник):

Определение дня недели с помощью функции ДЕНЬНЕД

Определим день недели для заданной даты в ячейке. Существует несколько решений в зависимости от того результата, который мы хотим получить: номер дня недели (1 — Пн, 2- Вт, . ), текстовую форму (Понедельник, Вторник, . ) или просто можно изменить формат ячейки.

День недели можно быстро отобразить с помощью Пользовательского формата. Пользовательский формат можно ввести через диалоговое окно Формат ячеек, вкладка Число, (все форматы), нажав CTRL+1. Для отображения вместо даты дня недели, на который она приходится, в поле Тип, предварительно все из него удалив, нужно ввести ДДД (краткий формат дня недели, например, Сб) или ДДДД (будет отражено Суббота).

Другим способом, определения дня недели является преобразование даты в текстовую форму с определенным форматом (см. файл примера ): =ТЕКСТ(01/01/10;»дддд»)

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

Для определения дня недели через числовую форму (понедельник=1; вторник=2, …) существует функция ДЕНЬНЕД() , английская версия WEEKDAY().

Если дата находится в ячейке А1, то формула =ДЕНЬНЕД(A1;2) вернет день недели. Номер 1 соответствует понедельнику.

Для того, чтобы вывести день недели на английском языке используйте формулу = ТЕКСТ(A6;»ДДД»)

Другие статьи на эту тему можно найти в разделе Неделя, День недели.

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

Создание критериев критерий для функции СУММЕСЛИ

2-ой аргумент функции именуется «Аспект». Данный логический аргумент употребляется и в остальных схожих логических функциях: СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. В любом случаи аргумент заполняется согласно одних и тех же правил составления логических критерий. Иными словами, для всех этих функций 2-ой аргумент с аспектом критерий является логическим выражением возвращающим итог ИСТИНА либо ЛОЖЬ. Это означает, что выражение обязано содержать оператор сопоставления, к примеру: больше (>) меньше (<) равно (=) неравно (<>), больше либо равно (>=), меньше либо равно (<=). Кроме можно не указывать оператор равно (=), если обязано быть испытано четкое совпадение значений.

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

Таблица правил составления критериев критерий:

Чтоб сделать условие	Примените правило	Пример
Значение равно данному числу либо ячейке с данным адресом.	Не используйте символ равенства и двойных кавычек.	=СУММЕСЛИ(B1:B10;3)
Значение равно текстовой строке.	Не используйте символ равенства, но используйте двойные кавычки по бокам.	=СУММЕСЛИ(B1:B10;»Клиент5″)
Значение различается от данного числа.	Расположите оператор и число в двойные кавычки.	=СУММЕСЛИ(B1:B10;»>=50″)
Значение различается от текстовой строчки.	Расположите оператор и число в двойные кавычки.	=СУММЕСЛИ(B1:B10;»<>выплата»)
Значение различается от ячейки по обозначенному адресу либо от результата вычисления формулы.	Расположите оператор сопоставления в двойные кавычки и соедините его эмблемой амперсант (&) совместно со ссылкой на ячейку либо с формулой.	=СУММЕСЛИ(A1:A10;»<«&C1) либо =СУММЕСЛИ(B1:B10;»<>»&СЕГОДНЯ())
Значение содержит фрагмент строчки	Используйте операторы неоднозначных знаков и расположите их в двойные кавычки	=СУММЕСЛИ(A1:A10;»*кг*»;B1:B10)

Во 2-м аргументе критериев критерий можно применять различные функции и формулы. Ниже на рисунке изображен перечень дат и присвоенных им значений. Принципиально отметить что сейчас на момент написания статьи дата — «03.11.2018». Чтоб суммировать числовые значения лишь по нынешней дате используйте формулу:

Чтоб суммировать лишь значения от нынешнего денька включительно и до конца периода времени воспользуйтесь оператором «больше либо равно» (>=) совместно с соответствующей функцией =СЕГОДНЯ(). Формула c операторам (>=):

Как посчитать для нескольких ячеек

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

• Кликните по ячейке, где нужно посчитать требуемое значение в Эксель.
• Жмите на «Вставить функцию», находящуюся с левой стороны от строки формул или же кликните Shift+F3.

• Запустите «Мастер функций», в перечне доступных опций найдите «СРЗНАЧ».
• Выделите его и кликните на «ОК».

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

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

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

Синтаксис

Аргументы

Обязательный аргумент. Одна или несколько ячеек для вычисления среднего, включающих числа или имена, массивы или ссылки, содержащие числа.
Параметр «диапазон_условий1» является обязательным, остальные диапазоны условий — необязательные. От 1 до 127 интервалов, в которых проверяется соответствующее условие.
Параметр «условие1» является обязательным, остальные условия — необязательные. От 1 до 127 условий в форме числа, выражения, ссылки на ячейку или текста, определяющих ячейки, для которых будет вычисляться среднее. Например, условие может быть выражено следующим образом: 32, «32», «>32», «яблоки» или B4.

Замечания

• Если «диапазон_усреднения» является пустым или текстовым значением, то функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
• Если ячейка в диапазоне условий пустая, функция СРЗНАЧЕСЛИМН обрабатывает ее как ячейку со значением 0.
• Ячейки в диапазоне, которые содержат значение ИСТИНА, оцениваются как 1; ячейки в диапазоне, которые содержат значение ЛОЖЬ, оцениваются как 0 (ноль).
• Каждая ячейка в аргументе «диапазон_усреднения» используется в вычислении среднего значения, только если все указанные для этой ячейки условия истинны.
• В отличие от аргументов диапазона и условия в функции СРЗНАЧЕСЛИ, в функции СРЗНАЧЕСЛИМН каждый диапазон_условий должен быть одного размера и формы с диапазоном_суммирования.
• Если ячейки в параметре «диапазон_усреднения» не могут быть преобразованы в численные значения, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ0!.
• Если нет ячеек, которые соответствуют условиям, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
• В этом аргументе можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, то перед ними следует поставить знак тильды (~).
• Функция СРЗНАЧЕСЛИМН измеряет среднее значение распределения, то есть расположение центра набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения:
  • Среднее значение    — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7,10 будет 5, которое получается в результате деления их суммы, равной 30, на их количество, равное 6.
  • Медиана    — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7, 10 будет 4.
  • Мода    — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7, 10 будет 3.

  ​При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. При смещенном распределении множества чисел значения могут быть разными.

Особенности использования финансовой функции ПС в Excel

Функция ПС используется наряду с прочими функциями (СТАВКА, БС, ПЛТ и др.) для финансовых расчетов и имеет следующий синтаксис:

=ПС(ставка; кпер; плт; ; )

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

• ставка – обязательный аргумент, который характеризует значение процентной ставки за 1 период выплат. Задается в виде процентного или числового формата данных. Например, если кредит был выдан под 12% годовых на 1 год с 12 периодами выплат (ежемесячный фиксированный платеж), то необходимо произвести пересчет процентной ставки на 1 период следующим способом: R=12%/12, где R – искомая процентная ставка. В качестве аргумента формулы ПС может быть указана как, например, 1% или 0,01;
• кпер – обязательный аргумент, характеризующий целое числовое значение, равное количеству периодов выплат. Например, если был сделан депозит в банк сроком на 3 года с ежемесячной капитализацией (вклад увеличивается каждый месяц), число периодов выплат рассчитывается как 12*3=36, где 12 – месяцы в году, 3 – число лет, на которые был сделан вклад;
• плт – обязательный аргумент, характеризующий числовое значение, соответствующее фиксированный платеж за каждый период. На примере кредита, плт включает в себя часть тела кредита и проценты. Дополнительные проценты и комиссии не учитываются. Должен быть взят из диапазона отрицательных чисел, поскольку выплата – расходная операция. Аргумент может быть опущен (указан 0), но в этом случае аргумент будет являться обязательным для заполнения;
• – необязательный аргумент (за исключением указанного выше случая), характеризующий числовое значение, равное остатку средств на конец действия договора. Равен 0 (нулю), если не указан явно;
• – необязательный аргумент, принимающий числовые значения 0 и 1, характеризующий момент выполнения платежей: в конце и в начале периода соответственно. Равен 0 (нулю) по умолчанию.

Примечания 1:

1. Функция ПС используется для расчетов финансовых операций, выплаты в которых производятся по аннуитетной схеме, то есть фиксированными суммами через определенные промежутки времени определенное количество раз.
2. Расходные операции для получателя записываются числами с отрицательным знаком. Например, депозит на сумму 10000 рублей для вкладчика равен -10000 рублей, а для банка – 10000 рублей.
3. Между всеми функциями, связанными с аннуитетными схемами погашения стоимости существует следующая взаимосвязь:
4. Приведенная выше формула значительно упрощается, если процентная ставка равна нулю, и принимает вид: (плт * кпер) + пс + бс = 0.
5. В качестве аргументов функции ПС должны использоваться данные числового формата или текстовые представления чисел. Если в качестве одного из аргументов была передана текстовая строка, которая не может быть преобразована в числовое значение, функция ПС вернет код ошибки #ЗНАЧ!.

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

Примеры функции СУММЕСЛИМН в Excel

У нас есть таблица с данными об оказанных услугах клиентам из разных городов с номерами договоров.

Предположим, нам необходимо подсчитать количество услуг в определенном городе с учетом вида услуги.

Как использовать функцию СУММЕСЛИМН в Excel:

1. Вызываем «Мастер функций». В категории «Математические» находим СУММЕСЛИМН. Можно поставить в ячейке знак «равно» и начать вводить название функции. Excel покажет список функций, которые имеют в названии такое начало. Выбираем необходимую двойным щелчком мыши или просто смещаем курсор стрелкой на клавиатуре вниз по списку и жмем клавишу TAB.
2. В нашем примере диапазон суммирования – это диапазон ячеек с количеством оказанных услуг. В качестве первого аргумента выбираем столбец «Количество» (Е2:Е11). Название столбца не нужно включать.
3. Первое условие, которое нужно соблюсти при нахождении суммы, – определенный город. Диапазон ячеек для проверки условия 1 – столбец с названиями городов (С2:С11). Условие 1 – это название города, для которого необходимо просуммировать услуги. Допустим, «Кемерово». Условие 1 – ссылка на ячейку с названием города (С3).
4. Для учета вида услуг задаем второй диапазон условий – столбец «Услуга» (D2:D11). Условие 2 – это ссылка на определенную услугу. В частности, услугу 2 (D5).
5. Вот так выглядит формула с двумя условиями для суммирования: =СУММЕСЛИМН(E2:E11;C2:C11;C3;D2:D11;D5).

Результат расчета – 68.

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

Теперь можно посмотреть, сколько услуг 2 оказано в том или ином городе (а не только в Кемерово). Формулу немного видоизменим: =СУММЕСЛИМН($E$2:$E$11;$C$2:$C$11;F$2;$D$2:$D$11;$D$5).

Все диапазоны для суммирования и проверки условий нужно закрепить (кнопка F4). Условие 1 – название города – ссылка на первую ячейку выпадающего списка. Ссылку на условие 2 тоже делаем постоянной. Для проверки из списка городов выберем «Кемерово»:

Результат тот же – 68.

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

голоса

Рейтинг статьи

Если достаточно выполнения хотя бы одного условия (логика ИЛИ).

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

Но что если вы хотите подсчитать значения, для которых хотя бы одно из указанных условий имеет значение ИСТИНА , то есть использовать логику ИЛИ? В принципе, есть два способа сделать это — 1) сложив несколько формул СЧЕТЕСЛИ или 2) использовать комбинацию СУММ+СЧЕТЕСЛИМН с константой массива.

Способ 1. Две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН.

Подсчитаем заказы со статусами «Отменено» и «Ожидание». Чтобы сделать это, вы можете просто написать 2 обычные формулы СЧЕТЕСЛИ и затем сложить результаты:

В случае, если нужно оценить более одного параметра отбора, используйте СЧЕТЕСЛИМН.

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

Способ 2. СУММ+СЧЁТЕСЛИМН с константой массива.

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

Вставьте СЧЕТЕСЛИМН в функцию СУММ, вот так:

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

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

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