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

A1:A7의 키 1, 3, 5, 5, 5, 7, 9에서 수식 XMATCH(5, A1:A7, 0, 2)는 3을 돌려줍니다. 마지막 인자만 -1로 바꾸면 같은 수식이 5를 돌려줍니다. 첫 버전을 누군가 다른 필드로 재정렬한 열에 겨냥하면, 시트에서 뻔히 보이는 5가 #N/A로 돌아올 수 있습니다. 세 답 모두 옳습니다. XMATCH의 마지막 인자, XLOOKUP으로 치면 여섯 번째 인자는 동시에 두 가지입니다. 어느 중복을 선호할지에 대한 지시와, 데이터가 어떻게 정렬되어 있는지에 대한 약속

여기까지 오게 하는 리포트는 검색 모드를 거의 언급하지 않습니다. 생성된 워크북이 몇몇 행에서 누군가의 기대와 어긋난다고 말하고, 그 행들은 무언가를 공유합니다. 반복되는 키, 이웃을 골라야 했던 근사 일치, 아니면 수식이 쓰인 뒤 순서가 바뀐 조회 열. 아래 절들은 각 수식이 내는 정확한 결과와 함께 규칙을 하나씩 짚어 갑니다

XLOOKUP은 어떤 match_mode와 search_mode 값을 받나요?

네이티브 Delphi·C++Builder 스프레드시트 컴포넌트인 HotXLS는 정확히 네 가지 일치 모드와 네 가지 검색 모드를 받는 하나의 공유 조회 루틴으로 XLOOKUP과 XMATCH를 평가합니다. match_mode는 0(정확), -1(정확 또는 다음으로 작은), 1(정확 또는 다음으로 큰), 2(와일드카드)일 수 있습니다. search_mode는 1(처음부터 끝까지 훑기), -1(끝에서 처음까지 훑기), 2(오름차순 데이터 위의 이진 탐색), -2(내림차순 데이터 위의 이진 탐색)일 수 있습니다. 그 외의 숫자는 #VALUE!를 돌려주고, 아무것도 가장 가까운 합법 모드로 접히지 않습니다. 인자를 빼면 일치 모드 0과 검색 모드 1이 선택되는데, 손으로 쓴 대부분의 수식이 그렇습니다

인자 개수가 먼저 검사됩니다. XLOOKUP은 3개에서 6개, XMATCH는 2개에서 4개의 인자를 받고, 그 범위 밖의 호출은 평가가 시작되기 전에 #VALUE!입니다. 모드 인자 자체는 평범한 워크시트 수식이므로, HotXLS는 각각을 숫자로 변환하고, NaN이나 무한대, 32비트 정수 범위 밖의 값에는 #VALUE!를 돌려준 뒤 0을 향해 버립니다. 1.5는 검색 모드 1이 되고, -1.5는 -1이, 2.9는 2가 됩니다. 버림은 모드가 산술로 계산된 셀에서 올 때 물어뜯습니다. 2로 표시되지만 1.9999999로 저장된 값은 선형 탐색을 고릅니다. 모드는 리터럴로 쓰거나 계산된 것은 ROUND로 감싸세요

다음 프로그램은 작은 요율 테이블과 그 규칙들을 행사하는 다섯 개의 조회를 씁니다. 주석은 위의 규칙들에서 유도된, Recalculate 뒤 각 수식 셀이 담는 값을 알려 줍니다

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

    // 기본값: match_mode 0, search_mode 1
    Sheet.Cells[1, 4].Formula := 'XLOOKUP(30,A1:A4,B1:B4)';               // 0.15
    // 정확 또는 다음으로 작은, 오름차순 키 위의 이진 탐색
    Sheet.Cells[2, 4].Formula := 'XLOOKUP(35,A1:A4,B1:B4,"none",-1,2)';   // 0.15
    // 1.5는 버려져 search_mode 1이 됩니다
    Sheet.Cells[3, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,1.5)';  // 0.10
    // 3은 검색 모드가 아닙니다
    Sheet.Cells[4, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,3)';    // #VALUE!
    // 와일드카드 일치는 이진 탐색에서 돌 수 없습니다
    Sheet.Cells[5, 4].Formula := 'XLOOKUP("4*",A1:A4,B1:B4,"none",2,2)';  // #VALUE!

    if Book.Recalculate = lxOk then
      Book.SaveAs('lookup-modes.xlsx');
  finally
    Book.Free;
  end;
end;

search_mode 2는 시트에 있는 키를 왜 놓칠까요?

검색 모드 2는 조회 벡터가 이미 오름차순 정렬되어 있다고 HotXLS에게 말하고, 엔진은 그것을 믿습니다. 그 주장을 검사하려면 열 전체를 한 번 훑어야 하는데, 이진 탐색이 존재하는 이유가 바로 그 비용을 피하는 것이므로, 하강은 매 탐침마다 구간을 절반으로 자르고 끝나는 곳에서 돌아옵니다. Microsoft는 XLOOKUP과 XMATCH에 같은 계약을 문서화합니다. 이진 모드는 정렬된 데이터를 요구하고 그 외에는 무효한 결과를 줍니다. SpreadsheetML 수식 절인 ISO/IEC 29500-1 §18.17은 자체 오름차순 요구와 함께 오래된 LOOKUP과 VLOOKUP을 묘사합니다. XLOOKUP과 XMATCH는 나중에 왔고 future-function 접두사 아래 _xlfn.XLOOKUP과 _xlfn.XMATCH로 파일에 저장됩니다

그림은 키 40, 10, 30 위의 XLOOKUP(40, A1:A3, B1:B3, "missing", 0, 2)를 추적합니다. 첫 탐침은 2행에 떨어져 10을 읽고, 10은 40 아래이므로 40을 담은 행을 포함해 1행과 2행을 버립니다. 3행은 30을 읽는데 이것도 40 아래이고, 구간은 비어 있습니다. 수식은 "missing"을 돌려줍니다. 검색 모드 1에서 같은 호출은 1행에서 40을 찾습니다

"오름차순"은 화면에 보이는 것보다 더 구체적인 뜻도 있습니다. 이진 경로는 비교 전에 값을 종류별로 서열화합니다. 숫자 먼저, 그다음 텍스트, 불리언, 오류 값, 그리고 공백. 세 셀이 부품 코드를 텍스트로 담고 있는 숫자 부품 코드 열은 아무리 단정해 보여도 그 규칙 아래에서는 오름차순이 아닙니다. 텍스트는 대소문자 무관하게 비교되고, 윈도우에서는 순서가 CompareStringW를 통해 사용자 로캘에서 오는데, 이것은 하이픈과 악센트 문자를 바이트 순 정렬과 다르게 다룹니다. 이진 콜레이션으로 정렬된 데이터베이스의 키 열은 문장부호를 담은 코드들에서 정확히 순서 약속을 어길 수 있습니다

공백이 마지막 서열이라는 것은 기울어진 결과를 낳습니다. 아래로 빈 행이 있는 오름차순 키 열은 여전히 오름차순이므로, A2:A5001 같은 과대한 범위에 검색 모드 2를 겨냥하는 것은 무해합니다. 빈 행이 뒤따르는 내림차순 열은 더 이상 내림차순이 아니고, 검색 모드 -2는 공백으로 곧장 걸어 들어갑니다

var
  R: TXLSFormulaEvaluationResult;
begin
  // B1:B3은 9, 7, 5를 담고 B4:B10은 비어 있습니다
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-2)');
  // R.Status = xlfeOk이고 VarIsError(R.Value): 결과는 #N/A입니다.
  // 첫 탐침이 빈 B5를 읽고, 공백은 모든 숫자 위의 서열이라
  // 하강은 오른쪽으로 계속 움직이다 돌아오지 않습니다

  // 범위를 채워진 행으로 줄이면 같은 호출이 동작합니다
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B3,0,-2)');     // 3

  // 선형 탐색은 공백을 건너뛰고 키를 어디에든 찾습니다
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-1)');    // 3
end;

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

XMATCH와 XLOOKUP은 중복 런의 고정된 끝에 내려앉고, 검색 모드가 그 끝을 고릅니다. 검색 모드 2에서 이진 하강은 만나는 각 같은 키를 기록하며 왼쪽으로 좁혀 가므로 결과는 런의 가장 낮은 인덱스입니다. 검색 모드 -2에서는 내림차순 데이터 위에서 위치를 기록하며 오른쪽으로 좁혀 가장 높은 인덱스를 줍니다. 선형 모드는 훑기 방향의 첫 적중을 돌려줍니다. 검색 모드 1은 가장 낮은 인덱스, 검색 모드 -1은 가장 높은 인덱스입니다

불일치 리포트가 특정 행에 몰리는 이유가 이것입니다. 고유 키의 샘플 파일은 네 검색 모드 모두에서 같은 답을 주므로 그것으로 만든 모든 테스트가 통과합니다. 실전 데이터가 고객 코드를 반복하는 순간 모드들은 정확히 반복된 행에서 서로 동의하지 않기 시작하고, 수식을 쓴 코드는 전혀 바뀌지 않았습니다

