С помощью сводной таблицы можно создавать сводки, анализировать, изучать и представлять сводные данные. Сводные диаграммы дополняют сводные таблицы, позволяя визуализировать содержащиеся в них данные. Кроме того, с помощью сводных диаграмм можно легко отображать сравнения, шаблоны и тенденции. И сводные таблицы, и сводные диаграммы помогают принимать взвешенные решения на основании критически важных данных вашей организации. Вы также можете подключаться к внешним источникам данных, например к таблицам SQL Server, кубам служб SQL Server Analysis Services, Azure Marketplace, файлам подключения к данным Office (ODC-файлам), XML-файлам, базам данных Access и текстовым файлам, чтобы создавать сводные таблицы, или создавать на основе сводных таблиц другие таблицы.
Если у вас есть правильные требования к лицензии, вы можете попросить Copilot помочь вам создать сводную таблицу или диаграмму.
Сведения о сводных таблицах
Сводная таблица — это интерактивный способ быстрого обобщения больших объемов данных. Вы можете использовать ее для подробного анализа числовых данных и для получения ответов на разнообразные вопросы, связанные с данными. Сводные таблицы предназначены для выполнения следующих задач:
- запрос больших объемов данных различными понятными способами;
- подведение промежуточных итогов и вычисление числовых данных, обобщение данных по категориям и подкатегориям, создание пользовательских вычислений и формул;
- развертывание и свертывание уровней представления данных для получения точных сведений о результатах, детализация итоговых данных по интересующим вопросам;
- перемещение строк в столбцы или столбцов в строки ("сведение") для просмотра различных сводок на основе исходных данных;
- фильтрация, сортировка, группировка и условное форматирование наиболее важных и часто используемых подмножеств данных для привлечения внимания к нужным сведениям;
- представление кратких, наглядных отчетов с примечаниями на веб-страницах или в напечатанном виде.
Например, слева показан простой список расходов семьи, а справа — созданная на его основе сводная таблица.
| Данные о продажах | Соответствующая сводная таблица |
|---|---|
|
|
Дополнительные сведения см. в статье Создание сводной таблицы для анализа данных листа.
Способы работы со сводной таблицей
После того как вы создадите сводную таблицу, выбрав для нее источник данных, упорядочив поля в списке полей сводной таблицы и выбрав исходный макет, вы сможете при работе с ней выполнять перечисленные ниже задачи.
Изучать данные. Вот какие действия доступны.
- Развертывание и свертывание данных, а также отображение базовых сведений, которые относятся к значениям.
- Сортировка, фильтрация и группировка полей и элементов.
- Изменение функций сведения и добавление настраиваемых вычислений и формул.
Измените макет формы и расположение полей , выполнив следующие действия.
- Изменение формы сводной таблицы: сжатая форма, структура или табличная форма.
- Добавление, изменение порядка и удаление полей.
- Изменение порядка полей или элементов.
Измените макет столбцов, строк и промежуточных итогов , выполнив следующие действия:
- Включение и отключение заголовков полей строк и столбцов, а также отображение или скрытие пустых строк.
- Отображение промежуточных итогов выше или ниже их строк.
- Настройка ширины колонок при обновлении.
- Перемещение поля столбца в область строк или поля строки в область столбцов.
- Объединение или отмена объединения ячеек для внешних элементов строки и столбца.
Измените отображение пустых и ошибок , выполнив следующие действия.
- Изменение способа отображения ошибок и пустых ячеек.
- Изменение способа отображения элементов и подписей без данных.
- Отображение и скрытие пустых строк.
Измените формат , выполнив следующие действия.
- Ручное и условное форматирование ячеек и диапазонов.
- Изменение стиля общего формата сводной таблицы.
- Изменение числового формата для полей.
- Включение форматирования сервера OLAP.
Дополнительные сведения см. в статье Разработка макета и формата сводной таблицы.
Сведения о сводных диаграммах
Сводные диаграммы служат для графического представления данных, содержащихся в сопоставленных с ними сводных таблицах. Как и сводные таблицы, сводные диаграммы — интерактивные объекты. При создании сводной диаграммы на экране отображается область фильтра сводной диаграммы. С помощью этой области вы можете сортировать и фильтровать исходные данные, используемые в сводной диаграмме. Изменения макета и данных в связанной сводной таблице немедленно отражаются в макете и данных в сводной диаграмме и наоборот.
Как и в обычных диаграммах, в сводных диаграммах отображаются ряды данных, категории, маркеры данных и оси. Кроме того, вы можете изменить тип диаграммы и другие параметры, например названия, расположение условных обозначений, метки данных, расположение диаграммы и т. д.
Вот сводная диаграмма, основанная на приведенном выше примере сводной таблицы.
Дополнительные сведения см. в статье Создание сводной диаграммы.
Различия между сводными и обычными диаграммами
Если вы знакомы со стандартными диаграммами, то работа со сводными диаграммами не будет для вас проблемой, так как большинство действий для этих двух типов диаграмм полностью совпадают. Тем не менее существуют и некоторые отличия.
Ориентация строк или столбцов В отличие от стандартной диаграммы, нельзя переключить ориентацию строки или столбца сводной диаграммы с помощью диалогового окна Выбор источника данных . Вместо этого вы можете изменить ориентацию меток строк и столбцов в связанной сводной таблице.
Типы диаграмм Сводную диаграмму можно изменить на любой тип диаграммы, кроме диаграммы xy (точечная), фондовая или пузырьковая диаграмма.
Исходные данные Standard диаграммах связаны непосредственно с ячейками листа, а сводные диаграммы основаны на связанном с ними источнике данных сводной таблицы. В отличие от обычной диаграммы в сводной диаграмме вам не удастся изменить диапазон данных диаграммы с помощью диалогового окна Выбор источника данных.
Форматирование Большая часть форматирования, включая добавляемые элементы диаграммы, макет и стиль, сохраняется при обновлении сводной диаграммы. Тем не менее линии трендов, метки данных, пределы погрешностей и другие изменения, вносимые в наборы данных, не сохраняются. В обычных диаграммах после применения такого форматирования оно не исчезает.
Несмотря на то что вам не удастся напрямую изменить размер меток данных в сводной диаграмме, вы можете увеличить размер шрифта и таким образом изменить размер меток.
Создание сводной таблицы или сводной диаграммы на основе данных листа
В качестве основы для сводной таблицы или сводной диаграммы вы можете использовать данные листа Excel. Данные должны представлять собой список, в первой строке которого содержатся метки столбцов. Приложение Excel использует эти метки в качестве имен полей. Каждая ячейка в последующих строках должна содержать данные, соответствующие заголовку столбца. Не следует использовать данные различных типов в одном и том же столбце. Например, в одном и том же столбце не следует использовать денежные значения и даты. Кроме того, в выбранном вами диапазоне данных не должно быть пустых строк или столбцов.
Таблицы Excel Таблицы Excel уже имеют формат списка и являются хорошими кандидатами для исходных данных сводной таблицы. При обновлении сводной таблицы новые и обновленные данные из таблицы Excel автоматически включаются в операцию обновления.
Использование динамического именованного диапазона Чтобы упростить обновление сводной таблицы, можно создать динамический именованный диапазон и использовать это имя в качестве источника данных сводной таблицы. Если вы расширите именованный диапазон, чтобы включить в него дополнительные данные, просто обновите сводную таблицу, и в нее будут включены эти новые данные.
Включая итоги Excel автоматически создает промежуточные итоги и общие итоги в сводной таблице. Если источник данных содержит автоматически вычисляемые промежуточные и общие итоги, которые вы создали с помощью команды Промежуточный итог в группе Структура на вкладке Данные, то с помощью этой же команды удалите промежуточные и общие итоги, прежде чем создавать сводную таблицу.
Создание сводной таблицы или сводной диаграммы на основе внешнего источника данных
Вы можете получить данные из внешнего источника данных, например базы данных, куба OLAP или текстового файла. Например, у вас может быть база данных со сведениями о продажах, для которых вы хотите создать сводку и которые вам необходимо проанализировать.
Файлы подключения к данным Office Если вы используете файл подключения к данным Office (ODC) (ODC) для получения внешних данных для сводной таблицы, вы можете ввести данные непосредственно в сводную таблицу. Рекомендуется получать внешние данные для отчетов с помощью ODC-файлов.
Исходные данные OLAP При получении исходных данных из базы данных OLAP или файла куба данные возвращаются в Excel только в виде сводной таблицы или сводной таблицы, преобразованной в функции листа. Дополнительные сведения см. в статье Преобразование ячеек сводной таблицы в формулы листа.
Исходные данные, отличные от OLAP Это базовые данные для сводной таблицы или сводной диаграммы, которые поступают из источника, отличного от базы данных OLAP. Например, это могут быть данные из реляционных баз данных или текстовых файлов.
Дополнительные сведения см. в статье Создание сводной таблицы с использованием внешнего источника данных.
Использование другой сводной таблицы в качестве источника данных
Кэш сводной таблицы Каждый раз, когда вы создаете новую сводную таблицу или сводную диаграмму, Excel сохраняет копию данных для отчета в памяти и сохраняет эту область хранения как часть файла книги. Это называется кэшем сводной таблицы. Для каждой новой сводной таблицы требуется дополнительный объем памяти и место на диске. Однако при использовании существующей сводной таблицы в качестве источника данных для новой таблицы в той же книге обе таблицы будут использовать один и тот же кэш. Благодаря повторному использованию кэша снижается размер книги и сокращается объем данных, хранящихся в памяти.
Требования к расположению Чтобы использовать одну сводную таблицу в качестве источника для другой, они должны находиться в одной книге. Если исходная сводная таблица находится в другой рабочей книге, скопируйте источник в рабочую книгу, в которой вы хотите создать другую сводную таблицу. Сводные таблицы и сводные диаграммы, находящиеся в разных рабочих книгах, — это отдельные объекты, для каждого из которых имеется собственная копия данных в памяти и в рабочих книгах.
Изменения влияют на обе сводные таблицы При обновлении данных в новой сводной таблице Excel также обновляет данные в исходной сводной таблице и наоборот. Когда вы группируете элементы или отменяете их группировку либо создаете вычисляемые поля или вычисляемые элементы в одной таблице, изменения будут внесены в обе таблицы. Если вам необходимо, чтобы одна сводная таблица не зависела от другой, создайте сводную таблицу на основе исходного источника данных, а не копируйте исходную сводную таблицу. Просто имейте в виду, что если выполнять такие операции слишком часто, это может сказаться на объеме свободной памяти.
Сводные диаграммы Новую сводную таблицу или сводную диаграмму можно создать на основе другой сводной таблицы, но нельзя создать новую сводную диаграмму непосредственно на другой сводной диаграмме. Изменения, вносимые в сводную диаграмму, влияют на связанную сводную таблицу и наоборот.
Изменение исходных данных существующей сводной таблицы
Изменение исходных данных может привести к тому, что для анализа будут доступны другие данные. Например, вам может быть необходимо удобно переключаться из тестовой базы данных в рабочую базу данных. Переопределив исходные данные, вы можете обновить сводную таблицу или сводную диаграмму, используя данные, структура которых похожа на структуру информации, получаемой из исходного подключения к данным. Если эти данные значительно отличаются, и в них имеется большое количество новых или дополнительных полей, возможно, будет проще создать другую сводную таблицу или сводную диаграмму.
Отображение новых данных, внесенных обновлением Обновление сводной таблицы также может изменить данные, доступные для отображения. Для сводных таблиц, основанных на данных листа, Excel получает новые поля, имеющиеся в указанном вами исходном диапазоне или именованном диапазоне. Для отчетов, основанных на внешних данных, Excel получает новые данные, которые соответствуют критериям для базового запроса или данных, которые становятся доступными в кубе OLAP. Вы можете просмотреть все новые поля в списке полей и добавить их в отчет.
Изменение создаваемых кубов OLAP Отчеты, основанные на данных OLAP, всегда имеют доступ ко всем данным в кубе. Если вы создали автономный куб, который содержит подмножество данных, имеющихся в кубе сервера, с помощью команды Автономный режим OLAP вы можете изменить файл куба так, чтобы он содержал другие данные с сервера.
См. также
Создание сводной таблицы для анализа данных на листе
Анализ данных с помощью сводных таблиц и других средств бизнес-аналитики