엑셀 신규 참조 방식 | 트리밍 참조, TRIMRANGE 완벽 정리
TRIMRANGE 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 새로운 참조 방식, TRIMRANGE 함수 완벽 정리 - 예제파일예제파일
TRIMRANGE & 트리밍 참조 살펴보기
엑셀 업무 효율을 크게 높여줄 엑셀의 게임 체인저가 등장했습니다! 기존 엑셀 참조 방식의 한계를 뛰어넘어, 데이터가 실제로 입력된 범위를 동적으로 참조하여 자동화 할 수 있는 '트리밍 참조(TRIMRANGE 함수)' 인데요.
피벗테이블와 TRIMRANGE 함수를 사용하면 다이나믹 차트도 손쉽게 만들 수 있습니다!😎 이 트리밍 참조를 사용하면 매일 누적되는 대량의 데이터를 효과적으로 관리하고 데이터 처리 시간을 획기적으로 단축할 수 있습니다.
- TRIMRANGE 함수 사용법 : 예제파일을 실행한 후, [트리밍참조] 시트로 이동합니다. 먼저 TRIMRANGE 함수로 선택한 범위에서 실제 데이터가 입력된 범위를 받아오는 방법을 알아보겠습니다.
=TRIMRANGE( 범위, [행정리], [열정리] )
// 범위에서 가장자리의 빈 행과 열을 제거하고 실제 데이터가 입력된 범위를 반환합니다.오빠두Tip : M365 버전에 새롭게 추가된 TRIMRANGE 함수의 자세한 설명은 아래 함수 게시글을 확인해주세요!😊
- 표 오른쪽에 비어있는 임의의 셀을 선택한 후, 아래와 같이 TRIMRANGE 함수를 입력하면 A1:D18 범위에서 실제로 데이터가 입력된 범위가 반환됩니다.
=TRIMRANGE(A1:D18)
TRIMRANGE 함수는 데이터가 입력된 범위를 동적으로 반환합니다. - TRIMRANGE 함수의 행정리/열정리 인수를 변경하여 앞 부분과 뒷 부분의 행/열 정리 방식을 지정할 수 있습니다. 예를 들어 다음과 같이 행/열의 선행(앞 부분)을 지우도록 인수를 입력하면 A1:D18 범위에서 앞 부분의 비어있는 범위가 정리됩니다.
=TRIMRANGE(A1:D18,1,1)
행/열 정리 인수로 선행/후행 범위를 정리할 수 있습니다. - 동일한 원리로 아래와 같이 후행(뒷 부분)을 지우도록 인수를 입력하면 A1:D18 범위에서 뒷 부분의 비어있는 범위가 정리됩니다.
=TRIMRANGE(A1:D18,2,2)
인수를 2로 입력하면 뒷 부분의 비어있는 범위가 정리됩니다. - 트리밍 참조 예제 : 이 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) 뒷 부분 빈 범위 제거 - 시트에서 비어있는 셀을 선택한 후, 다음과 같이 트리밍 참조를 입력하면 범위에서 앞 부분과 뒷 부분의 비어있는 부분을 제거하고 실제 데이터가 입력된 범위를 동적으로 반환할 수 있습니다.
=A2.:.D15
콜론기호 앞/뒤로 마침표를 붙이면 범위가 동적으로 참조됩니다. - 또한 트리밍 참조를 활용하면 다음과 같이 전체 열을 대상으로 실제 데이터가 입력된 범위만 동적으로 참조할 수 있습니다.
=A.:.D
전체 열을 대상으로 트리밍 참조를 활용해 실제 데이터가 입력된 범위를 편리하게 반환할 수 있습니다.
TRIMRANGE 실전 활용 예제
- 동적 검색하기 : 예제파일에서 TRIMRANGE 시트로 이동하면 주문서와 메뉴표가 작성되어 있습니다. 이번에는 주문서에 메뉴를 추가하면 가격이 동적으로 업데이트 되도록 TRIMRANGE 함수와 VLOOKUP 함수를 작성해보겠습니다.
입력한 제품의 가격이 동적으로 작성되는 주문서를 만듭니다. - D4셀을 선택한 후, 다음과 같이 VLOOKUP + TRIMRANGE 함수를 작성하면 제품의 가격이 동적으로 반환됩니다.
=VLOOKUP(TRIMRANGE(C4:C100),I:J,2,0)
TRIMRANGE+ VLOOKUP 함수로 공식을 작성합니다. - 주문서 밑으로 새 메뉴를 추가하면, 제품의 가격이 자동으로 업데이트됩니다.
제품을 추가하면 가격이 동적으로 업데이트 됩니다. - 자동 순번 만들기 : SEQUENCE 함수를 사용하면 작성한 제품의 개수 만큼 순번을 동적으로 반환할 수 있습니다. 예제파일에서 기존에 작성된 순번을 지운 후, B4셀에 다음과 같이 SEQUENCE 함수를 작성합니다.
=SEQUENCE(COUNTA(C4:C20))
SEQUENCE 함수로 자동 순번을 만들 수 있습니다. - 이제 메뉴를 추가하거나 삭제하면 주문서의 순번과 가격이 동적으로 업데이트됩니다.
제품을 추가/삭제하면 가격과 순번이 동적으로 작성됩니다. 오빠두Tip : 엑셀 모든 버전에서 적용 가능한 상황별 순번 입력 방법은 아래 1분 영상에서 자세히 정리했습니다!😊
- 자동 확장 목록상자 : TRIMRANGE 함수와 트리밍 참조를 활용하면 실제 데이터 범위를 참조하여 자동으로 확장/축소되는 목록 상자를 손쉽게 만들 수 있습니다. 주문서에 메뉴를 입력할 C4:C12 범위를 선택한 후, [데이터] 탭 - [데이터 유효성 검사]를 실행하고 제한 대상으로 '목록'을 선택한 후 다음과 같이 범위를 작성합니다.
트리밍 참조로 목록 상자를 추가합니다. - [확인] 버튼을 클릭해서 목록 상자를 적용하면, 메뉴를 추가하거나 삭제했을 때 실시간으로 반영되는 목록상자가 완성됩니다.
메뉴 범위를 동적으로 인식하는 목록상자가 완성됩니다.
일반 참조 vs. 트리밍 참조 처리 속도 비교
이번에 새롭게 추가된 TRIMRANGE 함수와 트리밍 참조를 사용하면, 전체 열을 참조하여 배열 수식을 작성했을 때 연산 속도가 현저히 느려지던 문제를 크게 개선할 수 있습니다.
- 예제파일에서 [다중조건] 시트로 이동합니다. 이번에는 오른쪽 표에 XLOOKUP 함수를 사용해서 각 이름과 부서를 동시에 만족할 경우 평가 점수를 구해보겠습니다.
이름과 부서를 모두 만족할 경우 평가점수를 구합니다. 오빠두Tip : 배열 연산을 활용한 다중 조건 검색 공식의 동작 원리는 아래 영상 강의에서 꼼꼼히 정리했습니다.👇
- H3셀에 아래 수식을 입력한 후, 수식을 자동채우기하면 전체 열을 대상으로 연산되어 처리가 지연되는 것을 확인할 수 있습니다.
=XLOOKUP(F3&G3,B:B&C:C,D:D)
전체 열을 참조하여 배열수식을 작성할 경우, 처리속도가 느려지는 문제가 있습니다. - 하지만 다음과 같이 트리밍 참조를 활용하여, 실제로 데이터가 입력된 범위만 참조하도록 수식을 작성하면 처리 속도가 크게 개선되어 실시간으로 계산되는 것을 확인할 수 있습니다.
=XLOOKUP(F3&G3,B.:.B&C.:.C,D.:.D)
트리밍 참조로 배열 수식을 작성하면 처리속도가 크게 개선됩니다. - 각 참조 방식별 실제 연산 처리속도가 궁금하실 경우, 매크로 편집기에 아래 코드를 추가한 후 '일반 참조'와 '트리밍 참조' 코드를 각각 실행해서 비교할 수 있습니다. Alt + F11 를 동시에 눌러 매크로 편집기를 실행한 후, [삽입] 탭 - [모듈]로 새 모듈을 추가하고 아래 코드를 붙여넣기 후 '재생' 버튼을 클릭해서 실행합니다.
미리 준비해드린 매크로로 일반 참조 vs 트리밍 참조의 연산 속도를 비교해보세요! 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 함수를 함께 활용하면 기존의 피벗테이블로는 제작할 수 없었던 고급 차트를 만들 수 있습니다.
- 예제파일에서 [다이나믹폭포] 차트로 이동한 후, 오른쪽 비어있는 셀에 다음과 같이 트리밍 참조로 피벗테이블의 실제 데이터 범위를 반환합니다.
피벗테이블에서 실제 데이터가 입력된 범위를 동적으로 반환합니다. - 반환된 범위를 선택한 후, [삽입] - [추천차트] - [모든 차트]에서 '폭포 차트'를 선택하고 [확인] 버튼을 클릭하면 폭포 차트가 완성됩니다.
삽입 - 추천 차트 - 모든 차트에서 폭포차트를 추가합니다. - 피벗테이블을 선택하고 [피벗테이블 분석] 탭 - [총 합계] 에서 '행 및 열의 총 합계 설정'으로 합계를 추가합니다.
피벗테이블 - 디자인 - 총 합계에서 총합계 필드를 추가합니다. - 폭포 차트에서 합계 막대를 선택한 후 우클릭 - [합계로 설정] 으로 합계 막대를 지정합니다.
차트에서 합계 막대를 우클릭 - 합계로 설정합니다. - 마지막으로 [피벗 테이블 분석] 탭 - [슬라이서 삽입]에서 원하는 필터를 추가하고 차트를 적절히 꾸며주면 다이나믹 폭포 차트가 완성됩니다.
피벗테이블에서 슬라이서를 추가한 후, 차트를 꾸며주면 폭포 차트가 완성됩니다.
- TRIMRANGE 함수 사용법 : 예제파일을 실행한 후, [트리밍참조] 시트로 이동합니다. 먼저 TRIMRANGE 함수로 선택한 범위에서 실제 데이터가 입력된 범위를 받아오는 방법을 알아보겠습니다.
