Extreme #1 – FORMATOS DE NUMERO EN EXCEL

Bajo la etiqueta de Extreme incluiremos algunos posts más largos y detallados, en los que trataremos de exprimir al máximo un tema concreto. El primer Extreme irá sobre los formatos de número en Excel.

FORMATOS DE NUMERO

El formato define cómo Excel mostrará un valor o expresión de una celda, únicamente es la forma de mostrarlo pero sin alterar su valor real. La celda aplicará un formato seleccionado a lo que hayas escrito, mientras que la barra de fórmulas lo muestra sin alterar:

Excel define el formato de una celda en cuatro secciones, separadas por [;] según a qué se quiera aplicar, siendo el orden de esas cuatro secciones:

• números positivos
• números negativos
• valores cero
• valores de texto

Por ejemplo si el formato dado a una celda es #.##0;-#.##0;; significa que los números positivos se mostrarán como 2.545, los negativos como –2.545, mientras que no se ha definido un formato específico para los valores cero ni para los de texto (las secciones 3 y 4 están vacías).

Nota: Para definir el formato, no es necesario indicar las cuatro formas o secciones, pero debes recordar que si solo especificas 2 secciones, la primera se usará para números positivos y valores cero, y la segunda para números negativos (por ejemplo #.##0;-#.##0 es suficiente para tratar con cualquier número). Por otro lado, si especificas solo 1 sección de las cuatro posibles, será la que se use para cualquier número. Por último, el texto (cuarta sección) solo será de aplicación en el formato si lo defines.

Para conocer el formato dado a una celda, o para modificarlo, selecciona el menú Formato > Celdas (o mejor usa el atajo de teclado [Ctrl+1]) para llegar a este cuadro de diálogo:

Hay doce categorías (General, Número, etc), y dentro de cada una hay mucho que ver. Las once primeras son las predeterminadas como más usuales por Excel (un día hablaremos en detalle de algunas de ellas), pero es la última (Personalizada), en la nos vamos a centrar porque es la más versátil y dónde más trucos podemos aplicar.

Nota: Excel permite almacenar entre 200 y 250 formatos de número en la categoría “Personalizada”. Estos se guardan en la propia hoja de cálculo, y en principio no estarán disponibles para nuevas hojas.

Nota: Para ver cómo codifica Excel un formato predeterminado, entra en una categoría cualquiera, por ejemplo la de Moneda y selecciona un formato de tu gusto. Ahora selecciona la categoría “Personalizada” y verás los códigos aplicables a ese formato concreto:

Hasta aquí todo fácil. Los usuarios Excel novatos estáis encantados, los usuarios avanzados ya estáis dormidos. Vamos a complicarlo.

CODIGOS DE FORMATO

Los códigos de formato son modificadores que pueden usarse para construir muchos tipos. Esta es una relación de los mismos:

El código # especifica el redondeo a un dígito significativo, y el 0 (cero) tiene una función similar pero fuerza el valor cero si es necesario. Por ejemplo #,### mostrará el número 0,12345 como ,123 pero el código 0.### lo mostrará como 0,123.

El código ? fuerza la alineación decimal de los números; lo que hace es añadir espacios en cualquier lado del número. Es recomendable combinar su uso con una fuente de espaciado fijo (como Courier) para que el efecto sea perfecto. por ejemplo el código de formato de número ,??? forzará que todos los números con este formato muestren tres espacios después de la coma decimal.

Y estos son algunos ejemplos aplicados de esos formatos:

Nota: La notación científica 1,23E+04 se lee como 1,23 x 10^4, es decir 1,23 por 10 elevado a la cuarta potencia (o, más fácil 12.300).

Bueno, ¿pero cómo creo exactamente un formato personalizado de número?. Selecciona en tu hoja de cálculo la celda o celdas que quieres formatear, ya sabes llegar al cuadro de diálogo de formato de celdas, y sabes además llegar a la categoría Personalizada. Ahora no tienes más que escribir lo que quieras, Excel lo guardará junto con tu hoja de cálculo cuando la guardes. Solo recuerda el criterio de las cuatro secciones:

Aún hay más, también puedes dar color a tus números, y establecer condiciones que determinen el formato de manera condicional. Respecto a los colores, basta con anteponer entre corchetes un nombre de color, por ejemplo [Rojo]#.##0 mostrará tus números sin decimales, con separador de miles y en color rojo. Los nombres de los colores principales que puedes utilizar son éstos:

Si no te parecen bastantes, utiliza el modificador [Color n], donde n es el código de color (desde 1 hasta 56) correspondiente a la paleta de Excel que esté activa en ese momento. Esta es la paleta estándar de Excel 2003:

Y llegamos a las condiciones para establecer formatos de número. Está muy bien eso de poner colores, pero ¿y si además quiero colores distintos para rangos de valores?. Nada más fácil; la teoría dice que al menos hay que establecer un operador de comparación y un valor:

• El operador de comparación puede ser cualquiera de éstos: <, >, =, >=, <=, <>
• Y el valor obviamente puede ser cualquier número
• La única limitación es que a un número solo pueden asignarse dos condiciones.

Con eso, y sumado a lo que sabemos de los colores, damos formato a esta serie de números en función de su valor por debajo o por encima de 100 de esta forma [Rojo][<=100]Estándar;[Azul][>100]Estándar:

Nota: Esta técnica es similar pero no es la de Formato Condicional al que se llega a través del menú específico ‘Formato>Formato condicional’. Esta última se aplica al formato de la celda en general, no solo al formato de un número, que es lo que estamos viendo ahora (el Formato Condicional se merece un Extreme propio en un futuro).

Nota: No es una limitación al formato de números, sino una característica de Excel (al menos hasta la versión 2003 que nosotros sepamos). Esta consiste en que Excel sigue el estándar IEEE 754 para almacenar y calcular números, lo cual hace que solo tome los primeros 15 dígitos significativos y sustituya los siguientes por ceros. En la práctica, supone que si quieres dar formato al número 1111222233334444 con el código ####-####-####-####, Excel mostrará 1111-2222-3333-4440 en la celda, sin el último 4.

Y si crees que ya lo sabes todo, disfruta con algunos ejemplos:

a) Este es original de http://www.ozgrid.com, y toma los colores y modificadores de texto para clasificar los valores de una columna de números. Además, el asterisco (que es un modificador de formatos de texto) se ocupa de alinear a la izquierda las palabras, mientras que los números quedan de forma natural a la derecha:

b) Microsoft tenía una plantilla de ejemplos de formato de números (de Lori Turner), de donde he tomado y traducido los siguientes. (Algunos incluyen formateo de texto, pero he preferido dejarlos todos):

c) Algunos ejemplos más, de formato de número con condiciones, tomados de una plantilla de Microsoft:

d) A menudo resulta útil mostrar las unidades con las que estás trabajando, pero si escribes texto junto con el número, no puedes hacer operaciones matemáticas después. Para estos casos puedes usar el formato de números de la siguiente forma:

e) Usando medidas variables: Con las condiciones puedes definir varias medidas (por ejemplo para añadir los sufijos millones y miles en función del valor de la celda). Sin embargo hay que señalar que al usar las condiciones de esta forma perdemos la posibilidad de asignar formato a los números negativos.

COMO TRABAJAR MEJOR CON FORMATOS DE NUMERO

1) Atajos de teclado: No hay muchos que hayamos identificado, pero aquí hay cuatro para dar formato rápido a tus números (funcionan al menos en Excel 2003):

2) Barras de herramientas: Sin entrar en personalizaciones, Excel ofrece algunos botones de acceso rápido a través de las barras de herramientas.Mostramos los disponibles en Excel 2003:

3) Recuerda que el formato de números es una técnica que puede aplicarse en cualquier momento en que muestras un número en Excel. Eso incluye los gráficos, en los que puedes mejorar la presentación ajustando los valores de los ejes:

haciendo que los valores cero queden ocultos y el resto en color en función de su valor:

o haciendo que se muestren más o menos decimales según el valor de la serie usando el formato condicionado, o para mostrar los valores negativos entre paréntesis, etc.

4) Utilidades (add-ins) específicas: Tampoco es que existan demasiadas, pero vamos a ello:

4.a) si trabajas con muchos formatos de número a la vez, es posible que te interese instalar esta pequeña utilidad que pone entre tus menús de Excel una exhaustiva relación de formatos de número para aplicarlos rápidamente, o personalizar tus preferidos. Su nombre es NumberFormats, es gratuita y está en inglés (sirve para Excel 2003 y versiones anteriores).

4.b) Ozgrid tiene una utilidad de pago (Number Manager, $30) que tiene buena pinta para automatizar o acelerar las técnicas que hemos descrito en este post. Lo tienes aquí: Ozgrid Number Manager.

5) Para añadir caracteres especiales a tus formatos de número (símbolos de grado Celsius, de copyright, divisas, etc), necesitas conocer los códigos del mapa de caracteres, cuyo equivalente se consigue pulsando la tecla ALT más el código ansi correspondiente:

Por ejemplo, para añadir el símbolo del copyright a la derecha del número, he tenido que introducir ALT+0169 en el campo de formato personalizado. Aquí está la lista completa de códigos, quien sabe cuándo puedes necesitarla:

-

Eso es todo amigos. Agradeceré comentarios y más ejemplos que añadir a este post.