Resumen
Esta guía es para ayudarte a entender las mejores maneras de solucionar un desequilibrio en los resultados de MindBridge cuando tu archivo de GL está formateado y puede ser fácilmente ingerido en MindBridge.
Este es un recorrido más avanzado y completo. Así que si aún no lo has hecho, te recomendamos intentar las soluciones encontradas en Los montos o los Débitos y Créditos no cuadran al importar GL en MindBridge.
Resolución
Considera el siguiente extracto de un archivo de GL:
Primero, revisaremos brevemente cómo formatear este archivo. Si prefieres, puedes saltarte esta parte y ir directamente a Solucionar el desequilibrio.
Los problemas:
- Este archivo está agrupado por cuenta: solo una fila contiene información de cuenta (filas 6, 10, 169), seguida de todas las transacciones para esa fila (filas 12-167, 171 en adelante) (viola requisito de fila 1).
- El número de cuenta generalmente está en la columna B (filas 10 y 169) pero a veces está en la columna A (fila 6) (viola requisito de columna 1) (Sin embargo, la descripción de la cuenta siempre está en la columna E para las filas de cuenta)
- Hay filas de total/subtotal innecesarias (9, 168) y filas en blanco bajo las filas de número de cuenta (viola requisito general 2)
La solución (formateo necesario):
- Desagrupar por cuenta (arrastrar la información de la cuenta a cada fila de transacción)
- Excluir filas de total/subtotal/en blanco innecesarias (filas 7-9, 11, 168-170) y filas innecesarias (filas 1-4) por encima de la fila de encabezado (fila 5)
Agregamos una columna "ID de cuenta" al final del archivo para extraer el número de cuenta de las filas de cuenta y arrastrarlo hacia abajo:
ID de cuenta: Celda K6 y abajo
=IF(NOT(ISERROR(SEARCH("????-??-??",B6))),B6,IF(NOT(ISERROR(SEARCH("????-??-??",A6))),A6,K5))
-
SEARCH("????-??-??",B6), SEARCH("????-??-??",A6)encuentra la posición en la cadena B6 o A6 donde aparece una cadena que coincide con la expresión "????-??-??". Si no se encuentra, se devuelve un error (#VALUE!) - la expresión "????-??-??" usa el carácter comodín ? que coincide con cualquier carácter único. Así que "????-??-??" significa cualquier cuatro caracteres únicos, seguidos de -, seguidos de cualquier dos caracteres únicos, seguidos de -, seguidos de cualquier dos caracteres únicos. El propósito es probar si se encuentra un número de cuenta en la celda (B6 o A6)
- El NOT(ISERROR()) envuelto alrededor de estas expresiones SEARCH() solo indica si las celdas B6 o A6 contienen números de cuenta (es decir, se encontró el patrón "????-??-??"). Así que
IF(NOT(ISERROR(SEARCH("????-??-??",B6))),B6,significa devolver B6 si B6 es un número de cuenta, y elIF(NOT(ISERROR(SEARCH("????-??-??",A6))),A6,que sigue significa si A6 es un número de cuenta, devolver A6, y finalmente, el K5 al final significa de lo contrario devolver K5 (el ID de cuenta calculado en la fila anterior, es decir, esto es lo que arrastra hacia abajo los números de cuenta encontrados)
=IF(OR(NOT(ISERROR(SEARCH("????-??-??",B6))),NOT(ISERROR(SEARCH("????-??-??",A6)))),E6,L5)
Filtro para mantener solo filas de transacción
Con los filtros activados, verifica la columna B (Fecha) y vemos que hay fechas de 2020 y 1900, luego el resto son números de cuenta:
Resulta que en este archivo, las fechas de 1900 también son datos basura que no queremos mantener, así que también las excluiremos en el siguiente paso.
Copiar y pegar valores en un nuevo libro de trabajo
Notando que todas las fechas de 2020 son las filas de transacción, selecciona solo las fechas de 2020:
Luego selecciona la fila de encabezado y las filas de transacción seleccionadas (es decir, 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.
Solucionar el desequilibrio
Así que ahora tenemos un archivo bien formateado que se ve así:
¡Pero encontramos que está fuera de balance porque los débitos no igualan a los créditos!
Extraer subtotales de cuenta
Afortunadamente, notamos que este archivo tiene subtotales de cuenta (filas que se ven como la fila 168 en la primera imagen en la parte superior de esta página). Y la forma en que identificamos estas filas de subtotal de cuenta es que la Fecha (columna B) está en blanco, y el Cambio Neto (columna I) es un número (es decir, no está en blanco).
Así que de vuelta en el archivo original de GL donde agregamos la fórmula a la columna K para extraer y arrastrar el ID de cuenta hacia abajo, y con los filtros aún activados, filtramos en Fecha (columna B) está en blanco y la columna I no está en blanco:
Ten en cuenta que las columnas reales para estas filas de subtotal de cuenta no corresponden con los encabezados de columna - están en un formato ligeramente diferente de las filas de transacción, una columna desplazada a la izquierda - el Balance Inicial está en realidad en la columna E y el Balance Final está en realidad en la columna I. Pero como no estamos formateando los subtotales para importarlos en MindBridge, eso está bien. Solo necesitamos copiar y pegar valores de las columnas que necesitamos (columnas E-I y K) en una nueva hoja de cálculo en el libro de trabajo con el GL formateado que usaremos para solucionar el desequilibrio.
Nombramos esta hoja de cálculo subtotales y renombramos los encabezados para evitar confusiones:
Tabla dinámica en los datos de GL formateados
A continuación, creamos una tabla dinámica en los datos de GL formateados en una nueva hoja de cálculo, totalizando Débitos y Créditos por número de cuenta:
- Selecciona todos los datos formateados (puedes comenzar en la parte superior y presionar Ctrl-Shift-End para llegar al final del archivo, y ajustar la posición según sea necesario), ve a Insertar -> Tablas -> Tabla dinámica
El Rango de Tabla: indicará automáticamente el rango que seleccionaste para los datos (en nuestro caso, hemos nombrado la hoja de cálculo con los datos de GL formateados "formattedGL" y los datos van de A1 a G16519)
- Mantén seleccionado el botón de opción "Nueva hoja de cálculo" (que debería estar seleccionado por defecto), luego haz clic en OK.
Esto te llevará a una nueva hoja de cálculo que se ve así:
Queremos totalizar Débito y Crédito por ID de cuenta.
- Desplázate hacia abajo en la lista de NOMBRES DE CAMPO y haz clic y arrastra el "ID de cuenta" a la sección de Filas:
- Luego arrastra Débito y Crédito a la sección de Valores:
- Ahora tu tabla dinámica debería verse así:
Buscar y comparar con subtotales de cuenta
Ahora queremos comparar estos totales de cuenta con los que habíamos extraído del GL original y guardado en la hoja de cálculo subtotales:
Subtotales de cuenta: fórmula de columna de débito
=SUMIF(subtotals!$G:$G,$A4,subtotals!B:B)
Recuerda que en la hoja de cálculo subtotales, la columna G tiene el ID de cuenta y las columnas B y C los montos de Débito y Crédito:
La columna A en la hoja de cálculo de la tabla dinámica tiene los IDs de cuenta. Así que esta fórmula SUMIF obtiene los totales de débito y crédito de la hoja de cálculo de subtotales para cada ID de cuenta en la tabla dinámica.
subtotales de cuenta: fórmula de columna de crédito (solo copia y pega la fórmula de la columna de débito)
=SUMIF(subtotals!$G:$G,$A4,subtotals!C:C)
Finalmente, agrega las fórmulas en las columnas F y G:
=B4-D4 =C4-E4
Selecciona las columnas D-G y agrega filtros:
Vemos que hay algunas diferencias en la columna de débito:
Ahora hemos identificado números de cuenta para investigar las diferencias.
Comparar subtotales con GL formateado comenzando desde los subtotales
Ten en cuenta que si hay números de cuenta en la lista de subtotales del GL original que no están en el GL formateado, esos no aparecerán aquí pero podrían explicar las diferencias. Para detectarlos, puedes ejecutar agregar a la hoja de cálculo de subtotales fórmulas SUMIF() que hagan referencia al GL formateado para obtener los totales de cuenta del GL formateado:
¿Algo más en tu mente? Chatea con nosotros o envía una solicitud para más ayuda.