Округление в Excel

green_fr — 22.12.2023 Пришёл мне по работе файл со странной датой. Показывает «17/03/2025», но когда кликаешь на эту ячейку, в строке формул показывает значение «16/03/2025 00:00:00».

Пытаюсь понять, что происходит. Снимаю формат даты — значение 45733. Пишу в соседней ячейке ручками «45733». Ставлю назад формат даты: обе ячейки показывают «17/03/2025». Но когда кликаешь в мою, в строке формул остаётся «17/03/2025», а в той ячейке — «16/03/2025 00:00:00». Побаловался с форматами, показывающими время — без проблем, может показывать «00:00:00» и в моей тоже.

Ладно, думаю, может быть моё 45733 чем-то отличается от их 45733? Ставлю рядом формулу разности между двумя ячейками — 0. Честный ноль. Не какое-то там 10E-10.

Достаём козыри. К чёрту Excel, мало ли что он там мудрит. Надо смотреть на исходные данные. Сохраняю файл, состоящий из этих двух ячеек. Открываю его в Total Commander как zip, спускаюсь до «xl\worksheets\sheet1.xml», открываю XML в текстовом редакторе, а там такая прелесть:

  
    
     45732.999999999993
    
  
  
    
     45733
    
  
  
    
     A2-A1
     0
    
  

  

То есть в двух ячейках примерно одна и та же дата — полночь с 16 на 17 марта. Только у меня точно полночь. А в другой ячейке — несколько микросекунд до полуночи. То есть, формально у меня 17 марта, а у них — 16 марта. Проблема в том, что их время 23:59:59.99999999 округляется не до 24:00:00, а до 00:00:00. Ну и то, что (из-за особенностей внутреннего формата хранения данных) 45733 — 45732.999999999993 реально равняется 0, а не 0.0000000000007 (поэтому я не мог увидеть эту разницу в Excel).

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

---

Читаю сейчас книжку от автора StandUp Math. У него вообще прекрасный пример. Наберите, говорит, в Excel формулу «=0.5-0.4-0.1». Он посчитает «честный ноль». А теперь наберите «=(0.5-0.4-0.1)*1» — и вы увидите что-то вроде 1E-17, потому что числа 0.4 и 0.1 не записываются в двоичной системе без округления (примерно потому же, почему нас не удивляет, что конечная запись 1/3+2/3 не даёт ровно 1: 0.33333333+0.66666666=0.99999999). Почему же в первом случае получается 0? Видимо, Excel в этом случае не считает по-честному, а каким-то образом понимает, что там должен быть ноль — и показывает его. А умножение на 1 ломает его эвристику, он не узнаёт формулы, начинает считать — и получает «логичный» рудимент округления.

Оставить комментарий

Популярные посты:

• Ранее
• Архив