Clase 04 · Módulo 04
Conciliar dos listas y buscar por tramos
Conciliar dos listas y buscar por tramos
Qué logras en esta clase
Comparas dos listas y dices con certeza qué calza, qué falta y qué sobra. Además asignas tramos sin escribir un SI dentro de otro.
- Preparas la clave antes de cruzar.
- Concilias en los dos sentidos y cuadras los totales.
- Buscas por tramos con una tabla que cualquiera puede editar.
02
Qué es conciliar
Conciliar es responder tres preguntas: qué está en ambas listas, qué falta en una y qué falta en la otra.
03
El caso de trabajo
Una distribuidora tiene dos listas del mismo mes:
| Lista | De dónde sale | Clave |
|---|---|---|
| Facturas | Sistema de ventas | N.º de factura |
| Pagos | Cartola del banco | N.º de factura informado |
Deberían calzar una a una. En la práctica hay facturas sin pago, pagos sin factura y montos que no coinciden. El trabajo es encontrar esos tres grupos.
04
Primero la clave
Toda conciliación falla o resulta según la clave. Antes de escribir una fórmula:
- Elige el dato que identifica cada registro en ambas listas.
- Deja el mismo tipo en las dos: todo texto o todo número.
- Quita espacios sobrantes y unifica mayúsculas.
- Comprueba que no se repita dentro de cada lista.
Las herramientas para los pasos 2 y 3 se vieron en el módulo 1, clase 5.
05
Detectar claves repetidas
En una columna nueva de cada lista:
=CONTAR.SI($A$2:$A$500;A2)Cuenta cuántas veces aparece la clave de esa fila en su propia lista. Todo lo que dé más de 1 está repetido.
06
Saber si existe en la otra lista
En la hoja Facturas:
=CONTAR.SI(Pagos[Factura];A2)| Resultado | Significa |
|---|---|
| 0 | La factura no tiene pago |
| 1 | Calza con un pago |
| 2 o más | Hay varios pagos para esa factura |
Para leerlo de un vistazo, envuélvelo: =SI(CONTAR.SI(Pagos[Factura];A2)=0;"Sin pago";"OK").
07
La mitad que se olvida
Una conciliación va en los dos sentidos. Revisar solo una lista deja sin ver lo que sobra en la otra.
08
El recorrido completo
Facturas contra Pagos muestra lo que falta cobrar. Pagos contra Facturas muestra dinero recibido que no sabes a qué corresponde. Son dos columnas distintas, una en cada hoja.
09
Traer el monto para compararlo
Que la clave exista no garantiza que el monto sea el mismo. En Facturas, columna D:
=BUSCARX(A2;Pagos[Factura];Pagos[Monto];"No está")Y en la columna E, la diferencia solo cuando hay un número que restar:
=SI(ESNUMERO(D2);C2-D2;"")Filtra E distinto de cero: esas son las facturas pagadas por otro monto.
10
Cuando un pago no es uno a uno
=SUMAR.SI(Pagos[Factura];A2;Pagos[Monto])Devuelve el total pagado por esa factura, haya uno o cinco abonos, y 0 si no hay ninguno.
11
Listar solo lo que falta
Con Excel 2021 o posterior, una sola fórmula arma la lista de facturas sin pago:
=FILTRAR(Facturas[Numero];CONTAR.SI(Pagos[Factura];Facturas[Numero])=0;"Nada pendiente")Escríbela fuera de las tablas, en una hoja de resultados. Se actualiza sola cuando cambian las listas.
En versiones anteriores, usa la columna de CONTAR.SI y un filtro normal sobre el 0.
12
Verlo con color
El formato condicional tiene una regla para resaltar valores duplicados dentro de un rango. Busca esa regla en el menú de formato condicional de tu aplicación.
Sirve para una revisión rápida de una sola lista. Para comparar dos listas es mejor la columna con CONTAR.SI: se puede filtrar, contar y dejar como evidencia.
13
El cuadre final
Una conciliación sin totales de control no está terminada.
| Control | Debe cumplirse |
|---|---|
| Conteo | Calzadas + sin pago = total de facturas |
| Monto | Pagado + pendiente + diferencias = total facturado |
| Sentido inverso | Pagos calzados + pagos sin factura = total de pagos |
Si alguno no cuadra, hay una clave repetida o una fila que quedó fuera de un rango.
14
Qué herramienta según el tamaño
| Situación | Conviene |
|---|---|
| Unas cientos de filas, una vez | CONTAR.SI y BUSCARX |
| Quieres la lista de faltantes viva | FILTRAR |
| Se repite cada mes con archivos nuevos | Power Query (módulo 11) |
| Claves sucias o que no calzan exactas | Claude sobre una copia, y verificas |
Si usas a Claude, pídele los conteos de cada grupo y compáralos con los tuyos, como se vio en el módulo 2, clase 7.
15
Segunda parte: buscar por tramos
Un tramo no se busca por igualdad: se busca en qué escalón cae el valor.
16
La tabla de tramos
Una tienda paga comisión según la venta mensual de cada vendedor. La tabla lleva el límite inferior de cada tramo, de menor a mayor:
| Desde | Tasa |
|---|---|
| 0 | 0 % |
| 1.000.000 | 2 % |
| 3.000.000 | 3,5 % |
| 6.000.000 | 5 % |
Una venta de 4.200.000 no aparece en la columna Desde. Cae en el tramo que empieza en 3.000.000.
17
La fórmula con BUSCARX
=BUSCARX(B2;Tramos[Desde];Tramos[Tasa];;-1)El quinto argumento, -1, pide coincidencia exacta o el valor menor siguiente: el mayor límite que no supera la venta. El cuarto queda vacío, por eso hay dos ; seguidos.
Comisión: =B2*BUSCARX(B2;Tramos[Desde];Tramos[Tasa];;-1).
18
En versiones anteriores a 2021
=BUSCARV(B2;$F$2:$G$5;2;VERDADERO)Este es el uso legítimo de la coincidencia aproximada que se anunció en la clase 1.
19
Los bordes se prueban
- Un valor menor que el primer límite da error: por eso el primer tramo parte en 0, o en el mínimo posible.
- Un valor igual a un límite cae en el tramo que empieza ahí.
- Un valor negativo, como una devolución, queda fuera de la tabla.
20
Por qué no un SI dentro de otro
| Tabla de tramos | SI anidados o SI.CONJUNTO | |
|---|---|---|
| Cambiar una tasa | Editas una celda | Editas todas las fórmulas |
| Agregar un tramo | Agregas una fila | Reescribes la fórmula |
| Revisar | Se lee en la tabla | Hay que abrir la fórmula |
Con dos tramos que nunca cambian, un SI basta. Desde tres en adelante, o si las tasas se revisan cada año, tabla.
21
Tasa plana o tasa por tramo
Antes de armar la planilla, confirma con quien definió la regla cuál de los dos es. Con 4.200.000 los resultados difieren mucho: compáralos a mano.
22
Encargar una conciliación a Claude
Lo que más cambia el resultado es describir la clave y lo que esperas ver.
| Pedido | Qué recibes |
|---|---|
| «Compara estas dos listas» | Una comparación con criterios que no elegiste |
| «Cruza por número de factura, que es texto. Quiero tres grupos: calzadas, sin pago y pagos sin factura, con conteo y suma de cada uno» | Grupos que puedes cuadrar contra tus totales |
23
Lo que te llevas
- La clave se limpia y se revisa por duplicados antes de cruzar.
- CONTAR.SI responde si existe; BUSCARX trae el dato; SUMAR.SI resuelve varios abonos.
- Se concilia en ambos sentidos y se cierra con totales de control.
- Los tramos van en una tabla con el límite inferior ordenado, y se buscan con modo
-1. - Antes de calcular, confirma si la tasa es plana o marginal.
24
Hazlo ahora
Quince minutos:
- Crea 10 facturas y una lista de pagos: deja una factura sin pago, un pago sin factura, un monto distinto y una factura con dos abonos.
- Marca cada factura con CONTAR.SI y trae el total pagado con SUMAR.SI.
- Haz el cruce inverso en la hoja de pagos.
- Comprueba los tres controles del cuadre.
- Arma la tabla de comisiones y prueba los cuatro valores de borde.
Clase terminada
Sigue con la próxima.
Fuentes · revisadas el 18 de noviembre de 2026
- Función BUSCARX: modos de coincidencia (soporte de Microsoft)support.microsoft.com ↗
- Función BUSCARV: coincidencia aproximada (soporte de Microsoft)support.microsoft.com ↗
- Corregir un error #N/A (soporte de Microsoft)support.microsoft.com ↗
- Fórmulas de matriz dinámica y comportamiento de matriz desbordada (soporte de Microsoft)support.microsoft.com ↗
- Lista de funciones de Hojas de cálculo de Google (ayuda de Google)support.google.com ↗