Ir para o conteúdo principal

Como somar apenas células filtradas ou visíveis no Excel?

Talvez seja fácil para você somar uma coluna de números no Excel, mas às vezes você precisa filtrar ou ocultar alguns dados para atender aos seus critérios. Depois de ocultar ou filtrar, agora você só deseja adicionar apenas os valores filtrados ou visíveis. Se você aplicar a função Soma no Excel, todos os valores incluindo os dados ocultos serão adicionados, neste caso, como você poderia somar apenas os valores das células filtradas ou visíveis no Excel?


Some apenas os valores filtrados ou visíveis das células com a fórmula

Com esta SUBTOTAL função que ignora linhas que foram excluídas por um filtro, você pode facilmente adicionar apenas as células visíveis. Você pode fazer o seguinte:

Suponha que você tenha uma variedade de dados e eles tenham sido filtrados conforme necessário, consulte a captura de tela:

1. Em uma célula em branco, C13 por exemplo, insira esta fórmula: = Subtotal (109, C2: C12) (109 indica quando você soma os números, os valores ocultos serão ignorados; C2: C12 é o intervalo que você somará ignorando as linhas filtradas.) e pressione o botão Entrar chave.

Observação: Esta fórmula também pode ajudá-lo a somar apenas as células visíveis se houver linhas ocultas em sua planilha. No entanto, essa fórmula não pode somar ignorando células em colunas ocultas.

Soma / contagem / média de células visíveis apenas em um intervalo especificado, ignorando células / linhas / colunas ocultas ou filtradas

A função normalmente SOMA/Contagem/Média contará todas as células no intervalo especificado, caso as células estejam ocultas/filtradas ou não. Enquanto a função Subtotal só pode somar/contar/média ignorando as linhas ocultas. No entanto, Kutools para Excel RESUMIDO / CONT.VISÍVEL / MÉDIA VISÍVEL funções irão calcular facilmente o intervalo especificado, ignorando quaisquer células, linhas ou colunas ocultas.


ad soma contagem média de células visíveis apenas

Soma apenas os valores filtrados ou visíveis das células com a função definida pelo usuário

Se você estiver interessado no código a seguir, ele também pode ajudá-lo a somar apenas as células visíveis.

1. Segure o ALT + F11 chaves, e abre o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o código a seguir na janela Módulo.

Function SumVisible(WorkRng As Range) As Double
'Update 20130907
Dim rng As Range
Dim total As Double
For Each rng In WorkRng
    If rng.Rows.Hidden = False And rng.Columns.Hidden = False Then
        total = total + rng.Value
    End If
Next
SumVisible = total
End Function

3. Salve este código e insira a fórmula = SumVisible (C2: C12) em uma célula em branco. Veja a imagem:

4. Então aperte Entrar chave e você obterá o resultado que deseja.


Soma/Contagem/Média apenas células filtradas ou visíveis com Kutools para Excel

Se você tiver o Kutools para Excel instalado, poderá calcular rapidamente a soma/contagem/média apenas de células visíveis ou filtradas no Excel com facilidade.

Kutools for Excel - Turbine o Excel com mais de 300 ferramentas essenciais. Desfrute de um teste GRATUITO de 30 dias com todos os recursos, sem necessidade de cartão de crédito! Get It Now

Por exemplo, se você deseja somar apenas as células visíveis, selecione a célula em que colocará o resultado da soma, digite a fórmula = SUMVISÍVEL (C3: C12) (C3: C13 é o intervalo em que você somará apenas as células visíveis) nele e pressione o botão Entrar chave.

E então o resultado da soma é calculado ignorando todas as células ocultas. Veja a imagem:

Para contar apenas células visíveis, aplique esta fórmula = CONTÁVEL TV (C3: C12); Para calcular a média apenas das células visíveis, aplique esta fórmula = AVERAGEVISIBLE (C3: C12).

Observação: Se você não consegue se lembrar das fórmulas com exatidão, pode seguir as etapas abaixo para somar / contar / fazer a média apenas das células visíveis com facilidade:

1. Selecione a célula em que colocará o resultado da soma e clique em Kutools > Funções > Estatística e Matemática > RESUMIDO (ou MÉDIA VISÍVEL, CONT.VISÍVEL como você precisa). Veja a imagem:

2. Na caixa de diálogo de abertura dos Argumentos da Função, especifique o intervalo em que você fará a soma ignorando as células ocultas e clique no botão OK botão. Veja a imagem:

Kutools for Excel - Turbine o Excel com mais de 300 ferramentas essenciais. Desfrute de um teste GRATUITO de 30 dias com todos os recursos, sem necessidade de cartão de crédito! Get It Now

E então o resultado da soma é calculado ignorando todas as células ocultas.


Demonstração: Soma / Contagem / Média apenas células filtradas ou visíveis


Kutools for Excel: Mais de 300 ferramentas úteis ao seu alcance! Comece hoje mesmo seu teste gratuito de 30 dias sem limitações de recursos. Baixe agora!

Soma / conta facilmente apenas células filtradas / visíveis, excluindo linhas ocultas no Excel

Ao somar/contar células filtradas no Excel, a função SUM ou a função Contar não ignorará as células ocultas. Se as linhas ocultas/filtradas forem removidas, só poderemos somar ou contar facilmente as células visíveis. Você pode experimentar o Kutools para Excel Excluir linhas e colunas ocultas (visíveis) utilidade para resolvê-lo.


anúncio excluir linhas ocultas colunas 3

Artigos relacionados:

Melhores ferramentas de produtividade de escritório

🤖 Assistente de IA do Kutools: Revolucionar a análise de dados com base em: Execução Inteligente   |  Gerar Código  |  Crie fórmulas personalizadas  |  Analise dados e gere gráficos  |  Invocar funções do Kutools...
Recursos mais comuns: Encontre, destaque ou identifique duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios    VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  |  Compare intervalos e colunas ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula    Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Palavras, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   ... e mais

Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo.  Clique aqui para obter o recurso que você mais precisa...

Descrição


Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
Comments (26)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Bonjour,
Moi j'ai un problème comparable mais différent, en gros quand je filtre mes colonnes et que je veux copier la même valeur dans mes lignes visibles, toute les lignes qui sont entre mes lignes visibles sont elles aussi modifiées.
Comment faire pour que seule mes lignes visibles soient modifiés.
This comment was minimized by the moderator on the site
Hello, Loux

Do you mean to copy and paste cell vaues to visible cells only? If so, maybe the below articles can help you:
https://www.extendoffice.com/documents/excel/2331-excel-paste-data-into-filtered-list.html
https://www.extendoffice.com/documents/excel/2617-excel-paste-to-visible-filtered-cells.html

Please try it, thank you!
This comment was minimized by the moderator on the site
Hola,

No me resulta sumar el rango de una fila considerando sólo las columnas visibles.

Alguna formula o alguna macro?

Gracias!!!
This comment was minimized by the moderator on the site
Hello, Cristobal,
Sorry, I can't understand your problem clearly, could you explain your problem in English?
Or you can insert a screenshot or a file to describe your problem.
Thank you!
This comment was minimized by the moderator on the site
Hola,

Quiero sumar dentro de un rango de fila que al ocultar columnas sume sólo las visibles.
No he logrado hacerlo, alguna fórmula? o tendría que hacerse con una marcro?

Muchas gracias!!!
This comment was minimized by the moderator on the site
how to skip hidden cells from excel formulae in filtered sheet....

yellow coloured cells are involving hidden cells due to filter....
This comment was minimized by the moderator on the site
Hi,
What kinds of calculation do you need to do? Kutools for Excel supports three functions to count/sum/average ignoring all hidden cells.
This comment was minimized by the moderator on the site
or like this



column a column d

100 10

90 10

80 10



where 90=100-10, 80=90-10, and so on.........

dragging the formula includes hidden cells in formula
This comment was minimized by the moderator on the site
When i am applying say G3-K2 Formula in filtered excel sheet & dragging the formula, it includes hidden cells

for example



Validity Lifting Qty
27054.59 123.41
26931.18 330.98
26600.20 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06


now value in validity comes after get subtracted by lifted qty (like 2nd value 26931.18=27054.59-123.41)

3rd value in validity=2nd value in validity-2nd value in lifted qty. and so on.....

now this sheet is filtered & when i drag the formulae in validity column, it includes hidden cells, due to filter.

which gives me wrong result
This comment was minimized by the moderator on the site
Validity Lifting Qty
27054.59 123.41
26931.18 330.98
26600.20 (26600.2=26931.18-330.98) 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06
This comment was minimized by the moderator on the site
When i am applying say G3-K2 Formula in filtered excel sheet & dragging the formula, it includes hidden cells

for example



Validity Lifting Qty
27054.59 123.41
26931.18 330.98
26600.20 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06


now value in validity comes after get subtracted by lifted qty (like 2nd value 26931.18=27054.59-123.41)

3rd value in validity=2nd value in validity-2nd value in lifted qty. and so on.....

now this sheet is filtered & when i drag the formulae in validity column, it includes hidden cells, due to filter.

which gives me wrong result
This comment was minimized by the moderator on the site
Hi,
The normal =G3-K2 will not ignore any hidden cells/rows/column by dragging to copy. I am sorry I can not figure out a proper formula for your. All formulas or methods talked in this article is just about sum/count/average ignoring hidden cells.
This comment was minimized by the moderator on the site
how i can skip hidden cells from excel formulae in filtered sheet
This comment was minimized by the moderator on the site
Im trying to use the AVERAGEVISIBLE function to average the 12 largest values in a column, this works in the normal AVERAGE function as =AVERAGE(LARGE(E971:E1540,{1,2,3,4,5,6,7,8,9,10,11,12})) however when i try to use the average visible function it returns #VALUE!, any ideas?
This comment was minimized by the moderator on the site
This is awesome! Thanks so much.
This comment was minimized by the moderator on the site
Sorry I messed up the posts security code at bottom and seems like my question may have been deleted as it asked me to try another one. I can get the VBA code for SUMVISIBLE to work well if my data is vertical and I hide rows. However it doesn't if my data runs horizontal and I want to hide columns. Is there a way to program this? Thanks!
This comment was minimized by the moderator on the site
I am able to get your VBA for =SUMVISIBLE above to work good. However just if my data runs vertically and I am hiding rows. Is there a way to program it so you can have your data run horizontally and it still work when you hide columns? THANKS!
This comment was minimized by the moderator on the site
The solution worked for me. :D
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations