¿Tiene preguntas? Tenemos respuestas.

Desagrupar y fraccionar: Extraer patrones complejos

  • Actualización

Resumen

Este artículo le guiará a través del proceso de fraccionar conjuntos de información más complejos en un formato utilizable.


Solución

Considere el siguiente extracto de un archivo de libro mayor:

mceclip1.png

Los problemas:

  1. No hay fila de encabezado (infringe el requisito general 1).
  2. Este archivo se agrupa por cuenta: solo una fila contiene información de la cuenta (filas 6, 3862), seguida de todas las transacciones para esa fila (filas 8-3860, 3864 al siguiente número de cuenta del libro mayor) (infringe el requisito de fila 1).
  3. La información de la cuenta se encuentra en la columna A, pero contiene "Número de cuenta del libro mayor: " seguido del número de cuenta, seguido de dos caracteres de espacio, seguidos de la descripción de la cuenta (filas 6, 3862) (infringe el requisito de columna 1).
  4. Hay filas totales/subtotales innecesarias (fila 3861) y filas en blanco bajo el encabezado Número de cuenta del libro mayor que inicia una nueva cuenta (infringe el requisito general 2).

La solución (se necesita formateo):

  1. Añada una fila de encabezado (la fila 5 es un lugar adecuado para poner los nombres de las columnas).
  2. Desagrupar por cuenta (arrastrar la información de la cuenta a cada fila de transacción).
  3. Fraccionar el número de cuenta y la descripción de la cuenta en diferentes columnas.
  4. Excluya filas extrañas totales/subtotales/en blanco y filas extrañas (filas 1-4) por encima de la fila del encabezado (fila 5).

Insertar dos columnas, "Identificador de cuenta" y "Descripción de cuenta", antes del comienzo del archivo para fraccionar el número y la descripción de la cuenta y arrastrarlos hacia abajo.

mceclip2.png

Identificador de cuenta: Celda A6 e inferior

=IF(LEFT(C6,19)="GL Account Number: ",MID(C6,20,SEARCH(" ",C6,20)-20),A5)
  • Las filas de información de la cuenta pueden identificarse mediante los valores de la columna C que comienzan con "Número de cuenta del libro mayor: ". El 19 de la fórmula representa el hecho de que el texto "Número de cuenta del libro mayor:" tiene 19 caracteres y el 20 representa la posición en la que comienza el número de cuenta (justo después del "Número de cuenta del libro mayor: ")
  • SEARCH(" ",C6,20)
    encuentra la posición del primer carácter de espacio en la cadena C6, comenzando desde la posición 20 (donde comienza el número de cuenta); en otras palabras, estamos intentando averiguar dónde termina el número de cuenta y comienza la descripción de la cuenta. Luego restamos 20 de esta posición para encontrar la longitud del número de cuenta, para pasar a MID() a continuación:
  • MID(C6,20,SEARCH(" ",C6,20)-20)
    es lo que extrae el número de cuenta. La sintaxis de la función MID() es:
    MID(text, start_num, num_chars)
    • donde el texto es la cadena de búsqueda (C6)
    • start_num es la posición por la que se debe iniciar (20, porque "Número de cuenta del libro mayor" tiene 19 caracteres y el número de cuenta comienza inmediatamente después)
    • y num_chars es el número de caracteres que se deben incluir,
      SEARCH(" ",C6,20)-20
      es decir, la longitud del número de cuenta, como se analizó anteriormente
  • IF(LEFT(C6,19)="GL Account Number: ", dice que devuelve el identificador de cuenta (el resultado de la función MID() anterior) si C6 comienza con "GL Account Number: " o, de lo contrario, A5 (la celda calculada anterior): esto es lo que arrastra hacia abajo (desagrupa) el número de cuenta a todas las transacciones de este.

Descripción de la cuenta: Celda B6 e inferior

=IF(LEFT(C6,19)="GL Account Number: ",TRIM(MID(C6,SEARCH(" ",C6,20)+2,LEN(C6))),B5)

Las fórmulas de esta columna siguen la misma lógica que la de la columna Identificador de cuenta (columna A), excepto que extrae el texto después del número de cuenta (es decir, después del primer espacio seguido de "Número de cuenta del libro mayor: "). LEN(C6) garantiza que se devuelve todo el texto restante hasta el final.

Subtotales

Las siguientes son las fórmulas para las células G1 y H1:

=SUBTOTAL(9,G7:G50000)
=SUBTOTAL(9,H7:H50000)

Utilizamos 50 000 porque este archivo en particular tiene menos de 50 000 filas. Cámbielo a un número lo suficientemente grande como para acomodar el número total de filas que tiene su archivo. Añadimos estas fórmulas para comprobar que el archivo se equilibra cuando realizamos el paso final de filtrado para mantener solo las filas de transacción (siguiente).

Filtrar para mantener solo filas de transacciones

Después de activar los filtros, marque la columna D (fecha) y verá que hay fechas en el año 2020 y espacios en blanco.

mceclip3.png

Copiar y pegar valores en un libro de trabajo nuevo

Teniendo en cuenta que todas las fechas son las filas de transacciones, seleccione solo las fechas (en blanco y sin comprobar).

mceclip5.png

Tenga en cuenta que los resultados de nuestras fórmulas SUBTOTAL() en las celdas G1 y H1 muestran que las transacciones totales están equilibradas (¡sí!)

Nuestro paso final es seleccionar la fila de encabezado y las filas de transacción seleccionadas (es decir, la fila 5 en adelante en la vista filtrada; puede comenzar en la celda A5, pulsar Ctrl-Mayús-Fin para llegar al final del archivo y ajustar la posición según sea necesario), luego copiar y pegar valores especiales en un nuevo libro de trabajo.

mceclip0.png

Por último, en el nuevo libro de trabajo, asegúrese de formatear la columna Fecha como Fecha en formato AAAA-MM-DD.

mceclip7.png

Y ahora tenemos un archivo con un formato precioso, listo para importar en MindBridge.

mceclip8.png


¿Tiene alguna otra idea?

¡Envíe sus ideas y consultas a la comunidad! También puede conversar en línea con nosotros o enviar una solicitud para recibir asistencia adicional.

¿Fue útil este artículo?