HotXLS Deep Recalc로 Excel 수식 캐시 감사하기

HotXLS는 모든 스프레드시트 파이프라인이 결국 물어야 하는 질문, 즉 워크북에 저장된 숫자가 그것을 만들어 낸 수식과 여전히 일치하는지에 답합니다. CalculateAndVerify는 의존성 그래프 전체를 격리 오버레이로 재계산하고, 각 결과를 셀에 이미 들어 있던 캐시 값과 비교하며, 어긋나는 것들을 보고합니다. 기본적으로는 아무것도 바꾸지 않습니다

이것이 중요한 이유는 스프레드시트 파일이 수식 셀마다 두 가지를 저장하기 때문입니다. 수식과, 누군가 마지막으로 계산한 값. Excel은 둘을 동기화합니다. 세상의 다른 모든 것은 그러지 않을 수 있습니다. 오래된 라이브러리를 거친 파일, 부분 재계산, 손으로 편집한 XML 파트, 재계산 없이 값을 쓴 도구는 입력에서 더 이상 도출되지 않는 합계를 아무렇지 않게 내놓으며, 파일 포맷 어디도 그것을 표시하지 않습니다

수식과 어긋난 캐시 값은 왜 그렇게 위험한가?

모든 평범한 읽기 경로에서 보이지 않기 때문입니다. 뷰어에서 파일을 열고, API로 셀을 읽고, CSV나 PDF로 내보내면 캐시된 숫자를 얻습니다. 수식은 같은 셀에 그 자리에 있는데 아무도 둘을 비교하지 않습니다. 불일치는 누군가 Excel에서 워크북을 열 때만 드러나는데, 대부분의 설정에서 Excel은 로드 시 재계산하고, 지난 분기에 결재받은 리포트가 갑자기 다른 합계를 보여 줍니다

감사는 그 비교를 사고가 아니라 의도적이고 계획된 연산으로 만들기 위해 존재합니다. 체크섬 검증의 스프레드시트판입니다. 인테이크 파이프라인에서 돌리기에 쌉니다. 조용한 데이터 무결성 문제를 조치 가능한 리포트로 바꾸는 유일한 것이죠

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

오버로드는 셋이고 서로 다른 세 질문에 답합니다. 파라미터 없는 CalculateAndVerify는 불일치 개수를 돌려주는데, 헬스 체크에는 그것으로 충분합니다. 불일치 out 배열을 받는 오버로드는 셀들을 줍니다. TXLSRecalcAuditOptions를 받는 오버로드는 완전한 TXLSCalculationAuditReport를 돌려주며, 값이 어긋난다는 사실만이 아니라 감사가 무엇을 평가하지 못했는지 알아야 할 때 손을 뻗을 것은 이것입니다

오버레이, 그리고 감사가 쓰지 않는 이유

재계산된 모든 값은 셀 캐시가 아니라 오버레이에 떨어지고, 오버레이는 두 워크북 엔진 모두에서 셀 읽기 콜백의 맨 앞에 주입됩니다. 그 배치가 감사를 자기 일관적으로 만듭니다. B1이 재계산되고 C1이 B1에 의존하면 C1은 낡은 캐시가 아니라 이번 감사 패스의 값을 봅니다. 그렇지 않으면 상류의 에러 하나가 한 번 보고된 뒤 흡수되고, 모든 하류 셀이 잘못된 입력에 일치하는 것처럼 보입니다

재계산 값이 캐시와 일치하는 셀은 오버레이에 전혀 들어가지 않습니다. 마이크로 최적화가 아니라 감사를 감당할 수 있게 만드는 것입니다. 십만 개 수식의 깨끗한 워크북은 오버레이 쓰기를 0번 하고 패스는 전체 재계산 대비 1.35배 예산 안에 머뭅니다. 매 인테이크마다 돌릴 수 있는 것과 분기마다 한 번 돌리는 것의 차이죠

평가는 의존성 그래프에서 도출된 직렬 위상 순서를 따르며 모든 노드를 먼저 dirty 표시하므로, 각 셀은 자기 입력 뒤에 정확히 한 번 계산됩니다. 저장된 워크북을 감사하는 대신 살아있는 워크북을 최신으로 유지하는 증분 장치가 필요하다면 그것은 다른 메커니즘으로, 증분 재계산과 의존성 그래프에 기술되어 있습니다

실패는 분류되지, 한데 뭉치지 않습니다

감사가 평가하지 못하는 셀은 값이 어긋나는 셀과 같은 발견이 아니며, TXLSCalculationAuditIssueKind는 범주를 분리해 둡니다. xlcaiCacheMismatch는 값의 불일치입니다. xlcaiMissingFunction과 xlcaiMissingName은 평가기가 구현하지 않았거나 해석할 수 없는 것을 만났다고 말합니다. xlcaiUnsupportedArguments는 지원 부분집합 밖의 인자 모양을 커버합니다. xlcaiExternalReferenceDenied와 xlcaiExternalReferenceMissing은 정책 거부와 없는 워크북을 가릅니다. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled, xlcaiInternalFailure가 집합을 완성합니다

흔한 전제를 뒤집으므로 한 구분은 적어 둘 가치가 있습니다. 양수 Excel 에러 코드는 결과이지 실패가 아닙니다. 정당하게 #DIV/0!로 평가되는 셀은 올바르게 계산된 것이므로, 감사는 그 에러를 오버레이에 저장하고 다른 값과 똑같이 캐시와 비교합니다. 의도적인 에러 셀들로 가득한 워크북은 발견 0을 낳고, 값이 캐시된 뒤 에러가 나타나거나 사라진 워크북은 정확히 여러분이 원하는 발견들을 낳습니다

순환 참조는 자체 처우를 받습니다. 사이클 안의 노드는 위상 순서에 절대 들어가지 않으므로 각각 xlcaiCircularReference로 개별 보고되고, 감사는 반복 솔버를 돌리지 않습니다. 의도적인 읽기 전용 계약입니다. 반복이 켜져 있는지는 결과 코드를 어떻게 해석해야 하는지에 영향을 줄 뿐 감사가 하는 일을 바꾸지 않습니다. 반복 평가의 역학은 반복 계산과 순환 참조에서 별도로 다룹니다

실패 체인 읽기

수식 평가가 실패했을 때 어느 셀이 실패했는지 아는 것만으로는 거의 부족합니다. 실패는 보통 참조 체인의 세 층 아래에 있으니까요. 그래서 각 이슈는 최외곽 프레임부터 렌더링된 Stack 문자열을 실으며, 형태는 Sheet1!A1 > Sheet1!B2 > Data!C7입니다. 보고서는 우연히 들여다본 셀이 아니라 실제로 깨진 셀을 가리킵니다

레코더는 경계가 있습니다. MaxStackFrames는 기본 64에 하한 8이고, 유지되는 것은 가장 깊이 실패한 체인입니다. 안쪽 프레임은 실패가 자기에게서 발원할 때 체인을 기록하고, 뒤이어 언윈딩하는 바깥 프레임들은 그것을 덮어쓰지 않습니다. 어떤 체인이든 예산을 넘겼다면 Report.StackTruncated가 설정되는데, 이것은 짧은 체인과 끝까지 보지 못한 체인을 가릅니다

// 기본은 읽기 전용입니다. ApplyResults는 완전히 성공한 감사 뒤에만
// 오버레이를 확정하며, 감사가 도는 동안 워크북 구조가 바뀌면 확정을
// 거부하는 쓰기 가드 아래에서 이루어집니다
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // 정확 비교, 드리프트를 드러냄
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // 감사는 다음 노드 경계에서 멈춥니다
end;

감사에게 워크북 수리를 맡겨도 되는 때는?

실패 부류 이슈가 완전히 없는 깨끗한 결과일 때만이고, 그것이 정확히 ApplyResults가 대신 강제하는 조건입니다. 확정은 완전히 성공한 패스 뒤에 일어나고, 취소되지 않았으며, 구조 가드를 통과합니다. 바이너리 엔진은 워크북 변경 식별자를 감시하고, OOXML 엔진은 워크시트별 구조 세대를 스냅샷합니다. 감사가 도는 동안 무엇이든 움직였다면 결과는 더 이상 존재하지 않는 워크북을 기술하는 것이고 확정은 거부됩니다

의도적 비대칭에 주목하세요. 캐시 불일치는 적용을 막지 않습니다. 그것들이야말로 확정이 수리하러 온 것입니다. 실패 부류 이슈는 막습니다. 일부 수식을 평가할 수 없었던 워크북은 절반만 수리되고, 절반 수리된 워크북은 불신해야 한다고 아는 미수리 워크북보다 나쁩니다

허용 오차는 기본값이 아니라 정책 결정

기본 비교는 상대 허용 오차를 끈 상태의 1E-6 절대 허용 오차로, 클래식 동작을 보존하고 4E-7의 드리프트를 조용히 받아들입니다. 보통은 옳습니다. 파일을 만든 무엇이든과 현재 평가기 사이의 부동소수점 평가 순서 차이는 긴 합계에서 그 크기의 차이를 내고, 그것을 무결성 발견으로 보고하는 것은 노이즈입니다

질문이 다를 때는 두 허용 오차를 0으로 설정하세요. 어떤 평가기가 버전 사이에서 동작을 바꿨는지, 서드파티 도구가 미묘하게 다른 방식으로 값을 재작성하는지 알아내려 할 때입니다. 0에서는 같은 4E-7 드리프트도 보이고 나머지 전부도 보입니다. 허용 오차는 무슨 질문을 하는지에 따라 고르고, 그 선택을 보고서 옆에 기록하세요. 허용 오차 없는 보고서는 해석할 수 없습니다

이웃한 두 기능이 그림을 완성합니다. 단일 수식이 왜 그 값을 내는지 알고 싶을 때는 수식 평가 트레이서의 단계별 뷰가 맞는 도구입니다. 캐시 값을 재계산 없이 그대로 존중받길 의도할 때, 예컨대 도착한 그대로 파일을 정확히 재현해야 하는 인테이크 경로에서는, 그 모드가 재계산 없이 캐시된 수식 값 읽기에 기술되어 있습니다. 감사는 그 둘 사이에 앉는 것입니다. 캐시를 신뢰하는 것이 안전한지 알려 줍니다. 바이너리와 OOXML 양 엔진용으로 HotXLS Delphi 스프레드시트 컴포넌트에 실려 나옵니다