sábado, 27 de marzo de 2010

REDONDEAR números a miles o millones.

Ya vimos en un post anterior algunas pistas de cómo configurar un formato de número personalizado; aprovecharemos la cuestión de un lector para adentrarnos un poco más en este mundo:
...necesito que aparezcan sólo los primeros digitos de millones y miles e.j. 366.000.000 asi 366...

Para esta labor deberemos configurar o personalizar nuestra celda como ya aprendimos en el post comentado, es decir, en la ventana de Formatos de celdas, en el tipo escribiremos:

[>=1000000]#.##0,00.." M";[>=1000]#.##0,00." K";#.##0,00


Podemos ver cómo quedarían algunos ejemplos de números con este formato:


Sin embargo, debemos recordar que esto es sólo una máscara de la celda, i.e., el valor de la celda sigue siendo el mismo. Si pretendemos cambiar el valor de nuestra celda, deberemos aplicarle algún tipo de formulación, en mi caso me he decantado por usar la función REDONDEAR:

=SI(COCIENTE(B2;1000000)>0;REDONDEAR(B2;-5)/1000000;SI(COCIENTE(B2;1000)>0;REDONDEAR(B2;-3)/1000;REDONDEAR(B2;-1)/1))

¡¡no nos asustemos!!, es más larga que complicada. Lo único que hace es determinar si el valor a cambiar está definido en millones o en miles, aplicándole en cada caso una función de redondeo.
Esta función REDONDEAR(valor; num_decimales) tiene la particularidad que al forzar el argumento 'num_decimales' con un valor en negativo, lo que hace es redondear el valor a la posición decimal indicada a la izquierda del separador decimal.
Lo vemos...


haz click en la imagen


Deberemos tener especial cuidado si optamos por esta forma, ya que tras la conversión perdemos la referencia de qué valores eran millones, cuáles miles y cuáles son unidades.

32 comentarios:

  1. Aparecio el comentario?

    ResponderEliminar
  2. Sin formato
    1500

    Con formario
    1500.000. K

    este resultado me da con la formula:
    [>=1000000]#.##0,00.." M";[>=1000]#.##0,00." K";#.##0,00

    ResponderEliminar
    Respuestas
    1. Hola...
      he probado tu fórmula, en formato personalizado, y aplicando ese formato sobre el valor 1500, me devuelve lo que era de esperar, esto es, 1,50 K...
      ¿Has seguido los pasos del ejemplo??, te has asegurado que el valor 1500 es tipo número??

      Eliminar
  3. de antemano agradesco tu atencion

    ResponderEliminar
  4. Buenas introduzco un numero ejemplo 3 y la celda me tira 0,3 ya revise todos los formatos sin resultado positivo no se que estará fallando...

    ResponderEliminar
    Respuestas
    1. Hola!!
      bueno, es algo bastante raro.. pero te diría que fueras a las Opciones de Excel > Avanzadas > sección Opciones de edición > marcar/desmarcar Insertar automáticamente un punto decimal; probablemente lo tendrás marcado, desmárcalo y listo.
      Por algún motivo se habrá activado sólo...

      Espero te sirva.
      Slds

      Eliminar
  5. como mostrar un valor que se encuentra en pesos a millones de pesos en la celda

    1.000.000.000 millones de pesos debe aparecer en la celda 1.000 millones de pesos:


    1000000000/1000000 = 1000

    que formato en muestra personalizada debo incluir?

    Gracias.

    ResponderEliminar
    Respuestas
    1. Hola que tal, espero estés bien.
      En el formato personalizado de celda para conseguir lo más parecido sería en Tipo:
      [>=1000000]#.###0,000.." M";Estándar

      pero te aparecerán separado con , en lugar del . decimal (dependerá de la configuración regional de tu sistema).

      Espero te pueda servir.
      Slds

      Eliminar
  6. bUENAS TARDES
    NECESITO EXPRESAS CIFRAS EN MILLONES DE PESOS EN MILES DE PESOS, POR EJEMPLO $1.000.000 QUE ME QUEDE COMO $1.000

    ResponderEliminar
    Respuestas
    1. Hola,
      sólo sigue las indicaciones de esta misma entrada...
      o el comentario anterior al tuyo.
      Slds

      Eliminar
  7. Hola....y cómo lo hago para expresar los negativos??..

    ResponderEliminar
    Respuestas
    1. Hola Rodrigo,
      mañana subiré un post explicando algo más el tema de los formatos personalizados.
      En él encontraras la respuesta.
      Slds

      Eliminar
    2. Gracias, Ismael. Como expliqué anteriormente traté de hacerlo con lo que tenías, pero no me resulta. Y si hago algo de la forma +redondear(expr; dec)/1000000)&" M", excel no lo opera con otras celdas que utilizan tu formato....
      Un Saludo desde Chile!

      Eliminar
    3. Hola Rodrigo,
      echa un vistazo a esta entrada:
      http://excelforo.blogspot.com.es/2013/04/formato-personalizado-avanzado-en-excel.html
      Prueba de todas formas con este formato:
      #.###0,000.." M";-#.###0,00.." M"

      Espero te sirva
      Slds

      Eliminar
    4. Gracias por tu tiempo y respuesta, Ismael. Ya había revisado esa entrada y me fue muy útil. Respecto al formato de prueba funciona perfecto para los millones, pero como ya utilicé el anterior que deja millones y miles, necesito uno que haga lo mismo en millones y miles en negativo (ej: -1,69 K ó -1,69 M)..
      Gracias!

      Eliminar
    5. Pues Rodrigo,
      no creo sea posible con formato personalizado. Si has leído las entradas anteriores, el formato personalizado tiene cuatro secciones, las tres primeras para números y la cuarta para texto, asi que sólo podemos configurar hasta tres condiciones para los números... y en tu caso, si lo entiendo bien, quieres Millones y Miles para positivos y también para negativos.

      pero lo que si puedes hacer es aplicar un Formato condicional, de tal forma que configures DOS reglas, una para los positivos asignándole un formato de número personalizado:
      [>1000000]##.##0,000.." M";[>1000]#.##0,000." K"
      y otra regla para los negativos aplicándole un formato de número personalizado:
      [<-1000000]-##.##0,000.." M";[<-1000]-#.##0,000." K"

      Así cubres los cuatro casos que te interesan...
      Espero te sirva.
      Slds

      Eliminar
    6. Gracias, Ismael. Voy a hacer las pruebas y posteo los resultados.

      Slds

      Eliminar
    7. Perfecto Rodrigo,
      creo que esta opción puede ser buena.
      Slds

      Eliminar
  8. hola excel foro tengo una pregunta como hago para que excel me genere una alerta cuando no se realiza una tarea por ejemplo tengo que visitar varias sedes de una escuela digamos 5 en una semana y solo visito como hago para que excel me genere un alerta y me muestre las escuelas faltantes
    PD:se que le pregunta no viene a este tema
    de antemano muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola!!
      no importa que la pregunta no esté relacionada con la entrada ;-)

      Entiendo que controlas en una plantilla de Excel las Sedes (escuelas visitadas), completando algunas celdas... si es así, podrías aplicar algún Formato condicional que resaltara con algún color el fondo de la celda que no haya sido visitada (que no se haya completado ).

      Es dificil afinar más sin conocer la estructura de tu Control.
      Si quieres adjúntame el fichero a
      excelforo@gmail.com

      Slds cordiales

      Eliminar
  9. Hola tengo un lió, si los miles es más grande que los millones o los millones más grandes que los miles, gracias por tu ayuda

    ResponderEliminar
    Respuestas
    1. Hola Ernesto...
      no comprendo la pregunta.. ¿está relacionada con el post??
      Si es una pregunta general, obviamente los Millones son mayores que los Miles.
      Slds

      Eliminar
  10. buenos dias ,quisiera que me ayudaras con la siguiente pregunta .redondea el numero hasta la unidad de millon por ejemplo 2600210

    ResponderEliminar
    Respuestas
    1. Hola!
      entiendo que quieres obtener el redondeo real y no visual como lo explicado en este post.
      Puedes aplicar la función
      =REDONDEAR(cifra;-6)

      Saludos cordiales

      Eliminar
  11. Buenas tardes estimados

    Consulta como puedo hacer que esto funciones sin las letras es decir que si tengo 25.351.215 quede como 25,35 y si tengo 452.251 quede como 0,45 para que la unidad de sea siempre los millones, desde ya agradeciendo su ayuda

    Saludos

    ResponderEliminar
  12. Muy bueno tu post, muchas gracias!! pude aplicarlo en un gráfico.

    ResponderEliminar
  13. =SI(COCIENTE(B2,1000000)>0,REDONDEAR(B2,-5)/1000000 &"M",SI(COCIENTE(B2,1000)>0,REDONDEAR(B2,-3)/1000&"K",REDONDEAR(B2,-1)/1))

    ResponderEliminar
    Respuestas
    1. Muchas gracias Sergio,
      la diferencia fundamental es que tu fórmula redondea realmente el valor de la celda, mientras que lo expuesto en el post es únicamente un redondeo visual, a través del formato... esto es, el número no cambia!

      Un saludo!

      Eliminar
    2. Tienes toda la razón, dejo una formato para los que tenemos el Excel en Español Latino

      [>=1000000]#,##0.00,," M";[>=1000]#,##0.00," K";#,##0.00

      Saludos

      Eliminar
  14. HOLA ISMAEL, SI YO TENGO 3800543.00 PERO QUE TODAS MIS CELDAS ME LAS REDONDEE A MILLONES DE PESOS Y ME QUEDE ASÍ: 3.80 QUE FORMULA TENDRÍA QUE USAR?

    ResponderEliminar
    Respuestas
    1. Hola Martha,
      si quieres aplicar un formato, i.e., no cambiar el valor de la celda, puedes aplicar este formato de celda personalizado:
      #.##0.00..

      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.