Hace un par de semanas, un lector me envió una pregunta interesante sobre cómo rastrear el "tiempo detenido" para una flota de camiones. Los camiones son rastreados por GPS, por lo que se registra una ubicación a cada hora del día para cada camión. Los datos se parecen a esto:
El desafío: ¿qué fórmula de la columna N calculará correctamente el total de horas detenidas?
He simplificado esto un poco reemplazando las coordenadas GPS reales con ubicaciones etiquetadas como AE, pero el concepto sigue siendo el mismo.
El rompecabezas
¿Durante cuántas horas se detuvo cada camión?
O, en Excel-speak:
¿Qué fórmula calculará el total de horas que se detuvo cada camión?
Por ejemplo, sabemos que Truck1 se detuvo durante 1 hora porque su ubicación se registró como "A" tanto a las 4 p.m. como a las 5 p.m.
Supuestos
- Hay 5 ubicaciones con estos nombres: A, B, C, D, E
- Un camión en el mismo lugar durante dos horas consecutivas = 1 hora detenido
¿Tienes una fórmula que lo haga?
Descargue el libro de trabajo y comparta su fórmula en los comentarios a continuación. Como ocurre con tantas cosas en Excel, ¡hay muchas formas de resolver este problema!
Respuesta (haga clic para expandir)En este caso, el versátil SUMPRODUCT es una forma elegante de resolver este problema:
=SUMPRODUCT(--(C6:K6=D6:L6))
Tenga en cuenta que los rangos C6: K6 están compensados por una columna. En esencia, estamos comparando "posiciones anteriores" con "posiciones siguientes" y contando los casos en los que la posición anterior es la misma que la siguiente.
Para los datos de la fila 6, la operación de comparación crea una matriz de valores VERDADERO FALSO:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
El doble negativo entonces coacciona los valores VERDADERO FALSO a unos y ceros, y SUMPRODUCTO simplemente la suma de la matriz, que es 1:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
