Сводные диаграммы в excel. Диаграммы в Excel

Эксель – одна из самых лучших программ для работы с таблицами. Она есть на компьютере практически у каждого пользователя, поскольку данный редактор нужен как для работы, так и для учёбы, во время выполнения различных курсовых или лабораторных заданий. Но при этом не все знают, как сделать диаграмму в Excel по данным таблицы. В этом редакторе вы сможете использовать огромное количество шаблонов, которые были разработаны в компании Microsoft. Но если вы не знаете, какой тип лучше выбрать, то предпочтительнее будет использовать автоматический режим.

Для того чтобы построить подобный объект, необходимо выполнить следующие действия.

1. Создайте какую-нибудь таблицу.

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

1. Перейдите на вкладку «Вставка». Кликните на иконку «Рекомендуемые диаграммы».

1. После этого вы увидите окно «Вставка диаграммы». Предложенные варианты будут зависеть от того, что именно вы выделите (перед нажатием на кнопку). У вас они могут быть другие, поскольку всё зависит от информации в таблице.

1. Для того чтобы построить диаграмму, выберите любую из них и нажмите на «OK».

1. В данном случае объект будет выглядеть вот так.

Ручной выбор типа диаграммы

1. Выделите нужные вам данные для анализа.

1. Затем кликните на какую-нибудь иконку из указанной области.

1. Сразу после этого раскроется список различных типов объектов.

1. Кликнув на любой из них, вы получите желаемую диаграмму.

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

Какие бывают диаграммы

Можно выделить несколько основных категорий:

• гистограммы;

• график или диаграмма с областями;

• круговые или кольцевые диаграммы;

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

• иерархическая диаграмма;

• статистическая диаграмма;

• точечный или пузырьковый график;

В данном случае точка является своеобразным маркером.

• каскадная или биржевая диаграмма;

• комбинированная диаграмма;

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

• поверхностная или лепестковая;

Как сделать сводную диаграмму

Данный инструмент более сложный по сравнению с описанными выше. Ранее всё происходило автоматически. Вам нужно было только выбрать внешний вид и желаемый тип. Здесь всё иначе. На этот раз придется всё делать вручную.

1. Выделите нужные ячейки в таблице и кликните на соответствующую иконку.

1. Сразу после этого появится окно «Создать сводную диаграмму». Необходимо указать:
   • таблицу или диапазон значений;
   • место, где следует разместить объект (на новый или текущий лист).
2. Для продолжения нажмите на кнопку «OK».

1. В результате этого вы увидите:
   • пустую сводную таблицу;
   • пустую диаграмму;
   • поля сводной диаграммы.

1. Необходимо перенести мышкой желаемые поля в области (по своему усмотрению):
   • легенды;
   • значений.

1. Кроме этого, вы можете настроить, какое именно значение нужно выводить. Для этого сделайте правый клик мыши по каждому полю и нажмите на пункт «Параметры полей значений…».

1. В результате этого появится окно «Параметры поля значений». Здесь вы сможете:
   • подписать источник своим имением;
   • выбрать операцию, которую следует использовать для сведения данных в выбранном поле.

Для сохранения нажмите на кнопку «OK».

Вкладка «Анализировать»

После того как вы создадите сводную диаграмму, у вас откроется новая вкладка «Анализировать». Она сразу же исчезнет, если активным станет другой объект. Для возврата достаточно снова кликнуть на диаграмму.

Рассмотрим каждый раздел более внимательно, поскольку при помощи их можно изменить все элементы до неузнаваемости.

Параметры сводной таблицы

1. Нажмите на самую первую иконку.
2. Выберите пункт «Параметры».

1. Благодаря этому появится окно настроек данного объекта. Здесь вы сможете задать желаемое имя таблицы и множество других параметров.

Для сохранения настроек необходимо кликнуть на кнопку «OK».

Как изменить активное поле

Если вы кликните на эту иконку, то увидите, что все инструменты не активны.

Для того чтобы можно было изменить какой-нибудь элемент, нужно сделать следующее.

1. Кликните на что-нибудь на вашей диаграмме.

1. В результате этого данное поле выделится «кружочками».
2. Если вы снова кликните на иконку «Активное поле», то увидите, что инструменты стали активными.

1. Для настроек нужно кликнуть на соответствующее поле.

1. В результате этого появится окно «Параметры поля».

1. Для дополнительных настроек перейдите на вкладку «Разметка и печать».

