Resumen
Esta guía es para ayudarle a comprender las mejores formas de solucionar un desequilibrio en los resultados de MindBridge cuando su archivo de libro mayor está formateado y puede procesarse fácilmente en MindBridge.
Este es un recorrido más avanzado y completo. Por lo tanto, si aún no lo ha hecho, le recomendamos que pruebe las soluciones que se encuentran en Importes o Débitos y Créditos que no se equilibran al importar el libro mayor en MindBridge .
Solución
Considere el siguiente extracto de un archivo de libro mayor:
En primer lugar, revisaremos brevemente cómo formatear este archivo. Si lo prefiere, puede saltarse esta parte y pasar directamente a Solución de problemas del desequilibrio.
Los problemas:
- Este archivo se agrupa por cuenta: solo una fila contiene información de la cuenta (filas 6, 10, 169), seguida de todas las transacciones para esa fila (filas 12-167, 171 en adelante) (infringe el requisito de fila 1).
- El número de cuenta suele estar en la columna B (filas 10 y 169), pero, a veces, está en la columna A (fila 6) (infringe el requisito de columna 1) (Sin embargo, la descripción de la cuenta siempre está en la columna E para las filas de la cuenta)
- Hay filas totales/subtotales innecesarias (9, 168) y filas en blanco debajo de las filas del número de cuenta (infringe el requisito general 2)
La solución (se necesita formateo):
- Desagrupar por cuenta (arrastrar la información de la cuenta a cada fila de transacción)
- Excluya filas extrañas totales/subtotales/en blanco (7-9, 11, 168-170) y filas extrañas (filas 1-4) por encima de la fila del encabezado (fila 5)
Agregamos una columna de "Identificador de cuenta" al final del archivo para extraer el número de cuenta de las filas de la cuenta y arrastrarlo hacia abajo:
Identificador de cuenta: Celda L6 e inferior
=IF(NOT(ISERROR(SEARCH("????-??-??",B6))),B6,IF(NOT(ISERROR(SEARCH("????-??-??",A6))),A6,K5))
-
SEARCH("????-??-??",B6), SEARCH("????-??-??",A6)encontrar la posición en la cadena B6 o A6 donde una cadena coincide con la expresión "????-??-??" aparece. Si no se encuentra, se devuelve un error (#VALUE!) - la expresión "????-??-??" utiliza el carácter comodín ? que coincide con cualquier carácter individual. Entonces, "????-??-??" significa cuatro caracteres individuales, seguidos de -, seguidos de dos caracteres individuales, seguidos de -, seguidos de dos caracteres individuales. El objetivo es comprobar si se encuentra un número de cuenta en la celda (B6 o A6)
- El mensaje NOT(ISERROR()) alrededor de estas expresiones SEARCH() simplemente indica si las celdas B6 o A6 contienen números de cuenta (es decir, se encontró el patrón "????-??-??"). Entonces,
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 que si A6 es un número de cuenta, devuelve A6 y, finalmente, el K5 al final significa que, de lo contrario, devuelve K5 (el identificador de cuenta calculado en la fila encima, es decir, lo que arrastra hacia abajo los números de cuenta encontrados)
=IF(OR(NOT(ISERROR(SEARCH("????-??-??",B6))),NOT(ISERROR(SEARCH("????-??-??",A6)))),E6,L5)
Filtrar para mantener solo filas de transacciones
Con los filtros activados, comprobamos la columna B (Fecha) y vemos que están las fechas de 2020 y 1900, y el resto son números de cuenta:
Resulta que en este archivo, las fechas de 1900 también son datos basura que no queremos conservar, así que también los excluiremos en el siguiente paso.
Copiar y pegar valores en un libro de trabajo nuevo
Teniendo en cuenta que todas las fechas de 2020 son las filas de transacciones, seleccione solo las fechas de 2020:
A continuación, seleccione 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.
Resolución de problemas del desequilibrio
Ahora tenemos un archivo con un formato agradable que tiene este aspecto:
¡Pero descubrimos que no está equilibrado porque el Debe no es igual al Haber!
Extraer subtotales de cuenta
Afortunadamente, observamos que este archivo tiene subtotales de cuentas (filas que son similares a 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 cuentas es que Fecha (columna B) está en blanco, y Cambio neto (columna I) es un número (es decir, no está en blanco).
De nuevo en el archivo de libro mayor original, donde añadimos la fórmula a la columna K para extraer y arrastrar el identificador de cuenta hacia abajo, y con los filtros aún activados, filtramos en Fecha (columna B) en blanco y la columna I en blanco:
Observe que las columnas reales para estas filas de subtotal de cuentas no se corresponden con los encabezados de columna: tienen un formato ligeramente diferente de las filas de transacción, ya que una columna se desplaza a la izquierda. El saldo inicial está en la columna E y el saldo final está en la columna I. Pero como no estamos formateando los subtotales para importarlos a MindBridge, no resulta un problema. Solo necesitamos copiar y pegar valores de las columnas que necesitamos (columnas E-I y K) en una nueva hoja de trabajo en el cuaderno con el libro mayor con formato que utilizaremos para solucionar el desequilibrio.
Asignaremos un nombre a los subtotales de esta hoja de trabajo y cambiaremos el nombre de los encabezados para evitar confusiones:
Tabla dinámica de los datos de libro mayor formateados
A continuación, creamos una tabla dinámica en los datos del libro mayor con formato en una nueva hoja de trabajo, sumando el Debe y el Haber por número de cuenta:
- Seleccione todos los datos con formato (puede comenzar en la parte superior y pulsar Ctrl-Mayús-Fin para llegar al final del archivo y ajustar la posición según sea necesario), vaya a Insertar -> Tablas -> Tabla dinámica
La Tabla/Rango: indicará automáticamente el rango que seleccionó para los datos (en nuestro caso, hemos nombrado la hoja de trabajo con los datos de libro mayor con formato "formattedGL" y los datos van de A1 a G16519)
- Mantenga seleccionado el botón de opción “Nueva hoja de trabajo” (que debería ser por defecto) y haga clic en Aceptar.
Esto le llevará a una nueva hoja de trabajo con este aspecto:
Queremos totalizar Debe y Haber por identificador de cuenta.
- Desplácese hacia abajo por la lista NOMBRE DE CAMPO y haga clic y arrastre el "identificador de cuenta" a la sección Filas:
- A continuación, arrastre Debe y Haber a la sección Valores:
- Ahora su tabla Pivote debe tener este aspecto:
Buscar y comparar con subtotales de cuenta
Ahora queremos comparar estos totales de cuenta con los que hemos extraído del libro mayor original y guardado en la hoja de trabajo desubtotales:
Subtotales de cuentas: fórmula de columna Debe
=SUMIF(subtotals!$G:$G,$A4,subtotals!B:B)
Recuerde que en la hoja de trabajo de subtotales, la columna G tiene el identificador de cuenta y las columnas B y C los importes de Debe y Haber:
La columna A de la hoja de trabajo de la tabla dinámica tiene los identificador de cuenta. Por lo tanto, esta fórmula SUMIF obtiene los totales del Debe y Haber de la hoja de cálculo de subtotales para cada identificador de cuenta en la tabla dinámica.
subtotales de cuentas: fórmula de columna Haber (solo copiar y pegar la fórmula de columna Debe)
=SUMIF(subtotals!$G:$G,$A4,subtotals!C:C)
Por último, añada las fórmulas en las columnas F y G:
=B4-D4 =C4-E4
Seleccione las columnas D-G y añada filtros:
Vemos que hay algunas diferencias en la columna Debe:
Ahora, hemos identificado números de cuenta para indagar sobre las diferencias.
Comparación de subtotales con libro mayor formateado a partir de los subtotales
Tenga en cuenta que, si hay números de cuenta en la lista de subtotales del libro mayor original que no aparecen en el libro mayor formateado, estos no aparecerán aquí, pero podrían tener en cuenta las diferencias. Para detectarlos, puede ejecutar las fórmulas SUMIF() de la hoja de cálculo de subtotales que hacen referencia al libro mayor formateado para extraer los totales de la cuenta del libro mayor formateado:
¿Tiene alguna otra idea en mente? Converse en línea con nosotros o envíe una solicitud de asistencia adicional.