Delphi에서 XLOOKUP과 XMATCH 이진 검색 모드

Delphi와 C++Builder용 네이티브 스프레드시트 컴포넌트인 HotXLS는 하나의 공유 조회 코어를 통해 XLOOKUP과 XMATCH를 평가합니다. 이 코어는 네 가지 일치 모드(-1, 0, 1, 2)와 네 가지 검색 모드(-2, -1, 1, 2)를 받아들이고, 절댓값 검색 모드가 2일 때는 로그 시간 이진 하강을 실행하며, 그 밖의 모든 조합은 수식 오류로 거부합니다

여러분을 이 글로 이끄는 버그 리포트는 "검색 모드"라고 말하는 법이 없습니다. 서버가 생성한 워크북이 같은 파일을 Excel에서 연 것과 다른 숫자를 보여준다고 말할 뿐이며, 9천 행 중 겨우 네 행 정도에서 그렇습니다. 그 네 행은 항상 공통점이 있습니다. 중복된 조회 키이거나, 이웃을 골라야 했던 근사 일치이거나, 지난주 누군가 다른 열 기준으로 정렬해버린 조회 열입니다. 조회 함수는 수식 엔진이 산술을 멈추고 계약이 되는 지점이며, 그 계약에는 대부분의 호출자가 읽어보지 않는 조항들이 있습니다

XLOOKUP이 실제로 받아들이는 모드 번호는 무엇인가

정확히 각각 네 개뿐이고, 그 밖에는 없습니다. HotXLS는 셀을 하나라도 건드리기 전에 match_mode를 -1, 0, 1, 2에 대해, search_mode를 -2, -1, 1, 2에 대해 검증하며, 그 밖의 값은 가장 가까운 합법 모드로 클램프되는 대신 #VALUE!를 반환합니다. 네 가지 일치 모드는 0(정확히 일치), -1(정확히 일치하거나 바로 아래의 작은 값), 1(정확히 일치하거나 바로 위의 큰 값), 2(와일드카드)이고, 네 가지 검색 모드는 1(정방향 선형 스캔), -1(역방향 선형 스캔), 2(오름차순 데이터에 대한 이진 검색), -2(내림차순 데이터에 대한 이진 검색)입니다. 생략하면 일치 모드 0과 검색 모드 1이 선택되며, 이는 실제 수식 대부분이 사용하는 조합입니다. 인자 개수도 같은 방식으로 단속됩니다. XLOOKUP은 3개에서 6개의 인자를, XMATCH는 2개에서 4개의 인자를 받으며, 그 범위 밖은 평가가 시작되기 전에 #VALUE!가 됩니다

// Shared by XLOOKUP and XMATCH, before any cell is read
if ((RequestedMatchMode <> -1) and (RequestedMatchMode <> 0) and
    (RequestedMatchMode <> 1) and (RequestedMatchMode <> 2)) or
   ((RequestedSearchMode <> -2) and (RequestedSearchMode <> -1) and
    (RequestedSearchMode <> 1) and (RequestedSearchMode <> 2)) then
begin
  Result := lxErrorValue;          // #VALUE!
  Exit;
end;

if Abs(RequestedSearchMode) = 2 then
begin
  if RequestedMatchMode = 2 then   // wildcards cannot ride a binary descent
  begin
    Result := lxErrorValue;
    Exit;
  end;
  // ... O(log n) descent over the lookup vector
end;

한 단계 앞서 알아둘 만한 더 조용한 검사가 하나 있습니다. 모드 인자는 워크시트 표현식으로 도착하므로, HotXLS는 그것을 숫자로 강제 변환하고 NaN과 무한대를 거부한 다음, 그 숫자가 자신의 반올림된 값과 같을 것을 요구합니다. XLOOKUP(x, A:A, B:B, "none", 0, 1.5)는 위장한 검색 모드 2가 아니라 #VALUE!입니다. 이는 모드가 반올림이 많은 계산이 만들어낸 셀에서 올 때 중요하며, 직접 작성한 워크북보다 생성된 워크북에서 더 흔한 일입니다

