Как сделать сводную диаграмму в Excel
Обновлено: 15.05.2024
Сводную таблицу можно использовать для суммирования, анализа, изучения и представления сводных данных. Сводные диаграммы дополняют сводные таблицы, добавляя визуализацию к сводным данным в сводной таблице и позволяя легко просматривать сравнения, закономерности и тенденции. Как сводные таблицы, так и сводные диаграммы позволяют принимать обоснованные решения в отношении критически важных данных на вашем предприятии. Вы также можете подключаться к внешним источникам данных, таким как таблицы SQL Server, кубы служб SQL Server Analysis Services, Azure Marketplace, файлы Office Data Connection (.odc), XML-файлы, базы данных Access и текстовые файлы, для создания сводных таблиц или использовать существующие сводные таблицы для создавать новые таблицы.
Примечание. Снимки экрана в этой статье были сделаны в Excel 2016. Если у вас другая версия, представление может немного отличаться, но, если не указано иное, функциональность такая же.
О сводных таблицах
Сводная таблица – это интерактивный способ быстрого суммирования больших объемов данных. Вы можете использовать сводную таблицу для подробного анализа числовых данных и ответов на непредвиденные вопросы о ваших данных. Сводная таблица специально разработана для:
Запрос больших объемов данных многими удобными способами.
Подведение итогов и агрегирование числовых данных, обобщение данных по категориям и подкатегориям, а также создание пользовательских расчетов и формул.
Разворачивание и свертывание уровней данных, чтобы сфокусировать результаты, а также переход к подробным сведениям из сводных данных по интересующим вас областям.
Перемещение строк в столбцы или столбцов в строки (или "поворот") для просмотра различных сводок исходных данных.
Фильтрация, сортировка, группировка и условное форматирование наиболее полезного и интересного подмножества данных, что позволяет сосредоточиться только на нужной информации.
Представление кратких, привлекательных и аннотированных онлайн-отчетов или печатных отчетов.
Например, вот простой список расходов на домашнее хозяйство слева и сводная таблица на основе списка справа:
Данные о расходах семьи
Соответствующая сводная таблица
После того как вы создадите сводную таблицу, выбрав ее источник данных, упорядочив поля в списке полей сводной таблицы и выбрав исходный макет, вы сможете выполнять следующие задачи при работе со сводной таблицей:
Изучите данные, выполнив следующие действия:
Развернуть и свернуть данные, а также показать базовые сведения, относящиеся к значениям.
Сортировка, фильтрация и группировка полей и элементов.
Измените сводные функции и добавьте собственные расчеты и формулы.
Измените макет формы и расположение полей, выполнив следующие действия:
Измените форму сводной таблицы: компактную, структурную или табличную.
Добавить, изменить порядок и удалить поля.
Изменить порядок полей или элементов.
Измените расположение столбцов, строк и промежуточных итогов, выполнив следующие действия:
Включить или отключить заголовки полей столбцов и строк, а также отобразить или скрыть пустые строки.
Отображать промежуточные итоги над или под их строками.
Настроить ширину столбцов при обновлении.
Переместите поле столбца в область строки или поле строки в область столбца.
Объединить или разъединить ячейки для элементов внешней строки и столбца.
Измените отображение пробелов и ошибок, выполнив следующие действия:
Изменить способ отображения ошибок и пустых ячеек.
Изменить способ отображения элементов и ярлыков без данных.
Отображать или скрывать пустые строки
Измените формат, выполнив следующие действия:
Ручное и условное форматирование ячеек и диапазонов.
Изменить общий стиль формата сводной таблицы.
Изменить числовой формат для полей.
Включить форматирование сервера OLAP.
Сводные диаграммы обеспечивают графическое представление данных в связанных с ними сводных таблицах. Сводные диаграммы также интерактивны. При создании сводной диаграммы появляется панель фильтров сводной диаграммы. Вы можете использовать эту панель фильтров для сортировки и фильтрации базовых данных сводной диаграммы. Изменения, которые вы вносите в макет и данные в связанной сводной таблице, немедленно отражаются в макете и данных в сводной диаграмме и наоборот.
Сводные диаграммы отображают ряды данных, категории, маркеры данных и оси так же, как и стандартные диаграммы. Вы также можете изменить тип диаграммы и другие параметры, такие как заголовки, размещение легенды, метки данных, расположение диаграммы и т. д.
Вот сводная диаграмма, основанная на приведенном выше примере со сводной таблицей.
Дополнительную информацию см. в разделе Создание сводной диаграммы.
Если вы знакомы со стандартными диаграммами, вы обнаружите, что большинство операций в сводных диаграммах одинаковы. Однако есть некоторые отличия:
Ориентация строки/столбца В отличие от стандартной диаграммы, вы не можете изменить ориентацию строки/столбца сводной диаграммы с помощью диалогового окна "Выбор источника данных". Вместо этого вы можете повернуть метки строк и столбцов связанной сводной таблицы, чтобы добиться того же эффекта.
Типы диаграмм Вы можете изменить сводную диаграмму на любой тип диаграммы, кроме xy (точечной), фондовой или пузырьковой диаграммы.
Исходные данные. Стандартные диаграммы напрямую связаны с ячейками рабочего листа, а сводные диаграммы основаны на связанном с ними источнике данных сводной таблицы. В отличие от стандартной диаграммы, вы не можете изменить диапазон данных диаграммы в диалоговом окне "Выбор источника данных" сводной диаграммы.
Форматирование Большая часть форматирования, включая добавляемые элементы диаграммы, макет и стиль, сохраняется при обновлении сводной диаграммы. Однако линии тренда, метки данных, планки погрешностей и другие изменения наборов данных не сохраняются. Стандартные диаграммы не теряют это форматирование после его применения.
Хотя вы не можете напрямую изменить размер меток данных в сводной диаграмме, вы можете увеличить размер шрифта текста, чтобы эффективно изменить размер меток.
Вы можете использовать данные из рабочего листа Excel в качестве основы для сводной таблицы или сводной диаграммы. Данные должны быть в формате списка с метками столбцов в первой строке, которые Excel будет использовать для имен полей. Каждая ячейка в последующих строках должна содержать данные, соответствующие заголовку ее столбца, и вы не должны смешивать типы данных в одном и том же столбце. Например, вы не должны смешивать значения валюты и даты в одном и том же столбце. Кроме того, в диапазоне данных не должно быть пустых строк или столбцов.
Таблицы Excel Таблицы Excel уже имеют формат списка и являются хорошими кандидатами для исходных данных сводной таблицы. При обновлении сводной таблицы новые и обновленные данные из таблицы Excel автоматически включаются в операцию обновления.
Использование динамического именованного диапазона Чтобы облегчить обновление сводной таблицы, можно создать динамический именованный диапазон и использовать это имя в качестве источника данных сводной таблицы. Если именованный диапазон расширяется, чтобы включить больше данных, обновление сводной таблицы будет включать новые данные.
Включение итогов Excel автоматически создает промежуточные и общие итоги в сводной таблице. Если исходные данные содержат автоматические промежуточные и общие итоги, созданные с помощью команды «Промежуточные итоги» в группе «Структура» на вкладке «Данные», используйте эту же команду для удаления промежуточных и общих итогов перед созданием сводной таблицы.
Вы можете получать данные из внешнего источника данных, такого как база данных, куб оперативной аналитической обработки (OLAP) или текстовый файл. Например, вы можете вести базу данных о продажах, которую хотите обобщить и проанализировать.
Файлы подключения к данным Office Если вы используете файл подключения к данным Office (ODC) (.odc) для извлечения внешних данных для сводной таблицы, вы можете вводить данные непосредственно в сводную таблицу. Мы рекомендуем получать внешние данные для отчетов с помощью файлов ODC.
Исходные данные OLAP При извлечении исходных данных из базы данных OLAP или файла куба данные возвращаются в Excel только в виде сводной таблицы или сводной таблицы, преобразованной в функции рабочего листа. Дополнительные сведения см. в статье Преобразование ячеек сводной таблицы в формулы листа.
Исходные данные, отличные от OLAP. Это базовые данные для сводной таблицы или сводной диаграммы, полученные из источника, отличного от базы данных OLAP. Например, данные из реляционных баз данных или текстовые файлы.
Кэш сводной таблицы Каждый раз, когда вы создаете новую сводную таблицу или сводную диаграмму, Excel сохраняет копию данных для отчета в памяти и сохраняет эту область хранения как часть файла рабочей книги — это называется кешем сводной таблицы. Для каждой новой сводной таблицы требуется дополнительная память и место на диске. Однако, когда вы используете существующую сводную таблицу в качестве источника для новой в той же книге, обе используют один и тот же кэш. Поскольку вы повторно используете кеш, размер книги уменьшается, а в памяти хранится меньше данных.
Требования к расположению Чтобы использовать одну сводную таблицу в качестве источника для другой, обе они должны находиться в одной книге. Если исходная сводная таблица находится в другой книге, скопируйте источник в то место, где должна появиться новая таблица. Сводные таблицы и сводные диаграммы в разных книгах разделены, каждая со своей собственной копией данных в памяти и в книгах.
Изменения затрагивают обе сводные таблицы. Когда вы обновляете данные в новой сводной таблице, Excel также обновляет данные в исходной сводной таблице и наоборот. Когда вы группируете или разгруппируете элементы, а также создаете вычисляемые поля или вычисляемые элементы в одном, затрагиваются и те, и другие.Если вам нужна сводная таблица, независимая от другой, вы можете создать новую на основе исходного источника данных, а не копировать исходную сводную таблицу. Просто помните о потенциальных последствиях для памяти, если делать это слишком часто.
Сводные диаграммы. Вы можете создать новую сводную таблицу или сводную диаграмму на основе другой сводной таблицы, но вы не можете создать новую сводную диаграмму непосредственно на другой сводной диаграмме. Изменения в сводной диаграмме влияют на связанную сводную таблицу и наоборот.
Изменения в исходных данных могут привести к тому, что для анализа будут доступны другие данные. Например, вы можете удобно переключиться с тестовой базы данных на рабочую базу данных. Вы можете обновить сводную таблицу или сводную диаграмму новыми данными, похожими на исходную информацию о подключении к данным, путем переопределения исходных данных. Если данные существенно отличаются с большим количеством новых или дополнительных полей, может быть проще создать новую сводную таблицу или сводную диаграмму.
Отображение новых данных, полученных путем обновления Обновление сводной таблицы также может изменить данные, доступные для отображения. Для сводных таблиц на основе данных рабочего листа Excel извлекает новые поля в указанном вами исходном диапазоне или именованном диапазоне. Для отчетов, основанных на внешних данных, Excel извлекает новые данные, соответствующие критериям базового запроса, или данные, которые становятся доступными в кубе OLAP. Вы можете просматривать любые новые поля в списке полей и добавлять поля в отчет.
Изменение создаваемых вами кубов OLAP Отчеты на основе данных OLAP всегда имеют доступ ко всем данным в кубе. Если вы создали автономный куб, который содержит подмножество данных в серверном кубе, вы можете использовать команду Offline OLAP, чтобы изменить файл куба, чтобы он содержал данные, отличные от серверных.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
Сводная таблица — это мощный инструмент для расчета, обобщения и анализа данных, который позволяет вам видеть сравнения, закономерности и тенденции в ваших данных.
Сводные таблицы работают немного по-разному в зависимости от того, какую платформу вы используете для запуска Excel.
Создание сводной таблицы в Excel для Windows
Выберите ячейки, из которых вы хотите создать сводную таблицу.
Примечание. Данные должны быть организованы в виде столбцов с одной строкой заголовка.
Выберите Вставка > Сводная таблица.
Примечание. Если выбрать «Добавить эти данные в модель данных», таблица или диапазон, используемые для этой сводной таблицы, будут добавлены в модель данных книги. Узнать больше.
Выберите, где вы хотите разместить отчет сводной таблицы. Выберите «Новый рабочий лист», чтобы поместить сводную таблицу на новый рабочий лист, или на «Существующий рабочий лист» и выберите место, где должна появиться новая сводная таблица.
Сводные таблицы из других источников
Нажав стрелку вниз на кнопке, вы можете выбрать другие возможные источники для своей сводной таблицы. Помимо использования существующей таблицы или диапазона, есть еще три источника, которые вы можете выбрать для заполнения сводной таблицы.
Примечание. В зависимости от ИТ-настроек вашей организации вы можете увидеть название вашей организации, включенное в кнопку. Например, "Из Power BI (Майкрософт)"
Из внешнего источника данных
Из модели данных
Используйте этот параметр, если ваша книга содержит модель данных и вы хотите создать сводную таблицу из нескольких таблиц, расширить сводную таблицу с помощью настраиваемых показателей или работать с очень большими наборами данных.
Из Power BI
Используйте этот вариант, если ваша организация использует Power BI и вы хотите обнаруживать утвержденные облачные наборы данных и подключаться к ним, к которым у вас есть доступ.
Создание сводной таблицы
Чтобы добавить поле в сводную таблицу, установите флажок имени поля на панели "Поля сводной таблицы".
Примечание. Выбранные поля добавляются в области по умолчанию: нечисловые поля добавляются в строки, иерархии даты и времени добавляются в столбцы, а числовые поля добавляются в значения.
Чтобы переместить поле из одной области в другую, перетащите поле в целевую область.
Суммировать значения по
По умолчанию поля сводной таблицы, помещенные в область значений, будут отображаться как СУММА. Если Excel интерпретирует ваши данные как текст, они будут отображаться как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений. Вы можете изменить вычисление по умолчанию, сначала щелкнув стрелку справа от имени поля, а затем выбрав параметр «Параметры поля значения».
Далее измените расчет в разделе Суммировать значения по. Обратите внимание, что при изменении метода расчета Excel автоматически добавит его в раздел «Пользовательское имя», например «Сумма имени поля», но вы можете изменить его. Если вы нажмете кнопку «Числовой формат», вы сможете изменить числовой формат для всего поля.
Совет. Поскольку изменение вычисления в разделе "Суммировать значения по" приведет к изменению имени поля сводной таблицы, лучше не переименовывать поля сводной таблицы, пока вы не закончите настройку сводной таблицы. Один из приемов заключается в том, чтобы использовать «Найти и заменить» (Ctrl+H) > «Найти что» > «Сумма», затем «Заменить на» > оставить пустым, чтобы заменить все сразу, вместо повторного ввода вручную.
Открыв диалоговое окно "Настройка поля значения", вы можете сделать свой выбор на вкладке "Показать значения как".
Отображать значение как в расчете, так и в процентах.
Просто дважды перетащите элемент в раздел "Значения", а затем задайте параметры "Суммировать значения по" и "Показать значения как" для каждого из них.
Прежде чем начать:
Ваши данные должны быть организованы в табличном формате и не должны содержать пустых строк или столбцов. В идеале вы можете использовать таблицу Excel, как в нашем примере выше.
Таблицы — отличный источник данных сводной таблицы, поскольку строки, добавленные в таблицу, автоматически включаются в сводную таблицу при обновлении данных, а все новые столбцы будут включены в список полей сводной таблицы. В противном случае необходимо либо изменить исходные данные для сводной таблицы, либо использовать формулу динамического именованного диапазона.
Типы данных в столбцах должны быть одинаковыми. Например, нельзя смешивать даты и текст в одном столбце.
Сводные таблицы работают со снимком ваших данных, называемым кешем, поэтому ваши фактические данные никак не изменяются.
Если у вас ограниченный опыт работы со сводными таблицами или вы не знаете, с чего начать, рекомендуется использовать рекомендуемую сводную таблицу. При использовании этой функции Excel определяет осмысленный макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это поможет вам стать отправной точкой для дополнительных экспериментов. После создания рекомендуемой сводной таблицы вы можете исследовать различные ориентации и переупорядочивать поля для достижения конкретных результатов.
Вы также можете загрузить наш интерактивный учебник по созданию первой сводной таблицы.
<р>1. Щелкните ячейку в диапазоне исходных данных или таблицы. <р>2. Выберите Вставка > Рекомендуемая сводная таблица. 
Если вы используете Excel для Mac 2011 и более ранних версий, кнопка "Сводная таблица" находится на вкладке "Данные" в группе "Анализ".
<р>3. Excel отобразит диалоговое окно «Создать сводную таблицу» с выбранным диапазоном или именем таблицы. В данном случае мы используем таблицу под названием «tbl_HouseholdExpenses». <р>4. В разделе «Выберите, где вы хотите разместить отчет сводной таблицы» выберите «Новый лист» или «Существующий лист». Для существующего рабочего листа выберите ячейку, в которую вы хотите поместить сводную таблицу. <р>5. Нажмите OK, и Excel создаст пустую сводную таблицу и отобразит список полей сводной таблицы.Список полей сводной таблицы
В верхней части области «Имя поля» установите флажок для любого поля, которое вы хотите добавить в сводную таблицу. По умолчанию нечисловые поля добавляются в область строк, поля даты и времени добавляются в область столбцов, а числовые поля добавляются в область значений. Вы также можете вручную перетащить любой доступный элемент в любое из полей сводной таблицы или, если вам больше не нужен элемент в сводной таблице, просто перетащите его из списка полей или снимите флажок. Возможность переупорядочивать элементы поля — это одна из функций сводной таблицы, позволяющая легко и быстро изменить ее внешний вид.
Список полей сводной таблицы
Обобщить по
По умолчанию поля сводной таблицы, помещенные в область значений, будут отображаться как СУММА. Если Excel интерпретирует ваши данные как текст, они будут отображаться как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений. Вы можете изменить расчет по умолчанию, сначала щелкнув стрелку справа от имени поля, а затем выбрав параметр «Настройки поля».
Далее измените расчет в разделе Суммировать по. Обратите внимание, что при изменении метода расчета Excel автоматически добавит его в раздел «Пользовательское имя», например «Сумма имени поля», но вы можете изменить его. Если щелкнуть номер. кнопку, вы можете изменить числовой формат для всего поля.
Совет. Поскольку изменение вычисления в разделе "Суммировать по" приведет к изменению имени поля сводной таблицы, лучше не переименовывать поля сводной таблицы, пока вы не закончите настройку сводной таблицы. Один из трюков заключается в том, чтобы нажать «Заменить» (в меню «Правка») > «Найти что» > «Сумма», затем «Заменить на» > оставить пустым, чтобы заменить все сразу, вместо повторного ввода вручную.
Показать данные как
Вместо вычисления для суммирования данных вы также можете отобразить его в процентах от поля. В следующем примере мы изменили суммы расходов домохозяйства, чтобы они отображались в процентах от общей суммы, а не в виде суммы значений.
Открыв диалоговое окно "Параметры поля", вы можете сделать выбор на вкладке "Показать данные как".
Отображать значение как в расчете, так и в процентах.
Просто дважды перетащите элемент в раздел "Значения", щелкните значение правой кнопкой мыши и выберите "Настройки поля", а затем установите параметры "Суммировать по" и "Показать данные как данные" для каждого из них.
Если вы добавляете новые данные в источник данных сводной таблицы, все сводные таблицы, созданные на основе этого источника данных, необходимо обновить. Чтобы обновить только одну сводную таблицу, вы можете щелкнуть правой кнопкой мыши в любом месте диапазона сводной таблицы и выбрать «Обновить». Если у вас есть несколько сводных таблиц, сначала выберите любую ячейку в любой сводной таблице, затем на ленте перейдите к анализу сводной таблицы > щелкните стрелку под кнопкой «Обновить» и выберите «Обновить все».
Если вы создали сводную таблицу и решили, что она вам больше не нужна, вы можете просто выделить весь диапазон сводной таблицы, а затем нажать клавишу DELETE. Это не повлияет на другие данные, сводные таблицы или диаграммы вокруг него. Если ваша сводная таблица находится на отдельном листе, на котором нет других данных, которые вы хотите сохранить, удаление этого листа — это быстрый способ удалить сводную таблицу.
Выберите таблицу или диапазон данных на листе и выберите «Вставка» > «Сводная таблица», чтобы открыть панель «Вставка сводной таблицы».
Вы можете либо вручную создать свою собственную сводную таблицу, либо выбрать рекомендуемую сводную таблицу, которая будет создана для вас. Выполните одно из следующих действий:
На карточке Создать собственную сводную таблицу выберите Новый лист или Существующий лист, чтобы указать место назначения сводной таблицы.
В рекомендованной сводной таблице выберите «Новый лист» или «Существующий лист», чтобы выбрать место назначения сводной таблицы.
Примечание. Рекомендуемые сводные таблицы доступны только подписчикам Microsoft 365.
Вставить сводную диаграмму
Чтобы вставить сводную диаграмму, выполните следующие действия.
<р>1. Щелкните любую ячейку в сводной таблице. <р>2. На вкладке "Анализ" в группе "Инструменты" нажмите "Сводная диаграмма".
Появится диалоговое окно "Вставить диаграмму".
Ниже вы можете найти сводную диаграмму. Эта сводная диаграмма удивит и впечатлит вашего босса.
Примечание. Любые изменения, которые вы вносите в сводную диаграмму, немедленно отражаются в сводной таблице и наоборот.
Сводная диаграмма фильтров
Чтобы отфильтровать эту сводную диаграмму, выполните следующие действия.
<р>1. Используйте стандартные фильтры (треугольники рядом с товаром и страной). Например, используйте фильтр «Страна», чтобы показать только общее количество каждого продукта, экспортируемого в США.
Изменить тип сводной диаграммы
Вы можете изменить тип сводной диаграммы на другой в любое время.
<р>1. Выберите диаграмму. <р>2. На вкладке "Дизайн" в группе "Тип" нажмите "Изменить тип диаграммы".
Примечание. В круговых диаграммах всегда используется один ряд данных (в данном случае Beans). Чтобы получить сводную диаграмму страны, поменяйте местами данные по оси. Сначала выберите график. Затем на вкладке "Конструктор" в группе "Данные" нажмите "Переключить строку/столбец".
Сводная диаграмма похожа на обычную диаграмму, созданную в Excel, за исключением того, что она отображает информацию сводной таблицы. Как и отчеты сводных таблиц, сводные диаграммы являются динамическими, что означает, что вы можете изменить сводную диаграмму, обновив значения в сводной таблице.
Вставить сводную диаграмму
Вкладки "Анализ" и "Дизайн" отображаются в разделе "Инструменты сводных таблиц" на ленте.
В диалоговом окне "Вставить диаграмму" можно выбрать один из множества типов сводных диаграмм. Некоторые типы диаграмм лучше отображают определенные данные, чем другие.
Наиболее распространенными типами диаграмм являются гистограммы, гистограммы, линейные и круговые диаграммы.
- Столбчатые и гистограммы хорошо подходят для сравнения различных наборов данных друг с другом.
- Линейные диаграммы и диаграммы с областями отлично подходят для отображения тенденций с течением времени.
- На круговых диаграммах различные категории представлены как части единого целого.
Диаграмма появится на рабочем листе вместе со сводной таблицей. Вы можете щелкнуть и перетащить, чтобы изменить его положение, и использовать маркеры изменения размера, чтобы масштабировать его, как и любой другой объект.
Изменить сводную диаграмму
Как и в сводных таблицах, на панели полей сводной диаграммы есть четыре области, в которые можно добавлять поля. Внешний вид вашей диаграммы будет меняться в зависимости от области, в которой вы поместите поля.
- При необходимости разверните группу Показать/Скрыть на вкладке Анализ.
- Нажмите кнопку "Список полей".
Любые изменения, которые вы вносите в сводную диаграмму, автоматически применяются и к сводной таблице. Значения в каждом из них всегда будут совпадать.
Сводная диаграмма мгновенно обновляется, чтобы отображать данные для всех добавленных полей.
При выборе сводной диаграммы на ленте появляется новый набор вкладок.
БЕСПЛАТНЫЙ краткий справочник
Бесплатно для распространения с нашей благодарностью; мы надеемся, что вы рассмотрите наше платное обучение.
Читайте также: