Este artículo revela secretos ocultos sobre el formato de números de Excel. Cómo puede almacenar números en Excel pero agregar palabras junto a los números. Siga leyendo para obtener más consejos interesantes sobre el formato de números.
Excel es increíble para almacenar un número y presentar otro número. Elija cualquier celda y seleccione Formato de moneda. Excel agrega un signo de dólar y una coma y presenta el número redondeado a dos lugares decimales. En la siguiente figura, la celda D2 realmente contiene 6.42452514. Afortunadamente, el formato numérico personalizado incorporado presenta los resultados en un formato fácil de leer.
El código de formato de número personalizado en D2 es $ #, ## 0.00. En este código, los 0 son dígitos obligatorios. Cualquier # son dígitos opcionales.
Sin embargo, los códigos de formato pueden ser mucho más complejos. El código anterior tiene un formato. Ese formato se aplica a todos los valores de la celda. Si proporciona un código con dos formatos, el primer formato es para números no negativos y el segundo formato es para números negativos. Separa los formatos con punto y coma. Si proporciona un código con tres formatos, el primero es positivo, luego negativo y luego cero. Si proporciona un código con cuatro formatos, se utilizan para positivo, negativo, cero y texto.
Incluso si está utilizando un formato integrado, puede ir a Formato de celdas, Número, Personalizado y ver el código utilizado para generar ese formato. Aquí está el código para el formato de contabilidad:
Para crear su propio formato personalizado, vaya a Formato de celdas, Número, Personalizado e ingrese el código en el cuadro Tipo. Consulte el ejemplo en el cuadro Muestra para asegurarse de que todo se vea correcto.
En el siguiente ejemplo, se utilizan tres zonas. El texto entre comillas se agrega al formato de número para personalizar el mensaje.
Si crea una zona para cero pero no coloca nada allí, ocultará todos los valores cero. El siguiente código usa códigos de color para positivo y negativo. El código termina en punto y coma, creando una zona para valores cero. Pero como la zona está vacía, no se muestran los valores cero.
Puede ampliar esto dejando todas las zonas en blanco. Un código de formato personalizado ;;; ocultará los valores en la pantalla y en la impresión. Sin embargo, aún podrá ver los valores en la barra de fórmulas. Si oculta los valores haciendo que la fuente sea blanca, el ;;; permanecerá oculto incluso si las personas cambian el color de relleno. La siguiente figura incluye algunos trucos de formato interesantes.
En B2 y B3, si pone ** antes del código numérico, se completará a la izquierda del número con asteriscos como lo harían las antiguas máquinas de escritura de cheques. Pero no hay nada que diga que debe usar asteriscos. Todo lo que ponga después del primer asterisco se repite para llenar el espacio. Fila 3 usos *! para repetir los signos de exclamación.
En B4 y B5, cada coma que coloques después del cero final dividirá el número entre 1,000. El código 0, K muestra números en miles, con una K después. Si desea mostrar millones, use dos comas. El código "M" se debe poner entre comillas, ya que M ya significa meses.
En B6 coloque un mensaje severo en la cuarta zona para alertar a cualquiera que ingrese datos de que desea un número en la celda. Si ingresan texto accidentalmente, aparece el mensaje.
En B7 a B9, las zonas normales Positivas, Negativas y Cero se sobrescriben con las condiciones que usted pone entre corchetes. Los números menores a 70 son rojos. Los números superiores a 90 son azules. Todo lo demás es negro.
En B10, esos símbolos _ (impares en el formato de contabilidad le dicen a Excel que deje tanto espacio como tomaría un paréntesis izquierdo. Resulta que un guión bajo seguido de cualquier carácter dejará tanto espacio en blanco como ese carácter. En B10, el código contiene 4 ceros. Pero hay diferentes cantidades de espacio entre cada uno. El espacio entre 1 y 2 es el ancho de 2 caracteres WW. El espacio entre 2 y 3 es el ancho de una N. El espacio entre 3 y 4 es el ancho de una letra i minúscula.
La siguiente figura muestra varios códigos de formato de fecha.
Tenga en cuenta que el formato mmmmm en la fila 8 es útil para producir etiquetas de gráficos JFMAMJJASOND.
Gracias a Dave Baylis, Brad Edgar, Mike Girvin y @best_excel por sugerir esta función.
Ver video
- El formato numérico es una fachada: Excel almacena una cosa, nos muestra otra.
- Por ejemplo: use decimales decrecientes para mostrar una versión redondeada del número.
- Excel todavía almacena todos los decimales, pero simplifica la visualización.
- Hay 11 formatos de números en el menú desplegable de la pestaña de inicio.
- Haga clic en el iniciador de diálogo para acceder al diálogo Formato de celdas.
- Hoy, estamos hablando de los códigos de formato en el grupo Personalizado.
- No tengo todo el conocimiento aquí hoy, así que si tienes mejores consejos, colócalos en los comentarios de YouTube.
- Un cero en el código de formato indica que Excel debe mostrar el dígito.
- Un signo # dice que pueden mostrar el dígito si hay suficiente precisión, pero no tienen que hacerlo cuando no lo hay.
- UN ? dejará espacio para que el dígito mantenga alineado el decimal.
- Puede haber 1, 2, 3 o 4 formatos, separados por punto y coma.
- Cuando hay 2 formatos, el primero es para cero y más. El segundo es negativo.
- Cuando hay 3 formatos, el tercero es cero.
- El cuarto formato es para texto.
- Usar el formato de número para mostrar texto con número o para mostrar texto en lugar de número.
- Especificar un color para una zona.
- Para cambiar (Color 5) visite las opciones de Excel.
- * x para repetir x hasta los números
- , al final para dividir por 1000
- ,, divide por millones
- Condiciones de uso en la zona
- _x dejará un espacio del tamaño de "x"
- m mm mmm mmmm mmmmm
- d dd ddd dddd
- (h): mm
- Gracias a Dave Baylis, Brad Edgar, Mike Girvin y @best_excel por sugerir esta función.
Transcripción del video
Aprenda Excel del podcast, episodio 2044 - ¡Formateo como fachada!
Muy bien, estoy publicando en podcast todos mis consejos en este libro, haga clic en la "i" en la esquina superior derecha para acceder a la lista de reproducción.
Hoy estamos hablando de formato de número personalizado, así que tengo Cantidad, Ingresos y estamos mostrando un montón de decimales aquí. Y, ya sabes, si no queremos jugar todos esos decimales, y no queremos usar la función REDONDEAR, simplemente vengo aquí y uso Disminuir decimal. no cambia el número que en realidad está almacenado detrás de escena, solo muestra la forma en que Excel nos muestra el número. Muy bien, estoy robando el término de Mike Girvin, que estamos usando el formato como fachada, cierto, hay un montón de decimales almacenados en esa celda, pero estamos usando formatos numéricos personalizados para mostrar eso. Y quiero ir más allá de los 11 formatos aquí, e incluso hacer clic en ese lanzador de diálogo, más allá de la mayoría de las cosas que están aquí, y quiero hablar sobre Personalizado.Los formatos de números personalizados nos dan el mundo, ¿de acuerdo?
Y aquí hay un montón de formatos de números personalizados que puede escribir en este cuadro, y los resultados se muestran a la izquierda. Entonces, solo General, obtienes todos los lugares decimales, pero aquí, los ceros antes del lugar decimal obligan a que aparezcan los ceros. Entonces, si el número <100, obtendré un 0 a la izquierda, a veces eso es útil para los números de pieza, los ceros después de los lugares decimales obligan a que aparezcan los números. El hashtag o el signo de almohadilla (#) antes del lugar decimal es opcional, está bien, entonces puede estar ahí, no tiene que estar ahí. Muy bien, en este caso tenemos el número 6, pero como tenemos cinco ceros, nos da ceros a la izquierda. Ahora, si necesita BUSCARV en una sección de texto que tiene 00006, esto no coincidirá, pero si solo necesita ver bien, funcionará. OK, algunas cosas interesantes aquí,usamos mucho signos de libra o hashtags, mucho "hashsigns". En los EE. UU. Lo llamamos un signo de libra, y entiendo que en Inglaterra no es un signo de libra, £ es su moneda y lo llama hash. Muy bien, lo siento, lo he estado llamando signo de libra toda mi vida, y es difícil para mí comenzar a llamarlo, pero trabajaré en eso.
Muy bien, aquí abajo el .000 dice que vas a mostrar los ceros si hay precisión o no, y cambié estos números para que sean 6.42 y 6.4. Ahora bien, esto es interesante, este es uno que nunca supe realmente, si pones un? en lugar del 0, dice que mostrará la precisión si está allí, pero si no, no la muestre, pero no deje que las cosas fluyan hacia la derecha. Muy bien aquí, cambiemos estos números, 6.42 y 6.4, está bien, mira aquí, los decimales no se quedan alineados. Si desea que los decimales permanezcan alineados, use los signos de interrogación, si desea forzar que los ceros estén allí, use los ceros.
Ahora bien, a medida que avanzamos por aquí, hay MUCHOS detalles en minucias, pero no tengo la esquina en el conocimiento aquí. Si ves que me pierdo algún truco genial de formato de números, déjalo en los comentarios de YouTube. Muy bien, aquí está el siguiente concepto, podemos mostrar 1, 2, 3 o 4 formatos de números en el cuadro Tipo, si muestra solo 1, es solo el número que se usa para todo, de acuerdo. Pero si muestra 2 y están separados por un punto y coma, entonces el primero es para números> = 0 y el segundo para números <0. Así que aquí mismo he usado dos códigos, +0; -0, y eso significa que los números negativos aparecen con un menos, pero los números positivos aparecen con el más, pero porque no especifiqué qué sucede con 0 , también aparece con un plus.
Entonces podemos agregar una tercera zona: positiva, negativa, cero, bien, entonces +0; -0; 0, bien, para que maneje ceros. Incluso puede haber un cuarto código de qué hacer si alguien ingresa texto. Muy bien ahora, próximo concepto, en el formato numérico personalizado, además de los ceros, ¡puedes poner texto entre comillas! Muy bien, echemos un vistazo al formato de número personalizado aquí, para números positivos, las palabras "Remitir" y luego el número, para el "Saldo de crédito negativo" del número y "(No pagar)". Y para 0, ni siquiera muestro el número, solo muestro "Sin saldo", de acuerdo. Entonces puedes usar esto para reemplazar el número, esto es -1, 0 y 1, y estas son fórmulas, para obtener la fórmula es solo copiar eso. Todos los números negativos dirán "¡Perdedor!" o "Abajo" o la palabra "Menos" y el número, por lo que ponemos -5, todavía dice "¡Perdedor!" o "Abajo" o "Menos 5".Para 0, podemos hacer que diga "Cero" o "Plano" o decir "0", y luego para un número positivo, pongámoslo en 55, "¡Ganador!", "Arriba" o "Positivo 55", bien, entonces puedes agregar palabras. Y lo que es genial, esto es 55, 55, 55, si SUMA eso, son realmente números, y realmente va a funcionar, como normalmente el SUM no funcionaría con texto, pero eso en realidad me da una respuesta . Entonces, si desea mantener los números allí, pero mostrar palabras en las celdas, este es un gran truco.No trabajo con texto, pero eso me da una respuesta. Entonces, si desea mantener los números allí, pero mostrar palabras en las celdas, este es un gran truco.No trabajo con texto, pero eso me da una respuesta. Entonces, si desea mantener los números allí, pero mostrar palabras en las celdas, este es un gran truco.
Muy bien, estos son los caracteres que se pueden usar en el formato de número personalizado sin comillas, por lo que si solo necesita un + o un -, es por eso que no tiene que ponerlo entre comillas. Y hay algunas adiciones a esto, como una vez que vi esta lista, dije "Está bien, veamos con qué podemos jugar". Todos estos son los números 1 2 3 4, así que si utilicé un formato de número personalizado de ((número> &, se mostrará todo. Si quiero separar los dígitos por guiones, este es un formato de número personalizado para obtener que funcione. Y todos son reales, así que 7854, mira todo cambia, todas estas son fórmulas que vienen aquí. Así que puedo jugar con el formato numérico personalizado, y poner caracteres antes y después. no en la lista que sé que funciona es K, se le permite poner una K al final, y 'no está entre comillas. Pero M, debido a que M significa meses o minutos, tendrías que ponerlo entre comillas o poner esta M para que aparezca una M al final.
Muy bien, siguiente concepto, en cada zona se le permite especificar el color, correcto, así que aquí arriba los números positivos son verdes y los números negativos son azules. Ctrl + 1 para acceder a Formato de celdas, antes del formato de número personalizado, en la primera zona, la zona positiva, dije verde, y para los números negativos dije azul. ¿Qué color podemos usar allí? Vuelve a los días de 8 colores, por lo que negro, verde, blanco, azul, magenta, amarillo, cian y rojo. Pero gracias a Mike Alexander, puedes usar otros 56 colores, Color 1, Color 2, tiene que estar en (), así, esos son los 56 colores. Si necesita cambiar esto, nos estamos profundizando mucho ahora, ¡ahora vamos al territorio de los niños de Charlie! Opciones de Excel, Guardar, y luego haga clic en Colores, está bien, y me gusta, si quisiera cambiar esa naranja a otra cosa, tal vez la púrpura, haga clic en Aceptar, haga clic en Aceptar,haga clic en Aceptar, y uno de estos, no puedo decir cuál habría cambiado a ese color. Otro truco genial, ¿y si quieres ocultar todos los números? Así que tienes algunos secretos aquí y no quieres que nadie los vea. Ctrl + 1, poner un formato de número personalizado, digo "No mostrar nada para positivo, nada para negativo, nada para 0", haga clic en Aceptar y todo estará oculto. Por supuesto, todavía pueden verlo aquí en la barra de fórmulas, por lo que no es perfecto, está bien, aquí hay otros caracteres de formato del cliente.nada para negativo, nada para 0 ”, haga clic en Aceptar y todo estará oculto. Por supuesto, todavía pueden verlo aquí en la barra de fórmulas, por lo que no es perfecto, está bien, aquí hay otros caracteres de formato del cliente.nada para negativo, nada para 0 ”, haga clic en Aceptar y todo estará oculto. Por supuesto, todavía pueden verlo aquí en la barra de fórmulas, por lo que no es perfecto, está bien, aquí hay otros caracteres de formato del cliente.
Un asterisco dice "Mostrar el siguiente carácter como un escritor de cheques". Entonces, esos ** dicen "Sabes, llena el área hasta el número con asteriscos", pero puedes cambiar eso, ¡puedes hacer que ese carácter sea lo que quieras! Entonces *! pondrá todos los signos de exclamación allí, cambiemos esto, Ctrl + 1, lo cambiaré a * X, y obtendré todas las X hasta ese punto. Entonces puede crear el efecto de escritura de cheques, pero no tiene que estar con un asterisco, puede poner lo que quiera allí. Ponga una coma al final, por lo que este es 123456, formato de número personalizado con una coma después del cero que dice "Muestre esto en miles, dividido por 1000", pongo la K allí para miles. Cuando quiera hacerlo en millones, entonces está nuestro 0.0, cada coma dice "Mostrar, pero dividirlo por otros 1000". Entonces eso es dividir por un millónponer la "M" para mostrar millones. Sé que algunas personas usan MM allí, mi hermana Alice (?) Que trabaja para Procter & Gamble, "MM" tiene que estar entre comillas.
Ahora aquí, este es uno en el que estamos forzando un número, si alguien ingresa algo que no es un número, el mensaje en la cuarta zona lo sobrescribirá. Entonces, si ponemos el número 1, es genial, ponemos -1, es genial, si ponemos "Hola", nos grita, "¡Ingrese un número!". Muy bien, de nuevo, estas son fórmulas que llevan todo lo que escribo de A a B. Puedes usar condiciones, de acuerdo, entonces el positivo, negativo, cero, puedes anular eso. Entonces, voy a decir rojo si es 90, y solo puede tener dos condiciones, y luego una tercera zona para cosas que no son 90, formato condicional antiguo y anticuado. Otro, aquí hay otro, esto es simplemente una locura, es poner un guión bajo y luego cualquier personaje deja suficiente espacio para adaptarse a ese personaje.Ves esto mucho con _ (para mantener los números positivos alineados con los números negativos, pero realmente puedes arruinar con Excel. Entonces, W es el carácter más ancho, entonces esto es poner el primer dígito, y luego suficiente espacio para 2 W , y luego el segundo dígito y suficiente espacio para una N, y luego el tercer dígito y suficiente espacio para una I, y finalmente, el cuarto dígito. ¿Es esto útil para algo? Vaya, no lo sé, pero es amable de salvaje, que puede forzar un espaciado diferente entre los dígitos utilizando los guiones bajos.pero es un poco salvaje, que puede forzar diferentes espacios entre los dígitos usando los guiones bajos.pero es un poco salvaje, que puede forzar diferentes espacios entre los dígitos usando los guiones bajos.
Muy bien, fechas, m / d / aa, obtienes un año de 2 dígitos, expande a aaaa, obtienes un año de 4 dígitos, pon mm, fuerza un 0 inicial antes del 7, dd fuerza un 0 inicial antes del 3 Si desea utilizar algún formato de fecha de cobalto antiguo, no ponga ningún /, solo todos los caracteres, AAAAMMDD. mmm deletrea la abreviatura del mes, mmmm deletrea el mes completo, mmmmm le da el formato JASON. ¿Por qué lo llamo formato JASON? Si miras el Wall Street Journal, sus cortos financieros siempre tienen JFMAMJJASOND. En 1984, quiero una copia en vinilo de "Frankie & the Knockouts - Below the Belt" para responder a la pregunta de trivia "¿Qué nombre de hombre se puede escribir usando las abreviaturas de los meses?" Y como estaba leyendo The Wall Street Journal en la escuela, sabía que Jason era el nombre y lo gané.Probablemente lo regalé en una venta de garaje por $ 50c, mi culpa. Muy bien, dd le da el primer dígito antes de la fecha, ddd deletrea la abreviatura, dddd le da el nombre completo del día, el nombre del día de la semana, ddddd no hace nada. Puedes combinarlos como quieras. dddd deletrea el día, martes, ponga la palabra "el" entre comillas, el día en sí de mmm, le da la abreviatura, está bien, así que puede combinarlos como desee.
El tiempo, una cosa extraña con el tiempo, h: mm, o AM / PM, así que si no pones el AM / PM lo fuerza en el tiempo de 24 horas, si lo haces, entonces obtienes 2:15 PM. Si tiene hh, fuerza el cero inicial antes del 2, incluso puede agregar .0 después de los segundos para obtener milisegundos. Ahora, aquí está lo extraño, en realidad antes de que llegue a lo extraño, no sé, hagamos lo extraño primero. Hagamos = SUMA de todos esos, entonces todos son 14 horas, 6 * 14 = 84, deberían ser 84 horas, pero si lo formateo como una hora, dice 1:30 de la tarde, está bien, y aquí está la cosa. Así que tenemos un número aquí, esta mañana 9/10/2016 a las 6 a.m., y solo tengo fórmulas que lo copian, y puedo anularlo, recuerde, está formateado como una fachada. Puedo decir "Oye, muéstrame los años, muéstrame el mes, muéstrame los días,solo muéstrame la parte de tiempo de eso ". Y aquí en este SUM, cuando pedimos una hora, decimos "Oye, ignora los años, los meses, los días, muéstrame la hora". y lo que realmente es esto, son tres días + 1:30 de la tarde.
Entonces, para que esto se muestre correctamente, ingrese a Time y busque el que dice 37 horas, o vaya a Personalizado, y verá que la (h) dice "Muéstranos el número completo de horas . " Entonces, si necesito ver horas y minutos, (h): mm me mostrará eso, eso es en realidad 85.5 horas, ya veo, estaba cancelando los 15 minutos, tratando de obtener los 84 en mi cabeza, estaba cerca. ¿Dónde más puedes usar corchetes? Entonces, 6 a. M., Puede mostrar las horas, puede mostrar solo los minutos, 6 a. M. Son 360 minutos o solo los segundos usando los corchetes. Muy bien, volvamos a uno de los formatos numéricos que tenemos bajo el formato de contabilidad, y veamos si ahora, después de este episodio tan largo, entendemos lo que está pasando.
Entonces, ¿qué dice esto? _ (Dice "Deja tanto espacio como a (en el valor positivo". "*" Dice "Repite espacios hasta que llegues a los números"). El #, ## 0 dice "Vamos para usar el separador de coma, no coloque caracteres iniciales si no tenemos la precisión, siempre coloque dos lugares decimales ”. Y luego _) dice“ Deje tanto espacio como), y eso es para los números positivos ”. Bien, ahora, para los números negativos, es realmente extraño aquí, antes de los $ están dejando tanto espacio como un (en otras palabras, nunca van a mostrar eso. De nuevo "*", pero aquí ellos ' ahora vamos a poner el (y el). Y luego para 0, deja tanto espacio como el (y tanto espacio como el). No pongas nada, estamos reemplazando el cero con el "-", pero luego deje tanto espacio como dos lugares decimales, de esa manera que 'va a alinearse con esto. Y finalmente, en la cuarta zona, si escriben texto, deje suficiente espacio para (y a), y luego coloque el texto.
Muy bien, esto siempre fue un montón de tonterías que yo, pero hay significados específicos para todos esos personajes. Es una locura que toda esta información, 18 minutos de podcasts, provenga de 3 páginas de este libro, imagínense cuánto hay en las 200 páginas del libro. Compre el libro, haga clic en la "i" en la esquina superior derecha, lo apreciará y yo lo agradeceré. Muy bien, episodio largo, largo: el formato de números es una fachada, Excel almacena una cosa, nos muestra otra. Decrease Decimal y Increase Decimal es un gran ejemplo de eso, todavía almacenan todos los decimales, pero simplifica la visualización. Aquí hay 11 formatos, pero realmente para llegar a ellos, vaya al iniciador de diálogo, y luego puede elegir algo y luego modificarlo en Personalizado si lo desea. Hablamos de todos estos códigos de formato de números personalizados,Si tiene mejores consejos, colóquelos en los comentarios de YouTube.
Cero en el código de formato dice "Excel DEBE mostrar el dígito, ya sea que haya precisión o no". Signo de almohadilla, signo de libra dice que pueden mostrar el dígito si hay precisión, pero no es necesario. El signo de interrogación dice "Deje el espacio para el dígito si no hay suficiente precisión". Puede haber hasta 4 formatos separados por punto y coma, de esa manera tenemos un número diferente para positivo, negativo, cero y texto. 2 formatos, el primero es para no negativo, el segundo es para negativo, si tenía un tercer formato, el tercer formato es para 0, el cuarto formato es para texto, si está ahí. Muy bien, podemos mostrar texto con un número, o mostrar texto en lugar del número, "Ganador", "Perdedor" o "Arriba", "Plano", "Abajo". Puede especificar un color para cada zona, para cambiar los códigos de color visite Opciones de Excel, Guardar, Colores.Asterique cualquier carácter para repetir ese carácter antes de los números, como haría un escritor de cheques. Ponga una coma al final para dividir por mil, dos comas para dividir por millones, 3 comas para dividir entre miles de millones, y así sucesivamente. Podemos usar condiciones en la zona, y luego subrayar X dejará un espacio del tamaño de X. De acuerdo, las diferentes variaciones de meses, ya sea que pones un dígito, dos dígitos, la abreviatura, la deletrea o solo la primera inicial . Días, un dígito, dos dígitos, abreviar o deletrear, no hay un quinto. Y finalmente, para obtener tiempos superiores a 24 horas, debe poner esa h entre corchetes y otras variaciones de eso.Podemos usar condiciones en la zona, y luego subrayar X dejará un espacio del tamaño de X. De acuerdo, las diferentes variaciones de meses, ya sea que pones un dígito, dos dígitos, la abreviatura, la deletrea o solo la primera inicial . Días, un dígito, dos dígitos, abreviar o deletrear, no hay un quinto. Y finalmente, para obtener tiempos superiores a 24 horas, debe poner esa h entre corchetes y otras variaciones de eso.Podemos usar condiciones en la zona, y luego subrayar X dejará un espacio del tamaño de X. De acuerdo, las diferentes variaciones de meses, ya sea que pones un dígito, dos dígitos, la abreviatura, la deletrea o solo la primera inicial . Días, un dígito, dos dígitos, abreviar o deletrear, no hay un quinto. Y finalmente, para obtener tiempos superiores a 24 horas, debe poner esa h entre corchetes y otras variaciones de eso.
¡Gracias por pasar el rato hasta el final! ¡Nos vemos la próxima vez para otro netcast de!
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2044.xlsm
