Tengo un conjunto de datos descargado de una base de datos web que tiene formato variable. El objetivo es reunir todos los datos en filas para poder ordenarlos y potencialmente convertirlos en un archivo CSV-KMZ(KML). Después de separar las celdas, los datos se formatean comoeste.
La solución de fuerza bruta que he encontrado implica usar='cell number'en las celdas de la derecha (Como esto.) y luego eliminar todas las celdas vacías para consolidar todos los datos en la misma fila (ejemplo aquí). Desde aquí puedo seleccionar y arrastrar hacia abajo para copiar esa matriz de celdas en las celdas inferiores. El problema surge cuando nos encontramos con entradas de partes responsables con varios sitios, como en la primera imagen, ya que eliminan el espacio, lo que implica mucho copiar y pegar para solucionarlos.
Debido a que este conjunto de datos llega hasta la fila 10 000 o más, y es probable que haya más conjuntos como este, espero que algunos de los grandes talentos aquí puedan pensar en otra solución. ¡Gracias a todos los que reflexionan sobre este problema!
Respuesta1
El ejemplo de diseño de datos que se muestra en la imagen es la clave de su éxito. Esbien caracterizadodatos. Eso significa que sigue un patrón establecido en lugar de estar por todas partes. Y eso significa que puedes escribir fórmulas sencillas y directas para recopilar cada parte.
Cuando leí la introducción antes de hacer clic, pensé que vería datos por todas partes. Por ejemplo, {Ciudad} podría estar en la celda D3, E3 o F3, tal vez, y otros similares por todas partes, o tal vez combinado con {Estado}, y cada registro diferente: no combinado en algunos, seis seguidos con {Ciudad} en E3, luego un par en F3, ese tipo de cosas.
¡No sufres NINGUNO de ese tipo de defecto!
Su única dificultad está en la información del sitio que consta quizás de varios sitios, en lugar de solo uno por registro, punto. Pero es una complicación menor que se soluciona con dos cosas:
1) Un juicio sobre cuántos sitios son posibles por registro. Ya debes estar pensando en eso, ya que planeas distribuir los resultados en una sola fila por RP, así que úsalo. 2) Uso de la función IF() para probar un dato en particular que le indicará si debe extraer información del Sitio o iniciar un nuevo registro.
El segundo parecerá un poco fuera de lugar ya que lo decidirás en dos lugares, por así decirlo, no solo en uno.
Una vez decididas, las celdas en la fila de salida pueden tener fórmulas más simples que prueban el contenido de la celda decisiva y se extienden hacia abajo en la fila.
¿A qué te dedicas? Primero, supongo que los elementos en negrita se agregaron para mayor claridad y no es necesario extraerlos. (Si lo hacen, hágalo de la misma manera). También estoy planeando una salida simple que tendrá una fila de datos, luego una serie de filas "vacías" y luego otra fila de datos, y así sucesivamente. La idea al final será Copiar y Pegar|Especial|Valores, luego ordenar y eliminar la enorme pila de filas "vacías" al final. Uno PUEDE volverse más elegante, pero eso no está en las cartas a las 11:30 p.m....
Para extraer la información principal, los datos "RP" (usaré la idea de que comience en T2 y asumiré que el "ID del partido es el "RP#"), necesita un poco de datos para fijarlos y luego relacionarlos. Se observaron las posiciones de las otras piezas. El "RP#" parece ideal para eso. Entonces, la celda T2 tendría:
= C1
Ahora use OFFSET() para encontrar el resto de los datos de RP. Pero recuerde que cada línea de la salida debe ver si debe recopilar datos. Entonces necesitas envolver eso en un IF() para ver si la fila obtiene datos o no:
= IF( C1 = "", "", C1)
Esto llenará T2 con algo de RP#. De T3 a T7, mostrará "". Pasa a U2. Ingrese la siguiente fórmula:
= IF( C1="", "", OFFSET( C1, 0, 2 ) )
Si C1 tenía un RP#, entonces encontrará el valor de la celda 0 filas hacia abajo y 2 columnas a la derecha. Si no es así, obtendrá "" en U2 (y seguirá apareciendo para todas las columnas que buscan datos que no sean del sitio).
Continúa en. Simplemente varíe los dos valores (las filas son el primero, las columnas son el segundo) según sea necesario para ubicar cada pieza en relación con C1. Thant maneja todos los datos buscados fuera del sitio. (Es interesante recordar, y lo usaremos a continuación, que sus compensaciones pueden ser valores negativos, por lo que puede usar OFFSET() para mirar hacia la izquierda y hacia arriba, así como hacia la derecha y hacia abajo.)
Para la celda AB2, ingrese un desplazamiento de +4 filas (de modo queabajo) y -1 columnas (por lo queizquierda). Entonces la prueba IF() simple, luego el desplazamiento. Parece imposible considerando los datos que no haya un mínimo de un Sitio por cada RP, pero si no hubiera ninguno, agregar al test IF():
= IF( OR( C1="", OFFSET( C1, 4, -1 ) = "" ), "", OFFSET( C1, 4, -1 ) )
Moviéndose hacia la derecha para recopilar datos del sitio, regrese a la prueba IF() simple pero use AB2 en lugar de C1. (Si C1 no era un RP#, tiene un AB2 "vacío", por lo que un AB2 "vacío" significa que C1 también estaba "vacío", por lo que no es necesario realizar pruebas cada vez). Recopile todos los datos de ese sitio tal como lo hizo con el RP datos.
Ahora el quid de la cuestión: ¿hay un segundo Sitio o el comienzo de un nuevo registro? La celda, la misma columna, pero 7 filas hacia abajo desde el RP# de este registro es un nuevo RP# o está en blanco. "En blanco" se puede probar como se hizo anteriormente. Digamos que AK2 es donde deberían comenzar los datos del segundo sitio. Simplemente pruebe esa celda para ver si está en blanco o no. Si está en blanco, entonces hay un segundo sitio y usted ubica sus datos de la misma manera que arriba. Usar:
= IF( OR( C1="", OFFSET( C1, 7, 0 ) = "" ), "", OFFSET( C1, 7, -1 ) )
que obtiene el número de sitio si existe, o un "". El mismo tipo de fórmulas que para el primer sitio, simplemente cambiando el desplazamiento de la fila (los desplazamientos de las columnas serán los mismos). Si no puede haber ninguna información del sitio, modifíquelo para probar también AB2 para "" de modo que, si está "vacío", los resultados "" se extenderán hacia la derecha desde aquí.
Haga esto para tantos conjuntos de datos del sitio como decida que sean posibles. Y quizás uno o dos más, ¿eh?
Ahora la "otra mitad" de la existencia de los datos del Sitio: pasamos a la fila 3 y a la celda T3. Copie todas las celdas de la fila 2 hasta, digamos, la fila 15. Habrá una serie de filas vacías, C2, c3, etc., que no tienen números de RP hasta llegar a C8. Entonces los datos aparecen nuevamente.
Y dado que T8 no será "", la fila a la derecha se completará con datos. ¡Hurra!
Quizás esté pensando que las celdas del sitio a la derecha de las colecciones de sitios reales pueden tener entradas extrañas o confusas porque leen el siguiente registro, o dos, o cuatro, para sus datos. Pero la PRIMERA verificación para ver si hay alguna razón para ellos y da como resultado "" para el número de sitio (preocupado por ser falso) y luego eso se desplaza hacia la derecha, lo que resulta en entradas "" en lugar de leer lo que serían los datos de algún otro registro. . No hay problema.
Copie y pegue todas las filas de datos que tenga, o tantas con las que pueda trabajar (recuerde que no solo hará que su máquina realice un seguimiento de todas esas fórmulas en esas 10,000 filas, sino que también copie y pegue sus valores). al mismo tiempo). Digamos que no hay preocupaciones aquí, pero si las hay, es algo que debe administrar en conjuntos de 1000 filas o lo que sea que funcione bien para usted.
Una vez que se hayan realizado todos los cálculos, copie las celdas de salida y pegue|Especial|Valores en... algún otro lugar, como una segunda hoja. (En este punto, si le preocupa la potencia de la máquina, elimine todas las filas de fórmulas excepto las primeras).
Estar en una hoja completamente diferente le permite hacer cualquier cosa con la salida sin afectar esas celdas de extracción de fórmulas o los datos de origen.
Una vez en la segunda hoja, sólo le queda una preocupación más: a continuación, ordenará el resultado. A veces uno necesita el orden de los datos originales y NO es un orden de clasificación que Excel producirá. Si ese es el caso, inserte una columna a la izquierda y rellénela como desee con una lista de números secuenciales. Simplemente tienen que ser "constantes", no fórmulas, para que nada cambie debido a que las fórmulas se vuelven a calcular después de ordenarse...
Bien, es hora de deshacerse de todas esas filas "vacías" y tener solo un conjunto reducido de filas de datos. Ordene los datos (Y también la columna de numeración, si necesita o desea una). Hazlo de menor a mayor, como de costumbre, para que las filas "vacías" aparezcan en la parte inferior. Encuentra la primera de esas filas "vacías". Hay muchas maneras de hacerlo... desplazándose, digamos... o quizás primero vaya a CUALQUIER fila vacía y coloque "zzzzzzzzzzzz" en su primera celda (la más a la izquierda) para que ordene hasta el final de los datos, lo que los convertirá en el primera fila antes de todas las filas "vacías". Una vez que llegue a la primera celda de la primera fila de "vacíos", presione Ctrl-Shift-Fin para resaltar TODA la basura que necesita sacar. Borre el contenido con la tecla Eliminar.
Ahora sus filas de datos están todas juntas, no hay filas falsas que no estén realmente vacías que interfieran con el uso de los datos y ya está listo para comenzar. Trabaja en ello, o cópialo y pégalo en un lugar de descanso final, tal vez en alguna otra hoja de cálculo, ¡y listo!
Por cierto, la construcción es muy sencilla y no requiere mucho tiempo. Una vez hecho esto, estará hecho para siempre, siempre y cuando los datos no se muevan. Usted lo sabrá, los sitios web cambian, el software se actualiza y columnas completamente nuevas cambian las cosas, pero hay muchas formas sencillas de solucionar esos problemas a medida que actualiza lentamente su trabajo inicial.
Una vez construidos, los datos se leen en el instante en que se cargan, luego simplemente copia y pega, clasifica, elimina (todo solo pulsaciones de teclas, un minuto, literalmente, no horas), y listo. Menciono esto porque habiendo estado en tu lugar y resolviendo cosas con esfuerzo bruto tal como mencionas en la pregunta, creo que puede que te resulte difícil de creer, pero HORAS de trabajo desagradable y brutal con errores que deben corregirse la tonelada, los deslizamientos del mouse, etc., ahora serán literalmente un minuto. ¡Disfruta de la vida que ya no te roba!