В программе Excel средства построения диаграмм позволяют изобразить основные статистические графики (кроме картограммы и картодиаграммы).
Для построения диаграммы необходимо ввести исходные данные и вызвать процедуру «Мастер диаграмм».
Исходные данные обычно можно представить в виде таблицы, в которой значения показателей находятся в столбцах или строках, при этом первая ячейка каждого столбца или первая ячейка каждой строки содержит название соответствующего показателя.
Например, на рисунке 3.11 исходные данные находятся в диапазоне А1:B11, при этом ряды данных содержатся в столбцах, имена показателей: Затраты на рекламу и объем выручки от реализации.
Рисунок 3.11. Затраты на рекламу продукции и объем выручки от реализации продукции
На рисунке 3.12 исходные данные находятся в диапазоне А1:Н2, а ряды данных содержатся в строках.
Рисунок 3.12. Объемы ежемесячных продаж за январь-июль 2010 года, млн.руб.
«Мастер диаграмм» предоставляет пользователю удобный интерфейс для создания диаграммы.
Для быстрого построения диаграммы достаточно
- выделить диапазон ячеек, образующих таблицу исходных данных, вместе с названиями столбцов или строк;
- выполнить команду меню «Вставка-Диаграмма...» (или щелкнуть курсором мыши значок «Мастер диаграмм» на панели инструментов), выбрать тип диаграммы, выбрать вид диаграммы и нажать кнопку «Готово»;
- в Office 2007 выбрать на панели инструментов вкладку «Вставка» и затем «Диаграммы».
При этом параметры диаграммы будут выбраны по умолчанию.
«Мастер диаграмм» программы Excel обычно умеет правильно распознать расположение данных (в столбцах или строках) по их заголовкам.
Если исходные данные введены в виде числовой таблицы, не содержащей названий столбцов или строк, то «Мастер диаграмм» не сможет выбрать правильный вариант («Ряды в столбцах» или «Ряды в строках»). В этом случае выбор нужно сделать пользователю, когда «Мастер диаграмм» предложит выбрать вариант расположения данных.
В общем случае «Мастер диаграмм» предлагает несколько шагов для построения диаграммы:
• сначала вызвать «Мастер диаграмм», выбрать тип и вид диаграммы;
• выбрать диапазон ячеек, содержащих данные, и указать вариант расположения рядов («Ряды в столбцах» или «Ряды в строках»);
• добавлять ряды, имена рядов, подписи оси X или корректировать имеющиеся ряды, удалять ряды;
• настраивать параметры диаграммы и т.д.;
• выбрать размещение диаграммы (на имеющемся или отдельном листе).
Рассмотрим основные типы диаграмм Excel, приводя соответствующие примеры их применения.
3.3. Тип диаграммы «Гистограмма»
Тип диаграммы «Гистограмма» имеет несколько видов, отличающихся по форме.
3.3.1. «Обычная гистограмма» (рис. 3.13) предназначена для отображения значений одного или нескольких рядов в виде прямоугольников, высота которых пропорциональна отображаемым значениям. При этом каждому ряду соответствуют прямоугольники одного цвета.
Рисунок 3.13. Окно «Мастера диаграмм» - Обычная гистограмма
Пример 3.1.
Построить диаграмму для отображения данных, представленных на рисунке 3.14.
Рисунок 3.14. Наблюдения за переменными x, y, z
Рассмотрим таблицу на рис. 2.1. Если показатели x, y, z являются независимыми, то подходит «Обычная гистограмма». Эта диаграмма позволит наглядно сравнить значения этих трех показателей. Каждому показателю будет соответствовать свой цвет прямоугольников.
Введите данные с рисунка 3.14 в диапазоне А1:С6. Выделите диапазон А1:С6, щелкните курсором мыши значок «Мастер диаграмм» на панели инструментов, и в появившемся окне выберите тип «Гистограмма», вид «Обычная гистограмма», как показано на рисунке 3.13, и нажмите кнопку «Готово» (или «OК»). Полученная диаграмма изображена на рисунке 3.15.
Рисунок 3.15. Обычная гистограмма
Обратите внимание на то, что на оси абсцисс отмечены значения 1, 2, 3, 4, 5, т.е. номера значений показателя. На диаграмме справа приведена легенда, которая показывает, какой цвет соответствует каждому показателю. Эта диаграмма позволяет сравнить соответствующие значения указанных трех показателей.
Если показатели являются зависимыми переменными, например, переменная x является аргументом функций y, z, то лучшим вариантом является диаграмма «Точечная», которая отмечает значения первого показателя на оси абсцисс. Этот тип диаграммы применяется для построения графиков функций, он будет рассмотрен далее.
Пример 3.2.
Рассмотрим данные, представленные на рисунке 3.12. В этой таблице приведены значения одного показателя (объем продаж). Очевидно, что значения даты, приведенные в первой строке таблицы, надо отметить на оси абсцисс. Здесь можно применить тот же тип диаграммы, что и в предыдущем случае.
Введите данные с рисунка 3.12 в диапазоне А1:Н2. Выделите диапазон А1:Н2, с помощью мастера диаграмм выберите тип «Обычная гистограмма», как показано на рисунке 3.13, и нажмите кнопку «Готово» (или «OК»).
На полученной диаграмме (рис.3.16) подведите курсор мыши к легенде, нажмите правую кнопку и выберите «Удалить». Получим диаграмму, представленную на рисунке 3.17.
Рисунок 3.16. Объем продаж по месяцам, млн.руб.
Рисунок 3.17. Объем продаж по месяцам, млн.руб.
Мы убрали легенду, которая нужна лишь в тех случаях, когда на диаграмме представлены несколько показателей.
Обратите внимание на то, что «Мастер диаграмм» сам «догадался», что надо разместить данные из первого ряда, имеющие формат даты, на оси X.
Пример 3.3.
Построить диаграмму для отображения данных, представленных на рисунке 3.18. Предполагается, что диаграмма будет использоваться для анализа выполнения плана продаж магазинами данного торгового предприятия.
Введите исходные данные в диапазоне А1:НЗ, как показано на рисунке. 3.18. Выделите диапазон А1:НЗ и с помощью «Мастера диаграмм» выберите обычную гистограмму (рис. 3.13) и нажмите кнопку «Готово» (или «OК»).
Рисунок 3.18. Фактический и плановый объемы продаж, млн.руб.
Щелкните на полученной диаграмме правой кнопкой мыши, выберите в контекстном меню «Параметры диаграммы», на вкладке «Легенда» выберите «Внизу». Получите диаграмму, изображенную на рисунке 3.19.
Рисунок 3.19. Фактический и плановый объемы продаж, млн.руб.
В первой строке выделенного диапазона содержатся текстовые данные, которые «Мастер диаграмм» понял как названия столбцов. При этом по умолчанию «Мастер диаграмм» принял расположение рядов «В строках».
Построим диаграмму еще раз, указав расположение рядов «В столбцах». Выделите диапазон А1:НЗ, с помощью «Мастера диаграмм» выберите тип «Обычная гистограмма» (рис. 3.13), нажмите кнопку «Далее», укажите расположение рядов «В столбцах», нажмите кнопку «Готово» (или «ОК»). Получим рисунок 3.20.
Рисунок 3.20. Фактический и плановый объемы продаж, млн.руб.
Какая из диаграмм, изображенных на рисунках 3.19 и 3.20, является лучшей? Ответ на этот вопрос зависит от цели, поставленной при анализе данных (рис.3.18).
Если требуется анализировать выполнение плана каждым магазином, то удобнее это сделать при помощи диаграммы 3.19,так как для каждого магазина показатель продаж и показатель плана расположены рядом. Поэтому легко сделать следующий вывод: Первый и второй магазины выполнили план продаж, а остальные магазины не выполнили план.
Диаграмма на рисунке 3.20 не удобна для анализа выполнения плана.
Из данного примера следует, что пользователь должен при построении диаграммы четко представлять себе цель диаграммы.
3.3.2. «Гистограмма с накоплением» (рис. 3.21) предназначена для отображения вклада каждого показателя в их сумму. Применяется для сравнения соответствующих значений нескольких показателей (не меньше двух рядов данных, не считая ряда, который будет использован для подписи оси X).
Рисунок 3.21. Гистограмма с накоплением
Пример 3.4.
Построить диаграмму для отображения показателей продаж двух магазинов (рис.3.22). Необходимо показать динамику общих продаж и вклад каждого магазина.
Рисунок 3.22. Динамика продаж двух магазинов, млн.руб.
Введите исходные данные в диапазоне А1:НЗ, как показано на рисунке 3.22. Выделите диапазон А1:НЗ и с помощью «Мастера диаграмм» выберите тип «Гистограмма с накоплением» (рис. 3.21) и нажмите кнопку «Готово» (или «OК»).
Щелкните на полученной диаграмме правой кнопкой мыши, выберите в контекстном меню «Параметры диаграммы», на вкладке «Легенда» выберите «Внизу».
Щелкните в области диаграммы правой кнопкой мыши, в контекстном меню выберите «Параметры диаграммы», перейдите на вкладку «Подписи данных», в разделе «Включить в подписи» отметьте «значения». Полученная диаграмма представлена на рисунке 3.23.
Рисунок 3.23. Динамика продаж двух магазинов, млн.руб.
Общий объем продаж двух магазинов был максимальным в апреле 2010 г.,максимум был достигнут за счет наибольшего вклада магазина № 2. У магазина № 1 максимум продаж был в феврале. В июле продажи обоих магазинов снизились.
3.3.3. «Нормированная гистограмма» (рис. 3.24.) предназначена для отображения доли каждого показателя в их сумме.
Рисунок 3.24. Нормированная гистограмма
Пример 3.5.
Построить диаграмму для отображения показателей продаж двух магазинов (рис.3.22). Необходимо показать динамику вклада каждого магазина в относительных величинах, то есть долях.
Выделите диапазон А1:НЗ и с помощью «Мастера диаграмм» выберите тип «Нормированная гистограмма» (рис. 3.24) и нажмите кнопку «Готово» (или «OК»). Щелкните на полученной диаграмме правой кнопкой мыши, выберите в контекстном меню «Параметры диаграммы», на вкладке «Легенда» выберите «Внизу». Щелкните в области диаграммы правой кнопкой мыши, в контекстном меню выберите «Параметры диаграммы», перейдите на вкладку «Подписи данных», в разделе «Включить в подписи» отметьте «значения». Полученная диаграмма представлена на рисунке 3.25.
Рисунок 3.25. Динамика вклада каждого магазина в общие продажи, млн.руб.
На диаграмме 3.25 видно, что доля магазина №1 в общих продажах была минимальной в апреле, а максимальной – в январе.
Было бы удобнее для анализа диаграммы на рисунке 3.25, если бы вместо значений можно было включить в подписи доли в процентах, однако в Excel эта опция не реализована.
3.4. Тип диаграммы «Линейчатая»
Тип диаграммы «Линейчатая» (рис. 3.26.) имеет шесть видов, которые отличаются от аналогичных видов типа «Гистограмма» только горизонтальным расположением столбиков Тип диаграммы «Линейчатая».
Рисунок 3.26. Линейчатая диаграмма
3.5. Тип диаграммы «График»
Тип диаграммы «График» (рис. 3.27) имеет семь видов, которые аналогичны соответствующим семи видам диаграмм типа «Гистограмма». Тип «График» предназначен для отображения изменения значений одного или нескольких показателей во времени.
Рисунок 3.27. Диаграмма «График»
Пример 3.6.
Построить графики изменения курса валют по отношению к рублю за неделю по данным рисунку 3.28.
Рисунок 3.28. Курсы валюты по отношению к рублю
Введите исходные данные в диапазоне А1:FЗ, как показано на рис. 3.28. Выделите диапазон А1: FЗ и с помощью «Мастера диаграмм» выберите тип «График», вид «График с маркерами» (рис. 3.27) и нажмите кнопку «Готово» (или «OК»).
Полученная диаграмма представлена на рисунке 3.29.
Рисунок 3.29. Недельная динамика курсов доллара и евро
по отношению к рублю
Проведем настройку параметров и форматирование диаграммы.
Подведите курсор мыши к оси Y и щелкните правой кнопкой, в появившемся окне выберите «Формат оси», перейдите на вкладку «Параметры оси» и введите минимальное значение 25, максимальное значение 50, цена основных делений 5, нажмите «ОК» (рис.3.30).
Рисунок 3.30. Настройка параметров оси
Щелкните на полученной диаграмме правой кнопкой мыши, выберите в контекстном меню «Параметры диаграммы», на вкладке «Легенда» выберите «Внизу».
Полученная диаграмма представлена на рисунке 3.31.
Рисунок 3.31. Недельная динамика курсов доллара и евро
по отношению к рублю
Подведите курсор мыши в область построения диаграммы, щелкните правой кнопкой, выберите «Формат области диаграммы», в появившемся окне выберите «Заливка – нет заливки» и «Границы – нет линий» (или «Рамка – невидимая») (рис.3.32-3.33).
Рисунок 3.32. Формат области диаграммы - заливка
Рисунок 3.33. Формат области диаграммы – границы
В результате получим следующую диаграмму (рис.3.34).
Рисунок 3.34. Недельная динамика курсов доллара и евро
по отношению к рублю
Пример 3.7.
Построить диаграмму для отображения данных (рис.3.35) о затратах на производство продукции предприятия за ряд лет. Необходимо показать динамику.
Рисунок 3.35. Затраты на производство продукции предприятия за 2005-2010 гг.
Введите исходные данные в диапазоне А1:G4, как показано на рисунке 3.35. Выделите диапазон А1: G4 и с помощью «Мастера диаграмм» выберите тип «График», вид «График с накоплением» (рис. 3.36) и нажмите кнопку «Готово» (или «OК»).
Рисунок.3.36. График с накоплением
Добавим к полученной диаграмме подписи данных. Подведите курсор мыши в область диаграммы, щелкните правой клавишей, выберите «Параметры диаграммы», перейти на вкладку «Подписи данных», отметить «значения». В Office 2007: в области диаграммы щелкните правой клавишей по каждой линии, выберете «Подписи данных». На рисунке 3.37 представлена полученная диаграмма.
На диаграмме 3.37 явно не указано, что это диаграмма с накоплением. Видно, что оплата труда изменяется о 4,2 млн.руб. в 2005 году до 5,3 млн.руб. в 2010 году. Аналогично можно сделать выводы о динамике остальных показателей.
Рисунок 3.37. Накопленные затраты на производство продукции предприятия за 2005-2010 гг.
3.6. Тип диаграммы «Круговая»
Тип диаграммы «Круговая» (рис. 3.38) соответствует структурным диаграммам статистики и предназначен для отображения вклада каждого показателя в их общую сумму. Каждому показателю соответствует сектор круга, площадь которого пропорциональна значению показателя. Около каждого сектора на диаграмму можно вывести значение показателя или его долю в процентах.
Рисунок 3.38. Круговая диаграмма
Пример 3.8.
Показать структуру себестоимости по данным о затратах на производство продукции предприятия (рис.3.39).
Рисунок 3.39. Затраты на производство продукции, млн.руб.
Введите исходные данные в диапазоне А1:В5, как показано на рис. 3.39. Выделите диапазон А1:В5 и с помощью «Мастера диаграмм» выберите тип «Круговая», вид «Круговая диаграмма» (рис. 3.39) и нажмите кнопку «Готово» (или «OК»).
Подведите курсор мыши в область диаграммы, щелкните правой клавишей, выберите «Подписи данных». Подведя курсор к подписям данных на диаграмме, щелкните правой клавишей, выберете «Формат подписей данных» и выберете «Доли», снимите флажок «Значения» (рис.3.40).
Рисунок 3.40. Параметры подписи данных
На рисунке 3.41 представлена полученная диаграмма.
Рисунок 3.41. Структура затрат на производство продукции
Диаграмма на рисунке 3.41 может быть проинтерпретирована следующим образом: наибольшую долю составляют затраты на материалы – 33,5%; наименьшая доля приходится на расходы на оплату труда – 12,64%.
Если среди значений показателей есть такие, доля которых мала по сравнению с остальными, то возможно применение другой круговой диаграммы.
Пример 3.9.
Охарактеризовать структуру затрат на производство продукции по данным, представленным на рисунке 3.42.
Рисунок 3.42. Затраты на производство продукции, млн.руб.
Значения некоторых затрат (транспорт, расходы на рекламу и прочие расходы) малы по сравнению с общей суммой затрат, поэтому обычная круговая диаграмма не будет наглядной. Можно использовать тип «Вторичная круговая».
Введите исходные данные в диапазоне А1:В7, как показано на рисунке 3.42. Выделите диапазон А1:В7 и с помощью «Мастера диаграмм» выберите тип «Круговая», вид «Вторичная круговая» (рис. 3.43) и нажмите кнопку «Готово» (или «OК»).
Рисунок 3.43. Вторичная круговая диаграмма
Подведите курсор мыши в область диаграммы, щелкните правой клавишей, выберите «Подписи данных». Подведя курсор к подписям данных на диаграмме, щелкните правой клавишей, выберете «Формат подписей данных» и выберете «Доли», снимите флажок «Значения».
Полученная диаграмма представлена на рисунке 3.44. Подведите курсор мыши в область любого сектора одного из кругов диаграммы, щелкните правой клавишей мыши, выберете пункт меню «Формат ряда данных».
Это окно позволит выбрать те показатели, которые на первой диаграмме будут объединены, а на вторичной – показаны пропорционально своим значениям.
В меню «Параметры ряда» в строке «Вторая область содержит значения» выберем не 2 (как было сделано по умолчанию), а 3 значения (рис.3.45).
Для большей информативности покажем проценты с сотыми долями. Для этого подведите курсор мыши в область подписи данных, щелкните правой клавишей, выберите «Формат подписей данных», перейдите на вкладку «Число», выберете формат «Процентный», в строке «Число десятичных знаков» введите значение 2 (рис.3.46).
Рисунок 3.44. Структура затрат на производство, %
Рисунок 3.45. Параметры ряда
Рисунок 3.46. Подписи данных
Полученная диаграмма представлена на рисунке 3.47.
Рисунок 3.47. Структура затрат на производство, %
3.7. Тип диаграммы «Точечная»
Тип диаграммы «Точечная» предназначен для построения графиков функций, заданных таблицей, в которой первый столбец (или строка) содержит значения независимой переменной, а второй столбец (или строка) содержит значения функции.
Если таблица содержит больше двух столбцов (или строк), то второй, третий и т.д. столбцы воспринимаются как значения функций, т.е. в таких случаях в одной системе координат будет построено несколько графиков функций.
Данный тип диаграммы имеет следующие пять видов.
- «Точечная диаграмма», которая позволяет сравнить пары значений (точки не соединены линиями).
- «Точечная диаграмма со значениями, соединенными сглаживающими линиями».
- «Точечная диаграмма со значениями, соединенными сглаживающими линиями без маркеров».
- «Точечная диаграмма, на которой значения соединены отрезками».
- «Точечная диаграмма, на которой значения соединены отрезками без маркеров».
Пример 3.10.
По данным рисунка 3.11 необходимо построить точечную диаграмму. Введите исходные данные в диапазоне А1:В11, как показано на рис. 3.42. Выделите диапазон А1:В11 и с помощью «Мастера диаграмм» выберите тип «Точечная», вид «Точечная с маркерами» (рис. 3.48) и нажмите кнопку «Готово» (или «OК»).
Рисунок 3.48.Точечная диаграмма
Полученная диаграмма представлена на рисунке 3.49.
Рисунок 3.49. Объем выручки в зависимости от затрат на рекламу, млн.руб.
3.8. Тип диаграммы «С областями»
Тип диаграммы «С областями» содержит шесть видов, которые аналогичны соответствующим видам диаграмм типов «Гистограмма», «Линейчатая» и «График». Диаграмма «С областями» практически совпадает с диаграммой «График», отличие от последней в том, что область между графиком и осью абсцисс закрашивается.
Применять диаграммы «С областями» можно в тех же задачах, что и диаграмму «График».
3.9. Тип диаграммы «Биржевая»
Тип диаграммы «Биржевая» предназначен для отображения биржевых показателей, относящихся к ценной бумаге.
Исходные данные для данного типа диаграммы должны по содержанию соответствовать следующим рядам:
• ряд «Подписи оси X», содержащий даты или названия ценных бумаг (акций и т.п.); если данный ряд не указан, то в качестве подписи оси X выводятся номера категорий 1, 2 и т.д.;
• ряд «Объем»;
• ряд «Цена открытия»;
• ряд «Максимальная цена»;
• ряд «Минимальная цена»;
• ряд «Цена закрытия».
Ряды могут располагаться в столбцах или строках.
Тип диаграммы «Биржевая» имеет четыре вида, которые отображают соответственно следующие наборы рядов (не считая ряда «Подписи оси X»):
1) «Максимальная цена», «Минимальная цена», «Цена закрытия»;
2) «Объем», «Максимальная цена», «Минимальная цена», «Цена закрытия»;
3) «Цена открытия», «Максимальная цена», «Минимальная цена», «Цена закрытия»;
4) «Объем», «Цена открытия», «Максимальная цена», «Минимальная цена», «Цена закрытия».
Пример 3.11.
Построить диаграмму для отображения динамики курса акций компании Х на основе данных рисунке 3.50.
Рисунок 3.50. Курс акций компании Х
Введите исходные данные в диапазоне А1:D6, как показано на рис. 3.50. Выделите диапазон А1:D6 и с помощью «Мастера диаграмм» выберите тип «Биржевая», вид «Биржевая» (рис. 3.51) и нажмите кнопку «Готово» (или «OК»).
Получим диаграмму на рисунке 3.52. В техническом анализе на фондовом рынке этот вид диаграммы называется «Бары».
Концы вертикальных отрезков на диаграмме показывают максимальную и минимальную цены, черточки – цену закрытия.
Рисунок 3.51. Биржевая диаграмма
Рисунок 3.52. Динамика курса акций компании Х
Пример 3.12.
Построить диаграмму для отображения динамики курса акций компании Х на основе данных рисунка 3.53.
Рисунок 3.53. Курс акций компании Х
Выберем второй вид диаграммы «Биржевая» (рис.3.54).
Рисунок 3.54. Биржевая диаграмма
Получим диаграмму на рисунке 3.55. В техническом анализе на фондовом рынке этот вид диаграммы называется «Японские свечи».
Рисунок 3.55. Динамика курса акций компании Х
При построении графика японских свечей используются все 4 основные значения цены: открытие, максимум, минимум и закрытие (open, high, low, close).
1. Широкая часть свечи (прямоугольник) называется телом (real body или джиттай). Тело - это ценовой диапазон между ценами открытия и закрытия. Заполненное тело свечи означает, что цена закрытия была меньше цены открытия периода, т.е. за период цены в целом упали (черная свеча - «ин-сен»). Пустое тело свечи означает, что цена закрытия была больше цены открытия, т.е. цены внутри диапазона росли (белая свеча - «йо-сен»).
2. Линия над телом свечи называется верхней тенью (upper shadow или uwakage), а ее верхняя точка указывает максимум, который достигали цены внутри периода.
3. Линия под телом свечи называется нижней тенью (lower shadow или shitakage) и ее нижняя точка - это минимум, который достигали цены внутри периода.
Пример 3.13.
Построить диаграмму для отображения динамики курса акций компании Х на основе данных рисунка 3.56.
Рисунок 3.56. Курс акций A, B, C, D и E
Выберем третий вид диаграммы «Биржевая» (рис.3.57).
Получим диаграмму на рис.3.58. Этот вид диаграммы описывает не только изменение курсов (как это делали «Бары»), нопозволяет кроме того рассмотреть динамику объемов продаж.
Рисунок 3.57. Биржевая диаграмма
Рисунок 3.58. Курсы акций A, B, C, D и E
Пример 3.14.
Построить диаграмму для отображения динамики курса акций компании Х на основе данных рисунка 3.59.
Рисунок 3.59. Курс акций A, B, C, D и E
Выберем четвертый вид диаграммы «Биржевая» (рис.3.60).
Рисунок 3.60.Биржевая диаграмма
Получим диаграмму на рис.3.61. Этот вид диаграммы описывает не только изменение курсов (как это делали «Японские свечи»), нопозволяет кроме того рассмотреть динамику объемов продаж.
Рисунок 3.61. Курсы акций A, B, C, D и E
