Baixe a apostila de exemplo
Este tutorial demonstrará como concatenar valores de células com base em critérios usando a função TEXTJOIN no Excel e no Google Sheets.
A função TEXTJOIN
Os usuários do Excel 2022+ têm acesso à função TEXTJOIN, que mescla várias sequências de texto, separando cada sequência com um delimitador especificado.
Observação: Os usuários do Planilhas Google podem usar a função TEXTJOIN, mas têm uma maneira um pouco diferente de inserir fórmulas de matriz. Consulte a seção posterior neste tópico.
Este exemplo usará as funções TEXTJOIN e IF em uma fórmula de matriz para criar uma string de texto separada por vírgulas de Nomes de jogadores que se relacionam com um Equipe valor de vermelho
| 1 | = TEXTJOIN (",", VERDADEIRO, SE (C3: C8 = "Vermelho", B3: B8, "")) |
Os usuários do Excel 2022 precisarão inserir essa fórmula como uma função de matriz pressionando CTRL + SHIFT + ENTER. Os usuários de versões posteriores do Excel não precisam seguir esta etapa.
Para explicar o que esta fórmula está fazendo, vamos dividi-la em etapas:
Esta é a nossa fórmula final:
| 1 | = TEXTJOIN (",", VERDADEIRO, SE (C3: C8 = "Vermelho", B3: B8, "")) |
Primeiro, os valores do intervalo de células são adicionados à fórmula:
| 1 | = TEXTJOIN (",", TRUE, IF ({"Red"; "Blue"; "Blue"; "Red"; "Blue"; "Red"} = "Red", {"A"; "B"; "C"; "D"; "E"; "F"}, "")) |
A seguir a lista de Equipe nomes são comparados com o valor vermelho:
| 1 | = TEXTOJOIN (",", VERDADEIRO, SE ({VERDADEIRO; FALSO; FALSO; VERDADEIRO; FALSO; VERDADEIRO}, {"A"; "B"; "C"; "D"; "E"; "F"} , "")) |
A função IF substitui valores TRUE com o Nome do jogador, e valores FALSE com “”
| 1 | = TEXTJOIN (",", TRUE, {"A"; ""; ""; "D"; ""; "F"}) |
A função TEXTJOIN então combina todos os valores da matriz em uma string de texto. Instruímos a função a ignorar os valores em branco e a adicionar o texto “,” entre cada valor. Isso produz o resultado final:
| 1 | = "A, D, F" |
TextJoin If - no pré-Excel 2022
Como a função TEXTJOIN não está disponível antes da versão do Excel 2022, precisamos resolver este problema de uma maneira diferente. A função CONCATENATE está disponível, mas não aceita intervalos de células como entradas ou permite operações de matriz e, portanto, somos obrigados a usar uma coluna auxiliar com uma função IF.
Este próximo exemplo mostra como usar uma coluna auxiliar para criar uma string de texto separada por vírgulas de Nomes de jogadores que se relacionam com um Equipe valor de Vermelho:
| 1 | = IF (C3 = "Vermelho", B3 & ",", "") & D4 |
A primeira etapa neste exemplo é usar uma função IF para replicar a condição de Equipe = Vermelho. Se a condição for atendida, o Nome do jogador o valor é escrito e seguido pelo delimitador escolhido “,“:
| 1 | = IF (C3 = "Vermelho", B3 & ",", "") |
Em seguida, podemos criar uma coluna que constrói uma lista desses valores em uma célula, referenciando também a célula abaixo dela:
| 1 | = D3 e E4 |
Esta fórmula usa o caractere & para juntar dois valores. Observe que a função CONCATENATE pode ser usada para criar exatamente o mesmo resultado, mas o método & é frequentemente preferido, pois é mais curto e torna mais claro qual ação a fórmula está executando.
Essas duas colunas auxiliares podem ser combinadas em uma fórmula:
| 1 | = IF (C3 = "Vermelho", B3 & ",", "") & D4 |
Uma célula de resumo pode então fazer referência ao primeiro valor no Lista de jogadores coluna auxiliar:
| 1 | = D3 |
Se o valor final “,” não for necessário, uma função IF aninhada adicional deve ser adicionada à fórmula:
| 1 | = IF (C3 = "Vermelho", IF (D4 = "", B3, B3 & ","), "") e D4 |
A parte adicional da fórmula é a Função IF abaixo:
| 1 | SE (D4 = "", B3, B3 & ",") |
Esta parte da fórmula verifica o valor da célula abaixo dela. Se estiver vazio, esta célula deve ser a última Nome do jogador na lista e o nome é escrito sem um delimitador. Se a célula abaixo contém um valor, então esta célula não é a última Nome do jogador na lista e, portanto, o delimitador “,” é adicionado.
TextJoin If no Google Sheets
Essas fórmulas funcionam da mesma forma no Planilhas Google e no Excel, exceto que a função ARRAYFORMULA deve ser usada no Planilhas Google para avaliar os resultados corretamente. Isso pode ser adicionado automaticamente pressionando as teclas CTRL + SHIFT + ENTER enquanto edita a fórmula:
| 1 | = ARRAYFORMULA (TEXTJOIN (",", TRUE, (IF (C3: C8 = "Vermelho", B3: B8, "")))) |
