Как создать электронный журнал для оценок в excel

Лабораторная
работа 7.

I.      Создание
электронного журнала успеваемости в MS Excel

Цель работы: создать
таблицу для подсчета статистики успеваемости  каждого ученика (студента). Для
работы вам потребуется один документ с тремя рабочими листами. Обратите внимание,
что в ходе выполнения заданий вы должны:

— отработать
некоторые приемы работы с комбинированными, сложными функциями, массивами;

— научиться
строить связанные графики.

Рекомендуем для
заполнения формул использовать Мастер функций.

Задание 1.
Заполнение Листа 1

Создать список учащихся (студентов)
из десяти произвольных фамилий, включая свою. После выполнения действий п. 1-5
у вас должна получиться таблица, аналогичная приведенной на рисунке 1.

Рисунок 1. Список студентов группы

1. На Листе1 создайте надпись
«Список студентов». Оформление выберите на свое усмотрение. Заполните строку 3
(шапку таблицы). Вместо графы «Телефон» можете вписать любой другой пункт,
например, адрес электронной почты, адрес проживания и т.д.

2. Заполните столбец А (порядковый номер
No), с
помощью команды автозаполнение. В графе «Факультет» укажите название своего
факультета (если название длинное, можно вписать аббревиатуру), а в графе
«Группа» — номер своей группы: 126 — цифра 1 – номер курса, цифра 2 – номер
потока, цифра 6 – номер группы на потоке. Скопируйте данные на весь столбик E и F (10 позиций).
Произвольными данными заполните столбец «Телефон».

3. В ячейках B20:B30
создайте список студентов (10 человек), причем, в одной ячейке, например, B20, должны
быть написаны и фамилия и имя. Отсортируйте полученный список по алфавиту
(Данные – Сортировка).

4. Затем выполните разделение
списка на два столбца. Для этого: Данные – Текст по столбцам. В
диалоговом окне разделения текста оставьте формат данных с разделителем. На
втором шаге поставьте галочку в поле «Пробел». На третьем шаге в поле
«Поместить в» мышью выделите ячейки C4:D13. Нажмите OK.

5. Заполните данные в столбце
«Идентификатор студента». Для этого в ячейку B4 введите формулу
=СЦЕПИТЬ(F4;»-«;A4). В
результате этих действий соединяются текстовые данные из ячейки «Номер группы»
и «Порядковый номер». В качестве разделителя мы указали дефис. Вы можете
выбрать свой символ разделителя, например, нижнее подчеркивание или «&» или
др. Скопируйте формулу на весь список.

6. В ячейке H4 вы снова
совместите фамилию и имя студента используя формулу =СЦЕПИТЬ(C4;»
«;D4).
Обратите внимание, что в кавычках указан один пробел.
Скопируйте формулу на весь список.

Задание 2. Заполнение
Листа 2

1. В первой строке сделайте
заголовок таблицы, например, «Таблица успеваемости студентов группы…».
Выделите несколько ячеек этой строчки и объедините их, нажав на кнопку. Выберите произвольный стиль
оформления своего заголовка.

2. Заполните шапку таблицы.
Цветовое и шрифтовое оформление выберите на ваш вкус. Заполните столбец «No п/п»,
используя функцию автозаполнения.

3. Заполните ячейки «дата
проведения занятий» (D3 — H3 … ):

 — установите формат ячеек D3 — H3 —
категория — «дата», формат «31 дек.99» (или свой формат)

 — В ячейках D3 и E3 введите
две даты с интервалом в одну неделю, например, D3 — 01.09.13; E3 —
07.09.13.

— с помощью команды автозаполнения
заполните все остальные ячейки на любые ДВА месяца. В нашем примере указан
только один месяц.

— измените формат всех этих ячеек (D3 — H3): разверните
текст на 90 градусов и установите выравнивание по середине и по горизонтали и
по вертикали (Формат – Ячейка — Выравнивание)

