Задать точность как на экране в excel

Microsoft Excel

трюки • приёмы • решения

Как в Excel решить проблему чисел с плавающей запятой

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

Некоторые числа не могут быть выражены именно с помощью 8 байт, поэтому хранятся в виде приближений. Чтобы продемонстрировать, как это ограничение может вызывать проблемы, введите следующую формулу в ячейку А1: =(5,1-5,2)+1 .

Результат должен быть равен 0,9. Однако если вы отформатируете ячейку так, чтобы она отображала 15 знаков после запятой, то обнаружите, что программа вычисляет формулу с результатом 0,899999999999999 — значением, очень близким к 0,9, но никак не 0,9. Такой результат получается, поскольку операция в круглых скобках выполняется первой, и этот промежуточный результат сохраняется в двоичном формате с использованием приближения. Затем формула добавляет 1 к этому значению, а погрешность приближения распространяется на конечный результат.

Во многих случаях ошибки такого рода не представляют проблемы. Однако если вам необходимо проверить результат формулы с помощью логического оператора, это может стать головной болью. Например, следующая формула (которая предполагает, что предыдущая формула находится в ячейке А1) возвращает ЛОЖЬ: =A1-0,9 .

Одно из решений этого типа ошибки заключается в использовании функции ОКРУГЛ. Следующая формула, например, возвращает ИСТИНА, поскольку сравнение выполняется с использованием значения в А1, округленного до одного знака после запятой: =ОКРУГЛ(А1;1)=0.9 . Вот еще один пример проблемы точности. Попробуйте ввести следующую формулу: =(1,333-1,233)-(1,334-1,234) . Эта формула должна возвращать 0, по вместо этого возвращает -2,22045Е-16 (число, очень близкое к нулю).

