Почему Excel неправильно округляет?

Обновлено: 07.07.2024

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

Если вы хотите автоматически округлить, вы можете сначала установить количество знаков после запятой, которое будет округлено в ячейке, и введенное значение будет автоматически округлено. Если вы хотите сохранить только десятичные дроби и не округлять, вы можете использовать функцию Trunc и функцию RoundDown. Если вы хотите округлить до указанного кратного, вы можете использовать функцию MRound. Ниже приведены 13 примеров округления Excel, не только включающие указанные выше проблемы, но и сумма сумм, разница 0,1, сумма процентов не равна 100%, а сумма после округления зарплат больше или меньше несколько центов.

I, способ 1 округления чисел в Excel: использование функции «Число»

<р>1. Округлить до двух знаков после запятой с помощью функции "Уменьшить десятичный знак"

Выберите ячейку, которую вы хотите округлить до двух знаков после запятой (например, A2:A4), текущая вкладка — «Главная», щелкните значок «Уменьшить десятичный знак» в группе «Число», выбранные значения будут уменьшается на один десятичный знак и округляется; шаги рабочего процесса, как показано на рисунке 1:

<р>2. Округлите до двух знаков после запятой с помощью «Числового формата»

Выберите две ячейки A2: A4, текущая вкладка «Главная», щелкните раскрывающийся список «Числовой формат» (в нем есть «Общие») над «Число» и выберите «Число» во всплывающих параметрах», затем числа A2:A4 округляются до двух знаков после запятой; этапы операции показаны на рисунке 2:

<р>3. Округлите значение до двух знаков после запятой с помощью «Числа»

Аналогичным образом выделите три ячейки A2:A4, нажмите Ctrl + 1, откройте диалоговое окно «Формат ячеек», выберите вкладку «Число», выберите слева «Число», значение «Десятичные разряды» равно 2, нажмите «ОК», число в выбранных ячейках округляется до двух знаков после запятой; этапы операции показаны на рисунке 3:

II, второй способ округления чисел в Excel: используйте функцию округления

(I) Округление до одного десятичного знака

<р>1. Если обороты десятичные, они округляются до одного десятичного знака; если они целые, возвращаются сами. Дважды щелкните ячейку B2, скопируйте формулу = ОКРУГЛ (A2,1) в B2 и нажмите клавишу ВВОД, чтобы вернуть 68905,9; выберите A2, щелкните маркер заполнения ячейки в правом нижнем углу A2 и верните оставшийся оборот, округленный до одного десятичного знака; этапы операции показаны на рисунке 4:

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

<р>2. Если вы хотите округлить десятичные дроби в исходной ячейке, вы можете скопировать округленные значения обратно в исходные ячейки; метод: выберите B2:B6, нажмите Ctrl + C, чтобы скопировать, выберите A2 и нажмите «Вставить» в левом верхнем углу экрана, выберите «Значение» в разделе «Вставить значения» во всплывающем окне, и все результаты, подлежащие округлению, будут скопированы обратно в исходные ячейки; этапы операции показаны на рисунке 5:

(II) Округление до одного десятичного знака после усреднения

<р>1. Если вы хотите получить среднее значение по «продвинутой математике и английскому языку», то их результаты округляются до одного десятичного знака. Дважды щелкните ячейку D2, скопируйте формулу = ОКРУГЛ (СРЗНАЧ (B2: B18), 1) в D2 и нажмите клавишу ВВОД, чтобы найти их средние значения и вернуть результат с одним десятичным знаком; этапы операции показаны на рисунке 6:

<р>2. Описание формулы:

AVERAGE(B2:B18) используется для нахождения среднего значения всех оценок в B2:B18 и возвращает результат функции округления, а затем округляет результат до одного десятичного знака с помощью функции округления.

III, Расширенное приложение округления чисел в Excel

(I) Округляется автоматически

Выберите ячейки для автоматического округления (например, нажмите A1, чтобы выбрать их, затем нажмите A7, выберите A1: A7), выберите вкладку «Главная», щелкните раскрывающийся список в группе «Число», и выберите «Число» из всплывающих опций, введите 2.345 в A1, нажмите Enter, чтобы автоматически округлить до двух знаков после запятой; затем введите 5,534 в A2, нажмите Enter для автоматического округления до двух знаков после запятой; этапы операции показаны на рисунке 7:

(II) Без раунда

<р>1. Если вы хотите сохранить два десятичных знака, но не округлить. Дважды щелкните ячейку B1, скопируйте формулу = ОКРУГЛВНИЗ (A1,2) в B1 и нажмите Enter, верните результат 2,34, значение которого в ячейке A1 округлено до двух знаков после запятой; выберите B1, переместите мышь к маркеру заполнения в правом нижнем углу B1, после того, как мышь изменится на жирный черный знак плюса, удерживайте левую кнопку и перетащите ее вниз к B2, значение в ячейке A2 округлите до двух знаков после запятой. . Дважды щелкните ячейку C1, скопируйте формулу =TRUNC(A1,2) в C1, нажмите Enter, чтобы вернуть тот же результат, что и в B1, перетащите вниз в C2 и верните тот же результат, что и в B2. Этапы операции показаны на рисунке 8:

<р>2. Описание:

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

(III) Число больше или равно 3, переводит 1 на число перед ним

<р>1. Дважды щелкните ячейку B1, скопируйте формулу =ОКРУГЛ(A1+0,001,2) в ячейку B1 и нажмите клавишу ВВОД, чтобы получить значение 2,64; вернуть 5,54 с перетаскиванием вниз; этапы операции показаны на рисунке 9:

<р>2. Описание:

Число больше или равно 3, переводит 1 в число, прежде чем оно станет таким же, как число округляется. Мы можем знать, что перед округлением мы добавляем 1 к числу за числами, которые нужно округлить, до числа десятичных знаков, а затем округляем до указанного десятичного знака из демонстрации. Например, сначала используется 2,643 + 0,001, а результат равен 2,663, а затем в демонстрации он округляется.

(IV) Число больше или равно 6, переводит 1 на число перед ним

<р>1. Дважды щелкните ячейку B1 и скопируйте формулу = ОКРУГЛ (A1-0,001,2) в ячейку B1, нажмите Enter, чтобы получить значение 2,64. Перетащите вниз к B2 и вернитесь к 5.54. Этапы операции показаны на рисунке 10:

<р>2. Описание:

Число больше или равно 6, переводит 1 в число, прежде чем оно станет таким же, как число округляется. Перед округлением числа за числами, подлежащими округлению, количество знаков после запятой следует уменьшить на единицу, а затем округлить до указанного знака после запятой.

(V) Excel округляет до указанного1 кратного

<р>1. Если вы хотите округлить два десятичных знака до 0,5 раза. Дважды щелкните ячейку B2, скопируйте формулу = ОКРУГЛ(A1,0,5) в B2, нажмите Enter и верните результат 2,5, который 2,64 округлен до 0,5 раза, и перетащите вниз до B2 и вернитесь к 6; шаги рабочего процесса, как показано на рисунке 11:

<р>2. Описание:

Функция MRound используется для округления значения до указанного кратного числа. Его выражение = ОКРУГЛ(Число,Множественное), "Число" - это значение, которое нужно округлить, "Множественное" - это кратное число, до которого нужно округлить; если остаток, на который «Число» делится по основанию, больше или равен половине основания, число округляется от нуля функцией MRound; например, округлить 5,8 в A3 до 0,5 раза, остаток от деления 5,8 на 0,5 равен 3, а 3 больше 0,5, поэтому 5,8 округляется от нуля, результат равен 6.

IV, Ошибка округления Excel(Как исправить ошибки округления в Excel)

(I) Разница 0,1 после округления

<р>1. Дважды щелкните ячейку D2 и скопируйте формулу =СУММ(ОКРУГЛ(A2,1),ОКРУГЛ(B2,1),ОКРУГЛ(C2,1)) в ячейку D2 и нажмите клавишу ВВОД, чтобы вернуть сумму трех сумм после округления 896,6. ; Дважды щелкните D3 и скопируйте формулу = ОКРУГЛ (СУММ (A2: C2), 1) в D3, нажмите Enter, чтобы вернуть результат округления после суммирования трех сумм. 896,7; шаги операции; как показано на рисунке 12:

<р>2. Описание:

Из демонстрации видно, что если три суммы сначала округлить, а затем просуммировать, разница будет равна 0,1, потому что число после округляемых чисел меньше 5 знаков после запятой и отбрасывается; и после того, как сумма будет округлена, сначала добавляются три суммы, числа за числами, подлежащими округлению, добавляются к количеству десятичных знаков трех сумм, они составляют от менее 5 до 5 или более, а затем округляются до один, так что это будет 0,1 больше. Например, 0,02 + 0,03 + 0,04 > 0,05 в демоверсии.

(II) Добавляемые проценты не равны 100 % после округления до

<р>1. Дважды щелкните ячейку C6, скопируйте формулу =СУММ(C2:C5), скопируйте C6 и нажмите Enter, чтобы вернуть результат суммы 100%; переместите мышь к маркеру заполнения в правом нижнем углу C6, и после того, как мышь станет жирным черным знаком плюс, удерживайте левую кнопку и перетащите вправо, чтобы вернуть результат суммы 100,01%; дважды щелкните C2, значение внутри вычисляется по формуле, а затем дважды щелкните D2, введено значение внутри, только 20.2%; выберите C2, нажмите Ctrl + C, чтобы скопировать, затем выберите C7, нажмите «Вставить» в левом верхнем углу экрана и выберите «Значение» в разделе «Вставить значения» во всплывающем окне, затем вставьте значение в C2 в C7, результатом является одна длинная строка чисел; шаги операции, как показано на рисунке 13:

<р>2. Описание:

А. Отображаемые проценты являются одним и тем же значением, но результаты суммирования отличаются. Процент, рассчитанный по формуле, равен ровно 100%, а процент, который необходимо ввести, равен 100,01%, что на 0,01% больше. Почему это происходит?

Б. Скопируйте значение из C2 в C7. Видно, что процент, полученный по формуле, показывает результат после округления. После округления до двух знаков после запятой последующие цифры на самом деле не отбрасываются, а скрываются, а вводимые проценты имеют округление только до двух знаков после запятой. Для первого добавляются все десятичные знаки, а для второго добавляются только два десятичных знака. Чем больше знаков после запятой, тем точнее сумма. Таким образом, сумма первого составляет ровно 100%, а второго — на 0,01% больше. Иногда сумма последних меньше 100% (такое бывает, если последняя цифра запятой меньше 5, что уже введено выше).

(III) Сумма каждой строки после округления больше или меньше нескольких центов, в результате чего общий результат слишком сильно отличается от фактического значения

<р>1. Дважды щелкните ячейку B2, скопируйте формулу =ROUND(A2,0) в B2 и нажмите Enter, чтобы вернуть результат 2565, до которого округляется зарплата в ячейке A2. Выберите B2 и перетащите вниз, чтобы вернуть 2484; дважды щелкните B4, скопируйте формулу =СУММ(B2:B3) в B4, нажмите Enter, чтобы вернуть результат суммы после округления заработной платы;

<р>2. Дважды щелкните C2 и скопируйте формулу =A2-B2 в C2, нажмите Enter, чтобы вернуться к окладу из оклада в A2. Перетащите вниз, чтобы вернуться к -0,43; дважды щелкните D4, скопируйте формулу =СУММ(B2:B3,C2:C3) в D4, нажмите Enter, чтобы вернуть промежуточные результаты округленных и отброшенных зарплат; этапы операции показаны на рис. 14:

