Gerar uma lista separada por vírgula do conteúdo da célula, excluindo espaços em branco

Question

Para concatenar certas células, eu usaria:

=CONCATENATE(A2,",",C2",",D2,",",F2)

Isso faria com que ...

• A2="Mateus"
• C2="Marcar"
• D2="Luke"
• F2="João"

resultaria em Matthew,Mark,Luke,John .

Mas nos deparamos com problemas como ...

• A2="Jesus"
• C2=""
• D2="Maria"
• F2="José"

que resultaria em Jesus,,Mary,Joseph .

Aqui, a vírgula extra é indesejada. Existe uma maneira de lidar com isso de modo que todas as células não-em branco sejam incluídas na lista separada por vírgulas, evitando a adição de vírgulas desnecessárias quando algumas células estiverem em branco?

Certamente, isso pode ser feito com um número razoável de IFs aninhados, mas eu realmente quero evitar isso, se possível. Pode ser feito com funções nativas do Excel ou talvez com uma fórmula de matriz? Ou alguém teria que recorrer ao VB Script para algo assim?

    

Answer 1

Como você disse, IFs aninhados não são sua xícara de chá, mas não vejo outra maneira de fazer isso, sem a solução VBA apontada por @nixda.

Para as soluções que apresento, presumo que seus dados sejam como os seguintes:

 |    A    |
-+---------+
1| Matthew |
2| Mark    |
3| Luke    |
4| John    |

Eu tinha uma solução que fazia o que você queria, mas tornou-se "enterrada" . Eu entendi depois de ler os comentários que você deixou em uma resposta recente (especialmente o comentário sobre o fato de você sempre ter a primeira célula preenchida). Isso permite que não exista um ninho, já que a primeira condição (no raciocínio abaixo) é sempre falsa.

=CONCATENATE(A1;IF(ISBLANK(A2);"";"," & A2);IF(ISBLANK(A3);"";"," & A3);IF(ISBLANK(A4);"";"," & A4))

Você sempre pode eliminar o CONCATENATE e substituir a fórmula por e comercial, conforme indicado na resposta do @Scott. Neste ponto, é simplesmente uma questão estética.

=A1 & IF(ISBLANK(A2);"";"," & A2) & IF(ISBLANK(A3);"";"," & A3) & IF(ISBLANK(A4);"";"," & A4)

O primeiro valor é sempre escrito, então a única coisa necessária é verificar se a célula ativa está em branco e adicionar uma vírgula por trás dela, se não estiver.

Dessa forma, você não precisa de nenhuma célula auxiliar, tudo está em uma única função.

Também escrevi uma versão mais elaborada, porque assumi o termo geral (ou seja, poderia haver uma situação em que você não preenchesse sua primeira célula). Requer apenas um ninho de 2 níveis, porque há no máximo 3 condições que precisam ser verificadas:

• As células antes da célula ativa estão vazias?
• A célula ativa está vazia?
• Nenhuma das afirmações acima é verdadeira? (condição de default)

A formatação necessária, supondo que você começou o raciocínio desde o início, é a seguinte:

• Se sim, então não coloque uma vírgula antes dos dados da célula. Se não, continue.
• Se sim, então não coloque nada (uma solução alternativa seria colocar a célula).
• Se nenhum for verdadeiro, coloque uma vírgula por trás dos dados da célula.

Como tal, aqui está a fórmula que usei. Eu assumi que todos os dados estavam em uma coluna, para usar em uma linha alterar algumas fórmulas e o intervalo.

=CONCATENATE(A1;IF(ROWS(A1:A1)=COUNTBLANK(A1:A1);A2; IF(ISBLANK(A2);"";"," & A2));IF(ROWS(A1:A2)=COUNTBLANK(A1:A2);A3; IF(ISBLANK(A3);"";"," & A3));IF(ROWS(A1:A3)=COUNTBLANK(A1:A3);A4; IF(ISBLANK(A4);"";"," & A4)))

ROWS conta o número de linhas em um determinado intervalo. Se for igual ao número de células em branco por trás da célula active , significa que todas as células por trás da célula active estão em branco, pelo que nenhuma vírgula deve ser colocada antes.

O intervalo em ROWS e COUNTBLANK são as células atrás da célula ativa .

    

Answer 2

No Excel 2016, você pode usar TEXTJOIN :

=TEXTJOIN(delimiter,ignore_blanks?,values_to_be_joined)

Então, no seu caso, você usaria essa fórmula:

=TEXTJOIN(",",TRUE,A2,C2,D2,F2)

    

Answer 3

Se você estiver disposto a usar algumas células "auxiliares" para valores intermediários, poderá gostar disso:

Defina a célula AA2 (ou Sheet2!A2 , ou onde quer que você queira colocá-la) para

=IF(A2="", "", A2&",")

(onde x & y é apenas uma maneira mais curta de dizer CONCATENATE(x, y) ) e defina as células AC2 , AD2 e AF2 da mesma forma. Defina AZ2 para

=AA2 & AC2 & AD2 & AF2

Então seu resultado final é

=IF(AZ2="", "", LEFT(AZ2, LEN(AZ2)-1))

Explicação:

As células AA - AF acrescentam vírgulas aos valores não em branco. Então, usando o seu exemplo,

Matthew         Mark            Luke            John

resultaria em

Matthew,        Mark,           Luke,           John,

enquanto

Jesus                           Mary            Joseph

resultaria em

Jesus,                          Mary,           Joseph,

(observe a falta de uma vírgula na coluna C ).

AZ2 é uma concatenação simples dos itens acima: Jesus,Mary,Joseph, , então eliminamos a vírgula extra depois de Jesus , mas adicionamos uma no final. LEFT(AZ2, LEN(AZ2)-1) é todo de AZ2 , exceto pelo último caractere. E precisamos testar se AZ2 é nulo para evitar um erro se todas as quatro células de entrada estiverem vazias.

    

Answer 4

Suponho que você esteja procurando por uma solução simples e não para melhorar suas habilidades.

Você pode usar um suplemento se isso for permitido (por exemplo, você tem permissão para instalar suplementos). ASAP Utilities é um que eu recomendo que é gratuito para uso não comercial.

Veja o que você pode fazer com uma de suas funções

This utility merges the data from the columns in your selection. For each row in your selection the data from the adjacent columns will be concatenated into the first cell in the row.

You can specify the following: Delimiter to put between the cell values Skip empty cells Use the value, formula or formatted value from the cells.

    

Answer 5

Se você quiser uma solução geral, a melhor resposta envolverá o VBA.

Se os seus dados de exemplo estiverem próximos do problema completo que você está abordando, basta fazer o seguinte para remover as duas vírgulas:

=SUBSTITUTE(CONCATENATE(A2,",",C2",",D2,",",F2),",,",",")

O acima só funcionará se você tiver apenas um valor de largura. Você precisará de fórmulas substitutas aninhadas () para lidar com lacunas maiores.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(A2,",",C2,",",D2,",",F2),",,,,,,,,,,,,,,,,",","),",,,,,,,,",","),",,,,",","),",,,",","),",,",",")

O acima irá reduzir até 398 vírgulas seqüenciais para uma. Isso também reduzirá muitos valores acima de 399, mas o próprio 399 e vários outros resultarão em mais de uma vírgula.

    

Answer 6

Suponho que seus dados tenham cabeçalhos e iniciem em A2.

1. Ir para qualquer coluna sobressalente, usarei B neste exemplo
2. Na célula B2, digite simplesmente = A2
3. Na célula B3 digite = SE (A3="", B2, CONCATENATE (B2, ",", A3)
4. Copie a coluna B de B3 (não B2!) para a última linha que contém dados na Coluna A

Voila, sua lista separada por vírgula está na última célula da Coluna B:)

Eu vim aqui procurando por uma solução, não encontrei nenhuma e assim surgiu o acima. Meus dados têm 1000 linhas, portanto, adicionar manualmente à concatenação não é uma opção.

    

Answer 7

Apenas combine Concatenate () com if () em uma função de matriz:

=arrayformula(concatenate(if(not(isempty(a2:f2)),a2:f2&", ","")))

Esta função vai deixar uma vírgula no final da sua lista, então se você precisa evitar isso e você sabe exatamente onde o final da lista está (no seu exemplo, coluna f), apenas manipule aquela separadamente:

=arrayformula(concatenate(if(not(isempty(a2:d2)),a2:d2&", ","")))&if(not(isempty(f2)),f2,"")

Felicidades!