HotXLS는 XLSX 워크시트에서 행이나 열을 삽입하거나 삭제할 때 수식 참조를 자동으로 조정합니다. 엔진의 InsertRows, DeleteRows, InsertCols 및 DeleteCols 메서드는 구조적 편집 이후에도 상대, 절대 및 범위와 같은 A1 참조가 동일한 데이터를 계속 가리키도록 남아 있는 모든 수식을 다시 작성하며, 삭제된 블록에 대한 참조는 Excel의 동작과 일치하게 #REF!로 변환됩니다
이 기능이 방지하는 버그는 보고서 생성 과정에서 가장 알아차리기 어려운 오류 중 하나입니다. 한 생성 프로그램이 C2:C9에 일별 수치를 기록하고 그 아래에 =SUM(C2:C9)를 작성한 후, 후속 단계에서 맨 위에 헤더 행을 삽입합니다. 만약 엔진이 셀 값만 이동하고 수식 텍스트를 그대로 둔다면, 데이터가 이제 C3:C10에 존재함에도 SUM 수식은 여전히 C2:C9를 읽어 들이게 됩니다 — 결과적으로 합계에서 마지막 날짜가 자동으로 유실되고 헤더 내용이 이중으로 합산됩니다. 예외도 발생하지 않고 파일도 정상적으로 열리지만 최종 값이 잘못 산출되는 것입니다. 버전 2.160 이전까지 HotXLS XLSX engine은 구조적 편집 시 수식을 수정하지 않고 그대로 두었으나, 2.160 버전부터는 재작성 작업이 자동으로 실행되며 별도의 플래그를 설정할 필요도 없습니다
Excel에서 행을 삽입하면 수식은 어떻게 됩니까?
Excel의 기본 규칙은 참조가 주소가 아닌 데이터를 따라간다는 점입니다. 행이 삽입되면, 삽입 지점 이하에 위치한 모든 참조의 행 인덱스가 삽입된 행의 개수만큼 아래로 이동합니다; 삽입 지점보다 위에 있는 참조들은 변경되지 않습니다. 행을 삭제할 때는 동일한 규칙이 역순으로 적용됩니다: 삭제된 블록 아래의 참조들은 위로 슬라이드하고, 삭제된 블록 자체에 대한 참조는 참조하던 셀이 더 이상 존재하지 않으므로 #REF!로 변환됩니다. 열(column)도 반대쪽 축을 따라 동일하게 작동합니다. 스프레드시트 라이브러리가 생성한 출력 파일이 Excel 사용자들에게 정상적으로 받아들여지려면 이 메커니즘을 정확하게 구현해야 합니다. 사용자들이 의식하지 않고 자연스럽게 이러한 규칙에 근거해 수식을 구상하기 때문입니다
개발자들을 놀라게 하는 점은 절대 참조도 함께 이동한다는 사실입니다. $B$2의 $ 고정 기호는 수식이 다른 셀로 복사되거나 채워질 때 작동하는 기호일 뿐, 구조적 편집 중에는 아무런 영향을 주지 않습니다. 2행 위에 행을 삽입하면 Excel은 수식이 종속된 실제 값이 3행으로 이동했으므로 달러 기호를 유지한 채 $B$2를 $B$3으로 고쳐 씁니다. 상대 참조만 이동시키는 엔진은 사용자가 가장 신중하게 고정한 수식들을 파괴하고 맙니다. HotXLS는 두 형식 모두 이동시키며 변환된 텍스트에서도 $ 표시를 그대로 유지합니다
HotXLS는 어떻게 수식 참조를 자동으로 이동시킵니까?
TXLSXWorksheet의 4가지 구조적 편집 메서드는 모두 단일 지오메트리 엔진인 ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta)에 작업을 위임합니다. InsertRows(BeforeRow, Count)는 양수의 행 델타로 이를 호출하고, DeleteRows(StartRow, Count)는 음수 값으로 호출하며, 열 메서드도 열 축에 대해 동일하게 처리합니다. 이 루틴은 먼저 셀 자체를 재배치하고(삭제된 블록 내에 속한 셀은 삭제함), 남아 있는 모든 수식 셀을 탐색하며 해당 수식 텍스트를 XlsxAdjustFormulaRowColRefs에 전달합니다. 이 스캐너는 A1 스타일 참조를 찾아 행 및 열 컴포넌트를 이동에 맞춰 고쳐 씁니다. 동일한 과정을 통해 병합된 범위, 하이퍼링크, 메모, 이미지, 차트, 조건부 서식, 데이터 유효성 검사 및 테이블 범위가 함께 재배치되므로 시트 전체가 하나의 단위로 일관되게 이동합니다
// Layout before the edit:
// C2..C9 daily figures
// C10 =SUM(C2:C9)
Sheet.InsertRows(2, 1); // one blank row before row 2
// Layout after the call:
// C3..C10 daily figures
// C11 =SUM(C3:C10) -- the range moved with the data이 스캐너의 몇 가지 세부 특징들을 알아두면 좋습니다. XlsxAdjustFormulaRowColRefs는 4가지 고정 형식(A1, $A1, A$1, $A$1)의 단일 셀 참조와 A1:B3과 같은 두 모퉁이 기반 범위를 인식하여 각 종단점을 독립적으로 조정합니다. 이미 #(이전 편집의 오류 마커)으로 시작하는 수식 텍스트는 다시 스캔하지 않고 건너뜁니다. 또한 InsertCols는 Excel의 동작과 일치하도록 유용한 기능을 제공합니다: 새로 삽입된 열은 왼쪽 이웃 열의 너비를 상속받으며, 이는 Excel의 시트 열 삽입 명령의 동작과 동일합니다
삭제된 참조가 #REF!로 변환되는 조건
단일 행 또는 열 인덱스에 대한 이동 규칙은 세 가지 결과로 나뉩니다. 편집 지점 이전의 인덱스는 변경되지 않습니다. 편집 지점 이상의 인덱스는 델타만큼 이동합니다. 그리고 행/열 삭제 시, 삭제된 블록 내에 속한 인덱스는 더 이상 가리킬 셀이 없으므로 스캐너가 참조 전체를 #REF!로 변경합니다. 범위 참조의 경우 양쪽 종단점 모두에 이 규칙이 적용되며, 어느 한쪽 종단점이라도 삭제된 블록 내에 들어가는 경우 불완전한 상태로 두지 않고 참조 전체를 #REF!로 고쳐 씁니다
// A12 holds =A4+A6+A10
Sheet.DeleteRows(5, 3); // delete rows 5..7
// The formula, now in A9, reads =A4+#REF!+A7
// A4 : above the deleted block, unchanged
// A6 : inside rows 5..7, gone -> #REF!
// A10 : below the block, slides up -> A7조용히 엉뚱한 셀을 가리키게 두는 대신 명시적으로 #REF! 오류를 발생시키는 것이 올바른 처리 방식이며 Excel도 이 방식을 취합니다. 실제 입력 데이터가 삭제되었는데 수식이 인접한 엉뚱한 셀을 계속 참조하면 겉보기에는 그럴듯한 숫자가 반환될 것입니다; #REF!는 종속된 수식들을 통해 전파되므로 초기 스모크 테스트 단계에서 문제를 확실하게 드러내 줍니다. 동일한 변환이 열 축에도 적용됩니다
// E1 holds =B1*$C$1
Sheet.DeleteCols(3, 1); // remove column C
// The formula, now in D1, reads =B1*#REF!
// The absolute anchor did not protect $C$1 -- the cell itself is gone재작성되지 않는 참조 형식
스캐너는 명시적인 열 문자 및 행 번호 조합 형태를 갖는 동일 시트 내 A1 참조를 대상으로 하며, 이 범위를 벗어나는 요소를 명확히 파악해 두어야 합니다. A:A와 같은 전체 열 참조 및 1:1과 같은 전체 행 참조는 두 성분 중 하나가 결여되어 있으므로 스캐너가 수정하지 않고 그대로 둡니다. 구조화된 테이블 참조(Table1[Amount])도 마찬가지로 무시됩니다. 또한 이 재작성 작업은 A1 표기법으로만 구동되므로, 만약 R1C1 표기법으로 수식을 작성하는 경우 구조적 편집 전에 Delphi에서의 R1C1 수식 표기법 기사에서 설명한 대로 먼저 A1 형식으로 변환해야 합니다
시트 간 참조 및 통합 문서 수준의 구성 요소는 셀 텍스트 스캐너가 아닌 별도의 프로세스를 통해 처리됩니다. 편집된 시트를 조정한 후 ShiftSheetGeometry는 해당 시트를 참조하는 다른 시트의 수식, 차트 계열 범위, 내부 하이퍼링크 대상 및 정의된 이름으로 동일한 레이아웃 변경 사항을 전파합니다. 정의된 이름은 시트 수명 주기 수준에서 추가적인 보호를 받습니다: 버전 2.150부터 워크시트를 삭제하면 정의된 이름 수식 내부의 모든 SheetN! 지정자가 #REF!로 변환되고, 시트 이름을 변경하면 새 이름으로 지정자가 재작성되어 실재하지 않는 시트를 가리키는 오류를 차단합니다. 이름 정의와 시트 간 수식의 관계에 대한 자세한 내용은 정의된 이름 및 시트 간 수식 기사에서 다룹니다
레이아웃 이동 후 재계산 실행
참조 조정 작업은 수식 텍스트만 고쳐 쓸 뿐 결과를 다시 계산하지는 않습니다. 구조적 편집 이후 수식과 함께 보존된 캐시 값들은 이전 지오메트리를 설명하므로, 다음과 같은 신뢰할 수 있는 순서를 따라야 합니다: 먼저 모든 삽입 및 삭제 작업을 마친 후, 재계산을 한 번 트리거한 다음 저장하십시오. 재계산 전에 참조 이동을 마침으로써 의존성 정보를 정합성 있게 유지할 수 있습니다 — 이동된 참조가 실제 선행 셀을 가리키므로, 이는 증분 재계산 엔진 및 해당 의존성 그래프가 영향을 받는 셀의 최소 집합을 다시 계산하는 데 꼭 필요한 정보입니다. 만약 이동된 수식에 #REF!가 포함된다면, 재계산 시 파일에 오래된 캐시 값을 남겨 두는 대신 즉시 에러 값을 노출해 줍니다
수식 참조 조정 기능은 Delphi 및 C++Builder를 위한 HotXLS Delphi Excel Component의 XLSX 엔진의 표준 동작으로 기본 제공됩니다; 제품 페이지에서 본문에 소개된 삽입 및 삭제 메서드를 포함한 전체 워크시트 편집 API 레퍼런스를 보실 수 있습니다