Техническая статья

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

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

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

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

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 считает по правилам динамических массивов, и формат файла тоже обязан об этом сказать. Если в A1:B2 лежат 1, 2, 3 и 4, вот пробные формулы и то, что показывает Excel 16:

Схема HotXLS, сравнивающая неявное пересечение и вычисление динамического массива для SUM(A1:B1*{10,100}) в ячейке E5: легаси-модель не находит в столбце E ни одной ячейки горизонтального диапазона A1:B1 и возвращает #VALUE!, а модель динамического массива перемножает 1 на 10 и 2 на 100 и возвращает 210
Excel вставляет @ в обычную формулу и показывает #VALUE!, потому что неявное пересечение не находит в столбце E ничего; с пометкой динамического массива от HotXLS та же формула перемножает поэлементно и приходит к 210
ФормулаРезультат HotXLSExcel 16 при хранении как обычной формулыХранение HotXLS
=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)1010Обычная формула, без изменений

Последняя строка важна не меньше первых четырёх. 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

Схема хранения HotXLS для формулы с оператором массива SUM(A1:B1*{10,100}): движок XLSX записывает динамический массив в одной ячейке с cm равным 1, элементом f типа array и записью XLDAPR в xl/metadata.xml, где GUID обязан быть в нижнем регистре, а движок XLS пишет запись FORMULA с PtgExp плюс запись ARRAY 0221
Движок XLSX помечает ячейку через cm=1 и запись метаданных XLDAPR, а классический движок соединяет FORMULA с PtgExp и запись ARRAY над одной ячейкой; Excel 365 сохраняет динамические массивы в XLS точно так же

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

uses
  SysUtils, Variants, lxCalc, lxHandle, lxHandleX;   // lxOk объявлен в lxCalc

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; эта — об операторах, которые в легаси-модели всегда требуют значений

Как вычислительный движок сходится со значениями Excel 365

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

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

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

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

Три детали, которые Excel проверяет, открывая вывод динамических массивов

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

  1. GUID расширения обязан быть в нижнем регистре. Атрибут ext uri в xl/metadata.xml обязан быть ровно {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Напишите его в смешанном регистре, и Excel 16 откажется открыть весь пакет, а не только ячейку
  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. Наивная конверсия поэтому заставляет =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

Три места в BIFF8-писателе легко написать неверно, и каждое даёт свой симптом в Excel, тогда как собственный читатель писателя читает файл без проблем:

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

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

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

  • Excel 365 показывает @, когда оператор в обычной, непомеченной формуле получает диапазон из многих ячеек или встроенный массив
  • HotXLS хранит такие формулы как динамические массивы 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