Resumen
Este artículo te guiará a través del proceso de fraccionar conjuntos más complejos de información en un formato utilizable.
Resolución
Considera el siguiente extracto de un archivo de libro mayor:
Los problemas
- No hay fila de encabezado (viola requisito general 1).
- Este archivo está agrupado por cuenta: solo una fila contiene información de cuenta (filas 6, 3862), seguida de todas las transacciones para esa fila (filas 8-3860, 3864 hasta el siguiente Número de Cuenta del Libro Mayor) (viola requisito de fila 1).
- La información de la cuenta está 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) (viola requisito de columna 1).
- Hay filas de total/subtotal innecesarias (fila 3861) y filas en blanco bajo el encabezado del Número de Cuenta del Libro Mayor que inicia una nueva cuenta (viola requisito general 2).
La solución (formateo necesario)
- Agregar una fila de encabezado (la fila 5 es un lugar adecuado para poner los nombres de las columnas).
- Desagrupar por cuenta (arrastrar la información de la cuenta a cada fila de transacción).
- Fraccionar el número de cuenta y la descripción de la cuenta en diferentes columnas.
- Excluir filas de total/subtotal/en blanco y filas innecesarias (filas 1-4) por encima de la fila de encabezado recién creada (fila 5).
Inserta dos columnas, "ID de cuenta" y "Descripción de la cuenta", antes del inicio del archivo para fraccionar el número de cuenta y la descripción de la cuenta y arrástralos hacia abajo.
ID de cuenta: Celda A6 y siguientes
=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 por los valores de la columna C que comienzan con "Número de Cuenta del Libro Mayor: ". El 19 en la fórmula representa el hecho de que el texto "Número de Cuenta del Libro Mayor: " tiene 19 caracteres de largo, y el 20 representa la posición donde comienza el número de cuenta (justo después de "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 tratando de 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(texto, start_num, num_chars)
- donde texto es la cadena a buscar (C6)
- start_num es la posición para comenzar (20 porque "Número de Cuenta del Libro Mayor: " tiene 19 caracteres de largo y el número de cuenta comienza justo después)
- y num_chars es el número de caracteres a incluir
SEARCH(" ",C6,20)-20
...es decir, la longitud del número de cuenta como se discutió anteriormente
IF(LEFT(C6,19)="Número de Cuenta del Libro Mayor: ", dice que devuelva el ID de cuenta (el resultado de la función MID() anterior) si C6 comienza con "Número de Cuenta del Libro Mayor: ", A5 (la celda calculada anteriormente) de lo contrario - esto es lo que hace el arrastre hacia abajo (desagrupación) del número de cuenta a todas las transacciones debajo de él.
Descripción de la cuenta: Celda B6 y siguientes
=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 ID de cuenta (columna A), excepto que extrae el texto después del número de cuenta (es decir, después del primer espacio después de "Número de Cuenta del Libro Mayor: "). LEN(C6) asegura que se devuelva todo el texto restante hasta el final.
Subtotales
Las siguientes son las fórmulas para las celdas G1 y H1:
=SUBTOTAL(9,G7:G50000)
=SUBTOTAL(9,H7:H50000)
Usamos 50000 porque este archivo en particular tiene menos de 50000 filas. Cambia eso a un número lo suficientemente grande para acomodar el número total de filas que tiene tu archivo. Agregamos estas fórmulas para verificar que el archivo esté balanceado cuando hagamos el paso final de filtrado para mantener solo las filas de transacción (a continuación).
Filtrar para mantener solo las filas de transacción
Después de activar los filtros, verifica la columna D (fecha) y vemos que hay fechas en el año 2020, y espacios en blanco.
Copiar y pegar valores en un nuevo libro de trabajo
Observando que todas las fechas son las filas de transacción, selecciona solo las fechas (desmarca los espacios en blanco).
Nota que los resultados de nuestras fórmulas SUBTOTAL() en las celdas G1 y H1 muestran que el total de transacciones está balanceado (¡yay!)
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 — puedes comenzar en la celda A5, presionar Ctrl-Shift-End para llegar al final del archivo y ajustar la posición según sea necesario), luego copiar, luego pegar valores especiales en un nuevo libro de trabajo.
Finalmente, en el nuevo libro de trabajo, asegúrate de formatear la columna Fecha como Fecha en formato YYYY-MM-DD.
¡Y ahora tenemos un archivo bellamente formateado, listo para importar a MindBridge!
¿Algo más en tu mente? Chatea con nosotros o envía una solicitud para obtener más ayuda.