Формулы массивов HotXLS: зачем Excel ставит @ и #VALUE!

Excel 365 вставляет @ в формулу вроде =SUM(A1:B1*{10,100}) и показывает #VALUE!, если файл сохранил её как обычную формулу: без пометки Excel применяет к каждому операнду оператора старое неявное пересечение. С v2.384.68 HotXLS Delphi Component хранит такие формулы с операторами массивов так же, как Excel 365: в XLSX это формулы динамического массива в одной ячейке, в XLS — формулы массива из одной ячейки

Симптом не выловишь ревью кода. Ваш Delphi-сервис пишет книгу, HotXLS пересчитывает её и кэширует 210 для =SUM(A1:B1*{10,100}), а клиент открывает файл в Excel 16 и видит в строке формул =SUM(@A1:B1*@{10,100}), а в ячейке — #VALUE!. В файле нет ничего битого. Не хватает метаданных, которые сказали бы Excel, что формула записана по правилам динамических массивов, — без них Excel возвращается к старой модели вычисления, действовавшей до появления динамических массивов

Зачем Excel 365 добавляет @ в формулу, которую HotXLS посчитал верно?

Excel 365 добавляет @, потому что формула без пометки динамического массива по определению считается легаси-формулой, а легаси-формулы всюду, где оператор ждёт одиночное значение, сворачивают диапазон из многих ячеек в одну. Это свёртывание и есть неявное пересечение: Excel берёт из диапазона ту ячейку, что стоит в строке формулы (для вертикального диапазона) или в её столбце (для горизонтального), а если такой ячейки нет, результат — #VALUE!. Для старых формул Excel 365 сохраняет это поведение и показывает @, чтобы свёртывание было видно

Положите =SUM(A1:B1*{10,100}) в E5 — и легаси-прочтение станет очевидным. A1:B1 — горизонтальный диапазон, формула сидит в столбце E, в столбце E у диапазона ячеек нет, так что @A1:B1 даёт #VALUE!, и вся SUM наследует этот результат. По правилам динамических массивов тот же текст перемножает поэлементно, 1 × 10 + 2 × 100, и возвращает 210. Формульный движок HotXLS считает по правилам динамических массивов начиная с релизов v2.384.61 и v2.384.63; формат файла об этом просто молчал. Если в A1:B2 лежат 1, 2, 3 и 4, вот пробные формулы и то, что показывает Excel 16:

Формула	Результат HotXLS	Excel 16 при хранении как обычной формулы	Хранение с v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Динамический массив, Excel показывает 210
=SUM((A1:B2>2)*1)	2	Неявное пересечение, неверно или ошибка	Динамический массив, Excel показывает 2
=SUMPRODUCT((A1:B2>2)*1)	2	Неявное пересечение, неверно или ошибка	Динамический массив, Excel показывает 2
=MAX(A1:B2-1)	3	Неявное пересечение, неверно или ошибка	Динамический массив, Excel показывает 3
=SUM(A1:B2)	10	10	Обычная формула, без изменений

Последняя строка важна не меньше первых четырёх. SUM(A1:B2) передаёт диапазон прямо в параметр функции, принимающий ссылки, так что ни один оператор не видит диапазона из многих ячеек и пересекаться нечему. Сам Excel 365 сохраняет такую формулу как обычную, и HotXLS поступает так же

Как HotXLS хранит формулы с операторами массивов в XLSX и XLS

В XLSX HotXLS записывает формулу с оператором массива как динамический массив в одной ячейке: элемент <c> получает cm="1", формула становится <f t="array" ref="E5">, а в пакет добавляется xl/metadata.xml с типом метаданных XLDAPR, чьё расширение несёт dynamicArrayProperties fDynamic="1". Атрибут cm — это индекс с единицы в блок cellMetadata той части, а стоящая за ним запись XLDAPR как раз и говорит Excel «считай это по правилам динамических массивов». Ровно такую структуру пишет сам Excel 16, когда вы вводите ту же формулу и сохраняете файл, — так целевая раскладка и была установлена

