Formuła ogólna
=ROUND(number,digits-(1+INT(LOG10(ABS(number)))))
Podsumowanie
Jeśli chcesz zaokrąglić liczbę do podanej (zmiennej) liczby określonych cyfr lub cyfr, możesz to zrobić za pomocą eleganckiej formuły, która wykorzystuje funkcje ROUND i LOG10.
W pokazanym przykładzie formuła w D6 wygląda następująco:
=ROUND(B6,C6-(1+INT(LOG10(ABS(B6)))))
Wyjaśnienie
Może to być onieśmielająca formuła, jeśli nie masz dobrego doświadczenia w matematyce, ale przejdźmy przez nią krok po kroku.
Po pierwsze, gdy masz formułę taką jak ta, w której jedna funkcja (w tym przypadku ZAOKR) otacza wszystkie inne, często pomocne jest wykonanie czynności od zewnątrz do wewnątrz. Tak więc, w istocie, ta formuła zaokrągla wartość w B6 za pomocą ZAOKR funkcjonować:
=ROUND(B6,x)
Gdzie x to liczba wymaganych cyfr znaczących. Trudną częścią tego wzoru jest obliczenie x. Jest to zmienna, ponieważ będzie się zmieniać w zależności od zaokrąglanej liczby. x oblicza się za pomocą tego bitu:
C6-(1+INT(LOG10(ABS(B6))))
Wydaje się to skomplikowane, więc najpierw przyjrzyjmy się, jak formuła musi działać dla podanych przykładów. Pamiętaj, że w przypadku ROUND ujemna liczba cyfr działa po lewej stronie przecinka. Tak więc, aby zaokrąglić liczbę 1234567 do rosnącej liczby cyfr znaczących, otrzymalibyśmy:
=ROUND(1234567,-6) = 1000000 // 1 sig. digit =ROUND(1234567,-5) = 1200000 // 2 sig. digits =ROUND(1234567,-4) = 1230000 // 3 sig. digits =ROUND(1234567,-3) = 1235000 // 4 sig. digits
Zatem głównym problemem jest to, jak obliczyć -6, -5, -4 itd. W zależności od liczby, którą zaokrąglamy.
Kluczem jest zrozumienie, jak te liczby można wyrazić za pomocą wykładników, jak w notacji naukowej:
=ROUND(1234567,-6) = 1000000 = 1.0*10^6 =ROUND(1234567,-5) = 1200000 = 1.2*10^6 =ROUND(1234567,-4) = 1230000 = 1.23*10^6 =ROUND(1234567,-3) = 1235000 = 1.235*10^6
Zauważ, że wykładnik wynosi 6 we wszystkich przypadkach, co jest określane tym bitem:
INT(LOG10(ABS(B6)))
Tak więc reszta wzoru polega po prostu na użyciu obliczonej wartości wykładnika, aby obliczyć odpowiednią liczbę, aby uzyskać ROUND w zależności od pożądanej liczby cyfr znaczących:
=ROUND(1234567,-6) // 1-(1+6) = -6 =ROUND(1234567,-5) // 2-(1+6) = -5 =ROUND(1234567,-4) // 3-(1+6) = -4 =ROUND(1234567,-3) // 4-(1+6) = -3
Podsumowując:
- ABS konwertuje wartość na wartość bezwzględną (dodatnią)
- LOG10 pobiera wykładnik, w tym przypadku 6 z wartością dziesiętną
- INT obcina część dziesiętną wykładnika
- Formuła wykorzystuje wykładnik i podane cyfry znaczące, aby obliczyć odpowiednią liczbę cyfr, aby uzyskać ZAOKR
- ROUND zaokrągla liczbę przy użyciu podanej liczby cyfr
