Задание 2

1. В электронной книге Экз_Ведомость.xls,созданной при выполнении вышеописанного основного задания, образуйте с помощью контекстного меню ярлычка листаВедомость его копию. Переименуйте лист-копию какРасширенная_ведомость.На новом листе дополните таблицу по горизонтали (строка с наименованиями дисциплин) наименованиями 4-х дополнительных столбцов“Сум. балл”, “Ср. балл”, “Кол-во 4 и 5”, “Кол-во двоек”, “Самая повт. оценка”. Отформатируйте при желании содержимое этих новых ячеек с поворотом на 90 градусов (предварительно выделить их, в контекстном меню выделенной области выбрать командуФормат ячеек… и далее на вкладке Выравнивание в окне Ориентациящелкнуть на нужном прямоугольничке, обеспечивающем поворот текста в ячейках на 90 градусов). Такое форматирование в шапке таблицы позволяет сделать таблицу намного уже по ширине, чем это было бы при начальном занесении.

2. Дополните таблицу по вертикали (столбцы с № п/п и с ФИО, клетки которых в каждой новой строке для этой цели можно объединить в одну, используя пиктограмму Объединить и поместить в центрепанелиФорматирование) такими же наименованиями (копированием через буфер обмена), а также ещё 2-мя наименованиями“% успеваемости”и“% качества”.

3. Далее, изучив по лекции или самостоятельно возможности Мастера функций (ВставкаФункция… или щелчок по пиктограмме.f_x на панелиФорматирование), занесите в соответствующие ячейки расчетные формулы для 1-го студента, используя встроенные функцииСУММ(…), СРЗНАЧ(…),два разаСЧЕТЕСЛИ(…,…), МОДА(…)и задавая в качестве аргументов для них в скобках адресный диапазон ячеек с оценками для данного студента по всем дисциплинам сессии, а для функцийСЧЕТЕСЛИдополнительно еще и критерий для подсчета нужных ячеек в виде выражения“>=4”(или“>3”) и“<3”(или“<=2”). При формировании расчетных формул с помощью Мастера функций учитывайте, что встроенные функции суммирования относятся к категории математических функций, а все другие, нужные вам в этой работе, – к категории статистических. Примеры подобных формул: формула=СРЗНАЧ(C3:F3) вычисляет среднее арифметическое значение содержимого всех ячеек блока, начиная от ячейки с адресом С3 и заканчивая ячейкой с адресомF3; формула=СЧЕТЕСЛИ(C3:F3;”>=4”) вычисляет (определяет) количество ячеек (среди всех ячеек блока от С3 доF3) с численным содержимым, большим или равным числу 4 (т.е. количество клеток с четверками и пятерками).

4. Проконтролируйте визуально достоверность полученных по формулам значений результатов. Если результаты недостоверны (или явно не верны), то найдите и исправьте ошибки в формулах. Если по завершении ввода формул вместо ожидаемых результатов вы видите по-прежнему образ (текст) формулы, то, вероятно, вы забыли (при ручном наборе с клавиатуры) в самом начале текста формулы набрать знак “=”. Вход в режим редактирования содержимого ячейки (при необходимости внести в уже набранную формулу изменения) – либо двойной щелчок ЛКМ по нужной ячейке, либо выделение ячейки (1 щелчок) и нажатие на клавиатуре функциональной клавиши F1. Вносить изменения в содержимое выделенной ячейки можно также в строке формул, над адресным бордюром имен столбцов электронной таблицы, перещёлкнувшись туда мышкой.

5. Выделите все 5 формул, занесенных для первого студента, и мышкой через общий маркер автозаполнения (элемент управления в правом нижнем углу выделенной области, позиционировать туда указатель мыши до появления маленького черного крестика и при нажатой ЛКМ тянуть вниз через нужную область) распространите их на ячейки для всех студентов таблицы.

6. Занесите аналогичные формулы и распространите их аналогичным образом для расчета показателей по каждой дисциплине, соответственно занесенным в столбце с ФИО наименованиям ниже основной таблицы ведомости.

7. В строку вашей таблицы, предназначенную для расчета процента успеваемости, занесите для первой дисциплины формулу со смыслом “кол-во студентов, не имеющих двоек по дисциплине, поделенное на общее кол-во студентов”. Используйте для неё в числителе встроенную функцию СЧЕТЕСЛИ(…),с указанием в качестве аргументов адресного диапазона оценок по первой дисциплине для всех студентов и критерия выбора вида“>2”, а в знаменателе – встроенную функциюСЧЕТ(…),с указанием в качестве аргумента того же адресного диапазона. Далее растиражируйте эту формулу с помощью маркера автозаполнения в ячейки и для других дисциплин.

