HotXLS 텍스트 비교: Delphi의 Excel 워드 정렬 순서

HotXLS Delphi Component는 v2.384.67부터 두 텍스트 값을 Excel 16처럼 비교합니다. Windows 사용자 로캘의 "워드 정렬" 순서, 즉 CompareStringW가 NORM_IGNORECASE 플래그와 함께 돌려주는 순서로, 대소문자를 무시합니다. 하이픈과 어포스트로피는 첫 패스에서 건너뛰고 동률만 가르므로 ="a-b">"ab"는 TRUE이고, 다른 문장 부호는 숫자와 문자보다 앞서므로 ="a~b"<"ab"도 TRUE입니다. 같은 순서가 이제 비교 연산자, > / < 조건, 범위 정렬, VLOOKUP을 구동합니다

"콜레이션 불일치"라는 제목의 버그 리포트는 아무도 쓰지 않습니다. 리포트는 이런 식입니다. 서버에서는 Excel보다 COUNTIF(A:A,">M")가 두 행을 더 세고, 리포팅 서비스가 정렬한 가격표는 X-100을 Excel이 두지 않을 곳에 두고, 열에 분명히 abc가 있는데 VLOOKUP("ABC",...)가 #N/A를 돌려준다는 식으로요. 셋 모두 같은 질문에서 나옵니다. 양쪽 피연산자가 텍스트일 때 어느 쪽이 더 작은가? Excel에는 정확한 답이 있고, 그것은 대부분의 Delphi 코드가 주는 답이 아니며, v2.384.67 전의 HotXLS는 어떤 코드 경로가 물었는지에 따라 세 가지 다른 답을 줬습니다

Excel은 두 텍스트 문자열을 비교할 때 어떤 규칙을 쓸까?

Excel은 사용자 로캘의 워드 정렬로 텍스트를 비교하며 대소문자를 무시합니다. 워드 정렬은 Windows NLS 비교 함수의 기본 콜레이션입니다. 문자는 코드 포인트가 아니라 언어적 순서로 비교되고, 악센트 문자는 밑 글자 곁에 놓이며, 두 문자는 특별 취급을 받습니다. 하이픈 -과 어포스트로피 '는 첫 패스에서 무시되므로 co-op과 coop은 바로 옆에 붙고, 문자열의 나머지가 동률일 때만 그 존재가 순서를 결정합니다. 그 밖의 모든 문장 부호는 의미가 있으며 숫자보다 앞서 정렬되고, 숫자는 문자보다 앞서 정렬됩니다

표는 그것이 실전에서 무엇을 뜻하는지, Delphi 개발자가 가장 손이 가는 두 비교를 나란히 놓고 보여 줍니다. Excel 열은 Excel 16이 IF(A<B,...)에 대해 돌려준 판정이며, HotXLS는 v2.384.67부터 그것을 재현합니다

A vs B	Excel 16 / HotXLS	CompareStr (ordinal)	CompareText
"a-b" vs "ab"	크다	작다	작다
"a'b" vs "ab"	크다	작다	작다
"a~b" vs "ab"	작다	크다	크다
"a_b" vs "ab"	작다	작다	크다
"ab" vs "AB"	같다	크다	같다
"é" vs "f"	작다	크다	크다
"Z" vs "f"	크다	작다	크다

놓치기 쉬운 결과가 둘 있습니다. 첫째, 하이픈의 동률 처리 역할 때문에 ="a-b"="ab"는 FALSE입니다. 문자열이 정렬에서는 바로 옆 이웃이지만 같지는 않습니다. 둘째, 같음은 대소문자를 완전히 무시하므로 ab, AB, Ab는 비교 관점에서 같은 키입니다. Excel의 Range.Sort로 20개 테스트 단어를 정렬하면 a b, a.b, a_b, a~b, a0, a1b, ab / AB / Ab, ab-, a'b, a-b, -ab, ab1, abc, b, e, é, f, Z 순이 됩니다. ab 그룹 안에서는 무시된 문자의 위치가 순서를 결정합니다

Excel의 텍스트 순서는 어떻게 확정했을까?

Excel의 텍스트 순서는 문서가 아니라 측정으로 확인했습니다. Excel 문서는 콜레이션 이름을 밝히지 않기 때문입니다. 테스트는 ASCII 문장 부호, 숫자, 양쪽 대소문자, 공백, é, ß, ä, 한자, 전각 형태, non-breaking space에서 길이 0부터 4까지의 무작위 문자열 쌍 4,000개를 만들었고, 절반은 서로 근접 미스가 되도록 구성했습니다. Excel 16은 모든 쌍에 대해 IF(A<B,-1,IF(A=B,0,1))을 평가했고, 판정을 다른 플래그 조합의 Windows 비교 API와 맞춰 보았습니다

• NORM_IGNORECASE 단독(기본 워드 정렬, 사용자 로캘): 진짜 불일치 없음. 유일한 7개 차이는 내용 전체가 '인 셀이었는데, Excel은 그것을 텍스트 접두 문자로 소비하므로 콜레이션 차이가 아니라 샘플링 인공물이었습니다
• NORM_IGNORECASE에 SORT_STRINGSORT를 더함: 41개 불일치. string sort는 하이픈과 어포스트로피를 평범한 기호로 다루는데, 그것이 바로 Excel에 없는 동작입니다
• NORM_IGNOREWIDTH 추가: 다른 방식으로 틀립니다. 같은 문자의 전각과 반각 형태를 같은 것으로 비교하게 만드는데, Excel은 둘을 구별합니다

두 번째로 직접 고른 검사는 까다로운 단어 20개에서 뽑은 190쌍 전부와 같은 열에 대한 Excel의 Range.Sort 결과를 비교했습니다. 둘 다 순수한 NORM_IGNORECASE 워드 정렬과 일치했고, 그 190 판정과 정렬 순서는 이제 HotXLS 회귀 스위트의 일부로, 클래식 TXLSWorkbook 엔진과 XLSX 네이티브 TXLSXWorkbook 엔진 양쪽으로 돌립니다

CompareText와 ordinal 비교는 왜 틀릴까?

CompareText와 ordinal 비교가 Excel의 순서를 틀리는 이유는 UTF-16 코드 유닛을 비교하기 때문이고, 코드 포인트 순서는 문장 부호를 문자를 기준으로 아무 데나 놓습니다. 하이픈은 U+002D, 어포스트로피는 U+0027로 둘 다 모든 문자보다 아래이므로, ordinal 비교는 하이픈을 동률 처리로 다루는 대신 "a-b"를 "ab"보다 작다고 판정합니다. 틸데 U+007E는 모든 문자 위에 있으므로 "a~b"가 더 크게 나오는데, Excel의 정반대입니다. Delphi RTL의 CompareText는 a..z만 대문자로 접은 뒤 코드 유닛을 비교하는데, 여기에 두 번째 왜곡이 더해집니다. 밑줄 U+005F는 대문자와 소문자 사이에 있으므로 대문자로 접으면 "a_b"가 "ab" 아래에서 위로 옮겨 갑니다. 어느 함수도 é가 e와 f 사이에 속한다는 것은 모릅니다

평범한 Delphi 도구들은 이 경계의 양쪽에 걸려 있습니다:

• CompareStr, 문자열 < 연산자, TComparer<string>.Default(CompareStr를 호출)는 ordinal이고 대소문자를 구분하므로, 비교자 없는 TArray.Sort<string>은 Z를 f 앞에 놓습니다
• CompareText와 SameText는 ASCII 전용 대소문자 접기 뒤의 ordinal입니다
• Windows의 Delphi RTL에서 AnsiCompareText와 WideCompareText는 CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)을 호출하는데, Excel과 일치하는 바로 그 호출입니다. 기본값(UseLocale True, CaseSensitive False)의 정렬 TStringList는 AnsiCompareText를 거치므로 Excel과 일치합니다
• POSIX 대상에서 Delphi RTL은 AnsiCompareText를 ICU 콜레이터로 우회시키는데, 이것은 문장 부호 규칙이 다른 별개 알고리즘입니다. Windows에서 Free Pascal의 AnsiCompareText는 ANSI 코드 페이지로 변환한 뒤 CompareStringA를 호출하며, 그 페이지가 표현하지 못하는 문자는 잃습니다

그러므로 로캘 인식 RTL 함수가 Windows에서 맞는 것은 계약이 아니라 구현 때문이고, Excel의 순서가 필요한 코드는 API 호출을 명시적으로 하는 편이 낫습니다. HotXLS도 내부적으로 같은 뒤죽박죽이었습니다. 비교 연산자는 두 문자열을 대문자로 만들어 코드 포인트를 비교했고, 조건 함수의 > / < 분기는 Delphi의 대소문자 구분 Variant 비교를 썼으며, VLOOKUP / HLOOKUP도 텍스트를 그 대소문자 구분 Variant 비교로 맞췄습니다. 그래서 VLOOKUP("ABC",A1:A20,1,FALSE)는 abc를 찾지 못했습니다. 범위 정렬만은 이미 WideCompareText를 썼습니다. 경로 셋, 순서 셋

HotXLS v2.384.67에서 바뀐 것은?

v2.384.67부터 HotXLS 계산과 정렬 경로의 텍스트 대 텍스트 비교는 하나의 함수, lxStandard.pas의 XlsCompareText를 통과합니다. 이 함수는 CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)를 호출하고 CSTR_EQUAL을 뺍니다. 호출자는 여섯 비교 연산자, 배열 수식의 요소별 비교, COUNTIF식 조건과 데이터베이스 함수의 >, <, >=, <= 분기, VLOOKUP과 HLOOKUP(정확히 일치와 근사), 동적 배열 함수와 XLOOKUP / XMATCH 뒤의 순서 헬퍼, 두 엔진의 범위 정렬입니다. 범위 정렬을 같은 함수로 라우팅하면 정렬 순서와 비교 순서가 다시 어긋날 수 없다는 보장이 생기는데, 이것이 중요한 이유는 텍스트에 대한 근사 VLOOKUP은 열이 조회가 비교하는 순서로 정렬돼 있을 때만 의미가 있기 때문입니다

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate는 활성 시트에 대해 평가함
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: 하이픈은 동률만 가름
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: 동률이 깨진 것이지 같지 않음
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: 문장 부호가 먼저
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: 대소문자 무시
  finally
    Book.Free;
  end;
end;

타입 간 비교는 별개 규칙이며 바뀌지 않았습니다. 모든 숫자는 모든 텍스트 값 아래이고 모든 텍스트 값은 모든 boolean 아래인데, 이는 비교 연쇄, 빈 피연산자, SUMIF에 관한 글에서 기술합니다. 워드 정렬은 양쪽 피연산자가 모두 텍스트일 때만 적용됩니다. 와일드카드 매칭도 별개입니다. "a*"나 "=ab" 같은 조건은 패턴 또는 같음 검사로, COUNTIF, MATCH, DSUM의 Excel 와일드카드 가이드에서 다루며, 여기서 논의하는 콜레이션은 순서 연산자만 결정합니다

다음 예제는 20개 테스트 단어를 열에 넣고 TXLSXWorksheet.SortRange로 정렬한 뒤, 조건 카운트와 조회 하나를 확인합니다. 카운트는 Excel 16이 같은 열에 대해 돌려준 값입니다

const
  Words: array [0..19] of string = ('ab', 'a-b', 'a~b', 'a_b', 'AB', 'a b',
    'ab1', 'ab-', '-ab', 'abc', 'a''b', 'Ab', 'b', 'a.b', 'a1b', 'a0',
    #$00E9, 'e', 'f', 'Z');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Words');
    for i := 0 to High(Words) do
      Sheet.Cells[i + 1, 1].Value := WideString(Words[i]);

    // 같은 열에 대한 Excel 16: 11, 11, 14
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">ab")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,"<a-b")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">=AB")')));

    // v2.384.67 전에는 #N/A: 조회가 대소문자를 구분해 비교했음
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // 키 열 하나, 오름차순: a b, a.b, a_b, a~b, a0, a1b, ab, AB, Ab, ...
    Sheet.SortRange(1, 1, 20, 1, [1], [False]);
    for i := 1 to 20 do
      Writeln(VarToStr(Sheet.Cells[i, 1].Value));
  finally
    Book.Free;
  end;
end;

TXLSXWorksheet.SortRange는 안정 병합 정렬을 쓰므로, 같다고 비교되는 ab, AB, Ab는 정렬 전의 상대적 순서를 유지합니다. 빈 셀은 Excel처럼 양쪽 방향 모두 끝으로 갑니다

내 Delphi 코드에서 Excel의 정렬 순서는 어떻게 맞추나?

자기 Delphi 코드에서 Excel의 텍스트 순서를 맞추려면 CompareStringW를 LOCALE_USER_DEFAULT와 NORM_IGNORECASE로 호출하고 SORT_STRINGSORT나 NORM_IGNOREWIDTH는 더하지 마세요. 반환값은 부호 있는 비교 결과가 아닙니다. API는 CSTR_LESS_THAN(1), CSTR_EQUAL(2), CSTR_GREATER_THAN(3)을 돌려주고 호출이 실패하면 0을 돌려줍니다. 익숙한 음수 / 0 / 양수 관례를 얻으려면 2를 빼고, 먼저 0을 검사하세요. 실패를 결과로 착각하면 -2, 조용한 "작다"가 되기 때문입니다

uses
  Winapi.Windows, System.SysUtils, System.Generics.Defaults,
  System.Generics.Collections;

// Excel의 텍스트 순서: 사용자 로캘 워드 정렬, 대소문자 무시
function ExcelCompareText(const A, B: string): Integer;
var
  R: Integer;
begin
  R := CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE,
    PWideChar(A), Length(A), PWideChar(B), Length(B));
  if R = 0 then
    RaiseLastOSError;          // 0은 실패지 비교 결과가 아님
  Result := R - CSTR_EQUAL;    // 1/2/3이 -1/0/1로 됨
end;

var
  Keys: TArray<string>;
begin
  Keys := ['abc', 'a-b', 'AB', 'a~b', '-ab', 'ab'];
  TArray.Sort<string>(Keys, TComparer<string>.Construct(
    function(const L, R: string): Integer
    begin
      Result := ExcelCompareText(L, R);
    end));
  // a~b, ab / AB (같음, 어느 순서든), a-b, -ab, abc
end;

TArray.Sort는 안정적이지 않으므로 ab와 AB처럼 같다고 비교되는 키는 어느 순서로든 나올 수 있습니다. 같은 키의 원래 순서가 중요하다면 원래 위치를 보조 키로 삼아 인덱스 배열을 정렬하세요. 반대 경우도 있습니다. X-100과 X100이 별개 코드이고 코드 포인트 순으로 정렬되어야 하는 부품 번호처럼, 열이 Excel의 순서를 따르지 말아야 할 때가 있습니다. TXLSXWorksheet.SortRange에는 TXLSSortCompareEvent, 즉 시그니처가 function(const Left, Right: Variant): Integer of object인 메서드를 받는 오버로드가 있어 내장 비교 대신 그것을 씁니다

uses
  System.SysUtils, System.Variants, lxStandard, lxHandleX;

type
  TPartNumberOrder = class
    function Compare(const Left, Right: Variant): Integer;
  end;

function TPartNumberOrder.Compare(const Left, Right: Variant): Integer;
begin
  // 커스텀 비교자는 빈 셀도(Null로) 받습니다: 직접 배치하세요
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal, 대소문자 구분
end;

var
  Sheet: TXLSXWorksheet;   // 채워진 시트, 2..501행, A..D열
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // A열 기준 키, 오름차순
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

커스텀 비교자가 공급되면 HotXLS는 자기 빈 셀 처리를 건너뛰고 날 키 값을 넘기므로, 비교자가 Null을 다뤄야 합니다. 내림차순 키에서는 HotXLS가 비교자의 반환값을 무엇이든 음수화하며, 비교자가 신경 쓰지 않으면 빈 셀도 꼭대기로 옮겨 갑니다. 이렇게 정렬된 열은 더 이상 Excel의 근사 VLOOKUP이나 이진 탐색 XLOOKUP이 기대하는 순서가 아니라는 점을 명심하세요. 다른 순서로 정렬된 데이터에서 그 모드들의 함정은 XLOOKUP과 XMATCH 이진 탐색 모드 가이드에서 다룹니다

같은 통합 문서가 다른 기계에서 다르게 정렬될 수 있는 이유는?

같은 통합 문서가 다른 기계에서 다르게 정렬될 수 있는 이유는 Excel의 텍스트 순서가 Windows 사용자 로캘에 의존하고 HotXLS가 그 의존성을 일부러 따르기 때문입니다. 워드 정렬은 언어별입니다. 예컨대 스웨덴 콜레이션은 ä를 z 뒤에 두지만 영어와 독일어는 a 옆에 둡니다. Excel은 실행 중인 로캘에서 그것을 물려받으므로, 스톡홀름 동료가 재계산한 통합 문서는 시카고 데스크톱의 같은 파일과 COUNTIF(...,">y")가 다르게 나올 수 있습니다. HotXLS는 같은 기계에서 결과가 Excel과 같도록 LOCALE_USER_DEFAULT를 넘깁니다. 고정 로캘을 쓰면 설정이 다른 모든 기계에서 HotXLS가 Excel과 어긋납니다

서버 측 생성에는 실용적 귀결이 셋 따라옵니다:

• 중요한 로캘은 프로세스가 실행되는 계정의 로캘입니다. Windows 서비스나 IIS 애플리케이션 풀은 개발자 데스크톱과 다른 지역 형식을 쓸 수 있으므로, IDE에서 관찰한 결과가 곧 프로덕션이 계산하는 것은 아닙니다
• 파일에 쓰인 캐시된 수식 결과는 생성 기계의 로캘을 반영합니다. Excel은 자기 로캘로 재계산하므로, 파일이 다른 곳에서 열려 재계산되면 값이 바뀔 수 있습니다. 그것은 Excel의 동작이지 HotXLS 인공물이 아닙니다
• 로캘은 주로 악센트 문자, 일부 언어가 하나의 글자로 다루는 문자 조합, 비 라틴 문자에서 갈리므로, 순수 영어 단어만으로 이루어진 테스트 데이터는 문제를 드러내지 못합니다

플랫폼 경계는 단순합니다. HotXLS는 Windows 라이브러리로, Delphi와 C++Builder로는 Win32와 Win64용, Lazarus와 Free Pascal로는 win32 / win64 대상용으로 빌드되며, 이 빌드 전부가 같은 CompareStringW를 호출합니다. 별개의 비 Windows 콜레이션 경로는 없습니다. 유일한 폴백은 실패한 API 호출용입니다. CompareStringW가 0을 돌려주면 XlsCompareText는 재계산 도중에 예외를 일으키는 대신 대문자로 접은 문자열을 코드 유닛으로 비교합니다. 계산은 계속 돌지만 Excel의 순서는 더 이상 보장되지 않습니다

빠른 참조: HotXLS의 Excel 텍스트 비교

• 규칙: NORM_IGNORECASE를 쓴 사용자 로캘 워드 정렬, SORT_STRINGSORT 없음, NORM_IGNOREWIDTH 없음. HotXLS는 v2.384.67부터
• -와 '는 동률만 가릅니다. ="a-b">"ab"는 TRUE이고 ="a-b"="ab"는 FALSE
• 다른 문장 부호는 숫자보다, 숫자는 문자보다 앞서 정렬됩니다. ="a~b"<"ab"와 ="a0"<"ab"는 TRUE
• 대소문자는 결코 문제가 되지 않습니다. ="ABC"="abc"는 TRUE이고 VLOOKUP("ABC",...)는 abc를 찾습니다
• 커버되는 경로: 비교 연산자, 배열 비교, > / < 조건, VLOOKUP / HLOOKUP, 동적 배열 순서화, 두 엔진의 SortRange
• 이 규칙이 커버하지 않는 것: 혼합 타입(number < text < boolean)과 와일드카드 조건. 각자 자기 규칙이 있습니다
• Delphi 코드에서는 CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)로 0을 검사하고 CSTR_EQUAL을 뺄 것. 결과가 Excel과 일치해야 한다면 CompareText, CompareStr, TComparer<string>.Default는 피할 것
• 결과는 코드를 실행하는 계정의 로캘에 의존합니다. Excel도 HotXLS도 마찬가지입니다

평범한 단어는 어떤 규칙 아래에서도 똑같이 정렬되므로, 하이픈 든 코드, 문장 부호, 악센트 이름만이 잘못된 콜레이션을 드러냅니다. HotXLS는 이제 XLS와 XLSX 엔진 모두에서 이 모든 것에 대해 Excel의 답을 줍니다. 라이선싱, 지원 Delphi와 C++Builder 버전, 평가판 다운로드의 세부는 HotXLS Delphi Excel 컴포넌트 페이지에 있습니다