Định dạng Công thức R1C1 trong Delphi với HotXLS

HotXLS biên dịch các công thức được viết bằng định dạng tham chiếu R1C1 thông qua TXLSCalculator.GetCompiledFormulaR1C1, nhận các dạng như R2C3 (tuyệt đối), R[-1]C[2] (độ lệch tương đối) và RC (ô hiện tại), chuyển đổi chúng sang định dạng A1 dựa trên hàng và cột của ô chứa công thức, rồi nạp kết quả vào cùng một trình biên dịch xử lý các công thức A1 thông thường. Đối với code Delphi và C++Builder tạo cùng một công thức trên hàng trăm hàng, phương thức duy nhất đó sẽ loại bỏ hoàn toàn một nhóm lỗi liên quan đến việc nối chuỗi

Nhóm lỗi này quen thuộc với bất kỳ ai đã từng điền một cột bằng lập trình. Bạn lặp qua các hàng, và với mỗi hàng, bạn xây dựng một chuỗi công thức A1 bằng Format('D%d*E%d', [Row, Row]). Mỗi lần lặp đều chèn số hàng vào văn bản, và số hàng là phần duy nhất thay đổi. Chỉ cần tính sai độ lệch một lần, trộn lẫn bộ đếm vòng lặp bắt đầu từ 0 với số hàng A1 bắt đầu từ 1, hoặc dịch chuyển khối dữ liệu bởi một hàng tiêu đề, và mọi công thức trong cột sẽ trỏ lệch đi một hàng. Không có gì báo lỗi; các con số đơn giản là bị sai. Công thức thực sự bạn muốn viết là "nhân hai ô bên trái tôi" không bao giờ đề cập đến số hàng cụ thể, và định dạng R1C1 cho phép bạn viết theo cách đó

Định dạng R1C1 là gì và khi nào bạn nên sử dụng nó?

Định dạng R1C1 định địa chỉ các ô bằng số hàng và số cột thay vì chữ cái cột cộng với số hàng, và nó đánh dấu các tham chiếu tương đối dưới dạng các độ lệch rõ ràng từ ô chứa công thức. R2C3 là ô tuyệt đối tại hàng 2, cột 3, mà A1 viết là $C$2. R[-1]C[2] là một hàng phía trên và hai cột bên phải của bất kỳ nơi nào công thức đang tọa lạc. RC chính là ô chứa công thức. Các độ lệch trong dấu ngoặc vuông là điểm mấu chốt: một tham chiếu tương đối trong R1C1 được đọc giống nhau cho dù ô nào chứa nó, trong khi cách thể hiện A1 của cùng một tham chiếu đó thay đổi theo từng hàng

Định dạng này chứng minh giá trị của nó trong chính xác một kịch bản, và đó là một kịch bản phổ biến: tạo template, nơi cùng một công thức tương đối phải được đặt vào mỗi hàng của một vùng dữ liệu. Trong A1, bạn phải dựng lại văn bản công thức cho mỗi hàng. Trong R1C1, văn bản là một hằng số. Điều này cũng gần hơn với cách các định dạng file bảng tính tư duy nội bộ: các bản ghi công thức chia sẻ (shared-formula) lưu trữ các tham chiếu tương đối dưới dạng các độ lệch hàng và cột từ ô chứa công thức, do đó một chuỗi A1 trên mỗi hàng là thứ mà code của bạn tổng hợp chỉ để trình phân tích cú pháp phân rã nó trở lại thành các độ lệch. R1C1 bỏ qua vòng lặp đó. Đối với các công thức tương tác do con người viết, A1 vẫn là sự lựa chọn tự nhiên, đó là lý do tại sao nó vẫn là mặc định ở mọi nơi trong HotXLS

Làm thế nào HotXLS biên dịch một công thức R1C1?

HotXLS cung cấp tính năng này ở hai cấp độ, được thêm vào trong phiên bản v2.175.0. TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula là hàm mà hầu hết các đoạn code gọi: nó trả về một công thức đã biên dịch sẵn sàng để đánh giá, giống hệt như hàm A1 cùng nhóm GetCompiledFormula, nhưng có thêm hai tham số xác định hàng và cột bắt đầu từ 0 của ô chứa công thức. Bên dưới nó, TXLSFormula.GetCompiledR1C1 tạo ra cây cú pháp thô, và một hàm độc lập R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String thực hiện việc chuyển đổi định dạng thực tế. Quy trình này rất đơn giản: dịch văn bản R1C1 sang văn bản A1 tương đương bằng cách sử dụng tọa độ ô chứa công thức, sau đó biên dịch văn bản A1 thông qua engine hiện có — cùng một engine phân giải tên được định nghĩa và tham chiếu chéo sheet và điều phối các hàm worksheet tùy chỉnh

Bởi vì việc chuyển đổi xảy ra trước khi biên dịch, mọi thứ phía sau hoạt động giống như bạn đã tự viết công thức A1. R1C1ToA1('R2C3', 4, 3) trả về '$C$2' bất kể ô chứa công thức là gì, vì cả hai tọa độ đều là tuyệt đối. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — một công thức nằm ở D5, vì CurRow = 4 and CurCol = 3 bắt đầu từ 0 — trả về 'SUM(D2:D4)': các độ lệch được phân giải theo hàng 5, cột D, và được phát ra dưới dạng các tham chiếu A1 tương đối thông thường. Các phạm vi không cần xử lý đặc biệt; dấu hai chấm được truyền qua và mỗi điểm đầu cuối được chuyển đổi độc lập

// The formula lives in D5: CurRow = 4, CurCol = 3 (both 0-based)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2'  (absolute row and column)

S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)'  (offsets resolved against D5)

S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)'  (the R in ROUND is left alone)

Điền một cột bằng một công thức tương đối

Lợi ích xuất hiện trong vòng lặp. Hãy so sánh phiên bản A1, vốn dựng lại văn bản công thức trên mỗi lần lặp, với phiên bản R1C1, nơi công thức là một hằng số và chỉ có tọa độ ô di chuyển. Cả hai đều biên dịch qua TXLSCalculator và đánh giá bằng GetValue; calculator nhận một callback cung cấp ô khi xây dựng để bộ đánh giá có thể lấy các giá trị ô từ nguồn dữ liệu của bạn

// A1 style: a different formula string for every row
for Row := 1 to 500 do
begin
  FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]);  // 1-based A1 rows
  Compiled := Calc.GetCompiledFormula(FormulaText, 0);
  // ... evaluate, store, free ...
end;

const
  AmountFormula = 'RC[-2]*RC[-1]';  // two cells to the left, same row
var
  Calc: TXLSCalculator;
  Compiled: TXLSCompiledFormula;
  Value: Variant;
  Row: Integer;
begin
  Calc := TXLSCalculator.Create(nil, Provider.GetValue);
  try
    for Row := 1 to 500 do
    begin
      Compiled := Calc.GetCompiledFormulaR1C1(AmountFormula, 0, Row, 5);
      try
        if Calc.GetValue(0, Compiled, Row, 5, Value, 1) = lxOk then
          StoreResult(Row, 5, Value);
      except
        Compiled.Free;
      end;
    end;
  finally
    Calc.Free;
  end;
end;

Vòng lặp R1C1 hoàn toàn không có phép toán hàng nào trong văn bản công thức. 'RC[-2]*RC[-1]' có nghĩa là "cùng một hàng, hai cột bên trái, nhân với cùng một hàng, một cột bên trái" ở hàng 2 cũng như hàng 500, và nếu khối dữ liệu sau đó dịch xuống bởi một hàng tiêu đề, hằng số công thức không thay đổi — chỉ có các biên vòng lặp thay đổi. Phiên bản A1 có hai chỗ để tính sai điều chỉnh + 1; phiên bản R1C1 thì có không

Những dạng R1C1 nào được bộ chuyển đổi chấp nhận?

Bộ chuyển đổi R1C1ToA1 nhận dạng các dạng được tài liệu hóa cho phiên bản v2.175.0: R[n]C[m] cho các độ lệch tương đối theo một trong hai hướng, RnCm cho hàng-và-cột tuyệt đối, R[-n]C[m] với các độ lệch âm, RC trần cho chính ô chứa công thức, và các phạm vi như R1C1:R3C3 hoặc R[-1]C:R[1]C. Phần hàng và cột phân tích độc lập, do đó các dạng hỗn hợp như R[1]C3 — hàng tương đối, cột tuyệt đối — cũng hoạt động, và các chữ cái không phân biệt chữ hoa-chữ thường, vì vậy r[-1]c[2] biên dịch giống như phiên bản chữ hoa của nó. Các phần trong ngoặc vuông trở thành tọa độ A1 không được neo (tương đối); các số trần trở thành tọa độ tuyệt đối được neo bằng $

Câu hỏi thú vị là làm thế nào bộ chuyển đổi tránh làm biến dạng mọi thứ khác trong công thức, vì R và C là những chữ cái phổ biến

Quy tắc phân biệt của nó dựa trên token: một chữ R chỉ được coi là điểm bắt đầu của một tham chiếu khi nó không được đi trước bởi một chữ cái khác, và ứng viên đó sau đó phải phân tích hoàn toàn thành công — phần hàng tùy chọn, chữ C bắt buộc, phần cột tùy chọn — nếu không văn bản sẽ được giữ nguyên không chạm tới. Đó là lý do tại sao ROUND(R[-1]C[0], 2) chỉ chuyển đổi tham chiếu bên trong: chữ R trong ROUND được theo sau bởi O chứ không phải một chữ số, dấu ngoặc vuông hoặc chữ C, do đó việc phân tích cú pháp thất bại và tên hàm đi qua nguyên vẹn. Logic tương tự bảo vệ hàm ROW(), và các tên hàm bắt đầu bằng chữ C không bao giờ là ứng viên, vì chỉ có R mới bắt đầu một tham chiếu. Ghi chú phát hành phiên bản v2.175.0 tuyên bố rằng các chuỗi literal và mã định danh cũng được bỏ qua; mặc dù vậy, nếu một chuỗi literal được trích dẫn trong công thức của bạn tình cờ chứa văn bản có dạng chính xác như một tham chiếu R1C1, bạn nên kiểm tra kết quả biên dịch một lần trước khi tin dùng nó trong môi trường production

Cơ sở tọa độ và chi tiết neo giữ đáng lưu ý

Hai quy ước gặp nhau bên trong API này, và phân biệt rõ chúng giúp tránh cái bẫy thực tế duy nhất. Các tham số CurRow và CurCol của GetCompiledFormulaR1C1 bắt đầu từ 0, tuân theo API calculator, trong khi các số bên trong chính định dạng đó bắt đầu từ 1, khớp với những gì Excel hiển thị: R2C3 là hàng 2, cột 3, tức là $C$2, không phải $D$3. Nếu bộ đếm vòng lặp của bạn đã bắt đầu từ 0, bạn truyền trực tiếp nó dưới dạng CurRow; phép cộng + 1 nằm bên trong bộ chuyển đổi, không phải trong code của bạn

Một chi tiết nữa quan trọng nếu công thức đã biên dịch tồn tại lâu hơn ô chứa nó. Khi bạn bỏ qua một phần hàng hoặc cột — các dạng RC[-1] hoặc R[2]C — tọa độ bị bỏ qua sẽ phân giải theo ô chứa công thức và được phát ra dưới dạng tọa độ tuyệt đối được neo bằng $ trong văn bản A1 được chuyển đổi. Tại thời điểm đánh giá, điều này là vô hình, vì giá trị là giống nhau theo cả hai cách. Nhưng việc neo tương đối hay tuyệt đối quyết định cách các tham chiếu dịch chuyển khi các hàng hoặc cột được chèn hoặc xóa sau đó, như được trình bày trong bài viết đi kèm về điều chỉnh tham chiếu công thức. Nếu bạn cần một tọa độ luôn ở trạng thái tương đối qua các chỉnh sửa cấu trúc, hãy viết rõ độ lệch — ví dụ như R[0]C[-1] thay vì RC[-1] — để bộ chuyển đổi phát ra một tham chiếu A1 không được neo

Biên dịch R1C1 là một phần của engine công thức trong HotXLS Delphi Excel Component, cùng với trình biên dịch A1, đồ thị tính toán lại và API đánh giá hiển thị ở trên. Nếu code của bạn xây dựng bảng tính bằng cách lặp các công thức xuống các cột, việc chuyển các vòng lặp đó từ A1 nối chuỗi sang một hằng số R1C1 duy nhất là một trong những cách nâng cấp độ tin cậy tiết kiệm nhất hiện có