2.2. Лабораторная работа 2

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

Цель работы:

• закрепить навыки копирования и редактирования таблиц, ввода формул при помощи мастера функций;

• понять разницу между абсолютными и относительными ссылками на ячейки и научиться правильно их использовать, научиться вводить ссылки на ячейки, содержащиеся на другом рабочем листе;

• научиться отображать данные таблиц в виде диаграмм.

Пример задания

На основе таблицы «Список товаров на овощном складе», соз­дать таблицу-справочник «Цены на фрукты»:

• номер по порядку;

• наименование товара;

• цена единицы товара.

Пользуясь этим справочником и считая, что в течение трех ме­сяцев держались стабильные цены, создать две таблицы: «Продажи фруктов в первом квартале текущего года», содержащую данные о продажах фруктов за январь, февраль и март в количественном и стоимостном выражении и таблицу «Нарастающие итоги продажи фруктов в первом квартале», содержащую те же столбцы, но содер­жащие суммарные данные текущего и предыдущих месяцев. Все три таблицы разместить сначала на одном рабочем листе, а затем пере­нести две таблицы по продажам на отдельный рабочий лист. Содер­жимое таблицы «Продажи фруктов в первом квартале текущего года» отобразить в виде диаграмм.

Порядок выполнения работы

Вид рабочего листа изображен на рис.2.2.

Сделаем активным лист рабочей книги, в котором содержится таблица «Список товаров на овощном складе» и скопируем эту табли­цу на чистый рабочий лист. Удалив лишние столбцы, получим спра­вочник цен. Затем оформим шапку следующей таблицы и скопируем содержимое столбцов «№», «Название» и «Единицы измерения». Все столбцы «Кол-во» заполняем произвольными данными.

Теперь осталось ввести формулы в столбцы «Сумма». Во всех трех таких столбцах это практически одна и та же формула, поэтому введем ее в ячейку Е16, а затем скопируем во все остальные ячейки. С помощью мастера функций заносим ПРОИЗВЕД(D16;D4). В этой фор­муле используются относительные ссылки на ячейки, которые при ко­пировании в другие ячейки будут автоматически изменяться. Но нам требуется, чтобы буква D ячеек, в которых содержится цена, все время оставалась неизменной, поэтому здесь разумнее использовать абсо­лютную ссылку, которая не меняется при копировании. Добавим знак «$» перед буквой D. Получим ПРОИЗВЕД(D16;$D4). При копировании этой формулы автоматически будут меняться все ссылки, кроме буквы D.

Распространяем формулу на весь столбец Е, а затем копируем и распространяем на столбцы G и I. Затем вводим ИТОГО и с помощью кнопки Автосуммирование заносим формулу для подсчета суммарной стоимости в ячейку Е24, а затем копируем ее в ячейки G24 и I24. В формуле были использованы относительные ссылки, поэтому при ко­пировании они автоматически изменятся.

Для получения таблицы с нарастающими итогами копируем таб­лицу продаж и исправляем формулы в столбцах F,G,H и I. В ячейку F30 заносим СУММ(D30;F16), а в затем распространяем эту формулу вправо до конца строки 30. Распространяем формулы по столбцам. При этом суммарные значения автоматически изменяются, так как формулы для их подсчета скопировались корректно, с изменением ссылок на диапазон

Первая часть работы закончена. Теперь разнесем таблицу-справочник и таблицы продаж по разным рабочим листам. Конечно, при решении реальных задач надо сразу оформлять таблицы на нуж­ных рабочих листах, чтобы не делать лишней работы, но в данном случае преследуется цель научиться вводить различные виды ссылок на ячейки.

Изменим заголовок активного рабочего листа. Например, дадим ему заголовок «Цены». Еще один чистый рабочий лист озаглавим «Продажи».

Копируем на рабочий лист «Продажи» только две таблицы про­даж. При этом в ячейках первой таблицы, содержащих формулы, по­является сообщение о недопустимой ссылке. Исправим в любой из этих ячеек $D4 на Цены!$D4. Это означает ссылку на ячейку $D4, на­ходящуюся на рабочем листе «Цены». Распространяем исправленную формулу на все остальные ячейки. Больше никаких исправлений не требуется, т.к. все остальные формулы содержат ссылки на ячейки, находящиеся на активном рабочем листе.

Осталось только отобразить содержимое таблицы продаж в виде диаграммы.

	A	B	C	D	E	F	G	H	I
1	Цены на фрукты
2
3	№	Название	Единицы измерения	Цена (руб.)
4	1	Яблоки	кг	5
5	2	Груши	кг	9
6	3	Мандарины	кг	10
7	4	Бананы	кг	5
8	5	Киви	штук	1
9	6	Лимоны	кг	11
10	7	Апельсины	кг	7

12	Продажа фруктов в первом квартале текущего года
13
14	№	Название	Единицы измерения	Январь		Февраль		Март
15				Кол-во	Сумма	Кол-во	Сумма	Кол-во	Сумма
16	1	Яблоки	кг	200	1000	230	1150	150	750
17	2	Груши	кг	300	2700	150	1350	100	900
18	3	Мандарины	кг	450	4500	400	4000	420	4200
19	4	Бананы	кг	640	3200	500	2500	600	3000
20	5	Киви	штук	2000	2000	3000	3000	1500	1500
21	6	Лимоны	кг	270	2030	210	2310	220	2420
22	7	Апельсины	кг	360	2520	350	2450	390	2730
23
24		Итого:			18890		16760		15500

25
	A	B	C	D	E	F	G	H	I
26	Нарастающие итоги продажи фруктов в первом квартале
27
28	№	Название	Единицы измерения	Январь		Февраль		Март
29				Кол-во	Сумма	Кол-во	Кол-во	Сумма	Кол-во
30	1	Яблоки	кг	200	1000	230	1150	150	750
31	2	Груши	кг	300	2700	150	1350	100	900
32	3	Мандарины	кг	450	4500	400	4000	420	4200
33	4	Бананы	кг	640	3200	500	2500	600	3000
34	5	Киви	штук	2000	2000	3000	3000	1500	1500
35	6	Лимоны	кг	270	2030	210	2310	220	2420
36	7	Апельсины	кг	360	2520	350	2450	390	2730
37
38		Итого:			18890		16760		15500

Рис.2.2.Рабочий лист «Продажа фруктов»

В Excel можно построить два типа диаграмм: внедренные диа­граммы, которые создаются на рабочем листе рядом с таблицами, и диаграммы на отдельных листах диаграмм. Оба типа диаграмм легко строятся при помощи мастера диаграмм.

Создадим внедренную диаграмму на одном рабочем листе с таблицей продаж. Но немного преобразуем таблицу, т.к. в исходном состоянии она плохо пригодна для создания диаграммы. Скопируем таблицу на чистый рабочий лист и удалим столбцы с суммами и другие лишние столбцы, а также часть строк. На рис. 2.3 изображена преоб­разованная таблица, по которой будет строиться диаграмма.

Продажа фруктов в первом квартале

Название	Январь	Февраль	Март
Яблоки Груши Лимон Бананы	200 300 270 640	230 150 210 500	150 100 220 600

Рис.2.3. Таблица для построения диаграммы

Построение диаграммы по такой таблице - дело несложное. Для этого сначала выделим таблицу. Затем выполняем команду Встав­ка/Диаграмма или нажимаем кнопку Мастер диаграмм на стандарт­ной панели инструментов. Указатель мыши примет форму тонкого кре­ста с диаграммой под ним. Перетаскиваем указатель мыши по ячей­кам, определяя место и размер будущей диаграммы, а затем отпуска­ем кнопку мыши.

После определения области диаграммы откроется первое окно Мастера диаграмм. В нем нужно выбрать тип диаграммы из имею­щихся типов. Для перехода ко всем последующим окнам нажимаем кнопку Далее. Второе окно позволяет уточнить и в случае необходимо­сти откорректировать диапазон данных, по которым будет строиться диаграмма, третье окно - ввести заголовок диаграммы и подписи к осям координат, а также установить другие параметры диаграммы. Выберите тот тип и формат, который соответствует рисунку 2.4 (объ­емная гистограмма).

В четвертом окне можно задать вывод диаграммы на сущест­вующий или отдельный новый лист. После закрытия последнего диа­логового окна Excel внедряет созданную диаграмму в выделенную область рабочего листа. Если нас не устраивают размеры или распо­ложение диаграммы, это легко поправить с помощью мыши. Любой элемент диаграммы может быть изменен. Для этого нужно двойным щелчком на соответствующем элементе вызвать диалоговое окно и внести изменения. Также можно щелкнуть по соответствующему эле­менту правой кнопкой и выбрать в контекстном меню пункт Формат.

В результате получили диаграмму, изображенную на рис.2.4.

Рис.2.4. Диаграмма продажи фруктов

Самостоятельное задание

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

Затем на основе этих таблиц создать третью таблицу всего из двух строк, содержащую средние баллы по каждому предмету для ка­ждой из групп. Отобразить полученную таблицу в виде диаграммы.