Conciliación contable: una sola fórmula por hoja que cuadra, descuadra y avisa

Conciliar sin columnas auxiliares

Cualquiera que haya cuadrado una conciliación en Excel conoce el patrón: una columna que agrupa, otra que busca el importe de referencia, otra que resta, otra que pinta un rótulo con el resultado. Cuatro columnas auxiliares arrastradas hasta la última fila, y la esperanza de que nadie añada registros por debajo.

El libro que compartió un miembro de la comunidad resuelve exactamente ese problema, pero con una diferencia de fondo: cada hoja se cuadra con una sola fórmula, y el resumen no recalcula nada, solo lee lo que las otras hojas ya han derramado.

El problema

El escenario es el habitual de una conciliación. Por un lado hay un mayor, que hace de fuente de verdad: un importe por código, sin repeticiones. Por otro llegan los registros parciales, troceados en varias líneas por el mismo código, porque así es como salen de la operativa real.

Hay que agrupar los parciales por código, compararlos con el importe del mayor y responder tres preguntas, no dos: qué cuadra, qué no cuadra y por cuánto, y qué códigos aparecen en los parciales pero no existen en el mayor. Esa tercera situación es la que suele romper las plantillas caseras, porque devuelve un error y contamina la columna entera.

La solución paso a paso

Toda la lógica vive dentro de un LET, que va nombrando cada paso del razonamiento antes de montar el resultado final:

`` =LET( _codEntradas; A4:A22; _codMayor; Consolidado!A4:A9; _Mparcial; B4:B22; _Mmayor; Consolidado!B4:B20; _UcodEntradas; UNICOS(_codEntradas); _VrEntradas; SUMAR.SI(_codEntradas; _UcodEntradas; _Mparcial); _Vrmayor; BUSCARX(_UcodEntradas; _codMayor; _Mmayor; "N/A"); _diferencia; SI.ERROR(_Vrmayor - _VrEntradas; "N/A"); _logica; SI(_Vrmayor="N/A"; "Código no encontrado"; SI(_VrEntradas=_Vrmayor; "Cuadra"; "Diferencia")); APILARH(_UcodEntradas; _VrEntradas; _Vrmayor; _diferencia; _logica) ) ``

Merece la pena detenerse en dos puntos.

El primero es el SUMAR.SI. No recibe un criterio suelto, recibe el vector entero de códigos únicos que acaba de calcular UNICOS. Al pasarle una matriz como criterio, devuelve un total por cada código de una vez, que es justo lo que normalmente se consigue arrastrando la fórmula hacia abajo. Es el mismo cambio de mentalidad que traen las matrices dinámicas: en lugar de escribir una fórmula por fila, se escribe una fórmula que ya piensa en vectores.

El segundo es el valor por defecto "N/A" de BUSCARX. No está para tapar un error, está para crear un estado. Los códigos que no existen en el mayor devuelven ese texto en lugar de fallar, y la capa de SI lo aprovecha para distinguir tres casos distintos donde casi todas las plantillas solo distinguen dos. El SI.ERROR de la diferencia cumple el mismo papel: si el importe del mayor es texto, la resta no cuadra y se marca como no aplicable en vez de propagar #¡VALOR!.

Al final, APILARH pega las cinco piezas (código, total de parciales, importe del mayor, diferencia y estado) en una única tabla que se derrama sola.

El resumen que no recalcula

La parte más elegante del libro es el consolidado, porque no repite ni uno solo de los cálculos anteriores:

`` =LET( _cuadran; UNIRCADENAS(", ";; FILTRAR(ELEGIRCOLS(Entradas!D4#;1); ELEGIRCOLS(Entradas!D4#;5)="Cuadra")); _brechas; SUMA(FILTRAR(ELEGIRCOLS(Entradas!D4#;4); ELEGIRCOLS(Entradas!D4#;5)="Diferencia")); APILARV(_cuadran; _brechas) ) ``

La clave es el # de Entradas!D4#. Esa almohadilla apunta al rango derramado completo, tenga el tamaño que tenga hoy. Si mañana aparecen tres códigos nuevos en los parciales, la tabla de la hoja de entradas crece sola y el resumen los recoge sin que haya que tocar una sola referencia. A partir de ahí, ELEGIRCOLS extrae la columna que interesa (la 1 son los códigos, la 4 las diferencias, la 5 el estado) y FILTRAR se queda con las filas de cada categoría.

Funciones clave

• LET: nombra cada paso intermedio, de modo que la fórmula se lee como un razonamiento y no como un paréntesis anidado interminable.
• UNICOS: extrae la lista de códigos sin repetir, que es la que marca el tamaño de todo el resultado.
• SUMAR.SI: aquí en versión matricial, con un vector de criterios en lugar de uno solo.
• BUSCARX: recupera el importe del mayor y, con su cuarto argumento, convierte la ausencia en información.
• APILARH y APILARV: montan el resultado final juntando columnas o filas ya calculadas.
• ELEGIRCOLS y FILTRAR: trocean el rango derramado de otra hoja por columna y por condición.
• UNIRCADENAS: concatena los códigos de cada categoría en una sola celda legible.

Conclusión

Lo que empezó como una petición sencilla en el grupo terminó siendo un buen ejemplo de cómo cambian las hojas de cálculo cuando se piensa en matrices: menos columnas auxiliares, menos arrastrar, y una estructura que se adapta sola cuando llegan datos nuevos. El libro incluso trae su propia hoja de contenido documentando la formulación, algo que se agradece cuando lo abres meses después.

Este tipo de intercambios son el día a día de la comunidad de Influexcel: alguien pregunta por una plantilla y acaba apareciendo una solución que enseña bastante más de lo que se pedía.

Más casos con estas funciones

• LET
• FILTRAR
• APILARH
• APILARV
• BUSCARX
• ELEGIRCOLS
• UNIRCADENAS
• UNICOS

Más contenido de Excel en InflueXcel

• FILTRAR con ELEGIR y BUSCARX para consultas cruzadas entre tablas Caso
  Interesante problema planteado por Hector sobre cómo construir una consulta dinámica que combine columnas de una tabla con datos cruzados de
• Cómo dejar una celda realmente vacía en fórmulas SI, BUSCARX y FILTRAR Caso
  Interesante problema planteado por un miembro de la comunidad: quiere que sus fórmulas devuelvan una celda realmente vacía cuando no hay res
• Consolidar múltiples hojas con APILARV, referencias 3D y FILTRAR Caso
  Fito necesita consolidar varias hojas de Excel que comparten la misma cabecera pero tienen distinto número de filas con datos de gastos de v
• Sustituir SUMAR.SI.CONJUNTO lento por MMULT y BYCOL en análisis de inventario Caso
  Un miembro desde Ecuador tiene un modelo de inventario con SUMAR.SI.CONJUNTO dentro de una fórmula LET con ELEGIRCOLS y FILTRAR. El problema
• Filtro multicriteria dinámico con LET, FILTRAR y LAMBDA Caso
  Hector comparte con la comunidad una fórmula avanzada para filtrar una tabla de productos/servicios por múltiples criterios opcionales (clav
• Funciones personalizadas en Excel: LET, LAMBDA y recursividad Tutorial
  Cómo pasar de una fórmula escrita a mano a una función propia que puedes llamar por su nombre en cualquier libro. Los tres vídeos de esta pá
• Filtrar datos por código parcial y crear listas desplegables dependientes Caso
  Rosario pone en práctica las enseñanzas de la comunidad pero se queda atascada: tiene una tabla con códigos jerárquicos (1.01, 1.02, 2.01, 2
• Filtrar una tabla por una lista de valores: una LAMBDA propia y su inversa Tutorial
  Te pasan una lista de 30 números de albarán y hay que sacar esas filas de una tabla de miles. Con el autofiltro es marcar casillas una a una
• Repetir un rango N veces según marca con MAP y CONTAR.SI Caso
  Un usuario necesita repetir un listado de locales (D2:D10) un número variable de veces para cada marca de la columna B. El número de repetic
• Proyecciones Temporales en Excel Tutorial
  Cómo utilizar las funciones TENDENCIA y PRONOSTICO.ETS para generar proyecciones