Redondeo de Excel frente a redondeo de Power Query: ¡ADVERTENCIA! Ellos son diferentes
El término “diferencia de redondeo” comúnmente describe algo insignificante; rara vez genera motivo de preocupación. Sin embargo, cuando descubre que el redondeo de Excel y el redondeo de Power Query son diferentes, de repente se da cuenta de que puede ser un problema mayor de lo que pensaba al principio.
Esta publicación explica los diferentes métodos de redondeo en Excel y Power Query, y cómo garantizar que apliquen el mismo cálculo.
Tabla de contenido
- Guión
- redondeo de excel
- Redondeo de Power Query
- Métodos de redondeo en Excel y Power Query
- Excel utiliza el redondeo tradicional
- Power Query utiliza el redondeo bancario
- ¿Lo cual está bien?
- ¿Cuándo la diferencia es un problema?
- Cambiar la metodología de redondeo en Excel o Power Query
- Calcular el redondeo bancario en Excel.
- Calcular el redondeo tradicional en Power Query
- Envolver
Veamos un ejemplo sencillo para ilustrar el problema.
Supongamos que el examen de ingreso a una universidad requiere que los solicitantes realicen 4 exámenes. Si su puntaje promedio redondeado es 65 o más, son aceptados. En caso contrario, son rechazados. Por eso, pasar se vuelve muy importante.
Aquí están los resultados promedio de 4 solicitantes.
Los requisitos eran para promedios redondeados . Entonces, sigamos adelante y redondeemos estos números en Excel y Power Query.
redondeo de excel
Para redondear los resultados en Excel, podemos ingresar lo siguiente en la celda D3 .
=ROUND([@Score],0)Esto conduce al siguiente resultado:
Hasta ahora, no hay problemas. Todo el mundo va a la universidad.
Redondeo de Power Query
Ahora carguemos estos datos en Power Query y apliquemos la transformación Redondear que se encuentra en Agregar redondeo de columnas...
Establezca los decimales en cero.
Finalmente, cierre y cargue los valores nuevamente en Excel.
En caso de que no lo hayas notado, he resaltado el problema. Para sally:
- Excel redondea 64,5 a 65
- Power Query redondea 64,5 a 64
Según Power Query, ¡Sally no irá a la universidad después de todo!
La puntuación de Vera de 69,5 ronda los 70 para ambos. ¿Por qué el valor de Sally se redondea de manera diferente entre Excel y Power Query? Veamos qué está causando esto.
Métodos de redondeo en Excel y Power Query
Se aplican diferentes tipos de redondeo.
Excel utiliza el redondeo tradicional
En la escuela, estoy seguro de que todos aprendimos que cuando se redondea a .5, siempre se redondea hacia arriba. ¡Siempre! ¡SIEMPRE!
No sé si este método tiene un nombre oficial, así que lo llamaremos “redondeo tradicional”.
La función REDONDEAR de Excel aplica esta metodología. Por tanto, los resultados son:
- 64,5 vueltas a 65
- 69,5 vueltas a 70
Power Query utiliza el redondeo bancario
Power Query utiliza un método de redondeo diferente conocido como redondeo bancario .
Estoy seguro de que puedo adivinar su respuesta… “¿Qué redondean los banqueros? ¡Nunca antes lo había escuchado!"
Usando el método tradicional, si 0,5 siempre se redondea hacia arriba, se crea un sesgo con el tiempo. El redondeo bancario es un método que busca eliminar ese sesgo.
Cuando los banqueros redondean, la regla es que 0,5 se redondea al número par más cercano. Por tanto, los resultados son:
- 64,5 vueltas a 64
- 69,5 vueltas a 70
Esto no es un nuevo concepto; El redondeo bancario también es el método predeterminado utilizado en VBA.
¿Lo cual está bien?
Entonces quizás te preguntes cuál es la correcta. ¿Cual deberías usar?
El problema es que ni está bien ni está mal; son simplemente diferentes formas de redondear.
¿Cuándo la diferencia es un problema?
El redondeo bancario elimina el sesgo de redondear siempre hacia arriba, por lo que muchos lo consideran más preciso. Pero, ¿cuánta diferencia supone realmente?
A modo de ejemplo, si redondea una muestra grande de números positivos entre 1,0 y 100,0 (con 1 decimal) a 0 decimales:
- El redondeo tradicional crea un sesgo hacia arriba de aprox. 1%
- El redondeo bancario tiene un sesgo de aprox. 0%.
Sin embargo, en la misma muestra, el redondeo bancario crea un sesgo hacia números pares del 10%, algo que no ocurre con el redondeo tradicional.
Por lo tanto, reducir un sesgo crea otro. Entonces, cuál es mejor realmente se reduce al contexto y la importancia de la diferencia potencial.
El mayor problema es cuando dejamos de analizar muestras de gran tamaño y consideramos a los individuos. En nuestro escenario inicial, los dos métodos marcaban la diferencia entre que alguien pudiera acceder a la universidad. Por lo tanto, la decisión de utilizar Excel o Power Query para calcular resultados podría ser una decisión que cambie el rumbo de la vida de una persona.
Cambiar la metodología de redondeo en Excel o Power Query
Hasta ahora, hemos visto el comportamiento predeterminado de Excel y Power Query. Pero no estamos atrapados en esos incumplimientos; Podemos usar el redondeo tradicional en Power Query y el redondeo bancario en Excel.
Calcular el redondeo bancario en Excel.
Usando los mismos datos que nuestro ejemplo original. Podemos simular a los banqueros redondeando en Excel con algunas fórmulas.
La fórmula en la celda E3 es:
=SI(MOD(C3,1)=0.5,RONDONADO(C3,2),RONDONADO(C3,0))
Esta fórmula proporciona el mismo resultado que el redondeo bancario.
Calcular el redondeo tradicional en Power Query
La transformación de redondeo de Power Query tiene parámetros opcionales para controlar el tipo de redondeo.
Agregar el parámetro RoundingMode.AwayFromZero a la función Number.Round obliga al tipo de redondeo a ser equivalente al redondeo tradicional.
Puede encontrar una lista de otros métodos de redondeo de Power Query aquí: https://docs.microsoft.com/en-us/powerquery-m/roundingmode-type
Envolver
Las opciones de redondeo predeterminadas en Power Query y Excel son diferentes. Excel utiliza el redondeo tradicional, mientras que Power Query utiliza el redondeo bancario. Ambos métodos crean una forma de sesgo. Por lo tanto, la mejor opción depende de su contexto.
En una muestra grande, el sesgo puede ser insignificante. Pero individualmente, puede alterar los resultados y tener un impacto significativo.
Tanto Excel como Power Query pueden calcular sobre cualquier base si están programados para hacerlo. Por lo tanto, ser consciente de la diferencia y del impacto potencial es la parte más crucial. Ahora que conoce la diferencia, puede elegir la base correcta para su escenario.
Artículos Relacionados:
- ¿Por qué las fórmulas de Excel no calculan?
- Excel puede calcular resultados incorrectos: ADVERTENCIA
- Errores comunes de Power Query cómo solucionarlos
Actualizar:
Después de la publicación, Bill Jelen y Celia Alves me señalaron que esto es aún más profundo y se vuelve aún más complejo. Consulte sus recursos aquí:
- Secretos útiles sobre ROUNDing en Power Query – Bill Jelen (Mr Excel)
- Redondeo en Power Query – El modo de redondeo predeterminado y el problema de la conversión binario-decimal en Excel Celia Alvez (Resolver Excel)
Si desea saber más sobre las matemáticas de los métodos, consulte este artículo de Wikipedia: https://en.wikipedia.org/wiki/Rounding
Deja una respuesta