Trabalhar com tabelas dinâmicas usando a API JavaScript do Excel

Artigo
04/07/2023

As Tabelas Dinâmicas simplificam conjuntos de dados maiores. Eles permitem a manipulação rápida de dados agrupados. A API JavaScript do Excel permite que seu suplemento crie tabelas dinâmicas e interaja com seus componentes. Este artigo descreve como as Tabelas Dinâmicas são representadas pela API JavaScript do Office e fornece exemplos de código para cenários-chave.

Se você não estiver familiarizado com a funcionalidade das Tabelas Dinâmicas, considere explorá-las como um usuário final. Consulte Criar uma Tabela Dinâmica para analisar dados de planilha para obter uma boa cartilha nessas ferramentas.

Importante

Não há suporte para tabelas dinâmicas criadas com OLAP no momento. Também não há suporte para o Power Pivot.

Modelo de objetos

Um diagrama mostrando a relação entre tipos de objeto para Tabelas Dinâmicas, conforme descrito na lista subsequente e documentação de referência associada.

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

Workbook.pivotTables e Worksheet.pivotTables são PivotTableCollections que contêm as Tabelas Dinâmicas na pasta de trabalho e na planilha, respectivamente.
Uma Tabela Dinâmica contém um PivotHierarchyCollection que tem várias PivotHierarchies.
Essas PivotHierarchies podem ser adicionadas a coleções de hierarquia específicas para definir como a Tabela Dinâmica gira os dados (conforme explicado na seção a seguir).
Uma PivotHierarchy contém um PivotFieldCollection que tem exatamente um PivotField. Se o design se expandir para incluir tabelas dinâmicas OLAP, isso poderá ser alterado.
Um PivotField pode ter um ou mais PivotFilters aplicados , desde que a PivotHierarchy do campo seja atribuída a uma categoria de hierarquia.
Um PivotField contém um PivotItemCollection que tem vários PivotItems.
Uma Tabela Dinâmica contém um PivotLayout que define onde os PivotFields e PivotItems são exibidos na planilha. O layout também controla algumas configurações de exibição para a Tabela Dinâmica.

Vamos examinar como essas relações se aplicam a alguns dados de exemplo. Os dados a seguir descrevem as vendas de frutas de várias fazendas. Ele será o exemplo ao longo deste artigo.

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

Esses dados de vendas do farm de frutas serão usados para fazer uma Tabela Dinâmica. Cada coluna, como Tipos, é um PivotHierarchy. A hierarquia Tipos contém o campo Tipos . O campo Tipos contém os itens Apple, Kiwi, Lemon, Lime e Orange.

Hierarquias

As tabelas dinâmicas são organizadas com base em quatro categorias de hierarquia: linha, coluna, dados e filtro.

Os dados do farm mostrados anteriormente têm cinco hierarquias: Farms, Type, Classification, Crates Sold at Farm e Crates Sold Wholesale. Cada hierarquia só pode existir em uma das quatro categorias. Se Type for adicionado a hierarquias de coluna, ele também não poderá estar na linha, nos dados ou nas hierarquias de filtro. Se Type for adicionado posteriormente a hierarquias de linha, ele será removido das hierarquias de coluna. Esse comportamento é o mesmo se a atribuição de hierarquia for feita por meio da interface do usuário do Excel ou das APIs JavaScript do Excel.

Hierarquias de linha e coluna definem como os dados serão agrupados. Por exemplo, uma hierarquia de linhas do Farms agrupará todos os conjuntos de dados do mesmo farm. A escolha entre a hierarquia de linha e coluna define a orientação da Tabela Dinâmica.

Hierarquias de dados são os valores a serem agregados com base nas hierarquias de linha e coluna. Uma Tabela Dinâmica com uma hierarquia de linhas de Farms e uma hierarquia de dados do Crates Sold Wholesale mostra a soma total (por padrão) de todos os diferentes frutos para cada farm.

As hierarquias de filtro incluem ou excluem dados do pivô com base em valores dentro desse tipo filtrado. Uma hierarquia de filtro de Classificação com o tipo Orgânico selecionado mostra apenas dados de frutas orgânicas.

Aqui estão os dados do farm novamente, ao lado de uma Tabela Dinâmica. A Tabela Dinâmica está usando Farm e Type como hierarquias de linha, Crates Sold at Farm e Crates Sold Wholesale como hierarquias de dados (com a função de agregação padrão de soma) e Classificação como uma hierarquia de filtro (com Orgânico selecionado).

