HotXLS จะปรับเปลี่ยนการอ้างอิงสูตรโดยอัตโนมัติเมื่อคุณแทรกหรือลบแถวหรือคอลัมน์ in เวิร์กชีต XLSX เมธอด InsertRows, DeleteRows, InsertCols และ DeleteCols ของเอนจินจะเขียนสูตรที่ยังเหลืออยู่ใหม่ทั้งหมดเพื่อให้การอ้างอิงแบบ A1 — ทั้งการอ้างอิงแบบสัมพัทธ์ (relative), แบบสัมบูรณ์ (absolute) และช่วงข้อมูล (range) — ยังคงชี้ไปยังข้อมูลเดิมหลังการแก้ไขโครงสร้าง และการอ้างอิงไปยังบล็อกข้อมูลที่ถูกลบไปแล้วจะกลายเป็น #REF! ซึ่งสอดคล้องกับพฤติกรรมของ Excel
บั๊กที่ฟีเจอร์นี้ช่วยป้องกันเป็นหนึ่งในบั๊กที่เงียบเชียบที่สุดในการสร้างรายงาน สมมติโปรแกรมสร้างรายงานเขียนตัวเลขรายวันลงใน C2:C9 โดยมี =SUM(C2:C9) อยู่ด้านล่าง จากนั้นในขั้นตอนถัดมาได้แทรกแถวหัวเรื่องไว้ด้านบนสุด หากตัวเอนจินเพียงแคย้ายค่าเซลล์แต่ปล่อยสูตรข้อความไว้เฉยๆ ฟังก์ชัน SUM นั้นก็จะยังคงอ่านค่าจาก C2:C9 ขณะที่ตัวข้อมูลจริงย้ายไปอยู่ที่ C3:C10 แล้ว — ส่งผลให้ยอดรวมละทิ้งข้อมูลของวันสุดท้ายไปอย่างเงียบๆ และนับแถวหัวเรื่องซ้ำสองครั้ง โดยไม่มีการแจ้งเตือนข้อยกเว้นใดๆ ไฟล์เปิดได้ตามปกติแต่ตัวเลขผลลัพธ์นั้นผิดพลาด ก่อนหน้าเวอร์ชัน 2.160 เอนจิน XLSX ของ HotXLS ปล่อยสูตรทิ้งไว้โดยไม่มีการอัปเดตระหว่างการแก้ไขโครงสร้าง แต่ตั้งแต่เวอร์ชัน 2.160 เป็นต้นมา การเขียนสูตรใหม่นี้จะทำงานโดยอัตโนมัติโดยไม่ต้องตั้งค่าแฟล็กใดๆ
เกิดอะไรขึ้นกับสูตรเมื่อคุณแทรกแถวใน Excel?
กฎของ Excel คือการอ้างอิงจะย้ายตามข้อมูล ไม่ใช่ย้ายตามที่อยู่เซลล์ เมื่อมีการแทรกแถว การอ้างอิงใดๆ ที่มีดัชนีแถวอยู่ที่หรือต่ำกว่าจุดแทรกจะเลื่อนลงตามจำนวนแถวที่แทรกเข้ามา; ส่วนการอ้างอิงที่อยู่เหนือจุดแทรกทั้งหมดจะไม่ได้รับผลกระทบ การลบแถวจะใช้กฎเดียวกันในทิศทางตรงกันข้าม: การอ้างอิงใต้บล็อกที่ถูกลบจะเลื่อนขึ้น และการอ้างอิงไปยังเซลล์ในบล็อกที่ถูกลบจะกลายเป็น #REF! เนื่องจากเซลล์ที่เคยอ้างอิงไม่มีอยู่อีกต่อไป ส่วนคอลัมน์ก็มีพฤติกรรมแบบเดียวกันในอีกแกนหนึ่ง ไลบรารีสเปรดชีตที่ต้องการให้ผลลัพธ์การทำงานทำงานร่วมกับผู้ใช้งาน Excel ได้อย่างราบรื่นจำเป็นต้องเลียนแบบกลไกเหล่านี้อย่างแม่นยำ เนื่องจากผู้ใช้มีความคุ้นเคยกับสูตรของพวกเขาในรูปแบบนี้โดยไม่ต้องมาคำนึงถึงรายละเอียด
ส่วนที่สร้างความประหลาดใจให้กับนักพัฒนาก็คือการอ้างอิงแบบสัมบูรณ์ (absolute references) ก็เลื่อนตามด้วยเช่นกัน เครื่องหมายยึด $ ใน $B$2 จะควบคุมสิ่งที่จะเกิดขึ้นเมื่อสูตรถูก คัดลอกหรือป้อน (fill) ไปยังเซลล์อื่น — แต่พวกมันไม่ได้ทำหน้าที่อะไรในระหว่างการแก้ไขโครงสร้าง หากแทรกแถวเหนือแถว 2 ทาง Excel จะเขียนสูตร $B$2 ใหม่เป็น $B$3 โดยที่เครื่องหมายดอลลาร์ยังคงอยู่ครบถ้วน เนื่องจากค่าที่สูตรอ้างอิงได้ย้ายตำแหน่งไปยังแถว 3 จริงๆ เอนจินที่เลื่อนเฉพาะการอ้างอิงแบบสัมพัทธ์จะสร้างความเสียหายให้กับสูตรที่ผู้ใช้ตั้งใจล็อกตำแหน่งไว้ HotXLS จะเลื่อนตำแหน่งทั้งสองรูปแบบและรักษาสัญลักษณ์ $ ไว้ในข้อความสูตรที่เขียนขึ้นใหม่
HotXLS เลื่อนตำแหน่งการอ้างอิงสูตรโดยอัตโนมัติได้อย่างไร?
เมธอดแก้ไขโครงสร้างทั้ง 4 เมธอดบน TXLSXWorksheet จะส่งงานไปยังเอนจินเรขาคณิตตัวเดียว: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta) โดย InsertRows(BeforeRow, Count) จะเรียกใช้งานร่วมกับแถวเดลต้าที่เป็นบวก, DeleteRows(StartRow, Count) เรียกใช้ร่วมกับเดลต้าที่เป็นลบ และเมธอดของคอลัมน์ก็ทำแบบเดียวกันบนแกนคอลัมน์ ขั้นตอนแรกคือการย้ายตำแหน่งของตัวเซลล์เอง — โดยละทิ้งเซลล์ใดๆ ที่ตกอยู่ในบล็อกที่ถูกลบ — จากนั้นจึงสำรวจเซลล์สูตรที่เหลืออยู่และส่งข้อความสูตรผ่าน XlsxAdjustFormulaRowColRefs ซึ่งเป็นตัวสแกนหาการอ้างอิงแบบ A1 และเขียนส่วนประกอบแถวและคอลัมน์ใหม่ตามระยะการเลื่อน ขั้นตอนเดียวกันนี้ยังช่วยย้ายตำแหน่งของช่วงที่ผสาน (merged ranges), ไฮเปอร์ลิงก์, ข้อคิดเห็น, รูปภาพ, แผนภูมิ, การจัดรูปแบบตามเงื่อนไข, การตรวจสอบความถูกต้องของข้อมูล และช่วงตาราง เพื่อให้ทั้งแผ่นชีตเคลื่อนย้ายไปพร้อมกันเป็นหน่วยเดียว
// 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รายละเอียดบางประการของตัวสแกนที่ควรทราบ: XlsxAdjustFormulaRowColRefs รู้จักการอ้างอิงเซลล์เดี่ยวในรูปแบบยึดทั้ง 4 แบบ (A1, $A1, A$1, $A$1) และช่วงแบบสองมุมอย่างเช่น A1:B3 โดยปรับแต่งแต่ละจุดสิ้นสุดอย่างอิสระ ส่วนสูตรที่ข้อความเริ่มต้นด้วย # อยู่แล้ว — ซึ่งเป็นสัญลักษณ์ข้อผิดพลาดจากการแก้ไขก่อนหน้า — จะถูกข้ามไปโดยไม่มีการสแกนซ้ำ และ InsertCols จะเพิ่มความสามารถเหมือน Excel อย่างหนึ่งคือ: คอลัมน์ที่แทรกใหม่จะสืบทอดความกว้างจากคอลัมน์เพื่อนบ้านฝั่งซ้าย ซึ่งเป็นพฤติกรรมเดียวกับคำสั่ง Insert Sheet Columns ของ Excel
เมื่อใดที่การอ้างอิงที่ถูกลบจะกลายเป็น #REF!?
กฎการเลื่อนตำแหน่งสำหรับดัชนีแถวหรือคอลัมน์เดี่ยวมีผลลัพธ์ 3 แบบ: ดัชนีที่อยู่ก่อนจุดแก้ไขจะไม่เปลี่ยนแปลง ดัชนีที่อยู่ที่หรือเลยจุดแก้ไขจะเลื่อนตามค่าเดลต้า และในกรณีการลบ ดัชนีที่อยู่ในบล็อกที่ถูกลบจะไม่มีค่าใหม่ที่มีความหมายอีกต่อไป — เซลล์หายไปแล้ว — ดังนั้นตัวสแกนจะเขียนการอ้างอิงใหม่ทั้งหมดเป็น #REF! สำหรับการอ้างอิงช่วง จุดสิ้นสุดทั้งสองจะถูกประมวลผลตามกฎเดียวกัน และหากจุดสิ้นสุดใดจุดหนึ่งตกอยู่ในบล็อกที่ถูกลบ การอ้างอิงจะถูกเขียนใหม่เป็น #REF! แทนที่จะปล่อยให้เป็นค่าที่ถูกต้องเพียงครึ่งเดียว
// 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การแสดงผล #REF! ออกมาอย่างชัดเจนแทนที่จะเป็นการเปลี่ยนเป้าหมายใหม่อย่างเงียบๆ เป็นทางเลือกที่ถูกต้อง และเป็นวิธีที่ Excel ใช้งาน สูตรที่ชี้ไปยังเซลล์ข้างเคียงหลังจากข้อมูลนำเข้าจริงถูกลบไปแล้วจะส่งคืนตัวเลขที่ดูสมเหตุสมผลแต่ผิดพลาด; ค่าความเสียหาย #REF! จะส่งผ่านไปยังสูตรที่มีความเกี่ยวข้องและแสดงให้เห็นตั้งแต่การทดสอบ (smoke test) ครั้งแรก การแปลงแบบเดียวกันนี้ยังมีผลกับแกนคอลัมน์ด้วย
// 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การอ้างอิงรูปแบบใดบ้างที่ไม่ถูกเขียนใหม่?
ตัวสแกนจะกำหนดเป้าหมายไปยังการอ้างอิงแบบ A1 ในแผ่นงานเดียวกันซึ่งมีรูปแบบอักษรคอลัมน์บวกกับเลขแถวที่ชัดเจน และสิ่งสำคัญคือต้องระบุสิ่งที่อยู่นอกเหนือจากนี้อย่างแม่นยำ การอ้างอิงทั้งคอลัมน์เช่น A:A และการอ้างอิงทั้งแถวเช่น 1:1 จะขาดส่วนประกอบใดส่วนประกอบหนึ่งไป ดังนั้นตัวสแกนจะปล่อยพวกมันไว้ตามเดิม ส่วนการอ้างอิงตารางที่มีโครงสร้าง (เช่น Table1[Amount]) จะถูกละเว้นไปโดยไม่มีการแก้ไขเช่นกัน นอกจากนี้การเขียนใหม่ยังทำงานเฉพาะกับรูปแบบ A1 เท่านั้น — หากโค้ดของคุณสร้างสูตรในรูปแบบ R1C1 ให้แปลงสูตรเหล่านั้นเป็น A1 ก่อนทำการแก้ไขโครงสร้าง ตามที่อธิบายไว้ในบทความคู่กันเกี่ยวกับรูปแบบสูตร R1C1 ใน Delphi
โครงสร้างข้ามแผ่นชีตและโครงสร้างระดับเวิร์กบุ๊กจะได้รับการจัดการโดยขั้นตอนแยกต่างหาก ไม่ผ่านตัวสแกนข้อความเซลล์ หลังจากปรับเปลี่ยนชีตที่แก้ไขแล้ว ฟังก์ชัน ShiftSheetGeometry จะส่งผ่านการเปลี่ยนแปลงเดียวกันไปยังสูตรบนแผ่นชีตอื่นที่อ้างอิงถึงชีตที่แก้ไข, ไปยังช่วงชุดข้อมูลแผนภูมิ, ไปยังเป้าหมายไฮเปอร์ลิงก์ภายใน และไปยังชื่อที่กำหนดไว้ (defined names) ตัวชื่อที่กำหนดไว้จะได้รับการปกป้องเพิ่มเติมในระดับวงจรชีวิตของแผ่นชีต: ตั้งแต่เวอร์ชัน 2.150 การลบเวิร์กชีตจะเขียนคำระบุ SheetN! ภายในสูตรของชื่อที่กำหนดไว้ใหม่เป็น #REF! และการเปลี่ยนชื่อแผ่นชีตจะเขียนคำระบุใหม่ตามชื่อใหม่ เพื่อให้ชื่อไม่มีทางชี้ไปยังชีตที่ไม่มีอยู่อีกต่อไป วิธีการทำงานร่วมกันของชื่อและสูตรข้ามแผ่นชีตได้รับการครอบคลุมในบทความเกี่ยวกับชื่อที่กำหนดไว้และสูตรข้ามแผ่นงาน
คำนวณผลลัพธ์ใหม่หลังการเลื่อนตำแหน่ง
การปรับการอ้างอิงจะเป็นการเขียน ข้อความ สูตรใหม่ แต่ไม่ได้คำนวณผลลัพธ์ใหม่ หลังการแก้ไขโครงสร้าง ค่าที่แคชไว้ซึ่งจัดเก็บควบคู่กับสูตรจะยังคงแสดงถึงโครงสร้างแบบเก่า ดังนั้นขั้นตอนการทำงานที่น่าเชื่อถือคือ: ดำเนินการแทรกและลบทั้งหมดให้เสร็จสิ้นก่อน จากนั้นจึงทริกเกอร์การคำนวณใหม่เพียงครั้งเดียว แล้วจึงทำการบันทึก การดำเนินการเลื่อนตำแหน่งก่อนการคำนวณใหม่จะช่วยรักษาความถูกต้องของข้อมูลความเกี่ยวพัน (dependency information) — ทำให้การอ้างอิงที่เขียนใหม่ทุกตัวชี้ไปยังเซลล์ต้นทางที่แท้จริง ซึ่งเป็นสิ่งที่เอนจินคำนวณใหม่ทีละส่วนและกราฟความเกี่ยวพันของมัน ต้องการสำหรับการคำนวณใหม่ในกลุ่มเซลล์ที่ได้รับผลกระทบเป็นจำนวนน้อยที่สุด หากสูตรที่เขียนใหม่มีค่า #REF! การคำนวณใหม่จะแสดงค่าข้อผิดพลาดขึ้นมาทันที แทนที่จะปล่อยให้มีตัวเลขที่ล้าสมัยค้างอยู่ในไฟล์
การปรับการอ้างอิงสูตรถูกจัดส่งมาในฐานะพฤติกรรมมาตรฐานของเอนจิน XLSX ใน HotXLS Delphi Excel Component สำหรับ Delphi และ C++Builder; โดยในหน้าผลิตภัณฑ์จะมีข้อมูลอ้างอิง API การแก้ไขเวิร์กชีตฉบับเต็ม รวมถึงเมทริกซ์การแทรกและการลบตามที่แสดงไว้ที่นี่