Contabilidad · Excel
Conciliación bancaria en Excel: cómo hacerla con fórmulas y cuándo deja de alcanzar
Un método concreto para cruzar el extracto bancario con el libro mayor en una planilla, el truco para los importes repetidos y las señales de que el volumen ya pide otra herramienta.
En resumen
Para conciliar en Excel, poné el extracto y el libro mayor en dos hojas con la misma estructura, creá una clave por movimiento —importe más número de aparición— y buscá cada clave en la otra hoja con BUSCARX. Lo que devuelve "Sin par" es lo que tenés que explicar.
Si todavía no tenés claro qué es una partida conciliatoria o cómo se arma el cuadro final, empezá por la guía de conciliación bancaria paso a paso. Acá nos concentramos en la parte mecánica: el cruce en la planilla.
Las fórmulas están escritas para Excel en español con configuración regional de Argentina, donde los argumentos se separan con punto y coma.
1. Prepará las dos hojas con la misma estructura
Exportá el extracto del home banking y el mayor de la cuenta banco de tu sistema contable. Pegá cada uno en una hoja —Extracto y Mayor— con estas columnas:
| Columna | Contenido |
|---|---|
| A · Fecha | Fecha del movimiento, como fecha real de Excel. |
| B · Concepto | Descripción tal como viene del banco o del sistema. |
| C · Importe | Un solo número con signo: positivo si entra plata, negativo si sale. |
| D · Clave | La calculamos en el paso 2. |
El signo es lo que más se equivoca. En el extracto, los créditos van positivos y los débitos negativos. En el mayor de la cuenta banco, el Debe es lo que entra y el Haber lo que sale, así que el importe queda:
=Debe-HaberRevisá también que los importes sean números y no texto: si al sumarlos la columna da cero, hay importes guardados como texto. Pasa mucho con extractos que usan punto como separador de miles.
2. Creá una clave que aguante importes repetidos
El error clásico es buscar solo por importe. Si en el mes hay dos transferencias de $ 50.000, las dos encuentran la misma fila del mayor y una diferencia queda escondida.
La solución es numerar las apariciones de cada importe. En D2 de las dos hojas:
=REDONDEAR(C2;2)&"|"&CONTAR.SI(C$2:C2;C2) Copiala hacia abajo. La primera transferencia de $ 50.000 queda como 50000|1, la segunda como 50000|2, y cada una busca a su propia pareja en la otra hoja.
3. Buscá cada movimiento del extracto en el mayor
En la hoja Extracto, en E2, traé el concepto del mayor que tiene la misma clave:
=BUSCARX(D2;Mayor!$D:$D;Mayor!$B:$B;"Sin par")Si tu Excel no tiene BUSCARX (versiones 2019 o anteriores), la alternativa es:
=SI.ERROR(INDICE(Mayor!$B:$B;COINCIDIR(D2;Mayor!$D:$D;0));"Sin par")4. Hacé el cruce al revés
Un movimiento puede estar en el mayor y no en el extracto: un cheque que todavía no cobraron o un depósito en tránsito. En la hoja Mayor, en E2:
=SI(CONTAR.SI(Extracto!$D:$D;D2)>0;"Conciliado";"Sin par")5. Controlá los desfasajes de fecha
Que dos importes coincidan no garantiza que sean el mismo movimiento. En la hoja Extracto, en F2, calculá cuántos días los separan:
=SI(E2="Sin par";"";ABS(A2-BUSCARX(D2;Mayor!$D:$D;Mayor!$A:$A)))Filtrá los pares con más de 3 o 5 días de diferencia y revisalos uno por uno. Muchas veces son acreditaciones demoradas; a veces, dos movimientos distintos que casualmente tienen el mismo importe.
6. Filtrá los "Sin par" y clasificalos
Con un filtro en la columna E de cada hoja vas a ver todo lo que falta explicar. Para saber cuántos son:
=CONTAR.SI(E:E;"Sin par")Clasificá cada uno: comisiones e impuestos a registrar, partidas en tránsito o errores de carga. Con eso armás el cuadro de conciliación, como en el ejemplo resuelto de la guía.
Cuándo Excel deja de alcanzar
Este método funciona bien con una cuenta y algunos cientos de movimientos. Las señales de que llegaste al límite son bastante claras:
- Cada banco exporta distinto. Rearmar columnas y signos todos los meses se vuelve la mitad del trabajo.
- Los conceptos no coinciden. El banco dice "TRF 0042" y el sistema "Pago honorarios": la fórmula no los relaciona.
- El banco agrupa movimientos. Un solo depósito por varios cobros obliga a sumar a mano.
- Varios clientes o CUITs por cierre. Una planilla por cuenta multiplica los archivos y los errores de fórmula.
- No queda registro de quién aprobó. La planilla dice el resultado, no cómo se llegó.
El mismo cruce, sin fórmulas
ConcilIA, el agente de conciliación bancaria de 7iDeas, hace los pasos 1 a 6 por vos. Subís el extracto y el mayor como los exportás, en Excel o CSV, y el mapeo de columnas se encarga de los formatos distintos.
El agente cruza fechas, importes y conceptos, concilia lo que coincide y separa las diferencias, los desfasajes y los cargos imprevistos para que tu equipo los revise. Con la aprobación de tu equipo, el reporte se envía en Excel y PDF.
Preguntas frecuentes
¿Hay una plantilla de conciliación bancaria en Excel?
Con esta guía armás la tuya: dos hojas, Extracto y Mayor, con Fecha, Concepto, Importe y Clave, más la columna de búsqueda. Es la misma estructura que usan la mayoría de las plantillas.
¿BUSCARV o BUSCARX?
BUSCARX, si tu versión la tiene: busca en cualquier dirección y permite definir el resultado cuando no encuentra nada. Con BUSCARV la clave tiene que estar a la izquierda del dato que querés traer.
¿Sirve en Google Sheets?
Sí. CONTAR.SI, SI.ERROR, INDICE y COINCIDIR funcionan igual. El separador de argumentos depende de la configuración regional de la hoja.
¿Cómo concilio cuando el banco agrupa varios cobros en un depósito?
La fórmula no va a encontrar el par. Sumá los registros del mayor que forman ese depósito, verificá que den el mismo importe y marcalos con la misma clave a mano.