Точность экрана Excel

Обновлено: 07.07.2024

Когда я учился в первом классе, я узнал, что 2 + 2 = 4. Excel, с которым я знаком и которому доверяю, считает, что ответ равен пяти. Как это возможно? Правду говорят после того, как я увеличиваю десятичные знаки, показанные для каждой из этих ячеек. Двойки в таблице на самом деле не двойки. Это 2,43 и 2,35, которые округляются до двух. Сумма 4,78 округляется до пяти. Чтобы исправить эту разницу в округлении в Excel 2010, щелкните вкладку «Файл» и перейдите в «Параметры». В левой части появившегося окна нажмите «Дополнительно». Прокрутите вниз и установите флажок «Установить отображаемую точность». Появится окно с предупреждением о том, что «данные навсегда потеряют точность». Например, когда я уменьшаю десятичное число, чтобы все числа отображались как целые числа, они на самом деле становятся целыми числами (еще раз увеличьте десятичное число, и обе двойки будут отображаться как 2,00). Не выбирайте этот параметр, если вам нужна полная точность данных в электронной таблице. Я не могу нести ответственность за несколько потерянных копеек. Эта опция особенно удобна для бухгалтера, чей отчет всегда должен быть в ногах. Спасибо Вэлу Стеду из K2 Enterprises за то, что он показал это на конференции AICPA TECH+ 2011 по информационным технологиям.

Комментарии (17)

Джоэл Унгар

Хороший совет, Дастин. Этот параметр также доступен в Excel 2007.

Я столкнулся с этой проблемой давным-давно. Мое решение, особенно при составлении финансового отчета, связанного с базой данных пробного баланса, всегда заключалось в использовании формулы @round. Я также научился вести свои пробные балансы в целых долларах!

Дастин Уиллер

Спасибо, Джоэл. Как и вы, я достаточно хорошо разобрался с формулой округления.

Ваш ответ — это именно то решение, которое я искал в Интернете повсюду. Однако, поскольку я использую «Числа» от Apple, а также Microsoft Excel для Mac (2008 г.), проблема остается. На вкладке «Файл» в версии для Mac нет «Параметры», и я не могу найти флажок «Установить отображаемую точность», чтобы установить флажок.

Было бы очень полезно, если бы вы расширили свой ответ, чтобы охватить компьютеры Mac.

к.э. Макгимпси

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

Установка точности как отображаемой обычно ДЕЙСТВИТЕЛЬНО плохая идея, поскольку XL фактически изменит введенные вами значения, если вы не настроите свою книгу очень тщательно, вы никогда не узнаете, где была потеряна точность. Обычно гораздо лучше использовать функцию ОКРУГЛ(), например: =ОКРУГЛ(A1,2) + ОКРУГЛ(A2, 2), которая, по крайней мере, намекает, где могут закрасться потенциальные неточности. Ваш гипотетический бухгалтер никогда не сможет чтобы узнать, почему он один, если базовые данные безвозвратно утеряны.

На вопрос Ника: «Настройки/Расчеты» установите флажок «Точность отображения».

Как установить это в Excel 2007?

В 2007 году я перехожу к параметрам – расширенным, но тогда нет флажка для установки точности.

Пожалуйста, помогите… мой бухгалтер теряет терпение. Так раздражает, что самая известная в мире расчетная таблица не работает…..

Дастин Уиллер, бухгалтер

Джон, я не знаю, почему вы его не видите, но в Excel 2007 он есть. Посмотрите в разделе "При расчете этой книги".

Джеффри А. Далтон, MBA

Обычно я использую =Trunc(Round(a2,2),2). Таким образом, я точно знаю, какие поля я хочу «обрезать» с точностью, отображаемой на экране, без ущерба для данных где-либо еще в таблице или на другом вкладки. Это было серьезной проблемой для государственных контрактов, где должны были совпадать сотни страниц и использовалось множество расчетов в процентах. Таким образом, каждый расчет в отчетах совпадал, сохраняя точность до тех пор, пока не были рассчитаны элементы в каждом отчете. Я думаю, что я старая школа. У меня было слишком много проблем с глобальными параметрами, я предпочитаю контроль.

Дастин Уиллер, бухгалтер

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

Андреа

Джеральдин

Можете ли вы установить это для отдельного листа, а не для рабочей книги, я не хочу использовать Trunc или Round

Каэль Монтас

Вы сделали мой день. Как раз то решение, которое я ищу.

Каэль Монтас

Как преобразовать PDF-файлы в Excel, Word и т. д.

Пол Д. CPA

Я конвертирую PDF-файлы в Excel и Word, но вам нужна полная версия Adobe, а не только программа для чтения. Даже с этой программой у меня были переменные успехи. Иногда я получаю хорошие файлы, иногда я получаю выходные данные со столбцами разной ширины, числами в виде текста с начальными пробелами и анализом различных данных, которые могут быть неприятными для рекомбинации.Со смесями =Left, =Mid и =Right вместе с командой =Trim я добивался успеха в большом проценте случаев. Но не всегда. Если вы знаете кого-то, у кого есть полная версия Adobe, попросите их попробовать конвертировать, особенно если это разовое мероприятие. Кроме того, полная версия очень полезна по многим причинам. Я добавляю и удаляю страницы, объединяю PDF-файлы в один файл, добавляю текст в PDF-файл, который не является заполняемым документом. Удачи.

Мисс. Холм

Я не вижу заданной точности, отображаемой в 2013 году.

Танука

Блэр ЛиттлДжон

У меня есть рабочий лист Excel с множеством разбросанных по нему ячеек, отформатированных в процентах — 2 знака после запятой, а процент вычисляется по формуле. У моего друга такой же рабочий лист с точно такими же данными. Иногда, но не всегда, при суммировании столбца процентов я получаю ответ, отличающийся от его на +/-.01. Мне интересно, настроен ли его процентный формат Excel на округление в большую сторону, а мой на округление в меньшую сторону. Я не могу найти способ установить округление Excel по умолчанию, если он есть. Кто-нибудь может мне помочь?

Унаджет

20.10.2020
Итак, после попытки выяснить, почему мои центы добавлялись неправильно, я увидел это и решил рискнуть исправить.
Это сработало.
Итак, «Файл», «Параметры», «Дополнительно», при расчете этой книги установите флажок «Установить точность как показано».
Теперь мой .02 + .02 = .03 больше не существует. Да.
Кроме того, решите, каким образом будут отображаться ваши ячейки — числовым, валютным или бухгалтерским. Оставьте их прежними.

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

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

При первом вводе числа Excel отображает именно то, что вы ввели (до пятнадцати цифр), в строке формул в верхней части экрана. То, что отображается в ячейке, определяется форматированием, которое вы применили к ячейке. Если задан формат «Общий», Excel отображает только одиннадцать цифр и считает десятичную точку одной из этих цифр. Таким образом, то, что вы видите на дисплее, будет 4,760546329. Обратите внимание, что это округленное значение, которое соответствует тому, как Excel отображает более короткие числа.

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

ExcelTips – это экономичное обучение работе с Microsoft Excel. Этот совет (1982 г.) относится к Microsoft Excel 97, 2000, 2002 и 2003.

Биография автора

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

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

Особые различия шрифтов

Замечали ли вы разницу в макете страницы при открытии документа в разных системах? На то есть ряд причин .

Использование полей слияния

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

Добавление подписей

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

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

Дополнительные советы Excel (меню)

Избавление от электронной таблицы запуска

При запуске Excel обычно отображается пустой рабочий лист. Вот как от этого избавиться.

Точность в Excel

Лучший способ понять, почему Excel предоставляет именно те ответы, — это изучить, как он обрабатывает точность. Этот совет.

Изменение цвета линий сетки рабочего листа

Хотите, чтобы линии сетки на листе были другого цвета? Вы не ограничены скучным черным цветом; Excel позволяет создавать .

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

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

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

На самом деле существует скрытая настройка, которая заставит Excel вычислять ячейки с такой же точностью, как и отображаемый формат. Чтобы включить/отключить этот параметр, выберите «Файл» > «Параметры» > «Дополнительно» > «При расчете этой книги»: > «Установить отображаемую точность».

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

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

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

Исходные значения — для столбца "Стоимость/галлон" установлено форматирование с тремя десятичными знаками

td>

Галлоны	Стоимость/галлон	Промежуточный итог
7	2,999 доллара	20,99 доллара
5	3,759 доллара	18,80 доллара
8	3,699$	29,59$
Всего	69,38$

«Установить точность, как показано» включено — форматирование установлено на 2 десятичных знака для столбца «Стоимость / галлон»

td>

Галлоны	Стоимость/галлон	Промежуточный итог
7	3,00$	21,00$
5	3,76$	18,80$
8	3,70$	29,60$
Всего	69,40 долларов США

Как видите, общая сумма округлена до 2 центов. Если вы добавите десятичные разряды обратно к стоимости/галлону, это округление не изменится.

Мне любопытно узнать, сколько других людей столкнулись с этой проблемой. Вы использовали эту настройку?

Чтобы увидеть краткий обзор 7 способов округления в Excel, посмотрите это короткое слайд-шоу. Под слайд-шоу есть подробная информация и пошаговые инструкции.

Кроме того, получите пример рабочей тетради "7 способов округления", чтобы вы могли следить за слайд-шоу. Заархивированный файл имеет формат xlsx и не содержит макросов.

Скругление с форматированием

При форматировании число может округляться на листе, но это не влияет на значение ячейки. На снимке экрана ниже сумма кажется неверной, поскольку 2+3 не равно 4.

Однако, если щелкнуть любую из ячеек, можно увидеть ее фактическое значение в строке формул. Например, цена кофе в ячейке B3 составляет 1,54 доллара США.

Числа в ячейках B3:B5 были отформатированы как денежный формат с использованием раскрывающегося списка Числовой формат на вкладке "Главная" ленты.

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

Если числовой формат валюты применяется повторно, он показывает форматирование по умолчанию с двумя десятичными знаками, поэтому общая сумма выглядит правильно: 1,54 + 2,54 = 4,08

Когда числа были округлены с форматированием, каждая сумма отображалась как ближайшая сумма в долларах:

цена кофе округлена до 2 долларов США.
сэндвич округлили до 3 долларов США.
общая сумма округлена до 4 долларов США.

Автоматическое округление длинных чисел

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

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

Максимум 11 символов

В моих тестах в ячейке отображалось не более 11 символов (включая десятичный разряд), даже если столбец достаточно широк, чтобы отображать больше чисел.

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

Слишком узкий столбец

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

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

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

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

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

Например, покажите 123456,78 в одном из следующих форматов.

Применить пользовательский формат

Чтобы применить пользовательский формат к ячейкам:

На вкладке "Главная" ленты нажмите кнопку запуска диалогового окна в правом нижнем углу группы "Число".

На вкладке "Число" в списке "Категории" нажмите "Пользовательские".

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

Затем нажмите кнопку ОК, чтобы закрыть диалоговое окно и применить формат.

Вот еще несколько настраиваемых числовых форматов, которые вы можете использовать:

Вы также можете использовать настраиваемый числовой формат для выравнивания чисел с разными десятичными разрядами. Знак вопроса (?) — это заполнитель, который добавляет дополнительный интервал, поэтому числа выстраиваются в ряд по десятичной точке.

Есть другие примеры пользовательских форматов на веб-сайте Microsoft: Создание пользовательского числового формата

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

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

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

Пример налога с продаж

На приведенном ниже снимке экрана налог с продаж (HST) округляется до ближайшего цента с использованием 2 в качестве количества знаков после запятой для округления.

Пример возраста по десятилетиям

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

Общая сумма с небольшой разницей

На листе, показанном ниже, есть небольшая разница между суммой в столбце "Итого" и столбце "Получено".

