Упорядочение данных на листе Excel. Вставка и построение

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

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

Как построить комбинированную диаграмму в Excel

Способы построения комбинированной диаграммы в Excel:

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

Создадим таблицу с данными, которые нужно отобразить на комбинированной диаграмме.

Выделим столбцы диапазона, включая заголовки. На вкладке «Вставка» в группе «Диаграммы» выберем обычный «График с маркерами».

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

Каким образом можно комбинировать разные типы диаграмм? Щелкнем правой кнопкой мыши «К-во, шт.». В открывшемся окне выберем «Изменить тип для ряда».

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

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

Выделим гистограмму, щелкнув по ней мышкой. Перейдем на вкладку «Макет». Группа «Текущий фрагмент» - инструмент «Формат выделенного фрагмента».

Откроется окно «Формат ряда данных». На вкладке «Параметры ряда» поставим галочку напротив «Построить ряд по вспомогательной оси».

Нажимаем кнопку «Закрыть».

Поработаем над внешним видом комбинированной диаграммы. Выделим область построения и перейдем на вкладку «Конструктор». Поменяем стиль. Удалим легенду (выделить – Delete). Добавим название и подписи вертикальных осей.

Для основной и вспомогательной оси выбираем вариант расположения (отдельно для каждой) и вводим подпись. Жмем Enter.

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

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

Изменение для одного ряда данных типа диаграммы

Создадим смешанную диаграмму путем изменения для одного из рядов типа.

Таблица с исходными данными:

Построим обычную гистограмму на основе двух рядов данных:

Выделим столбики гистограммы, отображающие плановые показатели. На вкладке «Конструктор» в группе «Тип» нажмем кнопку «Изменить тип диаграммы». Выберем из предложенных вариантов «С областями».

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

Внимание! Не все виды диаграмм можно комбинировать. Нельзя объединять некоторые объемные типы, пузырьковые с другими диаграммами. Программа Excel при невозможных комбинациях выдает ошибку.

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

Лабораторная работа

РЕДАКТОР ЭЛЕКТРОННЫХ ТАБЛИЦ MICROSOFT EXCEL 2007

ДИАГРАММЫ И ГРАФИКИ

Цель работы:

1. Изучить возможности редактора электронных таблиц Excel 2007.

2. Научиться строить диаграммы и графики при помощи Microsoft Excel 2007.

Задание:

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

Методические указания

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

Примечание. В Excel графики представлены как частный случай диаграмм.

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

Созданная диаграмма отобразится в рабочем листе, после чего станет доступна контекстная вкладка Работа с диаграммами (рис. 1) с тремя наборами инструментов (вкладками) для изменения диаграмм:

Рис. 1. Вкладка Работа с диаграммами

· Конструктор – содержит параметры, определяющие тип диаграммы, источник даныx и их упорядочивание, макеты диаграмм, а также команду Переместить диаграмму ;

· Макет - позволяет указывать свойства диаграмм, добавлять или редактировать элементы диаграммы, а также выбирать параметры, связанные с трехмерными графиками;

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

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

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

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

После указания исходных данных необходимо перейти на вкладку Вставка и выбрать тип будущего изображения (рис. 2). Например, в группе команд Диаграммы воспользоваться командой Гистограмма ç Гистограмма с группировкой .

Рис. 2. Выбор типа диаграммы

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

· широкая линия вокруг области диаграммы;

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

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

Рис. 3 . Элементы диаграммы

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

На рис. 3 показаны основные элементы диаграммы, которые можно изменять, добиваясь максимальной наглядности и удобства ее восприятия.

Изменение диапазона исходных данных

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

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

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

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

Рис. 4. Настройка источника данных

С целью формирования рядов данных необходимо воспользоваться областью Элементы легенды (Ряды) (см. рис. 4) рассматриваемого диалогового окна. Здесь можно выполнить детальную настойку рядов данных, добавление новых (кнопка Добавить ) или удаление имеющихся (кнопка Удалить ).

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

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

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

В поле Подписи горизонтальной оси (Категории) вводятся названия категорий, представляющие собой подписи по оси X (см. рис.4). Для их ввода удобно нажатием на кнопку Изменить предварительно свернуть окно Мастера (при этом отобразится окно Подписи оси , представлено на рис. 6) и выделить на рабочем листе диапазон, который содержит соответствующие данные. В рассматриваемом случае это значения x .

Рис. 5 Настройка ряда данных

Рис. 6 Настройка подписи по оси

Настройка параметров диаграммы

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

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

Рис. 7 Фрагмент вкладки Макет

· Название диаграммы - позволяет указать расположение название для диаграммы (над диаграммой или по центру с перекрытием). Устанавливается при помощи одноименной кнопки, расположенной в группе команд Подписи ;

· Название осей - служит для задания расположения (горизонтальное, вертикальное, повернутое) название осей диаграммы. Выполняется при помощи одноименной кнопки, расположенной в группе команд Подписи ;

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

· Оси и Сетка - эти команды позволяют настроить отображение подписей для осей и линий сетки для диаграммы одноимённых кнопок, расположенных в группе команд Оси;

· Формат выделенного фрагмента - служит для вызова окна настройки формата того элемента диаграммы, который активен в данный момент (например, Формат ряда данных, Формат оси, Формат области построения). Команда вызывается одноименной кнопкой, расположенной в группе команд Текущий фрагмент на вкладке Ма кет ;

· группа команд Макеты диаграмм - предназначена для выбора пользователем определенного макета построения диаграммы, по которому диаграмма будет наиболее информативна. Каждый макет имеет свои настройки отображения рядов данных, расположения названий осей и диаграммы, линий сетки, наличия подписей данных и пр. Группа команд Макеты диа грамм расположена на вкладке Конструктор.

· группа команд Стили диаграмм - служит для выбора стиля отображения диаграммы (цветового оформления) и расположена на вкладке Конструктор ;

· Переместить диаграмму - предназначается для вызова одноименного диалогового окна, при помощи которого можно изменить расположение диаграммы: разместить в виде отдельного листа или поместить на выбранный лист редактора (см. рис. 8).

Рис. 8. Окно Перемещение диаграммы

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

Операция форматирования для любых объектов выполняется по следующей схеме:

1. Для выбранного объекта щелчком правой кнопки мыши вызвать контекстное меню.

2. Выбрать команду форматирования (например, Формат оси) (Рис. 9).

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

Рис. 9. Диалоговое окно Формат оси диаграммы

Быстрое изменение исходных данных

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

Чтобы продлить диапазон исходных данных, следует совершить такие действия:

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

2. Подвести указатель к маркеру диапазона значений.

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

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

Типы диаграмм

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

В Excel используется следующие типы диаграмм:

· Гистрограммы;

· Графики;

· Круговые диаграммы;

· Диаграммы с областями;

· Биржевые диаграммы;

· Поверхностные диаграммы;

· Кольцевые диаграммы;

· Пузырьковые диаграммы;

· Лепестковые диаграммы.

Гистограммы

В диаграммах этого типа ось OX, или ось категорий, располагается горизонтально, ось OY – ось значений – вертикально. Гистограмма имеет семь подтипов (рис. 10), из которых всегда можно выбрать наиболее подходящий вид для графического отображения имеющихся данных:

· обычная – отображает значения различных категорий;

· с накоплением – отображает вклад каждой категории данных в общую сумму;

· нормированная – отображает долю (в %) каждой категории данных в общей сумме;

· обычная в объемном виде;

· с накоплением в объемном виде;

· нормированная в объемном виде;

· трехмерная – отображает раскладку значений по категориям и рядам данных.

Рис. 10. Фрагмент окна Вставка диаграммы

Круговые диаграммы

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

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

Графики

Графики обычно используют, когда требуется определить характер поведения какой-либо функции или процесса. Excel позволяет строить 7 типов графиков, разбитых на 3 категории: график, график с маркерами и объемный график.

Для примера рассмотрим построение графика функции y = sin(x ) на интервале одного полного периода. Для этого нужно построить таблицу, содержащую столбец аргументов и столбец значений функции. После построения графика по заданным точкам без использования сглаживания будет получена некоторая ломаная (рис.11).

Для того чтобы Microsoft Excel 2007 отобразил на экране плавную линию, следует включить режим сглаживания. Для этого следует выделить построенный график и вызвать команду Формат ряда данных контекстного меню. В открывшемся окне на вкладке Тип линии следует выбрать опцию сглаженная линия и нажать клавишу Закрыть. В результате график функции синуса примет вид, представленный на рис.12.

Рис.11. График функции y = sin(x ) без сглаживания

Рис. 12. График функции y = sin(x ) со сглаживанием

Задание

Построение двумерных графиков в Excel 2007

1. Построить следующие графики функций: y = f (x ), z = f (x ); нанести сетку, легенду, название графика и осей координат (варианты заданий приведены в табл. 1);

2. По графику сделать вывод о наличии экстремума (минимума или максимума) исследуемой функции.

Таблица 1

Как добавить в диаграмме, например, в круговой, к каждому сегменту его название? А еще помню, оно как-то автоматически высчитало проценты и отображало их, как это сделать?

Во всех диаграммах Excel есть возможность добавить подписи к данным. Для этого необходимо в кликнув правой кнопки мышки на объекте (в данном случае непосредственно на круговой диаграмме) в контекстом меню выбрать Формат подписи данных…

В появившемся диалоговом окре вы можете выбрать, чтобы отображались:

• Имя ряда - название столбца, из которого берутся данные.
• Имена категорий - это имена данных, которые отображаются. Они же отображаются в легенде.
• Значение - это значения категорий, на основании которых и построена данная диаграмма.
• Доли - выводит процентное соотношение между всеми частями диаграммы.
• Линии выноски - если вы выносите подписи данных за пределы рисунка диаграммы, то автоматически устанавливаются линии, которые связывают подпись с ее изображением на диаграмме.

Вы можете выбрать, как одну из этих подписей, так и все вместе.

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

Чтобы добавить подпись данных только к одному элементу диаграммы, нужно дважды кликнуть на необходимой подписи правой кнопки мышки. Первый клик - выделяет все подписи, второй - ту на которой установлен курсор.

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

Как мне укрупнить шкалу на диаграмме?

С помощью меню Работа с диаграммами - Макет в группе Оси нажмите на кнопку Оси . Здесь вы можете выбрать ось, на которой вам нужно изменить деление шкалы. Перейдя в меню вертикальной или горизонтально оси вам будут предложены варианты автоматического укрупнения оси до Тысячи, Милионы и т.д. Если они вас не устраивают, выберите пункт Дополнительные параметры основной вертикальной/горизонтальной оси…

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

Как на графике изменить маркер (например, я хочу не квадрат, а кружочек)?

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

Или же на вкладке Макет в группе Текущий фрагмент сначала выберите необходимый элемент из выпадающего списка Элементы диаграммы , а затем нажмите кнопку Формат выделенного фрагмента . Данный способ выбора элементов диаграммы очень удобный, если у вас много линий и они тесно переплетаются друг с другом.

В диалоговом окне Формат ряда данных в разделе Параметры маркера можно выбрать тип и размер маркера:

Как мне добавить еще одну ось на график? У меня один показатель значительно отличается от других, но все должны быть на одной диаграмме. :(

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

Или на вкладке Формат в группе Текущий фрагмент из выпадающего списка в поле Элементы диаграммы выберите ряд данных, который нужно отобразить на вспомогательной вертикальной оси. Затем кликните здесь же на кнопке Формат выделенного фрагмента…

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

На вкладке Макет в группе Оси у вас появится пункт Вспомогательная вертикальная ось , с помощью которого вы сможете форматировать ее точно так же как и обычную ось.

Хотите создать круговую диаграмму, но не знаете, как это сделать? Не беда. Всё делается очень просто. Вам нужно будет следовать дальнейшей инструкции и всё у вас обязательно получится. Рассмотрим пример. Для начала откройте программу Excel 2007. И заполните нужное число ячеек.

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

Следующим шаг – переход во вкладку Вставка | Диаграммы

Из раздела диаграмм выбираем круговую.

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

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

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

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

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

Если необходимо, можно также добавить подписи данных.

Также можно менять название диаграммы, по умолчанию, после построения диаграммы, её название выбирается автоматически по наименованию столбца. Для того чтобы поменять её название необходимо два раза щелкнуть по имеющейся надписи, в данном случае по надписи «Продано. Кг», правой кнопкой мыши.

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

После нажатия пункта «Формат области построения...», можно задавать дополнительные визуальные параметры таблицы: заливка, цвет границы, стили границ, тень, формат объемной фигуры, поворот объемной фигуры.

Можно задавать тень, объемность рамки, изменять положение отдельных секторов диаграммы и т.д. Для примера попробуем изменить расположение нашей диаграммы. Для этого необходимо выбрать в данном меню пункт «Поворот объемной фигуры».

Как показано на рисунке, с изменением координат x, y и перспективы можно изменять положение диаграммы.

Также после создания диаграммы, если необходимо можно поменять её тип, для этого необходимо нажать по диаграмме правой кнопкой мыши и выбрать пункт «Изменить тип диаграммы…». После нажатия появится следующее меню:

В данном меню имеется больший выбор различных диаграмм. В данном меню имеется большой выбор различных диаграмм.

В этой статье мы рассмотрим методы создания и использования сводной диаграммы.

Сводная диаграмма - это диаграмма, которая автоматически объединяет и обобщает большие массивы данных. Построить сводную диаграмму можно как на основе простой, так и сводной таблицы.

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

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

1. Выберите необходимую Вам сводную таблицу, кликнув по ней;

2. На вкладке Вставка в группе Диаграммы выберите необходимый тип диаграммы.

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

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

Окно Область фильтра сводной таблицы предназначено для удобного управления сводной таблицей и диаграммой, построенной на её основе:

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

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

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

1. Выделить нужный нам диапазон данных (или установить курсор на нужную нам таблицу - тогда Excel автоматически подставит всю таблицу в диапазон данных);

2. На вкладке Вставка в группе Таблицы выбрать раздел Сводная таблица , а затем пункт Сводная диаграмма .