В XLS части с метаданными нет, поэтому HotXLS берёт единственную конструкцию BIFF8 для вычисления массива: формулу массива из одной ячейки. Ячейка получает запись FORMULA, чей поток токенов — единственный PtgExp, указывающий на саму себя, а за ним идёт запись ARRAY ($0221) с настоящей разобранной формулой над диапазоном в одну ячейку. Excel 365 пишет формулы динамических массивов в XLS точно так же, а старая версия Excel при чтении файла видит классическую формулу массива, вводимую через Ctrl+Shift+Enter

Новый API тут не при чём. Пометка ставится, когда вы присваиваете формулу через обычный cell API, в обоих движках. На стороне XLSX это TXLSXCell.Formula:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 1;
    Sheet.Cells[1, 2].Value := 2;
    Sheet.Cells[2, 1].Value := 3;
    Sheet.Cells[2, 2].Value := 4;

    // Оператор над диапазоном или встроенным массивом: хранится как динамический массив
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Диапазон передан прямо в функцию: остаётся обычным <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

    if Book.Recalculate = lxOk then
      Writeln(VarToStr(Sheet.Cells[5, 5].Value));   // 210

    // Корень массива хранит текст без ведущего '='
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 и E6 получают cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

После конверсии TXLSXCell.Formula возвращает текст без = — в том же виде, какой хранит TXLSXRange.SetDynamicArrayFormula, так что коду, сравнивающему строки формул после присвоения, стоит нормализовать ведущий =

Классический движок следует тому же правилу через IXLSRange.Formula на одиночной ячейке. Присвоение формулы внутренне перенаправляет её в путь формулы массива на одну ячейку, так что сохранённый XLS содержит пару FORMULA плюс ARRAY:

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['A1', 'A1'].Value := 1;
  Sh.Range['B1', 'B1'].Value := 2;
  Sh.Range['A2', 'A2'].Value := 3;
  Sh.Range['B2', 'B2'].Value := 4;

  Sh.Range['E5', 'E5'].Formula := '=SUM(A1:B1*{10,100})';  // запись ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // запись ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // обычная FORMULA

  Writeln(VarToStr(Sh.Range['E5', 'E5'].Value));   // 210
  Writeln(VarToStr(Sh.Range['E6', 'E6'].Value));   // 3
  Wb.SaveAs('probe.xls');
end;

Если вы закрепляете результат из многих ячеек, а не скалярный агрегат, правильным инструментом остаются явные API: SetArrayFormula для заранее размеченного прямоугольника, как описано в статье о формулах с растеканием динамического массива в HotXLS, или TXLSXRange.SetDynamicArrayFormula, когда нужна пометка динамического массива XLSX на диапазоне, который вы размечаете сами. Автоматический путь из этой статьи покрывает только формулы, введённые в одну ячейку

Какие формулы HotXLS помечает как динамические массивы?

HotXLS помечает формулу, только когда у оператора есть поддерево операнда, выдающее массив. Проверка идёт по скомпилированному синтаксическому дереву, а операнд выдаёт массив, если это диапазон из многих ячеек, встроенная константа массива или другое выражение с оператором, у которого есть такой операнд. Скобки прозрачны. В счёт идут арифметические операторы (+ - * / ^), конкатенация (&), шесть сравнений, унарные плюс и минус и процент:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) и A1:B2-1 помечаются, где бы они ни стояли в формуле, включая SUMPRODUCT
• SUM(A1:B2) и SUMPRODUCT(A1:A2,{1;10}) не помечаются: диапазон и массив уходят прямо в аргумент функции, и ни один оператор их не касается
• A1*2 или SUM(A1,B1)*2 не помечаются: ссылки на одну ячейку и результаты функций для этой проверки — скаляры

Три границы нарочиты. Во-первых, пометка ставится, только когда формула введена через API: TXLSXCell.Formula в движке XLSX и присвоение Formula или Value одиночной ячейке в классическом. Формулы, загруженные из файла, записываются назад ровно в том виде, в каком найдены, — легаси-формула другого производителя может зависеть от неявного пересечения намеренно. Во-вторых, текст без : и без { пропускается без повторной компиляции. В-третьих, формула, которая бы растеклась, скажем =A1:B1*2 сама по себе, помечается как динамический массив в одной ячейке, закреплённый там, куда вы её положили. HotXLS её не растягивает, а Excel при следующем пересчёте дотянет результат до соседних ячеек

Это правило про операнды — родня правилу о классах аргументов из статьи о неявном пересечении для определённых имён в HotXLS. Та статья о параметрах функций, объявленных как value class; эта — об операторах, которые в легаси-модели всегда требуют значений

Что изменилось в вычислительном движке, чтобы результаты сошлись

Починка хранения в v2.384.68 опирается на то, что формульный движок HotXLS уже возвращал значения Excel 365, — а на это ушло несколько более ранних правок в обоих движках. Самая заметная касалась SUMPRODUCT: до v2.384.61 он принимал только два и более обычных диапазона, поэтому SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) и даже одноаргументный SUMPRODUCT(B1:B2) возвращали #N/A. Теперь HotXLS вычисляет аргументы-выражения поэлементно по правилам Excel:

• у всех аргументов должна быть в точности одна форма, скаляр считается как 1 × 1, иначе результат — #VALUE!
• ошибочное значение внутри любого аргумента возвращается как результат
• текстовые и логические элементы считаются нулями, так что (B1:B2>0)*1 или -- всё ещё нужны, чтобы превратить TRUE в 1
• аргументы, сплошь являющиеся обычными диапазонами, сохраняют исходный потоковый цикл, так что большие диапазоны не материализуются в массивы

Семейство SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) использует тот же поэлементный вычислитель, когда аргумент — выражение с оператором над диапазоном, так что =SUM((B1:B2>0)*1) считает обе строки, а не смотрит только на первую ячейку. v2.384.62 научил оператор пересечения пробелом возвращать общий прямоугольник двух ссылок, а при отсутствии перекрытия — #NULL!, так что =SUM(A1:B2 B1:B2) даёт 6, а не 2, и результат можно подать в параметры-ссылки вроде ROWS и INDEX. v2.384.63 добавил парсеру встроенные константы массивов вроде {1,2;3,4} (запятые разделяют столбцы, точки с запятой — строки) и объединения ссылок вроде (A1:B2,D4). Поэлементные сравнения также дают пустому элементу тип другой стороны, FALSE против логического, — в согласии со скалярным правилом из v2.384.53, описанным в статье о цепочках сравнения и пустых ячейках в HotXLS

var
  V: Variant;
begin
  // Book — это TXLSXWorkbook из первого примера;
  // его активный лист держит A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, один аргумент
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, общая часть B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, перекрытие посчитано дважды
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, до v2.384.61 было -1
end;

TXLSXWorkbook.Calculate вычисляет строку формулы на активном листе, не сохраняя её, — быстрый способ проверить поведение движка. Одно предостережение насчёт самого @: HotXLS исторически принимал @ между двумя ссылками как бинарное пересечение и теперь вычисляет эту форму с настоящей семантикой пересечения. В Excel 365 @ — унарный префикс неявного пересечения. Не пишите @ в текст формулы в надежде на смысл из Excel: для пересечения используйте пробел, а семантику динамических массивов пусть обрабатывают правила хранения выше

Почему Excel отказывался открыть файл или считал неверное значение?

Чтобы Excel принял пометку динамического массива, понадобились три правки, которые ни один тест на самопрочтение не поймал бы: HotXLS во всех случаях корректно читал собственный вывод. Каждую нашли, открывая вывод HotXLS в Excel 16 и меняя по одной переменной за раз:

1. GUID расширения обязан быть в нижнем регистре. Атрибут ext uri в xl/metadata.xml обязан быть ровно {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Старый шаблон HotXLS писал его в смешанном регистре, и Excel 16 отказывался открыть весь пакет, а не только ячейку. Книги, созданные через TXLSXRange.SetDynamicArrayFormula до v2.384.68, несли ту же проблему
2. Текст корня массива не несёт ведущего =. XLSX-писатель выкидывает сохранённый текст корня массива в <f> дословно. Сохрани конвертированная ячейка свой =, элемент читался бы как <f t="array" ref="E5">=SUM(...)</f>, и Excel отверг бы файл при открытии. HotXLS срезает его при конверсии, потому TXLSXCell.Formula и читается без него
3. Double(True) в Delphi — это -1. Конверсия Variant следует соглашению COM, где TRUE — все биты в единицу, и VarIsNumeric(True) тоже возвращает True. До v2.384.61 из-за этого =TRUE*1 возвращал -1, а логические элементы массивов классифицировались как числа, так что сравнение вроде (B1:B2>0)=TRUE шло вразнос. Теперь HotXLS проверяет varBoolean, прежде чем считать Variant числом — в скалярной арифметике, арифметике массивов и классификации элементов массивов, а TRUE считается как 1

Классы операндов BIFF8: байтовые детали для реализаторов формата

В BIFF8 каждый токен-операнд несёт свой класс операнда прямо в байте токена, и Excel доверяет этому классу больше, чем структуре формулы. [MS-XLS] определяет класс как двухбитное поле PtgDataType в битах 5 и 6 токена: 1 — ссылка, 2 — значение, 3 — массив. Пять младших бит называют токен, поэтому одна и та же ссылка на область имеет три написания:

Токен	Класс ссылки	Класс значения	Класс массива
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS ошибся в трёх из них в разных местах, и каждая ошибка давала свой симптом в Excel, тогда как сам HotXLS читал файл без проблем:

• Константы массивов в классе ссылки. Кодировщик выбирал класс по контексту, а параметры SUM или ROWS — класса ссылки, поэтому =SUM({1,2}) записывался с PtgArray как $20. Excel показывает всю формулу как =#N/A. Константа массива не может быть ссылкой никогда, так что с v2.384.63 HotXLS пишет класс массива $60 всюду, где контекст просит ссылку
• Операнды PtgIsect и PtgUnion в классе значения. Бинарные операторы брали операнды класса значения — для * это верно, для операторов ссылок нет. С областями $45 перед PtgIsect ($0F) Excel читал =SUM(A1:B2 B1:B2) как =SUM(@A1:B2 @B1:B2) и возвращал #VALUE!. С v2.384.62 операнды PtgIsect и PtgUnion ($10) пишутся в классе ссылки, $25
• Операнды класса значения внутри записи ARRAY. Excel применяет неявное пересечение даже внутри формулы массива, если операнд в классе значения. HotXLS писал там $45, и формула массива из одной ячейки для =SUM(A1:B1*{10,100}) вычислялась в Excel как 10. С v2.384.68 поток токенов записи ARRAY повышает каждую ссылку класса значения и константу массива до класса массива, $65 и $60, — ровно так пишет Excel

Читатель, игнорирующий биты класса, радостно прокручивает все три туда-обратно, так что если вы поддерживаете собственный BIFF8-писатель, сверяйте биты класса каждого токена-операнда с файлом, сохранённым Excel для той же формулы, а не только с номерами токенов

Краткая шпаргалка

• Excel 365 показывает @, когда оператор в обычной, непомеченной формуле получает диапазон из многих ячеек или встроенный массив
• HotXLS с v2.384.68 хранит такие формулы как динамические массивы XLSX в одной ячейке (cm="1", t="array", метаданные XLDAPR) и как формулы массива XLS из одной ячейки (FORMULA с PtgExp плюс ARRAY $0221)
• В счёт идут только операнды операторов; диапазон, переданный прямо в аргумент функции, остаётся обычной формулой
• Помечаются только формулы, введённые через TXLSXCell.Formula или классическое Formula / Value одиночной ячейки; загруженные формулы не трогаются
• Конвертированная корневая ячейка читается назад без ведущего =
• GUID ext uri динамического массива обязан быть в нижнем регистре, иначе Excel отвергнет пакет
• В Delphi Double(True) — это -1; проверяйте varBoolean перед числовой конверсией
• BIFF8: константы массивов никогда не в классе ссылки, операнды PtgIsect / PtgUnion — в классе ссылки, операнды записи ARRAY — в классе массива

HotXLS читает, пишет и вычисляет книги XLS и XLSX нативно из Delphi и C++Builder и хранит формулы с операторами массивов так, что Excel 365 открывает их со значениями, которые посчитал HotXLS. Издания, документация и пробная загрузка — на странице HotXLS Delphi spreadsheet component