Área miembros
¡Inscribirme!
Medidas estadísticas, Excel y creación de un cuadro de mando: guía completa paso a paso

1) Introducción: de los datos a las decisiones

Vivimos rodeados de datos: ventas, clientes, campañas, stocks, tiempos de entrega, incidencias… Sin embargo, disponer de datos no garantiza tomar buenas decisiones. Lo que marca la diferencia es transformar los datos en información y la información en acciones. Ahí entran en juego tres piezas:

  1. Medidas estadísticas (descriptivas) para entender comportamientos, niveles y variabilidad.
  2. Excel, como herramienta accesible que permite calcular, consolidar y visualizar.
  3. El cuadro de mando (dashboard o CMI), que convierte métricas en indicadores útiles para dirigir el negocio.

Este artículo recorre ese camino de forma práctica:

  • Primero, asentamos los conceptos clave de estadística descriptiva.
  • Después, los llevamos a Excel, con fórmulas y ejemplos.
  • Por último, diseñamos un cuadro de mando en Excel con KPIs accionables, segmentadores y gráficos dinámicos.

El objetivo es que puedas implantar un cuadro de mando funcional que se actualice a golpe de “Actualizar todo” y que te ayude a responder, en minutos, preguntas que hoy te llevan horas.

2) Fundamentos de estadística descriptiva (lo justo y necesario)

La estadística descriptiva resume un conjunto de observaciones mediante medidas que ayudan a comprender:

  • Tendencia central: ¿alrededor de qué valor se agrupan los datos?
  • Dispersión: ¿cuánto se separan los datos entre sí?
  • Posición: ¿cómo se distribuyen por percentiles o cuartiles?
  • Forma: ¿hay sesgo (asimetría) o concentración de valores (curtosis)?

2.1 Tipos de datos y escalas de medición

  • Cualitativos (nominales/ordinales): categoría sin orden (nominal) u ordenable (ordinal). Ej.: canal (“Tienda”, “Online”), satisfacción (1–5).
  • Cuantitativos (intervalo/razón): números con sentido de distancia; en razón, además hay cero absoluto. Ej.: ingreso (€), unidades vendidas.

Saber qué escala es cada variable evita errores: la media de un código postal no tiene sentido, pero la moda sí (el más frecuente).

2.2 Calidad del dato

Antes de medir, depura: duplicados, fechas incoherentes, nulos, outliers absurdos (ej.: márgenes > 1000%). Excel te permite detectar esto con Filtros, Formato condicional, Quitar duplicados y Power Query.

3) Medidas de tendencia central

3.1 Media (promedio)

La media indica el valor medio aritmético. En Excel:

  • =PROMEDIO(rango)
    Uso: ticket medio, coste medio, tiempo medio de entrega.

3.2 Mediana

Es el valor central cuando ordenamos de menor a mayor. En Excel:

  • =MEDIANA(rango)
    Utilidad: cuando hay outliers, la mediana refleja mejor el “centro” real que la media. Ej.: ventas con picos por pedidos gigantes.

3.3 Moda

Es el valor que más se repite. En Excel:

  • =MODA.UNO(rango) (valor con mayor frecuencia)
    Aplicación: producto más vendido, canal más utilizado, turno más habitual.

Regla práctica:

  • Datos sesgados o con atípicos → prioriza mediana.
  • Datos “limpios” y simétricos → media es muy informativa.
  • Para categorías → moda.

4) Medidas de dispersión

4.1 Rango

Diferencia entre máximo y mínimo. En Excel:

  • =MAX(rango)-MIN(rango)
    Rápido, pero sensible a outliers.

4.2 Varianza y desviación estándar

  • Varianza: promedio de las desviaciones cuadráticas respecto a la media.
  • Desviación estándar: raíz cuadrada de la varianza; expresa la dispersión en las mismas unidades que el dato.

En Excel (muestra):

  • Varianza muestral: =VAR.S(rango)
  • Desviación estándar muestral: =DESVEST.M(rango)

En Excel (población):

  • =VAR.P(rango) y =DESVEST.P(rango)

Cuándo usar M (muestral) vs P (poblacional):

  • Si trabajas con una muestra de un universo mayor (p.ej., un mes representativo) → .M
  • Si usas la población completa del periodo que analizas (todas las ventas del año) → .P

4.3 Coeficiente de variación (CV)

Dispersión relativa:

  • CV = DESVEST / MEDIA
    En Excel:
=DESVEST.M(rango)/PROMEDIO(rango)

Interpretación: dos productos con medias distintas pueden compararse mejor con CV. Menor CV = comportamiento más estable.

5) Medidas de posición

5.1 Cuartiles y percentiles

  • Cuartil 1 (Q1): 25% de los datos por debajo.
  • Mediana (Q2): 50%.
  • Cuartil 3 (Q3): 75%.
  • Percentiles: posición exacta p (0–100).

En Excel:

  • =CUARTIL.INC(rango;1) para Q1, ;2 para Q2, ;3 para Q3.
  • =PERCENTIL.INC(rango;0,9) para P90 (90%).

Uso comercial: detectar top 10% clientes por facturación (≥ P90), evaluar tiempos de entrega (P95 como objetivo de SLA), etc.

6) Medidas de forma: asimetría y curtosis

  • Asimetría (sesgo): si la cola larga está a la derecha (positiva), la media > mediana.
  • Curtosis: concentración alrededor de la media; alta curtosis implica picos pronunciados (más valores cerca de la media y colas más pesadas).

En Excel:

  • Asimetría: =COEFICIENTE.ASIMETRIA(rango)
  • Curtosis: =CURTOSIS(rango)

Aplicación: entender si las ventas tienen picos extremos (campañas, días especiales) y ajustar expectativas, stocks y personal.

7) Excel como laboratorio estadístico

7.1 Funciones imprescindibles (localizadas en español)

  • Tendencia central: PROMEDIO, MEDIANA, MODA.UNO
  • Dispersión: VAR.S, VAR.P, DESVEST.M, DESVEST.P, MAX, MIN
  • Posición: PERCENTIL.INC, CUARTIL.INC, K.ESIMO.MAYOR, K.ESIMO.MENOR
  • Forma: COEFICIENTE.ASIMETRIA, CURTOSIS
  • Contar y condiciones: CONTAR.SI, CONTAR.SI.CONJUNTO, SUMAR.SI, SUMAR.SI.CONJUNTO
  • Fecha/hora: HOY, MES, AÑO, FECHA, FIN.MES
  • Auxiliares modernas: UNIQUE (Excel 365 en español: UNIQUE), ORDENAR (SORT), FILTRAR (FILTER) — si tienes Microsoft 365.

7.2 Tablas y nombres

Convierte los datos en Tabla de Excel (Ctrl+T). Ventajas:

  • Fórmulas que crecen automáticamente.
  • Referencias estructuradas (Tabla1[Importe]).
  • Mejor integración con Tablas dinámicas.

7.3 Power Query (Obtener y transformar)

Para importar CSV/ERP, limpiar y automatizar la actualización.

  • Comandos: Datos → Obtener y transformar datos.
  • Beneficio: un clic para actualizar todo el cuadro de mando.

7.4 Tablas dinámicas, segmentadores y gráficos

  • Tablas dinámicas: agregan por dimensiones (Fecha, Cliente, Vendedor) y medidas (Importe, Unidades).
  • Segmentadores: filtros visuales por Canal, Vendedor, Región.
  • Cronología: filtro por fecha.
  • Gráficos dinámicos: se conectan a la tabla dinámica (y a sus segmentadores).

8) Del análisis a la decisión: lectura práctica

