엑셀 TRIMRANGE와 트리밍 참조로 동적 범위를 만드는 방법은?

TL;DR

TRIMRANGE 함수나 콜론 앞뒤에 마침표를 붙이는 트리밍 참조를 사용하면, 넓게 지정한 범위에서 가장자리의 빈 영역을 제외하고 실제 데이터 범위만 동적으로 참조할 수 있습니다. 제품 목록 자동 확장, 데이터 유효성 검사, 다중 조건 조회, 피벗 테이블 기반 동적 차트에 적용할 수 있으며, 전체 열 연산의 계산 시간도 크게 줄일 수 있습니다.

Transcript

어 잠시만 기다려 볼게요 약한 10초 정도 시간이 걸리는 거를 볼 수 있는데요 콜론의 앞뒤로 아침을 추가해서이 공식을 붙여 놓게 하면 순식간에 계산되는 것을 볼 수 있습니다 안녕하세요 오빠입니다 저 오늘은 많은 분께서 기다리셨던 바로 그 기능이 업데이트가 되어서 영상을 준비했습니다 바로 트리밍 참조인 어 실무에서 아마 이런 경험 많으실 겁니다 함수를 작성할 때이 범위를 선택해야 하는데 어 데이터가 많을 경우에는 이거를 직접 드래그해서 선택하기 어렵거든요 그래서 보통 출마에서 이렇게 전체 열을 선택해서 함수를 작성하곤 합니다 하지만 이렇게 작성하면이 위아래로 불필요한 빈셀이 포함되어서 속도가 느려지는 문제가 있고요 그리고... Read More

Key Insights

• TRIMRANGE는 지정한 범위나 배열의 가장자리에 놓인 빈 행과 열을 제거하고, 실제 값이 입력된 영역을 동적으로 반환한다.
• 조회 범위를 TRIMRANGE로 감싸면 제품을 추가하거나 삭제할 때 참조 범위가 함께 바뀌어 가격 결과를 다시 자동 채우기 할 필요가 줄어든다.
• TRIMRANGE가 반환한 동적 배열의 분산 영역에 다른 값이 있으면 분산 오류가 발생하므로, 결과가 펼쳐질 셀들을 비워 두어야 한다.
• 전체 열을 TRIMRANGE로 참조한 뒤 DROP으로 첫 번째 행을 제거하면, 머리글을 제외한 실제 제품 목록만 동적으로 반환할 수 있다.
• 트리밍 참조는 범위의 콜론 앞뒤에 마침표를 붙이는 문법으로, 시작과 끝에 있는 빈 영역을 선택적으로 제외할 수 있다.
• 데이터 유효성 검사의 목록 원본에 트리밍 참조를 지정하면 제품의 추가와 삭제가 선택 목록에 실시간으로 반영된다.
• 영상의 측정 예제에서는 다중 조건 조회의 일반 전체 열 참조가 약 4.3초, 트리밍 참조가 약 0.1초에 입력과 계산을 마쳤다.
• 피벗 테이블 결과를 TRIMRANGE로 일반 동적 범위에 출력하면, 피벗 차트에서 직접 지원되지 않는 폭포 차트도 구성할 수 있다.
• 🎬 이 채널의 다른 영상:
  • 직장인은 AI 시대에 도메인·데이터 통합·책임감을 어떻게 준비해야 할까?
  • AI 에이전트 시대의 일자리 변화에 어떻게 대비해야 하는가?
  • 엑셀 COPILOT 함수로 동적 데이터 분석을 자동화하는 방법은?
• 📺 이 채널: 오빠두엑셀 l 엑셀 강의 대표채널

Questions & Answers

Q: 엑셀 TRIMRANGE 함수란 무엇인가?

TRIMRANGE는 지정한 범위나 배열의 가장자리에 있는 빈 행과 열을 제거하고 실제 데이터가 포함된 영역을 반환하는 함수입니다. 데이터를 넉넉한 범위나 전체 열로 지정해도 값이 추가되거나 삭제될 때 반환 범위가 함께 바뀌므로, 조회식과 자동화 서식에서 동적 범위를 구성하는 데 사용할 수 있습니다.

Q: TRIMRANGE로 제품 가격 조회 범위를 자동 확장하려면 어떻게 해야 하나?

제품명 범위와 가격 범위를 넉넉하게 선택한 뒤 각각을 TRIMRANGE로 감싸고 VLOOKUP의 조회 범위로 사용하면 됩니다. 메뉴 아래에 새 제품과 가격을 추가하면 실제 데이터 범위가 자동으로 늘어나 주문서의 가격도 갱신됩니다. 반환 결과는 배열이므로 결과가 펼쳐질 중간 셀에는 다른 값을 두지 않아야 합니다.

Q: 트리밍 참조는 어떻게 작성하고 무엇을 제거하나?

