Excel 365는 파일이 수식을 일반 수식으로 저장하고 있으면 =SUM(A1:B1*{10,100}) 같은 수식에 @를 삽입하고 #VALUE!를 표시합니다. 그때 Excel이 모든 연산자 피연산자에 레거시 암시적 교차를 적용하기 때문입니다. HotXLS Delphi Component는 이런 배열 연산자 수식을 Excel 365와 같은 방식으로 저장합니다. XLSX에서는 단일 셀 동적 배열 수식으로, XLS에서는 한 셀 배열 수식으로요
이 증상은 코드 리뷰를 통과합니다. Delphi 서비스가 통합 문서를 쓰고, 그 수식 엔진이 일반 수식 셀의 =SUM(A1:B1*{10,100})에 대해 210을 캐시해도, 고객이 Excel 16에서 열면 수식 표시줄에는 =SUM(@A1:B1*@{10,100})이, 셀에는 #VALUE!가 들어 있습니다. 파일 어디도 잘못된 게 아닙니다. 빠진 것은 이 수식이 동적 배열 규칙 아래에서 작성됐다고 Excel에게 알려 주는 메타데이터이고, 그것이 없으면 Excel은 동적 배열 이전 평가 모델로 물러납니다
올바르게 계산된 수식에 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 수식 엔진은 동적 배열 방식으로 평가하고, 파일 형식도 그렇게 말해야 합니다. A1:B2에 1, 2, 3, 4가 들어 있을 때의 탐침 수식과 Excel 16이 표시하는 값입니다:
| 수식 | HotXLS 결과 | Excel 16, 일반 수식으로 저장 시 | HotXLS가 저장하는 방식 |
|---|---|---|---|
=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입니다:
uses
SysUtils, Variants, lxCalc, lxHandle, lxHandleX; // lxOk는 lxCalc에 선언되어 있습니다
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 클래스로 선언된 함수 매개변수에 관한 것이라면 이 글은 연산자에 관한 것이고, 레거시 모델에서 연산자는 언제나 값을 요구합니다
계산 엔진은 Excel 365 값과 어떻게 일치하는가
저장 표식은 수식 엔진이 애초에 Excel 365 값을 돌려줄 때만 빛을 발합니다. 양쪽 엔진에서요. 가장 눈에 띄는 사례는 SUMPRODUCT입니다. 평범한 범위 두 개 이상만 받는 구현은 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)은 첫 셀만 보는 대신 두 행을 모두 셉니다. 공백 교차 연산자는 두 참조의 공통 사각형을 돌려주고, 겹치지 않으면 #NULL!입니다. 그래서 =SUM(A1:B2 B1:B2)는 2가 아니라 6이고, 결과는 ROWS나 INDEX 같은 참조 매개변수에 먹일 수 있습니다. 파서는 {1,2;3,4} 같은 인라인 배열 상수(쉼표는 열을, 세미콜론은 행을 나눕니다)와 (A1:B2,D4) 같은 참조 합집합을 받아들입니다. 요소별 비교는 빈 요소에게 상대편의 타입, 논리에 대해서는 FALSE를 주는데, 이는 HotXLS의 비교 연쇄와 빈 셀에서 기술한 스칼라 규칙과 일치합니다
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, TRUE는 1로 친다
end;
TXLSXWorkbook.Calculate는 수식 문자열을 저장 없이 활성 시트에 대해 평가하는, 엔진 동작을 빠르게 확인하는 방법입니다. @ 자체에 대한 주의 하나: HotXLS는 두 참조 사이의 @를 이진 교차로 받아들이고 그 형태를 진짜 교차 의미론으로 평가합니다. Excel 365에서 @는 단항 암시적 교차 접두사입니다. 수식 텍스트에 @를 쓰고 Excel의 의미를 기대하지 마세요. 교차에는 공백을 쓰고, 동적 배열 의미론은 위의 저장 규칙에 맡기세요
Excel이 동적 배열 출력을 열 때 검사하는 세 가지 세부
Excel이 동적 배열 표식을 받아들이게 하는 것은 자기 왕복 테스트로는 잡히지 않는 세 가지 세부로 귀결됩니다. 어느 쪽이든 라이브러리는 자기 출력을 올바르게 읽어 돌리니까요. 각각은 HotXLS 출력을 Excel 16에서 열고 변수를 하나씩 바꿔 가야 드러납니다:
- 확장 GUID는 전부 소문자여야 합니다.
xl/metadata.xml의ext uri는 정확히{bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}여야 합니다. 대소문자를 섞어 쓰면 Excel 16은 셀만이 아니라 패키지 전체 열기를 거부합니다 - 배열 루트 텍스트는 선행
=을 달지 않습니다. XLSX writer는 배열 루트의 저장 텍스트를<f>에 그대로 내보냅니다. 변환된 셀이=을 간직하고 있었다면 요소는<f t="array" ref="E5">=SUM(...)</f>처럼 읽혔을 것이고 Excel은 열 때 그것도 거부합니다. HotXLS는 변환 중에 그것을 떼어내며, 그래서TXLSXCell.Formula가 읽을 때는 붙어 있지 않습니다 - Delphi에서
Double(True)는 -1입니다. Variant 변환은 TRUE가 모든 비트가 켜진 값인 COM 관례를 따르며VarIsNumeric(True)도 True를 돌려줍니다. 그대로 두면=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 |
BIFF8 작성기에서 틀리기 쉬운 곳은 세 곳이고, 각각은 작성기 자신의 리더에서는 잘 읽히면서 Excel에서 뚜렷이 다른 증상을 냅니다:
- 참조 클래스 배열 상수. 문맥에서 클래스를 고르는 인코더는 SUM이나 ROWS 매개변수가 참조 클래스임을 보고
=SUM({1,2})를PtgArray가$20인 채로 씁니다. Excel은 수식 전체를=#N/A로 표시합니다. 배열 상수는 결코 참조일 수 없으므로, HotXLS는 문맥이 참조를 요구하는 곳이면 어디든 배열 클래스$60을 씁니다 PtgIsect와PtgUnion의 값 클래스 피연산자. 이진 연산자에 값 클래스 피연산자를 주는 것은*에게는 맞지만 참조 연산자에게는 틀립니다.$45영역이PtgIsect($0F) 앞에 오면 Excel은=SUM(A1:B2 B1:B2)를=SUM(@A1:B2 @B1:B2)로 읽고#VALUE!를 돌려줍니다. HotXLS는PtgIsect와PtgUnion($10)의 피연산자를 참조 클래스인$25로 씁니다- ARRAY 레코드 안의 값 클래스 피연산자. Excel은 배열 수식 안에서도 피연산자가 값 클래스이면 암시적 교차를 적용합니다. 거기에
$45가 있으면=SUM(A1:B1*{10,100})의 한 셀 배열 수식은 Excel에서 10으로 평가됩니다. 그래서 HotXLS는 ARRAY 레코드의 토큰 스트림에서 모든 값 클래스 참조와 배열 상수를 배열 클래스인$65와$60으로 승격시키며, 그것이 Excel이 쓰는 방식입니다
클래스 비트를 무시하는 reader는 세 가지 모두 쾌적하게 왕복시킵니다. 자기 BIFF8 writer를 유지보수한다면 토큰 번호만이 아니라 모든 피연산자 토큰의 클래스 비트를 같은 수식을 Excel이 저장한 파일과 비교해 보세요
빠른 참조
- 표식 없는 일반 수식의 연산자가 다중 셀 범위나 인라인 배열을 받으면 Excel 365는
@를 표시합니다 - HotXLS는 그런 수식을 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 스프레드시트 컴포넌트를 참조하세요