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

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

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

2. На вкладке Вставка нажмите кнопку Сводная таблица.

   

3. При этом создается сводная таблица на основе существующей таблицы или диапазона.
   

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

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

5. Нажмите кнопку ОК.

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

Получение из внешнего источника данных

Получение из модели данных

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

Получение из Power BI

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

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

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

   

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

При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить только одну сводную таблицу, щелкните правой кнопкой мыши в любом месте диапазона сводной таблицы, а затем выберите Обновить. Если у вас несколько сводных таблиц, сначала выберите любую ячейку в любой сводной таблице, а затем на ленте перейдите к разделу Анализ сводной таблицы > щелкните стрелку под кнопкой Обновить , а затем выберите Обновить все.

Операция

По умолчанию поля сводной таблицы, размещенные в области Значения , отображаются в виде СУММ. Если Excel интерпретирует данные как текст, данные отображаются как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений. Вы можете изменить вычисление по умолчанию, сначала щелкнув стрелку справа от имени поля, а затем выберите параметр Параметры поля значения .

Затем измените функцию в разделе Операция. Обратите внимание, что при изменении метода вычисления Excel автоматически добавляет его в раздел Пользовательское имя , например "Sum of FieldName", но вы можете изменить его. Если выбран параметр Числовой формат, можно изменить числовой формат для всего поля.

Дополнительные вычисления

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

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

Отображение значения как результата вычисления и как процента

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

1. Выберите таблицу или диапазон данных на листе и выберите Вставить > сводную таблицу , чтобы открыть панель Вставка сводной таблицы.

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

• В карта Создание собственной сводной таблицы выберите Новый лист или Существующий лист, чтобы выбрать назначение сводной таблицы.

• В рекомендуемой сводной таблице выберите Новый лист или Существующий лист , чтобы выбрать назначение сводной таблицы.

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

1. В области Вставка сводной таблицы выберите текстовое поле в разделе Источник. При изменении источника карточки в области будут недоступны.

2. Выберите данные в сетке или введите диапазон в текстовое поле.

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

Получение из Power BI

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

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

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

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

Операция

По умолчанию поля сводной таблицы в области Значения отображаются в виде СУММ. Если Excel интерпретирует данные как текст, они отображаются как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений.

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

Дополнительные вычисления

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

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

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

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

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

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

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

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

• Все данные в столбце должны иметь один и тот же тип. Например, не следует вводить даты и текст в одном столбце.

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

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

1. Выберите ячейку в диапазоне исходных данных или таблицы.

2. На вкладке Вставка нажмите кнопку Рекомендуемые сводные таблицы.
   
   

3. Excel анализирует данные и предоставляет несколько вариантов, как в этом примере с использованием данных о расходах домашних хозяйств.
   
   

4. Выберите сводную таблицу, которая вам понравилась, и нажмите кнопку ОК. Excel создает сводную таблицу на новом листе и отображает список Полей сводной таблицы .

1. Выберите ячейку в диапазоне исходных данных или таблицы.

2. На вкладке Вставка нажмите кнопку Сводная таблица.
   
   

3. В Excel отображается диалоговое окно Создание сводной таблицы с выбранным именем диапазона или таблицы. В этом случае мы используем таблицу "таблица_СемейныеРасходы".

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

5. Нажмите кнопку ОК, и Excel создаст пустую сводную таблицу и отобразит список Полей сводной таблицы .

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

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

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

• Суммировать по

  По умолчанию поля сводной таблицы, размещенные в области Значения , отображаются в виде СУММ. Если Excel интерпретирует данные как текст, данные отображаются как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений. Вы можете изменить вычисление по умолчанию, сначала щелкнув стрелку справа от имени поля, а затем выбрав параметр Параметры поля .

  Затем измените функцию в разделе Суммировать по. Обратите внимание, что при изменении метода вычисления Excel автоматически добавляет его в раздел Пользовательское имя , например "Sum of FieldName", но вы можете изменить его. При выборе параметра Число... можно изменить формат чисел для всего поля.

  Совет: Так как изменение вычисления в разделе Суммирование по изменяет имя поля сводной таблицы, лучше не переименовывать поля сводной таблицы, пока не завершите настройку сводной таблицы. Один из способов заключается в том, чтобы выбрать Заменить (в меню Правка) >Найти, что > "Сумма", а затем заменить на > оставить пустым, чтобы заменить все сразу, а не вручную повторно.

• Дополнительные вычисления

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

  

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

• Отображение значения как результата вычисления и как процента

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

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

1. Выберите ячейку в диапазоне исходных данных или таблицы.

2. На вкладке Вставка нажмите кнопку Сводная таблица.

   

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

4. Нажмите кнопку Вставить.

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

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

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