HotXLS 와일드카드: COUNTIF, MATCH, DSUM, Find

HotXLS Delphi Component는 같은 패턴 문자열을 네 가지 방식으로 읽습니다. Excel 16이 그러하기 때문입니다. COUNTIF와 SUMIF에서는 텍스트 a~b가 조건에 *나 ?도 없다면 문자 그대로이고, MATCH와 XLOOKUP 와일드카드 모드에서는 틸데가 언제나 이스케이프이므로 a~b는 ab를 찾으며, DSUM과 다른 데이터베이스 함수에서는 평문이 "~로 시작"을 뜻하고, 셀 전체 Find는 마지막 *으로 되돌아가야 합니다. HotXLS는 이 측정된 규칙들을 v2.384.52, v2.384.60, v2.384.64부터 따릅니다

이 영역의 버그 리포트는 와일드카드를 언급하지 않습니다. 서버가 생성한 리포트가 Excel에서 재계산한 같은 파일보다 몇 행을 덜 세거나, 틸데가 든 부품 번호를 한 수식은 찾고 다음 수식은 무시한다는 보고일 뿐입니다. 원인은 패턴이 어디서나 한 가지를 뜻한다고 가정하는 매처입니다. Excel은 그렇게 동작하지 않으므로, 캐시된 결과가 Excel과 일치해야 하는 엔진도 그럴 수 없습니다. v2.384.52 전의 HotXLS는 모든 조건을 DOS식 파일 마스크에 흘려보냈고, 평범한 패턴은 맞췄지만 엣지 케이스는 조용히 틀렸습니다

하나의 패턴 문자열이 Excel에서 네 가지를 뜻하는 이유는?

하나의 패턴 문자열이 네 가지를 뜻하는 이유는 Excel이 네 기능에서 네 매칭 규칙을 물려받고 통일하지 않았기 때문입니다. 조건 함수(COUNTIF, SUMIF, AVERAGEIF, *IFS 계열)는 조건마다 와일드카드가 적용되는지 자체를 판단합니다. 조회 함수(match type 0의 MATCH, match_mode 2의 XLOOKUP)는 언제나 적용합니다. 데이터베이스 함수(DSUM, DCOUNTA 등)는 Advanced Filter를 따르며, 거기서 맨단어는 접두사입니다. Find 대화상자에는 자체적인 셀 전체와 부분 모드가 있습니다. 아래 표는 a~b, ab, AB, abc, abcb, a*b, axb를 담은 한 열에 대해 어떤 셀이 각 패턴에 맞는지, 모든 함수를 기본 대소문자 무시 모드로 나열합니다

패턴	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP mode 2	DSUM 조건	Find, 셀 전체, 와일드카드 켬
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	COUNTIF와 같음	모든 엔트리, abc 포함	COUNTIF와 같음
a~b	a~b만	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	a*b만	a*b만	a*b만	a*b만
=ab	ab, AB	해당 없음	ab, AB	해당 없음

a~b 행은 COUNTIF와 MATCH가 갈리는 행이고, 부품 번호와 손으로 입력한 코드에는 기대보다 틸데가 자주 들어 있습니다. a*b 행은 다른 함정을 보여 줍니다. abc는 DSUM에는 맞지만 COUNTIF에는 맞지 않은데, 데이터베이스 함수가 조용히 *을 덧붙이기 때문입니다. ab, a*b, =ab의 DSUM 엔트리는 Excel 16 실행에서 그대로 나온 것이고, a~b의 DSUM 엔트리는 같은 접두사 규칙에서 따라 나옵니다. 덧붙은 *가 조건을 와일드카드 패턴으로 바꾸고 그 안에서 ~b는 이스케이프된 b이기 때문입니다

COUNTIF는 언제 와일드카드 모드로 전환할까?

COUNTIF는 조건 텍스트가 *나 ?를 담고 있을 때, 이스케이프됐든 아니든, 그때만 와일드카드 모드로 전환합니다. 어느 쪽 문자도 없으면 Excel은 조건을 각 셀과 문자열 전체로 비교하며 대소문자를 무시하고, 틸데는 그저 틸데입니다. 그래서 COUNTIF(A1:A7,"a~b")는 문자 그대로 a~b를 담은 셀을 셉니다. 별 하나를 더하면 의미가 뒤집힙니다. "a~b*"에서 이제 틸데는 b를 이스케이프하고, 패턴은 "ab 뒤에 무엇이든"으로 읽히며, a~b 셀은 더 이상 세지 않습니다. HotXLS는 이 규칙을 v2.384.52부터 두 엔진 모두에 적용해 왔습니다. COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS와 데이터베이스 함수가 공유하는 lxCalc의 조건 매처 하나를 통해서요

와일드카드 모드 안에서의 이스케이프 규칙은 Excel의 다른 모든 곳과 같습니다. ~는 다음 문자가 무엇이든 문자 그대로로 만들므로 ~b는 b를, ~~는 틸데 하나를 뜻하고, 패턴 맨 끝의 틸데는 버려지므로 "a*~"는 "a*"처럼 동작합니다. 대괄호는 결코 특별하지 않습니다. "[x]" 조건은 세 문자 [x]를 담은 셀을 세고, "[a-z]"는 평범한 데이터에서는 아무것도 세지 않습니다. TXLSXWorkbook.Calculate는 수식 문자열을 활성 시트에 대해 평가해 Variant를 돌려주며, 자기 데이터로 이 규칙들을 확인하는 가장 빠른 방법입니다

uses
  System.Variants, lxHandleX;

const
  Names: array [1..7] of string = ('a~b', 'ab', 'AB', 'abc', 'abcb', 'a*b', 'axb');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;

  procedure Show(const Formula: string);
  begin
    Writeln(Formula, ' = ', VarToStr(Book.Calculate(Formula)));
  end;

begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    for i := 1 to High(Names) do
    begin
      Sheet.Cells[i, 1].Value := Names[i];
      Sheet.Cells[i, 2].Value := 1 shl (i - 1);  // 1, 2, 4 ... SUMIF 합계가 행을 지목하게
    end;
    Sheet.Cells[8, 1].Value := 5;                // 숫자 하나; A9는 빈 채로

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    *나 ? 없음: 평문, a~b 셀
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    와일드카드 모드: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    문자열 전체 와일드카드, abc 제외
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  abc(8)만 빼고 모든 행
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    문자 그대로의 a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    숫자 5와 빈 A9도 셈
    Show('=COUNTIF(A1:A9,"<>")');       // 8    빈 셀이 아닌 셀들
  finally
    Book.Free;
  end;
end.

"<>text"는 무엇을 셀까?

"<>text" 조건은 그 텍스트가 아닌 모든 셀을 세며, Excel 16에서 그에는 숫자, boolean, 오류 값, 빈 셀이 포함됩니다. 맨 "<>"은 완전히 다른 질문입니다. "빈 셀이 아님"을 뜻하므로 비어 있는 셀은 건너뛰지만 ="" 같은 수식이 돌려주는 빈 텍스트를 포함해 모든 값을 셉니다. 옛 HotXLS 코드는 텍스트 셀은 맞췄지만 숫자는 아니었습니다. Variant 부등식이 Delphi로 하여금 'ab'를 숫자로 변환하게 만들었고, 변환이 예외를 일으키면 핸들러가 "불일치"로 삼켜, 숫자 셀이 조용히 카운트에서 빠졌습니다. 빈 피연산자가 평범한 비교에서 무엇과 같은지를 포함한 이 이야기의 빈 셀 쪽은 HotXLS가 비교 연쇄, 빈 셀, SUMIF를 다루는 방법에서 다룹니다

a~b를 검색하면 MATCH는 왜 ab를 찾을까?

a~b를 검색하면 MATCH가 ab를 찾는 이유는 match type 0의 MATCH와 match_mode 2의 XLOOKUP이 언제나 와일드카드 모드라서, 패턴에 *나 ?가 없어도 틸데가 이스케이프이기 때문입니다. Excel 16은 a~b와 ab를 담은 두 셀 범위에서 그것을 확인해 줍니다. MATCH("a~b",D1:D2,0)는 2를 돌려주고, a~b만 담은 범위에서 같은 호출은 #N/A를 돌려줍니다. 문자 그대로의 텍스트 a~b를 찾으려면 "a~~b"라고 써야 합니다. 한편 같은 두 셀에 대한 COUNTIF(D1:D2,"a~b")는 다른 셀을 세서 1을 돌려줍니다. 같은 문자열, 같은 범위, 반대 셀

