Список контрольных вопросов:

1. Выделение блока смежных ячеек .

2. Выделение блока несмежных ячеек .

3. Форматировать блок ячеек .

4. Удалить блок (изменение структуры таблицы) .

5. Вставить блок ячеек (строк, столбцов) .

6. Копировать блок ячеек .

7. Вставить блок ячеек (предварительно выполнена команда копирования или вырезания) .

8. Вставить блок ячеек с размножением (предварительно выполнена команда копирования или вырезания) .

9. Очистить блок .

10. Перенести выделенный блок с помощью мыши .

11. Скопировать выделенный блок с помощью мыши .

12. Заполнение блока значениями .

13. Заполнение блока ячеек при копировании с помощью мыши .

14. Заполнение блока ячеек значениями с помощью мыши .

15. Заполнение блока ячеек списком значений (ряды) с помощью мыши .

16. Заполнение блока ячеек форматами с помощью мыши .

17. Заполнение блока ячеек значениями согласно прогрессии .

18. Скрыть блок строк (столбцов) .

19. Показать блок скрытых строк (столбцов) .

20. Построение экстраполяционных (прогнозных) рядов (т.е. расчет будущих значений в свободных ячейках на основании уже введенных значений) .

21. Присвоение имени блоку ячеек.

22. Присвоение блоку ячеек имени, находящегося в столбце или строке.

- 45 -

ЛИТЕРАТУРА

1. Карлберг, Кондрад. Бизнес-анализ с помощью EXCEL.: Пер. с англ. К.: Диалектика, 1997.

2. Гарнаев А. Ю. Использование MS EXCEL и VBA в экономике и финансах.- СПб.: БХВ Санкт Петербург,1999.

3. Маркарьян Э.А., Герасименко Г.П. Финансовый анализ. М.: «ПРИОР»,1999.

4. Макарова Н.В. Практикум по технологии работы на компьютере. – 3-е изд., перераб. М.: Финансы и статистика, 2004.

5. Заботин Ю. Д., Шапошников А. С. Новейшая энциклопедия персонального компьютера 2005. М.: РИПОЛ классик, 2005.

ЛАБОРАТОРНАЯ РАБОТА № 3

ПОСТРОЕНИЕ, ФОРМАТИРОВАНИЕ

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

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

Теоретические сведения

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

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

Диаграмма состоит из элементов: линий, столбиков, секторов, точек и т.п. Каждому элементу диаграммы соответствует число в таблице. Числа и элементы диаграммы связаны между собой таким образом, что при изменении чисел автоматически изменяется изображение элементов диаграммы и наоборот.

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

Мастер диаграмм позволяет строить диаграммы 11 стандартных типов плоскостного и объемного представления (с областями, линейчатая, гистограмма, график, кольцевая, лепестковая, точечная, пузырьковая, поверхностная и др.). Некоторые типы диаграмм представлены на рис. 3.1.

Построение диаграмм осуществляется для выделенного блока ячеек – диапазона (области) данных для построения диаграммы.

Примечание. Блок ячеек может быть выделен как до начала построения, так и после.

Этап 1. Выбор типа и формата диаграммы. На этом этапе необходимо выбрать тип диаграммы (рис. 3.2) и задать (в окне справа) формат, который делает ее более выразительной. После выбора надо нажать кнопку ОК и перейти на следующий этап.

а

б

в

Рис. 3.1. Начало (продолжение см. с.49).

г

Рис. 3.1. Примеры различных типов диаграмм Excel XP: а – объемный гистограмма с группировкой; б – заполненная лепестковая диаграмма; в – линейчатая диаграмма с группировкой; г – с областями.

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

Рис.3.2. Вставка диаграмм.

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

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

	Средний балл
	Информатика	Высшая математика
Группа 101	3,5	3,2
Группа 102	4,6	3,9
Группа 103	3,9	4,3
Группа 104	4,2	3,5
Факультет	4,2	3,9

Рис.3.3. Блок смежных ячеек с исходными данными для построения диаграммы «Сведения об успеваемости».

Excel XP позволяет также строить диаграмму на основании данных, распределенных по листам одной и той же или разных рабочих книг. Блоку ячеек можно заранее присвоить имя (выделить блок ячеек, вызвать контекстное меню, выбрать Имя диапазона, присвоить имя).

Этап 3. Задание параметров диаграммы. Задание параметров диаграммы осуществляется в трех дополнительных вкладках, которые появляются при выделении диаграммы (рис. 3.4.).

Рис. 3.4. Дополнительные вкладки для форматирования диаграмм

(этап 3).

На вкладке Макет возможны следующие действия (рис. 3.5):

Рис. 3.5.

В группе Подписи вводятся поочередно на соответствующую строку название диаграммы, название оси X, название оси У, название оси Z; Легенда (место расположения легенды), Подписи данных (здесь устанавливается переключатель отображения значений данных на диаграмме), Таблица данных (здесь устанавливается переключатель отображения на диаграмме таблицы исходных данных).

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

Этап 4. Размещение диаграммы. Созданную диаграмму можно разместить на том же листе, где находится таблица с исходными данными, либо на отдельном листе. Для этого, во вкладке Конструктор, в группе Расположение нажмите кнопку Переместить диаграмму. Будут предложены 2 варианта : на отдельном листе или на имеющемся листе. Выберите нужный и нажмите кнопку OK.(рис. 3.6).

Рис. 3.6. Диалоговое окно для выбора места размещения

диаграммы (этап 4).

Редактирование диаграмм

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

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

• изменить тип и формат диаграммы;

• изменить исходные данные;

• переопределить исходный интервал ячеек, на основании которых построена диаграмма;

• переопределить ориентацию рядов и их название;

• изменить данные, используемые для подписей оси;

• изменить параметры диаграммы (заголовки, оси, линии сетки, легенду, подписи данных);

• изменить размещение диаграммы.

Рис. 3.7. Контекстное меню для работы с диаграммами.

Форматирование типов диаграмм

Изменение типа диаграммы можно выполнить для любой построенной диаграммы, как внедренной, так и отдельного диаграммного листа. Подобные изменения могут быть осуществлены либо посредством кнопки Изменить тип диаграммы в дополнительно вкладке Конструктор, либо за счет нажатия кнопки Изменить тип диаграммы… в контекстном меню (см. рис. 3.7). Кроме того, контекстное меню дает также возможность корректировать вид трехмерных диаграмм. Для изменения формата трехмерной проекции необходимо выполнить команду Поворот объемной фигуры контекстного меню (см. рис. 3.7). В результате перед пользователем предстает диалоговое окно <Формат области диаграммы> (рис. 3.8), которое позволяет изменить угол поворота рядов вокруг своей оси, уменьшить или увеличить масштаб, изменить угол перспективы и возвышения всей диаграммы, представить проекцию в изометрии.

Рис. 3.8. Диалоговое окно «Формат области диаграммы».

Форматирование элементов диаграммы

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

Существует два пути форматирования компонентов диаграммы:

1. Указать курсором мыши на любой компонент диаграммы (ряд, ось, подписи, легенду и т. д.), щелкнуть ее правой кнопкой и в появившемся контекстном меню выполнить команду Формат ряда данных...(...= конкретный компонент).

2. С помощью дополнительной вкладки Формат.(рис. 3.5)

Форматирование области диаграммы и области

построения диаграммы

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

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

Форматирование рядов данных

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

При выделении отдельного ряда в поле имен появляется стандартное обозначение Р(номер) – номер ряда данных, в строке формул выводится формула ряда, например:

=РЯД(Лист1 !$С$1 ;Лист1 !$А$2:$А$6;Лист1 !$С$2:$С$6;2),

где Лист1!$С$1 – абсолютная ссылка на ячейку, содержащую название ряда, которое используется при построении легенды;

Лист! !$А$2:$А$6 – блок ячеек, содержащий метки (категории) значений оси X;

Лист1!$С$2:$С$6 –блок ячеек, содержащий элементы значений ряда;

2– порядковый номер ряда.

Выделенный ряд можно удалить.

Форматирование рядов диаграмм осуществляется при помощи диалогового окна «Формат ряда данных», пример которого представлен на рис. 3.9.

Рис. 3.9. Диалоговое окно для форматирования рядов

данных диаграмм.

Существует некоторое отличие в составе вкладок диалогового окна «Формат ряда данных», обусловленное типом диаграммы, который воспроизводят данные ряды, а именно плоскими и объемными диаграммами. Так вкладки Параметры ряда, Заливка, Цвет границы, Стиль границ, Тень, Формат объемной фигуры, являются общими для двухмерных и объемных (трехмерных) диаграмм. При этом для трехмерных – добавляется вкладка Фигура.

Вкладка Параметры ряда позволяет задать важнейшие характеристики диаграмм (рис. 3.10, 3.11). Состав настраиваемых параметров зависит от типа диаграммы:

Параметр Перекрытие (см. рис. 310) определяет процент наложения изображений маркеров элементов данных рядов друг на друга, указывается для групп плоских линейчатых диаграмм и гистограмм:

0 – маркеры различных рядов находятся рядом;

-100 – маркеры рядов отстоят друг от друга на максимальном расстоянии;

100 – маркеры рядов сливаются вместе.

Перекрытие +50 % Перекрытие 0%

Перекрытие -50%

Рис. 3.10. Настройка параметра Перекрытие.

Параметр Боковой зазор (рис. 3.11) устанавливает величину интервала между кластерами (группой элементов данных различных рядов, отнесенной к одной и той же категории оси X), изменяется в пределах от 0 до 500. Доступна для групп линейчатых диаграмм, графиков, гистограмм, объемных линейчатых диаграмм и объемных гистограмм.

Ширина зазора 0 % Ширина зазора 150 %

Ширина зазора 500 %

Рис. 3.11. Настройка параметра Боковой зазор.

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

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

Параметр Линии проекции обеспечивает установку перпендикуляров, опущенных от каждого маркера на ось X. Доступна для групп диаграмм с областями и графиков.

В группе Оси задается связь ряда данных с основной или вспомогательной осью Y диаграммы. Элементы ряда имеют привязку к двум осям:

X – порядковый номер элемента или определенная категория;

Y – значение элемента в соответствии с его единицей измерения.

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

На вкладке Макет, группа Анализ, Планка погрешностей – здесь определяется тип планок погрешностей для элементов ряда согласно его статистическим характеристикам.

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

Форматирование осей диаграммы

Оси диаграммы предназначены для обрамления области построения диаграммы, нанесения разметки (шкал), которым соответствуют основные значения элементов данных и категории.

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

Объемная диаграмма стоит на основании и окружена стенками, которые можно самостоятельно форматировать.

При форматировании осей активизируется:

вкладка Заливка – выбирается внешнее оформление оси (линии), указывается наличие и расположение основных и вспомогательных засечек и их меток;

вкладка Параметры оси – задается масштаб значений элементов данных ряда на оси и разметка оси (расстояние между засечками и линиями сетки), значение точки пересечения с осью X (для плоских) или с основанием (для объемных) диаграмм; выбирается при необходимости логарифмическая шкала и обратный порядок значений (для оси категорий – изменение порядка следования меток слева направо, для оси значений – изменение значения от меньшего к большему);

вкладка Число – задается формат меток засечек или самих числовых значений на оси;

вкладка Выравнивание – изменяется ориентация текста меток засечек.

И др.

Форматирование сетки

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

Форматирование легенды

Легенда – специальное окно, содержащее для каждого ряда данных ключ и поле-название ряда. Ключ легенды повторяет цвет и узор, заданный для элементов данных ряда. Легенда может создаваться в автоматическом режиме, если при построении диаграммы интервал ячеек включал названия рядов. Если имена рядов явно не заданы, то легенда использует стандартное имя Ряд (номер).

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

Тренды

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

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

• тренд в анализируемый период должен развиваться эволюционно;

• процесс, представленный диаграммой, должен обладать определенной инерционностью.

• Тренд можно строить для диаграмм типа:

• линейчатый график;

• гистограмма;

• диаграмма с областями;

• XY-точечная диаграмма.

• Построение тренда осуществляется по следующей технологии:

• построить диаграмму для одного ряда данных;

• выделить эту диаграмму, щелкнув по ней левой кнопкой мыши. На диаграмме должны появиться маркеры;

• вызвать контекстное меню и выполнить команду Добавить линию тренда;

• в диалоговом окне «Формат линии тренда» на вкладке Тип (см. рис. 3.13) выбрать тип тренда, а на вкладке Параметры (см. рис. 3.12) установить параметры: Количество периодов прогноза, Показывать уравнение на диаграмме,

Поместить на диаграмму величину достоверности аппроксимации;

• нажать кнопку Закрыть.

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

Рис. 3.12 . Формат линии тренда.

Рис. 3.13.