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

Московский государственный университет приборостроения и информатике Курсовая работа По дисциплине ИНФОРМАТИКА На тему:

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

Селезнев С. С.

ТИ-1

Группа-2

Москва 2008 г.

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

Разбор алгоритмов расчёта Диаграммы и гистограммы Excel

Задание

для excel

Отобрать всех отличников в фильтре и определить количество платных и неуспевающих студентов.

для Word

Стиль для заголовка — Times New Roman 12, Ж+Л, все прописные, 1 уровень, без переносов, Всегда с новой страницы, выравнивание по правому краю, интервал после 12 пт, стиль следующего абзаца — стили для подзаголовка.

Для подзаголовка — TNR 12, Ж, все прописные, с тенью, 2 уровень, Выравнивание по правому краю, интервал после 14 пт, межстрочный интервал 20 пт точно, стиль следующего абзаца — стиль основного текста.

Для основного текста — TNR 14, разряженный на 2 пт, уровень основного текста. Выравнивание по ширине, красная строка 1.5см. межстрочный интервал 1.5 интервала.

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

Логическая функция ЕСЛИ

Функция ЕСЛИ используется при проверке условий для значений и формул. Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Эта функция записывается следующим образом ЕСЛИ (лог_выр;знач_если_истина;знач_если_ложь). где:

«лог_выр» — условие которое требуется проверить. Например «А10=100», если «А10» равно «100» то ячейка «А10» принимает значение ИСТИНА, если не равно то ЛОЖЬ.

«знач_если_истина» — значение, которое возвращается если «лог_выр» принимает значение ИСТИНА. «знач_если_истина» можно записать в виде текста, числа или формулы.

«знач_если_ложь» — значение которое возвращается если «лог_выр» примет значении ЛОЖЬ. Данный аргумент также можно записать в виде формулы.

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

Текстовая функция ПРАВСИМ

Текстовая функция ПРАВСИМВ возвращает заданное число последних знаков текстовой строки. Записывается в таком виде: ПРАВСИМВ (текст;число_знаков) где:

«текст» — это текстовая строка, содержащая извлекаемые знаки.

«число_знаков» — количество знаков извлекаемых функцией ПРАВСИМ. Причем извлечение знаков происходит с конца, а если аргумент отсутствует то он равен 1 по умолчанию.

Например: в ячейке Е2 текст «7 045б», используем функцию С2=ПРАВСИМ (Е2;1) получаем в ячейке С2 текст «б».

Статистическая функция СРЗНАЧ

Функция СРЗНАЧ возращает среднее арифметическое своих аргументов. Записывается функция таким образом СРЗНАЧ (число1;число2;…) где «число (1,2,…)» может быть числом или ссылкой на ячейку.

Эту функцию я использовал при потщёте среднего балла студента и группы.

Статистическая функция СЧЁТЕСЛИ

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

Диапазон — это группа ячеек, количество которых будут вычислять.

Критерий может быть в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, «32», «>32», «отличник» .

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

Статистическая функция МИН

Функция МИН возвращает минимальное значение в списке аргументов. Эта функция записывается следующим образом МИН (диапазон)

Статистическая функция МАКС

Функция МАКС возвращает максимально значение в списке аргументов. Эта функция записывается следующим образом МАКС (диапазон) Эти функции я использовал при построении гистограммы.

Функция ВПР из раздела «ссылки и массивы»

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

Буква «В» в имени функции ВПР означает «вертикальный». Функция записывается следующим образом: ВПР (искомое_значение;таблица;номер_столбца;интервальный_просмотр) Искомое_значение — это значение, которое должно быть найдено в первом столбце массива (Массив — объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.). Искомое_значение может быть значением, ссылкой или текстовой строкой.

Таблица— таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например База Данных или Список.

Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке: …, -2, -1, 0, 1, 2, …, A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если «интервальный_просмотр» имеет значение ЛОЖЬ, то «таблица» не обязана быть отсортированной.

Данные можно упорядочить следующим образом: в меню Данные выбрать команду Сортировка и установить переключатель По Возрастанию.

Значения в первом столбце аргумента «таблица» могут быть текстовыми строками, числами или логическими значениями.

Текстовые строки сравниваются без учета регистра букв.

Номер_столбца — это номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение. Если «номер_столбца» равен 1, то возвращается значение из первого столбца аргумента «таблица»; если «номер_столбца» равен 2, то возвращается значение из второго столбца аргумента «таблица» и так далее. Если «номер_столбца» меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если «номер_столбца» больше, чем количество столбцов в аргументе «таблица», то функция ВПР возвращает значение ошибки #ССЫЛ!

Интервальный_просмотр — это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

Эту функцию я использовал при замене текстовой информации на числовую.

Разбор алгоритмов расчёта

Алгоритм пересчета оценок из текстовой формы в числовую форму

В Первых двух столбцах «оц. за экз. № 1,2» проведем пересчет оценок и текстовой формы в числовую используя функцию ЕСЛИ. Алгоритм вычисления будет происходить по этой схеме:

Алгоритм вычисления среднего балла

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

Эта функция записывается следующим образом: =СРЗНАЧ (F4:J4)

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

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

В Excel эту схему я записал таким образом: =ЕСЛИ (F5=2;" Неуспевающий" ;ЕСЛИ (G5=2;" Неуспевающий" ;ЕСЛИ (H5=2;" Неуспевающий" ;ЕСЛИ (I5=2;" Неуспевающий" ;ЕСЛИ (J5=2;" Неуспевающий" ;ЕСЛИ (K5>=4,75;" Отличник" ;ЕСЛИ (K5>=4,25;" Хорошоуспевающий" ;ЕСЛИ (K5>=3,75;" Успевающий" ;" Слабоуспевающий")))))))); также можно использовать функцию СЧЁТЕСЛИ для определения количества двоек, которая значительно сократит сам алгоритм поиска.

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

Диаграммы и гистограммы Excel

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

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

После построения таблиц можно строить диаграммы. Для этого необходимо: выделить данные, на основе которых строится диаграмма, вызвать Мастер диаграмм и далее следовать указаниям Мастера. На первом этапе работы мастера выбирают форму диаграммы (тип диаграммы). Доступные формы перечислены в списке Тип на вкладке Стандартные. Для выбранного типа диаграммы справа указывается несколько вариантов представления данных (палитра Вид), из которых следует выбрать наиболее подходящий. На вкладке «Нестандартные» отображается набор полностью сформированных типов диаграмм с готовым форматированием.

Внедрение и связывание диаграмм в документ Word

Для вставки диаграмм из Excel в Word применяется технология OLE (Object Link and Embedding) — связь и внедрение объектов. Это означает, что помещаемый в текст объект может включаться в него в двух вариантах:

1. как внедренный, т. е. он становится частью документа Word, и все изменения, которые производятся в источнике, не будут отражаться в документе;

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

Различают также термины технологии OLE: клиент — приложение, принимающее объект, и сервер — приложение, средствами которого создается объект. Внедренный или связанный объект можно впоследствии редактировать средствами приложения-сервера. Для этого надо выполнить двойной щелчок мыши на данном объекте (Word загрузит приложение-сервер), произвести изменения над объектом и вернуться в приложение-клиент.

Технология OLE осуществляется двумя способами:

1. через буфер обмена — командой приложения «Правка» «Специальная_вставка»…;

2. командой приложения «Вставка» «объект»…

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

Пример внедрение диаграммы в документ Word

Чтобы внедрить объект через буфер обмена:

1. в приложении-сервере (Excel) нужно выделить объект (диаграмму).

2. занести его в буфер обмена (команда «Правка» «Копировать»).

3. перейти в приложение-клиент (Word).

4. выполнить команду «Правка» «Специальная вставка».

5. в диалоговом окне Специальная вставка активизировать переключатель «Вставить».

word excel диаграмма гистограмма

Пример связывания гистограммы с документом Word

Чтобы связать объект через буфер обмена:

1. в приложении-сервере (Excel) выделить объект (диаграмму).

2. занести его в буфер обмена (команда «Правка» «Копировать»).

3. перейти в приложение-клиент (Word).

4. выполнить команду «Правка» «Специальная вставка».

5. в диалоговом окне Специальная вставка активизировать переключатель «Связать».