8. В строку вашей таблицы, предназначенную для расчета процента качества успеваемости по дисциплинам, занесите для первой дисциплины формулу со смыслом “кол-во студентов, имеющих оценки по дисциплине только выше троек, поделенное на общее кол-во студентов”. Используйте для неё в числителе встроенную функцию СЧЕТЕСЛИ(…),с указанием в качестве аргументов адресного диапазона оценок по первой дисциплине для всех студентов и критерия вида“>3” для выбора ячеек в указанном диапазоне (либо можно сделать просто адресную ссылку на ячейку с подсчитанным выше количеством четверок и пятерок), а в знаменателе – встроенную функциюСЧЕТ(…),с указанием в качестве аргумента того же адресного диапазона. Далее размножьте эту формулу с помощью маркера автозаполнения в ячейки и для других дисциплин.

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

10. С помощью мастера диаграмм постройте по новым составляющим таблицы (на этом же листе или на отдельных листах, на свое усмотрение) 2 линейных графика: 1-й график – средние баллы по всем студентам группы (выделив столбец ФИОи при нажатой клавишеCtrlстолбецСр. балл, значения вместе с наименованиями), 2-й – показатели успеваемости и качества по всем дисциплинам, в одной системе координат (выделив предварительно строку таблицы с наименованиями дисциплин и при нажатой клавишеCtrlстроки со значениями процентов успеваемости и качества, без наименований). Для 2-го графика на 2-м шаге мастера диаграмм (вкладкаРяд) не забудьте дать названия своим рядам данных, сославшись в соответствующих полях ввода на ячейки с наименованиями рядов данных (щелчками мышкой по клеткам с наименованиями показателей). Если графики созданы на отдельных листах, дайте новые имена этим листам (например,График1иГрафик2).

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

На рисунках 1- 5 представлены рисунки экранов листов электронной книги с фрагментами таблицы в режиме показа значений и в режиме показа формул, а также с диаграммами.

Рисунок 1 – Пример результирующей таблицы в режиме значений

Рисунок 2 – Фрагмент таблицы с вертикальной статистикой в режиме формул

Рисунок 3 – Фрагмент таблицы со статистикой по горизонтали и общей статистикой в режиме формул

Дополнительные рекомендации по форматированию и настройкам документов в электронных таблицах

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

Для преобразования листа таблицы из режима показа значений в режим показа формул везде, где они присутствуют (для печати отчета по работе или для проверки правильности проектирования расчетной части таблиц), необходимо применить команду СервисПараметры…и в диалоговом окнеПараметрына вкладкеВидв группеПараметры окнаактивизировать флажокформулы. Далее отрегулировать ширину столбцов на листе таким образом, чтобы формулы были видны полностью, а не обрезались. Для печати листов с отображением таблиц в режиме показа формул необходимо также осуществить настройку параметров страницы на показ адресного бордюраExcel(т.е., заголовков строк и столбцов):Файл Параметры страницы…, и в диалоговом окнеПараметрына вкладкеЛиств группеПечатьустановить флажкисеткаизаголовки строк и столбцов. Для сохранения в электронной книге образов таблицы как в режиме значений, так и в режиме показа формул желательно после проектирования таблицы создать копию листа таблицы (без графиков и диаграмм), а затем этот лист с копией перенастроить на режим показа формул, как это было рекомендовано выше.

Ширину столбцов таблицы принято планировать по внутреннему содержимому столбцов, а не по названиям их. Поэтому клетки (ячейки) “шапки” таблицы, содержащие длинные наименования столбцов (или строк), рекомендуется форматировать с переносом слов внутри ячейки в рамках установленной ширины клеток. Для этого надо выделить нужные блоки ячеек и применить команду ФорматЯчейки…, далее в появившемся диалоговом окнеФормат ячеекна вкладкеВыравниваниев группеОтображениеустановить флажокпереносить по словам. Обратите внимание на то, что там же есть полезные флажки объединение ячеек и автоподбор ширины, а в группеВыравнивание– раскрывающиеся спискипо горизонталиипо вертикали, с помощью которых вы можете заодно заказать выравнивание, например,по центру, как по горизонтали, так по вертикали (или по-разному выборочно). Более быстрый вызов диалогового окнаФормат ячеек для выделенных клеток – с помощью горячих клавиш Ctrl+1 (один).

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