Деловая графика в табличном редакторе Microsoft Excel

Лекция Построение диаграмм в Excel Деловая графика в табличном редакторе Microsoft Excel 1. 2. 3. 4. 5. 6. План. Диаграммы: основные понятия и термины Типы диаграмм Создание диаграммы с помощью Мастера диаграмм Настройка диаграммы Работа с данными диаграммы Добавление тренда Диаграммы: основные понятия и термины Одно из основных свойств, которое сделало Microsoft Excel мощным деловым пакетом – это возможность отображать числовые данные в виде полноцветных, легких для понимания диаграмм. Excel предоставляет широкий диапазон стандартных типов диаграмм научного и делового назначения, каждый из которых имеет несколько вариантов. Например, при создании гистограммы можно использовать перекрывающиеся, сгруппированные, составные или нормированные столбцы. Кроме того, предусмотрена возможность получения нового типа диаграмм на основе основных типов. К примеру, можно наложить график на гистограмму, чтобы отобразить на диаграмме цены акций и объемы их продаж. Можно создать «рисованные» диаграммы, которые для представления значений используют графические рисунки вместо обычных столбцов и линий. Эти типы диаграмм в сочетании со средствами форматирования Ехсеl предоставляют необычайно широкие возможности. Диаграммы в Excel включают много объектов, каждый из которых можно выделять и изменять отдельно. Таблица 1 предоставляет краткие сведения об основных элементах диаграмм Excel. Таблица 1. Элементы диаграммы Excel Название элемента Ось Описание Линия, используемая как основа измерений для построения данных на диаграмме. Ось категорий (Х) и ось значений (Y) образуют границы диаграммы и имеют шкалу для нанесения данных. Ось значений Z используется в трехмерных диаграммах Точка данных Элемент данных Ряд данных Совокупность точек данных, отображающая значения строк или столбцов рабочего листа. Каждый рад данных отображается по-своему. На диаграмме может быть отображен один или несколько радов данных Легенда Подпись, определяющая закраску или цвета точек данных или категорий диаграммы Маркер Элемент, который представляет точку данных на диаграмме. Вид маркера зависит от типа и вида диаграммы. Все маркеры одного ряда данных имеют одинаковую форму и цвет Область В двухмерной диаграмме это прямоугольная область, ограниченная двумя осями и построения содержащая на себе все ряды диаграммы. В трехмерной диаграмме это область, ограниченная осями и включающая ряды данных, названия категорий, подписи делений и названия осей Область Вся диаграмма вместе со всеми ее элементами диаграммы Формула Формула с внешними ссылками на данные определенного листа, по которым рядов построена диаграмма 1 Лекция Построение диаграмм в Excel Название элемента Текст Описание Надписи (название диаграммы, значения и категории на осях) и подписи (текст, связанный с точками данных). Любой текст на диаграмме можно изменять и перемещать Деление Деление шкалы вдоль оси категорий (Х) и осей значений (Y и Z) Таблица Таблица, содержащая отображаемые на диаграмме данные; обычно связана с осью данных категорий и заменяет подписи оси категорий. Каждая строка таблицы содержит ряд данных Некоторые элементы диаграммы показаны на рисунке: Рис. 1. Элементы диаграммы В Excel можно построить внедренные диаграммы и диаграммы на отдельных листах диаграмм. Внедренные диаграммы создаются на рабочем листе рядом с таблицами, данными и текстом. Внедрение диаграмм имеет смысл при создании отчетов, для демонстрации диаграмм рядом с данными, по которым они построены. Перед внедрением диаграммы необходимо подготовить место, вставив строки, столбцы или ячейки. Для работы с диаграммой отдельно от данных на рабочем листе можно построить ее на отдельном листе диаграмм. Этот лист помещается в активную рабочую книгу и сохраняется вместе с ней. Диаграмма на отдельном листе удобна, если, например, нужно вывести ее на печать. Диаграммы легко строятся с помощью Мастера диаграмм . Он руководит процессом создания диаграмм шаг за шагом и дает возможность перед завершением процесса просмотреть диаграмму и внести в нее необходимые изменения. Если выделить данные и нажать клавишу , Excel создает диаграмму на отдельном листе диаграмм, используя тип диаграммы по умолчанию. Типы диаграмм Гистограммы. В качестве маркеров используются вертикальные столбцы, чтобы создать впечатление отдельных измерений, относящихся к разным интервалам времени (рис. 2). Категории располагаются по горизонтали, а значения – по вертикали. Этот тип диаграмм будет лучшим выбором, если отображается информация, собираемая через регулярные интервалы времени. Например, если доход подсчитывается ежемесячно, можно поставить в соответствие каждому месяцу собственный блок. Этот тип диаграмм часто используется компаниями для показа своих прибылей за определенный промежуток времени. 2 Лекция Построение диаграмм в Excel Д о х о д C o m p a q C o m p u te r 700 600 млн. $ 462 454 500 400 1990 300 213 200 1991 131 1992 100 1993 1994 (пр огно з) Линия т р ен да 1994 1993 1992 1991 1990 год Рис. 2. Гистограмма Линейчатые. Очень похожи на гистограммы за исключением того, что осью категорий является вертикальная ось (Y), а осью значений – горизонтальная ось (Х) (рис. 3). Используются в основном для сопоставления отдельных значений в определенный момент времени. Горизонтальное расположение полос позволяет подчеркнуть положительные или отрицательные отклонения от некоторой величины. Можно использовать для отображения отклонений по разным статьям бюджета в определенный момент времени. Линейная диаграмма с накоплением показывает вклад отдельных элементов в общую сумму. Начальный капитал Наращенный капитал АО "СЕВЕР-ЮГ" СП "Приве т" ЧП "Евклид" ООО "Августин" АОЗТ "Бублик" АО "СИГМА" 20000 40000 60000 80000 100000 120000 Рис. 3. . Линейчатая диаграмма Графики. Отображают зависимость данных (ось Y) от величины, которая меняется с постоянным шагом (ось Х) (рис. 4). Применяются для отображения ситуации с производством, продажей или уровнем цен на бирже, чтобы продемонстрировать тенденцию дохода или продаж во времени. 500000 400000 300000 200000 100000 1 2 4 7 Рис. 4. График 3 9 13 Лекция Построение диаграмм в Excel Круговые. Отображают соотношение частей и целого. Строятся только по одному ряду данных, первому в выделенном диапазоне. Эту диаграмму следует использовать, когда компоненты в сумме составляют 100%. Сектора круговой диаграммы можно выдвинуть из общего круга, чтобы подчеркнуть точки данных, которые они отображают. Трудно делать сопоставление частей круговой диаграммы, если их больше пяти, для отображения вклада в общую сумму каждого из поставляемых продуктов или каждого дохода в процентах. Эти диаграммы хороши для рекламных материалов и презентаций, когда требуется только общее впечатление. Для облегчения работы с маленькими долями диаграммы в основной диаграмме их можно объединить в один элемент, а затем разбить их в отдельную диаграмму рядом с основной (рис. 5). Управленческая информация Юридическая информация Технологическая информация Состав денежных ресурсов Экономическая информация Социальная информация Состав материальных ресурсов Состав объектов управления Состав трудовых ресурсов Демографическая информация Рис. 5. Круговая диаграмма с круговой вторичной диаграммой Точечные. Предназначены для демонстрации тенденции изменения данных при неравных интервалах времени или других интервалах измерения, отложенных по оси категорий (рис. 6). Используются для представления дискретных измерений по осям X и Y. Например, данные опроса можно представить так, чтобы ответы были расположены по оси значений, а возраст опрашиваемых – по оси категорий, и тем самым отобразить разбиение мнений по возрастным группам. Многие научные и технические данные представляются точечными диаграммами. Рис. 6. Точечная Диаграммы с областями. Позволяют проследить непрерывное изменение суммы значений всех рядов данных и вклад каждого ряда в эту сумму (рис. 7). Удобны для отображения процесса производства или продажи изделий. Объектами диаграмм с областями могут быть поставки за день или объем заказов в зависимости от времени. 4 Лекция Построение диаграмм в Excel Доходы фирмы 30000 прибыль 25000 Транспорт Транспорт 20000 Бакалея Бакалея 15000 Холодильники Холодильники 10000 Мясо 5000 Мясо Январь Февраль Март месяц Рис. 7. . Диаграмма с областями Кольцевые. Сравнивают вклад частей в целое, однако, в отличие от круговых диаграмм, в них могут быть представлены два и более ряда данных (рис. 8). Каждое кольцо в кольцевой диаграмме представляет отдельный ряд данных. Когда необходимо различать точные процентные соотношения, лучше использовать гистограмму с ее осью значений. Рис. 8. Кольцевая диаграмма Лепестковые. Используются, чтобы показать соотношения отдельных рядов данных, а также одного определенного ряда данных и всех остальных рядов. Каждая категория имеет собственную ось координат. Точки данных располагаются вдоль луча. Линии, соединяющие точки данных одного ряда, охватывают площадь, характеризующую совокупность значений в этом ряду. Трудны для восприятия и интерпретации. С помощью такой диаграммы можно отобразить динамику затрат времени на проект, включающий несколько задач. В этом случае каждой категории (лучу диаграммы) соответствует определенная задача проекта, а точки на луче представляют собой затраты времени на нее к какому-то сроку. Каждый ряд данных характеризует время, потраченное на проект к определенному сроку. Чем больше площадь, охватываемая диаграммой, тем больше затраченное на проект время. Если все задачи требуют одинакового времени, диаграмма приближается к кругу (рис. 9). 5 Лекция Построение диаграмм в Excel Углеводы 50 40 30 Молоко 20 Кефир 10 Ряженка Творог Белки Жиры Рис. 9. Лепестковая диаграмма Поверхности. Подобно топографическим картам показывают низкие и высокие точки поверхности (рис. 10). Это превосходный способ наглядного представления высоких и низких значений в наборе данных, который зависит от двух переменных. Форматы предлагают объемные отображения в виде каркаса и в виде поверхности. Диаграмма с поверхностью представляет собой натянутую на точки поверхность. Одинаковым цветом закрашены области, принадлежащие одному интервалу величин по вертикальной оси значений (Z). На диаграмме в виде проволочного каркаса проще сравнивать точки данных, которые могут быть скрытыми на диаграмме с поверхностью. Цветная контурная диаграмма и контурная диаграмма являются проекциями поверхности на плоскость и служат как бы топографическими картами, на которых изменения в высоте по оси значений обозначены только цветом или только контуром. Диаграмму с поверхностью можно поворачивать и видеть ее с разных точек зрения. Для этого нужно щелкнуть по одному из углов на любой оси диаграммы. После появления черных маркеров во всех углах области построения можно перетаскивать любой из них до получения желаемого ракурса. 3 2 1 -1 0,6 -1 1 0,6 0,2 -0,2 -0,2 -0,6 -1 -2 Рис. 10. Поверхность Пузырьковые. Позволяют отображать на плоскости наборы данных из трех значений. Первые два значения откладываются по оси категории (Х) и по оси значений (Y) так же, как и при построении XYточечных (рис. 11). Третье значение представляется размером пузырька. Для построения пузырьковой диаграммы необходимо три строки или столбца значений: один – для значений по оси Х, один – для значений по оси Y и один – для размера пузырька. Можно использовать, например, для сравнения цены, срока эксплуатации и количества выходящих за это время из строя компьютеров 6 Лекция Построение диаграмм в Excel Рост и вес ребенка в возрасте до года рост, см 100 80 60 4,32 3,6 7,1 6,5 7 4,93 4,6 вес, кг 40 20 2 4 6 8 10 12 14 возраст, мес Рис. 11. Пузырьковая диаграмма Биржевые. Используются для отображения изменения курса акций во времени (рис. 12). Можно отображать следующие данные: а) самый высокий курс, самый низкий курс и курс закрытия; б) курс открытия, курс закрытия, самый высокий курс и самый низкий курс; в) объем, высокий курс, низкий курс и курс закрытия; г) объем, курс открытия, высокий курс, низкий курс и курс закрытия. При выборе одного из форматов биржевой диаграммы, который отображает вместе с курсами и объем, создаются две оси значений: одна – для объема, представляемого на диаграмме столбцами, а другая – для курсов, изображаемых вертикальными линиями. При вводе одного из форматов биржевой диаграммы, который отображает вместе с курсами и объем, создаются две оси значений: одна – для объема, представляемого на диаграмме столбцами, а другая – для курсов, изображаемых вертикальными линиями. При вводе данных для биржевой диаграммы необходимо размещать данные в последовательности, которая указывается внизу диалогового окна Тип диаграммы, при выборе соответствующего формата диаграммы. 12000 565 560 555 550 545 540 535 530 525 520 515 10000 8000 6000 4000 2000 02.11.1998 01.11.1998 31.10.1998 30.10.1998 29.10.1998 28.10.1998 27.10.1998 26.10.1998 25.10.1998 24.10.1998 Рис. 12. Биржевая диаграмма Цилиндрические, конические и пирамидальные. Объемные варианты гистограмм (рис. 13) и линейчатых диаграмм. Маркеры, используемые в этих диаграммах, украсят и придадут эффект всей диаграмме. Однако эти типы объемных диаграмм не добавляют новой информации к представлению данных на обычных объемных гистограммах и линейчатых диаграммах. 7 Лекция Построение диаграмм в Excel Рис. 13. Цилиндрическая диаграмма Процедура создания диаграммы с помощью Мастера диаграмм Шаг 0: вызов Мастера диаграмм. Выделить данные, по которым будет строиться диаграмма, и нажать кнопку Мастера диаграмм или выбрать команду Вставка/Диаграмма. Мастер диаграмм позволяет задать исходные данные для построения диаграммы в процессе диалога, но предварительное выделение упрощает процесс. С помощью четырех окон диалога Мастер диаграмм соберет всю информацию, необходимую Excel для построения диаграммы. Шаг 1. Выбор типа диаграммы Шаг 1: выбор типа диаграммы. В первом диалоговом окне задается тип (гистограмма, график, линейчатая и т.д.) и вид (обычная гистограмма, гистограмма с накоплением и т.д.) диаграммы. Это окно диалога содержит две вкладки: для стандартных и нестандартных типов диаграмм. К последним относятся как пользовательские типы диаграмм, полученные на основе встроенных диаграмм, так и смешанные (гистограмма с графиком, гистограмма с областями, график с двумя осями значений и т.п.) диаграммы Excel. Шаг 2: уточнение исходных данных. Второе окно диалога Мастера диаграмм, как и первое, содержит две вкладки. Вкладка Диапазон данных позволяет проверить или задать диапазон исходных данных. Excel автоматически выбирает ориентацию рядов, предполагая, что диаграмма должна содержать рядов меньше, чем точек. Но при необходимости можно указать, где расположены ряды данных: в строках или столбцах. Вкладка Ряд предоставляет возможность задать имена рядов, значения рядов, а также текст, идентифицирующий точки в рядах данных. 8 Лекция Построение диаграмм в Excel Шаг 2. Уточнение исходных данных Чтобы увидеть, из каких ячеек для конкретного ряда будут поступать значения и имя, которое появится в легенде, нужно выбрать имя ряда в списке Ряд. В поле Значения отображается ссылка на диапазон ячеек рабочего листа, в котором находятся значения ряда данных. Кроме того, это поле позволяет задать новый диапазон, что приведет к изменению значений ряда. Если исходный диапазон не содержит заголовков строк и столбцов, которые можно использовать в качестве имен рядов данных, то Excel назначает каждому ряду стандартные именования: Ряд 1, Ряд 2 и т.д. Чтобы изменить стандартное имя, нужно ввести текст в поле Имя или после щелчка на этом поле выделить ячейку рабочего листа, содержащую нужный текст. Если исходный диапазон содержит заголовки, в поле имя для каждого ряда отображается ссылка на ячейку, из которой поступает имя ряда. Если значение этой ячейки изменится, то автоматически изменится и имя ряда. Если исходный диапазон не содержит заголовки строк или столбцов, которые именуют точки ряда данных, то в этом случае для точек используются стандартные обозначения 1, 2, 3 и т.д. Чтобы изменить их, необходимо ввести текстовые значения прямо в поле Подписи оси Х (Подписи категорий, Подписи второй оси Х), разделяя их точкой с запятой, или после щелчка на этом поле выделить диапазон рабочего листа, содержащий нужные подписи. Шаг 3: задание параметров диаграммы. Третье окно диалога Мастера диаграмм содержит шесть вкладок. Они позволяют задать различные параметры диаграммы:  название диаграммы и заголовки для ее осей,  характеристики осей,  легенду,  подписи значений в рядах данных,  линии сетки,  таблицу данных. Название диаграммы и заголовки для ее осей. На вкладке Заголовки можно задать название диаграммы и заголовок для каждой ее оси. При выводе текста в диаграмме Excel использует установленные по умолчанию шрифт, выравнивание и расположение. Заголовки – это обычные поля надписей, которые можно перемещать, форматировать и редактировать после создания диаграммы. 9 Лекция Построение диаграмм в Excel Этап 3. Задание параметров диаграммы Характеристики осей. Некоторые диаграммы выглядят лучше без одной или нескольких осей. Вкладка Оси позволяет отменить в диаграмме вывод любой ее оси. Для этого нужно снять соответствующий флажок. Если данные для оси Х являются датами, то Excel распознает этот факт и автоматически применяет временное масштабирование к этой оси. Временное масштабирование отличается от обычного в нескольких отношениях: 1) Excel располагает точки в хронологическом порядке с учетом относительных значений дат. Например, если на оси времени есть значения для 1 января, 2 января и 6 января, то точка 2 января располагается ближе к 1 января, чем к 6 января; 2) Excel автоматически отображает точки данных в возрастающем порядке, даже если исходные значения дат не были отсортированы по возрастанию. Ось времени может применяться как в плоских, так и в объемных графиках, гистограммах, диаграммах с областями и линейчатых диаграммах. В этих типах диаграмм можно использовать для оси X обычную шкалу (категории) (даже если данные имеют формат даты) или шкалу времени (даже если данные не имеют формат даты), установив в группе Основная ось переключатель категории или ось времени соответственно. Если установлен переключатель автоматическая, Excel выберет тип оси (категории или ось времени) сам. Линии сетки – это горизонтальные или вертикальные линии, которые помогают определить положение маркеров данных относительно шкал осей. В большинстве типов диаграмм Excel по умолчанию используется сетка определенного вида. Можно удалить линии сетки или отобразить их в диаграмме с помощью вкладки Линии сетки. Основные линии сетки продолжают основные деления шкалы, а промежуточные линии сетки – промежуточные деления. Excel автоматически определяет положение этих делений шкалы, но с помощью команд форматирования можно изменить их расположение. Линии сетки в круговых и кольцевых диаграммах не используются. Размещение и скрытие легенды. Легенда автоматически располагается в правой части диаграммы. Вкладка Легенда позволяет выбрать для легенды новое место или совсем удалить ее из диаграммы. Кроме того, легенду можно переместить и после создания диаграммы, перетащив ее с помощью мыши в новое место, а команды форматирования позволяют изменить используемый шрифт. 10 Лекция Построение диаграмм в Excel Этап 3. Задание параметров диаграммы Подписи данных. Вкладка Подписи данных предоставляет возможность подсоединять к маркерам данных различного типа подписи. Например, секторы круговых и кольцевых диаграмм могут идентифицироваться с помощью долей (процентов), абсолютных значений или одновременно с помощью того и другого. Столбцы в гистограммах и маркеры в графиках могут иметь подписи, показывающие значения каждой точки или текст соответствующей категории на оси Х. Этап 3. Задание параметров диаграммы Таблица данных. В зависимости от типа создаваемой диаграммы Excel может позволить дополнительно включить таблицу данных. Это простой и эффективный способ визуализации диаграммы и данных. Чтобы добавить таблицу данных, нужно установить флажок Таблица данных на одноименной вкладке. Присоединяемая к диаграмме таблица связывается с данными рабочего листа, что обеспечивает изменение значений в таблице при изменении соответствующих значений данных рабочего листа, взятых для построения диаграммы. После создания диаграммы можно с помощью команд форматирования изменить тип линии и шрифт, используемый в таблице данных. Таблица данных не применяется в круговых, точечных, кольцевых, пузырьковых, лепестковых и поверхностных диаграмм. Шаг 4: размещение диаграммы. Четвертое окно Мастера диаграмм позволяет задать расположение диаграммы:  на рабочем листе рядом с исходными данными (переключатель имеющемся),  на отдельном листе диаграмм (переключатель отдельном). 11 Лекция Построение диаграмм в Excel Шаг 4. Размещение диаграммы Настройка диаграммы В Microsoft Excel имеется широкий ассортимент команд, с помощью которых можно изменить внешний вид диаграмм. Первым шагом при настройке любой диаграммы является ее выделение. Для этого необходимо активизировать лист диаграммы, если она построена на отдельном листе, или выделить диаграмму в рабочем листе, если она является внедренным объектом. Следующий шаг – вызов контекстного меню. Четыре команды контекстного меню:  Тип диаграммы…,  Исходные данные…,  Параметры диаграммы…,  Размещение… выводят на экран окна диалога, почти идентичные окнам Мастера диаграмм. Поэтому указанные команды меню можно использовать для пересмотра решений, принятых во время создания диаграммы. Чтобы изменить конкретный элемент диаграммы, такой как ось категорий или легенда, нужно выделить этот элемент. Excel облегчает проверку правильности выделения элемента: после щелчка на элементе диаграммы Excel отображает его имя в поле имени. Контекстное меню, вызванное на выделенном элементе диаграммы, предлагает пункт: Формат <название элемента диаграммы>…, где <название элемента диаграммы> – это легенда, ось, область диаграммы, область построения и т.п., вызывающий диалоговое окно, элементы управления которого позволяют отформатировать этот элемент. Можно просто дважды щелкнуть на элементе диаграммы, который нужно изменить. Форматирование и изменение шкал осей Microsoft Excel предоставляет широкие возможности для форматирования, а также изменения расположения и масштаба шкал осей диаграммы. Можно изменить тип, цвет и толщину осей, а также скрыть или, наоборот, отобразить деления и подписи делений шкалы. Кроме того, можно изменить стандартную шкалу и точку пересечения вертикальной и горизонтальной осей. Указанные параметры для оси (как горизонтальной, так и вертикальной) можно установить с помощью пяти вкладок диалогового окна Формат оси. Диалоговое окно Формат оси вызывается одноименной командой контекстного меню. Выбор типа, цвета и толщины линии. Задание расположения делений шкалы и их подписей По умолчанию используется сплошная тонкая черная линия. Вкладка Вид диалогового окна Формат оси (рис. 14) предоставляет на выбор восемь типов и четыре толщины линии. Здесь же можно задать цвет линии. Чтобы ось не отображалась на диаграмме, в группе Ось нужно установить переключатель невидимая. 12 Лекция Построение диаграмм в Excel Кроме того, вкладка Вид позволяет задать расположения деления шкалы и их подписи. Деления шкалы – это короткие черточки, расположенные вдоль линии оси через регулярные промежутки. Между основными делениями на оси могут отображаться промежуточные деления. Подписи делений характеризуют их значения на оси. На рис. 15 показаны режимы отображения основных делений для горизонтальной и промежуточных делений для вертикальной осей. Используемые обозначения: Г-осн – горизонтальная ось, основные деления, В-пром – вертикальная ось, промежуточные деления. Нет Внутрь Рис. 14. Настройка вида формата оси Наружу Пересекают ось Г-осн В-пром Рис. 15. Расположение основных и промежуточных делений В-ось Г-ось Варианты отображения подписей делений представлены на рис. 16. Используемые обозначения: Гось – горизонтальная ось, В-ось – вертикальная ось. Нет Внизу Вверху Рядом с ось Рис. 16. Расположение подписей делений Изменение шкалы оси В большинстве случаев Excel создает вполне удовлетворительные шкалы для осей диаграммы, но при необходимости можно задать свои параметры оси. Например, при построении графиков часто необходимо, чтобы оси пересекались в точке (0, 0) (по умолчанию Excel этого не делает). Для этого нужно воспользоваться вкладкой Шкала. Вид вкладки может быть различным (рис. 17, 18) и зависит от 13 Лекция Построение диаграмм в Excel того, форматирование какой шкалы – оси значений, оси категорий, оси времени, оси рядов – производится. В таблице 2 приведены описания всевозможных элементов вкладки Шкала. Рис. 17. Форматирование оси значений Рис. 18. Форматирование оси категорий Таблица 2. Элементы управления вкладки Шкала Название «Минимальное значение» («Максимальное значение») «Минимальное значение» («Максимальное значение») «Цена основных делений» («Цена промежуточных делений») Для какой Описание оси Флажок с Ось При установленном флажке осуществляется вывод полем значений наименьшего (наибольшего) округленного значения ввода для всех рядов данных. Если флажок сброшен, в поле ввода можно задать наименьшее (наибольшее) значение данных для оси. При этом точки данных, связанные со значениями, не превышающими (превышающими) наименьшее (наибольшее) значения, будут игнорироваться Флажок с Ось времени Если флажок установлен, производится полем автоматическое определение начала (конца) отсчета. ввода В случае, когда флажок сброшен, пользователь в поле ввода может указать нужное время начала (конца) отсчета. При этом точки данных, связанные с датами, предшествующими началу отсчета (следующие после даты конца отсчета), будут игнорироваться Флажок с Ось Для автоматического определения цены основных полем значений, (промежуточных) подписей делений и основной ввода ось времени сетки, флажок должен быть установлен. Если флажок сброшен, в поле ввода можно указать нужное значение. Для оси времени кроме того можно выбрать единицу измерения основных делений: дни, месяцы, года Элемент 14 Лекция Построение диаграмм в Excel Для какой оси «Ось Х (категорий) Флажок с Ось пересекается в полем значений, значении», «Ось Y ввода ось (значений) пересекается категорий в значении» (ось времени) «Логарифмическая Флажок Ось шкала» значений Название Элемент «Обратный порядок значений» («Обратный порядок категорий», «Даты в обратном порядке») «Пересечение с осью Х (категорий) в максимальном значении» («Пересечение с осью (Y) в максимуме», «Пересечение с осью Y (значений) в максимальной категории») «Пересечение с осью Y (значений) в категории номер» «Число категорий между подписями делений» «Число категорий между делениями» Флажок «Пересечение с осью Y (значений) между категориями» («Пересечение с осью Y между датами») «Единица измерения» Флажок Флажок Ось значений (ось категорий, ось времени) Ось значений (ось времени, ось категорий) Поле ввода Ось категорий Поле ввода Ось категорий Поле ввода Флажок Описание Установка флажка приводит к пересечению осей в точке 0 (в начале отсчета) или в точке на оси значений ближайшей к точке 0. При сброшенном флажке в поле ввода можно определить значение (дату), в котором пересекаются ось категорий Х (ось времени) и ось значений Y Используется для отображения значений как степени 10. При этом нулевые и отрицательные значения не допустимы. Цена основных и промежуточных делений должна быть не меньше 10 Используется для изменения порядка вывода значений (категорий, дат) на оси Флажок устанавливается в том случае, когда необходимо, чтобы ось значений и ось категорий (времени) пересекались в максимальном значении (в точке с максимальным значением даты, категории) В поле задается номер категории, в которой производится пересечение осей Используется для задания частоты подписей по оси. Если задано значение 1, выводится каждое значение; если значение 2 – каждое второе и т.д. Ось Используется для задания частоты делений по оси. категорий Если задано значение 1, то выводится деление для каждой категории; если значение 2 – выводится деление для каждой второй категории и т.д. Ось Если этот флажок установлен, точки данных категорий строятся между подписями делений. В противном (ось случае они строятся на метках делений. Для того времени) чтобы оси пересекались в точно указанной категории (дате), необходимо сбросить этот флажок Ось времени Устанавливается в том случае, когда нужно, чтобы Excel автоматически определял единицу измерения (дни, месяцы, годы) 15 Лекция Построение диаграмм в Excel Вывод и форматирование сетки Для того чтобы отформатировать отображаемые линии сетки, необходимо выделить любую линию в наборе линий. Затем в контекстном меню выбрать команду Формат линии сетки…. Появившееся диалоговое содержит две вкладки: Вид и Шкала (рис. 14, 17, 18). Первая позволяет задать тип, цвет и толщину линий сетки. Вторая – расположение и частоту отображаемых линий сетки за счет изменения значений в полях Цена основных делений и Цена промежуточных делений. Форматирование рядов и маркеров данных Excel предоставляет широкие возможности для формирования внешнего вида и расположения как отдельных маркеров, так и целых рядов данных. Основная часть форматов может применяться как к отдельному маркеру, так и ко всем маркерам ряда данных. Чтобы выделить конкретный маркер, следует сначала выделить его ряд данных, а затем щелкнуть еще один раз на маркере, который нужно отформатировать. Существуют форматы, которые применимы ко всем типам диаграмм, и форматы, которые используются только с определенными типами диаграмм. Форматирование рядов и маркеров данных В большинстве случаев форматирование рядов данных и отдельных маркеров производится с помощью вкладок диалогового окна Формат ряда данных и Формат элемента данных соответственно. Эти окна вызываются следующим образом: 1) выделяется ряд данных или маркер данных; 2) выбирается команда Формат рядов данных… или Формат элемента данных… из контекстного меню. Рис. 19. Диалог Формат рядов данных Рис. 20. Диалог Формат элемента данных Отображение рядов данных на вспомогательной оси. При большом различии диапазонов изменения значений для разных рядов данных можно отобразить один или несколько из них на вспомогательной оси. Вспомогательная ось обычно располагается в правой части диаграммы (рис. 21). Чтобы переключить ряд данных с основной оси на вспомогательную или переместить ряд со вспомогательной оси на основную, нужно на вкладке Ось в группе Построить ряд выбрать соответствующий переключатель (рис. 22). 16 Лекция Построение диаграмм в Excel Основная ось руб. шт. 20000 70 60 50 40 30 20 10 15000 10000 5000 Вспомогательная ось Сумма, руб. Количество, шт Для ряда Сумма использован тип диаграммы Гистограмма, для ряда Количество – тип График Маг1 Маг2 Маг3 Маг4 Рис. 21. Пример использования вспомогательной оси и смешивания типов диаграмм Рис. 22. Переключение ряда данных с основной оси на вспомогательную Изменение порядка рядов данных. Обычно Excel отображает ряды данных в соответствии с порядком расположения столбцов и строк исходных ячеек в рабочем листе. Вкладка Порядок рядов позволяет изменить порядок отображения рядов (рис. 23). 35000 30000 25000 20000 15000 10000 5000 1990 а) Калуга Липецк 1991 Рязань 1993 Тамбов 35 0 00 30 0 00 25 0 00 20 0 00 15 0 00 10 0 00 5 0 00 1 99 0 Р язань Рис. 23. Диалог Порядок рядов Липецк 1 9 91 К а л у га 1 99 3 Т ам бо в б) Рис. 24. Пример изменения порядка рядов данных: а) диаграмма до смены порядка рядов; б) диаграмма после смены порядка рядов 17 Лекция Построение диаграмм в Excel Изменение цвета, узора, заливки и границ маркеров данных. Чтобы изменить цвет, узор, заливку или границы отдельного маркера или всех маркеров ряда данных, нужно воспользоваться вкладкой Вид (рис. 19). На этой вкладке для рядов данных многих типов диаграмм присутствует флажок Инверсия чисел<0. В ряду данных, для которого установлен этот флажок, Excel отображает все маркеры отрицательных значений контрастным цветом (рис. 25). Следует отметить, что Excel использует один и тот же контрастный цвет для всех рядов, поэтому маркеры отрицательных значений разных рядов становятся трудноразличимыми. Маркеры отрицательных значений отображаются контрастным цветом, поскольку установлен флажок Инверсия чисел < 0 Рис. 25. Использование инверсии маркеров для отрицательных чисел В однорядовых диаграммах для каждого маркера данных может использоваться свой цвет. По умолчанию Excel отображает секторы круговых и кольцевых диаграмм разным цветом. Этот режим отображения маркеров данных контролируется флажком Разноцветные точки (Разноцветные дольки для круговых и кольцевых диаграмм) на вкладке Параметры. Рис. 26. Вкладка параметры диалога Формат ряда данных Настройка расположения маркеров в плоских гистограммах и линейчатых диаграммах. В линейчатых диаграммах и гистограммах можно изменить взаимное расположение маркеров внутри группы и промежутки между группами маркеров. Поле Перекрытие вкладки Параметры (рис. 26) отвечает за расположение маркеров внутри группы точек данных (рис. 27), относящихся к одной категории, а поле Ширина зазора определяет промежутки между группами (рис. 28). Настройка расположения маркеров в объемных диаграммах. В объемных диаграммах, в которых используется ось рядов, расположение маркеров данных контролируется тремя параметрами. Глубина и ширина зазора определяют относительную величину промежутков между маркерами, глубину – вдоль оси рядов, а ширина – вдоль оси категорий. Глубина диаграммы задает относительную длину оси рядов. При увеличении глубины или ширины зазора маркеры данных отдаляются, а при уменьшении этих параметров – сближаются. Увеличение глубины диаграммы придает ей объемность, а уменьшение делает диаграмму более плоской (рис. 29). Для изменения этих параметров объемной диаграммы нужно воспользоваться вкладкой Параметры (рис. 26). 18 Лекция Построение диаграмм в Excel Перекрытие 30000 маркеров на 50% 30000 20000 20000 10000 10000 Перекрытие маркеров на -50% Рязань 1990 Липецк 1991 Калуга Рязань 1992 1990 Липецк 1991 Калуга 1992 Рис. 27. Пример использования перекрытия маркеров данных Ширина зазора 35000 равна 0 30000 35000 30000 25000 25000 20000 20000 15000 Объем 15000 10000 10000 5000 5000 Рязань Липецк Ширина зазора равна 100 Объем Рязань Калуга Липецк Калуга Рис. 28. Пример использования ширины зазора между маркерами Глубина диаграммы 100 Глубина диаграммы 230 30000 20000 20000 1991 10000 Ширина зазора 50 1991 1990 Рязань 1990 Рязань Липецк Ширина зазора 150 Липецк Рис. 29. Пример расположения маркеров в объемных диаграммах Добавление линий рядов в гистограммах и линейчатых диаграммах с накоплением. Линии рядов соединяют маркеры в гистограммах и линейчатых диаграммах с накоплением. Они помогают отслеживать изменение величины маркеров данных, основание которых находится не на оси категорий. Добавить линии рядов в гистограмму или линейчатую диаграмму с накоплением можно с помощью вкладки Параметры: нужно установить флажок Соединить значения ряда (рис. 30). Объем продаж за 3 года 100000 75000 1992 50000 1991 25000 1990 Рязань Липецк Калуга Рис. 30. Использование линий рядов в гистограммах с накоплением 19 Лекция Построение диаграмм в Excel Изменение формы маркеров данных в объемных гистограммах и линейчатых диаграммах. В некоторых типах объемных гистограмм и линейчатых диаграмм можно использовать различные формы маркеров данных: параллелепипеды, цилиндры, конусы и пирамиды. Вкладка Фигура (рис. 30) позволяет изменить форму маркеров данных. Рис. 31. Вкладка Фигура диалога Формат ряда данных Сглаживание линий в графиках и точечных диаграммах. Excel может применять сглаживание к рядам данных на графиках и точечных диаграммах. Чтобы воспользоваться этой возможностью, нужно на вкладке Вид установить флажок Сглаженная линия (рис. 32). Не сглаженная пунктирная 40000 линия 1990 1990 Сглаженная сплошная линия 40000 30000 30000 20000 20000 10000 10000 Рязань Липецк Калуга Тамбов Рязань Липецк Калуга Тамбов Рис. 32. . Пример сглаживания линий и изменения типа линий в графиках Изменение линий маркеров в графиках, точечных и лепестковых диаграммах. Вкладка Вид позволяет изменить тип, толщину и цвет линии на графике (рис. 19, 20), лепестковой или точечной диаграмме. Кроме того, можно задать вид, цвет и размер маркеров или вовсе удалить их из ряда данных. Отображение в графиках коридоров колебания и полос повышения и понижения. Коридор колебания – это линия, соединяющая минимальное и максимальное значения и наглядно показывающая диапазон, в пределах которого изменяются значения в данной категории. Коридор колебания может быть показан только на плоских графиках. Полоса повышения и понижения – это прямоугольник, нарисованный между точками данных первого и последнего ряда. Excel заполняет прямоугольник одним цветом или узором, если первый ряд расположен выше последнего, и контрастным цветом или узором в противном случае. Полосы повышения и понижения обычно используются в биржевых диаграммах для отслеживания изменения цен открытия и закрытия, но можно отобразить их и на плоских графиках, содержащих, по крайней мере, два ряда данных. Флажок Минимум-максимум вкладки Параметры позволяет задать вывод коридора колебания (рис. 33), флажок Открытие-закрытие – полос повышения и понижения. Можно изменить внешний вид коридоров колебания и полос повышения и понижения. Для этого выделяется одна из них и затем выбирается команда Формат коридора колебания или Формат полос 20 Лекция Построение диаграмм в Excel понижения. Excel откроет окно диалога, позволяющее изменять цвет, толщину и тип линии коридоров колебания или цвет, узор и рамку полос повышения и понижения. Для заливки полос повышения и понижения можно использовать текстуру или рисунок. 40000 30000 20000 10000 Коридор колебания Рязань Липецк Калуга Тамбов Рис. 33. Отображение коридоров колебаний в графиках Отображение линий проекций в графиках и диаграммах с областями. Линия проекции – это прямая, которая проходит от точки данных до оси категорий. Линии проекций особенно полезны в диаграммах с областями, содержащих несколько рядов данных, но их можно добавить в любую диаграмму с областями, в плоский и объемный график (рис. 34). Флажок Линии проекции вкладки Параметры (рис. 33) отвечает за построение линий проекций от каждого ряда к оси Х на графике или диаграмме с областями. Чтобы отформатировать линии проекции для ряда данных, нужно выделить одну из них и выбрать команду контекстного меню Формат линий проекций. 1990 Линии проекций 40000 30000 20000 10000 Рязань Липецк Калуга Тамбов Рис. 34. Отображение линий проекций Вращение круговой или кольцевой диаграммы. Круговые и кольцевые диаграммы обычно изображаются так, что начальный радиус первого сектора проходит под углом 45 градусов к вертикали. Чтобы изменить этот угол, нужно на вкладке Параметры ввести нужное значение в поле Угол поворота (рис. 26). Секторы круговых и кольцевых диаграмм всегда отображаются последовательно по часовой стрелке. Параметры вывода для пузырьковых диаграмм. Пузырьковая диаграмма является специальным типом точечной диаграммы, в которой размер маркеров данных (пузырьков) определяется третьим столбцом или строкой исходных данных. По умолчанию эти значения определяют площадь пузырьков, но они могут задавать и их диаметр. Чтобы произвести это изменение, нужно на вкладке Параметры (рис. 35 а) в группе Значениям соответствует выбрать одно из двух значений: Диаметр пузырьков или Площадь пузырьков. На этой же вкладке находится поле Масштаб пузырьков. С помощью кнопок счетчика в правом конце этого поля можно уменьшить или увеличить размер пузырьков. Разрешены 21 Лекция Построение диаграмм в Excel значения от 0 до 300. При установке значения 0 пузырьки исчезнут. При значении 300 пузырьки увеличатся в три раза по сравнению с их размерами по умолчанию. а) б) Рис. 35. Изменение параметров: а) пузырьковой диаграммы; б) вторичной круговой диаграммы Форматирование вторичной круговой и кольцевой диаграммы Вторичная круговая диаграмма и вторичная гистограмма — это круговая диаграмма, в которой несколько точек данных отображаются на вспомогательной круговой диаграмме (рис. 5) или гистограмме. Вспомогательная диаграмма предоставляет более подробную информацию о некоторой части основной диаграммы. Чтобы преобразовать обычную круговую диаграмму во вторичную круговую диаграмму или гистограмму, нужно выделить любую ее часть и затем в контекстном меню выбрать команду Тип диаграммы. В правой части галереи видов круговой диаграммы нужно найти вторичную круговую диаграмму или вторичную гистограмму. По умолчанию при построении вспомогательной диаграммы Ехсеl использует три последних значения ряда данных, но можно использовать и другие способы разделения значений между основной и вспомогательной диаграммами. Для этого нужно выделить ряд данных во вторичной круговой диаграмме или гистограмме и выбрать команду контекстного меню Формат рядов данных…. После открытия окна диалога нужно воспользоваться вкладкой Параметры. Ряд данных можно разделить по положению (последние n точек данных отойдут к вспомогательной диаграмме), по значению (к вспомогательной диаграмме отойдут все точки данных, значение которых меньше n), по доле (к вспомогательной диаграмме отойдут все секторы, значение которых составляет меньше n процентов от общей суммы). Кроме того, можно выбрать Дополнительно в списке Разделение рядов и затем просто перетащить часть секторов из основной диаграммы во вспомогательную. Параметры настройки для вторичной круговой диаграммы и вторичной гистограммы одинаковы, и единственным их отличием является форма вспомогательной диаграммы. После изменения параметров разделения Ехсеl перерисует основную диаграмму и покажет на ней единый сектор, представляющий все точки данных, отображаемые на вспомогательной диаграмме. По умолчанию Ехсеl рисует линии от этого общего сектора ко всей вспомогательной диаграмме. Можно удалить эти линии, сняв флажок Соединить значения ряда. По умолчанию диаметр вспомогательного круга или высота гистограммы составляет 75 процентов от диаметра основного круга. Изменить размер вспомогательной диаграммы можно с помощью поля 22 Лекция Построение диаграмм в Excel Размер второй части. Кроме того, вспомогательную диаграмму можно приблизить к основной или отдалить от нее, введя меньшее или большее значение в поле Ширина зазора. Изменение ориентации исходных данных. В большинстве случаев Excel принимает правильное решение о том, как размещены ряды данных в ячейках рабочего листа: в строках или в столбцах. При необходимости ориентацию данных можно изменить следующим образом. Для этого нужно установить курсор мыши на область диаграммы, вызвать контекстное меню и выбрать команду Исходные данные. На вкладке Диапазон данных устанавливается переключатель Ряды (шаг 2 Мастера построения диаграмм) в либо в положение Строках, либо в положение Столбцах в соответствии с нужной ориентацией данных. Отделение секторов круга и кольца Круг или кольцо можно разорвать на отдельные секторы. Для этого нужно просто перетащить любой сектор по направлению от центра диаграммы. Чтобы вернуть кругу или кольцу первоначальный вид, следует перетащить сектор назад в центр диаграммы. Чтобы отделить только конкретный сектор круга или кольца в плоской или объемной диаграмме, нужно щелкнуть на этом секторе два раза. Первый щелчок выделит ряд данных, а второй – конкретный сектор. После выделения сектора можно перетащить его в сторону от сектора (рис. 5). Смешивание нескольких типов диаграмм Excel позволяет смешивать несколько различных типов диаграмм (рис. 21). Для этого нужно выделить ряд данных, который будет отображен в другом виде. Затем в контекстном меню воспользоваться пунктом Тип диаграммы…. После открытия окна диалога выбрать в нем нужный тип диаграммы. Форматирование текстовых элементов К текстовым элементам относятся название диаграммы, заголовки осей, текст легенды, подписи делений осей, подписи значений. При форматировании любого из упомянутых элементов диаграммы выполняется следующая последовательность действий: 1) выделить текстовый элемент, 2) вызвать контекстное меню, 3) выбрать команду Формат <текстовый элемент>, где <текстовый элемент> – это заголовок диаграммы, название оси, легенда, ось, подписи данных, 4) перейти на вкладку Шрифт диалогового окна для изменения шрифта, на вкладку Выравнивание – для изменения расположения текста, его ориентации и выравнивания, на вкладку Число – для изменения числового формата подписей делений и значений (рис. 36). Можно осуществить одновременное форматирование всех текстовых элементов. Для этого выделяют область диаграммы и в контекстном меню выбирают команду Формат области диаграммы. Эта команда вызывает диалоговое окно, также предлагающее вкладки Шрифт, Выравнивание и Число. Если щелкнуть один раз на текстовом элементе, Excel выведет вокруг него рамку и маркеры. Выполняемые команды форматирования будут применяться ко всему тексту этого элемента. Чтобы отформатировать только фрагмент текста, например, отдельное слово, после щелчка на элементе, необходимо выделить нужный фрагмент. Чтобы отформатировать конкретную подпись значения, нужно щелкнуть на этой подписи два раза. Первый щелчок выделит все подписи ряда данных, а второй щелчок оставит выделенной только подпись текущего значения. 23 Лекция Построение диаграмм в Excel Рис. 36. Форматирование текстовых элементов Все заголовки, создаваемые мастером диаграмм, состоят из одной строки текста. После создания диаграммы существующий заголовок можно разместить на нескольких строках. Для этого нужно дважды щелкнуть по заголовку диаграммы: один раз, чтобы выделить заголовок, и второй раз, чтобы поместить точку вставки (курсор) в тексте заголовка. Комбинация клавиш + позволит разбить строку на две подстроки. Форматирование фоновых областей Excel предоставляет широкие возможности для форматирования фоновых областей (внутренней заливки) диаграмм, к которым относятся область диаграммы и область построения, а также стенки и основание в объемных диаграммах. Такие же форматы можно применять к легенде, фоновым областям текстовых элементов, таких как название диаграммы, заголовки осей и подписи значений, а также к некоторым типам маркеров данных, включая столбцы, полосы, конусы, пирамиды, цилиндры, области, пузырьки, секторы круга и дольки кольца. Форматирование фоновых областей производится с помощью вкладки Вид (рис. 37). Чтобы вывести эту вкладку на экран, нужно выполнить следующую последовательность действий: 1) выделить элемент, который нужно отформатировать, 2) в контекстном меню выбрать команду Формат <элемент диаграммы>, где <элемент диаграммы> – любой из вышеупомянутых элементов диаграммы, 3) в появившемся диалоговом окне перейти на вкладку Вид. Рис. 37. Форматирование фоновых областей 24 Лекция Построение диаграмм в Excel Группа Рамка вкладки Вид позволяет задать цвет, ширину и тип линии. Можно избавиться от рамки, установив переключатель Невидимая. Кроме того, с помощью соответствующих флажков можно вывести рамку с тенью и/или со скругленными углами. Группа Заливка позволяет выполнить однотонную либо специальную заливку области. Однотонная заливка позволяет закрасить область одним тоном. Для этого нужно щелкнуть на квадрате с понравившемся цветом. Специальная заливка подразумевает следующие способы заливки: градиентная заливка, заполнение области узором, заполнение области текстурой или рисунком. Чтобы выполнить специальную заливку области, нужно воспользоваться кнопкой Способы заливки…, нажатие на которую вызывает вспомогательное диалоговое окно с четырьмя вкладками: Градиентная, Текстура, Узор, Рисунок. При градиентной заливке (вкладка Градиентная) область закрашивается цветом, постепенно изменяющимся в некотором направлении, например, от верхней части столбца к его основанию. Ехсеl предлагает два основных типа градиентной заливки: одноцветную и двухцветную. При заливке одним цветом окраска меняется за счет увеличения или уменьшения яркости. При заливке двумя цветами на одном конце преобладает первый выбранный цвет, а на другом конце доминирует второй цвет. Для двухцветной градиентной заливки можно задать два цвета самостоятельно или выбрать одну из заготовок, предлагаемых Ехсеl. Подобно двухцветной градиентной заливке узор позволяет смешивать два цвета, которые в этом случае называются цветом фона и цветом штриховки. Но в узоре два цвета чередуются в соответствии с повторяющимся геометрическим шаблоном. Заполнение области узором производится на вкладке Узор. Для заполнения области можно использовать рисунки (вкладка Рисунок), сохраненные в разнообразных графических форматах (рис. 38), или применять одно из двенадцати «текстурных» изображений, предлагаемых Excel (вкладка Текстура). Последние имитируют известные материалы, такие как ткань, мрамор или дуб. Рейтинг наиболее популярных женских профессий (2006 г.) Спортсменка 40000 Секретарь 35000 Учитель 30000 Врач 25000 20000 15000 10000 5000 Спортсменка Секретарь Учитель Врач Рис. 38. Использование рисунков при заполнении областей Создание пользовательского типа диаграмм Если пользователь создал законченную диаграмму в соответствии со своими вкусами и требованиями, то можно использовать ее в качестве модели. Чтобы использовать форматы из одной диаграммы в другую, сначала нужно выделить область диаграммы, форматы которой необходимо скопировать. После этого в контекстном меню выбрать команду Копировать, выделить другую диаграмму и затем в контекстном меню выбрать команду Вставить. Если нужно использовать конкретный набор форматов много раз, то лучше всего включить этот набор в список пользовательских типов диаграмм, который появляется в первом окне диалога мастера диаграмм. Для этого следует выполнить следующую последовательность действий. 25 Лекция Построение диаграмм в Excel 1. 2. 3. 4. 5. 6. Отформатировать диаграмму так, чтобы она приняла нужный вид. Выделить эту диаграмму и затем в контекстном меню выбрать команду Тип диаграммы. В окне диалога Тип диаграммы перейти на вкладку Нестандартные. Установить переключатель Вывести в положение Дополнительные. Нажать кнопку Добавить. В окне диалога Добавление нового пользовательского типа диаграммы. типа диаграммы ввести название для 7. Дважды нажать кнопу ОК, чтобы вернуться к диаграмме. Работа с данными диаграммы Диаграммы имеют тенденцию изменяться с течением времени. Появляются новые данные, первоначальные данные устаревают, возникает потребность в новых визуальных сравнениях. Работа с данными диаграммы предполагает добавление новых точек и рядов данных, изменение порядка вывода рядов, отображение многоуровневых категорий информации. Добавление, замена и удаление данных Excel предоставляет несколько способов для включения в диаграмму и исключения из диаграммы данных. При этом данные могут представлять собой точки данных или ряды данных. Следует не забывать о том, что при удалении данных из диаграммы они не удаляются их рабочего листа. Использование маркера заполнения. При выделении области диаграммы Excel всегда рисует цветные прямоугольники вокруг данных, по которым построена диаграмма: один прямоугольник очерчивает исходные данные, используемые для подписей делений на оси категорий, второй – исходные данные ряда или рядов. Чтобы включить в диаграмму или удалить из диаграммы данные, нужно перетащить любой из маркеров заполнения, находящихся в правых нижних углах этих прямоугольников, захватив при этом новые данные или исключив ненужные данные. Команда контекстного меню Исходные данные. Способ предполагает выполнение следующей последовательности действий. 1. Выделить область диаграммы. 2. Выбрать в контекстном меню команду Исходные данные. 3. Скорректировать данные на вкладке Ряд диалогового окна (Шаг 2 Мастера построения диаграмм). 4. Нажать кнопку ОК. Процедура перетаскивания (только для добавления данных). Этот способ предполагает выполнение следующей последовательности действий. 1) Выделить новые данные, включая ячейки, из которых поступает информация для подписей по оси категорий. 2) Поместить указатель мыши на границе выделения (он должен принять форму стрелки). 3) Перетащить выделение и отпустить его в любом месте диаграммы. Копирование и вставка (только для добавления данных). Этот способ заключается в следующем. 1. 2. 3. 4. Выделить новые данные. Выбрать в контекстном меню команду Копировать. Выделить диаграмму. Использовать команду Вставить из контекстного меню. 26 Лекция Построение диаграмм в Excel Команда Диаграмма/Добавить данные (только для добавления данных). При выборе этого способа Excel выведет на экран окно диалога Новые данные. В поле Диапазон этого окна диалога нужно ввести имя диапазона или ссылку на него либо выделить диапазон в рабочем листе с помощью мыши. Клавиша Delete (только для удаления данных). Выделить ряд данных, который нужно удалить из диаграммы, и нажать клавишу . Замена данных. Чтобы отобразить в диаграмме другое множество данных, нужно выделить область диаграммы и перетащить прямоугольник в рабочем листе. В отличие от процедуры добавления новых данных, нужно перетащить не маркер заполнения, а нижнюю или верхнюю границу прямоугольника. Изменение исходных значений с помощью маркеров данных В плоских линейчатых диаграммах, гистограммах, графиках и точечных диаграммах маркеров данных могут использоваться для изменения исходных значений рабочего листа. Для этого следует перетащить вверх или вниз маркеры данных, а Excel подберет и изменит исходные значения в рабочем листе. То есть, вместо того, чтобы возвращаться на рабочий лист и вводить новые значения, можно изменить данные прямо на диаграмме. Этот процесс иногда называют графическим подбором параметра. Чтобы с помощью диаграммы изменить значения в рабочем листе, нужно выполнить следующие действия: 1) выделить маркер данных, который нужно изменить. На нем появятся черные маркеры выделения; 2) установить указатель на верхнем центральном черном маркере. Указатель примет форму перекрестия с двусторонней стрелкой; 3) перетащить этот маркер вверх или вниз; 4) отпустить кнопку мыши, когда значение маркера данных достигнет нужной величины. Excel перерисует диаграмму и обновит значение в связанной ячейке рабочего листа. Если ячейка рабочего листа содержит формулу, а не константное значение, то Excel понимает, что нужно изменить значение ячейки с формулой, но при этом, по-видимому, не следует заменять формулу константой. В этом случае Excel выведет на экран окно диалога Подбор параметра. Добавление тренда Тренд — это функция заданного вида, с помощью которой можно аппроксимировать построенный по данным таблицы график. Тренд служит для выявления тенденций развития процесса, представленного в виде диаграммы, и обеспечивает прогноз на заданный период. 80 70 y = 5,0303x + 7,5333 60 50 40 30 20 10 1 2 3 4 5 6 7 8 9 10 11 12 Рис. 39. Линейный тренд с прогнозом на два периода вперед 27 Лекция Построение диаграмм в Excel В Excel предусмотрено несколько стандартных типов тренда (рис. 40 а)); линейный, логарифмический, степенной, экспоненциальный, полиномиальный, скользящее среднее. Необходимые условия построения тренда:  период времени, за который изучается прогнозируемый процесс, должен быть достаточным для выявления закономерности;  тренд в анализируемый период должен развиваться эволюционно;  процесс, представленный диаграммой, должен обладать определенной инерционностью Тренд можно строить для диаграмм типа:  линейчатый график;  гистограмма;  диаграмма с областями;  XY-точечная диаграмма. Построение тренда осуществляется по следующей технологии:  построить диаграмму для одного ряда данных;  выделить эту диаграмму, щелкнув по ней левой кнопкой мыши. На диаграмме должны появиться маркеры;  вызвать контекстное меню и выполнить команду Добавить линию тренда;  в диалоговом окне Линия тренда на вкладке Тип (см. рис. 40 а) выбрать тип тренда, а на вкладке Параметры (см. рис. 40 б) установить параметры: Количество периодов прогноза, Показывать уравнение на диаграмме, Поместить на диаграмму величину достоверности аппроксимации;  нажать кнопку ОК. Для последующего редактирования линии тренда требуется ее выделение (двойным щелчком левой кнопки мыши), при этом появляются специальные отметки на линии тренда. С помощью правой кнопки мыши можно вызвать контекстное меню, обеспечивающее форматирование (команда Форматировать линию тренда), либо удаление линии тренда (команда Очистить). а) б) Рис. 40. Вкладки Тип (а) и Параметры (б) для задания вида тренда 28