c++ - quitar - redondear promedio en excel
¿Cómo Excel redondea con éxito los números de punto flotante aunque sean imprecisos? (12)
Por ejemplo, este blog dice que 0.005 no es exactamente 0.005, pero al redondear ese número se obtiene el resultado correcto.
He intentado todo tipo de redondeo en C ++ y falla al redondear números a ciertos lugares decimales. Por ejemplo, Round (x, y) redondea x a un múltiplo de y. Entonces, la Ronda (37.785,0.01) debería darte 37.79 y no 37.78.
Estoy volviendo a abrir esta pregunta para pedirle ayuda a la comunidad. El problema es la imprecisión de los números de punto flotante (37.785 se representa como 37.78499999999).
La pregunta es, ¿cómo resuelve Excel este problema?
La solución en esta ronda () para float en C ++ es incorrecta para el problema anterior.
"La ronda (37.785,0.01) debería darle 37.79 y no 37.78".
En primer lugar, no hay consenso de que 37.79 en lugar de 37.78 sea la respuesta "correcta" aquí. Los desempates siempre son un poco duros. Si bien siempre el redondeo en el caso de un empate es un enfoque ampliamente utilizado, ciertamente no es el único enfoque.
En segundo lugar, esto no es una situación de desempate. El valor numérico en el formato de punto flotante binario IEEE64 es 37.784999999999997 (aproximadamente). Hay muchas formas de obtener un valor de 37.784999999999997 además de que un humano escriba un valor de 37.785 y que se haya convertido a esa representación de punto flotante. En la mayoría de estos casos, la respuesta correcta es 37.78 en lugar de 37.79.
Apéndice
Considere las siguientes fórmulas de Excel:
=ROUND(37785/1000,2)
=ROUND(19810222/2^19+21474836/2^47,2)
Ambas celdas mostrarán el mismo valor, 37.79. Hay un argumento legítimo sobre si 37785/1000 debería redondear a 37.78 o 37.79 con dos posiciones de precisión. La forma de lidiar con estos casos es un poco arbitraria y no hay una respuesta de consenso. Ni siquiera hay una respuesta de consenso dentro de Microsoft: " la función Round () no se implementa de manera consistente entre los diferentes productos de Microsoft por razones históricas " ( http://support.microsoft.com/kb/196652 ) Dado un máquina de precisión infinita, el VBA de Microsoft redondearía 37.785 a 37.78 (ronda de banqueros), mientras que Excel produciría 37.79 (ronda aritmética simétrica).
No hay discusión sobre el redondeo de esta última fórmula. Es estrictamente menor que 37.785, por lo que debe redondear a 37.78, no 37.79. Sin embargo, Excel lo redondea. ¿Por qué?
La razón tiene que ver con cómo se representan los números reales en una computadora. Microsoft, como muchos otros, utiliza el formato de punto flotante IEEE de 64 bits. El número 37785/1000 sufre una pérdida de precisión cuando se expresa en este formato. Esta pérdida de precisión no ocurre con 19810222/2 ^ 19 + 21474836/2 ^ 47; es un "número exacto".
Intencionalmente construí ese número exacto para tener la misma representación de punto flotante que la inexacta 37785/1000. Ese Excel redondea este valor exacto en lugar de bajar es la clave para determinar cómo funciona la función ROUND() Excel: es una variante del redondeo aritmético simétrico. Redondea basándose en una comparación con la representación de punto flotante del caso de la esquina.
El algoritmo en C ++:
#include <cmath> // std::floor
// Compute 10 to some positive integral power.
// Dealing with overflow (exponent > 308) is an exercise left to the reader.
double pow10 (unsigned int exponent) {
double result = 1.0;
double base = 10.0;
while (exponent > 0) {
if ((exponent & 1) != 0) result *= base;
exponent >>= 1;
base *= base;
}
return result;
}
// Round the same way Excel does.
// Dealing with nonsense such as nplaces=400 is an exercise left to the reader.
double excel_round (double x, int nplaces) {
bool is_neg = false;
// Excel uses symmetric arithmetic round: Round away from zero.
// The algorithm will be easier if we only deal with positive numbers.
if (x < 0.0) {
is_neg = true;
x = -x;
}
// Construct the nearest rounded values and the nasty corner case.
// Note: We really do not want an optimizing compiler to put the corner
// case in an extended double precision register. Hence the volatile.
double round_down, round_up;
volatile double corner_case;
if (nplaces < 0) {
double scale = pow10 (-nplaces);
round_down = std::floor (x * scale);
corner_case = (round_down + 0.5) / scale;
round_up = (round_down + 1.0) / scale;
round_down /= scale;
}
else {
double scale = pow10 (nplaces);
round_down = std::floor (x / scale);
corner_case = (round_down + 0.5) * scale;
round_up = (round_down + 1.0) * scale;
round_down *= scale;
}
// Round by comparing to the corner case.
x = (x < corner_case) ? round_down : round_up;
// Correct the sign if needed.
if (is_neg) x = -x;
return x;
}
Como dice mjfgates, Excel hace un trabajo duro para conseguir esto "correcto". Lo primero que debe hacer cuando intenta reimplementar esto, es definir lo que quiere decir con "correcto". Soluciones obvias: - implementar aritmética racional lenta pero confiable. - implemente un montón de heurísticas Rápido pero difícil de hacer bien (piense en "años de informes de errores").
Realmente depende de su aplicación.
Creo que el siguiente código C # redondea los números a medida que se redondean en Excel. Para replicar exactamente el comportamiento en C ++, es posible que necesite usar un tipo decimal especial.
En inglés simple, el número de doble precisión se convierte a un decimal y luego se redondea a quince dígitos significativos (no debe confundirse con quince decimales). El resultado se redondea una segunda vez al número especificado de lugares decimales.
Esto puede parecer extraño, pero lo que hay que entender es que Excel siempre muestra los números que se redondean a 15 cifras significativas. Si la función ROUND () no estuviera usando ese valor de visualización como un punto de inicio, y en su lugar usara la doble representación interna, habría casos en los que ROUND (A1, N) no parecía corresponder al valor real en A1. Eso sería muy confuso para un usuario no técnico.
El doble más cercano a 37.785 tiene un valor decimal exacto de 37.784999999999996589394868351919107818603515625. (Cualquier doble puede representarse precisamente con una base finita diez decimal porque un cuarto, un octavo, un dieciseisavo, etc., todos tienen expansiones decimales finitas). Si ese número se redondeara directamente a dos lugares decimales, no habría un vínculo con Quiebre y el resultado sería 37.78. Si redondeas a 15 cifras significativas primero obtienes 37.7850000000000. Si esto se redondea a dos decimales, obtienes 37.79, así que no hay un verdadero misterio después de todo.
// Convert to a floating decimal point number, round to fifteen
// significant digits, and then round to the number of places
// indicated.
static decimal SmartRoundDouble(double input, int places)
{
int numLeadingDigits = (int)Math.Log10(Math.Abs(input)) + 1;
decimal inputDec = GetAccurateDecimal(input);
inputDec = MoveDecimalPointRight(inputDec, -numLeadingDigits);
decimal round1 = Math.Round(inputDec, 15);
round1 = MoveDecimalPointRight(round1, numLeadingDigits);
decimal round2 = Math.Round(round1, places, MidpointRounding.AwayFromZero);
return round2;
}
static decimal MoveDecimalPointRight(decimal d, int n)
{
if (n > 0)
for (int i = 0; i < n; i++)
d *= 10.0m;
else
for (int i = 0; i > n; i--)
d /= 10.0m;
return d;
}
// The constructor for decimal that accepts a double does
// some rounding by default. This gets a more exact number.
static decimal GetAccurateDecimal(double r)
{
string accurateStr = r.ToString("G17", CultureInfo.InvariantCulture);
return Decimal.Parse(accurateStr, CultureInfo.InvariantCulture);
}
Del mismo modo que los números de base 10 se deben redondear, ya que se convierten a base-2, es posible redondear un número a medida que se convierte de base-2 a base-10. Una vez que el número tiene una representación de base 10, se puede redondear nuevamente de manera directa mirando el dígito a la derecha del que desea redondear.
Si bien no hay nada malo con la afirmación anterior, hay una solución mucho más pragmática. El problema es que la representación binaria intenta acercarse lo más posible al número decimal, incluso si ese binario es menor que el decimal. La cantidad de error está dentro de [-0.5,0.5] bits menos significativos (LSB) del valor verdadero. Para propósitos de redondeo, prefieres que esté dentro de [0,1] LSB, de modo que el error sea siempre positivo, pero eso no es posible sin cambiar todas las reglas de las matemáticas de coma flotante.
Lo único que puede hacer es agregar 1 LSB al valor, por lo que el error está dentro de [0.5,1.5] LSB del valor verdadero. Esto es menos preciso en general, pero solo en una cantidad muy pequeña; cuando el valor se redondea para representarlo como un número decimal, es mucho más probable que se redondee a un número decimal adecuado porque el error siempre es positivo.
Para agregar 1 LSB al valor antes de redondearlo, vea las respuestas a esta pregunta . Por ejemplo, en Visual Studio C ++ 2010 el procedimiento sería:
Round(_nextafter(37.785,37.785*1.1),0.01);
Entonces, su pregunta real parece ser cómo redondear correctamente el punto flotante -> conversiones de cadenas. Al buscar en Google para esos términos, obtendrás un montón de artículos, pero si estás interesado en algo para usar, la mayoría de las plataformas proporcionan implementaciones razonablemente competentes de sprintf () / snprintf (). Así que solo use esos, y si encuentra errores, presente un informe al proveedor.
Excel redondea números como este "correctamente" haciendo TRABAJO. Comenzaron en 1985, con un conjunto bastante "normal" de rutinas de punto flotante, agregaron un punto flotante falso de enteros escalados, y han estado afinando esas cosas y agregando casos especiales desde entonces. La aplicación DID solía tener la mayoría de los mismos errores "obvios" que todos los demás tenían, es solo que la mayoría los tenía hace mucho tiempo. Yo mismo presenté una pareja, cuando estaba haciendo soporte técnico para ellos a principios de los 90.
Hay muchas formas de optimizar el resultado de un punto flotante usando algoritmos estadísticos, numéricos ...
La más fácil es quizás buscar 9s o 0s repetitivos en el rango de precisión. Si lo hay, tal vez esos 9 sean compensadores, solo redondéalos. Pero esto puede no funcionar en muchos casos.
2.67899999 → 2.679
12.3499999 → 12.35
1.20000001 → 1.2
O puede incluir una cantidad de precisión junto con el número de punto flotante. Después de cada paso, ajuste la precisión según la precisión de los operandos. Por ejemplo
1.113 → 3 decimal digits
6.15634 → 5 decimal digits
Dado que ambos números están dentro del rango de precisión de 16-17 dígitos del doble, su suma será precisa para el mayor de ellos, que es de 5 dígitos. De manera similar, 3 + 5 <16, por lo que su producto será preciso con 8 números decimales
1.113 + 6.15634 = 7.26934 → 5 decimal digits
1.113 * 6.15634 = 6.85200642 → 8 decimal digits
Pero 4.1341677841 * 2.251457145 solo tomará la precisión del doble porque los números decimales del resultado real exceden la precisión del doble
Otro algoritmo eficiente es Grisu, pero no he tenido la oportunidad de intentarlo.
En 2010, Florian Loitsch publicó un maravilloso artículo en PLDI, "Imprimiendo números de punto flotante de forma rápida y precisa con enteros" , que representa el paso más grande en este campo en 20 años: descubrió cómo usar enteros de máquinas para realizar representaciones precisas. ! ¿Por qué digo "en su mayoría"? Porque aunque el algoritmo "Grisu3" de Loitsch es muy rápido, abandona aproximadamente el 0,5% de los números, en cuyo caso tiene que recurrir a Dragon4 o un derivado
De hecho, creo que Excel debe combinar muchos métodos diferentes para lograr el mejor resultado de todos.
Ejemplo cuando un valor llega a cero
En Excel 95 o anterior, ingrese lo siguiente en un nuevo libro:
A1: =1.333+1.225-1.333-1.225Haga clic con el botón secundario en la celda A1 y, a continuación, haga clic en Formato de celdas. En la pestaña Número, haga clic en Científico en Categoría. Establecer los lugares decimales a 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 resta da como resultado un valor en o muy cercano a cero, Excel 97 y posteriores compensarán cualquier error introducido como resultado de convertir un operando a y desde binario. El ejemplo anterior cuando se realiza en Excel 97 y versiones posteriores muestra correctamente 0 o 0.000000000000000E + 00 en notación científica.
La mayoría de las fracciones decimales no se pueden representar con precisión en binario.
double x = 0.0;
for (int i = 1; i <= 10; i++)
{
x += 0.1;
}
// x should now be 1.0, right?
//
// it isn''t. Test it and see.
Una solución es utilizar BCD. Es viejo. Pero, también es probado y cierto. Tenemos muchas otras ideas antiguas que usamos todos los días (como usar un 0 para no representar nada ...).
Otra técnica utiliza el escalado en la entrada / salida. Esto tiene la ventaja de que casi todas las matemáticas son matemáticas enteras.
Lo que necesitas es esto:
double f = 22.0/7.0;
cout.setf(ios::fixed, ios::floatfield);
cout.precision(6);
cout<<f<<endl;
Cómo se puede implementar (solo un resumen para redondear el último dígito):
long getRoundedPrec(double d, double precision = 9)
{
precision = (int)precision;
stringstream s;
long l = (d - ((double)((int)d)))* pow(10.0,precision+1);
int lastDigit = (l-((l/10)*10));
if( lastDigit >= 5){
l = l/10 +1;
}
return l;
}
No sé cómo lo hace Excel, pero imprimir números de punto flotante muy bien es un problema difícil: http://www.serpentine.com/blog/2011/06/29/here-be-dragons-advances-in-problems-you-didnt-even-know-you-had/
Para una precisión arbitraria muy precisa y el redondeo de números de punto flotante a un conjunto fijo de lugares decimales, debe echar un vistazo a una biblioteca matemática como GNU MPFR . Si bien se trata de una biblioteca C, la página web que publiqué también contiene enlaces a un par de enlaces C ++ diferentes si quiere evitar el uso de C.
También puede leer un artículo titulado "Lo que todo científico informático debería saber sobre la aritmética de punto flotante" de David Goldberg en el Centro de Investigación de Xerox Palo Alto. Es un excelente artículo que demuestra el proceso subyacente que permite aproximar los números de punto flotante en una computadora que representa todo en datos binarios, y cómo los errores de redondeo y otros problemas pueden aparecer en las matemáticas de punto flotante basadas en FPU.
Una función que toma un número de punto flotante como argumento y devuelve otro número de punto flotante, redondeado exactamente a un número dado de dígitos decimales, no se puede escribir, porque hay muchos números con una representación decimal finita que tienen una representación binaria infinita; Uno de los ejemplos más simples es 0.1.
Para lograr lo que desea, debe aceptar utilizar un tipo diferente como resultado de su función de redondeo. Si su necesidad inmediata es imprimir el número, puede usar una cadena y una función de formato: el problema es cómo obtener exactamente el formato que espera. De lo contrario, si necesita almacenar este número para realizar cálculos exactos, por ejemplo, si está realizando la contabilidad, necesita una biblioteca que sea capaz de representar números decimales exactamente. En este caso, el enfoque más común es utilizar una representación escalada: un entero para el valor junto con el número de dígitos decimales. Dividir el valor por diez elevado a la escala le da el número original.
Si alguno de estos enfoques es adecuado, intentaré ampliar mi respuesta con sugerencias prácticas.