port สูตร Excel มาเว็บให้ตรงทุกตำแหน่งทศนิยม: กับดัก floating point

พอดีผมรับงานพอร์ตเครื่องคำนวณโครงสร้าง (พวกออกแบบคาน แป จันทัน ฐานราก) จากไฟล์ Excel ก้อนโตมาเป็นเว็บที่รันด้วย JavaScript ล้วน กติกามีข้อเดียวคือ "ค่าที่เว็บออกต้องตรงกับ Excel แบบ bit-for-bit" เพราะ Excel คือต้นฉบับความจริง เว็บเป็นแค่ตัวพอร์ต ตอนเทียบด้วยสายตา (ปัดสามตำแหน่ง) มันเป๊ะทุกช่อง แต่พอเขียน regression ที่เทียบค่าเต็มความละเอียด ปรากฏว่าแดงเป็นแถบ — ค่าเพี้ยนที่ตำแหน่งประมาณ 1e-13 ผมนั่งไล่อยู่ครึ่งวันกว่าจะเข้าใจว่ามันไม่ใช่คณิตศาสตร์ผิด แต่เป็นเรื่องลำดับการคำนวณของ floating point ล้วนๆ เลยเอาบทเรียนมาเล่าให้ฟัง

ชื่อชีต ตำแหน่งเซลล์ และตัวเลขในบทความเป็นตัวอย่าง โครงสร้างวิธีคิดและโค้ดคือของจริง

ก่อนอื่น: อ่านสูตร Excel สองชั้น ไม่ใช่แค่ค่าที่เห็น

จุดพลาดที่คนพอร์ตทำกันบ่อยคือเปิด Excel แล้วก๊อปตัวเลขที่ตาเห็นมาเป็นเป้า ตัวเลขที่ตาเห็นมันคือค่าที่ผ่าน format แล้ว (ปัดทศนิยมเพื่อโชว์) ซึ่งไม่ใช่ค่าจริงในเครื่อง และที่สำคัญกว่าคือมันไม่บอกเราว่า Excel คำนวณด้วยลำดับไหน ผมเลยอ่านสองชั้นเสมอด้วย openpyxl ชั้นหนึ่งเอา "สูตร" อีกชั้นเอา "ค่าที่ประเมินแล้วเต็มความละเอียด":

# read_cells.py — อ่าน Excel สองชั้น: สูตร (formula) กับ ค่า (value)
import openpyxl

PATH  = "roof-design.xlsm"   # ไฟล์ต้นทาง (ชื่อสมมติ)
SHEET = "Beam"               # ชีตคาน

wf = openpyxl.load_workbook(PATH, data_only=False)   # ได้ "สูตร" เป็น string
wv = openpyxl.load_workbook(PATH, data_only=True)    # ได้ "ค่าที่ประเมินแล้ว"
sf, sv = wf[SHEET], wv[SHEET]

# แผนที่เซลล์ต้อง map เองก่อน: R8=L(สแปน), E12=w(โหลด), X16=M(โมเมนต์)
for cell in ("R8", "E12", "X16"):
    v = sv[cell].value
    # บังคับ .17g เอง — ถ้าใช้ repr Python มันปัดโชว์ให้สั้นเหมือนกัน (3.888)
    print(f"{cell}: {v:<22.17g} <=  {sf[cell].value}")

ผลที่ได้จะเห็นทั้งเป้าเต็มๆ และสูตรที่บอกลำดับ:

R8:  3.6000000000000001    <=  =Input!D8
E12: 2.3999999999999999    <=  =Input!D12
X16: 3.8879999999999999    <=  =E12*R8^2/8

เห็นบรรทัด X16 ไหมครับ — โมเมนต์คานช่วงเดียวรับ UDL คือ M = wL²/8 ค่าจริงในเครื่องคือ 3.8879999999999999 ไม่ใช่ 3.89 ที่ตาเห็น (แม้แต่ input 3.6 กับ 2.4 ก็ไม่ตรงเป๊ะในเลขฐานสอง) ที่ต้องบังคับ .17g เพราะแม้แต่ repr ของ Python ก็ปัดโชว์ให้สั้นเป็น 3.888 เหมือนกัน — เป้าที่จะเอาไปเทียบต้องขุดออกมาให้ครบทุกหลัก ส่วนสูตร =E12*R8^2/8 บอกเราตรงๆ ว่า Excel ยกกำลัง R8^2 ก่อน แล้วค่อยคูณ ตรงนี้แหละคือหัวใจของกับดักตัวแรก

กับดักที่หนึ่ง: wLL ไม่เท่ากับ w*L^2

ปัญหาคือ Excel กับ JavaScript "ยกกำลัง" คนละจังหวะ Excel ประเมิน ^ เป็นหน่วยเดียวก่อน แปลว่า L^2 = L*L เสร็จเป็นก้อน แล้วค่อยเอา w มาคูณ กลายเป็น w*(L*L) ส่วน JavaScript ไม่มี operator ^ (นั่นคือ XOR) คนพอร์ตส่วนใหญ่เลยเขียน w*L*L ตรงๆ ซึ่ง JS อ่านซ้ายไปขวา = (w*L)*L — คูณ w กับ L ก่อน แล้วค่อยคูณ L อีกที คนละลำดับกับ Excel

การคูณของ floating point ไม่มีคุณสมบัติการเปลี่ยนหมู่ (not associative) (w*L)*L กับ w*(L*L) ปัดเศษคนละจุด ผลเลยต่างกันได้ที่ ULP สุดท้าย ลองของจริง:

// M = w * L^2 / 8   (โมเมนต์คานช่วงเดียว รับ UDL)
const w = 2.4;   // kN/m
const L = 3.6;   // m

const naive = w * L * L / 8;    // JS อ่านซ้ายไปขวา = ((w*L)*L)/8
const excel = w * (L * L) / 8;  // ครอบ square ให้เป็นหน่วยก่อน = ตรงกับ Excel =w*L^2/8

console.log(naive.toPrecision(20));  // 3.8880000000000003446
console.log(excel.toPrecision(20));  // 3.8879999999999999005
console.log(naive === excel);        // false

ต่างกันแค่ 4.4e-16 (ประมาณหนึ่ง ULP) ฟังดูจิ๊บจ๊อย แต่ถ้าเป้าคือ bit-for-bit มันคือแดง เพราะ Excel ให้ 3.8879999999999999 ส่วนโค้ด naive ให้ 3.8880000000000003 ทางแก้ไม่ต้องคิดมาก — ทุกที่ที่ Excel เขียน ^2 ผมครอบ square เป็นก้อนเดียวให้หมด: L*L แล้วค่อยคูณตัวอื่น อย่าปล่อยให้ไหลซ้ายไปขวา

กฎที่ผมยึด: ตัวไหน Excel ยกกำลัง ให้ห่อวงเล็บครอบ square เสมอ ไม่ว่าจะเป็น L², S², arm², a² หรือ Bx²/By² ในสูตรฐานราก ทุกตัวต้องเป็น x*(y*y) ไม่ใช่ x*y*y

กับดักที่สอง: จัดพีชคณิตให้สวย แต่หลุดจาก Excel

ตัวนี้ร้ายกว่า เพราะมันคือความหวังดีที่ทำพัง สูตรจันทันที่มีระยะยื่น a ในไฟล์ Excel เขียนไว้เป็น (L+a)^2 * (L-a)^2 ตอนพอร์ต ผมเผลอจัดรูปให้มัน "สวยขึ้น" เพราะทางคณิตศาสตร์ (L+a)²·(L−a)² มันเท่ากับ (L²−a²)² เป๊ะ เขียนสั้นกว่า คำนวณครั้งเดียวจบ ดูฉลาดกว่าด้วยซ้ำ แต่พอเทียบ Excel มันหลุด:

const L = 4.2, a = 0.8;

// รูปที่ Excel เขียนไว้จริง
const asExcel  = (L + a) * (L + a) * ((L - a) * (L - a));  // (L+a)^2 * (L-a)^2

// รูปที่ผมจัดใหม่ให้ "สวย" — เท่ากันทางคณิต แต่คนละ bit
const cleaner  = (L * L - a * a) * (L * L - a * a);        // (L^2 - a^2)^2

console.log(asExcel.toPrecision(20));  // 289.00000000000005684
console.log(cleaner.toPrecision(20));  // 289.00000000000000000
console.log(asExcel === cleaner);      // false

ตลกตรงที่รูปที่ผมจัดใหม่ดันได้ 289 พอดีเป๊ะสวยกว่าเดิม ส่วนรูปของ Excel ได้ 289.00000000000006 รุงรังกว่า แต่ Excel คือความจริง — ผมต้องพอร์ตให้ได้ 289.00000000000006 ไม่ใช่ 289 บทเรียนคือ อย่าจัดรูปพีชคณิตให้ตรงตำรา ให้จัดให้ตรง Excel แม้รูปนั้นจะดูด้อยกว่าก็ตาม เพราะทุกครั้งที่เราแยกตัวประกอบ กระจายวงเล็บ หรือย้ายข้าง เราเปลี่ยนลำดับการปัดเศษของ floating point ไปแล้ว

fixtures ต้องใช้ค่าเต็มความละเอียด ไม่ใช่ค่าที่ปัดแสดง

