Neste artigo aprenderá como usar a função Pivotar ou PivotBy do Excel passo-a-passo com download gratuito.
Como Funciona a Função Pivotar ou PivotBy Excel
Abaixo detalhes de como funciona a função Pivotar do Excel e seus parâmetros:
| Objetivo: | A função PIVOTBY permite criar um resumo dos seus dados através de uma fórmula. Ele suporta agrupamento ao longo de dois eixos e agregação dos valores associados. Por exemplo, se você tivesse uma tabela de dados de vendas, poderia gerar um resumo das vendas por estado e ano. |
| Sintaxe: | =PIVOTBY(row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array]) |
| Parâmetros: | |
| row_fields (required) | Uma matriz ou intervalo orientado a colunas que contém os valores usados para agrupar linhas e gerar cabeçalhos de linha. A matriz ou intervalo pode conter várias colunas. Nesse caso, a saída terá vários níveis de grupo de linhas. |
| col_fields (required) | Uma matriz ou intervalo orientado a colunas que contém os valores usados para agrupar colunas e gerar cabeçalhos de colunas. A matriz ou intervalo pode conter várias colunas. Nesse caso, a saída terá vários níveis de grupo de colunas. |
| values (required) | Uma matriz ou intervalo orientado a colunas de dados a serem agregados. A matriz ou intervalo pode conter várias colunas. Nesse caso, a saída terá múltiplas agregações. |
| function (required) | Uma função lambda ou lambda eta reduzida (SUM, AVERAGE, COUNT, etc) que define como agregar os valores. Um vetor de lambdas pode ser fornecido. Nesse caso, a saída terá múltiplas agregações. A orientação do vetor determinará se eles serão dispostos em linhas ou colunas. |
| field_headers | Um número que especifica se row_fields , col_fields e valores têm cabeçalhos e se os cabeçalhos dos campos devem ser retornados nos resultados. Os valores possíveis são: Ausente : Automático. 0 : Não 1 : Sim e não mostra 2 : Não, mas gera 3 : Sim e mostra Nota: Automático assume que os dados contêm cabeçalhos com base no argumento de valores. Se o primeiro valor for texto e o segundo valor for um número, presume-se que os dados tenham cabeçalhos. Os cabeçalhos dos campos serão mostrados se houver vários níveis de grupo de linhas ou colunas. |
| row_total_depth | Determina se os cabeçalhos das linhas devem conter totais. Os valores possíveis são: Ausente : Automático: totais gerais e, sempre que possível, subtotais. 0 : Sem totais 1 : Totais gerais 2 : Totais gerais e subtotais -1 : Totais gerais no topo -2 : Totais gerais e subtotais no topo Nota: Para subtotais, row_fields deve ter pelo menos 2 colunas. Números maiores que 2 são suportados, desde que row_field tenha colunas suficientes. |
| row_sort_order | Um número que indica como as linhas devem ser classificadas. Os números correspondem às colunas em row_fields seguidas pelas colunas em values . Se o número for negativo, as linhas serão classificadas em ordem decrescente/inversa. Um vetor de números pode ser fornecido ao classificar com base apenas em row_fields . |
| col_total_depth | Determines whether the column headers should contain totals. The possible values are: Missing: Automatic: Grand totals and, where possible, subtotals. 0: No Totals 1: Grand Totals 2: Grand and Subtotals -1: Grand Totals at Top -2: Grand and Subtotals at Top Note: For subtotals, col_fields must have at least 2 columns. Numbers greater than 2 are supported provided col_field has sufficient columns. |
| col_sort_order | Um número que indica como as linhas devem ser classificadas. Os números correspondem às colunas em col_fields seguidas pelas colunas em values . Se o número for negativo, as linhas serão classificadas em ordem decrescente/inversa. Um vetor de números pode ser fornecido ao classificar com base apenas em col_fields . |
| filter_array | Uma matriz 1D de booleanos orientada a colunas que indica se a linha de dados correspondente deve ser considerada. Nota: O comprimento da matriz deve corresponder ao comprimento daqueles fornecidos para row_fields e col_fields . |
Exemplo de Criação de Tabela com a Função Pivotar
No exemplo abaixo temos uma tabela que iremos transpor com a função pivotar.
A função aplicada foi:
=PIVOTBY(Table1[[#All];[IMAGEM]:[REGIÃO]];Table1[[#All];[ANO]];Table1[[#All];[VALOR]];AVERAGE;3;2;;;;(Table1[[#All];[VENDEDOR]]=J5)*(Table1[[#All];[ANO]]=J4))
Como resultado temos a seguinte tabela:
Perceba que na função temos nas linhas as imagens e nas colunas temos o ano e nos valores as vendas por cada vendedor.
Como Conciliar Dados com a Função Pivotar ou Pivotby Excel
Outro exemplo que temos é para conciliar dados.
Veja que abaixo temos uma lista com o número da nota fiscal, valor e a origem dos dados.
Para realizar a conciliação copiamos os dados todos para uma tabela única como temos abaixo:
E aplicamos a seguinte fórmula:
=PIVOTBY(Table2[[#All];[Nota]];Table2[[#All];[Origem]];IF(Table2[[#All];[Origem]]=”Contábil”;-Table2[[#All];[Valor]];Table2[[#All];[Valor]]);SUM;;;)
Temos com isso os dados dispostos como vemos abaixo, com uma coluna com a chave, outro com o contábil e financeiro.
Após isso clique no filtro e desmarque os itens com diferença 0:
Como resultado temos a seguinte lista somente com as notas fiscais com diferenças:
Exemplo de Orçamento com a Função Pivotar
Neste exemplo temos o orçado e o realizado de cada despesa de uma empresa por departamento e data.
No nosso exemplo aplicamos a seguinte fórmula:
=IF($G$2=”Gasto”;PIVOTBY(Table3[[#All];[Despesas]];TEXT(Table3[[#All];[Data]];”MM/AA”);Table3[[#All];[Orçado]]-Table3[[#All];[Realizado]];SUM;1);
IF($G$2=”Percentual”;PIVOTBY(Table3[[#All];[Despesas]];TEXT(Table3[[#All];[Data]];”MM/AA”);1-(Table3[[#All];[Realizado]]/Table3[[#All];[Orçado]]);SUM;1);
PIVOTBY(Table3[[#All];[Despesas]];TEXT(Table3[[#All];[Data]];”MM/AA”);INDIRECT(“Table3[[#All];[“&G2&”]]”);SUM;1)))
Como resultado temos a tabela seguinte, separada por valores GASTO, ORÇADO, PERCENTUAL e REALIZADO:
Nela também aplicamos a seguinte fórmula, que será aplicada ao dar duplo clique em qualquer célula da tabela:
=FILTER(HSTACK(Table3[#All];Table3[[#All];[Orçado]]-Table3[[#All];[Realizado]]);(TEXT(Table3[[#All];[Data]];”MM/AA”)=INDIRECT(ADDRESS(5;COLUMN(INDIRECT(CELL(“address”))))));””)
Download Planilha Pivotar ou PivotBy Excel
Clique no botão abaixo para realizar o download do arquivo de exemplo: