Excel предоставляет в ваше распоряжение ряд функций, которые позволяют с помощью различных методов округлять введенные и вычисленные значения. Перечень этих функций приведен в таблице.
Предупреждение
Примите к сведению, что существует разница между округлением значений и их форматированием. При форматировании значения для отображения конкретного положения десятичной точки в формулах, обращающихся к этим значениям, используется внутреннее, истинное значение; отличие заключается лишь во внешнем представлении на экране. Когда же значение округляется, в связанных с ним формулах используются неточные (до определенного знака) величины.
Основные формулы округления
Функция ОКРУГЛ используется для округления значений до указанного количества десятичных знаков. Количество десятичных знаков указывается во втором аргументе функции. Например, ниже приведена формула, которая возвращает значение 123,40 (значение округляется до одного десятичного знака).
=ОКРУГЛ(123,37;1)
В том случае, если второй аргумент функции ОКРУГЛ равен нулю, значение округляется до ближайшего целого. Например, следующая формула возвращает значение 123,00:
=ОКРУГЛ(123,37;0)
Второй аргумент функции ОКРУГЛ может быть отрицательным. В таком случае число округляется до знака слева от десятичной запятой. Например, следующая формула возвращает значение 120,00:
=ОКРУГЛ(123,37;-1)
Функция ОКРУГЛ округляет значения как вверх, так и вниз. Но как может измениться значение, такое как 12,5 при округлении его до целого числа? Оказывается, функция ОКРУГЛ округляет такие числа в сторону удаления от нуля. Например, следующая формула возвращает значение 13,0:
=ОКРУГЛ(12,5;0)
Другая формула, использующая в качестве первого аргумента отрицательное значение, возвращает значение -13,0 (округление в сторону удаления от нуля).
=ОКРУГЛ(-12,5;0)
В особых случаях может потребоваться несколько усложнить округление значений. Тогда воспользуйтесь функцией ОКРУГЛВНИЗ и ОКРУГЛВВЕРХ. Например, следующая формула возвращает значение 12,0. Первоначальное значение округляется вниз.
=ОКРУГЛВНИЗ(12,5;0)
Следующая формула возвращает значение 13,0. Значение округлено вверх до ближайшего целого.
=ОКРУГЛВВЕРХ(12,43;0)
Округление до ближайшего кратного числа
Функция ОКРУГЛТ округляет одно число до ближайшего значения, кратного заданному. Следующая формула выполняет округление до ближайшего числа, кратного пяти. Результатом этой формулы будет 135.
=ОКРУГЛТ(133;5)
Округление денежных значений
Часто требуется перевести денежную величину в рублях до ближайшего значения в копейках. Например, вычисленная цена может быть 45,78923р. Как в этом случае округлить вычисляемое значение цены до ближайшего значения в копейках? Это достаточно просто, ведь существует три метода округления таких значений:
• округление вверх до ближайшего значения в копейках;
• округление вниз до ближайшего значения в копейках;
• округление до ближайшего значения в копейках (округление может произойти как вверх, так и вниз).
Следующая формула использует значение в рублях и копейках, содержащееся в ячейке А1, и округляет его до ближайшего значения в копейках. Например, если ячейка А1 содержит значение 12,421р., то формула возвращает значение 12,42р..
=ОКРУГЛ(А1;2)
Чтобы округлить значение вверх до ближайшего значения в копейках, воспользуйтесь функцией ОКРВВЕРХ. Следующая формула округляет значение ячейки А1 вверх до ближайшего значения в копейках. Если ячейка А1 содержит значение 12,421р., то формула возвращает значение 12,43р.
=ОКРВВЕРХ(А1;0,01)
Функция ОКРВНИЗ, может округлять значения в рублях вниз. Например, следующая формула округляет значение ячейки А1 вниз до ближайшего значения в копейках. Если ячейка А1 содержит значение 12,421р., то формула возвращает значение 12,42р.
=ОКРВНИЗ(А1;0,01)
Чтобы округлить значение в рублях вверх до ближайших пяти копеек, воспользуйтесь следующей формулой:
=ОКРВВЕРХ(А1;0,05)
Работа с дробными значениями денежных единиц
Если по роду своей деятельности вы занимаетесь финансовыми расчетами, например, на фондовой бирже, то найдете для себя полезными функции РУБЛЬ.ДРОБЬ и РУБЛЬ.ДЕС.
Возьмем, например, значение 9,25р. Какую функцию необходимо использовать, чтобы точно выразить десятичную часть как простую дробь (9 1/4, 9 2/8, 9 4/16 и т.п.)? Функция РУБЛЬ.ДРОБЬ использует два аргумента: десятичное значение в рублях и целое число, которое будет применять в качестве знаменателя дроби. Следующая формула возвращает значение 9,1 (где, 1 означает числитель дроби 1/4) :
=РУБЛЬ.ДРОБЬ(9,25;4)
Предупреждение
Значение, возвращаемое функцией РУБЛЬ.ДРОБЬ, нельзя использовать в других вычислениях. Значение, полученное в предыдущем примере, будет интерпретироваться как 9,1, но не как 9,25. Чтобы выполнить расчеты с использованием данного значения, его необходимо преобразовать обратно в значение десятичной дроби. Для этого используется функция РУБЛЬ.ДЕС.
Другая функция – РУБЛЬ.ДЕС – преобразует значение в рублях, выраженное в виде дроби, в значение в рублях, выраженное десятичным числом. Данная функция также использует второй аргумент, который представляет собой знаменатель дроби. Например, следующая формула возвращает значение 9,25:
=РУБЛЬ.ДЕС(9,1;4)
Безусловно, функции РУБЛЬ.ДРОБЬ и РУБЛЬ.ДЕС предназначены не только для обработки значений в денежном формате. Их можно использовать, например, для работы с футами и дюймами. Предположим, что мы имеем значение, представленное в футах – 8,5. Чтобы преобразовать его в футы и дюймы, воспользуйтесь приведенной ниже формулой. Данная формула возвращает значение 8,06 (или 8 футов и 6 дюймов).
=РУБЛЬ.ДРОБЬ(8,5;12)
Еще одним примером использования этих функций является ведение статистики в бейсболе. Подающий может выполнить 6 2/3 серии подач, что обычно отображается, как 6,2. Следующая формула возвращает значение 6,2:
=РУБЛЬ.ДРОБЬ(6+2/3;3)
Использование функций ЦЕЛОЕ и ОТБР
Внешне функции Excel ЦЕЛОЕ и ОТБР кажутся подобными, поскольку обе преобразуют исходное значение в целое. Функция ОТБР просто удаляет дробную часть числа. А функция ЦЕЛОЕ округляет число вниз до ближайшего целого, основываясь на дробной части числа.
На практике функции ЦЕЛОЕ и ОТБР возвращают различные результаты только тогда, когда в качестве аргумента функции используется отрицательное число. Например, следующая формула возвращает значение -14,0:
=ОТБР(-14,3)
Напротив, значение, которое возвращает следующая формула, будет равно -15,0, поскольку значение -14,3 округляется вниз до следующего меньшего целого.
=ЦЕЛОЕ(-14,3)
Функция ОТБР использует дополнительный (необязательный) параметр, отображающий количество десятичных знаков, которые должны присутствовать в возвращаемом значении. Например, приведенная ниже формула возвращает значение 54,33 (после запятой остается два знака).
=ОТБР(54,3333333;2)
Округление до четного и нечетного целого
Иногда может возникнуть необходимость округлить какое-либо значение вверх до ближайшего четного или нечетного целого. Для этого предназначены функции НЕЧЁТ и ЧЁТН. Данные функции используют только один аргумент и возвращают значение в виде целого числа. Функция ЧЁТН округляет аргумент вверх до ближайшего четного целого. Функция НЕЧЁТ округляет аргумент вверх до ближайшего нечетного целого. В таблице приведены примеры использования этих функций.
Округление значения до n-го разряда значащих цифр
Представьте себе, что вам необходимо округлить значение до определенного разряда значащих цифр. Например, округлить значение 1432187 до первых двух значащих цифр – 1400000. Или другой пример: значение 9187877 до значения с тремя значащими цифрами – 9180000.
Если значение представляет собой положительное число без десятичной части, можно воспользоваться следующей формулой, которая в данном случае будет работать. Формула округляет значение, содержащееся в ячейке А1, до двух значащих цифр. Для округления до другого числа значащих цифр следует заменить число 2 в этой формуле на необходимое значение.
=ОКРУГЛВНИЗ(А1;2-ДЛСТР(А1))
Чтобы выполнить данную операцию для нецелых и отрицательных значений, необходимо прибегнуть к небольшой хитрости. Следующая формула представляет собой более распространенный метод решения задачи округления значения, содержащегося в ячейке А1. При этом число значащих знаков определяется в ячейке А2. Данная формула работает для положительных, отрицательных и нецелых чисел.
=ОКРУГЛ(А1;А2-1-ЦЕЛОЕ(LOG10(ABS(А1))))
Например, если ячейка А1 содержит значение 1,27845, а ячейка А2 – значение 3, то формула возвращает значение 1,28000 (значение округлено до трех значащих цифр).