Conciliación bancaria en Excel con IA: guía a fondo paso a paso
Necesitas: Excel o Google Sheets + ChatGPT, Claude o Copilot
⚠️ Importante: Esta guía es solo informativa y no es asesoría financiera ni fiscal. Confirma con tu banco, CONDUSEF, el SAT o un contador.
🆕 ¿Primera vez? Así se usa este caso (1 minuto)
- Abre una IA gratis: ChatGPT, Gemini o Grok. ¿No tienes cuenta? Ábrela aquí.
- Baja hasta "Prompt listo para copiar" y toca el botón verde Copiar prompt. (Un prompt es la instrucción que le escribes a la IA.)
- Pégalo en el chat de la IA y cambia lo que está entre [corchetes] por tus datos.
- Revisa la respuesta con la sección "Cómo verificar" antes de usarla.
Qué hace
Cruzas el estado de cuenta del banco contra tus registros (facturas cobradas y pagadas) en Excel, con fórmulas BUSCARX y SUMAR.SI, para encontrar qué movimientos no cuadran y por qué. La IA te ayuda a limpiar el archivo, escribir fórmulas y explicar diferencias.
Paso a paso
- Descarga el estado de cuenta del mes desde la banca en línea en formato CSV o Excel (casi todos los bancos en México lo ofrecen en “Movimientos” o “Estado de cuenta”). Si solo tienes PDF, copia la tabla o usa la función de convertir de tu banco.
- Crea un libro con 3 hojas: Banco, Libros (tus registros) y Conciliación.
- En la hoja Banco deja estas columnas: Fecha, Concepto, Referencia, Cargo, Abono, Saldo. Borra filas de encabezado y totales del banco.
- En la hoja Libros pon tus movimientos del mes: Fecha, Concepto, Cliente/Proveedor, UUID del CFDI (si aplica), Ingreso, Egreso.
- Agrega en ambas hojas una columna Importe con signo: en Banco
=E2-D2(abono menos cargo); en Libros=E2-F2. - Agrega una columna Llave para emparejar por importe y fecha cercana. Lo más simple:
=TEXTO(G2;"0.00")(solo importe). Si tienes muchos importes repetidos, usa importe + referencia. - En Banco, columna ¿En libros?:
=SI.ERROR(BUSCARX(H2;Libros!H:H;Libros!B:B);"NO ENCONTRADO"). Haz lo mismo al revés en Libros. - Filtra “NO ENCONTRADO” en ambas hojas: esas son tus partidas en conciliación.
- Pega esas partidas (sin nombres de clientes) en la IA con el prompt de abajo para que te ayude a clasificarlas.
- Arma la hoja Conciliación con el formato de la tabla de ejemplo y verifica que la diferencia final sea cero.
Columnas sugeridas
| Hoja | Columnas |
|---|---|
| Banco | Fecha · Concepto · Referencia · Cargo · Abono · Saldo · Importe · Llave · ¿En libros? |
| Libros | Fecha · Concepto · Cliente/Proveedor · UUID · Ingreso · Egreso · Importe · Llave · ¿En banco? |
| Conciliación | Concepto · Importe |
Ejemplo de hoja Conciliación
| Concepto | Importe |
|---|---|
| Saldo según banco al 30/09 | 48,250.00 |
| (+) Depósitos en tránsito (en libros, no en banco) | 3,480.00 |
| (−) Cheques/transferencias no cobrados | −1,200.00 |
| (±) Errores o cargos bancarios sin registrar | −116.00 |
| Saldo conciliado | 50,414.00 |
| Saldo según libros | 50,414.00 |
| Diferencia (debe ser 0) | =B6-B7 |
Las cantidades son de ejemplo.
Fórmulas útiles
=SUMAR.SI(Banco!I:I;"NO ENCONTRADO";Banco!G:G): suma de lo que está en banco pero no en libros.=CONTAR.SI(Libros!H:H;H2): si da más de 1, el importe está repetido y debes revisar a mano.=ABS(A2-BUSCARX(H2;Libros!H:H;Libros!A:A))<=3: comprueba que la fecha coincida con tolerancia de 3 días.- En Google Sheets usa
BUSCARXigual (oXLOOKUPsi está en inglés) y coma en lugar de punto y coma según tu configuración regional.
Prompt listo para copiar
Actúa como contador mexicano experto en conciliaciones bancarias y en Excel en español.
Contexto: concilio la cuenta de [BANCO] de [MES]. Estas partidas no cuadraron (sin nombres de clientes):
EN BANCO, NO EN LIBROS: [PEGA FECHA, CONCEPTO, IMPORTE]
EN LIBROS, NO EN BANCO: [PEGA FECHA, CONCEPTO, IMPORTE]
Tarea: clasifica cada partida como depósito en tránsito, cheque/transferencia pendiente, comisión o cargo bancario, error de captura, o "revisar". Explica por qué en una línea y dime qué documento debo buscar para confirmarla.
Formato: tabla con columnas Partida, Clasificación, Motivo, Documento a revisar.
No inventes datos; si no puedes saberlo, márcalo como "revisar".
Cómo verificar
- La diferencia final debe ser exactamente 0.00. Si no, revisa importes duplicados (
CONTAR.SI) y movimientos de días límite (fin de mes). - Confirma comisiones e IVA de comisiones contra el estado de cuenta oficial en PDF.
- Cada ingreso cobrado debería tener su CFDI; si cobraste en parcialidades, revisa los complementos de pago (REP).
Riesgos
- No subas el estado de cuenta completo con número de cuenta, nombres o CLABE a una IA; quita esos datos primero.
- La IA puede clasificar mal; la decisión final es del contador.
Preguntas frecuentes
¿Cada cuánto debo conciliar?
Al menos cada mes, antes de la declaración. Si tienes muchos movimientos, cada semana es más fácil.
¿Copilot en Excel puede hacerlo solo?
Copilot puede sugerir fórmulas y resaltar diferencias si tienes la licencia, pero sigue siendo necesario revisar.
Casos relacionados
- Conciliación bancaria sencilla
- Revisa tus CFDI recibidos
- Ordena XML de facturas en Excel con ayuda de IA
- Guía: cómo conciliar tu banco con IA y Excel
¿Qué sigue?
Fuente: guía original de Guía de Agentes · Las palabras subrayadas como SAT o ITIN llevan al glosario.