¿Fórmulas de Excel extrañas e incorrectas?

Question

Quiero obtener la longitud de un resultado de fórmula, pero regresa incorrectamente. Por ejemplo, he puesto 1005 en la celda A1, poner la fórmula =(A1/1000-FLOOR(A1/1000,1))*1000 en B1 y el resultado es 5- Correcto. Pero cuando puse la fórmula =LEN(B1) en la celda C1, el resultado es 16? Aún peor, la fórmula =REPT(0,3-LEN(TEXT(B1, 0 )))&(B1) o =REPT(0,3-LEN(TEXT((A1/1000-FLOOR(A1/1000,1))*1000, 0 )))&((A1/1000-FLOOR(A1/1000,1))*1000) retornos 004.99999999999989?

algorithmwizard · Accepted Answer

La razón =LEN(B1) devuelve 16 es porque el valor en la célula B1 puede ser mostrado como 5, pero es en realidad almacenado internamente 4.99999999999989 - y ese número tiene 16 caracteres en él, cuando se evalúa como una cadena por LEN(). Puedes ver esto por ti mismo haciendo clic repetidamente en el botón  Increase Decimal : Aquí, la pantalla eventualmente tiene suficientes lugares decimales para reflejar el valor subyacente (el LEN función ignora ceros que siguen - de nuevo, esos son pantalla ceros no almacenados ceros). También puede ver la misma cosa si simplemente entra en la siguiente fórmula en una celda: =1.005-1 Esto en realidad tiene 19 caracteres, no 16, porque no hemos multiplicado por 1.000: 0.00499999999999989 La pérdida de precisión de su muestra 5 al almacenamiento real 4.99999999999989 es ciertamente debido a cómo funciona la matemática punto flotante, como menciona BigBen. Véase también ¿Está rota la matemática del punto flotante? para un debate más amplio.

Gpt · Answer

El problema que estás experimentando se debe a cómo Excel maneja los cálculos aritméticos y la precisión de los números flotantes.

En Excel, los números flotantes pueden presentar una pequeña cantidad de error debido a la representación binaria de los valores. Esto puede causar discrepancias en los cálculos y resultados inesperados.

Para resolver este problema y obtener el resultado esperado, puedes aplicar una función adicional para redondear el número a un número entero antes de calcular la longitud o mostrarlo en un formato específico.

Por ejemplo, puedes modificar la fórmula en la celda C1 a lo siguiente:


=LEN(TEXT(ROUND(B1,0),"0"))

Y para evitar problemas de precisión en la fórmula de concatenación, puedes ajustarla de la siguiente manera:


=REPT(0,3-LEN(TEXT(ROUND((A1/1000-FLOOR(A1/1000,1))1000,0),"0")))&ROUND((A1/1000-FLOOR(A1/1000,1))1000,0)

Al redondear el valor antes de aplicar otras funciones, puedes evitar la aparición de números con errores de redondeo. Espero que esta solución te sea útil.

Respondida el Dec 19, 2020 a las 03:11 - por Gpt

Gemini · Answer

El problema es que el resultado de la fórmula en la celda B1 es un número decimal, no un número entero. La función LEN cuenta el número de caracteres en una cadena de texto, por lo que cuando se aplica a un número decimal, cuenta el número de dígitos en el número, incluyendo el punto decimal y los ceros finales.

Para obtener la longitud del número entero en la celda B1, puedes utilizar la función INT para convertir el número decimal en un número entero y luego aplicar la función LEN al resultado:


=LEN(INT(B1))

Esto devolverá el valor correcto, que es 1.

Para las otras fórmulas, el problema es similar. La función TEXT convierte un número en una cadena de texto, pero conserva los ceros finales. Por lo tanto, cuando se aplica la función LEN a la cadena de texto resultante, cuenta el número de caracteres, incluidos los ceros finales.

Para obtener la longitud del número entero en la celda B1 sin los ceros finales, puedes utilizar la función TRIM para eliminar los espacios en blanco finales y luego aplicar la función LEN al resultado:


=LEN(TRIM(TEXT(B1, "0")))

Esto devolverá el valor correcto, que es 1.

Respondida el Dec 19, 2020 a las 03:13 - por Gemini

¿Fórmulas de Excel extrañas e incorrectas?

3 Respuestas: