Практическая работа по теме "Создание графиков и диаграмм в MS Excel"

Практическая работа

Табличный процессор MICROSOFT EXCEL 2007

Создание графиков и диаграмм

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

Д иаграмма – графическое представление данных, наглядно показывающее линейными отрезками или геометрическими фигурами соотношения меду различными величинами.

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

Microsoft Office Excel является хорошим инструментом для построения диаграмм различных видов (график, гистограмм, круговых диаграмм и т.д.). Диаграммы могут быть плоскостные (двумерные) и пространственные (трѐхмерные или объѐмные).

В EXCEL можно создавать диаграммы двух видов:

• внедренные диаграммы;

• диаграммные листы;

Внедренные диаграммы - это диаграммы, наложенные на рабочий лист с таблицей данных. Они сохраняются вместе с таблицей в одном файле.

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

Каждая диаграмма должна содержать: название диаграммы, название осей, подписи данных и легенду и др. элементы, представленные на рис.1.

Маркер Название Ряд Линии

данных диаграммы данных сетки

Ось Y

Подпись оси Y

Ось Х Легенда Подпись оси Х

Рис.1 Пример линейного графика (Основные элементы диаграммы)

При построении диаграммы используют следующие понятия: ряд данных, категории, легенда.

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

Категории - (аргументы функции на оси Х) служат для упорядочения значений в рядах данных.

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

Задача построения диаграмм обычно разбивается на несколько этапов.

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

Этап 2. Выбор типа диаграмм. Во вкладке Вставка нужно выбрать необходимую диаграмму.

Этап 3. Редактирование и форматирование полученных диаграмм. С помощью вкладок Конструктор, Макет и Формат – преобразуем полученную диаграмму: можем изменить тип, макет диаграммы, стиль; добавить необходимые подписи или оси и т.д.

Задание 1. Постройте гистограмму.

Внимание! Для построения диаграммы воспользуетесь данными находящиеся в книге Расчеты.xlsx.

Этапы построения Гистограммы:

1. Откройте книгу Расчеты.xlsx. Выберите лист Показатели.

2. Выделите данные (диапазон А4:F5), которые нужно показать на гистограмме.

3. На вкладке Вставка в группе Диаграммы щелкните Гистограмма  Гистограмма с группировкой.

1. Получаем Гистограмму следующего вида (рисунок 2):

Рис.2 Вид гистограммы

1. Для форматирования и редактирования диаграммы, щелкните на область диаграммы. При этом появится панель с вкладками Конструктор (рис. 3), Макет (рис.4) и Формат(рис.5).

Рис.3. Панель Конструктор.

Рис.4. Панель Макет.

Рис.5. Панель Формат.

1. Для изменения значений по оси Х необходимо правой кнопкой мышки щелкнуть по значениям оси Х диаграммы и выбрать команду Выбрать данные или на вкладке Конструктор нажать соответствующую кнопку.

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

Рис.6 Окно Выбор источника.

1. В появившемся окне Подписи оси выбрать Диапазон подписей оси (в нашем случае - это строка Годы (B3:F3)).

Затем нажать Ок

И еще раз нажать Ок

Рис.7 Алгоритм выбора.

1. Диаграмма примет следующий вид:

Рис.8. Гистограмма с измененой осью Х

1. Добавьте для гистограммы:

Рис.9 Вкладка Макет –группа Подписи.

Название диаграммы, для этого на вкладке Макет в группе Подписи щелкните Название диаграммы  Над диаграммой (введите заголовок Показатели работы больницы №8).

Названия осей, для этого на вкладке Макет в группе Подписи щелкните и Названия осей и выберите Название основной горизонтальной оси Под осью (введите заголовок Годы)

Подписи данных, для этого на вкладке Макет в группе Подписи щелкните

Подписи данных  У вершины, снаружи.

Легенду расположите внизу, для этого на вкладке Макет в группе Подписи щелкните Легенда Добавить легенду внизу.

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

Рис.10. Гистограмма Показатели больницы №8.

Задание 2. Постройте график.

Этапы построения графика:

1. Воспользуемся данными листа Показатели.

2. Выделите данные (диапазон А4:F5), которые нужно показать на графике.

3. На вкладке Вставка в группе Диаграммы щелкните график график с маркерами.
4.Измените диаграмму в соответствии с рисунком.

Рис.11. Вид графика Показатели работы №8.

Задание 3. Постройте смешанную (комбинированная) диаграмму.

Внимание! Смешанная (комбинированная) диаграмма использует два или более типа диаграмм, чтобы подчеркнуть использование различных типов информации.

Этапы построения Смешанной (комбинированной) диаграммы

1. Воспользуемся данными листа Показатели.

2. Выделите данные (диапазон А4:F5), которые нужно показать на гистограмме.

3. На вкладке Вставка в группе Диаграммы щелкните Гистограмма  График получиться диаграмма как на рисунке 12.

