Calcula el Tiempo Promedio de Finalización de Cursos con Excel Avanzado en RRHH

En el análisis de datos, especialmente en departamentos de Recursos Humanos o en la gestión de proyectos, una de las métricas más valiosas es el tiempo promedio que se tarda en completar una tarea o formación. La fórmula que analizamos aquí es una solución robusta y elegante en Excel para calcular el tiempo promedio de finalización de un curso específico, filtrando por múltiples criterios como el departamento, el estado del curso y el nombre del mismo, todo ello sin necesidad de crear columnas auxiliares.

Utilizando la versatilidad de la función SUMAPRODUCTO, podemos realizar operaciones de suma y producto sobre matrices de datos en una sola celda, permitiéndonos construir cálculos condicionales complejos que de otro modo requerirían fórmulas más largas o la manipulación de la tabla de datos original.

Sintaxis

=SUMAPRODUCTO((rango_fecha_fin - rango_fecha_inicio) * (rango_nombre_curso="nombre_curso") * (rango_estado_curso="Completado") * (rango_departamento="departamento")) / MAX(1;SUMAPRODUCTO((rango_nombre_curso="nombre_curso") * (rango_estado_curso="Completado") * (rango_departamento="departamento")))

Para entender su funcionamiento, es mejor descomponer la fórmula en sus dos partes principales: el numerador (que suma la duración total en días) y el denominador (que cuenta el número de cursos que cumplen los criterios).

• Numerador
  SUMAPRODUCTO((rango_fecha_fin - rango_fecha_inicio) * ...)

  Esta parte calcula la suma total de los días de duración de todos los cursos que cumplen con las condiciones especificadas.

  • (rango_fecha_fin - rango_fecha_inicio): Crea una matriz de valores numéricos donde cada valor es la duración en días de un curso (la resta de la fecha de fin y la de inicio).
  • (rango_nombre_curso="nombre_curso"): Crea una matriz de valores lógicos (VERDADERO/FALSO). Será VERDADERO si el curso en la fila coincide con el nombre buscado, y FALSO si no.
  • (rango_estado_curso="Completado"): Similar al anterior, crea una matriz lógica para el estado del curso.
  • (rango_departamento="departamento"): Otra matriz lógica para el departamento.
  • Al multiplicar estas matrices, Excel convierte los valores lógicos VERDADERO a 1 y FALSO a 0. El resultado de la multiplicación para una fila solo será la duración en días si todas las condiciones son VERDADERO (1 * 1 * 1 * duración). Si alguna condición es FALSO (0), el resultado para esa fila será 0. Finalmente, SUMAPRODUCTO suma todos estos resultados, dándonos el total de días.
• Denominador
  MAX(1; SUMAPRODUCTO(...))

  Esta parte cuenta cuántos cursos cumplen con todos los criterios. El uso de MAX es una medida de seguridad para evitar errores de división por cero.

  • SUMAPRODUCTO((rango_nombre_curso...)*(...)): Aquí, multiplicamos las mismas matrices condicionales de antes. El resultado es un recuento de cuántas filas cumplen todas las condiciones (la suma de los «1» resultantes).
  • MAX(1; ...): Si el SUMAPRODUCTO interior devuelve 0 (ningún curso cumple los criterios), MAX(1; 0) devolverá 1. Esto evita el error #¡DIV/0!, haciendo que el resultado de la fórmula sea 0 en lugar de un error.

Ejemplos

Imaginemos que el departamento de RRHH tiene la siguiente tabla para el seguimiento de la formación de los empleados. Quieren calcular el tiempo promedio que tardan los empleados de su propio departamento («RRHH») en finalizar el curso de «Excel Avanzado».

Ejemplo 1 Calcular el tiempo promedio de finalización para el curso «Excel Avanzado» dentro del departamento de «RRHH».

=SUMAPRODUCTO((E2:E8-D2:D8)*(C2:C8="Excel Avanzado")*(F2:F8="Completado")*(B2:B8="RRHH"))/MAX(1;SUMAPRODUCTO((C2:C8="Excel Avanzado")*(F2:F8="Completado")*(B2:B8="RRHH")))

Resultado esperado: 12

Desglose del cálculo:

• Ana Torres (Fila 2): Cumple todos los criterios. Duración: 15/02/2023 – 01/02/2023 = 14 días.
• Carla Solis (Fila 4): No cumple el criterio de «Completado». Se ignora.
• Sofia Marin (Fila 6): Cumple todos los criterios. Duración: 25/02/2023 – 15/02/2023 = 10 días.
• El numerador suma las duraciones: 14 + 10 = 24 días.
• El denominador cuenta el número de cursos que cumplen: 2 (Ana y Sofia).
• El resultado final es 24 / 2 = 12 días.

Ejemplo 2 Calcular el tiempo promedio de finalización de CUALQUIER curso completado por el departamento de «Ventas».

=SUMAPRODUCTO((E2:E8-D2:D8)*(F2:F8="Completado")*(B2:B8="Ventas"))/MAX(1;SUMAPRODUCTO((F2:F8="Completado")*(B2:B8="Ventas")))

Resultado esperado: 6.5

Desglose del cálculo:

• Luis Paez (Fila 3): Cumple. Duración: 5 días.
• Pedro Nieto (Fila 7): Cumple. Duración: 8 días.
• Suma de duraciones: 5 + 8 = 13 días.
• Número de cursos: 2.
• Resultado: 13 / 2 = 6.5 días.

Aplicaciones Prácticas

• Análisis de FormaciónIdentificar qué cursos son más largos o difíciles para ciertos departamentos, ayudando a optimizar los programas de capacitación.
• Gestión de ProyectosCalcular el tiempo medio para completar fases de un proyecto, filtrando por equipo, tipo de tarea o cliente, para mejorar las estimaciones futuras.
• Evaluación de RendimientoMedir la eficiencia de los empleados o equipos en la realización de tareas recurrentes.
• Análisis OperativoDeterminar el tiempo de ciclo promedio para un proceso de producción, filtrando por línea de montaje, turno o producto.

Observaciones

Formato de celda: Las columnas de fecha deben tener un formato de fecha válido en Excel para que la resta funcione correctamente. El resultado de la fórmula es un número de días. Puedes necesitar ajustar el formato de la celda de resultado a «General» o «Número».

Naturaleza de Matriz: SUMAPRODUCTO es una de las pocas funciones en Excel que maneja matrices de forma nativa, por lo que no es necesario introducirla como una fórmula de matriz con `Ctrl+Shift+Enter`, incluso en versiones antiguas de Excel.

Rendimiento: En hojas de cálculo con decenas de miles de filas, el cálculo puede volverse lento. En tales casos, las alternativas como Power Query o una tabla dinámica pueden ser más eficientes.

Errores comunes

• #¡VALOR! Este error suele aparecer si alguno de los rangos de fecha contiene texto o está vacío, o si los rangos utilizados en la fórmula no tienen exactamente el mismo tamaño (mismo número de filas).
• Resultado incorrecto A menudo causado por errores tipográficos en los criterios de texto (p. ej., «RRHH » con un espacio extra) o por rangos de celda mal definidos. Revisa que los textos coincidan exactamente y que los rangos cubran todos tus datos.

Alternativas

• =PROMEDIO.SI.CONJUNTO(rango_duracion; rango_criterio1; criterio1; ...)

  Esta es una alternativa más directa y a menudo más rápida, pero requiere una columna auxiliar. Primero, crearías una columna «Duración» con la fórmula =E2-D2 y la arrastrarías hacia abajo. Luego, la función PROMEDIO.SI.CONJUNTO podría calcular el promedio de esa nueva columna basándose en los mismos criterios.

• Tablas Dinámicas

  Una Tabla Dinámica es una excelente alternativa sin fórmulas. Puedes arrastrar «Departamento», «Curso» y «Estado» a los filtros, y luego añadir un campo calculado para la duración (Fecha Fin – Fecha Inicio). Finalmente, configura el campo de valores para que muestre el «Promedio» de esa duración.