Составление таблиц и построение диаграмм средствами 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
