Задача № 2. Обработка табличных данных
Условиезадачи
Составить таблицу, содержащую информацию о десяти абитуриентах, поступающих в вуз. Для каждого абитуриента известны результаты ЕГЭ (математика, физика или информатика), результаты сдачи экзамена по русскому языку («зачет» «незачет»). Определить:
· сумму набранных баллов по результатам ЕГЭ, сумму баллов у допущенных к участию в конкурсе на основании результатов экзамена по русскому языку, процент набранных каждым участником баллов по отношению к максимальному значению набранных баллов. Построить гистограмму сумм набранных абитуриентами баллов по результатам сдачи ЕГЭ.
· средний балл, максимальный и минимальный баллы по предметам и по сумме набранных баллов по результатам ЕГЭ.
· количество абитуриентов, сдававших ЕГЭ по каждому предмету, количество имеющих «зачет» по русскому языку.
При решении задачи не допускается использование в MS Excel автофильтра и расширенного фильтра.
Решение
На листе 2 рабочей книги MS Excel составим таблицу, показанную на рисунке 2, предварительно переименовав лист и присвоив ему имя «Задача 2».
Рисунок 2 – Таблица вычисления суммы числового ряда
В ячейку A1 введем название таблицы – «Абитуриенты» и отформатируем его: объединим ячейки интервала A1:H1; выровним текст по центру; изменим размер и начертание шрифта.
Первый столбец таблицы (столбец A) – «Фамилия». Введем в ячейку A3 текст «Абитуриент 1», а затем с использованием технологию заполнения ячеек рядом значений заполним интервал ячеек A3:A12.
Столбцы с заголовками «Математика», «Физика» и «Информатика» заполним числовыми данными, отражающими результаты сдачи абитуриентами ЕГЭ по предметам. В Столбце «Русский язык» разместим результаты сдачи экзамена по русскому языку («зачет» «незачет»).
Для расчета суммы набранных баллов по результатам ЕГЭ в ячейке F3 разместим формулу =СУММ(B3:D3). Скопируем эту формулу в интервал ячеек F4:F12.
Для расчета суммы баллов у допущенных к участию в конкурсе на основании результатов экзамена по русскому языку в ячейке G3 разместим формулу =ЕСЛИ(E3="зачет";F3;0). Скопируем эту формулу в интервал ячеек G4:G12.
Определим процент набранных каждым участником баллов по отношению к максимальному значению набранных баллов. Для этого в ячейку H3 поместим формулу =G3/$F$14 и скопируем ее в интервал ячеек H4:H12.
Вычислим средний балл, максимальный и минимальный баллы по предметам и по сумме набранных баллов по результатам ЕГЭ. Для этого выполним следующие действия.
В ячейке B13 разместим формулу =СРЗНАЧ(B3:B12) для вычисления среднего значения результатов ЕГЭ по математике. Скопируем эту формулу в ячейки C13, D13 и F13.
В ячейке B14 разместим формулу =МАКС(B3:B12) для вычисления максимального значения результатов ЕГЭ по математике. Скопируем эту формулу в ячейки C14, D14 и F14.
В ячейке B15 разместим формулу =МИН(B3:B12) для вычисления минимального значения результатов ЕГЭ по математике. Скопируем эту формулу в ячейки C15, D15 и F15.
Для расчета количества абитуриентов, сдававших ЕГЭ, используем функцию суммирования ячеек в соответствии с заданным условием. Для этого в ячейке B16 разместим формулу =СЧЁТЕСЛИ(B3:B12;">0"). Скопируем эту формулу в ячейки C16, D16.
Для расчета количества абитуриентов, имеющих «зачет» по русскому языку также, используем функцию суммирования ячеек в соответствии с заданным условием. Для этого в ячейке E16 разместим формулу =СЧЁТЕСЛИ(E3:E12;"зачет").
Отформатируем таблицу, используя различные типы линий для обозначения границ как показано на рисунке 2.
Построим на этом же листе (лист «Задача 2») гистограмму сумм набранных абитуриентами баллов по результатам сдачи ЕГЭ. Для построения гистограммы выделим в таблице два несвязанных интервала ячеек A3:A12 и F3:F12. Несвязанные интервалы можно выделить используя манипулятор «мышь» при нажатой клавише «CTRL». После выделения интервалов запустим мастер диаграмм и выполняя шаги мастера, сформируем диаграмму, как показано на рисунке 3.
Рисунок 3 – Гистограмма сумм набранных баллов
Установим защиту на ячейки, содержащие формулы, название таблицы, а также на ячейки заголовков таблиц (на рисунке 2 область исходных данных, в которой ячейки не должны быть защищены, выделена сплошной двойной линией). Выделим интервал ячеек A3:E12 и отменим блокировку ячеек (меню «Формат/ячейки, вкладка «Защита», убрать ключ «Защищаемая ячейка»). Защитим лист рабочей книги от изменений (меню «Сервис/Защита/Защитить лист»).