Построение, редактирование и форматирование диаграмм

 

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

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

Различают два вида диаграмм:

§ внедренные диаграммы − сохраняются на рабочем листе вместе с данными;

§ диаграммные листы − диаграмма в формате полного экрана на новом листе.

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

Мастер диаграмм позволяет строить диаграммы 14 стандартных типов плоскостное и объемного представления (с областями, линейчатая, гистограмма, график, кольцевая, лепестковая, точечная, пузырьковая, поверхностная и др.) и 22 нестандартных типа. Мастер Диаграмм осуществляет построение новой диаграммы в интерактивном ре­жиме за четыре шага только для выделенного блока ячеек − диапазона (области) данных для построения диаграммы.

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

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

 

 

Рис. 3.22

 

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

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

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

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

 

 

Рис. 3.23

Этап 3. Задание параметров диаграммы. Задание параметров диаграммы осуще­ствляется в окнах вкладок Мастера диаграмм, представленного на рис. 3.23.

На вкладке Заголовки вводятся поочередно на соответствующую строку название диаграммы, название оси X, название оси У, название оси Z.

На вкладке Оси устанавливаются переключатели выбора вида обозначения меток осей.

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

На вкладке Оси указывается место расположения легенды.

На вкладке Таблица данных устанавливается переключатель отображения на диаграмме таблицы исходных данных.

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

 

 

Рис. 3.24

Этап 4. Размещение диаграммы. Созданную диаграмму можно разместить на том же листе, где находится таблица с исходными данными, либо на отдельном листе. В диало­говом окне Мастер диаграмм на этапе 4 (рис. 3.25.) для этого надо установить соответст­вующий переключатель и нажать кнопку Готово.

 

Рис. 3.25

 

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

 

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

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

 

Рис. 3.26

 

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

 

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

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

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

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

 

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

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

 

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

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

 

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

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

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

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

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

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

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

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

 

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

 

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

 

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

 

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

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

 

Тренды

 

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

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

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

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

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

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

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

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

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

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

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

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

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

· в диалоговом окне Линия тренда на вкладке Тип выбрать тип
тренда, а на вкладке Параметры установить параметры: Количество
периодов прогноза, Показывать уравнение на диаграмме, Поместить на диаграмму величину достоверности аппроксимации;

· нажать кнопку ОК.

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

 

Рис. 3.27

Задание 1. Для ранее созданной таблицы постройте гистограмму, отражающую успеваемость студентов, сдавших экзамены по всем предметам. Разместите гистограмму на отдельном листе. Сравните результат с рис. 3.28.

 

 

Рис. 3.28

 

Задание 2. Отредактируйте построенную диаграмму в соответствии с заданием.

Для этого:

1. Измените формат диаграммы на объемный.

2. Измените параметры диаграммы − названия осей, уберите легенду.

3. Разместите диаграмму на новом листе.

Задание 3. Постройте на гистограмме, созданной в задании 1, экспоненциальную линию тренда и отобразите уравнение и величину достоверности аппроксимации. Результат сравните с рис. 3.29.

 

Рис. 3.29

 

Задание 4. Постройте круговую диаграмму, отражающую средний балл и фамилии учащихся. Результат сравните с рис. 3.30.

Рис. 3.30

Задание 5. Создайте таблицу значений функции y = sin (x) и y = cos (x) (аргумент х изменяется от − 6 до 6 с шагом 0,4) и построить графики данных функций на том же листе. Сохранить результат в файле с именем Тригонометрические функции в своей папке (рис. 3.31.).

 

Рис. 3.31

ПОРЯДОК РАБОТЫ

1. В ячейки А1, В1 и С1 введите заголовки столбцов x, sin(x) и cos(x) ( эти заголовки будут использованы в легенде графика ).

2. Последовательность аргументов х в столбце А (-6; -5,6; -5,2; …; 6) введите с помощью маркера заполнения.

3. В ячейки В2 и С2 введите формулы =SIN(A2) и =COS(А2), после чего скопируйте их в остальные ячейки столбцов В и С, используя маркер заполнения.

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

Контрольные вопросы

 

1. Перечислите этапы построения диаграммы.

2. Какие виды диаграмм можно построить в MS Excel?

3. Как можно отредактировать диаграмму?

4. Перечислите основные элементы диаграммы.

5. Для чего нужна линия тренда, как ее построить?

Списки. Сортировка данных. Фильтрация данных

 

Электронные таблицы Excel можно использовать для организации работы с небольшими реляционными базами данных. В этом случае электронную таблицу называют списком или базой данных Excel и используют соответствующую терминологию:

· строка списка − запись базы данных;

· столбец списка − поле базы данных.

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

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

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

Excel предоставляет возможности для работы с базами данных различных форматов, которые при открытии в среде Excel автоматически преобразуются в список. Такое преоб­разование называют импортом. Данные в электронную таблицу можно включить не только путем импорта из «чужой» базы данных, но и посредством запросов данных, адресованных тому или иному серверу баз данных. Такие запросы формируются специальной программой MS Query, вызываемой по команде Данные, Внешние данные, Создать запрос. Ре­зультат запроса возвращается в электронную таблицу в виде списка.

 

Сортировка данных в списке

 

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

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

В среде Excel предусмотрены три уровня сортировки, которые определяются в диалоговом окне Сортировка диапазона.

Задание 1. В ранее созданной рабочей книге отсортируйте фамилии учащихся по возрастанию. Проделайте это на всех листах рабочей книги.

Фильтрация (выборка данных)

Фильтрация данных в списке – это выбор по заданному критерию (условию). Осуществляется эта операция с помощью команды Данные, Фильтр. Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр.

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

Автофильтрация

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

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

 

Расширенный фильтр

 

Команда Данные, Фильтр, Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:

· критерий сравнения;

· вычисляемый критерий.

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

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

Этап 1 − формирование области критериев поиска;

Этап 2 − фильтрация записей списка.

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

Критерий сравнения формируется при соблюдении следующих требований:

§ состав столбцов области критериев определяется столбцами, по которым задаются ус­ловия фильтрации записей;

§ имена столбцов области критериев должны точно совпадать с именами столбцов ис­ходного списка;

§ ниже имен столбцов располагаются критерии сравнения типа:

− точного значения;

− значения, формируемого с помощью операторов отношения;

− шаблона значения, включающего символы * и (или) ?.