HotXLS 룩업 스캔과 거짓 순환 참조

열 B의 셀에 =VLOOKUP(A1,B:B,1)을 넣으면 Excel은 불평 없이 계산합니다. 같은 통합 문서를 의존성 그래프 재계산 엔진에 먹이면 순환 참조 오류를 받기 쉽습니다. 수식이 수식 자신을 담은 범위에 의존하기 때문입니다. HotXLS는 v2.361.98까지 정확히 그렇게 보고했습니다. 수정은 전체 컬럼 범위에 대한 특수 사례가 아니라, 스프레드시트 엔진에는 필요하고 평범한 유향 그래프에는 없는 두 종류의 의존성 간선 구분입니다

룩업 계열, 즉 LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP, XMATCH의 lookup-array 인자는 이제 스캔 참조로 표시됩니다. 스캔 참조는 여전히 더러움(dirtiness)을 씨 뿌리므로 범위 안의 셀을 편집하면 수식이 재계산되지만, 순환 탐지나 평가 순서에는 결코 기여하지 않습니다. 진짜 순환은 여전히 발견되고, 거짓은 사라집니다

Excel은 룩업 범위가 수식을 담는 것을 왜 허용하는가

그 인자가 산술 피연산자가 소비되는 방식으로 소비되지 않기 때문입니다. 룩업 계열은 범위에서 캐시된 값을 스캔해 일치를 돌려주며, 범위가 먼저 끝까지 평가되기를 요구하지 않습니다. Excel은 자기 자신과 겹치는 룩업 범위를 그 셀들이 현재 담고 있는 무엇이든 읽는 것으로 다루는데, 이는 비반복 통합 문서에 적용하는 것과 같은 의미론입니다. 이 패스에서 재계산되지 않은 셀은 마지막 계산값을 내어 줍니다

전체 컬럼 참조는 이것을 특이한 사례가 아니라 흔한 사례로 만듭니다. 행이 추가되는 시트에서 "루룩 테이블 전체"를 쓰는 관용적 방법이 B:B이고, 그러면 열 B에 사는 어떤 수식이든 자기 룩업 범위 안에 들어갑니다. 재무 모델, 조정(reconciliation) 시트, 감사 통합 문서는 이것을 끊임없이 하며, 보통 아무도 범위가 겹친다는 것을 알아차리지 못합니다

셀 B7이 자기 전체 컬럼 룩업 범위 B:B 안에서 VLOOKUP(A1,B:B,1)을 담음, Excel이 불평 없이 캐시값으로 계산하는 자기 겹침 — 전체 컬럼 룩업 범위는 재무 모델과 감사 통합 문서에서 자기 겹침을 특이한 구석이 아니라 정상 사례로 만듭니다

의존성 그래프가 같은 수식으로 하는 일

HotXLS는 증분 재계산하며, 이는 진짜 의존성 그래프를 요구합니다. 셀의 노드, 참조의 간선, 평가를 위한 위상 순서, 순환을 분류하는 강연결 성분(strongly connected component) 패스입니다. 그 기계장치는 증분 재계산 문서에서 설명하며, 거짓 양성이 나타난 것이 정확히 그 때문입니다

셀 B7의 =VLOOKUP(A1,B:B,1)에서 의존성을 뽑아내면 두 번째 인자가 B7 자신을 담은 범위를 낳습니다. 그래프는 이제 자기 순환(self-loop)을 갖습니다. 그 노드의 진입 차수는 절대 0에 닿지 않으므로 위상 패스는 그것을 절대 일정에 올릴 수 없고, 성분 패스는 그것을 순환으로 분류합니다. 엔진은 주어진 그래프에 대해 올바르게 추론하고 있습니다. 그래프가 잘못된 모델입니다. 스프레드시트에는 간선 유형이 두 개인데 하나만 인코딩했기 때문입니다

B:B 룩업 범위가 그래프 노드 B7에 자기 순환을 주어 진입 차수가 절대 0에 닿지 않고 v2.361.98 이전 HotXLS가 거짓 순환 참조를 보고함 — 재계산 엔진은 주어진 그래프에 대해 올바르게 추론하는 중이었습니다. 그래프가 스프레드시트에 대한 잘못된 모델이었습니다

두 간선 클래스, 하나의 그래프

변경은 해석된 참조 레코드인 TXLSDepRange.LookupScan에 플래그를 더하며, 의존성 추출기가 여섯 함수 중 하나의 lookup-array 인자를 훑을 때 설정합니다. 하류에서 그 참조에서 온 간선은 평범한 간선과 떨어져 저장됩니다. 그래프 노드는 자기 평범한 종속자와 선행자 목록 옆에 ScanDependents와 ScanPrecedents 목록을 유지합니다

분리가 의미론을 올바르게 만듭니다. 스캔 간선은 더러움 전파가 순회하므로 B:B 어디든 편집하면 여전히 B7이 더럽다고 표시되고 B7은 재계산합니다. 스캔 간선은 진입 차수에 절대 세어지지 않고 성분 작성기에 절대 들어가지 않으므로, 위상 교착을 만들 수도 순환으로 분류될 수도 없습니다. 라이브러리의 두 그래프 구현, 즉 클래식 통합 문서별 그래프와 성분 분석을 실어 가는 교차 통합 문서 워크스페이스 그래프는 함께 바뀌었습니다. 둘이 어긋나면 혼자 열었을 때와 워크스페이스의 일부로 열었을 때 재계산이 달라지는 통합 문서가 만들어집니다

TXLSDepRange.LookupScan의 스캔 간선은 ScanPrecedents와 ScanDependents로 더러움 전파를 이끌지만 진입 차수나 순환에는 절대 세어지지 않음 — B:B 안의 편집은 여전히 수식을 더럽다고 표시하지만, 스캔 간선은 위상 패스를 교착시키거나 순환을 만들어 낼 수 없습니다

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 'ACC-4471';
    Sheet.Cells[1, 2].Value := 1200.00;
    // 룩업 범위가 열 B를 커버하고 이 수식이 그 안에 삽니다
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // v2.361.98 전에는 이 시트에서 이 분기에 닿을 수 없었습니다
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

순서에서 스캔 간선을 빼면서 포기하는 것

정확히 하나이며, 숨기기보다 담백하게 밝힐 가치가 있습니다. 스캔 간선은 위상 순서에 참여하지 않으므로, 룩업 수식은 같은 패스에서 자기 룩업 범위의 일부 셀이 아직 재계산되기 전에 평가될 수 있고, 그러면 이전값을 읽습니다. 결과는 다음 재계산에서 수렴합니다

받아들일 수 있는 것은 Excel이 하는 것이 바로 그것이기 때문입니다. 반복 계산이 켜지지 않은 통합 문서에서 현재 패스에서 아직 재계산되지 않은 값에 대한 Excel 자신의 답은 마지막 계산값이므로, 이 동작을 재현하는 엔진은 근사가 아니라 기준 구현과 일치합니다. 자기 참조 모델 위에서 진짜 수렴한 답이 필요하다면 그 메커니즘은 명시적 반복 한도를 둔 반복 계산이며, 반복 계산 문서에서 다루고, 스캔 겹침이 아니라 진짜 순환에 적용됩니다

수정 안에 숨은 회귀 위험

LookupScan을 TXLSDepRange에 더하면서 룩업과는 무관하고 Pascal과는 전부 관련된 위험이 들어왔습니다. TXLSDepRange는 관리되지 않는 레코드이므로 그 타입의 지역 변수는 0으로 초기화되지 않습니다. 따라서 데이터 테이블 의존성 블록과 여러 테스트 헬퍼를 포함해 코드베이스에서 하나를 손으로 만드는 모든 곳을 새 필드를 명시적으로 설정하도록 업데이트해야 했습니다. 한 곳을 빠뜨리면 스택에 우연히 앉아 있던 바이트가 그 참조를 스캔 간선으로 다룰지를 정하며, 무관한 코드 변경과 함께 나타났다 사라지는 재계산 버그가 됩니다

// 관리되지 않는 레코드의 새 Boolean 필드는 손으로 만드는
// 모든 지점을 잠재 버그로 만듭니다. 안전한 관용구 둘:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // 전부 0으로 채운 뒤 값을 넣습니다
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // 또는 새 필드를 포함해 모든 필드를 모든 지점에서 설정합니다
  R.LookupScan := False;
end;

여기서 얻은 일반 규칙은 이렇습니다. 몇 군데를 넘어 스택에서 생성되는 레코드에 필드를 더하는 것은 겉보기보다 고위험 변경이며, 컴파일러는 그 지점들을 찾도록 도와주지 않습니다. 레코드가 핫 경로에서 닿을 수 있다면 모든 호출 지점이 업데이트되기를 믿는 것보다 완전히 초기화하는 헬퍼를 선호하십시오

진짜 순환과 스캔 겹침을 구분하기

이 변경은 순환 탐지를 약화시키는 것이 전혀 없습니다. B7의 =B7+1은 여전히 순환이고, 자기 자신을 닫는 세 수식의 사슬도 여전히 순환이며, 둘 다 재계산 결과를 통해 보고되면서 순환 구성원은 이전 캐시값을 유지하고 순환 밖의 모든 것은 최신으로 남습니다. 바뀐 것은 오직 lookup-array 인자가 더 이상 Excel이 보지 못하는 순환을 만들어 내지 않는다는 것뿐입니다

통합 문서를 감사하며 엔진이 실제로 어떤 참조를 어떤 순서로 해석했는지 알고 싶다면 평가 트레이서가 그 도구입니다. 수식 평가 트레이서 문서가 출력 읽는 법을 다룹니다. HotXLS는 Excel 설치 없이 XLS, XLSX, ODS, CSV를 읽고 쓰는 네이티브 Delphi 및 C++Builder 스프레드시트 컴포넌트이며, 재계산 엔진은 모든 포맷에서 같습니다. 현재 함수와 엔진 커버리지는 HotXLS Delphi spreadsheet component 제품 페이지에 정리되어 있습니다