Números grandes en Excel – primera nota

miércoles, julio 22, 2009

Un lector me consulta cómo hacer en Excel para que cuando introducimos un número grande éste no se convertido en forma automática a la notación científica.

Por ejemplo, si introducimos el número 123456789123 en una celda, lo que veremos en pantalla es 1.23457E+11



formato númerico en Excel

Esta consulta involucra en realidad dos temas:


# formato automático de números en Excel (cómo exhibe Excel los números en la pantalla)


# cálculos con números de más de 15 cifras significativas.


En esta nota tocaremos el primer tema, y dejaremos el segundo para una futura nota.


Empecemos por ver un ejemplo donde nuestro número tiene por lo menos 12 cifras, pero menos de 15.


Volviendo a nuestro ejemplo (que tiene 12 cifras) veamos qué significa 1.23568E+11.

La notación E+11 es una forma abreviada de escribir el número. Esta notación nos dice que hemos tomado el número y lo hemos divido por 10 elevado la potencia de 11 (es decir 100,000,000,000).


Cuando se trata de números grandes, Excel pone el formato científico automáticamente si la celda está formada como General (el formato por defecto de Excel).

Si queremos evitar que Excel ponga el formato científico tenemos que dar un formato de número a la celda antes de ingresar el número. Si el número tiene menos de 15 cifras, podemos cambiar el formato después de haber introducido el número.


En esta imagen podemos ver que si bien los números en la columna A aparecen en notación científica, Excel los “ve” tal como son; en la columna B vemos los mismos números introducidos en celdas previamente formadas con formato numérico

formato númerico en Excel

Ahora veamos una “curiosidad” de Excel cuando se trata de números grandes

formato númerico en Excel

En las celdas A1:A2 hemos puesto el número 123456789123 que Excel exhibe como 1,23457E+11. En la barra de fórmulas podemos ver que sólo se trata de exhibición; Excel sigue “viendo” el número tal como la introducimos en la celda. En el rango B1:B2 ponemos el mismo número pero previamente hemos formado las celdas como Número.


Ahora usaremos el icono de aumentar decimales para agregar un decimal en las celdas A1 y A2


Como puede observarse, se ha producido una pérdida de precisión. Ahora el número en las celdas, tanto en el rango A1:A2 como en B1:B2, es 123456800000!

formato númerico en Excel

Hasta aquí hemos usado un número de menos de 15 cifras. ¿Qué paso cuando usamos un número de más de 15 cifras?


En la celda A1 introducimos el número 123456789123456789 (18 cifras)

formato númerico en Excel


Sin embargo, el número que aparece en Excel después de apretar Enter es 123456789123456000


Es decir, las últimas tres cifras han sido reemplazadas por ceros!. Toda cifra después de la posición 15, será convertida a cero. Esto se debe a una limitación, no solamente de Excel, que será explicada en la próxima nota sobre el tema.


Lo que debemos saber a esta altura de los acontecimientos es que:


# Excel tiene una limitación de precisión si el número excede las 15 cifras significativas


# para introducir números de más de 15 cifras debemos convertirlos en texto, ya sea anteponiendo un ' o dando el formato Texto a la celda antes de introducir el número.


Esto genera un serio problema si tenemos que realizar cálculos con números grandes. Como enfrentarnos a estos problemas será el tema de la próxima nota.


Technorati Tags:

15 comments:

Anónimo,  20 agosto, 2009 21:09  

Hola queria decirte que me fue de mucha ayuda tu articulo.

MUCHAS GRACIAS POR TU TRABAJO!!

Anónimo,  30 agosto, 2009 23:32  
Este blog ha sido eliminado por un administrador de blog.
Anónimo,  19 octubre, 2009 15:46  

MUY INTERESANTE EL APORTE, ME FUE MUY UTIL. GRACIAS
SEBASTIAN

Anónimo,  08 septiembre, 2010 17:52  

Y la segunda nota?
Necesito utilizar cifras grandes y no encuentro la solucion :(

Anónimo,  30 julio, 2013 18:40  

La segunda nota: http://jldexcelsp.blogspot.com/2009/07/numeros-grandes-en-excel-segunda-nota.html

Nestor Stura 02 noviembre, 2013 21:44  

Gracias por la explicación, muy buena, pero tengo un problema: en mi caso el número es un código, es decir que necesito invariablemente los 15 o más dígitos. He intentado convirtiendo el campo a texto y antes de mostrarlo primero lo transforma a científica. Con el inconveniente de que si quiero exportar esos datos a csv los sigue manteniendo en científica.

Jorge Dunkelman 03 noviembre, 2013 07:08  

Nestor, si definís el rango como texto (Formato de Celdas-Número-Texto), el número de más de 15 dígitos se conserva como texto y así lo exportará al archivo .csv.
¿Podés describir exactamente lo que estás haciendo?

Anónimo,  18 febrero, 2014 21:38  

gracias por el aporte!

Saludos

Anónimo,  16 febrero, 2015 16:20  

ME CAUSO UN MALESTAR, PERO GRACIAS A ESTO YA SE COMO SOLUCIONARLO.

Anónimo,  23 mayo, 2015 05:48  

Este señor Dunkelman es mi heroe en excel. Con sus notas he aprendido muchìsimo. Felicidades y gracias por aportar a la gran comunidad de lectores que lo sigue señor Dunkelman.

Anónimo,  23 mayo, 2015 05:51  

Señor Dunkelman y que sucederìa si necesito exportar desde una base de datos nùmeros con este esquema 453453487E3 . Excel entenderà que el nùmero es notaciòn cientifica y lo transformarà. Yo necesito que lo deje tal cual cuando lo exporto. El tema es que desde la base de datos donde exporto los datos no me permite abrir una plantilla Excel con anticipaciòn para darle formato, sino que el mismo sistema me lo exporta abriendo el Excel y como usted sabrà, la hoja viene formateada en General.

Jorge Dunkelman 23 mayo, 2015 08:18  

Hola, si el esquema de los datos es 453453487E3, Excel lo entenderá como texto. Para tener control de como Excel importa los datos te convendría, de ser posible, exportar los datos como archivo de texto (.txt o .csv) y luego usar el asistente paara importar texto para deterninar cada campo.

Anónimo,  28 junio, 2015 08:02  

AUNQUE PONGA EN TEXTO EL NUMERO 12900110000002060943 LO CONVIERTE EN 1.29001E+19 SI LO PONGO EN FORMATO NUMEROS SALE 129001100000020000000, CUAL ES LA SOLUCION PARA QUE ME SALGA 12900110000002060943 PORQUE ESTO ES UN NUMERO DE CUENTA

Jorge Dunkelman 28 junio, 2015 15:08  

Estimado,
dos posibilidades:
1-Anteponer un apóstrofe al número ('12900110000002060943)
2-Definir la celda con formato Texto antes de introducir le número de cuenta.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP