Excel puede calcular resultados incorrectos: ADVERTENCIA
El viejo proverbio dice que “el mal trabajador culpa a sus herramientas”. Como Excel es una herramienta tan popular, se le culpa mucho cuando la persona que la usa realmente tiene la culpa. Sin embargo, no siempre es una persona la culpable; ¡ Excel puede calcular resultados incorrectos por sí solo!
En esta publicación, quiero explicar la causa del problema y brindar métodos para evitarlo.
Tabla de contenido
- Errores de hoja de cálculo
- Guión
- ¿Por qué ocurre este problema?
- ¿Por qué no solucionan el problema?
- ¿Cómo podemos evitar el problema?
- Usando la función REDONDEAR
- Aplicando precisión como se muestra
- Envolver
La estadística muy citada es que el 80% de las hojas de cálculo contienen errores; por lo tanto, no es raro encontrar un error en un libro de Excel. Todos los errores a los que se hace referencia en esta estadística son causados por humanos. Seamos realistas, las hojas de cálculo son complejas. Son aplicaciones pequeñas que no reciben las pruebas rigurosas que merecen. Por lo tanto, deberíamos esperar errores.
Los errores tienden a dividirse en 3 grupos:
- Errores lógicos : causados por el individuo que no comprende completamente la situación y, por lo tanto, elige la fórmula o solución incorrecta.
- Errores de omisión : causados por celdas y rangos excluidos accidentalmente de las fórmulas.
- Errores mecánicos : causados por inexactitud de los datos o errores de entrada/escritura
Hay otro tipo de error: el error en el que Excel no calcula los números correctos.
Guión
Usemos un escenario básico. Mire la captura de pantalla a continuación:
La fórmula en la celda F7 es:
=VLOOKUP(F5,B5:C7,2,FALSE)Esta es una función básica de BUSCARV. La fórmula busca el valor 20,4 en las celdas B5:B7 y devuelve el valor correspondiente de las celdas C5:C7 .
El valor devuelto es Bravo , que es correcto.
Ahora eche un vistazo a la segunda captura de pantalla.
Parece ser exactamente lo mismo. Mismo valor de búsqueda, mismos datos, misma fórmula. Pero hay un resultado diferente en la celda F7. El valor devuelto es el error #N/A .
¡El segundo ejemplo demuestra que Excel puede calcular el resultado incorrecto!
La única diferencia entre los dos escenarios está en las celdas B5:B7 . En la primera captura de pantalla, todos los valores están codificados:
- B5: 20,3
- B6: 20,4
- B7: 20,5
En la segunda captura de pantalla, se calculan los valores:
- B5: 20,3
- B6: =B5+0,1
- B7: =B6+0,1
Esto no es un problema con BUSCARV; el mismo problema ocurre con INDEX/MATCH y XLOOKUP.
En este escenario, hemos utilizado BUSCARV con una coincidencia exacta; por lo tanto, devuelve el error #N/A. Sin embargo, con una coincidencia aproximada, no habría producido error, sino el resultado “Alfa”. Por lo tanto, el impacto de esto podría estar oculto y ser significativo en algunos escenarios.
¿Por qué ocurre este problema?
¿Por qué Excel tiene este problema de calcular diferentes resultados para el mismo cálculo?
En la escuela nos enseñan que algunos números no se pueden expresar como decimales. Por ejemplo, un tercio (1/3) no se puede expresar como decimal; es 0,3333… recurrente hasta el infinito. Entonces, en lugar de eso, podríamos usar 0,33 como estimación cercana. Aceptamos plenamente que 0,33 no es igual a 1/3 pero lo consideramos insignificante.
Las computadoras calculan números usando binario. En segundo plano, se produce una conversión para cambiar de binario a decimal para que los humanos podamos entenderlo. Como puedes ver a continuación, es más fácil entender un número decimal que un número binario.
Decimal: 237 = Binario: 11101101
Cada número entero se puede convertir perfectamente de binario. Pero esto no es cierto para todos los números de coma flotante decimal.
Tomando un ejemplo, una décima parte (1/10) se expresa fácilmente como decimal: 0,1. Pero. 0,1 en binario es 000110011001100110011… recurrente hasta el infinito. Así como no podemos escribir correctamente un tercio en decimales, un décimo no se puede almacenar en binario.
Las computadoras tienen una cantidad finita de almacenamiento; por lo tanto, no pueden almacenar un número infinito. Además, Excel está programado para calcular con una precisión de 15 dígitos. Como resultado, Excel nunca podrá calcular 0,1 perfectamente.
Volviendo a nuestro ejemplo, si cargamos las celdas B5:B7 en Power Query, nos muestra el número subyacente.
20,3 + 0,1 no es exactamente 20,4 , sino que se calcula como 20,400000000000002 .
Pero espera, tómate unos segundos para contar esos dígitos. La captura de pantalla muestra que no tiene 15 dígitos de precisión, como afirma Microsoft en sus documentos de especificaciones , sino que el número tiene 17 dígitos.
Bien, intentemos algo más. Mire la captura de pantalla a continuación.
Las fórmulas en cada celda son las siguientes:
- Celda B1: 20,4
- Celda C2: =20,3+0,1
- Celda D2: B2=C2
Hemos visto que 20,3+0,1 = 20,400000000000002.
Por lo tanto, el resultado en D2 debería ser FALSO . El resultado de BUSCARV anterior nos dice que estos dos no son iguales. Sin embargo, el resultado en D2 es VERDADERO .
Podemos concluir de esto que algunas funciones de Excel calculan con 15 dígitos de precisión mientras que otras usan 17.
NOTA: 0.1 no es el único número que tiene este problema, pero es el más fácil de explicar.
¿Por qué no solucionan el problema?
Si es un problema conocido, ¿por qué Microsoft no lo soluciona? Desafortunadamente, no es tan simple.
En 1985, el Instituto de Ingenieros Eléctricos y Electrónicos publicó el estándar IEEE 754, el estándar para aritmética de coma flotante. El estándar detalla cómo se realizan los cálculos de punto flotante.
Microsoft Excel está diseñado según el estándar IEEE 754 (con algunas modificaciones menores para números infinitos). Sin embargo, no es sólo Excel el que utiliza este estándar; Miles de programas de software también utilizan el mismo estándar.
Por lo tanto, el problema no está necesariamente en Excel. El problema tampoco está en el estándar IEEE 754. Es simplemente la naturaleza compleja del mundo de las matemáticas y la informática en el que vivimos.
Microsoft está abierto a este tema. Está detallado en su documentación de solución de problemas . Pero supongo que el 99,9% de la gente nunca lo ha leído.
¿Cómo podemos evitar el problema?
Microsoft recomienda dos opciones para solucionar este problema (1) usar la función REDONDEAR o (2) aplicar la precisión como se muestra.
No me gusta particularmente ninguna de las opciones (preferiría que simplemente funcionara). Pero, dado que la precisión mostrada tiene un impacto destructivo en la precisión del libro de trabajo, siempre recomiendo usar la función REDONDEAR.
Usando la función REDONDEAR
Volviendo a nuestro ejemplo, obtenemos el resultado correcto si cambiamos las fórmulas en las celdas B6 y B7 para incluir la función REDONDEAR.
La fórmula de la celda B6 redondea el cálculo a 1 decimal.
=ROUND(B5+0.1,1)Aplicando precisión como se muestra
ADVERTENCIA: Antes de implementar la solución de precisión como se muestra, tenga en cuenta que cualquier número en su libro se redondeará automáticamente a su valor mostrado actual. Entonces, si el número original es 20,44, con la celda configurada para mostrar un decimal, el número se convertirá en 20,4; El 0,04 se perderá para siempre. Si debe utilizar esta opción, asegúrese de realizar una copia de seguridad antes de continuar.
Para aplicar la precisión mostrada:
- Haga clic en Opciones de archivo para abrir el cuadro de diálogo Opciones de Excel
- En la sección Avanzado , asegúrese de que la opción Establecer precisión como se muestra esté marcada
- Un mensaje de advertencia indica que los datos perderán precisión de forma permanente ; si desea continuar, haga clic en Aceptar .
- Haga clic en Aceptar para cerrar las opciones de Excel.
La fórmula ahora calculará el resultado correcto.
Envolver
Debido a la naturaleza del cálculo de números de coma flotante, no todos los decimales se pueden expresar perfectamente en binario. Como consecuencia, Excel puede calcular un resultado incorrecto.
Excel aplica el estándar IEEE 754 para definir cómo se calculan los números de punto flotante. Por lo tanto, Excel, al igual que otras aplicaciones, a veces puede calcular resultados incorrectos. Este es el complejo mundo de las matemáticas y la informática en el que todos vivimos, más que un problema con Excel.
Tenga cuidado con este problema. Asegúrese de verificar sus resultados y, si es necesario, aplique la función REDONDEAR para evitar posibles errores causados por cálculos con números de punto flotante.
Artículos Relacionados:
- ¿Por qué las fórmulas de Excel no calculan?
- Redondeo de Excel vs redondeo de Power Query: ADVERTENCIA Son diferentes
- ¿Por qué el modo de cálculo de Excel sigue cambiando?
Deja una respuesta