Baixe a apostila de exemplo
Este tutorial demonstrará como encontrar a soma dos valores de texto em que um código exclusivo é atribuído a cada valor de texto no Excel e nas Planilhas Google.
Números SUM armazenados como texto
Primeiro, veremos como somar os números armazenados ou formatados como texto.
No exemplo a seguir, a coluna Salário é armazenada como texto. Se tentar somar os valores, o Excel exibirá um zero.
| 1 | = SOMA (C3: C7) |
Em vez disso, para realizar a operação SUM em números armazenados como texto, você pode usar uma fórmula de matriz com as funções SUM e VALUE como esta:
| 1 | = SOMA (VALOR (C3: C7)) |
A função VALUE converte um texto que representa um número em um número. A função SUM soma esses números.
No Excel 365 e na versão do Excel mais recente que 2022, você pode simplesmente inserir a fórmula normalmente. No entanto, ao usar o Excel 2022 e anteriores, você deve inserir a fórmula de matriz pressionando CTRL + SHIFT + ENTER (ao invés de DIGITAR), informando ao Excel que a fórmula é uma fórmula de matriz. Você saberá que é uma fórmula de matriz pelas chaves que aparecem ao redor da fórmula (veja a imagem superior). Em versões posteriores do Excel e Excel 365, você pode simplesmente pressionar DIGITAR em vez de.
Vejamos a seguinte explicação para obter uma melhor compreensão da fórmula.
Função VALUE
Usada como uma fórmula de matriz, a função VALUE converte toda a gama de números armazenados como texto em uma matriz de números e a retorna como uma entrada para a função SUM.
| 1 | = SOMA ({10.000; 6.500; 7.500; 15.000; 8.000}) |
Para ver o que a função VALUE retorna, selecione a função necessária e pressione F9.
SOMA de valores de texto
Para somar um intervalo de valores de texto em que um código exclusivo é atribuído a cada valor de texto, uma fórmula de matriz pode ser usada.
A tabela a seguir registra o que as pessoas selecionam para três perguntas. A tabela à direita lista as quatro respostas possíveis, a cada uma das quais foi atribuído um valor de código. Temos que somar os códigos de cada pessoa.
| 1 | = SOMA (ÍNDICE (I $ 3: I $ 6, N (SE (1, CORRESPONDÊNCIA (C4: E4, H $ 3: H $ 6,0))))) |
Percorreremos a fórmula abaixo.
Função MATCH
A função MATCH procura por um item especificado em um intervalo e retorna sua posição relativa nesse intervalo. Sua sintaxe é:
Sendo uma fórmula de matriz neste exemplo, vamos ver o que a função MATCH retorna.
| 1 | = SOMA (ÍNDICE (I $ 3: I $ 6, N (SE (1, {1,3,2})))) |
Para uma determinada pessoa, a função MATCH encontra a posição relativa de cada resposta no intervalo H3: H6. O resultado é uma série de posições.
Observação: Em uma fórmula de matriz, para ver o que uma função retorna, selecione a função necessária e pressione F9.
Função IF & N
O IF e a função N usados juntos retornam a seguinte matriz como uma entrada para a função INDEX.
| 1 | = SOMA (ÍNDICE (I $ 3: I $ 6, {1,3,2})) |
Aqui, as duas funções retornam uma matriz de posição relativa de respostas no intervalo H3: H6. O objetivo de usar as funções IF e N é para executar um processo chamado desreferenciamento. Em termos simples, as duas funções forçam a função INDEX a passar todo o array de valores de código para a função SUM.
Explicaremos isso na próxima seção.
Função INDEX
A função INDEX retorna o valor posicionado na interseção de uma linha e coluna especificadas em um intervalo. Sua sintaxe é:
Vamos ver como funciona como uma fórmula de matriz:
| 1 | = SOMA ({10,5,8}) |
A função INDEX encontra os valores do código no intervalo I3: I6 de acordo com os números de posição fornecidos. Em seguida, ele retorna uma matriz de valores, ou seja, o respectivo código para cada resposta, para a função SUM para realizar as operações.
Certifique-se de que o número de linhas e colunas em ambos os Responder e a Código coluna é a mesma.
Função SUM
A função SUM somará os valores de código retornados pela função INDEX.
| 1 | = SOMA ({10,5,8}) |
Tudo isso junto produz nossa fórmula inicial:
| 1 | {= SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0)))))} |
SOMA de valores de texto - sem funções IF e N
Esta seção explica como o Excel responde se não usarmos o IF e a função N na fórmula mencionada acima.
O mesmo exemplo está sendo usado com os mesmos códigos e respostas.
| 1 | {= SOMA (ÍNDICE (I $ 3: I $ 6, CORRESPONDÊNCIA (C4: E4, H $ 3: H $ 6,0)))} |
Como você pode ver, a função INDEX passa apenas o código da primeira resposta para a função SUM. Se você examinar a função INDEX pressionando F9 você obterá o seguinte:
O valor que! O erro é retornado porque a função INDEX não pode ler a matriz de números de linha como uma matriz. Portanto, usar a função IF e N resolve o problema.
Observação: No Excel 365, você pode ignorar o uso das funções IF e N juntas.
Sum Text- Google Sheets
Essas fórmulas funcionam da mesma forma no Planilhas Google e no Excel.
