Como calcular a média ponderada numa Tabela Dinâmica do Excel?
Calcular a média ponderada para dados no Excel é uma necessidade frequente, especialmente quando os seus pontos de dados contribuem de forma desigual para o resultado final. Para intervalos simples, as funções SOMARPRODUTO e SOMA oferecem uma solução rápida e eficaz. Contudo, ao trabalhar com Tabelas Dinâmicas, poderá notar que os campos calculados não suportam nativamente estas funções — o que pode complicar os cálculos se pretender obter médias ponderadas diretamente na Tabela Dinâmica. Compreender estas limitações e dominar abordagens alternativas permite-lhe resumir os seus dados de forma eficiente em diversos cenários. Este artigo explora várias formas de calcular uma média ponderada numa Tabela Dinâmica, abrangendo soluções clássicas e funcionalidades mais recentes disponíveis no Excel.
Calcular a média ponderada numa Tabela Dinâmica do Excel
Código VBA – Automatizar o cálculo da média ponderada em Tabela Dinâmica
Power Pivot (Modelo de Dados) – Utilizar DAX para calcular a média ponderada em Tabela Dinâmica
Calcular a média ponderada numa Tabela Dinâmica do Excel
Imagine que tem uma tabela com dados de vendas de várias frutas, com colunas como Fruta, Peso e Preço por unidade, e que criou uma Tabela Dinâmica para resumir estes valores, conforme ilustrado abaixo.
Quando precisar de calcular o preço médio ponderado para cada fruta — ou seja, quando quiser refletir com precisão a contribuição de cada ponto de dados com base no seu peso — o Tabela Dinâmica não permite utilizar diretamente a função SOMARPRODUTO nem funções avançadas semelhantes num Campo Calculado. A solução manual seguinte contorna esta limitação: basta adicionar uma coluna auxiliar aos seus Dados de Origem e obter a média ponderada através das opções incorporadas do Tabela Dinâmica.
1. Comece por adicionar uma coluna auxiliar com o rótulo Montante na sua Dados de Origem.
Insira uma nova coluna em branco, dê-lhe o título Montante e, na primeira linha (por exemplo, C2), introduza a fórmula =D2*E2(em que)D2 é o peso e E2 é o preço por unidade — adapte conforme necessário aos cabeçalhos da sua tabela). Em seguida, arraste a alça de preenchimento para baixo para aplicar a fórmula a todas as linhas. Este passo multiplica o peso de cada item pelo respetivo preço, obtendo assim o preço total ponderado desse item. Veja a imagem:
Dicas:
- Certifique-se de que a sua tabela de origem não contém células mescladas, pois isso pode causar erros nas fórmulas.
- Se estiver a trabalhar com grandes conjuntos de dados, verifique se a fórmula foi aplicada a todas as linhas relevantes.
- Caso as atribuições de colunas mudem, atualize a fórmula em conformidade.
2. De seguida, atualize a Tabela Dinâmica para refletir a coluna auxiliar adicionada. Selecione qualquer célula dentro da Tabela Dinâmica, o que fará aparecer o separador contextual Ferramentas de Tabela Dinâmica. Clique em Analisar(ou)Opções, consoante a versão do Excel) > Atualizar. Este passo garante que o novo campo Montante apareça na Lista de Campos da Tabela Dinâmica.
3. Para adicionar um campo calculado de média ponderada, vá a Analisar > Campos, Itens e Conjuntos > Campo Calculado. Isto abre a caixa de diálogo Inserir Campo Calculado, onde pode configurar o seu cálculo personalizado.
Nota: O Campo Calculado utiliza campos já definidos nos seus dados. Certifique-se de que todas as colunas necessárias foram adicionadas e atualizadas antes deste passo.
4. Na caixa de diálogo Inserir Campo Calculado, escreva Média Ponderada (ou outro nome distinto) na caixa Nome. No campo Fórmula, introduza =Montante/Peso. Certifique-se de utilizar os nomes exatos da sua Dados de Origem — estes são sensíveis a maiúsculas e minúsculas e devem corresponder exatamente. Em seguida, clique em OK para adicionar o campo de média ponderada.
Resolução de problemas:
- Se vir erros #DIV/0!, confirme que os seus valores de peso não contêm zeros.
- Se o campo calculado não aparecer, certifique-se de que a ortografia e o uso de maiúsculas e minúsculas no nome da condição estão corretos.
O preço médio ponderado para cada tipo de fruta aparecerá agora nas linhas de subtotal da sua Tabela Dinâmica. O resultado garante que o cálculo do preço médio reflita verdadeiramente o impacto do peso de cada entrada.
Vantagens: Compatível com versões antigas do Excel; não requer extras nem funcionalidades avançadas.
Desvantagens: Requer modificação dos dados de origem através de colunas auxiliares; o recálculo pode ser menos dinâmico se os dados forem atualizados.
Dica prática: Para relatórios recorrentes, considere manter a fórmula da coluna auxiliar dinâmica ou automatizar a atualização com uma macro.
Power Pivot (Modelo de Dados) – Utilizar DAX para calcular a média ponderada em Tabela Dinâmica
Com versões modernas do Excel, o extra Power Pivot (também conhecido como Modelo de Dados) disponibiliza novas opções de cálculo através de fórmulas DAX (Análise de Dados Expressions). Isto permite-lhe calcular médias ponderadas diretamente na Tabela Dinâmica sem criar colunas auxiliares adicionais nos seus dados subjacentes.
Cenários aplicáveis: Ideal para trabalhar com grandes conjuntos de dados ou tabelas ligadas, especialmente quando pretende que os cálculos sejam atualizados automaticamente à medida que os seus dados mudam. Esta abordagem revela-se particularmente útil em análises empresariais e painéis de controlo, onde é preferível manter uma tabela de origem limpa.
Instruções:
- Ativar o extra Power PivotVá a Ficheiro > Opções > Extras. No menu pendente Gerir, selecione Extras COM, clique em Ir e marque a opção Power Pivot.
- Adicionar dados ao Power PivotSelecione a sua tabela na folha de cálculo e, em seguida, clique em Power Pivot>Gerirpara abrir a janela do Power Pivot.
- Criar Tabela Dinâmica a partir do Power PivotNa janela do Power Pivot, vá a Base>Tabela Dinâmica.
Depois escolha onde inseri-la (por exemplo,)Planilha existente) e clique em OK.
- Construa uma Tabela Dinâmica e adicione uma medidaNa lista de campos da Tabela Dinâmica recém-criada, arraste os campos para as áreas apropriadas. Em seguida, clique com o botão direito do rato no nome da tabela e selecione Adicionar Medida.
- Definir a medidaNa caixa de diálogo Medida:
- Dê um nome à medida (por exemplo, Preço Médio Ponderado).
- Introduza a seguinte expressão DAX para a média ponderada.
=SUMX(Table1, Table1[Weight] * Table1[Price]) / SUM(Table1[Weight])(Substitua)Table1, [Weight] e [Price] pelos nomes reais da sua tabela e dos seus Nome da condição.) - Clique em OKpara adicioná-la.
- Utilizar a medida na Tabela DinâmicaA medida recentemente adicionada aparecerá na lista de campos e poderá ser arrastada para a área Valorestal como qualquer outro campo.
Dicas e resolução de problemas:
– As fórmulas DAX não diferenciam maiúsculas de minúsculas, mas os nomes de campos e tabelas devem corresponder exatamente aos do seu modelo.
– Ao alterar os dados subjacentes, a medida na sua Tabela Dinâmica é atualizada automaticamente.
– Se obtiver resultados em branco ou inesperados, verifique se existem valores nulos ou em falta nos pesos e confirme que o seu modelo de dados está corretamente atualizado.
Vantagens: Não são necessárias alterações nos Dados de Origem; os cálculos atualizam-se instantaneamente sempre que os dados mudam e permitem sumarizações avançadas.
Desvantagens: O Power Pivot não está disponível em todas as edições do Excel e pode exigir uma configuração inicial; utilizadores menos familiarizados com DAX poderão enfrentar uma curva de aprendizagem.
Desbloqueie a Magia do Excel com KUTOOLS AI
- Execução Inteligente: Realize operações em células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
- fórmulas personalizadas: Crie fórmulas personalizadas para simplificar os seus fluxos de trabalho.
- Programação VBA: Escreva e implemente código VBA com facilidade.
- Interpretação de Fórmulas: Compreenda facilmente fórmulas complexas.
- Tradução de Texto: Elimine barreiras linguísticas nas suas folhas de cálculo.
Artigos relacionados:
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos