Как создать вычисляемое поле в сводной таблице Excel
Содержание
Сводные таблицы — эффективный инструментарий для анализа и визуализации данных. С ее помощью можно быстро создать подробный отчет на основе имеющихся данных. Также пользователи имеют возможность добавить собственное вычисляемое поле в сводной таблице программы Excel. Этот инструмент существенно упрощает работу с исходными данными и позволяет сократить срок подготовки документа. В данном материале рассмотрим, для чего необходимое вычисляемое поле и как правильно им пользоваться.
Создание сводной таблицы в Excel
Прежде чем перейти к рассмотрению вычисляемых полей, определим порядок действий при создании сводных таблиц. Данный инструмент Excel позволяет быстро систематизировать большой объем данных, делая выборку только той информации, которая нужна для итоговой отчетности.
В нашем примере предусмотрена таблица с информацией о продажах некоторых видов товаров. На основе данного документа рассмотрим порядок создания простой сводной таблицы в Эксель.
- Открываем исходный документ. Далее следует во вкладку «Вставка». Затем в левой части Панели инструментов выбираем «Сводная таблица».
Нажимаем сводная таблица во вкладке вставка - Протянем мышкой и выделим необходимый диапазон ячеек, которые будут добавлены в сводную таблицу. Можно выделить целый столбец или несколько колонок сразу. Ниже в окне параметров указываем куда поместить отчет — на новый лист. Можно добавить на существующий, но это менее удобно. После этого нажимаем «OK».
Указываем диапазон и лист - Программа автоматически перенаправляет на лист, в котором находится отчет по сводной таблицы. Чтобы его построить, достаточно правильно сгруппировать данные в исходном документе. Начнем с добавления фильтра. В данном примере это колонка «Категории». В правой части дисплея в конструкторе перетаскиваем поле в требуемую часть отчета.
Добавляем фильтр в отчет - Поле «Наименование» добавляем в группу «Строки», а колонки «Цена» и «Объем продаж» отправляется в группу «Значения». В результате в отчет будут добавлены все данные из документа-источника. Для удобства также проставим поля для всех ячеек.
Добавляем строки и значения
В сводных таблицах можно быстро группировать различные поля, менять их местами, добавлять новые объекты, не затрагивая при этом исходный массив данных. Это главное преимущество данного инструмента. Для примера рассмотрим способ вычисления в сводной таблице при помощи формул. В таком случае потребуется вычисляемое поле, предназначенное для выполнения различных операций. Подробнее данный инструмент в Excel рассмотрим далее ниже.
Общие сведения
Область данных, которая создается путем вычислительных операций с участием уже имеющихся полей сводной таблицы, называется вычисляемым элементом. Они отображаются только в отчете, а исходном документе эти элементы не фиксируются. Вычисляемые поля в сводной таблице пересчитываются автоматически. Это происходит если значения в исходном документе были изменены либо удалены.
По сути, вычисляемое поле является виртуальным. Тем не менее оно взаимодействует с данными, полученными из исходного документа. Также вычисляемые поля могут взаимодействовать друг с другом. Это позволяет выполнять сложный расчет или другие операции без изменения таблицы-источника.
В целом, вычисляемое поле в сводной Excel-таблице можно рассматривать как одно из вспомогательных средств при построении отчетов разного уровня сложности. Рассмотрим, как использовать данный инструмент и чем он может быть полезен при работе с документами в Экселе.
Добавление поля для вычислений
Используя вкладку «Конструктор» можно быстро изменить сводную таблицу, сделать перегруппировку полей или применить один из предложенных на выбор макетов. Однако в данный момент задача заключается в том, чтобы добавить вычисляемое поле. К примеру, добавим столбец, который отображает прибыль за последний месяц:
- Переходим на лист, где располагается сводная таблица. Далее нам потребуется вкладка «Анализ». Потом в Панели инструментов нажимаем «Поля, элементы и наборы». Затем нажимаем «Вычисляемое поле».
Нажимаем вычисляемое поле во вкладке анализ - Чтобы добавить вычисляемое поле, необходимо указать несколько параметров. Прежде всего следует озаглавить сам объект. Далее прописывается формула, которая будет использована для получения значения нового поля. В нашем примере нужно найти прибыль — это результат умножения объема продаж на стоимость товара. В сводной таблице не используются ссылки на ячейки. Однако можно выполнить операцию с полями, выбрав их в имеющемся списке.
Вписываем формулу и добавляем поле - Новое вычисляемое поле в сводной Excel-таблице автоматически будет добавлено в группу «Значения». Таким образом получится еще один столбец с результатами применения оператора умножения ячеек.Результат выполнения
Аналогичным способом можно добавить вычисляемый объект. Разница с полем заключается только в том, что операция применяется в виде строк, а не столбца. В остальном вычисляемый объект работает по аналогичному принципу.
Одно из преимуществ описанного инструмента — возможность осуществлять дополнительные вычисления независимо от основных операций. Например, в нашем отчете автоматически суммируются все значения. Результат этих вычислений отображается внизу таблицы.
Это самая простая операция, однако во время построения сводных таблиц можно использовать и более сложные формулы. К примеру, в вычисляемое поле можно добавить маркер для сортировки значений или поиск процента от какого-либо числа, если оно изначально задано в документе. Данная функция доступна как в виртуальных полях сводной таблицы, так и в обычных.
Как удалить поле
Мы выяснили выше, как вставить вычисляемое поле и добавить формулу в сводную таблицу в редакторе Excel. Теперь рассмотрим ситуацию, при которой из отчета необходимо удалить ранее добавленные виртуальные ячейки. Для этого в программе предусмотрена 2 способа удаления. Первый предусматривает ограничение видимости, когда вычисляемые поля перестают отображаться. Второй вариант — полное удаление из сводной таблицы таких объектов.
Чтобы скрыть поле из отчета:
- Вначале открываем вкладку «Анализ». Затем в правом верхнем углу нажимаем «Показать». После этого в контекстном меню выбираем «Список полей».
Нажимаем показать список полей - В списке убираем отметку напротив вычисляемого поля, которое необходимо скрыть. В нашем случае это столбец «Прибыль».
Скрываем вычисляемое поле
Аналогичным образом можно ограничить видимость не только вычисляемого поля, но и ячеек, полученных из документа-источника. Если потребуется вставить их обратно в отчет, достаточно поменять отметку, проставив галочку напротив нужного пункта в списке.
Скрыть вычисляемое поле можно и другим способом. Достаточно выделить заголовок и нажать правую кнопку мыши. Потом в окне контекстного меню щелкаем по команде «Удалить_Наименование поля_».
Благодаря этому поле, которое ранее было добавлено с помощью формул, перестанет отображаться в отчете. Однако оно при этом останется в сводной таблицы. Открыв вкладку «Анализ» его можно добавить обратно, используя список доступных полей такой таблицы.
Если потребуется поля и формулы в сводной таблице можно удалить полностью. Такая потребность может возникнуть в том случае, если исходный набор данных был изменен. Также эта необходимость появляется при изменении группировки элементов документа в сводном отчете. Рассмотрим, как в сводной таблице происходит удаление вычисляемых полей:
- Выделяем ячейку с заголовком вычисляемого поля. Затем открывается вкладка «Анализ». После этого в Панели инструментов нажимаем «Поля, элементы и наборы» и выбираем «Вычисляемое поле».Нажимаем вычисляемое поле
- В строке «Имя» выбираем название того поля, которое хотим убрать. Затем в правой части окна нажимаем «Удалить». В результате столбец полностью исчезнет из сводной таблицы без возможности восстановления.
Удаляем вычисляемое поле
Особенности использования вычисляемых элементов
Не во всех случаях вставка вычисляемого поля или объекта является оправданным действием. Это объясняется различными особенностями данного инструмента, которые следует учитывать при работе со сводными таблицами. Вот некоторые из них:
- Ограниченное количество формул. При построении вычисляемых элементов используются, преимущественно, простые математические операции. Их достаточно для выполнения основных видов расчетов, однако этого может оказаться недостаточным в том случае, если требуются специфические функции.
- Автоматическое изменение. В том случае, когда данные в документе-источнике меняются, происходит изменение результатов вычислений в сводной таблице. Это нельзя рассматривать как недостаток, однако в некоторых случаях может понадобиться таблица, в которой будет задействованы только изначальные значения. Это повлечет за собой необходимость выполнения защиты листа, что может существенно затруднить неопытных пользователей.
- Использование обычных формул. Ячейки в сводной таблице могут использоваться как вычисляемый объект. Для получения новых значений можно подставлять в формулу существующие. Однако диапазон с такими значениями не будет определяться конструктором в качестве поля. Поэтому его нельзя будет группировать удобным образом. По сути, такие значения не являются элементом сводной таблицы, а только визуально дополняют ее.
Таким образом, с одной стороны применение вычисляемых элементов позволяет автоматизировать различные операции. С другой стороны, эти операции возможны только с данными из сводной таблицы, а не за ее пределами. К тому же вычисляемые элементы визуально не отличаются от обычных столбцов и строк. Отсюда следует необходимость использования дополнительных средств форматирования.
Упростить работу с операциями позволит инструмент отображения формул. Это особенно удобно в том случае, если в таблице множество различных расчетов. Если нужно отобразить порядок выполнения вычислительных операций, переходим во вкладку «Анализ», затем жмем «Поля, элементы и наборы» и выбираем пункт «Вывести формулы».
Все операции будут отображены на новом листе, который, при необходимости, можно удалить. Такая схема поможет определить порядок выполнения расчетов, что особенно удобно, если в отчете содержится большое количество вычисляемых элементов.
Подводим итоги
В Excel, как и любом другом табличном редакторе, много полезных инструментов. Одним из таких инструментов является сводная таблица — очень мощный аналитический инструмент, позволяющий быстро обрабатывать данные из одного или нескольких листов, объединяя их в единый комплексный отчет. Пользователи Excel могут добавлять собственные поля для вычислений, применяя различные формулы и операции. Они действуют только в сводной таблице и не отображаются в документе-источнике. При необходимости вычисляемые элементы можно скрыть из области видимости, либо полностью удалить, если они больше не требуются.
Загрузка...
