Оглавление
×
Сводные таблицы
Сводные таблицы

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

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

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

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

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

    сводная таблица кнопка

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

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

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

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

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

Выбор сводная таблица источника

Примечание: В зависимости от параметров ИТ-среды
организации в кнопку может быть включено имя вашей организации. Например, "Из Power BI (Майкрософт)"

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

Сводная таблица из внешнего источника

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

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

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

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

сводная таблица из Power BI

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

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

    Сводная таблица

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

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

Обновление сводная таблица

Операция

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

Диалоговое окно "Параметры поля значений"

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

Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить (CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.

Диалоговое окно "Параметры поля значений" для параметров "Итоги по"

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

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

Пример сводной таблицы со значениями в процентах от общей суммы Пример сводной таблицы со значениями в процентах от общей суммы

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

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

Просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

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

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

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

Примечание:  Рекомендуемые сводные таблицы доступны только Microsoft 365 подписчикам.

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

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

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

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

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

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

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

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

сводная таблица из Power BI

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

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

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

сводная таблица полей в Excel для Интернета

Операция

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

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

Суммирование по в Excel для Интернета

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

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

Пример сводной таблицы со значениями в процентах от общей суммы

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

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

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

Показать как

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

Обновление сводной таблицы

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

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

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

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

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

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

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

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

    Выберите "Вставка" > "Рекомендуемые сводные таблицы" для автоматического создания сводной таблицы

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

    Диалоговое окно "Рекомендуемые сводные таблицы" в Excel

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

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

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

    Вкладка "Данные", группа "Анализ"

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

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

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

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

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

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

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

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

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

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

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

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

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

    Пример сводной таблицы со значениями в процентах от общей суммы

    Вы можете настроить такие параметры в диалоговом окне Параметры поля на вкладке Дополнительные вычисления.

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

Статьи по теме

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

Использование срезов для фильтрации данных сводной таблицы 

Создание временной шкалы сводной таблицы для фильтрации дат

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

Создание сводной таблицы, подключенной к наборам данных Power BI

Упорядочение полей сводной таблицы с помощью списка полей

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

Вычисление значений в сводной таблице

Удаление сводной таблицы

Нужна дополнительная помощь?

Совершенствование навыков
Перейти к обучению

Были ли сведения полезными?

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?

Спасибо за ваш отзыв!

×