D1에 a~b, D2에 ab를 넣고 =COUNTIF(D1:D2,"a~b")를 돌리면 1이 나오고, 세어진 셀은 D1입니다. =MATCH("a~b",D1:D2,0)는 2를 반환하는데, 이는 D2를 가리킵니다. 같은 패턴 문자열, 같은 두 셀인데 Excel은 패턴을 읽는 함수에 따라 다른 셀을 고릅니다. 캐시한 결과를 Excel과 일치시키고 싶은 스프레드시트 엔진이라면 이 분열을 그대로 재현해야 하고, 여기에 두 규칙이 더 얹힙니다. DSUM 같은 데이터베이스 함수는 연산자 없는 맨 텍스트 조건을 "시작 문자열"로 취급하고, 셀 전체 Find는 마지막 *로 되돌아가 봐야 합니다
이 문제로 들어오는 장애 보고는 와일드카드를 거의 언급하지 않습니다. 서버에서 만든 통합문서가 데스크톱에서 재계산한 같은 파일보다 두 행 적게 센다든가, 물결표가 들어간 부품 번호를 어떤 수식은 찾고 다음 수식은 무시한다든가 하는 식입니다. 모든 패턴을 DOS 스타일 파일 마스크로 처리하면 흔한 케이스는 맞아떨어지기 때문에, 실제 제품 코드에 ~나 [가 들어오기 전까지는 이 표류가 드러나지 않습니다
하나의 패턴 문자열, 네 가지 Excel 규칙
Excel은 매칭 규칙을 통일하지 않았습니다. 기능마다 자기 규칙을 들고 왔습니다. 조건 함수들(COUNTIF, SUMIF, AVERAGEIF, *IFS 계열)은 조건마다 와일드카드를 애초에 적용할지 스스로 판단합니다. 조회 함수들(매치 타입 0의 MATCH, match_mode 2의 XLOOKUP)은 항상 적용합니다. 데이터베이스 함수들(DSUM, DCOUNTA 등)은 고급 필터 규칙을 물려받아 맨 단어를 접두사로 봅니다. Find 대화상자는 셀 전체 모드와 부분 모드를 따로 갖고 있습니다. HotXLS Delphi Component는 클래식 엔진과 XLSX 엔진 양쪽에서 각 계열을 자기 규칙으로 평가합니다. 표는 a~b, ab, AB, abc, abcb, a*b, axb를 담은 한 열에 대해 어떤 셀이 각 패턴에 맞는지 보여주며, 모든 함수는 기본값인 대소문자 무시 모드입니다
| 패턴 | COUNTIF / SUMIF | MATCH(…,0) / XLOOKUP 모드 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에서는 틀린데, 데이터베이스 함수가 조건 뒤에 몰래 *를 붙이기 때문입니다. a~b의 DSUM 결과도 그 붙은 별에서 비롯됩니다. 별이 있는 이상 조건은 와일드카드 패턴이고 ~b는 그저 이스케이프된 b일 뿐입니다
COUNTIF는 언제 와일드카드 모드로 전환할까?
COUNTIF는 조건 텍스트에 *나 ?가 이스케이프 여부와 관계없이 들어 있을 때만 와일드카드 모드로 전환합니다. 둘 다 없으면 조건은 각 셀과 대소문자 무시로 문자열 전체를 비교하고 물결표는 보통 문자라서, COUNTIF(A1:A7,"a~b")는 문자 그대로 a~b를 담은 셀을 셉니다. 별 하나가 의미를 뒤집습니다. "a~b*"에서는 이제 물결표가 b를 이스케이프하고 패턴은 "ab 뒤에 무엇이든"으로 읽히며, a~b 셀은 카운트에서 빠집니다. HotXLS는 이 관문을 COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS와 데이터베이스 함수들에 똑같이 적용합니다
와일드카드 모드 안에서의 이스케이프 규칙은 짧습니다. ~는 다음 문자가 무엇이든 문자 그대로 만드니 ~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에서 그 대상에는 숫자, 불리언, 오류 값, 빈 셀까지 포함됩니다. 맨 "<>"는 전혀 다른 것을 묻습니다. "빈 셀이 아닌 것"이죠. 빈 셀은 건너뛰되 ="" 같은 수식이 반환하는 빈 텍스트를 포함해 모든 값을 셉니다. Delphi의 함정은 Variant 비교에 있습니다. 'ab'를 숫자 셀과 비교하면 Delphi는 'ab'를 숫자로 바꾸려 하고, 변환은 예외를 던지며, 그 예외를 "매칭 없음"으로 삼켜버리는 핸들러는 숫자 셀을 카운트에서 떨어뜨립니다. HotXLS는 그 셀들도 세므로 예제의 마지막 두 줄은 7과 8을 반환합니다. 빈 셀이 보통 비교에서 빈 피연산자와 같은지를 포함한 이쪽 이야기는 HotXLS의 비교 체인, 빈 셀, SUMIF 처리 방식에 정리되어 있습니다
a~b를 검색하면 MATCH가 왜 ab를 찾아낼까?
매치 타입 0의 MATCH와 match_mode 2의 XLOOKUP은 항상 와일드카드 모드라서, 패턴에 *나 ?가 없어도 물결표는 이스케이프입니다. 첫머리의 두 셀 범위에서 MATCH("a~b",D1:D2,0)는 2를 반환합니다. a~b만 담은 범위에서 같은 호출은 #N/A입니다. 리터럴 텍스트 a~b를 찾으려면 "a~~b"가 필요합니다. 조회 키가 사용자 입력이나 외부 시스템에서 오고 물결표를 포함할 수 있다면 수식을 만들기 전에 모든 물결표를 두 배로 하세요. 나중에 사라진 행을 설명하는 것보다 StringReplace 한 줄이 쌉니다
HotXLS는 이 두 결정을 분리해 둡니다. MATCH, XLOOKUP, 조건 함수들은 하나의 백트래킹 매처를 공유하며 이스케이프 처리와 끝물결표 규칙도 같지만, 앞에 놓인 관문이 다릅니다. 조건 경로는 먼저 텍스트에 *나 ?가 있는지 묻고, 조회 경로는 묻지 않습니다. 양쪽이 쓰는 "패턴 매칭" 진입점 하나로 몰아가면 관문을 어느 쪽으로 두느냐에 따라 한 계열은 맞고 다른 계열은 틀립니다. 와일드카드 조회에는 조건이 하나 더 있습니다. match_mode 2를 이진 검색 모드와 조합하면 XLOOKUP이 #VALUE!를 반환하는데, 이는 HotXLS의 XLOOKUP과 XMATCH 검색 모드 가이드에서 다룹니다
DSUM 조건: 맨 텍스트는 "~으로 시작"
DSUM 등 데이터베이스 함수는 선행 =, <, >가 없는 텍스트 조건을 와일드카드가 살아 있는 상태의 접두사로 읽습니다. 고급 필터 규칙이며 COUNTIF와는 의도적으로 다릅니다. abc, ab, xab, AB를 담은 Name 열에서 조건 ab는 abc, ab, AB에 맞고, =ab는 ab와 AB에만 맞습니다. <>ab는 항목 전체 부등식이고, a*b와 a? 역시 접두사 패턴이며, >ab는 평범한 텍스트 비교입니다. xab는 ab에 절대 맞지 않습니다. 규칙은 "~으로 시작"이지 "~을 포함"이 아닙니다
구현이 곤란한 지점은 연산자를 떼어내는 조건 파서가 ab와 =ab를 같은 등식 비교로 보는 일입니다. 둘을 가르는 건 조건의 날 텍스트뿐입니다. 그래서 HotXLS는 파싱된 조건을 믿기 전에 그 날 텍스트를 먼저 봅니다. 첫 문자가 =, <, >가 아닌 텍스트 조건에는 *를 붙여 와일드카드 매처로 보내고, 나머지는 항목 전체 비교를 유지합니다
코드에서 조건 범위를 만들 때는 엔진별 함정이 하나 있습니다. XLSX 엔진에서 '=ab' 문자열을 TXLSXCell.Value에 대입하면 텍스트로 저장되는데, 조건 셀에는 정확히 그게 필요합니다. 클래식 TXLSWorkbook 엔진은 =로 시작하는 문자열 값을 아포스트로피를 앞에 붙이지 않으면 수식으로 컴파일합니다. 런타임에 조립하는 조건 범위라면 실수가 덜한 쪽은 XLSX 엔진입니다. 아래 루프는 다섯 조건을 D2에 넣고 같은 DSUM을 매번 재계산합니다
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 같은 순서 비교 조건은 또 다른 규칙을 따릅니다. 소스 코드 포인트 비교 그대로라면 "a~b">"ab"는 TRUE입니다. Unicode에서 ~는 모든 글자 위에 놓이기 때문입니다. Excel은 문장부호를 글자보다 앞에 놓고 FALSE를 반환합니다. Windows에서 HotXLS는 >와 < 텍스트 조건, 평범한 텍스트 비교, 정렬을 현재 사용자 로케일의 단어 정렬 콜레이션으로 평가해 세 가지가 서로, 그리고 Excel과 일치하도록 만듭니다
Delphi의 셀 전체 Find: 마지막 별로 되돌아가기
셀 전체 Find의 유혹적인 지름길은 Replace 뒤의 부분 매처를 재활용하고 매칭이 셀 전체를 덮도록 요구하는 것입니다. 평범한 데이터에서 실패합니다. 부분 매처는 패턴이 소진되는 대로 반환하므로 abcb에 대한 a*b는 ab에서 멈춰 4문자 중 2문자를 소비한 채 셀을 거절합니다. HotXLS는 셀 전체 Find를 별도의 매처로 통과시키는데, 이 매처는 "패턴은 끝났는데 텍스트는 남았다"를 한 번의 불일치로 치고 마지막 별에서 재시도합니다. 덕분에 a*b는 abcb에, a?b*b는 axbyb에 맞습니다. Excel Find가 "셀 내용 전체와 일치"에 체크했을 때 그렇게 하는 것과 같습니다
Excel Find는 셀 전체 모드와 부분 모드 모두에서 ~를 뒤따르는 모든 문자의 이스케이프로 취급합니다. a~b는 ab를 찾고, a~~b는 a~b를 찾으며, 끝의 물결표는 무시되어 q~는 q처럼 동작합니다. ~*, ~?, ~~만 아는 매처라면 a~b가 텍스트 a~b, 즉 반대편 셀을 찾게 됩니다. HotXLS는 수식에서와 마찬가지로 Find와 Replace에서도 물결표 뒤의 모든 문자를 이스케이프합니다
XLSX 엔진에서의 호출은 TXLSXFindOptions 집합을 받는 TXLSXWorksheet.FindText입니다. lxfUseWildcards는 *, ?, ~를 켜고, lxfWholeCell은 셀 전체 매칭을 요구하며, lxfMatchCase는 비교를 대소문자 구분으로 만듭니다. lxfUseWildcards가 없으면 별까지 포함해 모든 문자가 리터럴입니다. Find는 텍스트 값만 봅니다. 숫자 셀은 건너뛰고, 수식 셀은 lxfSearchFormulas가 켜 있지 않으면 건너뛰는데, 켜 있으면 수식 텍스트를 검색합니다. lxfBackward와 lxfByColumns는 스캔 순서를 바꿉니다. 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 창을 더 받는데, 선택 영역 안에서 찾기의 프로그래밍 버전입니다. 경계를 열어 두려면 아무 값이나 0을 넣으세요. 클래식 엔진은 불리언 세 개를 받는 오버로드, 즉 TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell)과 짝이 되는 ReplaceText 오버로드로 같은 매칭 규칙을 노출합니다. 행과 열 결과는 1 기반이고 XLSX 버전처럼 수식 셀과 비텍스트 값은 건너뜁니다
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 파일 마스크가 Excel 조건에는 맞지 않는 매처인 이유
MatchesMask 같은 파일 마스크 매처는 *와 ?에서 겹치는 부분이 있어도 Excel 와일드카드와는 다른 언어를 씁니다. 그 겹침이야말로 선택되는 이유이자, 데이터가 흥미로워질 때까지 실수가 숨어 있는 이유입니다:
[x]가 문자 집합으로 읽혀COUNTIF(A1:A10,"[x]")가 괄호 텍스트 대신x를 담은 셀을 세고,"[a-z]"는 글자 하나짜리 셀 무엇이든 맞습니다- 물결표 이스케이프가 없어
"a~*b"가 리터럴 별표를 맞출 수 없습니다 - 닫지 않은 괄호 같은 기형 마스크는 예외를 던지고, 호출자가 그걸 "매칭 없음"으로 삼켜버리면 조건의 오타가 틀린 합계로 변합니다
- 조회 쪽도
~*,~?,~~만 허용하는 이스케이프 집합은 부족합니다.MATCH("a~b",…,0)는 리터럴a~b가 아니라ab를 찾아야 합니다
통합문서가 평범한 영숫자 데이터에 *와 ?만 쓴다면 어떤 매처든 다 같은 답을 내고 이 모든 이야기는 무의미합니다. 대괄호와 물결표, "<>text" 아래의 혼합형 열, 맨 단어로 쓴 DSUM 조건이 바로 단순한 매처의 합계가 Excel에서 표류하는 지점이고, 위 코드가 돌려본 것도 그 케이스들입니다. 저장된 필터는 Excel이 조건을 어떻게 저장하는지와 어떻게 비교하는지에 대한 별개의 질문을 던지는데, 이는 BIFF8 AutoFilter DOPER 조건에 대한 HotXLS 글에서 논의합니다
HotXLS는 COUNTIF, MATCH, XLOOKUP, DSUM과 함수 라이브러리 나머지를 클래식 엔진과 XLSX 엔진에서 Delphi와 C++Builder로 네이티브 평가하며, 기계에 Excel 설치가 필요 없습니다. 에디션, 문서, 트라이얼 빌드는 HotXLS Delphi 스프레드시트 컴포넌트 페이지에 정리되어 있습니다