Excel 365는 파일이 수식을 일반 수식으로 저장하고 있으면 =SUM(A1:B1*{10,100}) 같은 수식에 @를 삽입하고 #VALUE!를 표시하는데, 그때 Excel이 모든 연산자 피연산자에 레거시 암시적 교차를 적용하기 때문입니다. v2.384.68부터 HotXLS Delphi Component는 이런 배열 연산자 수식을 Excel 365와 같은 방식으로 저장합니다. XLSX에서는 단일 셀 동적 배열 수식으로, XLS에서는 한 셀 배열 수식으로요
이 증상은 코드 리뷰를 통과합니다. Delphi 서비스가 통합 문서를 쓰고 HotXLS가 재계산해 =SUM(A1:B1*{10,100})에 대해 210을 캐시해도, 고객이 Excel 16에서 열면 수식 표시줄에는 =SUM(@A1:B1*@{10,100})이, 셀에는 #VALUE!가 들어 있습니다. 파일 어디도 잘못된 게 아닙니다. 빠진 것은 이 수식이 동적 배열 규칙 아래에서 작성됐다고 Excel에게 알려 주는 메타데이터이고, 그것이 없으면 Excel은 동적 배열 이전 평가 모델로 물러납니다
HotXLS가 올바르게 계산한 수식에 Excel 365는 왜 @를 붙일까?
Excel 365가 @를 붙이는 이유는 동적 배열 표식이 없는 수식은 정의상 레거시 수식이고, 레거시 수식은 연산자가 단일 값을 기대하는 자리마다 다중 셀 범위를 한 셀로 줄이기 때문입니다. 그 축소가 암시적 교차입니다. Excel은 수식과 같은 행(세로 범위)이나 열(가로 범위)을 공유하는 범위의 셀을 가져오고, 그런 셀이 없으면 결과는 #VALUE!입니다. Excel 365는 옛 스타일 수식에 대해 그 의미를 유지하며 축소가 보이도록 @를 표시합니다
=SUM(A1:B1*{10,100})을 E5에 넣으면 레거시 해석이 명백해집니다. A1:B1은 가로 범위이고 수식은 E열에 있으며 범위에는 E열의 셀이 없으므로 @A1:B1은 #VALUE!이고 SUM 전체가 그것을 물려받습니다. 동적 배열 규칙 아래에서는 같은 텍스트가 요소별로 곱해져 1 × 10 + 2 × 100, 즉 210을 돌려줍니다. HotXLS 수식 엔진은 v2.384.61과 v2.384.63 릴리스부터 동적 배열 방식으로 평가해 왔고, 파일 형식만 그렇게 말하지 않았을 뿐입니다. A1:B2에 1, 2, 3, 4가 들어 있을 때의 탐침 수식과 Excel 16이 표시하는 값입니다:
| 수식 | HotXLS 결과 | Excel 16, 일반 수식으로 저장 시 | v2.384.68부터 저장 |
|---|---|---|---|
=SUM(A1:B1*{10,100}) | 210 | #VALUE! | 동적 배열, Excel은 210 표시 |
=SUM((A1:B2>2)*1) | 2 | 암시적 교차, 잘못된 값 또는 오류 | 동적 배열, Excel은 2 표시 |
=SUMPRODUCT((A1:B2>2)*1) | 2 | 암시적 교차, 잘못된 값 또는 오류 | 동적 배열, Excel은 2 표시 |
=MAX(A1:B2-1) | 3 | 암시적 교차, 잘못된 값 또는 오류 | 동적 배열, Excel은 3 표시 |
=SUM(A1:B2) | 10 | 10 | 일반 수식, 변경 없음 |
마지막 행은 앞의 네 행만큼 중요합니다. SUM(A1:B2)는 범위를 참조를 받는 함수 매개변수에 곧바로 넘기므로 어떤 연산자도 다중 셀 범위를 보지 못하고 교차가 일어날 수 없습니다. Excel 365 자신도 그 수식을 일반 수식으로 저장하며 HotXLS도 마찬가지입니다
HotXLS가 XLSX와 XLS에 배열 연산자 수식을 저장하는 방법
HotXLS는 XLSX에서 배열 연산자 수식을 단일 셀 동적 배열로 씁니다. <c> 요소가 cm="1"을 달고, 수식은 <f t="array" ref="E5">가 되고, 패키지에는 dynamicArrayProperties fDynamic="1"을 담은 확장이 있는 XLDAPR 메타데이터 타입과 함께 xl/metadata.xml이 생깁니다. cm 속성은 그 파트의 cellMetadata 블록에 대한 1 기반 인덱스이고, 그 뒤에 있는 XLDAPR 레코드가 Excel에게 "이것을 동적 배열 규칙으로 평가하라"고 말하는 장치입니다. 같은 수식을 입력하고 저장할 때 Excel 16이 쓰는 것과 동일한 구조이며, 목표 레이아웃도 원래 그렇게 확정된 것입니다
XLS에는 메타데이터 파트가 없으므로 HotXLS는 BIFF8에 배열 평가를 위해 존재하는 유일한 구성물인 한 셀 배열 수식을 씁니다. 셀에는 자기 자신을 가리키는 단일 PtgExp로 이루어진 토큰 스트림을 가진 FORMULA 레코드가 붙고, 뒤이어 한 셀 범위 위에 실제 파싱된 수식을 실은 ARRAY 레코드($0221)가 옵니다. Excel 365도 같은 방식으로 동적 배열 수식을 XLS에 쓰며, 파일을 읽는 더 오래된 Excel 버전에는 고전적인 Ctrl+Shift+Enter 배열 수식으로 보입니다
새 API는 관여하지 않습니다. 표식은 두 엔진 모두에서 평범한 셀 API로 수식을 대입할 때 붙습니다. XLSX 쪽에서는 TXLSXCell.Formula입니다:
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Data');
Sheet.Cells[1, 1].Value := 1;
Sheet.Cells[1, 2].Value := 2;
Sheet.Cells[2, 1].Value := 3;
Sheet.Cells[2, 2].Value := 4;
// 범위나 인라인 배열 위의 연산자: 동적 배열로 저장됨
Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
// 함수에 곧바로 넘긴 범위: 평범한 <f>로 유지됨
Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';
if Book.Recalculate = lxOk then
Writeln(VarToStr(Sheet.Cells[5, 5].Value)); // 210
// 배열 루트는 선행 '=' 없이 텍스트를 유지합니다
Writeln(Sheet.Cells[5, 5].Formula); // SUM(A1:B1*{10,100})
Book.SaveAs('probe.xlsx'); // E5와 E6에 cm="1" + t="array"가 붙음
finally
Book.Free;
end;
end;변환 후 TXLSXCell.Formula는 = 없는 텍스트를 돌려주며, 그것은 TXLSXRange.SetDynamicArrayFormula가 저장하는 형태와 같습니다. 그러므로 대입 후 수식 문자열을 비교하는 코드는 선행 =을 정규화해야 합니다
클래식 엔진도 단일 셀에 대한 IXLSRange.Formula를 통해 같은 규칙을 따릅니다. 수식을 대입하면 내부적으로 한 셀 배열 경로로 재라우팅되므로, 저장된 XLS에는 FORMULA와 ARRAY 짝이 들어 갑니다:
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add;
Sh.Range['A1', 'A1'].Value := 1;
Sh.Range['B1', 'B1'].Value := 2;
Sh.Range['A2', 'A2'].Value := 3;
Sh.Range['B2', 'B2'].Value := 4;
Sh.Range['E5', 'E5'].Formula := '=SUM(A1:B1*{10,100})'; // ARRAY 레코드
Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)'; // ARRAY 레코드
Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)'; // 일반 FORMULA
Writeln(VarToStr(Sh.Range['E5', 'E5'].Value)); // 210
Writeln(VarToStr(Sh.Range['E6', 'E6'].Value)); // 3
Wb.SaveAs('probe.xls');
end;스칼라 집계가 아니라 다중 셀 결과를 고정하려는 것이라면 명시적 API가 여전히 옳은 도구입니다. 미리 크기를 정한 사각형에는 HotXLS의 동적 배열 spill 수식에서 기술한 SetArrayFormula를, 직접 크기를 정한 범위에 XLSX 동적 배열 표식을 원하면 TXLSXRange.SetDynamicArrayFormula를 쓰세요. 이 글의 자동 경로는 한 셀에 입력한 수식만 다룹니다
HotXLS는 어떤 수식을 동적 배열로 표식할까?
HotXLS는 연산자가 배열을 생산하는 피연산자 하위 트리를 가질 때만 수식을 표식합니다. 검사는 컴파일된 구문 트리에서 돌고, 피연산자는 다중 셀 범위, 인라인 배열 상수, 혹은 그런 피연산자를 품은 또 다른 연산자 식일 때 배열을 생산합니다. 괄호는 투명합니다. 해당 연산자는 산술 연산자(+ - * / ^), 연결(&), 여섯 비교 연산자, 단항 플러스와 마이너스, 퍼센트입니다:
A1:B1*{10,100},(A1:B2>2)*1,--(B1:B2>0),A1:B2-1은 표식됩니다. 수식 어디에 나타나든, SUMPRODUCT 안이더라도요SUM(A1:B2)와SUMPRODUCT(A1:A2,{1;10})는 표식되지 않습니다. 범위와 배열이 함수 인자로 곧바로 들어가고 어떤 연산자도 그것들을 만지지 않기 때문입니다A1*2나SUM(A1,B1)*2는 표식되지 않습니다. 단일 셀 참조와 함수 결과는 이 검사에게 스칼라입니다
세 가지 경계는 의도적인 것입니다. 첫째, 표식은 수식이 API를 통해 입력될 때만 일어납니다. XLSX 엔진의 TXLSXCell.Formula와 클래식 엔진의 단일 셀 Formula 또는 Value 대입이 그 대상입니다. 파일에서 불러온 수식은 발견된 그대로 정확히 다시 쓰입니다. 다른 생산자의 레거시 수식은 암시적 교차에 의도적으로 의존할 수 있기 때문입니다. 둘째, :도 {도 포함하지 않는 텍스트는 두 번째 컴파일 없이 건너뜁니다. 셋째, =A1:B1*2 단독 같은 spill하는 수식은 여러분이 놓은 자리에 고정된 단일 셀 동적 배열로 표식됩니다. HotXLS는 spill시키지 않으며, Excel이 다음 재계산 때 결과를 이웃 셀로 확장할 것입니다
이 피연산자 규칙은 HotXLS의 정의된 이름에 대한 암시적 교차에서 다룬 인자 클래스 규칙의 형제입니다. 그 글이 value 클래스로 선언된 함수 매개변수에 관한 것이라면 이 글은 연산자에 관한 것이고, 레거시 모델에서 연산자는 언제나 값을 요구합니다
결과를 일치시키기 위해 계산 엔진에서 바뀐 것
v2.384.68의 저장 수정은 HotXLS 수식 엔진이 이미 Excel 365 값을 돌려주고 있다는 데 기대며, 그것은 두 엔진에서 여러 차례의 이전 수정을 거쳤습니다. 가장 눈에 띈 것은 SUMPRODUCT였습니다. v2.384.61까지는 평범한 범위 두 개 이상만 받아서 SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)), 인자 하나짜리 SUMPRODUCT(B1:B2)조차 #N/A를 돌려줬습니다. HotXLS는 이제 식 인자를 Excel의 규칙으로 요소별로 평가합니다:
- 모든 인자가 정확히 같은 모양이어야 합니다. 스칼라는 1 × 1로 칩니다. 그렇지 않으면 결과는
#VALUE!입니다 - 어떤 인자 안의 오류 값은 결과로 그대로 돌려줍니다
- 텍스트와 논리 요소는 0으로 치므로 TRUE를 1로 만들려면 여전히
(B1:B2>0)*1이나--가 필요합니다 - 모두 평범한 범위인 인자는 원래의 스트리밍 루프를 유지하므로 큰 범위가 배열로 구체화되지 않습니다
SUM 계열(SUM, COUNT, AVERAGE, MIN, MAX, COUNTA)은 인자가 범위 위의 연산자 식일 때 같은 요소별 평가기를 쓰므로, =SUM((B1:B2>0)*1)은 첫 셀만 보는 대신 두 행을 모두 셉니다. v2.384.62는 공백 교차 연산자가 두 참조의 공통 사각형을 돌려주도록 만들었고, 겹치지 않으면 #NULL!입니다. 그래서 =SUM(A1:B2 B1:B2)는 2가 아니라 6이고, 결과는 ROWS나 INDEX 같은 참조 매개변수에 먹일 수 있습니다. v2.384.63은 {1,2;3,4} 같은 인라인 배열 상수(쉼표는 열을, 세미콜론은 행을 나눕니다)와 (A1:B2,D4) 같은 참조 합집합을 파서에 추가했습니다. 요소별 비교는 빈 요소에게 상대편의 타입, 논리에 대해서는 FALSE를 주는데, 이는 HotXLS의 비교 연쇄와 빈 셀에서 기술한 v2.384.53의 스칼라 규칙과 일치합니다
var
V: Variant;
begin
// Book은 첫 예제의 TXLSXWorkbook이고
// 활성 시트에는 A1:B2 = 1, 2, 3, 4가 들어 있습니다
V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)'); // 2
V := Book.Calculate('=SUMPRODUCT(A1:B2)'); // 10, 단일 인자
V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})'); // 31 = 1*1 + 3*10
V := Book.Calculate('=SUM(A1:B2 B1:B2)'); // 6, 공통 범위 B1:B2
V := Book.Calculate('=SUM((A1:B2,B1:B2))'); // 16, 겹침을 두 번 셈
V := Book.Calculate('=ROWS({1,2,3;4,5,6})'); // 2
V := Book.Calculate('=TRUE*1'); // 1, v2.384.61 전에는 -1이었음
end;TXLSXWorkbook.Calculate는 수식 문자열을 저장 없이 활성 시트에 대해 평가하는, 엔진 동작을 빠르게 확인하는 방법입니다. @ 자체에 대한 한 가지 주의: HotXLS는 예전부터 두 참조 사이의 @를 이진 교차로 받아들여 왔고, 이제는 그 형태를 진짜 교차 의미론으로 평가합니다. Excel 365에서 @는 단항 암시적 교차 접두사입니다. 수식 텍스트에 @를 쓰고 Excel의 의미를 기대하지 마세요. 교차에는 공백을 쓰고, 동적 배열 의미론은 위의 저장 규칙에 맡기세요
Excel은 파일 열기를 거부하거나 엉뚱한 값을 왜 계산했을까?
Excel이 동적 배열 표식을 받아들이게 하는 데는 자기 왕복 테스트로는 잡을 수 없는 수정 세 번이 필요했습니다. 모든 경우에 HotXLS는 자기 출력을 올바르게 읽었으니까요. 각각은 HotXLS 출력을 Excel 16에서 열고 변수를 하나씩 바꿔 가며 발견됐습니다:
- 확장 GUID는 전부 소문자여야 합니다.
xl/metadata.xml의ext uri는 정확히{bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}여야 합니다. 오래된 HotXLS 템플릿은 대소문자를 섞어 썼고 Excel 16은 셀만이 아니라 패키지 전체 열기를 거부했습니다. v2.384.68 전에TXLSXRange.SetDynamicArrayFormula로 만든 통합 문서도 같은 문제가 있었습니다 - 배열 루트 텍스트는 선행
=을 달지 않습니다. XLSX writer는 배열 루트의 저장 텍스트를<f>에 그대로 내보냅니다. 변환된 셀이=을 간직하고 있었다면 요소는<f t="array" ref="E5">=SUM(...)</f>처럼 읽혔을 것이고 Excel은 열 때 그것도 거부합니다. HotXLS는 변환 중에 그것을 떼어내며, 그래서TXLSXCell.Formula가 읽을 때는 붙어 있지 않습니다 - Delphi에서
Double(True)는 -1입니다. Variant 변환은 TRUE가 모든 비트가 켜진 값인 COM 관례를 따르며VarIsNumeric(True)도 True를 돌려줍니다. v2.384.61 전에는 그 때문에=TRUE*1이 -1을 돌려주고 논리 배열 요소가 숫자로 분류됐으며,(B1:B2>0)=TRUE같은 비교가 틀어졌습니다. HotXLS는 이제 스칼라 산술, 배열 산술, 배열 요소 분류에서 Variant를 숫자로 다루기 전에varBoolean을 검사하고 TRUE는 1로 칩니다
BIFF8 피연산자 클래스: 형식 구현자를 위한 바이트 수준 세부
BIFF8에서 모든 피연산자 토큰은 토큰 바이트 자체에 피연산자 클래스를 실어 다니며, Excel은 수식의 구조보다 그 클래스를 더 신뢰합니다. [MS-XLS]는 클래스를 토큰의 비트 5와 6에 있는 2비트 PtgDataType 필드로 정의합니다. 참조는 1, 값은 2, 배열은 3입니다. 하위 5비트가 토큰 이름을 정하므로 같은 영역 참조에 세 가지 표기가 존재합니다:
| 토큰 | 참조 클래스 | 값 클래스 | 배열 클래스 |
|---|---|---|---|
PtgRef | $24 | $44 | $64 |
PtgArea | $25 | $45 | $65 |
PtgArray | $20 | $40 | $60 |
HotXLS는 이 중 세 곳을 서로 다른 자리에서 틀렸고, 각각은 HotXLS에서는 잘 읽히면서 Excel에서는 뚜렷이 다른 증상을 냈습니다:
- 참조 클래스 배열 상수. 인코더는 문맥에서 클래스를 골랐고 SUM이나 ROWS 매개변수는 참조 클래스이므로,
=SUM({1,2})는PtgArray가$20인 채로 쓰였습니다. Excel은 수식 전체를=#N/A로 표시합니다. 배열 상수는 결코 참조일 수 없으므로, v2.384.63부터 HotXLS는 문맥이 참조를 요구하는 곳이면 어디든 배열 클래스$60을 씁니다 PtgIsect와PtgUnion의 값 클래스 피연산자. 이진 연산자는 값 클래스 피연산자를 받았는데,*에게는 맞지만 참조 연산자에게는 틀립니다.$45영역이PtgIsect($0F) 앞에 오면 Excel은=SUM(A1:B2 B1:B2)를=SUM(@A1:B2 @B1:B2)로 읽고#VALUE!를 돌려줬습니다. v2.384.62부터PtgIsect와PtgUnion($10)의 피연산자는 참조 클래스인$25로 쓰입니다- ARRAY 레코드 안의 값 클래스 피연산자. Excel은 배열 수식 안에서도 피연산자가 값 클래스이면 암시적 교차를 적용합니다. HotXLS는 그곳에
$45를 썼으므로=SUM(A1:B1*{10,100})의 한 셀 배열 수식은 Excel에서 10으로 평가됐습니다. v2.384.68부터 ARRAY 레코드의 토큰 스트림은 모든 값 클래스 참조와 배열 상수를 배열 클래스인$65와$60으로 승격시키며, 그것이 Excel이 쓰는 방식입니다
클래스 비트를 무시하는 reader는 세 가지 모두 쾌적하게 왕복시킵니다. 자기 BIFF8 writer를 유지보수한다면 토큰 번호만이 아니라 모든 피연산자 토큰의 클래스 비트를 같은 수식을 Excel이 저장한 파일과 비교해 보세요
빠른 참조
- 표식 없는 일반 수식의 연산자가 다중 셀 범위나 인라인 배열을 받으면 Excel 365는
@를 표시합니다 - HotXLS v2.384.68 이상은 그런 수식을 XLSX 단일 셀 동적 배열(
cm="1",t="array",XLDAPR메타데이터)과 XLS 한 셀 배열 수식(PtgExp가 있는 FORMULA에 ARRAY$0221)로 저장합니다 - 연산자 피연산자만 해당됩니다. 함수 인자에 곧바로 넘긴 범위는 일반 수식으로 유지됩니다
TXLSXCell.Formula나 클래식 단일 셀Formula/Value로 입력된 수식만 표식됩니다. 불러온 수식은 그대로입니다- 변환된 루트 셀은 선행
=없이 읽힙니다 - 동적 배열
ext uriGUID는 소문자여야 하며, 그렇지 않으면 Excel이 패키지를 거부합니다 - Delphi에서
Double(True)는 -1입니다. 숫자 변환 전에varBoolean을 검사하세요 - BIFF8: 배열 상수는 결코 참조 클래스가 아니고,
PtgIsect/PtgUnion피연산자는 참조 클래스, ARRAY 레코드 피연산자는 배열 클래스입니다
HotXLS는 Delphi와 C++Builder에서 XLS와 XLSX 통합 문서를 네이티브로 읽고, 쓰고, 계산하며, 배열 연산자 수식을 HotXLS가 계산한 값 그대로 Excel 365가 열도록 저장합니다. 에디션, 문서, 평가판 다운로드는 HotXLS Delphi 스프레드시트 컴포넌트를 참조하세요