// A1:A7은 1, 3, 5, 5, 5, 7, 9를 담습니다(오름차순, 셋짜리 런)
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)';   // 3, 앞 방향 첫 적중
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)';  // 5, 뒤 방향 첫 적중
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)';   // 3, 런의 최저 인덱스

// B1:B7은 9, 7, 5, 5, 5, 3, 1을 담습니다(내림차순)
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)';  // 5, 런의 최고 인덱스

근사 일치는 차순위 값을 어떻게 고르나

어떤 셀도 조회 값과 같지 않을 때, 일치 모드 -1은 대상보다 크지 않은 가장 큰 값을 돌려주고 일치 모드 1은 대상보다 작지 않은 가장 작은 값을 돌려줍니다. 선형 모드에서 HotXLS는 벡터 전체를 훑으며 최적 후보를 유지하므로, 근사 일치는 정렬되지 않은 데이터에서도 동작합니다. 셀은 엄격히 더 가까울 때만 현직 후보를 대체하고, 단지 동점일 때는 대체하지 않습니다. 같은 차순위 값을 담은 여러 셀 중 유지되는 것은 훑기 순서에서 첫으로 만난 것입니다

A1:A6의 키가 10, 30, 20, 30, 50, 40이라 합시다. XMATCH(35, A1:A6, -1, 1)은 2를 돌려줍니다. 첫 30이 후보가 되고 두 번째 30은 동점만 시킵니다. XMATCH(35, A1:A6, -1, -1)로 훑기를 뒤집으면 결과는 4입니다. 뒤로 훑으면 다른 30을 먼저 만나기 때문입니다. XMATCH(35, A1:A6, 1, 1)은 6을 돌려줍니다. 35 위의 가장 작은 값으로는 40이 50을 이기기 때문입니다. 정렬된 데이터에서 이진 모드는 몇 번의 탐침으로 같은 이웃에 닿습니다. 10, 20, 30, 40, 50 위에서 XMATCH(35, A1:A5, -1, 2)는 3을, XMATCH(35, A1:A5, 1, 2)는 4를 돌려줍니다

정확한 적중도 받아들일 만한 이웃도 나오지 않으면, XLOOKUP은 공급됐다면 if_not_found 인자를, 공급되지 않았다면 #N/A를 돌려줍니다. XMATCH에는 폴백 인자가 없어 언제나 #N/A를 돌려줍니다

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

와일드카드 패턴은 정렬 순서 안에 자리가 없습니다. 일치 모드 2는 셀이 마스크에 맞는지를 묻는데, 예/아니오 질문입니다. 반면 이진 하강은 어느 절반을 유지할지 말해 주는 삼답이 필요합니다. ACME-*가 주어진 셀의 왼쪽인지 오른쪽인지 말할 방어 가능한 방법은 없으므로, HotXLS는 일치 모드 2와 검색 모드 2나 -2의 조합에 #VALUE!를 돌려주고 추측하지 않습니다. 와일드카드에는 검색 모드 1이나 -1을 쓰고 선형 비용을 받아들이세요

일치 모드 2는 언제나 와일드카드 규칙을 적용합니다. *는 임의의 문자 런, ?는 한 문자, ~는 다음 문자를 문자 그대로 만듭니다. 따라서 XLOOKUP("PN~*", ...)은 텍스트 PN* 자체를 찾는데, 제품 코드가 진짜로 별표를 담을 때 중요합니다. 일치 모드 0에서 선형 탐색은 텍스트를 대소문자 무관으로 비교하고, 이진 경로는 순서 비교가 동점을 보고할 때 두 값을 같은 것으로 다룹니다. 그 비교 역시 대소문자 무관이지만, 위에서 묘사한 같은 로캘 인식 순서이며, 이진 탐색이 정당히 쓸 수 있는 유일한 관계입니다. 조회가 많은 시트는 편집 뒤 증분 재계산 뒤에 있는 의존성 추적이 다시 평가해야 할 것의 모양도 결정합니다

형태 오류와 스필된 반환 슬라이스

두 함수 모두 1차원 조회 범위가 필요합니다. 동시에 여러 행과 여러 열에 걸치는 범위는 #VALUE!를 돌려줍니다. HotXLS는 여러분을 대신해 축을 고르지 않습니다. XLOOKUP은 두 번째 규칙을 더합니다. 반환 범위는 일치 축을 따라 조회 범위와 같은 길이여야 합니다. 500행 세로 조회와 짝지은 499행 반환 범위는 오류이고, 마지막 행이 조용히 잘리는 일은 없습니다. 조회 범위가 단일 셀이면 반환 범위의 모양이 조회를 세로로 셀지 가로로 셀지 결정합니다

세로 조회에서 한 열보다 넓은 반환 범위는 XLOOKUP이 짝지은 행 전체를 배열로 돌려주게 만듭니다. 스필 범위와 동적 배열 글이 다루는 다른 동적 배열 함수들과 같은 규칙 아래 이웃 셀로 스필됩니다. 스필된 슬라이스 안의 빈 셀은 0으로 돌아옵니다. 하나의 수식으로 테이블에서 완전한 레코드를 뽑아 내는 것은 편리하고, 수식의 오른쪽에 우연히 앉아 있던 열을 덮어쓰는 가장 빠른 길이기도 합니다

아무도 지켜보지 않는 워크북을 위한 모드 선택

서버 측 생성은 인터랙티브 용도보다 엄격한 정책을 받을 자격이 있습니다. 출하 전에 결과를 보는 사람이 없기 때문입니다. 거기서는 일치 모드 0과 검색 모드 1을 그대로 두세요. 일치는 정확하고, 탐색은 행 순서를 무시하며, 시트를 재정렬해도 답이 바뀌지 않습니다. 검색 모드 2는 같은 코드가 같은 실행에서 같은 열 위에 순서를 만들었을 때만 어울립니다. 다른 키로 정렬된 열 위의 이진 탐색은 자신 만만한 잘못된 숫자를 계산하는 가장 싼 길입니다

조회를 이진 모드로 옮길 때는 수식을 내보내는 지점에서 순서를 직접 검사하세요. 아래 함수는 HotXLS 일부가 아닙니다. 여러분 코드에 추가하는 가드입니다. 하나의 테이블에 대한 수많은 정확 조회에는 워크북 속성 RecalcLookupCache가 Recalculate가 범위의 첫 훓기를 나중 조회에 재사용하게 합니다. 캐시는 패스에 OFFSET, INDIRECT, 순환 참조, What-If 데이터 테이블이 있으면 스스로 꺼지고, 결과는 어느 쪽이든 같습니다

// HotXLS API가 아니라 여러분의 가드: search_mode 2 수식을 내보내기 전에
// 숫자 키 열이 오름차순인지 확인합니다
function KeysAscending(Sheet: TXLSXWorksheet; Col, FirstRow,
  LastRow: Integer): Boolean;
var
  Row: Integer;
  Prev, Cur: Variant;
begin
  Result := False;
  Prev := Sheet.Cells[FirstRow, Col].Value;
  if not VarIsNumeric(Prev) then Exit;
  for Row := FirstRow + 1 to LastRow do
  begin
    Cur := Sheet.Cells[Row, Col].Value;
    if (not VarIsNumeric(Cur)) or (Cur < Prev) then Exit;  // 텍스트, 공백 또는 순서 이탈
    Prev := Cur;
  end;
  Result := True;
end;

// Rates 시트: 키는 A2:A5001, 요율은 B2:B5001
UseBinary := KeysAscending(Rates, 1, 2, 5001);
Book.RecalcLookupCache := True;
for Row := 2 to OrderCount + 1 do
  if UseBinary then
    Orders.Cells[Row, 5].Formula := Format(
      'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,0,-1,2)', [Row])
  else
    Orders.Cells[Row, 5].Formula := Format(
      'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,"missing")', [Row]);

가드는 의도적으로 보수적입니다. 공백이나 텍스트 셀 하나가 열 전체를 선형 경로로 되돌립니다. 정렬된 뜨거운 열에서 이진 모드의 보수는 실재합니다. 조회마다 최대 n이 아니라 약 log₂ n 회의 읽기이고, 5,000개 키에서는 최대 13회 탐침을 뜻합니다

조회가 실은 변장한 비즈니스 규칙일 때, 이를 테면 예외가 있는 구간 요율일 때는, 내장 함수의 어떤 배열보다 여러분 자신의 Pascal 코드로의 콜백이 보통 더 명확합니다. 사용자 정의 워크시트 함수 글이 등록 방법을 보여 줍니다. 여기서 묘사한 XLOOKUP, XMATCH와 수식 엔진의 나머지는 HotXLS Delphi 스프레드시트 컴포넌트의 일부이고, 제품 페이지가 Delphi와 C++Builder용 지원 워크시트 함수를 나열합니다