Como calcular a média de células não adjacentes, excluindo os zeros, no Excel?
Ao trabalhar com dados no Excel, é comum deparar-se com situações em que precisa calcular a média de um conjunto de números não adjacentes e, ao mesmo tempo, excluir os valores zero — que muitas vezes representam dados em falta ou entradas anuladas. Por exemplo, numa folha de vendas ou de inventário, certos artigos podem registar stock zero ou ausência de vendas em determinadas datas; nestes casos, o cálculo simples da média pode não refletir com precisão a tendência real dos dados, já que os zeros tendem a distorcer o resultado. Excluir esses valores permite-lhe focar-se apenas nos pontos de dados ativos, obtendo assim uma análise mais fiel e útil.
Imagine que tem uma tabela de frutas como a apresentada na imagem abaixo e pretende calcular a média de todas as células da coluna Quantidade, excluindo os zeros. Os métodos seguintes orientam-no através de várias abordagens práticas para alcançar este objetivo no Excel, oferecendo flexibilidade consoante os seus dados estejam dispersos por células não adjacentes, distribuídos por múltiplas linhas ou colunas, ou quando preferir utilizar funcionalidades incorporadas do Excel ou fórmulas simples.
- Média de células não adjacentes excluindo zeros com fórmula
- Média de células não adjacentes excluindo zeros com uma macro VBA
- Média de células não adjacentes excluindo zeros utilizando a função incorporada do Excel
Média de células não adjacentes excluindo zeros com fórmula
Este método utiliza uma fórmula matricial para calcular rapidamente a média de células não adjacentes — por exemplo, cada segunda célula numa linha ou coluna — excluindo quaisquer zeros presentes nas células selecionadas. Aplica-se sempre que as suas células-alvo seguem um padrão de intervalo fixo, revelando-se particularmente eficiente com dados periódicos, como valores de dias alternados, ou com informações distribuídas em intervalos regulares, mesmo quando não são diretamente contíguas.
Limitações: Esta abordagem é ideal quando as células não adjacentes seguem um intervalo regular. Para seleções arbitrárias ou manuais, consulte o método baseado em fórmula abaixo.
1. Selecione uma célula vazia onde deseja que o resultado apareça e introduza a fórmula matricial:
=AVERAGE(IF(MOD(COLUMN(C2:G2)-COLUMN(C2),2)=0,IF(C2:G2,C2:G2)))Em seguida, prima simultaneamente as teclas Ctrl + Shift + Enter(para versões mais antigas do Excel; no Excel 365 ou 2019, basta premir)Enter, uma vez que suportam matrizes dinâmicas).
Nota: Na fórmula, C2 e G2 representam a primeira e a última células do seu intervalo-alvo. O número «2» indica o espaçamento entre colunas; ajuste este valor caso as suas células não adjacentes estejam separadas por um número diferente de colunas. Adapte o intervalo (C2:G2) e o valor do espaçamento (2) de acordo com a disposição dos seus dados.
Dicas:
- Certifique-se de que o intervalo selecionado inclui todas as células não adjacentes cuja média pretende calcular.
- Se a sua seleção de dados não seguir intervalos regulares, utilize a seguinte solução baseada em fórmula para células arbitrárias.
- Esta fórmula ignora naturalmente células vazias e valores zero, oferecendo-lhe a média verdadeira dos pontos de dados significativos.
- Para evitar erros, certifique-se de que as referências das suas células estão corretas e de que premiu a combinação de teclas adequada para fórmulas matriciais nas versões mais antigas do Excel.
Média de células não adjacentes excluindo zeros com uma macro VBA
Se precisar de calcular rapidamente a média de um grupo de células não adjacentes, omitindo os zeros — especialmente quando a sua seleção não segue um padrão fixo (por exemplo, ao escolher manualmente um conjunto específico de células em diferentes linhas e colunas) —, uma macro VBA oferece uma solução flexível e poderosa. O código percorre as células selecionadas e calcula a média, excluindo automaticamente todas as que contêm zero.
Este método é excelente para:
- Processamento rápido de grandes volumes ou seleções irregulares de dados.
- Guarde a sua lógica personalizada para reutilização em conjuntos de dados semelhantes.
- Supere as limitações das fórmulas tradicionais de folha de cálculo em seleções complexas.
1. Clique em Programador > Visual Basic; na janela que se abre, clique em Inserir > Módulo e cole o seguinte código no módulo:
Sub AverageNonAdjacentExcludeZero()
Dim rng As Range
Dim cell As Range
Dim SumVal As Double
Dim CountVal As Long
Dim SelectedRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set SelectedRng = Application.Selection
Set SelectedRng = Application.InputBox("Select non-adjacent cells to average (exclude zeros)", xTitleId, Type:=8)
On Error Resume Next
SumVal = 0
CountVal = 0
For Each cell In SelectedRng
If IsNumeric(cell.Value) And cell.Value <> 0 Then
SumVal = SumVal + cell.Value
CountVal = CountVal + 1
End If
Next
If CountVal > 0 Then
MsgBox "Average (excluding zeros): " & SumVal / CountVal, vbInformation, xTitleId
Else
MsgBox "No non-zero numeric cells selected.", vbExclamation, xTitleId
End If
End Sub2. Clique no botão 
Executar ou prima F5no editor do VBA. Aparecerá uma caixa de diálogo a solicitar que selecione as suas células. Utilize o rato em conjunto com a tecla Ctrlpara clicar e selecionar as células não adjacentes cuja média pretende calcular. Clique em OKe a macro apresentará o resultado, excluindo quaisquer zeros.
Média de células não adjacentes excluindo zeros utilizando a função incorporada do Excel
O Excel também oferece métodos incorporados para executar esta tarefa com pouco ou nenhum trabalho de fórmulas, ideais para cálculos rápidos e pontuais ou inspeções visuais.
Utilize a Barra de Status:
- Mantenha premida a tecla Ctrl e utilize o rato para selecionar manualmente cada célula não adjacente que pretende incluir na média, ignorando quaisquer células com valores zero.
- Após a seleção, observe a Barra de Estado do Excel no canto inferior direito da janela — o valor da Média será apresentado automaticamente se estiverem selecionadas mais do que uma célula.
- Este método é ideal para verificações visuais rápidas e conjuntos de dados pequenos, mas não produz um resultado diretamente numa célula da folha de cálculo.
Ao escolher um método adequado, tenha em conta o tamanho dos seus dados, o grau de dispersão das células selecionadas e se precisa de um resultado com uma fórmula reutilizável ou apenas de uma verificação visual rápida. Se encontrar erros (como)#DIV/0!), certifique-se de que existe pelo menos um valor diferente de zero nas suas seleções. Ao utilizar VBA, guarde sempre a sua pasta de trabalho antes de executar novas macros para evitar perdas acidentais de dados.
Selecionar rapidamente linhas ou colunas com intervalo (não adjacentes) em Intervalo limitado no Excel:
A funcionalidade Kutools para Excel Selecionar Linhas/Colunas em Intervalos permite-lhe selecionar facilmente linhas ou colunas num intervalo limitado no Excel, tal como ilustrado na imagem abaixo. Esta funcionalidade é especialmente útil antes de aplicar fórmulas ou macros, ajudando-o a selecionar exatamente as células que pretende processar.
Kutools para Excel– Potencie o Excel com mais de 300 ferramentas essenciais, tornando o seu trabalho mais rápido e fácil, e aproveite as funcionalidades de IA para um processamento de dados mais inteligente e uma maior produtividade.Obtenha Já
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