5.4 Построение, редактирование, настройка диаграмм

Цель работы: Изучение принципов построения, редактирования и настройки диаграмм.

Задание 1 Изучите принципы построения диаграмм.

Диаграммы создают на основе данных, расположенных на рабочих листах.

Диаграммы могут строиться на отдельных рабочих листах – листы диаграмм и на текущих рабочих листах – внедренные диаграммы.

В MS Excel можно создавать различные диаграммы. Всего существует 11 типов встроенных диаграмм, каждый из которых имеют еще множество разновидностей (видов). Выбор типа диаграммы определяется задачами, решаемыми при ее создании.

Один из типов диаграмм является стандартным, то есть он используется по умолчанию при создании диаграмм. Обычно стандартной диаграммой является плоская гистограмма.

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

1. Выделите диапазон таблицы или несмежные диапазоны, удерживая клавишу Ctrl.

2. На вкладке Вставка в группе Диаграммы щелкните по кнопке с нужным типом диаграмм и в галерее выберите конкретный вид диаграммы.

3. На листе будет создана диаграмма выбранного вида.

Для создания диаграммы стандартного типа достаточно выделить фрагмент листа и нажать клавишу F11.

Для удаления диаграммы достаточно выделить ее и нажать клавишу Delete.

1. В новой р/к на р/л Лист1 создать данные, подобные по форме показанным в таблице. Присвоить листу имя Данные (Рис. 5.9).

Рис. 5.9. Данные для построения диаграммы

2. Выделить диапазон A1:D7.

3. Нажать клавишу F11. На новом р/л Диаграмма1 появляется диаграмма, построенная с принятым по умолчанию типом и форматом.

Задание 2 Изучите приемы редактирования и настройки диаграмм.

Для редактирования и настройки диаграмм используется динамическая вкладка Работа с диаграммами, появляющаяся вместе с построенной или выделенной диаграммой и содержащей команды для редактирования и изменения диаграммы.

Все действия выполняются с выделенной диаграммой или с ее выделенными элементами.

Для выделения диаграммы или ее элементов следует щелкнуть мышью в любом месте области диаграммы или на любом ее элементе. Признаком выделения являются рамка. На рамке имеются маркеры, расположенные по углам и сторонам рамки.

Изменение типа диаграммы

После создания можно изменить тип и вид диаграммы.

1. Перейдите на лист Диаграмма1.

2. В группе Тип вкладки Работа с диаграммами|Конструктор нажмите кнопку Изменить тип диаграммы.

3. В окне Изменение типа диаграммы выберите требуемый тип и вид диаграммы.

Изменение источника данных

1. На р/л Лист1 заполните значения по полю «Район 4». Затем перейдите на р/л Диаграмма1 в группе Диапазон вкладки Работа с диаграммами|Конструктор нажмите кнопку Выбрать данные.

2. Для добавления ряда данных в окне Выбор источника данных нажмите кнопку Добавить.

3. В окне Изменение ряда очистите поле Имя ряда, а затем выделите на р/л Лист1 ячейку, содержащую название ряда данных «Район 4»; очистите поле Значение, оставив знак равно и затем выделите ячейки на листе1, содержащие значения ряда данных Е2:Е7 два раза нажмите ОК.

Добавление названия диаграммы

1. Перейдите на лист Диаграмма1 и добавьте название диаграммы «Учебная диаграмма», для этого в группе Подписи вкладки Работа с диаграммами|Макет щелкните по кнопке Название диаграммы.

2. Выберите вариант расположения названия диаграммы.

Связывание названия диаграммы с ячейкой рабочего листа

1. Введите в любую ячейку р/л, на котором находятся данные, новое название диаграммы, например в ячейку А10 введите «Новая диаграмма».

2. Перейдите на лист Диаграмма1 и выделите название диаграммы. В строке формул набрать знак «=» и щелкнуть на р/л Лист1 ячейку с новым названием А10. ОК.

Добавление легенды

1. На р/л Диаграмма1 добавьте легенду, для этого в группе Подписи вкладки Работа с диаграммами|Макет щелкните по кнопке Легенда.

2. Выберите вариант расположения легенды.

Добавление названий к осям диаграммы

В большинстве плоских диаграмм вертикальная ось называется осью значений, или осью Y, а горизонтальная ось — осью категорий или осью X. Исключения составляют линейчатые диаграммы, у которых ось категорий вертикальна, а ось значений горизонтальна, и круговые и кольцевые диаграммы, которые вообще не имеют осей. В объемных диаграммах — вертикальная ось — ось значений, ось Y — ось категорий, ось X — ось рядов данных:

1. Перейти на р/л Диаграмма1.

2. В группе Подписи вкладки Работа с диаграммами|Макет щелкните по кнопке Названия осей. Для осей по вертикали и горизонтали выберите расположение и добавьте название.

Добавление подписи данных

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

1. Перейти на лист Диаграмма1.

