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

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

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

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

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

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

22.1

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

22.2

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

22.3

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

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

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

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

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

x

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

n

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

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

x = n =	100	50	2	1	0,01
1,018	0	0	2	1	1,02
17	0	0	18	17	17,00
54,6	100	50	54	55	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 будет совершать ошибки при вычислении значений.

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

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

22.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!.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

22.3.1 #VALUE!

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

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

22.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, где это возможно.

Еще не пользуетесь нашим продуктом?