Semenalidery.com

IT Новости из мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция округлить в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ОКРУГЛТ

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

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

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

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

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

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

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

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



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

Доброго времени суток уважаемый читатель!

В этой статье я хочу рассказать о возможности округление чисел в Excel. Очень часто вы сталкиваетесь с тем, что выгружая данные из других источников или делая собственные вычисления вы получаете числа с огромным количеством значений после запятой, что в свою очередь может негативно сказаться на конечном результате вычислений, ведь вы видите число с двумя знаками после запятой (например, 98,12), а для компьютера это уже другое число (например, 98,1248247…. ) и соответственно он работает с тем, что видит сам, а не то что видите вы. Но такой вариант снижает точность получаемых данных, так что используйте функции округления в конкретных и нужных вычислениях. Эта возможность станет очень полезной в бухгалтерских вычеслений, когда каждое несоответствие в 1 копейку уже нарушает баланс и гармонию в вычислениях.

Как происходит округление чисел в Excel, рассмотрим четыре главных существующих функции:

  1. Функция ОКРУГЛ (ROUND) – позволит вам производить округление чисел вExcel до указанного критерия.
  • = ОКРУГЛ(A1;2) – округление до второго знака;
  • = ОКРУГЛ(A1;1) — округление до первого знака;
  • = ОКРУГЛ(A1;0) — округление до целого значения;
  • = ОКРУГЛ(A1;-1) – округление до ближайшего десятка;
  • = ОКРУГЛ(A1;-2) — округление до ближайшей сотни;

Напоминание! Округление чисел в Excel производится, для чисел от 1 до 4 в меньшую сторону, а от 5 до 9 в большую сторону. (Пример, число 98,1268…. при округлении будет 98,13, а при округлении до одного знака, станет 98,1)

2. Функция ОКРУГЛВВЕРХ (ROUNDUP) – позволит вам принудительно производить округление чисел в Excel в большую сторону, если оно положительное и в меньшую, если оно отрицательное. Основным критерием служит то, как далеко по числовой оси от нуля находится ваше число:

  • = ОКРУГЛВВЕРХ(A1;1) -98,1268 = -98,2
  • = ОКРУГЛВВЕРХ(A1;1) 98,1268 = 98,2

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

  • = ОКРУГЛВНИЗ(A1;1) -98,1268 = -98
  • = ОКРУГЛВНИЗ(A1;1) 98,1268 = 98
Читать еще:  Сортировка в алфавитном порядке в excel

4. Функция НЕЧЕТ – позволяет производить округление чисел в Excel к самому ближайшему нечётному числу. Несмотря на знаки числа, округление будет выполнено с избытком. Если число уже является нечётным, действий никаких не будет.

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

Главные функции, которые производят округление чисел в Excel, мы рассмотрели, теперь можете применять полученные знание на практике. С другими функциями вы можете ознакомится в «Справочнике функций». Успехов вам!

Я не терпел поражений. Я просто нашёл 10 000 способов, которые не работают.
Томас Эдисон

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

При выполнении деления или работе с дробными числами, Excel производит округление. Это связано, прежде всего, с тем, что абсолютно точные дробные числа редко когда бывают нужны, но оперировать громоздким выражением с несколькими знаками после запятой не очень удобно. Кроме того, существуют числа, которые в принципе точно не округляются. В то же время, недостаточно точное округление может привести к грубым ошибкам в ситуациях, где требуется именно точность. К счастью, в программе имеется возможность пользователям самостоятельно устанавливать, как будут округляться числа.

Особенности округления чисел Excel

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

С помощью операции округления Эксель отбрасывает некоторое количество знаков после запятой. В нем применяется общепринятый способ округления, когда число меньше 5 округляется в меньшую сторону, а больше или равно 5 – в большую сторону.

Округление с помощью кнопок на ленте

Самый простой способ изменить округление — это выделить ячейку или группу ячеек и, находясь на вкладке «Главная», нажать на ленте на кнопку «Увеличить разрядность» или «Уменьшить разрядность». Обе кнопки располагаются в блоке инструментов «Число». Будет округляться только отображаемое число, но для вычислений при необходимости будут задействованы до 15 разрядов чисел.

При нажатии на кнопку «Увеличить разрядность» количество внесенных знаков после запятой увеличивается на один.

Кнопка «Уменьшить разрядность», соответственно, уменьшает на одну количество цифр после запятой.

Округление через формат ячеек

Есть возможность также выставить округление с помощью настроек формата ячеек. Для этого нужно выделить диапазон ячеек на листе, кликнуть правой кнопкой мыши и в появившемся меню выбрать пункт «Формат ячеек».

В открывшемся окне настроек формата ячеек следует перейти на вкладку «Число». Если формат данных указан не числовой, необходимо выставить именно его, иначе вы не сможете регулировать округление. В центральной части окна около надписи «Число десятичных знаков» просто укажите цифрой то количество знаков, которое желаете видеть при округлении. После этого примените изменения.

Установка точности расчетов

Если в предыдущих случаях устанавливаемые параметры влияли только на внешнее отображения данных, а при расчетах использовались более точные показатели (до 15 знака), то сейчас мы расскажем, как изменить саму точность расчетов.

    Перейдите на вкладку «Файл», оттуда — в раздел «Параметры».

Откроется окно параметров Excel. В этом окне зайдите в подраздел «Дополнительно». Отыщите блок настроек под названием «При пересчете этой книги». Настройки в этом блоке применяются не к одному листу, а к книге в целом, то есть ко всему файлу. Поставьте галочку напротив параметра «Задать точность как на экране» и нажмите «OK».

  • Теперь при расчете данных будет учитываться отображаемая величина числа на экране, а не та, которая хранится в памяти Excel. Настройку же отображаемого числа можно провести любым из двух способов, о которых мы говорили выше.
  • Применение функций

    Если же вы хотите изменить величину округления при расчете относительно одной или нескольких ячеек, но не хотите понижать точность расчетов в целом для документа, в этом случае лучше всего воспользоваться возможностями, которые предоставляет функция «ОКРУГЛ» и различные ее вариации, а также некоторые другие функции.

    Среди основных функций, которые регулируют округление, следует выделить такие:

    • «ОКРУГЛ» – округляет до указанного числа десятичных знаков согласно общепринятым правилам округления;
    • «ОКРУГЛВВЕРХ» – округляет до ближайшего числа вверх по модулю;
    • «ОКРУГЛВНИЗ» – округляет до ближайшего числа вниз по модулю;
    • «ОКРУГЛТ» – округляет число с заданной точностью;
    • «ОКРВВЕРХ» – округляет число с заданной точностью вверх по модулю;
    • «ОКРВНИЗ» – округляет число вниз по модулю с заданной точностью;
    • «ОТБР» – округляет данные до целого числа;
    • «ЧЕТН» – округляет данные до ближайшего четного числа;
    • «НЕЧЕТН» – округляет данные до ближайшего нечетного числа.

    Для функций «ОКРУГЛ», «ОКРУГЛВВЕРХ» и «ОКРУГЛВНИЗ» используется следующий формат ввода: Наименование функции (число;число_разрядов) . То есть если вы, к примеру, хотите округлить число 2,56896 до трех разрядов, то применяете функцию «ОКРУГЛ(2,56896;3)». В итоге получается число 2,569.

    Для функций «ОКРУГЛТ», «ОКРВВЕРХ» и «ОКРВНИЗ» применяется такая формула округления: Наименование функции(число;точность) . Так, чтобы округлить цифру 11 до ближайшего числа, кратного 2, вводим функцию «ОКРУГЛТ(11;2)». На выходе получается результат 12.

    Функции «ОТБР», «ЧЕТН» и «НЕЧЕТ» используют следующий формат: Наименование функции(число) . Для того чтобы округлить цифру 17 до ближайшего четного, применяем функцию «ЧЕТН(17)». Получаем результат 18.

    Читать еще:  Впр в 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) .

    Чтобы округлить рублевое значение до пяти копеек, используйте эту формулу: =ОКРВВЕРХ(А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)))) .

    Читать еще:  Среднее арифметическое значение в excel

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

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

    Как округлять числа в Excel

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

    Округлить число в путем изменения формата ячейки

    Если вы хотите округлить числа в Excel исключительно для визуальной презентации, вы можете изменить формат ячейки, выполнив следующие шаги:

    1. Выберите ячейку с числами, которые вы хотите округлить.
    2. Откройте диалоговое окно « Формат ячеек », нажав Ctrl+1 или щелкните правой кнопкой мыши по ячейке и выберите « Формат ячеек » в контекстном меню.

    Как округлить число в Excel – Формат ячеек
    1. Во вкладке « Число » выберите « Числовой » или « Денежный » формат и введите число десятичных знаков, которое вы хотите отобразить в поле « Число десятичных знаков ». Предварительный просмотр, как будет округлено число отобразится в разделе « Образец ».
    2. Нажмите кнопку « ОК », чтобы сохранить изменения и закрыть диалоговое окно.

    Как округлить число в Excel – Округлить число, изменением формата ячейки

    Обратите внимание! Этот метод изменяет формат отображения без изменения фактического значения, хранящегося в ячейке. Если вы ссылаетесь на эту ячейку в любых формулах, то во всех вычислениях будет использоваться исходное число без округления. Если вам необходимо фактически округлить число в ячейке, то используйте функции округления Excel.

    Как округлить число функцией ОКРУГЛ

    ОКРУГЛ – это основная функция округления чисел в Excel, которая округляет число до указанного количества знаков.

    Синтаксис :

    Число — любое действительное число, которое вы хотите округлить. Это может быть число или ссылка на ячейку.

    Число_разрядов — количество знаков для округления числа. Вы можете указать положительное или отрицательное значение в этом аргументе:

    • Если число_разрядов больше 0, число округляется до указанного количества десятичных знаков. Например, =ОКРУГЛ(17,25; 1) округляет число 17,25 до 17,3.

    Чтобы округлить число до десятых, укажите в аргументе число_разрядов значение равное 1.

    Как округлить число в Excel – Как округлить число до десятых

    Если необходимо округлить число до сотых, задайте аргумент число_разрядов равным 2.

    Как округлить число в Excel – Как округлить число до сотых

    Для того чтобы округлить число до тысячных, введите 3 в число_разрядов.

    Как округлить число в Excel – Как округлить число до тысячных
    • Если число_разрядов меньше 0, все десятичные разряды удаляются, а число округляется слева от десятичной точки (до десятых, до сотен, до тысяч и т. д.). Например, =ОКРУГЛ(17,25; -1) округляет 17,25 к ближайшему кратному 10 числу и возвращает в результате 20.
    • Если число_разрядов равно 0, число округляется до ближайшего целого числа (без десятичных знаков). Например, =ОКРУГЛ(17,25; 0) округляет 17,25 до 17.

    На следующем изображении демонстрируются несколько примеров, как округлить число в Excel в формуле ОКРУГЛ:

    Как округлить число в Excel – Примеры формул, как округлить число, функцией ОКРУГЛ

    Как округлить число в большую сторону функцией ОКРУГЛВВЕРХ

    Функция ОКРУГЛВВЕРХ округляет число вверх (от 0) до заданного количества цифр.

    Синтаксис :

    Число — число для округления.

    Число_разрядов — количество знаков, до которых вы хотите округлить число. Вы можете указать как положительные, так и отрицательные числа в этом аргументе, и он работает как число_разрядов функции ОКРУГЛ, описанной выше, за исключением того, что число всегда округляется в большую сторону.

    Как округлить число в Excel – Примеры формул, как округлить число в большую сторону, функцией ОКРУГЛВВЕРХ

    Как округлить число в меньшую сторону функцией ОКРУГЛВНИЗ

    Функция ОКРУГЛВНИЗ в Excel делает противоположное тому, что делает ОКРУГЛВВЕРХ, т. е. округляет число вниз.

    Синтаксис :

    Число — число, подлежащее округлению.

    Число_разрядов — количество знаков, до которых вы хотите округлить число. Работает как аргумент число_разрядов функции ОКРУГЛ, за исключением того, что число всегда округлено в меньшую сторону.

    Следующее изображение демонстрирует, как округлить число в Excel в меньшую сторону функцией ОКРУГЛВНИЗ в действии.

    Как округлить число в Excel – Примеры формул, как округлить число в меньшую сторону, функцией ОКРУГЛВНИЗ

    Так выполняется округление чисел в Excel. Надеюсь, теперь вы знаете, как среди всех этих способов, как округлить число в Excel, выбрать наиболее подходящий для ваших нужд.

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