— отформатируйте ширину столбцов:  MS Excel: Формат
– Столбец – Автоподбор ширины.

5. Вернитесь на Лист 2. В столбце “Идентификатор
студента» создайте выпадающие списки с номером студента. Для этого:-
выделите диапазон B3 – B12, затем:
Данные – Проверка данных.

MS Excel 2010-2013: Тип
данных – Список. В поле Источник введите выделенный диапазон идентификатора
студентов с Листа 1. OK. Затем
заполните поля на вкладках Сообщение для ввода и Сообщение об ошибке.

На вкладке Сообщение для
ввода в поле Заголовок укажите свои фамилию и имя, а в поле Сообщение,
например «Выберите данные из списка» или другое сообщение. Оставьте галочку в
поле Отображать подсказку, если ячейка является текущей.

На вкладке Сообщение об ошибке
в поле Заголовок укажите факультет и группу на потоке, например, ППФ21,
а в поле Сообщение об ошибке наберите предупреждение о совершенной
пользователем ошибке при выборе варианта ответа.

MS Excel 2003: обратите внимание,
что данные для Источника должны быть на одном листе с выбранной ячейкой.
Поэтому рекомендуется продублировать на листе 2 в любом свободном месте столбец
с идентификаторами студентов. В более старших версиях MS Excel можно
данные брать с разных листов.

После этого рядом со всеми
выделенными ячейками появится кнопка выбора варианта.

7. В ячейке C3 должна появляться
фамилия студента в соответсвии с его личным номером. Используйте формулу Поиск
по вертикали: категория Ссылки и массивы –ВПР

В первом поле введите адрес ячейки
B3 (Лист 2). Во втором поле укажите диапазон всей таблицы с Листа 1 (ячейки B4
— H13). В третьем поле диалогового окна функции укажите номер столбца из
выделенного вами диапазона, откуда необходимо выбрать данные. В нашем примере
мы должны поместить Фамилию и имя из столбца H. Порядковый номер этого
столца в нашем выделении 7. Это число и нужно указать в поле Номер столбца.

=ВПР(B3;Лист1!B$4:H$13;7)

Скопируйте формулу на весь необходимый
диапазон, используя автозаполнение ячеек. 8. В ячейке L3 подсчитайте
средний балл по тесту, выбрав функцию СРЗНАЧ и выделив диапазон числовых данных
по тесту. В нашем примере =СРЗНАЧ(I3:K3) (категория Статистические) или
=AVERAGE(I3:K3). Скопируйте формулу на весь необходимый диапазон, используя
автозаполнение ячеек.

9. В ячейке L7 подсчитайте,
сколько осталось написать тестов студенту, используя условие, что ячейки с
результатами теста не должны содержать «0», «н», « »:

=СЧЁТЕСЛИ(I3:K3;»»)+СЧЁТЕСЛИ(I3:K3;0)+СЧЁТЕСЛИ(I3:K3;»н»)

В
категории Статистические находится функция {СЧЁТЕСЛИ()}, которая
позволяет сосчитать число значений внутри диапазона, удовлетворяющих заданному
критерию. Синтаксис данной функции: = СЧЁТЕСЛИ (диапазон;критерий)
Где диапазон — это диапазон ячеек, в котором нужно сосчитать число значений,
удовлетворяющих заданному критерию; критерий — критерий в форме числа,
выражения или текста, который определяет, какие ячейки надо подсчитывать.
Например: Функция = СЧЁТЕСЛИ (A1:A7;32) — подсчитывает число значений
равных 32 в диапазоне ячеек A1-A7. В кавычки надо заключать текст (например,  =
СЧЁТЕСЛИ(A1:A7;»яблоки») — будут сосчитаны все ячейки, содержащие
слово — яблоки).

10.Для подсчета суммарного балла
используйте функцию автосуммирования по строке.

11. Рассчитайте ранг студента в
общем списке.

Функция
РАНГ() (RANK) категория Статистические вычисляет ранг значения в выборке
(распределения участников по местам). Функция РАНГ() имеет три аргумента.
Первый – число, место (ранг) которого определяется. Второй аргумент ссылка –
диапазон, в котором происходит распределение по местам. В нашем примере это
столбец с суммарно набранным баллом. Диапазон должен быть неизменным,
следовательно, его нужно указать с помощью абсолютной адресаций. Третий
аргумент — Порядок – указатель порядка сортировки. Если третий аргумент 0 или
не указан, места распределяются по убыванию значений (т.е. чем больше – тем
лучше, 1-е место – максимальное значение). Если же поставить 1, то места будут
распределяться по возрастанию (т.е. чем меньше, тем лучше).

Логическая функция условие: ЕСЛИ()
(IF)

Для
формирования условий в формулах используется функция ЕСЛИ(). Она имеет три
аргумента. Первый аргумент тест – условие, второй аргумент тогда значение –
действия которое совершается при выполнении условия, третий аргумент иначе
значение – действия при не выполнении условия.Пусть, например, ячейка D5
содержит формулу «=ЕСЛИ (A1<100,С2*10,»н/у»)». Если
значение в ячейке A1 меньше 100, то D5 примет значение равное значению ячейки
C2, умноженному на 10. Если же значение в клетке A1 не меньше 100, то ячейка D5
примет текстовое значение — н/у.

11. Ниже таблицы в ячейки D13
— К13 введите предполагаемое максимальное количество баллов за каждый
вид заданий. В ячейке N13 выполните автосуммирование этих максимумов. Решите
для себя, при каких условиях студент получит зачет. Например, зачет получает
если набрал не менее 75% от общего количества баллов и сдал все тесты. В нашем
примере формула будет следующей:

=ЕСЛИ(И(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: Формат
– Условное форматирование – Условие.

MS Excel 2010-2013: Главная –
Условное форматирование – Правила выделения ячеек.

Задание 3. Подсчет
статистики данных

12. Подсчитайте частоту появления результатов
по тестам (0, 1, 2, 3), используя функцию ЧАСТОТА (категория Статистические)
.

Функция ЧАСТОТА()(категория
Статистические) служит для подсчета количества значений в массиве
данных, соответствующих определенному классу. Функцией ЧАСТОТА() можно воспользоваться,
например, для подсчета количества учащихся получивших — 5; 4; 3 и 2.

Ниже своей таблицы создайте
фрагмент, аналогичный нижеприведенному:

В нашем примере первый столбик занимает позиции H17
— H20. Это так называемый Массив интервалов(Классы).

1) выделить весь диапазон ячеек, в
которых будет располагаться результат подсчёта частот, т.е. I17 — I20.

2) Не снимая выделения вызвать
вставку функции Частота.

3) В поле Массив данных (Классы)
указать диапазон всех ячеек, содержащих результаты тестирования. В поле Массив
интервалов (Классы) ввести диапазон, содержащий возможные варианты оценки
тестирования в нашем случае H17 — H20.

4) нажать сочетание клавиш
Ctrl+Shift+Enter, чтобы вывелся массив чисел. Если этого не сделать, то будет
выведен только один первый результат.

5) Добавьте условное форматирование
к этому диапазону, выбрав опцию «Гистограмма»

Задание 4.
Построение графика успеваемости

Постройте график успеваемости по
столбцу БАЛЛ. Выделите столбец Фамилия и, удерживая клавишу Ctrl, столбец
Балл. Вызовите мастер диаграмм и заполните ВСЕ вкладки и поля
диалогового окна. Диаграмма должна быть ПОЛНОСТЬЮ оформлена (название
диаграммы, подписи под осями, размерность осей и т.д.).

Задание 5.
Заполнение листа 3

На Листе 3 сделайте свой вариант оформления
шапки таблицы, например, похожий на приведенный ниже:

5.1. Объедините ячейки С1-W1,
выровняйте содержимое ячейки по середине.

5.2. Объедините ячейки X1 и X2, Y1
и Y2. Введите в X — «средняя оценка», в Y — «итоговая
оценка», разверните текст на 90 градусов, выровняйте по середине.

5.3. Разделите фамилию и имя в разные
столбцы. Для этого выделите столбец B, далее Данные – Текст по столбцам.
Заполните все поля диалогового окна.

5.4. Оформите таблицу, произвольным
образом выбирая цвета ячеек, обрамление и т.д.

II. Создание
электронного журнала успеваемости в проекте «SmileS.Школьная карта»

1.    Запустите
браузер, наберите в строке адреса https://www.shkolnaya-karta.ru/
и кликните ссылку Демо-версия. Зарегистрируйтесь на данном сайте, указав адрес
своей электронной почты.

2.    Открыв
полученное письмо перейдите по ссылке, выбрав одну из ролей (учитель, директор
и т.д.), ознакомьтесь с возможностями проекта.

3.    Оформите
электронный журнал экспортируя данные с сайта в MS Word и MS Excel. Сформировать
отчеты.

Опубликовано Umar
вкл 13.09.2020 — 11:46

Проекты. Электронный журнал оценок

В программу информатизации нашей школы, входит проект создания
электронного журнала оценок. На этой страничке речь пойдет о журнале созданном
с помощью программы Excel.

Журнал оценок также реализован с помощью программы Access.

Страница журнала оценок в Access.

Назначение. Журнал оценок предназначен для ведения
учета оценок в школе с помощью компьютера. В базу данных (список) Excel
вносятся оценки по предметам, классам, ученикам, датам. С помощью сводных
таблиц сформированы и настроены 3 отчета. Можно добавлять свои сводные
таблицы, для получения и настройки других отчетов. Это совсем несложно!
Итак…

Организация ввода данных. Данные в файл журнала (Журнал
оценок.xls) вносятся в виде списка. Список — это попытка в Excel
вести на отдельном листе таблицу базы данных. Проще говоря, это таблица
особым образом организованная. А именно:

1. первая строка определяет заголовки столбцов списка;
2. в каждый столбец вносится однотипная информация (например, если это
   дата, то текст в этот столбец вносить не надо;
3. внутри списка не допускается полностью пустых строк и столбцов.

Формирование отчета на основе сводных таблиц. После
того как вы занесли оценки учеников в таблицу (список) данных, можно обрабатывать
эти данные с помощью сводных таблиц. Сводные таблицы — прекрасный инструмент
Excel, и познакомиться с ними поближе будет очень полезно!
За считанные секунды вы можете создать нужный вам отчет. А для того,
чтобы изменить сводную таблицу, вам надо только перетащить мышкой кнопки
полей, все остальное сделает Excel.

Достоинства Журнала оценок. Определяются тем, что
для его реализации применен Excel.

• А значит, вам не надо долго изучать — как работать с программой и
  вы можете изменять файл
  самостоятельно, не надо обращаться к программисту.
• При реализации журнала оценок не применялись всякие сложности типа
  Visual Basic, мудреные формулы. Так что «подводных
  камней» нет. Можно смело начинать работать, конечно, если вы знаете
  как работать в Excel.

Недостатки Журнала оценок. Определяются также тем,
что для его реализации применен Excel.

• Неудобная форма ввода информации об оценках. Excel не
  может обеспечить ссылочную целостность данных таблиц, как это делает
  Access. Поэтому за правильность внесенной информации
  полностью отвечаете вы. В случае, если количество учеников в школе небольшое
  (меньше 150), на это можно не обращать внимания.
• При большом количестве учеников, когда таблица данных разрастется,
  Excel будет долго их загружать. Для
  устранения этого недостатка надо либо разбивать файл на несколько, либо
  использовать Access (Страница журнала оценок
  в Access.).

Если у вас появились вопросы, замечания и предложения, пишите на наш адрес Imissc13@Yandex.ru

В начало