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

Soma se as células contêm texto específico em outra coluna

Este tutorial mostrará como somar valores se as células contiverem texto específico ou parcial em outra coluna. Tome o intervalo de dados abaixo como exemplo, para obter as quantidades totais dos produtos que contêm o texto “T-Shirt”, tanto a função SUMIF quanto a função SUMPRODUCT podem lidar com esta tarefa no Excel.


Soma os valores se a célula contiver texto específico ou parcial com a função SUMIF

Para somar valores se uma célula contiver texto específico em outra coluna, você pode usar a função SOMASE com um caractere curinga (*), as sintaxes genéricas são:

Fórmula genérica com texto codificado:

=SUMIF(range,"*text*",sum_range)
  • range: O intervalo de dados que você deseja avaliar usando os critérios;
  • *text*: Os critérios nos quais você deseja somar os valores. Aqui, o caractere curinga * é usado para encontrar qualquer número de caracteres, para combinar todos os itens que contêm um texto específico, coloque o texto entre os dois * caracteres. (Observe que você deve colocar o texto e o curinga entre aspas duplas.)
  • sum_range: O intervalo de células com valores numéricos correspondentes que você deseja somar.

Fórmula genérica com referência de célula:

=SUMIF(range,"*"&cell&"*",sum_range)
  • range: O intervalo de dados que você deseja avaliar usando os critérios;
  • "*"&cell&"*": Os critérios nos quais você deseja somar os valores;
    • *: O curinga que encontra qualquer número de caracteres.
    • célula: A célula contém o texto específico a ser procurado.
    • &: Este operador de concatenação (&) é usado para unir uma referência de célula com os asteriscos.
  • sum_range: O intervalo de células com valores numéricos correspondentes que você deseja somar.

Depois de conhecer o funcionamento básico da função, por favor, use qualquer uma das seguintes fórmulas que você precisa e, em seguida, pressione Entrar chave para obter o resultado:

=SUMIF($A$2:$A$12,"*T-shirt*",$B$2:$B$12)                     (Type the criteria manually)
=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12)                 
 (Use a cell reference)

Nota: Esta função SOMASE não faz distinção entre maiúsculas e minúsculas.


Soma os valores se a célula contiver texto específico ou parcial com a função SUMPRODUCT

A função SUMPRODUCT também pode ajudá-lo a terminar este trabalho sem usar nenhum caractere curinga. Aqui, você deve envolver as funções ISNUMBER e SEARCH dentro de uma função SUMPRODUCT, a sintaxe genérica é:

=SUMPRODUCT(sum_range *(ISNUMBER(SEARCH(criteria,range))))
  • sum_range: O intervalo de células com valores numéricos correspondentes que você deseja somar;
  • criteria: Os critérios nos quais você deseja somar os valores. Pode ser uma referência de célula ou um texto específico que você definiu;
  • range: O intervalo de dados que você deseja avaliar usando os critérios;

Use qualquer uma das fórmulas abaixo em uma célula em branco e pressione Entrar chave para retornar o resultado:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))))          (Type the criteria manually)
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12))))                   
(Use a cell reference)


Explicação desta fórmula:

= SUMPRODUCT ($ B $ 2: $ B $ 12 * (ISNUMBER (SEARCH ("Camiseta", $ A $ 2: $ A $ 12))))

  • PESQUISA ("Camiseta", $ A $ 2: $ A $ 12): A função SEARCH retorna a localização do texto específico “T-Shirt” do intervalo de dados A2: A12, então, você obterá uma matriz como esta: {5; #VALUE!; # VALUE!; 7; #VALUE! ; 7; #VALOR!; # VALOR!; # VALOR!; # VALOR!; 7}.
  • ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): Esta função ISNUMBER é usada para testar os valores numéricos e retorna uma nova matriz: {TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE}.
  • $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: Para multiplicar essas duas matrizes, a operação matemática força os valores TRUE e FALSE para 1s e 0s. Portanto, a multiplicação das duas matrizes será assim: {347; 428; 398; 430; 228; 379; 412; 461; 316; 420; 449} * {1; 0; 0; 1; 0; 1; 0; 0; 0; 0; 1} = {347; 0; 0; 430; 0; 379; 0; 0; 0; 0; 449}.
  • SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}): Finalmente, a função SUMPRODUCT soma todos os valores na matriz para obter o resultado: 1605.

Função relativa usada:

  • SUMIF:
  • A função SUMIF pode ajudar a somar células com base em um critério.
  • SUMPRODUCT:
  • A função SUMPRODUCT pode ser usada para multiplicar duas ou mais colunas ou matrizes e obter a soma dos produtos.
  • NÚMERO:
  • A função ISNUMBER do Excel retorna TRUE quando uma célula contém um número e FALSE se não.
  • Pesquisar:
  • A função SEARCH pode ajudá-lo a encontrar a posição de um caractere específico ou substring da string de texto fornecida

Mais artigos:

  • Soma os valores de N menores ou inferiores
  • No Excel, é fácil somar um intervalo de células usando a função SUM. Às vezes, você pode precisar somar os 3, 5 ou n números menores ou mais baixos em um intervalo de dados, conforme a captura de tela mostrada abaixo. Neste caso, o SUMPRODUCT em conjunto com a função PEQUENO pode ajudá-lo a resolver este problema no Excel.

As melhores ferramentas de produtividade para escritório

Kutools for Excel - ajuda você a se destacar da multidão

Você gostaria de completar seu trabalho diário de forma rápida e perfeita? O Kutools for Excel traz 300 recursos avançados poderosos (combinar pastas de trabalho, somar por cor, dividir o conteúdo da célula, converter data e assim por diante ...) e economizar 80% do tempo para você.

  • Projetado para 1500 cenários de trabalho, ajuda você a resolver 80% dos problemas do Excel.
  • Reduza milhares de cliques de teclado e mouse todos os dias, alivie seus olhos e mãos cansados.
  • Torne-se um especialista em Excel em 3 minutos. Não é mais necessário se lembrar de fórmulas dolorosas e códigos VBA.
  • Avaliação gratuita ilimitada de 30 dias. Garantia de devolução do dinheiro em 60 dias. Atualização e suporte gratuitos por 2 anos.
Faixa de opções do Excel (com Kutools para Excel instalado)

Guia Office - Habilitar leitura e edição com guias no Microsoft Office (incluindo Excel)

  • Um segundo para alternar entre dezenas de documentos abertos!
  • Reduza centenas de cliques do mouse para você todos os dias, diga adeus à mão do mouse.
  • Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
  • Traz guias eficientes para o Office (incluindo Excel), assim como o Chrome, Firefox e o novo Internet Explorer.
Captura de tela do Excel (com guia Office instalado)
Comentários (0)
Ainda não há classificações. Seja o primeiro a avaliar!
Não há comentários postados aqui ainda
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