Лабораторная работа №5
Тема: Электронные таблицы Microsoft Excel. Создание таблиц с расчётными
формулами. Абсолютная и относительная ссылка в EXCEL. Создание диаграмм в
EXCEL
Цель работы: Приобретение практических навыков создания и обработки таблиц
средствами MS Excel, научится использовать абсолютные и относительные
ссылки, строить диаграммы и графики.
Порядок выполнения работы
1. Подготовка к выполнению работы (предварительное ознакомление с
методическими указаниями к лабораторной работе).
2. Выполнение заданий к работе.
3. Составление отчета о выполнении работы.
4. Защита работы.
Короткие теоретические сведения
Под таблицей в EXCEL понимают компьютерный эквивалент обычной
таблицы, в клетках (ячейках) которой записаны данные различных типов: тексты,
даты, формулы, числа.
Главным достоинством электронной таблицы EXCEL является возможность
мгновенного пересчета всех данных, связанных формулами при изменении
значения любого операнда.
Данные в таблицу вводятся по ячейкам. Ячейка, в которую в данный момент
вводят данные (т.е. в которой в данный момент находится курсор), называется
активной ячейкой.
С числами в EXCEL можно производить различные математические действия
(сложение, умножение, вычитание, деление, вычисление среднего значения,
вычисление минимального и максимального значения и др.). Для выполнения
вычислений установите курсор в нужной ячейке и введите формулу.
Формула начинается со знака « = » и представляет собой совокупность
математических операторов, чисел, ссылок и функций.
Результат вычисления помещается в ячейку, в которой находится формула.
Команда Автоформат таблицы из меню Формат предоставляет ряд стандартных
стилей разметки, применяемых к таблицам – обрамление, заполнение и прочие
опции форматирования.
Под функцией в EXCEL понимают программу с уникальным именем, для которой
пользователь должен задать конкретные значения аргументов функции, стоящих
в скобках после ее имени.
Функции вводят в таблицу в составе формул либо отдельно.
В электронных таблицах могут быть представлены следующие виды функций:
•
•
•
•
•
•
Математические
Статистические
Текстовые
Логические
Финансовые
Функции даты и времени и др.
Функции вводятся с помощью диалогового окна «Мастер функций», которое
вызывается командой ВСТАВКА, Формула или кнопкой
Для каждой категории функции справа в окне Мастера функций показан их
состав. Выбирается категория функции (слева), имя функции (справа), внизу
дается краткий синтаксис функции.
Правила построения формул с помощью «Мастера функций»:
Активизировать ячейку, в которой необходимо получить результат
•
•
Вызвать Мастер функций
В диалоговом окне Мастера функций выбрать необходимую для
расчета функцию
•
В диалоговом окне выбранной функции ввести адрес блока
ячеек используемых в расчете
При копировании или перемещении формулы в другое место таблицы
необходимо организовать управление формированием адресов исходных данных.
Поэтому в электронной таблице при написании формул наряду с введенным ранее
понятием ссылки используются понятия Относительной и Абсолютной ссылок.
Абсолютная ссылка – это не изменяющийся при копировании и перемещении
формулы адрес ячейки, содержащий исходное данное (операнд).
Для указания абсолютной адресации вводится символ $ . Различают два типа
абсолютной ссылки: полная и частичная.
Полная абсолютная ссылка указывается, если при копировании и перемещении
адрес клетки, содержащий исходное данное, не меняется. Для этого символ $
ставится перед наименованием столбца и номером строки.
Например: $B$5 ;
$D$12 - полные абсолютные ссылки
Частичная абсолютная ссылка указывается, если при копировании и перемещении
не меняется номер строки или наименование столбца. При этом символ $ в первом
случае ставится перед номером строки, а во втором – перед наименованием
столбца.
Например: B$5 ;
строки;
D$12 - частичная абсолютные ссылка, где не меняется номер
Например: $B5 ;
$D12 - частичная абсолютные ссылка, где не меняется
наименование столбца.
Относительная ссылка – это изменяющийся при копировании и перемещении
формулы адрес ячейки, содержащий исходное данное (операнд). Изменение
адреса происходит по правилу относительной ориентации клетки с исходной
формулой и клеток с операндами. Форма написания относительной ссылки
совпадает с обычной записью.
Правило относительной ориентации клетки
Формула, где в качестве операндов используются ссылки ячеек, воспринимается
системой как шаблон, а ссылки ячеек в таком шаблоне – как средство указания на
местоположение ячеек с операндами относительно ячейки с формулой.
Данные, содержащиеся в клетках электронной таблицы, можно представить в
графическом форме – в виде диаграмм. Такой способ представления информации
обеспечивает наглядность и позволяет удобнее и быстрее анализировать
результаты обработки данных.
Для представления данных электронной таблицы в виде диаграммы необходимо
сначала указать эти данные, выделив нужный объект (строку, столбец или блок
ячеек) таблицы.
Диаграмма представляет собой самостоятельный объект электронной таблицы и
характеризуется рядом параметров, которые задаются при создании и могут быть
изменены при редактировании диаграммы.
К основным параметрам диаграммы относятся:
•
Тип диаграммы;
•
Заголовок диаграммы;
•
Рамка;
•
Координатная сетка.
К дополнительным параметрам:
•
Направление расположения ряда (по горизонтали или по вертикали);
•
Наличие или отсутствие легенды;
•
Надписи на осях и некоторые другие, описывающие свойства выводимой
диаграммы.
Для создания диаграммы необходимо:
•
выделить объект, содержащий данные для ее построения;
•
перейдите на вкладку Вставка и в группе Диаграммы щёлкните на кнопке
График.
•
выбрать тип диаграммы из предлагаемого набора;
•
задать основные и дополнительные параметры диаграммы
Задание для работы:
Задание 1. Создайте и оформите нижеследующую таблицу на листе FIZRA1 в
созданном файле Задания по Excel.xls в папке «Лабораторная работа 5».
A
1
2
B
C
D
E
F
Рост
Норма
роста
Вес
Норма
веса
Физическое развитие
Фамилия
3
4 Пол: Девочки
5 Замятина Анна
6 Захарова
Светлана
7 Арканова Ольга
8 Волошина
Наталья
9 Иванова
Екатерина
10 Копылова
Светлана
11 Левченко Ольга
12 Свиридова Елена
13 Удалова Мария
14 Среднее
значение:
15
16 Пол: Мальчики
17 Новосельцев
Андрей
18 Иванов Иван
19 Титов Владислав
20 Рогожин Павел
21 Шейнин Алексей
22 Воронин
Александр
23 Петров Денис
24 Булавин Михаил
25 Савельев Михаил
26 Суворов Сергей
Дата
рождения
21.09.83
24.11.84
153
151
45
45
15.10.83
15.07.83
154
162
49
51
07.01.83
160
51
22.01.83
154
57
24.03.83
23.02.84
10.05.84
162
171
167
68
73
50
12.12.83
154
45
12.09.84
29.06.83
07.03.84
06.06.84
26.03.84
153
165
164
161
170
47
52
53
56
60
26.01.84
13.08.83
06.04.84
10.01.84
171
175
158
149
63
65
54
61
27 Егоров Николай
28 Среднее
значение:
07.10.83
173
71
1.
Отсортируйте по алфавиту - отдельно список девочек и отдельно список
мальчиков.
2.
Вычислите при помощи Мастера функций среднее значение Роста и Веса
отдельно для мальчиков и отдельно для девочек.
3.
Заполните столбцы «Норма роста», «Норма веса», введя в ячейки
соответствующие формулы. За норму у девочек примите: рост 150-165, вес 51-56.
За норму у мальчиков – рост 150-170, вес 51-60.
4.
Название таблицы выполните подчеркнутым полужирным шрифтом
16 размера.
5.
Названия «Пол: Девочки», «Пол: Мальчики» отформатируйте полужирным
подчеркнутым шрифтом.
6.
Строки Среднего значения Роста и Веса отформатируйте полужирным
шрифтом.
Задание 2. Одна из наиболее распространенных ситуаций в жизни – покупка
товара. Магазин по продаже компьютерных аксессуаров продает товары,
указанные в прайс-листе. Стоимость товара указана в долларах. Если
стоимость товара превышает 250 руб., покупателю предоставляется 5%
скидка.
1. Создайте и оформите нижеследующую таблицу на листе PRICE1 в
файле Задания по Excel.xls в папке «Лабораторная работа 12».
A
1 курс доллара
2 скидка
3 сумма в руб. для учета
скидки
4
5 Прайс-лист
6 наименование товара
7 дискеты 3,5"
8 дискеты 5,25"
9 бокс для дискет 3,5"
10 бокс для дискет 5,25"
11 мышь
12 мышь
13 коврик для мыши
14 держатель листа(холдер)
B
7,987
0,05
250
C
D
цена ($) количест цена
во
(руб)
0,3
21
0,25
56
2
3
2,5
6
8
1
13
0
2,5
1
1
0
E
стоимость
(руб)
15
16 Итого в руб.:
17 Сумма в руб. с учетом
скидки:
Заполните столбцы «Цена (руб)», «Стоимость (руб)», введя в ячейки
соответствующие формулы.
Вычислите при помощи Мастера функций сумму «Итого» для столбца
«Стоимость».
Вычислите при помощи, ввода логической формулы, сумму итого в руб. с
учетом скидки.
Строки 5, 6, 16, 17 отформатируйте полужирным шрифтом.
180
22
Петров
Экономист
12
140
24
Марков
Программист
5
150
19
Сивов
Инженер
9
123
19
Носов
шофер
1
95
24
Итог.
с
учетом
стажа, руб.
15
Сумма
к
выдачи, руб.
Кол-во
отработан.
дней в мес.
Начальник
В
пенсионный
фонд, руб.
Тариф, руб
Иванов
Подоходный
налог, руб
должность
Начисленная
з/плата, руб
ФИО
Стаж, лет
Задание 3. Создание таблицы и построение диаграммы.
1. На листе ДИАГРАММА в файле Задания по Excel.xls в папке
«Лабораторная работа 12» рассчитать заработанную плату с учётом
подоходного налога, отчислений в пенсионный фонд, стажа работы:
Подоходный налог – 13%,
Отчисления в пенсионный фонд – 1%,
Итоговая з/плата – при стаже > 3 лет, з/плата умножается на
коэффициент 1,15.
2. Построить гистограмму с накоплением в виде пирамид.
3. Сохраните работу под именем ДИАГРАММА.xls в папке «Лабораторная
работа 12».
Структура отчета
Название, тема, задания, контрольные вопросы. Краткое описание
технологии решения поставленной задачи. Выводы по работе.
Контрольные вопросы
1. Сколько столбцов и сколько строк содержится в одном рабочем листе таблицы?
2. По какой малозаметной детали можно определить, только что открыт документ,
представленный на первом рисунке или в нем уже производились какие-то
изменения?
3. При перемещении указателя мыши по таблице его вид меняется в зависимости
от того, над каким элементом изображения он проходит. Сколько всего таких
видов и для чего они предназначены?
4. Что такое автозаполнение, когда оно используется?
5. Какие виды диаграмм можно построить в Excel?
6. Какой новый вид диаграмм появился в Excel 2016?
7. Какие типы данным могут присутствовать в ячейках Excel?
8. Как визуально можно отличить введен текст или числа в ячейках Excel?
9. Какие существуют аналоги Microsoft Excel?
10. Какие вы может назвать способы вставки функции в формулу?
Узнать о новых возможностях Excel 2016 можно по ссылке: http://msofficeprowork.com/masshtabnoe-obnovlenie-microsoft-office-2016-preview-sborka-160-6568-2016/