Uma seleção de dados de vendas de frutas ao lado de uma Tabela Dinâmica com hierarquias de linha, dados e filtro.

Essa Tabela Dinâmica pode ser gerada por meio da API JavaScript ou por meio da interface do usuário do Excel. Ambas as opções permitem mais manipulação por meio de suplementos.

Criar uma tabela dinâmica

As tabelas dinâmicas precisam de um nome, fonte e destino. A origem pode ser um endereço de intervalo ou um nome de tabela (passado como um Range, stringou Table tipo). O destino é um endereço de intervalo (dado como um Range ou string). Os exemplos a seguir mostram várias técnicas de criação de Tabela Dinâmica.

Criar uma tabela dinâmica com endereços de intervalo

await Excel.run(async (context) => {
    // Create a PivotTable named "Farm Sales" on the current worksheet at cell
    // A22 with data from the range A1:E21.
    context.workbook.worksheets.getActiveWorksheet().pivotTables.add(
      "Farm Sales", "A1:E21", "A22");

    await context.sync();
});

Criar uma tabela dinâmica com objetos Range

await Excel.run(async (context) => {
    // Create a PivotTable named "Farm Sales" on a worksheet called "PivotWorksheet" at cell A2
    // the data comes from the worksheet "DataWorksheet" across the range A1:E21.
    let rangeToAnalyze = context.workbook.worksheets.getItem("DataWorksheet").getRange("A1:E21");
    let rangeToPlacePivot = context.workbook.worksheets.getItem("PivotWorksheet").getRange("A2");
    context.workbook.worksheets.getItem("PivotWorksheet").pivotTables.add(
      "Farm Sales", rangeToAnalyze, rangeToPlacePivot);

    await context.sync();
});

Criar uma tabela dinâmica no nível da pasta de trabalho

await Excel.run(async (context) => {
    // Create a PivotTable named "Farm Sales" on a worksheet called "PivotWorksheet" at cell A2
    // the data is from the worksheet "DataWorksheet" across the range A1:E21.
    context.workbook.pivotTables.add(
        "Farm Sales", "DataWorksheet!A1:E21", "PivotWorksheet!A2");

    await context.sync();
});

Usar uma tabela dinâmica existente

As Tabelas Dinâmicas criadas manualmente também são acessíveis por meio da coleção Tabela Dinâmica da pasta de trabalho ou de planilhas individuais. O código a seguir obtém uma Tabela Dinâmica chamada Meu Pivô da pasta de trabalho.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.pivotTables.getItem("My Pivot");
    await context.sync();
});

Adicionar linhas e colunas a uma Tabela Dinâmica

Linhas e colunas giram os dados em torno dos valores desses campos.

A adição da coluna Farm gira todas as vendas em torno de cada farm. Adicionar as linhas Tipo e Classificação divide ainda mais os dados com base em qual fruta foi vendida e se era orgânica ou não.

Uma Tabela Dinâmica com uma coluna farm e linhas Tipo e Classificação.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Classification"));

    pivotTable.columnHierarchies.add(pivotTable.hierarchies.getItem("Farm"));

    await context.sync();
});

Você também pode ter uma Tabela Dinâmica com apenas linhas ou colunas.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Classification"));

    await context.sync();
});

Adicionar hierarquias de dados à Tabela Dinâmica

As hierarquias de dados preenchem a Tabela Dinâmica com informações a serem combinadas com base nas linhas e colunas. Adicionar as hierarquias de dados das Caixas Vendidas no Farm e crates Sold Wholesale fornece somas desses números para cada linha e coluna.

No exemplo , farm eType são linhas, com as vendas de caixa como os dados.

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

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    // "Farm" and "Type" are the hierarchies on which the aggregation is based.
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));

    // "Crates Sold at Farm" and "Crates Sold Wholesale" are the hierarchies
    // that will have their data aggregated (summed in this case).
    pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold at Farm"));
    pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold Wholesale"));

    await context.sync();
});

Layouts de tabela dinâmica e obtenção de dados dinâmicos

Um PivotLayout define o posicionamento de hierarquias e seus dados. Você acessa o layout para determinar os intervalos em que os dados são armazenados.

