Array-формули HotXLS: чому Excel додає @ і #VALUE!

Excel 365 вставляє @ у формулу на кшталт =SUM(A1:B1*{10,100}) і показує #VALUE!, коли файл зберігає її як звичайну формулу, бо тоді Excel застосовує legacy implicit intersection до кожного операнда оператора. Від v2.384.68 HotXLS Delphi Component зберігає такі формули з array operators так, як це робить Excel 365: як single-cell dynamic-array формули в XLSX і як one-cell array формули в XLS

Симптом переживає code review. Ваш Delphi-сервіс пише workbook, HotXLS перераховує його і кешує 210 для =SUM(A1:B1*{10,100}), а клієнт відкриває файл у Excel 16 і бачить =SUM(@A1:B1*@{10,100}) у formula bar та #VALUE! у клітинці. У файлі немає нічого пошкодженого. Бракує лише metadata, яка казала б Excel, що формулу написано за правилами dynamic array, а без неї Excel відкатується до своєї до-dynamic-array моделі обчислення

Чому Excel 365 додає @ у формулу, яку HotXLS обчислив правильно?

Excel 365 додає @, бо формула без dynamic-array позначки за визначенням є legacy формулою, а legacy формули згортають multi-cell range до однієї клітинки щоразу, коли оператор очікує одне значення. Це згортання і є implicit intersection: Excel бере ту клітинку range, що лежить у рядку формули (для вертикального range) чи в її стовпці (для горизонтального), а якщо такої клітинки немає, результат — #VALUE!. Excel 365 зберігає цей зміст для old-style формул і показує @, щоб згортання було видимим

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

Formula	Результат HotXLS	Excel 16, збережено як plain formula	Зберігається від v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynamic array, Excel показує 210
=SUM((A1:B2>2)*1)	2	Implicit intersection, неправильно чи error	Dynamic array, Excel показує 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implicit intersection, неправильно чи error	Dynamic array, Excel показує 2
=MAX(A1:B2-1)	3	Implicit intersection, неправильно чи error	Dynamic array, Excel показує 3
=SUM(A1:B2)	10	10	Plain formula, без змін

Останній рядок важить не менше за перші чотири. SUM(A1:B2) передає range прямо в parameter функції, що приймає references, тож жоден оператор не бачить multi-cell range і жоден intersection статися не може. Excel 365 сам зберігає таку формулу як plain formula, і HotXLS робить те саме

Як HotXLS зберігає формули з array operators у XLSX та XLS

HotXLS пише формулу з array operator в XLSX як single-cell dynamic array: елемент <c> несе cm="1", формула — це <f t="array" ref="E5">, а пакет отримує xl/metadata.xml із metadata типом XLDAPR, чиє розширення тримає dynamicArrayProperties fDynamic="1". Атрибут cm — це індекс з одиниці в блок cellMetadata тієї частини, а запис XLDAPR за ним — саме те, що каже Excel «обчислюй це за правилами dynamic array». Це та сама структура, яку Excel 16 пише, коли ви вводите ту саму формулу і зберігаєте файл, — саме так і було встановлено це цільове розташування

У XLS немає metadata-частини, тож HotXLS використовує єдину конструкцію, яку BIFF8 має для array обчислення: one-cell array формулу. Клітинка отримує запис FORMULA, чиїм token stream є один PtgExp, що вказує саму на себе, за яким іде запис ARRAY ($0221) зі справжньою розпарсеною формулою над one-cell range. Excel 365 пише dynamic-array формули в XLS так само, а старіша версія Excel, читаючи файл, бачить класичну array формулу під Ctrl+Shift+Enter

Жодного нового API. Позначка ставиться, коли ви присвоюєте формулу через звичайний cell API, в обох engines. З боку 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;

    // Оператор над range чи inline array: зберігається як dynamic array
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Range передано прямо у функцію: лишається звичайним <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Корінь array тримає текст без початкового '='
    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, тож код, що порівнює рядки формул після присвоєння, має нормалізувати початковий =

Classic engine дотримується того самого правила через IXLSRange.Formula на одній клітинці. Присвоєння формули внутрішньо перенаправляє її на one-cell array шлях, тож збережений 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;

Якщо ви якорите multi-cell результат, а не скалярний агрегат, явні API все ще правильний інструмент: SetArrayFormula для наперед розміряного прямокутника, як описано в dynamic array spill формулах із HotXLS, чи TXLSXRange.SetDynamicArrayFormula, коли вам потрібна XLSX dynamic-array позначка на range, який ви розміряєте самі. Автоматичний шлях із цієї статті покриває лише формули, введені в одну клітинку

Які формули HotXLS позначає як dynamic arrays?

HotXLS позначає формулу лише тоді, коли в оператора є піддерево операнда, що продукує array. Перевірка йде скомпільованим syntax tree, і операнд продукує array, якщо він є multi-cell range, inline array константою чи іншим operator-виразом, який сам має такий операнд. Дужки прозорі. Оператори, що рахуються, — арифметичні (+ - * / ^), конкатенація (&), шість порівнянь, унарні плюс і мінус та percent:

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

Три межі намірені. По-перше, позначка ставиться лише тоді, коли формулу введено через API, тобто TXLSXCell.Formula в XLSX engine і single-cell присвоєння Formula чи Value в classic engine. Формули, завантажені з файлу, записуються назад рівно такими, якими були знайдені, адже legacy формула від іншого виробника може навмисно покладатися на implicit intersection. По-друге, текст без : і без { пропускається без повторного компілювання. По-третє, формула, що spill-нула б, як-от =A1:B1*2 сама по собі, позначається як single-cell dynamic array, закорений там, куди ви її поклали. HotXLS не spill-ить її, і Excel розширить результат на сусідні клітинки, наступного разу коли перераховувати

Це правило операндів — sibling правила класів аргументів, описаного в implicit intersection для defined names у HotXLS. Та стаття — про parameters функцій, оголошені як value class; ця — про оператори, які в legacy моделі завжди вимагають значень

Що змінилося в calculation engine, щоб результати збіглися

Storage fix у v2.384.68 спирається на те, що formula engine HotXLS уже повертав значення Excel 365, а на це пішло кілька попередніх fixes в обох engines. Найпомітніший — SUMPRODUCT: до v2.384.61 він приймав лише два чи більше plain ranges, тож SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) і навіть single-аргументний SUMPRODUCT(B1:B2) повертали #N/A. Тепер HotXLS обчислює expression-аргументи поелементно за правилами Excel:

• кожен аргумент має мати точно таку саму форму, скаляр рахується як 1 × 1, інакше результат — #VALUE!
• error-значення всередині будь-якого аргументу повертається як результат
• текстові та логічні елементи рахуються як 0, тож (B1:B2>0)*1 чи -- все ще потрібні, щоб перетворити TRUE на 1
• аргументи, що всі є plain ranges, тримають початковий streaming loop, тож великі ranges не материалізуються в arrays

Родина SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) вживає той самий поелементний evaluator, коли аргумент — operator-вираз над range, тож =SUM((B1:B2>0)*1) рахує обидва рядки, а не дивиться лише на першу клітинку. v2.384.62 зробив так, що оператор intersection через пробіл повертає спільний прямокутник двох references, з #NULL!, коли вони не перетинаються, тож =SUM(A1:B2 B1:B2) — це 6, а не 2, і результат може живити reference-параметри на кшталт ROWS та INDEX. v2.384.63 додав у parser inline array константи на кшталт {1,2;3,4} (коми розділяють стовпці, крапки з комою — рядки) і reference unions на кшталт (A1:B2,D4). Поелементні порівняння також дають порожньому елементу тип другого боку, FALSE проти логічного, в унісон із scalar-правилом з v2.384.53, описаним у ланцюжках порівнянь і порожніх клітинках HotXLS

var
  V: Variant;
begin
  // Book — це TXLSXWorkbook з першого прикладу;
  // його активний sheet тримає 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, спільний range 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 обчислює рядок формули проти активного sheet, не зберігаючи його, — швидкий спосіб перевірити поведінку engine. Одне застереження щодо самого @: HotXLS історично приймав @ між двома references як бінарний intersection, і тепер він обчислює ту форму зі справжньою intersection семантикою. В Excel 365 @ — це унарний implicit-intersection префікс. Не пишіть @ у текст формули в надії на значення Excel; для intersection використовуйте пробіл, а dynamic-array семантику нехай обробляють правила зберігання вище

Чому Excel відмовлявся відкрити файл чи рахував неправильне значення?

