HotXLS-Arrayformeln: Warum Excel @ und #VALUE! einfügt

Excel 365 fügt in eine Formel wie =SUM(A1:B1*{10,100}) ein @ ein und zeigt #VALUE!, wenn die Datei sie als gewöhnliche Formel ablegt, denn Excel wendet dann auf jeden Operator-Operanden die klassische implizite Schnittmenge an. Seit v2.384.68 speichert die HotXLS Delphi Component diese Array-Operator-Formeln genau wie Excel 365: als Dynamic-Array-Formeln mit einer Zelle in XLSX und als Ein-Zellen-Array-Formeln in XLS

Das Symptom übersteht jedes Code-Review. Ihr Delphi-Dienst schreibt eine Arbeitsmappe, HotXLS berechnet sie neu und cacht 210 für =SUM(A1:B1*{10,100}), und der Kunde öffnet sie in Excel 16 und findet =SUM(@A1:B1*@{10,100}) in der Bearbeitungsleiste und #VALUE! in der Zelle. An der Datei ist nichts kaputt. Was fehlt, sind die Metadaten, die Excel sagen, dass die Formel unter Dynamic-Array-Regeln geschrieben wurde, und ohne sie fällt Excel auf sein Auswertungsmodell von vor der Dynamic-Array-Ära zurück

Warum fügt Excel 365 einer von HotXLS korrekt berechneten Formel @ hinzu?

Excel 365 fügt @ ein, weil eine Formel ohne Dynamic-Array-Kennzeichnung per Definition eine klassische Formel ist, und klassische Formeln reduzieren einen mehrzelligen Bereich auf eine Zelle, wo immer ein Operator einen Einzelwert erwartet. Diese Reduktion ist die implizite Schnittmenge: Excel nimmt die Zelle des Bereichs, die dieselbe Zeile wie die Formel hat (bei einem vertikalen Bereich) oder dieselbe Spalte (bei einem horizontalen Bereich), und gibt es keine solche Zelle, ist das Ergebnis #VALUE!. Für Formeln im alten Stil behält Excel 365 diese Bedeutung bei und zeigt @ an, um die Reduktion sichtbar zu machen

Setzen Sie =SUM(A1:B1*{10,100}) nach E5, und die klassische Lesart wird offensichtlich. A1:B1 ist ein horizontaler Bereich, die Formel steht in Spalte E, der Bereich hat keine Zelle in Spalte E, also liefert @A1:B1 ein #VALUE!, und das gesamte SUM erbt das. Unter Dynamic-Array-Regeln multipliziert derselbe Text elementweise, 1 × 10 + 2 × 100, und kommt auf 210. Die Formel-Engine von HotXLS rechnet seit v2.384.61 und v2.384.63 auf diese Weise; das Dateiformat hat es nur nicht gesagt. Mit 1, 2, 3 und 4 in A1:B2 sind dies die Testformeln und das, was Excel 16 anzeigt:

Formel	HotXLS-Ergebnis	Excel 16, als einfache Formel gespeichert	Gespeichert seit v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynamisches Array, Excel zeigt 210
=SUM((A1:B2>2)*1)	2	Implizite Schnittmenge, falsch oder Fehler	Dynamisches Array, Excel zeigt 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implizite Schnittmenge, falsch oder Fehler	Dynamisches Array, Excel zeigt 2
=MAX(A1:B2-1)	3	Implizite Schnittmenge, falsch oder Fehler	Dynamisches Array, Excel zeigt 3
=SUM(A1:B2)	10	10	Einfache Formel, unverändert

Die letzte Zeile ist so wichtig wie die ersten vier. SUM(A1:B2) übergibt einen Bereich direkt an einen Funktionsparameter, der Referenzen akzeptiert, also sieht nie ein Operator einen mehrzelligen Bereich, und keine Schnittmenge kann entstehen. Excel 365 selbst speichert diese Formel als einfache Formel, und HotXLS macht es genauso

Wie HotXLS Array-Operator-Formeln in XLSX und XLS speichert

HotXLS schreibt eine Array-Operator-Formel in XLSX als dynamisches Array mit einer Zelle: Das <c>-Element trägt cm="1", die Formel ist <f t="array" ref="E5">, und das Paket bekommt xl/metadata.xml mit einem XLDAPR-Metadatentyp, dessen Erweiterung dynamicArrayProperties fDynamic="1" enthält. Das cm-Attribut ist ein 1-basierter Index in den cellMetadata-Block dieses Parts, und der XLDAPR-Record dahinter sagt Excel: „Werte dies unter Dynamic-Array-Regeln aus“. Das ist dieselbe Struktur, die Excel 16 schreibt, wenn man dieselbe Formel eintippt und speichert — so wurde das Ziellayout überhaupt erst ermittelt

In XLS gibt es keinen Metadata-Part, also greift HotXLS zum einzigen Konstrukt, das BIFF8 für die Array-Auswertung hat: der Ein-Zellen-Array-Formel. Die Zelle bekommt einen FORMULA-Record, dessen Token-Stream ein einzelnes, auf sie selbst zeigendes PtgExp ist, gefolgt von einem ARRAY-Record ($0221), der die wirklich geparste Formel über den Ein-Zellen-Bereich trägt. Excel 365 schreibt Dynamic-Array-Formeln auf dieselbe Weise nach XLS, und ein älteres Excel liest die Datei als klassische Ctrl+Shift+Enter-Array-Formel

Es ist keine neue API im Spiel. Die Kennzeichnung passiert, wenn Sie die Formel über die normale Zellen-API zuweisen, in beiden Engines. Auf der XLSX-Seite ist das 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;

    // Operator über einen Bereich oder ein Inline-Array: wird als dynamisches Array gespeichert
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Bereich direkt an eine Funktion übergeben: bleibt ein gewöhnliches <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Der Array-Root behält seinen Text ohne das führende '='
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

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

Nach der Konvertierung gibt TXLSXCell.Formula den Text ohne = zurück, dieselbe Form, die auch TXLSXRange.SetDynamicArrayFormula speichert; Code, der nach der Zuweisung Formelstrings vergleicht, sollte das führende = also normalisieren

Die klassische Engine folgt derselben Regel über IXLSRange.Formula auf einer einzelnen Zelle. Die Zuweisung leitet die Formel intern auf den Ein-Zellen-Array-Pfad um, deshalb enthält das gespeicherte XLS das Paar aus 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})';  // ARRAY-Record
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // ARRAY-Record
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // einfacher FORMULA

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

Wenn Sie ein mehrzelliges Ergebnis verankern wollen statt eines skalaren Aggregats, sind die expliziten APIs weiterhin das richtige Werkzeug: SetArrayFormula für ein vorab dimensioniertes Rechteck, wie in Dynamic-Array-Spill-Formeln mit HotXLS beschrieben, oder TXLSXRange.SetDynamicArrayFormula, wenn Sie die XLSX-Kennzeichnung als dynamisches Array auf einen selbst dimensionierten Bereich wollen. Der automatische Pfad in diesem Artikel deckt nur Formeln ab, die in eine einzige Zelle getippt werden

Welche Formeln markiert HotXLS als dynamische Arrays?

HotXLS markiert eine Formel nur, wenn ein Operator einen Operanden-Teilbaum hat, der ein Array erzeugt. Die Prüfung läuft über den kompilierten Syntaxbaum, und ein Operand erzeugt ein Array, wenn er ein mehrzelliger Bereich, eine Inline-Array-Konstante oder ein anderer Operator-Ausdruck ist, der selbst einen solchen Operanden hat. Klammern sind transparent. Zu den zählenden Operatoren gehören die arithmetischen (+ - * / ^), die Verkettung (&), die sechs Vergleiche, unäres Plus und Minus sowie Prozent:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) und A1:B2-1 werden markiert, wo immer sie in der Formel auftauchen, auch innerhalb von SUMPRODUCT
• SUM(A1:B2) und SUMPRODUCT(A1:A2,{1;10}) werden nicht markiert, weil Bereich und Array direkt als Funktionsargumente eingehen und kein Operator sie anfasst
• A1*2 oder SUM(A1,B1)*2 werden nicht markiert: Einzelzellen-Referenzen und Funktionsergebnisse sind für diese Prüfung Skalare

Drei Grenzen sind Absicht. Erstens passiert die Markierung nur, wenn eine Formel über die API eingegeben wird, also TXLSXCell.Formula in der XLSX-Engine und eine Einzelzellen-Zuweisung an Formula oder Value in der klassischen Engine. Formeln aus einer Datei werden exakt so zurückgeschrieben, wie sie vorgefunden wurden, denn eine klassische Formel eines anderen Produzenten kann absichtlich auf der impliziten Schnittmenge beruhen. Zweitens wird Text, der weder : noch { enthält, ohne zweiten Compile-Vorgang übersprungen. Drittens wird eine Formel, die spillen würde, wie =A1:B1*2 für sich allein, als dynamisches Array mit einer Zelle markiert, verankert dort, wo Sie sie hingesetzt haben. HotXLS spillt sie nicht, und Excel dehnt das Ergebnis beim nächsten Neuberechnen auf die Nachbarzellen aus

Diese Operanden-Regel ist das Geschwister der Argumentklassen-Regel aus implizite Schnittmenge für definierte Namen in HotXLS. Jener Artikel handelt von Funktionsparametern der Wertklasse; dieser hier von Operatoren, die im klassischen Modell stets Werte verlangen

Was sich in der Berechnungs-Engine geändert hat, damit die Ergebnisse übereinstimmen

Der Speicher-Fix in v2.384.68 baut darauf, dass die HotXLS-Formel-Engine bereits Excel-365-Werte liefert, was mehrere frühere Fixes in beiden Engines brauchte. Am sichtbarsten war SUMPRODUCT: Bis v2.384.61 akzeptierte es nur zwei oder mehr einfache Bereiche, daher lieferten SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) und sogar das einargumentige SUMPRODUCT(B1:B2) ein #N/A. HotXLS wertet Ausdrucks-Argumente jetzt elementweise nach Excels Regeln aus:

• jedes Argument muss exakt dieselbe Form haben, ein Skalar zählt als 1 × 1, sonst ist das Ergebnis #VALUE!
• ein Fehlerwert innerhalb irgendeines Arguments wird als Ergebnis zurückgegeben
• Text- und logische Elemente zählen als 0, daher ist (B1:B2>0)*1 oder -- weiterhin nötig, um TRUE in 1 zu verwandeln
• Argumente, die durchweg einfache Bereiche sind, behalten die ursprüngliche Streaming-Schleife, große Bereiche werden also nicht als Arrays materialisiert

Die SUM-Familie (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) benutzt denselben elementweisen Evaluator, wenn ein Argument ein Operator-Ausdruck über einen Bereich ist, also zählt =SUM((B1:B2>0)*1) beide Zeilen statt nur in die erste Zelle zu schauen. v2.384.62 brachte den Schnittmengen-Operator per Leerzeichen dazu, das gemeinsame Rechteck zweier Referenzen zurückzugeben, mit #NULL!, wenn sie sich nicht überlappen, daher ist =SUM(A1:B2 B1:B2) gleich 6 statt 2, und das Ergebnis kann Referenzparameter wie ROWS und INDEX speisen. v2.384.63 ergänzte im Parser Inline-Array-Konstanten wie {1,2;3,4} (Kommas trennen Spalten, Semikolons Zeilen) und Referenz-Vereinigungen wie (A1:B2,D4). Elementweise Vergleiche geben einem leeren Element außerdem den Typ der anderen Seite, FALSE gegenüber einem logischen Wert, passend zur Skalar-Regel aus v2.384.53, beschrieben in Vergleichsketten und leere Zellen in HotXLS

