Блог

Я отслеживаю свой (биржевой) портфель в таблицах Google следующим образом:

 category     subcategory    company  amount
-------------------------------------------
health care  diagnostics     AA       100
health care  diagnostics     AB        50
materials    mining          BA        75
financials   banks           CA        30
financials   insurers        CB        35
financials   banks           CC        10
financials   banks           CD        40
financials   hedge fund      CE         5
health care  equipment       DA        50
 

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

• относительный размер каждой category из них в портфеле
• относительный размер каждой subcategory из них в портфеле
• относительный размер каждой company из их subcategory

Первые два я закончил:

Например, я вижу, что:

• категория financials имеет относительный размер 30% в портфолио
• подкатегория diagnostics имеет относительный размер 37.97% в портфолио

Однако не хватает третьего столбца, см. Макет ниже:

Теперь я могу видеть в последнем столбце, какой относительный размер каждого company из них находится в его subcategory :

• Компания CD относится 50% к подкатегории banks
• Компания AB относится 300.33% к подкатегории diagnostics

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

Кто-нибудь знает, как сделать этот последний столбец частью сводной таблицы?

Вот ссылка на используемый Google Sheet: Сводная таблица

В настоящее % by subtotal время в Google Таблицах нет опции для отображения процентных соотношений с промежуточными итогами для всей сводной таблицы:

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

Добавьте фильтр здесь и выберите subcategory :

Затем выберите нужное поле, например, banks , и нажмите OK.:

Это должно отображать промежуточные проценты следующим образом:

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

Вы также можете отправить запрос на идею функции в Google Workspace, см. Инструкции на этом сайте: Отправить идеи для Google Workspace и Cloud Identity

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

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

Используемая формула будет

 =amount/getpivotdata("amount",'Copy of Pivot'!$F$10, "category", category, "subcategory", subcategory)
 

Где amount , category , subcategory без двойных кавычек — это значения (ссылки), заполненные таблицами Google. В то время как двойные кавычки — это имена столбцов.

Вам также необходимо установить summarize by значение, чтобы custom оно работало.

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

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

 =amount/SUM(QUERY($A$1:$D,"Select D Where A='"amp;categoryamp;"' AND B='"amp;subcategoryamp;"'",false))
 

Тем не менее, он по-прежнему использует одни и те же общие строки.

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