Округление в Excel
Dec. 22nd, 2023 05:27 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
green_frПришёл мне по работе файл со странной датой. Показывает «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 в текстовом редакторе, а там такая прелесть:
То есть в двух ячейках примерно одна и та же дата — полночь с 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 ломает его эвристику, он не узнаёт формулы, начинает считать — и получает «логичный» рудимент округления.
Пытаюсь понять, что происходит. Снимаю формат даты — значение 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 в текстовом редакторе, а там такая прелесть:
<sheetData>
<row r="1" spans="1:1" x14ac:dyDescent="0.35">
<c r="A1" s="1">
<v>45732.999999999993</v>
</c>
</row>
<row r="2" spans="1:1" x14ac:dyDescent="0.35">
<c r="A2" s="2">
<v>45733</v>
</c>
</row>
<row r="3" spans="1:1" x14ac:dyDescent="0.35">
<c r="A3">
<f>A2-A1</f>
<v>0</v>
</c>
</row>
</sheetData>
То есть в двух ячейках примерно одна и та же дата — полночь с 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 ломает его эвристику, он не узнаёт формулы, начинает считать — и получает «логичный» рудимент округления.
