• Главная
• Контакты
• Нашёл ошибку
• Прислать материал
• Добавить в избранное

Часто программу Excel используют для создания документов, представляющих собой различные статистические и аналитические отчеты. Это могут быть отчеты о продажах, таблицы замеров температуры воздуха, данные социологических опросов и т. д. Цифры не всегда могут наглядно представить общую картину отчета (в них нужно разбираться, их необходимо сопоставлять, анализировать и т. д.), поэтому лучше использовать диаграммы, которые визуально покажут отличия тех или иных данных друг от друга.

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

МУЛЬТИМЕДИЙНЫЙ КУРС

Данная тема демонстрируется в видеолекциях, содержащихся в главе «Диаграммы».

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

Рис. 8.1. Таблица замера температуры

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

1. Выделите заполненный диапазон в таблице.

2. Перейдите на вкладку Вставка ленты.

3. Нажмите кнопку График, расположенную в группе Диаграммы. Появится галерея вариантов графика.

4. Выберите вариант График (самый первый). Программа Excel проанализирует вашу таблицу и создаст на листе график, изображенный на рис. 8.2.

Рис. 8.2. График изменения температуры

В созданном программой графике ось X представляет собой время (в нашем примере – дни), а ось Y – диапазон температур. На оси X и Y нанесены метки дат и шкала температур. Как видите, с помощью синей линии графика мы легко можем установить значение температуры в конкретный день, а также проследить периоды ее подъема и падения.

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

Теперь давайте усложним таблицу: добавим значения температуры за такой же период времени другого года (рис. 8.3) и отобразим это на графике.

Рис. 8.3. Измененная таблица замеров температуры

• Щелкните кнопкой мыши на рамке графика и нажмите клавишу Delete (Удалить).

• Нажмите кнопку График в группе Диаграммы вкладки Вставка и выберите вариант График. Появится пустая рамка диаграммы.

Мы создадим график температуры за одинаковый период двух лет вручную. Для этого нам понадобится создать две линии графика. Одна линия будет показывать изменения температуры за 2005 год, вторая – за 2006-й.

1. Щелкните правой кнопкой мыши на рамке графика.

2. В появившемся контекстном меню выберите пункт Выбрать данные. Появится диалоговое окно Выбор источника данных (рис. 8.4).

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

Нам следует создать компоненты графика (в данном случае – линии) и указать диапазон таблицы, данные из которого должны быть отображены на графике.

3. В области Элементы легенды (ряды) диалогового окна Выбор источника данных нажмите кнопку Добавить. Появится диалоговое окно Изменение ряда (рис. 8.5).

Рис. 8.5. Диалоговое окно Изменение ряда

4. В поле Имя ряда введите имя ряда графика (линии). Мы введем текст 2005 год.

5. В поле Значения следует указать значения температуры за период. Укажите диапазон ячеек R4C3:R11C3.

6. Нажмите кнопку ОК. Диалоговое окно будет закрыто, а в графике появится линия изменения температур за 2005 год.

Теперь нам нужно добавить линию графика, которая будет показывать изменения температуры за 2006 год.

ПРИМЕЧАНИЕ

Вы можете ввести диапазон ячеек вручную или воспользоваться кнопкой, расположенной в правой части поля Значения, и выделить диапазон ячеек R4C3:R11C3 с помощью мыши.

1. В группе Элементы легенды (ряды) диалогового окна Выбор источника данных нажмите кнопку Добавить. Вновь появится диалоговое окно Изменение ряда.

2. Введите имя ряда: 2006 год.

3. В поле Значения укажите диапазон ячеек с данными о температуре за период 2006 года (R4C4:R11C4).

4. Нажмите кнопку ОК. Диалоговое окно будет закрыто, а на графике появится вторая линия, отображающая изменения температуры за период 2006 года (рис. 8.6).

Рис. 8.6. Две линии на графике

Обратите внимание, что линии графика выполнены разными цветами. Справа от графика вы видите образцы линий с текстом 2005 год и 2006 год– это расшифровка графика, называемая легендой, которая подсказывает, какая линия за какой период показывает изменения температуры. В нашем примере синяя линия показывает изменения температуры за 2005 год, а красная – за 2006-й.

Если распечатывать такой график на монохромном принтере, вы можете не увидеть разницы между цветами линий. Поэтому на линиях есть маркеры, расположенные в точках преломления: линия 2005 год содержит ромбовидные маркеры, а 2006 год – квадратные. Вид маркеров для линий каждого года также отображен в легенде.

Нажмите кнопку ОК диалогового окна Выбор источника данных. Диалоговое окно будет закрыто.

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

• Выделите ячейку R7C3 (ячейку, отображающую температуру за 16 февраля 2005 года).

• Измените значение ячейки на 5 и нажмите клавишу Enter. Точка синей линии графика изменит свое положение. Теперь точка преломления линии находится на пересечении отметок 16 февраля и 5 градусов.

Вы можете попробовать сами изменить несколько значений диапазонов R4C3:R11C3 и R4C4:R11C4 и убедиться, что график реагирует на любое изменение данных таблицы.

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

Тип созданного графика вы можете изменить без искажения данных, отображенных в нем.

1. Установите указатель мыши на созданный график и щелкните правой кнопкой мыши.

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

Рис. 8.7. Диалоговое окно Изменение типа диаграммы

3. Выберите образец Объемный график (названия образцов отображаются во всплывающей подсказке при наведении на них указателя) в категории График.

4. Нажмите кнопку ОК. Диалоговое окно Изменение типа диаграммы закроется, а график будет представлен в трехмерном виде (рис. 8.8).

Рис. 8.8. Трехмерное представление графика

Теперь создайте таблицу, изображенную на рис. 8.9 (вы можете указать в ней свои данные). Чтобы не терять времени на ввод данных в ячейки R4C3:R7C6, я использовал во всех ячейках функцию СЛУЧМЕЖДУ, которая сгенерировала значения в этих ячейках.

Рис. 8.9. Пример таблицы отчета по продажам

Построим диаграмму статистики продаж, используя данные этой таблицы.

1. Перейдите на вкладку Вставка ленты.

2. Нажмите кнопку Гистограмма, расположенную в группе Диаграмма.

3. В появившемся списке выберите пункт Гистограмма с группировкой (самый первый пункт). В таблице появится пустая рамка диаграммы.

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

4. Щелкните правой кнопкой мыши на рамке диаграммы и в появившемся контекстном меню выполните команду Выбрать данные. Откроется уже знакомое вам диалоговое окно Выбор источника данных.

Сначала укажем данные о продажах за 2003 год.

5. Нажмите кнопку Добавить, расположенную в области Элементы легенды (ряды). Появится диалоговое окно Изменение ряда.

6. Введите название ряда в поле Имя ряда, например 2003. Вы также можете нажать кнопку, расположенную в правой части поля Имя ряда, и указать ячейку R4C2, которая содержит текст 2003 (имя ряда при этом будет использовать данные этой ячейки).

7. В поле Значения укажите диапазон ячеек, отображающих уровень продаж за 2003 год. Это диапазон R4C3:R4C6.

8. Нажмите кнопку ОК диалогового окна Изменения ряда. На гистограмме появятся четыре столбика, отображающих уровень продаж за каждый месяц 2003 года (рис. 8.10).

Рис. 8.10. Столбики гистограммы

9. Нажмите кнопку Добавить, расположенную в группе Элементы легенды (ряды). Вновь появится диалоговое окно Изменение ряда.

10. В качестве имени ряда укажите 2004 или ячейку R5C2.

11. В поле Значения укажите диапазон ячеек таблицы с продажами за 2004 год (R5C3:R5C6) и закройте диалоговое окно.

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

13. Вышеописанными способами добавьте в гистограмму данные о продажах за 2005 и 2006 годы.

В итоге у вас должна получиться гистограмма, изображенная на рис. 8.11.

Рис. 8.11. Готовая гистограмма

Данная гистограмма содержит четыре группы столбиков. Каждая группа, в свою очередь, состоит из четырех разноцветных столбиков. Каждый столбик определенного цвета отображает состояние продаж за определенный период (в нашем случае – месяц) конкретного года. В легенде представлены образцы цветов за каждый год (например, зеленые столбики – за 2003 год, синие – за 2004-й и т. д.).

Гистограмма, которую мы создали, не совсем информативна. Слева отображается шкала числовых значений (уровень продаж), легенда поясняет принадлежность цветов столбиков к определенным годам, но не совсем понятно, за какие месяцы группы столбиков гистограммы отображают уровень продаж. Вы видите под группами столбиков обозначения (1, 2, 3 и 4). Чтобы внести в гистограмму ясность, необходимо заменить эти цифры названиями месяцев.

• Нажмите кнопку Изменить, расположенную в области Подписи горизонтальной оси (категории) диалогового окна Выбор источника данных. Появится окно Подписи оси (рис. 8.12).

Рис. 8.12. Диалоговое окно Подписи оси

• В поле Диапазон подписей оси укажите диапазон ячеек с названиями месяцев (R3C3:R3C6) и нажмите кнопку ОК. Диалоговое окно будет закрыто, а под группами столбиков гистограммы появятся надписи Январь, Февраль, Март и Апрель.

ПРИМЕЧАНИЕ

Программа Excel произвольно присваивает цвета новым элементам диаграммы, поэтому мы не называем цвета, в которые окрашены столбики нашей гистограммы: у вас могут быть совсем другие.

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

Закройте диалоговое окно Выбор источника данных.

Теперь изменим вид созданной нами гистограммы.

• Щелкните правой кнопкой мыши на созданной гистограмме и в появившемся контекстном меню выполните команду Изменить тип диаграммы.

• В появившемся диалоговом окне Изменение типа диаграммы выберите образец Гистограмма с накоплением из категории Гистограмма и нажмите кнопку ОК. Вид гистограммы изменится (рис. 8.13).

Рис. 8.13. Гистограмма с накоплением

Теперь гистограмма представлена в виде четырех столбиков, каждый из которых состоит из четырех цветных секций. Нетрудно догадаться, что каждая из четырех секций столбика – это не что иное, как информация о продажах за определенный год. При этом общая высота столбиков позволяет проанализировать данные о сезонном уровне продаж. Например, гистограмма в нашем примере говорит о наиболее высоком уровне продаж в апреле по итогам четырех лет.

Измените тип гистограммы на Цилиндрическую с накоплением. Она примет трехмерный вид, а столбики будут представлены в виде цилиндров, состоящих из цветных секций (рис. 8.14).

Рис. 8.14. Цилиндрическая гистограмма

Измените тип диаграммы на Линейчатую с накоплением из категории Линейчатая. Диаграмма будет повернута на 90 градусов, при этом названия месяцев будут располагаться по оси Y, а шкала продаж – по оси X.

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

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

Для этого примера подготовьте таблицу, изображенную на рис. 8.15.

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

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

2. Щелкните правой кнопкой мыши на появившейся рамке и в контекстном меню выполните команду Выбрать данные.

3. Нажмите кнопку Добавить, расположенную в области Элементы легенды (ряды) появившегося диалогового окна. Откроется диалоговое окно Изменение ряда.

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

5. В поле Значения укажите диапазон ячеек R3C3:R8C3. Эти ячейки содержат данные о продажах.

6. Закройте диалоговое окно Изменение ряда, нажав кнопку ОК. Круг диаграммы будет разделен на несколько цветных секторов, каждый из которых показывает долю продаж определенного вида товара от общего объема (рис. 8.16).

Рис. 8.16. Сектора круга показывают долю продаж каждого вида продаж от общего объема

В данный момент мы не можем определить, какому товару принадлежит тот или иной сектор диаграммы, так как в легенде каждому цвету просто присвоен определенный номер. Исправим это.

1. Нажмите кнопку Изменить, расположенную в области Подписи горизонтальной оси (категории). Появится диалоговое окно Подписи оси.

2. В поле Диапазон подписей оси укажите диапазон ячеек с названиями категорий товаров (R4C2:R9C2).

3. Закройте диалоговое окно Подписи оси.

Теперь наша диаграмма наглядно представляет доли продаж каждой категории в общем объеме. Полный круг показывает собой общий уровень продаж компании, а сектора – доли продаж каждой категории товаров (рис. 8.17).

Рис. 8.17. Готовая круговая диаграмма

На этом работу с диаграммой можно было бы считать законченной, но вы можете внести еще несколько штрихов в общее оформление диаграммы.

Несколько изменим вид круговой диаграммы.

• Выделите диаграмму и перейдите на вкладку Конструктор.

• Раскройте галерею Макеты диаграмм и выберите Макет 1.

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

Рис. 8.18. Альтернативное представление диаграммы

Выберите для диаграммы Макет 2. Категории товаров теперь представлены в легенде, расположившейся над диаграммой, а в секторах указана процентная доля продаж отдельных видов товаров от общего объема.

Вы можете изменить цветовую схему диаграммы.

1. Выделите диаграмму.

2. Раскройте галерею Стили диаграмм. В галерее представлены различные образцы цветового оформления диаграмм.

3. Поочередно выберите несколько стилей для диаграммы. Цветовая схема диаграммы будет меняться.

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

Некоторые свойства диаграммы можно изменить и на вкладке Макет.

1. Перейдите на вкладку Макет.

2. Щелкните на кнопке Легенда, расположенной в группе Подписи.

3. В появившемся списке вариантов размещения легенды выберите пункт Добавить легенду снизу. Легенда появится под диаграммой.

4. Выберите вариант размещения легенды Добавить легенду слева. Легенда появится слева от диаграммы.

5. Выберите вариант размещения легенды Нет. Легенда будет удалена с диаграммы.

Некоторые свойства, доступные на вкладке Макет, различаются для разных видов диаграмм. К тому же для некоторых типов диаграмм определенные свойства могут быть недоступны. Взгляните на группы Оси и Фон вкладки Макет – кнопки, расположенные в этих группах, неактивны при работе с круговой диаграммой.

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

1. Перейдите на вкладку Макет.

2. Нажмите кнопку Сетка, расположенную в группе Оси.

3. В появившемся списке выполните команду Горизонтальные линии сетки по основной оси > Показывать основные и промежуточные линии сетки. За столбиками гистограммы появятся дополнительные линии, позволяющие более точно оценить высоту столбиков (рис. 8.19).

Рис. 8.19. Линии позади гистограммы позволяют более точно оценить высоту столбиков

4. Нажмите кнопку Оси и в появившемся списке выполните команду Основная горизонтальная ось > Справа налево. Гистограмма будет отражена по горизонтали, при этом шкала вертикальной оси окажется справа, а столбики, отражающие данные таблицы, расположатся справа налево.

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

Вы можете также добавить названия осей диаграммы.

1. Нажмите кнопку Названия осей, расположенную в группе Подписи вкладки Макет.

2. В появившемся списке выполните команду Название основной вертикальной оси > Повернутое название. В области шкалы по оси Y появится текстовый объект с надписью Название оси.

3. Выделите текст Название оси, удалите его и введите свой, например Уровень продаж.

Аналогичным образом вы можете добавить название горизонтальной оси диаграммы.

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

Из каких компонентов состоит диаграмма? Это зависит от типа диаграммы. Возьмем, к примеру, созданную нами гистограмму.

1. Щелкните кнопкой мыши на верхней секции любого столбика. Будут выделены верхние секции всех четырех столбиков гистограммы, о чем свидетельствуют круглые маркеры, появившиеся по углам секций (рис. 8.20).

Рис. 8.20. Верхние секции столбиков гистограммы выделены

2. Щелкните кнопкой мыши на любой другой секции (например, нижней). Будут выделены нижние секции столбиков.

3. Щелкните кнопкой мыши на любой линии сетки, расположенной за гистограммой. Будут выделены линии сетки.

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

Изменим цвет верхних секций столбиков гистограммы.

1. Выделите верхние секции столбиков, щелкнув кнопкой мыши на верхней секции любого столбика.

2. Перейдите на вкладку Формат.

3. Нажмите кнопку Формат выделенного фрагмента, расположенную в группе Текущий фрагмент. Появится диалоговое окно Формат ряда данных (рис. 8.21).

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

4. Переместите диалоговое окно на экране так, чтобы видеть диаграмму.

5. Выберите пункт Заливка в списке, расположенном в левой части открывшегося диалогового окна.

6. Установите переключатель Заливка в положение Сплошная заливка. Под переключателем появятся элементы управления для изменения свойств заливки выделенного элемента.

7. Нажмите кнопку Цвет. Под ней появится палитра цветов.

ПРИМЕЧАНИЕ

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

ПРИМЕЧАНИЕ

В категории Стили границ вы можете изменить толщину и тип линий границ.

8. Щелкните кнопкой мыши на образце нужного цвета. Верхние секции столбиков будут немедленно окрашены в выбранный вами цвет.

9. Переместите ползунок Прозрачность и посмотрите на изменения в выделенных секциях. Вы можете установить для выделенных элементов различную степень прозрачности.

10. Перейдите в категорию Цвет границы диалогового окна Формат ряда данных.

11. Установите переключатель Цвет границы в положение Сплошная линия.

12. Нажмите кнопку Цвет и на появившейся палитре выберите цвет линий границ выделенного объекта.

13. Закройте диалоговое окно Формат ряда данных, нажав кнопку Закрыть.

Чтобы изменить свойства другого объекта диаграммы, необходимо его выделить. Вы можете щелкнуть кнопкой мыши на элементе, который хотите выделить, но есть и другой способ.

1. Откройте раскрывающийся список, расположенный в группе Текущий фрагмент вкладки Формат. В списке отобразятся все элементы диаграммы.

2. Выберите элемент диаграммы, который хотите изменить, например Легенда. Блок легенды будет выделен.

3. Раскройте галерею Стили фигур, расположенную на вкладке Формат.

4. Выберите подходящий образец стиля и посмотрите на изменения в элементе Легенда (рис. 8.22).

Рис. 8.22. К блоку Легенда применен стиль

В группе Стили WordArt вкладки Формат вы можете задать цвет заливки и контура текста элемента Легенда, а также выбрать один из готовых шаблонов, раскрыв галерею, расположенную в этой группе. Кроме того, вы можете произвести изменения в выделенной группе в диалоговом окне Формат ряда данных, который появляется при нажатии кнопки Формат выделенного фрагмента.

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

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