Как сделать формулу в excel с округлением?

Microsoft Excel

трюки • приёмы • решения

Как в Excel округлять значения

Округление чисел — распространенная задача, и Excel предоставляет достаточно функций, которые округляют значения разными путями (табл. 111.1). Вы должны понимать разницу между округлением и форматированием значения.

Когда вы форматируете число так, чтобы оно отображало определенное количество знаков после запятой, формулы, которые ссылаются на это число, работают с его фактическим значением, которое может отличаться от отображаемого. Когда вы округляете числа, то формулы, которые ссылаются на эти значения, используют округленные числа.

Таблица 111.1. Функции округления Excel

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

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

Функция ОКРУГЛТ полезна для округления значений до ближайших кратных. Например, вы можете использовать ее, чтобы округлить число до ближайшего, кратного 5. Следующая формула возвращает 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) .

Читать еще:  Как сделать в excel анкету?

Чтобы округлить рублевое значение до пяти копеек, используйте эту формулу: =ОКРВВЕРХ(А1;0,05) .

Использование функций ЦЕЛОЕ и ОТБР

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

На практике ЦЕЛОЕ и ОТБР возвращают разные значения только при использовании отрицательных чисел. Например, эта формула возвращает -14.0: =ОТБР(-14,2) .

Следующая формула возвращает -15,0, поскольку число -14,2 округляется в меньшую сторону до следующего целого числа: =ЦЕЛОЕ(-14,2) .

Функция ОТБР принимает дополнительный (необязательный) аргумент, который может быть полезен для усечения десятичных значений. Например, следующая формула возвращает 54,33 (значение усекается до двух десятичных знаков): =ОТБР(54.3333333;2) .

Округление до N значащих цифр

В некоторых ситуациях может понадобиться округлить значение до определенного количества значащих цифр. Например, вы хотите выразить число 1432,187 в терминах двух значащих цифр (то есть как 1400,000). Число 84 356 выражается в трех значимых цифрах как 84 300.

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

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

Например, если ячейка А1 содержит число 1,27845, а в ячейке А2 хранится 3, то формула вернет 1,28000 (значение, округленное до трех значащих цифр).

Читать еще:  Как сделать ошибки в excel?

В ремонт металлических дверей замков входит починка дверных петель, коробок и полотен, которые с течением времени и из-за неправильного использования могут расклеиваться и перекашиваться. Бывает так, что они просто перестают плотно держаться в массиве, потому что их плохо прижимают шурупы, которые, в свою очередь, могут выходить из массива вследствие плохо нарезанной резьбы или сырой древесины, куда они были вкручены. В результате после усыхания материала отверстия стали шире и шурупы вышли из них.

Функции округления в Excel

В Excel часто используют округление чисел. В данной заметке рассмотрим разные типы округления, которые встречаются на практике.

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

Формат никак не влияет на точность числа в ячейке. Поэтому не стоит надеяться на формат, когда нужно реальное округление.

Функция ОКРУГЛ в Excel

Для обычного округления числа по математическим правилам существует функция ОКРУГЛ, которая имеет следующий синтаксис:

ОКРУГЛ(число или ссылка на округляемое число; количество оставляемых знаков).

число или ссылка на округляемое число – исходное число для округления;

количество оставляемых знаков – количество знаков после запятой.

Например, формула округления в Excel до двух знаков после запятой выглядит так.

Чтобы получить формулу округления в Excel до целого числа, во втором аргументе нужно поставить 0.

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

Функция ОКРУГЛ умеет округлять не только после запятой, но и до запятой, т.е. до тысяч, миллионов и т.д. Например, чтобы число 123456789 округлить до тысяч и получить 123457000, точность указывается с минусом.

На этом формулы округления в Эксель не заканчиваются.

ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ

В Excel есть формулы округления в большую и меньшую сторону независимо от последних цифр числа. Например, расчетные цены округляют вверх, чтобы не уменьшить прибыль; возраст человека округляют вниз до целого, чтобы узнать полное количество лет. Короче, для этих целей придуманы функции ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ.

Читать еще:  Как сделать схему для вязания в excel?

Данные функции имеют такие же параметры, как и ОКРУГЛ (ссылка на число и количество знаков до или после запятой).

Довольно полезная функция ОКРУГЛТ. С ее помощью можно добиться округления не только до нужного разряда (слева или справа от запятой), но и вообще до нужной точности (кратности). К примеру, нужно рассчитать заказ изделий в штуках, но так, чтобы он был равен целому количеству ящиков. Если в ящике 6 шт, то заказываемое количество должно быть кратно шести. Или другой пример. Нужно, чтобы расчетная цена заканчивалась либо на 00, либо на 50, то есть имела кратность 50 рублей. Функция ОКРУГЛТ легко решает такие задачи.

Очень, очень правильная функция. Позволяет избежать 3-х промежуточных расчетов (разделить на кратность, округлить до целого и снова умножить на кратность, как я сам делал, пока не узнал про эту формулу).

ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ

Округление с заданной точностью также можно производить принудительно вверх или вниз с помощью ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ. Например, при расчете заказа, когда никак нельзя заказать меньше расчетного объема, но при этом нужно и кратность сохранить. То есть одна «лишняя» штука требует заказа дополнительно целого ящика. Аргументы функции те же.

Это были основные формулы округления в Excel. Но есть еще парочку, которые используются реже.

Функция ЦЕЛОЕ в Excel отбрасывает дробную часть и таким образом округляет вниз до ближайшего целого. Будет полезна при расчете целого количество лет между датами (возраст человека).

Функции ЧЁТН и НЕЧЁТ округляют числа до ближайшего четного или нечетного значения соответственно.

Видеоуроки ниже показываю основные приемы округления, а также некоторые трюки.



Ссылка на основную публикацию
Adblock
detector