그래서 HotXLS는 두 결정을 하나의 "패턴 매칭" 진입점 뒤에 두지 않고 따로 유지합니다. 매처 자체는 공유됩니다. v2.384.52부터 MATCH, XLOOKUP, 조건 함수는 같은 백트래킹 매처를, 같은 이스케이프 처리와 같은 끝 틸데 규칙으로 돌립니다. 다른 것은 그 앞의 게이트입니다. 조건 경로는 먼저 "이 텍스트에 *나 ?가 있는가?"를 묻고, 조회 경로는 묻지 않습니다. 둘을 합치면 한 계열은 고쳐지고 다른 계열은 깨지며, 양방향 모두 두 엔진에서 Excel 16 값과 대조 검증됩니다. 와일드카드 조회에는 자기 전제조건도 있습니다. XLOOKUP은 이진 탐색 모드와 결합된 와일드카드 매칭을 거부하는데, 그 규칙은 XLOOKUP과 XMATCH 검색 모드의 HotXLS 가이드에 기술되어 있습니다

DSUM과 데이터베이스 함수는 평문 조건을 어떻게 읽을까?

DSUM과 다른 데이터베이스 함수는 선행 =, <, > 없는 텍스트 조건을 와일드카드가 여전히 활성인 채 "~로 시작"으로 읽습니다. 그것은 Advanced Filter 규칙이고, 의도적으로 COUNTIF와 다릅니다. abc, ab, xab, AB, a~b, a*b를 담은 Name 열에서 측정한 Excel 16 동작은 이렇습니다. 조건 ab는 abc, ab, AB에 맞고, =ab는 ab와 AB에만 맞으며, <>ab는 엔트리 전체 부등식이고, a*b와 a?도 접두사 패턴이며, >ab는 평범한 비교입니다. v2.384.64 전의 HotXLS는 ab를 정확히만 맞췄으므로, 그 테스트 데이터에 대한 DSUM은 Excel이 11을 돌려주는 곳에서 10을 돌려줬습니다

수정은 조건 파서를 우회해야 했습니다. 파서는 ab와 =ab를 같은 등식 조건으로 접어 버리니까요. 그래서 HotXLS는 파싱된 조건을 신뢰하기 전에 날 조건 텍스트를 검사합니다. 첫 문자가 =, <, >가 아닌 텍스트 조건에는 *을 덧붙여 와일드카드 매처로 보내고, 나머지는 엔트리 전체 비교를 유지합니다. 코드로 조건 범위를 만들 때의 실용적 참고 하나. XLSX 엔진에서는 문자열 '=ab'을 TXLSXCell.Value에 대입하면 텍스트로 저장되지만, 클래식 TXLSWorkbook 엔진은 =으로 시작하는 값을 어포스트로피를 붙이지 않으면 수식으로 컴파일합니다

const
  Names: array [1..7] of string = ('a~b', 'ab', 'AB', 'abc', 'abcb', 'a*b', 'axb');
  Criteria: array [0..4] of string = ('ab', '=ab', '<>ab', 'a*b', 'a~*');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Db');
    Sheet.Cells[1, 1].Value := 'Name';
    Sheet.Cells[1, 2].Value := 'Val';
    for i := 1 to High(Names) do
    begin
      Sheet.Cells[i + 1, 1].Value := Names[i];
      Sheet.Cells[i + 1, 2].Value := 1 shl (i - 1);
    end;
    Sheet.Cells[1, 4].Value := 'Name';            // D1의 조건 헤더
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // XLSX 엔진에서는 텍스트로 유지됨
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (접두사 일치)
    // =ab  -> 6    ab, AB (엔트리 전체)
    // <>ab -> 121  ab와 AB를 빼고 모두
    // a*b  -> 127  a*b*가 abc 포함 일곱 개 모두에 일치
    // a~*  -> 32   문자 그대로의 a*b만
  finally
    Book.Free;
  end;
end;

관련 차이 하나는 접두사 수정 뒤에도 남아 구형 빌드에서 문제가 됩니다. >ab 같은 텍스트 비교는 코드 포인트 순서를 썼는데 Excel은 문장 부호를 문자보다 앞에 두므로, "a~b">"ab"는 Excel에서 FALSE이고 HotXLS에서는 TRUE였습니다. v2.384.67부터 >와 < 조건은 평범한 텍스트 비교와 정렬과 함께 현재 사용자 로캘 아래의 Excel 워드 정렬 콜레이션을 쓰며, 둘은 다시 일치합니다

셀 전체 Find는 왜 abcb를 놓쳤을까?

셀 전체 Find가 abcb를 놓친 이유는 매처가 마지막 *으로 되돌아가는 대신 패턴이 다 쓰인 첫 지점에서 멈췄기 때문입니다. Replace 뒤의 부분 일치 매처는 패턴이 소진되는 대로 돌려줍니다. 셀 전체 Find는 그것을 재사용한 뒤 일치가 셀 전체를 덮으라고 요구했습니다. abcb에 대한 a*b는 ab 뒤에서 멈춰 4자 중 2자를 소비했고 거부됐습니다. v2.384.60부터 셀 전체 매처는 "패턴은 끝났는데 텍스트는 남았다"를 불일치 하나로 취급해 마지막 별표에서 재시도하는 별개 구현이므로, a*b는 abcb에, a?b*b는 axbyb에 맞습니다. "셀 내용 전체 일치"를 켠 Excel 16 Find가 그러하듯이요

같은 릴리스에서 틸데도 바뀌었습니다. Excel 16 Find는 셀 전체와 부분 모드 양쪽에서 ~를 뒤따르는 어떤 문자에 대한 이스케이프로 다룹니다. a~b는 ab를 찾고, a~~b는 a~b를 찾으며, 끝 틸데는 무시되어 q~는 q처럼 동작합니다. 더 오래된 HotXLS 매처는 ~*, ~?, ~~만 이스케이프로 인식했으므로, a~b는 텍스트 a~b를 찾았습니다. ~ 하나짜리 Find 패턴은 Excel 자체에서도 불안정합니다. 빈 패턴처럼 어떤 셀이든 맞추는데, HotXLS는 그것을 흉내 내지 않습니다

XLSX 엔진에서 검색은 TXLSXFindOptions 집합을 받는 TXLSXWorksheet.FindText입니다. lxfUseWildcards는 *, ?, ~를 켜고, lxfWholeCell은 셀 전체 일치를 요구하며, lxfMatchCase는 비교를 대소문자 구분으로 만듭니다. lxfUseWildcards가 없으면 별표를 포함한 모든 문자가 문자 그대로입니다. Find는 텍스트 값만 봅니다. 숫자 셀은 건너뛰고, 수식 셀도 lxfSearchFormulas가 설정되지 않으면 건너뛰는데, 설정되면 수식 텍스트가 검색됩니다. StartRow와 StartCol이 주는 앵커는 포함 범위이므로, Find All 루프는 히트마다 한 열 더 가서 시작합니다

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row, Col, NextRow, NextCol, Changed: Integer;
  Opts: TXLSXFindOptions;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Parts');
    Sheet.Cells[1, 1].Value := WideString('abc');
    Sheet.Cells[2, 1].Value := WideString('abcb');
    Sheet.Cells[3, 1].Value := WideString('a~b');
    Sheet.Cells[4, 1].Value := WideString('ab');

    Opts := [lxfUseWildcards, lxfWholeCell];
    if Sheet.FindText('a*b', Row, Col, Opts, 1, 1) then
      Writeln('a*b  whole cell -> row ', Row);   // 2: abc 거부, abcb에서 되돌아감
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b는 이스케이프된 b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~는 문자 그대로의 틸데 하나

    // 부분 일치, Find All: 앵커 셀이 포함되므로 히트마다 한 칸 전진
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // 1, 2, 3, 4행
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // 셀 전체 와일드카드 바꾸기는 문자 그대로의 a~b만 고침
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

부분 루프는 abc를 포함해 네 행을 모두 찾습니다. 부분 모드에서 a*b는 셀 안 어딘가에 나오기만 하면 되기 때문입니다. FindTextIn과 ReplaceTextIn은 같은 옵션에 FirstRow, FirstCol, LastRow, LastCol 창을 더 받는데, 선택 영역 안에서 검색하는 것의 프로그래밍 등가물입니다. 클래식 엔진은 세 boolean을 받는 오버로드, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell)과 짝 ReplaceText 오버로드로 같은 규칙을 노출하며, 행과 열 결과는 1 기반입니다:

var
  Classic: IXLSWorkbook;
  Sheet: TXLSWorksheet;
  Row, Col: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Sheet := Classic.Sheets.Add;
  Sheet.Range['A1', 'A1'].Value := 'abcb';
  // MatchCase = False, UseWildcards = True, WholeCell = True
  if Sheet.FindText('a*b', Row, Col, False, True, True) then
    Writeln('found at ', Row, ',', Col);           // 1,1
  if not Sheet.FindText('a*c', Row, Col, False, True, True) then
    Writeln('a*c does not cover abcb');
end;

옛 DOS 마스크 매처는 무엇을 틀렸을까?

옛 매처는 특수 문자를 틀렸습니다. DOS 파일 마스크는 Excel 와일드카드와는 다른 언어이기 때문입니다. v2.384.52 전에는 조건 함수와 데이터베이스 함수가 모든 패턴을 lxMasks 유닛의 파일 마스크 매처인 MatchesMask에 넘겼습니다. 흔한 사례에서는 문법이 Excel과 겹쳐 문제가 숨어 있었지만, 실제 데이터가 흥미로워지는 곳에서 갈라집니다:

• [x]가 문자 집합으로 읽혀 COUNTIF(A1:A10,"[x]")는 괄호 친 텍스트 대신 x를 담은 셀을 세고, "[a-z]"는 글자 하나짜리 셀이면 무엇이든 맞췄습니다
• 틸데 이스케이프가 없어 "a~*b"는 문자 그대로의 별표를 맞출 수 없었습니다
• 닫지 않은 괄호 같은 잘못된 마스크는 예외를 일으켰고 호출자가 "불일치"로 삼켜, 조건의 오타가 조용히 틀린 합계로 이어졌습니다
• 조회 쪽에서는 MATCH와 XLOOKUP이 ~*, ~?, ~~만 이스케이프로 다뤄, MATCH("a~b",…,0)는 ab 대신 문자 그대로의 a~b를 찾았습니다

통합 문서가 순수 영숫자 데이터에 *와 ?만 썼다면 결과는 이미 맞았고 바뀌지 않습니다. 괄호, 틸데, "<>text" 아래의 혼합 타입 열, 맨 단어로 쓴 DSUM 조건이 있다면 v2.384.64 이상으로 재계산하면 합계가 바뀔 수 있고, 새 합계가 Excel이 보여 주는 값입니다. Excel이 조건을 저장하는 방식과 비교하는 방식의 같은 구분은 저장된 필터에서도 나오는데, BIFF8 AutoFilter DOPER 조건의 HotXLS 글에서 다룹니다

빠른 참조: HotXLS의 Excel 와일드카드 규칙

• COUNTIF, SUMIF, AVERAGEIF, *IFS 계열은 조건에 *나 ?가 있을 때만 와일드카드를 씁니다. 그 외에는 문자열 전체를 대소문자 무시로 비교하며 ~는 문자 그대로입니다(v2.384.52부터)
• match type 0의 MATCH와 match_mode 2의 XLOOKUP은 언제나 와일드카드를 쓰므로, a~b는 ab를 찾고 문자 그대로는 a~~b가 필요합니다(v2.384.52부터)
• 와일드카드 모드에서 ~는 다음 문자 무엇이든 이스케이프하고 끝의 ~는 버려집니다. [과 ]는 평범한 문자입니다
• "<>text"는 숫자, boolean, 오류, 빈 셀을 셉니다. 맨 "<>"는 빈 셀이 아닌 셀을 세며 ="" 결과도 포함입니다
• DSUM과 다른 데이터베이스 함수는 평문을 "~로 시작"으로 다룹니다. =text와 <>text는 엔트리 전체를 비교합니다(v2.384.64부터)
• lxfUseWildcards와 lxfWholeCell을 쓴 셀 전체 Find는 백트래킹하므로 a*b가 abcb에 맞습니다. Find와 Replace는 ~를 어떤 문자에 대한 이스케이프로 다룹니다(v2.384.60부터)
• >와 < 조건의 텍스트 순서는 Excel의 워드 정렬 콜레이션을 따릅니다. 문장 부호가 문자보다 앞섭니다(v2.384.67부터)

수식 엔진에서의 Excel 호환성은 대개 이런 엣지 케이스입니다. 문서에서 추측하는 게 아니라 Excel에 대해 측정하는 것이죠. HotXLS는 COUNTIF, MATCH, XLOOKUP, DSUM과 함수 라이브러리의 나머지를 Delphi와 C++Builder에서 네이티브로 평가하며, 클래식 엔진과 XLSX 엔진 양쪽에서 Excel 설치 없이 동작합니다. 세부, 에디션, 평가판 다운로드는 HotXLS Delphi 스프레드시트 컴포넌트 페이지에 있습니다