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

Формула сводной таблицы в Excel (оглавление)

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

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

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

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

В этой статье мы продемонстрируем способы использования вычисляемых полей сводной таблицы для построения новых столбцов на основе наших требований.

Пользовательское поле для расчета суммы прибыли

Вы можете скачать этот шаблон Excel для формул сводной таблицы здесь - Шаблон Excel для формул сводной таблицы

Это наиболее часто используемое вычисляемое поле в сводной таблице. Посмотрите на приведенные ниже данные: столбец «Название страны», «Название продукта», «Проданные единицы», «Цена за единицу», «Валовой объем продаж», «COGS (стоимость проданных товаров)», «Дата» и «Год».

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

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

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

Шаг 2: В приведенном ниже диалоговом окне введите имя вашего нового вычисляемого поля.

Шаг 3: В разделе Формула примените формулу, чтобы найти Прибыль. Формула для определения прибыли: валовые продажи - COGS.

Зайдите в панель формул> Выберите Gross Sales из поля ниже и дважды щелкните по нему, чтобы отобразиться на панели формул.

Теперь введите символ минус (-) и выберите COGS> Двойной щелчок.

Шаг 4: Нажмите на ДОБАВИТЬ и ОК, чтобы завершить формулу.

Шаг 5: Теперь у нас есть общая колонка прибыли в сводной таблице.

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

Шаг 6: Теперь нам нужно рассчитать процент прибыли. Формула для расчета процента прибыли - это общая прибыль / валовой объем продаж.

Перейдите в Анализ и снова выберите Расчетное поле в разделе Поля, Элементы и Наборы.

Шаг 7: Теперь мы должны увидеть недавно вставленное вычисляемое поле Total Profit в списке полей. Вставьте это поле в формулу.

Шаг 8: Введите символ делителя (/) и вставьте поле валовых продаж.

Шаг 9: Назовите это Расчетное поле как Процент прибыли.

Шаг 10: Нажмите «Добавить» и «ОК», чтобы завершить формулу. У нас есть процент прибыли в качестве нового столбца.

Расширенная формула в вычисляемом поле

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

Если Прибыль%> 15%, стимул должен составлять 6% от общей прибыли.

Если Прибыль%> 10%, стимул должен составлять 5% от общей прибыли.

Если Прибыль% <10%, стимул должен составлять 3% от общей прибыли.

Шаг 1: Перейдите к Расчетному полю и откройте диалоговое окно ниже. Дайте название в качестве суммы стимулирования.

Шаг 2: Теперь я буду использовать условие ЕСЛИ для расчета суммы поощрения. Примените приведенные ниже формулы, как показано на рисунке.

= IF («ProfitPercentage»> 15%, «TotalProft» * 6%, IF («ProfitPercentage»> 10%, «Total Proft» * 5%, «Total Proft» * 3%))

Шаг 3: Нажмите ADD & OK для завершения. Теперь у нас есть столбец Incentive Amount.

Ограничение расчетного поля

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

Так что будьте осторожны при отображении Подытога рассчитанных полей. Это покажет вам неправильные суммы.

Получить список всех вычисляемых формул поля

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

Перейдите в Анализ> Поля, Элементы и наборы -> Список формул.

Это даст вам сводку всех формул в новом листе.

Что нужно помнить о формуле сводной таблицы в Excel

Мы можем удалить, изменить все рассчитанные поля.
Мы не можем использовать такие формулы, как VLOOKUP, SUMIF и многие другие формулы, связанные с диапазоном, в вычисляемых полях, т. Е. Не могут использоваться все формулы, для которых требуется диапазон.

Формула сводной таблицы в Excel - Шаги по использованию формулы сводной таблицы в Excel

Содержание: