Trabalhar com Tabelas Dinâmicas no Office Scripts

Artigo
05/03/2024

As Tabelas Dinâmicas permitem analisar grandes coleções de dados rapidamente. Com o poder deles vem a complexidade. As APIs de Scripts do Office permitem personalizar uma Tabela Dinâmica para atender às suas necessidades, mas o escopo do conjunto de API torna a introdução um desafio. Este artigo demonstra como executar tarefas comuns de Tabela Dinâmica e explica classes e métodos importantes.

Observação

Para entender melhor o contexto dos termos usados pelas APIs, leia primeiro a documentação da Tabela Dinâmica do Excel. Comece com Create uma Tabela Dinâmica para analisar dados de planilha.

Modelo de objetos

Uma imagem simplificada das classes, métodos e propriedades usadas ao trabalhar com tabelas dinâmicas.

A Tabela Dinâmica é o objeto central para Tabelas Dinâmicas na API de Scripts do Office.

O objeto Workbook tem uma coleção de todas as Tabelas Dinâmicas. Cada Planilha também contém uma coleção de Tabela Dinâmica que é local para essa planilha.
Uma Tabela Dinâmica contém PivotHierarchies. Uma hierarquia pode ser considerada como uma coluna em uma tabela.
PivotHierarchies podem ser adicionados como linhas ou colunas (RowColumnPivotHierarchy), dados (DataPivotHierarchy) ou filtros (FilterPivotHierarchy).
Cada PivotHierarchy contém exatamente um PivotField. Estruturas de tabela dinâmica fora do Excel podem conter vários campos por hierarquia, portanto, esse design existe para dar suporte a opções futuras. Para Scripts do Office, campos e hierarquias mapeiam para as mesmas informações.
Um PivotField contém vários PivotItems. Cada PivotItem é um valor exclusivo no campo. Pense em cada item como um valor na coluna de tabela. Os itens também poderão ser valores agregados, como somas, se o campo estiver sendo usado para dados.
O PivotLayout define como os PivotFields e PivotItems são exibidos .
PivotFilters filtram dados da Tabela Dinâmica usando critérios diferentes.

Para examinar como essas relações funcionam na prática, comece baixando a pasta de trabalho de exemplo. Esses dados descrevem as vendas de frutas de várias fazendas. É a base para todos os exemplos neste artigo. Execute os scripts de exemplo ao longo do artigo para criar e explorar tabelas dinâmicas.

Baixar a pasta de trabalho de exemplo

Uma coleção de vendas de frutas de diferentes tipos de diferentes fazendas.

Create uma Tabela Dinâmica com campos

As tabelas dinâmicas são criadas com referências aos dados existentes. Intervalos e tabelas podem ser a origem de uma Tabela Dinâmica. Eles também precisam de um lugar para existir na pasta de trabalho. Como o tamanho de uma Tabela Dinâmica é dinâmico, apenas o canto superior esquerdo do intervalo de destino é especificado.

O snippet de código a seguir cria uma Tabela Dinâmica com base em um intervalo de dados. A Tabela Dinâmica não tem hierarquias, portanto, os dados ainda não estão agrupados de forma alguma.

  const dataSheet = workbook.getWorksheet("Data");
  const pivotSheet = workbook.getWorksheet("Pivot");

  const farmPivot = pivotSheet.addPivotTable(
    "Farm Pivot", /* The name of the PivotTable. */
    dataSheet.getUsedRange(), /* The source data range. */
    pivotSheet.getRange("A1") /* The location to put the new PivotTable. */);

Uma Tabela Dinâmica chamada 'Farm Pivot' sem hierarquias.

Hierarquias e campos

As tabelas dinâmicas são organizadas por meio de hierarquias. Essas hierarquias são usadas para pivotar dados quando adicionadas como um tipo específico de hierarquia. Há quatro tipos de hierarquias.

Linha: exibe itens em linhas horizontais.
Coluna: exibe itens em colunas verticais.
Dados: exibe agregações de valores com base nas linhas e colunas.
Filtro: adicionar ou remover itens da Tabela Dinâmica.

Uma Tabela Dinâmica pode ter tantos ou poucos de seus campos atribuídos a essas hierarquias específicas. Uma Tabela Dinâmica precisa de pelo menos uma hierarquia de dados para mostrar dados numéricos resumidos e pelo menos uma linha ou coluna para pivotar esse resumo. O snippet de código a seguir adiciona duas hierarquias de linha e duas hierarquias de dados.

  farmPivot.addRowHierarchy(farmPivot.getHierarchy("Farm"));
  farmPivot.addRowHierarchy(farmPivot.getHierarchy("Type"));
  farmPivot.addDataHierarchy(farmPivot.getHierarchy("Crates Sold at Farm"));
  farmPivot.addDataHierarchy(farmPivot.getHierarchy("Crates Sold Wholesale"));

Uma Tabela Dinâmica mostrando o total de vendas de frutas diferentes com base na fazenda de onde vieram.

Intervalos de layout

Cada parte da Tabela Dinâmica é mapeada para um intervalo. Isso permite que seu script obtenha dados da Tabela Dinâmica para uso posterior no script ou para ser retornado em um fluxo do Power Automate. Esses intervalos são acessados por meio do objeto PivotLayout adquirido de PivotTable.getLayout(). O diagrama a seguir mostra os intervalos retornados pelos métodos em PivotLayout.

Um diagrama mostrando quais seções de uma Tabela Dinâmica são retornadas pelas funções de intervalo de obtenção do layout.

Saída total da Tabela Dinâmica

O local da linha total é baseado no layout. Use PivotLayout.getBodyAndTotalRange e obtenha a última linha da coluna para usar os dados da Tabela Dinâmica em seu script.

O exemplo a seguir encontra a primeira Tabela Dinâmica na pasta de trabalho e registra os valores nas células "Grand Total" (conforme realçado em verde na imagem abaixo).

Uma Tabela Dinâmica mostrando as vendas de frutas com a linha Grand Total realçada em verde.

function main(workbook: ExcelScript.Workbook) {
  // Get the first PivotTable in the workbook.
  const pivotTable = workbook.getPivotTables()[0];

  // Get the names of each data column in the PivotTable.
  const pivotColumnLabelRange = pivotTable.getLayout().getColumnLabelRange();

  // Get the range displaying the pivoted data.
  const pivotDataRange = pivotTable.getLayout().getBodyAndTotalRange();

  // Get the range with the "grand totals" for the PivotTable columns.
  const grandTotalRange = pivotDataRange.getLastRow();

  // Print each of the "Grand Totals" to the console.
  grandTotalRange.getValues()[0].forEach((column, columnIndex) => {
    console.log(`Grand total of ${pivotColumnLabelRange.getValues()[0][columnIndex]}: ${grandTotalRange.getValues()[0][columnIndex]}`);
    // Example log: "Grand total of Sum of Crates Sold Wholesale: 11000"
  });
}

Filtros e segmentações

Há três maneiras de filtrar uma Tabela Dinâmica.

FilterPivotHierarchies

FilterPivotHierarchies adicione uma hierarquia adicional para filtrar cada linha de dados. Qualquer linha com um item filtrado é excluída da Tabela Dinâmica e seus resumos. Como esses filtros são baseados em itens, eles funcionam apenas em valores discretos. Se "Classificação" for uma hierarquia de filtro no exemplo, os usuários poderão selecionar os valores de "Orgânico" e "Convencional" para o filtro. Da mesma forma, se "Crates Sold Wholesale" for selecionado, as opções de filtro serão os números individuais, como 120 e 150, em vez de intervalos numéricos.

FilterPivotHierarchies são criados com todos os valores selecionados. Isso significa que nada é filtrado até que o usuário interaja manualmente com o controle de filtro ou um PivotManualFilter seja definido no campo que pertence ao FilterPivotHierarchy.

O snippet de código a seguir adiciona "Classificação" como uma hierarquia de filtro.

  farmPivot.addFilterHierarchy(farmPivot.getHierarchy("Classification"));

Um controle de filtro que usa 'Classification' para uma Tabela Dinâmica.

PivotFilters

O PivotFilters objeto é uma coleção de filtros aplicados a um único campo. Como cada hierarquia tem exatamente um campo, você deve sempre usar o primeiro campo em PivotHierarchy.getFields() ao aplicar filtros. Há quatro tipos de filtro.

Filtro de data: filtragem baseada em data de calendário.
Filtro de rótulo: filtragem de comparação de texto.
Filtro manual: filtragem de entrada personalizada.
Filtro de valor: filtragem de comparação de números. Isso compara itens na hierarquia associada a valores em uma hierarquia de dados especificada.

Normalmente, apenas um dos quatro tipos de filtros é criado e aplicado ao campo. Se o script tentar usar filtros incompatíveis, um erro será gerado com o texto "O argumento é inválido ou ausente ou tem um formato incorreto".

O snippet de código a seguir adiciona dois filtros. O primeiro é um filtro manual que seleciona itens em uma hierarquia de filtro "Classificação" existente. O segundo filtro remove todas as fazendas que têm menos de 300 "Crates Sold Wholesale". Observe que isso filtra a "Soma" dessas fazendas, não as linhas individuais dos dados originais.

  const classificationField = farmPivot.getFilterHierarchy("Classification").getFields()[0];
  classificationField.applyFilter({
    manualFilter: { 
      selectedItems: ["Organic"] /* The included items. */
    }
  });

  const farmField = farmPivot.getHierarchy("Farm").getFields()[0];
  farmField.applyFilter({
    valueFilter: {
      condition: ExcelScript.ValueFilterCondition.greaterThan, /* The relationship of the value to the comparator. */
      comparator: 300, /* The value to which items are compared. */
      value: "Sum of Crates Sold Wholesale" /* The name of the data hierarchy. Note the "Sum of" prefix. */
      }
  });

Uma Tabela Dinâmica depois que o filtro de valor e o filtro manual foram aplicados.

Segmentações de dados

Os segmentadores filtram dados em uma Tabela Dinâmica (ou tabela padrão). Eles são objetos moveveis na planilha que permitem seleções de filtragem rápida. Uma segmentação opera de forma semelhante ao filtro manual e PivotFilterHierarchy. Os itens do PivotField são alternados para incluí-los ou excluí-los da Tabela Dinâmica.

O snippet de código a seguir adiciona uma segmentação para o campo "Tipo". Ele define os itens selecionados como "Lemon" e "Lime", em seguida, move a segmentação de 400 pixels para a esquerda.

  const fruitSlicer = pivotSheet.addSlicer(
    farmPivot, /* The table or PivotTale to be sliced. */
    farmPivot.getHierarchy("Type").getFields()[0] /* What source to use as the slicer options. */
  );
  fruitSlicer.selectItems(["Lemon", "Lime"]);
  fruitSlicer.setLeft(400);

Uma segmentação de dados de filtragem em uma Tabela Dinâmica.

Configurações de campo de valor para resumos

Altere como a Tabela Dinâmica resume e exibe dados com essas configurações. O campo em cada hierarquia de dados pode exibir os dados de maneiras diferentes, como percentuais, desvios padrão e comparações relativas.

Resumir por

O resumo padrão de um campo de hierarquia de dados é como uma soma. DataPivotHierarchy.setSummarizeBy permite combinar os dados para cada linha ou coluna de uma maneira diferente. AggregationFunction lista todas as opções disponíveis.

O snippet de código a seguir altera "Crates Sold Wholesale" para mostrar o desvio padrão de cada item, em vez da soma.

  const wholesaleSales = farmPivot.getDataHierarchy("Sum of Crates Sold Wholesale");
  wholesaleSales.setSummarizeBy(ExcelScript.AggregationFunction.standardDeviation);

Mostrar valores como

DataPivotHierarchy.setShowAs aplica um cálculo aos valores de uma hierarquia de dados. Em vez da soma padrão, você pode mostrar valores ou percentuais relativos a outras partes da Tabela Dinâmica. Use um ShowAsRule para definir como os valores de hierarquia de dados são mostrados.

O snippet de código a seguir altera a exibição de "Crates Sold at Farm". Os valores serão mostrados como uma porcentagem do total total para o campo.

  const farmSales = farmPivot.getDataHierarchy("Sum of Crates Sold at Farm");

  const rule : ExcelScript.ShowAsRule = {
    calculation: ExcelScript.ShowAsCalculation.percentOfGrandTotal
  };
  farmSales.setShowAs(rule);

Alguns ShowAsRuleprecisam de outro campo ou item nesse campo como comparação. O snippet de código a seguir altera novamente a exibição de "Crates Sold at Farm". Desta vez, o campo mostrará a diferença de cada valor em relação ao valor dos "Limões" nessa linha de farm. Se uma fazenda não tiver vendido limões, o campo mostrará "#N/A".

  const typeField = farmPivot.getRowHierarchy("Type").getFields()[0];
  const farmSales = farmPivot.getDataHierarchy("Sum of Crates Sold at Farm");

  const rule: ExcelScript.ShowAsRule = {
    calculation: ExcelScript.ShowAsCalculation.differenceFrom,
    baseField: typeField, /* The field to use for the difference. */
    baseItem: typeField.getPivotItem("Lemon") /* The item within that field that is the basis of comparison for the difference. */
  };
  farmSales.setShowAs(rule);
  farmSales.setName("Difference from Lemons of Crates Sold at Farm");

Partilhar via