Лабораторная работа. Создание электронного журнала успеваемости
Создание электронного журнала в MS Excel (LibreOffice.Calc)
Цель работы: создать таблицу для подсчета статистики успеваемости каждого ученика (студента). Для работы вам потребуется один документ с тремя рабочими листами. Обратите внимание, что в ходе выполнения заданий вы должны:
- отработать некоторые приемы работы с комбинированными, сложными функциями, массивами;
- научиться строить связанные графики.
Принцип работы в табличных редакторах разных разработчиков одинаков. Отличия по работе в MS Excel и Libre(Open)Office.Calc в методических указаниях прописываются рядом с соответствующим пунктом, например, п.4 и п.4.1 или выделяются разным цветом.
Отчет по работе необходимо выложить в блоге, используя облачные хранилища.
Задание 1. Заполнение Листа1
Создать список учащихся (студентов) из десяти произвольных фамилий, включая свою. После выполнения действий п. 1-5 у вас должна получиться таблица, аналогичная приведенной на рисунке 1.
Рисунок . Список студентов группы
Для этого выполните следующие действия.
На Листе1 создайте надпись «Список студентов». Оформление выберите на свое усмотрение. Заполните строку 3 (шапку таблицы). Вместо графы «Телефон» можете вписать любой другой пункт, например, адрес электронной почты, адрес проживания, размер ноги и т.д.
Заполните столбец А (порядковый номер №), с помощью команды автозаполнение. В графе «Факультет» укажите название своего факультета (если название длинное, можно вписать аббревиатуру), а в графе «Группа» - номер своей группы: 126 - цифра 1 – номер курса, цифра 2 – номер потока, цифра 6 – номер группы на потоке. Скопируйте данные на весь столбик E и F (10 позиций). Произвольными данными заполните столбец «Телефон».
В ячейках B20:B30 создайте список студентов (10 человек). Выполните разделение списка на два столбца. Для этого: Данные – Текст по столбцам. В диалоговом окне разделения текста оставьте формат данных с разделителем. На втором шаге поставьте галочку в поле «Пробел». На третьем шаге в поле «Поместить в» мышью выделите ячейки C4:D13. Нажмите OK.
Заполните данные в столбце «Идентификатор студента». Для этого в ячейку B4 введите формулу =СЦЕПИТЬ(F4;"-";A4). В результате этих действий соединяются текстовые данные из ячейки «Номер группы» и «Порядковый номер». В качестве разделителя мы указали дефис. Вы можете выбрать свой символ разделителя, например, нижнее подчеркивание или «&» или др. Скопируйте формулу на весь список.
В Libre(Open)Office.Calc вам следует выбрать из категории «Текстовые» функцию =CONCATENATE(F4;"-";A4). В поле «Текст 2» укажите в кавычках дефис, который разделит номер группы и порядковый номер в списке.
В ячейке H4 вы снова совместите фамилию и имя студента используя формулу =СЦЕПИТЬ(C4;" ";D4). Обратите внимание, что в кавычках указан один пробел. Скопируйте формулу на весь список.
В Libre(Open)Office.Calc вам следует выбрать из категории «Текстовые» функцию =CONCATENATE(C4;"-";D4). В поле «Текст 2» укажите в кавычках пробел, который разделит фамилию и имя.
Задание 2. Заполнение Листа2
В первой строке сделайте заголовок таблицы, например, «Таблица успеваемости студентов группы...». Выделите несколько ячеек этой строчки и объедините их, нажав на кнопку  . Выберите произвольный стиль оформления своего заголовка.
Заполните шапку таблицы. Цветовое и шрифтовое оформление выберите на ваш вкус. Заполните столбец «№ п/п», используя функцию автозаполнения.
3. Заполните ячейки "дата проведения занятий" (D3 ÷ H3 ... ):
установите формат ячеек D3 ÷ H3 - категория - "дата", формат "31 дек.99" (или свой формат)
В ячейках D3 и E3 введите две даты с интервалом в одну неделю, например, D3 - 01.09.13; E3 - 07.09.13.
с помощью команды автозаполнения заполните все остальные ячейки на любые ДВА месяца. В нашем примере указан только один месяц.
измените формат всех этих ячеек (D3 ÷ H3): разверните текст на 90 градусов и установите выравнивание по середине и по горизонтали и по вертикали (Формат – Ячейка - Выравнивание)
отформатируйте ширину столбцов:
MS Excel: Формат – Столбец – Автоподбор ширины.
LibreOffice.Calc: Формат – Столбец – Ширина. Установите ширину столбцов D ÷ H равную 0,8 – 1,0.
5. Вернитесь на Лист 2. В столбце “Идентификатор студента» создайте выпадающие списки с номером студента. Для этого:
- выделите диапазон B3 – B12, затем: Данные – Проверка данных.
MS Excel 2010-2013: Тип данных – Список. В поле Источник введите выделенный диапазон идентификатора студентов с Листа 1. OK. Затем заполните поля на вкладках Сообщение для ввода и Сообщение об ошибке.
На вкладке Сообщение для ввода в поле Заголовок укажите свои фамилию и имя, а в поле Сообщение, например «Выберите данные из списка» или другое сообщение. Оставьте галочку в поле Отображать подсказку, если ячейка является текущей.
На вкладке Сообщение об ошибке в поле Заголовок укажите факультет и группу на потоке, например, ППФ21, а в поле Сообщение об ошибке наберите предупреждение о совершенной пользователем ошибке при выборе варианта ответа.
MS Excel 2003: обратите внимание, что данные для Источника должны быть на одном листе с выбранной ячейкой. Поэтому рекомендуется продублировать на листе 2 в любом свободном месте столбец с идентификаторами студентов. В более старших версиях MS Excel и в Open(Libre)Office можно данные брать с разных листов.
Libre(Open)Office.Calc: Данные – Проверка данных. В поле Разрешить – Список. В поле Элементы укажите диапазон данных с листа 1 ячейки B3-B13, т.е. идентификаторы студентов (см. рис. ниже).
Затем заполните вкладки Помощь при вводе и Действия при ошибке (рекомендации см. в описании этого пункта к MS Excel 2010-2013).
После этого рядом со всеми выделенными ячейками появится кнопка выбора варианта.
В ячейке C3 должна появляться фамилия студента в соответсвии с его личным номером. Используйте формулу Поиск по вертикали:
MS Excel: категория Ссылки и массивы - ВПР
Libre(Open)Office.Calc – Категория - Электронные таблицы – VLOOKUP (см. рис. ниже)
В первом поле введите адрес ячейки B3 (Лист 2). Во втором поле укажите диапазон всей таблицы с Листа 1 (ячейки B4 ÷ H13). В третьем поле диалогового окна функции укажите номер столбца из выделенного вами диапазона, откуда необходимо выбрать данные. В нашем примере мы должны поместить Фамилию и имя из столбца H. Порядковый номер этого столца в нашем выделении 7. Это число и нужно указать в поле Номер столбца.
=ВПР(B3;Лист1!B$4:H$13;7)
=VLOOKUP(B3;Лист1.В$4:H$13;7)
Скопируйте формулу на весь необходимый диапазон, используя автозаполнение ячеек.
В ячейке L3 подсчитайте средний балл по тесту, выбрав функцию СРЗНАЧ и выделив диапазон числовых данных по тесту. В нашем примере =СРЗНАЧ(I3:K3) (категория Статистические) или =AVERAGE(I3:K3). Скопируйте формулу на весь необходимый диапазон, используя автозаполнение ячеек.
В ячейке L7 подсчитайте, сколько осталось написать тестов студенту, используя условие, что ячейки с результатами теста не должны содержать «0», «н», « »:
=СЧЁТЕСЛИ(I3:K3;"")+СЧЁТЕСЛИ(I3:K3;0)+СЧЁТЕСЛИ(I3:K3;"н")
=COUNTIF(I3:K3;"")+COUNTIF(I3:K3;0)+COUNTIF(I3:K3;"н")
В категории Статистические находится функция {СЧЁТЕСЛИ()}, которая позволяет сосчитать число значений внутри диапазона, удовлетворяющих заданному критерию. Синтаксис данной функции:
= СЧЁТЕСЛИ (диапазон;критерий)
Где диапазон - это диапазон ячеек, в котором нужно сосчитать число значений, удовлетворяющих заданному критерию; критерий - критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать.
Например:
Функция = СЧЁТЕСЛИ (A1:A7;32) - подсчитывает число значений равных 32 в диапазоне ячеек A1-A7. В кавычки надо заключать текст (например, = СЧЁТЕСЛИ(A1:A7;"яблоки") - будут сосчитаны все ячейки, содержащие слово - яблоки).
Для подсчета суммарного балла используйте функцию автосуммирования по строке.
Рассчитайте ранг студента в общем списке.
Функция РАНГ() (RANK) категория Статистические вычисляет ранг значения в выборке (распределения участников по местам). Функция РАНГ() имеет три аргумента.
Первый – число, место (ранг) которого определяется. Второй аргумент ссылка – диапазон, в котором происходит распределение по местам. В нашем примере это столбец с суммарно набранным баллом. Диапазон должен быть неизменным, следовательно, его нужно указать с помощью абсолютной адресаций. Третий аргумент - Порядок – указатель порядка сортировки. Если третий аргумент 0 или не указан, места распределяются по убыванию значений (т.е. чем больше – тем лучше, 1-е место – максимальное значение). Если же поставить 1, то места будут распределяться по возрастанию (т.е. чем меньше, тем лучше). 
Логическая функция условие: ЕСЛИ() (IF)
Для формирования условий в формулах используется функция ЕСЛИ(). Она имеет три аргумента. Первый аргумент тест – условие, второй аргумент тогда значение – действия которое совершается при выполнении условия, третий аргумент иначе значение – действия при не выполнении условия.
Пусть, например, ячейка D5 содержит формулу "=ЕСЛИ (A1<100,С2*10,"н/у")". Если значение в ячейке A1 меньше 100, то D5 примет значение равное значению ячейки C2, умноженному на 10. Если же значение в клетке A1 не меньше 100, то ячейка D5 примет текстовое значение - н/у.
Обратите внимание на то, что в OpenOffice.Calc текстовое значение надо заключать в двойные кавычки! MS Excel кавычки подставляет автоматически.
11. Ниже таблицы в ячейки D13 ÷ К13 введите предполагаемое максимальное количество баллов за каждый вид заданий. В ячейке N13 выполните автосуммирование этих максимумов. Решите для себя, при каких условиях студент получит зачет. Например, зачет получает если набрал не менее 75% от общего количества баллов и сдал все тесты. В нашем примере формула будет следующей:
=ЕСЛИ(И(N3>=0,75*N$13;M3=0);"зачет";"нет")
=IF(AND(N3>=0,75*N$13;M3=0);"зачет";"нет")
В электронных таблицах возможно использование более сложных логических конструкций с использованием вложенных функций ЕСЛИ(), когда ЕСЛИ() используется в качестве аргумента другой функции ЕСЛИ(). Например, сложная функция
=ЕСЛИ(A1<100,"утро",ЕСЛИ(A1=100,"вечер",C1))
выполняет следующие действия: если значение в ячейке A1 меньше 100, то выводится текстовое значение "утро". В противном случае проверяется условие вложенной функции ЕСЛИ(). Если значение в ячейке A1 равно 100 выводится текстовое значение "вечер", иначе выводится значение из ячейки C1. Toт же результат может быть получен с помощью выражения:
=ЕСЛИ(A1<>100,ЕСЛИ(A1<100,"утро",C1),"вечер").
При создании сложных логических конструкций, особенно с большим количеством вложенных функций ЕСЛИ(), нередко возникают ошибки, связанные с неправильным синтаксисом логического выражения. Если в ячейке, содержащей формулу, вызвать "Мастер функций", то будет показана структура формулы. Структура формулы помогает найти ошибки при большом количестве вложенных функций.
12. Выполните условное форматирование столбцов «Тесты» и «Зачет», которое позволит в автоматическом режиме изменять цвет ячейки в зависимости от задаваемого правила. Например, если тест написан на 0 баллов, ячейка приобретает красный оттенок. Для этого создайте свои правила:
MS Excel 2003, Open(Libre)Office.Calc: Формат – Условное форматирование – Условие.
MS Excel 2010-2013: Главная – Условное форматирование – Правила выделения ячеек.
Затем попробуйте условное форматирование с использованием цветовой школы.
Задание 3. Подсчет статистики данных
12. Подсчитайте частоту появления результатов по тестам (0, 1, 2, 3), используя функцию ЧАСТОТА (категория Статистические) или FREQUENCY (из категории Массив).
Функция ЧАСТОТА()(категория Статистические)или FREQUENCY() (из категории Массив) служит для подсчета количества значений в массиве данных, соответствующих определенному классу. Функцией ЧАСТОТА() можно воспользоваться, например, для подсчета количества учащихся получивших - 5; 4; 3 и 2.
Ниже своей таблицы создайте фрагмент, аналогичный нижеприведенному:
В нашем примере первый столбик занимает позиции H17 ÷ H20. Это так называемый Массив интервалов (Классы).
выделить весь диапазон ячеек, в которых будет располагаться результат подсчёта частот, т.е. I17 ÷ I20.
Не снимая выделения вызвать вставку функции Частота.
В поле Массив данных (Классы) указать диапазон всех ячеек, содержащих результаты тестирования. В поле Массив интервалов (Классы) ввести диапазон, содержащий возможные варианты оценки тестирования в нашем случае H17 ÷ H20.
нажать сочетание клавиш Ctrl+Shift+Enter, чтобы вывелся массив чисел. Если этого не сделать, то будет выведен только один первый результат.
Добавьте условное форматирование к этому диапазону, выбрав опцию «Гистограмма»
Задание 4. Построение графика успеваемости
Постройте график успеваемости по столбцу БАЛЛ. Выделите столбец Фамилия и, удерживая клавишу Ctrl, столбец Балл. Вызовите мастер диаграмм и заполните ВСЕ вкладки и поля диалогового окна. Диаграмма должна быть ПОЛНОСТЬЮ оформлена (название диаграммы, подписи под осями, размерность осей и т.д.).
Задание 5. Заполнение листа 3
На Листе 3 сделайте свой вариант оформления шапки таблицы, например, похожий на приведенный ниже:
5.1. Объедините ячейки С1-W1, выровняйте содержимое ячейки по середине.
5.2. Объедините ячейки X1 и X2, Y1 и Y2. Введите в X - "средняя оценка", в Y - "итоговая оценка", разверните текст на 90 градусов, выровняйте по середине.
5.3. Разделите фамилию и имя в разные столбцы. Для этого выделите столбец B, далее Данные – Текст по столбцам. Заполните все поля диалогового окна.
5.4. Оформите таблицу, произвольным образом выбирая цвета ячеек, обрамление и т.д.
Итоговый отчет: Файл с выполненным заданием разместить в блоге, используя ссылку на облачное хранилище.
|
