green_fr: (Default)
Пришёл мне по работе файл со странной датой. Показывает «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 в текстовом редакторе, а там такая прелесть:

<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 ломает его эвристику, он не узнаёт формулы, начинает считать — и получает «логичный» рудимент округления.

Excel 2016

Jun. 21st, 2018 03:25 pm
green_fr: (Default)
На работе затеяли миграцию на новую версию Офиса. Мы даже составили список всего, что может навернуться, нам выдали виртуальную машину для тестов, мы честно всё оттестировали — ни одной проблемы. Во вторник ночью нам заменили Офис, в среду всё очевидно рухнуло :-)

1. Первая ошибка просто анекдотична. Эту часть мы даже не думали тестировать, настолько она тривиальна: скрипт, который создаёт новый файл, переименовывает первые две закладки и пишет туда какие-то данные. Навернулось из-за того, что Excel 2016 создаёт новые файлы не с тремя пустыми закладками (как это было всегда), а с одной. Логично (я давно ждал, когда же они, наконец-то перестанут создавать пустые закладки), но скрипт, пытающийся переименовать вторую закладку, падает: index out of bounds.

2. Вторую ошибку я так и не понял. Эту часть мы тоже не думали проверять — Total Commander. После перехода на Office 2016 он виснет после длинного нажатия на правую кнопку мыши на директории (длинное нажатие там делает то же, что короткое нажатие в Windows Explorer — показывает контекстное меню). Опытным путём выяснили, что если сначала длинно нажать на файл, то контекстное меню показывается, и дальше в этой копии Total Commander’а ошибки не будет и на директории. А если начать с директории — то падает глухо (not responding). Вылечилось переходом на последнюю версию Total Commander, но мне чисто по-человечески интересно — какие могут быть варианты объяснения такого бага?
green_fr: (Default)
После вопроса про миллиард секунд разговорился с коллегами. Полезли проверять, чему соответствует дата 0 в наших системах.

Про Excel я помнил, что там это 00/01/1900, но при этом мне почему-то в голову не приходило проверить, чему соответствует дата −1. Оказалось, ничему — в Excel невозможно поставить дату до начала 1900 года.
(если я правильно помню, Excel под Mac долгое время ставил нулевую дату на 00/01/1904, поэтому в современных версиях Excel есь возможность выбора базы для дат — я на эти грабли уже наступал, прелесть)

В Matlab нулевая дата тоже приходится на нулевое января, только и год там нулевой: 00/01/0000. Зато — прогресс! — он понимает отрицательные числа. И дата −1 попадает на 30 декабря 9999 года. Почему 30 декабря, понятно — чтобы через 2 дня было 1 января, а что там между ними — 31 декабря или 0 января, — это уже детали. И почему 9999 года тоже, наверное, понятно — в какой-нибудь первой версии MatLab вывод даты был предусмотрен только в рамках 4 цифр: проблему 2000 года решили, а даты до нашей эры никого никогда не будут интересовать.
green_fr: (Default)
Принёс сыну задание из школы. «Посчитайте количество нулей в конце 50!»

Мальчик довольно резво стартанул: 10, 20, 30, 40, 50 — это 5 нулей. 2*5, 12*15, 22*25, 32*35, 42*45 — это ещё пять. Итого — 10 нулей!

Отлично, говорю, давай пойдём к компьютеру, посчитаем в Excel, проверим.
Пускай, думаю, объяснит, почему в ответе 12 нулей, а у него только 10. И информатику одновременно немного протолкну.

Открываем Excel. Первая колонка — числа от 1 до 50. Вторая колонка — факториал, причём я аккуратно подвёл к идее, что n! = (n-1)!*n.
Протянули формулу до конца... У результата 50 нулей... WTF?!
Все, кто догадался, объявляются программистами, вне зависимости от декларируемого рода занятий... )

CVS Excel

Feb. 14th, 2012 09:36 am
green_fr: (Default)
Скажите, а кто-нибудь хранит Excel в CVS (или ему подобных системах)?

Основной вопрос - сравнение двух версий файла. Я согласен на множество упрощений, например полностью отказаться от сравнения форматирования, даже от формул - только данные. Но я порылся в Internet'е, ничего приличногов упор не нахожу. А наши программеры предлагают перейти на Google Docs - при всём моём уважении к этой фирме, мне это пока что кажется опасной утопией.

С другой стороны, последние версии Excel сохраняют файлы в формате XML-ZIP, про который я уже когда-то спрашивал, но для которого тоже пока что решения не нашёл...

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

