Excel не считает дробные числа
При вводе значений в ячейки листа Excel все данные проходят через встроенный программный фильтр форматирования. Он позволяет упростить работу пользователя. Поэтому введенные данные могут отличаться от отображаемых значений после ввода.
К каким проблемам может привести фильтр форматов ячеек, рассмотрим на конкретных примерах. А также найдем лучшие решения для выхода из сложившихся ситуаций.
Если Excel считает некорректно числа после запятой
Рассмотрим простой пример, когда содержимое ячеек отличается, от отображения их значений. Например, могут возникнуть математические ошибки при округлении дробных чисел.
На готовом примере продемонстрируем следующие расчеты.
- Заполните исходную табличку как показано на рисунке:
- В ячейках B и C задайте финансовый формат (CTRL+1 «Формат ячеек»-«Число»-«Финансовый»).
- В ячейку C1 запишите то, что отображается в ячейке B1 ( 1,53 – результат после округления до двух знаков после запятой ). Так же само введите число в C2 как отображено в B2 ( символы валют не проставляем, так как они преставятся автоматически благодаря финансовому формату ).
- В третьей строке просуммируем значение каждой колонки таблички.
Как видите точность расчета знаков после запятой в Excel, может отличаться. Форматирование в реальности не округляет числовые значения в ячейках. Они остаются те же и реальными отображаются в строке формул.
При суммировании большого количества таких округлений ошибки могут быть очень большими. Поэтому при расчетах и вычислениях нельзя округлять с помощью форматирования. Необходимо знать, как округлить сумму в Excel. Точное округление можно сделать только специальными функциями такими как:
- =ОКРУГЛ;
- =ОКРУГЛВВЕРХ;
- =ОКРУГЛВНИЗ;
- =ЦЕЛОЕ ( функция позволяет в Excel округлить до большего целого ).
Для эффективного использования этих функций в больших количествах применяются массивы функций. Но это уже рассмотрим на следующих уроках.
Автоматическая вставка десятичной запятой
Автоматический фильтр форматов рассчитан на упрощение работы с программой, особенно если научится им управлять. Очень часто нам приходится поставить запятую при вводе денежных сумм для отображения копеек. Десятичный разделитель запятая в Excel может проставлятся автоматически при заполнении финансовых данных в денежном эквиваленте. Для этого:
- Откройте окно «Фай»-«Параметры».
- В окне «Параметр Excel» перейдите на «Дополнительно»-«Параметры правки» и отметьте галочкой пункт «Автоматическая вставка десятичной запятой». Число знаков после разделителя пусть останется «2». Теперь проверим результат.
- В ячейку A1 введите 1234 и нажмите «Enter», в результате мы видим 12,34 как на рисунке:
Теперь можно спокойно вводить суммы с копейками, не разделяя их запятой. После каждого ввода запятая будет проставляться автоматически перед двумя последними числами.
Стоит отметить, что если в сумме 00 копеек, то знаки после запятой в Excel нужно вводить обязательно. Иначе может получиться так 0,01 или 0,2.
Если при вводе с автоматическим режимом вставки разделителя вы ввели запятую, то она останется в том месте, где вы ее вводили.
То есть количество знаков после запятой можно изменить или перенести саму запятую.
При импорте в Excel данных из внешних программ, иногда возникает весьма неприятная проблема - дробные числа превращаются в даты:
Так обычно происходит, если региональные настройки внешней программы не совпадают с региональными настройками Windows и Excel. Например, вы загружаете данные с американского сайта или европейской учётной системы (где между целой и дробной частью - точка), а в Excel у вас российские настройки (где между целой и дробной частью - запятая, а точка используется как разделитель в дате).
При импорте Excel, как положено, пытается распознать тип входных данных и следует простой логике - если что-то содержит точку (т.е. российский разделитель дат) и похоже на дату - оно будет конвертировано в дату. Всё, что на дату не похоже - останется текстом.
Давайте рассмотрим все возможные сценарии на примере испорченных данных на картинке выше:
- В ячейке A1 исходное число 153.4182 осталось текстом, т.к. на дату совсем не похоже (не бывает 153-го месяца)
- В ячейке A2 число 5.1067 тоже осталось текстом, т.к. в Excel не может быть даты мая 1067 года - самая ранняя дата, с которой может работать Excel - 1 января 1900 г.
- А вот в ячейке А3 изначально было число 5.1987, которое на дату как раз очень похоже, поэтому Excel превратил его в 1 мая 1987, услужливо добавив единичку в качестве дня:
Вот такие варианты. И если текстовые числа ещё можно вылечить банальной заменой точки на запятую, то с числами превратившимися в даты такой номер уже не пройдет. А попытка поменять их формат на числовой выведет нам уже не исходные значения, а внутренние коды дат Excel - количество дней от 01.01.1900 до текущей даты:
Лечится вся эта история тремя принципиально разными способами.
Способ 1. Заранее в настройках
Если данные ещё не загружены, то можно заранее установить точку в качестве разделителя целой и дробной части через Файл - Параметры - Дополнительно (File - Options - Advanced) :
Снимаем флажок Использовать системные разделители (Use system separators) и вводим точку в поле Разделитель целой и дробной части (Decimal separator) .
После этого можно смело импортировать данные - проблем не будет.
Способ 2. Формулой
Если данные уже загружены, то для получения исходных чисел из поврежденной дата-тексто-числовой каши можно использовать простую формулу:
=--ЕСЛИ( ЯЧЕЙКА("формат";A1)="G" ; ПОДСТАВИТЬ(A1;".";",") ; ТЕКСТ(A1;"М,ГГГГ") )
В английской версии это будет:
=--IF (CELL ("format ";A1)="G"; SUBSTITUTE (A1;".";","); TEXT (A1;"M ,YYYY "))
Логика здесь простая:
- Функция ЯЧЕЙКА (CELL) определяет числовой формат исходной ячейки и выдаёт в качестве результата "G" для текста/чисел или "D3" для дат.
- Если в исходной ячейке текст, то выполняем замену точки на запятую с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) .
- Если в исходной ячейке дата, то выводим её в формате "номер месяца - запятая - номер года" с помощью функции ТЕКСТ (TEXT) .
- Чтобы преобразовать получившееся текстовое значение в полноценное число - выполняем бессмысленную математическую операцию - добавляем два знака минус перед формулой, имитируя двойное умножение на -1.
Способ 3. Макросом
Если подобную процедуру лечения испорченных чисел приходится выполнять часто, то имеет смысл автоматизировать процесс макросом. Для этого жмём сочетание клавиш Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставляем в нашу книгу новый пустой модуль через меню Insert - Module и копируем туда такой код:
Останется выделить проблемные ячейки и запустить созданный макрос сочетанием клавиш Alt + F8 или через команду Макросы на вкладке Разработчик (Developer - Macros) . Все испорченные числа будут немедленно исправлены.
При суммировании в Microsoft Excel 2010 функцией СУММ или методом простого суммирования чисел в столбце , полученных умножением двух констант , выскакивает невероятная для такой простой операции погрешность - 0,02 сотых на каждых 12 слагаемых!
Я в шоке! Даже самый простой китайский калькулятор считает лучше.
Можете проверить - посмотрите файл.
Погрешность при суммировании Excel
Никто не знает с чем может быть связана такая погрешность при суммировании? Она конечно не.
Крах Excel при суммировании
Крах Excel при суммировании. Столкнулся с уникальной ошибкой Excel. Если в ячейке D10 нажать.
Ошибка Excel в суммировании дробных чисел
"Ваш Excel не правильно считает!" - сколько раз слышал от экономистов/менеджеров подобные.
Проверка на переполнение при суммировании
Составить программу , j проверяет, не приводит ли суммирование двух целых чисел A и B к.
А Вы не возмущайтесь
Давно известно, что калькулятор считает точнее Excel, когда речь идёт о не целых числах. На то он и калькулятор, что бы считать, а Excel - это электронная таблица, которая умеет гораздо больше калькулятора, но имеет свои недостатки
Вот с других форумов (к сожалению здесь ссылки давать нельзя):
Фактически источником ошибки является ошибка округления. Данная ошибка является следствием дефекта арифметики плавающей точки и того факта, что чаще всего десятичные дроби являются повторяющимися долями в двоичной системе счисления. Такие числа не могут представляться в конечном количестве битов. В связи с этим текстовое округление получается не всегда точным, т.к. большинство компьютеров подбирает последние цифры дробной части, исходя из ближайшего (с наименьшей разницей) эквивалента. Некоторые компьютеры не производят округления, а просто обрезают (выключают) последние биты, получая результирующую ошибку, правильно называемую ошибкой округления (в противоположность ошибке усечения, когда усекается расширение ряда). Для получения дополнительной информации обратитесь к Introduction to Numerical Methods (введение в числовые методы) авторов Peter A. Stark, Macmillian Company, 1970
. я готов объяснить, почему 3.1-3=0.1, а 4.1-4
Основа проблемы кроется именно в особенностях работы с плавающей запятой. Итак:
Для начала - вот формат числа с плавающей запятой с двойной точностью (именно он используется в Excel). И "забудем" для простоты, что байты идут от младшего старшему. Так выглядит +1:
+1: 0 011 11111111 0000 00000000 00000000 00000000 00000000 00000000 00000000
Первый "0" - это знаковый бит: 0 - "+", 1 - "-"
Затем группа из 11 битов - порядок числа. Она увеличена (для данного типа формата) на величину 3ffh, чтобы было удобно отображать отрицательные степени экспоненты. Т.е., фактически, порядок числа +1 равен 0, что и понятно.
Не очень ясно, как интерпретировать 52 последующих нуля. Тут нужно сказать, что в формате чисел с плавающей запятой используется т.н. "нормализованная" запись чисел, то есть когда значащий разряд целой части мантиссы не равен нулю - в общем случае, или равен "1" в двоичной системе исчисления; экспонента при этом соответствующим образом изменяется. Но если старший разряд - всегда "1", то зачем его хранить? Его и не хранят для экономии места, а лишь подразумевают:
1.0000 00000000 00000000 00000000 00000000 00000000 00000000 = 1.0e0
Посмотрим теперь как выглядят другие наши "подопытные":
+0.1: 0 011 11111011 1001 10011001 10011001 10011001 10011001 10011001 10011010
(обратите внимание на выделенные жирным шрифтом биты последнего байта, они сыграют потом ключевую роль!)
+3.1: 0 100 00000000 1000 11001100 11001100 11001100 11001100 11001100 11001101
+3.0: 0 100 00000000 1000 00000000 00000000 00000000 00000000 00000000 00000000
Вычтем одно число из другого и получаем (порядок в вычитании не участвует, только мантисса и "подразумеваемая" единица):
+3.1: 1.1000 11001100 11001100 11001100 11001100 11001100 11001101
+3.0: 1.1000 00000000 00000000 00000000 00000000 00000000 00000000
---------------------------------------------------------------------------------
+0.1: 0.0000 11001100 11001100 11001100 11001100 11001100 11001101
Целая часть мантиссы оказалась равной нулю, поэтому выполним нормализацию - сдвинем ее влево на 5 позиций, дополнив справа нулями(!), а порядок, соответственно, уменьшим на 5 - было 400h, стало 3fbh:
+0.1: 0 011 11111011 1001 10011001 10011001 10011001 10011001 10011001 10100000
Обратите внимание, число на долю, на "1" в 47м разряде, но больше, чем 0.1, поэтому при округлении мы и видим в ячейке Excel то, что нам нужно!
Проделаем тоже самое с 4.1-4.0 (но уже без дополнительных комментариев):
+4.1: 0 100 00000001 0000 01100110 01100110 01100110 01100110 01100110 01100110
+4.0: 0 100 00000001 0000 00000000 00000000 00000000 00000000 00000000 00000000
---------------------------------------------------------------------------------
+0.1: 0 011 11111011 1001 10011001 10011001 10011001 10011001 10011001 10000000
В 47м разряде "0", а, следовательно, мы получили меньше, чем 0.1, что нам Excel честно и показывает при большой разрядности. Почему он не поступает столь же честно при превышении? А посмотрите внимательнее - где больше отличие от "эталонного" 0.1? Во втором случае, в первом ему просто не хватает точности.
Если проделать все это с последующими числами, то будет то же самое, только хуже
Так что здесь не проблема Excel, а родимое пятно двоичной арифметики. Хотя, конечно, доля вины дяди Билла в этом тоже есть. Можно было бы воспользоваться иным представлением чисел. Однако, я не думаю, чтобы в Quattro или Lotus решение было бы иным.
А теперь представьте, что дело идет не о копейках, а о миллионах и миллиардах рублей или долларов? Вот тут-то эта мелочь и вылезет. И о подобных случаях округлений писали.
Часто при вычислении разницы двух ячеек в 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 знака после запятой. При этом отменить данную операцию нельзя, если только не закрыть книгу без сохранения.
Можно, правда, выкрутиться и иначе. Умножить каждое число на некую величину(скажем на 1000, чтобы 100% убрать знаки после запятой) и после этого производить вычитание и сравнение:
=((10,8*1000)-(10,7*1000))/1000=0,1
Хочется верить, что хоть когда-нибудь описанную особенность стандарта IEEE754 Microsoft сможет победить или хотя бы сделать заплатку, которая будет производить простые вычисления не хуже 50-рублевого калькулятора :)
В настоящее время в большинстве вычислений используются десятичные дроби. Однако в некоторых случаях вы можете столкнуться с простыми (обыкновенными) дробями.[1] Это может быть учебный пример, или диаметр вентиля (в долях дюйма), или, наконец, вес золотого слитка (в долях унции). ?
Excel предлагает 9 стандартных форматов обыкновенных дробей (рис. 1). Кроме того, вы можете создать пользовательский формат по своему усмотрению (об этом чуть позже).
Рис. 1. Стандартные форматы обыкновенных дробей в Excel
Если вы уже ввели в ячейку число, например, 0,5, и хотите его отобразить в виде простой дроби, кликните на ячейке правой кнопкой мыши, выберите «Формат ячеек. », далее «Дробный» и «Простыми дробями» (рис. 2).
Рис. 2. Выбор формата «Дробный»
Excel так же, как и мы знает, что простые дроби используются редко, поэтому при попытке ввести в ячейку 1/2, Excel решит, что вы вводите дату и покажет «01.фев». Чтобы отобразить в ячейке дробь, введите 0, пробел, а затем 1/2. Аналогично вводится и составная дробь: наберите целую часть, пробел и дробную часть. Например, 1 2/3. Если вы выделите эту ячейку, в строке формул появится значение 1,666666666667, а запись в этой ячейке будет представлена как дробь.
Если числитель больше знаменателя, то Excel преобразует такую дробь в целую и дробную часть. Например, если вы введете 0 25/4, то Excel выразит это число как 6 1/4.
Рис. 3. Пользовательский дробный формат
В контрактах с иностранными компаниями я сталкивался с записью центов в виде простой дроби; что-то типа: 20 и 7/100 долларов. Начните с ввода в ячейку: 20, «пробел», 1/100. Выберите формат «Сотыми долями» (рис. 4а). Далее выберите «(все форматы)», и дополните шаблон двумя словами: «и», «долларов» (рис. 4б).
Рис. 4. Формат для отображения долларов и центов
В заключительном примере значение отображается в 16-х долях; за числом следует знак дюймов (рис. 5). Поскольку кавычки являются служебным символом для шаблона, чтобы отобразить сами кавычки, нужно перед ними набрать косую черту: \ "
Рис. 5. Формат для отображения дюймов
[1] Использованы официальные материалы Microsoft и советы Джона Уокенбаха.
Читайте также: