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

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

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

   Примечание

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

2. Выберите "Вставить>сводную таблицу".
   

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

   Примечание

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

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

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

Сводные таблицы из других источников

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

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

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

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

Получить из Power BI

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

Настройка сводной таблицы

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

   Примечание

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

   

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

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

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

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

Операция

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

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

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

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

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

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

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

Вставка сводной таблицы

1. Откройте электронную таблицу в Excel для Интернета.
2. Выберите таблицу или диапазон данных на листе и нажмите " Вставить > сводную таблицу ", чтобы открыть панель "Вставка сводной таблицы ".
3. Вы можете создать собственную сводную таблицу вручную или выбрать рекомендуемую сводную таблицу . Выполните одно из следующих действий:

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

Изменение исходных данных

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

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

Получить из Power BI

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

Работа с областью полей сводной таблицы

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

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

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

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

Операция

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

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

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

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

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

Выберите вариант из списка.

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

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

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

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

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

Начните работу со сводными таблицами в Excel в Интернете.

Перед началом работы

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

Создание с помощью рекомендуемой сводной таблицы

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

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

2. Перейдите в раздел "Вставить>рекомендуемую сводную таблицу".

   

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

   

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

Создание сводной таблицы вручную

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

2. Перейдите к вкладке "Вставка>сводной таблицы".

   

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

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

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

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

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

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

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

Значения в сводной таблице

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

  Совет

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

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

  

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

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

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

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

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

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

Сводная таблица на iPad доступна пользователям, использующим Excel на iPad версии 2.82.205.0 и более поздних. Чтобы получить доступ к этой функции, убедитесь, что ваше приложение обновлено до последней версии через App Store.

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

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

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

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

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

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

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

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