이제는 꼭 알아야 할, 엑셀의 새로운 참조 방식 | 트리밍 참조 | TRIMRANGE 함수 사용법

TRIMRANGE 함수와 트리밍 참조 살펴보기

엑셀 업무 효율을 한 단계 끌어올릴 새로운 참조 방식이 등장했습니다. 데이터가 실제로 입력된 범위만 동적으로 추적해서 자동화에 활용할 수 있는 '트리밍 참조(TRIMRANGE 함수)' 인데요. 기존 참조 방식의 한계를 보완하여, 매일 누적되는 대량의 데이터를 안정적으로 관리하고 처리 시간을 크게 단축할 수 있습니다.

1. TRIMRANGE 함수 사용법 : 예제파일을 실행한 후 [트리밍참조] 시트로 이동합니다. 먼저 TRIMRANGE 함수로 선택한 범위 안에서 실제 데이터가 입력된 범위만 반환하는 방법을 살펴봅니다.
   =TRIMRANGE( 범위, [행정리], [열정리] )
   // 범위에서 가장자리의 빈 행과 열을 제거하고 실제 데이터가 입력된 범위를 반환합니다.
   오빠두Tip : M365 버전에 새롭게 추가된 TRIMRANGE 함수의 자세한 설명은 아래 함수 게시글에서 확인하실 수 있습니다.
   
2. 표 오른쪽의 빈 셀을 선택한 후, 다음과 같이 TRIMRANGE 함수를 입력하면 A1:D18 범위 안에서 실제 데이터가 입력된 범위가 반환됩니다.
   =TRIMRANGE(A1:D18)

   

3. TRIMRANGE 함수의 행정리/열정리 인수를 조정하면 범위의 앞 부분과 뒷 부분을 어떻게 정리할지 지정할 수 있습니다. 예를 들어 다음과 같이 행과 열의 선행을 제거하도록 인수를 입력하면 A1:D18 범위에서 앞 부분의 빈 영역이 정리됩니다.
   =TRIMRANGE(A1:D18,1,1)

   

4. 같은 원리로 후행을 제거하도록 인수를 입력하면 A1:D18 범위에서 뒷 부분의 빈 영역이 정리됩니다.
   =TRIMRANGE(A1:D18,2,2)

   

5. 트리밍 참조 예제 : TRIMRANGE 함수와 함께 동적 범위를 더욱 간결하게 다루는 '트리밍 참조(Trim Reference)'도 추가되었습니다. 트리밍 참조는 일반 참조 범위에서 콜론(:) 기호의 앞뒤로 마침표(.)를 붙여서 동적 범위를 표현합니다.
   

   구분	트리밍 참조	TRIMRANGE 함수	설명
   일반 참조	=A1 : A100	=TRIMRANGE(A1:A100,0,0)	모든 범위 반환
   동적 참조 (앞/뒷 부분 제거)	=A1 . : . A100	=TRIMRANGE(A1:A100)	실제 데이터 범위 반환
   선행 제거	=A1 . : A100	=TRIMRANGE(A1:A100,1,1)	앞 부분 빈 범위 제거
   후행 제거	=A1 : . A100	=TRIMRANGE(A1:A100,2,2)	뒷 부분 빈 범위 제거

6. 시트의 빈 셀을 선택한 후, 다음과 같이 트리밍 참조를 입력하면 범위에서 앞 부분과 뒷 부분의 빈 영역을 제거하고 실제 데이터가 입력된 범위를 동적으로 반환할 수 있습니다.
   =A2.:.D15

   

7. 또한 트리밍 참조를 활용하면 전체 열을 대상으로 실제 데이터가 입력된 범위만 동적으로 참조할 수 있습니다.
   =A.:.D

   

TRIMRANGE 실전 활용 예제

1. 동적 검색하기 : 예제파일에서 TRIMRANGE 시트로 이동하면 주문서와 메뉴표가 작성되어 있습니다. 이번에는 주문서에 메뉴를 추가했을 때 가격이 동적으로 업데이트되도록 TRIMRANGE 함수와 VLOOKUP 함수를 조합해보겠습니다.

   

2. D4셀을 선택한 후, 다음과 같이 VLOOKUP과 TRIMRANGE 함수를 함께 작성하면 제품의 가격이 동적으로 반환됩니다.
   =VLOOKUP(TRIMRANGE(C4:C100),I:J,2,0)

   

3. 주문서 아래쪽에 새 메뉴를 추가하면 제품의 가격이 자동으로 업데이트됩니다.

   

4. 자동 순번 만들기 : SEQUENCE 함수를 사용하면 입력된 제품 개수만큼 순번을 동적으로 반환할 수 있습니다. 예제파일에서 기존에 작성된 순번을 지운 후, B4셀에 다음과 같이 SEQUENCE 함수를 작성합니다.
   =SEQUENCE(COUNTA(C4:C20))

   

