Control de ventas en Excel: registro, resumen por mes, producto y canal
Cómo llevar el control de ventas en Excel: tabla de registro, catálogo de productos, resumen mensual por producto, canal y vendedor, ticket medio, comparación con el año anterior, comisiones y cobros pendientes.
Respuesta rápida: para llevar el control de ventas en Excel necesitas una tabla de registro con una fila por línea de venta (fecha, número de pedido o factura, cliente, producto, canal, vendedor, unidades, precio y estado de cobro), un catálogo de productos con su coste y una hoja de resumen que calcule con SUMAR.SI.CONJUNTO las ventas por mes, producto, canal y vendedor. Con esos datos obtienes el ticket medio, el margen, la comparación con el año anterior, las comisiones y los cobros pendientes.
A continuación tienes el diseño paso a paso. Funciona igual para una tienda física, un comercio online o un negocio de servicios. Los datos del ejemplo son ficticios.
Las cuatro hojas del archivo
| Hoja | Para qué sirve |
|---|---|
| Ventas | Registro de cada línea vendida. Es la única hoja donde se introducen datos a diario. |
| Catálogo | Código, producto, categoría, precio de venta y coste unitario. |
| Listas | Canales (tienda, web, marketplace, teléfono), vendedores y estados de cobro. |
| Resumen | Totales por mes, producto, canal y vendedor, indicadores y gráficos. |
Paso 1: tabla de registro de ventas
Convierte el rango en tabla (Ctrl+T) y llámala Ventas. Columnas recomendadas:
| Col. | Campo | Cómo se rellena |
|---|---|---|
| A | Fecha | Manual |
| B | Nº pedido / factura | Manual |
| C | Cliente | Manual o lista |
| D | Código producto | Lista desplegable desde Catálogo |
| E | Producto | =BUSCARX([@Código];Catálogo[Código];Catálogo[Producto];"") |
| F | Canal | Lista desplegable |
| G | Vendedor | Lista desplegable |
| H | Unidades | Manual |
| I | Precio unitario sin IVA | =BUSCARX([@Código];Catálogo[Código];Catálogo[Precio];0), sobrescribible |
| J | Descuento % | Manual |
| K | Importe neto | =[@Unidades]*[@Precio]*(1-[@Descuento]) |
| L | Coste | =[@Unidades]*BUSCARX([@Código];Catálogo[Código];Catálogo[Coste];0) |
| M | Margen | =[@Importe neto]-[@Coste] |
| N | Mes | =FIN.MES([@Fecha];-1)+1 |
| O | Estado de cobro | Lista: Pendiente / Cobrado / Devuelto |
| P | Fecha de vencimiento | Manual o fecha + días de pago |
Guarda los importes sin IVA; el impuesto se calcula aparte en la factura. Si un pedido tiene varios productos, usa una fila por producto con el mismo número de pedido: así podrás analizar tanto el pedido como el producto.
Paso 2: resumen mensual
En la hoja Resumen, escribe los meses en la fila 4 (B4 = 01/01/2026, C4 = 01/02/2026…) y en la columna A los productos, canales o vendedores. La fórmula base:
- Ventas de un producto en un mes:
=SUMAR.SI.CONJUNTO(Ventas[Importe neto];Ventas[Producto];$A5;Ventas[Mes];B$4) - Ventas de un canal: igual, cambiando
Ventas[Producto]porVentas[Canal]. - Unidades vendidas: suma la columna Unidades en lugar del Importe.
- Total del mes:
=SUMAR.SI.CONJUNTO(Ventas[Importe neto];Ventas[Mes];B$4)
Si prefieres no escribir fórmulas, una tabla dinámica con Mes en columnas, Producto en filas e Importe neto en valores da el mismo resultado. Las fórmulas tienen la ventaja de que el diseño del informe no cambia cuando se actualizan los datos.
Ejemplo de resumen por canal
| Canal | Enero (€) | Febrero (€) | Variación | Peso feb. |
|---|---|---|---|---|
| Tienda física | 12.400 | 11.800 | −4,8 % | 39,3 % |
| Web propia | 9.600 | 11.200 | +16,7 % | 37,3 % |
| Marketplace | 6.200 | 7.000 | +12,9 % | 23,3 % |
| Total | 28.200 | 30.000 | +6,4 % | 100 % |
Variación: =SI.ERROR(C5/B5-1;""). Peso: =SI.ERROR(C5/C$8;""). En el ejemplo la tienda física baja, pero el total crece gracias a los canales online; sin el desglose, ese cambio de mezcla no se vería.
Paso 3: indicadores clave
| Indicador | Fórmula | Qué indica |
|---|---|---|
| Nº de pedidos del mes | Columna auxiliar Q en Ventas: =SI(CONTAR.SI.CONJUNTO($B$2:B2;B2)=1;1;0), luego SUMAR.SI.CONJUNTO por mes |
Cuenta cada pedido una sola vez aunque tenga varias líneas |
| Ticket medio | =SI.ERROR(Ventas mes / Pedidos mes;"") |
Gasto medio por pedido |
| Margen bruto % | =SI.ERROR(Margen mes / Ventas mes;"") |
Rentabilidad antes de gastos generales |
| Ventas acumuladas | =SUMA($B$9:B9) |
Avance hacia el objetivo anual |
| Cumplimiento de objetivo | =SI.ERROR(Ventas mes / Objetivo mes;"") |
% del objetivo alcanzado |
Ejemplo de ticket medio: 30.000 € en 600 pedidos = 50 € por pedido. Si al mes siguiente las ventas suben a 31.500 € con 700 pedidos, el ticket baja a 45 €: hay más clientes, pero cada uno compra menos. Para analizar si los descuentos explican esa bajada, compara el margen bruto de ambos meses. La guía para calcular el margen de beneficio explica cómo interpretarlo.
Paso 4: comparación con el año anterior
Las ventas de muchos negocios son estacionales, así que comparar febrero con enero puede engañar. Añade una fila con el mismo mes del año anterior:
=SUMAR.SI.CONJUNTO(Ventas[Importe neto];Ventas[Mes];FECHA.MES(B$4;-12))
y la variación interanual =SI.ERROR(B9/B10-1;""). Para que funcione, la tabla Ventas debe conservar al menos 13 meses de historial; si el archivo crece demasiado, archiva los años antiguos en otro libro y deja solo los totales mensuales.
Paso 5: comisiones de vendedores
Si pagas comisiones, define la regla en la hoja Listas: por ejemplo, 3 % sobre ventas netas cobradas y 5 % sobre el exceso de un objetivo mensual de 10.000 €. Fórmula para un vendedor con ventas cobradas en B20:
=MIN(B20;10000)*3%+MAX(0;B20-10000)*5%
Con 14.000 € cobrados: 10.000 × 3 % + 4.000 × 5 % = 300 + 200 = 500 €. Calcular la comisión sobre lo cobrado y no sobre lo facturado evita pagar por ventas que luego se devuelven o no se cobran.
Paso 6: cobros pendientes
Las ventas a crédito (empresas, distribuidores) deben vigilarse aparte:
- Importe pendiente total:
=SUMAR.SI.CONJUNTO(Ventas[Importe neto];Ventas[Estado];"Pendiente") - Pendiente vencido:
=SUMAR.SI.CONJUNTO(Ventas[Importe neto];Ventas[Estado];"Pendiente";Ventas[Vencimiento];"<"&HOY()) - Días de retraso por línea:
=SI(Y([@Estado]="Pendiente";HOY()>[@Vencimiento]);HOY()-[@Vencimiento];0)
Recuerda que el importe neto no incluye IVA; lo que el cliente debe es el total de la factura con IVA. Si necesitas el importe exacto, añade una columna con el total facturado. La plantilla de factura en Excel muestra cómo numerar y calcular cada factura.
Relación con el inventario
Cada venta reduce el stock. Si llevas el inventario en Excel, las salidas de cada producto pueden calcularse sumando las unidades de la tabla Ventas: =SUMAR.SI.CONJUNTO(Ventas[Unidades];Ventas[Código];A2). Así evitas registrar dos veces la misma operación y el stock se actualiza solo.
Errores frecuentes
- Escribir los productos a mano: “Camiseta azul M” y “camiseta azul m” se cuentan como productos distintos. Usa códigos y listas desplegables.
- Mezclar importes con y sin IVA en la misma columna.
- Celdas combinadas y filas de subtotales dentro de la tabla de registro: rompen las fórmulas y las tablas dinámicas.
- No registrar devoluciones: anótalas como una fila con unidades negativas y el mismo número de pedido.
- Un archivo por mes: impide comparar periodos. Mejor una sola tabla con todas las fechas.
Preguntas frecuentes
¿Excel o un programa de TPV?
Si tu TPV o tu tienda online ya registra las ventas, exporta los datos a la hoja Ventas cada semana y usa Excel para el análisis. Excel es útil para cruzar canales y fuentes que el programa no une.
¿Cuántas filas aguanta Excel?
Más de un millón por hoja, pero con muchas decenas de miles de filas y fórmulas SUMAR.SI.CONJUNTO el archivo se vuelve lento. En ese caso, conviene usar tablas dinámicas o Power Query.
¿Funciona en Google Sheets?
Sí, con SUMAR.SI.CONJUNTO, BUSCARX y FIN.MES. Sustituye las referencias de tabla por rangos con nombre.
Encontrarás más recursos en Venta online e inventario.