Al igual que con otras hojas de cálculo, Microsoft Excel funciona solo con una precisión limitada porque retiene solo un cierto número de cifras para describir números (tiene una precisión limitada ). Con algunas excepciones con respecto a valores erróneos, infinitos y números desnormalizados, Excel calcula en formato de punto flotante de doble precisión a partir de la especificación IEEE 754 [1] (además de números, Excel usa algunos otros tipos de datos [2] ). Aunque Excel puede mostrar 30 lugares decimales, su precisión para un número específico se limita a 15 cifras significativas , y los cálculos pueden tener una precisión incluso menor debido a cinco problemas: redondeo , [3] truncamiento y almacenamiento binario , acumulación de las desviaciones de los operandos en los cálculos, y lo peor: cancelación en sustracciones resp. 'Cancelación catastrófica' al restar valores con magnitud similar.
Precisión y almacenamiento binario
En la figura superior se muestra la fracción 1/9000 en Excel. Aunque este número tiene una representación decimal que es una cadena infinita de unos, Excel muestra solo las 15 cifras iniciales. En la segunda línea, se agrega el número uno a la fracción y, nuevamente, Excel muestra solo 15 cifras. En la tercera línea, se resta uno de la suma usando Excel. Debido a que la suma tiene solo once unos después del decimal, la verdadera diferencia cuando se resta "1" es tres ceros seguidos de una cadena de once unos. Sin embargo, la diferencia informada por Excel es de tres ceros seguidos de una cadena de 15 dígitos de trece unos y dos dígitos erróneos adicionales. Por lo tanto, los números con los que calcula Excel no son los números que muestra. Además, el error en la respuesta de Excel no es simplemente un error de redondeo, es un efecto en los cálculos de punto flotante llamado 'cancelación'.
La inexactitud en los cálculos de Excel es más complicada que los errores debido a una precisión de 15 cifras significativas. El almacenamiento de números en formato binario de Excel también afecta su precisión. [4] Para ilustrar, la figura inferior tabula la suma simple 1 + x - 1 para varios valores de x . Todos los valores de x comienzan en el decimoquinto decimal, por lo que Excel debe tenerlos en cuenta. Antes de calcular la suma 1 + x , Excel primero aproxima x como un número binario. Si esta versión binaria de x es una simple potencia de 2, la aproximación decimal de 15 dígitos ax se almacena en la suma, y los dos ejemplos superiores de la figura indican la recuperación de x sin error. En el tercer ejemplo, x es un número binario más complicado, x = 1.110111 ⋯ 111 × 2 −49 (15 bits en total). Aquí, el 'valor doble IEEE 754' resultante de la figura de 15 bits es 3.330560653658221E-15, que se redondea. por Excel para la 'interfaz de usuario' a 15 dígitos 3.33056065365822E-15, y luego se muestra con 30 dígitos decimales y se agrega un 'cero falso', por lo que los valores 'binarios' y 'decimales' en la muestra son idénticos solo en la pantalla, los valores asociados con las celdas son diferentes (1,1101111111111100000000000000000000000000000000000000 × 2 −49 frente a 1,11011111111110111111111111111111111111111111111101 × 2 −49 ). Lo mismo ocurre con otras hojas de cálculo, el manejo de la diferente cantidad de dígitos decimales que se pueden almacenar exactamente en la mantisa de 53 bits de un 'doble' (por ejemplo, 16 dígitos entre 1 y 8, pero solo 15 entre 0,5 y 1 y entre 8 y 10) es algo difícil y se resuelve como 'subóptimo'. En el cuarto ejemplo, x es un número decimal no equivalente a un binario simple (aunque concuerda con el binario del tercer ejemplo a la precisión mostrada). La entrada decimal se aproxima mediante un binario y luego se utiliza ese decimal. Estos dos ejemplos intermedios de la figura muestran que se introduce algún error.
Los dos últimos ejemplos ilustran lo que sucede si x es un número bastante pequeño. En el segundo del último ejemplo, x = 1.110111 ⋯ 111 × 2 −50 ; 15 bits en total. El binario se reemplaza muy crudamente por una sola potencia de 2 (en este ejemplo, 2 −49 ) y se usa su equivalente decimal. En el ejemplo inferior, un decimal idéntico al binario anterior con la precisión mostrada, sin embargo, se aproxima de manera diferente al binario y se elimina mediante el truncamiento a 15 cifras significativas, sin hacer ninguna contribución a 1 + x - 1 , lo que lleva ax = 0 . [5]
Para x ′ s que no son simples potencias de 2, puede ocurrir un error notable en 1 + x - 1 incluso cuando x es bastante grande. Por ejemplo, si x = 1/1000, entonces 1 + x - 1 = 9,99999999999 89 × 10 −4 , un error en la decimotercera cifra significativa. En este caso, si Excel simplemente suma y resta los números decimales, evitando la conversión a binario y de nuevo a decimal, no se produciría ningún error de redondeo y la precisión en realidad sería mejor. Excel tiene la opción de "Establecer la precisión como se muestra". [6] Con esta opción, dependiendo de las circunstancias, la precisión puede resultar mejor o peor, pero sabrá exactamente lo que está haciendo Excel. (Sin embargo, debe tenerse en cuenta que solo se conserva la precisión seleccionada y no se pueden recuperar dígitos adicionales invirtiendo esta opción). En este enlace se pueden encontrar algunos ejemplos similares. [7]
En resumen, se introduce una variedad de comportamiento de precisión mediante la combinación de representar un número con un número limitado de dígitos binarios, junto con números truncados más allá de la decimoquinta cifra significativa. [8] El tratamiento de Excel de los números más allá de las 15 cifras significativas a veces aporta una mejor precisión a las pocas cifras significativas finales de un cálculo que trabajar directamente con solo 15 cifras significativas, y otras veces no.
Para conocer el razonamiento detrás de la conversión a representación binaria y volver a decimal, y para obtener más detalles sobre la precisión en Excel y VBA, consulte estos enlaces. [9]
1. Las deficiencias en las tareas '= 1 + x - 1' son una combinación de 'debilidades de fp-math' y 'cómo lo maneja Excel', especialmente el redondeo excelente. Excel hace algunos redondeos y / o 'ajusta a cero' para la mayoría de sus resultados, en promedio cortando los últimos 3 bits de la doble representación IEEE. Este comportamiento se puede cambiar estableciendo la fórmula entre paréntesis: '= (1 + 2 ^ -52 - 1)'. Verá que incluso ese pequeño valor sobrevive. Los valores más pequeños desaparecerán ya que solo hay 53 bits para representar el valor, para este caso 1.0000000000 0000000000 0000000000 0000000000 0000000000 01, el primero representa el '1' y el último el '2 ^ -52'.
2. No se trata solo de poderes limpios de dos supervivientes, sino de cualquier combinación de valores construida de bits que estará dentro de los 53 bits una vez que se agregue el decimal 1. Como la mayoría de los valores decimales no tienen una representación finita limpia en binario, sufrirán de 'redondeo' y 'cancelación' en tareas como la anterior.
Por ejemplo, el decimal 0,1 tiene la representación doble IEEE 0 (1) .1001100110011001100110011001100110011001100110011010 veces 2 ^ -4, y sumado a 140737488355328.0 (que es 2 ^ 47) perderá todos sus bits excepto los dos primeros. Por lo tanto, de '= (140737488355328.0 + 0,1 - 140737488355328.0) volverá como 0.09375 calculado con www.weitz.de/ieee (64 bits), así como en Excel con los paréntesis alrededor de la fórmula. Este efecto se puede administrar principalmente mediante un redondeo significativo, que Excel no se aplica, depende del usuario.
No hace falta decirlo: otras hojas de cálculo tienen problemas similares, LibreOffice Calc usa un redondeo más agresivo, mientras que gnumeric intenta mantener la precisión y hacer que la precisión y la 'falta de' sean visibles para el usuario.
Ejemplos en los que la precisión no es un indicador de precisión
Funciones estadísticas
La precisión en las funciones proporcionadas por Excel puede ser un problema. Micah Altman y col. proporcione este ejemplo: [10] La desviación estándar de la población dada por:
es matemáticamente equivalente a:
Sin embargo, la primera forma mantiene precisión mejor numérico para valores grandes de x , porque los cuadrados de las diferencias entre x y x av lleva a menos de redondeo que las diferencias entre los números mucho más grandes sx 2 y (sx) 2 . Sin embargo, la función incorporada de Excel STDEVP () usa la formulación menos precisa porque es más rápida computacionalmente. [11]
Tanto la función de "compatibilidad" STDEVP como la función de "coherencia" STDEV.P en Excel 2010 devuelven la desviación estándar de población de 0,5 para el conjunto de valores dado. Sin embargo, la inexactitud numérica todavía se puede mostrar usando este ejemplo ampliando la figura existente para incluir 10 15 , con lo cual la desviación estándar errónea encontrada por Excel 2010 será cero.
Resta de los resultados de la resta
Hacer restas simples puede dar lugar a errores, ya que dos celdas pueden mostrar el mismo valor numérico mientras se almacenan dos valores separados. Un ejemplo de esto ocurre en una hoja donde las siguientes celdas están configuradas con los siguientes valores numéricos:
y las siguientes celdas contienen las siguientes fórmulas
Ambas celdas y monitor . Sin embargo, si la celda contiene la fórmula luego no muestra como era de esperar, pero muestra en lugar de.
(lo anterior no se limita a restas, pruebe '= 1 + 1,405 * 2 ^ -48' en una celda, Excel redondea la pantalla a 1,00000000000000000000 y '= 0,9 + 225179982494413 * 2 ^ -51' en otra, misma pantalla (en el rango por encima de 1 / por debajo de 1 el redondeo es diferente, lo que afecta a la mayoría de los cambios de magnitud decimal o binaria). arriba, un redondeo diferente para el valor y la visualización, viola uno de los requisitos elementales en Goldberg: 'Lo que todo científico informático debería Know About Floating-Point Aritmética ' (más o menos' el libro sagrado 'de fp-math), decía:' es importante asegurarse de que su uso sea transparente para el usuario. Por ejemplo, en una calculadora, si el La representación interna de un valor mostrado no se redondea con la misma precisión que la pantalla, entonces el resultado de otras operaciones dependerá de los dígitos ocultos y parecerá impredecible para el usuario '(el problema no se limita a sobresalir, por ejemplo, LibreOffice calc actúa de manera similar ).
Error de redondeo
Los cálculos del usuario deben organizarse cuidadosamente para garantizar que el error de redondeo no se convierta en un problema. Un ejemplo ocurre al resolver una ecuación cuadrática :
Las soluciones (las raíces) de esta ecuación están determinadas exactamente por la fórmula cuadrática :
Cuando una de estas raíces es muy grande en comparación con la otra, es decir, cuando la raíz cuadrada está cerca del valor b , la evaluación de la raíz correspondiente a la resta de los dos términos se vuelve muy inexacta debido al redondeo (¿cancelación? ).
Es posible determinar el error de redondeo utilizando la fórmula de la serie de Taylor para la raíz cuadrada: [12]
Como consecuencia,
lo que indica que, a medida que b crece, el primer término superviviente, digamos ε:
se vuelve cada vez más pequeño. Los números de by la raíz cuadrada se vuelven casi iguales y la diferencia se vuelve pequeña:
En estas circunstancias, todas las cifras significativas pasan a expresar b . Por ejemplo, si la precisión es de 15 cifras, y estos dos números, by la raíz cuadrada, son iguales a 15 cifras, la diferencia será cero en lugar de la diferencia ε.
Se puede obtener una mayor precisión con un enfoque diferente, que se describe a continuación. [13] Si denotamos las dos raíces por r 1 y r 2 , la ecuación cuadrática se puede escribir:
Cuando la raíz r 1 >> r 2 , la suma ( r 1 + r 2 ) ≈ r 1 y la comparación de las dos formas muestra aproximadamente:
tiempo
Así, encontramos la forma aproximada:
Estos resultados no están sujetos a errores de redondeo, pero no son precisos a menos que b 2 sea grande en comparación con ac .
La conclusión es que al hacer este cálculo usando Excel, a medida que las raíces se separan en valor, el método de cálculo tendrá que cambiar de la evaluación directa de la fórmula cuadrática a algún otro método para limitar el error de redondeo. El punto a métodos de conmutación varía de acuerdo al tamaño de los coeficientes de una y b .
En la figura, Excel se usa para encontrar la raíz más pequeña de la ecuación cuadrática x 2 + bx + c = 0 para c = 4 y c = 4 × 10 5 . Se grafica la diferencia entre la evaluación directa usando la fórmula cuadrática y la aproximación descrita anteriormente para raíces muy espaciadas vs. b . Inicialmente, la diferencia entre los métodos disminuye porque el método de raíz ampliamente espaciada se vuelve más preciso con valores b mayores . Sin embargo, más allá de algún valor b, la diferencia aumenta porque la fórmula cuadrática (buena para valores b más pequeños ) empeora debido al redondeo, mientras que el método de raíz ampliamente espaciada (bueno para valores b grandes ) continúa mejorando. El punto para cambiar de método se indica con puntos grandes y es más grande para valores de c más grandes . En valores b grandes , la curva de pendiente ascendente es el error de redondeo de Excel en la fórmula cuadrática, cuyo comportamiento errático hace que las curvas se ondulen.
Un campo diferente donde la precisión es un problema es el área de cálculo numérico de integrales y la solución de ecuaciones diferenciales . Algunos ejemplos son la regla de Simpson , el método de Runge-Kutta y el algoritmo de Numerov para la ecuación de Schrödinger . [14] Con Visual Basic para aplicaciones, cualquiera de estos métodos se puede implementar en Excel. Los métodos numéricos utilizan una cuadrícula donde se evalúan las funciones. Las funciones pueden interpolarse entre puntos de la cuadrícula o extrapolarse para localizar puntos de cuadrícula adyacentes. Estas fórmulas implican comparaciones de valores adyacentes. Si la cuadrícula está espaciada muy finamente, se producirá un error de redondeo y, cuanto menor sea la precisión utilizada, peor será el error de redondeo. Si se espacian ampliamente, la precisión se verá afectada. Si se piensa en el procedimiento numérico como un sistema de retroalimentación , este ruido de cálculo puede verse como una señal que se aplica al sistema, lo que conducirá a la inestabilidad a menos que el sistema se diseñe cuidadosamente. [15]
Precisión dentro de VBA
Aunque Excel funciona nominalmente con números de 8 bytes de forma predeterminada, VBA tiene una variedad de tipos de datos. El tipo de datos Double es de 8 bytes, el tipo de datos Integer es de 2 bytes y el tipo de datos Variant de propósito general de 16 bytes se puede convertir a un tipo de datos Decimal de 12 bytes utilizando la función de conversión VBA CDec . [16] La elección de los tipos de variables en un cálculo de VBA implica la consideración de los requisitos de almacenamiento, la precisión y la velocidad.
Referencias
- ^ "La aritmética de coma flotante puede dar resultados inexactos en Excel" . Revisión 8.2; identificación de artículo: 78113 . Soporte de Microsoft. 30 de junio de 2010 . Consultado el 2 de julio de 2010 .
- ^ Steve Dalton (2007). "Tabla 2.3: Límites y tipos de datos de la hoja de trabajo". Aplicaciones financieras que utilizan el desarrollo de complementos de Excel en C / C ++ (2ª ed.). Wiley. págs. 13-14. ISBN 0-470-02797-5.
- ^ El redondeo es la pérdida de precisión cuando se restan números que difieren en pequeñas cantidades. Debido a que cada número tiene solo quince dígitos significativos, su diferencia es inexacta cuando no hay suficientes dígitos significativos para expresar la diferencia.
- ^ Robert de Levie (2004). "Precisión algorítmica". Excel avanzado para análisis de datos científicos . Prensa de la Universidad de Oxford. pag. 44. ISBN 0-19-515275-1.
- ^ Para ingresar un número como binario, el número se envía como una cadena de potencias de 2: 2 ^ (- 50) * (2 ^ 0 + 2 ^ −1 + ⋯). Para ingresar un número como decimal, el número decimal se ingresa directamente.
- ^ Esta opción se encuentra en la pestaña "Opciones de Excel / Avanzado". Consulte Cómo corregir errores de redondeo: método 2
- ^ Extrañeza de adición de Excel
- ^ Robert de Levie (2004). trabajo citado . págs. 45–46. ISBN 0-19-515275-1.
- ^ Precisión en Excel:
- La aritmética de coma flotante puede dar resultados inexactos : una explicación detallada con ejemplos de las consecuencias del almacenamiento binario / 15 sig fig.
- ¿Por qué Excel parece dar respuestas incorrectas? Archivado el 30 de marzo de 2010 en Wayback Machine : otra discusión detallada con ejemplos y algunas correcciones.
- Lo que todo informático debería saber sobre el punto flotante Se centra en ejemplos de representaciones de números en punto flotante.
- Precisión visual básica y aritmética : Orientada a VBA, que hace las cosas de forma un poco diferente.
- Bernard V. Liengme (2008). "Limitaciones matemáticas de Excel". Una guía de Microsoft Excel 2007 para científicos e ingenieros . Prensa académica. pag. 31 y sigs . ISBN 0-12-374623-X.
- ^ Micah Altman; Jeff Gill; Michael McDonald (2004). "§2.1.1 Ejemplo revelador: Calcular la desviación estándar del coeficiente". Problemas numéricos en computación estadística para el científico social . Wiley-IEEE. pag. 12. ISBN 0-471-23633-0.
- ^ Robert de Levie (2004). Excel avanzado para análisis de datos científicos . Prensa de la Universidad de Oxford. págs. 45–46. ISBN 0-19-515275-1.
- ^ Gradshteyn, Izrail Solomonovich ; Ryzhik, Iosif Moiseevich ; Geronimus, Yuri Veniaminovich ; Tseytlin, Michail Yulyevich ; Jeffrey, Alan (2015) [Octubre de 2014]. "1.112. Serie de potencias". En Zwillinger, Daniel; Moll, Victor Hugo (eds.). Tabla de Integrales, Series y Productos . Traducido por Scripta Technica, Inc. (8 ed.). Academic Press, Inc. pág. 25. ISBN 0-12-384933-0. LCCN 2014010276 .
- ^ Este método aproximado se utiliza a menudo en el diseño de amplificadores de retroalimentación, donde las dos raíces representan los tiempos de respuesta del sistema. Consulte el artículo sobre respuesta al paso .
- ^ Algoritmos informáticos de Anders Blom para resolver las ecuaciones de Schrödinger y Poisson , Departamento de Física, Universidad de Lund, 2002.
- ^ RW Hamming (1986). Métodos numéricos para científicos e ingenieros (2ª ed.). Publicaciones de Courier Dover. ISBN 0-486-65241-6.Este libro analiza ampliamente el redondeo, el truncamiento y la estabilidad. Por ejemplo, consulte el Capítulo 21: Integrales indefinidas: retroalimentación , página 357.
- ^ John Walkenbach (2010). "Definición de tipos de datos". Programación de energía de Excel 2010 con VBA . Wiley. págs. 198 y sigs. y tabla 8-1. ISBN 0-470-47535-8.