ПОСТРОЕНИЕ И РЕДАКТИРОВАНИЕ ДИАГРАММ

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

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

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

Отчет сводной диаграммыпредставляет собой интерактивную сводку данных в формате диаграммы. Его создание отличается от обычных диаграмм Excel 2007. После создания отчета сводной диаграммы можно просматривать разные уровни детализации (подробностей) и изменять макет диаграммы, перетаскивая её поля и элементы.

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

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

Диаграмма состоит обычно из следующих элементов: координатные оси Xи Y (осей Y может быть две), ряды данных, заголовок диаграммы, заголовки осей, легенда, подписи данных, линии сетки. С помощью контекстно-зависимого меню можно редактировать элементы диаграммы (изменять размеры диаграммы; устанавливать толщину, цвет, размер и шкалу масштабирования осей; изменять шрифтовые настройки текстовых элементов; изменять данные на диаграмме, изменяя данные в таблице, и пр.). Для выполнения этих операций диаграмма или ее элемент выделяется двойным щелчком мыши и заключается в рамку с черными квадратиками маркеров, за которые можно передвигать рамку элемента, изменяя тем самым его размер.

 

Задача 1.

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

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

1. Таблицу заполните следующим образом:

· в строку Реализовано значения вводятся в соответствии с таблицей;

· строки Доход, Себестоимость и Прибыль заполняются в соответствии с нижеприведенными формулами:

Доход = Цена_товара*Реализовано

Себестоимость=Постоянные_издержки+Переменные_издержки*Реализовано

Прибыль = Доход–Себестоимость

2. Рабочему листу с таблицей дайте имя Пример.

3. Сохраните созданную таблицу в Моих документах под именем Dohod.xlsx.

4. Построение диаграммы:

· для удобства построения установите масштаб просмотра 75%;

· выделите диапазон ячеек A7:D8 (по данным, содержащимся в этих ячейках, должна быть построена диаграмма);

· щелкните по вкладке Вставка/Диаграммы/ ;

· в проявившемся окне щелкните значок Объемнаякруговая;

· автоматически на рабочем листе появляется круговая диаграмма.

Для добавления поля Название диаграммы выделите созданную диаграмму и перейдите на вкладку Макет/Подписи/ .В списке команд выберите: Название по центру с перекрытием илиНад диаграммой. Задание основных характеристик области названия диаграммы происходит с помощью вкладки Макет/Подписи/ /Дополнительные параметры заголовков.

Для добавления легенды в диаграмму перейдите к вкладке Макет/Подписи/ .В списке команда выберите расположение легенды в области диаграммы – сверху, справа, слева и т.д. Для области легенды можно менять все основные характеристики – цвет заливки, границы, объем, тень и т.д. В этом случае перейдите на вкладку Макет/Подписи/ /Дополнительные параметры легенды.В окне Формат легенды на вкладкахПараметры легенды, Заливка, Цвет границы, Стили граници Тень задайте все основные характеристики области легенды.

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

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

После выполнения всех операций появится диаграмма, показанная на рисунке.

5. Сохраните таблицу с диаграммой.

 

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

Задача 2.

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

1. Построение диаграммы:

· для удобства построения установите масштаб просмотра 75%.

· щелкните по вкладке Вставка/Диаграммы/ ;

· в проявившемся окне щелкните значок График с маркерами;

· на вкладке Конструктор в группе Данные щелкните по кнопке ;

· в проявившемся окне Выбор источника данных в строке Диапазон данных для диаграммы укажите вручную или мышкой диапазон ячеек =A7:D8;

· щелкните по кнопке <Ок>, после чего в выделенной области появится диаграмма.

2. Сохраните таблицу с диаграммами.

 

Задача 3.

Построить диаграмму, отражающую объем реализации по месяцам на отдельном листе, тип диаграммы – гистограмма.

1. Построение диаграммы:

· выделите диапазон ячеек A7:D8 (по данным, содержащимся в этих ячейках, должна быть построена диаграмма);

· нажмите клавишу F11, в результате диаграмма будет помещена на отдельном листе – листе диаграмм. Имя листа по умолчанию Диаграмма1, каждый последующий лист диаграмм будет содержать слово Диаграмма со следующим порядковым номером. Переименуйте этот лист, дав ему имя Гистограмма.

2. Сохраните таблицу с диаграммами.

 

Настройка параметров для предварительно выделенной диаграммы выполняется с помощью вкладок Конструктор(изменение вида диаграммы), Макет (изменение объектов диаграммы), Формат (изменение оформления элементов диаграммы).

Задача 4.

Для диаграммы на листе Гистограмма внести следующие изменения:

§ изменить тип диаграммы на линейчатую,

§ изменить названия диаграммы Объем реализации за первый квартал,

§ добавить подписи к осям Y (тыс. руб.)и X (месяцы квартала),

§ добавить подписи на диаграмму к столбцу данных за Февраль,

§ отформатировать текст диаграммы -название диаграммы оформить шрифтом вида TimesNewRoman, стиль – жирный, размер 14 пунктов, текст обвести рамкой;

§ провести форматирование оси Y: установить минимальное значение – 100, максимальное значение – 500, цена основных делений – 100, промежуточных – 20;

§ легенду разместить в правом верхнем углу области диаграммы, заключить в рамку с тенью;

§ добавить на диаграмму полнуюсетку из пунктирных линий.

§ столбцы диаграммы оформить различной текстурой.

1. Измените тип диаграммы:

· активизируйте диаграмму щелчком мышкой внутри внедренной диаграммы, чтобы по ее контуру появились черные квадратики, выберите вкладку Конструктор/Тип/ .В окне Изменение типа диаграммы в левой части выберите тип диаграммы (Линейчатая), а в правой уже саму диаграмму для замены.

· для того чтобы задать пропорциональный размер диаграммы перейдите к вкладке Формат/Размер/ . В окне Размер и свойства на вкладке Размер активируйте пунктСохранять пропорции и задайте нужны значения высоты и ширины в сантиметрах (в группе Размер и поворот) или же в процентах (в группе Масштаб).

2. Измените названия диаграммы:

· название диаграммы представляет собой текстовое поле. Выделите название диаграммы, после щелчка мышью вокруг текста появится контурная линия с маркерами;

· повторите щелчок еще раз, контурная линия исчезнет, появится мигающий курсор ввода текста;

· удалив старый текст Реализовано, введите новыйтекст Объем реализации за первый квартал;

· после внесения изменений либо нажмите клавишуESC, либо щелкните мышью вне поля названия;

3. Добавьте названия к осям диаграммы Y - тыс.руб., Х – Месяцы квартала;

· оси диаграммы – это две (X и Y), а иногда и три (дополнительно Z) прямые линии под прямым углом друг к другу, относительно меток которых и строится диаграмма;

· выберите вкладку Макет/Подписи/ /Название основной горизонтальной оси/Название под осью;

· выделите название горизонтальной оси диаграммы, после щелчка мышью вокруг текста появится контурная линия с маркерами;

· повторите щелчок еще раз, контурная линия исчезнет, появится мигающий курсор ввода текста;

· введите текст Месяцы квартала;

· нажмите клавишуESC, либо щелкните мышью вне поля названия;

· для задания основных параметров шкалы горизонтальной оси диаграммы перейдите к вкладке Макет/Оси/ /Основная горизонтальная ось/Дополнительные параметры основной горизонтальной оси.

· аналогично добавьте название к оси Y.

4. Добавьте подпись Февраль к столбцу диаграммы с данными за февраль:

· щелкните на одном из столбцов диаграммы, при этом на каждом из столбцов появиться квадратик – признак выделения всех столбцов;

· щелкните на столбце Февраль, он выделится маркерами;

· выберите вкладку Макет/Подписи/ /Дополнительные параметры подписей данных;

· в окне Формат подписей данных на вкладке Параметры подписи установите опцию Значение;

· щелкните по клавише<Закрыть> или нажмите клавишу ENTER;

· снимите выделение, нажав клавишуESC, либо щелкнув мышью вне столбца диаграммы Февраль.

5. Оформите текст названия диаграммы:

· на панели инструментов форматирования, которая появляется при щелчке правой кнопкой мыши на названии диаграммы, установите нужные значения шрифтового оформления;

· сделайте двойной щелчок мышью на названии диаграммы или выделите название диаграммы и выполните команду Формат заголовка диаграммы из контекстного меню;

· на вкладке Стили границ установите стиль, цвет и толщину рамки самостоятельно; вкладка Заливка позволяет задать цвет фона, на котором будет выводиться название диаграммы.

6. Проведите форматирование оси Y:

· выделите ось Y щелчком мышью;

· в контекстном меню выберите команду Формат оси;

· на вкладке Параметры оси установите Подписи оси – внизу, основные – наружу, промежуточные – внутрь; минимальное значение – 100, максимальное значение – 500, цена основных делений – 100, промежуточных – 20, цена деления – Сотни, флажокОтображать на диаграмме должен быть включен, <Закрыть>;

· проанализируйте изменения, произошедшие на диаграмме.

7. Измените местоположение легенды, заключите ее в рамку:

· выделите легенду и выполните команду Формат легендыиз контекстного меню;

· на вкладке Параметры легенды установите режим - сверху;

· на вкладках Цвет границы, Стили границ и Теньустановите параметры по своему усмотрению, <Закрыть>;

· снимите выделение, нажав клавишуESC, либо щелкнув мышью вне легенды.

8. Добавьте на диаграмму полную сетку из пунктирных линий:

Примечание. По умолчанию мастер диаграмм размещает на диаграмме сетку из горизонтальных линий.

 

· двойным щелчком активизируйте область диаграммы;

· перейдите на вкладку Макет/Сетка/ /Горизонтальные линии сетки по основной оси/Дополнительные параметры линий сетки на основной горизонтальной оси;

· на вкладке Тип линии укажите необходимые тип, цвет и толщину линий;

· аналогично задайте настройки для вертикальных линий сетки.

9. Наложите различную текстуру на столбцы диаграммы:

· щелкните на одном из столбцов диаграммы, при этом на каждом из столбцов появиться квадратик – признак выделения всех столбцов;

· щелкните на столбце Январь и откройте диалог Формат точки данных;

· на вкладке Заливка установите флажок Рисунок или текстура и щелкните по кнопке , выберите тип текстуры, <Закрыть>;

· оцените результат;

· аналогично оформите столбцы Февраль и Март.

10. Оцените результаты редактирования диаграммы. При необходимости внесите дополнительные изменения на диаграмму. Сохраните таблицу с диаграммами.

 

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

 

Задача 5.

По данным таблицы файла Dohod.xlsx построить гистограмму, отражающую результаты работы предприятия (доход, себестоимость, прибыль) за три месяца (январь, февраль, март). Построить для ряда данных Доход линейный тренд с прогнозом на следующий месяц и для ряда Прибыль – полиномиальный тренд с отображением уравнений соответствующих функций.

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

2. По данным таблицы постройте гистограмму:

· выделите блок ячеек A9:D11;

· щелкните по вкладке Вставка/Диаграммы/ ;

· в проявившемся окне щелкните значок Гистограмма с группировкой;

· самостоятельно задайте заголовок диаграммы Результаты деятельности предприятия и осей X–Период, Y–Сумма (руб.);

· самостоятельно настройте легенду и линии сетки, задайте отображение таблицы данных на диаграмме (вкладка Макет/ Подписи/ ; ; );

· поместите диаграмму на отдельном листе, переименуйте лист, задайте ему имя Тренд;

3. Проанализируйте диаграмму, обратите внимание на то, что по оси Х нет подписей, соответствующих названиям месяцев.

4. Настройте подписи по оси Х:

· выделите область диаграммы;

· перейдите на вкладку Конструктор/Данные/ , в области Подписи горизонтальной осив текстовом поле щелкните на кнопке , перейдите на лист с таблицей Пример и выделите блок ячеек B7:D7 (ячейки с текстом Январь, Февраль, Март). В поле Подписи горизонтальной оси отобразилась ссылка на диапазон

=Пример!$В$7:$D$7;

· <ОК>;

5. Постройте линейный тренд для ряда гистограммыДоход:

· щелкните на диаграмме на одном из столбцов ряда Доход, при этом на каждом из столбцов этого ряда появиться маркер;

· в контекстном меню выделенной гистограммы выберите командуДобавить линию тренда…;

· в диалоговом окне Параметры линии тренда выберите значок Линейная;

· установите Прогноз – вперед на 1 период (прогноз на следующий месяц), установите флажокПоказывать уравнение на диаграмме, на вкладке Тип линии определите тип линии тренда; <Закрыть>.

6. Самостоятельно постройте полиномиальный тренд для ряда Прибыль.

7. Проанализируйте диаграмму.

8. Отформатируйте текстовые и графические элементы диаграммы. Добейтесь максимального сходства с рисунком.

9. Сохраните таблицу с диаграммами. Сдайте работу преподавателю.