Microsoft Excel에서는 다른 워크 시트 또는 다른 Excel 파일의 셀을 참조하는 것이 일반적인 작업입니다. 처음에는 약간 까다 롭고 혼란스러워 보일 수 있지만 작동 방식을 이해하면 어렵지 않습니다.
이 기사에서는 동일한 Excel 파일에서 다른 시트를 참조하는 방법과 다른 Excel 파일을 참조하는 방법을 살펴 봅니다. 함수에서 셀 범위를 참조하는 방법, 정의 된 이름으로 더 간단하게하는 방법, 동적 참조에 VLOOKUP을 사용하는 방법 등도 다룹니다.
동일한 Excel 파일에서 다른 시트를 참조하는 방법
기본 셀 참조는 열 문자와 행 번호로 기록됩니다.
따라서 셀 참조 B3은 열 B와 행 3의 교차점에있는 셀을 나타냅니다.
다른 시트의 셀을 참조 할 때이 셀 참조 앞에 다른 시트 이름이 붙습니다. 예를 들어, 아래는 시트 이름 “January”의 B3 셀에 대한 참조입니다.
=January!B3
느낌표 (!)는 시트 이름과 셀 주소를 구분합니다.
시트 이름에 공백이 있으면 참조에서 작은 따옴표로 이름을 묶어야합니다.
='January Sales'!B3
이러한 참조를 작성하려면 셀에 직접 입력 할 수 있습니다. 그러나 Excel에서 참조를 작성하도록하는 것이 더 쉽고 안정적입니다.
셀에 등호 (=)를 입력하고 시트 탭을 클릭 한 다음 상호 참조 할 셀을 클릭하십시오.
이렇게하면 Excel이 수식 입력 줄에 참조를 작성합니다.
수식을 완료하려면 Enter를 누르십시오.
다른 Excel 파일을 참조하는 방법
동일한 방법을 사용하여 다른 통합 문서의 셀을 참조 할 수 있습니다. 수식 입력을 시작하기 전에 다른 Excel 파일이 열려 있는지 확인하십시오.
등호 (=)를 입력하고 다른 파일로 전환 한 다음 참조하려는 해당 파일의 셀을 클릭하십시오. 완료되면 Enter를 누르십시오.
완성 된 상호 참조에는 대괄호로 묶인 다른 통합 문서 이름과 시트 이름 및 셀 번호가 포함됩니다.
=(Chicago.xlsx)January!B3
파일 또는 시트 이름에 공백이 있으면 파일 참조 (대괄호 포함)를 작은 따옴표로 묶어야합니다.
='(New York.xlsx)January'!B3
이 예에서는 셀 주소 사이에 달러 기호 ($)를 볼 수 있습니다. 이것은 절대 셀 참조입니다 (절대 셀 참조에 대해 자세히 알아보십시오).
다른 Excel 파일에서 셀과 범위를 참조 할 때는 기본적으로 참조가 절대적으로 이루어집니다. 필요한 경우이를 상대 참조로 변경할 수 있습니다.
참조 된 통합 문서를 닫을 때 수식을 보면 해당 파일의 전체 경로가 포함됩니다.
다른 통합 문서에 대한 참조를 작성하는 것은 간단하지만 문제가 발생하기 쉽습니다. 폴더를 만들거나 이름을 바꾸고 파일을 이동하는 사용자는 이러한 참조를 손상시키고 오류를 일으킬 수 있습니다.
가능하면 하나의 통합 문서에 데이터를 유지하는 것이 더 안정적입니다.
함수에서 셀 범위를 상호 참조하는 방법
단일 셀을 참조하면 충분합니다. 그러나 다른 워크 시트 또는 통합 문서의 셀 범위를 참조하는 함수 (예 : SUM)를 작성하려고 할 수 있습니다.
평소와 같이 기능을 시작한 다음 시트와 셀 범위를 클릭하십시오. 이전 예제에서와 동일한 방식으로.
다음 예제에서 SUM 함수는 Sales라는 워크 시트에서 범위 B2 : B6의 값을 합산합니다.
=SUM(Sales!B2:B6)
간단한 상호 참조에 정의 된 이름을 사용하는 방법
Excel에서는 셀 또는 셀 범위에 이름을 지정할 수 있습니다. 이것은 셀이나 범위 주소보다 더 의미가 있습니다. 스프레드 시트에서 많은 참조를 사용하는 경우 해당 참조의 이름을 지정하면 수행 한 작업을 훨씬 쉽게 볼 수 있습니다.
또한이 이름은 해당 Excel 파일의 모든 워크 시트에 고유합니다.
예를 들어 셀 이름을 'ChicagoTotal'로 지정한 다음 상호 참조는 다음과 같습니다.
=ChicagoTotal
이것은 다음과 같은 표준 참조에 대한 더 의미있는 대안입니다.
=Sales!B2
정의 된 이름을 쉽게 만들 수 있습니다. 이름을 지정할 셀 또는 셀 범위를 선택하여 시작하십시오.
왼쪽 상단의 이름 상자를 클릭하고 할당 할 이름을 입력 한 다음 Enter 키를 누릅니다.
정의 된 이름을 만들 때는 공백을 사용할 수 없습니다. 따라서이 예에서 단어는 이름으로 결합되었으며 대문자로 구분되었습니다. 하이픈 (-) 또는 밑줄 (_)과 같은 문자로 단어를 구분할 수도 있습니다.
Excel에는 이름 관리자가있어 향후 이러한 이름을 쉽게 모니터링 할 수 있습니다. 수식> 이름 관리자를 클릭하십시오. 이름 관리자 창에서 통합 문서에 정의 된 모든 이름, 이름 및 현재 저장되어있는 값의 목록을 볼 수 있습니다.
그런 다음 상단의 버튼을 사용하여 이러한 정의 된 이름을 편집하고 삭제할 수 있습니다.
데이터를 테이블로 포맷하는 방법
광범위한 관련 데이터 목록으로 작업 할 때 Excel의 테이블 형식 기능을 사용하면 데이터를 참조하는 방법을 단순화 할 수 있습니다.
다음의 간단한 테이블을 가져 가십시오.
이것은 테이블로 형식화 될 수 있습니다.
목록에서 셀을 클릭하고 “홈”탭으로 전환하고 “표로 서식”버튼을 클릭 한 다음 스타일을 선택하십시오.
셀 범위가 올 바르고 테이블에 헤더가 있는지 확인하십시오.
그런 다음 “디자인”탭에서 테이블에 의미있는 이름을 지정할 수 있습니다.
그런 다음 시카고 판매액을 합산해야하는 경우 테이블의 이름 (모든 시트에서)과 대괄호 (())를 사용하여 테이블의 열 목록을 볼 수 있습니다.
목록에서 열을 두 번 클릭하여 선택하고 닫는 대괄호를 입력하십시오. 결과 수식은 다음과 같습니다.
=SUM(Sales(Chicago))
표가 표준 시트 참조보다 SUM 및 AVERAGE와 같은 집계 함수에 대한 참조 데이터를 만드는 방법을 알 수 있습니다.
이 표는 데모 목적으로 작습니다. 통합 문서에 테이블이 많고 시트가 많을수록 더 많은 혜택을 볼 수 있습니다.
동적 참조에 VLOOKUP 함수를 사용하는 방법
지금까지 실시 예에서 사용 된 참고 문헌은 모두 특정 세포 또는 세포 범위에 고정되어있다. 훌륭하고 종종 귀하의 요구에 충분합니다.
그러나 참조하는 셀에 새 행이 삽입 될 때 변경 될 가능성이 있거나 누군가가 목록을 정렬하면 어떻게됩니까?
이러한 시나리오에서는 원하는 값이 처음에 참조한 것과 동일한 셀에있을 것이라고 보장 할 수 없습니다.
이러한 시나리오의 대안은 Excel에서 조회 기능을 사용하여 목록에서 값을 검색하는 것입니다. 따라서 시트 변경에 대해 내구성이 향상됩니다.
다음 예에서는 VLOOKUP 함수를 사용하여 직원 ID로 다른 시트에서 직원을 찾은 다음 시작 날짜를 반환합니다.
아래는 직원 목록의 예입니다.
VLOOKUP 함수는 테이블의 첫 번째 열을 찾은 다음 지정된 열의 정보를 오른쪽으로 반환합니다.
다음 VLOOKUP 함수는 위에 표시된 목록에서 A2 셀에 입력 된 직원 ID를 검색하고 열 4 (테이블의 네 번째 열)에서 조인 된 날짜를 반환합니다.
=VLOOKUP(A2,Employees!A:E,4,FALSE)
아래는이 수식이 목록을 검색하고 올바른 정보를 반환하는 방법을 보여줍니다.
이전 예제보다이 VLOOKUP의 장점은 목록이 순서대로 변경 되더라도 직원을 찾을 수 있다는 것입니다.
노트 : VLOOKUP은 매우 유용한 공식이며이 기사에서는 그 가치의 표면 만 긁었습니다. 주제에 대한 기사에서 VLOOKUP 사용 방법에 대해 자세히 알아볼 수 있습니다.
이 기사에서는 Excel 스프레드 시트와 통합 문서를 상호 참조하는 여러 가지 방법을 살펴 보았습니다. 작업에 적합한 접근 방식을 선택하고 편안하게 작업하십시오.
