Los iconos son una manera gráfica de mostrar el resultado de una evaluación de unos datos. En esta ocasión vamos a aprender cómo mostrar celdas con iconos según formato condicional. Será el resultado de un análisis según otras columnas con datos de partida. Aprovecharemos también para mostrar cómo utilizar las funciones “=ALEATORIO()” y “=AHORA()”. También haremos un pequeño cálculo con horas. 4 tutoriales en 1., ¿alguien da más?
Datos iniciales
Partimos de unos datos recogidos y que nos muestran un registro de horas a las cuales ocurre una incidencia:
Función ALEATORIO
Para obtener datos hemos utilizado la función para generar números aleatorios. Esta función tiene 2 formatos:
- “=ALEATORIO()” . Genera un número decimal entre 0 y 1.
- “=ALEATORIO.ENTRE(inferior;superior). Genera un numero decimal entre 2 números.
Si quieres saber más sobre esta función puedes hacerlo mirando el tutorial de la función aleatorio.
NOTA: Una vez introducida la fórmula, hay que tener en cuenta que si tenemos activada la opción de cálculo automático, cada vez que modifiquemos cualquier celda o pulsemos F9, los valores van a cambiar. Para saber el modo de cálculo que tenemos hay que ir al menú Fórmulas / Opciones para el cálculo:
Si no queremos que los números cambien, podemos hacer clic en “Manual”. Pero deberemos recordar que no hará ningún cálculo en las celdas hasta que pulsemos F9.
Otra manera de “congelar” los valores es hacer copiar-pegar en la celda donde hemos introducido la fórmula e indicar que queremos pegar sólo los valores:
Generar datos aleatorios con formato de hora
Hay que hacerlo en 4 pasos:
- Generar números aleatorios para las horas. Se aplica la función“=ALEATORIO(0;23)”
- Generar números aleatorios para los minutos. Se aplica la función“=ALEATORIO(0;59)”
- Se unen los dos números formando una cadena de texto y añadiendo “:” que los separe:CONCATENAR(ALEATORIO.ENTRE(0;23);”:”;ALEATORIO.ENTRE(0;59))
- Se convierte la cadena de texto en hora con minutos: =HORANUMERO(CONCATENAR(ALEATORIO.ENTRE(0;23);”:”;ALEATORIO.ENTRE(0;59)))
Ahora ya tenemos celdas con valores en horas y minutos:
A los valores le hemos dado formato de hora y minutos con AM/PM:
Función AHORA
Ahora necesitamos saber cuánto tiempo ha transcurrido desde la hora de la incidencia hasta el momento del cálculo. La función “=AHORA()” nos dará la fecha actual con la hora y minutos:
si le damos el formato de hora (ver cuadro anterior):
Cálculo del tiempo transcurrido
Para saber cuánto tiempo ha transcurrido, sólo deberemos restarle la hora de la incidencia (por ejemplo celda A6) a la hora actual (celda B4):
¿Por qué delante de la B y del 4 está el símbolo $? Para aprender a bloquear referencias en Excel te recomiendo que le eches un vistazo al artículo: ¿Para qué sirve el símbolo $ en una fórmula?
En el caso del tutorial que estamos haciendo se puede dar el caso que la hora de la incidencia sea posterior a la hora de cálculo. Para evitar estos casos ponemos un condicional:
=SI(A6>$B$4;"";$B$4-A6)
En este caso cuando la hora aleatoria generada sea posterior a la hora de cálculo, nos devolverá una cadena vacía.
En una situación real no debería darse el caso de incidencia posterior a la hora de cálculo, excepto si se trata de cálculos en diferentes días, o si se han producido incidencias posteriormente a la hora que queremos hacer el cálculo (lógico, ¿no?)
Así ya tenemos nuestros datos a analizar, respecto la hora 17:20:
Formato condicional con iconos
Ahora debemos aplicar el formato condicional con los iconos.
En este caso según requerimiento de un subscriptor, aplicaremos las siguientes condiciones:
- rojo los que tengan 10 minutos o más
- amarillo los que tengan entre 5 y 10 min.
- verde los incidentes que lleven menos de 5min.
ahora ya tenemos el resultado que buscábamos:
También podemos expresar el resultado sin indicar el tiempo transcurrido, sólo con los iconos. Debemos indicarlo cuando hemos aplicado la regla:
y el resultado final:
Bonus track: descarga este ejemplo
Aquí os dejo el archivo que hemos utilizado como base para este tutorial. Regálanos un “like” en Facebook y podrás descargarlo totalmente gratis.
[sociallocker]
[/sociallocker]