Добавить столбец в сводную таблицу excel

Как добавить столбец в сводную таблицу excel?

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

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

Нажимаем на любое поле сводной таблицы, чтобы справа на экране отобразились её настройки. В настройках мы можем обратить внимание, что есть четыре поля: «Фильтр», «Столбцы», «Значения», «Строки». А также сверху есть раздел, в котором указаны все заголовки исходной таблицы.

Чтобы добавить дополнительный столбец по месяцам, нужно в поле, где отображаются заголовки исходной таблицы, найти строчку «Даты». Наведя на неё курсор, зажимаете левую кнопку мыши и перетаскиваете это название в раздел «Столбцы».

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

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

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

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

Доступные методы вычислений

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

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

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

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

В сводной диаграмме поле Регион может представлять собой поле категорий, в котором элементы Север, Юг, Восток и Запад отображаются как категории. Поле Месяц поле может быть полем рядов, в котором элементы Март, Апрель и Май отображаются как ряды, представленные в легенде. Поле значений с именем Сумма продаж может содержать маркеры данных, которые представляют общую выручку в каждом регионе за каждый месяц. Например, один маркер данных может представлять (своим положением на вертикальной оси, т. е. оси значений) сумму продаж за месяц Апрель в регионе Север.

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

Сумма значений. Функция по умолчанию для числовых данных.

Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых.

Количество числовых значений. Действует аналогично функции СЧЁТ.

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

Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные.

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

Дисперсия генеральной совокупности, которая содержит все сводимые данные.

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

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

Значение, введенное в данное поле.

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

% от суммы по столбцу

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

% от суммы по строке

Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории.

Значения в процентах от значения базового элемента в соответствующем базовом поле.

% от суммы по родительской строке

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента по строкам).

% от суммы по родительскому столбцу

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента по столбцам).

% от родительской суммы

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента в выбранном базовом поле).

Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.

Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.

С нарастающим итогом в поле

Значение в виде нарастающего итога для последовательных элементов в базовом поле.

% от суммы с нарастающим итогом в поле

Значение в виде нарастающего итога в процентах для последовательных элементов в базовом поле.

Сортировка от минимального к максимальному

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

Сортировка от максимального к минимальному

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

Рассчитывает значения следующим образом:

((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).

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

Влияние типа источника данных на вычисления

Доступность вычислений и параметров в отчете зависит от того, получены ли исходные данные из базы данных OLAP.

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

Кроме того, если сервер OLAP предоставляет вычисляемые поля, называемые «вычисляемыми элементами», вы увидите их в списке полей сводной таблицы. Вы также увидите все вычисляемые поля и вычисляемые объекты, созданные с помощью макросов, которые написаны на языке Visual Basic для приложений (VBA) и хранятся в книге, но не сможете их изменить. Если вам нужны дополнительные типы вычислений, обратитесь к администратору базы данных OLAP.

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

Вычисления на основе исходных данных не из базы данных OLAP. В сводных таблицах, основанных на внешних данных других типов или на данных листа Excel, для вычисления полей значений, содержащих числовые данные, используется функция «Сумма», а для вычисления полей данных, содержащих текст, — функция «Количество». Для дальнейшего анализа и обработки своих данных вы можете выбрать другие функции сведения, например «Среднее», «Максимум» или «Минимум». Кроме того, можно создавать собственные формулы, в которых используются элементы отчета или другие данные листа. Для этого нужно создать вычисляемое поле или вычисляемый объект в поле.

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

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

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

Имена полей и элементов. В Excel имена полей и элементов используются для идентификации этих элементов отчета в формулах. В приведенном ниже примере для данных в диапазоне C3:C9 используется имя поля Молоко. Для вычисляемого объекта в поле Тип, оценивающего объем продаж нового продукта на основе данных о продажах молочных продуктов, можно использовать формулу =Молоко * 115%.

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

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

Формулы для вычисляемых объектов оперируют отдельными записями. Например, формула вычисляемого объекта =Молоко * 115% умножает каждое отдельное значение продаж молочных продуктов на 115 %, после чего полученные величины суммируются в области «Значения».

Пробелы, цифры и символы в именах. В имени, которое содержит два или несколько полей, их порядок не имеет значения. В примере выше ячейки C6:D6 могут называться ‘Апрель Север’ или ‘Север Апрель’. Имена, которые состоят из нескольких слов либо содержат цифры или символы, нужно заключать в одинарные кавычки.

Итоги. Формулы не могут ссылаться на итоговые значения (в примере выше — это Сумма за март, Сумма за апрель и Общий итог).

Имена полей в ссылках на элементы. Вы можете включить имя поля в ссылку на элемент. Имя элемента должно быть заключено в квадратные скобки, например: Регион[Север]. Используйте этот формат, чтобы избежать ошибок #ИМЯ?, которые возникают, если два элемента в двух разных полях отчета имеют одинаковые имена. Например, если в отчете есть два элемента с именем «Мясо» в полях «Тип» и «Категория», можно избежать появления ошибок #ИМЯ?, ссылаясь на эти элементы следующим образом: Тип[Мясо] и Категория[Мясо].

Ссылки на элементы по позиции. Вы можете сослаться на элемент, указав его позицию в отчете (с учетом того, какие элементы фактически отображаются и как они отсортированы в настоящий момент). Тип[1] — это Молоко, а Тип[2] — Морепродукты. Когда позиции элементов изменятся, например, если какие-то из них будут скрыты или снова отображены, такая ссылка, возможно, будет указывать на другой элемент. Скрытые элементы не учитываются в этом индексе.

Для ссылки на элементы можно использовать относительные позиции. Они определяются относительно вычисляемого объекта, содержащего формулу. Если текущим регионом является Юг, то Регион[-1] — это Север. Если текущим регионом является Север, то Регион[+1] — это Юг. Например, для вычисляемого объекта можно использовать формулу =Регион[-1] * 3%. Например, для вычисляемого объекта можно использовать формулу =Регион[-1] * 3%. Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.

Использование формул в сводных диаграммах

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

Например, на этой сводной диаграмме представлены данные о продажах для каждого продавца по регионам:

Чтобы посмотреть, как будут выглядеть объемы продаж, если увеличатся на 10 %, можно создать вычисляемое поле в связанной сводной таблице и воспользоваться формулой =Продажи * 110%.

Результат сразу отображается на сводной диаграмме, как показано на этом рисунке:

Чтобы отобразить отдельный маркер данных для продаж в регионе «Север» за вычетом транспортных расходов, которые составляют 8 %, можно создать в поле «Регион» вычисляемый объект с такой формулой: =Север – (Север * 8%).

Диаграмма будет выглядеть следующим образом:

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

Управление сводными таблицами в Excel

Сводная таблица – мощный инструмент Microsoft Excel. С ее помощью пользователь анализирует большие по объему диапазоны, подводит итоги всего в несколько кликов, выводит на экран только нужную в данный момент информацию.

Фильтр в сводной таблице Excel

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

Для примера возьмем следующую таблицу:

Создадим сводную таблицу: «Вставка» — «Сводная таблица». Поместим ее на новый лист.

Мы добавили в сводный отчет данные по поставщикам, количеству и стоимости.

Напомним, как выглядит диалоговое окно сводного отчета:

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

По данным, которые помещены в поле «Значения», подводятся итоги. В автоматическом режиме – сумма. Но можно задать «среднее», «максимум» и т.д. Если сделать это нужно для значений всего поля, то щелкаем по названию столбца и меняем способ представления итогов:

Например, среднее количество заказов по каждому поставщику:

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

Установим фильтр в сводном отчете:

1. В перечне полей для добавления в таблицу ставим галочку напротив заголовка «Склад».
2. Перетащим это поле в область «Фильтр отчета».
3. Таблица стала трехмерной – признак «Склад» оказался вверху.

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

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

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

Сортировка в сводной таблице Excel

Немного преобразуем наш сводный отчет: уберем значение по «Поставщикам», добавим «Дату».

Сделаем таблицу более полезной – сгруппируем даты по кварталам. Для этого щелкнем правой кнопкой мыши по любой ячейке с датой. В выпадающем меню выберем «Группировать». Заполним параметры группировки:

После нажатия ОК сводная таблица приобретает следующий вид:

Отсортируем данные в отчете по значению столбца «Стоимость». Кликнем правой кнопкой мыши по любой ячейке или названию столбца. Выбираем «Сортировка» и способ сортировки.

Значения в сводном отчете поменяются в соответствии с отсортированными данными:

Теперь выполним сортировку данных по дате. Правая кнопка мыши – «Сортировка». Можно выбрать способ сортировки и на этом остановиться. Но мы пойдем по другому пути. Нажмем «Дополнительные параметры сортировки». Откроется окно вида:

Установим параметры сортировки: «Дата по убыванию». Кликнем по кнопке «Дополнительно». Поставим галочку напротив «Автоматической сортировки при каждом обновлении отчета».

Теперь при появлении в сводной таблице новых дат программа Excel будет сортировать их по убыванию (от новых к старым):

Формулы в сводных таблицах Excel

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

1. Добавим в отчет заголовок «Поставщик». Заголовок «Стоимость» три раза перетащим в поле «Значения» — в сводную таблицу добавятся три одинаковых столбца.
2. Для первого столбца оставим значение «Сумма» для итогов. Для второго – «Среднее». Для третьего – «Количество».
3. Поменяем местами значения столбцов и значения строк. «Поставщик» — в названия столбцов. «Σ значения» — в названия строк.

Сводный отчет стал более удобным для восприятия:

Научимся прописывать формулы в сводной таблице. Щелкаем по любой ячейке отчета, чтобы активизировать инструмент «Работа со сводными таблицами». На вкладке «Параметры» выбираем «Формулы» — «Вычисляемое поле».

Жмем – открывается диалоговое окно. Вводим имя вычисляемого поля и формулу для нахождения значений.

Получаем добавленный дополнительный столбец с результатом вычислений по формуле.

Экспериментируйте: инструменты сводной таблицы – благодатная почва. Если что-то не получится, всегда можно удалить неудачный вариант и переделать.

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

В прошлой статье рассказывалось про то как создать простую сводную таблицу. Сейчас мы ее немного усложним и заодно разберемся в чем отличие областей СТРОК, КОЛОНН и ЗНАЧЕНИЙ. Вы поймете когда и в какую область необходимо переносить поле сводной таблицы.

Возьмем все те же исходные данные (перечень активов компании) и сделаем сводный отчет в котором посчитаем рыночную стоимость по группам активов, и филиалам.

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

Теперь давайте конструировать отчет. Давайте перенесем поле Филиал в область СТРОКИ, тогда мы получим перечень всех филиалов (без дубликатов) из исходной таблицы.

Теперь давайте расположим все наши группы активов по столбцам, для этого перенесем поле Группа в область КОЛОННЫ. Получим следующую картину:

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

Так получилось намного нагляднее. Осталось добавить сумму по рыночной стоимости в наш отчет. Для этого перенесем поле Рыночная стоимость в область ЗНАЧЕНИЯ.

Сводные таблицы Excel

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

Теперь, когда в нашем распоряжении имеется сводная таблица, наступило время для создания, вычисляемого поля. Чтобы создать вычисляемое поле, активизируйте диалоговое окно Вставка вычисляемого поля (Insert Calculated Field).

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

Чтобы открыть его, на контекстной вкладке ленты Параметры (Options), выбранной в группе контекстных вкладок Работа со сводными таблицами (PivotTable Tools), перейдите в группу Вычисления (Calculations), щелкните на кнопке Поля, элементы и наборы (Fields, Items & Sets) и выберите в раскрывающемся меню команду Вычисляемое поле (Calculated Field), как показано на рис. 5.7.

Рис. 5.7. Создание вычисляемого поля начинается с этого раскрывающегося меню

На экране появится диалоговое окно Вставка вычисляемого поля (Insert Calculation Field), как показано на рис. 5.8.

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

В верхней части диалогового окна имеются два текстовых поля: Имя (Name) и Формула (Formula). В этих полях следует задать имя вычисляемого поля и создать формулу, указав необходимые поля данных и математические операторы. На рис. 5.9 показано, что вычисляемому полю присвоено описательное имя Средняя выручка за час. Это имя должно точно характеризовать тип выполняемой математической операции.

Рис. 5.9. Выбор для вычисляемого поля описательного имени

Далее перейдите к списку Поля (Fields) и дважды щелкните на поле Объем продаж. Введите символ косой черты /, чтобы указать программе Excel, что вы будете делить значение поля Объем продаж на другой элемент.

По умолчанию текстовое поле Формула диалогового окна Вставка вычисляемого поля содержит выражение = 0. Перед вводом собственной формулы следует удалить нуль.

С этого момента диалоговое окно должно выглядеть так, как показано на рис. 5.10.

Рис. 5.10. Начните ввод формулы со значения = ‘Объем продаж’/

Закончите ввод формулы, дважды щелкнув на поле Период продаж (в часах). Готовая формула показана на рис. 5.11.

Рис 5.11. Полная формула = ‘Объем продаж’/’Период продаж (в часах)’ отображает требуемое вычисляемое поле

Щелкните на кнопке Добавить (Add), а затем — на кнопке ОК для активизации нового вычисляемого поля. Как видно на рис. 5.12, в результате вы получите вычисляемое поле внутри сводной таблицы.

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

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

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

Означает ли это, что вы добавили столбец в источник данных? Нет. Вычисляемые поля похожи на элементы Промежуточная сумма (Subtotal) и Общая сумма (Grand Total) сводной таблицы, задаваемые по умолчанию, так как все они являются математическими функциями, которые выполняют перерасчет данных при изменении или обновлении сводной таблицы. Вычисляемые поля просто имитируют строго заданные поля в источнике данных. Можете перетаскивать их, изменять настройки полей, а также использовать вместе с другими вычисляемыми полями.

Взгляните внимательно на рис. 5.11. Обратите внимание на то, что формат введенной вами формулы похож на используемый в стандартной строке формул Excel. Очевидное различие состоит в том, что вместо использования строгих ссылок на ячейки или числовых значений вы ссылаетесь на поля данных сводной таблицы для определения аргументов, применяемых в расчетах. Если вы уже работали с формулами в Excel, то быстро усвоите концепции создания вычисляемых полей.