Точность как на экране excel
Перейти к содержимому

Точность как на экране excel

  • автор:

Точность как на экране excel

Итак, давайте сделаем простой расчет. Поделим 1 на 3.

Наложим на ячейку формат числовой с двумя знаками после запятой.

А рядом просто введем 0,33. И помножим оба числа на 10000.

Как видите разница на 33. Можете представить что это руб. или $. Как ошибочка? Когда будете выставлять счет клиенту, вспомните про это. Он может и пересчитать. Естественно, что эта разница будет накапливаться по мере увеличения количества расчетов в Вашей таблице. Но такого быть не должно. В данном случае Excel считает по научному. То есть привлекает большое количество знаков после запятой. В реальной жизни расчеты обычные и мы ограничиваемся двумя знаками после запятой. Решается все просто. Нужно установить точность как на экране. И тогда таблицу можно пересчитывать и все совпадет. Нам нужно пойти в сервис параметры на вкладку вычисления. В параметрах книги установить точность как на экране.

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

И после подтверждения нажав на ОК произойдет пересчет.

Задание точности округления

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

Примечание: Использование параметра «Точность как отображаемое » может привести к совокупному эффекту вычислений, что может сделать данные все более неточными с течением времени. Используйте этот параметр, только если вы уверены, что отображаемая точность будет поддерживать точность данных.

  1. Выберите Файл >Параметры. В Excel 2007 нажмите кнопку Microsoft Office

рядом с полем Число.

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

Арифметические операции с плавающей запятой могут давать неточный результат в Excel

В этой статье рассматривается, как Microsoft Excel хранит и вычисляет числа с плавающей запятой. Это может повлиять на результаты некоторых чисел или формул из-за округления или усечения данных.

Обзор

Microsoft Excel был разработан на основе спецификации IEEE 754 для определения способа хранения и вычисления чисел с плавающей запятой. IEEE — институт инженеров электротехники и электроники, международный орган, который, помимо прочего, определяет стандарты для компьютерного программного и аппаратного обеспечения. Спецификация 754 — это широко используемая спецификация, которая описывает, как числа с плавающей запятой должны храниться в компьютере, использующем двоичную систему счисления. Она популярна по той причине, что позволяет хранить числа с плавающей запятой в разумных объемах и выполнять вычисления относительно быстро. Стандарт 754 используется в модулях операций с плавающей запятой и арифметических сопроцессорах почти всех современных микропроцессоров на базе ПК, реализующих вычисления с плавающей запятой, включая процессоры Intel, Motorola, Sun и MIPS.

При хранении чисел соответствующее двоичное число может представлять каждое число или дробное число. Например, дробь 1/10 может быть представлена в десятичной системе счисления как 0,1. Однако то же самое число в двоичном формате становится следующим повторяющимся двоично-десятичным числом:

0001100110011100110011 (и т. д.)

Это число можно бесконечно повторять. Это число не может быть представлено в конечном (ограниченном) пространстве. Поэтому при хранении это число округляется примерно на -2.8E-17 в меньшую сторону.

Однако существуют некоторые ограничения спецификации IEEE 754, которые делятся на три общие категории:

  • Максимальные/минимальные ограничения
  • Точность
  • Повторяющиеся двоичные числа

Дополнительная информация

Максимальные/минимальные ограничения

Все компьютеры имеют максимальное и минимальное количество, которое может быть обработано. Поскольку количество битов памяти, в которых хранится число, является конечным, из этого следует, что максимальное или минимальное число, которое может храниться, также является конечным. Максимальное число, которое может храниться в Excel, — 1.79769313486232E+308, а минимальное положительное число — 2.2250738585072E-308.

Случаи, в которых мы придерживаемся стандарта IEEE 754
  • Потеря точности. Потеря точности возникает в том случае, если генерируется число, которое слишком мало для представления. В IEEE и Excel результат равен 0 (за исключением того, что в IEEE есть понятие -0, а в Excel — нет).
  • Переполнение. Переполнение возникает в том случае, если число слишком велико для представления. Excel использует свое специальное представление для этого случая (#NUM!).
Случаи, в которых мы не придерживаемся стандарта IEEE 754
  • Ненормализованные числа. Ненормализованное число содержит 0 в экспоненте. В этом случае все число хранится в мантиссе, которая не имеет неявной ведущей единицы. В результате теряется точность, и чем меньше число, тем больше теряется точность. Числа на малом конце этого диапазона имеют только одну цифру точности. Пример: нормализованное число имеет неявную ведущую единицу. Например, если мантисса представляет собой 0011001, нормализованное число становится 10011001 из-за неявной ведущей единицы. Ненормализованное число не имеет неявной ведущей единицы, поэтому в нашем примере ненормализованное число 0011001 не изменяется. В данном случае нормализованное число имеет восемь значащих цифр (10011001), а ненормализованное — пять значащих цифр (11001), причем ведущие нули являются несущественными. Ненормализованные числа — это, по сути, обходной путь, позволяющий хранить числа, которые меньше обычного нижнего предела. Корпорация Майкрософт не реализует эту необязательную часть спецификации, поскольку ненормализованные числа по своей природе имеют переменное количество значащих цифр. Это может привести к существенной ошибке в расчетах.
  • Положительная/отрицательная бесконечность: бесконечность возникает при делении на 0. Excel не поддерживает бесконечность и выдает ошибку #DIV/0! в таких случаях.
  • Не число (NaN): используется для представления недопустимых операций (таких как бесконечность/бесконечность, бесконечность-бесконечность или квадратный корень из -1). NaNs позволяют программе продолжать работу с недопустимой операцией. Вместо этого Excel немедленно выдает ошибку типа #NUM! или #DIV/0!.

Точность

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

Знак Экспонента Мантисса
1 бит знака 11-битная экспонента 1 неявный бит + 52-битная дробь

Знак хранит знак числа (положительный или отрицательный), экспонента хранит степень 2, в которую возводится или на которую понижается число (максимальная/минимальная степень 2 равна +1.023 и -1.022), а мантисса хранит действительное число. Конечная область хранения мантиссы ограничивает то, насколько близкими могут быть два соседних числа с плавающей запятой (то есть точность).

Мантисса и экспонента хранятся как отдельные компоненты. В результате возможная точность зависит от размера числа (мантиссы), над которым выполняется операция. Хотя Excel может хранить числа от 1.79769313486232E308 до 2.2250738585072E-308, это возможно только в пределах 15 разрядов. Это ограничение является прямым результатом строгого следования спецификации IEEE 754 и не является ограничением Excel. Такой уровень точности встречается и в других программах электронных таблиц.

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

X = дробь * 2^(экспонента — смещение)

Дробь — это нормализованная дробная часть числа, нормализованная по той причине, что экспонента корректируется таким образом, чтобы ведущий бит всегда был равен 1. Таким образом, ее не нужно хранить, и вы получаете еще один бит точности. Вот почему существует неявный бит. Это похоже на экспоненциальное представление, где вы управляете экспонентой, чтобы слева от десятичной запятой располагалась одна цифра; за исключением двоичной системы счисления, вы всегда можете управлять экспонентой так, чтобы первый бит был 1, потому что имеются только 1 и 0.

Смещение — это значение смещения, используемое для предотвращения хранения отрицательных экспонент. Смещение для чисел одинарной точности равно 127 и 1,023 (десятичное) для чисел двойной точности. Excel хранит числа с двойной точностью.

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

Введите следующие данные в новую рабочую книгу:

A1: 1.2E+200 B1: 1E+100 C1: =A1+B1 

Результирующее значение в ячейке C1 будет 1.2E+200, то же значение, что и в ячейке A1. На самом деле, если сравнить ячейки A1 и C1 с помощью функции IF, например IF(A1=C1), результатом будет TRUE. Это вызвано тем, что спецификация IEEE предусматривает хранение только 15 значащих цифр точности. Для хранения приведенного выше расчета Excel потребуется точность не менее 100 разрядов.

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

Введите следующие данные в новую рабочую книгу:

A1: 0.000123456789012345 B1: 1 C1: =A1+B1 

В результате значение в ячейке C1 будет равно 1.00012345678901 вместо 1.000123456789012345. Это вызвано тем, что спецификация IEEE предусматривает хранение только 15 значащих цифр точности. Для хранения приведенного выше расчета Excel потребуется точность не менее 19 разрядов.

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

Excel предлагает два основных метода компенсации ошибок округления: функцию ROUND и параметр в рабочей книге Точность как на экране или Задать точность как на экране.

Метод 1. Функция ROUND

Используя предыдущие данные, в следующем примере функция ROUND используется для приведения числа к пяти цифрам. Это позволяет успешно сравнить результат с другим значением.

A1: 1.2E+200 B1: 1E+100 C1: =ROUND(A1+B1,5) 

В результате получается 1.2E+200.

D1: =IF(C1=1.2E+200, TRUE, FALSE)

В результате получается значение TRUE.

Метод 2. Точность как на экране

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

  1. В меню Файл нажмите Параметры, затем выберите категорию Дополнительно.
  2. В разделе При пересчете этой книги выберите необходимую книгу, а затем установите флажок Задать точность как на экране.

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

Повторение двоичных чисел и вычислений с результатами, близкими к нулю

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

000110011001100110011 (и т. д.)

Спецификация IEEE 754 не допускает особых ограничений для любого значения. Все, что можно сохранить, хранится в мантиссе, а остальное удаляется. Неточность в таком случае случае составляет около -2.8E-17 или 0,000000000000000028 при хранении.

Даже обычные десятичные дроби, например 0,0001, невозможно точно представить в двоичном формате. (0,0001 — это повторяющаяся двоичная дробь с периодом в 104 бита). Этот явление похоже на невозможность точно представить дробь 1/3 в десятичной системе (повторяющаяся дробь 0.33333333333333333333).

Например, рассмотрим следующий простой пример в Microsoft Visual Basic для приложений:

 Sub Main() MySum = 0 For I% = 1 To 10000 MySum = MySum + 0.0001 Next I% Debug.Print MySum End Sub 

В таком случае в качестве выходных данных будет выведено значение 0,999999999999996. Небольшая неточность в представлении 0,0001 в двоичном формате распространяется на сумму.

Пример: добавление отрицательного числа

  1. Введите следующие данные в новую рабочую книгу: A1: =(43,1-43,2)+1
  2. Щелкните правой кнопкой мыши ячейку A1 и выберите пункт Формат ячеек. На вкладке «Числовые форматы» выберите «Инженерный» в разделе «Категория». Установите значение Знаки после запятой на 15.

Вместо значения 0,9 Excel отображает 0,899999999999999. Так как сначала вычисляется (43,1-43,2), значение -0.1 временно сохраняется и к расчету добавляется неточность, возникшая при хранении -0.1.

Пример, когда значение достигает нуля

  1. В Excel 95 или более ранней версии введите в новую книгу следующее: A1: =1,333+1,225-1,333-1,225
  2. Щелкните правой кнопкой мыши ячейку A1 и выберите пункт Формат ячеек. На вкладке «Числовые форматы» выберите «Инженерный» в разделе «Категория». Установите значение Знаки после запятой на 15.

Вместо 0 Excel 95 отображает -2,22044604925031E-16.

В Excel 97 добавлена оптимизация для устранения этой проблемы. Если в результате операции добавления или вычитания получается значение равно или близко к нулю, Excel 97 и более поздние версии компенсируют неточность, полученную в результате конвертации операнда в двоичную систему и обратно. При выполнении примера, указанного выше, в Excel 97 и более поздней версии в экспоненциальном представлении отображается правильное значение 0 или 0.000000000000000E+00.

Дополнительные сведения о числах с плавающей точкой и спецификации IEEE 754 см. на следующих веб-сайтах:

  • https://www.ieee.org
  • https://steve.hollasch.net/cgindex/coding/ieeefloat.html

Обратная связь

Были ли сведения на этой странице полезными?

Точность как на экране excel

Выпускники группы МЦ-18 (мастера по обработке цифровой информации) представили свои заключительные выступления государственной итоговой аттестационной комиссии под председательством руководителя фирмы «ЧАСТ» О.Л. Чашникова.

IMG 0094

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

— В чём надёжность и стабильность? – задал Ивану Фролову вопрос, касающийся работы сети, председатель экзаменационной комиссии. — Это – не совсем синоним…

Анастасия Меньшикова обозначила целью своей работы создание базы данных учёта товаров в мебельном цехе.

Владислав Динкелакер отчитался по разработке презентации «Великие Победы великой страны».

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

Алексей Дойва презентовал сообщение на тему графического редактора Photoshop, рассказав о создании коллажа для одногруппников. Задав вопросы по растровой и векторной графике, О.Л. Чашников посоветовал более детально углубиться в эту тему и другие, связанные с направлением защиты.

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

— Иногда у бухгалтеров, считающих налоги, «не идут» цифры. А «фишка» Excel – это точность, как на экране, — конечно, эрудита и знатока О.Л. Чашникова можно слушать часами…

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

Очень чётко, уверенно, со знанием нюансов прошла по своей теме и Мария Фоменко. «Видно, что сама много трудилась», — удовлетворённо кивнули экзаменаторы.

…Перед оглашением оценок председатель комиссии О.Л. Чашников по традиции напутствовал:

— Сегодня требуется постоянное повышение квалификации, совершенствование своих знаний. Поэтому приготовьтесь к систематическим экзаменам в вашей жизни. На защите самое главное, когда раздаётся дополнительный вопрос – не молчать! Многие из вас выстроили грамотную, бойкую защиту. Всё это – генеральная репетиция перед следующими испытаниями. Тем, кто уверенно оперирует знаниями, приятно задавать вопросы, и получать стабильные ответы…

Защиты дипломных работ у выпускников набора-2017-2018 завершены. Сегодня первыми свидетельства об окончании профессионального учебного заведения начинают получать учившиеся на отделении подготовки квалифицированных рабочих, служащих. Завтра и в последующие дни состоится вручение дипломов остальным выпускникам.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *