Fórmula de tabela dinâmica no Excel (índice)
- Fórmula de tabela dinâmica no Excel
- Campo personalizado para calcular o valor do lucro
- Fórmula avançada no campo calculado
Fórmula de tabela dinâmica no Excel
Tabela Dinâmica é uma ferramenta que nos permite analisar grandes intervalos de dados. Podemos analisar, interpretar e fazer muitas outras coisas sem quebrar muito a cabeça e o suor. Pode fornecer quase tudo o que existe nos dados de origem.
Se algumas informações estiverem nos dados de origem, talvez seja necessário calcular isso por nós mesmos. Por exemplo, se tivermos um Valor total de vendas e um custo total, talvez seja necessário calcular o lucro ou prejuízo total por conta própria.
Não precisamos fazer isso na fonte, mas podemos fazer isso dentro da própria tabela dinâmica, esses são chamados campos calculados dentro da tabela dinâmica. Podemos usar fórmulas personalizadas para criar os dados para contar mais histórias a partir dos dados. Campos calculados nos permite criar uma nova coluna calculada que não existe na fonte de dados real.
Neste artigo, demonstraremos as maneiras de usar os campos calculados da tabela dinâmica para criar novas colunas com base em nossos requisitos.
Campo personalizado para calcular o valor do lucro
Você pode fazer o download deste modelo de Excel de fórmula de tabela dinâmica aqui - Modelo de Excel de fórmula de tabela dinâmicaEste é o campo calculado usado com mais frequência na tabela dinâmica. Dê uma olhada nos dados abaixo, tenho a coluna Nome do país, Nome do produto, Unidades vendidas, Preço unitário, Vendas brutas, CPV (custo dos produtos vendidos), Data e Ano.
Deixe-me aplicar a tabela dinâmica para encontrar as vendas totais e o custo total de cada país. Abaixo está a tabela dinâmica para os dados acima.
O problema é que não tenho uma coluna de lucro nos dados de origem. Preciso descobrir o lucro e a porcentagem de lucro de cada país. Podemos adicionar essas duas colunas na própria tabela dinâmica.
Etapa 1: selecione uma célula na tabela dinâmica. Vá para a guia Analisar na faixa de opções e selecione Campos, itens e conjuntos. Abaixo deste, selecione Campo calculado.
Etapa 2: na caixa de diálogo abaixo, dê um nome ao seu novo campo calculado.
Etapa 3: na seção Fórmula, aplique a fórmula para encontrar o Lucro. A fórmula para encontrar o lucro é vendas brutas - CPV.
Vá para a barra de fórmulas> selecione Vendas brutas no campo abaixo e clique duas vezes em que aparecerá na barra de fórmulas.
Agora digite o símbolo de menos (-) e selecione COGS> Clique duas vezes.
Etapa 4: clique em ADICIONAR e OK para concluir a fórmula.
Etapa 5: Agora temos nossa coluna TOTAL PROFIT na tabela dinâmica.
Este campo calculado é flexível, não se limita apenas à análise por país, mas podemos usá-lo para todos os tipos de análise. Se eu quiser ver a análise por país e por produto, basta arrastar e soltar a coluna do produto no campo ROW para mostrar a divisão do lucro de cada produto em cada país.
Etapa 6: agora precisamos calcular a porcentagem de lucro. A fórmula para calcular a porcentagem de lucro é lucro total / vendas brutas.
Vá para Analisar e selecione novamente Campo calculado em Campos, itens e conjuntos.
Etapa 7: Agora devemos ver o lucro calculado recentemente inserido no campo calculado na lista Campos. Insira este campo na fórmula.
Etapa 8: Digite o símbolo do divisor (/) e insira o Campo de Vendas Brutas.
Etapa 9: Nomeie este campo calculado como porcentagem de lucro.
Etapa 10: clique em ADICIONAR e OK para concluir a fórmula. Temos a Porcentagem de lucro como a nova coluna.
Fórmula avançada no campo calculado
Tudo o que mostrei agora é o material básico do campo calculado. Neste exemplo, mostrarei as fórmulas avançadas nos campos calculados da tabela dinâmica. Agora, quero calcular o valor do incentivo com base na porcentagem de lucro.
Se o% de lucro for> 15%, o incentivo deve ser 6% do lucro total.
Se o% de lucro for> 10%, o incentivo deve ser 5% do lucro total.
Se o% de lucro for <10%, o incentivo deve ser 3% do lucro total.
Etapa 1: Vá para o campo calculado e abra a caixa de diálogo abaixo. Atribua o nome como valor do incentivo.
Etapa 2: Agora, usarei a condição SE para calcular o valor do incentivo. Aplique as fórmulas abaixo, como mostra a imagem.
= SE ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))
Etapa 3: clique em ADICIONAR & OK para concluir. Agora, temos uma coluna Valor do incentivo.
Limitação do campo calculado
Vimos a maravilha dos campos calculados, mas também tem algumas limitações. Agora, veja a imagem abaixo: se eu quiser ver a divisão do valor do incentivo em termos de produto, teremos SUB TOTAL & GRAND TOTAL de MONTANTE INCENTIVO.
Portanto, tenha cuidado ao mostrar o subtotal de campos calculados. Ele mostrará as quantias erradas.
Obter lista de todas as fórmulas de campo calculadas
Se você não souber quantas fórmulas existem no campo calculado da tabela dinâmica, poderá obter o resumo de todas elas em uma planilha separada.
Vá para Analisar> Campos, Itens e Conjuntos -> Listar Fórmulas.
Ele fornecerá um resumo de todas as fórmulas em uma nova planilha.
Lembretes sobre a fórmula da tabela dinâmica no Excel
- Podemos excluir, modificar todos os campos calculados.
- Não podemos usar fórmulas como VLOOKUP, SUMIF e muitas outras fórmulas de intervalo envolvidas nos campos calculados, ou seja, todas as fórmulas que requerem intervalo não podem ser usadas.
Artigos recomendados
Este foi um guia para a fórmula de tabela dinâmica no Excel. Aqui discutimos as etapas para usar a fórmula da tabela dinâmica no Excel, juntamente com exemplos e modelo do Excel para download. Você também pode examinar essas funções úteis no excel -
- Tutoriais sobre gráfico dinâmico no Excel
- Criando tabela dinâmica no Excel
- Tutorial VLOOKUP no Excel
- Banco de dados de criação do Excel