Вставка вычисляемого поля в сводной таблице: Вычисление значений в сводной таблице

Вычисляемое поле и формула сводной таблицы

Как добавить вычисляемое поле в сводную таблицу?

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

• Шаг 1: Выберите данные, которые будут использоваться в сводной таблице.

• Шаг 2: Перейдите на ленту и выберите вкладку «Вставить». На вкладке «Вставка» выберите вставку «Сводной таблицы».

• Шаг 3: Выберите поля сводной таблицы, такие как «Продавец» для строк и «Продажи за 1, 2, 3 и 4 квартал» для значений.

Теперь сводная таблица готова.

• Шаг 4: После того, как сводная таблица вставлена, перейдите на вкладку «Анализ», которая будет присутствовать, только если выбрана сводная таблица.

• Шаг 5: На вкладке «Анализ» выберите вариант «Поля, элементы и наборы» и выберите «Вычисляемые поля» сводной таблицы.

• Шаг 5: С помощью параметра «Вычисляемое поле в сводной таблице» вставьте формулу, как требуется в случае.

Здесь мы сформулировали формулу, по которой будет рассчитана комиссия 0,05% с продаж.

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

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

Использование функции GetPivotTable для ссылки на ячейку в формуле

Мы также можем отказаться от ввода местоположения ячейки вручную; в этом случае мы можем выбрать вставку местоположения с помощью клавиатуры вместо мыши.

Этот тип местоположения (GetpivotData) вставляется, если мы выбираем местоположение вместо того, чтобы вводить местоположение ячейки вручную.

Отключение функции таблицы «GetPivot» в сводной таблице для получения чистой формулы

Мы всегда можем отключить функцию «Getpivotdata», перейдя на вкладку «Анализ» и выбрав раскрывающийся список.

Здесь нам нужно отключить параметр «генерировать GETPIVOTDATA», и мы можем просто использовать формулы в сводной таблице, как мы это делаем в случае простого диапазона.

Вы можете скачать этот шаблон Excel для вычисляемого поля сводной таблицы здесь — Шаблон Excel для вычисляемого поля сводной таблицы

То, что нужно запомнить

• Мы можем использовать некоторые основные математические операции внутри вычисляемых полей в сводной таблице; это означает, что мы не можем использовать логические и другие функции потоков.
• Ссылка на ячейку не изменится, если ссылка создается с помощью функции «GetPivotDate».
• Формулы вычисляемых полей также являются частью сводной таблицы.
• В случае изменения исходных данных формулы останутся неизменными до обновления сводной таблицы.

УЗНАТЬ БОЛЬШЕ >>

Post Views: 692

Похожие записи

Прокрутить вверх

Как использовать вычисляемое поле в сводной таблице Excel за 3 шага • BUOM

8 апреля 2022 г.

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

Зачем использовать сводные таблицы в Excel?

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

Что такое вычисляемое поле в сводной таблице?

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

Например, рассмотрим сводную таблицу, которая отображает общий объем продаж для каждого представителя в компании. Если компания предоставляет каждому сотруднику бонус в размере 3% от его продаж, вы можете использовать вычисляемое поле, которое умножает общий объем продаж для каждого сотрудника на 0,03. Когда вы завершаете расчетное поле, в столбце отображается бонус каждого сотрудника.

Как использовать вычисляемое поле в сводной таблице Excel

Вот как использовать вычисляемое поле в сводной таблице Excel:

1. Откройте диалоговое окно «Вставить вычисляемое поле».

Откройте электронную таблицу и выберите любую ячейку в сводной таблице. На верхней ленте выберите «Анализ» под «Инструменты сводной таблицы». Перейдите в группу «Расчеты» и нажмите «Поля, элементы и наборы». Выберите «Вычисляемое поле».

2. Назовите вычисляемое поле

В диалоговом окне «Вставить вычисляемое поле» установите флажок рядом с «Имя». Вы можете стереть имя программы по умолчанию и ввести название, описывающее вычисление, которое вы хотите выполнить. Щелкните в любом месте за пределами этого поля после ввода имени.

3. Создайте формулу

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

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

Примеры использования вычисляемого поля в сводной таблице Excel

Вот несколько примеров использования вычисляемого поля в сводной таблице Excel:

Пример 1

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

Тип поздравительной открытки**Стоимость**ДоходРождество$6000$7200Пасха$4000$5000День рождения$4500$5175День святого Валентина$5000$6500Сотрудники продавца создают вычисляемое поле под названием «Прибыль». Что касается формулы, то из списка полей выбирают «Доход» и нажимают «Вставить поле». Набирают минус, выбирают «Стоимость» из списка полей и снова нажимают «Вставить поле». Эта формула рассчитывает прибыль от каждого типа поздравительных открыток путем вычитания стоимости из дохода. Как видно из таблицы ниже, поздравительные открытки ко Дню святого Валентина принесли наибольшую прибыль, а поздравительные открытки к Рождеству — наименьшую:

Тип поздравительной открытки**Стоимость**Доход**Прибыль**Рождество$6,000$7,200$1,200Пасха$4,000$5,000$1,000День рождения$4,500$5,175$675День святого Валентина$5,000$6,500$1,500### Пример 2

Супервайзер использует приведенную ниже сводную таблицу для отслеживания эффективности работы своего отдела продаж. Если продажи представителя превышают 2000 долларов, он имеет право на бонус в конце квартала в размере 3% от общего объема продаж. Представители, объем продаж которых составляет менее 2000 долларов США, не имеют права на получение бонусов в конце квартала. Супервайзер определяет, что вычисляемое поле может помочь им определить, кто имеет право на получение бонусов и в каком размере каждый бонус, поэтому она составляет следующую таблицу:

Имя представителя**Продажи*Педро Васкес 2 200 долларовСэмюэл Робертс 2 500 долларовЛуи Аллен 3 900 долларовДаниэль Хендерсон 1 900 долларовСупервайзер называет вычисляемое поле «Бонус» и решает использовать этот оператор ЕСЛИ в качестве формулы:*

ЕСЛИ(Продажи>$2000, Продажи * 0,03, 0)

Первый аргумент проверяет, достиг ли представитель продаж более 2000 долларов. Если это утверждение истинно, программа вычисляет второй аргумент, умножая продажи представителя на 3%. Если продажи составили менее 2000 долларов, программа выводит третий аргумент «0», чтобы указать, что представитель не имеет права на бонус. В приведенной ниже таблице показано, что Педро, Самуэль и Луи имели право на получение бонуса в размере 3% от их продаж, поскольку они превысили 2000 долларов.

Имя представителя**Продажи**БонусPedro Vasquez$2200$66Samuel Roberts$2500$75Louis Allen$3900$117Danielle Henderson$1900$0Обратите внимание, что ни одна из компаний или продуктов, упомянутых в этой статье, не связана с Indeed.

Вставить вычисляемое поле и вычисляемый элемент

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

Вставка вычисляемого поля

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

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

Чтобы вставить вычисляемое поле, выполните следующие действия.

1. Выберите ячейку в отчете сводной таблицы и в Инструменты сводной таблицы | На вкладке Анализ в группе Расчет нажмите кнопку Поля, элементы и наборы . В раскрывающемся меню выберите пункт Calculated Field… , чтобы открыть диалоговое окно

   Insert Calculated Field .

2. В диалоговом окне укажите имя и формулу для вычисляемого поля. Чтобы использовать данные из другого поля в формуле, щелкните нужное поле в Поле , а затем щелкните Вставить поле .

3. Щелкните Добавить , а затем щелкните OK . В результате новое поле будет автоматически добавлено в область Values ​​ отчета сводной таблицы.

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

   .

Вставка вычисляемого элемента

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

• Нельзя добавить вычисляемый элемент в поле страницы. Кроме того, вы не можете переместить поле строки или столбца, содержащее вычисляемые элементы, в область страницы отчета сводной таблицы.
• Вы не можете добавить вычисляемый элемент в сгруппированное поле. Разгруппируйте поле перед вставкой вычисляемого элемента.
• Нельзя добавить несколько копий поля, содержащего вычисляемые элементы, в область данных.
• Вы не можете добавить вычисляемый элемент в отчет сводной таблицы, который использует функцию агрегирования
  Average , StdDev , StdDevp , Var или Varp в области данных.

Чтобы вставить вычисляемый элемент, выполните следующие действия.

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

2. В вызванном диалоговом окне Вставить вычисляемый элемент укажите имя и формулу для вычисляемого элемента. Чтобы использовать значение другого элемента в формуле, щелкните нужный элемент в 9Список элементов 0003 , а затем щелкните Вставить элемент .

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

3. Щелкните Добавить , а затем щелкните OK . В результате новый элемент будет добавлен в поле.

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

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

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

Добавить вычисляемое поле в сводную таблицу

Предыдущий

Формула финансового года Excel

Следующий

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

Зачем добавлять вычисляемое поле в сводную таблицу?

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

Загрузка: Стартовый файл/Завершенный файл

Что такое вычисляемое поле?

Вычисляемое поле сводной таблицы — это способ записи формул в сводных таблицах. Это связано с тем, что невозможно писать формулы, как обычно, в сводных таблицах. Следовательно, если вы напишете что-то вроде =A2+B2, вы получите ошибку. Это связано с тем, что структура сводных таблиц сильно отличается от обычного способа организации электронной таблицы. В результате вам необходимо использовать вычисляемые поля, которые предназначены для использования в сводной таблице, если вы хотите использовать данные сводной таблицы в формулах.

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

Как добавить вычисляемое поле в сводную таблицу?

Добавление вычисляемого поля — 2:03

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

Затем нажмите Анализ сводной таблицы . Вы можете найти это на ленте в верхней части экрана.

После этого в разделе Расчеты нажмите на выпадающее меню Поля, элементы и наборы .

Выберите Поле расчета

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

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

Теперь вы должны увидеть окно «Вставить вычисляемое поле».

В поле Имя введите Прибыль . Затем в поле Формула введите следующую формулу.

=Получено+Расход

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

Нажмите Добавить , чтобы добавить вычисляемое поле в список полей сводной таблицы.

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

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

Вычисление процента двух столбцов в сводной таблице

Вычисленный процент прибыли — 3:27

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

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

Нажмите Поля, элементы и наборы на ленте, выберите Вставить вычисляемое поле.

В поле «Вставить вычисляемое поле» введите Прибыль % в поле имени и введите

= Прибыль/Полученная

Наконец, Щелкните правой кнопкой мыши в сводной таблице в любом месте столбца Сумма прибыли % .

Выберите Форматирование чисел .

Двойной щелчок Процент .

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

 

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

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