Методическое пособие по дисциплине «информатика» (2семестр)

Вид материала

Методическое пособие

Содержание ПУСКПрограммыMicrosoft Excel Заполнение Листа Основной список исходными данными Число в диалоговом окне Формат ячеек Основной список Заполнение Листов 2, 3, 4 (Ведомость1, Ведомость2, Ведомость3) Основной список Пересчет оценок из текстовой формы в числовую форму Основной список Объяснение формулы Мастер функций ЕСЛИ(адрес той же ячейки с листа с оценками в текстовой форме = “уд”;3;2))) ЕСЛИ(Ведомость1!$E3='Основной список'!$L$4;$M$4;$M$5))) Вычисление среднего балла Средний балл Расчет категории успеваемости Основной список Основной список Расчет стипендии Основной список Основной список ... Полное содержание

Подобный материал:

• Методическое пособие по выполнению курсовых работ по дисциплине, 450.1kb.
• Современной Гуманитарной Академии (С) современная гуманитарная академия, 2011 методическое, 218.52kb.
• Методическое пособие по курсу «Информатика» для студентов, обучающихся по всем направлениям, 1648.11kb.
• Современной Гуманитарной Академии © современная гуманитарная академия, 2011 оглавление, 190.71kb.
• Вопросы к зачёту 2семестр по дисциплине «Информатика», 931.8kb.
• Современной Гуманитарной Академии © современная гуманитарная академия, 2011 оглавление, 198.05kb.
• Современной Гуманитарной Академии © современная гуманитарная академия, 2011 оглавление, 191.23kb.
• Современной Гуманитарной Академии © современная гуманитарная академия, 2011 оглавление, 198.4kb.
• С. В. Тимченко информатика  4 Учебно-методическое пособие, 268.2kb.
• Учебно-методическое пособие по дисциплине «Налоги и налогообложение», 2006 г. Институт, 99.9kb.

Московская государственная академия

приборостроения и информатики


Методическое пособие по дисциплине «ИНФОРМАТИКА»

(2семестр)

Разбор Курсовой работы (Excel+Word)


Автор:

Галушкина Наталия Владимировна,

старший преподаватель кафедры ИТ-2


Москва, 2005

Табличный процессор Excel.

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

После того, как Вы запустите Табличный процессор Excel (например, из Главного меню Windows с помощью команды ПУСКПрограммыMicrosoft Excel), Excel по умолчанию предлагает начать создание нового документа под условным названием Книга1. Не забудьте сохранить результаты своей работы: Файл – Сохранить как .

Заполнение Листа Основной список исходными данными

Сразу переименуем Лист1. Для этого нужно дважды щелкнуть на ярлычке листа и ввести новое имя – Основной список.

В ячейку А1 введем текст: «№». Зафиксируем данные, т.е сообщим программе об окончании ввода, например, нажатием клавиши Enter или щелкнем мышью по другой ячейке или перейдем к другой ячейке с помощью клавиш управления курсором.

Далее введем оставшиеся названия столбцов таблицы: в ячейку В1 – текст «Фамилия», в ячейку С1 – «Имя», в ячейку D1 – «Дата рождения». Вводимый текст в ячейку (Дата рождения) превышает по длине видимую ширину столбца. Для изменения ширины столбца можно перетащить мышью правый разделитель в строке заголовка столбца или дважды щелкнуть по разделителю столбца или использовать команду меню Формат - Столбец – Ширина (Автоподбор ширины или Стандартная ширина). Продолжаем вводить данные. В ячейку E1 введем текст «№ зач. книжки», в ячейку F1 – текст «Оц. За экз 1» (т.е. Оценка за экзамен 1), в ячейку G1 – «Оц. За экз 2», в ячейку H1 – текст «Оц. За экз 3», в ячейку I1 – «Средний балл», в ячейку J1 – «Категория успеваемости», в ячейку K1 – «Стипендия».





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

Заполняем ячейки исходными данными. В ячейку А2 введем цифру «1», в ячейку А3 – «2». Можно продолжать вводить в ячейки цифры, а можно использовать Автозаполнение. Для Автозаполнения нужно выделить обе ячейки (А2 и А3), далее протащить маркер заполнения (маленький черный квадрат в правом нижнем углу рамки выделенной ячейки) на несколько ячеек вниз. Важно помнить, что курсор должен принять форму черного крестика.



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



Далее заполняем столбец «Дата рождения».



Формат отображения числовых данных (даты рождения) можно изменить или задать на вкладке Число в диалоговом окне Формат ячеек, которое выводится на экран командой ФОРМАТЯчейки или комбинацией клавиш Ctrl+1.

И, наконец, заполняем столбец «№ зач. книжки». Номер зачетной книжки состоит из 6 символов, первые 5 из которых цифры, а последний символ – буква «п» или «б», что соответствует платным либо бюджетным студентам, например 03001п (первые две цифры соответствуют году поступления в ВУЗ, следующие цифры (три) – порядковый номер студента, присвоенный ему при поступлении, и буква «п» - студент, который учится на платной основе).



Прежде чем заполнять остальные столбцы на Листе Основной список, нужно заполнить столбец оценка на Листах 2, 3, 4, которые также должны быть переименованы на Ведомость1, Ведомость2, Ведомость3 соответственно.

Заполнение Листов 2, 3, 4 (Ведомость1, Ведомость2, Ведомость3)

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

На листе Ведомость1 в ячейку А1 вводим текст «Экзаменационная ведомость по ИНФОРМАТИКЕ». Применим форматирование к этой ячейке: выделим ячейки А1, В1, С1, D1, Е1, F1, объединим перечисленные ячейки, а текст поместим в центре ячейки. Форматирование ячеек выполняют с помощью вкладки Выравнивание в диалоговом окне Формат ячеек, которое выводится на экран командой ФОРМАТЯчейки ( на вкладке Выравнивание нужно установить переключатель напротив «объединение ячеек» и в поле Выравнивание – по горизонтали выбрать по центру) или с помощью кнопки (Объединить и поместить в центре), которая используется для объединения и центрирования текста в группе ячеек. Из-за того, что лист 2 предполагает наличие твердой копии и должен быть соответственно оформлен, изменим шрифт набранного текста (например, выделить ячейку с текстом А1, команда Формат ячеек – вкладка Шрифт – на вкладке выбираем полужирное начертание, размер 12 пт)



Далее вводим названия столбцов №, Фамилия, Имя, № зачетной книжки и Оценка или названия столбцов можно скопировать с листа Основной список (например, выделить копируемые ячейки, выполнить команду Правка – Копировать).



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

='Основной список'!$A2,

т.е. идет ссылка на лист Основной список и смешанная ссылка на первую ячейку с данными $А2 (не будет изменять номер столбца). Изменение типа ссылки для редактируемого адреса производится с помощью клавиши F4. После ввода формулы нажать Enter, для того чтобы зафиксировать ввод данных в ячейку.



Далее нужно распространить формулу на весь столбец А, перетащив маркер заполнения. В формуле автоматически изменится номер строки, а номер столбца останется без изменения.



Теперь нужно распространить формулу на оставшиеся ячейки, изменяя номер столбцам в формуле.



Далее заполняем столбец Оценка следующими оценками из списка: отл (отлично), хор (хорошо), уд (удовлетворительно), неуд (неудовлетворительно), н/я (не явка). В ведомости должны присутствовать все варианты. Лист Ведомость1 предполагает наличие твердой копии, поэтому добавим обрамление ячеек (рамку). Для этого выделим всю таблицу, выполним команду меню Формат ячеек – Граница и выберем тип линий для внешних и внутренних границ.



Лист 3 (Ведомость2) и лист 4(Ведомость3) заполняются аналогично. Только нужно изменить название предмета и можно изменить оценки. Кроме того, листы Ведомость2 и Ведомость3 можно создать путем копирования листа Ведомость1 (команда меню Правка – Скопировать лист и отметить Создавать копию), а затем исправить необходимые данные.

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

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

Можно создать (не обязательно) на листе Основной список в столбцах L и M дополнительную табличку оценок, чтобы потом использовать в формулах значения из этой таблицы.



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

В формуле будем использовать логическую функцию ЕСЛИ. Описание функций и примеры использования функций можно найти в Справке Microsoft Exsel.

Логические функции (всего 6 функций – ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ) используются для построения логических выражений, результат которых зависит от истинности проверяемого условия. Логические выражения строятся с помощью логических функций и операторов сравнения (равно =, меньше <, больше >, меньше или равно <=, больше или равно >=, не равно <>). Например, =ЕСЛИ(В4<100,100,200) расшифровывается так: если ячейка В4 содержит число меньше 100, то функции присваивается значение 100, если же условие не выполняется (В4 больше или равно 100), то функции присваивается значение 200.

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

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь).

• Лог_выражение (логическое выражение) - условие, которое требуется проверить. Что это такое? Это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100— это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае— ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения.
  
