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. Komponent HotXLS Delphi Component zapisuje te formuły z operatorami tablicowymi 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. Serwis w Delphi zapisuje skoroszyt, jego silnik formuł cache'uje 210 dla =SUM(A1:B1*{10,100}) w zwykłej komórce formuły, 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 poprawnie wyliczonej formuły?
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 wynik. W reżimie tablic dynamicznych ten sam tekst mnoży element po elemencie, 1 × 10 + 2 × 100, i zwraca 210. Silnik formuł HotXLS liczy po tablicowo, i format pliku też musi to samo komunikować. 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 | Zapisywane przez HotXLS |
|---|---|---|---|
=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:
uses
SysUtils, Variants, lxCalc, lxHandle, lxHandleX; // lxOk jest zadeklarowany w lxCalc
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)iA1:B2-1są oznaczane, gdziekolwiek występują w formule, łącznie ze środkiem SUMPRODUCTSUM(A1:B2)iSUMPRODUCT(A1:A2,{1;10})nie są oznaczane, bo zakres i tablica trafiają wprost do argumentu funkcji i żaden operator ich nie dotykaA1*2iSUM(A1,B1)*2nie 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
Jak silnik obliczeniowy dopasowuje wartości Excel 365
Oznaczenie w zapisie procentuje tylko wtedy, gdy silnik formułów w ogóle zwraca wartości Excel 365 — i to w obu silnikach. Najbardziej widocznym przypadkiem jest SUMPRODUCT: implementacja przyjmująca wyłącznie dwa lub więcej zwykłych zakresów zwraca #N/A dla SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)), a nawet jednoargumentowego SUMPRODUCT(B1:B2). HotXLS liczy argumenty będące wyrażeniami 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)*1albo--, ż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ę. Operator przecięcia spacją zwraca wspólny prostokąt dwóch referencji, a gdy te się nie nakładają, #NULL!, więc =SUM(A1:B2 B1:B2) daje 6, a nie 2, a wynik może zasilać parametry referencyjne takie jak ROWS i INDEX. Parser przyjmuje 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łą 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, TRUE liczy się jako 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 przyjmuje @ między dwiema referencjami jako przecięcie binarne i 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
Trzy szczegóły, które Excel sprawdza przy otwieraniu wyniku z tablicami dynamicznymi
Zgoda Excela na oznaczenie tablicy dynamicznej sprowadza się do trzech szczegółów, których żaden test round-tripu na własnym wyjściu nie wyłapie, bo biblioteka czyta własne wyjście z powrotem poprawnie w każdym razie. Każdy z nich ujawnia się dopiero, gdy otwierasz wyjście w Excelu 16 i podmieniasz po jednej zmiennej naraz:
- GUID rozszerzenia musi być całkowicie małymi literami.
ext uriwxl/metadata.xmlmusi brzmieć dokładnie{bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Zapisz go mieszaną wielkością liter, a Excel 16 odmówi otwarcia całego pakietu, nie tylko komórki - 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, dlategoTXLSXCell.Formulaodczytuje tekst bez niego Double(True)to -1 w Delphi. Konwersja Variant trzyma się konwencji COM, w której TRUE to wszystkie bity ustawione, aVarIsNumeric(True)też zwraca True. Naiwna konwersja sprawia więc, że=TRUE*1zwraca -1, a logiczne elementy tablic bywają klasyfikowane jako liczby, więc porównanie typu(B1:B2>0)=TRUEidzie na opak. HotXLS sprawdzavarBoolean, 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 |
Trzy miejsca w pisarzu BIFF8 łatwo zepsuć, a każde z nich daje odrębny objaw w Excelu, przy czym u czytnika samego pisarza plik czyta się z powrotem bez zarzutu:
- Stałe tablicowe w klasie referencji. Enkoder, który wybiera klasę z kontekstu, widzi, że parametry SUM albo ROWS to klasa referencji, i zapisuje
=SUM({1,2})zPtgArrayjako$20. Excel pokazuje całą formułę jako=#N/A. Stała tablicowa nigdy nie może być referencją, więc HotXLS zapisuje klasę tablicy$60wszędzie tam, gdzie kontekst żąda referencji - Operandy w klasie wartości u
PtgIsectiPtgUnion. Dawanie operatorom binarnym operandów w klasie wartości jest w porządku dla*, ale nietrafione dla operatorów referencyjnych. Przy obszarach$45przedPtgIsect($0F) Excel czyta=SUM(A1:B2 B1:B2)jako=SUM(@A1:B2 @B1:B2)i zwraca#VALUE!. HotXLS zapisuje operandyPtgIsectiPtgUnion($10) 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. Przy
$45jednokomórkowa formuła tablicowa dla=SUM(A1:B1*{10,100})wylicza się w Excelu na 10. HotXLS awansuje więc, w strumieniu tokenów rekordu ARRAY, każdą referencję w klasie wartości i każdą stałą tablicową do klasy tablicy,$65i$60, czyli dokładnie tak, jak 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 zapisuje takie formuły jako jednokomórkowe tablice dynamiczne XLSX (
cm="1",t="array", metadaneXLDAPR) i jako jednokomórkowe formuły tablicowe XLS (FORMULA zPtgExpplus 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.Formulaalbo klasyczneFormula/Valuena pojedynczej komórce; formuły wczytane z pliku pozostają nietknięte - Skonwertowana komórka korzenia odczytuje się bez wiodącego
= - GUID
ext uritablicy 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/PtgUnionw 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