Если бы эта формула была в ячейке А1, то формула вернула бы Not Zero: =ЕСЛИ(А1=0;”Zero”;”Not Zero”) . Один способ справиться с такой ошибкой округления состоит в использовании формулы вроде этой: =ЕСЛИ(ABS(А1) . В данной формуле используется оператор «меньше чем» для сравнения абсолютного значения числа с очень малым числом. Эта формула будет возвращать Zero.

Другой вариант — указать Excel изменить значения листа в соответствии с их отображаемым форматом. Для этого откройте окно Параметры Excel (выберите Файл ► Параметры) и перейдите в раздел Дополнительно. Установите флажок Задать точность как на экране (который находится в области При пересчете этой книги).

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

Источник: excelexpert.ru

Точность округления как на экране в Microsoft Excel

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

Настройка округления как на экране

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

Включать точность как на экране, нужно в ситуациях следующего плана. Например, у вас стоит задача сложить два числа 4,41 и 4,34, но обязательным условиям является то, чтобы на листе отображался только один десятичный знак после запятой. После того, как мы произвели соответствующее форматирование ячеек, на листе стали отображаться значения 4,4 и 4,3, но при их сложении программа выводит в качестве результата в ячейку не число 4,7, а значение 4,8.

Это как раз связано с тем, что реально для расчета Эксель продолжает брать числа 4,41 и 4,34. После проведения вычисления получается результат 4,75. Но, так как мы задали в форматировании отображение чисел только с одним десятичным знаком, то производится округление и в ячейку выводится число 4,8. Поэтому создается видимость того, что программа допустила ошибку (хотя это и не так). Но на распечатанном листе такое выражение 4,4+4,3=8,8 будет ошибкой. Поэтому в данном случае вполне рациональным выходом будет включить настройку точности как на экране. Тогда Эксель будет производить расчет не учитывая те числа, которые программа держит в памяти, а согласно отображаемым в ячейке значениям.

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

Включение настройки точности как на экране в современных версиях Excel

Теперь давайте выясним, как включить точность как на экране. Сначала рассмотрим, как это сделать на примере программы Microsoft Excel 2010 и ее более поздних версий. У них этот компонент включается одинаково. А потом узнаем, как запустить точность как на экране в Excel 2007 и в Excel 2003.

    Перемещаемся во вкладку «Файл».

Запускается дополнительное окно параметров. Перемещаемся в нем в раздел «Дополнительно», наименование которого значится в перечне в левой части окна.

После того, как перешли в раздел «Дополнительно» перемещаемся в правую часть окна, в которой расположены различные настройки программы. Находим блок настроек «При пересчете этой книги». Устанавливаем галочку около параметра «Задать точность как на экране».

  • После этого появляется диалоговое окно, в котором говорится, что точность вычислений будет понижена. Жмем на кнопку «OK».
  • После этого в программе Excel 2010 и выше будет включен режим «точность как на экране».

    Для отключения данного режима нужно снять галочку в окне параметров около настройки «Задать точность как на экране», потом щелкнуть по кнопке «OK» внизу окна.

    Читайте также:  Гпр в excel примеры

    Включение настройки точности как на экране в Excel 2007 и Excel 2003

    Теперь давайте вкратце рассмотрим, как включается режим точности как на экране в Excel 2007 и в Excel 2003. Данные версии хотя и считаются уже устаревшими, но, тем не менее, их используют относительно немало пользователей.

    Прежде всего, рассмотрим, как включить режим в Excel 2007.

    1. Жмем на символ Microsoft Office в левом верхнем углу окна. В появившемся списке выбираем пункт «Параметры Excel».
    2. В открывшемся окне выбираем пункт «Дополнительно». В правой части окна в группе настроек «При пересчете этой книги» устанавливаем галочку около параметра «Задать точность как на экране».

    Режим точности как на экране будет включен.

    В версии Excel 2003 процедура включения нужного нам режима отличается ещё больше.

    1. В горизонтальном меню кликаем по пункту «Сервис». В открывшемся списке выбираем позицию «Параметры».
    2. Запускается окно параметров. В нем переходим во вкладку «Вычисления». Далее устанавливаем галочку около пункта «Точность как на экране» и жмем на кнопку «OK» внизу окна.

    Как видим, установить режим точности как на экране в Excel довольно несложно вне зависимости от версии программы. Главное определить, стоит ли в конкретном случае запускать данный режим или все-таки нет.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Источник: lumpics.ru

    Точность как на экране в Excel: как задать

    Довольно часто пользователи, производящие расчеты в программе Excel, не догадываются о том, что отображающиеся числовые значения в ячейках не всегда сходятся с теми данными, которые программа использует для осуществления расчетов. Речь идёт о дробных величинах. Дело в том, что программа Эксель хранит в памяти числовые значения, содержащие до 15 цифр после запятой. И несмотря на то, что на экране будет отображаться, скажем, всего 1, 2 или 3 цифры (в результате настроек формата ячеек), для расчетов Эксель будет задействовать именно полное число из памяти. Порой это приводит к неожиданному исходу и результатам. Чтобы такого не происходило, нужно настроить точность округления, а именно, установить её такой же как на экране.

    Как работает округление в Excel

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

    Устанавливать точность как на экране стоит в следующих случаях. Например, мы хотим сложить числа 6,42 и 6,33, но нам нужно отображение лишь одного десятичного знака, а не двух.

    Для этого, выделяем нужные ячейки, щелкаем по ним правой кнопкой мыши, выбираем пункт “Формат ячеек..”.

    Находясь во вкладке “Число” кликаем в перечне слева на формат “Числовой”, далее устанавливаем значение “1” для количества десятичных знаков и нажимаем OK, для выхода из окна форматирования и сохранения настроек.

    После произведённых действий в книге отобразятся значения 6,4 и 6,3. И если данные дробные числа сложить, программа выдаст сумму 12,8.

    Может показаться, что программа работает неправильно и ошиблась в расчетах, ведь 6,4+6,3=12,7. Но давайте разбираться, так ли это на самом деле, и почему получился именно такой результат.

    Как мы уже упомянули выше, Эксель берёт для расчетов исходные числа, т.е. 6,42 и 6,33. В процессе их суммирования получается результат 6,75. Но по причине того, что перед этим в настройках форматирования был указан один знак после запятой, в итоговой ячейке происходит соответствующее округление, и отображается конечный результат, равный 6,8.

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

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

    Как настроить точность как на экране

    Для начала разберемся, каким образом настраивается точность округления как на экране в версии Excel 2019.

    1. Заходим в меню “Файл”.
    2. Кликаем по пункту “Параметры” в перечне слева в самом низу.
    3. Запустится дополнительно окно с параметрами программы, в левой части которого щелкаем по разделу “Дополнительно”.
    4. Теперь в правой части настроек ищем блок под названием “При пересчете этой книги:” и ставим галочку напротив опции “Задать указанную точность”. Программа предупредит нас о том, что точность при такой настройке будет снижена. Соглашаемся с этим, щелкнув кнопку OK и затем еще раз OK для подтверждения изменений и выхода из окна параметров.

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

    Настройка точности округления в более ранних версиях

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

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

    Microsoft Excel 2010

    1. Переходим в меню «Файл».
    2. Нажимаем по пункту с названием «Параметры».
    3. В открывшемся окне параметров кликаем по пункту «Дополнительно».
    4. Ставим галочку напротив опции «Задать точность как на экране» в блоке настроек «При пересчете этой книги». Опять же, подтверждаем внесенные корректировки кликом по кнопке OK, приняв во внимание тот факт, что точность расчетов будет снижена.

    Microsoft Excel 2007 и 2003

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

    Рассмотрим для начала версию 2007-го года.

    1. Нажимаем на значок «Microsoft Office», который расположен в верхнем углу окна слева. Должен появиться перечень, в котором нужно выбрать раздел с названием «Параметры Excel».
    2. Откроется ещё одно окно, в котором нужен пункт «Дополнительно». Далее справа следует выбрать группу настроек «При пересчёте этой книги» и поставить галочку напротив функции «Задать точность как на экране».
    Читайте также:  Excel зависает при открытии файла

    С более ранней версией (2013) все несколько иначе.

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

    Заключение

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

    Источник: microexcel.ru

    Задать точность как на экране в excel

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

    в этом файле (могу сделать копию с листа ранее распечатанного, если надо)
    На игнатовой на марте изначально было 1644
    и в нижней строчке по янв фев и марту были ошибки на 1
    я про те столбцы где формулы
    Может кто сталкивался или что то знает

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

    в этом файле (могу сделать копию с листа ранее распечатанного, если надо)
    На игнатовой на марте изначально было 1644
    и в нижней строчке по янв фев и марту были ошибки на 1
    я про те столбцы где формулы
    Может кто сталкивался или что то знает Швалов

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

    в этом файле (могу сделать копию с листа ранее распечатанного, если надо)
    На игнатовой на марте изначально было 1644
    и в нижней строчке по янв фев и марту были ошибки на 1
    я про те столбцы где формулы
    Может кто сталкивался или что то знает Автор – Швалов
    Дата добавления – 12.04.2018 в 09:54

    Источник: www.excelworld.ru

    Excel 2010 – “точность как на экране”

    #1 OFF Alex1

  • Забаненные
  • Cообщений: 152
  • В 2003 версии – Сервис – Параметры – Вычисления – Точность как на экране.

    Не нашёл этого в Excel 2010. Если можно дополнительно и в английской версии офиса.

    Сообщение отредактировал Alex1: 01 March 2013 – 15:53

    #2 OFF Alex1

  • Забаненные
  • Cообщений: 152
  • Установил временно русскую версию 2010 и нашёл.

    А дело было так в тот день. Ехал с обеда, позвонила со своей работы знакомая девушка, попросила помочь.
    Я заскочил к ней на работу на 10-15 минут, а у них там английская версия офиса 2010. Ну и не смог найти. В общем, “попал”, в смысле, как знаток.
    Временно вышел из положения, показав ей функцию Round(*,2), копировать и вставить как значения.

    Прикрепленные изображения

    Сообщение отредактировал Alex1: 06 March 2013 – 15:02

    #3 ON Самогонщик

    Любитель Windows и Windows Mobile

  • Активисты
  • Cообщений: 3184
  • Награды

    Нет, все-таки самая большая напасть России – дураки, указывающие дорогу.
    ©Гейко Ю.В. 1995, 2000, 2004, 2008 гг.

    Источник: forum.kasperskyclub.ru

    Excel неправильно считает. Почему?

    Часто при вычислении разницы двух ячеек в Excel можно видеть, что она не равна нулю, хотя числа одинаковые. Например, в ячейках A1 и B1 записано одно и тоже число 10,7 , а в C1 мы вычитаем из одного другое:

    И самое странное то, что в итоге мы не получаем 0! Почему?

    Причина очевидная – формат ячеек
    Сначала самый очевидный ответ: если идет сравнение значений двух ячеек, то необходимо убедиться, что числа там действительно равны и не округлены форматом ячеек. Например, если взять те же числа из примера выше, то если выделить их -правая кнопка мыши –Формат ячеек (Format cells) -вкладка Число (Number) -выбираем формат Числовой и выставляем число десятичных разрядов равным 7:

    Теперь все становится очевидным – числа отличаются и были просто округлены форматом ячеек. И естественно не могут быть равны. В данном случае оптимальным будет понять почему числа именно такие, а уже потом принимать решение. И если уверены, что числа надо реально округлять до десятых долей – то можно применить в формуле функцию ОКРУГЛ:
    =ОКРУГЛ( B1 ;1)-ОКРУГЛ( A1 ;1)=0
    =ROUND(B1,1)-ROUND(A1,1)=0
    Так же есть более кардинальный метод:

    • Excel 2007:Кнопка офисПараметры Excel (Excel options)Дополнительно (Advanced)Задать точность как на экране (Set precision as displayed)
    • Excel 2010:Файл (File)Параметры (Options)Дополнительно (Advanced)Задать точность как на экране (Set precision as displayed)
    • Excel 2013 и выше:Файл (File)Параметры (Options)Дополнительно (Advanced)Задать указанную точность (Set precision as displayed)
    Читайте также:  Как в эксель сделать заголовок таблицы на каждой странице

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

    Причина программная
    Но нередко в Excel можно наблюдать более интересный “феномен”: разница двух дробных чисел, полученная формулой не равна точно такому же числу, записанному напрямую в ячейку. Для примера, запишите в ячейку такую формулу:
    =10,8-10,7=0,1
    по виду результатом должен быть ответ ИСТИНА (TRUE) . Но по факту будет ЛОЖЬ (FALSE) . И этот пример не единственный – такое поведение Excel далеко не редкость при вычислениях. Его можно встретить и в менее явной форме – когда вычисления основаны на значении других ячеек, которые тоже в свою очередь вычисляются формулами и т.д. Но причина во всех случаях одна.

    Почему с виду одинаковые числа не равны?
    Сначала разберемся почему Excel считает приведенное выше выражение ложным. Ведь если вычесть из 10,8 число 10,7 – в любом случае получится 0,1 . Значит где-то по пути что-то пошло не так. Запишем в отдельную ячейку левую часть выражения: =10,8-10,7 . В ячейке появится 0,1 . А теперь выделяем эту ячейку -правая кнопка мыши –Формат ячеек (Format cells) -вкладка Число (Number) -выбираем формат Числовой и выставляем число десятичных разрядов равным 15:

    и теперь видно, что на самом деле в ячейке не ровно 0,1 , а 0,100000000000001 . Т.е. в 15 значащем разряде у нас появился “хвостик” в виде лишней единицы.
    А теперь будем разбираться откуда этот “хвостик” появился, ведь и логически и математически его там быть не должно. Рассказать я постараюсь очень кратко и без лишних заумностей – их на эту тему при желании можно найти в интернете немало.
    Все дело в том, что в те далекие времена(это примерно 1970-е годы), когда ПК был еще чем-то вроде экзотики, не было единого стандарта работы с числами с плавающей запятой(дробных, если по простому). Зачем вообще этот стандарт? Затем, что компьютерные программы видят числа по своему, а дробные так вообще со статусом “все сложно”. И при этом одно и то же дробное число можно представить по-разному и обрабатывать операции с ним тоже. Поэтому в те времена одна и та же программа, при работе с числами, могла выдать различный результат на разных ПК. Учесть все возможные подводные камни каждого ПК задача не из простых, поэтому в один прекрасный момент началась разработка единого стандарта для работы с числами с плавающей запятой. Опуская различные подробности, нюансы и интересности самой истории скажу лишь, что в итоге все это вылилось в стандарт IEEE754. А в соответствии с его спецификацией в десятичном представлении любого числа допускаются ошибки в 15-м значащем разряде. Что и приводит к неизбежным ошибкам в вычислениях. Чаще всего это можно наблюдать именно в операциях вычитания, т.к. именно вычитание близких между собой чисел ведет к потере значимых разрядов.
    Подробнее про саму спецификацию так же можно узнать в статье Microsoft: Результаты арифметических операций с плавающей точкой в Excel могут быть неточными
    Вот это как раз и является виной подобного поведения Excel. Хотя справедливости ради надо отметить, что не только Excel, а всех программ, основанных на данном стандарте. Конечно, напрашивается логичный вопрос: а зачем же приняли такой глючный стандарт? Я бы сказал, что был выбран компромисс между производительностью и функциональностью. Хотя возможно, были и другие причины.

    Куда важнее другое: как с этим бороться?
    По сути никак, т.к. это программная “ошибка”. И в данном случае нет иного выхода, как использовать всякие заплатки вроде ОКРУГЛ и ей подобных функций. При этом ОКРУГЛ здесь надо применять не как в было продемонстрировано в самом начале, а чуть иначе:
    =ОКРУГЛ( 10,8 – 10,7 ;1)=0,1
    =ROUND(10.8-10.7,1)=0,1
    т.е. в ОКРУГЛ мы должны поместить само “глючное” выражение, а не каждый его аргумент отдельно. Если поместить каждый аргумент – то эффекта это не даст, ведь проблема не в самом числе, а в том, как его видит программа. И в данном случае 10,8 и 10,7 уже округлены до одного разряда и понятно, что округление отдельно каждого числа не даст вообще никакого эффекта. Можно, правда, выкрутиться и иначе. Умножить каждое число на некую величину(скажем на 1000, чтобы 100% убрать знаки после запятой) и после этого производить вычитание и сравнение:
    =((10,8*1000)-(10,7*1000))/1000=0,1

    Хочется верить, что хоть когда-нибудь описанную особенность стандарта IEEE754 Microsoft сможет победить или хотя бы сделать заплатку, которая будет производить простые вычисления не хуже 50-рублевого калькулятора 🙂

    Статья помогла? Поделись ссылкой с друзьями!

    Источник: www.excel-vba.ru