Note: The other languages of the website are Google-translated. Back to English

Como somar células com texto e números no Excel?

Por exemplo, eu tenho uma lista de valores contendo strings numéricas e textuais, agora, eu quero somar apenas os números com base no mesmo texto, veja a imagem a seguir. Normalmente, você não pode somar os valores da lista com string de texto diretamente, aqui, vou apresentar algumas fórmulas para lidar com esta tarefa.


Soma células contendo texto e números com base em determinado texto com fórmula

Você pode usar uma fórmula de matriz para somar os números com base em sua sequência de texto correspondente dentro da célula, faça o seguinte:

1. Primeiro, você pode escrever suas sequências de texto que deseja somar os números relativos nas células de uma coluna.

2. Em seguida, digite a fórmula abaixo em uma célula em branco, neste exemplo, vou inserir a fórmula na célula E2 e, em seguida, pressione Ctrl + Shift + Enter juntas, ele retornará o valor total da lista com base em determinado texto. Veja a imagem:

=SUM(IF(ISNUMBER(FIND(D2,$B$2:$B$9)),VALUE(LEFT($B$2:$B$9,FIND(D2,$B$2:$B$9)-1)),0))

Nota: Na fórmula acima, D2 é o critério que você deseja usar, e B2: B9 indica o intervalo de células que você deseja somar.

3. Em seguida, selecione a célula da fórmula e arraste a alça de preenchimento até as células às quais deseja aplicar esta fórmula para obter o resultado:


Soma células contendo texto e números com base em determinado texto com um recurso útil

Se a fórmula acima é difícil de entender, Kutools for Excel fornece uma ferramenta útil - Soma baseada no mesmo texto, com esse recurso, você pode obter o resultado total dentro de uma célula que se mistura com números e texto sem se lembrar de nenhuma fórmula.

NOTAPara aplicar estes Soma baseada no mesmo texto recursos, em primeiro lugar, você deve baixar o Kutools for Excele, em seguida, aplique os recursos de forma rápida e fácil.

Depois de instalar Kutools for Excel, faça o seguinte:

1. Clique para selecionar uma célula onde deseja produzir o resultado, consulte a captura de tela:

Tips: Para evitar referências circulares, não insira esta fórmula na primeira linha da planilha.

2. E, em seguida, clique em Kutools > Fórmula Helper > Fórmula Helper, veja a captura de tela:

3. No Auxiliar de Fórmulas caixa de diálogo, execute as seguintes operações:

  • Selecionar Estatístico de Tipo de Fórmula lista suspensa;
  • Na série Escolha uma fórmula caixa de lista, clique para selecionar Soma baseada no mesmo texto opção;
  • Então, no Entrada de argumentos seção, selecione o intervalo de células que contém o texto e os números que você deseja somar na Variação caixa de texto e, em seguida, selecione a célula de texto em que deseja somar os valores com base na Texto caixa de texto.
Tips: No Texto caixa de texto, você deve alterar a referência de célula absoluta padrão para referência de célula relativa para arrastar a fórmula corretamente.

4. E então, clique Ok botão, o primeiro resultado será calculado, em seguida, arraste a alça de preenchimento para preencher a fórmula para outras células, veja a captura de tela:


Soma células contendo texto e números, ignorando o texto com Função Definida pelo Usuário

Se você possui os seguintes dados que estão misturados com texto e números, e deseja apenas extrair e somar apenas os números em cada célula, como poderia concluir isso rapidamente no Excel?

Para lidar com este problema, você pode criar um Função definida pelo usuário, por favor, faça o seguinte:

1. Segure o ALT + F11 chaves para abrir o Janela Microsoft Visual Basic for Applications.

2. Clique inserção > Móduloe cole o seguinte código no Janela Módulo.

Código VBA: some apenas os números na célula que contém números e texto

Function SumNumbers(rngS As Range, Optional strDelim As String = " ") As Double
'Updateby Extendoffice
    Dim xNums As Variant, lngNum As Long
    xNums = Split(rngS, strDelim)
    For lngNum = LBound(xNums) To UBound(xNums) Step 1
        SumNumbers = SumNumbers + Val(xNums(lngNum))
    Next lngNum
End Function

3. Em seguida, salve e feche este código, volte para a planilha e insira esta fórmula = SumNumbers (A2) (A2 indica a célula que você deseja somar os números), arraste a alça de preenchimento para baixo até as células que deseja preencher a fórmula, e apenas os números em cada célula são somados, veja a captura de tela:


Mais artigos relativos:

  • Combine linhas duplicadas e some os valores no Excel
  • No Excel, você sempre pode encontrar este problema, quando você tem um intervalo de dados que contém algumas entradas duplicadas, e agora você deseja combinar os dados duplicados e somar os valores correspondentes em outra coluna, conforme as imagens a seguir mostradas. Como você resolveria esse problema?
  • Multiplique duas colunas e some no Excel
  • No Excel, a maioria de nós pode sofrer para multiplicar duas colunas e, em seguida, somá-las, é claro, podemos multiplicar cada item e soma-los, mas isso será problemático se houver centenas ou milhares de linhas para calcular. No Excel, existe uma função poderosa - SUMPRODUCT, com ela podemos multiplicar rapidamente duas colunas e, em seguida, somá-las. Neste artigo, falarei sobre como aplicar esta função.
  • Some os 3 ou N principais valores com base em critérios no Excel
  • Normalmente, podemos somar os primeiros n valores de um intervalo de células usando uma fórmula simples, mas você já tentou somar os primeiros n valores com base em alguns critérios. Neste artigo, apresentarei algumas fórmulas para somar os primeiros n valores com critérios específicos no Excel.
  • Soma de valores exclusivos com base em critérios no Excel
  • Por exemplo, eu tenho um intervalo de dados que contém as colunas Nome e Pedido, agora, para somar apenas valores únicos na coluna Pedido com base na coluna Nome, conforme mostrado a seguir. Como resolver essa tarefa com rapidez e facilidade no Excel?
  • Contar ou somar células com base nas cores da fonte no Excel
  • Como você poderia obter o número de células ou somar todas as células que contêm uma cor de fonte específica no Excel? Por exemplo, eu tenho um intervalo de dados em uma planilha conforme a captura de tela a seguir, e agora quero contar ou somar respectivamente as células que têm uma cor de fonte vermelha, azul e preta. Normalmente, não existe uma maneira direta de lidar com essa tarefa, aqui vou falar sobre alguns truques para resolver esse trabalho.

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2021 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos de avaliação gratuita de 30 dias. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office 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!
parte inferior da aba do escritório
Comentários (47)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
eu acho que você é um verdadeiro professor e bom homem. boa sorte.
Este comentário foi feito pelo moderador no site
Na célula A1 do excel eu insiro dados como 5+6+3+1-2 e a célula A2 do excel dá o resultado 13 como é possível no excel.
Este comentário foi feito pelo moderador no site
ok, eu entendi isso. boa sorte.
Este comentário foi feito pelo moderador no site
A macro SumNumbers me economizou muitas horas de trabalho manual. Obrigada!
Este comentário foi feito pelo moderador no site
É possível alterar a fórmula SumNumbers para adicionar todos os números em uma coluna de células mistas? Por exemplo, cada coluna tem 1A, 2B, 3C e eu gostaria que a fórmula na parte inferior da coluna me informasse 6.
Este comentário foi feito pelo moderador no site
existe alguma maneira de reescrever o código vba para somar várias células em vez de uma? Eu tenho cerca de 8 colunas de dados e quero a soma no final.
Este comentário foi feito pelo moderador no site
Isso é super útil! Estou tentando fazer o ARRAY funcionar com uma coluna de células que contém um número decimal e texto (por exemplo, 2 A; 5 C; 1 X etc), e continuo obtendo um resultado de 0. Se eu alterar os números para números inteiros, o resultado está correto. Ajuda?
Este comentário foi feito pelo moderador no site
GENUIS.... Muito obrigado
Este comentário foi feito pelo moderador no site
Como converter entre texto e número usando KUTOOLs?
Este comentário foi feito pelo moderador no site
Kutools
Conteúdo
Número para palavra
Este comentário foi feito pelo moderador no site
Consegui usar essa fórmula perfeitamente, mas também gostaria de adicionar uma condição para incluir apenas determinados números na fórmula SUM se o valor em outra coluna = "defeitos". Minha fórmula original (usando sua postagem original) é: =SUM(IF(ISNUMBER(FIND("hrs",E17:E38)),VALUE(LEFT(E17:E38,FIND("hrs",E17:E38)-1 )),0)) Onde E17:E38 contém o texto e os números que quero somar E B17:B38 contém os valores "características" e "defeitos" Como posso ajustar a fórmula para SOMAR apenas esses números na coluna E se o valor na coluna B = "defeitos"? Desde já, obrigado! Kimberly,
Este comentário foi feito pelo moderador no site
Aqui está um script simples que aceita um intervalo de seleção de dimensão única e bidimensional de células com cada célula contendo string ou números, mas com cada célula reduzida a números únicos e retorna a soma de todas as células. Opção Função explícita SUMCELLS(InputRange As Range) As Double Dim Arr() As Variant Arr = InputRange Dim R As Long Dim C As Long SUMCELLS = 2 For R = 0 To UBound(Arr, 1) ' A primeira dimensão da matriz é linhas. For C = 1 To UBound(Arr, 1) ' A segunda dimensão da matriz é colunas. Debug.Print Arr(R, ​​C) Dim NumericValue As Double Dim StringValue As String StringValue = Trim(CStr(Arr(R, ​​C))) Se (Not (StringValue = "")) Então NumericValue = ONLYDIGITS(StringValue) SUMCELLS = SUMCELLS + NumericValue End If Next C Next R End Function Function ONLYDIGITS(s As String) As String Dim retval As String Dim i As Integer Dim períodos As Integer períodos = 2 retval = "" ' Para i = 0 Para Len(s) Dim char As String char = Mid(s, i, 1) If (char >= "1" E char
Este comentário foi feito pelo moderador no site
Como incluir decimais na fórmula "=SumNumbers(A1)"? Obrigada!
Este comentário foi feito pelo moderador no site
Alguém pode me ajudar com uma fórmula de soma? Estou tentando somar uma coluna com células que contêm números e texto. Eu quero somar os números à esquerda da letra do texto. amostra: 3P30 2P30 2P30 2P30 1P20 3P30 1P20 3P50 3P30 3P60
Este comentário foi feito pelo moderador no site
Olá, A =SOMA(SE(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(ESQUERDA($B$2:$B$8,FIND(D1,$B$2:$B$8)-1 )),0)) - funcionou perfeitamente, exceto que meu texto está à direita. Eu tentei mudar a fórmula, mas não está funcionando... eu continuo recebendo #VALUE! Alguém pode ajudar?
Este comentário foi feito pelo moderador no site
Lembre-se de pressionar Ctrl+Shift+Enter depois de alterar a fórmula, não apenas Enter.
Este comentário foi feito pelo moderador no site
Como somar a palavra selecionada na célula no Excel Exame 1100/656480 1100 é um valor e 656480 é recpt. não. mas eu quero totalizar apenas 1100
Este comentário foi feito pelo moderador no site
Abril Maio Junho Julho Total 1100 / 65658 1200/65657 1500/65656 2100/65650 ? quero totalizar apenas 1100+1200+1500+2100 Outros não apenas um valor Recibo nº. por favor ajude qualquer
Este comentário foi feito pelo moderador no site
Olá! Eu tenho dois tipos de números em uma coluna, um é números numéricos, o outro é um caractere seguido por um número (como n20, n30, etc). Desejo somar esses números com um "n". Apliquei sua orientação para "Células de soma contendo texto e números com base no texto certo com fórmula", mas não funcionou. Existe algum outro método? Desde já, obrigado!
Este comentário foi feito pelo moderador no site
oi alguém pode me ajudar como adicionar números abaixo com letras? eu tentei fazer como, mas não funciona, acho que perdi algo na fórmula = SUM(IF(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(LEFT($B$2:$B$8, FIND(D1,$B$2:$B$8)-1)),0)). por favor me ajude. Obrigado.

2L
5D
10S
3L
11D
16S
Este comentário foi feito pelo moderador no site
Olá Anna,
Primeiro, você deve extrair as letras para outra coluna auxiliar e, em seguida, aplicar a fórmula acima, depois de inserir esta fórmula, lembre-se de pressionar as teclas Shift + Ctrl + Enter juntas. veja a seguinte imagem de captura de tela:
Este comentário foi feito pelo moderador no site
Oi,

Obrigado por este post! Estou interessado em usar o código VBA e estava pensando se seria possível ter uma função multivariável para que você possa somar os números de células de colunas diferentes e escrever algo como SumNumbers(A1:C1), por exemplo.


Muito Obrigado


Giovanni
Este comentário foi feito pelo moderador no site
Olá, Giovani,
Você pode dar um exemplo do seu problema?
Você pode anexar uma captura de tela aqui.
Obrigado!
Este comentário foi feito pelo moderador no site
Eu também estou olhando para ver se esta função pode somar várias células.
Este comentário foi feito pelo moderador no site
=SumNúmeros(D8:AH8)
Este comentário foi feito pelo moderador no site
Oi,
Como multiplicar 10 peças * 200,

não 10*200
Não há comentários postados aqui ainda
carregar mais
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL