Formato personalizado numérico condicional en Excel

viernes, febrero 10, 2012

En los albores de este blog ya había tocado el del formato personalizado de números. Vuelvo sobre el tema debido a la consulta que puso uno de mis lectores en un comentario de la entrada:

tengo unos registros en donde guardo la hora del día, pero quiero que me ponga madrugada si la hora es de 0 a las 5, mañana si es de 6 a 12, tarde de 13 a 19, y noche de 20 a 24, ¿se podrá con el formato personalizado?

En formato personalizado de celda de Excel existe la posibilidad de usar formatos condicionales. El método es muy elemental y admite sólo tres condiciones, de las cuales dos son explícitas y la tercera implícita. Las condiciones, o criterios, son comparaciones numéricas sencillas.

Si la consulta de mi estimado lector se limitara a tres condiciones, digamos mañana, tarde y noche, se podría hacer. Para el caso que queramos que aparezca "mañana" si la hora es anterior o igual a las 12:00, "tarde" si cae entre las 12:00 y las 18:00 y noche para el resto, la sintaxis es la siguiente

[<=0,5]"Mañana";[>0,75]"Noche";"Tarde"

Las dos condiciones explícitas las ponemos entre [] y la implícita es "todo el resto".

El valor 0,5 resulta de dividir 12 por 24; el valor 0,75 de dividir 18 por 24. Como ya explicamos en el pasado, en Excel las horas son números que resultan de dividir la hora por el total de horas del día (24).

En esta imagen podemos ver las horas 08:00 y 16:00 con tres formatos distintos: hora, general y personalizado condicional





Dado que el formato numérico condicional está limitado a tres criterios, no podemos resolver la consulta de mi lector con esta herramienta. La alternativa es usar formato condicional, que a partir de Excel 2007 no está limitado a tres condiciones. En el caso de la consulta los criterios son:



Usaremos formato condicional con la opción Fórmula, definiendo una fórmula para cada uno de los criterios. Para evitar problemas de precisión, usaremos la función HORANUMERO para calcular los números de series de las horas





El cuaderno con el ejemplo se puede descargar aquí.

11 comments:

Deibis 12 febrero, 2012 16:44  

Hola Jorge estoy creando una fórmula cuyo resultado puede dar bien sea en milímetros [mm] ó en pulgadas [in].

Aprovechando el tema de Formato personalizado numérico condicional en Excel el planteamiento es el siguiente:

Colocando dos botones de opción donde se pueda seleccionar que la respuesta venda dada en mm ó en pulgadas, se obtenga el resultado con los siguientes formatos condicionales según la opción escogida.

"tm = [ (PDo) / 2(SE + Py) ]+ A(mm) =" 0.000" mm"

"tm = [ (PDo) / 2(SE + Py) ]+ A(in) =" 0.000" in".

Es decir de acuerdo a lo anterior el resultado sería algo así:

tm = [ (PDo) / 2(SE + Py) ]+ A(mm) = 7.620 mm

tm = [ (PDo) / 2(SE + Py) ]+ A(in) = 0.278 in.

Procedimiento: Coloqué dos bones de opción ambos asociados a una celda, a esta misma celda asocié dos condiciones ó fórmulas en el formato condicional para cada formato según la opción seleccionada:

Fórmula: =SI($D$10=1,1,"")

Formato Condicional:
"tm = [ (PDo) / 2(SE + Py) ]+ A(mm) =" 0.000" mm"

Fórmula: =SI($D$10=2,2,"")
"tm = [ (PDo) / 2(SE + Py) ]+ A(in) =" 0.000" in".

Al ejecutar las opciones el resultado es ok es decir se puede obtener en mm ó en in (milímetros ó pulgadas) al menos es lo que se puede ver en la vista preliminar de la hoja pero dentro de la hoja como tal al escoger una opción o la otra el resultado numérico cambia pero el formato no dando en ambos casos:

tm = [ (PDo) / 2(SE + Py) ]+ A(in) = 7.620 in

tm = [ (PDo) / 2(SE + Py) ]+ A(in) = 0.278 in


Anexo la ecuación usada.
SI(D10=1,((((G10*G11)/(2*(G12+(G10*G13))))+G14)*2.54*10),(((G10*G11)/(2*(G12+(G10*G13))))+G14))

Agradezco tu recomendación al caso.

Saludos
Deibis

Jorge L. Dunkelman 13 febrero, 2012 06:55  

Deibis,
voy a tratar de simplificar tu consulta: quieres aplicar formato condicional de manera que si la celda D10=1.1 el formato numérico agregue "mm" al número de la celda y si D10=2.2 agregue "in".
No puedes usar formato condicional numérico ya que, como pongo en la nota, éste acepta sólo comparaciones sencillas. Es decir, no puedes usar [$D$10=1.1]"mm" por ejemplo.
Lo que tienes que usar es formato condicional con dos condiciones sencillas y obvias
1 - $D$10 =1.1 --> formato de numero-->personalizado-->tipo: #,##0.00 "mm"
2 - $D$10 =2.2 --> formato de numero-->personalizado-->tipo: #,##0.00 "in"

Jorge L. Dunkelman 13 febrero, 2012 06:56  

Deibis,

no publico el otro comentario ya que no está relacionado con el tema de la nota. Por favor, lee lo que pongo en el enlace "Ayuda" (en la parte superior de la plantilla).

Juani 14 febrero, 2012 21:17  

Hola Jorge, estoy usando excel 2010 y no encuentro la función "horanumero". Sabes si esta? o si hay algún equivalente?
Saludos

Juani 14 febrero, 2012 21:24  

Hola Jorge nuevamente, encontre la función VALHORA como equivalente en excel 2010.

Saludos

Jorge L. Dunkelman 15 febrero, 2012 07:28  

Lo mismo sucede con otras funciones como FECHANUMERO (VALFECHA) y BUSCARV (CONSULTAV). Pero parece ser que depende de la versión de Excel 2010. Un buen ejemplo de la confusión que existe puede verse en esta esta página oficial de Microsoft (funciones de búsqueda). Al final de la lista de funciones aparece BUSCARV; el enlace nos lleva a la página de CONSULTAV, donde a suvez, al final los ejemplos se refieren a la función BUSCARV.

Juani 16 febrero, 2012 21:47  

Gracias por el dato! hace poco que tengo excel 2010 y BUSCARV lo utilizo bastante. Es verdad, aparece como CONSULTAV.

Saludos

DËTH 11 enero, 2013 19:43  

Hola, lo resolví yo solo, organizar los valores de una celda(en horas!) en 3 horarios, según corresponda.

Mañana:7 a 15
Tarde: 15 a 23
Noche: 23 a 7


=SI(S2>=0.958333333333333,"Noche",SI(Y(S2>=0.625),"Tarde",SI(Y(S2>=0.291666666666667),"Mañana",SI(Y(S2>=0),"Noche","X"))))


Espero les sirva.....saludos

Anónimo,  08 marzo, 2013 20:54  

GRACIAS, GRACIAS, GRAAAAAAAACIAS!!!!!!! Lo estaba reeeeee necesitando!!!! GRACIASSSS!!!

Anónimo,  27 octubre, 2013 02:24  

como seria sI ingreso la HORA en la columna A y en la otra columna que me indique si es mañana o tarde ejm:

07:00:00am MAÑANA
07:35:00am .... MAÑANA
13:00:00pm ... TARDE
18:00:00 pm TARDE
Le Agradezco Muchooo....

Jorge Dunkelman 27 octubre, 2013 21:28  

Bien, lo más fácil sería usar la función SI. También podrías usar BUSCARV, suponiendo que tienes una tabla con los intervalos.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP