Excel отбросить дробную часть

Функция ОТБР в Excel используется для отбрасывания дробной части числового значения, переданного в качестве первого аргумента, до определенного количества знаков после запятой, которые указаны в качестве второго аргумента, и возвращает усеченное целое число.

Как найти частное от деления с помощью функции ОТБР в Excel

Операция отбрасывания дробной части отличается от округления чисел, тем, что итоговое значение не увеличивается или уменьшается в соответствии с правилами округления чисел. Например, при округлении числа 10,9 до целого будет возвращено число 11. Функция ОТБР вернет число 10.

Пример 1. В столбце таблицы записаны некоторые числа. Найти частное от деления каждого числа на 5. Если результатом является дробное число, отбросить дробную его до части до целого числа.

Вид таблицы данных:

Для нахождения искомых значений используем следующую формулу массива:

С помощью функции ЕСЛИ выполняется проверка наличия остатка от деления (функция ОСТАТ). Если остаток от деления больше нуля, отбрасывается дробная часть с помощью функции ОТБР, иначе – просто выполняется деление числа на 5.

В результате вычисления формулы мы нашли частное от деления в Excel для исходных чисел, разделенных на 5.

Как преобразовать числа десятичной дроби в дату в Excel

Пример 2. В таблице имеется столбец с дробными числами (с разным количеством знаков после запятой). Это закодированная информация, из которой нам нужно раскодировать дату – число дня и месяца для 2020-го года по следующему алгоритму:

1. Целая часть соответствует номеру месяца в 2020 году.
2. Первые 2 цифры дробной части (сотые) соответствуют номеру дня этого же месяца. Остальные цифры дробной части не учитываются. В смежном столбце необходимо вывести значения в формате Дата, соответствующие этим кодам записей дат.

Вид таблицы данных:

Для расчета значений используем следующую формулу:

Для получения данных требуемого формата из кодов используем функцию ДАТА. Чтобы вычислить значение номера месяца с помощью функции ОТБР отбрасываем дробную часть оставляя целое число – номер месяца. Для получения номера дня нам следует вычесть от исходного числа его целое число. Таким образом получим дробную часть, для которой следует переместить запятую вправо на 2 знака с помощью умножением дробной части на 100.

Результат вычислений для всех ячеек:

Как видно, некоторые ячейки содержат код ошибки поскольку 54 и 33 превышают допустимое число дней в месяце. Или 13 и 114 превышают количество месяцев в году. Обработаем это исключение с помощью формулы с проверкой на ошибки:

12;(A2-ОТБР(A2))*100>ДЕНЬ(КОНМЕСЯЦА(ДАТА(2020;ОТБР(A2);1);0)));"нет даты в коде";ДАТА(2020;ОТБР(A2);(A2-ОТБР(A2))*100))’ >

С помощью функции ИЛИ в первом аргументе ЕСЛИ проверяем значения при двух условиях:

1. Проверяем не превышает ли число 12 количество месяцев в исходном коде. Результат вычисления функции ОТБР возвращает целое число – если больше 12-ти, условие выполняется (число больше, чем количество месяцев в году), выводится сообщение об ошибке из второго аргумента ЕСЛИ, иначе – результат вычисления указанной выше формулы (в третьем аргументе функции ЕСЛИ).
2. Если число, полученное из первых двух цифр дробной части (сотая часть) больше, чем максимально возможное количество дней в этом месяце условие выполняется и получаем сообщение об ошибке «нет даты в коде». Иначе выполняется предыдущая формула раскодирования дат из исходных кодов.

Определим значения с помощью новой формулы и запишем в соседнем столбце:

Особенности использования функции ОТБР в Excel

Функция имеет следующую синтаксическую запись:

=ОТБР( число; [число_разрядов])

Описание аргументов функции:

• число – обязательный, принимает числовое значение, дробную часть которого требуется отбросить;
• [число_разрядов) – не обязательный, принимает числовое значение, указывающее на число знаков, до которых требуется произвести отсечение дробной части. Если явно не указан, принимается значение 0.

1. В качестве аргумента число могут быть указаны числа, текстовые представления числовых значений и данные логического типа. Если в качестве этого аргумента указано значение, которое не может быть преобразовано к данным числового типа, функция вернет код ошибки #ЗНАЧ!. Примеры допустимых значений: =ОТБР(“1,25”;0), ОТБР(ИСТИНА;0). Запись типа =ОТБР(“текст”;0) вернет код ошибки.
2. Если в качестве второго аргумента указано дробное число, оно будет отброшено. Например, =ОТБР(10,7345;2,5) и =ОТБР(10,7345;2) вернут одинаковый результат.
3. Если второй аргумент принимает значение, которое не может быть преобразовано к числовым данным, рассматриваемая функция вернет код ошибки #ЗНАЧ!
4. Функция может быть использована для округления чисел в меньшую сторону. Например, для округления числа 9999 до сотых используют следующую запись: =ОТБР(9999;-2). Результат вычисления – 9900. То есть, отрицательные значения второго аргумента указывают на операцию округления в меньшую сторону до определенного числа знаков перед запятой.
5. Для отбрасывания отрицательных чисел следует использовать функцию ОТБР вместо еще одной функции Excel – ЦЕЛОЕ, которая возвращает аналогичные результаты при работе с числами из диапазона положительных значений. Например, =ЦЕЛОЕ(-10,5) вернет ближайшее меньшее целое число (-11), в то время как =ОТБР(-10,5) просто отбросит дробную часть (-10).

В этой статье описаны синтаксис формулы и использование функции ОТБР в Microsoft Excel.

Описание

Усекает число до целого, отбрасывая дробную часть.

Синтаксис

Аргументы функции ОТБР описаны ниже.

Число Обязательный. Усекаемое число.

Число_разрядов Необязательный. Число, определяющее точность усечения. Значение по умолчанию — 0 (нуль).

Замечания

Функции ОТБР и ЦЕЛОЕ схожи в том отношении, что обе возвращают целые числа. Функция ОТБР отбрасывает дробную часть числа, а функция ЦЕЛОЕ округляет число в меньшую сторону до ближайшего целого в зависимости от значения дробной части числа. Функции ЦЕЛОЕ и ОТБР различаются только в работе с отрицательными числами: функция ОТБР(-4,3) возвращает значение -4, а функция ЦЕЛОЕ(-4,3) возвращает значение -5, поскольку -5 меньше, чем -4,3.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Усекает число 8,9 и возвращает целую часть (8).

Усекает отрицательное число и возвращает целую часть (-8).

Усекает число между 0 и 1, возвращая целую часть (0).

• ABS() — эта функция возвращает абсолютное значение переданного ей числа (то же число, но без знака). Например, ABS(3) и ABS(-3) вернут одно и то же значение 3.
• Int() , Fix() и Round() позволяют по разному округлять числа:

• Int() возвращает ближайшее меньшее целое;
• Fix() отбрасывает дробную часть;
• Round() округляет до указанного количества знаков после запятой.

Однако Round может вернуть не совсем ожидаемый результат. Если мы выполним такую строку кода

MsgBox Round(2.505, 2)

то результатом будет 2,5, хотя предполагалось получить 2,51. Поэтому порой для округления лучше использовать Format :

MsgBox Format(2.505, "#,##0.00")

Dim lRundNum As Long, lMinNum As Long, lMaxNum As Long lMinNum = 1: lMaxNum = 100 Randomize lRundNum = Int(lMinNum + (Rnd() * lMaxNum)) MsgBox lRundNum