O diagrama a seguir mostra quais chamadas de função de layout correspondem a quais intervalos da Tabela Dinâmica.

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

Obter dados da Tabela Dinâmica

O layout define como a Tabela Dinâmica é exibida na planilha. Isso significa que o PivotLayout objeto controla os intervalos usados para elementos de Tabela Dinâmica. Use os intervalos fornecidos pelo layout para obter dados coletados e agregados pela Tabela Dinâmica. Em particular, use PivotLayout.getDataBodyRange para acessar os dados produzidos pela Tabela Dinâmica.

O código a seguir demonstra como obter a última linha dos dados da Tabela Dinâmica passando pelo layout (o Total Total total das colunas Soma das Caixas Vendidas no Farm e Soma das Caixas Vendidas no exemplo anterior). Esses valores são resumidos juntos para um total final, que é exibido na célula E30 (fora da Tabela Dinâmica).

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    // Get the totals for each data hierarchy from the layout.
    let range = pivotTable.layout.getDataBodyRange();
    let grandTotalRange = range.getLastRow();
    grandTotalRange.load("address");
    await context.sync();

    // Sum the totals from the PivotTable data hierarchies and place them in a new range, outside of the PivotTable.
    let masterTotalRange = context.workbook.worksheets.getActiveWorksheet().getRange("E30");
    masterTotalRange.formulas = [["=SUM(" + grandTotalRange.address + ")"]];
    await context.sync();
});

Tipos de layout

As tabelas dinâmicas têm três estilos de layout: Compact, Outline e Tabular. Vimos o estilo compacto nos exemplos anteriores.

Os exemplos a seguir usam o contorno e os estilos tabulares, respectivamente. O exemplo de código mostra como fazer o ciclo entre os diferentes layouts.

Layout de contorno

Uma Tabela Dinâmica usando o layout de contorno.

Layout tabular

Uma Tabela Dinâmica usando o layout tabular.

Exemplo de código de comutador de tipo PivotLayout

await Excel.run(async (context) => {
    // Change the PivotLayout.type to a new type.
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.layout.load("layoutType");
    await context.sync();

    // Cycle between the three layout types.
    if (pivotTable.layout.layoutType === "Compact") {
        pivotTable.layout.layoutType = "Outline";
    } else if (pivotTable.layout.layoutType === "Outline") {
        pivotTable.layout.layoutType = "Tabular";
    } else {
        pivotTable.layout.layoutType = "Compact";
    }

    await context.sync();
});

Outras funções PivotLayout

Por padrão, as Tabelas Dinâmicas ajustam os tamanhos de linha e coluna conforme necessário. Isso é feito quando a Tabela Dinâmica é atualizada. PivotLayout.autoFormat especifica esse comportamento. Todas as alterações de tamanho de linha ou coluna feitas pelo suplemento persistem quando autoFormat é false. Além disso, as configurações padrão de uma Tabela Dinâmica mantêm qualquer formatação personalizada na Tabela Dinâmica (como preenchimentos e alterações de fonte). Defina PivotLayout.preserveFormatting como false para aplicar o formato padrão quando atualizado.

Um PivotLayout também controla o cabeçalho e as configurações totais da linha, como as células de dados vazias são exibidas e as opções de texto alt . A referência PivotLayout fornece uma lista completa desses recursos.

O exemplo de código a seguir faz com que as células de dados vazias exibam a cadeia de caracteres "--", formata o intervalo do corpo para um alinhamento horizontal consistente e garante que as alterações de formatação permaneçam mesmo após a atualização da Tabela Dinâmica.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
    let pivotLayout = pivotTable.layout;

    // Set a default value for an empty cell in the PivotTable. This doesn't include cells left blank by the layout.
    pivotLayout.emptyCellText = "--";

    // Set the text alignment to match the rest of the PivotTable.
    pivotLayout.getDataBodyRange().format.horizontalAlignment = Excel.HorizontalAlignment.right;

    // Ensure empty cells are filled with a default value.
    pivotLayout.fillEmptyCells = true;

    // Ensure that the format settings persist, even after the PivotTable is refreshed and recalculated.
    pivotLayout.preserveFormatting = true;
    await context.sync();
});

Excluir uma tabela dinâmica

As tabelas dinâmicas são excluídas usando seu nome.

await Excel.run(async (context) => {
    context.workbook.worksheets.getItem("Pivot").pivotTables.getItem("Farm Sales").delete();
    await context.sync();
});

Filtrar uma tabela dinâmica

O método principal para filtrar dados de Tabela Dinâmica é com PivotFilters. As segmentações oferecem um método de filtragem alternativo e menos flexível.

Os PivotFilters filtram dados com base nas quatro categorias de hierarquia de uma Tabela Dinâmica (filtros, colunas, linhas e valores). Há quatro tipos de PivotFilters, permitindo filtragem baseada em data de calendário, análise de cadeia de caracteres, comparação de números e filtragem com base em uma entrada personalizada.

As segmentações podem ser aplicadas a tabelas dinâmicas e regulares do Excel. Quando aplicados a uma Tabela Dinâmica, as segmentações funcionam como um PivotManualFilter e permitem filtragem com base em uma entrada personalizada. Ao contrário de PivotFilters, as segmentações têm um componente da interface do usuário do Excel. Com a Slicer classe, você cria esse componente de interface do usuário, gerencia a filtragem e controla sua aparência visual.

Filtrar com PivotFilters

Os PivotFilters permitem filtrar dados de Tabela Dinâmica com base nas quatro categorias de hierarquia (filtros, colunas, linhas e valores). No modelo de objeto PivotTable, PivotFilters são aplicados a um PivotField e cada PivotField um pode ter um ou mais atribuídos PivotFilters. Para aplicar PivotFilters a um PivotField, a PivotHierarchy correspondente do campo deve ser atribuída a uma categoria de hierarquia.

Tipos de PivotFilters

Tipo de filtro	Finalidade de filtro	Referência da API JavaScript do Excel
DateFilter	Filtragem baseada em data de calendário.	PivotDateFilter
LabelFilter	Filtragem de comparação de texto.	PivotLabelFilter
ManualFilter	Filtragem de entrada personalizada.	PivotManualFilter
ValueFilter	Filtragem de comparação de números.	PivotValueFilter

Criar um PivotFilter

Para filtrar dados de Tabela Dinâmica com um Pivot*Filter (como um PivotDateFilter), aplique o filtro a um PivotField. Os quatro exemplos de código a seguir mostram como usar cada um dos quatro tipos de PivotFilters.

PivotDateFilter

O primeiro exemplo de código aplica um PivotDateFilter ao Date Updated PivotField, ocultando todos os dados anteriores a 2020-08-01.

Importante

Um Pivot*Filter não pode ser aplicado a um PivotField a menos que a PivotHierarchy desse campo seja atribuída a uma categoria de hierarquia. No exemplo de código a seguir, o dateHierarchy deve ser adicionado à categoria da rowHierarchies Tabela Dinâmica antes de poder ser usado para filtragem.

await Excel.run(async (context) => {
    // Get the PivotTable and the date hierarchy.
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    let dateHierarchy = pivotTable.rowHierarchies.getItemOrNullObject("Date Updated");
    await context.sync();

    // PivotFilters can only be applied to PivotHierarchies that are being used for pivoting.
    // If it's not already there, add "Date Updated" to the hierarchies.
    if (dateHierarchy.isNullObject) {
        dateHierarchy = pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Date Updated"));
    }

    // Apply a date filter to filter out anything logged before August.
    let filterField = dateHierarchy.fields.getItem("Date Updated");
    let dateFilter = {
        condition: Excel.DateFilterCondition.afterOrEqualTo,
        comparator: {
        date: "2020-08-01",
        specificity: Excel.FilterDatetimeSpecificity.month
        }
    };
    filterField.applyFilter({ dateFilter: dateFilter });
    
    await context.sync();
});

Observação

Os três snippets de código a seguir exibem apenas trechos específicos do filtro, em vez de chamadas completas Excel.run .

PivotLabelFilter

O segundo snippet de código demonstra como aplicar um PivotLabelFilter ao Type PivotField, usando a LabelFilterCondition.beginsWith propriedade para excluir rótulos que começam com a letra L.

    // Get the "Type" field.
    let filterField = pivotTable.hierarchies.getItem("Type").fields.getItem("Type");

    // Filter out any types that start with "L" ("Lemons" and "Limes" in this case).
    let filter: Excel.PivotLabelFilter = {
      condition: Excel.LabelFilterCondition.beginsWith,
      substring: "L",
      exclusive: true
    };

    // Apply the label filter to the field.
    filterField.applyFilter({ labelFilter: filter });

PivotManualFilter

O terceiro snippet de código aplica um filtro manual com PivotManualFilter ao campo Classificação , filtrando dados que não incluem a classificação Organic.

    // Apply a manual filter to include only a specific PivotItem (the string "Organic").
    let filterField = classHierarchy.fields.getItem("Classification");
    let manualFilter = { selectedItems: ["Organic"] };
    filterField.applyFilter({ manualFilter: manualFilter });

PivotValueFilter

Para comparar números, use um filtro de valor com PivotValueFilter, conforme mostrado no snippet de código final. O PivotValueFilter compara os dados no Farm PivotField com os dados no Crates Sold Wholesale PivotField, incluindo apenas fazendas cuja soma de caixas vendidas excede o valor 500.

    // Get the "Farm" field.
    let filterField = pivotTable.hierarchies.getItem("Farm").fields.getItem("Farm");
    
    // Filter to only include rows with more than 500 wholesale crates sold.
    let filter: Excel.PivotValueFilter = {
      condition: Excel.ValueFilterCondition.greaterThan,
      comparator: 500,
      value: "Sum of Crates Sold Wholesale"
    };
    
    // Apply the value filter to the field.
    filterField.applyFilter({ valueFilter: filter });

Remover PivotFilters

Para remover todos os PivotFilters, aplique o clearAllFilters método a cada PivotField, conforme mostrado no exemplo de código a seguir.

await Excel.run(async (context) => {
    // Get the PivotTable.
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.hierarchies.load("name");
    await context.sync();

    // Clear the filters on each PivotField.
    pivotTable.hierarchies.items.forEach(function (hierarchy) {
        hierarchy.fields.getItem(hierarchy.name).clearAllFilters();
    });
    await context.sync();
});

Filtrar com segmentações

As segmentações permitem que os dados sejam filtrados de uma tabela ou tabela dinâmica do Excel. Uma segmentação usa valores de uma coluna especificada ou PivotField para filtrar linhas correspondentes. Esses valores são armazenados como objetos SlicerItem no Slicer. Seu suplemento pode ajustar esses filtros, assim como os usuários (por meio da interface do usuário do Excel). A segmentação fica na parte superior da planilha na camada de desenho, conforme mostrado na captura de tela a seguir.

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

Observação

As técnicas descritas nesta seção se concentram em como usar segmentações conectadas a tabelas dinâmicas. As mesmas técnicas também se aplicam ao uso de segmentações conectadas a tabelas.

Criar uma segmentação

Você pode criar uma segmentação em uma pasta de trabalho ou planilha usando o método ou Worksheet.slicers.add o Workbook.slicers.add método. Isso adiciona uma segmentação à SlicerCollection do objeto ou Worksheet especificadoWorkbook. O SlicerCollection.add método tem três parâmetros:

slicerSource: a fonte de dados na qual a nova segmentação se baseia. Pode ser uma PivotTablecadeia de caracteres , Tableou que representa o nome ou a ID de um PivotTable ou Table.
sourceField: o campo na fonte de dados pelo qual filtrar. Pode ser uma PivotFieldcadeia de caracteres , TableColumnou que representa o nome ou a ID de um PivotField ou TableColumn.
slicerDestination: a planilha em que a nova segmentação será criada. Pode ser um Worksheet objeto ou o nome ou ID de um Worksheet. Esse parâmetro é desnecessário quando o SlicerCollection é acessado por meio Worksheet.slicersde . Nesse caso, a planilha da coleção é usada como destino.

O exemplo de código a seguir adiciona uma nova segmentação à planilha Dinâmica . A origem da segmentação é a Tabela Dinâmica de Vendas do Farm e filtra usando os dados type . A segmentação também é chamada de Fruit Slicer para referência futura.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Pivot");
    let slicer = sheet.slicers.add(
        "Farm Sales" /* The slicer data source. For PivotTables, this can be the PivotTable object reference or name. */,
        "Type" /* The field in the data to filter by. For PivotTables, this can be a PivotField object reference or ID. */
    );
    slicer.name = "Fruit Slicer";
    await context.sync();
});

Filtrar itens com uma segmentação

