Сводная диаграмма в excel как сделать

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

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

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

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

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

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

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

Рис. 6.3. Сводная диаграмма добавляется на рабочий лист с исходной сводной таблицей

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

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

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

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

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

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

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

Создание сводной диаграммы «с нуля»

Вам вовсе не обязательно создавать сводную диаграмму на основе сводной таблицы. Можно построить сводную диаграмму сразу на основе необработанных исходных данных. Щелкните на любой из ячеек таблицы с исходными данными и перейдите на вкладку Вставка (Insert). В группе Таблицы (Tables) щелкните на кнопке Сводная таблица (PivotTable) и выберите в раскрывающемся меню команду Сводная диаграмма (PivotChart). На экране появится диалоговое окно создания сводной диаграммы. В нем задаются такие же настройки, что и при создании сводной таблицы.

Читать еще:  Как сделать файл excel с паролем?

Знакомство с панелью области фильтра сводной таблицы

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

Но многие пользователи выразили сомнение в целесообразности использования отдельной панели сводной диаграммы, которая выполняет те же функции, что и список полей сводной таблицы. Учитывая пожелания пользователей, сотрудники компании Microsoft в версии Excel 2010 вернули список полей сводной таблицы в область диаграммы. Используя окрашенные в серый цвет кнопки полей сводной таблицы, отображенные в области сводной диаграммы, можно оперативно изменять сводную диаграмму.

Сводные диаграммы в Excel на основе простой и сводной таблицы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как создать диаграмму в Excel: пошаговая инструкция

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

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

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

Читать еще:  Округление чисел в excel как сделать

  1. Прежде, чем приступать к построению любой диаграммы, необходимо создать таблицу и заполнить ее данными. Будущая диаграмма будет построена на основе именно этой таблицы.
  2. Когда таблица будет полностью готова, необходимо выделить область, которую требуется отобразить в виде диаграммы, затем перейти во вкладку “Вставка”. Здесь будут представлены для выбора разные типы диаграмм:
    • Гистрограмма
    • График
    • Круговая
    • Иерархическая
    • Статистическая
    • Точечная
    • Каскадная
    • Комбинированная

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

Также, существуют и другие типы диаграмм, но они не столь распространённые. Ознакомиться с полным списком можно через меню “Вставка” (в строке меню программы в самом верху), далее пункт – “Диаграмма”.

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

    Диаграмма в виде графика будет отображается следующим образом:

    А вот так выглядит круговая диаграмма:

    Как работать с диаграммами

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

    Например, чтобы поменять типа диаграммы и ее подтип, щелкаем по кнопке “Изменить тип диаграммы” и в открывшемся списке выбираем то, что нам нужно.

    Нажав на кнопку “Добавить элемент диаграммы” можно раскрыть список действий, который поможет детально настроить вашу диаграмму.

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

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

    Готово, теперь наша диаграмма не только наглядна, но и информативна.

    Настройка размера шрифтов диаграммы

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

    Здесь можно внести требуемые изменения и сохранить их, нажав кнопку “OK”.

    Диаграмма с процентами

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

    1. По тому же принципу, который был описан выше, создайте таблицу и выделите участок, который необходимо преобразовать в диаграмму. Далее переходим во вкладку «Вставка» и выбираем, соответственно, тип диаграммы “Круговая”.
    2. По завершении предыдущего шага программа вас автоматически направит во вкладку по работе с вашей диаграммой – «Конструктор». Просмотрите предложенные макеты и остановите свой выбор на той диаграмме, где имеются значки процентов.
    3. Вот, собственно говоря, и все. Работа над круговой диаграммой с процентным отображением данных завершена.

    Диаграмма Парето — что это такое, и как ее построить в Экселе

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

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

    1. Создаем таблицу, например, с наименованиями товаров. В одном столбце будет указан объем закупки в денежном выражении, в другом – полученная прибыль. Цель данной таблицы вычислить — закупка какой продукции приносит максимальную выгоду при ее реализации.
    2. Строим обычную гистограмму. Для этого нужно выделить область таблицы, перейти во вкладку «Вставка» и далее выбирать тип диаграммы.
    3. После того как мы это сделали, сформируется диаграмма с 2-мя столбиками разного цвета, каждая из которых соответствует данным разных столбцов таблицы.
    4. Следующее, что нужно сделать – это изменить столбик, отвечающий за прибыль, на тип “График”. Для этого выделяем нужный столбик и идем в раздел «Конструктор». Там мы видим кнопку «Изменить тип диаграммы», нажимаем на нее. В открывшемся диалоговом окне переходим в раздел «График» и кликаем по подходящему типу графика.
    5. Вот и все, что требовалось сделать. Диаграмма Парето готова.Далее, ее можно отредактировать точно так же, как мы рассказывали выше, например, добавить значения столбиков и точек со значениями на графике.

    Заключение

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

    Сводная диаграмма в excel как сделать

    На этом шаге мы рассмотрим использование сводных таблиц на различных примерах.

    Пример 1. Использование сводных таблиц для консолидации данных

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

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

    Для создания сводной таблицы выполните следующие действия.

    1. Добавьте новый лист, назовите его Итоги.
    2. Выберите команду Данные | Сводная таблица, чтобы запустить средство Мастер сводных таблиц и диаграмм.
    3. В первом диалоговом окне мастера выберите переключатель В нескольких диапазонах консолидации и щелкните на кнопке Далее.
    4. В следующем диалоговом окне Мастер сводных таблиц и диаграмм — шаг 2а из 3 выберите переключатель Создать одно поле страницы. Щелкните на кнопке Далее.
    5. Теперь определите диапазоны для консолидации. Первый диапазон — Магазин1!А$1:$D12 (его адрес можно ввести непосредственно или указать на рабочем листе). Щелкните на кнопке Добавить для добавления диапазона к списку Список диапазонов.
    6. Повторите пункт 5 для двух других диапазонов. Щелкните на кнопке Далее, чтобы снова перейти к диалоговому окну шага 3.
    7. В третьем диалоговом окне Мастер сводных таблиц и диаграмм щелкните на кнопке Готово.

    В результате сводная таблица будет иметь вид:

    Рис. 2. Сводная таблица

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

    Файл с данным примером можно взять здесь.

    Пример 2. Создание диаграмм по сводной таблице

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

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

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

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

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

    Рис. 4. Пример сводной диаграммы

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

    Сводные диаграммы всегда строятся на отдельном листе диаграмм. Чтобы преобразовать сводную диаграмму во внедренную диаграмму рабочего листа, перейдите на лист сводной диаграммы и выполните команду Диаграмма | Размещение. В открывшемся диалоговом окне Размещение диаграммы выберите переключатель имеющимся и рабочий лист, на который нужно поместить диаграмму. Щелкните на кнопке ОК.

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

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

    Рис. 5. Пример таблицы, содержащей данные опроса общественного мнения

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

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

    На рисунке 7 показан другой рабочий лист. Этот лист содержит 12 отдельных сводных таблиц — по одной для каждого из вопросов. В каждой сводной таблице отображена частота ответов и их процентное соотношение.

    Рис. 7. Пример одной из 12 сводных таблиц

    Файл с данным примером можно взять здесь.

    На следующем шаге мы рассмотрим анализ данных с помощью сценария «что-если».

  • Ссылка на основную публикацию
    Adblock
    detector