Array формули в HotXLS: защо Excel добавя @ и #VALUE!

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

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

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

Excel 365 добавя @, защото формула без маркировка за динамичен масив е по дефиниция legacy формула, а legacy формулите свиват многоклетъчен диапазон до една клетка навсякъде, където оператор очаква единична стойност. Това свиване е неявното пресичане: Excel взема клетката на диапазона, която споделя реда на формулата (при вертикален диапазон) или колоната (при хоризонтален), а ако такава клетка няма, резултатът е #VALUE!. Excel 365 пази това значение за формулите в стар стил и показва @, за да направи свиването видимо

Сложете =SUM(A1:B1*{10,100}) в E5 и legacy прочитането става очевидно. 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 съхранява формулите с array оператори в XLSX и XLS

HotXLS записва формула с array оператор в XLSX като динамичен масив в една клетка: елементът <c> носи cm="1", формулата е <f t="array" ref="E5">, а пакетът добива xl/metadata.xml с metadata тип XLDAPR, чието разширение държи dynamicArrayProperties fDynamic="1". Атрибутът cm е индекс от едно в блока cellMetadata на тази част, а записът XLDAPR зад него е това, което казва на Excel „изчислявай това по правилата на динамичните масиви“. Това е същата структура, която Excel 16 записва, когато напишете същата формула и запазите — така изобщо беше установен целевият формат

В XLS няма metadata част, затова 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;

    // Оператор върху диапазон или inline масив: записва се като динамичен масив
    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 на една клетка. Задаването на формулата я пренасочва вътрешно към едноклетъчния 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;

Ако закачате за много клетки, а не скаларен сбор, явните API си остават правилният инструмент: SetArrayFormula за предварително оразмерен правоъгълник, както е описано в spill формулите на динамичните масиви с HotXLS, или TXLSXRange.SetDynamicArrayFormula, когато искате XLSX маркировката за динамичен масив върху диапазон, който сами оразмерявате. Автоматичният път в тази статия покрива само формули, написани в една клетка

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

HotXLS маркира формула само когато оператор има поддърво от операнди, което произвежда масив. Проверката минава по компилираното синтактично дърво, а операнд произвежда масив, ако е многоклетъчен диапазон, inline array константа или друг операторен израз, който сам има такъв операнд. Скобите са прозрачни. Операторите, които броят, са аритметичните (+ - * / ^), конкатенацията (&), шестте сравнения, унарният плюс и минус и процентът:

• 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 на една клетка в класическия. Формулите, заредени от файл, се записват обратно точно както са били намерени, защото legacy формула от друг производител може нарочно да разчита на неявно пресичане. Второ, текст без нито :, нито { се прескача без второ компилиране. Трето, формула, която би дала spill, като =A1:B1*2 сама по себе си, се маркира като динамичен масив в една клетка, закачен там, където сте я сложили. HotXLS не прави spill, а Excel ще разшири резултата към съседните клетки при следващото преизчисление

Това правило за операнди е брат на правилото за клас на аргумента, разгледано в неявното пресичане за defined names в HotXLS. Онази статия е за параметри на функции, декларирани като value клас; тази е за операторите, които в legacy модела винаги искат стойности

Какво се промени в изчислителния двигател, за да съвпаднат резултатите

Поправката на съхранението в 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!
• грешкова стойност вътре в произволен аргумент се връща като резултат
• текстовите и логическите елементи се броят за 0, така че пак трябва (B1:B2>0)*1 или --, за да стане TRUE на 1
• аргументите, които са всички обикновени диапазони, пазят оригиналния streaming цикъл, така че големите диапазони не се материализират като масиви

Семейството 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 добави към парсера inline array константи като {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, беше -1 преди v2.384.61
end;

TXLSXWorkbook.Calculate изчислява низ с формула върху активния лист, без да го съхранява — бърз начин да проверите поведението на двигателя. Едно предупреждение за самия @: HotXLS исторически приема @ между две референции като двоично пресичане и вече изчислява тази форма с истинска семантика на пресичане. В Excel 365 @ е унарен префикс за неявно пресичане. Не пишете @ в текста на формула и не очаквате значението на Excel; ползвайте интервал за пресичане и оставете правилата за съхранение по-горе да се погрижат за семантиката на динамичните масиви

Защо Excel отказа да отвори файла или изчисли грешна стойност?

Да накараш Excel да приеме маркировката за динамичен масив струва три поправки, които никакъв round-trip тест на собствения изход не би хванал, защото 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 writer-ът извежда съхранения текст на array корен дословно в <f>. Ако преобразуваната клетка беше запазила своя =, елементът щеше да се чете <f t="array" ref="E5">=SUM(...)</f>, което Excel също отхвърля при отваряне. HotXLS го отрязва по време на конверсията — затова TXLSXCell.Formula се чете обратно без него
3. Double(True) е -1 в Delphi. 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 за reference, 2 за value, 3 за array. Долните пет бита назовават токена, така че същата area референция има три изписвания:

Токен	Reference клас	Value клас	Array клас
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS обърка три от тях на различни места и всяка даде различен симптом в Excel, докато в HotXLS се четеше обратно без проблем:

• Array константи в reference клас. Енкодерът избираше класа от контекста, а параметрите на SUM или ROWS са reference клас, така че =SUM({1,2}) се записваше с PtgArray като $20. Excel показва цялата формула като =#N/A. Array константа никога не може да е референция, затова от v2.384.63 насам HotXLS пише array клас $60, където и контекстът да иска референция
• Операнди в value клас на PtgIsect и PtgUnion. Двоичните оператори вземаха операнди в value клас, което е правилно за *, но грешно за референтните оператори. С $45 areas пред 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 прилага неявно пресичане дори вътре в формула за масив, когато операнд е в value клас. HotXLS пишеше $45 там, така че едноклетъчната формула за масив за =SUM(A1:B1*{10,100}) се изчисляваше на 10 в Excel. От v2.384.68 насам токен потокът на ARRAY запис повишава всяка референция в value клас и всяка array константа до array клас, $65 и $60, което е точно това, което пише Excel

Четец, който игнорира битовете за клас, преминава през задруга и с трите весело, затова ако поддържате собствен BIFF8 writer, сравнявайте битовете за клас на всеки операнден токен с Excel-записан файл на същата формула, а не само номерата на токените

Бърза справка

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

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