Занятие 6. Создание
диаграмм
MS Excel от новичка до профессионала
Николай Колдовский
msoffice-prowork.com
Slide 2
Slide 2 text
Содержание
1. Основы работы с диаграммами (графиками)
2. Выбор правильного типа диаграммы для
визуализации данных
3. Быстрое форматирование диаграмм с
помощью стилей и макета
4. Форматирование диаграмм в ручном режиме
5. Инструменты быстрого анализа
Slide 3
Slide 3 text
Основы работы с диаграммами
MS Excel от новичка до профессионала
Занятие 6.1
Slide 4
Slide 4 text
Основы работы с диаграммами
Команды вставки диаграмм находятся на вкладке "Вставка"
Рекомендуемые диаграммы, Excel попытается
подобрать наиболее подходящий тип исходя из
выделенного диапазона данных
Различные типы
диаграмм по группам
Группа мини-графиков
(Спарклайнов)
Slide 5
Slide 5 text
Основы работы с диаграммами
С помощью диалогового
окна "Вставка
диаграммы" можно
удобно выбрать тип и
подтип диаграммы
Вызов окна
«Вставка
диаграммы»
Slide 6
Slide 6 text
Основы работы с диаграммами
(дополнительная вкладка Конструктор)
Перемещение диаграммы
на новый лист или
существующий
Изменение типа диаграммы
(гистограмма, график, 3D и
т.д.)
Комплексное изменение цветовой
гаммы диаграммы или
применение одного из стилей
Управление данными
диаграммы
Добавление/ удаление
элементов диаграммы или
применение готового макета
Slide 7
Slide 7 text
Основы работы с диаграммами
(дополнительная вкладка Формат)
Управление размерами
диаграммы или элементами
Упорядочивание
элементов
Форматирование
отдельных элементов
(ряды данных, подписи)
Добавление фигур
Выделение элемента диаграммы для последующего
форматирования (выделить можно и кликом левой клавиши
мышки, но, иногда бывает трудно попасть по нужному элементу)
Slide 8
Slide 8 text
Основы работы с диаграммами (источники
данных)
0%
50%
100%
150%
200%
- ₽
5,00 ₽
10,00 ₽
15,00 ₽
20,00 ₽
25,00 ₽
30,00 ₽
1 2 3 4 5 6 7 8 9 10 11 12
Продажи техники и аксессуаров
Доход с продаж мобильной техники тыс. руб.
Доход с продаж акксесуаров тыс. руб.
План по продажам акксесуаров тыс. руб.
В % к периоду прошлого года
В % к периоду прошлого года
Данные по продажам мобильной
техники находятся на листе с
диаграммой
Данные по аксессуарам
находятся на другом листе
Данные для плана
находятся в другой книге
Slide 9
Slide 9 text
Основы работы с диаграммами
Excel не просто правильно подобрал
правильный тип диаграммы
(комбинированная с дополнительной
осью), но и в самой легенде
совместил название из разных ячеек
(«Доход с продаж аксессуаров» и к
2014 году и к 2015-му)
Данные для диаграммы можно выделять с подписями строк и столбцов
Slide 10
Slide 10 text
Основы работы с диаграммами (настройка
размеров диаграммы)
Грубая настройка размеров –
потянуть за край области
диаграммы
Точная настройка указать
ширину и высоту в
абсолютных единицах, либо
в процентах от изначальной
диаграммы
Если выделить несколько
диаграмм и задать размеры,
то они все станут одинакового
размера
Slide 11
Slide 11 text
Основы работы с диаграммами
Если Excel неправильно
определил ряды данных, или
на данные нужно взглянуть
под другим углом, то в один
клик можно поменять строки
и столбцы местами
Slide 12
Slide 12 text
Выбор правильного типа диаграммы для
визуализации данных
MS Excel от новичка до профессионала
Занятие 6.2
Slide 13
Slide 13 text
Выбор правильного графика для
визуализации данных
- ₽
10,00 ₽
20,00 ₽
30,00 ₽
Январь
Февраль
Март
Апрель
Май
Июнь
Июль
Август
Сентяб…
Октябрь
Ноябрь
Декабрь
Доход с продаж
мобильной техники
тыс. руб.
Динамика продаж
2015 год В % к периоду прошлого года
- ₽
10,00 ₽
20,00 ₽
30,00 ₽
Январь
Февраль
Март
Апрель
Май
Июнь
Июль
Август
Сентяб…
Октябрь
Ноябрь
Декабрь
Доход с продаж
мобильной техники
тыс. руб.
Динамика продаж
2015 год
0%
50%
100%
150%
Динамика продаж
В % к периоду прошлого года
0%
50%
100%
150%
- ₽
10,00 ₽
20,00 ₽
30,00 ₽
Январь
Март
Май
Июль
Сентяб…
Ноябрь
В % к периоду
прошлого года
Доход с продаж
мобильной техники
тыс. руб.
Динамика продаж
2015 год В % к периоду прошлого года
1. Проблемы с масштабом
2. Две диаграммы и не лучший тип для
отображения динамики
3. Комбинированная диаграмма лучший вариант
Slide 14
Slide 14 text
Выбор правильного графика для
визуализации данных. Как выбрать?
Тип диаграммы зависит от сообщения, которое она должна
передать
Подойдет для
сравнительного анализа
доходности продуктов
Подойдет, если необходимо
определить долю продукта в
общем доходе компании
Slide 15
Slide 15 text
Выбор правильного графика для
визуализации данных
Гистограммы и линейчатые
$-
$5 000,00
$10 000,00
$15 000,00
$20 000,00
$25 000,00
январь февраль март апрель май
Доход
Доход за 5 месяцев
Продукт1 Продукт2 Продукт3
Продукт4 Продукт5 Продукт6
$- $5 000,00
$10 000,00
$15 000,00
$20 000,00
$25 000,00
январь
февраль
март
апрель
май
Доход
Доход за 5 месяцев
Продукт6 Продукт5 Продукт4
Продукт3 Продукт2 Продукт1
Slide 16
Slide 16 text
Выбор правильного графика для
визуализации данных
Круговые диаграммы – если надо указать пропорции
относительно целого
41667
1610
445
415
76
68
44
39
37
36
33
3258
6061
Устройство
Apple iPhone
Apple iPad
Не определено
Google Nexus 5
Samsung GT-I9500 Galaxy
S IV
Samsung SM-G900F Galaxy
S5
Samsung GT-I9190 Galaxy
S4 Mini
87%
13%
Просмотры сайта msoffice-prowork.com
различными устройствами
Десктоп Мобильные
Slide 17
Slide 17 text
Количество просмотров
Десктоп Мобильные
Apple iPhone Apple iPad
Не определено Google Nexus 5
Samsung GT-I9500 Galaxy S IV Samsung SM-G900F Galaxy S5
Samsung GT-I9190 Galaxy S4 Mini Sony D5803 Xperia Z3 Compact
Samsung GT-I9192 Galaxy S4 Mini
Выбор правильного графика для
визуализации данных
Кольцевая – это круговая на
несколько рядов данных
Точечная популярна в
статистических исследованиях
0
500
1000
1500
2000
2500
0 500 1000 1500 2000
Просмотры
Посетители
Взаимосвязь количества
посетителей и просмотров
страниц сайта
Slide 18
Slide 18 text
Выбор правильного графика для
визуализации данных
Интересная реализация
пузырьковой диаграммы
-2,0
0,0
2,0
4,0
6,0
8,0
10,0
12,0
-2,0 0,0 2,0 4,0 6,0 8,0 10,0
Slide 19
Slide 19 text
Выбор правильного графика для
визуализации данных
В диаграммах с областями важен порядок
размещения рядов данных на диаграмме,
чтобы не было перекрытия
Slide 20
Slide 20 text
Выбор правильного графика для
визуализации данных
Биржевая диаграмма будет
полезна, если необходимо
указать разброс величин
-6
-4
-2
0
2
4
6
8
10
12
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
Температура в Киеве в феврале
2016
Slide 21
Slide 21 text
Выбор правильного графика для
визуализации данных
Поверхностная и лепестковая диаграммы
10
30
50
0
50
100
150
200
250
0,1 0,2 0,3 0,4 0,5 0,6 0,7 0,8 0,9
Температура
Прочность на разрыв
Секунды
Измерения прочности на разрыв
0-50 50-100 100-150 150-200 200-250
0
1000
2000
3000
понедельник
вторник
среда
четверг
пятница
суббота
воскресенье
Недельная посещаемость сайта
msoffice-prowork.com
Просмотры Посетители
Slide 22
Slide 22 text
Выбор правильного графика для
визуализации данных
Комбинированная – это
совмещение типов диаграмм
на одной плоскости
Slide 23
Slide 23 text
Выбор правильного графика для
визуализации данных
Новые типы диаграмм в Excel 2016:
• Древовидная
• Солнечные лучи
• Каскадная
• Гистограмма (это другая гистограмма)
• Парето
• Ящик с усами.
Slide 24
Slide 24 text
Выбор правильного графика для
визуализации данных (шаблоны)
Для сохранения диаграммы в качестве
шаблона следует:
1. Создать диаграмму.
2. Отформатировать диаграмму, произвести
настройку.
3. Выделить диаграмму/ вызвать
контекстное меню/ выбрать команду
"Сохранить как шаблон…"
4. Дать имя шаблону.
Slide 25
Slide 25 text
Быстрое форматирование диаграмм с
помощью стилей и макета
MS Excel от новичка до профессионала
Занятие 6.3
Slide 26
Slide 26 text
Быстрое форматирование диаграмм с
помощью стилей и макета
Стили диаграммы – это заранее подготовленный набор
визуальных параметров диаграммы.
Наборы отображения
существующих элементов
(цветовая гамма,
шрифты)
Отвечает за наличие/ отсутствие
элементов диаграммы (оси,
название, легенда и т.д.) и их
расположение
Slide 27
Slide 27 text
Быстрое форматирование диаграмм с
помощью стилей и макета
Стили диаграммы позволят в пару кликов изменить внешний вид
диаграммы до неузнаваемости.
-10
-5
0
5
10
15
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29
Температура в Киеве в
феврале 2016
-10
0
10
20
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29
Температура в Киеве в
феврале 2016
-10
0
10
20
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29
Температура в Киеве в
феврале 2016
-10
0
10
20
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29
Температура в Киеве в
феврале 2016
-10
0
10
20
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29
ТЕМПЕРАТУРА В КИЕВЕ
В ФЕВРАЛЕ 2016
-10
0
10
20
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29
Температура в Киеве в
феврале 2016
Slide 28
Slide 28 text
Быстрое форматирование диаграмм с
помощью стилей и макета
Макет диаграммы позволяет добавлять/удалять элементы диаграммы
(управлять элементами можно с помощью всплывающих команд).
Быстрая настройка
диаграммы с помощью
всплывающих команд
Slide 29
Slide 29 text
Форматирование диаграмм в ручном
режиме
MS Excel от новичка до профессионала
Занятие 6.4
Slide 30
Slide 30 text
Оси
Название диаграммы
Название основной оси Название вспомогательной оси
Подписи
данных
Таблица данных
Легенда
Ряд данных
Сетка
Линия
тренда
Slide 31
Slide 31 text
Форматирование диаграмм в ручном
режиме
Выделение области диаграммы (внешние границы)
и области построения (границы, ограниченные
осями координат)
Slide 32
Slide 32 text
Форматирование диаграмм в ручном
режиме
Количество доступных элементов диаграммы для
гистограммы с накоплением и поверхностной разное
Slide 33
Slide 33 text
Форматирование диаграмм в ручном режиме
(форматирование текстовых меток)
Текстовая метка
форматируется подобно
обычному тексту в Excel
(шрифт, размер, цвет и т.д.)
Slide 34
Slide 34 text
Форматирование диаграмм в ручном
режиме (форматирование рядов данных)
Выделен один элемент ряда данных
(точка данных)
За форматирование точки данных
отвечает группа «Стили фигур»
(применение готового стиля или
индивидуальные настройки).
Треугольник внизу вызывает
диалоговое окно с
дополнительными настройками,
например, можно настроить
прозрачность.
Slide 35
Slide 35 text
Форматирование диаграмм в ручном
режиме (форматирование легенды)
Легенда
Варианты размещения
легенды
Slide 36
Slide 36 text
Форматирование диаграмм в ручном
режиме (форматирование легенды)
Форматируется конкретный
элемент легенды
Установлен цвет подписи
оранжевый
Slide 37
Slide 37 text
Форматирование диаграмм в ручном
режиме (функция "=РЯД")
=РЯД(имя_ряда;
метки_категории;
диапазон_данных;номер_ряда;
размеры)
Slide 38
Slide 38 text
Инструменты быстрого анализа
MS Excel от новичка до профессионала
Занятие 6.5
Slide 39
Slide 39 text
Инструменты быстрого анализа
Иконка вызова инструментов
быстрого анализа. Вызов
инструментов быстрого
анализа можно сделать
сочетанием клавиш Ctrl+Q
Slide 40
Slide 40 text
Инструменты быстрого анализа
Применение условного
форматирования
Построение диаграммы
Различные формулы для
подсчета итогов
Преобразование
диапазона в таблицу
Построение спарклайнов
(инфолиний)
Slide 41
Slide 41 text
Инструменты быстрого анализа
(спарклайны)
Наглядно
Не наглядно
Slide 42
Slide 42 text
Инструменты быстрого анализа
(добавление спарклайнов)
С помощью маркера автозаполнения можно
построить спарклайны для остальных рядов
Slide 43
Slide 43 text
Инструменты быстрого анализа
(добавление спарклайнов)
Команда «Изменить
данные» позволяет
переопределить диапазон
и настроить параметры
Изменение типа
спарклайна
Добавление ярких
точек на спарклайн
(максимум/ минимум,
все точки и т.п.)
Внешний вид спарклайна
(цвет линий, маркеров)
Возможность группировать спарклайны (при
оформлении будут иметь единообразный стиль), либо
работать по отдельности. Можно очистить ячейки от
спарклайнов и настроить некоторые параметры
Slide 44
Slide 44 text
Инструменты быстрого анализа
Применено условное
форматирование, построена
диаграмма, добавлены
спарклайны и рассчитаны
средние значения. Все это
сделано инструментарием
быстрого анализа.