Добавление столбцов в сводную таблицу в Excel
Обновлено: 03.07.2024
Мы уже показали, как создать и скопировать сводную таблицу. Это может показаться довольно простым и скоро может стать вашей второй натурой.
Однако изменить внешний вид нашего листа со сводной таблицей может быть немного сложнее.
Что делать, если вы хотите добавить столбец или строку в существующую сводную таблицу? Мы покажем вам, как это сделать в тексте ниже.
Добавление данных в модель данных
В этом примере мы будем использовать таблицу с баскетболистами из лиги НБА, некоторыми их характеристиками и их зарплатами.
< бр />
Мы выберем всю таблицу, а затем перейдем на вкладку «Вставка» и затем «Сводная таблица». Появится знакомое всплывающее окно:
< бр />
Поскольку мы создали таблицу для нашего диапазона и назвали ее Таблица1, логично, что нашими исходными данными будет Таблица1.
Чтобы упростить объяснение, мы будем использовать Новый рабочий лист для расположения нашей сводной таблицы.
Наконец, и это самое главное, мы должны щелкнуть маленькое поле: Добавить эти данные в модель данных.
< бр />
Модель данных позволяет интегрировать данные из нескольких таблиц, эффективно создавая реляционный источник данных внутри книги Excel.
В нашем случае это полезно, поскольку позволяет добавлять столбцы в сводную таблицу, не затрагивая числа в ней.
Вы заметите, что Excel займет больше времени для создания сводной таблицы с этой опцией, чем без нее. Это дело нескольких секунд, но все же стоит отметить.
Добавить столбец в сводную таблицу
Теперь, когда у нас есть данные в сводной таблице, мы поместим игроков в поле строки и средние баллы в поля значений:
< бр />
Если вам по какой-либо причине нужно другое значение (например, общая сумма баллов), все, что вам нужно сделать, это щелкнуть поле значений (в данном случае Среднее количество баллов) и выбрать Настройки поля значений.< /p>
Вы увидите окно, показанное на рисунке ниже:
Вы можете выбрать один из нескольких вариантов.
После того, как вы закончите это, ваша финальная таблица будет выглядеть так:
< бр />
Теперь предположим, что мы получили информацию о том, что нам нужно добавить название команды для каждого игрока в списке.
Мы могли бы добавить столбец с названием команды в нашу исходную таблицу, но нам пришлось бы делать это для некоторых игроков более одного раза, поскольку их имена снова появляются в нашей таблице.
Самый быстрый способ — добавить столбец в нашу таблицу. Однако, если мы выберем столбец B, щелкнем по нему правой кнопкой мыши и выберем вставку, мы получим сообщение об ошибке:
< бр />
Это сообщение сообщает нам, что мы не можем изменить эти ячейки, поскольку это повлияет на нашу сводную таблицу. Excel предлагает переместить нашу сводную таблицу, а затем вставить столбец. Но мы этого не хотим.
Мы получили бы такое же сообщение, даже если бы не решили добавить эту таблицу в модель данных.
Однако разница в том, что в модели данных мы можем щелкнуть таблицу, затем перейти к инструментам сводной таблицы на ленте, выбрать «Анализ», а затем выбрать «Инструменты OLAP».
< бр />
OLAP – это сокращение от Online Analytical Processing, которое представляет собой технологию, используемую для организации больших бизнес-баз данных и поддержки бизнес-аналитики.
Наконец, нам нужно выбрать «Преобразовать в формулы» из раскрывающегося меню «Инструменты OLAP», чтобы иметь возможность манипулировать нашей таблицей.
< бр />
Мы можем заметить, что наша таблица выглядит по-другому и что ячейки таблицы теперь имеют разные значения, например:
< бр />
Кроме того, в нашем распоряжении нет инструментов сводных таблиц.
С другой стороны, теперь мы можем добавлять строки и столбцы по своему усмотрению. Мы нажимаем на столбец B и добавляем столбец. Затем мы можем добавить все подходящие команды для наших игроков.
Теперь наша таблица выглядит так:
< бр />
Эта таблица по-прежнему интерактивна, а это означает, что если мы изменим данные для некоторых игроков в нашей исходной таблице, данные в этой таблице также изменятся.
Все, что нам нужно сделать, это обновить его. Поскольку таблица теперь является частью модели данных, для внесения изменений нам нужно щелкнуть таблицу, затем перейти на вкладку «Данные» и выбрать «Обновить все».
После создания сводной таблицы вы увидите список полей. Вы можете изменить дизайн сводной таблицы, добавив и упорядочив ее поля. Если вы хотите отсортировать или отфильтровать столбцы данных, отображаемые в сводной таблице, см. разделы Сортировка данных в сводной таблице и Фильтрация данных в сводной таблице.
Список полей должен отображаться при нажатии в любом месте сводной таблицы. Если вы щелкнете внутри сводной таблицы, но не увидите список полей, откройте его, щелкнув в любом месте сводной таблицы. Затем отобразите инструменты сводной таблицы на ленте и нажмите «Анализ»> «Список полей».
В списке полей есть раздел полей, в котором вы выбираете поля, которые хотите отобразить в сводной таблице, и раздел Области (внизу), в котором вы можете расположить эти поля по своему усмотрению.
Совет. Если вы хотите изменить способ отображения разделов в списке полей, нажмите кнопку "Инструменты" и выберите нужный макет.
Добавить и изменить порядок полей в списке полей
Используйте раздел полей списка полей, чтобы добавить поля в сводную таблицу, установив флажок рядом с именами полей, чтобы поместить эти поля в область списка полей по умолчанию.
ПРИМЕЧАНИЕ. Обычно нечисловые поля добавляются в область строк, числовые поля добавляются в область значений, а иерархии даты и времени онлайн-аналитической обработки (OLAP) добавляются в область столбцов.
Используйте раздел областей (внизу) списка полей, чтобы переупорядочить поля так, как вы хотите, перетащив их между четырьмя областями.
Поля, размещенные в разных областях, отображаются в сводной таблице следующим образом:
Поля области фильтров отображаются как фильтры отчета верхнего уровня над сводной таблицей, например:
Поля области столбцов отображаются в виде меток столбцов в верхней части сводной таблицы, например:
В зависимости от иерархии полей столбцы могут быть вложены в столбцы, расположенные выше по положению.
Поля области строк отображаются в виде меток строк в левой части сводной таблицы, например:
В зависимости от иерархии полей строки могут быть вложены в строки, расположенные выше по положению.
Поля области значений отображаются в виде сводных числовых значений в сводной таблице, например:
Если у вас есть более одного поля в области, вы можете изменить порядок, перетащив поля в нужное место. Чтобы удалить поле из сводной таблицы, перетащите поле за пределы области его областей.
Подробнее о сводных таблицах
Если книга, которую вы открыли в Excel в Интернете, имеет сводную таблицу, вы можете использовать список полей, чтобы добавлять, удалять или упорядочивать ее поля. Он появляется, когда вы щелкаете в любом месте сводной таблицы. Если вы не видите список полей, попробуйте щелкнуть правой кнопкой мыши в любом месте сводной таблицы, чтобы выбрать «Показать список полей».
В списке полей есть раздел полей, где вы можете выбрать поля, которые хотите отобразить в сводной таблице, и раздел областей, где вы можете перетаскивать поля между областями, чтобы упорядочить их по своему усмотрению.
Чтобы добавить поля в сводную таблицу, установите флажок рядом с именем поля, чтобы поместить это поле в область раздела областей списка полей. Обычно:
Нечисловые поля добавляются в область строк
Числовые поля добавляются в область значений
Иерархия даты и времени Online Analytical Processing (OLAP) добавляется в область столбцов.
Чтобы удалить поля из сводной таблицы, просто снимите флажок рядом с этими полями.
Используйте раздел "Области" в списке полей, чтобы переупорядочить поля нужным образом, перетащив их между четырьмя областями.
Поля, помещаемые в разные области, отображаются в сводной таблице следующим образом:
Поля области фильтров отображаются как фильтры отчета верхнего уровня над сводной таблицей.
Поля области столбцов отображаются в виде меток столбцов в верхней части сводной таблицы.
В зависимости от иерархии полей столбцы могут быть вложены в столбцы, расположенные выше по положению.
Поля области строк отображаются в виде меток строк в левой части сводной таблицы.
В зависимости от иерархии полей строки могут быть вложены в строки, расположенные выше по положению.
Поля области значений отображаются в виде сводных числовых значений в сводной таблице.
Если у вас есть несколько полей в области, вы можете изменить их порядок, перетащив их в нужное место. Чтобы удалить поле, перетащите его за пределы раздела областей.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
Некоторые передовые методы работы со сводными таблицами необходимы для успешной карьеры. Бывает ситуация, когда нам нужно мыслить нестандартно, чтобы преодолеть стоящую перед нами задачу. Обычно мы используем столбцы таблицы данных и строим отчет, но часто вы могли видеть столбец извлечения, которого нет в таблице данных, но он существует в полях сводной таблицы. Так что это из-за «вычисляемого поля» в сводной таблице, и в этой статье мы покажем вам, как добавить новый столбец в сводную таблицу, используя указанные выше параметры.
В «Таблице данных» у нас есть только столбцы «Продажи» и «Стоимость», но когда мы смотрим на сводную таблицу, у нас есть «Прибыль» в качестве дополнительного столбца, поэтому, как новый ученик, я просто интересно, откуда взялась эта новая колонка, и потратил много дней, чтобы понять это.
Что такое вычисляемое поле в сводной таблице?
Вычисляемое поле — это настраиваемый столбец или поле, созданное с использованием существующих столбцов сводной таблицы с применением формул, поэтому это вычисляемое поле является виртуальным столбцом для сводной таблицы, которого нет в фактической таблице данных.
Вы можете скачать этот шаблон Excel для добавления столбца сводной таблицы здесь — Шаблон Excel для добавления столбца сводной таблицы
Например, взгляните на приведенную ниже сводную таблицу.
В приведенной выше таблице у нас нет столбца «Прибыль», обычно кроме сводных таблиц; мы просто вставляем новый столбец «Прибыль», вычитая «Стоимость» из «Продажи».
Для сводной таблицы только в следующем столбце мы вставили эту формулу и получили столбец прибыли, но проблема в том, что столбец «Прибыль» не является частью сводной таблицы, поэтому любые изменения в полях сводная таблица покажет значения ошибок.
Например, мы фильтруем первые 3 месяца.
Посмотрите на столбец «Прибыль» только для первых трех столбцов, показывающих значения для других ячеек, он по-прежнему показывает нулевые значения, поэтому, чтобы избежать всего этого, мы создаем новый виртуальный столбец через «Вычисляемое поле».
Используя ту же формулу, мы создадим новый столбец.
Следуйте приведенным ниже шагам, чтобы добавить вычисляемое поле в сводную таблицу.
- Поместите курсор в сводную таблицу, перейдите на вкладку "Анализ" и нажмите "Поля, элементы и наборы".
В приведенном выше окне нам нужно указать две вещи для создания вычисляемого поля: «Имя и формула». Полю будет присвоено имя, а для создания поля используется формула.
Поскольку мы создаем столбец как «Прибыль», дайте ему такое же имя.
Там у нас есть новый виртуальный столбец, которого нет в реальной таблице данных.
В продолжение приведенного выше примера теперь мы вставим столбец «Прибыль %». Таким образом, формула «Прибыль %» будет выглядеть как «Прибыль/Продажи».
Выполните те же шаги, что и выше, и обрамите формулы, как показано ниже.
Нажмите «ОК», и у нас будет готовый «Прибыль %».
Примечание. Измените формат номера столбца «Прибыль %» на «%».
Теперь для той же сводной таблицы, что и выше, мы создадим расширенное вычисляемое поле с помощью оператора IF.
Чтобы получить, расчет столбца BONUS будет следующим.
Если объем продаж >4000, то бонус составляет 1% от продаж; в противном случае было бы 0,5%.
Поэтому выполните описанные выше шаги, как показано выше, и вставьте формулу, как показано ниже.
Нажмите «ОК», и у нас появится новый столбец «Бонус» в сводной таблице.
Подобным образом мы можем вставлять столбцы в сводную таблицу, используя «Вычисляемое поле».
Что нужно помнить
- Если вы не видите поля в таблице данных, это должно быть только вычисляемое поле.
- Вычисляемые поля можно получить с помощью формул и существующих полей таблицы данных.
Рекомендуемые статьи
Это руководство по добавлению столбца в сводную таблицу. Здесь мы обсудим, как добавить новый столбец в сводную таблицу, используя вычисляемые поля, а также некоторые практические примеры и загружаемый шаблон Excel. Вы можете узнать больше об Excel из следующих статей –
Все мы знаем, что функция сводной таблицы — это мощная и полезная функция. Но это связано с некоторыми особенностями. Например, мы не можем вставить пустую строку или столбец в сводные таблицы.
Итак, сегодня позвольте мне поделиться несколькими идеями о том, как можно вставить пустой столбец.
Но сначала попробуем вставить столбец
Представьте, что вы смотрите на сводную таблицу вот так.
И вы хотите вставить столбец или строку. Давай, попробуй. Вот что происходит.
- Excel приходит в ярость, думая, что вы пытаетесь установить анархию, и выдает строгое, но очень длинное и запутанное предупреждающее сообщение.
На самом деле сообщение об ошибке такое длинное, что я даже не могу уместить его на одном изображении в этом блоге. Вот оно, дословное.
Итак, как нам вставить столбец в сводку
Ответ прост.
Не утруждайте себя вставкой столбцов в настоящую сводную таблицу. Вместо этого следуйте этому подходу.
- Выберите любую ячейку в сводке.
- Нажмите Ctrl+Shift+8 — это выделит всю сводную точку.
- Скопируйте его, нажав CTRL+C.
- Перейти к новому листу
- Вставить как ссылки — ALT+CTRL+V и L.
- Выделите все ячейки, содержащие 0, и нажмите клавишу DELETE.
- Теперь вставьте любое количество столбцов и строк в этот новый лист.
- При изменении сводной таблицы (из-за обновления или появления новых данных) лист копирования также меняется.
- Дополнительный бонус: новые ячейки листа можно форматировать как угодно. Это просто работает.
Вот пример того, что вы можете сделать.
Но я хочу вставить столбец в сводную таблицу.
Хорошо, очевидно, у вас случай OCDIS (синдром навязчивого удаления/вставки столбца).
Вот один из способов технически вставить столбец в сводную таблицу.
Прежде чем понять процесс, давайте сделаем паузу и спросим: "Зачем вы хотите вставить столбец?"
Вот несколько возможных причин.
- Причины внешнего вида/форматирования. Пустой столбец облегчает чтение.
- Для добавления комментариев/примечаний/дополнительных данных
- Чтобы выполнить промежуточные вычисления с данными
Если ваш ответ – 1, описанный выше подход (копирование сводки и вставка в качестве ссылок) дает вам максимальный контроль над макетом и форматированием. Делайте это.
Если ваш ответ – 2, опять же, описанный выше подход по-прежнему хорош.
Если ваш ответ – 3, лучше всего использовать вычисляемые элементы/поля.
Если ваш ответ – 3 и вы используете Excel 2013 (или Power Pivot), вы можете использовать функцию "Наборы" или формулы многомерных выражений для имитации пустых строк. К сожалению, я не могу этого объяснить, потому что белки знают больше MDX, чем я.
Допустим, вы хотите рассчитать определенный процент или что-то подобное…
Хорошо, теперь нужно рассчитать % Север/Запад в приведенной ниже опорной точке.
В этом случае вы можете использовать вычисляемые элементы сводной таблицы следующим образом.
- Выберите любое название региона в метках столбцов, которые являются опорными.
- Перейдите на главную > Вставить > Вычисляемый элемент.
- Назовите вычисляемый элемент, например, "К северу через запад %"
- Запишите формулу =Север/Запад
Как видите, это работает нормально, пока мы не достигнем строки общей суммы. Там наш Север/Запад % должен быть 96%. Вместо этого он читает 386%. Явно число, рассчитанное моим 6-летним сыном.
Почему сумма неверна? Потому что общие итоги сводной таблицы представляют собой простую сумму всех вышеперечисленных значений. Поэтому Excel пошел дальше и сложил четыре процента.
Как это исправить? Один из простых вариантов — отключить общие итоги. Обратите внимание, что четные общие итоги на уровне строки отключены, так как % был добавлен к фактическим значениям.
Если вам необходимо просмотреть общие итоги, лучше всего использовать Power Pivot. Он позволяет определять формулы (используя DAX) и создавать мощные сводные таблицы.
Значит, нет простого способа вставить столбцы?
Нам потребовалось несколько минут, чтобы добраться сюда, но это ответ. Нет простого решения этой проблемы. Вместо этого вам следует выполнить четырехэтапный процесс.
Читайте также: