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

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

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

Таблица 4.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

• ⇐ Назад
• -2
• -1
• 0
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8910
• 11
• 12
• 13
• Далее ⇒