• Значение_если_истина — это значение, которое возвращается, если лог_выражение равно ИСТИНА. Например, если этот аргумент— строка «В пределах бюджета» и лог_выражение равно ИСТИНА, тогда функция ЕСЛИ отобразит текст «В пределах бюджета». Если лог_выражение равно ИСТИНА, а значение_если_истина пусто, то возвращается значение 0. Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. Значение_если_истина может быть формулой.
  
• Значение_если_ложь — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Например, если этот аргумент — строка «Превышение бюджета» и лог_выражение равно ЛОЖЬ, то функция ЕСЛИ отобразит текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а значение_если_ложь опущено (то есть после значение_если_истина нет точки с запятой), то возвращается логическое значение ЛОЖЬ. Если лог_выражение равно ЛОЖЬ, а значение_если_ложь пусто (то есть после значение_если_истина стоит точка с запятой с последующей закрывающей скобкой), то возвращается значение 0. Значение_если_ложь может быть формулой.
  

Когда значения аргументов значение_если_истина и значение_если_ложь вычислены, функция ЕСЛИ возвращает полученное значение. Например, в ячейке набираем формулу =ЕСЛИ(A2<=100;"Внутри бюджета";"Вне бюджета"). Что это означает? Если число, находящееся в ячейке А2, меньше либо равно числу 100, формула отображает строку «Внутри бюджета». В противном случае — строку «Вне бюджета»

Создадим формулу пересчета оценок. Для облегчения работы с встроенными функциями используется Мастер функций. Мастер – инструмент, позволяющий выполнить требуемое действие по шагам с уточнением параметров по каждому шагу. Для вызова Мастера функций можно использовать команду горизонтального меню окна Excel ВСТАВКАФункция, кнопку Вставка функции на панели инструментов и комбинацию клавиш Shift+F3.

На листе Основной список в ячейке F2 (столбец Оценка за экзамен1) вводим формулу:

=ЕСЛИ(адрес ячейки с листа с оценками в текстовой форме = “отл”;5;

ЕСЛИ(адрес той же ячейки с листа с оценками в текстовой форме = “хор”;4;

ЕСЛИ(адрес той же ячейки с листа с оценками в текстовой форме = “уд”;3;2)))

или, используя вспомогательную таблицу,

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

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

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

Далее нужно распространить формулы на оставшиеся ячейки столбца Оценка за экзамен1, а затем и на другие столбцы Оценка за экзамен2 и Оценка за экзамен3, изменяя в формуле лишь название листа Ведомость (заменяем Ведомость1 на Ведомость2 или Ведомость3).

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

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

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

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

Число1, число2, ...   — это от 1 до 30 аргументов, для которых вычисляется среднее. Аргументы должны быть либо числами, либо именами, массивами или ссылками, содержащими числа. Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.

Будем вычислять средние значения оценок для каждого студента. Сначала вычислим среднее арифметическое оценок первого студента (значения оценок находятся в ячейках F2, G2 и H2). В ячейку I2 (столбец Средний балл) введем формулу: =СРЗНАЧ($F2:$H2).



Далее распространим формулу на оставшиеся ячейки столбца Средний балл.



Для получившихся средних значений ограничимся лишь двумя знаками после запятой. Для этого нужно изменить формат ячеек: сначала выделим ячейки, в которых необходимо изменить формат ячеек, выполним команду Формат ячеек – на вкладке Число выберем Числовой формат и установим число десятичных знаков 2.

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

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

На листе Основной список в столбцах L, M и N создадим вспомогательную таблицу категории успеваемости.



Во вспомогательной таблице третий столбец (столбец N) является пояснением к данным, записанными в двух предыдущих столбцах (столбцах L и M).

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

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

Логическая функция И возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. Записывается следующим образом:

И(логическое_значение1; логическое_значение2; ...)

Логическое_значение1, логическое_значение2, ... — это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

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

ИЛИ(логическое_значение1;логическое_значение2; ...)

Логическое_значение1, логическое_значение2, ... — от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

Примерный вид формулы:

=ЕСЛИ(ИЛИ(адрес ячейки Оц. за экз1=2; адрес ячейки Оц. за экз2=2 адрес ячейки Оц. за экз3=2);адрес ячейки из вспомогательной таблицы (столбца подстановки), в которой находится слово “неуспевающий”; ЕСЛИ(адрес ячейки Средний балл первого студента>4,75; адрес ячейки из вспомогательной таблицы “отличник”;ЕСЛИ(И(адрес ячейки Средний балл первого студента >4,25; адрес ячейки Средний балл первого студента <4,75); адрес ячейки из вспомогательной таблицы “хорошо успевающий”; ЕСЛИ(И(адрес ячейки Средний балл первого студента >3,75; адрес ячейки Средний балл первого студента <4,25); адрес ячейки из вспомогательной таблицы “успевающий”; адрес ячейки из вспомогательной таблицы “ слабоуспевающий”))))

или, используя вспомогательные таблицы,

=ЕСЛИ(ИЛИ($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))))






Теперь распространим формулу на оставшиеся ячейки столбца Категория успеваемости.

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

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

Расчеты выполняем, т.е. вводим формулу, на листе Основной список в ячейке К2. Сначала нужно отсортировать платных студентов, которые не получают стипендию (значение стипендии равно 0). Для этого создадим (не обязательно) вспомогательную таблицу, в которой платным студентам будет соответствовать буква «п», а бюджетным –«б».



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



Формула для расчета стипендии должна включать следующие условия:

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

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

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

ПРАВСИМВ(текст;число_знаков)

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

Число_знаков — количество знаков, извлекаемых функцией ПРАВСИМВ. Число_знаков должно быть больше либо равно нулю. Если число_знаков больше длины текста, то функция ПРАВСИМВ возвращает весь текст. Если число_знаков опущено, то предполагается, что оно равно 1.

Пример: в ячейке А2 находится текст «Цена продаж». Формула =ПРАВСИМВ(A2;6) возвращает 6 знаков текста с конца (справа), находящегося в ячейке А2, т.е. слово «продаж».

Примерный вид формулы:

=ЕСЛИ(ПРАВСИМВ(адрес ячейки № зач кн)= “б”;

ЕСЛИ(И(ИЛИ(адрес ячейки Оц. за экз1>=3; адрес ячейки Оц. за экз2>=3; адрес ячейки Оц. за экз3>=3);адрес ячейки Категория успеваемости=”успевающий”);”300 рублей”;ЕСЛИ(адрес ячейки Категория успеваемости=”хорошо успевающий”;“600 рублей”;ЕСЛИ(адрес ячейки Категория успеваемости=”отличник”;“1200 рублей”;“0 рублей”)));“0 рублей”)

или, используя вспомогательные таблицы,

