Вычисление в EXCEL среднего по условию (один ТЕКСТовый критерий)

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


Пусть дана таблица с наименованием фруктов и количеством ящиков на каждом складе (см. файл примера ).

Рассмотрим 2 типа задач:

  • Найдем среднее только тех значений, у которых соответствующие им ячейки (расположенные в той же строке) точно совпадают с критерием (например, вычислим среднее для значений,соответствующих названию фрукта "яблоки");
  • Найдем среднее только тех значений, у которых соответствующие им ячейки (расположенные в той же строке) приблизительно совпадают с критерием (например, вычислим среднее для значений, которые соответствуют названиям фруктов начинающихся со слова "груши"). В критерии применяются подстановочные знаки (*, ?) .

Рассмотрим эти задачи подробнее.

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

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

=СРЗНАЧЕСЛИ($A$6:$A$16;"яблоки";$B$6:$B$16)

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

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

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

В критерии применяются подстановочные знаки (*, ?)



Найдем среднее содержание в ящиках с грушами. Теперь название ящика не обязательно должно совпадать с критерием «груша», а должно начинаться со слова «груша» (см. строки 15 и 16 на рисунке).

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

Решение задачи выглядит следующим образом (учитываются значения содержащие слово груши в начале названия ящика):= СРЗНАЧЕСЛИ($A$6:$A$16; "груши*";B6)

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

Найти среднее значение, если соответствующие ячейки:

Критерий

Формула

Значения, удовлетворяющие критерию

Примечание

заканчиваются на слово яблоки

*яблоки

= СРЗНАЧЕСЛИ($A$6:$A$16; "*яблоки";B6)

Свежие яблоки

Использован подстановочный знак * (перед значением)

содержат слово яблоки

*яблоки*

= СРЗНАЧЕСЛИ($A$6:$A$16; "*яблоки*";B6)

яблоки местные , свежие яблоки или Лучшие яблоки на свете

Использован подстановочный знак * (перед и после значения)

начинаются с гру и содержат ровно 5 букв

гру??

= СРЗНАЧЕСЛИ($A$6:$A$16; "гру??";B6)

груша или груши

Использован подстановочный знак ?


Комментарии
Только для авторизованных пользователей

Аноним, 14 января 2018 г.
Очень помогли, спасибо!
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved