กลไกการคำนวณของ Excel

ภาพรวม

HotXLS มีกลไกในตัวสำหรับการแยกวิเคราะห์และคำนวณสูตร กลไกนี้ประเมินสูตรเซลล์แบบโปรแกรมภายในกระบวนการของคุณ โดยขจัดความจำเป็นในการเรียก Excel หรือตัวควบคุม OLE ภายนอกเพื่อแก้ไขค่าสมุดงานที่คำนวณแล้ว

การดำเนินการคำนวณ

ก่อนบันทึกหรือส่งออก ให้กระตุ้นการคำนวณสมุดงานใหม่ทั้งหมดโดยใช้อินเทอร์เฟซสมุดงานหลัก

procedure Calculate;

กลไกรองรับตระกูลฟังก์ชันทางคณิตศาสตร์ วันที่ ตรรกะ สตริง และสถิติมาตรฐาน โดยแก้ไขการพึ่งพาเซลล์ตามลำดับและติดตามห่วงโซ่อ้างอิงเพื่อป้องกันการอ้างอิงแบบวนกลับ

การประเมินสูตรตามบริบท

TXLSWorksheet และ TXLSXWorksheet เปิดเผย EvaluateFormulaAt ไว้คำนวณข้อความสูตรที่แถวและคอลัมน์ที่เลือก (เริ่มนับจาก 1) โดยไม่ต้องเขียนเซลล์ชั่วคราวและไม่กระทบสถานะการคำนวณของสมุดงาน

รูปแบบการอ้างอิง A1 และ R1C1 ชื่อท้องถิ่น การอ้างอิงเชิงโครงสร้าง dependency ของสูตร array root ค่า error ของ Excel แบบมีชนิด สถานะความไม่สดใหม่ การอนุญาตการอ้างอิงภายนอกแบบชัดเจน การยกเลิก และหมวดความล้มเหลวที่เสถียร ล้วนใช้ตัวประเมินสมุดงานปกติร่วมกัน

CreateFormulaEvaluationTemplate คอมไพล์จุดยึดที่เปลี่ยนแปลงไม่ได้เพียงครั้งเดียวแล้วใช้ซ้ำได้กับเซลล์เป้าหมายต่าง ๆ การอ้างอิงเชิงโครงสร้างแบบ A1 ที่ผลลัพธ์ขึ้นกับแถวของเป้าหมายจะถูกแก้ทีละบริบทเป้าหมาย ส่วนขีดจำกัดต่อการเรียกจะผูกความยาวสูตร จำนวนขั้นของรายการไวยากรณ์ ความลึก recursion และจำนวนไบต์ของอาร์เรย์ที่สร้างขึ้น

ดูชนิดผลลัพธ์ งบทรัพยากรค่าเริ่มต้น กฎอายุการใช้งาน และตัวอย่าง Delphi หรือ C++Builder ได้ที่ การประเมินสูตรตามบริบท

ตัวดำเนินการอาร์เรย์

ตัวดำเนินการทางคณิตศาสตร์ การเปรียบเทียบ การต่อข้อความ การปฏิเสธลบ unary และเปอร์เซ็นต์ รับได้ทั้งอาร์เรย์ที่คำนวณแล้วและค่า scalar

ค่า scalar จะ broadcast ไปทุกเอลิเมนต์ ส่วนอาร์เรย์แถวเดียวหรือคอลัมน์เดียวจะ broadcast ตามมิติที่เข้ากันได้ ถ้ารูปร่างไม่เข้ากันจะได้ #VALUE!

error จะถูกเก็บไว้ที่เซลล์ผลลัพธ์ของแต่ละตัว หนึ่งเซลล์หารด้วยศูนย์หรือเอลิเมนต์ไม่ถูกต้องจะไม่ทำให้ค่าที่คำนวณได้ของเซลล์ที่เหลือหายไป

COMBINA และ PERMUTATIONA ใช้ semantics แบบ scalar และ broadcast-array เดียวกัน โดยตัดอาร์กิวเมนต์จำนวนเต็มไม่ติดลบทิศทางเข้าหาศูนย์และตรวจ overflow ให้ ส่วน MUNIT คืนเมทริกซ์เอกลักษณ์สองมิติแบบ Double ที่กะทัดรัด

FormulaArrayMemoryLimit ค่าเริ่มต้น 64 MiB ใช้จำกัดเพย์โหลดเมทริกซ์ที่สร้างขึ้น ส่วน MUNIT ยังตรวจพื้นที่ spill ที่เหลือของแผ่นงานด้วย แทนการตั้งเพดานมิติคงที่

การตรวจสอบการมีอยู่ของสูตร

ISFORMULA(reference) ตรวจว่าเซลล์เป็นเจ้าของสูตรหรือไม่โดยไม่คำนวณเซลล์ที่ถูกอ้างถึง สูตรจึงยังตรวจพบได้แม้ค่าแคชของมันเป็น error หรือเป็นสตริงว่าง

ถ้าส่งช่วงสี่เหลี่ยมเข้าไปจะได้อาร์เรย์ Boolean รูปร่างเดียวกัน shared formula และสมาชิกของ array formula ทั้งแบบคลาสสิกและ XLSX จะคืน true ส่วน dynamic spill จะนับเป็นเซลล์สูตรเฉพาะจุด anchor เท่านั้น

ค่าตรง ๆ และชื่อที่กำหนดแบบ scalar จะคืน #VALUE! การอ้างอิงที่ไม่ถูกต้องจะคง #REF! ไว้ และอาร์เรย์ Boolean ที่สร้างขึ้นถูกจำกัดด้วย FormulaArrayMemoryLimit

ฝั่ง XLSX เขียนเป็น _xlfn.ISFORMULA แล้วเรียกคืนชื่อเปล่าตอนเปิดไฟล์ ODS ใช้ syntax มาตรฐาน of:=ISFORMULA ส่วนผลลัพธ์ Classic XLS จะปฏิเสธ future function ที่ยังไม่รองรับตั้งแต่ก่อนเขียนข้อมูลลงไฟล์

ฟังก์ชันข้อความยุคใหม่

UNICHAR ตัดอินพุตตัวเลขทิศทางเข้าหาศูนย์ แล้วแปลง Unicode scalar value ที่ใช้ได้เป็น UTF-16 รวมถึงกรณีออกมาเป็น surrogate pair สำหรับ supplementary plane และครอบคลุมช่วง private-use ทั้งหมด

อินพุตเป็นศูนย์หรือเลยช่วงจะคืน #VALUE! ส่วน surrogate code point ที่โดดเดี่ยวและ Unicode noncharacter จะคืน #N/A ถ้าอินพุตเป็นช่วงหรืออาร์เรย์ที่สร้างขึ้น error เหล่านี้จะถูกคงไว้รายเอลิเมนต์

BAHTTEXT แปลงตัวเลขจำกัด (finite) เป็นข้อความบาทและสตางค์ ปัดค่ากึ่งกลางออกจากศูนย์ที่ทศนิยมสองตำแหน่ง คงเครื่องหมายลบไว้แม้ขนาดของค่าที่ปัดแล้วจะเป็นศูนย์ และขยายเลขแบบ scientific notation ให้เต็มโดยไม่บีบผ่านชนิดจำนวนเต็ม

XLSX บันทึก UNICHAR เป็น _xlfn.UNICHAR แล้วเรียกคืนชื่อเปล่าเมื่อเปิดใหม่ ส่วน BAHTTEXT ใช้ตัวตนฟังก์ชันมาตรฐานของมันและคงชื่อเปล่าอยู่ในสูตรทั้ง XLS และ XLSX

การถดถอยและการพยากรณ์

LINEST และ LOGEST รับคอลัมน์หรือแถวตัวแปรต้น (predictor) ตั้งแต่หนึ่งชุดขึ้นไป แล้วคืนสัมประสิทธิ์ตามลำดับของ Excel ตามด้วยค่า intercept ในกรณีที่เลือกใช้

เมื่ออาร์กิวเมนต์ statistics เป็น true ทั้งสองฟังก์ชันจะคืนผลลัพธ์ครบห้าแถว ประกอบด้วยค่าคลาดเคลื่อนของสัมประสิทธิ์ สถิติการฟิต ค่าสถิติ F องศาอิสระ ผลรวมกำลังสองจากการถดถอย และผลรวมกำลังสองของเศษเหลือ

TREND และ GROWTH ใช้โมเดลหลายตัวแปรเดียวกัน และคงรูปร่างแถวหรือคอลัมน์ของค่าตัวแปรต้นชุดใหม่ที่ป้อนเข้าไป

การคำนวณใหม่ตาม dependency ของ XLS แบบคลาสสิก

TXLSWorkbook.Recalculate จะสร้างกราฟ dependency ของสูตรตั้งแต่การเรียกครั้งแรก แล้วประเมินสูตรโดยให้ precedent มาก่อน ไม่ใช่ตามลำดับเซลล์ในไฟล์

การเรียกครั้งถัด ๆ ไปจะใช้กราฟเดิมและคำนวณใหม่เฉพาะ dependent แบบทอดต่อของค่าที่เปลี่ยน บวกกับสูตรแบบ volatile การแก้สูตร ชื่อ โครงสร้างแผ่นงาน และการเขียนการอ้างอิงใหม่ จะทำให้กราฟหมดอายุและสร้างใหม่ได้อย่างปลอดภัย

การอ้างอิงวนรอบใช้การตั้งค่า EnableIteration, MaxIterations และ MaxIterationChange ของสมุดงานแบบคลาสสิก ส่วน UseFullPrecision=False จะใช้ความละเอียดตัวเลขแบบที่แสดงผลกับค่าแคชและการคำนวณที่อยู่ท้ายสายของมัน

Dependency ของชื่อที่กำหนด

การคำนวณแบบเพิ่มเติม (incremental) จะไล่ตามชื่อระดับสมุดงานและระดับแผ่นงานผ่านสูตรที่ซ้อนกันและการอ้างอิงหลายพื้นที่

สูตรของชื่อที่ไม่มีวงวนจะยื่น dependency ระดับเซลล์ที่แม่นยำเข้ามา ส่วนชื่อที่วนรอบแบบ recursive จะ fallback อย่างปลอดภัยไปเป็นการคำนวณใหม่แบบ volatile

ตารางข้อมูล What-If

แผ่นงานทั้งแบบ XLS คลาสสิกและ XLSX สมัยใหม่สร้าง คำนวณ อ่าน เขียน คัดลอก และปรับโครงสร้างตารางข้อมูล What-If แบบหนึ่งตัวแปรหรือสองตัวแปรได้

function AddDataTable(
  const AResultRange, ARowInputCell, AColumnInputCell: WideString
): TXLSDataTable;

ช่วงผลลัพธ์มีแต่เซลล์ผลลัพธ์ที่คำนวณแล้ว ส่วนสูตรและค่าทดลองใช้เซลล์รอบ ๆ ตาม layout เดียวกับ Excel

• ตารางแบบ column-input — เว้น ARowInputCell ให้ว่าง วางสูตรต้นทางไว้เหนือแต่ละคอลัมน์ผลลัพธ์ แล้ววางค่าทดลองไว้ทางซ้ายของแถวผลลัพธ์
• ตารางแบบ row-input — เว้น AColumnInputCell ให้ว่าง วางสูตรต้นทางไว้ทางซ้ายของแต่ละแถวผลลัพธ์ แล้ววางค่าทดลองไว้เหนือคอลัมน์ผลลัพธ์
• ตารางสองตัวแปร — ระบุเซลล์อินพุตทั้งสอง วางสูตรต้นทางไว้เหนือและทางซ้ายของช่วงผลลัพธ์ ค่าทดลองแบบ row-input ไว้เหนือผลลัพธ์ และค่าทดลองแบบ column-input ไว้ทางซ้าย

Sheet.Range['B1', 'B1'].Value := 0;
Sheet.Range['D1', 'D1'].Formula := '=B1*2+1';
Sheet.Range['C2', 'C2'].Value := 1;
Sheet.Range['C3', 'C3'].Value := 2;
Sheet.Range['C4', 'C4'].Value := 3;

Table := Sheet.AddDataTable('D2:D4', '', 'B1');
Workbook.Recalculate;

ตัวอย่าง column-input นี้คำนวณได้ 3, 5 และ 7 ที่ D2:D4 ค่าทดลองแต่ละตัวถูกใช้ผ่านบริบทการคำนวณที่แยกจากกัน สูตรที่ไปถึง B1 ทางอ้อมผ่านเซลล์สูตรอื่นจึงถูกคำนวณใหม่ได้ถูกต้อง

AddDataTable จะคืน nil ถ้าช่วงไม่ถูกต้อง การอ้างอิงอินพุตไม่ถูกต้อง หรือการอ้างอิงอินพุตทั้งสองว่างเปล่า คอลเลกชัน DataTables ของแผ่นงานเปิดเผยนิยามที่ได้และ flags ของอินพุตที่ถูกลบ

การลบเซลล์อินพุตที่ถูกอ้างถึงจะคงนิยามตารางไว้และคืน #REF! ให้ผลลัพธ์ของมัน ตรงกับ semantics ของสมุดงานที่เก็บอยู่

ฟังก์ชันที่ผู้ใช้กำหนด

ขยายกลไกสูตรด้วยตรรกะทางธุรกิจที่กำหนดเอง ลงทะเบียนฟังก์ชันของคุณเองโดยใช้เหตุการณ์ด้านล่าง

ชื่อแบบ external และ macro ที่อันตรายจะถูกปฏิเสธตั้งแต่ก่อนอาร์กิวเมนต์หรือ handler จะทำงาน เว้นแต่สมุดงานหรือการประเมินตามบริบทแต่ละครั้งจะเปิดใช้ AllowUnsafeFormulaCallbacks อย่างชัดเจน

• เหตุการณ์ OnUserFunction — จัดการฟังก์ชันพื้นฐานที่ผู้ใช้กำหนด
• เหตุการณ์ OnUserFunctionEx — แก้ไขฟังก์ชันที่ซับซ้อนด้วยพารามิเตอร์ช่วงเซลล์
• การเรียกกลับฟังก์ชันผู้ใช้ — ข้อกำหนดลายเซ็นสำหรับตัวจัดการภายนอก