domingo, mayo 21, 2006

Formato personalizado de números en Excel – Nota II

En la nota anterior sobre formatos de números personalizados en Excel, expliqué, brevemente, como crear formatos de números en Excel. En esta nota completaré la explicación mostrando algunos de los códigos con los cuales se crean los formatos.

Para crear formatos personalizados usamos el menú Formato de Celdas---Personalizada. En la ventanilla "Tipo" escribimos la combinación de códigos que crean el formato deseado. Estos códigos son los siguientes:

"General": el formato general de Excel

"#": muestra únicamente los dígitos significativos y no muestra los ceros sin valor.

"0" (cero): muestra los ceros sin valor si un número tiene menos dígitos que ceros en el formato

"?": agrega los espacios de los ceros sin valor a cada lado del separador decimal, para alinear los decimales con el ancho fijo del formato. También se puede utilizar ? para las fracciones que tengan un número de dígitos variable.

"%": porcentaje

[color]: determina el color del número (ver ejemplo en la
entrada anterior). El color debe ser el primer elemento de la sección. Ejemplos de colores: [Negro], [Azul], [Aguamarina], [Verde], [Fucsia] [Rojo], [Blanco], [Amarillo]

"texto": para agregar texto inmediatamente después (o antes) del número, ponga el texto deseado entre comillas, como en el
ejemplo de la entrada anterior.

También se pueden generar formatos condicionales que se aplicarán únicamente si coinciden con las condiciones que se hayan especificado. La condición se escribe entre corchetes y consta de un operador de comparación y un valor. Por ejemplo, el siguiente formato muestra los números iguales o inferiores a 100 en color rojo y los números superiores a 100 en color azul.

[Rojo][<=100];[Azul][>100]

En general es más eficiente utilizar el comando Formato Condicional en el menú Formato para formatos condicionales.

En la próxima nota mostraré los códigos que se utilizan para crear formatos de fechas y horas.


Categorías: Varios_


Technorati Tags:

