Maticové vzorce HotXLS: proč Excel přidává @ a #VALUE!

Excel 365 vloží do vzorce jako =SUM(A1:B1*{10,100}) znak @ a zobrazí #VALUE!, když ho soubor ukládá jako obyčejný vzorec, protože Excel pak na každý operand operátora aplikuje staré implicitní průniky. Od v2.384.68 ukládá HotXLS Delphi Component tyhle vzorce s operátory nad poli stejně jako Excel 365: v XLSX jako dynamická pole v jedné buňce a v XLS jako maticové vzorce přes jednu buňku

Tuhle vadu neprozradí code review. Služba v Delphi sešit zapíše, HotXLS ho přepočítá a pro =SUM(A1:B1*{10,100}) uloží do mezipaměti 210 a zákazník soubor otevře v Excelu 16, kde v liště vzorců najde =SUM(@A1:B1*@{10,100}) a v buňce #VALUE!. V souboru není nic poškozeného. Chybí metadata, která Excelu říkají, že vzorec vznikl podle pravidel dynamických polí, a bez nich Excel spadne zpátky do vyhodnocovacího modelu z doby před dynamickými poli

Proč Excel 365 přidává @ do vzorce, který HotXLS spočítal správně?

Excel 365 přidává @ proto, že vzorec bez označení dynamického pole je z definice starý vzorec a staré vzorce redukují vícebuněčnou oblast na jednu buňku všude tam, kde operátor čeká jedinou hodnotu. Ta redukce je implicitní průnik: Excel vezme buňku oblasti, která sdílí řádek vzorce (u svislé oblasti), případně sloupec (u vodorovné), a pokud taková buňka není, výsledkem je #VALUE!. Pro staré vzorce si Excel 365 tenhle význam nechává a @ zobrazuje, aby redukce byla vidět

Dejte =SUM(A1:B1*{10,100}) do E5 a staré čtení je najednou zjevné. A1:B1 je vodorovná oblast, vzorec sedí ve sloupci E, oblast nemá ve sloupci E žádnou buňku, takže @A1:B1 dává #VALUE! a celé SUM to zdědí. Podle pravidel dynamických polí ten samý text násobí prvek po prvku, 1 × 10 + 2 × 100, a vrátí 210. Vzorcové jádro HotXLS hodnotí po způsobu dynamických polí už od vydání v2.384.61 a v2.384.63; formát souboru o tom prostě nic neříkal. Když A1:B2 drží 1, 2, 3 a 4, tohle jsou testovací vzorce a to, co zobrazí Excel 16:

Vzorec	Výsledek HotXLS	Excel 16, uloženo jako obyčejný vzorec	Uloženo od v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynamické pole, Excel ukazuje 210
=SUM((A1:B2>2)*1)	2	Implicitní průnik, špatně nebo chyba	Dynamické pole, Excel ukazuje 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implicitní průnik, špatně nebo chyba	Dynamické pole, Excel ukazuje 2
=MAX(A1:B2-1)	3	Implicitní průnik, špatně nebo chyba	Dynamické pole, Excel ukazuje 3
=SUM(A1:B2)	10	10	Obyčejný vzorec, beze změny

Poslední řádek znamená stejně moc jako první čtyři. SUM(A1:B2) předává oblast rovnou do parametru funkce, který přijímá reference, takže žádný operátor nikdy nevidí vícebuněčnou oblast a žádný průnik nemůže nastat. Sám Excel 365 tenhle vzorec ukládá jako obyčejný vzorec a HotXLS dělá totéž

Jak HotXLS ukládá vzorce s operátory nad poli v XLSX a XLS

V XLSX zapisuje HotXLS vzorec s operátorem nad polem jako dynamické pole v jedné buňce: prvek <c> nese cm="1", vzorec je <f t="array" ref="E5"> a balíček dostane xl/metadata.xml s typem metadat XLDAPR, jehož rozšíření drží dynamicArrayProperties fDynamic="1". Atribut cm je index od jedničky do bloku cellMetadata této části a záznam XLDAPR za ním je to, co Excelu říká „vyhodnoť tohle podle pravidel dynamických polí“. Je to tatáž struktura, kterou zapíše Excel 16, když stejný vzorec napíšete a uložíte — takhle jsme cílové rozložení vůbec zjistili

V XLS žádná metadata část nejsou, takže HotXLS použije jedinou konstrukci, kterou BIFF8 pro maticové vyhodnocení má: maticový vzorec přes jednu buňku. Buňka dostane záznam FORMULA, jehož token stream je jediný PtgExp ukazující sám na sebe, za ním následuje záznam ARRAY ($0221) nesoucí skutečný rozparsovaný vzorec nad jednobuněčnou oblastí. Excel 365 zapisuje vzorce dynamických polí do XLS stejně a starší verze Excelu vidí při čtení klasický maticový vzorec přes Ctrl+Shift+Enter

Nehraje žádné nové API. Označení proběhne, když vzorec přiřadíte přes běžné buněčné API, v obou jádrech. Na straně XLSX je to 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;

    // Operátor nad oblastí nebo vloženým polem: uloží se jako dynamické pole
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Oblast předaná rovnou funkci: zůstane obyčejný <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Kořen pole si ponechá text bez úvodního '='
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 a E6 dostanou cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

Po konverzi vrací TXLSXCell.Formula text bez =, ve stejném tvaru, jaký ukládá TXLSXRange.SetDynamicArrayFormula, takže kód porovnávající po přiřazení texty vzorců by měl úvodní = normalizovat

Klasické jádro drží stejné pravidlo přes IXLSRange.Formula na jedné buňce. Přiřazení vzorce ho uvnitř přesměruje na jednobuněčnou maticovou cestu, takže uložené XLS obsahuje pár FORMULA plus 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})';  // záznam ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // záznam ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // obyčejný FORMULA

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

Kotvíte-li spíš výsledek přes více buněk než skalární agregát, správným nástrojem zůstávají explicitní API: SetArrayFormula pro předem stanovený obdélník, jak popisuje článek maticové vzorce s rozlitím (spill) v HotXLS, nebo TXLSXRange.SetDynamicArrayFormula, když chcete označení dynamického pole XLSX na oblasti, kterou si rozměrujete sami. Automatická cesta z tohoto článku pokrývá jen vzorce napsané do jedné buňky

Které vzorce HotXLS označí jako dynamická pole?

HotXLS označí vzorec jen tehdy, když má operátor podstrom operandu, který produkuje pole. Kontrola běží nad zkompilovaným syntaktickým stromem a operand produkuje pole, pokud je to vícebuněčná oblast, vložená konstanta pole nebo jiný operátorový výraz, který sám takový operand má. Závorky jsou průhledné. Počítají se operátory aritmetické (+ - * / ^), zřetězení (&), šest porovnání, unární plus a mínus a procenta:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) i A1:B2-1 se označí, kdekoliv ve vzorci se objeví, včetně uvnitř SUMPRODUCT
• SUM(A1:B2) a SUMPRODUCT(A1:A2,{1;10}) se neoznačí, protože oblast i pole jdou rovnou do argumentu funkce a žádný operátor se jich nedotkne
• A1*2 nebo SUM(A1,B1)*2 se neoznačí: jednobuněčné reference a výsledky funkcí jsou pro tuhle kontrolu skaláry

Tři hranice jsou záměrné. Za prvé, označení nastane jen když se vzorec zadává přes API, tedy TXLSXCell.Formula v XLSX jádře a jednobuněčné přiřazení Formula nebo Value v klasickém. Vzorce načtené ze souboru se zapisují zpátky úplně tak, jak byly nalezeny, protože starý vzorec od jiného producenta může na implicitním průniku úmyselně záviset. Za druhé, text bez : i bez { se přeskočí bez druhého překladu. Za třetí, vzorec, který by se rozlil, jako =A1:B1*2 samotný, se označí jako dynamické pole jedné buňky ukotvené tam, kam jste ho dali. HotXLS ho nerozlije a Excel výsledek příště při přepočtu rozšíří do sousedních buněk

Tohle pravidlo operandu je sourozenec pravidla tříd argumentů popsaného v článku implicitní průnik u definovaných názvů v HotXLS. Tam jde o parametry funkcí deklarované jako value class, tady o operátory, které ve starém modelu požadují vždycky hodnoty

Co se změnilo ve výpočetním jádru, aby výsledky seděly

Oprava ukládání ve v2.384.68 stojí na tom, že vzorcové jádro HotXLS už vrací hodnoty Excelu 365, což si vyžádalo několik dřívějších oprav v obou jádrech. Nejviditelnější byl SUMPRODUCT: do v2.384.61 přijímal jen dvě a více obyčejných oblastí, takže SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) a dokonce i jednoargumentový SUMPRODUCT(B1:B2) vracely #N/A. HotXLS teď vyhodnocuje výrazové argumenty prvek po prvku podle pravidel Excelu:

• každý argument musí mít úplně stejný tvar, skalár se počítá jako 1 × 1, jinak je výsledkem #VALUE!
• chybová hodnota uvnitř kteréhokoliv argumentu se vrátí jako výsledek
• textové a logické prvky se počítají jako 0, takže (B1:B2>0)*1 nebo -- je pořád potřeba, aby se z TRUE stala jednička
• argumenty, které jsou všechny obyčejné oblasti, si nechávají původní streamovací smyčku, takže velké oblasti se nematerializují do polí

Rodina SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) používá stejné vyhodnocování po elementech, když je argumentem operátorový výraz nad oblastí, takže =SUM((B1:B2>0)*1) sečte oba řádky místo aby se dívala jen na první buňku. v2.384.62 naučila operátor průniku mezerou vracet společný obdélník dvou referencí, s #NULL! když se nepřekrývají, takže =SUM(A1:B2 B1:B2) dá 6 a ne 2 a výsledek může krmit referenční parametry jako ROWS a INDEX. v2.384.63 přidala parseru vložené konstanty pole jako {1,2;3,4} (čárky oddělují sloupce, středníky řádky) a sjednocení referencí jako (A1:B2,D4). Porovnání po elementech dávají navíc prázdnému prvku typ druhé strany, FALSE proti logické hodnotě, v souladu se skalárním pravidlem z v2.384.53 popsaným v článku porovnávací řetězce a prázdné buňky v HotXLS

var
  V: Variant;
begin
  // Book je TXLSXWorkbook z prvního příkladu;
  // jeho aktivní list drží A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, jediný argument
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, společná oblast B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, překryv se počítá dvakrát
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, před v2.384.61 to bylo -1
end;

TXLSXWorkbook.Calculate vyhodnotí text vzorce nad aktivním listem bez ukládání, rychlý způsob, jak vyzkoušet chování jádra. Jedna výhrada k samotnému @: HotXLS historicky přijímal @ mezi dvěma referencemi jako binární průnik a teď tuhle formu vyhodnocuje se skutečnou sémantikou průniku. V Excelu 365 je @ unární prefix implicitního průniku. Nepište @ do textu vzorce a nečekejte význam Excelu; pro průnik použijte mezeru a sémantiku dynamických polí nechte na uvedených ukládacích pravidlech

Proč Excel odmítl soubor otevřít nebo spočítal špatnou hodnotu?

Přimět Excel přijmout označení dynamického pole stálo tři opravy, které by žádný test vlastního round-tripu neodhalil, protože HotXLS čte vlastní výstup v každém případě správně. Každou odhalilo otevření výstupu HotXLS v Excelu 16 a výměna jedné proměnné po druhé:

1. GUID rozšíření musí být celé malými písmeny. ext uri v xl/metadata.xml musí být přesně {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Starší šablona HotXLS to měla smíšenými písmeny a Excel 16 odmítl otevřít celý balíček, ne jen tu buňku. Sešity vytvořené přes TXLSXRange.SetDynamicArrayFormula před v2.384.68 měly tentýž problém
2. Text kořene pole nezačíná =. XLSX zapisovač vypíše uložený text kořene pole slovo od slova do <f>. Kdyby konvertovaná buňka své = nechala, prvek by zněl <f t="array" ref="E5">=SUM(...)</f>, což Excel při otevírání také odmítne. HotXLS ho při konverzi odstraňuje, a proto ho TXLSXCell.Formula čte zpátky bez něj
3. Double(True) je v Delphi -1. Konverze Variant se řídí konvencí COM, kde TRUE znamená všechny bity nastavené, a VarIsNumeric(True) rovněž vrací True. Před v2.384.61 z toho =TRUE*1 vracelo -1 a logické prvky pole se mohly klasifikovat jako čísla, takže porovnání jako (B1:B2>0)=TRUE vyšlo špatně. HotXLS teď testuje varBoolean, než s Variantem pracuje jako s číslem ve skalární i maticové aritmetice a při klasifikaci prvků pole, a TRUE se počítá jako 1

Třídy operandů v BIFF8: bajtové detaily pro implementátory formátu

V BIFF8 nese každý operandový token svou třídu operandu přímo v bajtu tokenu a Excel té třídě věří víc než struktuře vzorce. [MS-XLS] definuje třídu jako dvoubitové pole PtgDataType v bitech 5 a 6 tokenu: 1 pro referenci, 2 pro hodnotu, 3 pro pole. Spodních pět bitů pojmenovává token, takže tatáž referenční oblast má tři hláskování:

Token	Třída reference	Třída hodnoty	Třída pole
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS měl tři z nich špatně na různých místech a každá chyba dávala v Excelu jiný příznak, zatímco zpětné čtení v HotXLS bylo v pořádku:

• Konstanty pole ve třídě reference. Kodér volil třídu podle kontextu a parametry SUM či ROWS jsou třídy reference, takže =SUM({1,2}) se zapsalo s PtgArray jako $20. Excel zobrazí celý vzorec jako =#N/A. Konstanta pole nikdy nemůže být reference, takže od v2.384.63 zapisuje HotXLS třídu pole $60, kdekoliv kontext žádá referenci
• Operandy PtgIsect a PtgUnion ve třídě hodnoty. Binární operátory braly operandy třídy hodnoty, což sedí u *, ale u referenčních operátorů je to špatně. S oblastmi $45 před PtgIsect ($0F) četl Excel =SUM(A1:B2 B1:B2) jako =SUM(@A1:B2 @B1:B2) a vracel #VALUE!. Od v2.384.62 se operandy PtgIsect a PtgUnion ($10) zapisují ve třídě reference, $25
• Operandy ve třídě hodnoty uvnitř záznamu ARRAY. Implicitní průnik aplikuje Excel i uvnitř maticového vzorce, když je operand třídy hodnoty. HotXLS tam psal $45, takže maticový vzorec jedné buňky pro =SUM(A1:B1*{10,100}) vyšel v Excelu 10. Od v2.384.68 token stream záznamu ARRAY povyšuje každou referenci třídy hodnoty i konstantu pole na třídu pole, $65 a $60, což je přesně to, co zapisuje Excel

Čtečka, která bity třídy ignoruje, přežije round-trip všech tří bez mrknutí, takže pokud udržujete vlastní BIFF8 zapisovač, porovnávejte bity třídy každého operandového tokenu se souborem uloženým Excelem u téhož vzorce, ne jen čísla tokenů

Rychlý přehled

• Excel 365 zobrazí @, když operátor v obyčejném neoznačeném vzorci dostane vícebuněčnou oblast nebo vložené pole
• HotXLS v2.384.68 a novější ukládá takové vzorce jako dynamická pole jedné buňky v XLSX (cm="1", t="array", metadata XLDAPR) a jako maticové vzorce jedné buňky v XLS (FORMULA s PtgExp plus ARRAY $0221)
• Počítají se jen operandy operátorů; oblast předaná rovnou do argumentu funkce zůstává obyčejným vzorcem
• Označí se jen vzorce zadané přes TXLSXCell.Formula nebo klasické jednobuněčné Formula / Value; načtené vzorce zůstávají nedotčené
• Konvertovaná kořenová buňka se čte zpátky bez úvodního =
• GUID ext uri dynamického pole musí být malými písmeny, jinak balíček Excel odmítne
• V Delphi je Double(True) -1; před číselnou konverzí testujte varBoolean
• BIFF8: konstanty pole nikdy třídu reference, operandy PtgIsect / PtgUnion ve třídě reference, operandy záznamu ARRAY ve třídě pole

HotXLS čte, zapisuje a počítá sešity XLS a XLSX nativně z Delphi a C++Builderu a ukládá vzorce s operátory nad poli tak, aby je Excel 365 otevřel s hodnotami, které spočítal HotXLS. Edice, dokumentaci a zkušební verzi najdete na stránce tabulková komponenta HotXLS pro Delphi