Модифицированное округление

Денис Парфенов    | 2021.08.09

Саманте нужна формула, округляющая значение в зависимости от того, меньше оно или равно 0,25. Так, например, 3,24 округляет до 3, а 3,25 (или больше) округляет до 4.

Округление в обычном месте (.5) в Excel довольно просто, так как программа предоставляет функцию ОКРУГЛЕНИЯ рабочего листа. Если вы хотите, чтобы «точка останова» для округления была чем-то, кроме 0,5, вам нужно полагаться на формулу, а не на простую функцию рабочего листа.

Есть (буквально) десятки формул, которые вы можете придумать, чтобы получить нужный результат. Как и во многих других случаях в Excel, решение зависит от характера ваших данных. Если округляемое значение всегда будет положительным, то следующие формулы будут работать нормально. (Я не включал эти формулы ни в каком конкретном порядке, кроме сортировки по алфавиту.)

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

ExcelTips - ваш источник экономичного обучения Microsoft Excel. Этот совет (10126) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.

Автор биографии

На его счету более 50 научно-популярных книг и многочисленные журнальные статьи, поэтому Аллен Вятт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

БОЛЬШЕ ОТ АЛЛЕН

Ярлык для выбора диапазона данных

Хотите выбрать все данные в непрерывном разделе листа? Ярлык, описанный в этом совете, очень упрощает задачу.

Отмена редактирования

При редактировании ячейки вы можете в какой-то момент отменить редактирование. Есть два способа сделать это, оба описаны здесь.

Неправильные ссылки после сортировки гиперссылок

При сортировке данных всегда следует проверять, правильно ли была произведена сортировка. Что делать, если сортировка не удалась.

Создавайте собственные приложения с помощью VBA!Узнайте, как расширить возможности Office 2013 (Word, Excel, PowerPoint, Outlook и Access) с помощью программирования VBA, используя его для написания макросов, автоматизации приложений Office и создания пользовательских приложений. Оцените освоение VBA для Office 2013 сегодня!

Дополнительные подсказки Excel (лента)

Как избежать ошибок округления в результатах формул

Некоторые формулы просто не дают ожидаемых результатов. Иногда это связано с тем, как Excel обрабатывает округление. .

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

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

Необходимо округлить время в ячейке до определенного значения? Есть несколько способов сделать это с помощью формулы.

Подписаться

БЕСПЛАТНАЯУСЛУГА: получайте подобные советы каждую неделю в бесплатном информационном бюллетене ExcelTips . Введите свой адрес и нажмите «Подписаться».

Комментарии

Я заметил два вопроса Стива Миллера и Джеймса. Надеюсь, они давно нашли свои ответы.

Их вопросы казались интересными для других, поэтому позвольте мне предложить это.

Основываясь на примере Стива Миллера, если OLF и AREA определены в ячейках A1 и A2, то он может поместить эту формулу в A3:

= MAX (ROUNDDOWN (A2 / A1), 1)

Эта формула вычислит то, что он хочет, и никогда не будет меньше 1.

Формула Джеймса будет заключена в a = MAX (>, 0,25)

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

Вот обходной путь, который я бы использовал:

= ЕСЛИОШИБКА (ЕСЛИ (СЧЁТ (A1, A2) = 2, MAX (ROUNDDOWN (A2 / A1), 1), «н / д»), «н / д»)

На первый взгляд это может показаться немного сложным, но обратите внимание на исходную формулу посередине. Я заключил это в "IF (COUNT (A1, A2),.," N / a ")". Это просто говорит о вычислении формулы, если у нас есть два числовых входных значения. Если нет, то возвращаем значение «н / п».

«ЕСЛИОШИБКА (.,« Н / п »)» предназначена для возврата значения «н / п», если формула оценивается как ошибка. Чего ждать? Что ж, если значение A1 равно нулю, тогда вычисление вернет ошибку деления на ноль. хотя пользователь не может вводить ноль в A1, возможно, что A1 может быть результатом какого-либо другого вычисления, которое возвращает ноль.

Ваш пост с советами был мне очень полезен.

Мне нужно было округлить числа, включая 0,5, и округлить до 0,6.

Я получил следующую формулу = IF (A1-INT (A1)

Спасибо миллион,

Изабела

Привет, я долго искал в Интернете и, похоже, не нашел ответа, так что надеюсь.

Я ищу формулу, которая делит площадь на вместимость:

Коэффициент загрузки площади / занятости (OLF), округленный до ближайшего целого числа. итак формула = округление (площадь / OLF)

Однако, если возврат находится между 0 и 1, я получаю возврат 0. Мне нужно, чтобы это было 1.

Я пробовал использовать функции MROUND, ROUNDDOWN, FLOOR и FLOOR.MATH.

OLF = 20

Площадь = 25

КРУГЛЫЙ ВНИЗ (25/20) = 1

OLF 20

Площадь = 12

ОКРУГЛ ВНИЗ (12/20) = 0. Мне нужен ответ 1 (минимум)

Еще раз спасибо Аллену за исчерпывающее и хорошо объясненное решение проблемы Excel. За 10 секунд я применил это к своим собственным потребностям. Спасибо, что были там и поделились своими знаниями и навыками с другими.

Я продолжил поиск и нашел это:

https://blogs.msdn.microsoft.com/ericlippert/2003/09/26/bankers-rounding/

Теперь я знаю название соглашения, которое я использовал на протяжении полувека, Bankers 'Rounding. Эта страница также дала мне удивительную информацию, которая может оказаться полезной другим: функция Round, набранная напрямую, округляет 0,5 в большую сторону. Однако округление в пользовательской функции использует округление Банкиров и округляет до ближайшего четного целого числа.

Итак, я смог создать эту красивую простую запись в файле надстройки:

Открытая функция RoundEven (Num As Double)

RoundEven = Round (Num)

Конечная функция

Очень благодарен за ответы на мой вопрос; они помогли мне осознать важность для моей цели различий между Round и Round (в разных контекстах Excel).

Я повторно просмотрел исходный пост и думаю, что смог позолотить аффинированное золото Нира Либермана по этой формуле:

Спасибо, что указали мне на функцию ISEVEN. Эта формула теперь дает мне то, что я хочу для цели «округлить 0,5 до ближайшего четного числа». Я флиртовал с TRUNC вместо INT, но INT, кажется, лучше работает с отрицательными числами для моей цели.

Хотя у меня есть пара чрезвычайно простых функций в моем файле personal.xlsb или xlam, я безнадежно не могу написать одну, чтобы превратить эту формулу в функцию ROUNDEVEN, которая будет доступна для всех рабочих книг. Буду очень признателен, если кто-нибудь сможет показать мне нужную формулировку.

Вот еще один

Майкл (Микки) Авидан

@James,

Только если я вас правильно понял - попробуйте:

= ОКРУГЛ (A1 + 0,25,0) + (ОКРУГЛ (A1 + 0,25,0)
-------------- ------------

Майкл (Микки) Авидан

«Ответы Microsoft®» - Автор Wiki и модератор форумов

«Microsoft®» Excel MVP - Excel (2009-2018)

ИЗРАИЛЬ

Здравствуйте,

мне нужно округлить до ближайшего 0,25 с минимальным значением 0,25.

Другими словами, мне нужно округлять в большую или меньшую сторону на 1/4, но никогда до 0,00.

Имеет ли это смысл?

Спасибо.

Майкл (Микки) Авидан

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

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

Существует множество очень конкретных сайтов вопросов и ответов по Excel, к которым пользователи могут обратиться, если им потребуется более глубокая помощь.

_____

Кстати, чтобы предоставить небольшую справочную информацию (которую следует воспринимать как информацию, а не как хвастовство), я инженер-механик, у которого также есть степени в области электротехники и математики. Я программировал почти 40 лет, начиная с Basic и Fortran, а затем с языков CAD, ориентированных на c и lisp. Я самоучка в Excel и активно использую его в течение 20 лет. Как и все остальное, если вы занимаетесь чем-то достаточно долго, вы, как правило, становитесь действительно хороши в этом. Я считаю Excel прекрасным инструментом. Хотел бы я иметь его в 1980-х, когда я писал программы на Фортране на 10 000 строк. Это сэкономило бы мне человеко-годы времени и дало бы гораздо более удобные для пользователя результаты.

Майкл (Микки) Авидан

@Dave,

это был отличный пример, почему я - на ведущем израильском форуме Excel, которым я управляю последние 10 лет - всегда настаиваю, чтобы OP прикрепил свою рабочую книгу со ВСЕМИ (!) Примерами возможных исходных данных и вручную набрал ожидаемые результаты .

(Как всегда, конфиденциальные данные можно изменить / заменить).

Если г-н Вятт не согласится разрешить прямые вложения - каждый автор вопроса должен загрузить свой WB на сайт хостинга файлов и предоставить ссылку для загрузки.

--------------------------

Майкл (Микки) Авидан

«Ответы Microsoft®» - Автор Wiki и модератор форумов

«Microsoft®» MVP - Excel ( 2009-2016)

ИЗРАИЛЬ

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

Джон Смит имеет в виду то, что, как мне кажется, иногда называют «военным обходом». В более общем смысле цель состоит в том, чтобы последняя отображаемая цифра была четным значением, если есть только одна отброшенная цифра, и она точно равна 5.

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

В ответ только на исходный комментарий Джона Смита - На мой взгляд, логика, лежащая в основе этого, уместна только в тех случаях, когда в наборе чисел возможны только 2 или 4 [или 8.] десятичных варианта, например: a ) x.0, x.5; или б) y.0, y.25, y.5, y.75; . Предполагая, что это правда, и предполагая, что существует равная возможность для любого десятичного числа иметь место для любого конкретного числа, тогда вы бы изменили больше чисел в наборе на большее значение, чем на меньшее значение; так что лучше всего подойдет нечетная / четная техника.

Однако, если есть другие возможные десятичные значения (например, 10 или 100), метод станет спорным, поскольку существует равное количество шансов округлить в большую или меньшую сторону.

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

Статья побудила меня искать решение проблемы Excel с помощью Excel. Нир Либерман дал прекрасный ответ, еще раз спасибо за это.

Вильгельм Оккама здесь добился бы успеха: я спросил, Нир Либерман ответил, проблема была решена. Я доволен.

Спасибо тем, кто добавил свои комментарии, особенно DaveS.

@ Стюарт Боровски + Джон Смит

Я должен согласиться с Майклом (Микки) Авиданом в том, что первоначальный пост был не очень ясным.

В своем комментарии я использовал пример из сообщения, а не вопрос как ссылку на свой ответ.

Майкл (Микки) Авидан

@Stewart Borowski,

в двух словах: «БЕЗ КОММЕНТАРИИ».

--------------------------

Майкл (Микки) Авидан

«Ответы Microsoft®» - Автор Wiki и модератор форумов

«Microsoft®» MVP - Excel ( 2009-2016)

ИЗРАИЛЬ

@ Майкл (Микки) Авидан,

Уточните, с чем конкретно вы не согласны?

Джон Смит заявил, что вы неправильно поняли его просьбу. Учитывая, что он является автором этого запроса, его прерогатива определять, кто понял или не понял то, о чем он просил. Вы не можете «не согласиться» с такой точкой зрения.

Я МОГУ согласиться с тем, что было неясно, что его первоначальное утверждение / запрос относилось ТОЛЬКО к значениям 0,5 и не имел отношения к каким-либо другим фракциям (0,2, 0,3, 0,6, 0,8 и т. Д.), Однако этот конкретный аспект был прояснен в последующих действиях. комментарий (который, кстати, был опубликован за 5 часов до того, как вы «не согласились»?).

Вы также комментируете, и я цитирую (пожалуйста, никаких ссылок на начало! :-): «Предложение (и я цитирую):». до ближайшего целого числа, будь то (!) нечетное (!) или четное (!) "".

Я не могу найти комментариев, где Джон Смит написал то, что вы цитировали. Его ссылки на четные и нечетные находятся в отдельных постах. Я также предполагаю, что! после каждого слова - ваша собственная интонация ?.

Я тоже никогда до сих пор не слышал о концепции округления точных половинок до ближайшего четного числа. Но это не делает его автоматически недействительным ни как нечто, чему «однажды научили», либо как что-то еще действительное. Лично это имеет смысл при использовании в больших наборах данных, так как предотвращает завышение веса значения 0,5 в сторону более высоких чисел с течением времени / объемов.

Как автор форума MVP и ответов и модера, я уверен, что вы хотели бы помочь внести ясность и понимание по таким темам?

Майкл (Микки) Авидан

@John Smith,

Исправление небольшого TIPO:

я хотел спросить: «Где вы упомянули:». до ближайшего ЧЕТНОГО ИЛИ (!) НЕЧЕТНОГО (!) числа »?

--------------------------

Майкл (Микки) Авидан

« Ответы Microsoft® »- Автор Wiki и модератор форумов

« Microsoft® »MVP - Excel (2009-2016)

ИЗРАИЛЬ

Майкл (Микки) Авидан

@ Джон Смит,

мне очень жаль, но я категорически не согласен с вашим утверждением относительно моего "недоразумения".

Предложение (и я цитирую): «. Каждое до ближайшего целого числа, будь (!) Нечетным (!) Или четным (!)» - строго «новое» (для меня).

Не могли бы вы проверить (еще раз) свой исходный вопрос, чтобы сообщить мне / нам, где именно (!) Вы упомянули слово «ИЛИ» (имеющее отношение к части «нечетное ИЛИ четное» в вашем текущем намерении / исправление)?

--------------------------

Майкл (Микки) Авидан

«Ответы Microsoft®» - Автор Wiki и модератор форумов

«Microsoft®» MVP - Excel ( 2009-2016)

ИЗРАИЛЬ

Вы также можете использовать MROUND. Синтаксис:

где «num» - это число, которое нужно округлить, а «multiple» - ближайшее кратное. Итак, чтобы округлить 437 до ближайших 25, вы должны использовать

что дает результат 425.

Мне очень жаль, что вы неправильно поняли мою просьбу. То, что вы цитируете, верно, но это только часть проблемы. Приведенные вами иллюстрации не имеют отношения к значениям 0,5, предмету моего запроса.

Я спросил конкретно о значениях 0,5. Я ожидаю, что значения до 0,49 округляются в меньшую сторону и до 0,51 и более для округления в большую сторону, каждое до ближайшего целого числа, будь то четное или нечетное.

Метод Excel всегда округлять 0,5 UP в длинных рядах приведет к смещению. Условие, которому меня учили, обеспечивает баланс. (Было бы так же эффективно, если бы все значения 0,5 округлялись до ближайшего нечетного числа, баланс является важным битом.)

Решение Нира Либермана помогает решить мой вопрос.

@Michael (Micky) Avidan

Если продолжить цитату из вопроса Джона Смита: «… по соглашению 0,5 округлялось в большую или меньшую сторону до ближайшего ЧЕТНОГО числа». Таким образом, он ссылался не на «все числа», а только на конкретный случай дроби 0,5 и по указанной причине.

Майкл (Микки) Авидан

Джон Смит,

Если да, то вы должны срочно перефразировать свой вопрос.

Вы упомянули и подчеркнули (заглавными буквами), что - и я цитирую: «... округлять в большую или меньшую сторону до ближайшего ЧЕТНОГО числа».

Предложение г-на Либермана НЕ округляет все числа до БЛИЖАЙШЕГО ЧЕТНОГО числа.

Ближайшее ЧЕТНОЕ число для: 8,7 - 8, а не 9.

То же самое и для: 11.4, которое должно читаться: 12, а не 11.

И последнее, но не менее важное: 1234567.2 должно давать 1234568 (ЧЕТНОЕ), а не 1234567 (НЕЧЕТНОЕ).

-------------------------

Майкл (Микки) Авидан

«Ответы Microsoft®» - автор вики и модератор форумов

«Microsoft®», MVP - Excel (2009 г.) -2016)

ИЗРАИЛЬ

Спасибо вам обоим за ваши предложения. Тот, который у меня сразу сработал, - от nir liberman.

Я превращу это в макрос с кнопкой быстрого доступа.

Майкл (Микки) Авидан

@John Smith,

Если я правильно вас понял, попробуйте предложенную мной UDF (функцию, определяемую пользователем).

-----------------------------------

Функция Round_2_Even (Num As Double)

Round_2_Even = CLng (Num * 0.5) / 0.5

Конечная функция

http://screenpresso.com/=yBatd

-----------------------

Майкл (Микки) Авидан

«Ответы Microsoft®» - автор Wiki и модератор форумов

» Microsoft® »MVP - Excel (2009-2016)

ИЗРАИЛЬ

@John Smith

Вы можете добавить / вычесть небольшое число:

добавьте к 10000 столько нулей, сколько вам нужно.

Я узнал в школе (в 50-х, но никому не говорю), что по соглашению 0,5 округлялось в большую или меньшую сторону до ближайшего ЧЕТНОГО числа, поэтому 6,5 округлялось до 6, а 7,5 округлялось до 8.

Таким образом, большое количество округлений уравновесит корректировки.

Формула функции для этого?

Этот сайт

У вас есть версия Excel, в которой используется ленточный интерфейс (Excel 2007 или новее)? Этот сайт для Вас!Если вы используете более раннюю версию Excel, посетите наш сайт ExcelTips,посвященный интерфейсу меню.

Денис Парфенов Автор статей

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