Как добавить столбец в сводную таблицу (Pivot Table)
В создании этой статьи участвовала наша опытная команда редакторов и исследователей, которые проверили ее на точность и полноту.
Команда контент-менеджеров wikiHow тщательно следит за работой редакторов, чтобы гарантировать соответствие каждой статьи нашим высоким стандартам качества.
Количество просмотров этой статьи: 59 051.
Данная статья расскажет вам о том, как с помощью инструмента создания сводных таблиц можно создать и добавить новый столбец в сводную таблицу Microsoft Excel. В столбец сводной таблицы можно преобразовать уже существующее поле или колонку либо создать совершенно новый расчетный столбец с необходимой вам формулой.
Как добавить столбец в сводную таблицу excel?
Работа со сводными таблица очень важно при обработки большого массива данных. Часто по их созданию и настройки возникают разнообразные вопросы. Давайте сейчас разберемся, как в сводную таблицу добавить дополнительный столбец.
Перед нами сводная таблица, в которой есть информация по видам статьям затрат, а также общая сумма затрат по каждой. Задача заключается в том, что представленные затраты по статьям в исходной таблице распределены по месяцам, нам необходимо в сводной таблице добавить столбцы за каждый расчетный месяц.
Нажимаем на любое поле сводной таблицы, чтобы справа на экране отобразились её настройки. В настройках мы можем обратить внимание, что есть четыре поля: «Фильтр», «Столбцы», «Значения», «Строки». А также сверху есть раздел, в котором указаны все заголовки исходной таблицы.
Чтобы добавить дополнительный столбец по месяцам, нужно в поле, где отображаются заголовки исходной таблицы, найти строчку «Даты». Наведя на неё курсор, зажимаете левую кнопку мыши и перетаскиваете это название в раздел «Столбцы».
Теперь смотрим на обновленную сводную таблицу, в ней появились два дополнительных столбца: февраль и март. А также столбец содержащий общую сумму за два месяца.
Как добавить столбец в сводную таблицу excel
Сводная таблица — это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций. Сводные таблицы работают немного по-разному в зависимости от платформы, которую вы используете для запуска Excel.
Выделите ячейки, на основе которых вы хотите создать сводную таблицу.
Примечание: Данные должны быть организованы в столбцы с одной строкой заголовка.
На вкладке Вставка нажмите кнопку Сводная таблица.
В результате будет создаваться сводная таблица на основе существующей таблицы или диапазона.
Примечание: При выборе параметра «Добавить эти данные в модель данных» таблица или диапазон, используемые для этой сводной таблицы, будут добавлены в модель данных книги. Дополнительные сведения.
Выберите расположение отчета сводной таблицы. Выберите «Создать лист«, чтобы поместить сводную таблицу на новый или существующий лист, и выберите место, где должна отображаться новая сводная таблица.
Нажмите кнопку ОК.
Нажав стрелку вниз на кнопке, можно выбрать другие источники сводной таблицы. Помимо существующей таблицы или диапазона, существует три других источника, из которого можно выбрать заполнение сводной таблицы.
Примечание: В зависимости от параметров ИТ-среды
организации в кнопку может быть включено имя вашей организации. Например, «Из Power BI (Майкрософт)»
Получение из внешнего источника данных
Получение из модели данных
Используйте этот параметр, если книга содержит модель данных и вы хотите создать сводную таблицу из нескольких таблиц, улучшить сводную таблицу с помощью пользовательских мер или работать с очень большими наборами данных. 
Получение из Power BI
Используйте этот параметр, если ваша организация использует Power BI и вы хотите обнаруживать и подключаться к рекомендованным облачным наборам данных, к которым у вас есть доступ.
Чтобы добавить поле в сводную таблицу, установите флажок рядом с именем поля в области Поля сводной таблицы.
Примечание: Выбранные поля добавляются в области по умолчанию: нечисловые поля добавляются в строки, иерархии даты и времени добавляются в столбцы, а числовые поля — в значения.
Чтобы переместить поле из одной области в другую, перетащите его в целевую область.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить. При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на ленте откройте вкладку Анализ сводной таблицы, щелкните стрелку под кнопкой Обновить и выберите команду Обновить все.
По умолчанию поля сводной таблицы, размещенные в области значений, будут отображаться в виде суммы. Если Excel интерпретирует ваши данные как текст, они будут отображаться как COUNT. Именно поэтому так важно не смешивать типы данных для полей значений. Чтобы изменить вычисление по умолчанию, сначала щелкните стрелку справа от имени поля, а затем выберите параметр «Параметры поля значения «.
Затем измените функцию в разделе Операция. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, «Сумма по полю имя_поля»), но вы можете изменить имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Числовой формат.
Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить (CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.
Дополнительные вычисления
Значения можно также выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Вы можете настроить такие параметры в диалоговом окне Параметры поля значений на вкладке Дополнительные вычисления.
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.
Выберите таблицу или диапазон данных на листе и нажмите кнопку «Вставить > сводную таблицу», чтобы открыть панель «Вставка сводной таблицы «.
Вы можете вручную создать собственную сводную таблицу или выбрать рекомендуемую сводную таблицу для создания. Выполните одно из указанных ниже действий.
На странице «Создание собственной карточки сводной таблицы» выберите «Создать лист» или «Существующий лист», чтобы выбрать место назначения сводной таблицы.
В рекомендуемой сводной таблице выберите новый или существующий лист , чтобы выбрать место назначения сводной таблицы.
Примечание: Рекомендуемые сводные таблицы доступны только подписчикам Microsoft 365.
Источник данных своднойтаблицы можно изменить при его создании.
В области «Вставка сводной таблицы » выберите текстовое поле в разделе «Источник». При изменении источника карточки на панели будут недоступны.
Выберите данные в сетке или введите диапазон в текстовом поле.
Нажмите клавишу ВВОД или кнопку, чтобы подтвердить выбор. На панели будут обновлены новые рекомендуемые сводные таблицы на основе нового источника данных.
Получение из Power BI
Используйте этот параметр, если ваша организация использует Power BI и вы хотите обнаруживать и подключаться к рекомендованным облачным наборам данных, к которым у вас есть доступ.
В области полей сводной таблицы установите флажок для любого поля, которое вы хотите добавить в сводную таблицу.
По умолчанию в область «Строки» добавляются нечисловые поля, в область «Столбцы» добавляются поля даты и времени, а в область значений — числовые поля.
Вы также можете вручную перетащить любой доступный элемент в любое из полей сводной таблицы или, если элемент в сводной таблице больше не нужен, перетащите его из списка или снимите его.
По умолчанию поля сводной таблицы в области значений будут отображаться в виде суммы. Если Excel интерпретирует ваши данные как текст, они будут отображаться как COUNT. Именно поэтому так важно не смешивать типы данных для полей значений.
Измените вычисление по умолчанию, щелкнув правой кнопкой мыши любое значение в строке и выбрав параметр «Суммировать значения по «.
Дополнительные вычисления
Значения можно также выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Щелкните правой кнопкой мыши любое значение в столбце, для который вы хотите отобразить значение. Выберите «Показать значения как » в меню. Отобразится список доступных значений.
Сделайте выбор из списка.
Чтобы отобразить значение процента родительского итога, наведите указатель мыши на этот элемент в списке и выберите родительское поле, которое вы хотите использовать в качестве основы вычисления.
При добавлении новых данных в источник данных сводной таблицы необходимо обновить все сводные таблицы, созданные на основе этого источника данных. Щелкните правой кнопкой мыши в любом месте диапазона сводной таблицы и выберите команду «Обновить».
Если вы создали сводную таблицу и решили, что она больше не нужна, выделите весь диапазон сводной таблицы и нажмите клавишу DELETE. Это не повлияет на другие данные, сводные таблицы или диаграммы вокруг них. Если сводная таблица находится на отдельном листе, на котором нет других данных, которые вы хотите сохранить, удаление листа — это быстрый способ удаления сводной таблицы.
Данные должны быть представлены в виде таблицы, в которой нет пустых строк или столбцов. Рекомендуется использовать таблицу Excel, как в примере выше.
Таблицы — это отличный источник данных для сводных таблиц, так как строки, добавляемые в таблицу, автоматически включаются в сводную таблицу при обновлении данных, а все новые столбцы добавляются в список Поля сводной таблицы. В противном случае необходимо либо изменить исходные данные для сводной таблицы, либо использовать формулу динамического именованного диапазона.
Все данные в столбце должны иметь один и тот же тип. Например, не следует вводить даты и текст в одном столбце.
Сводные таблицы применяются к моментальному снимку данных, который называется кэшем, а фактические данные не изменяются.
Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей. При этом Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы вы можете изучить различные ориентации и изменить порядок полей для получения нужных результатов. Вы также можете скачать интерактивный учебник Создание первой сводной таблицы.
Щелкните ячейку в диапазоне исходных данных и таблицы.
На вкладке Вставка нажмите кнопку Рекомендуемые сводные таблицы.
Excel проанализирует данные и предоставит несколько вариантов, как в этом примере:
Выберите сводную таблицу, которая вам понравилась, и нажмите кнопку ОК. Excel создаст сводную таблицу на новом листе и отобразит список полей сводной таблицы
Щелкните ячейку в диапазоне исходных данных и таблицы.
На вкладке Вставка нажмите кнопку Сводная таблица.
Появится диалоговое окно Создание сводной таблицы, в котором указан ваш диапазон или имя таблицы. В этом случае мы используем таблицу «таблица_СемейныеРасходы».
В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист или На существующий лист. При выборе варианта На существующий лист вам потребуется указать ячейку для вставки сводной таблицы.
Нажмите кнопку ОК. Excel создаст пустую сводную таблицу и выведет список Поля сводной таблицы.
Список полей сводной таблицы
В верхней части области «Имя поля» установите флажок для любого поля, которое вы хотите добавить в сводную таблицу. По умолчанию нечисловые поля добавляются в область строк, поля даты и времени добавляются в область «Столбец», а числовые поля — в область значений. Вы также можете вручную перетащить любой доступный элемент в любое из полей сводной таблицы или, если элемент в сводной таблице больше не нужен, просто перетащите его из списка полей или снимите его. Возможность переупорядочить элементы поля — одна из функций сводной таблицы, которая позволяет быстро изменить внешний вид.
Список полей сводной таблицы
Суммировать по
По умолчанию поля сводной таблицы, размещенные в области значений, будут отображаться в виде суммы. Если Excel интерпретирует ваши данные как текст, они будут отображаться как COUNT. Именно поэтому так важно не смешивать типы данных для полей значений. Чтобы изменить вычисление по умолчанию, сначала щелкните стрелку справа от имени поля, а затем выберите параметр «Параметры поля».
Затем измените функцию в разделе Суммировать по. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, «Сумма по полю имя_поля»), но вы можете изменить это имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Число. .
Совет: Так как при изменении способа вычисления в разделе Суммировать по обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно выбрать пункт Найти (в меню «Изменить»), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.
Дополнительные вычисления
Значения также можно выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Вы можете настроить такие параметры в диалоговом окне Параметры поля на вкладке Дополнительные вычисления.
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, щелкните значение правой кнопкой мыши и выберите команду Параметры поля, а затем настройте параметры Суммировать по и Дополнительные вычисления для каждой из копий.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить. При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на ленте откройте вкладку Анализ сводной таблицы, щелкните стрелку под кнопкой Обновить и выберите команду Обновить все.
Если вы создали сводную таблицу и решили, что она больше не нужна, можно просто выбрать весь диапазон сводной таблицы, а затем нажать клавишу DELETE. Он не будет влиять на другие данные, сводные таблицы или диаграммы вокруг них. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Рекомендации сводной таблицы являются частью подключенного интерфейса в Office и анализируют данные с помощью служб искусственного интеллекта. Если вы решили отказаться от подключенного интерфейса в Office, ваши данные не будут отправляться в службу искусственного интеллекта и вы не сможете использовать рекомендации сводной таблицы. Дополнительные сведения см. в заявлении о конфиденциальности Майкрософт.
Сводная таблица Добавить столбец
Некоторые из передовых методов работы с сводными таблицами имеют важное значение для нашей карьеры. Есть ситуация, когда нам нужно мыслить нестандартно, чтобы преодолеть стоящую перед нами задачу. Обычно мы используем столбцы таблицы данных и отчета построения, но часто вы могли видеть столбец извлечения, которого нет в таблице данных, но существует в полях сводной таблицы. Это из-за «вычисляемого поля» в сводной таблице, и в этой статье мы покажем вам, как добавить новый столбец в сводную таблицу, используя указанные выше параметры.
Позвольте мне рассказать вам сценарий, через который я прошел; посмотрите на изображение ниже «Таблица данных в Excel» и «Сводная таблица».
В «Таблице данных» у нас есть только столбцы «Продажи» и «Стоимость», но когда мы смотрим на сводную таблицу, у нас есть «Прибыль» в качестве дополнительного столбца, поэтому, как новый ученик, мне просто интересно, где же эта новая колонка пришла из и потратила много дней, чтобы понять это.
Об этом очень сложно узнать, особенно если вы учитесь самостоятельно; эти новые столбцы в сводной таблице появляются из-за «Вычисляемых полей в сводной таблице» и «Вычисляемых элементов».
Что такое вычисляемое поле в сводной таблице?
Вычисляемое поле — это настраиваемый столбец или поле, созданное с использованием существующих столбцов сводной таблицы с применением формул, поэтому это вычисляемое поле является виртуальным столбцом для сводной таблицы, которого нет в реальной таблице данных.
Например, посмотрите на приведенную ниже сводную таблицу.
В приведенной выше таблице у нас нет столбца «Прибыль», кроме сводных таблиц; мы просто вставляем новый столбец «Прибыль», вычитая «Стоимость» из «Продажи».
Для сводной таблицы только в следующий столбец мы вставили эту формулу и прибыли в столбец прибыли, но проблема в том, что столбец «Прибыль» не является частью сводной таблицы, поэтому любые изменения в полях сводной таблицы покажет значения ошибок.
Например, мы фильтруем первые 3 месяца.
Посмотрите на столбец «Прибыль» только для первых трех столбцов, показывающих значения для других ячеек, он все еще показывает нулевые значения, поэтому, чтобы избежать всего этого, мы создаем новый виртуальный столбец через «Расчетное поле».
Пример # 1
Используя ту же формулу, мы создадим новый столбец.
Шаг 1: Поместите курсор внутри сводной таблицы, чтобы заполнить вкладки «Анализ и дизайн» на ленте.
Шаг 2: Перейдите в «Анализировать» и нажмите «Поля, предметы и наборы».
Шаг 3: В раскрывающемся списке выберите «Расчетное поле».
Шаг 4: Это откроет для нас окно вставки вычисляемого поля.
В приведенном выше окне мы должны указать две вещи для создания вычисляемого поля: «Имя и формула». Имя будет дано для поля, и формула будет использоваться для создания поля.
Поскольку мы создаем столбец «Прибыль», дайте то же имя.
Шаг 5: Итак, формула прибыли — «Продажи — Стоимость», поэтому используйте существующие поля и обрамляйте формулу.
Шаг 6: Нажмите «ОК» или «Добавить», новый вычисляемый столбец был автоматически вставлен в сводную таблицу.
У нас есть новый виртуальный столбец, которого нет в реальной таблице данных.
Пример # 2
Продолжая приведенный выше пример, теперь мы вставим столбец «Прибыль%». Итак, формула «% прибыли» будет выглядеть так: «Прибыль / Продажи».
Выполните те же действия, описанные выше, и обрамляйте формулы, как показано ниже.
Нажмите «ОК», и у нас будет готовый «Прибыль%».
Заметка: Измените формат номера столбца «Прибыль%» на «%».
Пример # 3
Теперь для той же сводной таблицы выше мы создадим некоторое расширенное вычисляемое поле с помощью оператора IF.
Чтобы прибыть, расчет столбца БОНУС будет следующим.
Если сумма продаж> 4000, то бонус составляет 1% от продаж; в противном случае — 0,5%.
Поэтому выполните указанные выше действия, как показано выше, и вставьте формулу, как показано ниже.
Нажмите «ОК», и у нас появится новый столбец «Бонус» в сводной таблице.
Таким образом, мы можем вставлять столбцы в сводную таблицу с помощью «Вычисляемого поля».