Диаграммы
Диаграммы используются для представления рядов числовых данных в графическом виде. Они призваны облегчить восприятие больших объемов данных и взаимосвязей между различными рядами данных. Для построения графика (диаграммы) на основе имеющихся данных необходимо: а) Выбрать данные, которые будут участвовать в построении диаграммы (выбирать ячейки, которые будут названиями рядов и подписями по оси X не нужно) как показано на рис. 115; б) на вкладке ленты «Вставка» нажать на любой из представленных видов диаграмм в группе «Диаграммы» (рис. 116). После этого на листе появится диаграмма, построенная по выбранным данным (рис. 117).
|
Рис. 115 |
|
Рис. 116 |
|
Рис. 117 |
Элементами диаграммы являются:
— Область диаграммы (1);
— Область построения диаграммы (2);
— Элементы данных в рядах данных, которые используются для построения диаграммы (3);
— Горизонтальная (ось категорий) и вертикальная (ось значений) оси, по которым выполняется построение диаграммы (4);
— Легенда диаграммы (5);
— Название диаграммы и названия осей (6);
По умолчанию название диаграммы и подписи осей не отображаются на диаграмме. Для их отображения нужно выделить диаграмму, после чего на вкладке ленты «Конструктор» в группе «Макеты диаграмм» выбрать макет, содержащий те или иные подписи (рис. 118).
|
Рис. 118 |
Для изменения названий рядов и подписей по горизонтальной оси (оси категорий) нужно нажать правой кнопкой мыши на области построения диаграммы и в открывшемся меню выбрать пункт «Выбрать данные…». После этого откроется окно выбора данных «Выбор источника данных». В левой части этого окна можно изменить названия рядов. Для этого выбрать нужный ряд и нажать кнопку «Изменить» в левой части окна. В открывшемся окне «Изменение ряда» (рис. 119) в поле имя ряда ввести требуемое название ряда, или выделить ячейку, содержащую название ряда. В нашем случае – это ячейка A2 (исходные данные представлены на рис. 115). Для изменения подписей по горизонтальной оси (оси категорий) нужно нажать кнопку «Изменить» в правой части окна «Выбор источника данных» и в открывшемся окне «Подписи оси» выбрать диапазон, содержащий подписи. В нашем случае – это ячейки с B1 по F1 (исходные данные представлены на рис. 115).
|
Рис. 118 |
|
Рис. 119 |
|
Рис. 120 |
Изменить название диаграммы и названия осей можно, выделив соответствующее название и написав вместо него своё. В результате вышеописанных операций диаграмма с рис. 117 приняла вид, показанный на рис. 121.
|
Рис. 121 |
Для изменения типа диаграммы после ее построения достаточно нажать правой кнопкой мыши на диаграмме и выбрать в открывшемся меню пункт «Изменить тип диаграммы…». Альтернативный вариант: Выделить диаграмму и на ленте, на вкладке «Конструктор» нажать на кнопку «Изменить тип диаграммы».
Для перемещения диаграммы на отдельный лист нужно нажать правой кнопкой мыши на диаграмме и выбрать в открывшемся меню пункт «Переместить диаграмму…». В открывшемся окне «Перемещение диаграммы» выбрать пункт «На отдельном листе», при желании изменить название этого листа и нажать кнопку «Ок» (рис. 122).
|
Рис. 122 |
Сводные таблицы
Отчет сводной таблицы позволяет обобщать, анализировать, изучать и представлять данные из электронной таблицы. Отчет сводной таблицы может оказаться особенно полезным в ситуации, когда необходимо просуммировать большой набор чисел, а объединенные данные и промежуточные итоги позволяют взглянуть на данные под разными углами и сравнить однотипные показатели из разных групп данных.
В приведенном ниже примере за основу взята таблица, содержащая расходы на разные типы канцтоваров у разных отделов за период с 2009 по 2013 годы.
Перед созданием сводной таблицы необходимо выбрать диапазон данных, который будет использоваться для сводной таблицы. Диапазоном может быть как диапазон ячеек, так и несколько столбцов. Отличия в выборе диапазона следующие: если выбран ограниченный диапазон данных, то при добавлении новых данных в строки снизу от выбранного диапазона, эти данные не будут учитываться в сводной таблице. Если выбирать столбцы, то при добавлении новых данных в строки снизу, эти данные будут учитываться в сводной таблице.
Важно: Выбранный диапазон не должен содержать пустых строк или пустых ячеек в первой строке.
На рис. 123 показаны исходные данные, по которым будет создана сводная таблица. Для создания сводной таблицы нужно выделить диапазон данных (в данном случае это диапазон A1:G10). После этого на вкладке ленты «Вставка» нужно нажать на кнопку «Сводная таблица» (рис. 124)
|
Рис. 123 |
|
Рис. 124 |
В диалоговом окне «Создать сводной таблицы»выбрать вариант «Выбрать таблицу или диапазон»и проверить правильность диапазона ячеек в поле «Таблица или диапазон». Также в зависимости от того, куда следует поместить создаваемую таблицу нужно выбрать «На новый лист» или «На существующий лист» и нажать кнопку «Ок» (рис. 125).
|
Рис. 125 |
Будет создана пустая сводная таблица и открыт «Список полей сводной таблицы» (рис.126). В верхней части этого списка расположены все доступные поля сводной таблицы, а в нижней – макет таблицы, в который эти поля можно добавлять. Для добавления поля в макет таблицы нужно нажать левой кнопкой мыши на названии поля в списке полей, и перетащить в соответствующий контейнер макета. Второй вариант: нажать правой кнопкой мыши на названии нужного поля в списке полей и в открывшемся меню выбрать команду «Добавить в фильтр отчета», «Добавить в названия столбцов», «Добавить в названия строк»или «Добавить в значения». Логично добавлять поля, содержащие текстовые значения или даты в контейнеры «Названия строк» или «Названия столбцов», а числовые данные – в «Значения».
|
Рис. 126 |
Пример №1. Если нужно отобразить в сводной таблице затраты каждого отдела на канцтовары по годам, то в контейнер «Названия строк» нужно добавить поле «Номер отдела», в контейнер «Названия столбцов» – поле «Год» (или наоборот – от этого зависит только то, что будет отображаться в названиях строк или столбцов соответственно). В контейнер «Значения» нужно добавить поле «Сумма затрат» (рис. 127). В результате получится сводная таблица, представленная на рис. 128.
|
Рис. 127 |
|
Рис. 128 |
Пример №2. Если нужно отобразить в сводной таблице общие затраты каждого отдела на каждый вид товара, то в контейнер «Названия строк» нужно добавить поле «Номер отдела», в контейнер «Названия столбцов» – поле «Наименование товара» (или наоборот – от этого зависит только то, что будет отображаться в названиях строк или столбцов соответственно). В контейнер «Значения» нужно добавить поле «Сумма затрат» (рис. 129). В результате получится сводная таблица, представленная на рис. 130.
|
Рис. 129 |
|
Рис. 130 |
Тип операции, применяющейся в контейнере «Значения» может быть не только суммой. Для выбора типа операции необходимо нажать левой кнопкой мыши на стрелку в названии поля в контейнере «Значения» и в открывшемся меню выбрать пункт «Параметры полей значений…» (рис. 131). В открывшемся окне «Параметры поля значений» выбрать нужный тип операции и нажать «Ок» (рис. 132)
Для скрытия/отображения списка полей сводной таблицы нужно щелкнуть правой кнопкой мыши на таблице, и в появившемся меню соответственно выбрать пункт «Скрыть список полей» или «Показать список полей». Если исходные данные были изменены, то сводную таблицу можно обновить для отображения внесенных изменений. Для этого нужно щелкнуть правой кнопкой мыши на таблице, и в появившемся меню выбрать пункт «Обновить». Над ячейками сводной таблицы можно совершать те же операции, что и над ячейками обычных таблиц: применять различные виды форматирования, устанавливать фильтры, выполнять сортировку и т.д.
|
Рис. 131 |
|
Рис. 132 |
Также, если в сводной таблице выделить любую ячейку (кроме названий строк и столбцов), нажать правую кнопку мыши, и в открывшемся меню выбрать «Показать детали» (рис. 133), то на новый лист скопируются все данные, соответствующие названию столбца и строки, на пересечении которых находится выделенная ячейка (рис. 134).
|
Рис. 133 |
|
Рис. 134 |
Как видно из рисунка 134, из исходных данных были выбраны все данные, номер отдела которых равен «Отдел 2» (название строки в сводной таблице) и наименование товара равно «Карандаши» (название столбца в сводной таблице).
Сводная диаграмма
Процесс создания сводной диаграммы практически полностью повторяет процесс создания сводной таблицы. Вначале выбирается диапазон данных, по которым будет построена сводная диаграмма, потом на вкладке ленты «Вставка» нужно нажать на стрелку в углу кнопки «Сводная таблица» и выбрать пункт «Сводная диаграмма» (рис. 135). В диалоговом окне «Создать сводной таблицы и сводной диаграммы»выбрать вариант «Выбрать таблицу или диапазон»и проверить правильность диапазона ячеек в поле «Таблица или диапазон». Также в зависимости от того, куда следует поместить создаваемую таблицу нужно выбрать «На новый лист» или «На существующий лист» и нажать кнопку «Ок» (рис. 136).
|
Рис. 135 |
|
Рис. 136 |
Будет создана пустая сводная таблица и пустая сводная диаграмма, а также открыт «Список полей сводной таблицы». Единственное отличие этого списка от аналогичного при создании сводной таблицы – это названия контейнеров в макете. Так, вместо контейнера «Названия строк» теперь «Поля осей (категорий)», а вместо контейнера Названия столбцов» теперь «Поля легенды (ряды)» (рис.137). Остальной функционал остался прежним: названия полей добавляются в контейнеры макета и на основании их формируются сводная таблица и диаграмма.
Важно: при создании сводной диаграммы сама диаграмма и созданная вместе с ней сводная таблица, неразрывно связаны друг с другом, и при изменении одной соответственно изменяется другая.
Со сводными диаграммами можно совершать тот же набор действий, что и с обычными: изменять тип, макет, стиль диаграммы, добавлять и изменять названия осей и самой диаграммы, переносить диаграмму на отдельный лист.
|
Рис. 137 |
Рассмотрим пример 1 из п. 10.2 и на основе его создадим сводную диаграмму.
В контейнер «Поля осей (категорий)» нужно добавить поле «Номер отдела», в контейнер «Поля легенды (ряды)» – поле «Год». В контейнер «Значения» нужно добавить поле «Сумма затрат» (рис. 138). В результате получится сводная таблица и сводная диаграмма, представленная на рис. 139.
|
Рис. 138 |
|
Рис. 139 |
Если же в контейнер «Поля осей (категорий)» добавить поле «Год», а в контейнер «Поля легенды (ряды)» – поле «Номер отдела», то в результате получится сводная таблица и сводная диаграмма, представленная на рис. 140.
|
Рис. 140 |
Если в один контейнер (например, «Поля осей (категорий)») добавить оба поля «Год» и «Номер отдела», то в результате получится следующая сводная таблица (рис. 141) и сводная диаграмма (рис. 142).
|
Рис. 141 |
|
Рис. 142 |
Как видно из рисунков, в таком случае в сводной таблице данные автоматически группируются по номеру отдела, а внутри групп появляются года, сумма затрат отображается в один столбец. В сводной диаграмме при этом также происходит группировка данных по названию отдела на оси категорий Внутри групп также отображены года, а данные на графике представляют собой общую сумму затрат по каждому отделу за тот или иной год.
