รูปแบบสูตร R1C1 ใน Delphi ด้วย HotXLS

HotXLS คอมไพล์สูตรที่เขียนขึ้นด้วยรูปแบบการอ้างอิง R1C1 ผ่านเมธอด TXLSCalculator.GetCompiledFormulaR1C1 ซึ่งยอมรับรูปแบบอย่างเช่น R2C3 (แบบสัมบูรณ์), R[-1]C[2] (ระยะเลื่อนแบบสัมพัทธ์) และ RC (เซลล์ปัจจุบัน) โดยจะแปลงพวกมันให้เป็นรูปแบบ A1 เทียบกับแถวและคอลัมน์ของเซลล์ที่สูตรนั้นตั้งอยู่ และส่งผลลัพธ์ไปยังตัวคอมไพเลอร์ตัวเดียวกันที่ทำหน้าที่จัดการสูตร A1 ทั่วไป สำหรับโค้ด Delphi และ C++Builder ที่ต้องสร้างสูตรเดียวกันนี้ข้ามแถวหลายร้อยแถว เมธอดนี้จะช่วยกำจัดประเภทของบั๊กที่เกิดจากการต่อสตริงออกไปทั้งหมด

ประเภทของบั๊กนี้เป็นเรื่องที่คุ้นเคยสำหรับทุกคนที่เคยเขียนโปรแกรมกรอกข้อมูลในคอลัมน์ คุณวนลูปตามแถว และในแต่ละแถวคุณสร้างสตริงสูตร A1 ด้วยคำสั่ง Format('D%d*E%d', [Row, Row]) การประมวลผลแต่ละรอบจะต่อตัวเลขแถวเข้ากับข้อความ และตัวเลขแถวนั้นคือส่วนเดียวที่มีการเปลี่ยนแปลง หากระบุระยะเลื่อน (offset) พลาดไปเพียงครั้งเดียว สลับจำนวนลูปที่อิง 0 เข้ากับหมายเลขแถว A1 ที่อิง 1 หรือเกิดการเลื่อนบล็อกข้อมูลตามแถวหัวเรื่อง สูตรทุกสูตรในคอลัมน์ก็จะชี้ตำแหน่งผิดพลาดไปหนึ่งแถวทันที โดยไม่มีการแจ้งเตือนใดๆ แสดงขึ้นมา ตัวเลขผลลัพธ์ผิดพลาดไปเฉยๆ สูตรที่คุณต้องการจริงๆ อย่าง "คูณสองเซลล์ทางซ้ายของฉัน" ไม่เคยต้องอ้างถึงหมายเลขแถวเลย และรูปแบบ R1C1 ช่วยให้คุณเขียนในลักษณะนั้นได้

รูปแบบ R1C1 คืออะไรและควรใช้งานเมื่อใด?

รูปแบบ R1C1 จะระบุตำแหน่งเซลล์ด้วยหมายเลขแถวและคอลัมน์ แทนอักษรคอลัมน์บวกกับตัวเลขแถว และระบุการอ้างอิงแบบสัมพัทธ์ในลักษณะระยะเลื่อนที่ชัดเจนจากเซลล์สูตร R2C3 คือเซลล์แบบสัมบูรณ์ที่แถว 2 คอลัมน์ 3 ซึ่งรูปแบบ A1 เขียนว่า $C$2 ส่วน R[-1]C[2] คือตำแหน่งหนึ่งแถวด้านบนและสองคอลัมน์ถัดไปทางขวาจากตำแหน่งที่สูตรนั้นตั้งอยู่ และ RC คือเซลล์สูตรนั้นเอง ระยะเลื่อนในวงเล็บเหลี่ยมคือหัวใจสำคัญ: การอ้างอิงแบบสัมพัทธ์ในรูปแบบ R1C1 จะอ่านค่าได้เหมือนเดิมเสมอไม่ว่าสูตรจะอยู่ในเซลล์ใดก็ตาม ขณะที่รูปแบบ A1 ของการอ้างอิงเดียวกันนั้นจะเปลี่ยนไปตามแต่ละแถว

รูปแบบการอ้างอิงนี้มีประโยชน์อย่างมากในสถานการณ์หนึ่งซึ่งพบได้บ่อย: นั่นคือการสร้างเทมเพลต (template generation) ที่ต้องใส่สูตรแบบสัมพัทธ์ตัวเดียวกันลงในทุกแถวของพื้นที่ข้อมูล ในรูปแบบ A1 คุณต้องสร้างข้อความสูตรใหม่สำหรับทุกแถว แต่ในรูปแบบ R1C1 ข้อความสูตรจะเป็นค่าคงที่ สิ่งนี้ยังใกล้เคียงกับแนวคิดภายในของไฟล์สเปรดชีตด้วย: บันทึกข้อมูลสูตรร่วม (shared-formula records) จะเก็บการอ้างอิงสัมพัทธ์ในรูปของระยะเลื่อนแถวและคอลัมน์จากเซลล์หลัก ดังนั้นสตริง A1 ในแต่ละแถวจึงเป็นสิ่งที่โค้ดของคุณสร้างขึ้นมาเพียงเพื่อให้ตัวแยกวิเคราะห์ (parser) แยกองค์ประกอบกลับไปเป็นระยะเลื่อนดังเดิม รูปแบบ R1C1 จึงช่วยข้ามขั้นตอนไปกลับนี้ สำหรับสูตรที่มนุษย์สร้างขึ้นและโต้ตอบได้นั้น รูปแบบ A1 ยังคงเป็นตัวเลือกที่เป็นธรรมชาติมากกว่า ซึ่งเป็นเหตุผลที่มันยังคงเป็นค่าเริ่มต้นในทุกที่ของ HotXLS

HotXLS คอมไพล์สูตร R1C1 ได้อย่างไร?

HotXLS เปิดเผยฟีเจอร์นี้ในสองระดับซึ่งเพิ่มเข้ามาในรุ่น v2.175.0 เมธอด TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula คือตัวที่โค้ดส่วนใหญ่เรียกใช้งาน: โดยจะส่งคืนสูตรที่คอมไพล์แล้วซึ่งพร้อมสำหรับการประมวลผล เช่นเดียวกับเมธอด A1 คู่กันอย่าง GetCompiledFormula แต่จะมีพารามิเตอร์เพิ่มเติมอีก 2 ตัวเพื่อระบุแถวและคอลัมน์อิง 0 ของเซลล์ที่สูตรนั้นเป็นเจ้าของ ส่วนข้างใต้เมธอดนี้ TXLSFormula.GetCompiledR1C1 จะสร้างผังโครงสร้างไวยากรณ์ดิบ และฟังก์ชันอิสระ R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String จะทำหน้าที่แปลงรูปแบบอ้างอิงจริง กระบวนการทำงานได้รับการออกแบบมาให้เรียบง่ายโดยเจตนา: แปลงข้อความ R1C1 ให้เป็นข้อความ A1 ที่เทียบเท่ากันโดยใช้พิกัดของเซลล์หลัก จากนั้นจึงคอมไพล์ข้อความ A1 ผ่านเอนจินที่มีอยู่ — ซึ่งเป็นเอนจินเดียวกับที่ช่วยแก้ปัญหาชื่อที่กำหนดไว้และการอ้างอิงข้ามแผ่นงาน และจัดสรรฟังก์ชันเวิร์กชีตแบบกำหนดเอง

เนื่องจากการแปลงเกิดขึ้นก่อนการคอมไพล์ ทุกสิ่งที่อยู่ถัดมาจึงทำงานเสมือนว่าคุณเขียนสูตร A1 ขึ้นมาด้วยตัวเอง ตัวอย่างเช่น R1C1ToA1('R2C3', 4, 3) จะส่งคืนค่า '$C$2' ไม่ว่าเซลล์หลักจะเป็นเซลล์ใด เนื่องจากพิกัดทั้งสองเป็นแบบสัมบูรณ์ ส่วน R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — ซึ่งเป็นสูตรในเซลล์ D5 เนื่องจาก CurRow = 4 และ CurCol = 3 อิงตามฐาน 0 — จะส่งคืนค่า 'SUM(D2:D4)': โดยระยะเลื่อนจะได้รับการคลี่คลายเทียบกับแถว 5 คอลัมน์ D และแสดงผลในรูปแบบการอ้างอิงสัมพัทธ์ A1 ธรรมดา ส่วนช่วงข้อมูลไม่ต้องการการจัดการกรณีพิเศษใดๆ เครื่องหมายโคลอนจะได้รับการส่งผ่านและแต่ละจุดสิ้นสุดจะแปลงค่าแยกกันอย่างอิสระ

// 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)

การกรอกข้อมูลในคอลัมน์ด้วยสูตรสัมพัทธ์สูตรเดียว

ความคุ้มค่าจะแสดงให้เห็นในส่วนของการวนลูป ให้เปรียบเทียบรุ่น A1 ที่ต้องสร้างข้อความสูตรใหม่ในการประมวลผลแต่ละรอบ เทียบกับรุ่น R1C1 ที่ตัวสูตรเป็นค่าคงที่และมีเพียงพิกัดเซลล์หลักเท่านั้นที่เคลื่อนย้าย ทั้งสองแบบคอมไพล์ผ่าน TXLSCalculator และประมวลผลด้วย GetValue; ตัวเครื่องคำนวณจะรับฟังก์ชันเรียกกลับของตัวให้บริการเซลล์ (cell-provider callback) ในตอนสร้าง เพื่อให้ตัวประมวลผลสามารถดึงค่าเซลล์จากแหล่งข้อมูลของคุณได้

// 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);
      finally
        Compiled.Free;
      end;
    end;
  finally
    Calc.Free;
  end;
end;

การวนลูปแบบ R1C1 จะไม่มีคณิตศาสตร์เกี่ยวกับแถวในข้อความสูตรเลย 'RC[-2]*RC[-1]' หมายถึง "แถวเดียวกัน, สองคอลัมน์ถัดไปทางซ้าย คูณกับ แถวเดียวกัน, หนึ่งคอลัมน์ถัดไปทางซ้าย" ทั้งในแถว 2 และในแถว 500 และหากบล็อกข้อมูลเคลื่อนย้ายลงตามแถวหัวเรื่องในภายหลัง ค่าสูตรคงที่ก็ไม่เปลี่ยนแปลง — มีเพียงขอบเขตลูปเท่านั้นที่เปลี่ยนไป รุ่น A1 มีจุดให้คำนวณระยะ + 1 ผิดพลาด 2 จุด ส่วนรุ่น R1C1 ไม่มีจุดผิดพลาดเลย

ตัวแปลงรองรับรูปแบบ R1C1 แบบใดบ้าง?

ตัวแปลง R1C1ToA1 รู้จักรูปแบบที่ระบุไว้ในรุ่น v2.175.0: ได้แก่ R[n]C[m] สำหรับระยะเลื่อนสัมพัทธ์ในทิศทางใดทิศทางหนึ่ง, RnCm สำหรับแถวและคอลัมน์แบบสัมบูรณ์, R[-n]C[m] สำหรับระยะเลื่อนที่เป็นลบ, รูปแบบเปล่า RC สำหรับเซลล์สูตรเอง และช่วงข้อมูลเช่น R1C1:R3C3 หรือ R[-1]C:R[1]C ส่วนประกอบแถวและคอลัมน์จะวิเคราะห์แยกกันอย่างอิสระ ดังนั้นรูปแบบผสมเช่น R[1]C3 — แถวสัมพัทธ์ คอลัมน์สัมบูรณ์ — ก็สามารถทำงานได้เช่นกัน และตัวอักษรไม่คำนึงถึงตัวพิมพ์เล็กใหญ่ ดังนั้น r[-1]c[2] จึงคอมไพล์ได้เหมือนกับตัวพิมพ์ใหญ่ ส่วนประกอบที่มีวงเล็บเหลี่ยมจะกลายเป็นพิกัด A1 แบบสัมพัทธ์ที่ไม่มีการยึดตำแหน่ง; ตัวเลขธรรมดาจะกลายเป็นพิกัดสัมบูรณ์ที่ยึดด้วยสัญลักษณ์ $

คำถามที่น่าสนใจคือตัวแปลงหลีกเลี่ยงการปะปนกับตัวอักษรอื่นในสูตรได้อย่างไร เนื่องจาก R และ C เป็นตัวอักษรทั่วไปที่ใช้บ่อย กฎการคัดกรองของมันทำงานแบบอิงโทเค็น (token-based): ตัวอักษร R จะถือว่าเป็นจุดเริ่มต้นของการอ้างอิงก็ต่อเมื่อไม่มีตัวอักษรอื่นนำหน้า และตัวที่ผ่านเกณฑ์ต้องสามารถแกะส่วนประกอบได้ครบถ้วน — ส่วนแถวที่เป็นทางเลือก, ตัวอักษร C ที่จำเป็นต้องมี, ส่วนคอลัมน์ที่เป็นทางเลือก — มิฉะนั้นข้อความจะถูกปล่อยไว้ตามเดิม นี่คือสาเหตุที่ ROUND(R[-1]C[0], 2) แปลงเฉพาะการอ้างอิงภายใน: เนื่องจากตัวอักษร R ในคำว่า ROUND มีตัว O ตามหลัง ไม่ใช่ตัวเลข, วงเล็บเหลี่ยม หรือตัว C ดังนั้นการแกะคำจึงล้มเหลวและชื่อฟังก์ชันจะถูกส่งผ่านไปตรงๆ ตรรกะเดียวกันนี้ยังช่วยปกป้องฟังก์ชัน ROW() และชื่อฟังก์ชันที่เริ่มต้นด้วย C จะไม่ถูกจัดเข้ารายการพิจารณาเลย เนื่องจากมีเฉพาะ R เท่านั้นที่เริ่มต้นการอ้างอิง บันทึกประจำรุ่น v2.175.0 ระบุว่าสตริงลิเทอรัล (string literals) และตัวระบุ (identifiers) จะถูกข้ามไปเช่นกัน แต่อย่างไรก็ตาม หากข้อความในเครื่องหมายคำพูดในสูตรของคุณมีลักษณะคล้ายกับการอ้างอิง R1C1 ก็ควรตรวจสอบผลลัพธ์ที่คอมไพล์แล้วสักครั้งก่อนนำไปใช้งานจริง

ฐานพิกัดและรายละเอียดการยึดตำแหน่งที่ควรทราบ

ข้อตกลงสองแบบมาบรรจบกันใน API นี้ และการทำความเข้าใจให้ชัดเจนจะช่วยหลีกเลี่ยงกับดักเดียวที่มีอยู่ พารามิเตอร์ CurRow และ CurCol ของ GetCompiledFormulaR1C1 เป็นแบบอิง 0 ตามข้อกำหนดของ calculator API ขณะที่ตัวเลขภายในรูปแบบ R1C1 จะเป็นแบบอิง 1 ซึ่งตรงกับที่ Excel แสดงผล: R2C3 คือ แถว 2 คอลัมน์ 3 ซึ่งตรงกับเซลล์ $C$2 ไม่ใช่ $D$3 หากตัวนับลูปของคุณเป็นแบบอิง 0 อยู่แล้ว คุณสามารถส่งค่าตรงไปที่ CurRow ได้เลย; ระยะ + 1 จะได้รับการจัดการภายในตัวแปลง ไม่ใช่ในโค้ดของคุณ

รายละเอียดอีกประการหนึ่งที่มีความสำคัญในกรณีสูตรที่คอมไพล์แล้วจะอยู่ข้ามเซลล์ที่คอมไพล์มา เมื่อคุณละเว้นการระบุส่วนแถวหรือคอลัมน์ — เช่นในรูปแบบ RC[-1] หรือ R[2]C — พิกัดที่ถูกละเว้นจะเปลี่ยนไปเป็นเซลล์หลักและถูกแสดงผลเป็นพิกดแบบสัมบูรณ์ที่ยึดด้วยเครื่องหมาย $ ในข้อความ A1 ที่แปลงออกมา ในเวลาประมวลผลสิ่งนี้จะไม่แสดงความแตกต่างเนื่องจากมีค่าเท่ากัน แต่การยึดตำแหน่งแบบสัมพัทธ์เทียบกับแบบสัมบูรณ์จะมีผลต่อการเลื่อนตำแหน่งการอ้างอิงเมื่อมีการแทรกหรือลบแถวหรือคอลัมน์ในภายหลัง ตามที่ครอบคลุมไว้ในบทความคู่กันเกี่ยวกับการปรับตำแหน่งอ้างอิงสูตร หากคุณต้องการให้พิกัดยังคงมีความสัมพันธ์แบบสัมพัทธ์ระหว่างการแก้ไขโครงสร้าง ให้เขียนระยะเลื่อนอย่างชัดเจน — เช่น R[0]C[-1] แทนการเขียน RC[-1] — เพื่อให้ตัวแปลงส่งผลลัพธ์การอ้างอิง A1 แบบไม่มีการยึดตำแหน่ง

การคอมไพล์สูตร R1C1 เป็นส่วนหนึ่งของเอนจินสูตรใน HotXLS Delphi Excel Component ควบคู่ไปกับตัวคอมไพเลอร์ A1, กราฟการคำนวณใหม่ และการประมวลผล API ที่แสดงไว้ด้านบน หากโค้ดของคุณสร้างสเปรดชีตด้วยการวนลูปสูตรลงในคอลัมน์ การเปลี่ยนลูปเหล่านั้นจากการต่อสตริง A1 ไปเป็นการใช้สูตรคงที่ R1C1 ถือเป็นการอัปเกรดความน่าเชื่อถือที่คุ้มค่ามากที่สุดตัวหนึ่ง