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

Функция COUNT (СЧЁТ) определяет количество числовых ячеек диапазона, при этом пустые и текстовые ячейки не учитываются. Например, для таблицы на рис. 5.11 найти количество участников 2004 года рождения можно было по формуле:

=COUNT(D2:D1001)                  =CЧET(D2:D1001)

 

Ещё лучше использовать для этой цели функцию COUNTIF (СЧЁТЕСЛИ) — она считает ячейки диапазона, удовлетворяющие какому-то условию. Например, количество участников 2004 года можно было вычислить даже без использования вспомогательного столбца:

=COUNTIF(B2:B1001;"=2004")                  =СЧЁТЕСЛИ(В2:В1001;"=2004")

 

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

По формуле

=COUNTIF(C2:C1001;">57")                  =СЧЁТЕСЛИ(С2:С1001;">57")

 

мы определяем количество участников, вес которых больше 57 кг.

Если после точки с запятой стоит число, это значит, что подсчитывается количество ячеек, равных этому числу. То есть вместо "=2004" можно записать просто 2004:

=COUNTIF(B2: В1001;2004)                  =СЧЁТЕСЛИ(В2:В1001;2004)

 

Функция COUNTIF (СЧЁТЕСЛИ) не может работать со сложными условиями, т. е. в условиях нельзя использовать операции И, ИЛИ, НЕ.

Требуется найти количество учеников 2004 года рождения, которые весят больше 60 кг. Для этого используется вспомогательный столбец D. Какую формулу нужно записать в ячейку D2? Как затем решить задачу?

Используя дополнительные источники, выясните, как работает функция COUNTIFS (СЧЁТЕСЛИМН).

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

=COUNTIF(B2:B1001;2004)/COUNT(B2:B1001)

=СЧЁТЕСЛИ(В2:В1001;2004)/СЧЁТ(В2:В1001)

 

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

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

=SUMIF(B2:B1001;2004;C2:C1001)

=СУММЕСЛИ(В2:В1001;2004;С2:С1001)

 

Функция SUMIF принимает три аргумента:

1) диапазон, по которому выполняется проверка условия (В2:В1001);
2) условие, которое проверяется ("=2004");
3) диапазон, по которому вычисляется сумма (С2:С1001). Приведённая только что формула означает: «если ячейка из диапазона В2:В1001 равна 2004, включить в сумму значение соответствующей ячейки из диапазона С2:С1001».

Аналогично вычисляется средний вес этих же участников:

=AVERAGEIF(B2:B1001;2004;C2:C1001)

=СРЗНАЧЕСЛИ(В2:В1001;2004;С2:С1001)