La aritmética de punto flotante puede proporcionar resultados no exactos en Excel

Seleccione idioma Seleccione idioma
Id. de artículo: 78113 - Ver los productos a los que se aplica este artículo
Expandir todo | Contraer todo

En esta página

Resumen

En este artículo se explica cómo almacena y calcula Microsoft Excel los números en coma flotante. Esto puede afectar a los resultados de algunos números o fórmulas debido al redondeo y al truncamiento de los datos.

Información general

Microsoft Excel se diseñó en torno a la especificación de IEEE 754 con respecto al almacenamiento y cálculo de los números en coma flotante. IEEE es el Instituto de ingenieros eléctricos y electrónicos, un organismo internacional que, entre otras cosas, determina estándares para el software y el hardware informático. La especificación 754 se usa mucho y describe cómo se deben almacenar los números en coma flotante en un equipo binario. Se utiliza en gran medida porque permite que los números en coma flotante se almacenen en una cantidad de espacio razonable y que los cálculos se realicen con relativa rapidez. El estándar 754 se utiliza en los procesadores de datos numéricos y unidades de coma flotante de casi todos los microprocesadores basados en PC actuales que implementan una matemática de coma flotante, incluidos los procesadores Intel, Motorola, Sun y MIPS.

Al almacenar números, el número binario correspondiente puede representar cada número o número fraccionario. Por ejemplo, la fracción 1/10 puede representarse en un sistema numérico decimal como 0,1. Sin embargo, el mismo número en formato binario se convierte en decimal binario extensible
0001100110011100110011 (y así sucesivamente)
que puede repetirse de forma infinita. Este número no se puede representar en una cantidad de espacio finita (limitada). Por consiguiente, este número se redondea hacia abajo a aproximadamente -2.8E-17 cuando se almacena.

Sin embargo, hay algunas limitaciones de la especificación IEEE 754 que entran dentro de tres categorías generales:
  • limitaciones de números máximo y mínimo
  • precisión
  • números binarios extensibles

Más información

Limitaciones de números máximo y mínimo

Todos los equipos tienen un número máximo y un número mínimo que pueden administrar. Dado que el número de bits de memoria en los que se almacena el número es finito, el número máximo o mínimo que puede almacenarse también es finito. En Excel, el número máximo que puede almacenarse es 1.79769313486232E+308 y el número positivo mínimo es 2.2250738585072E-308.

Casos en los que se cumple IEEE 754

  • Subdesbordamiento: el subdesbordamiento se produce cuando se genera un número que es demasiado pequeño para ser representado. En IEEE y Excel, el resultado es 0 (con la excepción de que IEEE tiene el concepto de -0, y Excel no).
  • Desbordamiento: el desbordamiento se produce cuando un número es demasiado grande para ser representado. Excel utiliza su propia representación especial para este caso (#NUM!).

Casos en los que no seguimos IEEE 754

  • Números sin normalizar: Un número sin normalizar se indica mediante el exponente cero (0). En ese caso, todo el número se almacena en la mantisa, que no tiene un uno (1) inicial implícito. Como resultado, pierde precisión, y cuanto menor es el número, más precisión se pierde. Los números más pequeños de este intervalo sólo tienen un dígito de precisión.
    Ejemplo: un número normalizado tiene un uno inicial implícito. Por ejemplo, si la mantisa representa 0011001, el número normalizado se convierte en 10011001 debido al uno inicial implícito. Un número sin normalizar no tiene un uno inicial implícito, por lo que en nuestro ejemplo 0011001, el número sin normalizar sigue siendo el mismo. En este caso, el número normalizado tiene ocho dígitos significativos (10011001) mientras que el número sin normalizar tiene cinco dígitos significativos (11001) y los ceros iniciales son insignificantes.

    Los números sin normalizar son básicamente una solución alternativa para permitir que se almacenen los números menores que el límite inferior normal. Microsoft no implementa esta parte opcional de la especificación porque los números sin normalizar, por su propia naturaleza, tienen un número variable de dígitos significativos. Esto puede permitir un error significativo al escribir en los cálculos.
  • Infinitos positivos o negativos: Las infinidades se producen al dividir por 0. Excel no admite infinitos, sino que se devuelve el error #DIV/0! en estos casos.
  • No es un número (NaN): NaN se utiliza para representar operaciones no válidas (como infinito/infinito, infinito-infinito o la raíz cuadrada de -1). Los NaN permiten que un programa continúe después de una operación no válida. En cambio, Excel genera inmediatamente un error como #NUM! o #DIV/0!.

Precisión

Un número en coma flotante se almacena en binario en tres partes dentro de un intervalo de 65 bits: el signo, el exponente y la mantisa.
Contraer esta tablaAmpliar esta tabla
El signo de 1 bitEl exponente de 11 bits1 bit implícitoLa mantisa de 52 bits
El signo almacena el signo del número (positivo o negativo), el exponente almacena la potencia de 2 a la que se eleva o se reduce el número (la potencia máxima o mínima de 2 es +1023 y -1022) y la mantisa almacena el número real. El área de almacenamiento finita para la mantisa limita cuánto pueden acercarse dos números de coma flotante contiguos (es decir, la precisión).

La mantisa y el exponente se almacenan ambos como componentes independientes. Como resultado, la cantidad de precisión posible puede variar según el tamaño del número (la mantisa) que se utiliza. En el caso de Excel, aunque puede almacenar los números de 1,79769313486232E308 a 2,2250738585072E-308, sólo puede usar 15 dígitos de precisión. Esta limitación es un resultado directo de seguir estrictamente la especificación IEEE 754, y no es ninguna limitación de Excel. Este nivel de precisión también se encuentra en otros programas de hoja de cálculo.

Los números en coma flotante se representan en el formato siguiente, donde exponente es el exponente binario:
X = Fracción * 2^(exponente - bias)
Fracción es la parte fraccionaria normalizada del número, normalizada debido a que el exponente se ajusta para que el bit inicial sea siempre un 1. De este modo, no tiene que almacenarse y obtiene más de un bit de precisión. Esto es por lo que hay un bit implícito. Esto es similar a la notación científica, en la que se manipula el exponente para tener un dígito a la izquierda del separador decimal; excepto en binario, siempre puede manipular el exponente para que el primer bit sea un uno, porque hay sólo unos y ceros.

El sesgo es el valor que se usa para evitar almacenar exponentes negativos. El sesgo para los números de precisión simple es 127 y 1023 (decimal) para los números de precisión doble. Excel almacena los números con precisión doble.

Ejemplo de uso de números muy grandes

En un libro nuevo, escriba lo siguiente:
   A1: 1.2E+200
   B1: 1E+100
   C1: =A1+B1
El valor resultante en la celda C1 sería 1.2E+200, el mismo valor que en la celda A1. De hecho, si compara las celdas A1 y C1 mediante la función SI, por ejemplo SI(A1=C1), el resultado será TRUE. Esto se debe a la especificación de IEEE que indica que sólo se almacenan 15 dígitos significativos de precisión. Para poder almacenar el cálculo anterior, Excel requería al menos 100 dígitos de precisión.

Ejemplo de uso de números muy pequeños

En un libro nuevo, escriba lo siguiente:
   A1: 0.000123456789012345
   B1: 1
   C1: =A1+B1
The resulting value in cell C1 would be 1.00012345678901 instead of 1.000123456789012345. El valor resultante de la celda C1 sería 1.00012345678901 en lugar de 1.000123456789012345. Esto se debe a la especificación de IEEE que indica que sólo se almacenan 15 dígitos significativos de precisión. Para poder almacenar el cálculo anterior, Excel requería al menos 19 dígitos de precisión.

Corregir los errores de precisión

Excel ofrece dos métodos básicos para compensar los errores de redondeo: la función ROUND y la opción de libro Precisión de pantalla o Establecer precisión de pantalla.

Método 1: la función ROUND

El ejemplo siguiente, que usa los datos anteriores, utiliza la función ROUND para hacer que un número tenga cinco dígitos. Esto le permite comparar correctamente el resultado con otro valor.
   A1: 1.2E+200
   B1: 1E+100
   C1: =ROUND(A1+B1,5)
da como resultado 1.2E+200.
   D1: =IF(C1=1.2E+200, TRUE, FALSE)
da como resultado el valor TRUE.

Método 2: precisión de pantalla

En algunos casos, quizás pueda evitar que los errores de redondeo afecten a su trabajo mediante la opción Precisión de pantalla. Esta opción hace que el valor de cada número de la hoja de cálculo sea el que se muestra. Para activar esta opción, siga estos pasos:
  1. En Excel 2003 y en versiones anteriores, haga clic en Opciones en el menú Herramientas.
  2. En la ficha Cálculo, haga clic para activar la casilla Precisión de pantalla.
  1. En Excel 2007, haga clic en el Botón de Microsoft Office, haga clic en Opciones de Excel y haga clic en la categoría Opciones avanzadas.
  2. En la sección Al calcular este libro, seleccione el libro que desea y, a continuación, active la casilla Establecer precisión de pantalla.
Por ejemplo, si elige un formato de número que muestra dos posiciones decimales y, a continuación, activa la opción Precisión de pantalla, se perderá toda la exactitud más allá de dos decimales cuando guarde el libro. Esta opción afecta al libro activo incluidas todas las hojas de cálculo. No puede deshacer esta opción y recuperar los datos perdidos. Se recomienda que guarde el libro antes de habilitar esta opción.

Números binarios extensibles y cálculos con resultados cercanos a cero

Otro problema confuso del almacenamiento de números de coma flotante en binario es que algunos números, que son números finitos y no extensibles en base 10 decimal, son números infinitos y extensibles en binario. El ejemplo más común de esto es el valor 0,1 y los tipos Variant. Aunque estos números se pueden representar perfectamente en base 10, el mismo número en formato binario se convierte en el número binario extensible siguiente cuando se almacena en la mantisa:
000110011001100110011 (y así sucesivamente)
La especificación IEEE754 no hace ninguna disposición especial para ningún número; almacena lo que puede en la mantisa y trunca el resto. Esto produce un error de aproximadamente -2.8E-17 ó 0.000000000000000028 al almacenarlos.

Incluso las fracciones decimales comunes, como el 0,0001 decimal, no se pueden representar exactamente en binario. (0,0001 es una fracción binaria extensible con un período de 104 bits). Esto es similar a por qué la fracción 1/3 no se puede representar exactamente en decimal (un número 0,33333333333333333333 extensible).

Esto explica por qué un ejemplo simple en Microsoft Visual Basic para Aplicaciones
   Sub Main()
      MySum = 0
      Para I % = 1 a 10000
         MySum = MySum + 0.0001
      Siguiente I%
      Debug.Print MySum
   End Sub
imprimirá PRINT 0.999999999999996 como salida. El pequeño error al representar 0,0001 en binario se propaga a la suma.

Ejemplo de uso de un número negativo

  1. Escriba lo siguiente en un libro nuevo:
       A1: =(43.1-43.2)+1
  2. Haga clic con el botón secundario del mouse en la celda A1 y, a continuación, haga clic en Formato de celdas. En la ficha Número, haga clic en Científico en Categoría. Establezca Lugares decimales en 15.
En lugar de mostrar 0,9, Excel muestra 0,899999999999999. Dado que (43.1-43.2) se calcula primero, -0.1 se almacena temporalmente y el error de almacenar -0.1 se introduce en el cálculo.

Ejemplo cuando un valor llega a cero

  1. En Excel 95 o versiones anteriores, escriba lo siguiente en un libro nuevo:
       A1: =1.333+1.225-1.333-1.225
  2. Haga clic con el botón secundario del mouse en la celda A1 y, a continuación, haga clic en Formato de celdas. En la ficha Número, haga clic en Científico en Categoría. Establezca Lugares decimales en 15.
En lugar de mostrar 0, Excel 95 muestra -2.22044604925031E-16.

Sin embargo, Excel 97 introdujo una optimización que intenta corregir este problema. Si una operación de suma o de resta produce el valor cero o un valor muy próximo a cero, Excel 97 y las versiones posteriores compensarán cualquier error introducido como resultado de convertir un operando a o desde binario. El ejemplo anterior, cuando se realiza en Excel 97 y versiones posteriores, muestra correctamente 0 ó 0.000000000000000E+00 en notación científica. Para obtener más información al respecto, haga clic en los números de artículo siguientes para verlos en Microsoft Knowledge Base:
172911 Resultado incorrecto al elevar 10 a una potencia muy grande o muy pequeña
214373 Resultado incorrecto al elevar 10 a una potencia muy grande o muy pequeña
Para obtener más información acerca de los números en coma flotante y de la especificación IEEE 754, vea los sitios de World Wide Web siguientes:
http://www.ieee.org

http://stevehollasch.com/cgindex/coding/ieeefloat.html

Referencias

Para obtener más información acerca de cómo evitar estos errores, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
214118 Cómo corregir errores de redondeo en aritmética en punto flotante

Propiedades

Id. de artículo: 78113 - Última revisión: domingo, 18 de septiembre de 2011 - Versión: 2.0
La información de este artículo se refiere a:
  • Microsoft Excel 2010
  • Microsoft Office Excel 2008 for Mac
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X para Macintosh
  • Microsoft Excel 2001 para Macintosh
  • Microsoft Excel 98 para Macintosh
  • Microsoft Office Excel 2003
Palabras clave: 
kbinfo KB78113

Enviar comentarios

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com