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

Синтаксис: =МАКС(число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, т.к. логическое значение задано явно, поэтому не игнорируется и преобразуется в единицу.

Задача2 (2 числовых критерия)

Другой задачей может быть нахождение сумм ящиков только тех партий товаров, у которых количество ящиков попадает в определенный интервал, например от 5 до 20 (см. файл примера Лист 2Числа ).

Формулы строятся аналогично задаче 1: =СУММЕСЛИМН(B2:B13;B2:B13;”>=”&D2;B2:B13;”

Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($B2>=$D$2;$B2

Функция Excel AVERAGE

Функция Excel AVERAGE вычисляет среднее (среднее арифметическое) предоставленных чисел. AVERAGE может обрабатывать до 255 отдельных аргументов, которые могут включать числа, ссылки на ячейки, диапазоны, массивы и константы.

  • номер 1 – Номер или ссылка на ячейку, относящиеся к числовым значениям.
  • номер 2 – [необязательно] Число или ссылка на ячейку, которая относится к числовым значениям.

Функция СРЕДНЕЕ вычисляет среднее значение чисел, представленных как аргументы . Чтобы вычислить среднее значение, Excel суммирует все числовые значения и делит их на количество числовых значений. Это поведение можно воспроизвести с помощью СУММ и СЧИТАТЬ функционирует так:

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

Функция СРЕДНИЙ автоматически игнорирует текстовые значения и пустые ячейки. Однако включены нулевые (0) значения. Вы можете игнорировать нулевые (0) значения и / или применять определенные критерии, используя Функция СРЗНАЧЕСЛИМН , как описано ниже.

Функция AVERAGE игнорирует логические значения и числа, введенные как текст. Если вам нужно включить эти значения в среднее значение, см. Функция СРЗНАЧ .

Если значения, заданные для СРЕДНЕГО, содержат ошибки, СРЗНАЧ возвращает ошибку. Вы можете использовать Функция AGGREGATE для игнорирования ошибок .

Основное использование

Типичный способ использования функции AVERAGE – указать диапазон, как показано ниже. Копируемая формула в F3:

Базовое использование функции СРЕДНИЙ

В каждой новой строке AVERAGE вычисляет среднее значение оценок каждого человека.

Пустые ячейки

Функция СРЕДНИЙ автоматически игнорирует пустые ячейки. Обратите внимание, что на приведенном ниже экране ячейка C4 пуста, а AVERAGE просто игнорирует ее и вычисляет среднее значение только с B4 и D4:

Функция СРЕДНЕЕ с пустыми ячейками

Однако обратите внимание на нулевое (0) значение в C5 Включено в среднем, поскольку это допустимое числовое значение. Чтобы исключить нулевые значения, используйте СРЕДНИЙ или же В СРЕДНЕМ вместо. В приведенном ниже примере AVERAGEIF используется для исключения нулевых значений. Как и функция СРЕДНИЙ, СРЕДНИЙ автоматически исключает пустые ячейки.

Функция СРЗНАЧЕСЛИ исключает ноль

Смешанные аргументы

Числа, предоставленные для AVERAGE, могут быть смесью ссылок и констант:

Функция СРЗНАЧ со смешанными аргументами

Среднее по критериям

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

Функция СРЗНАЧЕСЛИМН с критериями

Функция СРЗНАЧЕСЛИМН также может применять несколько критериев .

Средний топ-3

Объединив функцию СРЕДНИЙ с НАИБОЛЬШИЙ функция , вы можете вычислить среднее из n верхних значений. В приведенном ниже примере формула в столбце I вычисляет среднее значение трех лучших результатов тестов в каждой строке:

СРЕДНЯЯ функция верхняя 3

Подробное объяснение здесь .

Средневзвешенное

Для расчета взвешенный в среднем, вы захотите использовать функцию СУММПРОИЗВ, как показано ниже:

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

Функция Excel СРЗНАЧЕСЛИМН вычисляет среднее число чисел в диапазоне, удовлетворяющем одному или нескольким критериям. Критерии, используемые для СРЗНАЧЕСЛИМН, могут включать логические операторы (>, 10′, A1 или ‘ =), оператор и число заключены в двойные кавычки.

Двойные кавычки также используются для текстовых значений. Например, для усреднения значений в B1: B10, когда значения в A1: A10 равны «красному», вы можете использовать следующую формулу:

Несколько критериев

Введите критерии парами [диапазон, критерии]. Например, для усреднения значений в A1: A10, где B1: B10 = ‘A’, и C1: C10> 5, используйте:

Значение из другой ячейки

Значение из другой ячейки может быть включено в критерии с помощью конкатенация . В приведенном ниже примере СРЗНАЧЕСЛИМН вернет среднее значение чисел в ячейке A1: A10, которые меньше значения в ячейке B1. Обратите внимание на менее чем оператор (текст) заключен в кавычки.

Подстановочные знаки

В подстановочный знак в критериях можно использовать знак вопроса (?), звездочку (*) или тильду (

). Знак вопроса (?) Соответствует любому одному символу, а звездочка (*) соответствует нулю или более символов любого типа. Например, для усреднения значений в B1: B10, когда значения в A1: A10 содержать текст «красный» можно использовать по следующей формуле:

) – это escape-символ, позволяющий находить буквальные символы подстановки. Например, чтобы сопоставить буквальный знак вопроса (?), Звездочку (*) или тильду (

), добавьте тильду перед подстановочным знаком (т. Е.

Примечание: порядок аргументов в СРЗНАЧЕСЛИМН и в СРЕДНИЙ . Диапазон средних значений всегда является первым аргументом в СРЗНАЧЕСЛИМН.

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

Функция Excel СРЗНАЧЕСЛИ вычисляет среднее число чисел в диапазоне, удовлетворяющем заданным критериям. Критерии AVERAGEIF могут включать логические операторы (>, 10′, A1 или ‘ , =), оператор и число заключены в двойные кавычки.

Двойные кавычки также используются для текстовых значений. Например, для усреднения значений в B1: B10, когда значения в A1: A10 равны «красному», вы можете использовать следующую формулу:

Значение из другой ячейки

Значение из другой ячейки может быть включено в критерии с помощью конкатенация . В приведенном ниже примере СРЗНАЧЕСЛИ вернет среднее значение чисел в ячейке A1: A10, которые меньше значения в ячейке B1. Обратите внимание на менее чем оператор (текст) заключен в кавычки.

Подстановочные знаки

В подстановочный знак в критериях можно использовать знак вопроса (?), звездочку (*) или тильду (

). Знак вопроса (?) Соответствует любому одному символу, а звездочка (*) соответствует нулю или более символов любого типа. Например, чтобы усреднить ячейки в B1: B10, когда ячейки в A1: A10 содержат текст «красный» в любом месте, вы можете использовать следующую формулу:

) – это escape-символ, который позволяет вам находить буквальные подстановочные знаки. Например, чтобы сопоставить буквальный знак вопроса (?), Звездочку (*) или тильду (

), добавьте тильду перед подстановочным знаком (т. Е.

Примечания

  • Ячейки в классифицировать которые содержат ИСТИНА или ЛОЖЬ, игнорируются.
  • Пустые ячейки игнорируются в классифицировать и средний_ диапазон при расчете средних значений.
  • AVERAGEIF возвращает # DIV / 0! если ни одна ячейка в диапазоне не соответствует критериям.
  • Средний_ диапазон не обязательно быть того же размера, что и классифицировать . Левая верхняя ячейка в средний_ диапазон используется в качестве отправной точки, а ячейки, соответствующие ячейкам в классифицировать усреднены.
  • AVERAGEIF позволяет использовать подстановочные знаки вопросительного знака (?) И звездочки (*) в критериях. ? соответствует любому одиночному символу, а * соответствует любой последовательности символов. Чтобы найти буквальный? или * используйте тильду (
Читайте также:  Как сделать сортировку в Excel по возрастанию и по убыванию

) перед символом, т.е.

Двойные кавычки (”) в критериях

Как правило, текстовые значения заключаются в двойные кавычки (”), а числа – нет. Однако когда логический оператор входит в число, число и оператор должны быть заключены в кавычки. Обратите внимание на разницу в двух приведенных ниже примерах. Поскольку во второй формуле используется оператор ‘больше или равно’ (> =), оператор и число заключены в двойные кавычки.

Двойные кавычки также используются для текстовых значений. Например, для усреднения значений в B1: B10, когда значения в A1: A10 равны «красному», вы можете использовать следующую формулу:

Значение из другой ячейки

Значение из другой ячейки может быть включено в критерии с помощью конкатенация . В приведенном ниже примере СРЗНАЧЕСЛИ вернет среднее значение чисел в ячейке A1: A10, которые меньше значения в ячейке B1. Обратите внимание на менее чем оператор (текст) заключен в кавычки.

Подстановочные знаки

В подстановочный знак в критериях можно использовать знак вопроса (?), звездочку (*) или тильду (

). Знак вопроса (?) Соответствует любому одному символу, а звездочка (*) соответствует нулю или более символов любого типа. Например, чтобы усреднить ячейки в B1: B10, когда ячейки в A1: A10 содержат текст «красный» в любом месте, вы можете использовать следующую формулу:

) – это escape-символ, который позволяет вам находить буквальные подстановочные знаки. Например, чтобы сопоставить буквальный знак вопроса (?), Звездочку (*) или тильду (

Инструкции по использованию функции Excel СРЗНАЧЕСЛИМН

Функция СРЗНАЧЕСЛИМН в Excel имеет формулу функции СРЗНАЧЕСЛИМН (средний_диапазон, диапазон_критерия1, критерий1, [criteria_range2, criteria2],.).

  1. средний_ диапазон: область данных, в которой необходимо вычислить среднее значение, включает номер ссылки или имя или массив, содержащий число, которое является обязательным параметром.
  2. диапазон_критерия1, диапазон_критерия2: условие, содержащее общее условие, диапазон_критерия1 является обязательным параметром, остальные параметры диапазона_критерия до 127 аргументов являются обязательными параметрами.
  3. критерий1, критерий2. в качестве условия для усреднения плюс, riteria1 является обязательным параметром, другие критерии могут содержать не более 127 условий.

Обратите внимание на использование функций:

  1. Если значение аргумента содержит логическое значение или пустую ячейку, это значение будет проигнорировано.
  2. Если диапазон пустой или текстовый, функция возвращает значение ошибки # ДЕЛ / 0.
  3. Если ячейки в Criteria_range пусты, функция по умолчанию – 0.
  4. Если значение в ячейке данных – логическое значение = Истина, оно рассматривается как 1, если значение Ложь считается равным 0.
  5. Если значения в average_range не могут быть преобразованы в числа, выдается сообщение об ошибке # DIV / 0.
  6. Если ни одно значение не соответствует всем условиям, функция ошибки # DIV / 0!
  7. Можно использовать альтернативные символы, например *. в условиях среднего расчета.

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

Изображение 1 из Как использовать функцию СРЗНАЧЕСЛИМН в Excel

Пример 1. Рассчитайте среднюю заработную плату сотрудников от 3 000 000 до 7 000 000 человек.

Вы вводите формулу = СРЗНАЧЕСЛИМН (D2: D6, D2: D6, “> 3000000”, D2: D6, “.

Где D2: D6 – столбец данных для вычисления, а D2: D6, “> 3000000″, D2: D6, ”

Изображение 2: Как использовать функцию СРЗНАЧЕСЛИМН в Excel

Затем он отобразит средний результат плюс данные, основанные на установленном условии. Расчетная сумма составляет 5 433 333 человека.

Изображение 3: Как использовать функцию СРЗНАЧЕСЛИМН в Excel

Пример 2: Расчет среднего бонуса сотрудников-мужчин с зарплатой 250 000.

Также в поле ввода пользователи вводят формулу для вычисления значения = СРЕДНЕЕ РАЗМН (E2: E6, E2: E6, «250000», C2: C6, «Nam»).

Где E2: E6 – это область для вычисления значения, E2: E6, «250000» – первое условие для значения в этой области. C2: C6, «Мужской» – второе условие с другой областью данных.

Изображение 4 из Как использовать функцию СРЗНАЧЕСЛИМН в Excel

При нажатии Enter результат будет 250 000, как показано ниже.

Изображение 5 из Как использовать функцию СРЗНАЧЕСЛИМН в Excel

Пример 3: Рассчитайте среднюю заработную плату сотрудников-мужчин, превышающую 200 000, и заработную плату, превышающую 4 500 000 человек.

В поле ввода вводим формулу = = СРЗНАЧЕСЛИМН (E2: E6, E2: E6, “> 200000”, C2: C6, “Male”, D2: D6, “> 4500000”).

Где E2: E6 – это область данных для вычисления среднего значения, E2: E6, «> 200000» – первое условие в области данных E2: E6, C2: C6, «Male» – второе условие в области данных C2: C6, D2: D6, “> 4500000”) – это 3-е условие в области данных D2: D6.

Изображение 6 из Как использовать функцию СРЗНАЧЕСЛИМН в Excel

Нажмите Enter, и результат будет 375 000, как показано ниже. Потому что только 2 сотрудника-мужчины соответствуют всем 3 условиям в запросе.

Изображение 7 из Как использовать функцию СРЗНАЧЕСЛИМН в Excel

Пример 4: Рассчитайте среднюю зарплату сотрудников-женщин с заработной платой более 7 500 000 человек.

Также в поле, в котором отображаются результаты, пользователь вводит формулу для вычисления СРЕДНЕЛИМН = = СРЕДНЕМН (D2: D6, D2: D6, «> 7500000», C2: C6, «Женский») и затем нажимает Enter.

Изображение 8 из Как использовать функцию СРЗНАЧЕСЛИМН в Excel

Результат будет указывать на ошибку # DIV0! Поскольку в формуле выполняется только одно условие, ни одна ячейка не удовлетворяет всем двум условиям в формуле.

Изображение 9 из Как использовать функцию СРЗНАЧЕСЛИМН в Excel

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

Что такое функция СРЗНАЧЕСЛИМН в Excel? Что с ее помощью делают?

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

текст при наведении

Более подробно об этой полезной функции можете прочитать по этой ссылке.

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

Общее определение и задачи

«ЕСЛИ» является стандартной функцией программы Microsoft Excel. В ее задачи входит проверка выполнения конкретного условия. Когда условие выполнено (истина), то в ячейку, где использована данная функция, возвращается одно значение, а если не выполнено (ложь) – другое.

Синтаксис этой функции выглядит следующим образом: «ЕСЛИ(логическое выражение; [функция если истина]; [функция если ложь])» .

Среднее значение в Эксель по нескольким критериям

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

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

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

таблица продаж по категориям

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

среднее по двум критериям

Обратите внимание на ссылки в формуле. Знаки $ перед координатами означают, что они не должны изменяться при копировании формулы в остальные строки. Это очень важно и если вам этот прием не знаком, прочтите статью о видах ссылок в Excel.

В результатах работы есть неприятный момент. Функция возвращает ошибку #ДЕЛ/0! в тех строках, где продаж не было. Например, восточный регион не продавал смартфоны и ТВ. Естественно, вместо ошибки должен стоять ноль. Чтобы заменить ошибки на нули, используем функцию обработки ошибок ЕСЛИОШИБКА. Обернём ею ранее написанную формулу:

Теперь вместо ошибок появятся нули. Я отключу отображение нулевых значений, чтобы ячейка выглядела пустой. Так, на мой взгляд, нагляднее. Снимем галку: Файл – Параметры – Дополнительно – Параметры отображения листа – Показывать нули в ячейках, которые содержат нулевые значения . Эта настройка указана для Office 2013.В других версиях расположение галки может немного отличаться.

Читайте также:  Динамическая карта в Excel из фигур и макросов VBA скачать

среднее без ошибок

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

Примеры

Рассмотрим случай, когда аргумент Диапазон_усреднения опущен. В этом случае вычисление среднего будет производиться по диапазону ячеек, указанному в первом аргументе Диапазон (т.е. он должен содержать числа). В нем же будет производиться поиск значений соответствующих аргументу Условие , которые затем и будут участвовать в вычислении среднего. Пусть это будет диапазон A5:A15 , см. Файл примера .

  • найти среднее всех чисел менее или равных заданному (10): =СРЗНАЧЕСЛИ(A5:A15;” .
  • найти среднее всех положительных чисел в диапазоне: =СРЗНАЧЕСЛИ(A5:A15;”>0″) . Другой вариант с использованием функции СУММПРОИЗВ() : =СУММПРОИЗВ((A5:A15)*(A5:A15>0))/ СЧЁТЕСЛИ(A5:A15;”>=10″)

Форма задания критерия достаточно гибка. Например, в файле примера в формуле =СРЗНАЧЕСЛИ(A5:A15;C7&C8) критерий >=10 задан через ссылку C7&C8 : в С7 содержится текстовое значение >= , а в С8 – число 10 . Пользователь может легко изменить критерий.

СОВЕТ: В статье Вычисление среднего по условию (один Текстовый критерий) рассмотрен более общий случай, когда критерий применяется к соседнему диапазону, содержащему текстовые значения, а суммирование производится только соответствующих ячеек из числового диапазона.

Вычисление с помощью Мастера функций

Для случаев, когда нужно подсчитать среднюю арифметическую массива ячеек, или разрозненных ячеек, можно использовать Мастер функций. Он применяет все ту же функцию «СРЗНАЧ», известную нам по первому методу вычисления, но делает это несколько другим способом.

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

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

Открывается окно аргументов данной функции. В поля «Число» вводятся аргументы функции. Это могут быть как обычные числа, так и адреса ячеек, где эти числа расположены. Если вам неудобно вводить адреса ячеек вручную, то следует нажать на кнопку расположенную справа от поля ввода данных.

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

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

После этого, жмите на кнопку «OK».

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

Критерий точно соответствует значению

Как видно из рисунка выше, яблоки бывают 2-х сортов: обычные яблоки и яблоки RED ЧИФ. Найдем среднее количество на складах ящиков с обычными яблоками. В качестве критерия функции СРЗНАЧЕСЛИ() будем использовать слово «яблоки» .

При расчете среднего, функция СРЗНАЧЕСЛИ() учтет только значения 2; 4; 5; 6; 8; 10; 11, т.е. значения в строках 6, 9-14. В этих строках в столбце А содержится слово «яблоки» , точно совпадающее с критерием.

В качестве диапазона усреднения можно указать лишь первую ячейку диапазона – функция СРЗНАЧЕСЛИ() вычислит все правильно:= СРЗНАЧЕСЛИ($A$6:$A$16;”яблоки”;B6)

Критерий «яблоки» можно поместить в ячейку D 8 , тогда формулу можно переписать следующим образом:= СРЗНАЧЕСЛИ($A$6:$A$16;D8;B6)

Задача1 (1 текстовый критерий и 1 числовой)

Найдем количество ящиков товара с определенным Фруктом И , у которых Остаток ящиков на складе не менее минимального. Например, количество ящиков с товаром персики ( ячейка D 2 ), у которых остаток ящиков на складе >=6 ( ячейка E 2 ) . Мы должны получить результат 64. Подсчет можно реализовать множеством формул, приведем несколько (см. файл примера Лист Текст и Число ):

1. = СУММЕСЛИМН(B2:B13;A2:A13;D2;B2:B13;”> Function_F”>

Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)

  • B2:B13 Интервал_суммирования — ячейки для суммирования, включающих имена, массивы или ссылки, содержащие числа. Пустые значения и текст игнорируются.
  • A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в которых проверяется соответствующее условие.
  • D2 и “> ” height=”498″ src=”https://excel2.ru/sites/default/files/pic_node/Sum/sum-32.png” width=”544″/>

Порядок аргументов различен в функциях СУММЕСЛИМН() и СУММЕСЛИ() . В СУММЕСЛИМН() аргумент интервал_суммирования является первым аргументом, а в СУММЕСЛИ() – третьим. При копировании и редактировании этих похожих функций необходимо следить за тем, чтобы аргументы были указаны в правильном порядке.

2. другой вариант = СУММПРОИЗВ((A2:A13=D2)*(B2:B13);–(B2:B13>=E2)) Разберем подробнее использование функции СУММПРОИЗВ() :

  • Результатом вычисления A2:A13=D2 является массив <ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики . Массив можно увидеть, выделив в Строке формул A2:A13=D2 , а затем нажав F9 ;
  • Результатом вычисления B2:B13 является массив<3:5:11:98:4:8:56:2:4:6:10:11>, т.е. просто значения из столбца B ;
  • Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является <0:0:0:0:4:8:56:0:0:0:0:0>. При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
  • Разберем второе условие: Результатом вычисления –( B2:B13>=E2) является массив <0:0:1:1:0:1:1:0:0:1:1:1>. Значения в столбце « Количество ящиков на складе », которые удовлетворяют критерию >=E2 (т.е. >=6) соответствуют 1;
  • Далее, функция СУММПРОИЗВ() попарно перемножает элементы массивов и суммирует полученные произведения. Получаем – 64.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) .

4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) похожа на вышеупомянутую формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После ее ввода нужно вместо ENTER нажать CTRL + SHIFT + ENTER

5. Формула массива =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.

6. Формула =БДСУММ(A1:B13;B1;D14:E15) требует предварительного создания таблицы с условиями (см. статью про функцию БДСУММ() ). Заголовки этой таблицы должны в точности совпадать с соответствующими заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И (см. диапазон D14:E15 ).

Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

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

Функция Averageifs в excel используется для нахождения среднего значения из целевого диапазона ячеек, когда необходимо выполнить более одного условия, для одного условия мы использовали averageif, но для нескольких условий мы используем averageifs, он может принимать аргументы, где первым аргументом является Столбец диапазона, среднее значение которого необходимо вычислить, а остальные – это диапазон критериев, за которым следуют критерии.

Хорошо, теперь посмотрим на синтаксис функции Excel СРЗНАЧЕСЛИМН, затем мы перейдем к разделу примеров.

Функция AVERAGEIFS в синтаксисе Excel

  • Средний диапазон: для диапазона ячеек нам нужно взять среднее
  • Диапазон критериев 1: исходя из этого, нам нужно вычесть среднее значение, поэтому в этом аргументе нам нужно выбрать этот столбец.
  • Критерии1: В вышеупомянутом Диапазон критериев 1 для которого значение нужно считать средним.
  • Диапазон критериев 2: это второй диапазон критериев для вычисления среднего значения, поэтому в этом аргументе нам нужно выбрать второй столбец.
  • Criteria2: В вышеупомянутом Диапазон критериев 2 для которого значение нужно считать средним.

Заметка: Логическое значение ИСТИНА или ЛОЖЬ рассматривается как 1 и 0. Даже нулевое значение считается средним.

Примеры

Пример # 1

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

Функция СРЗНАЧЕСЛИМН в примере 1.1 Excel

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

  • Откройте функцию AVERAGEIFS сейчас.

Функция СРЗНАЧЕСЛИМН в Excel, пример 1.2

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

Функция СРЗНАЧЕСЛИМН в Excel, пример 1.3

  • Следующий аргумент Диапазон критериев 1, поэтому выберите столбец 9 * «Регион» в качестве ссылки.

Функция СРЗНАЧЕСЛИМН в Excel, пример 1.4

  • Для этого диапазона критериев нам нужно среднее значение «Восточного» региона, поэтому укажите Критерий 1 как «Восток».

средний пример 1.4

  • Следующий аргумент Диапазон критериев 2, поэтому выберите столбец «Месяц» как ссылку.

средний пример 1.5

  • Критерий 2 из Диапазон критериев 2, за какой месяц нам нужно вычесть среднее значение. Нам нужно среднее значение за месяц «Мар», поэтому укажите это в Критерии 2.

средний пример 1.6

  • Теперь нажмите Enter, чтобы получить результат.

пример 1.7

Так, средние продажи по региону «Восток» за месяц «март» составляют 2067,67.

Пример # 2

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

пример 2.1

Из этих данных нам нужно взять среднюю продажу между двумя датами. Во-первых, нам нужно взять средние продажи с 27 апреля 2019 года по 26 мая 2019 года, а затем средние продажи с 26 мая 2019 года по 17 июня 2019 года.

Эти критерии должны быть снабжены логическим оператором в символах Excel.

  • Давайте теперь откроем функцию СРЗНАЧЕСЛИМН в ячейке D4.

пример 2.2

  • Выберите Средний диапазон в столбце «Продажи».

пример 2.3

  • Затем выберите Диапазон критериев 1 как столбец даты.

пример 2.5

  • Критерий 1 должно быть больше 27 апреля 2019 г .; сначала укажите символ «больше» в двойных кавычках, а затем объедините его с ячейкой D3.

Функция AVERAGEIFS в Excel, пример 2.6

  • Теперь снова для Диапазон критериев 2, выберите столбец Дата.

Функция СРЗНАЧЕСЛИМН в Excel, пример 2.7

  • Следующий, Критерий 2 нам нужны средние продажи на дату менее 26 мая -2019. Поэтому введите меньше символа в двойные кавычки и объедините его с ячейкой E3.

Функция AVERAGEIFS в Excel, пример 2.8

  • Нажмите клавишу Enter, чтобы получить среднее значение.

Функция AVERAGEIFS в Excel, пример 2.9

  • Таким образом, средний объем продаж с 27 апреля по 26 мая 2019 года составил 4620,8. Точно так же нам нужны средние продажи с 25 мая 2019 года по 18 июня 2019 года; просто скопируйте и вставьте формулу из ячейки D4 в ячейку D8.

Функция СРЗНАЧЕСЛИМН в Excel, пример 2.10

Здесь мы получили средние продажи 3770,21. Таким образом, мы можем сделать вывод, что средние продажи больше в первый период времени, то есть с 27 апреля 2019 года по 26 мая 2019 года.

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

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

СУММЕСЛИМН и умные таблицы

Посмотрите на пример 1 нашего учебного файла, формула в L6 . Обратите внимание, как удобно использовать структурные ссылки умных таблиц в формулах вообще и в СУММЕСЛИМН в частности.

L4 и L5 содержат значения критериев.

Критерий для периода дат

А вот как задать период дат (с. по . ) при суммировании:

Обратите внимание на очень важную конструкцию:

“> db_range”>$O$5

Это не что иное, как выражение в виде текстовой строки. Причём это не статическое выражение, которое обычно приводят в справке по СУММЕСЛИМН , типа ” >= 200000 “. Это выражение динамическое, то есть – гораздо более ценное и интересное. Всё что вам нужно – это вставить между знаком операции ” >= ” и ссылкой на ячейку с параметром – знак операции сложения строк ” & “.

Динамическая операция сравнения

А почему бы не дать на откуп пользователю право определять операцию для критерия? В предыдущем примере пользователь мог выбирать порог N в критерии ” >=N “. А в этом примере пользователь определяет и порог и саму операцию! А почему нет?

В L12 у нас находится выпадающий список, ссылающийся на 4 возможные операции: >, >=, ? ” – заменяет любой символ, ” * ” – заменяет любое количество символов (в том числе и его отсутствие). Например критерий ” *т* ” сработает и на слово “Центр”, и на слово “Восток”. А, если бы существовала такая альтернатива, то сработал бы и на слово “опт” (это, как раз случай, когда вторая звёздочка в “*т*” заменила ноль символов справа от “т”).

Ответы на сложные вопросы

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

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

Набор магазинов

А что делать, если нам нужны продажи двух магазинов? В рамках одной формулы это решить в общем случае нельзя (метод через символы подстановки не универсален, так как магазины могут иметь полностью уникальные имена). Ответ очевиден – надо использовать 2 формулы и просто сложить их результаты. Хотя понятно, что такой метод подойдёт далеко не во всех ситуациях.

Но есть и ограничения.

Надеюсь вы убедились, что *ЕСЛИМН достаточно гибки, но есть один узкий момент, который надо хорошо понимать. Например, я хочу знать, сколько раз я торговал в Центре, предоставляя покупателям скидку более или равную 5%? Как я могу это узнать? Только вычисляя по каждой строке отношение предоставленной скидки к базовой цене. Понимаете – по каждой строке надо делать вычисление и сравнивать с 5%! Вот такое формулы *ЕСЛИМН сами, без вашей помощи сделать не смогут, так как формула один раз вычисляет критерий, а потом сравнивает его со всеми строками критериального диапазона, а нам надо это делать динамически. Но никто нам не мешает организовать дополнительный столбец, который будет предварительно считать отношение скидки к базовой цене, а после этого можно уже остальную работу поручить формуле СЧЁТЕСЛИМН (см. пример 7).

Пустые ячейки

Если вы хотите, чтобы формула *ЕСЛИМН отреагировала на пустые ячейки, то следует использовать критерии “” (пустая строка) или ” db_sheet”>Blank нашего учебного файла:

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; ” db_formula”>ЕСЛИ ), то такая ячейка отреагирует только на критерий “”, то есть:

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; “” )

Не пустые ячейки

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

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; “<>” )

Ну что ж, я надеюсь, что вы почувствовали всю силу, сосредоточенную в формулах этого семейства. Удачи!

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