Công thức mảng HotXLS: Vì sao Excel chèn @ và #VALUE!

Excel 365 chèn @ vào một công thức như =SUM(A1:B1*{10,100}) và hiện #VALUE! khi tệp lưu nó thành công thức thường, vì khi đó Excel áp dụng implicit intersection kiểu cũ lên mọi operand của toán tử. Kể từ v2.384.68, HotXLS Delphi Component lưu các công thức có toán tử mảng này đúng cách Excel 365 làm: thành dynamic-array formula một ô trong XLSX và array formula một ô trong XLS

Triệu chứng này vượt qua nổi cả code review. Service Delphi của bạn ghi một workbook, HotXLS tính lại và cache 210 cho =SUM(A1:B1*{10,100}), rồi khách hàng mở bằng Excel 16 để thấy =SUM(@A1:B1*@{10,100}) trên formula bar và #VALUE! trong ô. Trong tệp không có gì hỏng. Thiếu là thiếu cái metadata cho Excel biết công thức được viết theo luật dynamic array, và thiếu nó Excel quay về mô hình đánh giá thời tiền dynamic array

Vì sao Excel 365 chèn @ vào công thức HotXLS đã tính đúng?

Excel 365 thêm @ vì một công thức không có dấu dynamic array, theo định nghĩa, là công thức cũ, còn công thức cũ thu một range nhiều ô về một ô ở bất cứ đâu toán tử đòi một giá trị đơn. Việc thu đó chính là implicit intersection: Excel lấy ô của range nằm cùng hàng với công thức (với range dọc) hay cùng cột (với range ngang), và nếu không có ô như vậy thì kết quả là #VALUE!. Excel 365 giữ nguyên ý nghĩa đó cho công thức kiểu cũ và hiển thị @ để cho thấy việc thu đã diễn ra

Đặt =SUM(A1:B1*{10,100}) vào E5 là kiểu đọc kiểu cũ lộ liễu ngay. A1:B1 là range ngang, công thức nằm ở cột E, range chẳng có ô nào ở cột E, nên @A1:B1 cho #VALUE! và cả SUM kế thừa nó. Theo luật dynamic array, cùng đoạn text đó nhân từng phần tử, 1 × 10 + 2 × 100, và trả về 210. Bộ máy công thức của HotXLS đã đánh giá theo kiểu dynamic array từ các bản phát hành v2.384.61 và v2.384.63; chỉ là định dạng tệp chưa nói điều đó. Với A1:B2 giữ 1, 2, 3 và 4, đây là các công thức thử và những gì Excel 16 hiển thị:

Công thức	Kết quả HotXLS	Excel 16, lưu thành công thức thường	Lưu từ v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynamic array, Excel hiện 210
=SUM((A1:B2>2)*1)	2	Implicit intersection, sai hoặc lỗi	Dynamic array, Excel hiện 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implicit intersection, sai hoặc lỗi	Dynamic array, Excel hiện 2
=MAX(A1:B2-1)	3	Implicit intersection, sai hoặc lỗi	Dynamic array, Excel hiện 3
=SUM(A1:B2)	10	10	Công thức thường, không đổi

Hàng cuối quan trọng ngang bốn hàng đầu. SUM(A1:B2) đưa một range thẳng vào tham số hàm chấp nhận reference, nên không toán tử nào nhìn thấy range nhiều ô và không phép giao nào có thể xảy ra. Đích thân Excel 365 cũng lưu công thức đó thành công thức thường, và HotXLS làm y như vậy

HotXLS lưu công thức có toán tử mảng trong XLSX và XLS ra sao

HotXLS ghi một công thức có toán tử mảng trong XLSX thành dynamic array một ô: phần tử <c> mang cm="1", công thức là <f t="array" ref="E5">, và gói nhận thêm xl/metadata.xml với một metadata type XLDAPR mà phần extension giữ dynamicArrayProperties fDynamic="1". Thuộc tính cm là chỉ số tính từ 1 vào khối cellMetadata của part đó, còn record XLDAPR đứng sau chính là thứ bảo Excel "hãy đánh giá cái này theo luật dynamic array". Đây là cùng một cấu trúc mà Excel 16 ghi khi bạn gõ cùng công thức rồi lưu, và bố cục đích cũng được xác lập ban đầu theo đúng cách đó

Trong XLS không có part metadata nào, nên HotXLS dùng construct duy nhất BIFF8 có cho việc đánh giá mảng: array formula một ô. Ô nhận một record FORMULA mà token stream chỉ là một PtgExp trỏ vào chính nó, theo sau bởi một record ARRAY ($0221) mang công thức đã parse thật trên range một ô. Excel 365 ghi dynamic-array formula xuống XLS đúng cách đó, và một phiên bản Excel cũ hơn đọc tệp sẽ thấy một array formula kiểu Ctrl+Shift+Enter kinh điển

Không có API mới nào liên quan. Việc đánh dấu xảy ra khi bạn gán công thức qua API ô bình thường, ở cả hai engine. Phía XLSX là 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;

    // Toán tử trên một range hay mảng inline: được lưu thành dynamic array
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Range đưa thẳng vào một hàm: vẫn là <f> thường
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Text gốc của array được giữ không có dấu '=' đứng đầu
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 và E6 nhận cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

Sau khi chuyển đổi, TXLSXCell.Formula trả về text không có =, cùng dạng mà TXLSXRange.SetDynamicArrayFormula lưu, nên code so sánh chuỗi công thức sau khi gán nên chuẩn hóa dấu = đứng đầu

Engine kinh điển đi theo đúng luật đó qua IXLSRange.Formula trên một ô đơn. Việc gán công thức được chuyển hướng nội bộ sang đường array một ô, nên XLS đã lưu chứa cặp FORMULA cộng 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})';  // record ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // record ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // FORMULA thường

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

Nếu bạn đang neo một kết quả nhiều ô thay vì một tổng hợp vô hướng, các API tường minh vẫn là công cụ đúng: SetArrayFormula cho một hình chữ nhật đặt cỡ sẵn, như đã nói trong dynamic array spill formulas với HotXLS, hay TXLSXRange.SetDynamicArrayFormula khi bạn muốn dấu dynamic array XLSX trên một range tự đặt cỡ. Đường tự động trong bài này chỉ phủ các công thức gõ vào một ô

Những công thức nào được HotXLS đánh dấu thành dynamic array?

HotXLS chỉ đánh dấu một công thức khi một toán tử có một cây con operand sinh ra mảng. Phép kiểm tra chạy trên syntax tree đã compile, và một operand sinh mảng nếu nó là một range nhiều ô, một hằng mảng inline, hay một biểu thức toán tử khác mà bản thân nó có operand dạng vậy. Dấu ngoặc trong suốt. Các toán tử được tính là toán tử số học (+ - * / ^), nối chuỗi (&), sáu phép so sánh, cộng trừ một ngôi, và phần trăm:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) và A1:B2-1 được đánh dấu, ở bất cứ đâu chúng xuất hiện trong công thức, kể cả bên trong SUMPRODUCT
• SUM(A1:B2) và SUMPRODUCT(A1:A2,{1;10}) không được đánh dấu, vì range và mảng đi thẳng vào một đối số hàm và không toán tử nào đụng tới chúng
• A1*2 hay SUM(A1,B1)*2 không được đánh dấu: reference một ô và kết quả hàm là vô hướng trong phép kiểm tra này

Ba ranh giới là chủ đích. Thứ nhất, đánh dấu chỉ xảy ra khi công thức được nhập qua API, nghĩa là TXLSXCell.Formula ở engine XLSX và một phép gán Formula hay Value một ô ở engine kinh điển. Công thức nạp từ tệp được ghi lại y như lúc tìm thấy, vì một công thức cũ từ producer khác có thể cố tình phụ thuộc implicit intersection. Thứ hai, text không chứa : lẫn { bị bỏ qua mà không cần compile lần hai. Thứ ba, một công thức lẽ ra spill, như =A1:B1*2 đứng một mình, được đánh dấu thành dynamic array một ô neo tại nơi bạn đặt nó. HotXLS không spill nó, và Excel sẽ kéo kết quả sang các ô lân cận ở lần tính lại kế tiếp

Luật operand này là anh em với luật argument-class đã bàn trong implicit intersection cho defined names trong HotXLS. Bài kia nói về tham số hàm khai báo dạng value class; bài này nói về toán tử, thứ mà trong mô hình kiểu cũ luôn đòi giá trị

Engine tính toán đã đổi gì để cho kết quả khớp nhau

Bản sửa lưu trữ trong v2.384.68 dựa trên việc engine công thức HotXLS đã trả về các giá trị kiểu Excel 365, thứ từng đòi vài bản sửa trước đó ở cả hai engine. Dễ thấy nhất là SUMPRODUCT: cho tới v2.384.61 nó chỉ nhận hai plain range trở lên, nên SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) và kể cả SUMPRODUCT(B1:B2) một tham số đều trả về #N/A. HotXLS giờ đánh giá các đối số biểu thức từng phần tử theo luật của Excel:

• mọi đối số phải có hình dạng y hệt nhau, vô hướng tính là 1 × 1, nếu không kết quả là #VALUE!
• giá trị lỗi bên trong bất kỳ đối số nào được trả về làm kết quả
• phần tử text và logic tính là 0, nên vẫn cần (B1:B2>0)*1 hay -- để biến TRUE thành 1
• các đối số toàn plain range giữ nguyên vòng lặp streaming ban đầu, nên range lớn không bị vật chất hóa thành mảng

Nhà SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) dùng cùng bộ đánh giá từng phần tử khi một đối số là biểu thức toán tử trên một range, nên =SUM((B1:B2>0)*1) đếm cả hai hàng thay vì chỉ nhìn ô đầu tiên. v2.384.62 khiến toán tử giao bằng dấu cách trả về hình chữ nhật chung của hai reference, với #NULL! khi chúng không chồng nhau, nên =SUM(A1:B2 B1:B2) là 6 thay vì 2 và kết quả có thể nạp vào các tham số reference như ROWS và INDEX. v2.384.63 thêm hằng mảng inline như {1,2;3,4} (dấu phẩy tách cột, dấu chấm phẩy tách hàng) và hợp reference như (A1:B2,D4) vào parser. So sánh từng phần tử cũng cho phần tử trống lấy kiểu của phía bên kia, FALSE gặp giá trị logic, khớp luật vô hướng từ v2.384.53 đã nói trong chuỗi so sánh và ô trống trong HotXLS

var
  V: Variant;
begin
  // Book là TXLSXWorkbook từ ví dụ đầu;
  // sheet active của nó giữ A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, một tham số
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, range chung B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, phần chồng nhau đếm hai lần
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, trước v2.384.61 là -1
end;

TXLSXWorkbook.Calculate đánh giá một chuỗi công thức trên sheet active mà không lưu nó, một cách nhanh để kiểm tra hành vi của engine. Một lưu ý về chính @: HotXLS theo lịch sử chấp nhận @ giữa hai reference như một phép giao hai ngôi, và giờ nó đánh giá dạng đó với ngữ nghĩa giao thật. Trong Excel 365, @ là một tiền tố implicit intersection một ngôi. Đừng viết @ vào text công thức rồi mong ý nghĩa của Excel; hãy dùng dấu cách cho phép giao và để các luật lưu trữ ở trên lo phần ngữ nghĩa dynamic array

Vì sao Excel từng từ chối mở tệp hay tính ra giá trị sai?

Việc khiến Excel chấp nhận dấu dynamic array cần ba bản sửa mà không test round-trip với chính mình nào bắt được, vì HotXLS đọc đúng output của chính mình trong mọi trường hợp. Mỗi bản sửa được tìm ra bằng cách mở output của HotXLS trong Excel 16 rồi thay từng biến một:

1. GUID của extension phải toàn chữ thường. ext uri trong xl/metadata.xml phải đúng bằng {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Một template HotXLS cũ viết nó lẫn hoa thường, và Excel 16 từ chối mở cả gói, không chỉ ô đó. Các workbook tạo bằng TXLSXRange.SetDynamicArrayFormula trước v2.384.68 dính đúng vấn đề này
2. Text gốc của array không mang = đứng đầu. XLSX writer phát text đã lưu của array root nguyên văn vào <f>. Nếu ô đã chuyển đổi giữ = của nó, phần tử sẽ đọc thành <f t="array" ref="E5">=SUM(...)</f>, thứ Excel cũng từ chối lúc mở. HotXLS cắt nó trong lúc chuyển đổi, đó là lý do TXLSXCell.Formula đọc lại không thấy nó
3. Double(True) là -1 trong Delphi. Chuyển đổi Variant đi theo quy ước COM nơi TRUE là mọi bit bật, và VarIsNumeric(True) cũng trả về True. Trước v2.384.61 điều đó khiến =TRUE*1 trả về -1 và để các phần tử mảng logic bị xếp loại là số, nên một phép so sánh như (B1:B2>0)=TRUE đi sai. HotXLS giờ kiểm tra varBoolean trước khi coi một Variant là số trong số học vô hướng, số học mảng và xếp loại phần tử mảng, và TRUE tính là 1

Operand class BIFF8: chi tiết mức byte cho người hiện thực định dạng

Trong BIFF8, mọi token operand mang operand class của nó ngay trong byte token, và Excel tin class đó hơn cả cấu trúc công thức. [MS-XLS] định nghĩa class là một trường PtgDataType hai bit tại bit 5 và 6 của token: 1 cho reference, 2 cho value, 3 cho array. Năm bit thấp đặt tên token, nên cùng một area reference có ba cách viết:

Token	Reference class	Value class	Array class
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS từng sai ba chỗ trong số này ở những vị trí khác nhau, và mỗi lỗi cho một triệu chứng riêng trong Excel trong khi đọc lại vẫn ngon lành trong HotXLS:

• Hằng mảng dạng reference class. Bộ encode chọn class theo ngữ cảnh, còn tham số SUM hay ROWS là reference class, nên =SUM({1,2}) được ghi với PtgArray là $20. Excel hiển thị cả công thức thành =#N/A. Một hằng mảng không bao giờ là reference, nên kể từ v2.384.63 HotXLS ghi array class $60 ở bất cứ đâu ngữ cảnh đòi một reference
• Operand dạng value class của PtgIsect và PtgUnion. Toán tử hai ngôi nhận operand dạng value class, đúng cho * nhưng sai với các toán tử reference. Với area $45 đứng trước PtgIsect ($0F), Excel đọc =SUM(A1:B2 B1:B2) thành =SUM(@A1:B2 @B1:B2) và trả về #VALUE!. Kể từ v2.384.62, operand của PtgIsect và PtgUnion ($10) được ghi ở reference class, $25
• Operand dạng value class bên trong record ARRAY. Excel áp implicit intersection ngay cả bên trong một array formula khi operand là value class. HotXLS ghi $45 ở đó, nên array formula một ô cho =SUM(A1:B1*{10,100}) đánh giá ra 10 trong Excel. Kể từ v2.384.68, token stream của một record ARRAY nâng mọi reference dạng value class và hằng mảng lên array class, $65 và $60, đúng như Excel ghi

Một reader bỏ qua các bit class sẽ round-trip cả ba ngon lành, nên nếu bạn tự giữ một BIFF8 writer riêng, hãy so bit class của mọi token operand với một tệp lưu bởi Excel của cùng công thức, chứ đừng chỉ so số token

Tra nhanh

• Excel 365 hiện @ khi một toán tử trong công thức thường không đánh dấu nhận một range nhiều ô hay mảng inline
• HotXLS v2.384.68 trở lên lưu các công thức như vậy thành dynamic array một ô XLSX (cm="1", t="array", metadata XLDAPR) và array formula một ô XLS (FORMULA với PtgExp cộng ARRAY $0221)
• Chỉ operand của toán tử được tính; một range đưa thẳng vào đối số hàm vẫn là công thức thường
• Chỉ công thức nhập qua TXLSXCell.Formula hay Formula / Value một ô kiểu kinh điển được đánh dấu; công thức nạp lên không bị đụng tới
• Ô gốc đã chuyển đổi đọc lại không có = đứng đầu
• GUID ext uri của dynamic array phải chữ thường nếu không Excel từ chối cả gói
• Trong Delphi, Double(True) là -1; kiểm tra varBoolean trước khi chuyển sang số
• BIFF8: hằng mảng không bao giờ ở reference class, operand của PtgIsect / PtgUnion ở reference class, operand của record ARRAY ở array class

HotXLS đọc, ghi và tính các workbook XLS và XLSX thuần nhất từ Delphi và C++Builder, và lưu các công thức có toán tử mảng để Excel 365 mở chúng với đúng những giá trị HotXLS đã tính. Xem HotXLS Delphi spreadsheet component để biết các edition, tài liệu và bản dùng thử