HotXLS Tracer를 사용하여 Delphi에서 Excel 수식 디버깅하기

Excel은 눈에 띄는 곳에 작은 디버거를 숨겨두고 있습니다. 셀을 선택하고 '수식(Formulas)'을 연 다음 '수식 계산(Evaluate Formula)'을 클릭하면 하나의 하위 표현식에 밑줄이 그어진 수식을 보여주는 대화 상자가 나타납니다. '계산(Evaluate)'을 누르면 해당 하위 표현식이 해당 값으로 축소되고 그다음 항목에 밑줄이 그어지며 한 번에 하나의 축소 단계를 거치면서 긴 표현식이 단일 숫자로 줄어드는 것을 볼 수 있습니다. 이것은 중첩된 IF 문의 어느 분기가 실제로 실행되었는지 또는 어떤 참조가 잘못된 합계를 제공했는지 찾는 가장 빠른 방법입니다. HotXLS는 TXLSFormulaTracer를 통해 그 정확한 동작을 재현하므로 Delphi 또는 C++Builder 프로그램은 통합 문서를 감사하거나, 생성된 수식을 디버깅하거나, 누군가에게 결과가 왜 그렇게 나왔는지 가르치기 위해 동일한 단계 목록을 렌더링할 수 있습니다. 기록된 각 단계에는 하위 표현식 텍스트와 축소되는 값이 포함됩니다

축소 엔진이 표현식을 탐색하는 방법

Tracer는 계산 엔진 내부까지 침투하지 않습니다. 수식을 토큰화하고 재귀 하향 파서(recursive-descent parser)로 구문을 분석한 다음, 트리에서 평가 가능한 가장 안쪽 하위 표현식부터 깊이 우선(depth-first)으로 축소합니다. 노드가 값으로 축소되면 그 값은 리터럴로 주변 표현식에 다시 대입되고 엔진은 실제 계산기에게 이제 더 단순해진 표현식을 다시 계산하도록 요청합니다. 모든 단계는 프라이빗 바로가기가 아닌 워크시트의 퍼블릭 Calculate 메서드를 통해 평가되므로 각 단계는 셀을 완전히 다시 계산할 때 생성되는 결과와 정확히 일치합니다. 파서는 설계상 비침투적이므로 상태를 방해하지 않고 모든 워크시트에 대해 실행할 수 있습니다

파서는 연산자 우선순위 사다리를 따르며, 우선순위 밴드당 하나의 재귀 레벨을 갖습니다. 가장 낮은 결합도부터 가장 높은 순서로 밴드는 다음과 같습니다: 레벨 0 비교(=, <>, <, >, <=, >=), 레벨 1 문자열 연결(&), 레벨 2 덧셈과 뺄셈, 레벨 3 곱셈과 나눗셈, 레벨 4 거듭제곱, 그리고 마지막으로 그 아래에 단항 더하기와 빼기가 있습니다. 각 레벨은 피연산자를 얻기 위해 자신보다 상위 레벨의 구문을 분석하므로 상위 밴드가 더 강하게 결합됩니다. 이는 Excel이 적용하는 것과 동일한 우선순위이며, A1*B1+A2*B1이 합계 전에 두 곱을 먼저 축소하는 이유입니다. 곱셈은 레벨 3에, 덧셈은 레벨 2에 있으므로 곱셈이 트리에서 더 깊숙이 위치하고 먼저 축소됩니다

수식 추적 및 단계 진행

사용법은 Demo/Delphi/FormulaTrace/FormulaTrace.dpr에 제공된 데모와 같습니다. 워크시트를 작성하거나(또는 기존 통합 문서를 열거나), 시트 위에 Tracer를 구성하고, Trace를 호출한 다음, 반환된 배열을 반복합니다. 각 TXLSFormulaStep은 들여쓰기를 위한 Depth, 원래 하위 표현식인 Source, 피연산자가 이미 대입된 해당 하위 표현식인 Expression, 그리고 단계의 결과인 Value를 노출합니다

uses
  SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Tracer: TXLSFormulaTracer;
  Steps: TXLSFormulaStepArray;
  Final: Variant;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Order');
    Sheet.Cells[1, 1].Value := 10;    // A1 수량
    Sheet.Cells[1, 2].Value := 25;    // B1 단가
    Sheet.Cells[1, 3].Value := 0.08;  // C1 세율

    Tracer := TXLSFormulaTracer.Create(Sheet);
    try
      Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
      for I := 0 to High(Steps) do
        Writeln(StringOfChar(' ', Steps[I].Depth * 2),
                Steps[I].Source, ' -> ', Steps[I].Expression,
                ' = ', VarToStr(Steps[I].Value));
      Writeln('result = ', VarToStr(Final));
    finally
      Tracer.Free;
    end;
  finally
    Book.Free;
  end;
end;

셀 참조가 먼저 확인되어 고유한 단계로 나타나고, 그 다음 곱셈이 축소되고, 괄호로 묶인 세금 요소가 축소되며, 마지막으로 최종 곱셈이 마무리됩니다. Depth 필드를 사용하면 들여쓰기를 할 수 있으므로, Excel이 바깥쪽 용어보다 가장 안쪽 용어에 밑줄을 긋는 것과 정확히 일치하게 가장 안쪽의 축소가 눈에 띄게 가장 깊숙이 위치하게 됩니다

로케일 없는 리터럴 함정

전체 구조에서 가장 위험한 세부 사항은 영어 컴퓨터에서는 보이지 않지만 독일어 컴퓨터에서는 요란하게 깨집니다. 계산된 숫자가 수식 텍스트로 다시 대입될 때 문자열로 작성된 후 .을 소수점으로 취급하는 계산 엔진에 의해 다시 구문 분석되어야 합니다. 대입 시 시스템 로케일을 사용하는 경우 독일어 TFormatSettings는 세금 요소에 대해 1,08을 쓰고, 쉼표는 인수 구분 기호로 읽히게 되며, A1*B1*1,08의 재계산은 잘못된 모양으로 구문 분석되거나 완전히 실패하게 됩니다

Tracer는 생성 시 DecimalSeparator를 .로 강제하고 그룹화 문자가 아예 방출되지 않도록 ThousandSeparator를 #0으로 설정하여 고정시킨 프라이빗 TFormatSettings를 통해 모든 숫자 리터럴의 형식을 지정함으로써 이 문제를 방지합니다. 그러면 FloatToStr은 운영자의 지역 설정에 관계없이 엔진이 항상 다시 읽을 수 있는 리터럴을 생성합니다

// 개념적으로 Tracer가 생성 시 한 번 고정하는 것
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// 축소된 모든 숫자는 다음으로 작성됩니다: FloatToStr(Double(V), FFloatFmt)

이것은 작성자 본인의 테스트에서는 절대 나타나지 않다가 다른 로케일의 고객이 동일한 코드를 실행할 때만 표면화되는 종류의 버그이므로 명확하게 언급할 가치가 있습니다: 수식 텍스트를 통해 값을 왕복(round-tripping)하는 것은 직렬화(serialization) 문제이며, 직렬화는 로케일이 없어야(locale-free) 합니다

부울 값은 1과 0으로 축소됩니다

관련된 대입 결정은 논리값과 관련이 있습니다. 하위 표현식이 부울로 평가될 때 Tracer는 이를 TRUE나 FALSE가 아니라 1 또는 0으로 다시 씁니다. 그 이유는 축소된 리터럴이 주변의 어떤 컨텍스트에서든 깔끔하게 다시 구문 분석되어야 하며 연산은 꽤 까다로운 상황이기 때문입니다. 만약 A1>A2와 같은 비교가 TRUE 텍스트로 축소되고 그 텍스트가 TRUE*B1 안에 놓인다면, 재계산은 엔진이 곱셈에서 노출된 부울 키워드를 허용하는지 여부에 의존하게 됩니다. 1을 대입하면 1*B1은 어떤 연산 위치에서도 모호하지 않기 때문에 해당 문제를 완전히 피할 수 있습니다. 이는 숫자가 예상되는 순간 TRUE가 1로, FALSE가 0으로 작동하는 Excel 고유의 강제 변환과도 일치합니다

원자적으로 축소되는 함수 호출

단순한 단계 엔진은 함수의 인수를 먼저 축소한 다음 호출을 축소합니다. 이는 Excel에서는 틀린 것이며 Tracer는 의도적으로 그렇게 하지 않습니다. 함수 호출은 원래 텍스트에서 단일 단계로 전체적으로 평가됩니다. 그 이유는 단락(short-circuit) 의미론 때문입니다. IF, CHOOSE 및 IFERROR는 선택한 분기만 평가하며 인수를 먼저 축소하면 엔진이 Excel에서 절대 건드리지 않는 분기를 계산해야 합니다. 전형적인 희생양은 IF(B1=0,0,A1/B1)와 같은 0으로 나누기 방지(guard)입니다: Tracer가 IF를 평가하기 전에 A1/B1을 축소하면 이 방지가 오작동하여 애초에 방지하기 위해 존재하는 바로 그 오류를 발생시킵니다. 전체 호출을 원자적으로 평가함으로써 Tracer는 이러한 방지가 작동하도록 하는 지연 평가(lazy evaluation)를 유지합니다

// IF는 하나의 원자적 단계이며, 선택된 분기만 평가됩니다.
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2가 참이므로 이 단계는 선택된 결과로 A1*B1을 기록합니다.
// Excel이 수행하는 것과 정확히 일치하게 A2*B1은 결코 계산되지 않습니다.

단점은 함수 호출 내부를 개별 단계로 보지 못한다는 점이지만, 이것이 올바른 동작입니다. Excel이 수행하지 않는 인수 축소를 보여주는 것은 실제 단일 평가 단위인 호출을 처리하는 것보다 더 오해의 소지가 있는 추적이 될 것입니다

인수 구분 기호와 온전한 범위

두 가지 더 많은 정규화가 재계산을 정직하게 유지합니다. 계산 엔진의 컴파일러는 함수 인수 구분 기호로 ;를 기대하므로, Tracer가 파싱된 트리에서 함수 호출을 다시 작성할 때 사용자가 원래 ,를 입력했더라도 ;로 인수를 결합합니다. SUM(A1,A2,A3)으로 작성된 수식은 SUM(A1;A2;A3)으로 다시 계산되며 엔진은 이를 수용합니다. 값의 대입은 이러한 재구성을 필요하게 만드는 것이며 구분 기호를 올바르게 설정하는 것은 재구성의 구문 분석을 가능하게 하는 것입니다

범위 참조는 또 다른 경우입니다. A1:A3과 같은 범위는 스칼라가 아니며, 이를 소비하는 함수가 범위 인수를 기대하므로 세 개의 개별 값으로 분할되어서는 안 됩니다. Tracer는 범위를 원래 텍스트 그대로 온전하게 유지하고 포함하는 함수가 전체적으로 축소되도록 둡니다. SUM(A1:A3)*B1에서 범위는 전체로 유지되고 SUM(A1:A3)은 하나의 원자적 단계에서 하나의 숫자로 축소되며, 그런 다음에야 바깥쪽 곱셈이 실행됩니다. 이것은 범위 피연산자와 결과적으로 기여하는 스칼라 사이에 Excel이 긋는 동일한 경계입니다

// 범위 A1:A3는 절대 분할되지 않습니다; SUM은 하나의 원자적 축소이고,
// 그 다음 그 위에서 B1과의 곱이 축소됩니다.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
  Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));

이러한 규칙들을 종합하면 단계 목록이 단순히 대략적인 근사치가 아닌 Excel의 수식 계산 명령을 충실하게 모방하는 미러가 됩니다. 축소는 Excel이 수행하는 순서대로 일어나며, 대입된 리터럴은 어떤 로케일에서도 유지되고, 부울은 Excel이 강제하는 방식으로 변환되며, 지연 함수는 지연 상태를 유지합니다. 자체 함수로 엔진을 더 밀어붙이고 싶다면 수식 엔진 및 사용자 지정 함수 기사에서 함수를 등록하는 방법을 보여주며, 더 무거운 수치 작업을 원한다면 Delphi의 통계 분포 함수 기사에서 Tracer가 평가하는 기본 제공 라이브러리를 다룹니다. 이 모든 기능은 이 블로그의 다른 곳에서 다루는 읽기, 쓰기, 형식 지정 및 계산 API와 함께 Delphi 및 C++Builder용 HotXLS spreadsheet component의 일부로 제공됩니다