В столбце C налог рассчитывается путем умножения цены продажи на TaxRate (ячейка F1)
- =[@Cost]*TaxRate
- Введенная сумма всегда проверяется, чтобы убедиться, что она соответствует общей сумме к оплате.
Почему итоговые суммы отличаются, если суммы тщательно вводятся и проверяются на точность?
- В столбце F формула находит разницу между общей и полученной суммами.
- Несмотря на то, что на листе они выглядят одинаково, в большинстве строк есть небольшая разница.
- Это вызвано скрытыми десятичными знаками в столбце "Налоги".
Исправить различия в сумме с помощью функции ОКРУГЛ

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

Поскольку налог округлен до двух знаков после запятой, он точно соответствует сумме, указанной в столбце «Получено».
- В каждой строке расчет разницы показывает нулевой результат
- В строке "Итого" суммы в двух столбцах абсолютно одинаковы.
Функция ОКРУГЛВНИЗ

Функция Excel ROUNDDOWN округляет число в меньшую сторону до определенного количества знаков после запятой. Например:

На приведенном ниже снимке экрана год публикации округлен до ближайшего десятилетия с использованием -1 в качестве количества цифр в функции ОКРУГЛВНИЗ. Поскольку число отрицательное, оно округляется слева от десятичного знака.

=ОКРУГЛВНИЗ(B4,-1)

Функция ОКРУГЛВВЕРХ

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

На приведенном ниже снимке экрана цена округляется до ближайшего десятицентовика с использованием 1 в качестве количества цифр в функции ОКРУГЛВВЕРХ.

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

Функция ОКРУГЛ округляет число до ближайшего указанного кратного. Например:

На приведенном ниже снимке экрана общая сумма продаж округлена до никеля с использованием множителя 5 в функции ОКРУГЛ. Вместо жесткого кодирования множителя в формуле он вводится в ячейку B9, где его можно легко изменить.

Функция ЭТАЖ

Функция Excel FLOOR округляет числа в меньшую сторону до нуля на основе указанного вами кратного значения значимости.

ЭТАЖ(число,значительность)

На приведенном ниже снимке экрана год округлен до ближайшего десятилетия с использованием 10 в качестве значимости в функции FLOOR.

Функция FLOOR.PRECISE или FLOOR.MATH

В Excel 2010 Microsoft рекомендует использовать функцию FLOOR.PRECISE вместо FLOOR.

Для Excel 2013 используйте FLOOR.MATH вместо FLOOR.

И FLOOR.PRECISE, и FLOOR.MATH возвращают число, округленное в меньшую сторону до ближайшего целого числа или до ближайшего кратного значения. Независимо от знака числа оно округляется в меньшую сторону.

FLOOR.PRECISE(число,значительность)

FLOOR.MATH(число;значимость)

Три функции сравниваются в следующей таблице. Результаты FLOOR.PRECISE и FLOOR.MATH одинаковы, но FLOOR отличается: он округляется до нуля и возвращает ошибку, если число положительное, а значение отрицательное.

Функция ПОТОЛОК

Функция ПОТОЛКА в Excel округляет числа в большую сторону до нуля на основе указанного вами кратного значения значимости. Например:

На приведенном ниже снимке экрана цена округлена до ближайшего квартала с использованием множителя 0,25 в функции ПОТОЛОК.

=ПОТОЛОК(B4,0,25)

Сумма округленных чисел

Если в столбце есть десятичные числа, вы можете использовать функцию ОКРУГЛ, чтобы округлить каждое число по отдельности, ПЕРЕД подсчетом суммы. Это может дать другой результат, чем округление чисел ПОСЛЕ их суммирования. .

В этом примере в ячейках A1:A5 есть десятичные числа

1,5 2.9 3.8 9.2 3.5

В ячейке A6 формула округляет итог до 0 знаков после запятой ПОСЛЕ их суммирования. Возвращает результат 21,0

=ОКРУГЛ(СУММ(A1:A5), 0)

Формула в ячейке A7 округляет каждое число ПЕРЕД их суммированием. Возвращает результат 22,0

Читайте также: