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

Большая часть людей лучше принимают данные в графическом, а не цифровом виде. Сводные таблицы со собственными поразительно широкими возможностями впечатляют лишь тех, кто готовит итоговую данные к просмотру. Иначе, люди, каковые принимают важные ответы на базе проанализированных данных, предпочитают изучать эти самые данные в виде графиков и диаграмм. [1] Диаграммы нагляднее, четче и информативнее.

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

Скачать заметку в формате Word либо pdf. примеры в формате Excel

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

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

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

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

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

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

Рис. 1. В данной базисной сводной таблице представлены продажи по регионам, и выполняется фильтрация данных по бизнес-сегментам

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

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

Рис. 2. Выберите тип создаваемой сводной диаграммы

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

Рис. 3. Сводная диаграмма добавляется на рабочий лист, содержащий исходную сводную таблицу

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

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

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

В случае если необходимо скрыть кнопки, отображаемые в области сводной диаграммы, их. Щелкните на сводной диаграмме и перейдите на контекстную вкладку Разбирать. Щелкните на кнопке раскрывающегося меню Кнопки полей. дабы скрыть кое-какие либо все кнопки полей сводной диаграммы (рис.

4).

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

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

Рис. 5. Удаление кнопок полей сводной диаграммы через контекстное меню

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

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

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

Рис. 6. На сводной диаграмме выводятся те же эти, каковые представлены в сводной таблице

В сводные диаграммы возможно кроме этого додавать срезы. Для исполнения данной операции щелкните в области сводной диаграммы, выберите контекстную вкладку Разбирать, а после этого щелкните на кнопке Засунуть срез. (подробнее о срезах см. Excel 2013. Срезы сводных таблиц; создание временной шкалы ).

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

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

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

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

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

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

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

Рис. 7. Эта структура данных оптимальнее подходит при работе со сводными таблицами

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

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

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

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

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

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

9).

Рис. 9. Эта сводная диаграмма, основанная на поменянной сводной таблице, очень удобна для просмотра

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

В силу перечисленных ограничений многие пользователи относились к сводным диаграммам как к громоздкому и непрактичному инструменту.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[1] Заметка написана на базе книги Джелен, Александер. Сводные таблицы в Микрософт Excel 2013. Глава 6.

Источник: baguzin.ru

Excel для полных чайников Урок 16 Линия тренда

Важное на сайте:

• Как повысить эффективность процессора
• Как повысить индекс производительности windows
• Как правильно обменять валюту.
• Как правильно провести акцию "приведи друга и получи скидку"
• Как правильно работать со стопами?

Самые интересные результаты статей, подобранные именно по Вашим интересам:

• Как построить график точки безубыточности в excel

  ВСЕ УРОКИ ОБНОВЛЯЕМЫЙ ГРАФИК ТОЧКИ БЕЗУБЫТОЧНОСТИ В EXCEL Как выстроить обновляемый график точки безубыточности в Excel Итак, перед Вами стоит задача…

• Как посчитать маржу и наценку в excel

  маржи и Понятие наценки (в народе еще говорят «зазор») схожи между собой. Их легко спутать. Исходя из этого сперва четко определимся с отличием между…

• Как построить биржевую диаграмму excel

  Типы диаграмм Гистограмма показывает изменение данных за определенный период времени и иллюстрирует соотношение отдельных значений данных. Категории…

• Как добавить линию тренда

  Редактор электронных таблиц MS Excel. Графики функций. Поиск зависимости между данными при помощи графиков функций. Два типа графиков функций в Excel….

• Как построить линию тренда?

  Содержание статьи: Как выстроить линию тренда Торговля по линиям тренда Пробой линии тренда Факторы определения важности линии тренда: Добавлено в…

• Урок 13. линии тренда. трендовые линии

  Что такое линии тренда и трендовые линии Трендовые линии и линии тренда — это одно да и то же. Линии тренда рисуются на графиках приблизительно равно как…