Построение и форматирование диаграмм

Диаграммы используются для наглядного представления информации, содержащейся в таблицах, и ее анализа. Диаграмма является графическим объектом и располагается поверх ячеек рабочего листа или на отдельном листе диаграммы. В Excel можно строить различные типы диаграмм: гистограммы, графики, поверхности, линейчатые, круговые, кольцевые, лепестковые, точечные, пузырьковые, биржевые. Для создания диаграммы нужно подготовить данные на рабочем листе, выделить их и выбрать нужный тип диаграммы на закладке Вставка ленты инструментов (рис. 5.1).

Рис. 5.1. Закладка Вставка ленты инструментов

После вставки диаграммы при ее выделении на ленте инструментов появляются три дополнительные закладки для работы с диаграммами (рис. 5.2).

Рис. 5.2. Закладки для работы с диаграммами

Диаграмма состоит из ряда элементов (рис. 5.2), к которым относятся: оси, легенда, маркеры, ряды данных, область построения диаграммы, подписи, линии сетки. Созданную диаграмму можно перемещать, форматировать, изменять ее размеры. Форматирование диаграммы и ее отдельных элементов осуществляется с помощью команд контекстного меню либо кнопок на закладках Конструктор, Макет, Формат ленты инструментов. Создание диаграммы и форматирование ее элементов рассмотрим более подробно на примерах.

Пример 5.1. Построить график функции для значений x Î [‑1,2; 1,4] с шагом 0,2. Отформатировать построенную диаграмму так, чтобы оси пересекались в нуле, цена деления по оси значений — 1,5, маркеры ряда данных — в виде треугольника. Продлить построенный график для значений x Î ]1,4; 2,2].

Выполнение:

1. Ввести подписи: в А1 — х, в В1 — y (рис. 5.4).

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

1. В А2 ввести -1,2, в А3 — -1. Выделить А2:А3 и протянуть маркером до А15, при этом ячейки заполнятся значениями х с шагом 0,2.
2. В ячейку В2 ввести формулу для y и протянуть маркером до В15 =ЕСЛИ(A2<=0;(1+A2^3)/(4+COS(A2));2*A2^2*SIN(A2)^3)
3. Выделить диапазон В1:В15, перейти на закладку Вставка, раскрыть кнопку График и выбрать тип диаграммы — график с маркерами (рис. 5.1).
4. Нажать кнопку Выбрать данные на закладке Макет ленты инструментов и в открывшемся диалоге (рис. 5.3) нажать кнопку Изменить подписи горизонтальной оси. В появившемся окне указать диапазон подписей оси (выделить мышью). После этого на графике вместо порядковых номеров на оси х будут отображаться его значения.

Рис. 5.4. График функции y = f (x) (пример 5.1)

6. В построенном графике нужно отформатировать оси и ряд данных. Для этого:

- в списке элементов диаграммы на закладке Макет (рис. 5.2) выбрать элемент Горизонтальная ось (категорий) и затем нажать кнопку ;

- в открывшемся диалоге активизировать опции, как показано на рис. 5.5 слева, чтобы оси пересекались в нуле;

Рис 5.5. Форматирование оси категорий (слева) и оси значений (справа)

- затем выбрать элемент Вертикальная ось (значений) и отформатировать его, как показано на рис. 5.5 справа;

- после выбора элемента Ряд “y” отформатировать тип, размер и заливку маркера, цвет и тип линии ряда (рис. 5.4, 5.6)

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

1. Для продления графика заполнить ячейки А16:А19 значениями х и протянуть формулу для y до В19.
2. Выделить диаграмму щелчком мыши. Диапазоны ячеек, которые были использованы для построения диаграммы, будут выделены цветными рамками. Протянуть левой кнопкой мыши за маркер (правый нижний угол ячейки В15) до ячейки В19, после чего новые данные будут отображены на графике.

Пример 5.2. Построить круговую диаграмму по данным таблицы А1:В10 (рис. 5.8).

Выполнение:

1. Имеющиеся данные будут отображены более наглядно на вторичной круговой диаграмме à , так как продажи за май—сентябрь значительно меньше, чем в первые месяцы.

2. После вставки вторичной круговой диаграммы необходимо изменить параметры ряда данных так, чтобы на вторичную диаграмму попали только данные за май—сентябрь (рис. 5.7).

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

Рис. 5.8. Вторичная круговая диаграмма (пример 5.2)

3. Для вставки подписей данных нужно раскрыть кнопку Подписи данных на закладке Макет, выбрать строку Дополнительные параметры подписей данных и изменить подписи, как показано на рис. 5.9. Можно также выдвинуть мышкой один сектор, при этом должен быть выделен только этот сектор.

Рис. 5.9. Форматирование подписей данных

Пример 5.3. Построить гистограмму по данным таблицы А2:D6 (рис. 5.10).

Выполнение:

1. Выделить А2:D6 и вставить гистограмму с группировкой.

Рис. 5.10. Гистограмма со вспомогательной осью (пример 5.3)

2. Объем производства товара С на два порядка меньше, чем товаров А и В, поэтому данные по товару С практически не будут видны на гистограмме. Чтобы этого избежать, нужно выбрать элемент диаграммы Ряд “Товар С” на закладке Макет, открыть его формат и указать опцию по вспомогательной оси, а также боковой зазор 300% (рис. 5.11). Не отменяя выделение ряда “Товар С” добавить для него подписи значений с ключом легенды (см. пример 5.2).

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

Пример 5.4. Построить поверхность z = y² – 4x для х и у Î [-1; 1] c шагом 0,2.

Выполнение:

Для построения поверхности нужно правильно подготовить исходные данные на листе. Значения Х и Y должны образовывать плоскость (т.е. одному Х соответствует ряд Y и наоборот), поэтому значения Х вводятся в первый столбец A2:A12, а Y — в первую строку B1:L1, ячейка А1 должна быть пустой. Затем в ячейку B2 вводится формула для Z с использованием ссылок на Х и Y, меняется тип адресации и копируется по строкам и по столбцам: =B$1^2 – 4*$A2. После такой подготовки данных нужно выделить весь диапазон A1:L12,раскрыть кнопку Другие диаграммы на закладке Вставка и выбрать Поверхность. Удалить легенду, изменить стиль диаграммы по своему усмотрению.

Рис. 5.12. Исходные данные для построения поверхности

Литература

1. Microsoft Office 2007: все программы пакета: Word, Excel, Access, PowerPoint, Publisher, Outlook, OneNote, InfoPath, Groove / Тихомиров А. Н. [и др.]. - Санкт-Петербург: Наука и техника, 2009. - 599 с.

2. Microsoft Office Excel 2007: [русская версия: перевод с английского] / Кёртис Д. Фрай. - Москва: ЭКОМ, 2009. - 479 с.

3. Excel 2007 для менеджеров и экономистов: логистические, производственные и оптимизационные расчеты / Александр Трусов. - Санкт-Петербург: Питер: Питер Пресс, 2009. – 254 с.

4. Графики, вычисления и анализ данных в Excel 2007 / Серогодский В. В. [и др.]. - Санкт-Петербург: Наука и техника, 2009. - 333 с.

5. Функции в Excel 2007: справочник пользователя / Минько А. А.. - Москва: Эксмо, 2008. - 480 с.

6. Экономическая информатика / под ред. П.В. Конюховского и Д.Н. Колесова. – СПб: Питер, 2001. – 560 с.

7. Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. – СПб.: BHV–Петербург, 2001. – 816 с.

8. Додж М., Кината К., Стинсон К. Эффективная работа с Excel 7.0: пер. с англ. – СПб: Питер, 1996. – 1031 с.

9. Обработка и анализ экономической информации в Microsoft Excel: Пособие для студентов экономических специальностей / Д.П. Подкопаев, В.И. Яшкин. – Мн.: Издательство БГУ, 2001. – 50 с.

10. Бизнес-анализ с помощью Microsoft Excel / Карлберг Конрад. – 2-е изд. – М.: Вильямс, 2003. – 446 с.

11. Использование макросов в Excel / С. Роман. – 2-е изд. – СПб: Питер, 2004. – 507 с.

12. Гетц К., Гилберт М. Программирование на Visual Basic и VBA. Руководство разработчика: пер с англ. – К.: Издательская группа BHV, 2001. – 912 с.