Excel & IT Info

아이엑셀러 닷컴, 엑셀러TV

Excel

엑셀 파일에 생기(?)를 되찾게 해 준 점 2개

권현욱(엑셀러) 2026. 11. 4. 17:00
반응형

들어가기 전에

Excel의 TRIMRANGE 함수와 Trim References를 활용하면 범위 가장자리의 빈 행·열을 자동으로 제외해, 동적 배열 수식을 더 간결하고 효율적으로 작성할 수 있습니다. 특히 전체 열 참조에서도 실제 데이터만 계산하도록 범위를 자동 조정해 불필요한 계산을 줄이고, 성능과 수식 가독성을 함께 높여줍니다. 데이터가 늘어나거나 줄어드는 환경에서 수식 수정 없이 범위를 자동 관리할 수 있어, 동적 데이터 처리와 대용량 시트 작업에 유용합니다.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft MVP · 엑셀 솔루션 프로바이더 · 작가

gemini / iexceller


이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

  • 원문: I added two dots to my Excel references and my slow workbook came back to life
  • URL: https://www.makeuseof.com/trimrange-and-trim-references-in-excel/

"엑셀 고급 차트" 전자책 미리보기

TRIMRANGE 및 Trim References

muo

 

A:A처럼 전체 열을 참조할 때 Excel은 데이터 아래의 빈 셀을 무시하지 않습니다. 오히려 수식이 다시 계산될 때마다 모든 빈 행(백만 개 이상의 빈 행)을 처리합니다. Microsoft는 이 문제를 해결하기 위해 TRIMRANGE라는 함수를 개발했습니다. 이 함수는 범위의 바깥쪽 가장자리에 있는 빈 행과 열을 제외하도록 설계되어 Excel의 동적 배열 에서 끝에 0이 남는 현상을 방지하고, 수식이 빈 공간에 대한 처리에 불필요한 연산 시간을 낭비하지 않도록 합니다.

 

TRIMRANGE 함수는 범위의 가장자리에서 안쪽으로 스캔하여 실제 데이터에 도달할 때까지 진행하며, 중요한 셀만 포함하도록 참조를 효과적으로 축소합니다. 구문은 다음과 같습니다.

=TRIMRANGE(range, [trim_rows], [trim_cols])

 

trim 인수는 0~3의 간단한 척도를 사용합니다. 0은 아무것도 다듬지 않고, 1은 앞쪽 공백만 다듬고, 2는 뒤쪽 공백만 다듬고, 3(기본값)은 앞쪽과 뒤쪽 공백 모두를 다듬습니다.

 

이것만으로도 유용하지만, 더 좋은 기능도 있습니다. 트림 참조(Trim References)를 사용하면 표준 범위 참조에서 콜론 하나만 수정하여 동일한 동작을 실행할 수 있습니다. 세 가지 변형이 있습니다.

변형 예시
전체 트리밍 (.:.) A1.:.E10은 양쪽 끝에서 빈 부분을 잘라냅니다.
끝 공백 제거 (:.) A1:.E10은 후행 공백만 다듬습니다.
앞쪽 공백 제거 (.:) A1.:E10은 선행 공백만 다듬습니다.

 

실제로는 뒤쪽 공백 제거 버전을 가장 자주 사용하게 될 겁니다. 대부분의 스프레드시트는 행을 추가할수록 아래쪽으로 확장되므로, 엑셀에서 무시하기를 원하는 공백은 일반적으로 데이터 위가 아니라 아래에 위치합니다. Trim Refs는 TRIMRANGE 함수가 이미 수행할 수 없는 작업을 하는 것은 아니며, 참조를 전체 함수로 감싸지 않고도 몇 번의 키 입력만으로 동일한 공백 제거 동작을 얻을 수 있게 해줄 뿐입니다.


"엑셀 대시보드" 전자책 미리보기

권현욱(엑셀러) 집필 · 287쪽 · 13,500원

 

엑셀 대시보드 실전 워크북 | 아이엑셀러

Microsoft Excel MVP 권현욱(엑셀러) 지음. 핵심 기술 6단계 + 실전 프로젝트 6개로 완성하는 업무 보고서.

www.iexceller.com


A.:.A (또는 A:.A)가 전체 열 지연을 해결하는 방법

muo

 

전체 열을 참조하는 것은 편리하지만 성능 문제가 발생하기 쉽습니다. 다음과 같은 수식을 예로 들어 보겠습니다.

=LEN(G:G)

 

G열의 모든 주문 ID의 텍스트 길이를 측정하여 형식이 올바른지 확인하고 싶다고 가정해 보겠습니다. 수식 자체는 간단해 보이지만, Excel은 이를 해당 열의 1,048,576개 행 전체를 처리하라는 요청으로 받아들입니다. 여기에는 아무 내용도 없는 1,048,566개 행(예시)도 포함되며, Excel은 이러한 행들 모두에 대해 결과(대부분 0으로 이루어진 문자열)를 반환합니다. 이와 유사한 수식이 수십 개 있는 통합 문서에서 이러한 현상이 반복되면 Excel은 매우 느려지고 문제가 발생할 수 있습니다.

이 두 수식 중 하나에 대한 참조를 바꾸면 Excel의 동작 방식이 완전히 달라집니다.

=LEN(G:.G)
=LEN(G.:.G)

 

엑셀은 열 전체를 평가하는 대신, 값이 입력된 부분에만 집중하고 나머지는 마치 없는 것처럼 무시합니다. 이렇게 하면 열 전체를 참조하는 편리함을 유지하면서도 성능 저하 문제를 해결할 수 있습니다.

 

이 접근 방식은 특히 LAMBDA 함수와 동적 배열을 사용할 때 유용합니다. 예를 들어, 텍스트 블록에서 특정 문자가 몇 번 나타나는지 세는 COUNTCHAR라는 사용자 지정 LAMBDA 함수를 만들었다 고 가정해 보겠습니다. 주문 우선순위 열을 이 함수에 전달하여 재고에서 문자 "H"가 몇 번 나타나는지 확인하고 싶습니다. 트림 참조를 사용하지 않는 경우 다음과 같은 수식을 사용할 수 있습니다.

=BYROW(E:E, LAMBDA(row, COUNTCHAR(row, "H")))

 

문제는 LAMBDA 함수와 BYROW 같은 헬퍼 함수가 데이터를 순차적으로 처리한다는 점입니다. E:E 순으로 전달하면 사용자 지정 로직이 백만 개가 넘는 행을 순환해야 하는데, 이 행들 대부분이 비어 있습니다. 엑셀은 이러한 빈 행들을 내부적으로 처리해야 하므로 계산 속도가 느려지고 통합 문서가 응답하지 않을 수 있습니다.

Trim Ref를 사용하면 해당 문제가 해결됩니다.

=BYROW(E.:.E, LAMBDA(row, COUNTCHAR(row, "H")))

 

E.:.E는 자동으로 데이터가 포함된 셀 범위로 지정하기 때문에 람다 루프가 훨씬 빠르게 완료됩니다. 수백만 개의 빈 행을 반복하며 시간을 낭비하는 대신, Excel은 중요한 레코드에 집중할 수 있습니다.

참조 범위 자르기(Trim Ref)는 동적 배열에서도 유용합니다. 이를 통해 수식이 방대한 범위에 걸쳐 적용되어 워크시트에 원치 않는 소수점 이하 자리가 0으로 채워지는 것을 방지할 수 있습니다. 더 나아가, 넓은 범위를 반복적으로 스캔하는 모든 수식은 참조 범위 자르기를 적용하기에 적합합니다. 이러한 계산을 값이 입력된 셀로 제한하면 계산량이 많아지는 통합 문서에서 상당한 성능 향상을 기대할 수 있습니다.

물론, 그렇다고 해서 전체 열 참조를 완전히 버려야 한다는 의미는 아닙니다. 전체 열 참조는 여전히 수식을 만드는 가장 빠르고 유연한 방법 중 하나입니다. 더 나은 접근 방식은 빠르고 간단한 작업을 할 때는 A:A를 계속 사용하고, 통합 문서 속도가 느려지기 시작하면 A:.A로 전환하는 것입니다.

 

마치며

TRIMRANGE는 데이터의 어떤 부분을 중요하게 여길지 명확하고 세밀하게 제어할 수 있도록 해줍니다. Trim Refs는 이와 동일한 기능을 훨씬 간단한 형태로 제공하며, 기존에 사용 중인 참조에 마침표 몇 개만 추가하면 되므로 수식 작성 흐름을 방해하지 않습니다.