Щоб Excel прийняв dynamic-array позначку, знадобилися три fixes, які жоден self-round-trip тест не зловив би, адже HotXLS у кожному випадку правильно читав власний вивід. Кожен знайшли, відкриваючи вивід HotXLS в Excel 16 і змінюючи по одній змінній за раз:

1. GUID розширення мусить бути весь у нижньому регістрі. ext uri в xl/metadata.xml має бути точно {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Старіший шаблон HotXLS писав його у змішаному регістрі, і Excel 16 відмовлявся відкрити весь пакет, а не лише клітинку. Workbooks, створені через TXLSXRange.SetDynamicArrayFormula до v2.384.68, мали ту саму проблему
2. Текст кореня array не несе початкового =. XLSX writer виводить збережений текст кореня array дослівно в <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 і дозволяло класифікувати логічні array елементи як числа, тож порівняння на кшталт (B1:B2>0)=TRUE ішло неправильно. Тепер HotXLS тестує varBoolean, перш ніж рахувати Variant числом у scalar арифметиці, array арифметиці та класифікації array елементів, і TRUE рахується як 1

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

У BIFF8 кожен operand token несе свій operand class у самому token byte, і Excel довіряє тому класу більше, ніж структурі формули. [MS-XLS] визначає клас як двобітове поле PtgDataType у бітах 5 і 6 token: 1 для reference, 2 для value, 3 для array. Молодші п’ять бітів називають token, тож той самий area reference має три написання:

Token	Клас Reference	Клас Value	Клас Array
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS помилився в трьох із них у різних місцях, і кожна помилка давала окремий симптом в Excel, тоді як у HotXLS читалося назад чисто:

• Array константи класу reference. Encoder обирав клас із контексту, а parameters SUM чи ROWS — reference класу, тож =SUM({1,2}) писався з PtgArray як $20. Excel показує всю формулу як =#N/A. Array константа ніколи не може бути reference, тож від v2.384.63 HotXLS пише array клас $60, де б контекст ні вимагав reference
• Операнди PtgIsect і PtgUnion класу value. Бінарні оператори брали операнди класу value, що правильно для *, але неправильно для reference операторів. З areas $45 перед PtgIsect ($0F) Excel читав =SUM(A1:B2 B1:B2) як =SUM(@A1:B2 @B1:B2) і повертав #VALUE!. Від v2.384.62 операнди PtgIsect і PtgUnion ($10) пишуться в класі reference, $25
• Операнди класу value всередині запису ARRAY. Excel застосовує implicit intersection навіть всередині array формули, коли операнд — класу value. HotXLS писав там $45, тож one-cell array формула для =SUM(A1:B1*{10,100}) обчислювалася в Excel у 10. Від v2.384.68 token stream запису ARRAY підвищує кожен value-class reference і array константу до класу array, $65 і $60, — саме те, що пише Excel

Reader, що ігнорує біти класу, round-trip-ить усі три без проблем, тож якщо ви підтримуєте власний BIFF8 writer, порівнюйте біти класу кожного operand token із файлом, збереженим Excel для тієї самої формули, а не лише з номерами token

Швидка довідка

• Excel 365 показує @, коли оператор у plain, непозначеній формулі отримує multi-cell range чи inline array
• HotXLS v2.384.68 і новіші зберігає такі формули як XLSX single-cell dynamic arrays (cm="1", t="array", metadata XLDAPR) і як XLS one-cell array формули (FORMULA з PtgExp плюс ARRAY $0221)
• Рахуються лише операнди операторів; range, переданий прямо в аргумент функції, лишається plain формулою
• Позначаються лише формули, введені через TXLSXCell.Formula чи classic single-cell Formula / Value; завантажені формули не чіпаються
• Конвертована коренева клітинка читається назад без початкового =
• GUID ext uri для dynamic array мусить бути в нижньому регістрі, інакше Excel відхиляє пакет
• У Delphi Double(True) — це -1; тестуйте varBoolean перед числовою конвертацією
• BIFF8: array константи — ніколи клас reference, операнди PtgIsect / PtgUnion — у класі reference, операнди запису ARRAY — у класі array

HotXLS читає, пише й обчислює XLS та XLSX workbooks нативно з Delphi і C++Builder і зберігає формули з array operators так, щоб Excel 365 відкривав їх зі значеннями, які порахував HotXLS. Видання, документацію та пробне завантаження шукайте на сторінці HotXLS Delphi spreadsheet component