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 121314
15161718192021
22232425262728
2930     

Syndicate

RSS Atom

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Jun. 13th, 2025 06:38 am
Powered by Dreamwidth Studios