Formuły tablicowe HotXLS: dlaczego Excel dodaje @ i #VALUE!

Excel 365 wstawia @ do formuły takiej jak =SUM(A1:B1*{10,100}) i pokazuje #VALUE!, gdy plik trzyma ją jako zwykłą formułę, bo Excel stosuje wtedy do każdego operandu operatora dziedziczone niejawne przecięcie. Od v2.384.68 komponent HotXLS Delphi Component zapisuje te formuły tak samo jak Excel 365: jako jednokomórkowe formuły tablic dynamicznych w XLSX i jako jednokomórkowe formuły tablicowe w XLS

Objaw przechodzi przez code review bez śladu. Twój serwis w Delphi zapisuje skoroszyt, HotXLS go przelicza i cache'uje 210 dla =SUM(A1:B1*{10,100}), a klient otwiera plik w Excelu 16 i widzi w pasku formuły =SUM(@A1:B1*@{10,100}), a w komórce #VALUE!. Nic w pliku nie jest uszkodzone. Brakuje metadanych, które mówią Excelowi, że formuła powstała w reżimie tablic dynamicznych, a bez nich Excel cofa się do modelu obliczania z czasów sprzed tablic dynamicznych

Dlaczego Excel 365 dodaje @ do formuły, którą HotXLS policzył poprawnie?

Excel 365 dodaje @ dlatego, że formuła bez oznaczenia tablicy dynamicznej jest z definicji formułą w starym stylu, a stare formuły sprowadzają zakres wielokomórkowy do jednej komórki wszędzie tam, gdzie operator oczekuje pojedynczej wartości. To sprowadzenie to właśnie niejawne przecięcie: Excel bierze tę komórkę zakresu, która leży w wierszu formuły (dla zakresu pionowego) albo w kolumnie formuły (dla zakresu poziomego), a gdy takiej komórki nie ma, wynikiem jest #VALUE!. Excel 365 zachowuje to znaczenie dla starych formuł i pokazuje @, żeby to sprowadzenie było widoczne

Wstaw =SUM(A1:B1*{10,100}) do E5, a stare odczytanie staje się oczywiste. A1:B1 to zakres poziomy, formuła siedzi w kolumnie E, zakres nie ma komórki w kolumnie E, więc @A1:B1 daje #VALUE!, a cała funkcja SUM dziedziczy ten błąd. W reżimie tablic dynamicznych ten sam tekst mnoży element po elemencie, 1 × 10 + 2 × 100, i zwraca 210. Silnik formuł HotXLS liczy w ten sposób od wydań v2.384.61 i v2.384.63; po prostu format pliku jeszcze tego nie mówił. Gdy A1:B2 trzyma 1, 2, 3 i 4, to są formuły próbne i to, co pokazuje Excel 16:

Formuła	Wynik HotXLS	Excel 16, zapisana jako zwykła formuła	Zapis od v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Tablica dynamiczna, Excel pokazuje 210
=SUM((A1:B2>2)*1)	2	Niejawne przecięcie, zły wynik albo błąd	Tablica dynamiczna, Excel pokazuje 2
=SUMPRODUCT((A1:B2>2)*1)	2	Niejawne przecięcie, zły wynik albo błąd	Tablica dynamiczna, Excel pokazuje 2
=MAX(A1:B2-1)	3	Niejawne przecięcie, zły wynik albo błąd	Tablica dynamiczna, Excel pokazuje 3
=SUM(A1:B2)	10	10	Zwykła formuła, bez zmian

Ostatni wiersz znaczy tyle, co pierwsze cztery. SUM(A1:B2) przekazuje zakres wprost do parametru funkcji przyjmującego referencje, więc żaden operator nigdy nie widzi zakresu wielokomórkowego i przecięcie nie ma prawa zajść. Sam Excel 365 zapisuje taką formułę jako zwykłą formułę, i HotXLS robi to samo

Jak HotXLS zapisuje formuły z operatorami tablicowymi w XLSX i XLS

HotXLS zapisuje formułę z operatorem tablicowym w XLSX jako jednokomórkową tablicę dynamiczną: element <c> dostaje cm="1", formuła ma postać <f t="array" ref="E5">, a pakiet zyskuje xl/metadata.xml z typem metadanych XLDAPR, którego rozszerzenie trzyma dynamicArrayProperties fDynamic="1". Atrybut cm to indeks liczony od jedynki w blok cellMetadata tej części, a rekord XLDAPR, który za nim stoi, mówi Excelowi „oblicz to według zasad tablic dynamicznych”. To dokładnie ta sama struktura, którą zapisuje Excel 16, gdy wpisujesz tę formułę ręcznie i zapisujesz plik — tak właśnie ustalono docelowy układ

W XLS nie ma części z metadanymi, więc HotXLS używa jedynego konstruktu, jaki BIFF8 ma do obliczeń tablicowych: jednokomórkowej formuły tablicowej. Komórka dostaje rekord FORMULA, którego strumień tokenów to pojedynczy PtgExp wskazujący na nią samą, a za nim idzie rekord ARRAY ($0221) z prawdziwą sparsowaną formułą nad zakresem jednej komórki. Excel 365 zapisuje tablice dynamiczne do XLS dokładnie tak samo, a starszy Excel czytający plik widzi klasyczną formułę tablicową z Ctrl+Shift+Enter

Nie ma tu żadnego nowego API. Oznaczenie powstaje w momencie, gdy przypisujesz formułę przez zwykłe API komórki, w obu silnikach. Po stronie XLSX jest 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;

    // Operator na zakresie albo tablicy inline: zapisywane jako tablica dynamiczna
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Zakres podany wprost do funkcji: zostaje zwykłym <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Korzeń tablicy trzyma tekst bez wiodącego '='
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

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

Po konwersji TXLSXCell.Formula zwraca tekst bez = — w tej samej postaci, jaką zapisuje TXLSXRange.SetDynamicArrayFormula — więc kod porównujący po przypisaniu teksty formuł powinien znormalizować wiodący =

Silnik klasyczny trzyma się tej samej reguły przez IXLSRange.Formula na pojedynczej komórce. Przypisanie formuły jest wewnętrznie przekierowywane na ścieżkę tablicy jednokomórkowej, więc zapisany XLS zawiera parę 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})';  // rekord ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // rekord ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // zwykły FORMULA

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

Jeśli zakotwiczasz wynik wielokomórkowy, a nie skalarną agregację, właściwym narzędziem są wciąż jawne API: SetArrayFormula dla z góry wymiarowanego prostokąta, opisane w artykule o formułach rozlewanych tablic dynamicznych w HotXLS, albo TXLSXRange.SetDynamicArrayFormula, gdy chcesz oznaczenie tablicy dynamicznej XLSX na zakresie, który sam wymiarujesz. Automatyczna ścieżka z tego artykułu obejmuje wyłącznie formuły wpisane do jednej komórki

Które formuły HotXLS oznacza jako tablice dynamiczne?

HotXLS oznacza formułę tylko wtedy, gdy któryś operator ma poddrzewo operandu zwracające tablicę. Sprawdzenie działa na skompilowanym drzewie składniowym, a operand zwraca tablicę, jeśli jest wielokomórkowym zakresem, inline'ową stałą tablicową albo innym wyrażeniem operatorowym, które samo ma taki operand. Nawiasy są przezroczyste. Liczą się operatory arytmetyczne (+ - * / ^), konkatenacja (&), sześć porównań, jednoargumentowy plus i minus oraz procent:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) i A1:B2-1 są oznaczane, gdziekolwiek występują w formule, łącznie ze środkiem SUMPRODUCT
• SUM(A1:B2) i SUMPRODUCT(A1:A2,{1;10}) nie są oznaczane, bo zakres i tablica trafiają wprost do argumentu funkcji i żaden operator ich nie dotyka
• A1*2 i SUM(A1,B1)*2 nie są oznaczane: referencje do pojedynczych komórek i wyniki funkcji są dla tego sprawdzenia skalarami

Trzy granice są zamierzone. Po pierwsze, oznaczanie zachodzi tylko wtedy, gdy formuła jest wprowadzana przez API — to znaczy TXLSXCell.Formula w silniku XLSX oraz przypisanie Formula albo Value na pojedynczej komórce w silniku klasycznym. Formuły wczytane z pliku są zapisywane z powrotem dokładnie w takim stanie, w jakim je znaleziono, bo formuła w starym stylu od innego producenta może celowo polegać na niejawnym przecięciu. Po drugie, tekst bez : i bez { jest pomijany bez drugiej kompilacji. Po trzecie, formuła, która by się rozlała, jak samotne =A1:B1*2, jest oznaczana jako jednokomórkowa tablica dynamiczna zakotwiczona tam, gdzie ją postawiłeś. HotXLS jej nie rozlewa; Excel rozciągnie wynik na sąsiednie komórki przy najbliższym przeliczeniu

Ta reguła operandów jest rodzeństwem reguły klasy argumentów opisanej w artykule o niejawnym przecięciu dla nazw zdefiniowanych w HotXLS. Tamten artykuł dotyczy parametrów funkcji zadeklarowanych jako klasa wartości; ten dotyczy operatorów, które w starym modelu zawsze żądają wartości

Co się zmieniło w silniku obliczeń, żeby wyniki się zgadzały

Poprawka zapisu z v2.384.68 zakłada, że silnik formuł HotXLS już zwraca wartości zgodne z Excel 365, co wymagało kilku wcześniejszych poprawek w obu silnikach. Najbardziej widoczna dotyczyła SUMPRODUCT: do v2.384.61 funkcja przyjmowała wyłącznie dwa lub więcej zwykłych zakresów, więc SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)), a nawet jednoargumentowy SUMPRODUCT(B1:B2) zwracały #N/A. HotXLS liczy teraz argumenty wyrażeń element po elemencie według zasad Excela:

• każdy argument musi mieć dokładnie ten sam kształt, przy czym skalar liczy się jako 1 × 1, inaczej wynikiem jest #VALUE!
• wartość błędu w środku dowolnego argumentu jest zwracana jako wynik
• elementy tekstowe i logiczne liczą się jako 0, więc nadal potrzeba (B1:B2>0)*1 albo --, żeby zamienić TRUE na 1
• argumenty będące wyłącznie zwykłymi zakresami zachowują oryginalną pętlę strumieniową, więc duże zakresy nie są materializowane jako tablice

Rodzina SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) korzysta z tego samego evaluatora elementowego, gdy argumentem jest wyrażenie operatorowe nad zakresem, więc =SUM((B1:B2>0)*1) liczy oba wiersze zamiast patrzeć tylko na pierwszą komórkę. Wydanie v2.384.62 nauczyło operator przecięcia spacją zwracać wspólny prostokąt dwóch referencji, z #NULL!, gdy się nie nakładają, więc =SUM(A1:B2 B1:B2) daje 6, a nie 2, a wynik może zasilać parametry referencyjne takie jak ROWS i INDEX. Wydanie v2.384.63 dodało do parsera inline'owe stałe tablicowe jak {1,2;3,4} (przecinki rozdzielają kolumny, średniki wiersze) oraz unie referencji jak (A1:B2,D4). Porównania elementowe dają też pustemu elementowi typ drugiej strony — FALSE wobec wartości logicznej — zgodnie ze skalarną regułą z v2.384.53 opisaną w artykule o łańcuchach porównań i pustych komórkach w HotXLS

var
  V: Variant;
begin
  // Book to TXLSXWorkbook z pierwszego przykładu;
  // jego aktywny arkusz trzyma A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, pojedynczy argument
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, wspólny zakres B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, nakładka liczona dwa razy
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, przed v2.384.61 było -1
end;

TXLSXWorkbook.Calculate wylicza tekst formuły na aktywnym arkuszu bez zapisywania jej — szybki sposób na sprawdzenie zachowania silnika. Jedno ostrzeżenie o samym @: HotXLS historycznie przyjmował @ między dwiema referencjami jako przecięcie binarne i teraz wylicza tę postać z prawdziwą semantyką przecięcia. W Excelu 365 @ to jednoargumentowy prefiks niejawnego przecięcia. Nie wpisuj @ do tekstu formuły w nadziei na znaczenie z Excela; do przecięcia użyj spacji, a semantykę tablic dynamicznych załatwią opisane wyżej reguły zapisu

Dlaczego Excel odmawiał otwarcia pliku albo liczył złą wartość?

Przekonanie Excela do zaakceptowania oznaczenia tablicy dynamicznej wymagało trzech poprawek, których żaden test rundy na własnym wyjściu by nie wyłapał, bo HotXLS czytał własne wyjście poprawnie w każdym przypadku. Każdą znaleziono, otwierając wyjście HotXLS w Excelu 16 i podmieniając po jednej zmiennej naraz:

1. GUID rozszerzenia musi być całkowicie małymi literami. ext uri w xl/metadata.xml musi brzmieć dokładnie {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Starszy szablon HotXLS zapisywał go mieszaną wielkością liter, a Excel 16 odmawiał otwarcia całego pakietu, nie tylko komórki. Skoroszyty utworzone przez TXLSXRange.SetDynamicArrayFormula przed v2.384.68 miały ten sam problem
2. Tekst korzenia tablicy nie ma wiodącego =. Pisarz XLSX wypluwa przechowany tekst korzenia tablicy dosłownie do <f>. Gdyby skonwertowana komórka zachowała swój =, element brzmiałby <f t="array" ref="E5">=SUM(...)</f>, co Excel też odrzuca przy otwieraniu. HotXLS zdejmuje go podczas konwersji, dlatego TXLSXCell.Formula odczytuje tekst bez niego
3. Double(True) to -1 w Delphi. Konwersja Variant trzyma się konwencji COM, w której TRUE to wszystkie bity ustawione, a VarIsNumeric(True) też zwraca True. Przed v2.384.61 sprawiało to, że =TRUE*1 zwracało -1, a logiczne elementy tablic były klasyfikowane jako liczby, więc porównanie typu (B1:B2>0)=TRUE szło na opak. HotXLS sprawdza teraz varBoolean, zanim potraktuje Variant jako liczbę w arytmetyce skalarnej, arytmetyce tablic i klasyfikacji elementów tablic, a TRUE liczy się jako 1

Klasy operandów BIFF8: szczegóły na poziomie bajtów dla implementatorów formatów

W BIFF8 każdy token operandu niesie swoją klasę operandu w samym bajcie tokena, a Excel ufa tej klasie bardziej niż strukturze formuły. [MS-XLS] definiuje klasę jako dwubitowe pole PtgDataType w bitach 5 i 6 tokena: 1 dla referencji, 2 dla wartości, 3 dla tablicy. Pięć niskich bitów nazywa token, więc ta sama referencja obszaru ma trzy pisownie:

Token	Klasa referencji	Klasa wartości	Klasa tablicy
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS machnął przy trzech z nich w różnych miejscach, a każda pomyłka dawała odrębny objaw w Excelu, choć w HotXLS plik czytał się z powrotem poprawnie:

• Stałe tablicowe w klasie referencji. Enkoder wybierał klasę z kontekstu, a parametry SUM albo ROWS to klasa referencji, więc =SUM({1,2}) było zapisywane z PtgArray jako $20. Excel pokazywał całą formułę jako =#N/A. Stała tablicowa nigdy nie może być referencją, więc od v2.384.63 HotXLS zapisuje klasę tablicy $60, gdziekolwiek kontekst żąda referencji
• Operandy w klasie wartości u PtgIsect i PtgUnion. Operatory binarne brały operandy w klasie wartości, co jest w porządku dla *, ale nietrafione dla operatorów referencyjnych. Przy obszarach $45 przed PtgIsect ($0F) Excel czytał =SUM(A1:B2 B1:B2) jako =SUM(@A1:B2 @B1:B2) i zwracał #VALUE!. Od v2.384.62 operandy PtgIsect i PtgUnion ($10) są zapisywane w klasie referencji, $25
• Operandy w klasie wartości w środku rekordu ARRAY. Excel stosuje niejawne przecięcie nawet wewnątrz formuły tablicowej, gdy operand jest w klasie wartości. HotXLS zapisywał tam $45, więc jednokomórkowa formuła tablicowa dla =SUM(A1:B1*{10,100}) wyliczała się w Excelu na 10. Od v2.384.68 strumień tokenów rekordu ARRAY awansuje każdą referencję w klasie wartości i każdą stałą tablicową do klasy tablicy, $65 i $60, czyli do tego, co zapisuje Excel

Czytnik ignorujący bity klasy przepuszcza wszystkie trzy przypadki bez mrugnięcia, więc jeśli utrzymujesz własnego pisarza BIFF8, porównuj bity klasy każdego tokena operandu z plikiem zapisanym przez Excela dla tej samej formuły, a nie tylko z numerami tokenów

Ściąga

• Excel 365 pokazuje @, gdy operator w zwykłej, nieoznaczonej formule dostaje zakres wielokomórkowy albo tablicę inline
• HotXLS od v2.384.68 zapisuje takie formuły jako jednokomórkowe tablice dynamiczne XLSX (cm="1", t="array", metadane XLDAPR) i jako jednokomórkowe formuły tablicowe XLS (FORMULA z PtgExp plus ARRAY $0221)
• Liczą się tylko operandy operatorów; zakres podany wprost do argumentu funkcji zostaje zwykłą formułą
• Oznaczane są tylko formuły wprowadzone przez TXLSXCell.Formula albo klasyczne Formula / Value na pojedynczej komórce; formuły wczytane z pliku pozostają nietknięte
• Skonwertowana komórka korzenia odczytuje się bez wiodącego =
• GUID ext uri tablicy dynamicznej musi być małymi literami, inaczej Excel odrzuca pakiet
• W Delphi Double(True) to -1; sprawdź varBoolean, zanim skonwertujesz na liczbę
• BIFF8: stałe tablicowe nigdy w klasie referencji, operandy PtgIsect / PtgUnion w klasie referencji, operandy rekordu ARRAY w klasie tablicy

HotXLS czyta, zapisuje i przelicza skoroszyty XLS i XLSX natywnie z Delphi i C++Buildera, a formuły z operatorami tablicowymi zapisuje tak, żeby Excel 365 otwierał je z tymi samymi wartościami, które wyliczył HotXLS. Wydania, dokumentację i wersję próbną znajdziesz na stronie komponentu arkuszy kalkulacyjnych HotXLS dla Delphi