Цепочки сравнений, пустые ячейки и SUMIF в HotXLS

HotXLS Delphi Component вычисляет =1<2<3 как FALSE — тот же ответ, что даёт Excel 16, — потому что с v2.384.3 его парсер формул сворачивает операторы сравнения слева направо: 1<2 становится TRUE, а TRUE<3 — FALSE, поскольку boolean стоит выше любого числа. Тот же релиз делает пустой операнд равным и 0, и "", и позволяет SUMIF растягивать одноклеточный sum range до формы его criteria range. Каждое из этих свойств выглядит мелочью, пока книга, посчитанная в Delphi, не разойдётся с той же книгой, открытой в Excel

Расхождение обычно начинается с формулы, которую человек написал по интуиции. Кто-то набирает =0<B2<100, чтобы проверить, что количество в диапазоне, Excel тихо отвечает FALSE на каждой строке, и лист уходит в продакшн с вшитым багом. Вычислительный движок не вправе чинить замысел пользователя; его работа — выдать то значение, которое выдал бы Excel, чтобы кэшированный результат, который HotXLS пишет в файл, совпадал с тем, что Excel показывает после пересчёта. До v2.384.3 HotXLS отвечал TRUE на такую проверку диапазона в каждой строке — неверно в противоположную сторону, и отчёт, собранный на сервере, противоречил тому же отчёту, открытому на десктопе

Почему =1<2<3 возвращает FALSE в Excel?

Excel возвращает FALSE, потому что читает цепочку сравнений как (1<2)<3, и внутренний TRUE затем проигрывает конкурс рангов числу 3. Старый парсер HotXLS читал тот же текст как 1<(2<3): TXLSSyntax.Parse_expr в lxFormula.pas парсил один операнд, видел токен сравнения и уходил в рекурсию в Parse_expr для правой части, что делает оператор правоассоциативным. Получалось 1<TRUE, число стоит ниже boolean, и результатом был TRUE. Ошибка симметрична: =3>2>1 — TRUE в Excel и FALSE в HotXLS, а =1=1=TRUE — TRUE в Excel и FALSE до исправления. Регрессия CalculateFormula_ComparisonChainsFoldLeftToRight пришпиливает семь таких формул к значениям, которые возвращает Excel 16, и прогоняет каждую через обе архитектуры движка, классический TXLSWorkbook и XLSX-нативный TXLSXWorkbook, используя метод Calculate, описанный в обзоре formula engine HotXLS

const
  Formulas: array [0..6] of string = ('=1<2<3', '=3>2>1', '=(1<2)<3',
    '=1<(2<3)', '=1=1=TRUE', '=3>2>1=TRUE', '=1<2<3=FALSE');
  // Что возвращает Excel 16:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate вычисляет на активном листе и
    // возвращает Null, когда в книге вообще нет листов
    Xlsx.Sheets.Add('Data');
    for i := 0 to High(Formulas) do
      Writeln(Formulas[i], '  classic=', VarToStr(Classic.Calculate(Formulas[i])),
        '  xlsx=', VarToStr(Xlsx.Calculate(Formulas[i])));
  finally
    Xlsx.Free;
  end;
end;

Исправление превращает Parse_expr в цикл той же формы, которую Parse_expr1 уже использовал для +, - и &. Он парсит первый операнд через Parse_expr1 и, пока следующий токен — один из =, <>, <, >, <= или >=, создаёт узел сравнения, прицепляет накопленный левый результат первым ребёнком, парсит следующий операнд через Parse_expr1, а не Parse_expr, и делает новый узел левым результатом для следующего круга. Две детали легко испортить при превращении рекурсии в итерацию, и обе описаны в заметках мейнтейнеров: накопленный узел надо передавать со сменой владельца (lChild := Item; Item := nil) именно в таком порядке, а ветка ошибки должна Exit-ить после освобождения полусобранного узла, а не выпадать из цикла и возвращать висячее дерево

Как HotXLS ранжирует числа, текст и boolean в сравнении?

HotXLS ранжирует смешанные типы так же, как Excel: любое число меньше любого текстового значения, а любое текстовое значение меньше любого boolean. TXLSCalculator.CompareVariants в lxCalc.pas классифицирует оба операнда через GetRetValueType в перечисление TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue) и, когда классы различаются, просто сравнивает их ординалы, так что порядок объявления того enum и есть межтиповое правило. Внутри одного класса сравнение естественное, с одним Excel-специфичным поворотом для текста: обе строки сперва прогоняются через lxUpperCase, поэтому ="abc"="ABC" — TRUE. Именно эта ранжировка делает результат цепочки не рассуждаемым без неё. TRUE<3 — не приведение TRUE к 1, а boolean в сравнении с числом, и boolean выигрывает. Даты для движка — серийные числа (varDate классифицируется как xlNumberValue), так что дата всегда ниже любого текста, включая текст, который лишь притворяется датой

Чему равна пустая ячейка в сравнении?

Пустая ячейка как операнд сравнения равна 0, когда другая сторона — число, равна "", когда другая сторона — текст, и с v2.384.53 равна FALSE, когда другая сторона — логическое значение, так что при пустой A1 =A1=0, =A1="" и =A1=FALSE — все TRUE. TXLSCalculator.CompareVarValues, обслуживающий все шесть операторов сравнения, подставляет пустоту перед вызовом CompareVariants: если ровно один операнд — Null, он становится WideString(''), когда партнёр — строка, False, когда партнёр — boolean, и 0 в остальных случаях. Две пустоты по-прежнему сравниваются друг с другом как равные без всякой подстановки. Арифметический путь всегда превращал пустоту в 0, отчего =A1+1 давало 1, но CompareVariants держал Null как собственный низший ранг, ниже любого числа, и операторы сравнения использовали тот ранг напрямую

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 пуста нарочно

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: пустая сравнивается как 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; до v2.384.3 было True
end;

Последняя строка — та, что ранила на практике. Под старым рангом пустота была меньше любого числа, включая отрицательные, поэтому =IF(A1<0,"overdrawn","ok") клеил ярлык «overdrawn» на каждую пустую ячейку баланса, а =A1=0 было FALSE для ячейки, которую любой пользователь описал бы как ноль. После v2.384.3 осталась одна граница: подстановка выбирала только между 0 и пустой строкой, поэтому пустота в сравнении с boolean становилась 0, что стоит ниже и TRUE, и FALSE, и =A1=FALSE на пустой A1 вычислялось как FALSE. Начиная с HotXLS 2.384.53 пустота в сравнении с логическим значением трактуется как FALSE в обоих движках, XLS и XLSX, как у Excel: при пустой A1 =A1=FALSE и =A1<TRUE возвращают TRUE, а =A1=TRUE — FALSE. Из этого же следует, что сравнение не отличит пустоту от FALSE — ни в Excel, ни в HotXLS; когда листу нужно то различие, проверяйте через ISBLANK или =A1=""

Почему SUMIF с одноклеточным sum range возвращал 0?

SUMIF возвращал 0, потому что HotXLS зажимал итерацию по меньшему из двух диапазонов, тогда как Excel держит форму criteria range и использует sum range лишь ради его левого верхнего угла. Поэтому =SUMIF(A1:A10,">5",B1) в Excel означает B1:B10 — удобство, на которое опирается масса шаблонов, собранных руками. Общий воркер TXLSCalculator.GetValueItemRange2 сжимал счётчики строк и столбцов до размеров value range, что сводило пример к единственному сравнению A1 с B1. v2.384.3 снимает зажим: цикл теперь идёт по criteria range и читает каждое значение на том же смещении от левого верхнего угла sum range. Поскольку CalcSumIF и CalcAverageIF зовут тот же воркер, AVERAGEIF получает тот же ресайз, а sum range больше criteria range по той же причине обрезается до формы критериев. Аргумент критерия посередине — аргумент класса значений, а два внешних — класса ссылок; различие разобрано в статье о неявной интерсекции и классах аргументов

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    for Row := 1 to 10 do
    begin
      Sheet.Cells[Row, 1].Value := Row;          // колонка критериев: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // суммы: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // sum range из одной ячейки
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // явный sum range
    if Book.Recalculate = lxOk then
      // И D1, и D2 равны 4000 (600+700+800+900+1000); до v2.384.3 D1 был 0
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT и YEARFRAC: два более тихих исправления

INDIRECT теперь уважает второй аргумент, а текст после валидной ссылки — это ошибка, а не то, что можно проигнорировать. При a1 FALSE текст парсится как абсолютный R1C1, поэтому =INDIRECT("R2C3",FALSE) читает C2; старый код игнорировал флаг, читал «R2» как столбец R, строку 2, и молча возвращал не ту ячейку. Флаг диспетчеризуется по variant-типу (boolean, число или текст), потому что конверсия строкового variant напрямую в Double поднимает исключение. Относительный текст R1C1 вроде R[1]C[1] возвращает #REF!, поскольку у INDIRECT нет ячейки-формулы, относительно которой его можно разрешить, и текст A1 с хвостовыми символами, "B2 junk", тоже возвращает #REF!. YEARFRAC с базисом 0 теперь применяет правила NASD для последнего дня февраля, которые DAYS360 уже реализовал: когда обе даты — последний день февраля, конечный день становится 30, а затем старт на последнем дне февраля становится 30. С 2024-02-29 по 2025-02-28 счёт теперь 360 дней, дробь ровно 1, где прежний Days360US насчитывал 359

Что гарантируют эти исправления и какой урок из них?

Поведение цепочек сравнений гарантировано тестом, сверяющим оба движка со значениями, измеренными в Excel 16, и тот тест существует, потому что первое описание исправления было неверным. Заметка к релизу v2.384.3 изначально говорила, что свёртка слева направо делает =1<2<3 равным TRUE — ровно то, что выдавал старый правоассоциативный парсер, и противоположное тому, что возвращают и Excel, и новый код. Никто не вычислил пример: он был написан из интуиции «1 меньше 2, что меньше 3». Заметку поправили, а семиформульный тест добавили последующим коммитом, и правило, из этого вышедшее, касается всякого, кто документирует семантику электронных таблиц: прогоните пример в Excel, прежде чем записывать ожидаемое значение. Подстановка пустого операнда и ресайз SUMIF следуют тому же поведению Excel, включая случай пустота-против-boolean с v2.384.53, а условным агрегатам, которым к тому же надо пропускать отфильтрованные или скрытые строки, ведают отдельные правила из статьи о SUBTOTAL и AGGREGATE и скрытых строках

HotXLS — нативный Delphi и C++Builder компонент электронных таблиц, который читает, пересчитывает и пишет XLS, XLSX, ODS и CSV без установленного Excel, а правила сравнения, пустоты и SUMIF, описанные здесь, живут в вычислительном движке, общем для обеих архитектур книг. Полный список функций и варианты лицензирования — на странице продукта HotXLS Delphi spreadsheet component