Excel의 INDIRECT 함수가 시트 통합 작업을 간소화하는 방법

여러 시트를 사용하는 경우, 요약 시트나 대시보드에 분석할 데이터를 통합하는 것은 종종 어려운 작업이 됩니다.많은 사용자가 데이터를 수동으로 복사하여 붙여넣는 시간 소모적인 방법을 사용합니다.하지만 더 효율적인 솔루션인 INDIRECT 함수를 사용하면 시트 간 데이터 참조 및 자동화를 간소화할 수 있습니다.

INDIRECT 함수를 사용하면 필수 데이터 포인트를 원활하게 참조하고 집계할 수 있습니다.단일 수식을 사용하므로 데이터 통합에 필요한 작업을 크게 줄일 수 있습니다. INDIRECT의 주요 장점은 복원력입니다.원본 시트의 이름이 변경되어도 참조는 그대로 유지되고 자동으로 업데이트됩니다.

수동 시트 통합의 문제점

공식의 취약성

North, South, East 라는 세 개의 시트가 있는 통합 문서가 있다고 가정해 보겠습니다.각 시트에는 다양한 제품의 판매 데이터가 포함되어 있습니다.판매 총액을 비교하는 요약 시트를 만들려면 North 시트 에 SUM 함수를 적용하는 것부터 시작해 보세요.

=SUM(North!D:D)

남쪽 및 동쪽 시트 의 경우, 해당 수식을 수동으로 입력해야 하는데, 시트가 많을 경우 번거로울 수 있습니다.이러한 방식은 단조로울 뿐만 아니라 오류가 발생하기 쉽고, 실수가 발생하면 #REF! 오류가 발생하는 경우가 많습니다.더욱이 시트 이름을 변경하면 요약 전체를 수동으로 업데이트해야 하므로 오류 발생 가능성이 커집니다.

INDIRECT 함수 이해하기

동적 셀 참조 설명

INDIRECT 함수는 텍스트 문자열을 셀 참조로 변환하는 강력한 도구입니다.예를 들어, A1 셀 에 값 5가 있고 B1 셀 에 텍스트 A1 이 있는 경우, 아래 수식은 값 5를 반환합니다.

=INDIRECT(B1)

이 경우 INDIRECT 함수는 B1 의 참조를 해석하여 A1 에 저장된 값을 반환합니다.이 기능은 GPS를 사용하여 주소를 기반으로 특정 위치를 검색하는 것과 유사합니다.간접적으로 보일 수 있지만, 동적 셀 참조를 쉽게 생성할 수 있도록 해줍니다.

이전 예를 계속하면, B1 의 값을 1 (텍스트 값) 로 바꾸면 다음 수식은 참조를 동적으로 생성합니다.

=INDIRECT("A"& B1)

여기서 함수는 문자열 ” A “와 B1 의 값을 결합하여 셀 참조 A1을 생성합니다.B1 의 값을 2 로 변경하면 함수는 A2를 참조합니다.

요약 보고서에 INDIRECT 사용

INDIRECT의 최적 사용

이를 판매 데이터 시나리오에 적용해 보겠습니다.시작하려면 시트 이름을 포함하는 도우미 열을 만듭니다.예를 들어 A2:A4 범위 ( A2 에 North 포함 )에 시트 이름을 입력합니다. SUM 함수와 INDIRECT 함수를 모두 사용하는 해당 SUM 수식을 다음과 같이 B2 에 배치할 수 있습니다.

=SUM(INDIRECT("'"& A2 & "'!D:D"))

이 수식은 North 시트 의 D 열을 구체적으로 가리키는 문자열 참조를 생성합니다.작은따옴표를 포함하면 공백이나 특수 문자가 있는 시트의 경우 특히 유용하여 안정성을 보장합니다.

INDIRECT 함수는 닫힌 통합 문서를 참조할 수 없다는 점에 유의해야 합니다.열린 통합 문서가 있어야 참조할 수 있기 때문입니다.닫힌 통합 문서를 참조할 경우 #REF! 오류가 발생합니다. INDIRECT 함수의 장점은 수식을 B3 및 B4 까지 쉽게 복사하여 South 및 East 시트 를 참조하도록 조정할 수 있다는 것입니다.이러한 접근 방식은 시간을 절약할 뿐만 아니라 정확도를 크게 향상시킵니다.

시트 이름이 변경되면 A 열만 업데이트하면 수식이 자동으로 새 이름을 참조하므로 번거로운 수동 수정 작업이 필요 없습니다.잠재적인 오류를 처리하기 위해 IFERROR 함수를 통합할 수 있습니다.

=IFERROR(SUM(INDIRECT("'"& A2 & "'!D:D")), "")

INDIRECT 사용 시 주의사항

휘발성 함수 이해

INDIRECT 함수는 휘발성 함수로 분류됩니다.이 속성은 워크시트 내에서 변경 사항이 발생할 때마다 다시 계산한다는 것을 의미하며, 특히 큰 통합 문서에서 성능 문제가 발생할 수 있습니다.따라서 INDIRECT 함수는 신중하게 사용하는 것이 좋습니다.간단한 요약 시트의 경우 성능에 미치는 영향이 미미할 수 있습니다.

Excel 기능을 사용하여 시트 통합 강화

요약 시트와 대시보드의 경우 INDIRECT 함수를 활용하면 데이터 통합 방식에 혁신을 가져올 수 있습니다.드롭다운 목록과 같은 Excel의 추가 기능을 INDIRECT와 함께 사용하면 여러 시트를 유연하게 탐색할 수 있습니다. INDIRECT의 이러한 혁신적인 활용은 시트 데이터 관리에 있어 더욱 효율적이고 역동적인 환경을 제공합니다.

출처 및 이미지