Формули за закръгляне на числата в Excel

Често клиентите ви искат да гледат чисти, кръгли числа. Заливането на потребител с десетични стойности и ненужни цифри с цел прецизност всъщност може да направи отчетите ви по-трудни за четене. Поради тази причина може да помислите за използването на функциите за закръгляване на Excel.

Тази фигура илюстрира как числото 9.45 се влияе от използването на функциите ROUND, ROUNDUP и ROUNDDOWN.

Функцията ROUND на Excel се използва за закръгляне на дадено число до определен брой цифри. Функцията ROUND приема два аргумента: първоначалната стойност и броя на цифрите, до които да се закръгли.

Въвеждането на 0 като втори аргумент казва на Excel да премахне всички десетични знаци и да закръгли цялата част от числото въз основа на първия знак след десетичната запетая. Например, тази формула се закръгля до 94:

=КРЪГЛО(94,45,0)

Въвеждането на 1 като втори аргумент казва на Excel да закръгля до един десетичен знак въз основа на стойността на втория знак след десетичната запетая. Например, тази формула се закръгля до 94,5:

=КРЪГЛО(94,45;1)

Можете също да въведете отрицателно число като втори аргумент, като кажете на Excel да закръгля въз основа на стойности вляво от десетичната запетая. Следната формула, например, връща 90:

=КРЪГЛО(94,45,-1)

Можете да принудите закръгляне в определена посока, като използвате функциите ROUNDUP или ROUNDDOWN.

Тази формула ROUNDDOWN закръгля 94,45 надолу до 94:

=ЗАКРЪГЛ(94.45,0)

Тази формула ROUNDUP закръгля 94,45 до 95:

=КРЪГЛА(94.45,0)

Закръгляване до най-близката стотинка в Excel

В някои индустрии е обичайна практика сумата в долари да се закръгля до най-близката стотинка. Фигурата показва как закръгляването на сума в долар нагоре или надолу до най-близката стотинка може да повлияе на полученото число.

Можете да закръглите до най-близката стотинка, като използвате функциите ТАВАН или ПОД.

Функцията CEILING закръглява число до най-близкото кратно на значимостта, което му предавате. Тази помощна програма е полезна, когато трябва да замените стандартния протокол за закръгляване с вашите собствени бизнес правила. Например, можете да принудите Excel да закръгли 123,222 до 124, като използвате функцията CEILING със значение 1.

=ТАВАН(123.222,1)

Така че въвеждането на .01 като значимост казва на функцията CEILING да закръгли до най-близката стотинка.

Ако искате да закръглите до най-близкия никел, можете да използвате .05 като значение. Например следната формула връща 123.15:

=ТАВАН(123.11,.05)

Функцията FLOOR работи по същия начин, с изключение на това, че закръглява надолу до най-близкото значение. Следната примерна функция закръгля 123,19 надолу до най-близкия никел, давайки 123,15 като резултат:

=ПОД(123.19,.05)

Закръгляване до значими цифри в Excel

В някои финансови отчети цифрите са представени със значителни цифри. Идеята е, че когато се занимавате с числа в милиони, не е нужно да заливате отчет с излишни числа, за да покажете прецизност до десетки, стотици и хиляди места.

Например, вместо да показвате числото 883 788, можете да изберете да закръглите числото до една значима цифра. Това би означавало показване на същото число като 900 000. Закръгляването на 883 788 до две значими цифри ще покаже числото като 880 000.

По същество смятате, че мястото на определено число е достатъчно значимо, за да се покаже. Останалата част от числото може да бъде заменена с нули. Може да почувствате, че това може да създаде проблеми, но когато имате работа с достатъчно големи числа, всяко число под определена значимост е незначително.

Следващата фигура показва как можете да приложите формула, която закръгля числата до определен брой значими цифри.

Използвате функцията ROUND на Excel, за да закръглите дадено число до определен брой цифри. Функцията ROUND приема два аргумента: първоначалната стойност и броя на цифрите, до които да се закръгли.

Въвеждането на отрицателно число като втори аргумент казва на Excel да закръгля въз основа на значими цифри вляво от десетичната запетая. Следната формула, например, връща 9500:

=КРЪГЛО(9489,-2)

Промяната на аргумента със значими цифри на -3 връща стойност от 9000.

=КРЪГЛО(B14,-3)

Това работи чудесно, но какво ще стане, ако имате числа в различни мащаби? Тоест, какво ще стане, ако някои от вашите числа са милиони, докато други са стотици хиляди? Ако искате да ги покажете всички с 1 значима цифра, ще трябва да изградите различна функция ROUND за всяко число, за да отчетете аргумента за различни значими цифри, който ще ви е необходим за всеки тип число.

За да помогнете за решаването на този проблем, можете да замените вашия твърдо кодиран аргумент със значими цифри с формула, която изчислява какво трябва да бъде това число.

Представете си, че вашият номер е -2330.45. Можете да използвате тази формула като аргумент за значими цифри във вашата функция ROUND:

LEN(INT(ABS(-2330,45)))*-1+2

Тази формула първо обвива вашия номер във функцията ABS, като ефективно премахва всеки отрицателен символ, който може да съществува. След това обвива резултата във функцията INT, премахвайки всички десетични знаци, които може да съществуват. След това обгръща това, което води до функцията LEN, за да получи мярка за това колко знака има в числото без никакви десетични знаци или символи за отрицание.

В този пример тази част от формулата води до числото 4. Ако вземете числото –2330,45 и премахнете десетичните и отрицателния символ, остават ви четири знака.

След това това число се умножава по -1, за да стане отрицателно число, и след това се добавя към броя на значимите цифри, които търсите. В този пример това изчисление изглежда така: 4 * –1 + 2 = –2.

Отново тази формула ще се използва като втори аргумент за вашата ROUND функция. Въведете тази формула в Excel и закръглете числото до 2300 (2 значими цифри):

=КРЪГЛО(-2330,45, LEN(INT(ABS(-2330,45)))*-1+2)

След това можете да замените тази формула с препратки към клетки, които сочат към номера на източника и клетка, която съдържа броя на желаните значими цифри.

=КРЪГЛ(B5,LEN(INT(ABS(B5)))*-1+$E$3)