O segmentador filtra a Tabela Dinâmica com itens do sourceField. O Slicer.selectItems método define os itens que permanecem na segmentação. Esses itens são passados para o método como um string[], representando as chaves dos itens. Todas as linhas que contêm esses itens permanecem na agregação da Tabela Dinâmica. Chamadas subsequentes para selectItems definir a lista para as chaves especificadas nessas chamadas.

Observação

Se Slicer.selectItems for passado um item que não está na fonte de dados, um InvalidArgument erro será gerado. O conteúdo pode ser verificado por meio da Slicer.slicerItems propriedade, que é uma SlicerItemCollection.

O exemplo de código a seguir mostra três itens sendo selecionados para a segmentação: Limão, Limão e Laranja.

await Excel.run(async (context) => {
    let slicer = context.workbook.slicers.getItem("Fruit Slicer");
    // Anything other than the following three values will be filtered out of the PivotTable for display and aggregation.
    slicer.selectItems(["Lemon", "Lime", "Orange"]);
    await context.sync();
});

Para remover todos os filtros da segmentação, use o Slicer.clearFilters método, conforme mostrado no exemplo a seguir.

await Excel.run(async (context) => {
    let slicer = context.workbook.slicers.getItem("Fruit Slicer");
    slicer.clearFilters();
    await context.sync();
});

Estilo e formatar uma segmentação

O suplemento pode ajustar as configurações de exibição de uma segmentação por meio de Slicer propriedades. O exemplo de código a seguir define o estilo como SlicerStyleLight6, define o texto na parte superior da segmentação como Tipos de Frutas, coloca a segmentação na posição (395, 15) na camada de desenho e define o tamanho da segmentação como 135x150 pixels.

await Excel.run(async (context) => {
    let slicer = context.workbook.slicers.getItem("Fruit Slicer");
    slicer.caption = "Fruit Types";
    slicer.left = 395;
    slicer.top = 15;
    slicer.height = 135;
    slicer.width = 150;
    slicer.style = "SlicerStyleLight6";
    await context.sync();
});

Excluir uma segmentação

Para excluir uma segmentação, chame o Slicer.delete método. O exemplo de código a seguir exclui a primeira segmentação da planilha atual.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.slicers.getItemAt(0).delete();
    await context.sync();
});

Função de agregação de alterações

As hierarquias de dados têm seus valores agregados. Para conjuntos de dados de números, essa é uma soma por padrão. A summarizeBy propriedade define esse comportamento com base em um tipo AggregationFunction .

Os tipos de função de agregação atualmente compatíveis são Sum, Count, , Average, Max, ProductMin, CountNumbers, StandardDeviation, StandardDeviationP, , Variance, VarianceP, e Automatic (o padrão).

Os exemplos de código a seguir alteram a agregação para serem médias dos dados.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.dataHierarchies.load("no-properties-needed");
    await context.sync();

    // Change the aggregation from the default sum to an average of all the values in the hierarchy.
    pivotTable.dataHierarchies.items[0].summarizeBy = Excel.AggregationFunction.average;
    pivotTable.dataHierarchies.items[1].summarizeBy = Excel.AggregationFunction.average;
    await context.sync();
});

Alterar cálculos com um ShowAsRule

As tabelas dinâmicas, por padrão, agregam os dados de suas hierarquias de linha e coluna de forma independente. Um ShowAsRule altera a hierarquia de dados para valores de saída com base em outros itens na Tabela Dinâmica.

O ShowAsRule objeto tem três propriedades:

calculation: o tipo de cálculo relativo a ser aplicado à hierarquia de dados (o padrão é none).
baseField: o PivotField na hierarquia que contém os dados base antes do cálculo ser aplicado. Como as Tabelas Dinâmicas do Excel têm um mapeamento um para um de hierarquia em campo, você usará o mesmo nome para acessar a hierarquia e o campo.
baseItem: o PivotItem individual comparado com os valores dos campos base com base no tipo de cálculo. Nem todos os cálculos exigem esse campo.

O exemplo a seguir define o cálculo sobre a hierarquia de dados Soma das Caixas Vendidas no Farm como uma porcentagem do total de colunas. Ainda queremos que a granularidade se estenda ao nível do tipo de fruta, portanto, usaremos a hierarquia de linha type e seu campo subjacente. O exemplo também tem Farm como a primeira hierarquia de linha, portanto, as entradas totais do farm exibem o percentual que cada farm também é responsável pela produção.

Uma Tabela Dinâmica mostrando os percentuais de vendas de frutas em relação ao total total para fazendas individuais e tipos de frutas individuais em cada fazenda.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    let farmDataHierarchy = pivotTable.dataHierarchies.getItem("Sum of Crates Sold at Farm");

    farmDataHierarchy.load("showAs");
    await context.sync();

    // Show the crates of each fruit type sold at the farm as a percentage of the column's total.
    let farmShowAs = farmDataHierarchy.showAs;
    farmShowAs.calculation = Excel.ShowAsCalculation.percentOfColumnTotal;
    farmShowAs.baseField = pivotTable.rowHierarchies.getItem("Type").fields.getItem("Type");
    farmDataHierarchy.showAs = farmShowAs;
    farmDataHierarchy.name = "Percentage of Total Farm Sales";
});

O exemplo anterior define o cálculo como a coluna, em relação ao campo de uma hierarquia de linha individual. Quando o cálculo estiver relacionado a um item individual, use a baseItem propriedade.

O exemplo a seguir mostra o differenceFrom cálculo. Ele exibe a diferença das entradas da hierarquia de dados de vendas de caixa de farm em relação às de A Farms. O baseField é Farm, portanto, vemos as diferenças entre as outras fazendas, bem como as divisões para cada tipo de fruta (Tipo também é uma hierarquia de linhas neste exemplo).

Uma Tabela Dinâmica mostrando as diferenças das vendas de frutas entre

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    let farmDataHierarchy = pivotTable.dataHierarchies.getItem("Sum of Crates Sold at Farm");

    farmDataHierarchy.load("showAs");
    await context.sync();
        
    // Show the difference between crate sales of the "A Farms" and the other farms.
    // This difference is both aggregated and shown for individual fruit types (where applicable).
    let farmShowAs = farmDataHierarchy.showAs;
    farmShowAs.calculation = Excel.ShowAsCalculation.differenceFrom;
    farmShowAs.baseField = pivotTable.rowHierarchies.getItem("Farm").fields.getItem("Farm");
    farmShowAs.baseItem = pivotTable.rowHierarchies.getItem("Farm").fields.getItem("Farm").items.getItem("A Farms");
    farmDataHierarchy.showAs = farmShowAs;
    farmDataHierarchy.name = "Difference from A Farms";
});

Alterar nomes de hierarquia

Os campos hierarquia são editáveis. O código a seguir demonstra como alterar os nomes exibidos de duas hierarquias de dados.

await Excel.run(async (context) => {
    let dataHierarchies = context.workbook.worksheets.getActiveWorksheet()
        .pivotTables.getItem("Farm Sales").dataHierarchies;
    dataHierarchies.load("no-properties-needed");
    await context.sync();

    // Changing the displayed names of these entries.
    dataHierarchies.items[0].name = "Farm Sales";
    dataHierarchies.items[1].name = "Wholesale";
});

Compartilhar via

Trabalhar com tabelas dinâmicas usando a API JavaScript do Excel

Modelo de objetos

Hierarquias

Criar uma tabela dinâmica

Criar uma tabela dinâmica com endereços de intervalo

Criar uma tabela dinâmica com objetos Range

Criar uma tabela dinâmica no nível da pasta de trabalho

Usar uma tabela dinâmica existente

Adicionar linhas e colunas a uma Tabela Dinâmica

Adicionar hierarquias de dados à Tabela Dinâmica

Layouts de tabela dinâmica e obtenção de dados dinâmicos

Obter dados da Tabela Dinâmica

Tipos de layout

Layout de contorno

Layout tabular

Exemplo de código de comutador de tipo PivotLayout

Outras funções PivotLayout

Excluir uma tabela dinâmica

Filtrar uma tabela dinâmica

Filtrar com PivotFilters

Tipos de PivotFilters

Criar um PivotFilter

PivotDateFilter

PivotLabelFilter

PivotManualFilter

PivotValueFilter

Remover PivotFilters

Filtrar com segmentações

Criar uma segmentação

Filtrar itens com uma segmentação

Estilo e formatar uma segmentação

Excluir uma segmentação

Função de agregação de alterações

Alterar cálculos com um ShowAsRule

Alterar nomes de hierarquia

Confira também

Recursos adicionais