Лабораторная работа Создание электронного журнала успеваемости20.09

Лабораторная работа 4
Создание электронного журнала успеваемости в MS Excel (LibreOffice.Calc)
Цель работы: создать таблицу для подсчета статистики успеваемости каждого студента. Для работы потребуется один документ с двумя рабочими листами.
Принцип работы в табличных редакторах разных разработчиков одинаков.
Задание 1. Заполнение листа 1
Создать список студентов из 10 произвольных фамилий, включая свою. После выполнения действий п.1-5 у вас должна получиться таблица, аналогичная представленной на рисунке 1.

Для этого выполните следующие действия :
На Листе 1 создайте надпись «Список студентов». Оформление выберите на свое усмотрение. Заполните строку 3 (шапку таблицы).
Заполните столбец А (порядковый номер №) с помощью команды автозаполнение. В графе «Факультет » и «Группа» продублируйте данные. Столбец «Телефон» заполнить произвольно.
В ячейках H4:H13 создайте список студентов (10 чел), в одной ячейке фамилию и имя. Отсортируйте данные по алфавиту.
Выполните разделение списка на два столбца: Данные-Текст по столбцам. В диалоговом окне разделения текста оставьте формат данных с разделителем. Поставьте галочку в поле «Пробел». В поле «Поместить в» мышью выделить ячейки С4:D13. Нажмите ОК. Очистить столбец H/
Заполните данные «Идентификатор студента». Для этого в ячейку В4 введите формулу =СЦЕПИТЬ(F4;”-”;A4) (категория Текстовые) , в качестве разделителя используется дефис, можно использовать другой символ. Скопируйте формулу на весь список.
В OpenOffice.Calc вам следует выбрать из категории «Текстовые» функцию =CONCATENATE(F4;”-”;A4), в поле «Текст 2» укажите в кавычках дефис, который разделит номер группы и порядковый номер в списке.

Рисунок 2. Мастер Функций
В ячейке Н4 вы снова совместите фамилию и имя студента используя формулу =СЦЕПИТЬ(C4,” ”,D4)В кавычках указан один пробел. Скопировать на весь список
В OpenOffice.Calc вам следует выбрать из категории «Текстовые» функцию =CONCATENATE(С4;”-”;D 4), в поле «Текст 2» укажите в кавычках пробел, который разделит фамилию и имя.
Задание 2. Заполнение листа 2
Откройте Лист 2
В первой строке сделать заголовок таблицы, например «Таблица успеваемости студентов группы» . Объединить ячейки, использовать произвольный стиль оформления.
Заполнить шапку таблиц. Цветовое и шрифтовое оформление на ваш вкус. Заполнить столбец «№ п/п», используя функцию автозаполнения.

Рисунок 3. Таблица успеваемости группы
Заполните ячейки «дата проведения занятий» (D3:Н3), для этого:
Установите формат ячеек (D3:Н3) – категория – «дата» любой выбранный формат.
В ячейках D3:E3 введите две даты с интервалом в одну неделю, например D3-01.09.17; E3-08.09.17.
Автозаполнением заполнить все остальные ячейки на любые два месяца.
Изменить формат всех этих ячеек (D3:H3): разверните текст на 90 градусов , выравнивание по середине и по горизонтали и по вертикали. ()
Отформатируйте ширину столбцов
MS Excel: Формат-Столбец- Автоподбор ширины.
В OpenOffice.Calc Формат-Столбец- Ширина. (Ширина столбцов D-H 0,8-1,0)
На листе 2 в столбце «Идентификатор студента» создайте выпадающие списки с номером студента. Для этого:
Выделите диапазон В3-В12, затем Данные-Проверка данных

MS Excel 2010-2013:Тип данных-Список. В поле Источник введите выделенный диапазон идентификатора студентов с Листа 1. ОК.(=Лист1!диапазон) Затем заполните поля на вкладках Сообщение для ввода и Сообщение об ошибке.
На вкладке Сообщение для ввода в поле Заголовок укажите свою фамилию и имя, а в поле Сообщение, например «Выберите данные из списка.» Оставьте га
дочку в поле Отображать подсказку, если ячейка является текущей.
На вкладке Сообщение об ошибке наберите предупреждение о совершенной пользователем ошибке при выборе варианта ответа.
MS Excel 2003 данные для источника должны быть на одном листе с выбранной ячейкой. Поэтому рекомендуется продублировать на Листе 2 в любом свободном месте столбец с идентификаторами студентов. В более старших версиях MS Excel OpenOffice можно брать данные с разных листов.

OpenOffice.Calc : Данные-Проверка данных. В поле Разрешить-Список. В поле Элементы укажите диапазон данных с листа 1 ячейки В3:В13,т.е. идентификаторы студентов.
Заполните вкладки Помощь при вводе и Действия при ошибках.(см. выше)

Рис
После этого рядом со всеми выделенными ячейками появится кнопка выбора варианта.
В ячейке С3 должна появиться фамилия студента, в соответствии с его личным номером. Используйте формулу Поиск по вертикали:
MS Excel категория Ссылки и массивы-ВПР

OpenOffice.Calc : Категория-Электронные таблицы-VLOOKUP
В первом поле введите адрес ячейки В3 (Лист2). Во втором поле укажите диапазон всей таблицы с Листа 1(В4:Н13). В третьем поле № столбца из выделенного диапазона, откуда берутся данные.ФИ из столбца Н. Порядковый № =7, это число указываем в поле Номер столбца.
=ВПР(В3;Лист1!В$4:H$13;7)
=VLOOKUP(B3; Лист1!В$4:H$13;7)
Скопировать формулу на весь диапазон, используя Автозаполнение.

