Московский Государственный Университет Приборостроения и Информатики

Открываю Exсel.

Сначало я заполняю исходный лист списком исходных данных.

• Я переименовываю Лист 1 и даю новое имя-Основной список. ( этот лист будет содержать описания объектов группы студентов)

• Исходными данными будут являються названия столбцов (первая строка листа): «№», «Фамилия», «Имя», «Дата рождения». «№ зач. книжки», «Оц. За экз1» (т.е. Оценка за экзамен 1), «Оц. За экз2», «Оц. За экз3», «Оц. За экз4», «Оц. За экз5», «Средний балл», «Категория успеваемости», «Стипендия»

• Номер зачетной книжки состоит из 6 символов, первые 5 из которых цифры, а последний символ – буква «п» или «б», что соответствует платным либо бюджетным студентам.

Кроме того, Лист Основной список будет содержать справочные исходные данные:

• таблицу соответствия символьных оценок числовым оценкам;

• категории успеваемости;

• количество студентов группы в каждой категории.

• Потом я опять создаю Лист2, Лист3, Лист4, Лист5 и переименовываю их в Ведомость1, Ведомость2, Ведомость3, Ведомость4, Ведомость5 и заполяю Листы 2, 3, 4, 5, 6 (Ведомость1, Ведомость2, Ведомость3, Ведомость4, Ведомость5)

Лист 2 содержит первую ведомость, которая включает столбцы №, Фамилия, Имя, № зачетной книжки и Оценка, причем данные во всех столбцах, кроме столбца Оценка, являются связанными с соответствующими данными с листа Основной список.

Лист Ведомость1предполагает наличие твердой копии и должен быть соответственно оформлен.

Данные во всех столбцах, кроме столбца Оценка, должны быть связанными с соответствующими данными с листа Основной список. Связь с данными с другого листа можно организовать с помощью формулы:

='Название листа'!адрес ячейки.

Когда лист Ведомость1 заполнен (кроме столба Оценка), можно создать остальные листы Ведомость2, Ведомость3, Ведомость4, Ведомость5, создав копию листа Ведомость1. На созданных ведомостях останется только изменить название предмета и заполнить столбец Оценка.

Столбец Оценка заполняется следующими оценками из списка:

• отл (отлично),

• хор (хорошо),

• уд (удовлетворительно),

• неуд (неудовлетворительно),

• н/я (не явка).

Алгоритмы расчетов.

После заполнения данными всех листов ведомостей возвращаемся к листу Основной список. Теперь можно заполнить столбцы Оц. За экз1, Оц. За экз2, Оц. За экз3, Оц. За экз4, Оц. За экз5. Следует осуществить пересчет оценок из текстовой формы с листов Ведомость1, Ведомость2, Ведомость3, Ведомость4, Ведомость5 в числовую на листе Основной список путем создания формул. Пересчёт по 1 и 2 ведомости осуществляется с помощью вложенных функций ЕСЛИ, по 3–5 ведомостям с помощью функции ВПР, при пересчете следует учесть, что все значения в столбце Оценка, кроме «отл», «хор» и «уд», приравниваются к 2.

Средний балл вычисляется как среднее арифметическое оценок за пять экзаменов. Для вычисления среднего балла в Excel имеется функция СРЗНАЧ из категории Статистические.

Эта функция записывается следующим образом:

СРЗНАЧ(оценка за экзамен1: оценка за экзамен5).

Расчет категории производится по следующей схеме: если у студента есть хотя бы одна 2, то он – неуспевающий, если его средний балл ниже 3,75, то он – слабоуспевающий, между 3,75 и 4,25 – успевающий, между 4,25 и 4,75 – хорошо успевающий, выше 4,75 – отличник. Категории задаются из столбца подстановки.

Стипендия рассчитывается следующим образом: платные студенты стипендию не получают, бюджетные студенты получают 1 базовую стипендию, равную 300 рублей, если у него не более одной 3 и он в категории успевающих, если студент в категории хорошо успевающих, то он получает 2 базовые стипендии, если отличник, то 4.

Фильтрация в Итоги.

Фильтрация (выборка) данных в таблице позволяет отображать только те строки, содержимое ячеек которых отвечает заданному условию или нескольким условиям. Сначала нужно скопировать лист Основной список или лучше Лист7 связать с соответствующими данными с листа Основной список. Новый лист (Лист7) можно назвать Фильтр.

Фильтрацию данных можно выполнить с помощью автофильтра, для этого следует использовать команду меню ДАННЫЕ Фильтр Автофильтр, а затем произвести выборку в зависимости от задания по варианту.

Снова необходимо скопировать лист Основной список (или Лист8 связать с соответствующими данными с листа Основной список), а новый лист (Лист8) назвать, например Итоги.

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

Далее с помощью команды меню ДАННЫЕ Итоги можно подводить промежуточные итоги:

• в диалоговом окне Промежуточные итоги из списка При каждом изменении в: выбрать столбец, содержащий группы, по которым необходимо подвести итоги, т.е. это тот же столбец, по которому проводилась сортировка списка;

• из списка Операция выбрать функцию, необходимую для подведения итогов;

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

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

Построение диаграмм.

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

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

Названиями строк таблицы для построения круговой диаграммы пусть будут имена категорий (отличник, хорошо успевающий, успевающий, слабоуспевающий, неуспевающий), а данными – количество студентов в каждой категории.

Для вычисления количества студентов в каждой категории можно использовать статистическую функцию СЧЁТЕСЛИ, которая подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию. Записывается эта функция следующим образом: СЧЁТЕСЛИ(диапазон; критерий), где диапазон – это группа ячеек, количество которых будут вычислять, а критерий может быть в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать.

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

После построения таблиц можно строить диаграммы. Для этого необходимо: выделить данные, на основе которых строится диаграмма, вызвать Мастер диаграмм и далее следовать указаниям Мастера.

Московский Государственный Университет Приборостроения и Информатики.

Курсовая работа.

По дисциплине

Информатика

На тему:

Анализ успеваемости группы средствами Excel.

Студента: Захарченко Алексея

Специальность: Машиностроение

Группа: Ти-2 (150700)

Преподаватель: Микаева Анжела Сергеевна.

Москва 2012.