พอแก้สองกับดักบนเสร็จ เว็บตรง Excel ทุก bit แล้ว ทีนี้ต้องล็อกไว้ด้วย regression ไม่ให้ refactor รอบหน้ามาทำหลุด จุดตายของ fixture อยู่ที่ "ค่าเป้า" — ถ้าเผลอก๊อปค่าที่ตาเห็นจากช่อง Excel (เช่น 3.89) มาเป็นเป้า ต่อให้โค้ดถูก มันก็ให้ 3.888 ซึ่งต่างจาก 3.89 อยู่ 0.05% แล้ว เพื่อให้ผ่านเราก็จะไปคลาย tolerance เป็น 0.1% ทีนี้บั๊กจริงที่ทำค่าเพี้ยนไป 3.885 (ต่าง 0.08%) ก็เล็ดลอดผ่านหน้าตาเฉย — เท่ากับ regression ตาบอด:

// validate.js — เทียบกับค่าเต็มความละเอียดที่ได้จาก openpyxl data_only=True
const beamM = (w, L) => w * (L * L) / 8;   // เวอร์ชันที่แก้ลำดับแล้ว

const fixtures = [
  // way ที่ผิด: ก๊อปค่าที่ตาเห็น "3.89" -> ต้องคลาย tol เป็น 1e-3 -> จับ regression ไม่ได้
  { name: "M ตาเห็น",    excel: 3.89,               got: beamM(2.4, 3.6), tol: 1e-3 },
  // way ที่ถูก: ค่าเต็มจาก data_only=True -> tol แน่นได้ถึง 1e-9 -> refactor เพี้ยนนิดเดียวก็จับได้
  { name: "M เต็มความละเอียด", excel: 3.8879999999999999, got: beamM(2.4, 3.6), tol: 1e-9 },
];

for (const f of fixtures) {
  const rel = Math.abs(f.got - f.excel) / Math.abs(f.excel);
  console.log(`${rel <= f.tol ? "PASS" : "FAIL"}  ${f.name.padEnd(20)} rel=${rel.toExponential(2)}`);
}

ค่าที่ pad มาเป็นเป้าต้องเป็นตัวที่ data_only=True คืนมาแบบเต็ม 17 หลัก ไม่ใช่ตัวที่ ROUNDDOWN หรือ format ปัดโชว์ ยิ่งเป้าละเอียด ยิ่งตั้ง tolerance แน่นได้ ยิ่งจับ regression เจอเร็ว หลักคิดที่ผมยึดตลอดคือ ระหว่างคำนวณใช้ full precision ห้ามปัด เก็บทศนิยมไว้ให้ครบทุกขั้น ไปปัดเอาตอนแสดงผลขั้นสุดท้ายทีเดียว (โชว์สามตำแหน่งก็พอสำหรับคนอ่าน) การปัดกลางทางแม้แต่ครั้งเดียวคือการทิ้งความตรงกับต้นฉบับไปฟรีๆ

สรุปเป็นเช็คลิสต์ก่อนบอกว่าพอร์ตเสร็จ

รวบสิ่งที่เจอเป็นสี่ข้อสั้นๆ ไว้เช็คทุกสูตรก่อนปิดงาน:

1. อ่าน Excel สองชั้นเสมอ — data_only=False เอาสูตร (รู้ลำดับ) และ data_only=True เอาค่าเต็ม (เป็นเป้า) อย่าเชื่อค่าที่ตาเห็น
2. ตัวไหน Excel ยกกำลัง ครอบ square เป็นก้อน x*(y*y) ห้ามปล่อย x*y*y ไหลซ้ายไปขวา
3. พอร์ตพีชคณิตตามรูปที่ Excel เขียนเป๊ะๆ อย่าแยกตัวประกอบหรือจัดรูปให้สวย แม้รูปใหม่จะดู "ถูกกว่า"
4. fixture ใช้ค่าเต็มความละเอียดจาก data_only=True ตั้ง tolerance ให้แน่น (1e-9 หรือกว่านั้น) ระหว่างทางห้ามปัด ไปปัดตอนแสดงผล

ส่วนตัวผมมองว่าหลักพวกนี้ไม่ได้จำกัดแค่งานพอร์ต Excel มันคือกฎเดียวกับการย้ายการคำนวณข้ามภาษา ข้าม compiler หรือข้าม GPU/CPU ตัวเลขในเครื่องมันไวต่อลำดับการคำนวณเสมอ ใครก็ตามที่ต้องทำผลลัพธ์ให้ตรงกับต้นฉบับแบบ bit-for-bit ต้องเลิกคิดว่า "ทางคณิตมันเท่ากัน" แล้วหันมาถามว่า "ต้นฉบับมันปัดเศษตรงไหนบ้าง" ใครจะเอาแนวนี้ไปใช้ ปรับ tolerance กับ tooling ตามงานได้ตามสะดวกครับ ตรงไหนผมอธิบายตกหล่นหรือพิมพ์ผิด เดี๋ยวกลับมาแก้เพิ่มให้ภายหลังครับ </content> </invoke>