Урок 6. "Функции СЧЕТЕСЛИ(), СУММЕСЛИ(), СРЗНАЧЕСЛИ()")

Функции СЧЕТЕСЛИ, СУММЕСЛИ, СРЗНАЧЕСЛИ в Excel и примеры ее использования

1. Функция СЧЕТЕСЛИ входит в группу статистических функций. Позволяет найти число ячеек по определенному критерию. Работает с числовыми и текстовыми значениями, датами.

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

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

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

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

Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию).

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

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

Примеры:

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

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

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

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

Формула: =СЧЁТЕСЛИ(A1:A12;"яблоки"). Или:

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

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

Посчитаем, сколько ячеек содержат текст «яблоки» и «груши».

Формула: =СЧЁТЕСЛИ(A1:A12;"яблоки")+СЧЁТЕСЛИ(A1:A12;"груши"). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».

3. Посчитаем число ячеек в диапазоне В1:В12 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B12;">=100")-СЧЁТЕСЛИ(B1:B12;">200").

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

Синтаксис: =СЧЕТЕСЛИМН(Диапазон1; Критерий1; Диапазон2; Критерий2;....;ДиапазонN; КритерийN)

Например, подсчитаем количество поступлений яблок в магазин по весу не превышающих 100 кг. Для этого используем формулу: =СЧЁТЕСЛИМН(A1:A12;"яблоки";B1:B12;"<100")

5. Для нахождения суммы по какому-либо критерию используется функция СУММЕСЛИ().

Синтаксис:  =СУММЕСЛИ(Диапазон отбора; Критерий отбора; Диапазон суммирования)

Найдем количество кг яблок поступивших в магазин, для этого используем формулу: =СУММЕСЛИ(A1:A12;"яблоки";B1:B12)

6. Для нахождения суммы ячеек, удовлетворяющих нескольким критериям, используется функция СУММЕСЛИМН().

Синтаксис:  =СУММЕСЛИ(Диапазон суммирования;Диапазон отбора1, Критерий отбора1,Диапазон отбора2, Критерий отбора2,...)

Например, найдем количество кг яблок, завезенных в 1 магазин: =СУММЕСЛИМН(B2:B13;A2:A13;"яблоки";C2:C13;"1")

7. Для нахождения среднего значения по какому-либо критерию используется функция СРЗНАЧЕСЛИ().

Синтаксис:  =СРЗНАЧЕСЛИ(Диапазон отбора; Критерий отбора; Диапазон для расчета)

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

Синтаксис:  =СРЗНАЧЕСЛИМН(Диапазон расчета;Диапазон отбора1, Критерий отбора1,Диапазон отбора2, Критерий отбора2,...).

Задания для выполнения:

1. Найдите на сколько количество завозов яблок превышает количества завозов груш в магазины. Результат запишите в ячейку E2.

2. Найдите количество завозов яблок во 2 магазин. Результат запишите в ячейку E3.

3. Найдите на сколько кг мандарин завезли больше в 1 магазин, чем во 2 магазин. Результат запишите в ячейку E4.

4. Найдите среднее значение кг завезенных яблок во все магазины. Результат запишите в ячейку E5.

5. Найдите среднее значение кг завезенных яблок в 1 магазин. Результат запишите в ячейку E6.