Yearfrac

Apr. 6th, 2011 02:09 pm
green_fr: (Default)
В MatLab есть замечательная функция yearfrac, которая возвращает разницу между двумя датами, выраженную в годах. При этом у функции есть третий параметр, правило подсчёта разницы — в финансах регулярно используются довольно экзотические правила, рудименты докомпьютерной эпохи. Например, между 1 январём и 1 февралём может быть 1/12 года (правило 30/360), 31/365 (правило actual/365) или иногда 31/365, а иногда 31/366, в зависимости от високосности года (правило actual/actual). Правил несколько десятков, но в нормальных условиях всегда используется правило actual/actual, это значение параметра by default.

А только что я обнаружил ужасный баг в этой, казалось бы, простой функции. Если посчитать разницу между 01/01/2007 года и 01/01/2009 года (здесь и далее только actual/actual), то функция выдаёт значение 731/365. Между 01/01/2008 года и 01/01/2010 года — 731/366. И только между 01/01/2009 и 01/01/2011 получаем разумный результат — ровно 2 года.

То есть авторы проверяют, есть ли 29 число в первом феврале промежутка, и если да, то все годы объявляются високосными, если нет — то все невисокосными. Понятно, что функцию писали из рассчёта на её использование внутри одного года, но в документации об этом ограничении ни слова!

P.S. К слову, поведение аналогичной функции Excel я вообще не понимаю. Первые два примера дают 731/365,333 (откуда берётся 365 1/3 дня?!), последний — уверенное 2.
green_fr: (Default)
Или это мы выбираем программы, похожие на нас?

Я вдруг заметил. что меня раздражает тот факт, что в Excel нельзя переходить по закладкам по кругу. То есть Ctrl-PgUp позволяет перейти на предыдущую закладку, но с первой закладки он не переходит, зациклившись, на последнюю, а остаётся не первой. А я уже привык по Firefox/Chrome бегать по кругу, в зависимости от того, как короче.

Или вот, Open Office и FireFox используют общую систему проверки орфографии. А в chrome какая-то своя, убогая — например, «ё» вообще за букву не держит. А в Excel вообще нет проверки орфографии (или это у меня версия старая?) — как можно с ним работать?

Или принцип, что всё должно делаться клавиатурой. С удовольствием нашёл все нужные short-cut в MatLab, а в Excel никак не могу найти, как а) уничтожить закладку, б) «продолжить формулу» (эффект достигается double-click’ом по правому нижнему углу ячейки с формулой — формула «растягивается» на всю колонку. Кстати, жутко не хватает такой же функции для строчек.
green_fr: (Default)
«Шутка» для любителей Excel (смеяться грустно, после слова «L516»).
Знаете, есть там возможность дать какой-то области имя, и потом это имя использовать в формулах? В английском Excel это делается через Insert->Names.
Я только что потерял пол часа, пытаясь сделать то же самое через VBA.
Read more... )
green_fr: (Default)
Кто-нибудь знает хорошую документацию о матричных формулах Excel?
Точнее, как можно указать, какую формулу я хочу воспринимать, как матричную, а какую, как обычную, скалярную.
Пример: есть колонка, в которой я хочу посчитать элементы, находящиеся в заданных пределах (между 0 и 100, например).
Посчитать элементы меньшие 0 можно как минимум двумя способами: функция =nb.si(range, "<0") (по-английски, по-моему, она называется countif) и {=somme(si(range<0;1;0))} (английские эквиваленты sum и iif). Ни тот, ни другой метод мне не удаётся примирить со вторым критерием: в первом случае просто синтаксис не предусмотрен, во втором Excel не воспринимает et (and) как матричную функция: {=somme(si(et(range<0;range>100);1;0))} даёт выражение, равное {=si(et(range<0;range>100);1;0)}, потому как оно скалярное, потому как {=et(range<0;range>100)} скалярное. Последнее, к слову, равно true тогда и только тогда, когда все элементы матрицы попадают под заданное условие.
Откуда и изначальный вопрос.

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

P.S. Варианты с созданием промежуточных колонок, подсчётом через VBA, SQL и "перейди на unix" не предлагать :-)

Profile

green_fr: (Default)
green_fr

June 2025

S M T W T F S
123 4 567
89 1011 12 1314
15 16 17 18 19 2021
22 2324 25262728
2930     

Syndicate

RSS Atom

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Jun. 26th, 2025 03:58 pm
Powered by Dreamwidth Studios