Conciliar órdenes de compra y recepciones en Excel antes de aprobar una factura

Respuesta rápida

Para conciliar órdenes de compra y recepciones en Excel, cruza OC y SKU, valida los campos numéricos y compara lo recibido con lo facturado y autorizado. Cuenta las recepciones existentes y revisa registros sin orden: una suma de cero no demuestra que exista una recepción. El ejemplo usa comparaciones directas para evitar coincidencias por comodines y bloquea cantidades vacías, cero o texto.

Cómo conciliar órdenes de compra y recepciones en Excel

Imagen de apoyo: Cómo conciliar órdenes de compra y recepciones en Excel
Imagen de apoyo: Cómo conciliar órdenes de compra y recepciones en Excel

Antes de aprobar una factura, comprueba que sus líneas estén autorizadas, tengan recepción y respeten cantidades y precios. No basta con obtener dos sumas iguales: ambas podrían ser cero por documentos inexistentes o datos faltantes.

Este caso ficticio supone una línea por OC-SKU, una factura en revisión, precios positivos y recepciones que no se hayan facturado anteriormente. Excluye impuestos, descuentos, devoluciones y cargos adicionales. Si un SKU aparece varias veces en una orden, necesitas un identificador de partida y otro diseño de cruce.

Usaremos Excel para Microsoft 365, Excel 2024 o Excel 2021 de escritorio para Windows, con funciones en español y ; como separador de argumentos. Si tu configuración regional exige ,, sustituye los separadores de las fórmulas; revisa también el separador decimal. Los datos iniciales contienen enteros para evitar esa ambigüedad. (learn.microsoft.com)

1. Carga tres tablas sin alterar los tipos

Imagen de apoyo: 1. Carga tres tablas sin alterar los tipos
Imagen de apoyo: 1. Carga tres tablas sin alterar los tipos

Crea las hojas Ordenes, Recepciones y Factura. Pega cada bloque tabulado en A1 de su hoja: contiene tabulaciones entre campos y saltos de línea entre registros. Comprueba que los encabezados ocupen columnas distintas. Selecciona cada rango, pulsa Ctrl+T, confirma los encabezados y asigna el nombre correspondiente en Diseño de tabla. (support.microsoft.com)

Ordenes

OC	SKU	CantidadOrdenada	PrecioUnitario
OC-104	A-10	10	25
OC-104	B-20	5	40
OC-105	A-10	4	25

Recepciones

OC	SKU	CantidadRecibida
OC-104	A-10	6
OC-104	A-10	4
OC-104	B-20	4
OC-105	A-10	4

Factura

OC	SKU	CantidadFacturada	PrecioFacturado
OC-104	A-10	10	25
OC-104	B-20	5	40
OC-105	A-10	4	25

OC y SKU deben conservarse como texto; cantidades y precios, como números. Preformatea las columnas de códigos como Texto si contienen ceros iniciales. Para introducir un vacío real, selecciona la celda y pulsa Supr: no escribas espacios, guiones ni etiquetas. Una fórmula que devuelve "" no representa una celda realmente vacía. ESNUMERO permite distinguir un número de un texto numérico y rechaza ambos tipos de ausencia en los controles siguientes. (support.microsoft.com)

2. Valida cada registro antes de sumar

Imagen de apoyo: 2. Valida cada registro antes de sumar
Imagen de apoyo: 2. Valida cada registro antes de sumar

Añade una columna Valida en las tres tablas. En Ordenes:

=SI(Y([@OC]<>"";[@SKU]<>"";ESNUMERO([@CantidadOrdenada]);[@CantidadOrdenada]>0;ESNUMERO([@PrecioUnitario]);[@PrecioUnitario]>0);1;0)

En Recepciones:

=SI(Y([@OC]<>"";[@SKU]<>"";ESNUMERO([@CantidadRecibida]);[@CantidadRecibida]>0);1;0)

En Factura:

=SI(Y([@OC]<>"";[@SKU]<>"";ESNUMERO([@CantidadFacturada]);[@CantidadFacturada]>0;ESNUMERO([@PrecioFacturado]);[@PrecioFacturado]>0);1;0)

SI devuelve 1 únicamente cuando Y confirma todas las condiciones. Así, una cantidad vacía, cero o escrita como texto devuelve 0; un precio faltante también invalida la línea. Esta regla excluye deliberadamente operaciones gratuitas y devoluciones. Las columnas calculadas utilizan [@...] para evaluar la fila actual. (support.microsoft.com)

3. Cuenta coincidencias sin interpretar comodines

Imagen de apoyo: 3. Cuenta coincidencias sin interpretar comodines
Imagen de apoyo: 3. Cuenta coincidencias sin interpretar comodines

CONTAR.SI.CONJUNTO interpreta * y ? como comodines. Por eso no usaremos los códigos directamente como criterios de esa función. Los cruces siguientes emplean comparaciones con = dentro de SUMAPRODUCTO, siguiendo el patrón de comparación y agregación documentado por Microsoft: aquí esos caracteres forman parte del código, no de un patrón de búsqueda. (support.microsoft.com)

Añade ControlOrden en las tres tablas, con esta misma fórmula:

=SUMAPRODUCTO((Ordenes[OC]=[@OC])*(Ordenes[SKU]=[@SKU]))

El resultado cuenta órdenes coincidentes: 0 indica ausencia; 1, correspondencia única; más de 1, duplicidad. Esto también permite detectar filas huérfanas en recepción o factura.

En Ordenes, añade cuatro columnas, en este orden:

NRecepciones

=SUMAPRODUCTO((Recepciones[OC]=[@OC])*(Recepciones[SKU]=[@SKU]))

RecepcionesValidas

=SUMAPRODUCTO((Recepciones[OC]=[@OC])*(Recepciones[SKU]=[@SKU]);Recepciones[Valida])

NFacturas

=SUMAPRODUCTO((Factura[OC]=[@OC])*(Factura[SKU]=[@SKU]))

FacturasValidas

=SUMAPRODUCTO((Factura[OC]=[@OC])*(Factura[SKU]=[@SKU]);Factura[Valida])

Estas fórmulas derivan del producto de condiciones y, cuando corresponde, del indicador numérico Valida. Conserva los nombres exactos para que funcionen las referencias posteriores. (support.microsoft.com)

4. Agrega cantidades y verifica el estado

Imagen de apoyo: 4. Agrega cantidades y verifica el estado
Imagen de apoyo: 4. Agrega cantidades y verifica el estado

En Ordenes, crea CantidadRecibida, CantidadFacturada y PrecioFacturado:

=SUMAPRODUCTO((Recepciones[OC]=[@OC])*(Recepciones[SKU]=[@SKU]);Recepciones[CantidadRecibida])
=SUMAPRODUCTO((Factura[OC]=[@OC])*(Factura[SKU]=[@SKU]);Factura[CantidadFacturada])
=SUMAPRODUCTO((Factura[OC]=[@OC])*(Factura[SKU]=[@SKU]);Factura[PrecioFacturado])

El precio agregado solo es utilizable cuando existe una línea de factura válida. Las entradas no numéricas se tratan como cero en estos argumentos de matriz; por eso las sumas nunca sustituyen los controles de existencia y validez. (support.microsoft.com)

Añade Estado:

=SI(Y([@Valida]=1;[@ControlOrden]=1;[@NRecepciones]>0;[@RecepcionesValidas]=[@NRecepciones];[@NFacturas]=1;[@FacturasValidas]=1);SI(Y([@CantidadRecibida]<=[@CantidadOrdenada];[@CantidadFacturada]=[@CantidadRecibida];[@PrecioFacturado]=[@PrecioUnitario]);"COINCIDE";"REVISAR");"REVISAR")

La primera prueba exige orden única, recepción existente, recepciones válidas y factura única válida. La segunda compara cantidades y precio. Esta combinación de SI y Y devuelve COINCIDE solo si se cumplen ambas etapas. Una entrega parcial puede coincidir si la factura cubre exactamente lo recibido sin superar la orden. (support.microsoft.com)

Resultados esperados y pruebas de control

Los siguientes resultados se deducen de las fórmulas; no corresponden a una ejecución declarada de Excel. (support.microsoft.com)

OC/SKURecibidaFacturadaEstado esperado
OC-104/A-101010COINCIDE
OC-104/B-2045REVISAR
OC-105/A-1044COINCIDE

Haz estas pruebas por separado y restaura los datos después:

  • Sin recepción: elimina la fila de recepción OC-105/A-10 y cambia su cantidad facturada a 0. NRecepciones=0, ambas cantidades agregadas quedan en 0 y Estado=REVISAR.
  • Cantidad vacía: en esa misma factura, borra la cantidad con Supr. Valida=0 y el estado sigue en REVISAR.
  • Código con asterisco: cambia únicamente el SKU de la primera orden a A-*. Sus conteos de recepción y factura quedan en 0: no cruza con A-10 y devuelve REVISAR.
  • Factura huérfana: añade OC-999, Z-99, cantidad 1 y precio 25 a Factura. Su ControlOrden devuelve 0. (support.microsoft.com)

Errores frecuentes antes de aprobar

  • Revisar solo órdenes: examina también ControlOrden y Valida en las tablas fuente.
  • Sumar precios duplicados: NFacturas debe ser 1; dos líneas requieren revisión, no un precio consolidado.
  • Ocultar errores de Excel: si aparece un error en datos o fórmulas, detén la revisión. No lo reemplaces por cero para conseguir una coincidencia.
  • Confundir recepción con pago pendiente: verifica que las unidades no hayan respaldado facturas anteriores; este ejemplo no controla ese historial.
  • Aceptar códigos inconsistentes: establece una convención de captura y revisa espacios, ceros iniciales y partidas repetidas.

Como procedimiento empresarial, almacén confirma los comprobantes, compras resuelve diferencias de autorización o precio y cuentas por pagar conserva la evidencia. COINCIDE es un control previo, no una aprobación automática ni una prueba contra recepciones duplicadas.

Para profundizar, consulta cómo comprobar números con ESNUMERO y la plantilla de factura en Excel.

Preguntas frecuentes

¿Una cantidad facturada vacía puede producir COINCIDE?

No con estos controles: ESNUMERO rechaza la cantidad vacía y Valida devuelve 0. Estado exige una factura válida y al menos una recepción válida, aunque las sumas de cantidades sean iguales a cero.

¿Puedo utilizar códigos que contengan asteriscos o signos de interrogación?

Sí en los cruces propuestos, porque comparan los códigos mediante el operador = dentro de SUMAPRODUCTO. No se utilizan esos códigos como criterios con comodines de CONTAR.SI.CONJUNTO.

¿Cómo se revisan las entregas parciales?

Registra cada entrega como una fila de Recepciones. El ejemplo permite COINCIDE si todas esas filas son válidas, la cantidad facturada iguala la recibida y la recepción acumulada no supera la orden. Debes comprobar por separado que las recepciones no se hayan facturado antes.

¿Qué hago si el mismo SKU aparece dos veces en una orden?

El cruce OC-SKU deja de identificar una partida única. Agrega un identificador de línea a órdenes, recepciones y factura, y adapta todos los cruces para incluirlo. No uses este ejemplo sin esa modificación.

Fuentes y referencias

  • Usar referencias estructuradas con las tablas de Excel
  • Función SUMAPRODUCTO
  • Funciones ES
  • Usar SI con las funciones Y, O y NO en Excel
  • Crear fórmulas condicionales
  • Función CONTAR.SI.CONJUNTO
  • Errores de fórmula cuando el separador de listas no está configurado correctamente

Contenido preparado con asistencia de IA y revisado por el equipo editorial de ExcelFull.