1. Чтобы сохранить внесенные изменения, необходимо нажать на кнопку «OK».

Как вставить срез

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

1. Кликните на кнопку «Вставить срез».
2. В результате этого появится окно со списком полей, которые есть в сводной таблице.

1. Выберите какое-нибудь поле и нажмите на кнопку «OK».

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

1. Если вы нажмете на какую-нибудь строчку, то увидите, что все остальные записи в таблице исчезли. Осталось только то, где среднее значение соответствует выбранному.

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

1. Если кликнуть на другую цифру, то результат сразу же изменится.

1. Количество строк может быть абсолютно любым (минимум одна).

Меняться будет как сводная таблица, так и диаграмма, которая построена по ее значениям.

1. Если хотите удалить срез, нужно кликнуть на крестик в правом верхнем углу.

1. Благодаря этому таблица восстановится до исходного вида.

Для того чтобы убрать это окно срезов, необходимо сделать несколько несложных шагов:

1. Кликните правой кнопкой мыши по данному элементу.
2. После этого появится контекстное меню, в котором нужно выбрать пункт «Удалить ‘название поля’».

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

Как вставить временную шкалу

Для того чтобы вставить срез по дате, нужно сделать следующие шаги.

1. Кликните на соответствующую кнопку.

1. В нашем случае мы увидим следующее окно с ошибкой.

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

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

Как обновить данные в диаграмме

Для обновления информации в таблице нужно кликнуть на соответствующую кнопку.

Как изменить информацию для построения

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

1. Кликните на иконку «Источник данных».
2. В появившемся меню выберите одноименный пункт.

1. Далее вас попросят указать нужные ячейки.
2. Для сохранения изменений кликните на «OK».

Редактирование диаграммы

Если вы работаете с диаграммой (неважно какой – обычной или сводной), у вас будет появляться вкладка «Конструктор».

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

Добавить элемент

При желании вы всегда можете добавить какой-нибудь объект, который отсутствует в данном шаблоне диаграммы. Для этого необходимо:

1. Нажать на иконку «Добавить элемент диаграммы».
2. Выбрать желаемый объект.

Благодаря этому меню вы сможете изменить свою диаграмму и таблицу до неузнаваемости.

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

1. Кликните на соответствующую иконку.
2. Выберите нужный вам макет.

Необязательно сразу же вносить изменения в ваш объект. При наведении на любую иконку будет доступен предварительный просмотр.

Если найдете что-то подходящее, просто кликните на этот шаблон. Внешний вид автоматически изменится.

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

1. Нажмите на соответствующую иконку.
2. В результате этого вы увидите огромную палитру различных оттенков.

1. Если хотите посмотреть, как это будет выглядеть на вашей диаграмме, просто наведите курсор на любой из цветов.

1. Для сохранения изменений нужно кликнуть на выбранный оттенок.

Кроме этого, можно использовать готовые темы оформления. Для этого необходимо сделать несколько простых операций.

1. Раскройте полный список вариантов у данного инструмента.

1. Для того чтобы увидеть, как это выглядит в увеличенном виде, достаточно навести курсор на любую из иконок.

1. Для сохранения изменений необходимо кликнуть на выбранный вариант.

Кроме этого, доступны манипуляции с отображаемой информацией. Например, можно поменять строки и столбцы местами.

После нажатия на эту кнопку вы увидите, что диаграмма стала выглядеть совсем иначе.

Данный инструмент очень помогает в том случае, если вы не можете правильно указать поля для строк и столбцов при построении данного объекта. Если вы ошиблись или результат выглядит некрасиво – нажмите на эту кнопку. Возможно, станет намного лучше и информативнее.

Если нажать еще раз, то всё вернется обратно.

Для того чтобы изменить диапазон данных в таблице для построения диаграммы, нужно кликнуть на иконку «Выбрать данные». В данном окне вы сможете

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

Для сохранения изменений нужно нажать на кнопку «OK».

Как сменить тип диаграммы

1. Нажать на указанную иконку.
2. В появившемся окне выбрать нужный вам шаблон.

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

1. Для упрощения выбора можно навести курсор на любую из миниатюр. В результате этого вы увидите её в увеличенном размере.

1. Для изменения типа нужно кликнуть на любой из вариантов и сохранить при помощи кнопки «OK».

Заключение

В этой статье мы пошагово рассмотрели технологию построения диаграмм в редакторе Excel. Помимо этого, было особое внимание уделено оформлению и редактированию созданных объектов, поскольку недостаточно уметь использовать только готовые варианты от разработчиков Microsoft. Вы должны учиться изменять внешний вид под свои нужды и быть оригинальными.

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

Видеоинструкция

Если у вас всё равно по каким-то причинам ничего не выходит, сколько бы вы ни старались, ниже добавлено видео, в котором вы сможете найти различные комментарии к описанным выше действиям.

СМОТРЕТЬ УКРАИНОЯЗЫЧНЫЙ ВАРИАНТ СТАТЬИ

В этой статье мы рассмотрим методы создания и использования сводной диаграммы.

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

Первым мы рассмотрим способ создания сводной диаграммы на основе сводной таблицы. Для практической тренировки Вы можете воспользоваться нашим примером, загрузив его по ссылке (простая таблица.xlsx).

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

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

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

Мы выбрали простой линейный график. В результате появился готовый график, содержащий данные сводной таблицы, а так же окно :

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

Окно Область фильтра сводной таблицы предназначено для удобного управления сводной таблицей и диаграммой, построенной на её основе:

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

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

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

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

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

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

Excel создаст новую сводную таблицу и сводную диаграмму:

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

В начало страницы

В начало страницы

Сводная диаграмма в Excel похожа на сводную таблицу. Несколько отличаются режимы отображения. В сводную таблицу программа помещает подробные сведения. В диаграмму – итоги или сводки. Вместо строк и столбцов – ряды и категории. Каждый ряд имеет определенный цвет и представляет собой связанные точки данных. Категория – это одна точка с каждого ряда (на оси Х).

Как создать сводную диаграмму в Excel из нескольких таблиц

На разных листах одной книги находятся отчеты по продажам в нескольких магазинах:

Они имеют одинаковую структуру и одинаковые способы представления данных.

Прежде чем делать сводную диаграмму в Экселе, добавим на панель быстрого доступа кнопку «Мастера сводных таблиц и диаграмм». Через «Офис» заходим в «Параметры Excel» - «Настройка». Выбираем «Все команды». Находим инструмент «Мастер сводных таблиц и диаграмм». Жмем «Добавить».

На панели появится такой значок:

Переходим на новый лист. Вызываем «Мастера»:

1. Первый шаг. Указываем, что будем создавать «сводную диаграмму со сводной таблицей» на основе данных, которые находятся «в нескольких диапазонах консолидации».
2. Второй шаг. Меняем настройку по умолчанию – «создать поля страницы». Если оставить параметр «создать одно поле страницы», данные отобразятся путано.
3. Третий шаг. Формируем список диапазонов, на основании которых будет построена сводная диаграмма. Сначала переходим на лист с первой таблицей – выделяем ее – жмем добавить. Переходим на лист со второй таблицей – повторяем все те же процедуры. Указываем количество полей – 1. Становится активным окошко «Первое поле». Даем ему название – «2013». Повторяем эту же операцию для каждого диапазона – названия «2014», «2015».
4. Четвертый шаг. Переходим на лист, где будет находиться сводная диаграмма. Ставим курсор в первую ячейку диапазона для сводной таблицы (весь диапазон Excel сформирует самостоятельно).

После нажатия кнопки «Готово» получаем сводную диаграмму и таблицу следующего вида:

Пока на этом остановимся и рассмотрим другой метод создания сводной диаграммы.

Сводная диаграмма в Экселе на основе готовой сводной таблицы

Выбираем сводную таблицу, щелкнув в любом месте мышкой. После чего переходим на вкладку «Вставка-Гистограмма».

Выбираем простую гистограмму. В результате появляется диаграммы с данными из сводной таблицы и область фильтра. Зададим фильтр для значений в столбцах – отобразить продажи за 1 квартал 2013, 2014, 2015 гг.

Окно «Область фильтра сводной таблицы» не предназначено для изменения условий построения диаграммы. Гистограмма формируется только на основе данных в сводной таблице. Внести сюда значения из основных таблиц невозможно. Но добавленные в сводную таблицу новые значения отражаются в графике.

С помощью окна «Область фильтра» можно управлять сводной таблицей и диаграммой.

Сравнение продаж по кварталам за анализируемый период:

Анализ продаж в магазине «Серебрянка»:

Если для удобства необходимо переместить диаграмму на новый лист, щелкаем правой кнопкой по области построения и нажимаем «Переместить диаграмму».

В первом примере мы объединяли в одну диаграмму сразу несколько диапазонов. Но можно построить график и на основе одной базовой таблицы. Выделить ее – перейти на вкладку «Вставка».

Программа создаст одновременно сводную таблицу и диаграмму. Настраивайте поля – и анализируйте.

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

Решение: Нужно построить сводную диаграмму. Графическое представление информации, которая собрана в сводной таблице, может пригодиться для презентации в PowerPoint, использования на собрании, в отчёте или для быстрого анализа. Сводная диаграмма даёт снимок интересующих данных (как и обычная диаграмма), но вдобавок она снабжена интерактивными фильтрами непосредственно из сводной таблицы, которые позволяют быстро анализировать различные срезы данных.

Создаём сводную диаграмму

В Excel 2013 можно создать сводную диаграмму двумя способами. В первом случае мы используем достоинства инструмента «Рекомендуемые диаграммы » в Excel. Работая с этим инструментом, нам не потребуется сначала создавать сводную таблицу, чтобы далее из неё построить сводную диаграмму.

Второй способ – создать сводную диаграмму из существующей сводной таблицы, используя для этого уже созданные фильтры и поля.

Вариант 1: Создаём сводную диаграмму при помощи инструмента «Рекомендуемые диаграммы»

1. Выделите данные, которые нужно показать на диаграмме.
2. На вкладке Вставка (Insert) в разделе Диаграммы (Charts) нажмите кнопку Рекомендуемые диаграммы (Recommended Charts), чтобы открыть диалоговое окно Вставка диаграммы (Insert Chart).
3. Диалоговое окно откроется на вкладке Рекомендуемые диаграммы (Recommended Charts), где в меню слева приведён список подходящих шаблонов диаграмм. В правом верхнем углу миниатюры каждого шаблона есть иконка сводной диаграммы:
4. Кликните по любой диаграмме из списка рекомендованных, чтобы увидеть результат в области предварительного просмотра.
5. Выберите, подходящий (или почти подходящий) тип диаграммы и нажмите ОК .

Слева от листа с данными будет вставлен новый лист, на котором будет создана сводная диаграмма (и сопутствующая сводная таблица).

Вариант 2: Создаём сводную диаграмму из существующей сводной таблицы

1. Кликните в любом месте сводной таблицы, чтобы на Ленте меню появилась группа вкладок Работа со сводными таблицами (PivotTable Tools).
2. На вкладке Анализ (Analyze) нажмите кнопку Сводная диаграмма (Pivot Chart), при этом откроется диалоговое окно Вставка диаграммы (Insert Chart).
3. В левой части диалогового окна выберите подходящий тип диаграммы. Далее выберите подтип диаграммы в верхней части окна. Будущая сводная диаграмма будет показана в области предварительного просмотра.
4. Нажмите ОК , чтобы вставить сводную диаграмму на тот же лист, где находится исходная сводная таблица.
5. После того, как сводная диаграмма создана, можно заняться настройкой её элементов и цветов при помощи списка полей на Ленте меню или иконок Элементы диаграммы (Chart Elements) и Стили диаграмм (Chart Styles).
6. Посмотрите на получившуюся сводную диаграмму. Вы можете управлять фильтрами прямо на диаграмме, чтобы увидеть различные срезы данных. Здорово, правда!

Урок подготовлен для Вас командой сайта office-guru.ru

Диаграммы

Диаграммы используются для представления рядов числовых данных в графическом виде. Они призваны облегчить восприятие больших объемов данных и взаимосвязей между различными рядами данных. Для построения графика (диаграммы) на основе имеющихся данных необходимо: а) Выбрать данные, которые будут участвовать в построении диаграммы (выбирать ячейки, которые будут названиями рядов и подписями по оси X не нужно) как показано на рис. 115; б) на вкладке ленты "Вставка" нажать на любой из представленных видов диаграмм в группе "Диаграммы" (рис. 116). После этого на листе появится диаграмма, построенная по выбранным данным (рис. 117).

Элементами диаграммы являются:

Область диаграммы (1);

Область построения диаграммы (2);

Элементы данных в рядах данных, которые используются для построения диаграммы (3);

Горизонтальная (ось категорий) и вертикальная (ось значений) оси, по которым выполняется построение диаграммы (4);

Легенда диаграммы (5);

Название диаграммы и названия осей (6);

По умолчанию название диаграммы и подписи осей не отображаются на диаграмме. Для их отображения нужно выделить диаграмму, после чего на вкладке ленты "Конструктор" в группе "Макеты диаграмм" выбрать макет, содержащий те или иные подписи (рис. 118).

Для изменения названий рядов и подписей по горизонтальной оси (оси категорий) нужно нажать правой кнопкой мыши на области построения диаграммы и в открывшемся меню выбрать пункт "Выбрать данные…". После этого откроется окно выбора данных "Выбор источника данных". В левой части этого окна можно изменить названия рядов. Для этого выбрать нужный ряд и нажать кнопку "Изменить" в левой части окна. В открывшемся окне "Изменение ряда" (рис. 119) в поле имя ряда ввести требуемое название ряда, или выделить ячейку, содержащую название ряда. В нашем случае - это ячейка A2 (исходные данные представлены на рис. 115). Для изменения подписей по горизонтальной оси (оси категорий) нужно нажать кнопку "Изменить" в правой части окна "Выбор источника данных" и в открывшемся окне "Подписи оси" выбрать диапазон, содержащий подписи. В нашем случае - это ячейки с B1 по F1 (исходные данные представлены на рис. 115).

Для изменения типа диаграммы после ее построения достаточно нажать правой кнопкой мыши на диаграмме и выбрать в открывшемся меню пункт "Изменить тип диаграммы…". Альтернативный вариант: Выделить диаграмму и на ленте, на вкладке "Конструктор" нажать на кнопку "Изменить тип диаграммы".

Для перемещения диаграммы на отдельный лист нужно нажать правой кнопкой мыши на диаграмме и выбрать в открывшемся меню пункт "Переместить диаграмму…". В открывшемся окне "Перемещение диаграммы" выбрать пункт "На отдельном листе", при желании изменить название этого листа и нажать кнопку "Ок" (рис. 122).

Сводные таблицы

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

В приведенном ниже примере за основу взята таблица, содержащая расходы на разные типы канцтоваров у разных отделов за период с 2009 по 2013 годы.

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

Важно: Выбранный диапазон не должен содержать пустых строк или пустых ячеек в первой строке.

На рис. 123 показаны исходные данные, по которым будет создана сводная таблица. Для создания сводной таблицы нужно выделить диапазон данных (в данном случае это диапазон A1:G10). После этого на вкладке ленты "Вставка" нужно нажать на кнопку "Сводная таблица" (рис. 124)

Будет создана пустая сводная таблица и открыт "Список полей сводной таблицы" (рис.126). В верхней части этого списка расположены все доступные поля сводной таблицы, а в нижней - макет таблицы, в который эти поля можно добавлять. Для добавления поля в макет таблицы нужно нажать левой кнопкой мыши на названии поля в списке полей, и перетащить в соответствующий контейнер макета. Второй вариант: нажать правой кнопкой мыши на названии нужного поля в списке полей и в открывшемся меню выбрать команду " Добавить в фильтр отчета", " Добавить в названия столбцов", " Добавить в названия строк"или "Добавить в значения". Логично добавлять поля, содержащие текстовые значения или даты в контейнеры "Названия строк" или "Названия столбцов", а числовые данные - в "Значения".

Пример №1. Если нужно отобразить в сводной таблице затраты каждого отдела на канцтовары по годам, то в контейнер "Названия строк" нужно добавить поле "Номер отдела", в контейнер "Названия столбцов" - поле "Год" (или наоборот - от этого зависит только то, что будет отображаться в названиях строк или столбцов соответственно). В контейнер "Значения" нужно добавить поле "Сумма затрат" (рис. 127). В результате получится сводная таблица, представленная на рис. 128.

Рис. 128

Пример №2. Если нужно отобразить в сводной таблице общие затраты каждого отдела на каждый вид товара, то в контейнер "Названия строк" нужно добавить поле "Номер отдела", в контейнер "Названия столбцов" - поле "Наименование товара" (или наоборот - от этого зависит только то, что будет отображаться в названиях строк или столбцов соответственно). В контейнер "Значения" нужно добавить поле "Сумма затрат" (рис. 129). В результате получится сводная таблица, представленная на рис. 130.

Тип операции, применяющейся в контейнере "Значения" может быть не только суммой. Для выбора типа операции необходимо нажать левой кнопкой мыши на стрелку в названии поля в контейнере "Значения" и в открывшемся меню выбрать пункт "Параметры полей значений…" (рис. 131). В открывшемся окне "Параметры поля значений" выбрать нужный тип операции и нажать "Ок" (рис. 132)

Для скрытия/отображения списка полей сводной таблицы нужно щелкнуть правой кнопкой мыши на таблице, и в появившемся меню соответственно выбрать пункт "Скрыть список полей" или "Показать список полей". Если исходные данные были изменены, то сводную таблицу можно обновить для отображения внесенных изменений. Для этого нужно щелкнуть правой кнопкой мыши на таблице, и в появившемся меню выбрать пункт "Обновить". Над ячейками сводной таблицы можно совершать те же операции, что и над ячейками обычных таблиц: применять различные виды форматирования, устанавливать фильтры, выполнять сортировку и т.д.

Как видно из рисунка 134, из исходных данных были выбраны все данные, номер отдела которых равен "Отдел 2" (название строки в сводной таблице) и наименование товара равно "Карандаши" (название столбца в сводной таблице).

Сводная диаграмма

Процесс создания сводной диаграммы практически полностью повторяет процесс создания сводной таблицы. Вначале выбирается диапазон данных, по которым будет построена сводная диаграмма, потом на вкладке ленты "Вставка" нужно нажать на стрелку в углу кнопки "Сводная таблица" и выбрать пункт "Сводная диаграмма" (рис. 135). В диалоговом окне " Создать сводной таблицы и сводной диаграммы"выбрать вариант " Выбрать таблицу или диапазон"и проверить правильность диапазона ячеек в поле " Таблица или диапазон". Также в зависимости от того, куда следует поместить создаваемую таблицу нужно выбрать "На новый лист" или "На существующий лист" и нажать кнопку "Ок" (рис. 136).

Будет создана пустая сводная таблица и пустая сводная диаграмма, а также открыт "Список полей сводной таблицы". Единственное отличие этого списка от аналогичного при создании сводной таблицы - это названия контейнеров в макете. Так, вместо контейнера "Названия строк" теперь "Поля осей (категорий)", а вместо контейнера Названия столбцов" теперь "Поля легенды (ряды)" (рис.137). Остальной функционал остался прежним: названия полей добавляются в контейнеры макета и на основании их формируются сводная таблица и диаграмма.

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

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

Если в один контейнер (например, "Поля осей (категорий)") добавить оба поля "Год" и "Номер отдела", то в результате получится следующая сводная таблица (рис. 141) и сводная диаграмма (рис. 142).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ЛАБОРАТОРНАЯ РАБОТА № 28

ТЕМА ЗАНЯТИЯ: Использование сводных таблиц Excel

УЧЕБНЫЕ ВОПРОСЫ:

1. Создание сводных таблиц и диаграмм

Редактирование структуры сводной таблицы и диаграммы.

КОНТРОЛЬНЫЕ ВОПРОСЫ:

1. В каких случаях используют макрос?

2. Какие существуют способы запуска макросов?

3. Какой объект в Excel называется связанным?

Основные действия и приемы работы при выполнении лабораторной работы

Создание сводных таблиц и диаграмм

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

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

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

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

Сводная таблица - особый объект. Для него нельзя обычным способом:

· вставлять и удалять строки, столбцы, ячейки,

· изменять или перемещать ячейки.

Если встать в любую ячейку сводной таблицы, то на экране отображается панель инструментов «Сводные таблицы», дающая быстрый доступ к командам работы со сводной таблицей.

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

Отчет сводной таблицы можно создать на основе списка Microsoft Excel, внешней базы данных, нескольких листов Excel или другого отчета сводной таблицы.

Исходные данные в списках Excel и большинстве баз данных организованы в строки и столбцы (рис. 28.2.). Используемые исходные данные должны содержать в каждом столбце информацию одного типа. В приведенном примере регион продаж всегда отображается в столбце E, объем продаж - в столбце D и так далее.

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

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

Приведенный пример (рис. 28.3) показывает соответствие между полями в каждом типе отчета.

Макет отчета сводной диаграммы - то есть положение его полей- при создании диаграммы из отчета сводной таблицы первоначально определяется макетом отчета сводной таблицы. Если отчет сводной диаграммы создается первым, макет диаграммы формируется перетаскиванием полей из группы кнопок на панели инструментов Сводная таблица в соответствующие области размещения на листе диаграммы. Microsoft Excel автоматически создает связанный отчет сводной таблицы, который использует соответствующий макет.