Tenho uma coluna cheia de dados, e não existe um padrão específico, que após 10 ou 20 linhas eu deva repetir uma nova linha. Portanto, essas soluções de Índice e Offset só podem funcionar se a fórmula puder definir o início e o fim de cada seção.
Para entender direito, tenho uma coluna como:
A
B
C
D
- (isso quebra uma seção)
A
C
E
F
G
- (isso quebra outra linha)
B
C
F
G
- (quebra novamente)
Portanto, seria bom para mim se cada caractere "-" iniciasse uma nova linha em uma tabela. É possível fazer no Excel, ou Access, com fórmula ou VBA?
Obrigado!
Responder1
Usar:
=FILTERXML("<Group><Element>"&SUBSTITUTE(TEXTJOIN("",FALSE,A1:A15),"-","</Element><Element>")&"</Element></Group>","/Group/Element")
Isso é usado TEXTJOIN()para unir todos os valores na coluna (A1:A15 na fórmula. Ele não assume nenhum "-" como a célula final, e isso pode ser conseguido simplesmente selecionando a célula acima da última entrada para ser o final do intervalo se os caracteres finais "-" sempre aparecerem.) Ele não pula nenhuma célula, mesmo que esteja vazia, mas se células vazias devem ser ignoradas, use TRUEpara o segundo parâmetro na função.
Então você transformará a TEXTJOIN()matriz de elemento único dividida em partes pelos caracteres "-" na mesma string, mas com "" no lugar dos caracteres "-". Isso torna a string parcialmente pronta para uso na FILTERXML()função que você vê. Observe que as substituições são todas internas à string ou, em outras palavras, aparecem apenas dentro dela, e não em nenhuma das extremidades. Se você estiver familiarizado com HTML, saberá que o formulário "</tag name>" fecha uma tag e o "" abre uma tag.
Em seguida, você adiciona a tag de abertura no início (acrescenta-a antes de SUBSTITUTE()ocorrer) e a tag de fechamento é anexada ao... final. Portanto, você tem um par de tags de abertura e fechamento em torno de cada um dos grupos desejados que os caracteres "-" usaram para separar.
Finalmente, todos esses elementos marcados individualmente (viu de onde tirei o nome da tag ""?) precisam ser agrupados em um único grupo (viu de onde vem a tag?).
Agora você tem uma string HTML que FILTERXML()pode fazer sentido e ser quebrada em pedaços. É necessária essa string para o primeiro parâmetro e para o segundo parâmetro, você informa como as tags se relacionam por meio do que é chamado de "Caminho"... que é o bit "/Grupo/Elemento" no final.
É difícil entender por que o Excel configura essa necessidade quando essa string é obviamente organizada de uma maneira específica, certo? Mas a intenção era dissecar o HTML ativo real de sites ativos, não desmontar strings "mundanas" e poderia haver muitas outras tags em tal sequência de HTML e isso não seria tão óbvio então! Curiosamente, porém, isso sugere que, se você criou uma string com múltiplas marcações, poderá fazer com que o Excel pegue conjuntos diferentes da mesma string usando diferentes valores de Path.
De qualquer forma, FILTERXML()gosta de apresentar seus resultados em um layout vertical, linhas em uma coluna. Eu acredito que você preferiria isso[sim, vejo nos comentários que você solicita linhas explicitamente], mas se não, se você quiser um layout horizontal, colunas em uma linha, basta agrupar tudo em uma TRANSPOSE()função.
Outro uso interessante FILTERXML()é que você pode especificar exatamente qual elemento de tal grupo deseja, por número (1, 2, 3,... etc.) E o último por número (se conhecido) OU por "último" qual pode ser útil.
Ele pode fazer uma variedade maior de coisas com strings se você tiver uma maneira funcional de inserir os pares de tags internos () nos lugares certos.