Tham chiếu Công thức khi Chèn các Hàng trong HotXLS

HotXLS tự động điều chỉnh các tham chiếu công thức khi bạn chèn hoặc xóa các hàng hoặc cột trong một worksheet XLSX. Các phương thức InsertRows, DeleteRows, InsertCols, và DeleteCols của engine viết lại mọi công thức còn sót lại để các tham chiếu A1 của nó — tương đối, tuyệt đối và phạm vi — tiếp tục trỏ vào cùng một dữ liệu sau khi chỉnh sửa cấu trúc, và các tham chiếu vào một khối bị xóa sẽ trở thành #REF!, khớp với hành vi của Excel

Lỗi mà tính năng này ngăn chặn là một trong những lỗi âm thầm nhất trong việc tạo báo cáo. Một trình tạo ghi các số liệu hàng ngày vào C2:C9 với công thức =SUM(C2:C9) bên dưới, sau đó một bước sau chèn thêm một hàng tiêu đề ở đầu. Nếu engine chỉ di chuyển các giá trị ô và giữ nguyên văn bản công thức, SUM đó vẫn đọc C2:C9 trong khi dữ liệu hiện nằm ở C3:C10 — do đó tổng số tiền sẽ âm thầm bỏ sót ngày cuối cùng và tính hai lần một tiêu đề. Không có gì đưa ra exception, file vẫn mở bình thường, và con số đơn giản là bị sai. Trước phiên bản 2.160, engine HotXLS XLSX giữ nguyên các công thức trong quá trình chỉnh sửa cấu trúc; kể từ phiên bản 2.160, việc viết lại là tự động và không cần thiết lập cờ nào cả

Điều gì xảy ra với các công thức khi bạn chèn một hàng trong Excel?

Quy tắc của Excel là các tham chiếu đi theo dữ liệu, không phải địa chỉ. Khi một hàng được chèn, mọi tham chiếu có chỉ mục hàng nằm tại hoặc dưới điểm chèn sẽ dịch xuống theo số lượng hàng được chèn; các tham chiếu hoàn toàn nằm trên điểm chèn không bị ảnh hưởng. Việc xóa các hàng chạy cùng một quy tắc theo chiều ngược lại: các tham chiếu dưới khối bị xóa trượt lên, và các tham chiếu vào chính khối bị xóa sẽ trở thành #REF! vì các ô mà chúng đặt tên không còn tồn tại. Các cột hoạt động tương tự dọc theo trục kia. Một thư viện bảng tính muốn đầu ra của nó tương thích với người dùng Excel phải tái tạo chính xác các cơ chế này, vì người dùng lập luận về công thức của họ theo các thuật ngữ này mà không cần suy nghĩ về nó

Phần làm các nhà phát triển ngạc nhiên là các tham chiếu tuyệt đối cũng di chuyển. Các neo $ trong $B$2 kiểm soát những gì xảy ra khi một công thức được sao chép hoặc điền sang một ô khác — chúng không làm gì trong quá trình chỉnh sửa cấu trúc. Chèn một hàng phía trên hàng 2 và Excel viết lại $B$2 thành $B$3, các dấu đô la vẫn giữ nguyên, vì giá trị mà công thức phụ thuộc vào đã thực sự di chuyển đến hàng 3. Một engine chỉ dịch chuyển các tham chiếu tương đối sẽ làm hỏng chính xác các công thức mà mọi người neo giữ một cách có chủ đích nhất. HotXLS dịch chuyển cả hai dạng và bảo toàn các ký tự $ trong văn bản được viết lại

Làm thế nào HotXLS dịch chuyển các tham chiếu công thức tự động?

Cả hai phương thức chỉnh sửa cấu trúc trên TXLSXWorksheet đều ủy quyền cho một geometry engine duy nhất: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) gọi nó với một delta hàng dương, DeleteRows(StartRow, Count) với một delta âm, và các phương thức cột cũng làm như vậy trên trục cột. Quy trình trước tiên di dời chính các ô — loại bỏ bất kỳ ô nào rơi vào bên trong một khối bị xóa — và sau đó duyệt qua mọi ô công thức còn sót lại và chuyển văn bản của nó qua XlsxAdjustFormulaRowColRefs, một trình quét tìm kiếm các tham chiếu kiểu A1 và viết lại các thành phần hàng và cột của chúng theo sự dịch chuyển. Lượt chạy tương tự cũng di dời các phạm vi được gộp (merged range), siêu liên kết, bình luận, hình ảnh, biểu đồ, định dạng có điều kiện, xác thực dữ liệu và phạm vi bảng, nhờ đó toàn bộ sheet di chuyển như một đơn vị

// Layout before the edit:
//   C2..C9  daily figures
//   C10     =SUM(C2:C9)
Sheet.InsertRows(2, 1);      // one blank row before row 2
// Layout after the call:
//   C3..C10 daily figures
//   C11     =SUM(C3:C10)    -- the range moved with the data

Một vài chi tiết của trình quét rất đáng lưu ý. XlsxAdjustFormulaRowColRefs nhận diện các tham chiếu ô đơn ở cả bốn dạng neo (A1, $A1, A$1, $A$1) and các phạm vi hai góc như A1:B3, điều chỉnh độc lập từng điểm đầu cuối. Các công thức có văn bản đã bắt đầu bằng # — một dấu hiệu lỗi từ lần chỉnh sửa trước — sẽ bị bỏ qua thay vì quét lại. Và InsertCols bổ sung một chi tiết nhỏ tương thích với Excel: các cột mới được chèn sẽ kế thừa chiều rộng của cột bên trái của chúng, đây là những gì lệnh Insert Sheet Columns của Excel thực hiện

Khi nào một tham chiếu bị xóa trở thành #REF!?

Quy tắc dịch chuyển cho một chỉ mục hàng hoặc cột đơn lẻ có ba kết quả. Một chỉ mục trước điểm chỉnh sửa không thay đổi. Một chỉ mục tại hoặc sau điểm chỉnh sửa sẽ di chuyển theo delta. Và khi xóa, một chỉ mục rơi vào bên trong khối bị xóa sẽ không có giá trị mới có ý nghĩa — ô đó đã biến mất — vì vậy trình quét viết lại toàn bộ tham chiếu thành #REF!. Đối với một tham chiếu phạm vi, cả hai điểm đầu cuối đều được chạy qua cùng một quy tắc, và nếu một trong hai điểm đầu cuối rơi vào bên trong khối bị xóa, tham chiếu đó sẽ được viết lại thành #REF! thay vị để lại trạng thái hợp lệ một nửa

// A12 holds  =A4+A6+A10
Sheet.DeleteRows(5, 3);      // delete rows 5..7
// The formula, now in A9, reads  =A4+#REF!+A7
//   A4  : above the deleted block, unchanged
//   A6  : inside rows 5..7, gone      -> #REF!
//   A10 : below the block, slides up  -> A7

Tạo ra một lỗi rõ ràng #REF! thay vì âm thầm trỏ lại là sự đánh đổi chính xác, và đó là sự đánh đổi mà Excel thực hiện. Một công thức trỏ đến một ô lân cận sau khi dữ liệu đầu vào thực sự của nó bị xóa sẽ trả về một con số trông có vẻ hợp lý; #REF! lan truyền qua các công thức phụ thuộc và xuất hiện ngay trong lần smoke test đầu tiên. Sự chuyển đổi tương tự cũng được áp dụng trên trục cột

// E1 holds  =B1*$C$1
Sheet.DeleteCols(3, 1);      // remove column C
// The formula, now in D1, reads  =B1*#REF!
// The absolute anchor did not protect $C$1 -- the cell itself is gone

Những dạng tham chiếu nào không được viết lại?

Trình quét nhắm mục tiêu vào các tham chiếu A1 cùng sheet có định dạng chữ cái cột cộng với số hàng rõ ràng, và cần xác định chính xác những gì nằm ngoài phạm vi đó. Các tham chiếu toàn bộ cột như A:A và tham chiếu toàn bộ hàng như 1:1 thiếu một trong hai thành phần, vì vậy trình quét giữ nguyên như lúc viết. Các tham chiếu bảng có cấu trúc (Table1[Amount]) cũng được bỏ qua mà không đụng tới. Việc viết lại cũng hoạt động nghiêm ngặt trên định dạng A1 — nếu code của bạn xây dựng các công thức theo kiểu R1C1, hãy chuyển đổi chúng sang A1 trước khi chỉnh sửa cấu trúc, như được mô tả trong bài viết đi kèm về định dạng công thức R1C1 trong Delphi

Các cấu trúc chéo sheet và cấp độ workbook được xử lý

Các cấu trúc chéo sheet và cấp độ workbook được xử lý bằng các lượt chạy riêng biệt thay vì bằng trình quét văn bản ô. Sau khi điều chỉnh sheet được chỉnh sửa, ShiftSheetGeometry truyền cùng một thay đổi hình học đến các công thức trên các sheet khác tham chiếu đến sheet được chỉnh sửa, đến các phạm vi chuỗi biểu đồ, đến các mục tiêu siêu liên kết nội bộ và đến các tên được định nghĩa (defined names). Các tên được định nghĩa được bảo vệ thêm ở cấp độ vòng đời sheet: kể từ phiên bản 2.150, việc xóa một worksheet viết lại mọi bộ định nghĩa SheetN! bên trong công thức của tên được định nghĩa thành #REF!, và việc đổi tên một sheet viết lại bộ định nghĩa thành tên mới, nhờ đó các tên không bao giờ trỏ đến một sheet không còn tồn tại. Cách các tên và công thức chéo sheet khớp với nhau được trình bày trong bài viết về tên được định nghĩa và công thức chéo sheet

Tính toán lại sau khi dịch chuyển

Việc điều chỉnh tham chiếu viết lại văn bản công thức; nó không tính toán lại kết quả. Sau khi chỉnh sửa cấu trúc, các giá trị được cache được lưu trữ cùng với các công thức sẽ mô tả hình học cũ, vì vậy trình tự đáng tin cậy là: thực hiện tất cả các thao tác chèn và xóa trước, sau đó kích hoạt tính toán lại một lần, rồi lưu. Việc chạy dịch chuyển trước khi tính toán lại cũng giữ cho thông tin phụ thuộc chính xác — mọi tham chiếu được viết lại đều đặt tên cho tiền lệ thực sự của nó, đây chính xác là những gì engine tính toán lại gia tăng và đồ thị phụ thuộc của nó cần để tính toán lại tập hợp tối thiểu các ô bị ảnh hưởng. Nếu một công thức được viết lại hiện chứa #REF!, việc tính toán lại sẽ xuất hiện giá trị lỗi ngay lập tức thay vị để lại một con số lỗi thời trong file

Điều chỉnh tham chiếu công thức đi kèm dưới dạng hành vi tiêu chuẩn của engine XLSX trong HotXLS Delphi Excel Component dành cho Delphi và C++Builder; trang sản phẩm cung cấp tài liệu tham khảo API chỉnh sửa worksheet đầy đủ, bao gồm các phương thức chèn và xóa được hiển thị ở đây