С19 (высокий уровень, время – 45 мин)
Тема: Умение проводить обработку большого массива данных с использованием средств электронной таблицы или базы данных.
Что нужно знать:
· С19 работы является практическим заданием, проверяющим умение проводить обработку большого массива данных (до 1000 записей) с использованием средств электронной таблицы, таких как встроенные функции, сортировка, фильтрация и т.д. Задание выполняется на компьютере, и проверяемым результатом выполнения задания является файл. Ответы на задание 19 проверяются и оцениваются экспертами (устанавливается соответствие ответов определенному перечню критериев).
· Для выполнения задания 19 необходима одна из программ для работы с электронными таблицами - Microsoft Excel, OpenOffise.org Calc или др.
· Правила ввода формул:
1. Всегда начинается со знака =
2. Может включать в себя ссылки (адреса ячеек), знаки операций (+, -, *, /, ^), функции и числа.
3. Формулы записываются в линейном виде, порядок выполнения операций определяется скобками и приоритетом (старшинством) операций; операции одинакового приоритета выполняются слева направо.
4. Для ввода в формулу имени ячейки достаточно поместить табличный курсор в соответствующую ячейку.
5. В процессе ввода формулы она отображается как в самой ячейке, так и в строке ввода. Для редактирования формулы выделите ячейку и внесите изменения в строке ввода.
· При обработки данных в электронных таблицах применяются встроенные функции – заранее определенные формулы. При выполнении табличных расчетов в заданиях С19 достаточно часто используются функции:
СУММ(число1;число2;…) – суммирование аргументов
Примеры: =СУММ(153;2111), =СУММ(A2:A4), =СУММ(A2:A4;15), =СУММ(A5;A6;2)
МИН(число1;число2;…) – определение наименьшего значения из списка аргументов
Примеры: =МИН(С1;В2;А3), =МИН(В2:В6), =МИН(В2:В6;100)
МАКС(число1;число2;…)- определение наименьшего значения из списка аргументов
Примеры: =МАКС(А1;В2;С3), =МАКС(A2:A8), =МАКС(A2:A8;33)
СРЗНАЧ(число1;число2;…) – определение среднего (арифметического) своих аргументов
Примеры: =СРЗНАЧ(С1;В2;А3), =СРЗНАЧ(A2:A6), =СРЗНАЧ(A2:A6;5)
И(логическое_значение1;логическое_значение2;…) - возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Аргументы функции - логические выражения, принимающие значения либо истина, либо ложь.
Примеры: =И(2+2=4;2+3=5) – истина(1), =И(1<A2;A2<100), =И(ИСТИНА; ЛОЖЬ) – ложь(0)
ИЛИ(логическое_значение1;логическое_значение2;…)- возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Аргументы функции - логические выражения, принимающие значения либо истина, либо ложь.
Примеры: =ИЛИ(1+1=1;2+2=5) – ложь(0), =ИЛИ(A2>12;B2<100)
НЕ(логическое_значение) - меняет на противоположное логическое значение своего аргумента. Функция НЕ используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине.
Аргумент функции - логическое выражение, принимающие значения либо истина, либо ложь.
Примеры: =НЕ(1+1=2) – ложь(0), =НЕ(10<8) – истина(1), =НЕ(С1=5)
ЕСЛИ(логическое_выражение;значение1;значение2) - используется при проверке условий для значений и формул.
Здесь логическое_выражение – любое выражение, построенное с помощью операций отношения и логических операций, принимающее значения ИСТИНА или ЛОЖЬ.
Если логическое_выражениеистинно, то ячейка, в которую записана условная функция, принимает значение1, если ложно - значение2.
Примеры: =ЕСЛИ(D3>270;«принят»;«не принят») – решение о зачислении в университет (в текстовом формате) при сумме баллов выше 270
=ЕСЛИ(И(В2>90;С2>85);1;0) - решение о зачислении на медицинский факультет (в числовом формате) при оценке по химии (столбец В) выше 90 и оценке по биологии (столбец С) выше 85
СЧЁТЕСЛИ(диапазон;критерий) - подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.
Примеры: =СЧЕТЕСЛИ(A2:A55;ИСТИНА), =СЧЕТЕСЛИ(B22:B122;«>55»)
СУММЕСЛИ(диапазон;критерий;диапазон_суммирования) - суммирует ячейки, заданные критерием.
Здесь диапазон — диапазон вычисляемых ячеек.
Критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки.
Диапазон_суммирования — фактические ячейки для суммирования; ячейки в «диапазон_суммирования» суммируются только тогда, когда соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию.
Пример: Какова общая масса груза при автоперевозках, осуществлённых из города Липки? (таблица содержит 370 записей)
| A | B | C | D | E | F | G | |
| Дата | Пункт отправления | Пункт назначения | Расстояние | Расход бензина | Масса груза | ||
| 1 октября | Липки | Березки | |||||
| 1 октября | Орехово | Дубки | |||||
| 1 октября | Осинки | Вязово | |||||
| 1 октября | Липки | Вязово |
В G2 запишем формулу =СУММЕСЛИ(B2:B371;"Липки";F2:F371)
· Упростить создание формул и свести к минимуму количество опечаток и синтаксических ошибок позволяет диалоговое окно Мастер функций.
· Нередко в текстах заданий ответ необходимо представить в определенном формате. Например, «Ответ на этот вопрос с точностью до двух знаков запишите …» (числовой с количеством десятичных знаков 2) или «Сколько процентов от общего числа дней года …» (процентный формат)
· Важной частью анализа данных является сортировка по возрастанию (для текста - от А до Я, для чисел - от наименьших к наибольшим) или по убыванию (для текста - от Я до А, для чисел - от наибольших к наименьшим).
Пример: Отсортируйте таблицу в порядке уменьшения результатов участников, то есть по количеству решенных задач, а при равном количестве решенных задач – по уменьшению суммы баллов, полученных участником. При этом первая строка таблицы, содержащая заголовки столбцов, должна остаться на своем месте.
| A | B | C | D | E | F | G | H | I | |
| Фамилия | Имя | Класс | Зад. 1 | Зад. 2 | Зад. 3 | Зад. 4 | Решено задач | Сумма баллов | |
| Корнеев | Сергей | ||||||||
| Васильев | Игорь | ||||||||
| Лебедев | Николай |
Задача 1 (демо ГИА 2014):
В электронную таблицу занесли данные о калорийности продуктов. Ниже приведены первые пять строк таблицы.
| A | B | C | D | E | |
| Продукт | Жиры, г | Белки, г | Углеводы, г | Калорийность, Ккал | |
| Арахис | 45,2 | 26,3 | 9,9 | 552,0 | |
| Арахис жареный | 52,0 | 26,0 | 13,4 | 626,0 | |
| Горох отварной | 0,8 | 10,5 | 20,4 | 130,0 | |
| Горошек зеленый | 0,2 | 5,0 | 8,3 | 55,0 |
В столбце A записан продукт; в столбце B – содержание в нём жиров; в столбце C – содержание белков; в столбце D – содержание углеводов и в столбце Е – калорийность этого продукта.
Всего в электронную таблицу были занесены данные по 1000 продуктам.
Выполните задание:
Откройте файл «Таблицы для выполнения тренировочных заданий 19.xls», лист «Задача 1». На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
1. Сколько продуктов в таблице содержат меньше 50 г углеводов и меньше 50 г белков? Запишите число этих продуктов в ячейку H2 таблицы.
2. Какова средняя калорийность продуктов с содержанием жиров менее 1 г? Ответ на этот вопрос запишите в ячейку H3 таблицы с точностью не менее двух знаков после запятой.
Решение(для Microsoft Excel)
Сначала в столбец F для каждого продукта запишем логическое значение О (ложь) или 1 (истина) в зависимости от того, выполняется ли для него условие первого вопроса. Для этого в ячейку F2 запишем формулу, использующую условную функцию со сложным логическим условием =ЕСЛИ(И(D2<50;C2<50);1;0). Скопируем ячейку F2 в буфер обмена, выделим блок F3:F1001 и вставим в этот блок содержимое буфера обмена. Благодаря использованию относительных ссылок, в столбце F для строк 2-1001 будет записан признак того, выполнено ли для данного продукта условие первого вопроса. Для того чтобы подсчитать количество таких продуктов, в ячейку H2 запишем формулу =СУММ(F2:F1001)
Второй вариант ответа на первый вопрос:
в ячейку F2: =ЕСЛИ(И(D2<50;C2<50);ИСТИНА;ЛОЖЬ)
в ячейку H2: =СЧЁТЕСЛИ(F2:F1001;ИСТИНА)
Для ответа на второй вопрос «Какова средняя калорийность продуктов с содержанием жиров менее 1 г» мы должны:
1) найти сумму калорийностей тех продуктов, содержание жиров в которых <1; запишем формулу =СУММЕСЛИ(B2:B1001;"<1";E2:E1001), например, в ячейку G2.
2) найти количество продуктов, содержание жиров в которых <1, используя формулу =СЧЁТЕСЛИ(B2:B1001;”<1”), например в ячейке G3.
3) найти отношение первого значения ко второму, записав в ячейку H3 формулу =G2/G3.
Ответ на второй вопрос можно найти сразу, записав в ячейку H3 формулу
=СУММЕСЛИ(B2:B1001;”<1”;E2:E1001)/СЧЁТЕСЛИ(B2:B1001;”<1”)
Ответ на этот вопрос должен быть записан с точностью не менее двух знаков после запятой, не забудьте установить соответствующие настройки числового формата ячейки H3.
Другой способ решения задачи на вопрос №2:
Сначала в столбец I для каждого продукта запишем логическое значение О (ложь) или 1 (истина) в зависимости от того, выполняется ли для него условие второго вопроса. Для этого в ячейку I2 запишем формулу =ЕСЛИ(B2<1;1;0). Скопируем ячейку I2 в буфер обмена, выделим блок I3:I1001 и вставим в этот блок содержимое буфера обмена. Благодаря использованию относительных ссылок, в столбце I для строк 2-1001 будет записан признак того, выполнено ли для данного продукта условие второго вопроса. Для того чтобы подсчитать количество таких продуктов, в ячейку I1002 запишем формулу =СЧЁТЕСЛИ(I2:I1001;1).
Затем в столбец J запишем для каждого продукта его калорийность, если он удовлетворяет условию второго вопроса, или значение 0, если он не удовлетворяет условию. Для этого в ячейку J2 запишем формулу =E2*I2. Скопируем ячейку J2 в буфер обмена, выделим блок JЗ:J1001 и вставим в этот блок содержимое буфера обмена. Благодаря использованию относительных ссылок, в столбце J для строк 2-1001 будет записана калорийность продуктов, для которых выполнено условие второго вопроса, и число 0 для остальных продуктов. Суммарная калорийность продуктов, содержание жиров в которых <1, найдем в ячейке J1002 по формуле =СУММ(J2:J1001). Для получения ответа на второй вопрос в ячейку H3 запишем формулу = J1002/ I1002.
Задача 2:
После проведения олимпиады по информатике жюри олимпиады внесло результаты всех участников олимпиады в электронную таблицу. На рисунке приведены первые строчки получившейся таблицы:
| A | B | C | D | E | F | G | |
| Фамилия | Имя | Класс | Зад. 1 | Зад. 2 | Зад. 3 | Зад. 4 | |
| Корнеев | Сергей | ||||||
| Васильев | Игорь | ||||||
| Лебедев | Николай |
В столбце А электронной таблицы записана фамилия участника, в столбце В – имя участника, в столбце С – класс, в котором учится участник, в столбцах D, E, F и G – оценки каждого участника по четырем задачам, предлагавшимся на олимпиаде. Всего в электронную таблицу были занесены результаты 1000 участников.
По данным результатам жюри хочет определить победителя и лучших участников олимпиады. Победитель и лучшие участники определяется по количеству полностью решенных задач, а при равенстве количества решенных задач – по сумме набранных баллов по всем задачам (чем больше сумма баллов при равном числе решенных задач, тем выше участник стоит в таблице). Задача считается полностью решенной, если за нее стоит 9 или 10 баллов.
Выполните задание:
Откройте файл «Таблицы для выполнения тренировочных заданий 19.xls», лист «Задача 2». Для каждого участника посчитайте количество решенных им задач и сумму набранных баллов. После этого отсортируйте данную таблицу в порядке уменьшения результатов участников, то есть по количеству решенных задач, а при равном количестве решенных задач – по уменьшению суммы баллов, полученных участником. При этом первая строка таблицы, содержащая заголовки столбцов, должна остаться на своем месте.
Решение:
Решение для Microsoft Excel
Сначала в столбец Н запишем количество задач, полностью решённых участником. Для этого в ячейку H1 запишем заголовок столбца «Решено задач» и запишем в ячейку Н2 формулу =СЧЁТЕСЛИ(D2:G2;">=9"). Скопируем ячейку Н2 в буфер обмена, выделим блок НЗ:Н1001 и вставим в этот блок содержимое буфера обмена. Благодаря использованию относительных ссылок в столбце Н для строк 2-1001 будет записано количество верно решённых задач для каждого участника.
Затем в столбец I запишем сумму баллов, набранных каждым участников. Для этого в ячейку I1 запишем заголовок столбца «Сумма баллов», в ячейку I2 запишем формулу для подсчёта суммы баллов для участника в строке 2: =СУММ(D2:G2). Скопируем ячейку I2 в буфер обмена, выделим блок I3:I1001 и вставим в этот блок содержимое буфера обмена.
Мы подсчитали для каждого участника количество решённых им задач (в столбце Н) и сумму набранных им баллов (в столбце I).
Выделим таблицу и, зафиксировав заголовки в первой строке таблицы, отсортируем всю таблицу по убыванию количества решённых задач (столбец Н), а при равных значениях в столбце Н — по убыванию суммы баллов (столбец I). После сортировки в верхней строке (строка 2) будет содержаться победитель олимпиады, а в последующих строках — призёры олимпиады.