Урок 25
§27. Обработка больших массивов данных
Содержание урока
Использование вспомогательных столбцов
Стандартные функции
Практическая работа №28 «Обработка больших массивов данных»
Практическая работа № 28-а «Обработка больших массивов данных»
Стандартные функции
Во многих случаях можно обойтись без вспомогательных столбцов, если использовать стандартные функции.
Функция 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)
Следующая страница 
Работа с листами
Cкачать материалы урока
