Функции округления в Excel | statanaliz.info

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

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

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

Формат числа для отображения знаков после запятой

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

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

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

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

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

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

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

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

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

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

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

Округление до тысяч

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

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

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

okrugl-04

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

ОКРУГЛТ

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

Округление с заданной кратностью

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

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

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

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

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

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

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

Поделиться в социальных сетях:
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  • Аноним

    Дмитрий, я в тупике… не знаю что делать… Подскажите мне новечку, чем сможете.
    =ОКРВВЕРХ.МАТ(J5;0,9) это выражение в ячейке K5.
    В ячейке J5 у меня значение 1,805 литров растительного масла. Далее одна бутылка масла имеет обьём 900 мл. а одна бутылка стоит скажем 55 рублей (ячейка Е5). Так вот, задача у меня стоит в том, чтобы вывести стоимость трёх бутылок масла! И что у меня получается :
    Ячейка K5 (=ОКРВВЕРХ.МАТ(J5;0,9)) в ней я вывожу колличество бутылок, где 0,9 это 900 мл. — получаю 3
    Ячейка E5 (55) стоимость одной бутылки обьёмом 900 мл.
    Ячейка L5 (=K5*E5) в этой ячейки я хочу 55 умножить 3 и получить 165 рублей… Но я получаю 148,5 рублей, потому что формула умножает не на 3 (бут.), а на 2,7 л. (0,9+0,9+0,9).
    Так как значение в ячейке J5 будет в дальнейшем изменяться, то мне нужно что бы и конечная стоимость изменялась автоматически, без всякого возможного ввода вручную. Такие вот дела…
    Дмитрий, помогите пожалуйста. Может я ошибку где-то допустил… Или может есть и другая формула… У меня уже голова от них кружится… Ну или в крайнем случае может совет дадите где бы я смог решить эту проблемку? Я сначала сам переискал возможные и доступные способы, и только теперь к Вам обращаюсь… Есть решения ?

    • Езепов Дмитрий

      Добрый день. Пришлите лучше пример, чтобы я сразу на формулы посмотрел. И укажите, что должно получиться для сверки.

  • Аноним

    В файле я выделил ячейку зелёным цветом, где нужно выявить конечную цифру

    • Аноним
      • Езепов Дмитрий

        а где файл? Пришлите на почту (слева внизу этой страницы указан адрес)

  • Аноним

    Отправил файл на [email protected]

    • Езепов Дмитрий

      Отправил назад.

  • Аноним

    Класс ! ))
    Огромное спасибо!

  • Аноним

    эти округления значения не имеют. Пример: забиваем в столб(А) формулу =А1(оно равно 1,05) *2,1+ОКРВВЕРХ.МАТ(любые числа), далее в столбе(С) формула =А1*В1(можно поставить любое число). в итоге таблица берет данные в ячейке А1 не округленное число, а натуральное.

  • Аноним

    у вас опечатка в слове видеоурок. А в целом очень полезная статья.

Комментарии для сайта Cackle
Пролистать наверх

Электронная книга трюков MS Excel

Секреты эффективной работы

Конфиденциальность данных гарантирована.
Никакого спама.