ลูกโซ่การเทียบ, cell ว่าง และ SUMIF ใน HotXLS Delphi

HotXLS Delphi Component ประเมิน =1<2<3 เป็น FALSE คำตอบเดียวกับที่ Excel 16 ให้ เพราะตั้งแต่ v2.384.3 parser ของสูตรพับตัวดำเนินการเทียบจากซ้ายไปขวา: 1<2 กลายเป็น TRUE และ TRUE<3 เป็น FALSE เพราะ boolean จัดอันดับสูงกว่าตัวเลขทุกตัว release เดียวกันยังทำให้ operand ว่างเทียบเท่ากับทั้ง 0 และ "" และให้ SUMIF ยืด sum range หนึ่ง cell ออกให้ได้รูปทรงเดียวกับ criteria range แต่ละเรื่องดูเหมือนเกร็ดเล็กเกร็ดน้อย จนกว่า workbook ที่คำนวณใน Delphi จะขัดกับ workbook ตัวเดียวกันที่เปิดใน Excel

ความขัดแย้งมักเริ่มจากสูตรที่คนเขียนด้วยสัญชาตญาณ ใครสักคนพิมพ์ =0<B2<100 เพื่อเช็กว่าจำนวนอยู่ในช่วง Excel ตอบ FALSE เงียบ ๆ ทุกแถว และชีตก็ส่งมอบไปพร้อม bug ที่อบไว้ในตัว calculation engine ไม่มีสิทธิ์ไปแก้เจตนาของผู้ใช้ หน้าที่ของมันคือผลิตค่าที่ Excel จะผลิต เพื่อให้ cached result ที่ HotXLS เขียนลงไฟล์ตรงกับสิ่งที่ Excel แสดงหลัง recalculation ก่อน v2.384.3 HotXLS ตอบ TRUE ให้การเช็กช่วงนั้นทุกแถว ผิดไปทางตรงข้าม รายงานที่ generate บนเซิร์ฟเวอร์จึงขัดกับรายงานฉบับเดียวกันที่เปิดบนเดสก์ท็อป

ทำไม =1<2<3 จึงคืน FALSE ใน Excel

Excel คืน FALSE เพราะมันอ่านลูกโซ่การเทียบเป็น (1<2)<3 แล้ว TRUE ข้างในก็แพ้การประชันอันดับ type ให้กับเลข 3 ตัว parser เก่าของ HotXLS อ่านข้อความเดียวกันเป็น 1<(2<3): TXLSSyntax.Parse_expr ใน lxFormula.pas parse operand หนึ่งตัว เห็น token การเทียบ แล้วเรียกซ้ำเข้า Parse_expr หาฝั่งขวา ทำให้ตัวดำเนินการเป็น right-associative ผลที่ได้คือ 1<TRUE และเลขต่ำกว่า boolean ผลจึงเป็น TRUE ความผิดพลาดเป็นแบบสมมาตร: =3>2>1 เป็น TRUE ใน Excel แต่เคยเป็น FALSE ใน HotXLS และ =1=1=TRUE เป็น TRUE ใน Excel แต่เคยเป็น FALSE ก่อนแก้ regression CalculateFormula_ComparisonChainsFoldLeftToRight ตรึงสูตรแบบนี้เจ็ดสูตรกับค่าที่ Excel 16 คืน แล้วรันทุกสูตรผ่านสถาปัตยกรรม engine ทั้งสองแบบ ทั้ง TXLSWorkbook คลาสสิกและ TXLSXWorkbook ที่เป็น XLSX แท้ ๆ ด้วยเมธอด Calculate ที่อธิบายไว้ในภาพรวม formula engine ของ HotXLS

const
  Formulas: array [0..6] of string = ('=1<2<3', '=3>2>1', '=(1<2)<3',
    '=1<(2<3)', '=1=1=TRUE', '=3>2>1=TRUE', '=1<2<3=FALSE');
  // สิ่งที่ Excel 16 คืน:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate ประเมินกับชีตที่ active และ
    // คืน Null เมื่อ workbook ไม่มีชีตเลย
    Xlsx.Sheets.Add('Data');
    for i := 0 to High(Formulas) do
      Writeln(Formulas[i], '  classic=', VarToStr(Classic.Calculate(Formulas[i])),
        '  xlsx=', VarToStr(Xlsx.Calculate(Formulas[i])));
  finally
    Xlsx.Free;
  end;
end;

การแก้เปลี่ยน Parse_expr ให้เป็น loop รูปทรงเดียวกับที่ Parse_expr1 ใช้กับ +, - และ & อยู่แล้ว มัน parse operand ตัวแรกด้วย Parse_expr1 และในขณะที่ token ถัดไปเป็น =, <>, <, >, <= หรือ >= ก็สร้าง node การเทียบ ผูกผลลัพธ์ซ้ายที่สะสมไว้เป็น child ตัวแรก parse operand ถัดไปด้วย Parse_expr1 แทนที่จะเป็น Parse_expr แล้วตั้ง node ใหม่เป็นผลลัพธ์ซ้ายสำหรับรอบถัดไป มีรายละเอียดสองจุดที่พลาดง่ายตอนแปลง recursion เป็น iteration และทั้งสองจุดอยู่ในโน้ตของผู้ดูแล: node ที่สะสมไว้ต้องส่งต่อด้วย (lChild := Item; Item := nil) ตามลำดับนี้ และเส้นทาง error ต้อง Exit หลังคืน node ที่สร้างครึ่ง ๆ กลาง ๆ แทนที่จะหลุดออกจาก loop แล้วคืน tree ที่ห้อยคอ

HotXLS จัดอันดับตัวเลข ข้อความ และ boolean ในการเทียบอย่างไร

HotXLS จัดอันดับ type ผสมแบบเดียวกับ Excel: ตัวเลขทุกตัวน้อยกว่าค่าข้อความทุกค่า และข้อความทุกค่าน้อยกว่า boolean ทุกตัว TXLSCalculator.CompareVariants ใน lxCalc.pas จัดประเภท operand ทั้งสองด้วย GetRetValueType ลงใน enumeration TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue) เมื่อสองประเภทต่างกันก็แค่เทียบ ordinal กัน ลำดับการประกาศของ enum นั้นจึงเป็นกฎข้าม type ภายในประเภทเดียวกันการเทียบเป็นแบบธรรมชาติ มีเกมเฉพาะแบบ Excel หนึ่งจุดสำหรับข้อความ: string ทั้งสองผ่าน lxUpperCase ก่อน ="abc"="ABC" จึงเป็น TRUE อันดับนี้แหละที่ทำให้ผลของลูกโซ่คิดต่อไม่ได้ถ้าไม่มีมัน TRUE<3 ไม่ใช่การบังคับ TRUE เป็น 1 แต่เป็น boolean ถูกเทียบกับตัวเลข และ boolean เป็นฝ่ายชนะ วันที่เป็น serial number ในสายตา engine (varDate ถูกจัดเป็น xlNumberValue) วันที่จึงอยู่ต่ำกว่าข้อความทุกข้อความ รวมถึงข้อความที่หน้าตาเหมือนวันที่ด้วย

cell ว่างเท่ากับอะไรในการเทียบ

cell ว่างที่ถูกใช้เป็น operand ของการเทียบเท่ากับ 0 เมื่ออีกฝั่งเป็นตัวเลข เท่ากับ "" เมื่ออีกฝั่งเป็นข้อความ และตั้งแต่ v2.384.53 เท่ากับ FALSE เมื่ออีกฝั่งเป็นค่าตรรกะ โดย A1 ว่าง =A1=0, =A1="" และ =A1=FALSE เป็น TRUE ทั้งหมด TXLSCalculator.CompareVarValues ซึ่งรับใช้ตัวดำเนินการเทียบทั้งหก จะแทนค่าว่างก่อนเรียก CompareVariants: ถ้ามี operand เป็น Null ตัวเดียวมันจะกลายเป็น WideString('') เมื่อคู่ของมันเป็น string, เป็น False เมื่อคู่ของมันเป็น boolean และเป็น 0 ในกรณีอื่น ว่างสองตัวเทียบกันเองยังเท่ากันโดยไม่ต้องแทนค่า เส้นทางเลขคณิตเปลี่ยนค่าว่างเป็น 0 มาตลอด =A1+1 จึงให้ 1 แต่ CompareVariants เก็บ Null ไว้เป็นอันดับต่ำสุดของตัวเอง ต่ำกว่าตัวเลขทุกตัว และตัวดำเนินการเทียบก็ใช้อันดับนั้นตรง ๆ

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // ตั้งใจปล่อยให้ A1 ว่าง

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: ค่าว่างถูกเทียบเป็น 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False เคยเป็น True ก่อน v2.384.3
end;

บรรทัดสุดท้ายคือตัวที่เจ็บตัวในการใช้งานจริง ภายใต้อันดับเก่าค่าว่างเล็กกว่าตัวเลขทุกตัว รวมถึงตัวเลขลบ =IF(A1<0,"overdrawn","ok") จึงติดป้ายว่า overdrawn ให้ cell ยอดคงเหลือว่างทุก cell และ =A1=0 เป็น FALSE กับ cell ที่ผู้ใช้คนไหน ๆ ก็จะบรรยายว่าเป็นศูนย์ ยังเหลือขอบเขตหนึ่งจุดหลัง v2.384.3: การแทนค่าเลือกได้แค่ระหว่าง 0 กับ string ว่าง ค่าว่างที่เทียบกับ boolean จึงกลายเป็น 0 ซึ่งอยู่ต่ำกว่าทั้ง TRUE และ FALSE และ =A1=FALSE บน A1 ว่างประเมินออกมาเป็น FALSE ตั้งแต่ HotXLS 2.384.53 ค่าว่างที่เทียบกับค่าตรรกะถูกถือเป็น FALSE ในทั้ง engine XLS และ XLSX อย่างที่ Excel ทำ: A1 ว่าง =A1=FALSE กับ =A1<TRUE คืน TRUE และ =A1=TRUE คืน FALSE นั่นยังแปลว่าการเทียบแยกค่าว่างกับ FALSE ไม่ออก ทั้งใน Excel และ HotXLS ตรงที่ชีตต้องการความต่างนั้น ให้ทดสอบด้วย ISBLANK หรือ =A1=""

ทำไม SUMIF ที่ sum range เป็นหนึ่ง cell ถึงคืน 0

SUMIF คืน 0 เพราะ HotXLS หนีบการวนซ้ำไว้ที่ช่วงที่เล็กกว่าของสองช่วง ขณะที่ Excel คงรูปทรงของ criteria range แล้วใช้ sum range แค่มุมบนซ้ายเท่านั้น =SUMIF(A1:A10,">5",B1) จึงหมายถึง B1:B10 ใน Excel ซึ่งเป็นความสะดวกที่ template สร้างมือจำนวนมากพึ่งพา worker ร่วม TXLSCalculator.GetValueItemRange2 เคยหดจำนวนแถวกับคอลัมน์ลงให้เท่า value range ซึ่งย่อตัวอย่างให้เหลือการทดสอบเดียวระหว่าง A1 กับ B1 v2.384.3 เอาการหนีบออก: loop ตอนนี้เดินไล่ criteria range แล้วอ่านแต่ละค่าที่ offset เดียวกันจากมุมบนซ้ายของ sum range เพราะ CalcSumIF กับ CalcAverageIF เรียก worker ตัวเดียวกัน AVERAGEIF จึงได้การขยายแบบเดียวกันด้วย และ sum range ที่ใหญ่กว่า criteria range ก็ถูกตัดให้เหลือรูปทรงเกณฑ์ด้วยเหตุผลเดียวกัน argument เกณฑ์ตรงกลางเป็น argument คลาสค่า ส่วนสองตัวนอกเป็นคลาสอ้างอิง ความต่างนี้เล่าไว้ในบทความเรื่อง implicit intersection กับคลาสของ argument

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    for Row := 1 to 10 do
    begin
      Sheet.Cells[Row, 1].Value := Row;          // คอลัมน์เกณฑ์: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // จำนวนเงิน: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // sum range หนึ่ง cell
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // sum range แบบระบุชัด
    if Book.Recalculate = lxOk then
      // ทั้ง D1 และ D2 เป็น 4000 (600+700+800+900+1000) D1 เคยเป็น 0 ก่อน v2.384.3
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT กับ YEARFRAC: การแก้เงียบ ๆ อีกสองเรื่อง

INDIRECT ตอนนี้เคารพ argument ตัวที่สอง และข้อความที่อยู่หลัง reference ที่ใช้ได้เป็น error แทนที่จะถูกเมิน เมื่อ a1 เป็น FALSE ข้อความถูก parse เป็น R1C1 แบบสัมบูรณ์ =INDIRECT("R2C3",FALSE) จึงอ่าน C2 โค้ดเก่าเมินธงนี้ อ่าน "R2" เป็นคอลัมน์ R แถว 2 แล้วคืน cell ผิดไปเงียบ ๆ ธงถูก dispatch ตาม variant type ของมัน (boolean, ตัวเลข หรือข้อความ) เพราะการแปลง string variant ตรงเป็น Double จะโยน exception ข้อความ R1C1 แบบสัมพัทธ์อย่าง R[1]C[1] คืน #REF! เพราะ INDIRECT ไม่มีจุดกำเนิดของ formula cell จะเอาไว้ resolve ด้วย และข้อความ A1 ที่มีตัวอักษรต่อท้ายอย่าง "B2 junk" ก็คืน #REF! เช่นกัน YEARFRAC ที่ basis 0 ตอนนี้ใช้กฎ NASD เรื่องวันสุดท้ายของกุมภาพันธ์ที่ DAYS360 implement ไว้แล้ว: เมื่อวันที่ทั้งสองเป็นวันสุดท้ายของกุมภาพันธ์ วันปลายทางจะกลายเป็น 30 แล้วจุดเริ่มที่เป็นวันสุดท้ายของกุมภาพันธ์จึงกลายเป็น 30 จาก 2024-02-29 ไป 2025-02-28 จำนวนนับตอนนี้เป็น 360 วัน เศษส่วนเป๊ะ ๆ เท่ากับ 1 ที่ซึ่ง Days360US ตัวเก่านับได้ 359

การแก้เหล่านี้รับประกันอะไร และบทเรียนคืออะไร

พฤติกรรมลูกโซ่การเทียบถูกประกันด้วย test ที่เทียบทั้งสอง engine กับค่าที่วัดจาก Excel 16 และ test นี้มีอยู่เพราะคำอธิบายแรกของการแก้ผิด release note ของ v2.384.3 เดิมบอกว่าการพับซ้ายไปขวาทำให้ =1<2<3 เป็น TRUE ซึ่งเป๊ะกับสิ่งที่ parser แบบ right-associative เก่าผลิต และตรงข้ามกับที่ทั้ง Excel และโค้ดใหม่คืน ไม่มีใครประเมินตัวอย่างเลย มันถูกเขียนจากสัญชาตญาณว่า "1 น้อยกว่า 2 ที่น้อยกว่า 3" note ถูกแก้และ test เจ็ดสูตรถูกเพิ่มใน commit ตามมา และกฎที่ได้ออกมาใช้กับทุกคนที่เขียนเอกสาร semantics ของ spreadsheet: รันตัวอย่างใน Excel ก่อนเขียนค่าที่คาดหวังลงไป การแทนค่า operand ว่างกับการขยาย SUMIF เดินตามพฤติกรรม Excel เดียวกัน รวมถึงกรณีค่าว่างเทียบกับ boolean ตั้งแต่ v2.384.53 ส่วน aggregate แบบมีเงื่อนไขที่ต้องข้ามแถวที่ถูกกรองหรือซ่อนด้วย ให้ตามกฎแยกต่างหากในบทความเรื่องแถวที่ซ่อนของ SUBTOTAL กับ AGGREGATE

HotXLS เป็น spreadsheet component แท้ของ Delphi กับ C++Builder ที่อ่าน recalculate และเขียน XLS, XLSX, ODS และ CSV ได้โดยไม่ต้องติดตั้ง Excel และกฎการเทียบ ค่าว่าง และ SUMIF ที่เล่าไว้นี้อาศัยอยู่ใน calculation engine ที่สถาปัตยกรรม workbook ทั้งสองแบบใช้ร่วมกัน รายการฟังก์ชันเต็มกับตัวเลือกการซื้อ license อยู่บนหน้าผลิตภัณฑ์HotXLS Delphi spreadsheet component