Методические рекомендации к выполнению практических заданий

Составление таблиц и построение диаграмм средствами Microsoft Excel

Средствами табличного процесса Excel составить таблицу 4.1– «Результаты экзаменационной сессии». Первая фамилия в списке – фамилия исполнителя. Остальные фамилии начинаются со следующих букв фамилии исполнителя. Заполнить произвольным образом графы «Оценка» и «Дата сдачи» для каждого предмета. Оформить границы в таблице согласно приведенному образцу. Заголовок таблицы оформить отличительным шрифтом.

Таблица 4.1 – Результаты экзаменационной сессии

Ведомость начисления стипендии по результатам сессии
ФИО	Предметы	Средний балл	Сумма баллов	Размер стипендии
Высш.мат.	Информатика	История	Ин. Яз.
оценка	дата сдачи	оценка	дата сдачи	оценка	дата сдачи	оценка	дата сдачи	Начислено
Иванов И. И.		11.1		14.1		20.1		25.1
Власов П. С.		11.1		14.1		20.1		25.1
Анохин С. Б.		11.1		14.1		20.1		25.1
Новиков Н. С.		11.1		14.1		20.1		25.1
Одинцов А. Т.		11.1		14.1		30.1		5.2
Воронов Д. Л.		11.1		14.1		20.1		25.1
Средний балл по предмету									Итого:

Вычислить значения для диапазонов «Сумма баллов» и «Средний балл по сессии». В ячейках диапазона «Размер стипендии» вывести сумму стипендий с доплатой за успеваемость по следующей схеме: стипендия не выплачивается, если сессия сдана с тройкой. Если сессия сдана с двумя четверками, то размер стипендии увеличивается на 25 %. Если сессия сдана с одной четверкой, то размер стипендии увеличивается на 50 %. Если сессия сдана на отлично, то стипендия увеличивается в два раза.

Если сумма стипендии увеличена в два раза, то ее выводить в таблице красным цветом.

Подсчитать общую сумму выплат стипендий.

На отдельном листе построить график, отражающий сумму баллов каждого студента.

Создание таблицы

1 Создать новую книгу MS Excel.

2 Сохранить эту книгу в файле в личной папке одним из известных способов.

3 Выделить на рабочем листе диапазон A1:L11. Отформатировать таблицу: Формат ® Ячейки ® Границы (рис. 4.1). Выбрать параметры согласно условию (толстая линия – внешние; тонкая – внутренние).

Рисунок 4.1

4 Выделить диапазон A1:L1. Формат ® Ячейки ® Выравнивание (рис. 4.2), установить флажок Объединение ячеек.

Рисунок 4.2

5 Аналогичные действия проделать для диапазонов А2:А4; B2:I2; В3:С3; D3:E3; F3:G3; H3:I3; J2:J4; K2:K4; J11:K11.

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

7 Установить выравнивание текста в ячейках (рис. 4.3) согласно условию для диапазонов A1:L1; A2:A4; B2:I2; J2:J4; K2:K4; L2.

Рисунок 4.3

8 Для надписей «дата сдачи», «Средний балл», «Сумма баллов» и «Размер стипендии» установить флажок Переносить по словам (рис. 4.4):

Рисунок 4.4

9 Для диапазонов С5:С10; Е5:Е10; G5:G10; I5:I10 установить соответствующий формат даты. Для этого выделить соответствующие диапазоны Формат ® Ячейки ® Число ® Дата. Выбрать тип согласно условию (рис. 4.5).

Рисунок 4.5

10 Для ячеек L3 и L5:L11 установить денежный формат. Для этого выделить соответствующие диапазоны Формат ® Ячейки ® Число ® Денежный (рис. 4.6).

Рисунок 4.6

11 Установить режим автоподбора ширины столбцов. Выделить таблицу. Из пункта Формат ® Столбец выбрать Автоподбор столбца (рис. 4.7). Или двойным щелчком по границе между именами столбцов установить автоподбор ширины для каждого столбца в отдельности.

Рисунок 4.7

12 Выделить ячейки с надписью «Ведомость начисления стипендии по результатам сессии», выбрать вкладку Формат ® Ячейки ® Шрифт. Установить тип шрифта Times New Roman; Начертание – полужирный; Размер – 14 (рис. 4.8).

Рисунок 4.8

13 Для ячеек B11; D11; F11; H11; J5:J10; K5:K10 установить формат ячеек: Формат ® Ячейки ® Число ® Числовой. Установить Число десятичных знаков – 2 (рис. 4.9).

Рисунок 4.9

После выполнения всех действий исходная таблица будет выглядеть следующим образом (рис. 4.10):

Рисунок 4.10

Расчеты

1 Сделать ячейку J5 активной.

2 Вызвать Мастер функций. Выбрать в категории Статистические функцию СРЗНАЧ(), а затем нажать кнопку ОК (рис. 4.11).

Рисунок 4.11

Щелчком по кнопке Минимизация, минимизировать окно второго шага мастера функций и удерживая кнопку CTRL выделить мышью ячейки B5;D5;F5;H5 (рис. 4.12).

Рисунок 4.12

Щелчком кнопки Минимизация (рис. 4.13) восстановить окно второго шага мастера функций и нажать кнопку ОК.

Рисунок 4.13

3 Используя маркер автозаполнения, заполнить диапазон К6:К10.

4 Аналогичными действиями рассчитать значения в ячейке B11 (используя в качестве диапазона функции СРЗНАЧ() ячейки В5:В10). Скопировать используемую формулу для ячеек D11; F11; H11.

5 Сделать ячейку K5 активной.

6 Вызвать Мастер функций. Выбрать в категории Математические (рис. 4.14) функцию СУММ(), а затем нажать кнопку ОК.

Рисунок 4.14

Щелчком по кнопке Минимизация минимизировать окно второго шага мастера функций и, удерживая кнопку CTRL, выделить мышью ячейки B5; D5; F5; H5 (рис. 4.15).

Рисунок 4.15

Щелчком кнопки Минимизация восстановить окно второго шага мастера функций (рис. 4.16) и нажать кнопку ОК.

Рисунок 4.16

7 Используя маркер автозаполнения, заполнить диапазон J6 : J10.

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

9 В нашем случае это будет: если (все из условий (B5 > 3; D5 > 3; F5 > 3; H5 > 3) имеют значение истина; тогда если K5 < 18; тогда содержимое текущей ячейки приравнивается к L3, иначе, если К5 = 18; тогда содердимое текущей ячейки приравнять к L3, увеличенному на 25 %; иначе если К5 = 19; тогда содержимое текущей ячейки приравнять к L3 увеличенному на 50 %; иначе содержимое текущей ячейки приравнять к L3 увеличенному вдвое; иначе (для самого первого если) в текущей ячейки вывести слово «отказать».

10 Записать вышеприведенное высказывание (рис. 4.17) по правилам Excel, получим формулу:

= ЕСЛИ (И (B5 > 3;D5 > 3; F5 > 3; H5 > 3); ЕСЛИ (K5 < 18; $L$3; ЕСЛИ (K5 = 18; $L$3 * 25 % + $L$3; ЕСЛИ (K5 = 19; $L$3 * 50 % + $L$3; $L$3 * 2))); «отказать»)

Рисунок 4.17

11 Установив активной ячейку L5 и выбрав команду Формат ® Условное форматирование устанавливаем параметры полей. Ссылку = $L$3 можно ввести, минимизировав окно «Условное форматирование» и щелкнув мышью по ячейке L3, а остальную часть формулы ввести с помощью клавиатуры непосредственно в поле (рис. 4.18), а затем, щелкнув по кнопке Формат, установить в появившемся окне «Формат ячейки» красный цвет символов (рис. 4.19).

Рисунок 4.18

Рисунок 4.19

12 Воспользовавшись автозаполнением поместить формулу из ячейки L5 в ячейки L6:L10.

13 В ячейке L11 записать формулу: =СУММ(L5:L10).

Рассчитанная таблица имеет вид, представленный на рисунке 4.20:

Рисунок 4.20

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

По стандарту любая диаграмма строится за четыре шага.

Шаг 1: Вызываем Мастер диаграмм. Выбираем Тип диаграммы График с маркерами, помечающими точки данных (рис. 4.21). Нажимаем клавишу Далее.

Рисунок 4.21

Шаг 2: В строке Диапазон данных указываем ячейки К5 : К10. Для этого минимизируем окно Диапазон данных и выделяем курсором мышки требуемые ячейки (рис. 4.22).

Рисунок 4.22

Переходим на вкладку Ряд (рис. 4.23) и указываем Имя ряда и подписи по оси Х, выделив соответствующие ячейки К2 и А5 : А10.

Рисунок 4.23

Нажимаем кнопку Далее.

Шаг 3: В окне Параметры диаграммы, вкладка Заголовки (рис. 4.24) добавляем название диаграммы и подписи осей.

Рисунок 4.24

На вкладке Легенда (рис. 4.25) убираем флажок с опции Добавить легенду.

Рисунок 4.25

На вкладке Линии сетки (рис. 4.26) убираем флажок с опции основные линии (для оси У).

Рисунок 4.26

На вкладке Подписи данных (рис. 4.27) ставим флажок на опции Значения.

Рисунок 4.27

Нажимаем кнопку Далее.

Шаг 4 Выбираем месторасположения диаграммы. Активизируем опцию Отдельном и вводим название нового листа «Сумма баллов набранных каждого студента» (рис. 4.28).

Рисунок 4.28

Редактируем полученную диаграмму. Для того чтобы убрать серый фон с диаграммы вызываем контекстное меню (рис. 4.29)

Рисунок 4.29

Выбираем вкладку Формат области построения: Заливка – прозрачная (рис. 4.30).

Рисунок 4.30

С помощью редактирования рядов данных (контекстное меню ® Формат рядов данных) изменяем цвет и толщину линий и маркеров (рис. 4.31, 4.32).

Рисунок 4.31

Рисунок 4.32

Полученный график (рис. 4.33) будет выглядеть следующим образом.

Рисунок 4.33