Лабораторная работа 4
Создание электронного журнала успеваемости в MS Excel (LibreOffice.Calc)
Цель работы: создать таблицу для подсчета статистики успеваемости каждого студента. Для работы потребуется один документ с двумя рабочими листами.
Принцип работы в табличных редакторах разных разработчиков одинаков.
Задание 1. Заполнение листа 1
Создать список студентов из 10 произвольных фамилий, включая свою. После выполнения действий п.1-5 у вас должна получиться таблица, аналогичная представленной на рисунке 1.
Для этого выполните следующие действия:
1. На Листе 1 создайте надпись «Список студентов». Оформление выберите на свое усмотрение. Заполните строку 3 (шапку таблицы).
2. Заполните столбец А (порядковый номер №) с помощью команды автозаполнение. В графе «Факультет » и «Группа» продублируйте данные. Столбец «Телефон» заполнить произвольно.
3. В ячейках H4:H13 создайте список студентов (10 чел), в одной ячейке фамилию и имя. Отсортируйте данные по алфавиту.
4. Выполните разделение списка на два столбца: Данные-Текст по столбцам. В диалоговом окне разделения текста оставьте формат данных с разделителем. Поставьте галочку в поле «Пробел ». В поле «Поместить в » мышью выделить ячейки С4:D13. Нажмите ОК. Очистить столбец H/
5. Заполните данные «Идентификатор студента». Для этого в ячейку В4 введите формулу =СЦЕПИТЬ(F4;”-”;A4) (категория Текстовые), в качестве разделителя используется дефис, можно использовать другой символ. Скопируйте формулу на весь список.
5.1 В OpenOffice.Calc вам следует выбрать из категории «Текстовые» функцию = CONCATENATE(F4;”-”;A4), в поле «Текст 2» укажите в кавычках дефис, который разделит номер группы и порядковый номер в списке.
Рисунок 2. Мастер Функций
6 В ячейке Н4 вы снова совместите фамилию и имя студента используя формулу =СЦЕПИТЬ(C4,” ”,D4)В кавычках указан один пробел. Скопировать на весь список
6.1 В OpenOffice.Calc вам следует выбрать из категории «Текстовые» функцию = CONCATENATE(С4;”-”;D 4), в поле «Текст 2» укажите в кавычках пробел, который разделит фамилию и имя.
Задание 2. Заполнение листа 2
- Откройте Лист 2
· В первой строке сделать заголовок таблицы, например «Таблица успеваемости студентов группы…». Объединить ячейки, использовать произвольный стиль оформления.
· Заполнить шапку таблиц. Цветовое и шрифтовое оформление на ваш вкус. Заполнить столбец «№ п/п», используя функцию автозаполнения.
Рисунок 3. Таблица успеваемости группы
· Заполните ячейки «дата проведения занятий» (D3:Н3), для этого:
1. Установите формат ячеек (D3:Н3) – категория – «дата» любой выбранный формат.
2. В ячейках D3:E3 введите две даты с интервалом в одну неделю, например D3-01.09.17; E3-08.09.17.
3. Автозаполнением заполнить все остальные ячейки на любые два месяца.
4. Изменить формат всех этих ячеек (D3:H3): разверните текст на 90 градусов, выравнивание по середине и по горизонтали и по вертикали. ()
5. Отформатируйте ширину столбцов
· MS Excel: Формат-Столбец- Автоподбор ширины.
· В OpenOffice.Calc Формат-Столбец- Ширина. ( Ширина столбцов D-H 0,8-1,0 )
6. На листе 2 в столбце «Идентификатор студента» создайте выпадающие списки с номером студента. Для этого:
Выделите диапазон В3-В12, затем Данные-Проверка данных
MS Excel 2010-2013:Тип данных-Список. В поле Источник введите выделенный диапазон идентификатора студентов с Листа 1. ОК.(=Лист1!диапазон) Затем заполните поля на вкладках Сообщение для ввода и Сообщение об ошибке.
На вкладке Сообщение для ввода в поле Заголовок укажите свою фамилию и имя, а в поле Сообщение, например «Выберите данные из списка.» Оставьте га
дочку в поле Отображать подсказку, если ячейка является текущей.
На вкладке Сообщение об ошибке наберите предупреждение о совершенной пользователем ошибке при выборе варианта ответа.
MS Excel 2003 данные для источника должны быть на одном листе с выбранной ячейкой. Поэтому рекомендуется продублировать на Листе 2 в любом свободном месте столбец с идентификаторами студентов. В более старших версиях MS Excel OpenOffice можно брать данные с разных листов.
OpenOffice.Calc: Данные-Проверка данных. В поле Разрешить-Список. В поле Элементы укажите диапазон данных с листа 1 ячейки В3:В13,т.е. идентификаторы студентов.
Заполните вкладки Помощь при вводе и Действия при ошибках.(см. выше)
Рис
После этого рядом со всеми выделенными ячейками появится кнопка выбора варианта.
В ячейке С3 должна появиться фамилия студента, в соответствии с его личным номером. Используйте формулу Поиск по вертикали:
a. MS Excel категория Ссылки и массивы-ВПР
b. OpenOffice.Calc: Категория-Электронные таблицы-VLOOKUP
В первом поле введите адрес ячейки В3 (Лист2). Во втором поле укажите диапазон всей таблицы с Листа 1(В4:Н13). В третьем поле № столбца из выделенного диапазона, откуда берутся данные. ФИ из столбца Н. Порядковый № =7, это число указываем в поле Номер столбца.
=ВПР(В3;Лист1!В$4:H$13;7)
=VLOOKUP(B3; Лист1!В$4:H$13;7)
Скопировать формулу на весь диапазон, используя Автозаполнение.
6. В ячейке L3 подсчитать средний балл по тесту, ф-я СРЗНАЧ и выделив диапазон числовых данных по тесту. =СРЗНАЧ(I3:K3) или AVERAG E (I3:K3). Скопируйте на весь диапазон
7. В ячейке L7пожсчитайте, сколько осталось написать тестов студенту, используя условие, что ячейки результатов не должны содержать «0», «н», «»:
СЧЕТЕСЛИ(I3:K3;””)+ СЧЕТЕСЛИ(I3:K3;”н”)+ СЧЕТЕСЛИ(I3:K3;0
=COUNTIF(I3:K3;””)+COUNTIF(I3:K3;0)+COUNTIF(I3:K3;”и”)
В категории Статические находится функция СЧЕТЕСЛИ(Iдиапазон;критерий), где диапазон- диапазон ячеек, в котором нужро сосчитать число значений, удовлетворяющих заданному критерию, критерий — в форме числа, выражения или текста, который определяет какие ячейки надо подсчитывать. В кавычках- текст, например СЧЕТЕСЛИ(I3:K3;”яблоки”),- будут сосчитаны все ячейки, содержащие слово -яблоки.
8. Для подсчета суммарного балла-используйте функцию автосуммирование по строке.
9. Рассчитать ранг студента в общем списке.
Функция РАНГ() RANK – категория Статические вычисляет ранг значения в выборке (распределение участников по местам)
РАНГ() имеет три аргумента.
Первый- число, место (ранг) которого определяется. Второй аргумент-ссылка- диапазон, в котором происходит распределение по местам. В нашем примере это.столбец с суммарно набранным баллом
Диапазон д.б. Неизменным, следовательно его нужно указать с помощью абсолютной адресации.
Третий аргумент — Порядок — указатель сортировки, если 0 или не указан-места распределяются по убыванию значений (1 место- мах. Значение), если поставить 1- места по возрастанию (чем меньше тем лучше).