Округление чисел в Excel

Рубрика: 7. Полезняшки Excel

Округление чисел — очень распространенная задача, и Excel предоставляет ряд функций, позволяющих округлять числа различными способами. Помните о различии между округлением и форматированием значения. Если вы форматируете число, чтобы в нем отображалось только конкретное количество десятичных знаков, то формулы, ссылающиеся на это число, оперируют точным значением, которое может отличаться от отображаемого. Когда вы округляете число, формулы, оперирующие им, работают именно с округленным значением. [1] В таблице приведены функции округления, применяемые в Excel (в алфавитном порядке; описание соответствует контекстному меню).

%d1%80%d0%b8%d1%81-1-%d1%84%d1%83%d0%bd%d0%ba%d1%86%d0%b8%d0%b8-%d0%be%d0%ba%d1%80%d1%83%d0%b3%d0%bb%d0%b5%d0%bd%d0%b8%d1%8f-%d0%bf%d1%80%d0%b8%d0%bc%d0%b5%d0%bd%d1%8f%d0%b5%d0%bc%d1%8b%d0%b5

Рис. 1. Функции округления, применяемые в Excel

Скачать заметку в формате Word или pdf, примеры в формате Excel

Функции ЧЁТН и НЕЧЁТ имеют один аргумент, и округляют до ближайшего четного (нечетного) в сторону от нуля (рис. 2). Если нужно округлить до ближайшего четного в сторону к нулю, воспользуйтесь функцией =ОКРВНИЗ.МАТ(G2;2;1). Третий аргумент этой функции должен отличаться от нуля, чтобы округление было выполнено в сторону нуля. Для округления до ближайшего нечетного в сторону нуля простой функции нет, требуется более сложная формула.

%d1%80%d0%b8%d1%81-2-%d0%be%d0%ba%d1%80%d1%83%d0%b3%d0%bb%d0%b5%d0%bd%d0%b8%d0%b5-%d0%b4%d0%be-%d0%b1%d0%bb%d0%b8%d0%b6%d0%b0%d0%b9%d1%88%d0%b5%d0%b3%d0%be-%d1%87%d0%b5%d1%82%d0%bd%d0%be%d0%b3%d0%be

Рис. 2. Округление до ближайшего четного/нечетного

Функции ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ дают следующие возможности округления (рис. 3):

  • В большую или меньшую сторону
  • Для отрицательных чисел – к нулю или от нуля
  • Позволяет указать кратное, до которого требуется округлить число; может быть любым (целым или дробным)

%d1%80%d0%b8%d1%81-3-%d0%be%d0%ba%d1%80%d1%83%d0%b3%d0%bb%d0%b5%d0%bd%d0%b8%d0%b5-%d0%b4%d0%be-%d0%b1%d0%bb%d0%b8%d0%b6%d0%b0%d0%b9%d1%88%d0%b5%d0%b3%d0%be-%d0%ba%d1%80%d0%b0%d1%82%d0%bd%d0%be%d0%b3

Рис. 3. Округление до ближайшего кратного

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

%d1%80%d0%b8%d1%81-4-%d0%be%d0%ba%d1%80%d1%83%d0%b3%d0%bb%d0%b5%d0%bd%d0%b8%d0%b5-%d0%b4%d0%be-%d0%b7%d0%b0%d0%b4%d0%b0%d0%bd%d0%bd%d0%be%d0%b3%d0%be-%d1%87%d0%b8%d1%81%d0%bb%d0%b0-%d0%b4%d0%b5

Рис. 4. Округление до заданного числа десятичных знаков или значащих цифр

Функция ОКРУГЛТ возвращает число, округленное с нужной точностью. В отличие от функций ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ она округляет по правилам математики (рис. 5). В справке сказано, что функция производит округление с избытком. Округление производится в том случае, если остаток от деления числа на точность больше или равен половине точности. Но есть два исключения:

  • Иногда половина точности округляется в меньшую сторону (см. строку 3 на рис. 5)
  • Если число и кратность разного знака, возвращается ошибка #ЧИСЛО!

%d1%80%d0%b8%d1%81-5-%d0%b4%d0%b2%d0%b5-%d1%81%d1%82%d1%80%d0%b0%d0%bd%d0%bd%d0%be%d1%81%d1%82%d0%b8-%d1%84%d1%83%d0%bd%d0%ba%d1%86%d0%b8%d0%b8-%d0%be%d0%ba%d1%80%d1%83%d0%b3%d0%bb%d1%82

Рис. 5. Две странности функции ОКРУГЛТ: не всегда корректно отрабатывает значение половинной точности, и выдает ошибку, если число и кратность разного знака

Функции ЦЕЛОЕ и ОТБР. ЦЕЛОЕ округляет значение в меньшую сторону до ближайшего целого. В том числе и для отрицательных чисел, т.е. в сторону от нуля (рис. 6). ОТБР – отбрасывает дробную часть. Для отрицательных чисел это приводит к округлению в сторону к нулю. У функции ЦЕЛОЕ один аргумент, а у функции ОТБР – два. Второй (опциональный) указывает, сколько знаков после запятой оставить после округления. Если второй аргумент отрицательный, это указывает Excel, сколько знаков перед запятой округлить до нуля.

%d1%80%d0%b8%d1%81-6-%d1%84%d1%83%d0%bd%d0%ba%d1%86%d0%b8%d0%b8-%d1%86%d0%b5%d0%bb%d0%be%d0%b5-%d0%b8-%d0%be%d1%82%d0%b1%d1%80

Рис. 6. Функции ЦЕЛОЕ и ОТБР

Округление до n значащих разрядов. В некоторых ситуациях может потребоваться округлить значение до определенного количества значащих разрядов. Например, нужно выразить значение 143 187 в виде двух значащих разрядов, как 140 000. Если значение является положительным числом без десятичных разрядов, то справится следующая формула: =ОКРУГЛ(A2;2-ДЛСТР(A2)).

При работе с нецелыми или отрицательными числами задача усложняется. Следующая формула дает более общее решение, округляя число в ячейке А3 до количества значащих разрядов, указанного в В3: =ОКРУГЛ(A3;B3-1-ЦЕЛОЕ(LOG10(ABS(A3)))). Эта формула работает с любыми целыми числами (положительными и отрицательными), а также с дробными (рис. 7).

%d1%80%d0%b8%d1%81-7-%d0%be%d0%ba%d1%80%d1%83%d0%b3%d0%bb%d0%b5%d0%bd%d0%b8%d0%b5-%d0%b4%d0%be-n-%d0%b7%d0%bd%d0%b0%d1%87%d0%b0%d1%89%d0%b8%d1%85-%d1%80%d0%b0%d0%b7%d1%80%d1%8f%d0%b4%d0%be%d0%b2

Рис. 7. Округление до n значащих разрядов

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 135–137.


Прокомментировать