jueves, 4 de abril de 2013

Formato personalizado avanzado en Excel.

Hoy nos adentraremos algo más profundamente en el tema del formato personalizado de celda, algo que he tratado con anterioridad (ver).
Comenzaremos recordando cuál es la estructura, cuáles son las cuatro secciones, de estos formatos personalizados:
Positivos; Negativos; Cero; Texto

En general, debemos saber también el uso de los caracteres más habituales. Así en la ayuda de Excel podemos leer:
0 (cero): Este marcador de posición de dígitos muestra los ceros no significativos si un número tiene menos dígitos que los ceros especificados en el formato. Por ejemplo, si escribe 8,9 y desea que se muestre como 8,90, use el formato #,00.
# (almohadilla): Este marcador de posición de dígitos sigue las mismas reglas que el 0 (cero). Sin embargo, Excel no muestra ceros adicionales cuando el número que escribe tiene menos dígitos a ambos lados de la coma decimal que el número de símbolos # especificados en el formato. Por ejemplo, si el formato personalizado es #,## y escribe 8,9 en la celda, se muestra el número 8,9.
? (cierre interrogación):Este marcador de posición de dígitos sigue las mismas reglas que el 0 (cero). Sin embargo, Excel agrega un espacio para los ceros no significativos a ambos lados de la coma decimal para que las comas decimales queden alineadas en la columna. Por ejemplo, el formato personalizado 0,0? alinea las posiciones decimales de los números 8,9 y 88,99 en una columna.
, (coma): Este marcador de posición de dígitos muestra la coma decimal en un número.
. (punto): Este marcador de posición de dígitos muestra la coma de miles, millones, etc en un número. Excel separa los millares con puntos si el formato contiene un punto incluido entre signos de almohadilla (#) o ceros. Un punto detrás de un marcador de posición de dígitos ajusta el número a 1.000. Por ejemplo, si el formato personalizado es #,0. y escribe 12.200.000 en la celda, se muestra el número 12,200,0.
@ (arroba): Si se incluye, la sección de texto siempre es la última sección del formato de número. Incluya un carácter de arroba (@) en la sección en la que desea mostrar el texto que escribe en la celda. Si el carácter @ se omite en la sección de texto, no se mostrará el texto que escriba. Si desea mostrar siempre caracteres de texto específicos con el texto escrito, incluya el texto adicional entre un carácter de dobles comillas (" "). Por ejemplo, "recibos brutos de "@
Si el formato no incluye una sección de texto, todos los valores no numéricos que escriba en una celda con ese formato aplicado no resultarán afectados por el formato. Además, toda la celda se convertirá en texto.


Veamos algunos ejemplos empleando estos caracteres antes de avanzar algo más:

Formato personalizado avanzado en Excel.
haz clic en la imagen



Abordemos ahora el tema de los colores en el formato personalizado. Sabemos existen algunos colores predefinidos, y que estos deben incorporarse entre corchetes al principio de cada sección. Los colores básicos son:
[Negro], [Verde], [Blanco], [Azul], [Magenta], [Cian], [Amarillo] y [Rojo]
Ahora bien, podemos disponer de la paleta general de colores (los 56 estándar), escribiendo entre corchetes [Color n] (siendo n un valor entre 1 y 56).
Por ejemplo:
[Color 13]##.##0,000
[Cian]##.##0,000


Y llegamos al momento 'avanzado' de la entrada. Existen muchos más caracteres que podemos emplear como parte de nuestras cuatro secciones (€, $, +, (, :, ^, ', {, <, =, -, /, ), !, &, ~, }, >, carácter de espacio), sin embargo, nos detendremos en dos muy especiales.

El guión bajo (_) (Para agregar espacios): Para crear un espacio que tenga el ancho de un carácter en un formato de número, incluiremos un carácter de subrayado (_), seguido del carácter que deseamos utilizar. Por ejemplo, si colocáramos un paréntesis de cierre detrás de un carácter de subrayado, como _), los números positivos se alinearán correctamente con los números negativos que se incluyan entre paréntesis.
El asterisco (*) (Para repetir caracteres): Para repetir el siguiente carácter del formato hasta rellenar el ancho de la columna, incluiremos un asterisco (*) en el formato de número. Por ejemplo, escribir 0*- para incluir los guiones que hagan falta detrás de un número para rellenar la celda, o escriba *0 delante del formato para incluir ceros iniciales.

Estos dos caracteres nos permitirán jugar de una manera muy especial replicando alineaciones y justificaciones en nuestras celdas. Veamos en la imagen algunas aplicaciones:

Formato personalizado avanzado en Excel.
haz clic en la imagen


Las explicaciones a estos formatos. El primero empleado para el rango B3:E3 ha sido:
;;;
Con este formato personalizado conseguimos que no se ve nada, aunque, aunque el dato existe, como se pueda ver en la barra de fórmulas.


Otro formato es para B4:E4:
##.##0,000_{_{;(##.##0,000)_{_{;0,00;_{_{@
donde hemo empleado el caracter _ (guión bajo o subrayado), que según decíamos genera un espacio, seguido en este ejemplo de una llave { deja un espacio en blanco a la izquierda o a la derecha del dato escrito en la celda.


De manera similar en B5:E6 formateamos con:
##.##0,000_X_X;(##.##0,000)_X_X;0,00;_X_X@
Vemos que es la misma estructura de formato, pero hemos sustituido la llave { por otro caracter X; el resultado es fácilmente observable, existe más espacio a izquierda a derecha... lógico ya que el tamaño de un caracter y otro son diferentes (ocupa más X que {).
En definitiva, replicamos el efecto de la Sangría o Indentado en las celdas.


En B6:E6 practicamos con la repetición de caracteres, es decir, aplicamos el asterisco. el formato es:
*. ##.##0,000;(##.##0,000) *.;0,00;*/ @
Vemos como repetimos a izquierda y derecha con diferentes caracteres (punto y barra), repitiendo dichos caracteres hasta completar el ancho de la celda.


De igual forma en B7:E7 con este formato:
* ##.##0,000;(##.##0,000) * ;0,00*/;@ *-
repetimos caracteres (en este caso el espacio en blanco, un guión - o una barra /) por la izquierda o derecha, imitando el efecto de alineación; ya que si repetimos el espacio por la derecha estaríamos viendo un número alineado por la izquierda igual que si fuera un texto!!!.


Finalizaremos con un ejemplo que recopile todo lo visto, aplicando colores en función del valor (de acuerdo a la tabla adjunta siguiente), con repeticiones y espacios:
[Rojo][<0]"Negativo "#.###0,000 * ;[Verde][>500]"Mayor que 500 "#.###0,000 * ;[Azul]"Entre 0 y 500 "#.###0,000 * ;[Cian]*-@

Formato personalizado avanzado en Excel.


Con este formato nos saltamos de alguna manera el orden de las secciones preestablecido, configurándolo nosotros mediante las condiciones:
[menor que 0];[mayor que 500];'resto';Texto
Lo que no podemos 'saltarnos' es que siempre serán cuatro secciones, y que la última debe ser siempre para el formato del Texto

54 comentarios:

  1. Saludos, como puedo poner el apostrofe de millones en los valores numericos.

    ResponderEliminar
  2. osea escribir: 1234567.89 y que me muestre automáticamente 1'234,567.89

    ResponderEliminar
    Respuestas
    1. Hola J.Stein
      el problema de lo que planteas es que deseas ver con dos tipos de separadores diferentes lo que para Excel es una solo (el separador de miles)...
      En todo caso podrías insertar este tipo de formato personalizado:
      #'###.###,00
      con los separadores de decimales y de miles de tu sistema, pero el número que te aparecerá sería:
      1,'234,567.89

      Espero te pueda servir...
      Slds

      Eliminar
  3. Hola Ismael, se te ocurre alguna solución para que en una celda validada con un texto que tiene código nombre, sólo me muestre los primeros 4 caracteres correspondientes al código en la celda? es decir truncar el texto por formato personalizado es posible?

    ResponderEliminar
    Respuestas
    1. Hola,
      no creo tal cosa sea posible con formato personalizado
      ;-)
      Tendrás que trabajarlo sobre la lista de donde tome los valores permitidos la celda validada
      slds

      Eliminar
  4. Desde hoy ya tiene sentido para mí todos esos símbolos _^{..... Gracias!!

    ResponderEliminar
  5. estimado personalizar un código c-001, como seria el formato he visto en algún foro que si se puede

    ResponderEliminar
    Respuestas
    1. Hola Luis,
      entiendo tienes el valor 1 en la celda...
      Entre y cambia el formato personalizado por:
      "c-"000

      Recuerda que esto sólo cambia el formato nunca el valor de la celda!!
      Saludos

      Eliminar
  6. Existe un código para agregar linea (similar a Alt + Intro)?

    ResponderEliminar
    Respuestas
    1. Hola Jordi,
      un placer saludarte igualmente.
      Si te refieres para incorporarlo dentro del formato personalizado, no existe tal cosa.
      Un cordial saludo

      Eliminar
  7. Como hago para agregar letras que cambian al final de X número. Ejemplo 300380P. OTRO 150380Y. ETC

    ResponderEliminar
    Respuestas
    1. Hola que tal jhodgsonni,
      espero te encuentres bien.
      Para poder darte una respuesta habría que conocer en base a qué cambian esas letras al final de un número...
      ¿hay que aplicar algún criterio?, en ese caso, ¿cuál?

      Un cordial saludo

      Eliminar
  8. Como hago para personalizar este formato. 215 a 21.5 Muchas gracias.

    Saludos,

    ResponderEliminar
    Respuestas
    1. Hola,
      prueba en formato personalizado con:
      0"."0

      Saludos

      Eliminar
  9. Hola,sabes como calcula Excel el formato ???/??? de manera que al ecribir un número decimal, por ejemplo 52,8351 nos muestra 3540/67? es decir, cómo calcula a partir de un cociente, el dividendo y el diviso.

    Gracias y saludos

    ResponderEliminar
    Respuestas
    1. Hola Pere,
      si, claro, para 'convertir' el valor decimal a fracción busca el Máximo Común divisor de ambos valore Numerador y Denominador a través del Mínimo común Múltiplo.
      En breve subiré un post para explicarlo.

      Un saludo

      Eliminar
  10. Buenas....
    Por favor, alguien me puede decir que significa o para que se usa el signo de almohadilla (#) en un codigo de macros de excel.
    Por ejemplo, en una macro de excel encuentro repetidamente algo similar a los siguiente:
    For i = 1 To NC
    If ts >= 1# Then
    F2(i) = SI(i) * (400# + 710# * (2# * ts) ^ -0.75)
    Else
    F2(i) = GetFOV2us(SI(i))
    End If
    F6(i) = GetFOV6us(SI(i))
    Next i

    Y la verdad no entiendo que es lo que hace el signo "#" detras de cada numero.

    En todo caso Muchas gracias a las personas que puedan ayudarme.

    ResponderEliminar
    Respuestas
    1. Hola,
      antiguamente se empleaban ciertos signos para declarar el tipo de dato asociado a una variable/constante.
      Por ejemplo, la almohadilla # indica que el tipo de dato es Double

      Saludos

      Eliminar
  11. Hola!!
    Quería saber si de alguna manera con formato personalizado puedo hacer que sólo el número 1 se vea en color rojo y el resto de los valores en negro.
    Gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      algo extraño pero si es posible...
      desde el formato personalizado de número incorpora este modelo:
      [Rojo][=1]0;0;0
      en lugar del cero puedes poner el formato que desees...
      Slds

      Eliminar
    2. Muchas gracias. No es tan extraño, simplemente al hacer un gráfico con coeficientes de absorción, cuyo valor límite teórico es = 1, pero no el valor real que puede alcanzar, me pareció útil o interesante que en el eje de ordenadas del gráfico se resaltara el valor 1 de esa forma. Ahora ya entendiendo el funcionamiento, pinto los mayores de un color y los menores de otro...!
      Muchas gracias.

      Eliminar
    3. ;-)
      un uso muy concreto
      Me alegra haberte ayudado

      Un cordial saludo

      Eliminar
  12. buenas tardes! por favor como coloco el formato 0+000.00 en excell?

    ResponderEliminar
    Respuestas
    1. Hola Rosa,
      entra en el formato de Número personalizado y añade el Tipo:
      0+000"."00
      Saludos

      P.D.: Excel se escribe con una ele al final ;-)

      Eliminar
  13. hola genio !!!
    tengo una consulta muy sencilla, necesito agregar un codigo de formato de numero con centesimo, osea #´#´´#´´´ (unidad de tiempo)
    desde ya muchas gracias por tu predisposicion !!

    ResponderEliminar
    Respuestas
    1. Hola Pablo
      podrías emplear:
      h'mm''ss'''
      escribiendo el dato como
      10:30:45
      hora:minuto:segundo

      Un saludo

      Eliminar
    2. hola, si hasta ahi si pude hacerlo, pero necesito minutos:segundos y centecimas

      Eliminar
    3. ummm
      podría ser:
      [h]:mm:ss,00
      No tengo claro sea posible mostrarlo con ' - '' - '''

      Saludos

      Eliminar
    4. si, ahí pude con ,00 ...
      MUCHAS GRACIAS ISMAEL QUE DIOS TE BENDIGA!!

      Eliminar
  14. Hola nuevamente, existirá la posibilidad de poner las centésimas en formato superíndice¿? para distinguirlas mejor de los segundos. gracias

    ResponderEliminar
    Respuestas
    1. Hola Pablo,
      Investigaré el asunto, pero no parece posible emplear superíndice 'variable'...
      Slds

      Eliminar
  15. Hola Ismael, que formato debo poner para que al escribir un numero (1000) figure en la celda 1000 mts ¿? Gracias

    ResponderEliminar
  16. Hola Ismael, para q en una celda se deje un numero mínimo de numeros se personaliza llenando de los ceros requeridos el formato, por ejemplo dígito 123 y quiero q muestre mínimo 5 digitos y muestra 00123. Ahora quiero hacer lo mismo en texto, pero, q llene de espacios en blanco a la derecha lo q falta, q debo hacer ? por ejemplo completar 12 dígitos cuando dígito AVE MARIA q muestre AVE MARIA--- mostrando 3 espacios en blanco o 3 espacios con lineas. Muy Amable

    ResponderEliminar
    Respuestas
    1. Hola,
      lo siento pero no es posible tal cosa sobre el formato personalizado de texto

      Un cordial saludo

      Eliminar
  17. hola como hago por ejemplo que me ponga en verde los numeros entre el -5 y el 5, despues los mayores a 5 amarillo y los menos a -5 rojos.. gracias!!!

    ResponderEliminar
    Respuestas
    1. Hola,
      podrías generar tres reglas de formato condicional, una para los valores entre -5 y 5: color verde
      otra para los menores a -5: color rojo
      y una tercera para los mayores a 5: color amarillo
      Sería la forma más sencilla.

      Si quieres en formato personalizado de número podría ser:
      [<-5][Rojo]0;[>5][Amarillo]0;[Verde]0

      Saludos

      Eliminar
  18. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  19. Hola, escribo el número 1 seguido de cuatro decimales y excel me corre la coma un lugar a la derecha. Cómo puedo solucionarlo y que escriba el número que yo quiero?

    ResponderEliminar
    Respuestas
    1. Hola,
      quizá la respuesta la tengas aquí:
      <a href="http://excelforo.blogspot.com.es/2013/01/insertar-automaticamente-un-punto.html>http://excelforo.blogspot.com.es/2013/01/insertar-automaticamente-un-punto.html</a>
      Saludos

      Eliminar
    2. Gracias! Mi computadora se estaba confundiendo porque tenía el mismo separador decimal que de miles.
      Saludos

      Eliminar
  20. amigo tengo una consulta. quiero crear un formato en donde automaticamente excel me separe texto de numeros, es decir, escribire en la celda J123456789 y quiero que el me separe el texto de los numeros de esta forma J-12345678-9, sin usar formulas solo formato, podria hacerlo colocando "J-"00000000"-"0 este formato pero, no puedo ya que la "J" Puede cambiar, por "V" o por "E", necesito ayuda, llevo dias en esto.

    ResponderEliminar
    Respuestas
    1. Hola,
      el problema que estos formatos son para números.. y en tu caso al aparecer una letra (y un guión) el valor se convierte en tipo texto, por lo que a priori no lo veo posible.

      Un saludo y suerte

      Eliminar
  21. buenos días, Ismael Romero, mira la situación que tengo es la siguiente:
    tengo una hoja que por medio de datos valido para que en ella solo escriban número enteros e incluso envía un mensaje y protejo en Revisar con clave para que solo acensen información y no me muevan las fórmulas, y si funciona, el problema es que si copio valores con decimales de otra hoja excel y las sobrepongo sobre las celdas validadas a números enteros, desbloquea la validación y acepta los números con decimales, espero haberme dado a entender.. ¿Tendras algún consejo o recurso que me ayude hacer que solo acepte información con números enteros y no acepte el clásico copy-paste de cualquier hoja a mi hoja validada ?. Muchas gracias de antemano por tu atención.

    ResponderEliminar
    Respuestas
    1. Hola Vidal,
      soy consciente del 'problema', pero debes entender que la validación de datos solo restringe la posibilidad de escribir/introducir valores directamente... y que como has comprobado no está preparada para evitar sobre esas celdas validadas el pegado de cualquier dato (desde cualquier otra celda)...

      Una posible solución, no muy práctica, se me ocurre que pasaría por integrar un evento de programación en la ventana de código de esa hoja (evento Change) para que cada vez que cambie el valor de una celda, comprobar cumple ciertas restricciones, y en caso de no hacerlo, lanzar un mensaje o borrar el contenido...

      El problema sería que se ralentizaría en exceso todo el recalculo y procesos de trabajo

      Un saludo

      Eliminar
  22. Saludos Ismael, veras tengo un problema para escribir numeros grandes, es decir millones, según lo que APA exige para investigaciones científicas como tesis y demas... es un problema porque APA no permite separador de miles con puntos o comillas, sino con espacio en balanco y coma decimal y no logro configurar ambos en el excel, solo los espacio para los miles... Lo que requiero es configurar números de esta manera: 3 474 526,84

    ResponderEliminar
    Respuestas
    1. Hola Doris,
      explicaré el proceso para conseguir lo que necesitas en el próximo post que escriba, en un par de días

      Saludos

      Eliminar
  23. Hola Experto Ismael como personalizo excel que cuando introduzco 10 digitos en una celda me lo separe por guiones automáticamente. por ejemplo A-12345678-2

    ResponderEliminar
    Respuestas
    1. Hola Elías,
      lo siento pero a priori tal cosa no es posible... ya que un formato personalizado solo afectaría a valores numéricos.
      Salvo que siempre empiece por 'A-' y el resto sean números, en cuyo caso podrías aplicar el siguiente formato personalizado de número:
      "A-"00000000-0

      Saludos

      Eliminar
    2. Gracias pero la primera letra varía

      Eliminar
    3. Ah ya entendí gracias amigo por la aclaración saludos!

      Eliminar