20. Округление данных в Excel

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

Пример точных значений в Excel

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

Округление с помощью функции форматирования ячеек Excel

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

Пример использования функции ROUND в Excel

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

Пример округления think-cell

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

20.1
Использование округления think-cell
20.2
Ограничения округления think-cell
20.3
Устранение неполадок с формулами TCROUND

20.1 Использование округления think-cell

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

Лента округления think-cell в Excel 2010 и более поздних версиях

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

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

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

Как и в Excel, округление think-cell позволяет округлять значения не только до целых чисел, но и до любых кратных чисел. Например, если вы хотите представить данные в последовательности 5-10-15..., просто округляйте значения до чисел, кратных пяти.

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

В следующей таблице представлен ряд примеров округления: с использованием панели инструментов, ROUND или MROUND вместе с соответствующим параметром n.

Панель инструментов 100 50 2 1 0.01
ROUND -2 - - 0 2
MROUND 100 50 2 1 -
1.018 0 0 2 1 1.02
17 0 0 18 17 17.00
54.6 100 50 55 54 54.60
1234.1234 1200 1250 1234 1234 1234.12
8776.54321 8800 8800 8776 8777 8776.54

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

Кнопка Формула Описание
изображение TCROUND(x, n) Позволить think-cell определять ближайшее кратное число для округления, чтобы минимизировать ошибку округления.
изображение TCROUNDUP(x, n) Принудительное округление x от нуля.
изображение TCROUNDDOWN(x, n) Принудительное округление x к нулю.
изображение TCROUNDNEAR(x, n) Принудительное округление x к ближайшему кратному числу с требуемой точностью.
изображение Удалить все функции округления think-cell из выбранных ячеек.
изображение
Выберите или введите требуемое кратное число для округления.
изображение Выделить все ячейки, которые надстройка think-cell решила округлить до наиболее отдаленного из двух кратных чисел вместо наиболее близкого числа.
изображение Вращающееся колесико означает, что функция округления think-cell применяется.

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

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

20.1.2 Макет вычисления

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

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

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

Неправильно: =TCROUND(A1, 1)+TCROUND( SUM(B1:E1), 1 )
Правильно: =TCROUND( A1+SUM(B1:E1), 1 )
 
Неправильно: =3*TCROUNDDOWN(A1, 1)
Правильно: =TCROUNDDOWN(3*A1, 1)

Если вы введете что-то в строках неправильных примеров, think-cell уведомит вас о значении ошибки Excel #VALUE!.

20.2 Ограничения округления think-cell

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

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

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

Умножение на константу при округлении в think-cell

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

Пример округления с использованием функции округления think-cell (TCROUND)

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

Несогласованное округление с использованием функции округления think-cell

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

Ошибка #NUM! в округлении think-cell

Примечание. Функция AVERAGE Excel интерпретируется округлением think-cell как комбинация суммирования и умножения на константу. Кроме того, сумма, в которой одно слагаемое используется несколько раз, математически эквивалентно умножению на константу, и существование решения не гарантируется.

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

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

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

Во многих случаях результаты округления think-cell по-прежнему будут рациональными. Однако в некоторых случаях использование операторов, отличных от +, -, SUM и AVERAGE, приводит к получению округленных результатов, которые существенно отличаются от вычисления без округления. Рассмотрим следующий пример:

Последствия округления из-за неправильного использования формулы

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

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

Округление и умножение с использованием функции округления think-cell

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

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

При использовании округления think-cell вы можете столкнуться с двумя ошибками: #VALUE! и #NUM!.

20.3.1 #VALUE!

Ошибка #VALUE! указывает на синтаксические проблемы, такие как неправильно введенные формулы или недопустимые параметры. Например, второй параметр TCROUND должен быть целым числом. Кроме того, уделите внимание правильному использованию разделителей. Например, в международной версии Excel формула выглядит так: =TCROUND(1.7, 0), а в немецкой версии Excel ее следует записать следующим образом: =TCROUND(1,7; 0)

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

20.3.2 #NUM!

Ошибка #NUM! возникает из-за числовых проблем. Если результат функции TCROUND равен #NUM!, это значит, что задача, определенная данным набором формул, не имеет математического решения. (См. раздел Ограничения округления think-cell )

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

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

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

  • Используйте более высокую точность для некоторых или всех операторов TCROUND.
  • Не используйте TCROUND с умножением или числовыми функциями, отличными от +, - и SUM.
  • Используйте одинаковую точность (второй параметр) для всех операторов TCROUND.
  • Используйте TCROUND вместо функций TCROUNDDOWN, TCROUNDUP и TCROUNDNEAR, где это возможно.

Еще не пользователь?

think-cell использует файлы cookie для улучшения функциональности, эффективности и безопасности этого веб-сайта. Если вы хотите пользоваться всеми функциями этого сайта, требуется ваше согласие. С более подробной информацией об использовании файлов cookie компанией think-cell, вашем согласии и правах на конфиденциальность данных можно ознакомиться в нашей Политике конфиденциальности.