=ЕСЛИ(ПРАВСИМВ($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)



Далее распространяем формулу на оставшиеся ячейки столбца Стипендия.

Фильтрация данных

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

Выполним фильтрацию данных с помощью автофильтра. Например, отберем всех отличников.

Для выполнения Фильтрации данных с использованием автофильтра:

1. установим курсор внутри таблицы;
   
2. введем команду меню ДАННЫЕ  Фильтр  Автофильтр (на заголовках столбцов таблицы появятся кнопки со стрелками);
   

3. щелчком мыши по кнопке со стрелкой раскроем список столбца Категория успеваемости, по которому будет производить выборку;
   

4. укажем требуемое значение (отличник).
   

Таким образом, мы скрыли все записи списка, кроме тех записей, которые отвечают нашим требованиям – отличник.

Чтобы отобрать студентов по двум критериям, например, отберем бюджетных студентов и успевающих, снова воспользуемся автофильтром.



Далее щелчком мыши по кнопке со стрелкой раскроем список столбца № зач. книжки. Выберем строку «Условие» и зададим критерий выборки в диалоговом окне Пользовательский фильтр – «№ зач. книжки заканчивается на б». На экране отобразятся все бюджетные студенты.



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

Подведение промежуточных итогов

Снова скопируем лист Основной список, а новый лист назовем Итоги. Подведем промежуточные итоги: определим количество отличников, неуспевающих, успевающих, хорошо успевающих и слабоуспевающих студентов.

Перед вычислением промежуточных итогов следует выполнить сортировку по тем столбцам, по которым будут подводиться итоги: в нашем примере это столбец Категория успеваемости. Сначала выделим всю таблицу. Затем отсортируем данные в алфавитном порядке (по возрастанию). Сортировка данных производится с помощью команды меню ДАННЫЕ  Сортировка. В диалоговом окне Сортировка диапазона указываем требуемые параметры – тип и порядок сортировки, т.е. выбираем из списка Сортировать по столбец Категория успеваемости, а порядок сортировки устанавливаем по возрастанию.

До сортировки была таблица следующего вида:



После сортировки по возрастанию по столбцу Категория успеваемости:



Теперь можно подвести промежуточные итоги: определить количество студентов в каждой категории успеваемости, т.е. количество отличников, неуспевающих и т.д. Выделим всю таблицу с данными. Далее введем команду меню ДАННЫЕ  Итоги. В диалоговом окне Промежуточные итоги из списка При каждом изменении в: выберем столбец, содержащий группы, по которым необходимо подвести итоги, т. е. это тот же столбец, по которому проводилась сортировка списка – в нашем примере это столбец Категория успеваемости. Из списка Операция выберем функцию, необходимую для подведения итогов: Количество. В списке Добавить итоги по: выберем столбец, содержащий значения, по которым необходимо подвести итоги – это столбец Категория успеваемости. Фрагмент полученной таблицы приведен ниже.



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

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

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

Диаграммы будем строить на листе Основной список за вспомогательными таблицами (столбец Р и далее).

Построение круговой диаграммы.

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

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

В качестве диапазона укажем ячейки из основной таблицы (столбец J).В этих ячейках вычислялась категория успеваемости. Причем, укажем абсолютный адрес этих ячеек, т.е. $J$2:$J$8.

В качестве критерия укажем абсолютный по столбцу адрес ячейки из таблицы для построения круговой диаграммы, т.е. $P3 (в ячейке Р3 находится текст «отличник») или можно было указать в качестве критерия текст “отличник” (затем критерий можно было изменить на “хорошо успевающий”, ”успевающий” и т.д.).

Примерный вид формулы: =СЧЁТЕСЛИ($J$2:$J$8;$P3). Далее нужно распространить формулу на оставшиеся ячейки: указанный диапазон изменяться не будет, а будет изменяться критерий (номер строки столбца Р). Для одного и того же диапазона будем определять сначала количество отличников, затем хорошо успевающих, успевающих студентов и т.д.

Полученная таблица имеет вид:



Количество всех студентов можно подсчитать, использую функцию суммирования ячеек (СУММ(Q3:Q7)).



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

Сравните таблицу с промежуточными итогами и выше приведенную таблицу:



Теперь можно построить круговую диаграмму. Для этого выделим столбцы таблицы для диаграммы (Р и Q), кроме заголовка таблицы и строки, содержащей общее количество (итого).



Далее вызываем Мастер диаграмм (кнопка ). Следуем указаниям Мастера. Выбираем Тип диаграммы – Круговая, Вид (первая).



Щелкаем мышью на кнопке Далее. Диапазон данных будет уже указан.



Снова щелкаем на кнопке Далее.



И постепенно заполняем предложенные пункты на разных вкладках: название диаграммы – Количество студентов в категориях успеваемости. Название сразу появляется в окне справа.



Легенду можно убрать.



Добавляем Подписи данных – имена категорий.



Щелкаем на кнопке Далее. Диаграмму размещаем на листе Основной список.



Щелкаем на кнопке Готово. Получится диаграмма следующего вида:



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



Можно изменить Тип диаграммы на объемный вариант круговой диаграммы.

Построение столбчатой диаграммы.

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

Минимальный средний балл рассчитывается по формуле: =МИН(I2:I8). Для расчета минимального значения используется функция МИН(число1;число2;…), которая возвращает наименьшее значение в списке аргументов (от 1 до 30 чисел, среди которых требуется найти наименьшее). В скобках указан диапазон средних значений 7 студентов (напомню, что рассматривается список из 7 студентов) – это ячейки (I2:I8).

Максимальный средний балл рассчитывается по формуле: =МАКС(I2:I8)). Для расчета максимального значения используется функция МАКС(число1;число2;…), которая возвращает наибольшее значение в списке аргументов (от 1 до 30 чисел, среди которых требуется найти наибольшее). В скобках указан тот же диапазон средних значений 7 студентов (ячейки (I2:I8)), что и для нахождения наименьшего значения.

Средний по среднему рассчитывается по той же формуле, по которой рассчитывался средний балл, т.е. по формуле, используя функцию СРЗНАЧ для всех ранее вычисленных средних баллов студентов: =СРЗНАЧ(I2:I8).

Средний балл самого студента можно ввести, например, как абсолютный адрес на ячейку, в которой находится данное значение, например, =$I$7.

Построенная таблица может иметь вид:



Теперь можно построить круговую диаграмму. Для этого выделим столбцы таблицы для диаграммы (Р и Q).



Далее вызываем Мастер диаграмм (кнопка ). Следуем указаниям Мастера. Выбираем Тип диаграммы – Гистограмма, Вид (первая).



Щелкаем мышью на кнопке Далее. Диапазон данных будет уже указан.



Снова щелкаем на кнопке Далее.



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



Добавляем Подписи данных – значения.



Щелкаем на кнопке Далее. Диаграмму размещаем на листе Основной список.



Щелкаем на кнопке Готово. Получится диаграмма следующего вида:



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



Курсовая работа в Excel готова! Теперь можно перейти к выполнению Курсовой работы в текстовом редакторе Word.