Работа с диаграммами в MS Excel

Выполнена Работа с диаграммами в MS Excel

ТЕМА: Работа с диаграммами в MS Excel.

ЦЕЛЬ: изучить дополнительные возможности MS Excel по работе с диаграммами, закрепить навыки построения и форматирования диаграмм, освоить редко используемые приемы настройки диаграмм.

1  Задание.

1. В рабочей книге MS Excel построить все диаграммы, изображенные на рис. 1 – 10. В названии диаграмм добавить свои Фамилию И.О.

2     Ход работы.

2.1     Обработка пропущенных данных

Иногда в наборе данных для диаграммы может быть пропущено одно или несколько значений. Как видно на рис. 1, Excel предлагает три способа обработки таких пропущенных данных.

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

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

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

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

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

Рис. 1. Три возможности обработки пропущенных данных.

2.2     Построение комбинированных диаграмм

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

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

Если при выборе команды «Работа с диаграммами – Конструктор – Тип – Изменить тип диаграммы» не был выделен ни один ряд данных, то новый выбранный тип диаграммы будет применен ко всем рядам данных.

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

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

Рис. 2. Значения второго ряда почти не видно на совместной диаграмме.

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

1. Для появления диалогового окна Формат ряда данных дважды щелкните на ряде данных Осадки.

2. В диалоговом окне Формат ряда данных перейдите на вкладку Параметры ряда и установите переключатель По вспомогательной оси.

3. Пока выделен ряд Осадки, выберите команду Работа с диаграммами – Конструктор – Тип – Изменить тип диаграммы.

4. В диалоговом окне Изменение типа диаграммы выберите тип График и щелкните на кнопке ОК.

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

Рис. 3. Новый тип и дополнительная ось для второго ряда данных.

2.3     Применение в диаграммах графических объектов

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

Рис. 4. Фоном области диаграммы является фотография.

Вставка изображения в какой-либо элемент диаграммы осуществляется с помощью вкладки Заливка диалогового окна Формат этого элемента. На этой вкладке установите переключатель Рисунок или текстура и щелкните на одной из кнопок, определяющей источник изображения (эти кнопки называются Файл, Буфер обмена и Клип). Щелчок на копке Буфер обмена вставляет изображение, которое заранее должно быть помещено в буфер обмена. После щелчка на двух других кнопках необходимо выбрать изображение.

На рис. 5 представлены два примера диаграмм, в которых использованы изображения из коллекции Microsoft Office и стандартные фигуры Office.

Рис. 5. Использование в диаграммах разнообразных изображений.

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

2.4     «Градусники»

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

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

За 15 дней планируется привлечь 1000 новых клиентов. Это число записано в ячейке В18. В ячейке В19 записана простая формула суммирования значения из диапазона В2:В16. В ячейке В21 с помощью формулы = В19/В18 вычисляется процент выполнения задачи.

Рис. 6. Эта диаграмма позволяет определить, на сколько процентов выполнена поставленная задача.

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

1. Удалите горизонтальную ось категорий с диаграммы, выделив ее и нажав клавишу <Delete>.

2. Удалите легенду.

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

4. В диалоговом окне Формат ряда данных на вкладке Параметры ряда установите значение 100% для параметра Перекрытие рядов и значение 0 – для параметра Боковой зазор.

5. Откройте диалоговое окно Формат оси для оси значений, на вкладке Параметры оси установите параметры максимальное значение равным 1 и минимальное значение равным 0.

2.5     Процентная диаграмма

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

Рис. 7. Эта диаграмма по внешнему виду напоминает спидометр и отображает значения от 0 до 100%

Нижняя часть круга скрыта и всегда будет равной 50%. (Чтобы скрыть сектор круговой диаграммы, выделите этот сектор (одну точку данных) и в диалоговом окне Формат точки данных установите опции Нет заливки и Нет линий.) Размеры двух других секторов основаны на значении из ячейки В1. В ячейку А4 введена следующая формула:

=МИН(В1, 100% )/2

Эта формула определяет, какое из двух значений (хранящееся в ячейке В1 или 100%) является меньшим. Затем, поскольку мы видим лишь половину круга, полученное значение нужно поделить на 2. С помощью функции МИН на диаграмму налагается ограничение, и она никогда не будет отображать значение больше 100%.

Ячейка А5 используется для определения оставшейся части круга, т.е. области диаграммы, которая находится справа от разделителя:

=50%-А5

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

2.6     Гистограмма с условными цветами столбцов

Вы могли заметить, что если гистограмма содержит один ряд данных, то в диалоговом окне Формат ряда данных на вкладке Заливка появляется опция Разноцветные точки.

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

К сожалению, эти цвета нельзя связать со значениями ряда данных. В этом разделе будет показано, как можно связать значения ряда данных гистограммы с цветом столбцов. На рис. 8 показана такая гистограмма (более выразительно она смотрится в цвете). Данные, которые используются для создания диаграммы, хранятся в диапазоне A1:F14.

Рис. 8. Цвет каждого столбца этой гистограммы зависит от соответствующего значения ряда данных.

Данные для этой диаграммы записаны в столбце В, но сама диаграмма строится по четырем рядам данных, записанных в столбцах С:F. Формулы в столбцах С:F выбирают из данных столбца В те значения, которые не превышают величин, записанных в диапазоне C1:F1.

Например, в ячейке СЗ записана формула

=ЕСЛИ(ВЗ<=$С$ 1 ; В З ; " " )

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

Формула в ячейке D3 чуть посложнее предыдущей, поскольку с ее помощью отбираются значения, которые превышают значения в ячейке С1, но которые меньше значения в ячейке D1. Эта формула имеет вид

=ЕСЛИ(И( $ВЗ > С $ 1 ; $B3 < =D$ 1 ) ; $ В З ; " " )

Эта формула копируется вниз по столбцу D. Аналогичные формулы используются в столбцах Е и F.

На диаграмме ряды данных перекрывают друг друга. Но поскольку все четыре ряда данных имеют пропуски значений, то это перекрытие визуально отсутствует. Весь “фокус” этой диаграммы состоит в том, чтобы для всех рядов данных подобрать такое одинаковое значение параметра Перекрытие рядов на вкладке Параметры ряда диалогового окна Формат ряда данных (в данном случае выбрано значение этого параметра, равное 75%), чтобы не было видно принадлежности столбцов разным рядам данных. Другими словами, с помощью этого параметра надо добиться того, чтобы расстояние между любыми соседними столбцами было примерно одинаковое.

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

2.7     Сравнительные гистограммы

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

Рис. 9. Сравнительная гистограмма

Вот как построить такую диаграмму.

1. Введите данные в диапазон А1:С8, как показано на рис. 9. Обратите внимание: значения для групп женщин введены в виде отрицательных чисел.

2. Выделите диапазон A1:С8 и создайте линейчатую диаграмму. Используйте подтип диаграммы Линейчатая с группировкой.

3. Выделите горизонтальную ось и откройте ее диалоговое окно Формат оси.

4. На вкладке Число диалогового окна Формат оси задайте числовой формат 0%;0%;0%. Это специальное форматирование переводит отрицательные значения в проценты.

5. Выделите вертикальную ось и откройте ее диалоговое окно Формат оси.

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

7. Выберите по очереди каждый ряд данных и откройте его диалоговое окно Формат ряда данных.

8. На вкладке Параметры ряда диалогового окна Формат оси установите значение опции Перекрытие рядов равным 100, а опции Боковой зазор – равным 0.

9. Вместо легенды дополните диаграмму двумя текстовыми окнами с надписями Женщины и Мужчины.

10. Примените необходимые атрибуты форматирования.

2.8     Диаграмма Ганта

Диаграмма Ганта – это горизонтальная линейчатая диаграмма, которая часто используется в программах управления проектами.

Несмотря на то что в Excel диаграммы Ганта как таковые не поддерживаются, их можно достаточно просто создать. Здесь самое главное – правильно задать исходные данные.

На рис. 10 показана диаграмма Ганта, которая графически отображает расписание проекта, расположенного в диапазоне А2:С13. На горизонтальной оси отображается общая длительность выполнения проекта, а каждый прямоугольник соответствует одному заданию.

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

Рис. 10. Диаграмма Ганта строится на основе линейчатой диаграммы.

В столбце А содержатся названия заданий; в столбце В – дата начала выполнения соответствующего задания; в столбце С – длительность выполнения задания в днях.

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

1. Выделите диапазон А1:С13 и создайте линейчатую диаграмму с накоплениями.

2. Удалите легенду диаграммы.

3. Выделите ось категорий (здесь это вертикальная ось) и откройте ее диалоговое окно Формат оси.

4. На вкладке Параметры оси диалогового окна Формат оси установите флажок Обратный порядок категорий. На этой же вкладке установите переключатель Горизонтальная ось пересекает в максимальной категории.

5. Выделите ряд данных Дата начала и откройте его диалоговое окно Формат ряда данных.

6. На вкладке Параметры оси диалогового окна Формат ряда данных установите значение параметра Перекрытие рядов равным 100%. На вкладке Заливка этого окна установите переключатель Нет заливки. На вкладке Цвет границы установите переключатель Нет линий. Эти установки надежно скроют данный ряд значений.

7. Выделите ось значений (здесь это горизонтальная ось) и откройте ее диалоговое окно Формат оси.

8. На вкладке Параметры оси диалогового окна Формат оси в полях Минимальное значение и Максимальное значение задайте начальную и конечную даты, которые будут отображаться на диаграмме. Эти даты надо задавать в виде порядковых чисел (внутренний формат Excel), а не в привычном формате дат. В нашем примере Минимальное значение равно 40301 (3 мая 2010), Максимальное значение – 40385 (26 июля 2010). Для отображения интервалов в одну неделю задайте значение 7 в поле Цена основных делений. На вкладке Число этого окна задайте формат дат, в котором они будут отображаться как подписи оси.

9. Выполните другое форматирование, которое вы сочтете нужным.