В ячейке L3 подсчитать средний балл по тесту, ф-я СРЗНАЧ и выделив диапазон числовых данных по тесту. =СРЗНАЧ(I3:K3) или AVERAGE(I3:K3). Скопируйте на весь диапазон
В ячейке L7пожсчитайте, сколько осталось написать тестов студенту, используя условие, что ячейки результатов не должны содержать «0», «н», «»:
СЧЕТЕСЛИ(I3:K3;””)+ СЧЕТЕСЛИ(I3:K3;”н”)+ СЧЕТЕСЛИ(I3:K3;0
=COUNTIF(I3:K3;””)+COUNTIF(I3:K3;0)+COUNTIF(I3:K3;”и”)
В категории Статические находится функция СЧЕТЕСЛИ(Iдиапазон;критерий), где диапазон-диапазон ячеек, в котором нужро сосчитать число значений, удовлетворяющих заданному критерию, критерий в форме числа, выражения или текста, который определяет какие ячейки надо подсчитывать. В кавычках- текст , например СЧЕТЕСЛИ(I3:K3;”яблоки”),-будут сосчитаны все ячейки, содержащие слово -яблоки.
Для подсчета суммарного балла-используйте функцию автосуммирование по строке.
Рассчитать ранг студента в общем списке.
Функция РАНГ() RANK – категория Статические вычисляет ранг значения в выборке (распределение участников по местам)

РАНГ() имеет три аргумента.
Первый- число, место (ранг) которого определяется. Второй аргумент-ссылка- диапазон, в котором происходит распределение по местам. В нашем примере это .столбец с суммарно набранным баллом
Диапазон д.б. Неизменным, следовательно его нужно указать с помощью абсолютной адресации.
Третий аргумент Порядок указатель сортировки, если 0 или не указан-места распределяются по убыванию значений (1 место- мах. Значение), если поставить 1- места по возрастанию (чем меньше тем лучше).
Логическая функция условие : ЕСЛИ() (IF)
Функция ЕСЛИ() (IF)- для формирования условий в формулах. Имеет три аргумента : (условие, тогда значение, иначе значение ).
Пусть, например, в ячейке D5 записана формула: ”=ЕСЛИ(А1<100,C2*10,”н/”)”,Если значение в ячейке А1<100, то D5=C2*10, иначе D5=н/у
В OpenOffice.Calc текстовое значение заключается в двойные кавычки, MS Excel кавычки подставляет автоматически.
Ниже таблицы в ячейки D13:K13 введите предполагаемое максимальное количество баллов за каждый вид заданий. В ячейке N13- автосуммирование максимумов. Студент получает зачет, если набрал не менее 75% баллов и сдал все тесты. Формула будет следующей:
=ЕСЛИ(И(N3>=0,75*N$13; M3=0);”зачет”;”нет”),
=IF(AND(N3>=0,75*N$13; M3=0);”зачет”;”нет”),

Рисунок Аргументы функции ЕСЛИ

Выполните условное форматирование столбцов «Тесты» и «Зачет», которое позволяет в автоматическом режимеизменять цвет ячейкив зависимости от задаваемого правила. Например,если тест написан на 0 баллов, ячейка приобретает красный оттенок. Для этого создайте свои правила:
MS Excel 2003, OpenOffice.Calc: Формат - Условное форматирование – Условие
MS Excel 2010- 2013: Главная – Условное форматирование – Правила выделения ячеек
Затем попробуйте условное форматирование с использованием цветовой шкалы.

Рисунок Условное форматирование
Задание 3. Подсчет статистики данных
Подсчитать частоту появления результатов по тестам (0,1,2,3), используя функцию ЧАСТОТА (категория Статистические) или FREQUENCY (из категории Массив)
Функция ЧАСТОТА ()(категория Статистические) или FREQUENCY() (из категории Массив) служит для подсчета значений в массиве данных, соответствующих определенному классу. Функцией ЧАСТОТА() можно воспользоваться , например, для подсчета количества учащихся, получивших – 5; 4; 3; 2.
Ниже своей таблицы создайте фрагмент, аналогичный нижеприведенному:

В нашем примере первый столбик занимает позиции H17:H20. Это так называемый Массив интервалов (Классы).
Выделить весь диапазон ячеек в которых будет располагаться результат подсчета частот, т.е. I17:I20
Не снимая выделения вызвать вставку функции ЧАСТОТА
В поле Массив данных (Классы) указать диапазон всех ячеек, содержащих результаты тестирования. В поле Массив интервалов (Классы) ввести диапазон, содержащий возможные варианты оценки тестирования, в нашем случае H17:H20.

Рисунок Аргументы ф-ии
Нажать сочетание клавиш Ctrl+Shift+Enter, чтобы вывелся массив чисел. Если этого не сделать, то будет выведен один первый результат.
Добавьте условное форматирование к этому диапазону, выбрав опцию «Гистограмма».
Задание 4. Построение графиков успеваемости
Построить график успеваемости по столбцу БАЛЛ. Выделите столбец Фамилия и, удерживая клавишу Ctrl, столбец Балл. Вызовите мастер диаграмм и заполните ВСЕ вкладки и поля диалогового окна. Диаграмма должна быть полностью оформлена (название диаграммы, подписи под осями, размерность осей и т.д.)

Результат: Итоговый отчет. Файл с выполненным заданием.










13 PAGE \* MERGEFORMAT 14515




Рисунок 2 Заголовок 315

Приложенные файлы

  • doc 8750838
    Размер файла: 637 kB Загрузок: 0

Добавить комментарий