search_mode 2는 왜 정렬되지 않은 데이터에서 잘못된 답을 주는가

정확히 여러분이 요청한 대로 하고 있기 때문입니다. 검색 모드 2는 조회 벡터가 이미 오름차순이라고 엔진에게 말하는 것이며, 이진 검색은 그 존재 이유를 무너뜨릴 O(n) 패스 없이는 그 주장을 확인할 수 없습니다. 그래서 HotXLS는 호출자를 신뢰하고, 구간을 절반씩 나누고, 하강이 도달한 곳을 반환합니다. 정렬되지 않은 입력에서는 결과가 오류가 아니라 조용히 틀리며, 이는 엔진의 결함이 아니라 계약 위반입니다

Microsoft는 XLOOKUP과 XMATCH에 대해 같은 비대칭성을 문서화합니다. 이진 모드는 정렬된 데이터를 요구하며 그렇지 않으면 잘못된 결과를 냅니다. SpreadsheetML 수식 문법을 정의하는 ISO 29500-1 18.17절은 자체적으로 오름차순 요구사항을 가진 더 오래된 LOOKUP과 VLOOKUP 설명을 담고 있으며, XLOOKUP과 XMATCH는 그 텍스트보다 한참 뒤에 나와서 미래 함수 규약 아래 _xlfn.XLOOKUP과 _xlfn.XMATCH로 파일 안에 실립니다. 세대는 다르지만 같은 거래입니다. 호출자가 순서 불변조건을 공급하고, 엔진이 로그를 공급합니다

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Rates');
    Sheet.Cells[1, 1].Value := 40;  Sheet.Cells[1, 2].Value := 0.10;
    Sheet.Cells[2, 1].Value := 10;  Sheet.Cells[2, 2].Value := 0.25;
    Sheet.Cells[3, 1].Value := 30;  Sheet.Cells[3, 2].Value := 0.15;

    // Forward linear scan: finds key 40 wherever it sits
    Sheet.Cells[5, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,1)';
    // Binary ascending: the promise was broken, the key is never visited
    Sheet.Cells[6, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,2)';

    Book.SaveAs('lookup-modes.xlsx');
  finally
    Book.Free;
  end;
end;

두 번째 수식을 추적해보면 실패는 완전히 기계적입니다. 하강은 가운데 셀을 살펴보고, 10을 읽고, 10이 40보다 작다고 판단해서 실제로 40을 담고 있던 행을 포함한 왼쪽 절반을 버리고, 30을 살펴보고, 다시 버리고, 구간이 소진됩니다. Excel도 똑같이 동작하는데, 그것이 요점입니다. 잘못된 답을 재현하는 것은 예의가 아니라 호환성 요구사항입니다. 순서 전제는 "숫자가 오름차순"보다도 더 엄격한데, 비교자가 값을 먼저 종류별로 순위 매기기 때문입니다. 숫자, 텍스트, 부울, 오류 값, 빈 값 순서이며, 종류 안에서만 그 뒤에 비교합니다. 텍스트가 세 개 섞인 숫자 부품 코드 열은 화면에서 어떻게 보이든 그 비교자 아래에서는 오름차순이 아니며, 이진 모드는 이를 얼마든지 잘못 읽어냅니다

중복된 키는 어디에 떨어지는가

결정적인 한쪽 끝에 떨어지며, 어느 쪽 끝인지는 운이 아니라 검색 모드에 달려 있습니다. 이진 하강이 검색 모드 2 아래에서 같은 키를 만나면 위치를 기록한 뒤 계속 왼쪽으로 좁혀가므로, 결과는 해당 구간의 가장 낮은 인덱스입니다. 검색 모드 -2 아래, 내림차순 데이터에서는 위치를 기록한 뒤 오른쪽으로 좁혀가므로, 결과는 가장 높은 인덱스입니다. 선형 모드는 더 단순합니다. 검색 모드 1은 정방향으로 첫 번째 일치를, 검색 모드 -1은 역방향으로 첫 번째 일치를 반환합니다. 이것이 서두에서 말한 네 행 불일치를 만드는 세부 사항입니다. 키가 유일한 워크북은 네 검색 모드 모두에서 동일한 답을 내며, 여러분이 깨끗한 샘플 파일로 작성한 모든 테스트에서 그 차이를 숨깁니다. 프로덕션 데이터에 중복된 고객 코드 하나를 더하면, 모드들은 정확히 그 중복된 행에서 서로 어긋나기 시작합니다. 엔진에서 바뀐 것은 없습니다. 그저 입력이 집합이기를 멈추고 다중집합이 되었을 뿐입니다

// A1:A7 holds 1, 3, 5, 5, 5, 7, 9 - ascending, with a run of three
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)';   // 3, first forward hit
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)';  // 5, first reverse hit
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)';   // 3, lowest index of the run

// B1:B7 holds 9, 7, 5, 5, 5, 3, 1 - descending
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)';  // 5, highest index of the run

근사 일치는 차선 후보를 어떻게 고르는가

정확한 일치 검색과 나란히 최선 후보를 유지하다가 정확한 일치가 나타나지 않을 때만 그것을 반환하는 방식입니다. HotXLS는 match_mode -1을 "목표보다 크지 않은 값 중 가장 큰 것"으로, match_mode 1을 "목표보다 작지 않은 값 중 가장 작은 것"으로 취급하며, 둘 다 첫 번째로 적합한 이웃에서 멈추는 대신 스캔된 전체 영역에 대해 해석됩니다. 이진 경로에서는 같은 발상이 하강 과정에서 공짜로 나옵니다. 초과하거나 미달하는 모든 단계가 후보를 갱신하므로, 최종 후보는 키가 삽입되었을 위치 바로 옆의 경계 요소가 됩니다

// Linear path: refine the candidate only on a strict improvement
if (RequestedMatchMode = -1) or (RequestedMatchMode = 1) then
begin
  CompareResult := CompareDynamicValues(CurrentValue, RequestedValue);
  if ((RequestedMatchMode = -1) and (CompareResult <= 0) and
      ((CandidateIndex < 0) or
       (CompareDynamicValues(CurrentValue, CandidateValue) > 0))) or
     ((RequestedMatchMode = 1) and (CompareResult >= 0) and
      ((CandidateIndex < 0) or
       (CompareDynamicValues(CurrentValue, CandidateValue) < 0))) then
  begin
    CandidateIndex := ScanIndex;
    CandidateValue := CurrentValue;
  end;
end;

안쪽 조건을 자세히 읽어보십시오. 동점 판단이 거기 있습니다. 새 셀은 기존 후보보다 엄격히 더 나을 때만 교체하며, 단순히 같을 때는 교체하지 않습니다. 그래서 같은 차선 값을 가진 여러 셀 중에서 유지되는 것은 스캔 순서상 먼저 만난 것입니다. 정방향 스캔에서는 가장 낮은 인덱스, 역방향 스캔에서는 가장 높은 인덱스입니다. XLOOKUP과 XMATCH가 정확한 일치도 적합한 이웃도 찾지 못하면, XLOOKUP은 제공된 경우 if_not_found 인자로, 제공되지 않은 경우 #N/A로 대체되고, XMATCH는 항상 #N/A를 냅니다

와일드카드와 이진 검색이 공존할 수 없는 이유

와일드카드 패턴은 순서상의 위치가 아니기 때문입니다. 일치 모드 2는 셀이 마스크와 일치하는지를 묻고, 마스크 일치는 예 또는 아니오로 답합니다. 이진 하강은 어느 절반을 유지할지 알려주는 삼중 답이 필요합니다. ACME-*가 주어진 셀의 왼쪽에 있는지 오른쪽에 있는지 물을 방어 가능한 방법은 없으므로, HotXLS는 순서를 추측해서 그럴듯한 헛소리를 만드는 대신 match_mode 2와 search_mode 2 또는 -2의 조합을 즉시 #VALUE!로 거부합니다. 두 경로는 값을 비교하는 방식도 다르며, 이것이 이 분리를 강화합니다. 선형 스캔은 대소문자를 구분하지 않는 텍스트 비교로, 또는 와일드카드가 켜져 있으면 마스크 일치로 동등성을 판단합니다. 이진 하강은 순서 비교자에게 0을 요청해서 동등성을 판단합니다. 이는 우연한 계층화가 아니라 의도적인데, 이진 경로는 자신이 실제로 탐색하고 있는 관계만 사용할 수 있기 때문입니다. 와일드카드가 필요하다면 검색 모드 1이나 -1을 쓰고 선형 비용을 받아들이십시오. 이는 증분 재계산 뒤의 의존성 추적이 여러분의 임계 경로에서 배제하도록 설계된 것과 같은 트레이드오프입니다

모양 오류: 2차원 범위와 크기가 맞지 않는 반환 벡터

두 함수 모두 진정으로 1차원인 조회 범위를 요구합니다. 지정된 범위가 동시에 두 행 이상과 두 열 이상에 걸쳐 있으면, HotXLS는 대신 축을 골라주는 대신 #VALUE!를 반환하며, 단일 행이나 단일 열 범위는 긴 축을 따라 읽힙니다. XLOOKUP은 두 번째 모양 규칙을 추가합니다. 반환 범위는 일치하는 축을 따라 조회 범위와 정확히 같은 길이여야 하므로, 500행에 걸친 세로 조회와 499행짜리 반환 범위를 짝지으면 마지막 행에서 조용히 해소되는 한 자리 오차가 아니라 오류가 됩니다. 반환 범위가 세로 조회에서 한 열보다 넓거나 가로 조회에서 한 행보다 높으면, XLOOKUP은 일치한 슬라이스 전체를 배열로 돌려주고 스필 범위와 동적 배열 글에서 설명하는 다른 동적 배열 함수들과 같은 규칙 아래 이웃 셀로 스필됩니다. 이는 하나의 수식으로 표 전체 레코드를 뽑아내는 데 정말로 유용하며, 동시에 여러분이 보존하려던 열을 덮어쓰는 가장 빠른 방법이기도 합니다

화면을 아무도 지켜보지 않을 때 모드 고르기

서버 측 생성은 대화형 사용보다 더 엄격한 정책을 받을 자격이 있습니다. 합계가 이상해 보인다는 것을 알아챌 사람이 없기 때문입니다. 방어적인 기본값은 선형, 정확, 순서 무관, 시트를 재정렬해도 무효화될 수 없는 검색 모드 1과 일치 모드 0입니다. 검색 모드 2는 같은 코드 경로가 같은 실행에서, 같은 열에 대해, 그 순서까지 만들어낸 경우에만 사용하고, 그 의존성을 수식 옆에 적어두십시오. 다른 키로 정렬된 열에 대한 이진 검색은 확신에 찬 오답을 계산하는 가장 저렴한 방법이기 때문입니다. 조회가 정말로 자주 실행되고 데이터가 정말로 정렬되어 있을 때 그 대가는 실질적입니다. 하강은 n개가 아니라 대략 log n개의 셀을 읽으며, 그 각각의 읽기는 완전한 워크북 셀 해석을 거치므로, 절감 효과는 명령어 개수가 시사하는 것보다 큽니다

문제의 형태가 조회보다는 도메인 규칙에 더 가깝다면, 커스텀 워크시트 함수 글에서 다루는 여러분 자신의 Pascal 코드로의 콜백이 보통 내장 함수를 아무리 영리하게 조합하는 것보다 낫습니다. 여기서 논의한 XLOOKUP과 XMATCH 구현은 Delphi와 C++Builder용 전체 지원 함수 레퍼런스를 담은 제품 페이지를 가진 표준 HotXLS Delphi 스프레드시트 컴포넌트와 함께 제공됩니다