Ejemplos típicos:

  • Ticket medio vs dispersión:
    • Ticket medio = 58 €. CV alto (0,65) → la venta es muy volátil. Acción: paquetes y bundles para estabilizar el importe por operación.
  • Top clientes (P90):
    • P90 clientes = 1.200 €/mes → los clientes por encima concentran 40% de la facturación. Acción: crear plan de fidelización VIP.
  • Tiempo de entrega (P95):
    • Promedio 48 h, P95 = 96 h. Acción: fijar SLA a 72 h y atacar causas de los casos extremos (logística, validación de pagos).

9) Cuadro de mando: concepto y utilidad

Un cuadro de mando sintetiza la estrategia y el desempeño mediante KPIs visibles y accionables. Debe responder a “¿vamos bien?” y “¿dónde actuar?”. Tres principios:

  1. Relevancia: pocos KPIs, ligados a objetivos (ventas, margen, rotación).
  2. Claridad: semáforos, variaciones, contexto histórico.
  3. Accionabilidad: cada KPI debe tener propietario y plan de acción si cae por debajo del umbral.

9.1 KPIs típicos por área

  • Comerciales
    • Ventas totales (mensuales/diarias)
    • Ticket medio
    • Unidades por ticket
    • Tasa de conversión (pedidos / oportunidades)
    • Clientes nuevos / repetición
  • Finanzas/Precio
    • Margen bruto %
    • Descuento medio %
    • Devoluciones %
  • Operaciones/Servicio
    • Tiempo medio de entrega (P50, P95)
    • Nivel de servicio (% entregas dentro de SLA)
    • Stockout (quiebres)
  • Marketing
    • CAC (coste de adquisición)
    • ROAS/ROI campaña
    • Leads cualificados

10) Diseño de un cuadro de mando en Excel (paso a paso)

10.1 Estructura del archivo

Crea un libro con hojas:

  1. Datos (o varias: Ventas, Clientes, Productos…).
  2. Dimensiones (calendario, catálogos si los necesitas).
  3. Medidas (columnas calculadas, tablas auxiliares).
  4. Pivots (tablas y gráficos dinámicos que alimentan el dashboard).
  5. Dashboard (diseño final con KPIs y segmentadores).
  6. PQ (consultas de Power Query, opcional).

10.2 Modelo de datos de ejemplo (Ventas)

Columnas mínimas:

  • Fecha (dd/mm/aaaa)
  • PedidoID
  • Cliente
  • Vendedor
  • Canal (Tienda, Online, Mayorista)
  • Producto
  • Categoría
  • Unidades
  • PrecioUnitario
  • Descuento% (0–1)
  • CosteUnitario

Columnas calculadas:

  • Importe bruto: =Unidades*PrecioUnitario
  • Importe neto: =Importe bruto*(1-Descuento%)
  • Coste total: =Unidades*CosteUnitario
  • Margen €: =Importe neto - Coste total
  • Margen %: =SI(Importe neto=0;0;Margen €/Importe neto)

Convierte a Tabla (Ctrl+T). Nómbrala tblVentas.

10.3 Hoja DimCalendario (opcional pero potente)

Crea un calendario con columnas: Fecha, Año, Mes, MesNombre, Trimestre, AñoMes (ej.: 2025-09). Puedes generarlo con fórmulas o Power Query. Úsalo para agrupar por periodos.

10.4 KPIs básicos (fórmulas)

En Medidas crea celdas con:

  • Ventas totales (mes en curso)
    • Con SUMAR.SI.CONJUNTO filtrando por Mes/Año.
    • Ej.: si tienes AñoMes en una columna auxiliar:
    =SUMAR.SI.CONJUNTO(tblVentas[Importe neto];tblVentas[AñoMes];$B$1) donde B1 contiene el AñoMes seleccionado (ej. “2025-09”).
  • Margen % (mes) =SUMAR.SI.CONJUNTO(tblVentas[Margen €];tblVentas[AñoMes];$B$1) / SUMAR.SI.CONJUNTO(tblVentas[Importe neto];tblVentas[AñoMes];$B$1)
  • Ticket medio (mes)
    • Si “PedidoID” identifica tickets individuales:
    =SUMA(SI.UNICOS(FILTRAR(tblVentas[PedidoID];tblVentas[AñoMes]=$B$1))>0) Más sencillo: crea Tabla dinámica por PedidoID para sumar Importe neto y contar pedidos; luego Ventas/ NºPedidos.
  • Clientes nuevos (mes)
    • Marca la primera compra de cada cliente con MIN.SI.CONJUNTO sobre Fecha, y compara con el mes actual:
    =SI(MIN.SI.CONJUNTO(tblVentas[Fecha];tblVentas[Cliente];[@Cliente])>=FechaInicioMes;1;0) Luego suma esa columna.

Consejo: Si usas Microsoft 365, UNIQUE, FILTER, LET y LAMBDA simplifican mucho estos cálculos.

10.5 Tablas dinámicas y gráficos

  1. Inserta Tabla dinámica desde tblVentas.
  2. Arrastra Año, Mes a Filas o utiliza Cronología.
  3. En Valores coloca Importe neto, Margen €, Unidades.
  4. Inserta Gráfico dinámico (columnas apiladas, líneas, barras).
  5. Agrega Segmentadores para Canal, Vendedor, Categoría. Conéctalos a todas las tablas/gráficos dinámicos (clic derecho → Conexiones de informes).

10.6 Diseño del dashboard (layout recomendado)

En hoja Dashboard, usa un lienzo limpio (fondo blanco o gris muy claro):

  • Fila superior (KPIs grandes):
    • Ventas Mes, Variación vs mes anterior (% y flecha ↑/↓), Margen %, Ticket medio, % Cumplimiento Objetivo.
    • Formato de tarjetas (rectángulos con bordes redondeados).
    • Usa formato condicional (semaforización).
  • Zona izquierda (tendencias):
    • Gráfico de líneas “Ventas por mes (últimos 12)”.
    • Gráfico de columnas “Margen % por mes”.
  • Zona derecha (top y concentración):
    • Barras horizontales “Top 10 productos por ventas”.
    • Barras “Top 10 clientes”.
  • Pie (calidad de servicio):
    • Boxplot (si te animas) o columnas “Entrega dentro de SLA % por semana”.
    • Tabla resumen “Devoluciones % por categoría”.
  • Panel de filtros arriba o a la izquierda**:** Segmentadores: Año, Mes, Canal, Vendedor, Categoría; Cronología de fechas.

Tips visuales:

  • Máximo una paleta neutra + color de acento para alertas.
  • Etiquetas claras, sin ruido (evita fondos vistosos).
  • Mostrar variación vs periodo anterior: =(ValorActual/ValorAnterior)-1.

11) Buenas prácticas y errores habituales

Buenas prácticas

  1. Menos es más: 5–7 KPIs clave, el resto en “detalle”.
  2. Contexto: siempre compara vs objetivo y vs histórico (YoY/ MoM).
  3. Automatiza: importa con Power Query, usa Tablas y Tablas dinámicas para refrescar todo.
  4. Documenta: en una hoja “Diccionario de KPIs” explica fórmula, fuente, objetivo y responsable.
  5. Control de calidad: validaciones (listas desplegables, control de fechas, formato condicional para nulos/ceros extraños).

Errores comunes

  • Mezclar fechas como texto (rompe cronologías). Solución: =FECHA(AÑO(A1);MES(A1);DIA(A1)) o Power Query (Detección de tipo).
  • No distinguir margen € vs %.
  • Usar media con datos muy sesgados (mejor mediana).
  • Hacer gráficos sin escala uniforme o sin ejes claros.
  • No conectar segmentadores a todos los objetos.

12) Caso práctico completo (empresa ficticia)

12.1 Contexto

Empresa “Comerzia” vende pequeños electrodomésticos en Tienda y Online. Quiere un dashboard mensual con:

  • Ventas totales y por canal
  • Margen %
  • Ticket medio
  • Top 10 productos y clientes
  • Nivel de servicio (entregas en ≤72 h)
  • Devoluciones %

12.2 Estructura de datos (hoja Datos)

Columnas (ejemplo):

  • Fecha, PedidoID, Cliente, Región, Vendedor, Canal, Producto, Categoría, Unidades, PrecioUnitario, Descuento%, CosteUnitario, FechaEntrega, Devuelto (0/1)

Cálculos:

  • Importe bruto: =Unidades*PrecioUnitario
  • Importe neto: =Importe bruto*(1-Descuento%)
  • Coste total: =Unidades*CosteUnitario
  • Margen €: =Importe neto-Coste total
  • Margen %: =SI(Importe neto=0;0;Margen €/Importe neto)
  • Plazo entrega (horas): =SIERROR((FechaEntrega-Fecha)*24;"" )
  • SLA cumplido (≤72 h): =SI(PlazoEntrega<=72;1;0)

Convierte el rango a Tabla tblVentas.

12.3 Tabla dinámica de KPIs

Crea varias tablas dinámicas:

  1. KPI Ventas y Margen por mes
    • Filas: Mes/Año (o AñoMes).
    • Valores: Suma de Importe neto, Suma de Margen €, Promedio de Margen %.
    • Gráficos: línea para ventas, columnas para margen %.
  2. Ticket medio
    • Tabla dinámica con PedidoID en Filas y Importe neto en Valores (Suma).
    • Calcula Nº Pedidos (Recuento de PedidoID).
    • Ticket medio = Ventas totales / Nº Pedidos.
    • Alternativa: medidas con CONTAR.SI.CONJUNTO y SUMAR.SI.CONJUNTO.
  3. Top 10 productos
    • Filas: Producto.
    • Valores: Suma de Importe neto.
    • Ordenar descendente y filtrar Top 10.
    • Gráfico de barras horizontales.
  4. Top 10 clientes (igual que productos).
  5. Nivel de servicio (SLA)
    • Valores: Promedio de SLA cumplido (formateado como %).
    • Por mes y por canal (usa Canal en Columnas).
    • Objetivo: ≥ 92%.
  6. Devoluciones %
    • Promedio de Devuelto (o Suma de Devuelto / Nº pedidos).
    • Por categoría.

Segmentadores: Canal, Vendedor, Categoría, Región. Cronología: Fecha.

12.4 Medidas estadísticas para interpretar

En hoja Análisis:

  • Tendencia central:
    • Venta por pedido: =PROMEDIO(rangoImportePorPedido) y =MEDIANA(rangoImportePorPedido).
    • Si media ≫ mediana → hay pedidos excepcionales; reporta también mediana en el dashboard.
  • Dispersión:
    • =DESVEST.M(rangoImportePorPedido) y CV (=DESVEST.M/ PROMEDIO).
    • Si CV alto por canal, revisa el surtido o las promociones.
  • Posición (P90 clientes):
    • Calcula ventas por cliente (Tabla dinámica o SUMAR.SI.CONJUNTO).
    • =PERCENTIL.INC(rangoVentasCliente;0,9) → umbral de “clientes oro”.
    • Crea una vista “Clientes Oro” con FILTRAR.
  • Servicio (P95 entrega):
    • =PERCENTIL.INC(rangoPlazoEntrega;0,95) → fija SLA realista.

Conclusión accionable del caso (ejemplo de lectura):

  • Ventas crecen +12% intermensual, pero margen % cae 1,8 pp en Online (descuento medio sube al 13%).
  • Ticket medio: media 58 €, mediana 43 € → hay picos; potencia packs 45–55 €.
  • P95 entrega en Online: 96 h → ajustar promesa de entrega a 72–96 h y atacar causas.
  • Top 10 clientes concentran 41% ventas → programa VIP (envío gratuito y ofertas exclusivas).

13) Cómo actualizarlo “a prueba de lunes por la mañana”

  1. Conecta la hoja Datos a tus fuentes con Power Query (CSV, ERP, Google Sheets…).
  2. Limpia y tipa campos (fechas, números, nulos).
  3. Carga como Tabla (o al Modelo de datos si usas Power Pivot).
  4. En el Dashboard, pulsa Datos → Actualizar todo.
  5. Revisa una hoja “Control” con: fecha de última actualización, nº de filas, checks de coherencia (ventas ≠ 0, fechas válidas).

14) Plantillas de fórmulas útiles (copiar/pegar y adaptar)

  • Variación vs mes anterior (%)
    Si B3 = valor actual y B2 = anterior: =SI.ERROR(B3/B2-1;0)
  • Objetivo cumplido (Semáforo) =SI(valor>=objetivo;"OK";"ALERTA") Aplica formato condicional con reglas “Texto que contiene”.
  • AñoMes (texto) =TEXTO([@Fecha];"aaaa-mm")
  • Clientes nuevos (marca 1/0)
    Supón FechaPrimeraCompra: =MIN.SI.CONJUNTO(tblVentas[Fecha];tblVentas[Cliente];[@Cliente]) Luego: =SI([@FechaPrimeraCompra]>=InicioMes;1;0)

15) Visualización: patrones que funcionan

  • Líneas para tendencias de tiempo.
  • Barras horizontales para rankings (Top 10).
  • Columnas para comparativas por categoría o canal.
  • Tarjetas KPI para cifras principales (con variación y flecha ↑/↓).
  • Boxplot (si hace falta analizar dispersión/atípicos) — se puede simular con 5 segmentos (mín, Q1, mediana, Q3, máx).

Reglas de oro:

  • Un gráfico → una idea.
  • Ejes claramente etiquetados y unidades visibles.
  • Evita 3D y efectos que distorsionen.
  • Etiquetas de datos solo cuando aporten (no satures).

16) Checklist para pasar de “tablas sueltas” a “cuadro de mando”

  1. Datos en Tablas (no rangos sueltos).
  2. Dimensiones (calendario, catálogo) coherentes.
  3. KPIs definidos con fórmula y dueño.
  4. Tablas dinámicas base y segmentadores conectados.
  5. Gráficos limpios y legibles.
  6. Hoja Dashboard con layout claro (KPI → Tendencia → Detalle).
  7. Botón/Indicador de actualización y hoja “Diccionario de KPIs”.
  8. Seguridad: protege celdas de fórmulas, oculta hojas técnicas si compartes.

17) Extensiones avanzadas (cuando quieras subir de nivel)

  • Power Pivot / Modelo de datos: relaciones entre tablas (Ventas–Clientes–Productos–Calendario) y medidas DAX (más rápido y robusto).
  • Medidas DAX:
    • Ventas: SUM(Ventas[Importe neto])
    • Ventas LY (año anterior): CALCULATE([Ventas];SAMEPERIODLASTYEAR(Calendario[Fecha]))
    • Variación %: DIVIDE([Ventas]-[Ventas LY];[Ventas LY])
  • Macros (VBA): para formatear fechas, exportar dashboard a PDF, enviar resumen por correo.
  • Power Automate / Office Scripts: refrescar y distribuir informes.

18) Conclusión: del “dato crudo” a la ventaja competitiva

  • Las medidas estadísticas te dan lectura objetiva: centro (media/mediana), variabilidad (desviación/coef. variación), posición (percentiles) y forma (asimetría/curtosis).
  • Excel convierte esos conceptos en práctica diaria: tablas, fórmulas, dinámicas, segmentadores, gráficos.
  • Un cuadro de mando bien diseñado alinea a la organización, reduce la fricción de análisis y acelera decisiones. Menos intuición, más evidencia; menos “creo”, más “sé”.