Файл: 3146.pdf

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

Центр Компьютерного обучения «Специалист»   

www.specialist.ru 

31 



Изменить цвет заливки области построения диаграммы. 



Добавить подписи данных – щелкнуть правой кнопкой мыши по ряду и выбрать 

Добавить 

подписи данных

 [Add Data Labels]. 

Диаграммы с пользовательскими элементами управления 

Диаграмма с включением/выключением рядов данных 

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

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

ЭТАПЫ ПОСТРОЕНИЯ ДИАГРАММЫ: 

1.

ПОДГОТОВКА ИСХОДНЫХ ДАННЫХ

– обычная таблица с исходными данными, по которой 

строится таблица данных для диаграммы. 



Создание элементов выбора 

ФЛАЖОК

.  

На вкладке 

Разработчик

 [Developer] раскрыть список команды 

Вставить

 [Insert] и выбрать 

в группе 

Элементы управления формы

 [Form Controls] элемент 

Флажок

 [Check Box (Form 

Control)]. 



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



Настроить параметры элемента – щелкнуть правой кнопкой 
мыши по элементу, выбрать 

Формат Объекта

 [Format Control]. 

На вкладке 

Элемент управления

 [Control] задать 

Связь с 

ячейкой

 [Cell link] – указать ячейку, в которую размещается 

значение ЛОЖЬ или ИСТИНА. 



Визуально убрать значение ИСТИНА или ЛОЖЬ в связанных ячейках – выделить ячейки, 
в диалоговом окне 

Формат ячеек

 [Format Cells] на вкладке 

Число

 [Number], выбрать 

числовой формат 

(все форматы)

 [Custom], в поле 

Тип

 [Type] ввести 

;;;

 и нажать 

ОК

. 

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

www.specialist.ru 

   Центр Компьютерного обучения «Специалист» 

32 



Построение таблицы данных для диаграммы – процесс копирования данных из исходной 
таблицы возможен с использованием функции ЕСЛИ [IF]. Т.е. если ряд включен, то 
значение в таблицу помещается, в противном случае – #Н/Д *#N/A]. 

2.

ПОСТРОЕНИЕ ДИАГРАММЫ

: 



Выделить всю таблицу Данные для диаграммы.  



На вкладке 

Вставка

 [Insert] выбрать 

График

 [Line] и тип 

График

[Line].  

Диаграмма с выбором значений 

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

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

Центр Компьютерного обучения «Специалист»   

www.specialist.ru 

33 

ЭТАПЫ ПОСТРОЕНИЯ ДИАГРАММЫ: 

1.

ПОДГОТОВКА ИСХОДНЫХ ДАННЫХ

– обычная таблица с исходными данными, по которой 

строится таблица данных для диаграммы. 



Построение таблицы данных для диаграммы: 



В зависимости от выбираемого года (ячейка 

A21

) в таблицу подтягиваются данные из 

исходной таблицы с помощью функции 

ВПР

 [VLOOKUP] и 

ПОИСКПОЗ

 [MATCH] 

(определяет номер столбца месяца в выделенной таблице). 



Вычислить среднее значение температуры функцией 

СРЗНАЧ

 [AVERAGE]. 



Определить минимальную (

МИН

 [MIN]) и максимальную (

МАКС

 [MAX]) температуру за 

год – если значение температуры месяца совпадает минимальным (максимальным), то 
его копируем, в противном случае – пусто. 

2.

ПОСТРОЕНИЕ ДИАГРАММЫ

: 



Выделить всю таблицу Данные для диаграммы.  



На вкладке 

Вставка

 [Insert] выбрать 

График

 [Line] и тип 

График

[Line]. 

3.

НАСТРОЙКА ВНЕШНЕГО ВИДА ДИАГРАММЫ

: 



Изменение типов диаграмм для отдельных рядов – щелкнуть правой кнопкой мыши по 
ряду и выбрать 

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

 [Change Series Chart Type]. 



Для ряда 

Максимальная

 и 

Минимальная

 изменить тип диаграммы на 

Гистограмма

[Column] и вид 

Гистограмма с группировкой

 [Clustered Column]. 



Сглаженная линия ряда год – щелкнуть правой кнопкой мыши по ряду и выбрать 

Формат 

ряда данных

 [Format Data Series], в группе 

Тип линии

 [Type line] поставить флажок 

Сглаженная линия

 [Smoothed line]. 



Добавление названия диаграммы – выделить диаграмму и на вкладке 

Макет

 [Layout] 

выбрать 

Название диаграммы

 [Chart Title]. 



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

A19

, 

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

=A1&"за "&A21&" год"

, где в ячейке A1 

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

www.specialist.ru 

   Центр Компьютерного обучения «Специалист» 

34 

содержится текст "Среднемесячная температура в Москве, °C", а в ячейке A21 – 
значение выбранного года. 



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

=

, щелкнуть по 

нужной ячейке и нажать 

Enter

.  

4.

СОЗДАНИЕ ЭЛЕМЕНТА УПРАВЛЕНИЯ СЧЕТЧИК: 



На вкладке 

Разработчик

 [Developer] раскрыть список команды 

Вставить

 [Insert] и выбрать в группе 

Элементы управления 

формы

 [Form Controls] элемент 

Счетчик

 [Spin Button (Form 

Control)]. 



Щелкнуть левой кнопкой мыши в свободной области 
диаграммы. 



Настроить параметры элемента – щелкнуть правой кнопкой мыши по элементу, выбрать 

Формат Объекта

 [Format Control]. На вкладке 

Элемент управления

 [Control] задать 

значения. Для элемента, отвечающего за 

Кол-во точек

: 



Текущее

 [Current Value] – значение, которое будет размещено в связанной ячейкой. 

Например, 1986. 



Минимальное значение

 [Minimum value] – минимально возможное значение. 

Например, 1981. 



Максимальное

значение

 [Maximum value] – максимально возможное значение. 

Например, 2011. 



Шаг

изменения

 [Incremental change] – шаг изменения. Например, 1. 



Связь с ячейкой

 [Cell link] – ячейка, в которой размещается значение. Например, 

A21

. 

Диаграмма с зумом и прокруткой 

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

Центр Компьютерного обучения «Специалист»   

www.specialist.ru 

35 

ПОСТРОЕНИЕ ДИАГРАММЫ: 

1.

СОЗДАНИЕ ЭЛЕМЕНТОВ УПРАВЛЕНИЯ – ПОЛОСЫ ПРОКРУТКИ

: 



На вкладке 

Разработчик

 [Developer] раскрыть список команды 

Вставить

 [Insert] и выбрать в группе 

Элементы управления 

формы

 [Form Controls] элемент 

Полоса прокрутки

 [Scroll bar 

(Form Control)]. 



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



Настроить параметры элемента – щелкнуть правой кнопкой мыши по элементу, выбрать 

Формат Объекта

 [Format Control]. На вкладке 

Элемент управления

 [Control] задать 

значения. Для элемента, отвечающего за 

Кол-во точек

: 



Текущее

 [Current Value] – значение, которое будет размещено в связанной ячейкой. 



Минимальное значение

 [Minimum value] – минимально возможное значение. 

Например, 1. 



Максимальное

значение

 [Maximum value] – максимальное количество точек по 

которым будет построена диаграмма. Например, 122. 



Шаг

изменения

 [Incremental change] – шаг изменения количества точек на диаграмме. 

Например, 1. 



Шаг изменения по страницам

 [Page Change] – шаг, который будет задействован если 

щелкать в полосе прокрутке слева или справа (сверху или снизу при вертикальной 
ориентации) от бегунка. Например, 5. 



Связь с ячейкой

 [Cell link] – ячейка, в которой размещается значение. Например, 

H3

. 



По аналогии создать элемент управления Полоса прокрутки для элемента, отвечающего 
за 

Сдвиг

 и связать его с ячейкой 

H5

2.

СОЗДАНИЕ ИМЕНОВАННЫХ ДИАПАЗОНОВ

:



На вкладке 

Формулы

 [Formulas+ выбрать 

Диспетчер имен 

[Name Manager] или нажать 

клавиши 

Ctrl

+

F3

.