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

Размещено на http://www.allbest.ru/

Московский государственный университет приборостроения и информатики

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

По дисциплине Информатика

На тему:

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

Студент: Михайлов Андрей

Специальность: 110120

Группа: ТИ-7

Преподаватель: Кондрашева С.В.

Москва 2011

Оглавление

• Анализ успеваемости группы средствами Excel
• Задание
• Описание основных используемых функций
• Логическая функция «если»
• Логическая функция «срзнач»
• Логическая функция «мин»
• Логическая функция «макс»
• Функция впр
• Алгоритм расчета
• Диаграммы
• Задание

Курсовая работа состоит из 3 документов: книги Excel и двух документов Word. Книга Excel содержит 8 листов.

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

Круговая диаграмма, отражающая количество студентов в каждой категории.

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

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

Столбец «оценка» заполняется для каждой записи в каждой ведомости следующими оценками из списка: отл (отлично), хор (хорошо), уд (удовлетворительно), неуд (неудовлетворительно), н/я (не явка). Эти листы предполагают наличие твердой копии и оформляются соответственно.



Экзаменационная ведомость по <предмет>
№	Фамилия	Имя	№ зач. кн.	Оценка

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

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

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

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

Первый документ Word состоит из шаблона и собственно документа.

Шаблон содержит стандартный титульный лист и следующие стили, создаваемые студентами (по вариантам). Например,

Стиль для заголовка имеет установки Times New Roman (TNR) 18, Ж (полужирный), все прописные, разреженный на 3 пт, 1 уровень, без переносов, всегда с новой страницы, выравнивание по центру, интервал после 18 пт, стиль следующего абзаца - стиль для подзаголовка.

Стиль для подзаголовка имеет установки TNR 16. Ж (полужирный), 2 уровень, выравнивание по центру, интервал после 12 пт, межстрочный интервал 20 пт точно, стиль следующего абзаца - стиль основного текста.

Стиль основного текста имеет установки TNR 14, уровень основного текста, выравнивание по ширине, красная строка 1,5 см, межстрочный интервал 1,5 интервала.

Документ1 создается на основе ранее созданного шаблона и включает в себя только стили, созданные студентом. Название стиля, созданного студентом, должно включать Фамилию студента, группу и название заголовка, например, ИвановТИб Глава.

Содержание документа1. Титульный лист, оглавление, задание, описание основных использованных функций, разбор алгоритмов расчета. В документ путем связывания и внедрения переносятся диаграммы из книги EXCEL. Оглавление вставляется автоматически. Документ содержит нумерацию страниц (причем титульный лист в нумерацию не входит) и верхний колонтитул, содержащий фамилию студента и шифр специальности.

Второй документ Word (Документ2) представляет собой основной документ-бланк включающий поля слияния из листа Основной список книги Excel. Он имеет следующий вид

МГУПИ Московский филиал
УВЕДОМЛЕНИЕ
Уважаемый Иванов Иван Иванович сообщаем Вам, что Ваша стипендия за 2 семестр 2004/2005 учебного года составляет 300 рублей
Деканат Московского филиала

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

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

25.05, ТИ-6, 2007 г. На дискете должно быть три файла (2 файла Word и файл Excel), имена файлов должны содержать личные данные студента, например, Иванов 0710 Курсовая.

Описание основных используемых функций

Логическая функция «если»

Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Функция ЕСЛИ используется при проверке условий для значений и формул. ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь).

Лог_выражение - это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.

Значение_если_истина - это значение, которое возвращается, если лог_выражение равно ИСТИНА.

Значение_если_ложь - это значение, которое возвращается, если лог_выражение равно ЛОЖЬ.

До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь для конструирования более сложных проверок.

Логическая функция «срзнач»

Возвращает среднее (арифметическое) своих аргументов.

СРЗНАЧ (число 1; число 2; ...)

Число 1, число 2, ... -- это от 1 до 30 аргументов, для которых вычисляется среднее.

Аргументы должны быть либо числами, либо именами, массивами или ссылками, содержащими числа.

Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.

Логическая функция «мин»

Возвращает наименьшее значение в списке аргументов.

МИН (число 1;число 2; ...)

Число 1, число 2, ...-- от 1 до 30 чисел, среди которых требуется найти наименьшее.

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

Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения или текст в массиве или ссылке игнорируются. Если логические значения или текст игнорироваться не должны, следует пользоваться функцией МИНА.

Если аргументы не содержат чисел, то функция МИН возвращает 0.

Логическая функция «макс»

Возвращает наибольшее значение из набора значений.

МАКС (число 1;число 2; ...)

Число 1, число 2, ...-- от 1 до 30 чисел, среди которых требуется найти наибольшее.

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

Если аргумент является массивом или ссылкой, то в нем учитываются только числа. Пустые ячейки, логические значения или текст в массиве или ссылке игнорируются. Если логические значения или текст не должны игнорироваться, следует использовать функцию МАКСА.

Если аргументы не содержат чисел, то функция МАКС возвращает 0 (ноль).

Функция впр

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

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

ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр).

Искомое_ значение. Значение, которое должно быть найдено в первом столбце массива «таблица». Искомое_ значение может быть значением или ссылкой. Если искомое значение меньше наименьшего значения в первом столбце массива «таблица», ВПР возвращает значение ошибки #Н/Д.

Таблица. Два или более столбцов данных. Можно использовать ссылку на интервал или имя интервала. Значения в первом столбце массива «таблица» являются значениями, поиск которых выполняется с помощью аргумента «искомое_значение». Эти значения могут быть текстовыми строками, числами или логическими значениями. Текстовые строки сравниваются без учета регистра букв.

Номер_столбца. Номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение. Если «номер_столбца» равен 1, то возвращается значение из первого столбца аргумента «таблица»; если «номер_столбца» равен 2, то возвращается значение из второго столбца аргумента «таблица» и так далее.

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

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

Пересчет оценок из текстовой формы в числовую.

=ЕСЛИ(Ведомость1!$E3='Основной список'!$L$2;$M$2;

ЕСЛИ(Ведомость1!$E3='Основной список'!$L$3;$M$3;

ЕСЛИ(Ведомость1!$E3='Основной список'!$L$4;$M$4;$M$5)))

Объясним данную формулу так: если значение оценки в текстовой форме с листа Ведомость1 будет соответствовать слову «отл» во вспомогательной таблице (первое значение), построенной на листе Основной список, то это значение заменяется на 5, которая находится в столбце М. Если то же самое значение в текстовой форме с листа Ведомость1 будет соответствовать слову «хор» в той же вспомогательной таблице (второе значение), то это значение должно заменится на 4. Если то же самое значение в текстовой форме с листа Ведомость1 будет соответствовать слову «уд» (третье значение), то это значение должно заменится на 3. Если все перечисленные значения не подходят, то значение оценки в текстовой форме с листа Ведомость1 заменяется на 2.

Вычисление среднего балла

=СРЗНАЧ($F2:$H2)

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

Расчет категории успеваемости

функция логический успеваемость студент

=ЕСЛИ(ИЛИ($F2=$M$5;$G2=$M$5;$H2=$M$5);$L$12;ЕСЛИ($I2>$M$8;$L$8;ЕСЛИ(И($I2>$M$9;$I2<$M$8);$L$9;ЕСЛИ(И($I2>$M$10;$I2<$M$9);$L$10;$L$11))))

Объясним эту формулу так: сначала укажем все условия для проверки, есть ли у студента хотя бы одна двойка за экзамен. Если есть, то студент попадает в категории неуспевающих, даже если у него средний балл выше 3,75. Если двоек за экзамены у студента нет, то проверяем его средний балл. Если средний балл студента > 4,75, то этому соответствует ячейка во вспомогательной таблице, содержащая слово «отличник». Если средний балл студента > 4,25,но меньше 4,75, то этому условию соответствует ячейка со значением «хорошо успевающий». Если средний балл студента больше 3,75, но меньше 4,25, то этому условию соответствует ячейка со значением «успевающий». Если все перечисленные значения не подходят, то этому соответствует ячейка со значением «слабоуспевающий».

Расчет стипендии

=ЕСЛИ(ПРАВСИМВ($E2)=$M$15;

ЕСЛИ(И(ИЛИ($F2>=$M$4;$G2>=$M$4;$H2>=$M$4);$J2=$L$10);$O$10;

ЕСЛИ($J2=$L$9;$O$9;ЕСЛИ($J2=$L$8;$O$8;$O$12)));$O$12)

Объясним эту формулу так: сначала проверим платный студент или бюджетный. Для этого проверяем, соответствует ли номер зачетной книжки второму значению во вспомогательной таблице («б»). Если студент бюджетный, то проверяем, чтобы у него было не больше одной тройки за экзамены. Если это условие выполняется, то студент должен находится в категории «успевающий» и получает стипендию в размере 300руб. Если категория успеваемости совпадает со значением «хорошо успевающий» во вспомогательной таблице, то студент получает стипендию - 600руб. Если категория успеваемости совпадает со значением «отличник» во вспомогательной таблице, то студент получает стипендию - 1200руб. Если не выполняется ни одно из этих условий и если студент платный его стипендия составляет 0руб.

Диаграммы

Связанная диаграмма

Внедренная диаграмма

Размещено на Allbest.ru