2. Щелкнуть один раз любой маркер данных за февраль, затем щелкнуть этот же маркер еще раз.

3. В группе Подписи вкладки Работа с диаграммами|Макет щелкните по кнопке Подписи данных и выберите любой вариант размещения подписи.

4. Повторить шаги 2-3 для любой метки данных за июнь.

Выбор макета диаграммы

Макет диаграммы определяет наличие и расположение элементов диаграммы.

1. Измените макет диаграммы на листе Диаграмма1, для этого в группе Макеты диаграмм вкладки Работа с диаграммами|Конструктор щелкните по кнопке Дополнительные параметры галереи макетов.

2. Выберите любой вариант.

Перемещение диаграммы

Диаграмму, размещенную на отдельном листе, можно переместить на лист с данными.

1. Выделите диаграмму на листе Диаграмма1 нажмите кнопку Переместить диаграмму в группе Расположение вкладки Работа с диаграммами/Конструктор.

2. В окне Размещение диаграммы установите переключатель на имеющемся листе, где по умолчанию установлен Лист1. ОК.

Оформление диаграммы

Стиль оформления диаграммы определяет, в первую очередь, цвета элементов диаграммы.

Независимо от выбранного стиля диаграммы можно оформлять ее отдельные элементы. Для этого используют элементы вкладки Работа с диаграммами|Формат. Кроме того, для установки параметров оформления элементов можно использовать диалоговые окна. Для отображения окна выделите элемент диаграммы и в группе Текущий фрагмент вкладки Работа с диаграммами|Формат нажмите кнопку Формат выделенного элемента.

Диалоговые окна для разных элементов имеет примерно одинаковый вид. В левой части окна отображается список разделов (вкладок) окна. В правой части – элементы для настройки параметров элемента.

1. Выделите диаграмму на р/л Диаграмма1, затем в группе Стили диаграмм вкладки Работа с диаграммами|Конструктор щелкните по кнопке Дополнительные параметры галереи стилей.

2. Выберите любой вариант оформления.

3. Выделите название диаграммы и измените оформление, установив заливку, контур фигуры, эффект для фигуры.

Задание 3 Изучите построение трехмерной диаграммы.

Рассмотрим построение трехмерной диаграммы для выражения . Параметры, определяющие характер распределения функции, q и .

Построение диаграммы следует вести по следующему алгоритму:

1. Создать новый р/л с именем «Поле температур».

2. Ввести в ячейки: В1 – q-мощность источника, С1 – lambda , В2 – 1000, С2 – 0,4.

3. Ввести в ячейку D3 значение X – -1,0001. С помощью прогрессии заполнить диапазон D3:N3 данными с шагом 0,2 см, конечное значение 0,9999, заполнять по строкам. (смещение начала координат на 0,0001 используется для исключения деления на ноль) (Рис. 5.10.).

Рис. 5.10. Диалоговое окно прогрессии

4. Ввести в интервал С4-С14 значение Y – 1,0001 с шагом 0,2 см. Заполнить так же, с помощью прогрессии шаг 0,2, конечное значение 0,9999, заполнять по столбцам.

5. В ячейку D4 ввести формулу и протянуть маркером по строке, а затем протянуть маркером всю строку вниз:

=$B$2/(6,283*$C$2*КОРЕНЬ(D$3*D$3+$C4*$C4)).

6. Выделить область C3:N14. Построить диаграмму на отдельном листе, тип – поверхность. В ячейку Е1 листа с данными поместить название диаграммы «Распределение температур» связать название диаграммы с этой ячейкой. Названия осей: Температура в градусах (Y), Расстояние в см (X и Y).

7. С помощью контекстного меню, щелчок правой кнопкой мыши по оси температур перейти в диалоговое окно Формат оси и ограничить диапазон температур: mах – 1500°, min – 200°.

8. Изменить исходные значения: мощность источника – q₁=500, q₂=2000, теплопроводность – ₁ = 0,7; ₂ = 1,5. Наблюдать изменения вида трехмерного объекта.

Задание 4 Изучите построение графиков функций.

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

Рассмотрим построение графика функции у = х² в диапазоне [-3,+3] с шагом 0,5.

Построение диаграммы следует вести по следующему алгоритму:

1. Создайте новый р/л с именем «График функций»

2. В ячейку А1 введите название графика «График функции у=х²». В следующие ячейки введите данные: А2 – Шаг =, А3 – Х, А4 – У, В2 – 0,5, С2 – В диапазоне от, F2 – -3, G2 – до, Н2 – 3.

3. Заполните значения Х данными, Введите формулы в ячейки: ВЗ – =F2, СЗ – =ВЗ+$В$2. Протяните маркером вправо до получения значения 3.

4. Введите формулу в ячейку В4 – =СТЕПЕНЬ(B3;2). Протяните маркером вправо.

5. При таком построении таблицы и ссылок достаточно изменять границы диапазона, шаг и исходную формулу для получения другого графика.

6. Выделите диапазон A4:N4 и постройте график на отдельном листе.

7. Добавьте название диаграммы, связав с ячейкой А1 на р/л График функций. Добавьте подписи осей.

Задания для индивидуальной работы

Задание 1

1. Используя р/л Данные, постройте объемную круговую диаграмму для Района 2 с вырезанными секторами и рассмотрите ее особенности.

2. Используя р/л Данные, постройте объемный вариант диаграммы с областями с накоплением и рассмотрите ее особенности.

3. Используя р/л Данные, постройте заполненную лепестковую диаграмму и рассмотрите ее особенности.

4. Используя р/л Данные, постройте график с маркерами и рассмотрите его особенности.

5. Используя р/л Данные, постройте точечную диаграмму с гладкими кривыми и маркерами на листе диаграмм.

6. Используя р/л Данные, постройте линейчатую диаграмму и сделайте ее стандартной. Покажите, что такая диаграмма строится по умолчанию.

7. Используя р/л Данные, постройте гистограмму для двух несмежных интервалов.

Задание 2

8. Используя р/л Данные, постройте вторичную круговую диаграмму для региона 3 с вынесенными во вторую диаграмму данными за май и июнь. Сформируйте подписи данных.

9. Используя р/л Данные, постройте диаграмму по умолчанию и присвойте ей название «Учебная диаграмма». Проведите форматирование заголовка.

10. Введите в ячейку А11 р/л Данные текст «Другая диаграмма». Присвойте Учебной диаграмме название «Новая диаграмма».

11. Введите в ячейку А12 р/л Данные текст «Моя диаграмма». Присвойте Новой диаграмме название «Моя диаграмма».

12. На диаграмме, построенной по умолчанию, введите названия осей – «Ось Y» и «Ось X».

13. На диаграмме, построенной по умолчанию, установите метки на 3 наибольших значения маркеров.

14. Измените макет в диаграмме, построенной по умолчанию.

15. Измените тип диаграммы в диаграмме, построенной по умолчанию.

16. На диаграмме, построенной по умолчанию, измените стиль оформления.

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

18. На диаграмме, построенной по умолчанию, измените формат рядов данных: для района 1– градиентная заливка, для района 2 – текстура «Джинсовая ткань», для района 3 – рисунок Эйфелевой башни из коллекции клипов.

19. Используя данные приведенной ниже таблицы, постройте биржевую диаграмму по трем параметрам — самый высокий курс, самый низкий курс, курс закрытия. В качестве подписи горизонтальной оси Х, добавьте диапазон данных А2:А11.

20. Используя данные таблицы, приведенной в задании 19, постройте биржевую диаграмму по четырем параметрам - курс открытия, самый высокий курс, самый низкий курс, курс закрытия.

21. Используя данные таблицы, приведенной в задании 19, постройте биржевую диаграмму по четырем параметрам - объем, самый высокий курс, самый низкий курс, курс закрытия.

22. Используя данные таблицы, приведенной в задании 19, постройте биржевую диаграмму по пяти параметрам – объем, курс открытия, самый высокий курс, самый низкий курс, курс закрытия.

Задание 3

23. Постройте трехмерную диаграмму для выражения Z=8x₁²+

+4x₁x₂+5x₂². Ввести в диапазон D3:N3 значения X₁ от – -0,1 до 0,1 с шагом 0,02, в диапазон С4:С14 значения X₂ от -0,1 до 0,1 с шагом 0,02. Формулу введите в ячейку D4 следующего вида: =8*D$3*D$3+4*D$3*$C4+5*$С4*$С4. Скопируйте формулу на диапазон D4:N14. При построении используйте диаграмму типа Поверхность.

Задание 4

24. Постройте кубический график функции у=х³, где Х изменяется в диапазоне [-5,+5] с шагом 0,5. Добавьте название диаграммы и подписи данных.

25. Постройте график функции у=2х²+5х+5 , где Х изменяется в диапазоне [-6,+4] с шагом 1. Добавьте название диаграммы и подписи данных.

26. Постройте график функции в диапазоне [-3,+3] с шагом 0,5.

В ячейке В4 используйте формулу следующего вида =ЕСЛИ(B3>=0;5*СТЕПЕНЬ(B3;2)-7*B3-12;B3+5). Добавь-те название диаграммы и подписи данных.

Контрольные вопросы

1. Как быстро создать диаграмму?

2. Сколько существует способов размещения диаграмм в рабочей книге? Перечислите их.

3. Дайте определение внедренной диаграмме?

4. Какую команду следует использовать при построении диаграммы для нескольких несмежных интервалов?

5. Как добавить название диаграммы?

6. Как связать название диаграммы с ячейкой рабочего листа?

7. Как добавить названия к осям диаграммы?

8. Как добавить метки к элементам данных?

9. Как выполняется смена типа диаграммы?

10. Как изменить стиль диаграммы?

11. Как установить максимальное и минимальное значение шкалы для трехмерной диаграммы?