SUBTOTAL และ AGGREGATE กับแถวที่ซ่อนอยู่ใน Delphi ด้วย HotXLS

ถ้า SUBTOTAL(109, ...) กับ SUBTOTAL(9, ...) คืนตัวเลขเดียวกันบน workbook ที่มีแถวซ่อนอยู่ นั่นแปลว่าตัวใดตัวหนึ่งในสองตัวนี้ผิด HotXLS ซึ่งเป็น native Excel spreadsheet component สำหรับ Delphi และ C++Builder มีพฤติกรรมแบบนั้นเป๊ะ ๆ จนถึงเวอร์ชัน 2.197.0 เพราะเอนจินคำนวณของมันไม่มีทางถามเวิร์กชีตได้เลยว่าแถวหนึ่งซ่อนอยู่หรือไม่

อาการนี้แทบไม่เคยมาในรูปแบบรายงานบั๊กเกี่ยวกับรหัสฟอร์มูล่า มันมาในรูปแบบความไม่ตรงกัน: batch job บนเซิร์ฟเวอร์คำนวณผลรวมหนึ่ง ผู้ใช้เปิดไฟล์เดียวกันใน Excel พร้อม filter ที่ใช้อยู่ แล้วตัวเลขทั้งสองก็ต่างกันเท่ากับผลรวมของแถวที่ถูกกรองออกไปพอดี ไม่มีใครสงสัยฟังก์ชัน aggregation เลย เพราะสตริงฟอร์มูล่าใน cell เหมือนกันทุกประการในทั้งสองที่ ความแตกต่างอยู่ที่สิ่งที่ evaluator ได้รับอนุญาตให้เห็นทั้งหมด

ทำไม SUBTOTAL 109 ถึงรวมแถวที่ซ่อนอยู่ด้วย

เพราะใน engine ส่วนใหญ่ layer ที่ประเมินผลฟอร์มูล่าไม่เคยรู้เรื่องการมองเห็นของแถวเลย HotXLS เป็นกรณีตัวอย่างในตำราเรียน: เอนจินคำนวณใน lxCalc.pas เข้าถึงค่า cell ผ่าน callback TXLSGetValue ตัวเดียวที่ตอบค่ากลับมาสำหรับสามค่า (sheet, row, column) และไม่มีอะไรอื่นเลย การมองเห็นเป็น attribute ด้าน presentation ที่เก็บไว้ใน row record และไม่มีส่วนไหนของ record นั้นเดินทางลงไปตาม call chain เอนจินจึงมี path เดียวสำหรับ aggregation และครึ่งทั้งสองของตาราง function-number ของ SUBTOTAL ก็ resolve ไปยังมันทั้งคู่ นั่นไม่ใช่ข้อบกพร่องประเภทข้อผิดพลาดในการปัดเศษ มันคือเหตุผลทั้งหมดที่ครึ่งหลังของตารางนั้นมีอยู่ ECMA-376 Part 1 ที่ตีพิมพ์เป็น ISO/IEC 29500-1 นิยาม SUBTOTAL ไว้ในนิยามฟังก์ชันฟอร์มูล่าของมัน (§18.17.7) โดยมี argument แรกที่เลือกทั้ง aggregation ภายในและนโยบายเรื่องแถวซ่อน รหัส 1 ถึง 11 แมปไปยัง AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR และ VARP พร้อมกับรวมค่าบนแถวที่ถูกซ่อนด้วยมือด้วย รหัส 101 ถึง 111 เลือก aggregation สิบเอ็ดแบบเดียวกันนี้และไม่รวมแถวเหล่านั้น ผู้ใช้ที่พิมพ์ 109 แทนที่จะพิมพ์ 9 กำลังแสดงเจตนาที่ตั้งใจเกี่ยวกับข้อมูลที่ซ่อนอยู่ และเอนจินที่ยุบความแตกต่างนี้เข้าด้วยกันก็กำลังคว่ำเจตนานั้นอย่างเงียบ ๆ

หมายเลขฟังก์ชันแมปไปเป็นอะไรภายในเอนจิน

HotXLS resolve argument แรกของ SUBTOTAL ใน CalcSubtotalFunc ซึ่ง normalize รหัส 101 ถึง 111 ลงมาให้เป็นตัวระบุฟังก์ชันภายในเดียวกันกับรหัส 1 ถึง 11 แล้วจึง dispatch ไปยังตัว aggregation เอง ส่วนใหญ่ของตระกูลนี้ไหลผ่าน accumulator แบบ incremental ของ ExcelSum ซึ่งจัดการ SUM, COUNT, COUNTA, MIN, MAX และ AVERAGE ห้าตัวที่ทำแบบนั้นไม่ได้คือ STDEV, VAR, STDEVP, VARP และ PRODUCT ซึ่งต้องการการวนผ่านข้อมูลแบบ closed-form ดังนั้น CalcSubtotalFunc จึงส่งรหัสภายใน 12, 46, 193, 194 และ 183 ไปยัง reducer แยกต่างหากคือ SubtotalReduceVariance การแบ่งแยกนั้นเป็นสิ่งแรกที่ควรทำแผนที่ไว้ก่อนแตะอะไรเลย เพราะ path aggregation อิสระสองเส้นหมายความว่าลูปไล่ cell อิสระสองลูป และการแก้ไขที่ทำแค่กับเส้นเดียวจะสร้างผลลัพธ์ที่แย่ที่สุดเท่าที่จะเป็นไปได้ คือ SUBTOTAL(109, ...) เคารพ filter ในขณะที่ SUBTOTAL(107, ...) บนช่วงเดียวกันไม่เคารพ การนับลูปใน HotXLS พบว่ามีหกลูปเมื่อรวม AGGREGATE เข้าไปด้วย กระจายอยู่ในการประเมิน range, การรวบรวม range ธรรมดา และ reducer แยกต่างหากสามตัว

ทำไมถึงใช้ scratch field แทนที่จะสร้าง signature ใหม่หกตัว

เพราะการส่งพารามิเตอร์ใหม่ผ่านฟังก์ชันไล่ cell หกตัวบวกทุกอย่างที่เรียกมัน เป็นการเปลี่ยนแปลงกว้างบน hot code path เพื่อ boolean ตัวเดียว HotXLS มีตัวอย่างมาก่อนหน้าอยู่แล้วสำหรับทางเลือกนี้ คือฟิลด์ชั่วคราวบน calculator ในจิตวิญญาณเดียวกับ scratch field ที่ GetRangeInfo ใช้บันทึกไว้เมื่อ 3D reference resolve เข้าไปใน external workbook เวอร์ชัน 2.197.0 เพิ่มตัวที่สองเข้ามา เอนจินได้ callback type ใหม่คือ TXLSIsRowHidden ประกาศเป็นฟังก์ชันของ (SheetIndex, row) ที่คืนค่า Boolean เก็บไว้ใน FIsRowHidden บวก flag ชั่วคราว FIgnoreHiddenRows flag นี้ถูกติดตั้งที่จุดเข้าของ CalcSubtotalFunc เมื่อรหัสฟังก์ชันอยู่ในช่วง 101 ถึง 111 และที่จุดเข้าของ CalcAggregateFunc สำหรับรหัสตัวเลือกของ AGGREGATE ที่เลือกการไม่รวมแถวซ่อน จากนั้นลูปไล่ cell ทุกลูปจะตรวจสอบมันและข้ามหนึ่งแถวเมื่อมันถูกตั้งค่าไว้ โดยเพิ่มแค่หนึ่งบรรทัดต่อลูป

// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
  for rr := r1 to r2 do
  begin
    if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
      Continue;
    for cc := c1 to c2 do
    begin
      // ... fold Cells[rr, cc] into the accumulator ...
    end;
  end;

มีสองรายละเอียดในโค้ดการติดตั้งที่แบกความถูกต้องของทั้งแผนไว้ flag นี้ถูกบันทึกและคืนค่ากลับ แทนที่จะแค่ตั้งค่าแล้วล้างเฉย ๆ เพราะ argument ของ SUBTOTAL อาจบรรจุ expression ที่รันการประเมินผลของตัวเองในขณะที่ aggregation ชั้นนอกยังอยู่บน stack และงานที่ซ้อนอยู่นั้นต้องไม่สืบทอดหรือทำลาย gate ชั้นนอก และการคืนค่าอยู่ใน block finally เพราะ CalcSubtotalFunc มีทางออกก่อนกำหนดหลายทางสำหรับรหัส error flag ที่ยังติดตั้งค้างอยู่หลังการคืนค่าแบบ error จะทำให้ฟอร์มูล่าถัดไปที่ไม่เกี่ยวข้องกันในลำดับการคำนวณใหม่เสียหายอย่างเงียบ ๆ

prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
  FIgnoreHiddenRows := True;
try
  // aggregate over Item.Child[2] .. Item.Child[ChildCount]
  // every Exit path below is covered by the finally
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
end;

การทดสอบ Assigned คือสิ่งที่ทำให้การเปลี่ยนแปลงนี้ยังคงเข้ากันได้ HotXLS ขยาย constructor ของ calculator ด้วยพารามิเตอร์ตัวที่สามที่ค่าเริ่มต้นเป็น nil ดังนั้นโค้ดใดก็ตามที่สร้าง TXLSCalculator ด้วยการเรียกแบบสอง argument แบบเดิมก็ยัง compile ผ่านและยังได้พฤติกรรม include-hidden แบบเดิมอยู่ ไม่มีอะไรในรูปร่างของ API ที่มีอยู่เปลี่ยนไปเลย

บิตของแถวที่ซ่อนมาจากไหนจริง ๆ

มาจากเวิร์กชีต ผ่านสองแหล่งที่ต่างกัน เพราะ HotXLS บรรทุกเอนจิน workbook สองตัว ฝั่ง BIFF แบบเก่าตอบจาก TXLSRowInfoList.GetHidden ที่เข้าถึงผ่าน TXLSWorkbook.GetRowHidden ฝั่ง OOXML ตอบจาก TXLSXWorksheet.GetRowHidden ที่เข้าถึงผ่าน TXLSXWorkbook.GetCalcRowHidden ทั้งสองถูกเชื่อมต่อเข้ากับ calculator ตั้งแต่ตอนสร้าง ควบคู่ไปกับ callback ค่า cell ที่มันสะท้อนอยู่ ธรรมเนียมเรื่องแถวคือจุดที่สะพานแบบนี้มักผิดพลาดกัน จึงควรระบุให้ชัดเจน calculator ส่งแถวแบบ 0-based ให้ callback ตรงกับพิกัดที่ TXLSGetValue ใช้อยู่แล้ว เวิร์กชีต XLSX ทำ key map ของแถวซ่อนด้วยหมายเลขแถวแบบ 1-based เป๊ะตามที่ Excel นับแถว ซึ่งเป็นสิ่งเดียวกับที่ property สาธารณะ RowHidden[ARow] เปิดเผยด้วย สะพาน XLSX จึงบวกหนึ่งก่อนการค้นหา ส่วนสะพาน BIFF ไม่บวก เพราะ TXLSRowInfoList เป็นแบบ 0-based อยู่แล้ว สะพานทั้งสองปฏิบัติต่อ sheet index หรือแถวที่อยู่นอกช่วงที่ถูกต้องว่ามองเห็นได้ ดังนั้นคำค้นหาที่นอกขอบเขตจะถดถอยลงเป็นคำตอบแบบ include-hidden เดิม แทนที่จะทิ้งข้อมูลไป

อะไรเปลี่ยนไปสำหรับ workbook ที่ถูกกรอง

นี่คือกรณีที่สร้าง ticket support การใช้ AutoFilter ใน HotXLS ผ่าน ApplyAutoFilter ประเมินเงื่อนไขคอลัมน์และซ่อนทุกแถวข้อมูลที่ไม่ตรงกัน ซึ่งเป็นสิ่งเดียวกับที่ Excel ทำเมื่อผู้ใช้คลิก dropdown ของ filter ก่อน v2.197.0 แถวที่ซ่อนเหล่านั้นมองไม่เห็นสำหรับผู้ใช้แต่มองเห็นได้เต็มที่สำหรับเอนจินคำนวณ ดังนั้น SUBTOTAL(109, ...) ฝั่งเซิร์ฟเวอร์จึงรายงานผลรวมที่ยังไม่ถูกกรอง ตอนนี้การเรียกเดียวกันนั้นรายงานค่าที่ถูกกรองแล้ว

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  VisibleRows: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
    VisibleRows := Sheet.ApplyAutoFilter;   // hides the non-matching rows

    Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
    Book.Recalculate;
    // The cell value now agrees with what Excel shows for the same filter,
    // and VisibleRows tells you how many rows fed into it

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

การซ่อนด้วยมือทำงานแบบเดียวกัน เพราะ RowHidden[ARow] := True คือ state เดียวกับที่ filter เขียนไว้ ความเท่าเทียมนั้นตั้งใจให้เป็นแบบนั้นใน Excel และตอนนี้ก็เป็นจริงใน HotXLS ด้วย มีข้อสรุปหนึ่งที่ควรค่าแก่การเขียนโน้ตไว้ในเอกสารใดก็ตามที่มาพร้อม workbook ที่คุณสร้างขึ้น: ผลรวมที่คำนวณด้วยรหัส 109 เป็นตัวเลขที่ขึ้นกับมุมมอง ดังนั้นผู้รับที่ล้าง filter จะเปลี่ยนมัน เมื่อรายงานต้องระบุตัวเลขคงที่ไม่ว่าผู้อ่านจะทำอะไรกับมุมมองก็ตาม รหัส 9 คือทางเลือกที่ถูกต้องและเป็นแบบนั้นมาโดยตลอด Filter, validation และ table ครอบคลุมไว้ด้วยกันใน บทความเรื่อง data validation, AutoFilter และ table เพราะการซ่อนแถวไม่แตะฟอร์มูล่าใดเลย มันจึงไม่ทำให้ dependency graph สกปรกด้วยตัวเอง ซึ่งควรรู้ไว้ถ้าคุณพึ่งพา การคำนวณใหม่แบบ incremental บน dirty subgraph เพื่อให้ workbook ขนาดใหญ่ตอบสนองได้ไว

รหัสตัวเลือกของ AGGREGATE และขีดจำกัดหนึ่งที่ยังเปิดอยู่

AGGREGATE คือ SUBTOTAL ที่มี argument นโยบายตัวที่สอง และ HotXLS จัดการมันใน CalcAggregateFunc argument ตัวเลือกเข้ารหัสสวิตช์อิสระ: การเรียก SUBTOTAL และ AGGREGATE ที่ซ้อนอยู่ภายใน range ถูกข้ามหรือไม่ ค่าบนแถวที่ซ่อนถูกข้ามหรือไม่ และค่า error ถูกปิดกั้นแทนที่จะแพร่กระจายหรือไม่ HotXLS ติดตั้ง gate สำหรับแถวซ่อนที่ใช้ร่วมกันสำหรับรหัสตัวเลือก 2, 3, 6 และ 7 และปิดกั้นค่า error สำหรับรหัสตัวเลือก 4 ถึง 7 argument หมายเลขฟังก์ชันจากนั้นเลือก aggregation เหมือนกับที่ SUBTOTAL ทำ รวมถึงการส่ง variance, standard deviation และ product ไปยัง reducer ของตัวเองด้วย มีช่องว่างหนึ่งที่ถูกบันทึกไว้ว่ายังคงเหลืออยู่ และดีกว่าที่จะระบุไว้ที่นี่มากกว่าให้ค้นพบในการใช้งานจริง: semantics ของการเพิกเฉยต่อ SUBTOTAL ที่ซ้อนกันซึ่งเชื่อมโยงกับรหัสตัวเลือกต่ำยังไม่ถูก implement ใน HotXLS การตรวจจับ SUBTOTAL ที่ซ้อนกันภายใน range ที่ถูกอ้างถึงต้องการทำเครื่องหมาย state การเรียกซ้ำของ evaluator เพื่อให้ aggregation ชั้นในสามารถประกาศตัวเองต่อชั้นนอกได้ ซึ่งเป็นการเปลี่ยนแปลงที่ใหญ่กว่า gate ของแถวซ่อน ในทางปฏิบัติความเสี่ยงนี้เล็กน้อย เพราะ workbook จริงเกือบทั้งหมดวางฟอร์มูล่า SUBTOTAL ไว้นอก range ที่ฟอร์มูล่า SUBTOTAL อื่น aggregate ทับอยู่ ถ้า generator ของคุณสร้าง range aggregation ที่ทับซ้อนกันจริง ๆ อย่าพึ่งพารหัสตัวเลือกต่ำเพื่อกำจัดความซ้ำซ้อนนั้น

ตัวป้องกัน arity ที่มาพร้อมกัน

เวอร์ชัน 2.197.0 ยังปิดช่องว่างการตรวจสอบใน dispatcher เดียวกันด้วย และเหตุผลด้านการออกแบบก็เป็นเหตุผลเดียวกับที่ผลักดัน scratch field คือวางการตรวจสอบไว้ในที่ที่เขียนได้ครั้งเดียว body ของฟังก์ชันในตัวประมาณ 280 ตัวแต่ละตัวตรวจสอบจำนวน argument ของตัวเองเทียบกับ Item.ChildCount ซึ่งไม่มีขอบเขตที่สอดคล้องกันเลยสำหรับกรณีที่มี argument มากเกินไป การเรียกอย่าง =SIN(1,2) ไปถึง body ของฟังก์ชันที่ตรวจสอบ argument แรกของมัน เพิกเฉยต่อส่วนเกิน แล้วคืนตัวเลขที่ดูสมเหตุสมผลในจุดที่ Excel คืน #VALUE! HotXLS เก็บ arity ที่ประกาศไว้ของทุกฟังก์ชันในตัวไว้อยู่แล้วใน function registry ของมัน เปิดเผยผ่าน THashFunc.ArgsCnt โดย -1 กำกับฟังก์ชันแบบ variadic เช่น SUM, IF หรือ CONCAT เวอร์ชัน 2.197.0 ส่งต่อสิ่งนั้นผ่าน property ใหม่ TXLSFormula.FuncArgsCntByPtg และเพิ่ม gate หนึ่งตัวที่ด้านบนของ GetValueItemFunc ซึ่งเป็น dispatcher หลัก

lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
  lProvidedArgs := Item.ChildCount - 1;   // Child[0] is the function node
  if lProvidedArgs > lDeclaredArgs then
  begin
    Result := lxErrorValue;               // =SIN(1,2) now yields #VALUE!
    Exit;
  end;
end;

ตัวป้องกันนี้ปฏิเสธ argument ที่มากเกินไป และจงใจไม่พูดอะไรเลยเกี่ยวกับที่น้อยเกินไป การละเว้น argument ที่เป็นทางเลือกท้าย ๆ นั้นถูกกฎหมายใน Excel สำหรับ VLOOKUP, SUBSTITUTE และรายการยาวเหยียดของฟังก์ชันอื่น ดังนั้นการตรวจสอบแบบสมมาตรจะทำให้ฟอร์มูล่าที่ถูกต้องพังเพื่อจับฟอร์มูล่าที่ผิด ตัวระบุที่ไม่รู้จักจะรายงานว่าเป็น variadic และข้าม gate ไปทั้งหมด ซึ่งเป็นสิ่งที่ทำให้ฟังก์ชันที่ผู้ใช้กำหนดเองไม่ถูกกีดขวาง ถ้าคุณลงทะเบียนฟังก์ชันของคุณเอง พฤติกรรมที่อธิบายไว้ใน คู่มือเรื่องเอนจินฟอร์มูล่าและฟังก์ชันที่กำหนดเอง จะไม่ได้รับผลกระทบ การรวมศูนย์กรณี argument น้อยเกินไปเป็นงานแยกต่างหาก เพราะแต่ละ body ในทั้ง 280 ตัวนั้นมี semantics ของรหัส error ของตัวเอง และต้องถูกทบทวนทีละตัวแทนที่จะสันนิษฐานเอาเอง

เอนจินคำนวณที่กล่าวถึงที่นี่ ทั้ง workbook facade ทั้งสองตัว และ API ของ AutoFilter กับการมองเห็นของแถวที่ป้อนเข้าไปในนั้น เป็นส่วนหนึ่งของ HotXLS Delphi spreadsheet component ซึ่งมาพร้อม source code เต็มรูปแบบสำหรับ Delphi และ C++Builder และไม่ต้องติดตั้ง Excel บนเครื่องที่รันมันเลย