트리밍 참조는 일반 범위를 작성한 뒤 콜론 앞이나 뒤에 마침표를 추가해 만듭니다. 콜론 앞쪽의 마침표는 범위 앞부분의 빈 영역을, 뒤쪽의 마침표는 뒷부분의 빈 영역을 제거하며, 양쪽에 모두 붙이면 앞뒤 빈 영역을 제외하고 실제 데이터가 들어 있는 동적 범위만 참조할 수 있습니다.

Q: TRIMRANGE로 전체 열을 참조할 때 머리글은 어떻게 제외하나?

전체 열을 TRIMRANGE에 전달한 다음 그 결과를 DROP 함수로 감싸 첫 번째 행을 제거하면 됩니다. TRIMRANGE만 사용하면 머리글부터 마지막 데이터까지 반환되지만, DROP으로 첫 행을 제외하면 머리글 없는 제품 목록을 얻을 수 있습니다. 열 아래쪽에 값이 추가되어도 실제 데이터 범위에 자동으로 포함됩니다.

Q: 트리밍 참조로 자동 확장 목록 상자를 만드는 방법은?

목록 상자를 적용할 셀에서 데이터 유효성 검사를 열고 제한 대상을 목록으로 선택한 뒤, 원본에 메뉴 범위를 넉넉하게 지정하고 콜론 앞뒤에 마침표를 붙이면 됩니다. 이후 원본 범위에 제품을 추가하면 선택 목록에도 나타나고, 원본 제품을 삭제하면 해당 항목도 목록에서 자동으로 제거됩니다.

Q: 트리밍 참조가 전체 열 계산 속도를 높이는 이유는 무엇인가?

트리밍 참조는 전체 열을 지정하더라도 가장자리의 빈 셀을 제외하고 실제 데이터가 있는 범위만 계산 대상으로 사용하기 때문입니다. 특히 이름과 부서를 동시에 확인하는 다중 조건 조회처럼 계산 범위가 반복되는 수식에서 차이가 커집니다. 영상의 예제 측정값은 일반 참조 약 4.3초, 트리밍 참조 약 0.1초였습니다.

Q: TRIMRANGE와 동적 배열을 사용할 때 분산 오류는 왜 발생하나?

TRIMRANGE를 사용한 조회식이 여러 결과를 동적 배열로 반환할 때, 결과가 펼쳐져야 할 셀 중 하나라도 다른 값으로 차 있으면 분산 오류가 발생합니다. 배열이 차지할 예상 영역의 기존 값을 지우면 결과가 정상적으로 펼쳐집니다. 따라서 주문서 서식을 설계할 때 반환 영역을 비워 두어야 합니다.

Q: TRIMRANGE로 피벗 테이블 기반 폭포 차트를 만드는 방법은?

피벗 테이블 영역을 넉넉하게 선택해 TRIMRANGE로 일반 동적 범위에 출력한 뒤, 그 출력 범위를 선택하여 폭포 차트를 삽입하면 됩니다. 피벗 테이블에는 분류 슬라이서를 연결하고 차트는 TRIMRANGE 결과를 참조하게 구성합니다. 그러면 슬라이서 선택에 따라 피벗 결과와 폭포 차트가 함께 갱신됩니다.

Q: 동적 주문서의 순번을 제품 수에 맞춰 자동 생성하려면 어떻게 하나?

SEQUENCE 함수의 생성 개수를 COUNTA로 계산한 제품명 수에 연결하면 됩니다. 제품 범위에 값이 추가되면 COUNTA 결과가 늘어나 순번이 더 생성되고, 제품을 지우면 순번 수도 함께 줄어듭니다. TRIMRANGE로 만든 동적 제품 및 가격 범위와 결합하면 주문서의 목록, 가격, 순번을 함께 갱신할 수 있습니다.

Summary & Key Takeaways

• TRIMRANGE는 넉넉하게 선택한 범위나 배열의 가장자리에 있는 빈 행과 열을 정리해 실제 데이터가 입력된 범위를 반환한다. 이를 VLOOKUP 같은 조회 함수와 결합하면 메뉴가 추가되거나 삭제될 때 결과 배열과 주문서 가격이 자동으로 갱신된다.

• 트리밍 참조는 일반 범위의 콜론 앞이나 뒤에 마침표를 붙여 시작 또는 끝의 빈 영역을 제거하는 방식이다. 콜론 양쪽에 마침표를 붙이면 앞뒤 빈 영역을 모두 제외할 수 있어, 전체 열을 선택하더라도 실제 데이터 영역만 간결하게 참조할 수 있다.

• 동적 범위는 자동 확장 목록 상자와 대량 데이터 계산, 피벗 테이블 연동 차트에 활용할 수 있다. 예제에서는 일반 전체 열 참조 계산이 약 4.3초 걸린 반면 트리밍 참조는 약 0.1초였으며, 피벗 결과를 일반 범위로 반환해 폭포 차트도 만들었다.