기술 문서

HotXLS 배열 수식: Excel이 @와 #VALUE!를 붙이는 이유

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 다이어그램: 셀 E5의 SUM(A1:B1*{10,100})에 대한 암시적 교차 평가와 동적 배열 평가 비교. 레거시 모델은 가로 범위 A1:B1의 E열 셀을 찾지 못해 #VALUE!를 돌려주고, 동적 배열 모델은 1에 10을 곱하고 2에 100을 곱해 210을 돌려줍니다
암시적 교차는 E열에서 아무것도 찾지 못하므로 Excel은 일반 수식에 @를 삽입하고 #VALUE!를 표시합니다. HotXLS 동적 배열 표식이 있으면 같은 수식이 요소별로 곱해져 210에 도달합니다
수식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)1010일반 수식, 변경 없음

마지막 행은 앞의 네 행만큼 중요합니다. 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 배열 수식으로 보입니다

HotXLS 저장 다이어그램: 배열 연산자 수식 SUM(A1:B1*{10,100})을 XLSX 엔진은 cm=1, 타입 array의 f 요소, 소문자 GUID가 요구되는 xl/metadata.xml의 XLDAPR 레코드와 함께 단일 셀 동적 배열로 쓰고, XLS 엔진은 PtgExp가 있는 FORMULA 레코드에 ARRAY 레코드 0221을 짝지어 씁니다
XLSX 엔진은 셀에 cm=1과 XLDAPR 메타데이터 레코드를 표식으로 붙이고 클래식 엔진은 PtgExp FORMULA에 한 셀 위의 ARRAY 레코드를 짝짓습니다. Excel 365도 동적 배열을 XLS에 같은 방식으로 저장합니다

새 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에서 열고 변수를 하나씩 바꿔 가야 드러납니다:

  1. 확장 GUID는 전부 소문자여야 합니다. xl/metadata.xml의 ext uri는 정확히 {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}여야 합니다. 대소문자를 섞어 쓰면 Excel 16은 셀만이 아니라 패키지 전체 열기를 거부합니다
  2. 배열 루트 텍스트는 선행 =을 달지 않습니다. XLSX writer는 배열 루트의 저장 텍스트를 <f>에 그대로 내보냅니다. 변환된 셀이 =을 간직하고 있었다면 요소는 <f t="array" ref="E5">=SUM(...)</f>처럼 읽혔을 것이고 Excel은 열 때 그것도 거부합니다. HotXLS는 변환 중에 그것을 떼어내며, 그래서 TXLSXCell.Formula가 읽을 때는 붙어 있지 않습니다
  3. 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이 쓰는 방식입니다
HotXLS BIFF8 다이어그램: 각 토큰 바이트의 비트 5와 6이 참조, 값, 배열 클래스를 고르므로 PtgArea는 25, 45, 65로 표기되며, 인코딩 함정 세 가지는 이렇습니다. 배열 상수를 20으로 쓰면 #N/A, PtgIsect 피연산자를 45로 쓰면 #VALUE!, ARRAY 레코드 피연산자를 45로 쓰면 SUM(A1:B1*{10,100})이 10을 돌려줌
모든 BIFF8 피연산자 토큰은 비트 5와 6에 클래스를 실으며 Excel은 구조보다 그 비트를 신뢰합니다. HotXLS는 배열 상수를 60으로, PtgIsect 피연산자를 25로 쓰고 ARRAY 레코드 토큰을 배열 클래스로 승격시킵니다

클래스 비트를 무시하는 reader는 세 가지 모두 쾌적하게 왕복시킵니다. 자기 BIFF8 writer를 유지보수한다면 토큰 번호만이 아니라 모든 피연산자 토큰의 클래스 비트를 같은 수식을 Excel이 저장한 파일과 비교해 보세요

빠른 참조

  • 표식 없는 일반 수식의 연산자가 다중 셀 범위나 인라인 배열을 받으면 Excel 365는 @를 표시합니다
  • HotXLS는 그런 수식을 XLSX 단일 셀 동적 배열(cm="1", t="array", XLDAPR 메타데이터)과 XLS 한 셀 배열 수식(PtgExp가 있는 FORMULA에 ARRAY $0221)로 저장합니다
  • 연산자 피연산자만 해당됩니다. 함수 인자에 곧바로 넘긴 범위는 일반 수식으로 유지됩니다
  • TXLSXCell.Formula나 클래식 단일 셀 Formula / Value로 입력된 수식만 표식됩니다. 불러온 수식은 그대로입니다
  • 변환된 루트 셀은 선행 = 없이 읽힙니다
  • 동적 배열 ext uri GUID는 소문자여야 하며, 그렇지 않으면 Excel이 패키지를 거부합니다
  • Delphi에서 Double(True)는 -1입니다. 숫자 변환 전에 varBoolean을 검사하세요
  • BIFF8: 배열 상수는 결코 참조 클래스가 아니고, PtgIsect / PtgUnion 피연산자는 참조 클래스, ARRAY 레코드 피연산자는 배열 클래스입니다

HotXLS는 Delphi와 C++Builder에서 XLS와 XLSX 통합 문서를 네이티브로 읽고, 쓰고, 계산하며, 배열 연산자 수식을 HotXLS가 계산한 값 그대로 Excel 365가 열도록 저장합니다. 에디션, 문서, 평가판 다운로드는 HotXLS Delphi 스프레드시트 컴포넌트를 참조하세요