Лаб_10_Excel_3_Диаграммы

ЛАБОРАТОРНАЯ РАБОТА №10 ПОСТРОЕНИЕ, РЕДАКТИРОВАНИЕ И ФОРМАТИРОВАНИЕ

ДИАГРАММ В MS EXCEL 2010

Таблица 1. Основные действия при работе с диаграммой в MS Excel 2010

Действие

Команды

выделяем необходимый диапазон значений1 → вкладке Вставка в группе Диаграмм выбираем нужный тип диаграммы

Создание

диаграммы

	при выделении диаграммы появляются контекстная вкладка Работа с
Редактирование	диаграммой (Конструктор, Макет, Формат) при помощи которой
диаграммы	можно изменить тип диаграммы, исходные данные, параметры
			диаграммы, размещение и т.п.
	на вкладке Макет (в контекстной вкладке Работа с диаграммой) в
	группе Подписи или Оси выбираем соответственный компонент
	диаграммы (ряд, ось, подписи, легенда и т.п.)
				ИЛИ
Форматирование	п.к.м. по компоненту диаграммы (ряд, ось, подписи, легенда и т.п.)
			→ Формат компонента диаграммы
диаграммы
				ИЛИ
	на вкладке Формат (в контекстной вкладке Работа с диаграммой) в
	группе Текущий фрагмент выбираем соответственный компонент
		диаграммы (например, боковая стенка)
				→

1 Диапазон значений может быть выделен как до вызова Мастера диаграмм, так и после его вызова.

1

Рисунок 1. Контекстная вкладка Работа с диаграммой (с вкладками: Конструктор, Макет, Формат)

2

Рисунок 2. Пример гистограммы, построенной по данным из столбца «Выручка»

Рисунок 3. Пример гистограммы, построенной по данным из столбцов «Выручка» и «Налог»

Например, мы построили гистограмму для таблицы и отформатировали ее нужным образом, как показано на Рисунок 2. Данная диаграмма иллюстрирует объем выручки в

3

зависимости от сорта яблок, но может заинтересовать вопрос: «Какой при этом будет размер налога?», т.е. в существующую диаграмму нужно добавить еще данные из столбца «Налог» (см. Рисунок 3), для этого необходимо выполнить следующее:

Дополнение данных на диаграмме:

выделяем диаграмму → на вкладке Конструктор (в контекстной вкладке Работа с

диаграммой) в группе Данные выбираем		→ в окне «Выбор источника данных»

нажимаем → в окне «Изменение ряда» в поле Имя ряда указываем название соответственного столбца, в поле Значения – выделяем нужный диапазон данных

ИЛИ

п.к.м. по диаграмме → ИЛИ

выделяем диаграмму →подводим курсор мыши к границе диапазона, по данным из

которого ранее была построена диаграмма, пока он не сменит свой знак на → нажав л.к.м. перемещаем границу диапазона в нужном нам направлении

Удаление данных с диаграммы:

выделяем диаграмму → на вкладке Конструктор (в контекстной вкладке Работа с

диаграммой) в группе Данные выбираем		→ в окне «Выбор источника данных»

нажимаем удаляем ряд, который следует удалить.

На практике часто возникает необходимость найти функциональную зависимость между величинами x и y, которые получены в результате эксперимента, когда вид эмпирической зависимости известен, но числовые параметры неизвестны (аналитический вид). С целью сглаживании экспериментальных данные строятся Линии тренда.

Линии тренда можно построить 6 видов: экспоненциальная, линейная, логарифмическая, полиномиальная (многочлен до 6 степени), степенная, линейная фильтрация.

Для выбора вида Линии тренда, который наилучшим образом отражает взаимосвязь между переменными, необходимо посмотреть на коэффициент

детерминированности R 2 , который показывает, насколько хорошо уравнение, полученное с помощью регрессионного анализа (построении Линии тренда), объясняет взаимосвязи между имеющими переменными. Чем ближе значение коэффициента

детерминированности R 2 к единицы, тем лучше полученная эмпирическая зависимость отражает взаимосвязь между переменными.

4

Добавление Линии тренда:

строим точечную диаграмму по имеющимся данным → нажимаем п.к.м. по точке

(маркеру) на диаграмме → → в диалоговом окне «Формат линии тренда» выбираем соответственный вид Линии тренда (экспоненциальный, линейный, логарифмический, полиномиальный (многочлен до 6 степени), степенной, линейной фильтрации) и отмечаем поля показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2)

Например, в результате статистический расчетов были получены данные (Таблица

2):

5

Таблица 2. Количество зарегистрированных преступлений, связанных с похищением человека в г. Чита

Годы	2005	2006	2007	2008	2009	2010
Всего	3	8	7	11	6	12

На основании данных из Таблица 2 можно построить точечную диаграмму (Рисунок 4), на которой видно, что данные на ней близки к некоторой функции. Для нахождения данной функции построим Линию тренда (см. Рисунок 5 - Рисунок 8).

Рисунок 4

Количество зарегистрированных преступлений,

связанных с похищением человека в г. Чита

кол.

14

12

y = 3E-162e0,1863x

R² = 0,4852

10

8				Всего
6
4				Экспоненциальная
2				(Всего)
0				гг.
2004	2006	2008	2010	2012

Рисунок 5. Пример Линии тренда экспоненциального вида

6

Количество зарегистрированных преступлений,

		связанных с похищением человека в г. Чита
кол.
14																y = 1,2286x - 2458,5
12
10																	R² = 0,4817
8																	Всего
6
4																	Линейная (Всего)
2
0																	гг.
2004			2006			2008			2010			2012

Рисунок 6. Пример Линии тренда линейного вида

Количество зарегистрированных преступлений, связанных с похищением человека в г. Чита

кол.
14
12				y = 2466,6ln(x) - 18750
10				R² = 0,4818
8				Всего
6
4				Логарифмическая
2				(Всего)
0				гг.
2004	2006	2008	2010	2012

Рисунок 7. Пример Линии тренда полиномиального вида четвертой степени

Количество зарегистрированных преступлений, связанных с похищением человека в г. Чита

кол.
14
12				y = -0,1964x2 + 789,89x - 794076
10				R² = 0,508
8				Всего
6
4				Полиномиальная
2				(Всего)
0				гг.
2004	2006	2008	2010	2012

7

Рисунок 8. Пример Линии тренда полиномиального вида второй степени Из построенных Линий трендов видно, что функция

			y	0,1964 х2		789,89х 794076
оптимально	отражаем	зависимость				рассматриваемых годов от		количества
зарегистрированных преступлений, связанных с похищением человека								в г. Чита, т.к.
коэффициент	детерминированности				R2	0,508 . Теперь данные из Таблица 2 можно
заменить на функцию y		0,1964 х2			789,89х 794076 .

При помощи данной функции можно ответить на вопрос: «Какое количество преступлений, связанных с похищением человека в г. Чита, можно ожидать в 2011 году?». Для ответа на данный вопрос достаточно войти в Формат

Линии тренда и выбрать, где по данным графика можно определить ожидаемое значение количества похищенных людей в 2011 году в г. Чита.

ЗАДАНИЯ ДЛЯ ВЫПОЛНЕНИЯ

Упражнение 1

Научитесь по табличным данным строить диаграммы и форматировать их, выполнив следующее:

1)в вашей папке в файле Работа_в_Excel.xlsx на листе «таблица» создайте линейчатую диаграмму любого вида по значениям из столбца (Таблица 3), согласно своего варианта. Дайте название ряду, сделайте подпись оси Ох, дайте название диаграмме, осям Ох, и Оz:

			Таблица 3
Вариант	Название столбца	Вариант	Название столбца
1	Кол–во, кг.	6	Информатика
2	Кол–во электроэнергии, кВтч	7	Кол–во, кг
3	Кол–во, шт.	8	Кол–во минут
4	Кол–во путевок, шт.	9	Итого к выдаче, руб.
5	Кол–во деталей, шт.	10	Кол–во посещений, чел.

2)измените тип линейчатой диаграммы на гистограмму любого вида;

3)отформатируйте оси Ох и Оz. Параметры форматирования осей: измените цену деления шкал, измените направление текста подписей осей;

4)отформатируйте область построения диаграммы произвольным образом.

Упражнение 2

Продолжите работу по получению навыков создания и форматирования диаграмм,

атакже получите навыки редактирования диаграммы, выполнив следующее:

1)в вашей папке в файле Работа_в_Excel.xlsx на листе «значения функций» постройте точечную диаграмму со значениями, соединенными сглаживающими линиями без маркеров для квадратичной функции. Дайте название ряду, сделайте подпись оси Ох, дайте название диаграмме, осям Ох, и Оz;

2)измените несколько значений на отрезке a; b . Что произошло с диаграммой?

8

3)дополните диаграмму графиками тригонометрической, логарифмической и обратно тригонометрической функции. Подпишите добавленные ряды в диаграмме.

Упражнение 3

Продолжите работу по закреплению навыков создания и форматирования диаграмм, выполнив следующее:

в вашей папке в файле Работа_в_Excel.xlsx на листе «функция ЕСЛИ» создайте точечную диаграмму со значениями, соединенными сглаживающими линиями без маркеров для квадратичной функции для функции из упражнения 2 в лабораторной работе №9. Дайте название ряду, сделайте подпись оси Ох, дайте название диаграмме, осям Ох, и

Оz.

Упражнение 4

Получите навык построения и работы с Линией тренда, выполнив следующее:

1)в вашей папке в файле Работа_в_Excel.xlsx создайте лист и дайте ему название «Линии тренда», на котором постройте точечную диаграмму, в соответствии со своим вариантом (Таблица 4 - Таблица 13);

2)на постройте все виды Линии тренда (на отдельных диаграммах);

3)сделайте вывод: «Какой вид функции лучше отражает зависимость?» и сделайте прогноз на 2011 год.

Таблица 4. Количество зарегистрированных преступлений по убийствам и покушениям на убийства в г. Чита

Годы	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010
Всего	447	547	611	577	582	582	457	392	428	413	387

Таблица 5. Количество зарегистрированных преступлений по умышленному причинению тяжкого вреда здоровью в г. Чита

Годы		2000		2001		2002	2003	2004	2005		2006		2007		2008		2009			2010
Всего		915		1021		1091	1019	1145	1125		1130		1030		1017		926			854
		Таблица 6. Количество зарегистрированных преступлений по изнасилованию и
										покушениям на изнасилование в г. Чита
Годы		2000		2001		2002	2003	2004	2005		2006		2007		2008		2009			2010
Всего		80		100		98	121	176	188		133		117		85		96			93
		Таблица 7. Количество зарегистрированных преступлений по грабежам в г. Чита
Годы		2000		2001		2002	2003	2004	2005		2006		2007		2008		2009			2010
Всего		1836		2052		1944	1850	1884	2222		2374		2056		1823		1744			1733
		Таблица 8. Количество зарегистрированных преступлений по разбоям в г. Чита
Годы		2000		2001		2002	2003	2004	2005		2006		2007		2008		2009			2010
Всего		310		383		386	393	397	506		582		395		317		331			339
		Таблица 9. Количество зарегистрированных преступлений по кражам в г. Чита
Годы	2000			2001		2002	2003	2004		2005		2006		2007		2008		2009			2010
Всего	14113			14574		10584	11764	10913		12736		12871		12345		12857		12697			13376

Таблица 10. Количество зарегистрированных преступлений, связанных с незаконным оборотом наркотиков в г. Чита

9

Годы	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010
Всего	2064	2645	1928	1798	1370	1596	1396	1601	1754	1831	1828

Таблица 11. Количество зарегистрированных преступлений, связанных с нарушениями правил дорожного движения и эксплуатации в г. Чита

Годы	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010
Всего	403	451	490	526	299	334	348	388	341	314	301

Таблица 12. Количество зарегистрированных преступлений, связанных с нарушениями правил дорожного движения и эксплуатации транспортных средств в г. Чита

Годы	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010
Всего	403	451	490	526	299	334	348	388	341	314	301

Таблица 13. Количество зарегистрированных преступлений, связанных с взяточничеством в г. Чита

Годы	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010
Всего	20	24	26	16	26	36	36	40	43	99	69

Упражнение 5

Продолжите работу по закреплению навыка оформления и подготовки документа к печати, выполнив следующее:

построенные диаграммы на листах «таблица», «значения функций» и «функция ЕСЛИ» перенесите на новые страницы.

КОНТРОЛЬНЫЕ ВОПРОСЫ

1)Какие существуют компоненты диаграммы?

2)Создание диаграммы.

3)Форматирование диаграммы. Дайте определение форматированию.

4)Редактирование диаграммы. Дайте определение редактированию.

5)Линии тренда.

10