Урок №30. Обработка больших наборов данных.
Табличные процессоры позволяют обрабатывать большие массивы данных, например содержащие несколько тысяч строк. В таких случаях полезно знать специальные приёмы, с которыми вы познакомитесь в этом параграфе.
Представьте себе, что вам нужно выделить для обработки 1000 ячеек, расположенных в одном столбце. Конечно, в этом случае можно использовать и стандартный способ: нажать левую кнопку мыши на первой ячейке диапазона и протащить мышь до последней ячейки, однако это очень неудобно.
Существуют и другие методы. Например, можно выделить щелчком мышью первую ячейку, затем прокрутить таблицу до последней строки и щёлкнуть на последней ячейке при нажатой клавише Shift.
Можно обойтись вообще без прокрутки. Выделив первую ячейку, нажмите клавиши Ctrl+Shift+↓. При этом выделяются все элементы данного столбца вниз до первой пустой ячейки. Аналогично выделяются данные в нескольких столбцах: для этого на первом шаге нужно выделить несколько ячеек в верхней строке диапазона.
Можно выделить несколько диапазонов одновременно: второй и следующий диапазоны выделяются при нажатой клавише Ctrl.
Задача 1. В большой таблице записаны данные участников спортивных соревнований: фамилия, год рождения и вес. Требуется найти количество спортсменов, которые родились в 2004 году. Выполнять сортировку не разрешается.
Один из способов решения этой задачи — добавить в таблицу вспомогательный столбец, в котором напротив каждого участника будет записана единица, если его год рождения равен 2004, или 0, если не равен (рис. 5.10).
Используя материал предыдущего параграфа, запишите формулу, которую нужно добавить в ячейку D2. Предложите два варианта решения задачи.
Формулу из D2 нужно скопировать во все ячейки столбца D. Напомним, что для такого копирования достаточно выполнить двойной щелчок мышью на маркере заполнения выделенной верхней ячейки с формулой.
Теперь количество участников 2004 года рождения находится как сумма значений в столбце D (функция SUM или СУММ). Если в таблице 1000 строк (со 2-й по 1001-ю), формула запишется так:
=SUM(D2:D1001)
=СУММ(D2:D1001)
Задача 2. Для таблицы из задачи 1 требуется найти средний вес тех, кто родился в 2004 году.
Здесь тоже можно использовать вспомогательный столбец. Выведем в нём вес участника, если год рождения равен 2004, или пустую строку («»), если год рождения другой (рис. 5.11).
Используя материал предыдущего параграфа, запишите формулу, которую нужно добавить в ячейку D2. Предложите два варианта решения задачи.
После этого остаётся подсчитать среднее значение ячеек столбца D с помощью функции AVERAGE (СРЗНАЧ), например:
=AVERAGE(D2: D1001)
=СРЗНАЧ(D2:D1001)
Эта функция вычисляет среднее только по тем ячейкам, в которых содержится числовое значение, пустые ячейки не учитываются.
Во многих случаях можно обойтись без вспомогательных столбцов, если использовать стандартные функции.
Функция COUNT (СЧЁТ) определяет количество числовых ячеек диапазона, при этом пустые и текстовые ячейки не учитываются. Например, для таблицы на рис. 5.11 найти количество участников 2004 года рождения можно было по формуле:
=COUNT(D2:D1001)
=СЧЁТ(D2:D1001)
Ещё лучше использовать для этой цели функцию COUNTIF (СЧЁТЕСЛИ) — она считает ячейки диапазона, удовлетворяющие какому-то условию. Например, количество участников 2004 года можно было вычислить даже без использования вспомогательного столбца:
=COUNTIF(B2:В1001;"=2004")
=СЧЁТЕСЛИ(В2:В1001;"=2004")
Второй аргумент этой функции — условие, записанное в кавычках.
По формуле
=COUNTIF(C2:C1001;">57")
=СЧЁТЕСЛИ(С2:С1001;">57")
мы определяем количество участников, вес которых больше 57 кг.
Если после точки с запятой стоит число, это значит, что подсчитывается количество ячеек, равных этому числу. То есть вместо «=2004» можно записать просто 2004:
=COUNTIF(B2:B1001;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 принимает три аргумента:
- диапазон, по которому выполняется проверка условия (В2:В1001);
- условие, которое проверяется («=2004»);
- диапазон, по которому вычисляется сумма (С2:С1001).
Приведённая только что формула означает: «если ячейка из диапазона В2:В1001 равна 2004, включить в сумму значение соответствующей ячейки из диапазона С2:С1001».
Аналогично вычисляется средний вес этих же участников:
=AVERAGEIF(B2:B1001;2004;C2:C1001)
=СРЗНАЧЕСЛИ(В2:В1001;2004;С2:С1001)
Когда данных много, неудобно размещать всё на одном листе. Возможно, вы знаете, что файл электронной таблицы — это рабочая книга, которая может состоять из многих листов. Каждый лист — это отдельная электронная таблица, причём с одного листа вы можете обращаться к данным других листов.
В левом нижнем углу окна расположены элементы управления листами — кнопки для перехода по листам, ярлычки листов и кнопка для создания нового листа (рис. 5.12).
Используя контекстное меню ярлычков, которое появляется при нажатии правой кнопки мыши, можно добавлять, удалять, переименовывать листы. Порядок расположения листов изменяется с помощью мыши (перетаскиванием).
Предположите, как можно скопировать лист. Проверьте свою догадку в программе.
Если нужно использовать данные другого листа, ссылка должна содержать имя этого листа и адрес ячейки (или диапазона) на листе. Эти части ссылки в программе Calc отделяются точкой, а в Excel — восклицательным знаком, например:
=Январь.В2+Февраль.В2+Март.В2
=Январь!В2+Февраль!В2+Март!В2
Если имя листа содержит пробелы, оно заключается в одиночные апострофы:
=SUM('K оплате'.В2:С4)
=СУММ('К оплате'!В2:С4)