Вычисление в MS 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)

Критерий «яблоки» можно поместить в ячейку D8, тогда формулу можно переписать следующим образом:
=СРЗНАЧЕСЛИ($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)

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

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.8 (6 оценок)