Tengo una columna llena de datos y no hay un patrón específico, después de 10 o 20 filas debo repetir una nueva fila. Por lo tanto, estas soluciones de índice y compensación solo pueden funcionar si la fórmula puede definir el inicio y el final de cada sección.
Para entenderlo bien tengo una columna como:
A
B
C
D
- (esto rompe una sección)
A
C
E
F
G
- (esto rompe otra fila)
B
C
F
G
- (romper de nuevo)
Por lo tanto, estaría bien para mí si cada carácter "-" comenzara una nueva línea en una tabla. ¿Es posible hacerlo en Excel, o Access, con fórmula o VBA?
¡Gracias!
Respuesta1
Usar:
=FILTERXML("<Group><Element>"&SUBSTITUTE(TEXTJOIN("",FALSE,A1:A15),"-","</Element><Element>")&"</Element></Group>","/Group/Element")
Esto se utiliza TEXTJOIN()para unir todos los valores de la columna (A1:A15 en la fórmula. No se supone que haya "-" como celda final, y eso se puede lograr simplemente seleccionando la celda encima de la última entrada para que sea el final de la columna). rango si siempre aparecen los caracteres finales "-".) No omite ninguna celda incluso si está vacía, pero si se deben omitir celdas vacías, utilícelas TRUEpara el segundo parámetro de la función.
Luego, convertirá la TEXTJOIN()matriz de un solo elemento dividida en partes por los caracteres "-" en la misma cadena pero con "" en lugar de los caracteres "-". Esto es para que la cadena esté parcialmente lista para usar en la FILTERXML()función que ve. Observe que todos los reemplazos son internos a la cadena o, en otras palabras, aparecen solo dentro de ella, no en ninguno de los extremos. Si está familiarizado con HTML, sabrá que el formulario "</tag name>" cierra una etiqueta y "" abre una etiqueta.
Luego agrega la etiqueta de apertura al principio (antepóngala antes de que SUBSTITUTE()ocurra) y la etiqueta de cierre se agrega al... final. Por lo tanto, tiene un par de etiquetas de apertura y cierre alrededor de cada uno de los grupos deseados que los caracteres "-" solían dividir.
Finalmente, todos esos elementos etiquetados individualmente (¿Ves de dónde obtuve el nombre de la etiqueta ""?) deben agruparse en un solo grupo (¿Ves de dónde viene la etiqueta?).
Ahora tienes una cadena HTML que FILTERXML()puede tener sentido y romperse en pedazos. Toma esa cadena para el primer parámetro y para el segundo parámetro, usted le dice cómo se relacionan las etiquetas a través de lo que se llama "Ruta"... que es el bit "/Grupo/Elemento" al final.
Es difícil entender por qué Excel configuró esa necesidad cuando una cadena de este tipo está obviamente organizada de una manera particular, ¿verdad? Pero estaba destinado a diseccionar HTML en vivo real de sitios web en vivo, no desarmar cadenas "mundanas" y podría haber muchas otras etiquetas en dicha cadena de HTML y ¡no sería tan obvio entonces! Sin embargo, es interesante que sugiere que si creaste una cadena con múltiples etiquetas, podrías hacer que Excel tome diferentes conjuntos de la misma cadena usando diferentes valores de Ruta.
De todos modos, FILTERXML()le gusta dar sus resultados en un diseño vertical, filas en una columna. Creo que preferirías esto[Sí, veo que en Comentarios solicitas explícitamente filas], pero si no, si desea un diseño horizontal, columnas en una fila, simplemente envuelva todo en una TRANSPOSE()función.
Otro uso interesante de FILTERXML()es que puede especificar exactamente qué elemento de dicho grupo desea, por número (1, 2, 3,... etc.) Y el último por número (si se conoce) O por "último" que puede ser útil.
Puede hacer una gama más amplia de cosas con cadenas si tiene una forma funcional de insertar los pares de etiquetas internas () en los lugares correctos.