Trabalhar com Tabelas Dinâmicas no Office Scripts

As Tabelas Dinâmicas permitem-lhe analisar rapidamente grandes coleções de dados. Com o seu poder vem a complexidade. As APIs de Scripts do Office permitem-lhe personalizar uma Tabela Dinâmica de acordo com as suas necessidades, mas o âmbito do conjunto de API torna a introdução um desafio. Este artigo demonstra como realizar tarefas de Tabela Dinâmica comuns e explica classes e métodos importantes.

Observação

Para compreender melhor o contexto dos termos utilizados pelas APIs, leia primeiro a documentação da Tabela Dinâmica do Excel. Comece por Criar uma Tabela Dinâmica para analisar os dados da folha de cálculo.

Modelo de objetos

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

• O objeto Livro tem uma coleção de todas as tabelas dinâmicas. Cada Folha de Cálculo também contém uma coleção de tabela dinâmica local para essa folha.
• Uma tabela dinâmica contém pivotHierarchies. Uma hierarquia pode ser considerada uma coluna numa tabela.
• Os gráficos dinâmicos podem ser adicionados como linhas ou colunas (RowColumnPivotHierarchy), dados (DataPivotHierarchy) ou filtros (FilterPivotHierarchy).
• Cada PivotHierarchy contém exatamente um Campo Dinâmico. As estruturas de tabela dinâmica fora do Excel podem conter vários campos por hierarquia, pelo que este design existe para suportar opções futuras. Para Scripts do Office, os campos e hierarquias mapeiam para as mesmas informações.
• Um Campo Dinâmico contém váriosItems Dinâmicos. Cada PivotItem é um valor exclusivo no campo. Pense em cada item como um valor na coluna da tabela. Os itens também podem ser valores agregados, como somas, se o campo estiver a ser utilizado para dados.
• O PivotLayout define a forma como os Campos Dinâmicos e osItems Dinâmicos são apresentados .
• Os Filtros Dinâmicos filtram os dados da Tabela Dinâmica com critérios diferentes.

Para ver como estas relações funcionam na prática, comece por transferir o livro de exemplo. Estes dados descrevem as vendas de frutas de várias explorações agrícolas. É a base para todos os exemplos neste artigo. Execute os scripts de exemplo ao longo do artigo para criar e explorar tabelas dinâmicas.

Transferir o livro de exemplo

Criar uma Tabela Dinâmica com campos

As tabelas dinâmicas são criadas com referências a dados existentes. Os intervalos e as tabelas podem ser a origem de uma tabela dinâmica. Também precisam de um local para existir no livro. Uma vez que o tamanho de uma tabela dinâmica é dinâmico, só é especificado o canto superior esquerdo do intervalo de destino.

O fragmento de código seguinte cria uma tabela dinâmica com base num intervalo de dados. A Tabela Dinâmica não tem hierarquias, pelo que 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. */);

Hierarquias e campos

As tabelas dinâmicas estão organizadas através de hierarquias. Essas hierarquias são utilizadas para dinamizar dados quando adicionadas como um tipo específico de hierarquia. Existem quatro tipos de hierarquias.

• Linha: apresenta itens em linhas horizontais.
• Coluna: apresenta itens em colunas verticais.
• Dados: apresenta 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 tão poucos campos atribuídos a estas 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 dinamizar esse resumo. O fragmento de código seguinte adiciona duas hierarquias de linhas 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"));

Intervalos de esquema

Cada parte da tabela dinâmica mapeia para um intervalo. Isto permite que o script obtenha dados da Tabela Dinâmica para utilização posterior no script ou para ser devolvido num fluxo do Power Automate. Estes intervalos são acedidos através do objeto PivotLayout adquirido a partir de PivotTable.getLayout(). O diagrama seguinte mostra os intervalos que são devolvidos pelos métodos em PivotLayout.

Resultado total da tabela dinâmica

A localização da linha total baseia-se no esquema. Utilize PivotLayout.getBodyAndTotalRange e obtenha a última linha da coluna para utilizar os dados da Tabela Dinâmica no script.

O exemplo seguinte localiza a primeira tabela dinâmica no livro e regista os valores nas células "Total Geral" (conforme realçado a verde na imagem abaixo).

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

Existem três formas de filtrar uma Tabela Dinâmica.

• FilterPivotHierarchies
• PivotFilters
• Slicers

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 dos respetivos resumos. Uma vez que estes filtros são baseados em itens, só funcionam em valores discretos. Se "Classificação" for uma hierarquia de filtros no exemplo, os utilizadores podem selecionar os valores "Orgânico" e "Convencional" para o filtro. Da mesma forma, se "Caixas Vendidas Por Grosso" estiver selecionada, 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. Isto significa que nada é filtrado até que o utilizador interaja manualmente com o controlo de filtro ou um PivotManualFilter esteja definido no campo que pertence ao FilterPivotHierarchy.

O fragmento de código seguinte adiciona "Classificação" como uma hierarquia de filtros.

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

PivotFilters

O PivotFilters objeto é uma coleção de filtros aplicados a um único campo. Uma vez que cada hierarquia tem exatamente um campo, deve sempre utilizar o primeiro campo no PivotHierarchy.getFields() ao aplicar filtros. Existem quatro tipos de filtro.

• Filtro de data: filtragem baseada em datas do calendário.
• Filtro de etiqueta: filtragem de comparação de textos.
• Filtro manual: filtragem de entrada personalizada.
• Filtro de valor: filtragem de comparação de números. Isto compara itens na hierarquia associada com valores numa hierarquia de dados especificada.

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

O fragmento de código seguinte adiciona dois filtros. O primeiro é um filtro manual que seleciona itens numa hierarquia de filtros "Classificação" existente. O segundo filtro remove quaisquer farms que tenham menos de 300 "Caixas Vendidas Por Grosso". Tenha em atenção que isto filtra a "Soma" desses farms e 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. */
      }
  });

Segmentações de dados

As segmentações de dados filtram dados numa tabela dinâmica (ou tabela padrão). São objetos movíveis na folha de cálculo que permitem seleções de filtragem rápida. Uma segmentação de dados funciona de forma semelhante ao filtro manual e PivotFilterHierarchy. Os itens do PivotField são alternados para os incluir ou excluir da tabela dinâmica.

O fragmento de código seguinte adiciona uma segmentação de dados para o campo "Tipo". Define os itens selecionados como "Limão" e "Lima" e, em seguida, move a segmentação de dados 400 píxeis 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);

Definições de campo de valor para resumos

Altere a forma como a Tabela Dinâmica resume e apresenta dados com estas definições. O campo em cada hierarquia de dados pode apresentar os dados de diferentes formas, tais como percentagens, desvios padrão e comparações relativas.

Resumir por

O resumo predefinido de um campo de hierarquia de dados é como uma soma. DataPivotHierarchy.setSummarizeBy permite-lhe combinar os dados de cada linha ou coluna de uma forma diferente. AggregationFunction lista todas as opções disponíveis.

O fragmento de código seguinte 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 predefinida, pode mostrar valores ou percentagens em relação a outras partes da tabela dinâmica. Utilize um ShowAsRule para definir a forma como os valores da hierarquia de dados são apresentados.

O fragmento de código seguinte altera a apresentação de "Caixas Vendidas no Farm". Os valores serão apresentados como uma percentagem 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 ShowAsRuleprecisam de outro campo ou item nesse campo como uma comparação. O fragmento de código seguinte altera novamente a apresentação de "Caixas Vendidas no Farm". Desta vez, o campo mostrará a diferença de cada valor face ao valor dos "Limões" nessa linha do farm. Se uma quinta não tiver vendido 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

• Noções básicas para Scripts do Office no Excel
• Referência da API de scripts do Office