Цель работы: получение навыков представления данных в виде различных диаграмм.
Краткая теория
Мастер Диаграмм осуществляет построение новой диаграммы в интерактивном режиме за четыре шага только для выделенного блока ячеек – диапазона (области) данных построения диаграммы.
Диаграммы – это графическое представление данных. Они используются для анализа, сравнения данных, представления их в наглядном виде. Пример диаграммы представлен на рис. 9.
Рисунок. 9. Диаграмма.
Диаграмма состоит из элементов: линий, столбиков, секторов, точек и т.п. Каждому элементу диаграммы соответствует число в таблице. Числа и элементы диаграммы связаны между собой таким образом, что при изменении чисел автоматически изменяется изображение элементов диаграммы и наоборот.
Различают два вида диаграмм:
- Внедренные диаграммы (сохраняются на рабочем листе вместе с данными);
- Диаграммные листы (диаграмма в формате полного экрана на новом листе).
Диаграмма создается с помощью Мастера диаграмм, вызываемого командой Вставка, Диаграмма или кнопкой на панели Стандартная, либо кнопкой на панели Диаграмма.
Мастер диаграмм позволяет строить диаграммы 14 стандартных типов плоскостного и объемного представления (с областями, линейчатая, гистограмма, график, кольцевая, лепестковая, точечная, пузырьковая, поверхностная и др.) и 22 нестандартных типа.
Этап 1. Выбор типа и формата диаграммы. На этом этапе необходимо выбрать тип диаграммы и задать (в окне справа) формат, который делает ее более выразительной. После выбора надо нажать кнопку <Далее> и перейти на следующий этап.
Этап 2. Выбор и указание диапазона данных для построения диаграммы. На этом этапе задается диапазон данных, для которого будет построена диаграмма. Для этого в таблице с помощью переключателя укажите расположение данных – по строкам или по столбцам будет строиться выбранный тип диаграммы. Далее с помощью мыши выделите необходимый блок ячеек, адрес которого автоматически отобразился в строке Диапазон. В окне образца будет отображаться выбранный тип диаграммы для заданного диапазона данных.
Блок ячеек может включать как сами данные, так и их названия, которые используются для обозначения меток по оси Х и в легендах (расшифровка условных обозначений на диаграммах). Например, в таблице 4 показан блок исходных данных, состоящий из чисел и названий (номеров групп). Блок ячеек может содержать несмежные ячейки одного рабочего листа. В этом случае выделенные блоки должны иметь одинаковую конфигурацию.
Этап 3.Задание параметров диаграммы. Задание параметров диаграммы осуществляется в окнах вкладок Мастера диаграмм.
На вкладе Заголовки вводятся поочередно на соответствующую строку название диаграммы, название оси Х, название оси У, название оси Z.
На вкладе Оси устанавливаются переключатели выбора вида обозначения меток осей, а также указывается место расположения легенды.
На вкладе Линии сетки устанавливаются переключатели отображения сетки на диаграмме.
На вкладе Таблица данных устанавливается переключатель отображения на диаграмме таблицы исходных данных.
На вкладе Подписи данных устанавливается переключатель отображения значений данных на диаграмме.
Этап 4. Размещение диаграммы. Созданную диаграмму можно разместить на том листе, где находится таблица с исходными данными либо на отдельном листе. В диалоговом окне Мастер диаграмм на этапе 4 для этого надо установить соответствующий переключатель и нажать кнопку <Готово >.
Редактирование диаграмм выполняется как с помощью контекстного меню, так и с помощью команд управляющего меню Диаграмма. Вызов контекстного меню осуществляется путем установки указателя мыши в пустое место диаграммы (т.е. в один из четырех ее углов) и нажатия правой клавиши мыши. Пункты меню 1-5 предоставляют пользователю возможность вернуться к любому из четырех этапов создания диаграммы и осуществить необходимые изменения, т.е.:
■изменить тип и формат диаграммы;
■изменить исходные данные:
■переопределить исходный интервал ячеек, на основании которых построена диаграмма;
■переопределить, ориентацию рядов и их название;
■изменить данные, используемые для подписей оси Х;
■изменить параметры диаграммы (заголовки, оси, линии сетки, легенду, подписи данных);
■изменить размещение диаграммы.
Excel обеспечивает удобный режим работы для «подбора» наиболее подходящего типа диаграммы в целом, для группы рядов или отдельного ряда данных. Изменение типа диаграммы можно выполнить для любой построенной диаграммы, как внедренной, так и отдельного диаграммного листа. Подобные изменения могут быть осуществлены либо посредством кнопки <<Тип диаграммы>> на панели инструментов Диаграмма, либо за счет перехода к первому этапу построения диаграмм и выбора команды Тип диаграммы в контекстном меню. Кроме того, контекстное меню дает также возможность корректировать вид трехмерных диаграмм. Для изменения формата трехмерной проекции необходимо выполнить команду Объемный вид контекстного меню. В результате перед пользователем предстает диалоговое окно <Формат трехмерной проекции >, которое позволяет изменить угол поворота рядов вокруг своей оси, уменьшить или увеличить масштаб, изменить угол перспективы и возвышения всей диаграммы, представить проекцию в изометрии.
После активизации диаграммы можно выделить любой из ее элементов для корректировки, которая заключается в удалении либо в изменении свойств компонента путем форматирования.
Существует два пути форматирования компонентов диаграммы:
· указать курсором мыши на любой компонент диаграммы (ряд, ось, подписи, легенду...), щелкнуть ее правой кнопкой и в появившемся контекстном меню выполнить команду Формат...(...= конкретный компонент);
· в окне <<Элементы диаграммы>> панели инструментов Диаграмма выбрать необходимый компонент и нажать соседний с окном значок Формат.
Диаграмма окружена областью. Форматирование обеспечивает изменение вида области (цвета фона, выбор узора, использование рамок вокруг области форматирования) и шрифта (типа, стиля и размера для размещаемых в области форматирования символов текста).
Диаграмма любого типа расположена в области построения. Выделенную область можно удалить (ряды данных располагаются в области диаграмм, а область построения является их окружением). При форматировании области построения изменению так же, как и для области диаграммы, подлежит лишь ее вид.
Диаграмма может содержать несколько рядов данных – групп элементов данных, соответствующих одному блоку ячеек рабочего листа, не обязательно смежных. Каждый ряд на диаграмме выделяется цветом или узором.
При выделении отдельного ряда в поле имен появляется стандартное обозначение Р (номер) — номер ряда данных, в строке формул выводится формула ряда, например:
=РЯД(Лист1!$С$1;Лист1!$А$2:$А$6;Лист1!$С$2:$С$6;2), где
- Лист1!$С$1 — абсолютная ссылка на ячейку, содержащую название ряда, которое используется при построении легенды;
- Лист1!$А$2:$А$6 —блок ячеек, содержащий метки (категории) значений оси Х;
- Лист1!$С$2:$С$6 — блок ячеек, содержащий элементы значений ряда;
- 2— порядковый номер ряда.
Выделенный ряд можно удалить.
Форматирование рядов диаграмм осуществляется при помощи диалогового окна <<Формат ряда данных>>.
Существует некоторое отличие в составе вкладок диалогового окна <<Формат данных>>, обусловленное типом диаграммы, который воспроизводят данные ряды, а именно плоскими и объемными диаграммами. Так вкладки Вид, Подписи данных, порядок рядов, Параметры являются общими для двухмерных и объемных (трехмерных) диаграмм. При этом для двухмерных диаграмм добавляются вкладки Ось и Y-погрешности, а для трехмерных — добавляется вкладка Фигура.
На вкладке Вид задаются установки внешнего вида элементов ряда.
На вкладке Подписи данных выполняется настройка типа подписей (отсутствие, значения, категория и т.п.) для элементов ряда.
Вкладка Порядок рядов позволяет задать требуемый порядок следования рядов, осуществить их перестановку на диаграмме, не изменяя физического местоположения в таблице; в списке рядов выделяется имя перемещаемого ряда; с помощью кнопок <Вверх>, <Вниз> изменяется его порядок следования в диаграмме.
Вкладка Параметры позволяет задать важнейшие характеристики диаграмм. Состав настраиваемых параметров зависит от типа диаграмм.
Параметр Перекрытие определяет процент наложения изображений маркеров элементов данных рядов друг на друга, указывается для групп плоских линейчатых диаграмм и гистограмм:
· 0 – маркеры различных рядов находятся рядом;
· -100 – маркеры рядов отстоят друг от друга на максимальном расстоянии;
· 100 – маркеры рядов сливаются вместе.
Параметр Ширина зазора устанавливает величину интервала между кластерами (группой элементов данных различных рядов, отнесенной к одной и той же категории оси X), изменяется в пределах от 0 до 500. Доступна для линейчатых диаграмм, графиков, гистограмм, объемных линейчатых диаграмм и объемных гистограмм.
Параметр Соединять значения ряда обеспечивает соединение линией вершин маркеров данных одного ряда. Применима только к наложенным линейчатым диаграммам и наложенным гистограммам.
Параметр Разноцветные точки элементов данных задается только для диаграмм, содержащих один ряд данных. Доступна для групп линейчатых диаграмм, гистограмм, графиков, круговых и кольцевых диаграмм, радаров, XY -точечных диаграмм, объемных линейчатых диаграмм, объемных гистограмм и объемных круговых диаграмм.
Параметр Линии проекции обеспечивает установку перпендикуляров, опущенных от каждого маркера на ось Х. Доступна для групп диаграмм с областями и графиков.
На вкладке Ось задается связь ряда данных с основной или вспомогательной осью Y диаграммы. Элементы ряда имеют привязку к двум осям:
X – порядковый номер элемента или определенная категория;
Y – значение элемента в соответствии с его единицей измерения.
Различные ряды диаграммы могут представлять данные, несопоставимые по масштабу. Поэтому при построении диаграмм можно привязать определенный ряд к основной или вспомогательной оси Y, причем к одной оси Y может быть сразу отнесено несколько рядов. Для каждой оси Y выполняется независимая от другой оси Y настройка.
На вкладке Y-погрешности определяется тип планок погрешностей для элементов ряда согласно его статистическим характеристикам.
На вкладке Фигура в качестве визуального представления ряда данных можно выбрать одну из шести трехмерных фигур: параллелепипед, конус, цилиндр, пирамиду, усеченный конус или усеченную пирамиду.
Оси диаграммы предназначены для обрамления области построения диаграммы, нанесения разметки (шкал), которым соответствуют основные значения элементов данных и категории.
Для большинства плоских диаграмм используются первичные (основные) и вспомогательные оси, что обусловлено типом диаграммы, величиной (порядком величин) значений элементов данных рядов. В частности, круговые и кольцевые диаграммы не имеют осей, радиалъные диаграммы имеют отдельные оси для каждого ряда дранных. Ориентация ряда относительно оси Y выполняется при форматировании рядов, по умолчанию все ряды отнесены к основной оси. Ось Х является общей для всех рядов, независимо от их ориентации относительно оси Y.
Объемная диаграмма стоит на основании и окружена стенками, которые можно самостоятельно форматировать.
При форматировании осей активизируется:
вкладка Вид — выбирается внешнее оформление оси (линий), указывается наличие и расположение основных и вспомогательных засечек и их меток;
вкладка Шрифт — выбирается вид и размер шрифта меток засечек;
вкладка Шкала — задается масштаб значений элементов данных ряда на оси и разметка оси (расстояние между засечками и линиями сетки), значение точки пересечения с осью X (для плоских) или с основанием (для объемных) диаграмм; выбирается при необходимости логарифмическая шкала и обратный порядок значений (для оси категорий – изменение порядка следования меток слева направо, для оси значений – изменение значения от меньшего к большему);
вкладка Число — задается формат меток засечек или самих числовых значений на оси;
вкладка Выравнивание — изменяется ориентация текста меток засечек.
Сетка обеспечивает лучшее изображение числовых данных, зрительно облегчает сопоставление данных. При выделении сетки в поле имен появляется стандартное название компонента — Сетка. При форматировании сетки указывается тип линии и шкала разметки (пересечение линий сетки с осями).
Легенда — специальное окно, содержащее для каждого ряда данных ключ и поле-название ряда. Ключ легенды повторяет цвет и узор, заданный для элементов данных ряда. Легенда может создаваться в автоматическом режиме, если при построении диаграммы интервал ячеек включал названия рядов. Если имена рядов явно не заданы, то легенда использует стандартное имя Ряд (номер).
При форматировании целого окна легенды задается вид рамки, цвет и узор закраски области легенды, определяется шрифт, используемый для текста легенды, размещение легенды (внизу, вверху, слева, справа, в углу)
Тренд — это функция заданного вида, с помощью которой можно аппроксимировать построенный по данным таблицы график. Тренд служит для выявления тенденций развития процесса, представленного в виде диаграммы, и обеспечивает прогноз на заданный период. В Excel предусмотрено несколько стандартных типов тренда: линейный, логарифмический, степенной, экспоненциальный, полиномиальный, скользящее среднее.
Необходимые условия построения тренда:
· период времени, за который изучается прогнозируемый процесс, должен быть достаточным для выявления закономерности;
· тренд в анализируемый период должен развиваться эволюционно;
· процесс, представленный диаграммой, должен обладать определенной инерционностью.
Тренд можно строить для диаграмм типа:
· линейчатый график;
· гистограмма;
· диаграмма с областями;
· XY-точечная диаграмма.
Построение тренда осуществляется по следующей технологии:
· построить диаграмму для одного ряда данных;
· выделить эту диаграмму, щелкнув по ней левой кнопкой мыши. На диаграмме должны появиться маркеры;
· вызвать контекстное меню и выполнить команду Добавить линию тренда;
· в диалоговом окне <<Линия тренда>> на вкладке Тип выбрать тип тренда, а на вкладке Параметры установить параметры: Количество периодов прогноза, Показывать уравнение на диаграмме, Поместить на диаграмму величину достоверности аппроксимации;
· нажать кнопку <ОК>.
Для последующего редактирования линии тренда требуется ее выделение (двойным щелчком левой кнопки мыши), при этом появляются специальные отметки на линии тренда. С помощью правой кнопки мыши можно вызвать контекстное меню, обеспечивающее форматирование (команда Форматировать линию тренда), либо удаление линии тренда (команда Очистить).
Ход работы
- Провести подготовительную работу:
· Создать новую рабочую книгу командой Файл, Создать;
· Сохранить в выбранной папке созданную книгу под именем DIAG командой Файл, Сохранить как;
· Переименовать Лист 1 на Успеваемость (установить курсор на Лист 1 и вызвать правой кнопкой мыши контекстное меню, выполнить команду Переименовать и ввести новое имя Успеваемость.
Таблица 5. Сведения об успеваемости
| № группы | Средний балл | |
| Физика | Экономика | |
| 4.4 | 3.3 | |
| 4.8 | 3.9 | |
| 3.4 | 4.5 | |
| 3.9 | 4.2 | |
| Институт |
- Создать таблицу в соответствии с таблицей 5, например, начиная с ячейки А1. Вычислить средний балл по факультету по каждому предмету. В пустые ячейки с названием Институт ввести формулу вычисления среднего балла по Институту, например в ячейку В7 столбца Физика:
· Вызвать Мастер функций, щелкнув по его кнопке на панели инструментов;
· Выбрать категорию функций Статистическая, имя функции – СРЗНАЧ, щелкнуть по кнопке <ОК >;
· Ввести в первую строку диалогового окна адреса первой и последней ячеек столбца с оценками, используя для этого мышь, например В3:В6;
· Скопировать формулу в ячейку С7 столбца Экономика.
- Построить внедренную диаграмму:
· Нажать кнопку Мастер диаграмм и выполнить команду Вставка, Диаграмма.
Этап 1. Выбор типа и формата диаграммы:
- На вкладе Стандартные выбрать тип диаграммы ГИСТОГРАММА и вид диаграммы – номер 1;
- Щелкнуть на кнопке <Далее>.
Этап 2. Выбор и указание диапазонов данных для построения диаграммы:
- На вкладе Диапазон данных установить переключатель Ряды в столбцах;
- Выделить диапазон данных А3:С7;
- В том же диалоговом окне щелкнуть по вкладке Ряд;
- В окне Ряд выделена строка с названием Ряд 1, установить курсор в строке Имя, щелкнуть в ячейке B2 с названием Физика;
- В окне Ряд щелкнуть по названию Ряд 2, установить курсор в стоке Имя и щелкнуть в ячейке С2 с названием Экономика;
- Для создания подписей по оси Х щелкнуть в строке Подписи оси Х и выделить данные первого столбца таблицы, т.е. диапазон А3:А7;
- щелкнуть по кнопке < Далее >.
Этап 3. Задание параметров диаграммы:
- на вкладке Заголовки ввести названия в соответствующих строках:
Название диаграммы: Сведения об успеваемости
Ось Х: Учебные группы
Ось Y: Средний балл
- на вкладке Легенда поставить флажок Добавить легенду и переключатель Справа;
- щелкнуть по кнопке < Далее >.
Этап 4. Размещение диаграммы:
- установить переключатель Поместить диаграмму на имеющемся листе и выбрать из списка лист Успеваемость;
- щелкнуть по кнопке < Готово >. В результате на рабочем листе будет создана внедренная диаграмма.
- Скопировать внедренную диаграмму в другое место листа:
· выделить внедренную диаграмму, щелкнув левой клавишей мыши в области диаграммы один раз. Появятся метки на контуре области диаграммы;
· выполнить команду Правка, Копировать;
· переместить курсор в новое место на рабочем листе;
· выполнить команду Правка, Вставить.
- Добавить в исходную таблицу новый столбец История с различными оценками.
- Изменить формат диаграммы, сделав ее объемной. Для этого:
· установить курсор мыши во внутренней незаполненной области диаграммы и, щелкнув правой кнопкой, вызвать контекстное меню диаграммы;
· выполнить команду Тип диаграммы и выбрать на вкладке Стандартные тип Гистограмма, последний из представленных форматов (3-мерная гистограмма);
· нажать кнопку <ОК>.
- Вставить в диаграмму столбцы, отражающие успеваемость по истории. Для этого:
· установить курсор мыши во внутренней незаполненной области диаграммы и, щелкнув правой кнопкой, вызвать контекстное меню диаграммы;
· выполнить команду Исходные данные и изменить параметры;
· во вкладке Диапазон данных указать весь диапазон данных A3:D7, включив информацию столбца История;
· установить переключатель Ряды в в положение столбцах и нажать клавишу <ОК>;
· на вкладке Ряд в окне Подписи оси ввести диапазон ячеек А3: А7, а в окне Ряд щелкните по названию Ряд 3, установите курсор в строке Имя и щелкните в ячейке D2 с названием История;
· нажать кнопку <ОК>.
- Изменить параметры диаграммы:
· установить курсор мыши во внутренней незаполненной области диаграммы и, щелкнув правой кнопкой, вызвать контекстное меню диаграммы;
· выполнить команду Параметры диаграммы;
· указать на вкладке Заголовки:
