마이크로소프트 엑셀에서 다른 워크시트 또는 다른 엑셀 파일의 특정 셀을 참조하는 것은 아주 일반적인 작업입니다. 처음에는 복잡해 보일 수 있지만, 그 작동 원리를 이해하고 나면 생각보다 어렵지 않다는 것을 알게 될 것입니다.
본 문서에서는 동일한 엑셀 파일 내에서 다른 시트를 참조하는 방법과 더 나아가 다른 엑셀 파일을 참조하는 방법을 자세히 알아보겠습니다. 또한, 함수 내에서 셀 범위를 참조하는 방법, 정의된 이름을 활용하여 더 간편하게 참조하는 방법, 그리고 동적 참조를 구현하기 위해 VLOOKUP 함수를 사용하는 방법까지 폭넓게 다룰 것입니다.
동일한 엑셀 파일 내 다른 시트 참조 방법
기본적인 셀 참조는 열을 나타내는 문자와 행을 나타내는 숫자로 구성됩니다. 예를 들어, 셀 참조 ‘B3’은 B열과 3행이 교차하는 지점의 셀을 가리킵니다.
다른 시트의 셀을 참조할 때는 해당 시트의 이름을 셀 참조 앞에 추가해야 합니다. 예를 들어, ‘January’라는 시트의 B3 셀을 참조하고 싶다면 아래와 같이 작성합니다.
=January!B3
여기서 느낌표(!)는 시트 이름과 셀 주소를 구분하는 역할을 합니다.
만약 시트 이름에 공백이 포함되어 있다면, 참조 시 해당 이름을 작은따옴표로 묶어주어야 합니다. 예를 들면 다음과 같습니다.
='January Sales'!B3
이러한 참조는 셀에 직접 입력할 수도 있지만, 엑셀이 자동으로 참조를 생성하도록 하는 것이 더 편리하고 오류 발생 가능성을 줄이는 방법입니다.
참조를 생성하려면, 먼저 셀에 등호(=)를 입력하고, 참조하고자 하는 시트 탭을 클릭한 다음, 해당 시트 내에서 참조할 셀을 선택하면 됩니다. 엑셀이 수식 입력줄에 자동으로 참조를 입력해줍니다.
마지막으로 Enter 키를 누르면 수식이 완성됩니다.
다른 엑셀 파일 참조 방법
다른 통합 문서에 있는 셀을 참조하는 방법도 유사합니다. 수식을 입력하기 전에 참조하고자 하는 다른 엑셀 파일이 열려 있는지 확인해야 합니다.
먼저 등호(=)를 입력한 다음, 다른 파일로 전환하여 해당 파일에서 참조하려는 셀을 클릭합니다. 원하는 셀을 선택했다면 Enter 키를 눌러 수식을 완성합니다.
생성된 참조는 대괄호로 묶인 다른 통합 문서의 이름, 시트 이름, 그리고 셀 주소를 포함합니다. 예를 들면 다음과 같습니다.
=[Chicago.xlsx]January!B3
만약 파일 이름이나 시트 이름에 공백이 포함되어 있다면, 파일 참조(대괄호 포함) 전체를 작은따옴표로 묶어야 합니다. 예를 들어, 아래와 같이 표현할 수 있습니다.
='[New York.xlsx]January'!B3
위 예시에서 셀 주소 사이에 달러 기호($)가 보이는 것을 알 수 있는데, 이는 절대 셀 참조를 의미합니다. (절대 셀 참조에 대한 더 자세한 정보는 관련 문서를 참고하십시오.)
다른 엑셀 파일에 있는 셀이나 범위를 참조할 때, 참조는 기본적으로 절대 참조로 설정됩니다. 필요에 따라 이를 상대 참조로 변경할 수도 있습니다.
참조된 통합 문서가 닫힌 상태에서 수식을 확인하면 해당 파일의 전체 경로가 포함된 것을 확인할 수 있습니다.
다른 통합 문서에 대한 참조는 간단하게 설정할 수 있지만, 사용자가 폴더를 변경하거나 파일 이름을 바꾸거나 위치를 이동하는 경우, 참조가 손상되어 오류가 발생할 가능성이 있습니다. 따라서 가능하면 데이터를 하나의 통합 문서에 보관하는 것이 보다 안정적인 방법입니다.
함수에서 셀 범위 상호 참조하기
단일 셀을 참조하는 것도 유용하지만, 다른 워크시트나 통합 문서에 있는 셀 범위에 대한 참조를 활용하여 함수(예: SUM)를 실행해야 할 때도 있습니다.
이 경우, 함수를 시작한 후, 앞서 설명한 방식과 동일하게 시트와 셀 범위를 클릭하여 참조를 생성합니다.
아래 예시에서는 ‘Sales’라는 워크시트에 있는 B2:B6 범위의 값을 SUM 함수를 사용하여 합산합니다.
=SUM(Sales!B2:B6)
정의된 이름을 사용하여 단순 참조 간소화하기
엑셀에서는 특정 셀 또는 셀 범위에 고유한 이름을 지정할 수 있습니다. 이렇게 하면 셀 주소 대신 지정된 이름을 사용하여 참조를 더 명확하게 만들 수 있습니다. 특히 스프레드시트에서 참조를 많이 사용할 경우, 참조에 이름을 부여하면 내용을 훨씬 쉽게 이해하고 관리할 수 있습니다.
정의된 이름은 해당 엑셀 파일의 모든 워크시트에서 고유하게 적용됩니다.
예를 들어, 셀에 ‘ChicagoTotal’이라는 이름을 부여하면, 해당 셀을 참조할 때 다음과 같이 간단하게 나타낼 수 있습니다.
=ChicagoTotal
이것은 아래와 같은 표준 참조보다 훨씬 이해하기 쉽습니다.
=Sales!B2
정의된 이름을 만드는 방법은 간단합니다. 이름을 부여할 셀이나 셀 범위를 선택한 후,
좌측 상단에 있는 이름 상자를 클릭하고, 원하는 이름을 입력한 다음, Enter 키를 누르면 됩니다.
정의된 이름을 생성할 때 공백을 사용할 수 없으므로, 위 예시처럼 여러 단어를 결합하여 작성하거나, 단어 사이에 하이픈(-) 또는 밑줄(_)을 사용하여 구분할 수 있습니다.
엑셀에는 정의된 이름을 손쉽게 관리할 수 있는 이름 관리자 기능도 있습니다. 수식 메뉴에서 이름 관리자를 클릭하면 통합 문서에 정의된 모든 이름의 목록, 각 이름이 참조하는 위치 및 현재 저장된 값을 한눈에 확인할 수 있습니다.
이름 관리자 창에서 상단의 버튼을 사용하여 정의된 이름을 편집하거나 삭제할 수도 있습니다.
데이터를 테이블 형식으로 변환하기
관련된 데이터 목록을 다룰 때 엑셀의 테이블 형식 기능을 사용하면 데이터를 참조하는 방식을 효율적으로 관리할 수 있습니다.
다음과 같은 간단한 데이터 목록을 예시로 들어보겠습니다.
이러한 목록은 테이블 형식으로 변환하여 관리할 수 있습니다.
목록 내 셀을 클릭한 후, 홈 탭으로 이동하여 ‘표로 서식 지정’ 버튼을 누르고 원하는 스타일을 선택합니다.
테이블의 셀 범위가 정확하고 머리글을 포함하고 있는지 확인합니다.
그런 다음, 디자인 탭에서 테이블에 의미 있는 이름을 부여할 수 있습니다.
이제 시카고의 매출 합계를 구해야 할 경우, 테이블 이름과 대괄호([)를 사용하여 테이블의 열 목록을 볼 수 있습니다.
목록에서 해당 열을 더블 클릭하고 닫는 대괄호를 입력합니다. 결과적으로 다음과 유사한 수식이 생성됩니다.
=SUM(Sales[Chicago])
이 예시를 통해 테이블이 표준 시트 참조보다 SUM, AVERAGE와 같은 집계 함수에 대한 데이터 참조를 얼마나 간편하게 생성할 수 있는지 확인할 수 있습니다.
여기서는 작은 테이블을 예시로 사용했지만, 테이블 크기가 커지고 통합 문서 내 시트가 많을수록 테이블 형식 기능이 제공하는 이점은 더욱 커집니다.
VLOOKUP 함수를 사용하여 동적 참조하기
지금까지 살펴본 예시에서는 특정 셀이나 셀 범위에 대한 고정된 참조를 사용했습니다. 이러한 방법은 유용하고, 대부분의 상황에서 충분히 활용할 수 있습니다.
그러나 참조하는 셀의 데이터가 변경되거나 새로운 행이 추가되거나 일부 행이 삽입될 때, 참조가 변경될 필요가 있는 경우에는 어떻게 해야 할까요?