4. Выделите график, построенный по значениям Плана, щелкните правой кнопкой мыши и выберите команду Изменить тип диаграммы для ряда.

Рис.12. Вид контекстного меню.

1. Выберите для ряда План тип диаграммы гистограмма.

2. Получим смешанную (комбинированную) диаграмму следующего вида.

Рис. 13.Вид комбинированной диаграммы

Задание 4. Постройте линейчатую диаграмму

Этапы построения Линейчатой диаграммы:

1. Для построения Линейчатой диаграммы воспользуемся введенными данными на листе Грипп

2. Выделите данные (диапазон А3:С7), которые нужно показать на Линейчатой диаграмме.

3. На вкладке Вставка в группе Диаграммы щелкните Линейчатая  Линейчатая с группировкой.

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

Полученную диаграмму отформатируйте в соответствии с рисунком 14.

Рис.14 Линейчатая диаграмма

Преобразуйте эту диаграмму  в линейчатую с накоплениями, для этого щелкните по области диаграмм и на вкладке Конструктор щелкните по и выбрать

Диаграмма примет следующий вид:

Рис. 15. Линейчатая диаграмма с накоплениями

Задание 5. Построите круговую диаграмму.

Этапы построения Круговой диаграммы:

1. Для построения Круговой диаграммы необходимо воспользоваться данными на листе Дизентерия.

2. Для построения Круговой диаграммы необходимо выделить интервал столбца «коэф-т» (С4:С7) и интервал столбца «формы болезни» (А4:А7), для легенды.

Рис.16. Круговая диаграмма и соотвествующий выделенные диапозоны.

Добавьте для круговой диаграммы Название над диаграммой
и Подписи данных у вершины снаружи. Для этого используйте команды  появится диалоговое окно (рис 15), выберите доли, имена категорий и У вершины, снаружи

Рис.15.Диалоговое окно Формата подписи данных

Легенду уберите и если нужно подкорректируйте размер диаграммы.

Рис. 16. Окончательный вид круговой диаграммы.

Задание 6. Постройте лепестковую диаграмму.

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

Этапы построения лепестковой диаграммы:

1. Для построения Лепестковой диаграммы создайте новый лист и введите данные представленные на рисунке 17.

2. Выберите данные (А5:D16), которые нужно показать на Лепестковой диаграмме.

Рис.17. Исходные данные для построения лепестковой диаграммы.

3. На вкладке Вставка в группе Диаграммы щелкните Другие диаграммы  в группе Лепестковая диаграмма выберите Заполненная лепестковая диаграмма.

Диаграмма примет следующий вид:

Рис.18.Лепестковая диаграмма.

4. Чтобы диаграмму можно было прочитать, необходимо изменить Легенду, для этого: на вкладке Конструктор в группе Данные щелкните Выбрать данные. Появиться диалоговое окно (рисунок 19) и поэтапно изменяем элементы легенды

Рис.19. Диалоговое окно Выбор источника данных.

Добавьте для диаграммы Название и диаграмма примет следующий вид:

Рис.20.Окончательный вид лепестковой диаграммы.

Задание 7. Постройте диаграмму со вспомогательными осями.

Если значения различных рядов данных на диаграмме значительно отличаются друг от друга или если на диаграмме представлены данные различных типов, целесообразно вывести один или несколько рядов данных на вспомогательной оси значений (Y). Масштаб вспомогательной оси выбирается с учетом значений соответствующего ряда. В приведенном примере построения диаграммы по левой оси Y откладывается число аварий и количество смертельно травмированных, а по правой - общий объем производства, млн. т.

Этапы построения диаграммы со вспомогательными осями:

1. Для построения диаграммы со вспомогательными осями вначале построим простую диаграмму график с маркерами. Необходимо ввести данные на новый лист рабочей книги в соответствии с риунком.21.

Рис.21.Исходные данные для построения диаграммы.

2. Выделите данные таблицы (это интервал В3:D12), который нужно показать на графике с маркерами. Получим следующую диаграмму рисунок 22. Видим, что масштаб графиков не соответствует правильному восприятию информации.

Рис.22. График.

3.Заменим значения по оси Х на Годы. Для изменения значений по оси Х необходимо правой кнопкой мышки щелкнуть по значениям оси Х графика и выбрать команду Выбрать данные. Далее в окне Выбор источника данных нажать на команду Изменить в окне Подписи горизонтальной оси. В следующем окне Подписи окна выбрать Диапазон подписей оси (в нашем случае - это колонка Годы).

Рис. 23 График, где на оси Х отображаются годы.

5. Введем вспомогательную ось Y для значений Общий объем производства, млн. т. Для этого выделим линию графика Общий объем производства, млн. т. и выберем команду Формат ряда данных.

Рис. 24. Вид контекстного меню.

Далее в окне Формат ряда данных в закладке Параметры ряда необходимо выбрать По вспомогательной оси.

Рис.25. Диалоговое окно Формат ряда данных.

Получим следующую диаграмму. Масштаб вспомогательной оси выбирается с учетом значений соответствующего ряда

Рис.26. Вид графика со вспомогательной осью.

Добавьте для графика Названия осей.

Легенду у диаграммы разместите снизу графика.

В приведенном примере диаграммы по левой оси Y откладывается число аварий и количество смертельных травмированных, а по правой - Общий объем производства нефти в год, млн. тонн.

Рис. 27. График. Динамика объемов производства, травматизма со смертельным исходом и аварийности в нефтеперерабатывающей промышленности за 1997-2005 г.г.

Преобразуйте полученную диаграмму в комбинированную диаграмму в соответствии с рисунком 28:

Рис.28. Комбинированная диаграмма Динамика объемов производства, травматизма со смертельным исходом и аварийности в нефтеперерабатывающей промышленности
за 1997-2005 г.г.

Задание 8. Постройте кольцевую диаграмму.

Этапы построения кольцевой диаграммы:

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

Рис.29 Исходные данные.

1. Выберите данные (А3:Е6), которые нужно показать на Кольцевой диаграммы.

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

3. Получим кольцевую диаграмму следующего вида рисунок 30. Добавьте название диаграммы и легенду расположите внизу.

Рис.30 Кольцевая диаграмма.

Внимание! Добавьте с помощью инструментов рисования пояснения (Предприятие 1, Предприятие 2, Предприятие 3).

Задание 9. Постройте диаграмму с линией тренда.

Для наглядного показа тенденции изменения некоторой переменной целесообразно на график вывести линию тренда. Это возможно не для всех типов диаграмм, а только для гистограмм, линейчатых диаграмм, диаграмм с областями, графиков. Введенная линия тренда сохраняет связь с исходным рядом, т. е. при изменении данных соответственно изменяется линия тренда. Линию тренда можно использовать для прогноза данных

Этапы построения диаграммы:

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

Рис.31 Исходные данные для построения диаграммы.

2. Для построения диаграммы выделите интервал ячеек с данными численности населения (A4:G4).

3. На вкладке Вставка в группе Диаграммы щелкните Гистограмма  Гистограмма с группировкой

4. Замените значения по оси Х на Годы и добавить название диаграммы Численность населения.

Получим диаграмму следующего вида рис.32.

Рис.32 Гистограмма

5. Осуществить прогноз численности населения России на 2012 г. добавлением линии тренда к ряду данных графика. Для этого сделайте диаграмму активной щелчком мыши по ней и на вкладке Макет щелкните по кнопке выберите команду Дополнительные параметры линии тренда. В открывшемся окне Формат линии тренда ( рис. 34) выберите вид тренда — полиномиальный 4-й степени, введите название кривой «Линия тренда» и установите параметр прогноза — вперед на один период.

Рис. 34. Диалоговое окно для задания типа и параметров линии тренда.

6. Нa диаграмме будут показаны линия тренда и прогноз на один период вперед. Если вы все сделали правильно, то прогноз численности населения России на 2012 г по линии тренда составит 131 млн чел (рис.35).

Рис.35. Окончательный вид диаграммы с линией тренда

7.Сохраните книгу под новым именем Диаграммы, с помощью команды Сохранить как...

Задание для самостоятельной работы

Запустите Microsoft Excel, каждое задание выполните на новом листе.

Задание 1. Создайте таблицу, содержащую следующие данные и постройте график.

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

Укажите в метках на диаграмме десятые доли в %.

Измените цвет всех секторов.

Отбуксируйте сектора на 0,5 см.

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

Задание 4. Создайте таблицу Продолжительности жизни в России с 1991 по 1997 год.

Представьте данные таблицы в виде графика.

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

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

Задание 6. В школьные годы для детей наиболее нужен четырёхразовый режим питания: первый завтрак -25%, второй завтрак -15%; обед – 40%; ужин -20% дневного рациона. Представьте данные в виде таблицы, и построить круговую диаграмму (назовите «Режим питания»)

Задание 7. Установлено, что здоровье зависит от следующих факторов: 50% образ жизни, 20% наследственность, 20% экологическая обстановка, 10% здравоохранение. Представьте данные в виде таблицы, и построить круговую диаграмму (назовите «Факторы, влияющие на здоровье»)

Задание 8. Сохраните книгу под именем Диаграммы_фамилия в своей папке.

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

1. Что такое диаграмма?

2. Перечислите основные типы диаграмм.

3. Из каких элементов состоят диаграммы?

4. Опишите последовательность создания диаграммы.

5. Как можно модифицировать диаграмму?

6. Как изменить размеры и месторасположение диаграммы?

7. Для отображения каких данных используется гистограмма?

8. Что такое линейчатая диаграмма?

9. Для отображения каких данных используются графики?

10. В каких случаях целесообразно использовать круговые диаграммы?

11. Что представляет собой комбинированная диаграмма?

12. Опишите способы построения комбинированных диаграмм.

7