5. 이제 메뉴를 추가하거나 삭제하면 주문서의 순번과 가격이 동적으로 함께 업데이트됩니다.

   

   오빠두Tip : 엑셀 모든 버전에서 적용 가능한 상황별 순번 입력 방법은 아래 1분 영상에 자세히 정리되어 있습니다.
   
6. 자동 확장 목록상자 : TRIMRANGE 함수와 트리밍 참조를 활용하면 실제 데이터 범위를 참조해 자동으로 확장·축소되는 목록 상자를 손쉽게 만들 수 있습니다. 주문서에 메뉴를 입력할 C4:C12 범위를 선택한 후 [데이터] 탭 - [데이터 유효성 검사]를 실행하고, 제한 대상으로 '목록'을 선택한 다음 아래와 같이 범위를 작성합니다.

   

7. [확인] 버튼을 클릭해 목록 상자를 적용하면, 메뉴를 추가하거나 삭제할 때 실시간으로 반영되는 목록상자가 완성됩니다.

   

일반 참조 vs. 트리밍 참조 처리 속도 비교

새롭게 추가된 TRIMRANGE 함수와 트리밍 참조를 사용하면, 전체 열을 참조해 배열 수식을 작성할 때 연산 속도가 현저히 느려지던 문제를 크게 개선할 수 있습니다.

1. 예제파일에서 [다중조건] 시트로 이동합니다. 이번에는 오른쪽 표에 XLOOKUP 함수를 사용해 이름과 부서를 동시에 만족하는 행의 평가 점수를 구해보겠습니다.

   

   오빠두Tip : 배열 연산을 활용한 다중 조건 검색 공식의 동작 원리는 아래 영상 강의에 꼼꼼히 정리되어 있습니다.
   
2. H3셀에 아래 수식을 입력한 후 자동채우기로 채우면 전체 열을 대상으로 연산되어 처리가 지연되는 것을 확인할 수 있습니다.
   =XLOOKUP(F3&G3,B:B&C:C,D:D)

   

3. 반면 다음과 같이 트리밍 참조로 실제 데이터가 입력된 범위만 참조하도록 수식을 변경하면 처리 속도가 크게 개선되어 실시간으로 계산되는 것을 확인할 수 있습니다.
   =XLOOKUP(F3&G3,B.:.B&C.:.C,D.:.D)

   

4. 각 참조 방식의 실제 처리 속도가 궁금하다면, 매크로 편집기에 아래 코드를 추가한 후 '일반 참조'와 '트리밍 참조' 코드를 각각 실행해 비교할 수 있습니다. Alt + F11 을 동시에 눌러 매크로 편집기를 실행한 후 [삽입] 탭 - [모듈]로 새 모듈을 추가하고, 아래 코드를 붙여넣은 다음 '재생' 버튼을 클릭해 실행합니다.

   

   Sub 일반참조()
       Sheet3.Range("H3:H407").ClearContents
       tStart = Timer
       Application.EnableEvents = False
       Sheet3.Range("H3:H407").Formula2R1C1 = "=XLOOKUP(RC[-2]&RC[-1],C[-6]&C[-5],C[-4])"
       Application.EnableEvents = True
       tEnd = Timer
       MsgBox "처리 시간: " & Format(tEnd - tStart, "0.000") & "초"
   End Sub
    
   Sub 트리밍참조()
       Sheet3.Range("H3:H407").ClearContents
       tStart = Timer
       Application.EnableEvents = False
       Sheet3.Range("H3:H407").Formula2 = "=XLOOKUP(RC[-2]&RC[-1],C[-6].:.C[-6]&C[-5].:.C[-5],C[-4].:.C[-4])"
       Application.EnableEvents = True
       tEnd = Timer
       MsgBox "처리 시간: " & Format(tEnd - tStart, "0.000") & "초"
   End Sub

피벗테이블 + TRIMRANGE 동적 차트 만들기

피벗테이블과 TRIMRANGE 함수를 함께 활용하면 기존의 피벗테이블만으로는 제작하기 까다롭던 고급 차트도 손쉽게 만들 수 있습니다.

1. 예제파일에서 [다이나믹폭포] 시트로 이동한 후, 오른쪽 빈 셀에 다음과 같이 트리밍 참조로 피벗테이블의 실제 데이터 범위를 반환합니다.

   

2. 반환된 범위를 선택한 후 [삽입] - [추천차트] - [모든 차트]에서 '폭포 차트'를 선택하고 [확인] 버튼을 클릭하면 폭포 차트가 만들어집니다.

   

3. 피벗테이블을 선택하고 [피벗테이블 분석] 탭 - [총 합계]에서 '행 및 열의 총 합계 설정'으로 합계를 추가합니다.

   

4. 폭포 차트에서 합계 막대를 선택한 후 우클릭 - [합계로 설정]으로 합계 막대를 지정합니다.

   

5. 마지막으로 [피벗 테이블 분석] 탭에서 [슬라이서 삽입]으로 원하는 필터를 추가하고 차트 디자인을 정리하면 다이나믹 폭포 차트가 완성됩니다.