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:
Los problemas
- No hay fila de encabezado (infringe el requisito general 1).
- 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).
- 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).
- 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 formato)
- Añada 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.
- 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.
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. Lo cambiamos por 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.
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).
Observe que los resultados de nuestras fórmulas SUBTOTAL() en las celdas G1 y H1 muestran que las transacciones totales están en equilibrio (¡sí!)
Nuestro paso final es seleccionar la fila de encabezado y las filas de operació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.
Por último, en el nuevo libro de trabajo, asegúrese de formatear la columna Fecha como Fecha en formato AAAA-MM-DD.
Y ahora tenemos un archivo con un formato precioso, listo para importar en MindBridge.
¿Tiene alguna otra idea en mente? Converse en línea con nosotros o envíe una solicitud de asistencia adicional.