Как округлить до значащей цифры формула в Excel

В некоторых финансовых отчетах данные показателей представлены с точностью округления до определенных значащих цифр. Что значит значащая цифра? Дело в том, что при визуальном анализе презентаций отчетов с миллионами не нужно засорять точными числовыми значениями отображая каждую цифру в числе (единицы, десятки, сотни и тысячи), чтобы не ухудшить читабельность данных.

Например, вместо того чтобы в презентации отчета указывать сумму 883 788 $, можно округлить до одной значащей цифры. Это значит, что после округления будет отображена сумма легкая к восприятию 900 000 $. Если округлить исходное число до двух значащих цифр, в результате получим значение числа 880 000 $.

Как округлить до трех значащих цифр в Excel

В Excel все решает пользователь. Программа округлит дробные или даже целые числа в зависимости какое число значащих цифр удовлетворит потребность пользователя. Несомненно, на первый взгляд такое округление может вызывать сомнение в рациональности решения. Однако в презентациях есть место быть как точным показателям, так и относительным. И в других ситуациях это также применимо. Например, в стратегическом планировании более важные относительные показатели, так как сколько не планируй никогда не угадаешь точные результирующие числа. В тактическом планировании более важны точные значения чтобы избежать серьезных просчетов. В стратегическом планировании где показатели достигают миллионов, каждое значение ниже определенного числа значащих цифр – не существенно.

Ниже на рисунке показано, как составить формулу, которая округляет миллионные числовые значения до заданного числа значащих цифр:

Функция ОКРУГЛ используется для округления исходного числового значения до определенного количества разрядов после запятой. Функция содержит 2 аргумента:

1. Число – ссылка на исходное значение, которое необходимо округлить.
2. Число-разрядов – количество разрядов, которое необходимо оставить после округления исходного числа.

Если во втором аргументе функции ОКРУГЛ указать отрицательное число, тогда Excel округлит исходное числовое значение в соответствии цифр по левой стороне запятой. Например, следующая формула возвращает в результате вычисления число 9500:

А если указать во втором аргументе значение -3, тогда функция ОКРУГЛ возвращает результат 9000:

Такая формула прекрасно работает, но не всегда. Например, что будет если исходные числовые значения будут разных величин числовых радов? Одни будут более миллиона, другие едва превышают сотни тысяч. Если возникнет необходимость округлить все такие исходные значения до одной и той же значащей цифры используя при этом одну и туже формулу (как обычно принято в Excel). Применять для отдельных групп исходных значений функцию ОКГРУГЛ с разными значениями в аргументах – это не правильное, а точнее не наилучшее решение. Хотя теоретически все может сработать.

Для красивого решения данной задачи следует использовать постоянное неизменяемое число значащих цифр в формуле, которая вычислит соответствующие значения.



Как узнать количество значащих цифр в Excel

Допустим исходное значение — это число -2330,45. Для вычисления числа значащих цифр в функции ОКРУГЛ можно использовать такую формулу:

Данная формула сначала преобразовывает заданное исходное отрицательно число в положительное используя функцию ABS (уберет знак минус спереди при его наличии). Полученный результат далее обрабатывается функцией ЦЕЛОЕ, котаря убирает все разряды после запятой. Далее полученный результат обрабатывается функцией ДЛСТР с целью подсчета количества символов из которых состоит исходное числовое значение без знака минуса и запятой.

В данном случаи функция ДЛСТР возвращает значение 4. Если из числа -2330,45 убрать знак минус и числа после запятой мы получим 4 цифры.

Далее результат функции умножается на отрицательное число -1, чтобы получить отрицательное значение (для второго аргумента функции ОКГРУГЛ). А после добавляется желаемое количесвто значащих цифр. В данном примере вычисление выглядит следующим образом: 4*(-1)+2=-2.

Подобно как в предыдущих примерах данная формула будет использоваться в качестве второго аргумента для функции ОКРУГЛ. Введите в Excel следующую формулу и округлите исходные данные -2330,45 до -2300:

Округляем до 3 значащих цифр в Excel

Число 2 указанное в аргументе данной формулы можно заменить на ссылку с ячейкой в которой будет указано желаемое число заданных значащих цифр пользователем. Как на рисунке с примером.

Таким образом можно округлить до 3 или 5 значащих цифр. Просто укажите необходимое значение в ячейке C2.