43 comentarios:

  1. Buenos días,

    Estoy pensando en realizar un tipo de dato especial para una hoja de EXCEL y no consigo hacerlo. Se trata de una dirección IP, que son del tipo A.B.C.D, donde A, B, C y D son valores numéricos entre 0 y 255. Necesito también que sea posible ordenarlos siguiendo: primero por el valor de A, en igualdad de condiciones por el valor de B y así hasta D. ¿Es posible hacer esto con formato de personalizado de datos? ¿cómo podría hacerlo de otra forma?

    Muchas gracias.

    Un saludo.

    ResponderBorrar
  2. Con formato personalizado puedes cambiar el aspecto de los datos, es decir, como se ven en el monitor, pero no los datos en si mismos. Si tienes que ordenar las distitnas partes de un texto (la dirección IP es para Excel un texto), tienes que usar funciones o macros.

    ResponderBorrar
  3. Muy útil la información Jorge! A mí también me causa problemas el formateo de los datos tipo IP en Excel ya que cuando le digo que sea texto, pues convierte a valor exponencial cuando el primer octeto tiene 3 dígitos... :(
    Además cuando los 3 últimos octetos tienen 3 dígitos el Excel asume que es un número y lo trata como tal. Si pongo formato de texto a esos casos, el Excel le saca los puntos y en algunos casos inclusive le da el formato exponencial como te decía...

    Tienes alguna idea de cómo se puede decir al Excel que estoy usando un dato "tipo IP"?

    Muchas gracias!
    Silvia

    ResponderBorrar
  4. Hola Silvia

    cuando entras una dirección IP en una celda, Excel la interpreta como texto. He experimentado un poco con los casos que describes, pero no veo que excel le saque los ceros a la dirección o lo que convierta a cualquier otra cosa que no sea texto.
    Puedes enviarme el archivo con el problema?

    ResponderBorrar
  5. por ejemplo como ordenar las filas :
    192.168.1.12
    192.168.1.2
    192.168.1.21
    192.168.1.3
    192.168.1.31
    192.168.1.4
    ?

    ResponderBorrar
  6. Pedro,
    Excel ordena esa lista como texto. ¿Cuál sería el problema?

    ResponderBorrar
  7. lo mismo que pedro....tengo un rango de ip, y todas desordenadas, se que excel las interpreta como texto pero habra alguna manera de que ordenadas de menor a mayor, tomando el ejemplo de pedro:

    192.168.1.12
    192.168.1.2
    192.168.1.21
    192.168.1.3
    192.168.1.31
    192.168.1.4

    a algo asi:

    192.168.1.2
    192.168.1.3
    192.168.1.4
    192.168.1.12
    192.168.1.21
    192.168.1.31

    esop..!

    ResponderBorrar
  8. ¿Cuál es la lógica de ese orden? ¿Por qué 192.168.1.12 va después de 196.168.1.4?

    ResponderBorrar
  9. Para excel no es lo mismo 192.168.1.4 que 192.168.1.04, si se antepone el cero, la lista se organiza en el orden que estan buscando.

    igual aplica para 04 y 004, deben anteponer tantos ceros como necesiten.

    ResponderBorrar
  10. Hola Jorge, estamos intentando una compañera y yo saber como utilizar el comando * para los formatos. Le hemos dado muchas vueltas y no sacamos agua clara, ¿podrias darnos una pista por favor? lo poco que sabemos de este formato es que sirve para repetir el siguiente carácter hasta completar el ancho de la celda.
    Muchas gracias por anticipado.

    Ana y Toni

    ResponderBorrar
  11. Ana y Toni, pongamos un ejemplo:
    si ponemos el formato @*-, al entrar el texto Jorge en la celda, veremos Jorge-----; o si ponemos un número, por ejemplo, 123.45 veremos 123.45------
    Si queremos un formato númerico, por ejemplo para usarlo en una plantilla de factura, podemos usar

    #,##0.00*-;-#,##0.00*-

    ResponderBorrar
  12. Muchas gracias Jorge, recibe un cordial saludo y una felicitación por tu blog que usamos con bastante asiduidad y que nos resulta tan útil.

    Ana y Toni

    ResponderBorrar
  13. Muchas gracias Jorge, recibe un cordial saludo y una felicitación por tu blog que usamos con bastante asiduidad y que nos resulta tan útil.

    Ana y Toni

    ResponderBorrar
  14. Muy buenas.
    Me gustaria saber que codigo tengo que hacer para que cuando escribo un numero de documento de identificacion (DNI en españa) me aparezca por ejemplo 12.123.123-B si lo que he introducido en la celda ha sido 12123123B.

    Muchas gracias anticipadas.

    ResponderBorrar
  15. Dado que 12123123B no es un número sino texto (por la presencia de B al final) no podemos aplicar ningún formato numérico. Existen dos soluciones:
    1 - Sin macros: usar una columna auxiliar con una fórmula que haga la transformacion. Por ejemplo, si el número está en la celda A4, en la celda B4 podríamos poner una fórmula como esta

    =CONCATENAR(IZQUIERDA(A4;2);".";EXTRAE(A4;3;3);".";EXTRAE(A4;6;3);"-";DERECHA(A4;1))

    2 - con macros: programar un evento de manera que al introducir el valor en una celda, éste sea transformado automáticamente.

    ResponderBorrar
  16. Buenos días, Jorge:

    he usado el formato condicional 0 añadiéndole "Cine " delante quedanndo como: "Cine "0. Mi problema viene cuando introduzco datos en ese rango de celdas. Mi explico: si introduzco un número cualquiera a mano, efectivamente me lo muestra como Cine 4 (por ejemplo)pero lo que realmente tengo en esas celdad es una función, concretamente la función EXTRAE, la cual sí funciona bien pero únicamente muestra el valor, sin "Cine " delante.

    Me podrías indicar que hago mal?? Necesitas más datos??

    Esto no es para hacer ningún tipo de plantilla profesinal ni nada por el estilo; estoy realizando ejercicios para practicar excel de cara a una oposición.

    Gracias un saludo

    ResponderBorrar
  17. El resultado de la función EXTRAE, como el de todas las funciones de texto, es texto, no numérico. Por ese motivo Excel no aplica el formato personalizado. Lo que te sugiero hacer es contactenar el resultado de la fórmula a la palabra Cine. Algo así como

    ="Cine "&EXTRAE(.....

    Suerte en la oposición!

    ResponderBorrar
  18. Gracias Jorge

    Después de tu explicación, y hacerme ver el tipo de valor obtenido con la función EXTRAE, lo que he aplicado es la función VALOR, anadidando EXTRAE dentro de ésta para convertirlo en numérico, quedando así: =VALOR(EXTRAE(A2;2;1))

    Muchas gracias

    ResponderBorrar
  19. Quisiera poner en una celda un formato de hora donde te muestre los : de tal manera que le de 1224 y quede como 12:24 ¿es posible?

    ResponderBorrar
  20. Si, es posible, pero primero una aclaración. Si entras 1224 en una celda Excel no lo interpretará como hora sino con un número corriente. Para que aparezca como 12:24 podemos aplicar el formato personalizado ##":"##, pero esto no convertira la entrada en hora. Excel seguirá "viendo" 1224.
    Para que Excel "vea" la hora 12:24, tienes que escribir 12:24, con los dos puntos. Luego puedes usar el formato de hora hh:mm
    Te sugiero que leas mis entradas sobre como Excel trata las horas y las fechas.

    ResponderBorrar
  21. Tengo un problema, intento meter en una celda el formato 01/01 o 02/02 y directamente me lo trata como fecha, pero no quiero que me ponga 1-ene o 2-feb sino como tal y por mucho que cambio a formato de celda genérico, luego me lo cambia a vete tu a saber por qué ¬¬

    Por más que copie el formato de otra celda, texto o número especial me sigue tomando como fecha. Odio los automatismos de esta clase.

    Ni cambiando el símbolo por un punto, ni como un guión, ni nada, es desesperante que tengas que aceptarlo por tripas.

    ¿Hay algún modo de "engañar" al programa?

    ResponderBorrar
  22. Si estás intentando ingresar fracciones tienes que darle el formato de fracción a la celda (o a todo el rango) antes de ingresar el número.
    Si se trata de texto, darle formato de texto, siempre antes de ingresar el valor, o anteceder el texto con una comilla (').

    ResponderBorrar
  23. Jorge estoy utilizando el formato #0":"#0":"#0";"#0 para hh:mm:ss;ff
    ff serían los frames, los cuales deberian ir solo hasta 30, pero el excel me lo toma como rango de 0 a 100. O sea que cuando sumo 100 frames recien ahi me suma un segundo. En realidad quiero que al sumar 30 frames, sume un segundo más.
    Ejemplo:

    quiero lograr:

    01:22:30:24
    02:05:10:20 +
    -----------
    03:27:41:14

    pero lo que logro es
    01:22:30:24
    02:05:10:20 +
    -----------
    03:27:40:44

    ResponderBorrar
  24. Jorge, te comento esperando tu ayuda!

    En la celda A1 tengo un codigo
    En la ceda B1 tengo una formula para que valla a buscar el codigo de A1 y lo despliege en la celda B1 (el valor que devuelve es un numero de 0 a 100)

    Quiero que si el valor que devuelva sea 0 lo pinte de rojo y en caso contrario lo pinte de negro

    para ello tengo este formato condicional [Rojo][=0]Estándar;[Negro][>0]Estándar

    Pero tambien quiero que ese numero lo devuelva en formato de %

    Ejemplo

    0 (en color rojo)% y 100 (en color negro) %

    de antemano muchas gracias!

    ResponderBorrar
  25. Tendría que ser

    [Rojo][=0]0%;0%

    No hace falta señalar [negro] ya que este es el color por defecto.
    También podrías usar formato condicional para establecer el color de la fuente.

    ResponderBorrar
  26. Gracias por tu ayuda Jorge!

    ResponderBorrar
  27. Jorge, Felicidades por tu Blog.

    Me gustaría que me ayudaras con esta consulta.

    Necesito hacer referencia a varias celdas de una hoja desde otra. El tema es que algunas tienen el formato personalizado "<"0,000; es decir que si tengo un valor como 0,064 este debe aparecer como <0,064. He intentado haciendo =(Datos!C10)
    pero solo me aparece el valor sin el formato.

    ¿Existe alguna forma de resolverlo sin usar el botón para copiar formato??

    ResponderBorrar
  28. Te acabo de responder en el comentario que dejaste en el otro post sobre el tema

    ResponderBorrar
  29. Jorge, quiero realizar operaciones con numeros IPs.
    Ejemplo:
    quiero restar 1 a un numero ip.
    10.123.26.23 a 10.123.26.22
    10.123.20.200 a 10.123.20.199

    agradeciendo tus respuesta saludos.

    ResponderBorrar
  30. Los IP no son números, sino texto. Por eso no los puedes usar en operaciones matemáticas. Suponiendo que el IP se encuentra en la celda B3, puedes usar esta fórmula

    =EXTRAE(B3;1;10)&VALOR(DERECHA(B3;LARGO(B3)-10))-1

    ResponderBorrar
  31. Buenas tardes, necesito dar formato a lo siguiente: Debo ingresar datos que siempre comienzan con IC04- luego viene un número de 6 dígitos y finalmente terminan con - y alguna letra (Ej, IC04-090047-V, IC04-128969-W, etc) para realizar el trabajo de manera más rápida, utilicé los formatos, para introducir solo los números, el pre-fijo me lo respeta bárbaro, pero no logro que ingrese la última letra. Ej. Yo quiero ingresar 090047V y que por el formato me aparezca IC04-090047-V o 128969W y que aparezca IC04-128969-W. Alquien podrá ayudarme? Desde ya muchas gracias!

    ResponderBorrar
  32. Hola, al poner en la celda 090047V Excel no ve un número sino un texto. Por eso no puede aplicar el formato. Si el dígito final fuera siempre el mismo, podrías usar simplemente el formato personalizado "IC04-"#"-V".
    Podrías usar una columna auxiliar con esta fórmula
    ="IC04-"&IZQUIERDA(A1,6)&"-"&DERECHA(A1,1)

    donde en A1 ponés 090047V

    ResponderBorrar
  33. Hola! Tengo una columna de datos donde puedo tener diferentes variantes, tales como:
    2283-3214
    2122-33
    0138-17
    Tengo que llegar a tener en total 10 caracteres en todos los casos y si es necesario, completarlos con cero a la izquierda, quedando así:
    02283-3214
    0002122-33
    0000138-17
    Hay algún modo? Gracias!

    ResponderBorrar
  34. No me parece que con formato personalizado; pero podrías usar esta fórmula

    =REPETIR(0;10-LARGO(A1))&A1

    donde A1 es la celda que contiene el valor original.

    ResponderBorrar
  35. necesito saber como seria el formato personalizado para la cedula de identidad uruguaya del tipo 1.234.567-8 he intente varios formatos y quedan de la siguiente manera: 12.345.67-5, agradezco la ayuda

    ResponderBorrar
  36. Hola Alexander,
    podés usar este formato personalizado

    0"."000"."000"-"0

    ResponderBorrar
  37. hola como puedo hacer para que cuando introduzca numeros de tarjeta que estan compuestos de 4 bloques de 4 no me aproxime el ultimo numero a 0 ??

    ResponderBorrar
  38. La forma más práctica es dar a la celda formato Texto, antes de ingresar el número, o anteponer un apóstrofe (') al número al ingesarlo manualmente. Fijate en esta nota.

    ResponderBorrar
  39. Hola me gustaria saber si puedo añadir 23/ delante de un numero desde formato de celdas, es decir quedaria asi 23/2010555 5555, gracias

    ResponderBorrar
  40. Si, tienes que usar el formato personalizado "23/"# (con las comillas incluidas).

    ResponderBorrar
  41. Jorge Dunkelman, el formato personalizado "23/"# no me lo hace, me han comentado de hacerlo con concatenar, tu sabes como? gracias !!!

    ResponderBorrar
  42. Estimado, el formato personalizado "23/"# funciona (lo he testeado, por supuesto). Fijate si estás definiéndolo en el lugar indicado.
    Otra forma de hacer es concatenando el texto 23/ con el número. Eso se hace usando el operador &. Por ejemplo, si en la celda A1 está el número 12345, en la celda B2 ponemos la fórmla ="23/"&A1.
    El problema con este método, además de la sobrecarga de usar fórmulas, es que el número se convierte en texto. Usando formato numérico personalizado, el número queda como tal.
    Si necesitas más detalle de como crear el formato personalizado, ponte en contacto conmigo por mail privado (ver el enlace Ayuda, en la parte superior del blog).

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.