Методические материалы для лабораторного занятия №7

Тема: Построение и редактирование диаграмм.

Количество часов: 2 ч.

Упражнение 1. Построение обычной и трехмерной гистограмм.

Для построения диаграммы необходимо иметь числовые данные, на основе которых она будет построена. Введите в диапазон В2:В7 следующие числа: 13; 14; 17; 21; 15; 28.

Выделите любую ячейку вне этого диапазона. Именно «вне», т.к. в этом случае EXCEL не будет автоматически определять исходные данные. Этот способ более длинный, но зато мы лучше поймем все этапы построения диаграммы.

Выполните команды: Вставка  Диаграмма...

Шаг 1. Выбор типа и формата диаграммы. На этом шаге необходимо выбрать тип диаграммы и задать (в области справа) формат, который делает ее более выразительной. Выберите тип Гистограмма и вид Обычная. После выбора надо нажать на кнопке Далее и перейти на следующий шаг.

Шаг 2. Выбор и указание диапазона данных. На этом шаге задается диапазон данных, для которого будет построена диаграмма. Для этого с помощью переключателя укажите расположение данных – по строкам или по столбцам будет строиться выбранный тип диаграммы (в нашем случае по столбцам). Далее установите курсор в поле Диапазон и с помощью мыши выделите необходимый блок ячеек, адрес которого автоматически отобразится в поле. Выделите диапазон В2:В7, в поле отобразится формула =Лист1!$B$2:$B$7, что является так называемой трехмерной абсолютной ссылкой этого диапазона. Нажмите кнопку Далее >.

Шаг 3. Задание параметров диаграммы. Задание параметров диаграммы осуществляется в окнах вкладок Мастера диаграмм. Оставьте все без изменений и нажмите на кнопке Далее >

Шаг 4. Размещение диаграммы.

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

Полученную диаграмму можно редактировать, а именно, перемещать, изменять размер, задавать параметры элементов диаграммы. Выделите элемент диаграммы, например Ряд1, и выполните Формат  Выделенный ряд или два раза щелкните на элементе. В появившемся диалоговом окне измените значения параметров (цвет ряда, например).

В EXCEL’е есть возможность так называемого графического под бора параметра. Она заключается в том, что мы можем, изменяя размер элемента диаграммы (высоту столбца) изменить и число, соответствующее этому элементу.

На нашей диаграмме мы видим, что пятый столбец ряда «нарушает» прослеживающуюся тенденцию, заключающуюся в постоянном увеличении чисел. Выделите его (сначала один раз щелкните по ряду, затем по этому столбцу) и потянув за маркер увеличьте его высоту. Вы увидите, что число в ячейке В6 соответственно изменилось. Вытянув столбец сделайте так, чтобы число в ячейке В6 стало равным 24.

Введите дополнительные данные (см. таблицу).

	A	B	C
1		Нефть	Газ
2	1998	13	5
3	1999	14	7
4	2000	17	8
5	2001	21	10
6	2002	24	14
7	2003	28	18

Выделите диаграмму и выполните команды Диаграмма  Исходные данные…Вкладка Ряд.

В списке Ряд выделите Ряд1. Установите курсор в строку Имя. Выделите ячейку В1. Установите курсор в поле Подписи по оси Х и выделите диапазон А2:А7. Итак, мы изменили подписи по оси Х и название ряда на «Нефть». Теперь добавим еще один ряд: нажмите на кнопке Добавить, в списке Ряд появится Ряд2, укажите какие значения он должен включать: щелкните в поле Значения и выделите диапазон С2:С7, имя ряда возьмите из ячейки С1. Нажмите ОК. Добавьте заголовок диаграммы: выделите диаграмму и выполните команды Диаграмма  Параметры диаграммы…  вкладка Заголовки  в поле Название диаграммы введите «Динамика роста добычи углеводородов». Установите значения над каждым столбцом: Диаграмма  Параметры диаграммы…  вкладка Подписи данных  Включить в подписи значения.

Полученная диаграмма должна иметь следующий вид:

Построим диаграмму в виде графика более быстрым способом. Выделите любую ячейку из таблицы. Выполните команды Вставка Диаграмма.

Шаг 1. Тип гистограмма, вид трехмерная гистограмма (№7).

Шаг 2. На этом шаге ничего менять нам не придется, т.к. EXCEL сам «распознал», где у нас значения рядов, подписи по оси Х, названия рядов и т.д.

Шаг 3. Включите в подписи данных значения элементов. Названия рядов разместите внизу: на вкладке Легенда установите переключатель внизу.

Шаг 4. Разместите диаграмму на отдельном листе (Диаграмма 1).

Поменяйте порядок рядов: сделайте так чтобы ряд «Газ» шел перед рядом «Нефть». Выделите ряд «Газ» и выполните команды Формат  Выделенный ряд…, на вкладке Порядок рядов выделите «Газ» и щелкните по кнопке Вверх.

Упражнение 2. Элементы экономического анализа: линия тренда, прогноз на будущий период

Задача. Необходимо спрогнозировать рост накоплений компании на ближайший период.

	A	B	C	D	E
1	Месяц	Доходы	Расходы	Остаток	Накопления
2	янв	100	90	10,00	10
3	фев	90	50	40,00	50
4	мар	129	140	-11,00	39
5	апр	143	150	-7,00	32
6	май	209	100	109,00	141
7	июн	150	120	30,00	171
8	июл	167	150	17,00	188
9	авг	178	167	11,00	199
10	сен	213	200	13,00	212
11	окт	110	100	10,00	222
12	ноя	156	170	-14,00	208
13	дек	178	180	-2,00	206
14	Итого	1823	1617	206

Заполните данными ячейки таблицы от В2 до С13.

Введите в ячейку D2 формулу =B2-C2. Скопируйте формулу на остальные месяцы.

В ячейке В14 посчитайте сумму диапазона В2:В13. Скопируйте эту формулу на ячейки С14 и D14.

Пусть на начало года накоплений не было. Тогда накопления после января будут равны январским остаткам. Для этого введем в ячейку E2 формулу =D2. В феврале накопления составят сумму январских накоплений и февральских остатков, т.е. в ячейку E3 следует ввести формулу =E2+D3. Для остальных месяцев процедура начисления накоплений аналогичная. Поэтому скопируйте формулу ячейки Е3 на ячейки Е4:Е13.

Выделите ячейки D2:D14 и выполните команды Формат  Ячейки  Число  Числовой и в области Отрицательные числа выберите вариант -1234,10.

Сделайте так чтобы в тех ячейках, в которых накопления превысят 200, шрифт был красного цвета, полужирного начертания (выделите Е2:Е13 и выполните Формат  Условное форматирование…  значение | больше | 200  нажать кнопку Формат  вкладка Шрифт  выбрать яркий цвет и полужирное начертание).

Постройте диаграмму на основе данных диапазона Е2:Е13. Вид диаграммы: График с маркерами. Подписи по оси Х возьмите из диапазона А2:А13.

Рассмотрим перспективы фирмы. В математике это называется «оценить тренд». Выделите диаграмму и выполните команды Диаграмма  Добавить линию тренда… На вкладке Тип выберите Линейная. На вкладке Параметры в области Прогноз установите вперед на 6 периодов. Установите флажок в опции Показывать уравнение на диаграмме.

Мы получили диаграмму роста накоплений и тенденцию на следующие 6 месяцев. По виду линии тренда можно оценить качество деятельности компании. Уравнение линии тренда позволит оценить и количественные показатели перспектив: первый коэффициент отражает угол наклона линии, и если он положителен, то имеется тенденция к росту.

Полученная диаграмма может использоваться для так называемого анализа «Что будет, если…?». Измените расход в июне со 120 до 320 единиц. Вы сразу же увидите, что появятся перспективы стать банкротом – кривая накоплений уверенно пошла вниз. Угловой коэффициент линии тренда k = -3,6713 < 0.

Упражнение 3. Построение графика функции.

Задача. Построить график функции y = sin x. Значение аргумента х выбрать в пределах от –6 до 6 с шагом 0,5.

Построим таблицу следующего вида.

	A	B
1	х	sin x
2	-6,0	=sin(A2)
3	-5,5
…
25	6,0

1. Ряд чисел -6,…,6 постройте как арифметическую прогрессию с шагом 0,5.

2. В ячейку В2 введите формулу: =sin(A2).

3. Подведите курсор к маркеру автозаполнения ячейки и дважды щелкните по нему.

4. Постройте диаграмму на основе диапазона В2:В25.

5. Выберите диапазон А2:А25 в качестве подписей оси Х. Тип диаграммы – график.