Дата: 16 марта 2017 Категория:

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

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

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

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

Чтобы построить сводную диаграмму, нужно выполнить следующую последовательность действий:

1. Строим сводную таблицу, которая будет источником данных для диаграммы
2. Выделяем любую ячейку таблицы и жмем на ленте: Работа со сводными таблицами – Анализ – Сервис – Сводная диаграмма
3. В открывшемся окне выбираем и нажимаем Ок
4. При необходимости,

Кстати, если у Вас версия Microsoft Office 2013 и выше, первый пункт можно пропустить. Просто нажмите на ленте Вставка – Диаграммы – Сводная диаграмма . Процесс создания будет напоминать компоновку сводной таблицы, однако, таблица не будет отображена. В более ранних версиях, все же, придется предварительно строить сводную таблицу.

Вот и все дела, мы с Вами сделали сводную диаграмму и теперь наши отчеты приобрели законченный вид, а расчеты стали быстрыми и непринужденными!

В следующей статье ждите очень важную тему – . Если Вы с ним еще не знакомы – прочтите, не пожалеете. Я, например, пользуюсь им почти каждый день.

Как всегда, жду Ваших вопросов и комментариев!

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

Скачать заметку в формате или , примеры в формате

При создании стандартной диаграммы на основе обычных данных (не сводной таблицы), вы подставляете в диаграмму диапазон ячеек, содержащих отдельные значения. Каждая ячейка является индивидуальным объектом с собственным значением. Диаграмма представляет каждую ячейку в виде точки на графике и выводит их все по отдельности. В то же время, данные в сводной таблице относятся к единому большому объекту. Значения, отображаемые в сводной таблице, не представляют независимые данные, занимающие отдельные ячейки, - это элементы большего объекта сводной таблицы, хранящегося на рабочем листе.

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

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

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

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

Как видно на рис. 3, после выбора типа диаграммы она отобразится в окне программы.

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

На диаграмме отображаются кнопки полей сводной таблицы (см. рис. 3). Эти кнопки расположены на сводной диаграмме, окрашены в серый цвет и снабжены раскрывающимися списками. С помощью этих кнопок можно переупорядочить диаграмму либо применить фильтры к базовой сводной таблице. Кнопки полей сводной таблицы отображаются при печати сводной таблицы. Если нужно скрыть кнопки, отображаемые в области сводной диаграммы, просто удалите их. Щелкните на сводной диаграмме и перейдите на контекстную вкладку Анализировать . Щелкните на кнопке раскрывающегося меню Кнопки полей , чтобы скрыть некоторые или все кнопки полей сводной диаграммы (рис. 4).

Рис. 4. Щелкните на Скрыть все , чтобы кнопки полей не портили вид диаграммы при печати

Также кнопки полей сводной диаграммы можно убрать, кликнув на одной из них правой кнопкой мыши и выбрав в контекстном меню Скрыть все кнопки полей на диаграмме (рис. 5).

Вы получили диаграмму, которая является визуальным представлением числовых данных сводной таблицы. Более того, поскольку такая диаграмма основывается на сводной таблице, любые изменения в сводной диаграмме незамедлительно вызовут соответствующие изменения в самой сводной таблице и наоборот. Например, на рис. 6 показано, что после добавления в сводную таблицу поля Регион на диаграмме будет отображаться еще одно измерение, отображающее распределение объема продаж по регионам. Обратите внимание: на диаграмме, показанной на рис. 6, не выводятся промежуточные итоги. При создании сводных диаграмм Excel полностью игнорирует ячейки с любыми итоговыми данными. Если в качестве фильтра поля страницы выбрать Бизнес-сегмент, то он будет применяться не только к сводной таблице, но и к сводной диаграмме. Подобное поведение программы основывается на том, что сводная диаграмма и сводная таблица создаются на основе данных, взятых из одного кеша. Таким образом, если вы добавите в источник данных новую информацию, а затем обновите сводную таблицу, то сводная диаграмма будет обновлена автоматически.

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

Правила работы со сводными диаграммами

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

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

Предположим, необходимо создать на основе сводной таблицы диаграмму. Вы интуитивно представляете, что вдоль оси X будут откладываться фискальные периоды, а бизнес-сегменты будут указываться вдоль оси Y. Но, как показано на рис. 8, в полученной сводной диаграмме вдоль оси X откладываются значения поля Регион , а вдоль оси Y - значения поля Фискальный период .

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

• Ось Y. Соответствует области столбцов сводной таблицы и образует вертикальную ось сводной диаграммы.
• Ось X. Соответствует области строк сводной таблицы и образует горизонтальную ось сводной диаграммы.

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

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

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

Сводные диаграммы в Excel 2013 практически не отличаются от стандартных диаграмм, что делает их просто незаменимым средством создания отчетов. Но все же и в этой версии Excel остались ограничения, которые следует учитывать:

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

Альтернатива сводным диаграммам

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

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

Способ 1. Преобразование сводной таблицы в статические значения. После создания сводной таблицы и настройки ее структуры выделите всю таблицу и скопируйте ее в буфер обмена (для выделения всей таблицы можно воспользоваться клавиатурным сокращением Ctrl + Shift+ *). Перейдите на вкладку Главная и щелкните на кнопке Вставка, а затем выберите в раскрывающемся меню команду Вставить значения . Тем самым вы удалите сводную таблицу и замените ее статическими значениями, полученными на основе последнего состояния сводной таблицы. Полученные значения становятся основой создаваемой впоследствии диаграммы. Эта методика применяется для удаления интерактивных элементов сводной таблицы. Таким образом, вы преобразуете сводную таблицу в стандартную таблицу и создадите не сводную диаграмму, а обычную диаграмму, не поддающуюся фильтрации и реорганизации. Это замечание касается также способов 2 и 3.

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

Способ 3. Создание рисунка на основе сводной таблицы. Многим может показаться странной сама идея представлять сводную таблицу в виде рисунка, но это вполне адекватный способ передачи анализируемых данных, не требующий применения специальных мер защиты. В дополнение к небольшому размеру конечного файла вы всегда имеете возможность полностью контролировать то, какую информацию пользователь сможет увидеть на экране. Чтобы воспользоваться этим способом, скопируйте всю сводную таблицу в буфер обмена. Откройте новую рабочую книгу, щелкните в любом ее месте правой кнопкой мыши и выберите команду Специальная вставка . Укажите требуемый формат вставляемых данных (например, Рисунок ). На рабочий лист вставляется моментальная копия сводной диаграммы.

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

В сводной таблице, показанной на рис. 11, представлены 10 лучших рынков сбыта, определенных по показателю Период продаж (в часах) . Обратите внимание на то, что область фильтра отчета позволяет фильтровать данные по направлениям деятельности.

Рис. 11. Сводная таблица позволяет фильтровать данные первых десяти рынков, определенных по периоду и объемам продаж, а также по направлениям деятельности

Предположим, что вам требуется представить показанные данные в виде графика, чтобы показать взаимосвязь между временем выполнения работ и доходом. При этом нужно сохранить возможность фильтрации первой десятки рынков сбыта. К сожалению, сводная диаграмма в рассматриваемом случае неприменима. В общем случае представить сводную таблицу в виде графика нельзя. Способы 1–3 также не годятся, поскольку утрачиваются любые интерактивные элементы. Каково же решение? Воспользуйтесь ячейками вокруг сводной таблицы, чтобы настроить связь между исходными данными и диаграммой, которая создается на их основе. Другими словами, нужно создать блок данных, который будет служить источником информации для обычной диаграммы. Этот блок данных связан с элементами сводной таблицы. Это означает, что если сводная таблица изменяется, то изменяется и исходный блок данных диаграммы.

Установите курсор в ячейке, расположенной рядом со сводной таблицей, как показано на рис. 12. Задайте ссылку для первого элемента данных, указав в ней диапазон, который будет использоваться в стандартной диаграмме (если при попытке сослаться на ячейку сводной таблицы у вас в формуле «вылазит» функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, то вы не сможете «протащить» формулу. Чтобы преодолеть это затруднение ознакомьтесь с заметкой ).

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

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

Создав блок данных со ссылками на сводную таблицу, можно создать для него обычную диаграмму. В рассматриваемом примере на основе данного набора была создана точечная диаграмма. При использовании сводных диаграмм вы не получите ничего подобного. На рис. 14 показан результат применения описанной методики. Вы можете фильтровать данные по направлениям деятельности, для чего применяется поле ФИЛЬТРЫ, но при этом сохраняете возможность произвольного форматирования стандартной диаграммы, не ограниченной рамками сводной таблицы. Попробуйте выбрать один или другой бизнес-сегмент и посмотреть, как изменится диаграмма.

Рис. 14. Эта методика позволяет применять функциональные средства сводной таблицы, но сохраняет возможность произвольного форматирования диаграммы

Заметка написана на основе книги Джелен, Александер. . Глава 6.

Сортировка и группировка элементов сводных таблиц.

Форматирование сводных таблиц.

Вычисления в сводных таблицах.

Работа со сводной таблицей.

Создание сводной таблицы на основе данных списка.

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

Создадим сводную таблицу на основе списка Заказы товаров книги Продажа товаров (см. рис. 4.1).

Рис. 4.1. Список Заказы товаров

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

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

2. С помощью меню Данные , команды Сводные таблицы вызвать мастер создания сводных таблиц и диаграмм (см. рис. 4.2).

Рис. 4.2. Первый шаг мастера сводных таблиц и диаграмм

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

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

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

5. На последнем шаге мастера необходимо указать место, где вы хотите расположить сводную таблицу.

Безопасней всего создавать таблицу на новом листе, для чего установить переключатель «Помещать таблицу в…» на «Новый лист» . В противном случае данный переключатель нужно установить на «Существующий лист» и указать диапазон (или абсолютный адрес первой ячейки, создаваемой таблицы) текущего листа или любого другого существующего листа.

6. По окончании работы мастера на рабочем листе отобразится:

§ пустой макет таблицы,

§ список возможных полей сводной таблицы,

§ панель инструментов Сводные таблицы (см. рис. 4.3).

Рис. 4.3. Создание сводной таблицы.

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

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

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

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

Продолжая работу с нашим примером (см. рис. 4.1), перетащив поле Страна получателя на ось столбцов, поле Категория на ось строк и поле Стоимость заказов в область элементов данных ,получим сводную таблицу, изображенную на рисунке 4.4.

После того как начальная структура таблицы создана, можно ее реорганизовывать исходя из насущной необходимости анализа данных. Реорганизовать сводную таблицу – это значит изменить ориентацию или положение одного или нескольких полей. Изменение положения таблицы используется для просмотра данных под другим углом зрения. В оригинале сводная таблица называется Pivot Table (вращающаяся таблица). Именно возможность изменения ориентации таблицы, например транспонирование заголовков столбцов в заголовки строк (см. рис. 4.5) и наоборот, сделали сводные таблицы мощным аналитическим средством.

Рис. 4.4. Сводная таблица на основе данных списка Заказы товаров

Рис. 4.5. Транспонирование поля Страна получателя в заголовки столбцов

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

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

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

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

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

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

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

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

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

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

К числовым полям, помещаемым в область данных сводной таблицы, Excel применяет функцию Сумма , а к любым нечисловым полям – функцию Количество значений .

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

1. Поставить указатель ячейки на любую ячейку области данных.

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

3. В поле Операция открытого диалогового окна выбрать необходимую функцию.

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

1. Перетащить нужное число раз кнопку данного поля с панели Списка полей сводной таблицы (см. рис. 4.3) в область данных. Если вышеназванная панель отсутствует на экране, используется клавиша Отобразить список полей панели инструментов Сводные таблицы .

Рис. 4.7. Диалоговое окно Вычисление поля сводной таблицы

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

3. Повторить второй пункт нужное число раз.

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

Рис. 4.8. Использование итоговых функций Сумма и Количество
для поля Стоимость заказа

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

Чтобы использовать дополнительные вычисления, необходимо выделить любую ячейку в области данных, открыть диалоговое окно Вычисление поля сводной таблицы , в котором с помощью клавиши Дополнительно открыть и выбрать дополнительные вычисления (см. рис. 4.9).

Рис. 4.9. Использование дополнительных вычислений в сводных таблицах

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

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

1. Поставить указатель ячейки на любую ячейку сводной таблицы.

2. С помощью команды Формулы – Вычисляемое поле на панели инструментов Сводные таблицы (кнопка Сводная таблица ) открыть диалоговое окно Вставка вычисляемого поля (см. рис. 4.10).

3. В поле Имя ввести имя создаваемого поля.

4. В поле Формула составить с помощью арифметических операций, применяемых к полям сводной таблицы, вычисляемую формулу. Поля таблицы выбираются из списка Поле двойным щелком мыши или клавишей Добавить поле .

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

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

Рис. 4.10. Диалоговое окно Вставка вычисляемого поля

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

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

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

2. С помощью команды Формулы – Вычисляемый объект на панели инструментов Сводные таблицы (кнопка Сводная таблица ), открыть диалоговое окно Вставка вычисляемого элемента в… (см. рис. 4.11).

3. В поле Имя вышеназванного диалогового окна добавляется имя нового элемента, а в поле Формула – указать формулу его расчета. Элементы добавляются в формулу двойным щелчком мыши в поле Элементы или нажатием кнопки Добавить элемент .

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

Рис. 4.11. Добавление вычисляемого элемента в поле Категория

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

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

Excel предлагает более 20 автоформатов сводных таблиц, которые выбираются с помощью команды Формат отчета , на панели инструментов Сводные таблицы , меню Сводная таблица .

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

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

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

3. В диалоговом окне Вычисление поля сводной таблицы (см. рис. 4.7) нажать клавишу Формат .

4. В открывшемся окне Формат ячеек установить необходимый числовой формат.

Надписи внешних полей можно центрировать относительно надписей внутренних полей. Например на рисунке 4.12 заголовки поля Страна Получателя центрированы по вертикали относительно заголовков поля Категория , надписи Австрия Итог, Бразилия Итог отцентрированы по горизонтали. Для того чтобы отформатировать подобным образом сводную таблицу, необходимо в диалоговом окне Параметры сводной таблицы (команда Параметры таблицы меню Сводная таблица , панель инструментов Сводные таблицы ), включить флажок Объединять ячейки заголовков .

Рис. 4.12. Центрирование надписей внешних полей по отношению
к надписям внутренних полей

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

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

1. С помощью команды Выделить – Разрешить выделение , меню Сводная таблица на панели инструментов Сводные таблицы сделать доступным меню структурного выделения.

2. В открывшемся меню структурного выделения выбрать элементы выделения: Выделить – Только заголовки, Только данные, Данные и заголовки .

Например, на рисунке 4.12 выделены данные и заголовки итогов по стране (Австрия Итог , Бразилия Итог и т. д.).

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

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

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

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

1. Выделить ячейку любого элемента поля или кнопку поля, которое необходимо сортировать (в рассматриваемом примере рисунка 4.13 – поле Категория ).

2. Нажать кнопку Параметры поля на панели инструментов Сводные таблицы или выбрать аналогичную команду в меню Сводная таблица .

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

4. В окне диалога , показанном на рисунке 4.14, установить переключатель по возрастанию (или по убыванию ), а затем в свернутом списке с помощью поля выбрать поле, значения которого будут использоваться при автосортировке.

Рис. 4.14. Установка автосортировки с помощью диалогового окна
Дополнительные параметры поля сводной таблицы

В рассматриваемом примере основанием для сортировки поля Категория буду служить значения поля Сумма по полю Стоимость заказа .

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

Если в структуре сводной таблицы несколько полей поместить на ось строк или столбцов, то Excel автоматически группирует элементы внутреннего поля для каждого заголовка внешнего поля. Например, на рисунке 4.12 элементы поля Категория сгруппированы для каждого элемента поля Страна получателя , по которому добавлен промежуточный итог по группе элементов внутреннего поля.

Кроме того, в случае необходимости Excel позволяет осуществлять группировку:

§ выбранных элементов полей на оси строк или столбцов;

§ числовых элементов полей, размещенных на оси строк или столбцов;

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

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

1) выделить элементы полей, находящихся на осях, которые нужно группировать;

2) выбрать команду Группировать в подменю Группа и структура , меню Данные .

В результате будет создано новое поле, в котором выделенные элементы будут сгруппированы в группу с именем Группа1 (см. рис. 4.15).

Рис. 4.15. Создание группы элементов Кондитерские изделия ,
Напитки и Фрукты

Можно скрыть элементы группы, дважды щелкнув мышью на имени группы (Группа1 ); чтобы снова вывести элементы группы на экран, необходимо дважды щелкнуть мышью по заголовку группы еще раз. Кроме того, для скрытия или отображения элементов группы используются команды Скрыть детали и Отобразить детали меню Данные , подменю Группа и структура .

Полностью удалить из таблицы поле, содержащее элементы группы, можно, перетащив заголовок поля за пределы таблицы, на рисунке 4.15 это поле Категория .

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

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

Рис. 4.16. Группировка числовых элементов поля

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

Рис. 4.17. Группировка поля даты

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

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

Для построения сводной диаграммы по уже готовой сводной таблице нужно поставить указатель мыши в любое место сводной таблицы и выбрать команду Диаграмма в меню Вставка или любым другим удобным способом вызвать команду Мастер диаграмм .

При построении сводной диаграммы на основе списка необходимо, выделив любую ячейку списка, вызвать мастер построения сводных таблиц и диаграмм (см. рис. 4.2). На первом шаге мастера в группе Вид создаваемого отчета поставить переключатель на поле Сводная диаграмма (со сводной таблицей) . Благодаря работе мастера будет создана сводная диаграмма и связанная с ней сводная таблица. Например, на рисунке 4.18 показана сводная диаграмма, связанная с таблицей рисунка 4.4.

Кроме таких элементов обычных диаграмм Microsoft Excel, как ряд, значение, оси, сводные диаграммы имеют специализированные элементы:

§ поле страниц используется для фильтрации данных по конкретному элементу, соответствует оси страниц сводной таблицы;

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

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

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

Рис. 4.18. Сводная диаграмма

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

Вопросы для самоконтроля.

1. Дать определение сводной таблице.

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

3. Особенности работы с элементами поля, помещенного на ось страниц.

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

5. Обновление сводной таблицы.

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

7. Изменение итоговых функций полей, помещенных в область данных.

8. Создание вычисляемых полей и вычисляемых элементов.

9. Особенности форматирования сводных таблиц.

10. Перечислить элементы структурного выделения сводных таблиц.

11. Автосортировка элементов поля сводной таблицы.

12. Отображение нескольких наибольших или наименьших элементов поля сводной таблицы.

13. Группировка выбранных элементов полей на оси строк или на оси столбцов.

14. Группировка полей сводных таблиц по временным диапазонам.

15. Особенности построения сводных диаграмм.

16. Специальные элементы сводных диаграмм и их соответствие элементам связанных сводных таблиц.

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

1. Создать, используя команду Итоги , структуру таблицы списка Заказы товаров книги Продажа товаров , отражающую продажи по каждой категории товаров по всем странам.Разместить поле Страна получателя в область строк, перед полем Категория .

2. Разместить поле Страна получателя в область строк, а поле Категория – в область столбцов.

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

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

5. В список Заказы товаров книги Продажа товаров добавить 20 000 000 в первый заказ по кондитерским изделиям, заказанным в Австрию.

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

Удалить 20 000 000, добавленные по первому заказу в Австрию (кондитерские изделия) и вновь обновить сводную таблицу.

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

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

8. Используя свернутый список Данные, отобразить только суммупо полю Стоимость заказов .

9. Добавить в сводную таблицу вычисляемое поле Стоимость со скидкой , рассчитываемое уменьшением суммарной стоимости на 15%.

11. Для полученной сводной таблицы установить, используя команды автоформата, формат Таблица 10 , а затем Классическая сводная таблица .

12. Добавить в существующую сводную таблицу поле, в котором подсчитывается общая стоимость товара Цена*Количество*(1–Скидка) . Установить для этого поля денежный формат, грн, только целая часть.Поле Сумма по количеству единиц товара удалить из области данных.

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

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

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

16. В созданной диаграмме перетащить поле Дата размещения заказа в поле страниц и посмотреть зависимости по отдельным месяцам и годам.

Задания лабораторной работы 4.1.

1. Скопировать к себе в папку файл Лабораторная работа 4 . Все последующие задания лабораторной работы выполнить на основании списка Отправка товаров книги файла Лабораторная работа 4.xls , в каждом задании создавая независимый отчет.

2. На новом листе, с именем Сводная таблица_1 , создать сводную таблицу, имеющую структуру аналогичную итоговой таблице четвертого задания лабораторной работы 3.1.

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

4. Лист, на котором отражена сводная таблица по всем способам доставки, переименовать во Все способы доставки .

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

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

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

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

9. В поле Категория товаров добавить вычисляемый элемент Вегетарианская продукция , в котором будет подсчитано количество заказов по всем категориям товаров кроме Рыбопродуктов , Молокопродуктов , Мяса и птицы .

Задания лабораторной работы 4.2.

1. Используя список в файле Лабораторная работа 4_2.xls , создать сводную диаграмму, показывающую зависимость средней цены поставляемого товара от его категории и города поставки. Показать в диаграмме данные только по заказам, доставленным авиа.

2. Лист, на котором создалась связанная с диаграммой сводная таблица, переименовать в Сводная таблица_1 .

3. Используя данные листа Сводная таблица_1 ,вывести на отдельный лист детальную информацию о ценах на рыбопродукты, направленные в Новый Орлеан авиа.

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

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

6. Лист переименовать в Менеджеры.

7. Всех менеджеров выделить в отдельную группу Менеджеры . Надписи внешних полей центрировать относительно внутренних полей.

8. Удалить из таблицы поле, содержащее элементы группы Менеджеры.

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

10. На новом листе Кварталы создать сводную диаграмму (создавать диаграмму не на основе существующего отчета, а независимую), показывающую количество заказов на каждую категорию товара по квартально, в диаграмме показать только информацию 2003 года.

Диаграммы

Диаграммы используются для представления рядов числовых данных в графическом виде. Они призваны облегчить восприятие больших объемов данных и взаимосвязей между различными рядами данных. Для построения графика (диаграммы) на основе имеющихся данных необходимо: а) Выбрать данные, которые будут участвовать в построении диаграммы (выбирать ячейки, которые будут названиями рядов и подписями по оси 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).

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

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

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

Вставка и построение

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

янв.13

фев.13

мар.13

апр.13

май.13

июн.13

июл.13

авг.13

сен.13

окт.13

ноя.13

дек.13

Выручка

150 598р.

140 232р.

158 983р.

170 339р.

190 168р.

210 203р.

208 902р.

219 266р.

225 474р.

230 926р.

245 388р.

260 350р.

Затраты

45 179р.

46 276р.

54 054р.

59 618р.

68 460р.

77 775р.

79 382р.

85 513р.

89 062р.

92 370р.

110 424р.

130 175р.

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

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

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

На упомянутом выше окне нажмите кнопку «Добавить» в поле «Элементы легенды». Появится форма «Изменение ряда», где нужно задать ссылку на имя ряда (не является обязательным) и значения. Можно указать все показатели вручную.

После занесения требуемой информации и нажатия кнопки «OK», новый ряд отобразиться на диаграмме. Таким же образом добавим еще один элемент легенды из нашей таблицы.

Теперь заменим автоматически добавленные подписи по горизонтальной оси. В окне выбора данных имеется область категорий, а в ней кнопка «Изменить». Кликните по ней и в форме добавьте ссылку на диапазон этих подписей:

Посмотрите, что должно получиться:

Элементы диаграммы

По умолчанию диаграмма состоит из следующих элементов:

• Ряды данных – представляют главную ценность, т.к. визуализируют данные;
• Легенда – содержит названия рядов и пример их оформления;
• Оси – шкала с определенной ценой промежуточных делений;
• Область построения – является фоном для рядов данных;
• Линии сетки.

Помимо упомянутых выше объектов, могут быть добавлены такие как:

• Названия диаграммы;
• Линий проекции – нисходящие от рядов данных на горизонтальную ось линии;
• Линия тренда;
• Подписи данных – числовое значение для точки данных ряда;
• И другие нечасто используемые элементы.

Изменение стиля

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

Часто имеющихся шаблонов достаточно, но если Вы хотите большего, то придется задать собственный стиль. Сделать это можно кликнув по изменяемому объекту диаграммы правой кнопкой мыши, в меню выбрать пункт «формат Имя_Элемента» и через диалоговое окно изменить его параметры.

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

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

Как и со стилями, каждый элемент можно добавить либо удалить по-отдельности. В версии Excel 2007 для этого предусмотрена дополнительная вкладка «Макет», а в версии Excel 2013 данный функционал перенесен на ленту вкладки «Конструктор», в область «Макеты диаграмм».

Типы диаграмм

График

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

Гистограмма

Хорошо подходит для сравнения нескольких объектов и изменения их отношения со временем.
Пример сравнения показателя эффективности двух отделов поквартально:

Круговая

Предназначения для сравнения пропорций объектов. Не может отображать динамику.
Пример доли продаж каждой категории товаров от общей реализации:

Диаграмма с областями

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

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

Так как для нас первостепенно видеть именно потенциал, то данный ряд отображается первым. Из ниже приведенной диаграммы видно, что с 11 часов до 16 часов отдел не справляет с потоком клиентов.

Точечная

Представляет собой систему координат, где положение каждой точки задается значениями по горизонтальной (X) и вертикальной (Y) осям. Хорошо подходить, когда значение (Y) объекта зависит от определенного параметра (X).

Пример отображения тригонометрических функций:

Поверхность

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

Биржевая

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

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

Лепестковая

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

На ниже приведенной диаграмме представлено сравнение 3-х организаций по 4-ем направлениям: Доступность; Ценовая политика; Качество продукции; Клиентоориентированность. Видно, что компания X лидирует по первому и последнему направлению, компания Y по качеству продукции, а компания Z предоставляет лучшие цены.

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

Смешанный тип диаграмм

Приложение Excel позволяет совмещать в одной диаграмме несколько типов. В качестве примера совместим тип графика и гистограммы.

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

Иногда, из-за сильных различий значений рядов диаграммы, использование единой шкалы невозможно. Но можно добавить альтернативную шкалу. Перейдите в меню «Формат ряда данных…» и в разделе «Параметры ряда» переместите флажок на пункт «По вспомогательной оси».

Теперь диаграмма приобрела такой вид:

Тренд Excel

Каждому ряду диаграммы можно установить свой тренд. Они необходимы для определения основной направленности (тенденции). Но для каждого отдельного случая необходимо применять свою модель.

Выделите ряд данных, для которого хотите построить тренд, и кликнете по нему правой кнопкой мыши. В появившемся меню выберите пункт «Добавить линию тренда…».

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

• Экспоненциальный тренд. Если значения по вертикальной оси (Y) возрастают с каждым изменением по горизонтальной оси (X).
• Линейный тренд используется, если значения по Y имеют приблизительно одинаковые изменения для каждого значения по X.
• Логарифмический. Если изменение по оси Y замедляется с каждым изменениям по оси X.
• Полиномиальный тренд применяется, если изменения по Y происходят как в сторону увеличения, так в уменьшения. Т.е. данные описывают цикл. Хорошо подходит для анализа большого набора данных. Степень тренда выбирается в зависимости от количества пиков циклов:
  • Степень 2 – один пик, т.е. половина цикла;
  • Степень 3 – один полный цикл;
  • Степень 4 – полтора цикла;
  • и т.д.
• Степенной тренд. Если изменение по Y растет с примерно одинаковой скоростью при каждом изменением X.

Линейная фильтрация. Не применим для прогноза. Используется для сглаживания изменений Y. Усредняет изменение между точками. Если в настройках тренда параметру точки задать 2, то усреднение производится между соседними значениями оси X, если 3, то через одну, 4 через – две и т.д.

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

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

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

• Выделите сводную таблицу;
• Пройдите на вкладку «Анализ» (в Excel 2007 вкладка «Параметры»);
• В группе «Сервис» щелкните по пиктограмме «Сводная диаграмма».

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

• < Назад
• Вперёд >

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

У Вас недостаточно прав для комментирования.