<р>3. Описание:

А. Отбросить зарплаты с отрицательными знаками, обозначающими зарплату к выплате, без отрицательных знаков, обозначающих вычитаемую зарплату.

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

Иногда мы получаем письма от наших клиентов, утверждающих, что обнаружили ошибку вычисления в Excel, хотя на самом деле вычисление правильное, но побочные эффекты двоичной точности с плавающей запятой делают это так. Сегодняшний автор: Джессика Лю, руководитель программы в команде Excel, обсуждает, как Excel выполняет вычисления, объясняет, почему иногда вы видите неожиданные ответы, и дает несколько советов, как избежать проблем с округлением.

Взгляните на следующую таблицу:

Я хочу иметь возможность быстро определять случаи, когда абсолютная разница больше или равна 0,005. Поэтому я применяю правило условного форматирования к столбцу абсолютная разница, чтобы форматировать значения, больше или равные 0,005, чтобы они были красными. Просматривая таблицу, я замечаю, что значение 0,005 не выделено. Я проверяю свое правило условного форматирования и формулу, которую я использовал для вычисления абсолютной разницы (=ABS(A2-B2)), они кажутся правильными. Затем я увеличиваю точность столбца абсолютная разница, чтобы получить более точные результаты. Я обнаружил, что мои результаты изменились. Почему 1,3240 – 1,3190 = 0,0049999999999999?

Майкрософт Эксель

Превратите данные в идеи.

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

Обзор

Excel был разработан в соответствии со стандартом IEEE для двоичных арифметических операций с плавающей запятой (IEEE 754). Стандарт определяет, как хранятся и вычисляются числа с плавающей запятой. Стандарт IEEE 754 широко используется, поскольку он позволяет хранить числа с плавающей запятой в разумном объеме, а вычисления могут выполняться относительно быстро.

Преимущество представления с плавающей запятой по сравнению с представлением с фиксированной точкой заключается в том, что оно может поддерживать более широкий диапазон значений. Например, представление с фиксированной запятой, которое имеет 5 десятичных цифр с десятичной точкой, расположенной после третьей цифры, может представлять числа 123,34, 12,23, 2.45 и т. д., тогда как представление с плавающей запятой с точностью до 5 цифр может представлять 1,2345, 12345, 0,00012345 и т. д. Аналогично, представление с плавающей запятой также позволяет выполнять вычисления в широком диапазоне величин при сохранении точности. Например,

Все числа, представленные в формате с плавающей запятой, являются рациональными числами. Иррациональные числа, такие как Ï€ или , или неограниченные рациональные числа должны быть аппроксимированы. Количество разрядов точности также ограничивает точность чисел. Excel хранит 15 значащих цифр точности. Например, число 1234567890123456 не может быть точно представлено, если используется 15-значная точность.

Стандарт IEEE 754 для операций с плавающей запятой требует, чтобы числа хранились в двоичном формате. Это означает, что преобразование должно произойти, прежде чем числа можно будет использовать в вычислениях. Если число может быть точно представлено в формате с плавающей запятой, то преобразование будет точным. Если нет, то преобразование приведет к округленному значению, которое будет представлять исходное значение. Числа, которые кажутся точными в десятичном формате, могут нуждаться в аппроксимации при преобразовании в двоичные числа с плавающей запятой. Например, дробь 1/10 может быть представлена в десятичном формате как рациональное число 0,1. Однако 0,1 не может быть точно представлено в двоичном формате с плавающей запятой конечной точности. 0,1 становится повторяющимся двоичным десятичным числом 0,0001100110011…, где последовательность 1100 повторяется бесконечно. Это число не может быть представлено в конечном пространстве. Таким образом, в Excel при сохранении оно округляется в меньшую сторону примерно на 2,8E-17.

Структура числа с плавающей запятой

Число с плавающей запятой хранится в двоичном формате и состоит из трех частей в пределах 65-битного диапазона: знак, показатель степени и мантисса.

Бит знака

Знак хранит знак числа (положительный или отрицательный). 0 – положительное число, 1 – отрицательное.

Экспонента

Показатель степени хранит степень двойки, до которой число увеличивается или уменьшается. Поле экспоненты должно иметь возможность представлять как положительные, так и отрицательные показатели. Чтобы не хранить отрицательные показатели, к фактическому показателю степени добавляется значение bias. Смещение для чисел с двойной точностью равно 1023. Например, сохраненное значение 1000 указывает на показатель степени от 1000 до 1023 или -23.

Мантисса

Мантисса хранит фактическое число. Он состоит из подразумеваемого ведущего бита и дробных битов. Размер хранилища мантиссы определяет, насколько близко могут быть два соседних числа с плавающей запятой. Мантисса и экспонента хранятся в отдельных компонентах. Точность числа зависит от размера мантиссы. Excel может хранить числа от 1,79769313486232E308 до 2,2250738585072E-308; однако он может сделать это с точностью до 15 знаков.

Распространенные примеры ошибок из-за вычисления с плавающей запятой

Пример 1. Потеря точности при использовании очень больших чисел

Результирующее значение в A3 — 1,2E+100, то же значение, что и в A1. Это связано с тем, что Excel хранит 15 цифр точности. Для расчета приведенной выше формулы требуется точность не менее 100 разрядов.

Пример 2. Потеря точности при использовании очень маленьких чисел

Результирующее значение в ячейке A1 равно 1,00012345678901 вместо 1,000123456789012345. Это еще раз потому, что Excel хранит 15 цифр точности. Для вычисления приведенной выше формулы требуется не менее 19 цифр точности.

Пример 3. Повторяющиеся двоичные числа

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

может оцениваться как количество (-2.78E-17) или -0,00000000000000000278 вместо 0. Это связано с тем, что стандарт IEEE 754 требует, чтобы числа хранились в двоичном формате. Как я уже говорил ранее, не все десятичные числа можно точно преобразовать в двоичные, как в случае с 0,1. Преобразование привело к потере точности.

Исправление ошибок точности

Вернемся к моему самому первому примеру, где условное форматирование, похоже, не сработало. Теперь я знаю, что это произошло из-за того, что числа, которые я использовал для вычисления абсолютной разницы, не имели точных двоичных эквивалентов. В результате получилось 1,3240 – 1,3190 = 0,00499999999999999.

Есть два основных способа компенсировать некоторые ошибки, связанные с вычислениями с плавающей запятой. Первый способ заключается в использовании функции ОКРУГЛ(). Функцию ОКРУГЛ() можно использовать для округления чисел до количества знаков после запятой, которое требуется в ваших вычислениях. Для моего столбца абсолютная разница мне требуется только 4 десятичных знака точности. Поэтому я изменил формулу в столбце абсолютная разница с:

Мое правило условного форматирования теперь работает должным образом, так как 0,0049999999999999 округлено до 0,0050.

Второй способ предотвратить влияние ошибок округления на вашу работу – использовать параметр "Точность, как отображается". Этот параметр заставляет отображать значение каждого числа на листе. Чтобы включить этот параметр, выполните следующие действия:

Нажмите кнопку Microsoft Office -> Параметры Excel -> Дополнительно.
В разделе «При расчете этой книги» выберите нужную книгу, а затем установите флажок «Установить точность как отображаемую».
Нажмите "ОК".

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

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

При компиляции данных для отчета или презентации PowerPoint частая проблема заключается в округлении сумм в Excel. Часто желательно, но трудно добиться, чтобы округленные итоги точно совпадали с итогом округленных слагаемых. Например, рассмотрим следующую таблицу:

При округлении значений до целых чисел с помощью функции Excel Format-Cell получается следующая таблица. Итоги, которые кажутся «неверно рассчитанными», выделены жирным шрифтом:

Аналогично, когда используются стандартные функции округления Excel, итоговые значения округленных значений рассчитываются правильно, но накапливаются ошибки округления, и результаты часто существенно отклоняются от фактических итогов исходных значений. В следующей таблице показан результат =ROUND(x,0) для приведенного выше примера. Итоги, отличающиеся от исходного значения на 1 или более, выделены жирным шрифтом:

Используя think-cell round, вы можете последовательно округлять итоги с минимальным «мошенничеством»: хотя большинство значений округляются до ближайшего целого числа, некоторые значения округляются в противоположном направлении, что позволяет поддерживать правильные расчеты и не накапливать ошибку округления. Поскольку существует много возможностей добиться правильного округления итоговых значений путем изменения значений, программное обеспечение выбирает решение, требующее минимального количества измененных значений и минимального отклонения от точных значений. Например, округление в меньшую сторону от 10,5 до 10 предпочтительнее, чем округление в меньшую сторону от 3,7 до 3. В следующей таблице показано оптимальное решение для приведенного выше примера, где «обманные» значения выделены жирным шрифтом:

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

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

22.1 Использование think-cell round

Округление think-cell легко интегрируется в Microsoft Excel, предоставляя набор функций, аналогичных стандартным функциям округления Excel. Вы можете легко применить эти функции к своим собственным данным, используя круглую группу ленты think-cell на вкладке "Формулы".

22.1.1 Параметры округления

Как и функции Excel, функции округления think-cell принимают два параметра:

x Значение, которое необходимо округлить. Это может быть константа, формула или ссылка на другую ячейку. n Точность округления. Значение этого параметра зависит от используемой вами функции. Параметры для функций think-cell такие же, как и для эквивалентных функций Excel. Примеры приведены в таблице ниже.

think-cell round может округлять не только до целых значений, но и до любых кратных. Например, если вы хотите представить свои данные в формате 5-10-15-. шагов, просто округляем до числа, кратного пяти. В раскрывающемся списке на панели инструментов округления think-cell просто введите или выберите нужную точность округления. think-cell round выбирает для вас подходящие функции и параметры. В следующей таблице приведены некоторые примеры округления определенных значений x с помощью панели инструментов вместе с их конкретным параметром n.

Если значения отображаются не так, как вы ожидаете, убедитесь, что для параметра Форматирование ячеек Excel задано значение «Общее», а ширина столбцов достаточна для отображения всех десятичных знаков.

Для достижения оптимальных результатов с минимальным отклонением от исходных значений следует использовать TCROUND везде, где это возможно. Используйте более строгие функции TCROUNDDOWN , TCROUNDUP или TCROUNDNEAR только в случае необходимости.

Внимание: никогда не следует использовать недетерминированные функции, такие как RAND(), ни в одной из формул TCROUND. Если функции возвращают разные значения каждый раз, когда они оцениваются, цикл think-cell будет допускать ошибки при вычислении значений.

22.1.2 Схема расчета

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

22.1.3 Размещение функций TCROUND

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

22.2 Ограничения раунда think-cell

think-cell round всегда находит решение для произвольного суммирования с промежуточными и итоговыми суммами. think-cell round также предоставляет разумные решения для некоторых других вычислений, связанных с умножением и числовыми функциями. Однако по математическим причинам невозможно гарантировать наличие последовательно округленного решения, если используются операторы, отличные от +, - и СУММ.

22.2.1 Умножение на константу

Во многих случаях округление think-cell дает хорошие результаты, когда используется постоянное умножение, т. е. максимум один из коэффициентов получается из результата другой функции TCROUND. Рассмотрим следующий пример:

Точный расчет для ячейки C1: 3×1,3+1,4=5,3. Этот результат можно получить, округлив значение от 1,4 до 2:

Точный расчет для ячейки C1: 6×1,3+1,4=9,2. Округление ячеек A1 и B1 даст 6×1+2=8 или 6×2+1=13. Фактический результат 9,2 нельзя округлить до 8 или 13, и результат округления think-cell выглядит следующим образом:

Примечание. Функция Excel СРЗНАЧ интерпретируется think-cell как комбинация суммирования и постоянного умножения. Кроме того, суммирование, в котором одно и то же слагаемое появляется более одного раза, математически эквивалентно постоянному умножению, и существование решения не гарантируется.

22.2.2 Общее умножение и другие функции

Поскольку функции TCROUND используются для всех соответствующих ячеек, а промежуточные результаты связаны просто +, -, SUM и AVERAGE , сложения, а также (промежуточные) итоги объединяются в единую задачу округления. В этих случаях цикл think-cell найдет решение, обеспечивающее согласованность во всех задействованных ячейках, если такое решение существует.

Поскольку TCROUND — это обычная функция Excel, ее можно комбинировать с произвольными функциями и операторами. Но когда вы используете функции, отличные от упомянутых выше, для соединения результатов операторов TCROUND, раунд think-cell не может интегрировать компоненты в одну взаимосвязанную задачу. Вместо этого компоненты формулы будут рассматриваться как отдельные задачи, которые будут решаться независимо друг от друга. Затем результаты будут использоваться в качестве входных данных для других формул.

Во многих случаях результаты раунда think-cell все же будут разумными. Однако бывают случаи, когда использование операторов, отличных от +, -, СУММ и СРЗНАЧ, приводит к округленным результатам, которые далеки от результата неокругленного вычисления. Рассмотрим следующий пример:

В этом случае точное вычисление для ячейки C1 будет 8,6 × 1,7 = 14,62. Поскольку ячейки A1 и ячейки B1 связаны умножением, think-cell round не может интегрировать формулы из этих ячеек в общую задачу. Вместо этого, после обнаружения ячейки A1 как действительного ввода, ячейка B1 оценивается независимо, а вывод принимается как константа в оставшейся задаче. Поскольку дополнительных ограничений нет, значение 1,7 из ячейки B1 округляется до ближайшего целого числа, равного 2.

На данный момент «точный» расчет для ячейки C1 равен 8,6 × 2 = 17,2. Это проблема, которую сейчас пытается решить think-cell round. Существует последовательное решение, которое требует округления 17,2 до 18. Результат выглядит следующим образом:

Обратите внимание, что округленное значение в ячейке C1, равное 18, сильно отличается от исходного значения 14,62.

22.3 Устранение неполадок с формулами TCROUND

Еще одна ошибка, характерная для цикла think-cell, — это размещение вызова функции TCROUND: функцию TCROUND нельзя использовать в другой формуле. Убедитесь, что TCROUND является самой внешней функцией формулы ячейки. (см. Размещение функций TCROUND)

Если формулы, включенные в функции TCROUND, содержат только +, - и SUM , а все операторы TCROUND имеют одинаковую точность (второй параметр), решение гарантированно существует и будет найдено циклом think-cell. Однако в следующих случаях нет гарантии, что решение с постоянным округлением существует:

Формулы включают другие операции, такие как умножение или числовые функции. Кроме того, суммирование, в котором одно и то же слагаемое встречается более одного раза, математически эквивалентно умножению.
Вы используете разные точности во втором параметре функции TCROUND.
Вы часто используете определенные функции TCROUNDDOWN , TCROUNDUP и TCROUNDNEAR .

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

Примечание. Другие языки веб-сайта переводятся Google. Назад на английский

Как избежать ошибок округления при расчетах в Excel?

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

Плохо:	= TCROUND (A1, 1)+ TCROUND ( SUM (B1:E1), 1 )
Хорошо:	= TCROUND ( A1+ SUM (B1:E1), 1 )
Плохо:	= 3* TCROUNDDOWN (A1, 1)
Хорошо:	= TCROUNDDOWN (3*A1, 1)