Глава 9. Построение диаграмм

С помощью приложения MS Excel можно легко создавать профессионально оформленные диаграммы разнообразных типов. Выбрав тип, макет и стиль каждой диаграммы можно быстро и профессионально обработать массивы данных любого размера.

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

В MS Excel 2013 существует 10 типов диаграмм, показанные на рис.9.1, каждый из которых имеет несколько вариантов.

Рис. 9.1. Все виды диаграмм

Гистограмма

Гистограмма (имеет 7 подтипов) подчеркивает характер изменения данных за определенный период времени и иллюстрирует соотношение отдельных значений данных. Категории располагаются по горизонтали, а значения - по вертикали. Таким образом, достаточно подробно описывается динамика изменения параметра во времени.

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

Ваши действия:

1. Откройте книгу Лабораторные работы. xlsx, перейдите на лист Лаб.8.

2. Выделите диапазон ячеек А1:Е6 и скопируйте в книгу Упражнения. xlsx на лист Упр.9 (рис.9.2).

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

3. Выделите два диапазона ячеек: A3: D3 и при нажатой клавише Ctrl добавьте второй диапазон A6: D6.

4. На вкладке Вставка выберите тип диаграммы – Гистограмма (рис.9.3).

5. Используя команду Вставка, Добавить элемент диаграмы, Название диаграммы, Над диаграммой дайте название диаграммы – Сравнительный анализ продаж.

6. Выполните команду Конструктор, Добавить элемент диаграммы, Легенда, Сверху. В результате Легенда переместится над диаграммой.

7. Выделите диаграмму правой кнопкой мыши и выберите команду Выбрать данные.

8. Измените подписи горизонтальной оси, указав диапазон ячеек – B2: D2.

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

Круговая

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

Круговую диаграмму рекомендуется использовать, если:

- нужно отобразить только один ряд данных;

- все значения данных являются положительными;

- имеется не более семи категорий.

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

Ваши действия:

1. Выделите итоговые данные – Е3:Е6.

2. На вкладке Вставка выберите тип диаграммы – Объемная круговая (рис.9.4).

3. Дайте название диаграммы – Продажа билетов.

4. Легенду расположите внизу диаграммы.

5. В параметрах подписи, вызванной по команде Конструктор, Добавить элемент диаграмы, Подписи данных, Дополнительные параметры подписей данных снимите флажок – значения и установите флажок –   доли, Положение метки выберите – У края, снаружи.

Рис.9.4.Объемная круговая диаграмма

6. Выделите данные за февраль – С3:С6.

7. На вкладке Вставка выберите тип диаграммы – Круговая (рис.9.5).

8. Дайте название диаграммы – Продажа билетов в феврале.

9. В подписях данных для диаграммы укажите значения и имена категорий.

10. В подписях данных выберите Положение метки – У края, снаружи и оттяните все метки от края диаграммы в разные стороны.

11. Поверните диаграмму вокруг своей оси на угол ~20°, используя команду Формат ряда данных на правой кнопке мыши. Это делается для того, чтобы линии выноски хорошо просматривались и не перекрещивались.

Рис.9.5. Круговая диаграмма

Линейчатая

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

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

Ваши действия:

1. Выделите данные за январь – А3:В6.

2. На вкладке Вставка выберите тип диаграммы – Линейчатая с группировкой (рис.9.6).

3. Легенду удалите (она становится не нужна, так как стротся диаграмма для одного ряда данных).

4. Дайте название диаграммы – Количество проданных билетов в январе.

5. В подписях данных для диаграммы укажите значения, Положение метки – У края, снаружи.

6. Выделите правой кнопкой мыши любое значение горизонтальной оси и выберите команду Формат оси.

7. Установите максимальное значение – 120 и цену основного деления – 40.

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

График

График отражает изменение значений данных за равные промежутки времени (имеет 7 подтипов). График в Excel строится по точкам. На графиках данные категорий равномерно распределяются вдоль горизонтальной оси, а все значения равномерно распределяются вдоль вертикальной оси. Графики идеально подходят для представления тенденций изменения данных с равными интервалами, такими как месяцы, кварталы или финансовые годы.

Задание 4. Построить график.

Ваши действия:

1. Выделите в таблице исходных данных значения за январь, февраль и март вместе с заголовками – А2:D6.

2. На вкладке Вставка выберите График с маркерами (рис.9.7).

3. На вкладке Конструктор выполните команду Добавить элемент диаграммы, Таблица данных, Показывать легенду.

4. Подпишите вертикальную (Билеты, шт.) и горизонтальную оси (Пункты назначения), используя команду Конструктор, Добавить элемент диаграммы, Названия осей.

5. Дайте название диаграммы – Продажа билетов.

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

Точечная

Точечные диаграммы обычно используются для отображения и сравнения числовых значений, например научных, статистических или технических данных. Точечная диаграмма имеет две оси значений: горизонтальную (X) и вертикальную (Y).

Задание 5. Построить точечную диаграмму для функции Y = X ³ -12 X ² +3.

Ваши действия:

1. В ячейки А11-А31 введите значения аргумента Х:

- начальное значение аргумента: -5;

- приращение аргумента равно 1;

- конечное значение равно +15.

2. В ячейку В11 введитеформулу =А11^3-12* A 11^2+3, затем распространите ее до ячейки В31.

Рис. 9.8. Исходные данные для точечной диаграммы

3. По полученным данным постройте точечную диаграмму на этом же листе справа от таблицы данных (рис.9.9).

Рис.9.9. Точечная диаграмма

Поверхность

Поверхность используется для отображения отношений между большими объемами данных, которые трудно продемонстрировать иначе. Цветные полосы на поверхности не являются рядами данных — они указывают разницу между значениями.

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

Задание 6. Построить поверхность для функции Y=EXP(-((X1 -2)² -(X1 -2)(X2 -1)+(X2 -1)²)).

Ваши действия:

1. Создайте таблицу значений функции двух переменных с использованием смешанной адресации.

2. Переменная Х1 меняется от 0 до 3,5 с шагом 0,25 и расположена в строке (рис.9.10).

3. Переменная Х2 меняется от 0 до 4,5 с шагом 0,25 и расположена в столбце.

4. В ячейку В2 введитеследующую формулу, используя смешанную адресацию:

=EXP(-((B$35-2)^2-(B$35-2)*($A36-1)+($A36-1)^2))

Рис.9.10. Таблица исходных данных

5. Постройте диаграмму поверхности и расположите ее под исходной таблицей (рис.9.11).

Рис.9.11. График поверхности

Комбинированная

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

Задание 7. Построить Комбинированную диаграмму.

Ваши действия:

1. Перейдите на лист Лаб.8 и для Задания 2 (см. рис.8.13) постройте Комбинированную диаграмму.

2. Выделите ячейки В26-С33.

3. Выполните команду Вставка, Диаграммы, Комбинированная.

4. С помощью команды Конструктор, Выбрать данные измените названия рядов легенды: Ряд 1 – Данные по реализации (млн.руб.), Ряд 2 – Относительный прирост за год.

5. Легенду расположите над диаграммой.

6. Подпишите вертикальную ось – млн.руб.

7. Итоговая диаграмма, отображающая данные по реализации в виде гистограммы и процент относительного прироста, который необходимо построить по вспомогательной оси в виде графика, показана на рис.9.12.

8. Диаграмму расположите рядом с данными.

Рис. 9.12. Комбинированная диаграмма со вспомогательной осью