Trabalhar com Tabelas Dinâmicas no Office Scripts

As Tabelas Dinâmicas permitem analisar grandes coleções de dados rapidamente. Com seu poder 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 APIs torna a introdução um desafio. Este artigo demonstra como realizar tarefas comuns da 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 de Tabela Dinâmica do Excel. Comece com Criar uma Tabela Dinâmica para analisar os dados da 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.

Para ver 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. Ele é 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.

Criar uma Tabela Dinâmica com campos

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

O trecho 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

Hierarquias e campos

As Tabelas Dinâmicas são organizadas por meio de hierarquias. Essas hierarquias são usadas para dinamizar dados quando adicionadas como um tipo específico de hierarquia. Existem 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: adiciona ou remove itens da Tabela Dinâmica.

Uma Tabela Dinâmica pode ter tantos ou poucos campos atribuídos a essas hierarquias específicas. Uma Tabela Dinâmica precisa ter pelo menos uma hierarquia de dados para mostrar dados numéricos resumidos e pelo menos uma linha ou coluna para dinamizar esse resumo. O trecho 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 diferentes frutas com base na fazenda de onde elas vieram.

Intervalos de layout

Cada parte da Tabela Dinâmica é mapeada para um intervalo. Isso permite que o script obtenha dados da Tabela Dinâmica para serem usados posteriormente no script ou para serem retornados em um fluxo do Power Automate. Esses intervalos são acessados por meio do objeto PivotLayout adquirido do 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 obtenção de intervalo do layout.

Saída total da Tabela Dinâmica

O local da linha de totais é 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 localiza a primeira Tabela Dinâmica na pasta de trabalho e registra os valores nas células do "Total Geral" (conforme destacado em verde na imagem abaixo).

Uma Tabela Dinâmica mostrando as vendas de frutas com a linha Total Geral destacada 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 de dados

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

Hierarquias Dinâmicas de Filtro

FilterPivotHierarchies Adicione uma hierarquia adicional para filtrar cada linha de dados. Qualquer linha com um item que é filtrado é excluída da Tabela Dinâmica e de 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 "Caixas Vendidas no Atacado" 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 será filtrado até que o usuário interaja manualmente com o controle de filtro ou um PivotManualFilter seja definido no campo pertencente ao FilterPivotHierarchy.

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

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

Um controle de filtro que usa 'Classificação' 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 ao PivotHierarchy.getFields() aplicar filtros. Há quatro tipos de filtro.

  • Filtro de data: filtragem baseada em datas do Calendar.
  • 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 com 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 trecho de código a seguir adiciona dois filtros. O primeiro é um filtro manual que seleciona itens em uma hierarquia de filtro de "Classificação" existente. O segundo filtro remove todas as fazendas que tenham menos de 300 "Caixas Vendidas no Atacado". Observe que isso filtra a "Soma" desses farms, 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 após a aplicação do filtro de valor e do filtro manual.

Segmentações de dados

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

O trecho de código a seguir adiciona uma segmentação de dados para o campo "Tipo". Ele define os itens selecionados como "Limão" e "Lima" e, em seguida, move a segmentação 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);

Segmentação de dados filtrando dados em uma Tabela Dinâmica.

Configurações do campo de valor para resumos

Altere o modo 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 diferentes maneiras, como porcentagens, 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 de cada linha ou coluna de uma maneira diferente. AggregationFunction lista todas as opções disponíveis.

O trecho de código a seguir altera "Caixas Vendidas no Atacado" 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 porcentagens relativas a outras partes da Tabela Dinâmica. Use a ShowAsRule para definir como os valores da hierarquia de dados são mostrados.

O trecho de código a seguir altera a exibição de "Caixas Vendidas na Fazenda". Os valores serão mostrados como uma porcentagem do total geral do campo.

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

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

Alguns ShowAsRules precisam de outro campo ou item nesse campo como comparação. O trecho de código a seguir altera novamente a exibição de "Caixas Vendidas na Fazenda". Desta vez, o campo mostrará a diferença de cada valor do valor de "Limões" naquela linha da fazenda. Se uma fazenda não vendeu limões, o campo mostra "#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");

Confira também

  • Last updated on