Сводные диаграммы

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

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

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

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

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

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

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

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

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

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

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

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

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

3. В открывшемся окне Создать сводную таблицу и сводную диаграмму задать диапазон или источник данных и место размещения таблицы и диаграммы, и нажать ОK.

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

3. Анализ данных с помощью таблиц данных

Для анализа зависимости результата от различных наборов исходных данных в Excel используются таблицы данных.

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

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

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

Таблица данных с одним параметром для одной функции

Принцип использования Таблицы данных состоит в следующем:

1. задаются исходные данные задачи, в том числе начальное значение аргумента-параметра;

2. задается диапазон значений параметра (в виде столбца или строки);

3. выделяется место для размещения значений функции от заданных значений аргумента (столбец справа или строка ниже);

4. задается формула, определяющая зависимость функции от исходных данных, в том числе и от аргумента-параметра.

Для получения искомых значений следует выделить блок таблицы, содержащий значения заданного диапазона параметра и расчетную формулу, и на вкладке Данные в группе Работа с данными выбрать команду Анализ "что если", а затем выбрать в списке пункт Таблицы данных. В открывшемся диалоговом окне Таблица данных необходимо указать, в какую ячейку подставлять значения аргумента-параметра из диапазона значений параметра. Очевидно, что нужно указать адрес ячейки, содержащей значение аргумента-параметра. Таблица данных обеспечивает последовательный выбор элементов из диапазона значений аргумента и подстановку каждого из них в ячейку-параметр, после чего выполняется пересчет таблицы и значение функции, определяемое формулой, заносится в отведенное для него место рядом с соответствующим значением аргумента:

• для каждой ячейки столбца в соседнюю ячейку строки, т.е. по строкам;

• для каждой ячейки строки в соседнюю ячейку столбца, т.е. по столбцам.

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

Таблица данных с одним параметром для нескольких функций

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

Таблица данных с двумя параметрами

Используя команду Таблица данных, можно построить таблицу с двумя параметрами. При этом значения одного из них должны располагаться в столбце, а значения другого — в строке. Формула в этом случае должна находиться в левом верхнем углу блока заполняемых ячеек таблицы (над столбцом значений параметра) и рассчитывать значение заданной функции для исходных данных, содержащих две ячейки с начальными значениями аргументов-параметров.

Для получения искомых значений функции выделяется блок таблицы, содержащий ячейку формулы и ячейки значений заданных диапазонов параметров. Затем открывается окно Таблица данных в списке команды Анализ "что если" в группе Работа с данными на вкладке Данные. В окне необходимо указать, в какую ячейку подставлять значения параметра из диапазона значений параметра по столбцам, а в какую — по строкам. Очевидно, что нужно указать ячейки, где находятся начальные значения изменяемого аргумента вычисляемой функции.

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