Планирование уроков на учебный год (по учебнику К.Ю. Полякова, Е.А. Еремина, углубленный уровень)



Урок 50
§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качать материалы урока








Наверх