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 | Excel 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) | 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:
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помечаются, где бы они ни стояли в формуле, включая SUMPRODUCTSUM(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 и меняете по одной переменной за раз:
- GUID расширения обязан быть в нижнем регистре. Атрибут
ext uriвxl/metadata.xmlобязан быть ровно{bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Напишите его в смешанном регистре, и Excel 16 откажется открыть весь пакет, а не только ячейку - Текст корня массива не несёт ведущего
=. XLSX-писатель выкидывает сохранённый текст корня массива в<f>дословно. Сохрани конвертированная ячейка свой=, элемент читался бы как<f t="array" ref="E5">=SUM(...)</f>, и Excel отверг бы файл при открытии. HotXLS срезает его при конверсии, потомуTXLSXCell.Formulaи читается без него 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-писатель, сверяйте биты класса каждого токена-операнда с файлом, сохранённым 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