var
  V: Variant;
begin
  // Book ist das TXLSXWorkbook aus dem ersten Beispiel;
  // sein aktives Blatt enthält A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, ein einziges Argument
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, gemeinsamer Bereich B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, Überlappung doppelt gezählt
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, vor v2.384.61 war es -1
end;

TXLSXWorkbook.Calculate wertet einen Formelstring gegen das aktive Blatt aus, ohne ihn zu speichern — ein schneller Weg, das Verhalten der Engine zu prüfen. Eine Warnung zu @ selbst: HotXLS hat @ zwischen zwei Referenzen historisch als binäre Schnittmenge akzeptiert und wertet diese Form jetzt mit echter Schnittmengen-Semantik aus. In Excel 365 ist @ ein unäres Präfix für die implizite Schnittmenge. Schreiben Sie kein @ in den Formeltext und erwarten Sie nicht Excels Bedeutung; nehmen Sie für die Schnittmenge ein Leerzeichen und überlassen Sie die Dynamic-Array-Semantik den Speicherregeln von oben

Warum verweigerte Excel das Öffnen der Datei oder berechnete den falschen Wert?

Excel zur Akzeptanz der Dynamic-Array-Kennzeichnung zu bringen, kostete drei Fixes, die kein Selbst-Roundtrip-Test gefunden hätte, denn HotXLS las seine eigene Ausgabe in jedem Fall korrekt. Jeder wurde gefunden, indem man die HotXLS-Ausgabe in Excel 16 öffnete und jeweils eine Variable nach der anderen austauschte:

1. Die GUID der Erweiterung muss komplett in Kleinschreibung sein. Der ext uri in xl/metadata.xml muss exakt {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3} lauten. Eine ältere HotXLS-Vorlage schrieb ihn in gemischter Schreibweise, und Excel 16 verweigerte das Öffnen des gesamten Pakets, nicht nur der Zelle. Arbeitsmappen, die vor v2.384.68 mit TXLSXRange.SetDynamicArrayFormula erzeugt wurden, hatten dasselbe Problem
2. Der Array-Root-Text trägt kein führendes =. Der XLSX-Writer schreibt den gespeicherten Text eines Array-Roots wörtlich in <f>. Hätte die konvertierte Zelle ihr = behalten, würde das Element <f t="array" ref="E5">=SUM(...)</f> lauten, was Excel beim Öffnen ebenfalls ablehnt. HotXLS entfernt es während der Konvertierung, deshalb liest sich TXLSXCell.Formula ohne es zurück
3. Double(True) ist in Delphi -1. Die Variant-Konvertierung folgt der COM-Konvention, nach der TRUE alle Bits gesetzt hat, und auch VarIsNumeric(True) liefert True. Vor v2.384.61 ließ das =TRUE*1 -1 zurückgeben und logische Array-Elemente als Zahlen klassifizieren, wodurch ein Vergleich wie (B1:B2>0)=TRUE schiefging. HotXLS prüft jetzt auf varBoolean, bevor ein Variant in der Skalar-Arithmetik, der Array-Arithmetik und der Array-Element-Klassifizierung als Zahl behandelt wird, und TRUE zählt als 1

BIFF8-Operandenklassen: die Details auf Byte-Ebene für Format-Implementierer

In BIFF8 trägt jeder Operand-Token seine Operandenklasse direkt im Token-Byte, und Excel vertraut dieser Klasse mehr als der Struktur der Formel. [MS-XLS] definiert die Klasse als zweibitiges PtgDataType-Feld in Bit 5 und 6 des Tokens: 1 für Referenz, 2 für Wert, 3 für Array. Die unteren fünf Bits benennen den Token, dieselbe Bereichsreferenz hat also drei Schreibweisen:

Token	Referenzklasse	Wertklasse	Arrayklasse
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS hatte drei davon an verschiedenen Stellen falsch, und jedes erzeugte ein eigenes Symptom in Excel, während HotXLS es fehlerfrei zurücklas:

• Array-Konstanten in der Referenzklasse. Der Encoder wählte die Klasse aus dem Kontext, und SUM- oder ROWS-Parameter sind Referenzklasse, also wurde =SUM({1,2}) mit PtgArray als $20 geschrieben. Excel zeigt die ganze Formel als =#N/A. Eine Array-Konstante kann nie eine Referenz sein, deshalb schreibt HotXLS seit v2.384.63 die Arrayklasse $60, wo immer der Kontext eine Referenz verlangt
• Wertklassen-Operanden von PtgIsect und PtgUnion. Binäre Operatoren bekamen Wertklassen-Operanden, was für * stimmt, für die Referenzoperatoren aber falsch ist. Mit $45-Bereichen vor PtgIsect ($0F) las Excel =SUM(A1:B2 B1:B2) als =SUM(@A1:B2 @B1:B2) und lieferte #VALUE!. Seit v2.384.62 werden die Operanden von PtgIsect und PtgUnion ($10) in der Referenzklasse $25 geschrieben
• Wertklassen-Operanden innerhalb des ARRAY-Records. Excel wendet die implizite Schnittmenge sogar innerhalb einer Array-Formel an, wenn ein Operand die Wertklasse hat. HotXLS schrieb dort $45, also evaluierte die Ein-Zellen-Array-Formel für =SUM(A1:B1*{10,100}) in Excel zu 10. Seit v2.384.68 stuft der Token-Stream eines ARRAY-Records jede Wertklassen-Referenz und Array-Konstante zur Arrayklasse hoch, $65 und $60, genau das schreibt auch Excel

Ein Reader, der die Klassenbits ignoriert, round-tript alle drei munter, wenn Sie also einen eigenen BIFF8-Writer pflegen, vergleichen Sie die Klassenbits jedes Operanden-Tokens mit einer von Excel gespeicherten Datei derselben Formel, nicht nur mit den Token-Nummern

Kurzreferenz

• Excel 365 zeigt @, wenn ein Operator in einer einfachen, unmarkierten Formel einen mehrzelligen Bereich oder ein Inline-Array bekommt
• HotXLS ab v2.384.68 speichert solche Formeln als XLSX-Dynamische-Arrays mit einer Zelle (cm="1", t="array", XLDAPR-Metadaten) und als XLS-Ein-Zellen-Array-Formeln (FORMULA mit PtgExp plus ARRAY $0221)
• Nur Operator-Operanden zählen; ein Bereich, der direkt als Funktionsargument übergeben wird, bleibt eine einfache Formel
• Markiert werden nur Formeln, die über TXLSXCell.Formula oder das klassische Einzelzellen-Formula / Value eingegeben wurden; geladene Formeln bleiben unangetastet
• Die konvertierte Root-Zelle liest sich ohne das führende = zurück
• Die ext uri-GUID des dynamischen Arrays muss in Kleinschreibung sein, sonst weist Excel das Paket zurück
• In Delphi ist Double(True) gleich -1; vor der numerischen Konvertierung auf varBoolean prüfen
• BIFF8: Array-Konstanten nie in der Referenzklasse, PtgIsect- / PtgUnion-Operanden in der Referenzklasse, ARRAY-Record-Operanden in der Arrayklasse

HotXLS liest, schreibt und berechnet XLS- und XLSX-Arbeitsmappen nativ aus Delphi und C++Builder und speichert Array-Operator-Formeln so, dass Excel 365 sie mit denselben Werten öffnet, die HotXLS berechnet hat. Editionen, Dokumentation und einen Testdownload finden Sie unter der HotXLS Delphi spreadsheet component