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

Создание электронного журнала в MS Excel (LibreOffice. Calc)

Создание электронного журнала

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

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

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

Принцип работы в табличных редакторах разных разработчиков одинаков. Отличия по работе в MS Excel и Libre(Open)Office. Calc в методических указаниях прописываются рядом с соответствующим пунктом, например, п.4 и п.4.1 или выделяются разным цветом.

Отчет по работе необходимо выложить в блоге, используя облачные хранилища.

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

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

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

Для этого выполните следующие действия.

На Листе1 создайте надпись «Список студентов». Оформление выберите на свое усмотрение. Заполните строку 3 (шапку таблицы). Вместо графы «Телефон» можете вписать любой другой пункт, например, адрес электронной почты/text/category/abbreviatura/» rel=»bookmark»>аббревиатуру), а в графе «Группа» — номер своей группы: 126 — цифра 1 – номер курса, цифра 2 – номер потока, цифра 6 – номер группы на потоке. Скопируйте данные на весь столбик E и F (10 позиций). Произвольными данными заполните столбец «Телефон». В ячейках B20:B30 создайте список студентов (10 человек). Выполните разделение списка на два столбца. Для этого: Данные – Текст по столбцам. В диалоговом окне разделения текста оставьте формат данных с разделителем. На втором шаге поставьте галочку в поле «Пробел». На третьем шаге в поле «Поместить в» мышью выделите ячейки C4:D13. Нажмите OK. Заполните данные в столбце «Идентификатор студента». Для этого в ячейку B4 введите формулу =СЦЕПИТЬ(F4;»-«;A4). В результате этих действий соединяются текстовые данные из ячейки «Номер группы» и «Порядковый номер». В качестве разделителя мы указали дефис. Вы можете выбрать свой символ разделителя, например, нижнее подчеркивание или «&» или др. Скопируйте формулу на весь список. В Libre(Open)Office. Calc вам следует выбрать из категории «Текстовые» функцию =CONCATENATE(F4;»-«;A4). В поле «Текст 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. Затем заполните поля на вкладках Сообщение для ввода и Сообщение об ошибке.

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

Читать еще:  Как сделать формулу с процентами в excel на весь столбец?

На вкладке Сообщение об ошибке в поле Заголовок укажите факультет и группу на потоке, например, ППФ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. Это число и нужно указать в поле Номер столбца.

Скопируйте формулу на весь необходимый диапазон, используя автозаполнение ячеек.

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

В категории Статистические находится функция <СЧЁТЕСЛИ()>, которая позволяет сосчитать число значений внутри диапазона, удовлетворяющих заданному критерию. Синтаксис данной функции:

Где диапазон — это диапазон ячеек, в котором нужно сосчитать число значений, удовлетворяющих заданному критерию; критерий — критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать.

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

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

Функция РАНГ() (RANK) категория Статистические вычисляет ранг значения в выборке (распределения участников по местам). Функция РАНГ() имеет три аргумента.

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

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

Для формирования условий в формулах используется функция ЕСЛИ(). Она имеет три аргумента. Первый аргумент тест – условие, второй аргумент тогда значение – действия которое совершается при выполнении условия, третий аргумент иначе значение – действия при не выполнении условия.

Читать еще:  Как сделать прогресс бар в excel?

Пусть, например, ячейка D5 содержит формулу «=ЕСЛИ (A1 =0,75*N$13;M3=0);»зачет»;»нет»)

В электронных таблицах возможно использование более сложных логических конструкций с использованием вложенных функций ЕСЛИ(), когда ЕСЛИ() используется в качестве аргумента другой функции ЕСЛИ(). Например, сложная функция

=ЕСЛИ(A1 100,ЕСЛИ(A1 Подпишитесь на рассылку:

Работа в Microsoft Excel. Создание электронного журнала.

Добавляйте авторские материалы и получите призы от Инфоурок

Еженедельный призовой фонд 100 000 Р

В программе Microsoft Excel создать следующие листы:

На листе «Общий» в 1-ой строке озаглавить ячейки следующим образом:

Дата зачисления в школу

Срок учебы в школе

Средний балл по гуманитарным предметам

Средний балл по предметам естествознания

Заполнить десятью записями столбцы

, Фамилия, Имя, Дата рождения, Дата зачисления в школу

Дата рождения и дата зачисления — в формате дд.мм.гггг

Колонки Возраст и Срок учебы в школе должны автоматически высчитываться с помощью функции РАЗНДАТ относительно текущей даты.

(!) Функция СЕГОДНЯ () – возвращает текущую дату

(!) Функция РАЗНДАТ(дата1;дата2;” y ”) – возвращает в годах разницу между датой1 и датой2

Перенести колонки с данными №, Фамилия, Имя на оставшиеся листы.

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

Затем на предметных листах, в первой строке после имени добавьте 7 дат (формат даты 01 марта). Последнюю ячейку озаглавить «Итог».

Заполнить журналы оценками.

(!) Оценки ставятся от 1 до 5, также используется буква «Н» для отсутствующих, а также возможно некоторое количество пустых клеток.

Используя функцию СРЕДНЕЕ в колонке «Итог» рассчитать среднюю оценку по каждому предмету.

Округлить получившиеся значения до десятых.

Путем копирования ссылки вывести итоговые оценки по всем предметам на лист «Общий»

Рассчитать значения в колонках «Средний балл по гуманитарным предметам» (русский и английский языки) и «Средний балл по предметам естествознания» (математика, физика и химия).

Рассчитать средний балл каждого ученика по всем предметам и записать его в колонку «Средний балл».

Создать на листе Общий еще одну колонку и назвать ее Переведен на следующий год.

Если средний балл ученика «3» и выше, то в колонке Переведен на следующий год напротив фамилии данного ученика должна появляться надпись «Переведен», иначе «Оставлен на второй год».

(!) Для выполнения данного задания используйте функцию ЕСЛИ (см. презентацию 16-18 слайды)

Создать дополнительный лист Успеваемость и поместить его вторым по счету среди листов.

Пользуясь функцией СЧЕТЕСЛИ (см. презентацию 20 слайд) вывести количество Отличников, Хорошистов, Троечников и Двоечников.

Отличником считается ученик, средняя оценка которого «5», Хорошист — «4» и т.д.

(!) Учитывать, что оценки, полученные на листе Общий округлены по правилам округления.

Если выполнены все вышеприведенные пункты, то оценка за работу – «4».

Для получения «5» необходимо выполнить следующие 2 пункта.

В п.13. Если средний балл ученика «3» и выше и у него НЕТ ДВОЕК В ЧЕТВЕРТИ НИ ПО ОДНОМУ ИЗ ПРЕДМЕТОВ, то в колонке Переведен на следующий год напротив фамилии данного ученика должна появляться надпись «Переведен», иначе «Оставлен на второй год».

Создать дополнительный лист Стипендия и поместить его третьим по счету среди листов.

Вывести на этот лист список класса и добавить колонку Стипендия.

стоят пятерки в колонках «Средний балл по гуманитарным предметам» и «Средний балл по предметам естествознания», то стипендия 1000 руб.;

500 руб. — если пятерка либо по «Гуманитарным предметам», либо по «Предметам естествознания»;

300 руб. – если он закончил без троек;

0 руб. – в остальных случаях.

(!) Для выполнения данного задания используйте функцию ЕСЛИ (см. презентацию 16-18 слайды)

Читать еще:  Как сделать интерактивную таблицу в excel?

Создать дополнительный лист Посещаемость и также вывести на этот лист список класса и добавить колонку Кол-во пропущенных уроков.

Напротив каждого ученика вывести общее число пропущенных уроков по всем предметам.

(!) Пропущенные уроке на листах предметов отмечаются маленькой буквой «н» (без кавычек).

Что-бы показать более-менее реальное применение экселя в жизни, можно с учениками создать «Электронный журнал», в котором они создадут небольшой класс по нескольким предметам, выставят оценки и «н» воображаемым ученикам и определят, кому какая стипендия полагается за старания, а кого лучше отправить на олимпиаду.

Создание электронного журнала.

  1. В программе Microsoft Excel создать следующие листы:
  2. · Общий

  • На листе «Общий» в 1-ой строке озаглавить ячейки следующим образом:
  • · №

    · Дата зачисления в школу

    · Срок учебы в школе

    · Средний балл по гуманитарным предметам

    · Средний балл по предметам естествознания

  • Придумать 10 учеников и записать их в таблицу.
  • №, Фамилия, Имя, Дата рождения, Дата зачисления в школу

    Дата рождения и дата зачисления — в формате дд.мм.гггг

    Колонки Возраст и Срок учебы в школе должны автоматически высчитываться с помощью функции РАЗНДАТ относительно текущей даты.

    (!) Функция СЕГОДНЯ () – возвращает текущую дату

    (!) Функция РАЗНДАТ(дата1;дата2;”y”) – возвращает в годах разницу между датой1 и датой2

  • Перенести колонки с данными №, Фамилия, Имя на оставшиеся листы.
  • (!) Копирование должно быть ссылкой с листа Общий, т.е. при изменении на листе Общий, данные будут меняться на всех листах.
  • Затем на предметных листах, в первой строке после имени добавьте 7 дат (формат даты 01 марта).
  • Последнюю ячейку озаглавить «Итог».
  • Заполнить журналы оценками.
  • (!) Оценки ставятся от 1 до 5, также используется буква «Н» для отсутствующих, а также возможно некоторое количество пустых клеток.
  • Используя функцию СРЕДНЕЕ в колонке «Итог» рассчитать среднюю оценку по каждому предмету.
  • Округлить получившиеся значения до десятых.
  • Путем копирования ссылки вывести итоговые оценки по всем предметам на лист «Общий»
  • Рассчитать значения в колонках «Средний балл по гуманитарным предметам» (русский и английский языки) и «Средний балл по предметам естествознания» (математика, физика и химия).
  • Рассчитать средний балл каждого ученика по всем предметам и записать его в колонку «Средний балл».
  • Создать на листе Общий еще одну колонку и назвать ее Переведен на следующий год.
  • Если средний балл ученика «3» и выше, то в колонке Переведен на следующий годнапротив фамилии данного ученика должна появляться надпись «Переведен», иначе «Оставлен на второй год».
  • Создать дополнительный лист Успеваемость и поместить его вторым по счету среди листов.
  • Пользуясь функцией СЧЕТЕСЛИ вывести количество Отличников, Хорошистов, Троечников и Двоечников.
  • Отличником считается ученик, средняя оценка которого «5», Хорошист — «4» и т.д.
  • (!) Учитывать, что оценки, полученные на листе Общий округлены по правилам округления.

    1. Создать дополнительный лист Стипендия и поместить его третьим по счету среди листов.
    2. Вывести на этот лист список класса и добавить колонку Стипендия.
    3. Если у ученика
    • · стоят пятерки в колонках «Средний балл по гуманитарным предметам» и «Средний балл по предметам естествознания», то стипендия 1000 руб.;

      · 500 руб. — если пятерка либо по «Гуманитарным предметам», либо по «Предметам естествознания»;

      · 300 руб. – если он закончил без троек;

      · 0 руб. – в остальных случаях.

      (!) Для выполнения данного задания используйте функцию ЕСЛИ (см. презентацию 16-18 слайды)

      1. Создать дополнительный лист Посещаемость и также вывести на этот лист список класса и добавить колонку Кол-во пропущенных уроков.

      2. Напротив каждого ученика вывести общее число пропущенных уроков по всем предметам.

      (!) Пропущенные уроке на листах предметов отмечаются маленькой буквой «н» (без кавычек).

    Ссылка на основную публикацию
    Adblock
    detector