martes, 21 de enero de 2014

Un reloj literal en español para Excel.

Navegando por Internet encontré una web de Excel con temas interesantes, y entre alguno de ellos, dí con un reloj literal en inglés (diseñado por Biegert and Funk), que decidí convertir al español.
Lo que aporta de especial este ejercicio es la buena combinación entre los Nombres definidos con fórmula, las constantes matriciales y los formatos condicionales.


Veamos en primer lugar el aspecto final del reloj:

Un reloj literal en español para Excel.


Lo especial de este reloj es el control que hacemos para mostrar los textos 'especiales' que empleamos en el momento de leer una hora, expresiones del tipo: '...menos cuarto' o '... y diez'.

Fundamental comenzar con la plantilla de letras en la hoja de cálculo, que debe ser exactamente igual a las constantes matriciales que definiremos a continuación. El orden en que aparecen hará que posteriormente resalten y lean la hora:

Un reloj literal en español para Excel.


El siguiente paso es también muy importante, ya que consiste en construir los diferentes Nombres definidos, algunos de ellos como Constantes matriciales:
Tres constantes matriciales:
arr_c ={0;"SONDLASDIEZ";"UNASEISTRES";"CUATROCINCO";"OCHOONCEDOS";"SIETETWDOCE";"YMENOSNUEVE";"DIEZEVEINTE";"AYCUARTORDC";"VEINTICINCO";"YMEDIAJUSTO"}
que coincide con las letras colocadas por fila en la hoja de cálculo

arr_h ={"UNA";"DOS";"TRES";"CUATRO";"CINCO";"SEIS";"SIETE";"OCHO";"NUEVE";"DIEZ";"ONCE";"DOCE"}
que nos dice las doce horas de un reloj

arr_m ={"";"CINCO";"DIEZ";"CUARTO";"VEINTE";"VEINTICINCO";"MEDIA"}
que nos leerá los minutos con intervalos de cinco minutos...

Resto Nombres definidos con fórmula:
My_h =AHORA()
nos dice la hora exacta del sistema

Round_h =MULTIPLO.INFERIOR(My_h;"0:05")
redondeamos la hora al minuto más cercano inferior múltiplo del intervalo de cinco minutos

My_m =MINUTO(Round_h)
obtenemos el minuto exacto de la hora del sistema

arr_r =FILA(Hoja1!$A$1:$A$11)
servirá para localizar la fila correcta dentro de la matriz de letras (diez filas de letras más la primera vacía)

t_1 =INDICE(arr_m;ABS((My_m>30)*60-My_m)/5+1)
devuelve la constante correspondiente al minuto a mostrar de entre los valores de la constante matricial de los Minutos (arr_m)

t_2 =INDICE(arr_h;RESIDUO(HORA(My_h)+(My_m>30);12)+12*(RESIDUO(HORA(My_h)+(My_m>30);12)=0))
devuelve la constante correspondiente a la hora a mostrar de entre los valores de la constante matricial de las Horas (arr_h)

n_1 =SI(My_m;MIN(SI(--SI(ESNUMERO(ENCONTRAR(t_1;arr_c));arr_r)>=7;arr_r));0)
lo utilizaremos junto con el formato condicional que resaltará las letras de la plantilla. Identifica los minutos.

n_2 =SI(t_2="";0;MIN(SI(--SI(ESNUMERO(ENCONTRAR(t_2;arr_c));arr_r)>0;arr_r)))
lo utilizaremos junto con el formato condicional que resaltará las letras de la plantilla. Identifica las horas.


Por ejemplo, digamos son las 11:26:53, del día 31/12/2013.
Entonces los diferentes valores devueltos serían:
My_h = 41639,47701 = 31-12-13 11:26:53 como hora del sistema
Round_h = 41639,47569 = 31-12-13 11:25:00 la hora redondeada
My_m = 25 el minuto de la hora redondeada
t_1 = VEINTICINCO sería el valor correspondiente de nuestra constante matricial arr_m.
t_2 = ONCE corresponde al texto de la hora dentro de la constante arr_h de las horas.
n_1 = 10 identifica que los minutos (VEINTICINCO) lo encontramos en la fila número 10 (contando la primera fila vacía), a efectos del formato condicional.
n_2 = 5 identifica que la hora (ONCE) lo encontramos en la fila número 5 (contando la primera fila vacía), a efectos del formato condicional.


Una vez configurados los nomnres definidos y por tanto los cálculos necesarios, rematamos. Hacemos visible en la hoja de cálculo, en nuestra plantilla, los valores obtenidos, es decir, configuramos los formatos condicionales.
Serán cinco formatos condicionales con fórmulas aplicados sobre nuestra plantilla de letras.
El primero remarcará de blanco la celda C9 (es decir la letra 'Y') cuando se cumpla la igualdad
=MEDIANA(My_m;1;30)=My_m
esto es, cuando el valor de My_m se encuentre en medio de los valores 1 y 30

Otro formato es el que resaltará el texto del rango I13:M13 (i.e., 'JUSTO') cuando
=My_m=0
cuando el minuto de la hora redondeada sea cero.

Un tercer formato que formateará el rango D9:H9 (texto 'MENOS') cuando
=My_m>30
es decir, cuando los minutos pasen de la media hora.

Un cuarto formato condicional aplicado al rango C9:M13 (texto de las letras que componen los minutos) con la fórmula:
=O((FILA(C9)-FILA($C$4)+2=n_1)*(COLUMNA(C9)=COLUMNA($C$3)+SI(t_1="";0;ENCONTRAR(t_1;INDICE(arr_c;n_1)))+FILA(DESREF($B$1;;;LARGO(t_1)))-2))
donde jugamos con el valor del nombre definido t_1 y n_1.

Y el último formato aplicado sobre el rango C4:M10 (texto de las letras que componen las horas) con la fórmula:
=O((FILA(C4)-FILA($C$4)+2=n_2)*(COLUMNA(C4)=COLUMNA($C$3)+SI(t_2="";0;ENCONTRAR(t_2;INDICE(arr_c;n_2)))+FILA(DESREF($B$1;;;LARGO(t_2)))-2))
donde jugamos con el valor del nombre definido t_2 y n_2.

Un reloj literal en español para Excel.



El resultado de la combinación de estas técnicas nos deja este Reloj literal en español, actualizado cada vez que calculemos sobre nuestra hoja de cálculo...

11 comentarios:

  1. Mi nombre es Juan,me gusta la idea, lo intente pero soy muy burro en Excel. Hice todo y no me sale, tengo una duda donde debo poner las primeras formulas en, que celda (arr_c y demas)? el resto de formato condicional. Gracias

    ResponderEliminar
    Respuestas
    1. Hola Juan,
      son todos Nombres definidos, tienes que ir creando cada nombre definido con la fórmula que va a continuación.. es decir, no las fórmulas no van en celdas!!!

      Las últimas son Formatos Condicionales con fórmula.

      Saludos cordiales

      Eliminar
  2. Gracias Ismael, lo intentare... ya te digo algo. saludos

    ResponderEliminar
  3. Hola Ismael,
    Donde podemos ver el reloj literal.
    Gracias
    Jorge

    ResponderEliminar
    Respuestas
    1. Hola Jorge,
      sólo sigue los pasos indicados en esta entrada
      Saludos

      Eliminar
  4. Ismael, muy buen ejemplo. Me ha gustado mucho.

    He estado pensando alguna forma de poder utilizarlo de forma más o menos profesional, pero no se me ocurre ninguna... ¿Alguna sugerencia?

    ResponderEliminar
    Respuestas
    1. Hola Sergio!!, muchas gracias!
      es una adaptación del mismo en inglés...
      Pues es un reloj curioso, pero no le veo utilidad profesional... existen otros relojes más prácticos (pero menos vistosos, eso sí..).

      Un cordial saludo

      Eliminar
  5. Que tal Ismael

    Esta muy bueno tu blog de Excel, felicidades. Tengo una duda, estoy haciendo una lista en excel (con validación de datos) y todo va muy bien pero me surge una duda:

    Yo selecciono el rango para la lista A1:A5, pero solo existen datos en las celdas A1, A3 y A5, hay alguna forma de que al momento de desplegar la lista solo me muestre las celdas con valores y excluya las celdas vacías (A2 y A4)?

    Gracias, saludos.

    ResponderEliminar
    Respuestas
    1. Muchas gracias Edgar!
      Difícil cuestión, dede luego NO directamente...
      habría que generar primero un rango 'virtual' con Nombres definidos que reemplazara las celdas vacías por algo (quizá la celda superior - $a$1).
      Luego, en base a este rango, construimos un segundo que deberemos plasmar físicamente en unas celdas de la hoja, que sólo nos devuelva los valores únicos.

      Finalmente construiremos sobre este rango ordenado de celdas la Validación.

      Es algo retorcido, pero con Validación de datos no creo sea posible otra cosa.

      Subiré al blog un paso a paso de como hacer lo indicado.
      Un cordial saludo

      Eliminar
  6. Excelente!! El ejemplo no es tan útil, pero muy beneficioso para ejercitar la mente, y practicar el excel. Después de varios intentos lo logré, solo hay un detalle por allí: los puntos y coma generan error, deben sustituirse por comas. En mi caso, use Excel 2010. saludos..

    ResponderEliminar
    Respuestas
    1. Gracias María!
      el problema de los 'puntos y comas' vs 'comas' no es tanto un tema de versión sino de configuración del sistema.
      Me alegro te haya sido de utilidad en el aprendizaje.. de eso trata este blog.

      Un cordial saludo

      Eliminar