Fixando os Formatos em Tabelas Dinâmicas

O uso de tabelas dinâmicas é muito comum em relatórios de entrega periódica. Por isso, costuma-se atualizar a fonte dos dados e, para refletir tais alterações nas dinâmicas, seleciona-se a opção de Atualizar (na aba Dados ou clicando com o botão direito na tabela). Vejamos um exemplo, em que temos uma base de registro de volumes de vendas e reclamações recebidas por mês e por loja:

Podemos criar uma tabela dinâmica fazendo uso do atalho [Alt + T + B], confirmando com o OK. Em seguida, arrastamos o campo "Mês" para a área Colunas e o campo "Loja" para a área Linhas. Fazendo isso, obtemos a seguinte estrutura da tabela:

Agora vamos criar um campo calculado para o índice de reclamações, que seria a quantidade de reclamações dividida pela quantidade de ordens de venda em um determinado período. Para isso, seleciona-se a tabela dinâmica e, na barra superior de ferramentas, escolhemos a opção:

Neste caso, o novo campo, que podemos chamar de "ÍndiceReclamações", é uma conta de divisão entre "Qtd Reclamações" e "Qtd Ordens de Venda":

Arrastando os campos "ÍndiceReclamações" e "Qtd Reclamações" para a área Valores (e passando as informações para as linhas), removendo a coluna de total geral, ajustando a largura das colunas e formatando as células com valores, podemos obter o seguinte resultado:

Ok, tudo estaria pronto para reportarmos este mês. Mas vamos supor que no mês que vem sejam adicionadas duas linhas na nossa base inicial:

Primeiramente, é preciso garantir que a tabela dinâmica incluirá as novas linhas, selecionando a opção de alterar fonte de dados:

Nota-se que o intervalo de dados termina na linha 11 (range $A$1:$D$11):

Após alterar para a linha 13 (range $A$1:$D$13), incluindo os novos dados, e confirmar, a tabela dinâmica será atualizada automaticamente:

E note que houve duas mudanças na formatação:

1. A largura das colunas foi auto-ajustada pelo conteúdo;
2. Os formatos dos valores foram perdidos (o índice de reclamação não consta mais como porcentagem).

Para evitar que o primeiro problema volte a ocorrer, é necessário clicar com o botão direito na tabela dinâmica e selecionar Opções da Tabela Dinâmica.... Na tela recém aberta, vamos à aba Layout e Formato, onde precisamos desmarcar a opção Ajustar automaticamente a largura das colunas ao atualizar:

Feito isso, vamos garantir que a formatação dos valores não seja alterada:

Selecionando Formato do Número:

Aqui, o campo "ÍndiceReclamações" será configurado como porcentagem com um algarismo depois da vírgula:

Pronto! Agora basta formatar a tabela uma última vez e sempre que a base ou a TD for atualizada no futuro, todos os formatos serão mantidos:

Para acessar a planilha deste exemplo, clique aqui.

Tem alguma dúvida, crítica ou sugestão? Comente aí embaixo!

Contagem Distinta em Tabelas Dinâmicas

Uma ferramenta muito útil no Excel é a tabela dinâmica (Pivot Table), que permite criar e alterar resumos dos dados de forma rápida e prática. O objetivo deste post é mostrar uma possibilidade não muito conhecida de exibir a contagem distinta de itens em uma base.

Vejamos um exemplo em que temos uma loja de médio porte de melhoramentos domiciliares (construção, acabamento, decoração etc) e queremos incentivar os vendedores a concluírem vendas em departamentos diferentes para promover um maior entendimento sobre estoque e posicionamento dos itens. Para avaliação dos resultados do primeiro dia da campanha interna, recebemos uma base de dados com as vendas concluídas em 01/05/2020:

Selecionando uma célula da tabela, é possível criar uma tabela dinâmica com o atalho [Alt + T + B], sendo necessário apenas confirmar o intervalo de dados e onde a tabela dinâmica deverá ficar. Aqui, precisamos selecionar a opção Adicionar estes dados ao Modelo de Dados para habilitar a contagem distinta (versão 2013 ou mais recente):

Prosseguindo com a criação, uma nova aba será adicionada ao arquivo e, após arrastarmos o campo "UserVendedor" para a área Linhas e o campo "Setor" para a área Valores, devemos configurar a exibição do campo "Setor":

Na nova tela que deve aparecer, deverá ser selecionada a opção de Contagem Distinta, de forma a exibir o número de setores distintos em que cada vendedor concluiu pedidos:

Como resultado, obtemos a tabela dinâmica abaixo, a partir da qual podemos constatar que o vendedor que concluiu vendas em mais departamentos neste dia foi o de usuário "yfernandes".

Note que se, na criação da tabela dinâmica, não tivéssemos selecionado a opção de Adicionar estes dados ao Modelo de Dados, a opção de contagem distinta não seria exibida:

Para acessar os dados deste exemplo, clique aqui.

Tem alguma dúvida, crítica ou sugestão? Comente aí embaixo!

Calculando Porcentagens de Categorias no Power BI

Ao elaborar um relatório no Power BI, é muito comum termos que exibir algumas informações que não estão explícitas nas bases de dados. Vejamos um exemplo, onde os dados representam a receita bruta de uma empresa fictícia por canal de venda para acompanhamento dos impactos do COVID-19, de acordo com a tabela abaixo:

Neste caso, vamos elaborar um gráfico de colunas empilhadas que mostra a evolução dos canais de venda em termos de receita. Após importação dos dados para o Power BI, ajustes dos tipos e algumas configurações do visual, obtemos o seguinte resultado:

Agora vamos supor que queremos exibir a representatividade de cada canal sobre o total das vendas de cada mês ao mover o cursor sobre uma região do gráfico. Por exemplo, na imagem abaixo, ao selecionar a região de "Varejo (lojas físicas)" no mês de fev/2020, desejamos que apareça 29,4% (que seriam as vendas em lojas físicas, R$ 12.183 kBRL, sobre as vendas totais naquele mês, de R$ 41.383 kBRL).

É necessário criar uma nova Medida (Measure, na versão em Inglês). Neste caso, precisamos que o cálculo seja a receita de um determinado canal dividida pela soma das receitas desconsiderando a categorização por canal de venda. Ou seja, para que o denominador não seja filtrado pela coluna "Canal de venda", faremos uso da função ALL do DAX (linguagem padrão das medidas no Power BI):

Representatividade = SUM(Vendas[Receita (kBRL)])/CALCULATE(SUM(Vendas[Receita (kBRL)]);ALL(Vendas[Canal de venda]))

No numerador, a função SUM é aplicada de forma usual, uma vez que deve ser suscetível à categorização gerada pela coluna "Canal de venda" no campo "Legenda". Note que o SUM é utilizado neste caso, pois, para que a coluna "Receita (kBRL)" retorne um valor, ela deve estar contida dentro de uma função agregadora (como SUM ou AVG). No caso do denominador, torna-se necessário o uso da função CALCULATE, uma vez que não apenas desejamos somar as vendas, como também ignorar os filtros do visual na coluna "Canal de venda" por meio do ALL.

Em seguida, ao selecionar a medida, é possível configurá-la para exibição como porcentagem e com apenas um algarismo depois da vírgula:

Por fim, basta arrastar a nova medida para o campo "Tooltips" da visualização e já é possível verificar a representatividade ao passar o ponteiro do mouse sobre as regiões do gráfico.

Teste você mesmo, acesse os dados deste exemplo clicando aqui.

Tem alguma dúvida, crítica ou sugestão? Comente aí embaixo!

ÍNDICE + CORRESP com Múltiplios Critérios

Quando temos uma tabela com mais de uma informação nas linhas ou nas colunas, pode surgir uma dúvida em como prosseguir com as funções de busca. Vejamos o caso abaixo, por exemplo:

Aqui, temos a evolução histórica do preço por quilograma para cada "Loja" e "Produto". Muitas vezes, fazemos uso das funções ÍNDICE e CORRESP quando temos apenas uma informação no início das linhas e outra no topo das colunas, mas como buscar o preço do kg do tomate na loja B no dia 29/fev/2020 com uma expressão?

Podemos aplicar as funções ÍNDICE e CORRESP de duas formas:

{=ÍNDICE(C2:F7;CORRESP(1;(A2:A7=H2)*(B2:B7=I2);0);CORRESP(J2;C1:F1;0))}

(por verificação booleana)

ou

{=ÍNDICE(C2:F7;CORRESP(H2&I2;A2:A7&B2:B7;0);CORRESP(J2;C1:F1;0))}

(por concatenação)

Em ambos os casos, no primeiro argumento da função ÍNDICE, é indicada a matriz de valores, que seria o conjunto C2:F7 e, no terceiro argumento, a função CORRESP é aplicada de forma usual, buscando a data (J2) no intervalo C1:F1 com correspondência exata, retornando 2, uma vez que 29/fev/2020 é a 2ª data do cabeçalho.

Método Booleano

No primeiro caso, foi utilizada a lógica booleana, onde as igualdades entre os cabeçalhos e os valores pretendidos retornam vetores com 0 e 1. Neste caso:

(A2:A7=H2)={FALSO;FALSO;FALSO;VERDADEIRO;VERDADEIRO;VERDADEIRO}

verifica a correspondência do campo "Loja"

(B2:B7=I2)={VERDADEIRO;FALSO;FALSO;VERDADEIRO;FALSO;FALSO}

verifica a correspondência do campo "Produto"

E o resultado do produto será 1 somente na posição em que há VERDADEIRO em ambos os vetores acima:

(A2:A7=H2)*(B2:B7=I2)={0;0;0;1;0;0}

Com a função CORRESP, buscando o valor 1 no  intervalo acima, obtém-se 4, que corresponde à posição da linha em que a loja é B e o produto é tomate. Assim, a função ÍNDICE buscará o elemento na 4ª linha e 2ª coluna da matriz C2:F7, ou seja, o preço de 5,49 por quilograma.

Concatenação

Outra forma de resolver este problema seria concatenando os campos "Loja" e "Produto". Desta forma, ao invés de buscar "B" no campo "Loja" e "Tomate" no campo "Produto" separadamente, buscamos "BTomate" no vetor resultante da junção de "Loja"&"Produto". Nas expressões:

H2&I2="BTomate"

e

A2:A7&B2:B7={"ATomate";"APepino";"ARabanete";"BTomate";"BPepino";"BRabanete"}

Sendo equivalente somente na posição 4 (resultado do CORRESP) e fazendo com que a função ÍNDICE retorne 5,49 também.

Para ter acesso aos dados deste exemplo, clique aqui.

Tem alguma dúvida, crítica ou sugestão? Comente aí embaixo!

Funções de Busca PROCV e ÍNDICE + CORRESP

Para quem trabalha com o Microsoft Excel, é muito comum se deparar com a necessidade de buscar valores em um conjunto de dados. Por exemplo, na tabela abaixo, temos o PIB (Produto Interno Bruto) nominal referente a 2018 de alguns países (em milhões de dólares americanos), assim como sua posição no ranking e a região geográfica:

Vamos supor que precisemos buscar rapidamente o país cuja posição no ranking é a de #5, por meio de uma fórmula. Isso pode ser feito com a função PROCV da seguinte forma:

No caso do PROCV, inserimos como parâmetros na função:

1. Valor procurado - neste caso, buscamos o país com ranking #5 e, portanto, será necessário buscar o número 5 na primeira coluna da tabela do exemplo (Posição);
2. Matriz/tabela - aqui, indicamos a tabela que contém as informações que nós temos (Posição) e as que desejamos (País), que, neste caso, é o conjunto A1:D31 (o valor procurado, a posição, deve estar na primeira coluna desta tabela);
3. Coluna do resultado desejado - como a função deve retornar o país correspondente ao ranking #5 e os nomes dos países estão na 3ª coluna da tabela selecionada, devemos indicar o número 3;
4. Tipo correspondência - se a correspondência precisa ser exata, é necessário inserir FALSO (ou 0), caso contrário, se a correspondência pode ser aproximada, devemos inserir VERDADEIRO (ou 1). Aqui, queremos um resultado exato e inserimos o 0.

Com isso, o resultado seria "Reino Unido", como esperado:

Uma outra maneira de resolver este problema seria por meio da popular combinação das funções ÍNDICE e CORRESP:

Começando de dentro para fora, a função CORRESP resulta na posição de um elemento em um vetor (linha ou coluna). Neste caso, desejamos localizar em qual linha está o país de ranking #5, com os seguintes parâmetros:

1. Valor procurado - assim como na função PROCV, devemos indicar o valor que estamos buscando, que aqui é o número 5;
2. Matriz procurada - estamos buscando o número 5 na coluna "Posição", que no caso é a primeira da tabela, ou seja, o intervalo A1:A31;
3. Tipo correspondência - assim como no caso do PROCV, optaremos pela correspondência exata, inserindo o número 0.

Neste ponto, a função CORRESP retornará 6 como resultado, pois o número 5 está na 6ª posição do conjunto A1:A31.

Em seguida, faremos uso da função ÍNDICE, com os inputs:

1. Matriz - o range do resultado desejado, que, neste caso, é a coluna de países, no intervalo C1:C31;
2. Número da linha - que é a posição no intervalo C1:C31 do elemento que queremos retornar, indicada pela função CORRESP.

Com isso, também obtemos o resultado "Reino Unido":

Para obter a planilha do exemplo, clique aqui.

Tem alguma dúvida, crítica ou sugestão? Comente aí embaixo!