- •Лабораторная работа
- •Построение основных типов графиков и диаграмм в ms Excel.
- •Редактирование гистограмм для визуализации.
- •Условное форматирование и гистограмма в ячейках Excel.
- •В линейчатой диаграмме Excel кликните правой кнопкой мыши по любому ряду данных (полосе) и в контекстном меню нажмите Формат ряда данных (Format Data Series)
- •В появившейся панели в разделе Параметры ряда (Series Options) сделайте одно из следующих действий:
- •1) Выделите диапазон a1:d7.
- •3) Измените подтип диаграммы на с областями и накоплением (Stacked Area). Результат:
- •Объясните, какие типы графиков и диаграмм применяются для тех или иных целей.
- •Есть следующие данные о количестве детей с определённым ростом и ростом их родителей, данные приведены в таблице:
- •Найдите данные и постройте графики следующих зависимостей:
- •Зависимость скорости ферментативной реакции от концентрации субстрата;
- •Вариационную кривую распределения количества семян по их размеру.
Редактирование гистограмм для визуализации.
Для того, чтобы диаграмма имела приличный вид, необходимо её оформление, редактирование. Ознакомимся с некоторыми методами редактирования.
В современных версиях Excel 2013 и 2016 настройка диаграмм - это просто и удобно. Корпорация Microsoft приложила немало усилий, чтобы сделать процесс настройки простым, а необходимые параметры легко доступными.
Доступ к основным инструментам для работы с диаграммами можно осуществить одним из трёх способов:
Выделить диаграмму и использовать вкладки из группы Работа с диаграммами (Chart Tools) - Конструктор (Design) и Формат (Format).
Кликнуть правой кнопкой мыши по элементу диаграммы, который необходимо настроить, и выбрать нужную команду из контекстного меню.
Использовать специальные иконки, которые появляются возле правого верхнего угла диаграммы, если кликнуть по ней мышью.
Ещё больше параметров находится в панели Формат области диаграммы (Format Chart), которая появляется в правой части рабочего листа, если нажать пункт Дополнительные параметры (More options) в контекстном меню диаграммы или на вкладках группы Работа с диаграммами (Chart Tools).
Совет: Чтобы сразу же открыть нужный раздел панели для настройки параметров диаграммы, дважды кликните по соответствующему элементу на диаграмме.
Добавление названия диаграммы и его редактирование. Чтобы добавить название к диаграмме в Excel 2010 и более ранних версиях, выполните следующие действия:
Кликните в любом месте диаграммы Excel, чтобы на Ленте меню появилась группа вкладок Работа с диаграммами (Chart Tools).
Microsoft Excel Insert Page Layout
Data
Review
view
Formulas Design
Layout
Format
chart Г
Chart
chart
Title w
N о n e
Data
Labe s T Tab e T
Axes Gridlines Trendline Lines Up/Down Error
Axes
bars
bars
Analysis
Prop
На вкладке Макет (Layout) нажмите Название диаграммы (Chart Title) > Над диаграммой (Above Chart) или По центру (наложение) (Centered Overlay).
№
Irb
Centered Overlay Title
Overlay centered Title on chart without resizing chart
Above Chart
Display Title at top of chart area and resize chart
More Title Options
Диаграммы различных типов в Excel чаще всего создаются с замещающим текстом вместо названия. Чтобы задать собственное название для диаграммы, можно либо выделить поле диаграммы и ввести текст вручную, либо привязать его к какой-либо ячейке рабочего листа, содержащей, например, название таблицы. В таком случае название диаграммы Excel будет автоматически обновляться каждый раз, когда будет изменяться содержимое связанной ячейки.
Чтобы связать название диаграммы с ячейкой рабочего листа:
Выделите название диаграммы.
В строке формул введите знак равенства (=), кликните по ячейке, содержащей нужный текст, и нажмите Enter.
В данном примере мы связываем название диаграммы Excel с ячейкой A1. Можно выбрать две или более ячеек (например, несколько заголовков столбцов), и в результате в названии диаграммы будет показано содержимое всех выбранных ячеек.
Если нужно переместить название диаграммы в другое место, выделите его и перетащите при помощи мыши:
Чтобы изменить шрифт названия диаграммы в Excel, кликните по нему правой кнопкой мыши и нажмите Шрифт (Font) в контекстном меню. Откроется одноименное диалоговое окно, в котором можно настраивать различные параметры шрифта.
300
250
200
150
100
а
Styl
if?
I
I
IS
□
P
De
ete
Reset
Exit
Edit
Ch
an
Se
ect
[у
Out
r-lstcri
Text
Chart
Data
Sty
I
Tvp
3-D
Rotation... Format Chart Titl
Если нужны более детальные настройки, выделите название диаграммы, откройте вкладку Формат (Format) и поиграйте различными параметрами. Вот как, например, можно преобразить название диаграммы при помощи Ленты меню:
Таким же образом можно изменить оформление других элементов диаграммы, таких как названия осей, подписи на осях и легенда диаграммы.
Редактирование осей диаграммы и их названий. Для большинства типов диаграмм в Excel вертикальная ось (она же - ось значений или ось Y) и горизонтальная ось (она же - ось категорий или ось Х) добавляются автоматически при создании диаграммы.
Чтобы скрыть или показать оси диаграммы, кликните по иконке Элементы диаграммы (Chart Elements), затем нажмите стрелку в строке Оси (Axes) и отметьте галочкой те оси, которые нужно показать, или уберите галочку рядом с теми, которые нужно скрыть.
Для некоторых типов диаграмм, например, для комбинированных диаграмм, может быть показана вспомогательная ось.
При
создании объёмных диаграмм, можно
отобразить ось глубины:
Создавая диаграмму в Excel, можно добавить названия вертикальной и горизонтальной осей, чтобы пользователям было проще понять, что за данные показаны на диаграмме. Чтобы добавить названия осей, нужно сделать вот что:
Кликните в любом месте диаграммы Excel, затем нажмите по иконке Элементы диаграммы (Chart Elements) и поставьте галочку в строке Названия осей (Axis Titles). Если нужно показать название только для одной из осей (либо вертикальной, либо горизонтальной), нажмите стрелку справа и уберите одну из галочек.
Кликните на диаграмме по текстовому полю названия оси и введите
текст.
Чтобы настроить оформление названия оси, кликните по нему правой кнопкой мыши и в контекстном меню нажмите Формат названия оси (Format Axis Title). При этом откроется одноимённая панель с большим выбором настраиваемых параметров оформления. Можно также использовать параметры, предложенные на вкладке Формат (Format) Ленты меню, как мы делали при настройке параметров названия диаграммы.
Как и название диаграммы, название оси можно связать с заданной ячейкой рабочего листа при помощи ссылки, чтобы это название обновлялось автоматически при изменении данных в связанной ячейке.
Чтобы создать такую ссылку, выделите название оси и в строке формул введите знак равенства (=), затем кликните по ячейке, с которой нужно связать название оси, и нажмите Enter.
Microsoft Excel автоматически определяет минимальное и максимальное значения, а также единицы измерения для вертикальной оси, основываясь на том, какие данные использованы для построения диаграммы. При необходимости для вертикальной оси можно настроить собственные более подходящие параметры.
Выделите вертикальную ось диаграммы и щелкните по иконке Элементы диаграммы (Chart Elements).
Нажмите стрелку в строке Оси (Axis) и в появившемся меню выберите Дополнительные параметры (More options). Откроется панель Формат оси (Format Axis).
В разделе Параметры оси (Axis Options) сделайте одно из следующих действий:
Чтобы задать начальное и конечное значения вертикальной оси, введите соответствующие значения в поля Минимум (Minimum) или Максимум (Maximum).
Чтобы изменить масштаб оси, введите значения в поля Основные деления (Major) и Промежуточные деления (Minor).
Чтобы отложить значения по оси в обратном порядке, отметьте галочкой параметр Обратный порядок значений (Values in reverse order).
Если нужно, чтобы числа в подписях оси отображались как валюты, проценты, время или в каком-либо другом формате, кликните по подписям правой кнопкой мыши и в контекстном меню нажмите Формат оси (Format Axis). В открывшейся панели перейдите в раздел Число (Number) и выберите один из доступных числовых форматов:
Совет: Чтобы установить для чисел формат исходных данных (тот, что в ячейках рабочего листа), поставьте галочку Связь с источником (Linked to source). Если не получается найти раздел Число (Number) в панели Формат оси (Format Axis), проверьте, что на диаграмме выделена ось значений (это, как правило, вертикальная ось).
Добавление подписи данных в диаграмме. Чтобы сделать график в Excel более понятным, добавьте подписи данных, которые будут показывать подробную информацию о ряде данных. В зависимости от того, на что нужно обратить внимание пользователей, можно добавить подписи к одному ряду данных, ко всем рядам или к отдельным точкам.
1) Кликните по ряду данных, для которого нужно добавить подписи. Чтобы добавить подпись только к одной точке данных, кликните ещё раз по этой точке.
Нажмите по иконке Элементы диаграммы (Chart Elements) и поставьте галочку в строке Подписи данных (Data Labels).
Например, вот так выглядит наша диаграмма Excel с подписями для одного из рядов данных.
В некоторых случаях можно выбрать, как будут располагаться подписи. Для этого нажмите стрелку в строке Подписи данных (Data Labels) и укажите подходящий вариант. Чтобы показать подписи внутри плавающих текстовых полей, выберите Выноска данных (Data Callout).
zzz
15:
i::
Jur
Fruit
Sales
Apples
I Apples
Oct
□ranges
■ Apples ■ Lemons
Fruit Sa es
25Z
2ZZ
15:
Jun
Oranges ■ Apples ■Lemons
Format Data Labels
LABEL OPTIONS ▼ TEXT OPTIONS
<2* 0 ih ill
^ LABEL OPTIONS
Label Contains
Value From Cells 0 Series Name E Category Name 0 Value
/ 0 Show Leader Lines
E Legend key Separator ,
Reset Label Text
> NUMBER
Чтобы изменить содержание подписей данных на диаграмме, кликните по иконке Элементы диаграммы (Chart Elements) > Подписи данных (Data Labels) > Дополнительные параметры (More Options). Откроется панель Формат подписей данных (Format Data Labels) в правой части рабочего листа. На вкладке Параметры подписей (Label Options) в разделе Включать в подпись (Label Contains) выберите нужное из предложенных вариантов.Если нужно добавить собственный текст к одной из точек данных, кликните по подписи этой точки, затем кликните ещё раз, чтобы выделенной осталась только эта подпись, и ещё раз по тексту подписи, чтобы выделить его. Далее введите собственный текст.
Если окажется, что слишком большое число подписей перегружает диаграмму Excel, то можно удалить любую из них. Кликните по подписи правой кнопкой мыши и в контекстном меню нажмите Удалить (Delete).
Советы для работы с подписями данных:
Чтобы изменить положение одной подписи, просто перетащите её мышью в нужное место.
Чтобы изменить цвет шрифта и заливку подписей данных, выделите их, затем откройте вкладку Формат (Format) и настройте нужные параметры форматирования.
Редактирование формата легенды диаграммы. При создании диаграммы в Excel 2013 и Excel 2016 легенда по умолчанию добавляется в нижней части области диаграммы. В Excel 2010 и более ранних версиях - справа от области построения.
Чтобы убрать легенду, нажмите иконку Элементы диаграммы (Chart Elements) возле правого верхнего угла диаграммы и уберите галочку в строке Легенда (Legend).
Чтобы переместить легенду диаграммы в другое место, выделите диаграмму, откройте вкладку Конструктор (Design), нажмите Добавить элемент диаграммы (Add Chart Element) > Легенда (Legend) и выберите новую позицию для легенды. Чтобы удалить легенду, нажмите Нет (None).
Ещё один способ переместить легенду - кликнуть по ней дважды мышью и выбрать нужное положение в разделе Параметры легенды (Legend Options) панели Формат легенды (Format Legend).
Format Legend
LEGEND
OPTIONS ▼
TEXT OPTIONS
25:
2:;
is:
■V
-
* LEGEND OPTIONS
Legend Position
Top
* Bottom
Fruit Sa es
Oct
Right
Oranges ■ Apples ■Lemons
_ Top Right
Для настройки форматирования легенды существует множество параметров на вкладках Заливка и границы (Fill & Line) и Эффекты (Effects) панели Формат легенды (Format Legend).
Параметры сетки на диаграмме. В Excel 2013 и 2016 показать или скрыть сетку - дело нескольких секунд. Просто кликните по иконке Элементы диаграммы (Chart Elements) и поставьте или уберите галочку в строке Сетка (Gridlines).
Microsoft Excel автоматически определяет, какие линии сетки лучше подойдут для данного типа диаграммы. Например, для линейчатой диаграммы будут показаны основные вертикальные линии, в то время как для гистограммы - основные горизонтальные линии сетки.
CHART
ELEMENTS
0
Axes
Axis
Titles
I
Chart Title
I
I Data Labels 1 Data Table
Primary
Major Horizontal Primary Major Vertical Primary Minor Horizontal
Primary Minor Vertical
More
Options...
Редактирование рядов данных. Когда на диаграмме Excel показано очень много данных, то иногда требуется на время скрыть часть рядов, чтобы сфокусировать внимание на нужных в данный момент.
Для этого справа от графика нажмите иконку Фильтры диаграммы (Chart Filters) и уберите галочки с тех рядов данных и/или категорий, которые нужно скрыть.
Для редактирования ряда данных нажмите кнопку Изменить ряд (Edit Series) справа от его названия. Кнопка появляется, если навести мышь на название этого ряда. При этом соответствующий ряд будет выделен на графике, так что Вы легко увидите, какой именно элемент будет отредактирован.
Изменение типа, цвета и стиля диаграммы. Если созданная диаграмма не лучшим образом подходит для отображаемых данных, Вы можете без труда изменить тип диаграммы. Для этого выделите диаграмму, откройте вкладку Вставка (Insert) и в разделе Диаграммы (Charts) выберите другой тип диаграммы.
Другой способ - кликните правой кнопкой мыши в любом месте диаграммы и в контекстном меню нажмите Изменить тип диаграммы (Change Chart Type).
Для того, чтобы быстро изменить стиль созданной диаграммы, нажмите иконку Стили диаграмм (Chart Styles) справа от области построения и выберите из предложенных стилей подходящий.
Или выберите один из стилей в разделе Стили диаграмм (Charts Styles) на вкладке Конструктор (Design):
CHART
TOOLS
PAGE
LAYOUT
DATA
REVIEW
VIEW
FORMULAS
DESIGN FORMAT
Чтобы изменить цветовую тему диаграммы в Excel, нажмите иконку Стили диаграмм (Chart Styles), откройте вкладку Цвет (Color) и выберите одну из предложенных цветовых тем. Выбранные цвета тут же будут применены к диаграмме, и Вы сразу же сможете оценить хорошо ли она выглядит в новом цвете.
Чтобы выбрать цвет для каждого ряда индивидуально, выделите ряд данных на диаграмме, откройте вкладку Формат (Format) и в разделе Стили фигур (Shape Styles) нажмите кнопку Заливка фигуры (Shape Fill).
Замена мест X и Y местами. При создании диаграммы в Excel ориентация рядов данных определяется автоматически, исходя из количества строк и столбцов исходных данных, по которым строится график. Другими словами, Microsoft Excel самостоятельно решает, как лучше начертить график по выбранным строкам и столбцам.
Если выбранное по умолчанию расположение строк и столбцов на графике не устраивает, то можно без труда поменять местами горизонтальную и вертикальную оси. Для этого выделите диаграмму и на вкладке Конструктор (Design) нажмите кнопку Строка/Столбец (Switch Row/Column).
Поворот диаграммы. Случалось ли Вам создать диаграмму в Excel и только в самом конце понять, что точки данных расположены в порядке противоположном тому, который Вы хотели получить? Чтобы исправить такую ситуацию, нужно развернуть порядок построения категорий в диаграмме, как показано далее.
Кликните правой кнопкой мыши по горизонтальной оси диаграммы и нажмите Формат оси (Format Axis) в контекстном меню.
Change Chart Type,,, Select Data,,,
Jun
Jul
Aug
Sep
■ranges Apples Lemon?
Delete
Reset to Match Style Font...
3-D Rotation.,,Add Major Gridlines Add Minor Gridlines Format Axis...
HOME
INSERT
PAGE
LAYOUT
*ee
Lh
Qu
Lh
E
ement
Co
Axes
onzc
ntal
Job
T
itl
Chart
Tit
ry
Vert
Data
Data
Tab e
В любом случае появится панель Формат оси (Format Axis), где на вкладке Параметры оси (Axis Options) нужно отметить галочкой параметр Обратный порядок категорий (Categories in reverse order).
Format Axis
W
X
Y
Z
Fruit
Sales
250
inn
Oranges
Apples В
Jun
BJul l Aug В
1"
Lemons Sep
В
Oct
Fruit
Sales
250
■
Jun
■ JuI ■ Aug iSep
Ul
I I L-^| | fr,
1 1 =g=
^ О ih ill ^ AXIS OPTIONS
Axis Type
Automatically select based on data
Text axis О Date axis Vertical axis crosses
+ Automatic
At category number
At maximum category Axis position
0 Categories in reverse order
P TICK MARKS P LABELS > NUMBER
Помимо разворота диаграммы в Excel слева направо, можно изменить порядок категорий, значений или рядов данных на диаграмме, развернуть порядок построения точек данных, повернуть круговую диаграмму на любой угол и многое другое.
Задание: Предшественники овощных, ягодных или плодовых культур - это растения, что росли на тех же самых грядах в прошлом сезоне. Данные, полученные в Научно-исследовательском институте имени В.В. Докучаева при испытании гречихи сорта «Богатырь» на урожайность в зависимости от предшественника, приведены в таблице:
Предшественники
Урожай
гречихи по повторностям, ц/га
Средний урожай
1
2
3
Чечевица
23,6
25,1
21,1
23,2
Чина
степная
26,7
23,2
23,8
24,6
Ячмень
26,0
24,9
25,3
25,4
Горох
23,7
20,1
20,5
21,4
Какой из этих предшественников самый лучший? Сделайте гистограмму (линейную и пространственную) для этих данных и оформите её соответствующим образом, используя правила редактирования диаграмм, сделайте цветовое оформление, удалите сетку, подпишите оси, подпишите данные, оформите названия диаграммы, рядов и их оформление.