Формули для округлення чисел в 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)

Таким чином, введення 0,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:

=ROUND(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 значущих цифри):

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

Потім ви можете замінити цю формулу посиланнями на клітинки, які вказують на вихідний номер і клітинку, яка містить кількість бажаних значущих цифр.

=ROUND(B5,LEN(INT(ABS(B5)))*-1+$E$3)