Home > 강좌 > 엑셀수식강좌 > 05. 엑셀 이름정의와 표기능 > 보다 직관적인 엑셀 표 구조적 참조

보다 직관적인 엑셀 표 구조적 참조

  • by

이번 글에서는 엑셀 표 기능의 구조적 참조에 대해 알아보겠습니다.

※ 엑셀 표의 기본적인 기능은 아래 링크를 참조하세요.
[ 엑셀 표 기능 사용하기 - 데이터 관리를 더욱 편리하게!]

엑셀의 표는 다음과 같은 구조로 되어 있습니다.  표의 이름을 먼저 정의하고 표 하위의 머리글, 데이터, 요약행을 참조하는 방식을 구조적 참조라고 합니다. 구조적 참조를 사용하면 기존의 셀주소 참조 방식보다 훨씬 직관적이고 사용하기 편리합니다.

엑셀 표의 구조

위의 표에서 성유리 과장의 월급여는 [G2]셀에 입력되어 있는데 월급여를 참조할 때 =[G2]와 같이 셀 주소를 입력하는 것보다 =인사[@월급여]처럼 구조적 참조가 직관적입니다. 처음에는 낯설어서 더 어려워 보일 수도 있으나 조금만 익히면 수식을 바로 이해할 수 있고 참조의 위치가 바뀌어도 수식을 변경하지 않아도 되는 등 장점이 많습니다.

실습용 엑셀파일 다운로드 : 엑셀표구조적참조.xlsx

 

1. 표 이름 정의하기

표를 만들면 아래와 같이 표 이름이 자동으로 만들어집니다. 보통 '표1', '표2'... 의 형식으로 만들어집니다.

엑셀 표 이름 정의하기

 

'표1'의 형식의 이름을 그대로 사용해도 되지만 의미 있는 이름으로 바꾸면 수식을 참조할 때 알아보기 쉽습니다. 예제의 표는 인사정보를 담고 있으므로 '표1'이 입력되어 있는 곳을 클릭하여 표 이름을 '인사'로 바꿉니다.

엑셀 표 이름 정의하기

 

표의 이름은 엑셀의 이름으로 정의됩니다. [Ctrl+F3]를 눌러 [이름 관리자]에서 확인해 보면 '인사'라는 이름이 만들어져 있는 것을 확인할 수 있습니다.

엑셀 표 이름 정의하기

 

2. 표의 행과 열의 데이터 구조적 참조하기

표가 만들어졌으면 구조적 참조라는 독특한 방식으로 데이터를 참조할 수 있습니다.

아래의 표는 이름이 '인사'입니다. 이제부터 '인사'라는 이름으로 표의 여러 요소를 참조할 수 있습니다.

엑셀 표 구조적 참조 - 행과 열 참조

1) 단일 행의 데이터 참조

표이름[@머리글]로 참조합니다.

표 안에서 참조할 때는 [@머리글]만 입력하면 됩니다.

월급여에 12를 곱해서 연봉을 계산하는 수식은 다음과 같이 입력할 수 있습니다.
=[@월급여]*12

다음과 같이 셀 참조로 입력할 수도 있습니다만 구조적 참조의 이점을 누릴 수 없습니다.
=G2*12

엑셀 표 구조적 참조 - 행과 열 참조

 

표 밖에서 참조할 때는 표이름[@머리글]형식으로 입력해야 합니다.

=인사[@월급여]

셀 참조와는 달리 행이 달라도 참조는 동일하며 항상 같은 행의 데이터만 참조할 수 있습니다. 만약 다른 행의 데이터를 참조해야 한다면 셀 참조로 입력해야 합니다.

엑셀 표 구조적 참조 - 행과 열 참조

 

[Tip] 구조적 참조 입력 시 '= 표이름[' 까지 입력하면 아래와 같이 목록이 나타납니다. 방향키로 커서를 이동하여 필요한 항목을 선택하여 자동으로 입력할 수 있습니다.

엑셀 표 구조적 참조 - 항목 자동 선택

 

2) 열 단위 데이터 참조

표이름[머리글]로 참조합니다.

행 단위 참조와는 달리 머리글 앞에 @가 붙지 않습니다.

엑셀 표 구조적 참조 - 행과 열 참조

=SUM(인사[월급여]) => 월급여 열을 참조하므로 월급여 전체 합계를 구해 줍니다.
=COUNTA(인사[이름]) => 이름 열의 비어 있지 않은 행의 개수(직원 수)를 구해줍니다.

[기억하세요!]
만약 새로운 직원이 입사하여 위의 표에 행을 하나 더 추가하면 표가 자동으로 확장되고 표가 참조하는 범위도 자동으로 늘어납니다. 따라서 위의 수식은 수정하지 않아도 여전히 전체 합계를 구해 줍니다. 이것이 구조적 참조의 장점 중의 하나입니다.
구조적 참조 대신 셀참조를 사용하여 수식을 작성했다면 수식에서 참조범위를 수정하거나 동적으로 범위를 참조할 수 있도록 OFFSET, COUNTA함수를 써서 해결(참고-동적 범위에 이름 정의하기)해야 하는 불편함이 따릅니다.

 

< 표의 구조적 참조 방식 정리 >

참조 방식 설명
표이름[@머리글] 표에서 동일 행을 참조

표의 안에서 참조할 때는
[@머리글]의 형식으로 '표 이름'을 앞에 붙이지 않아도 된다.

표이름[머리글] 표의 특정 열 전체를 참조

표의 안에서 참조할 때는
[머리글]의 형식으로 '표 이름'을 앞에 붙이지 않아도 된다.

 

3. #기호를 이용하여 구조적 참조하기

#기호를 이용하면 표의 머리글, 데이터, 요약, 전체 영역을 참조할 수 있습니다.

엑셀 표 구조적 참조 - #기호 이용하여 참조

1) 머리글 참조

표이름[#머리글]로 참조합니다.

아래는 머리글의 열의 개수를 구해줍니다.

=COUNTA(인사[#머리글]) => 8

 

2) 데이터 참조

#데이터로 참조합니다.

아래는 데이터 영역을 참조한 결과를 구해줍니다.

=COUNTA(인사[#데이터]) => 168(데이터 영역의 값이 있는 셀의 개수)
=SUM(인사[[#데이터],[월급여]]) => 111,060,000(데이터 영역에서 월급여 열의 합계)

 

3) 요약 참조

#요약으로 참조합니다.

아래는 요약 영역을 참조한 결과를 구해줍니다.

=인사[[#요약],[이름]] => 21(요약행의 이름 항목의 값(직원 수))
=인사[[#요약],[월급여]] => 111,060,000(요약행의 월급여(월급여 합계))

 

4) 전체(모두) 참조

#모두로 참조합니다.

아래는 전체 영역을 참조한 결과를 구해줍니다.

=ROWS(인사[#모두]) => 23(머리글,데이터,요약을 포함한 전체의 행수)

 

< #기호를 이용한 표의 구조적 참조 방식 정리 >

참조 방식 설명
표이름[#머리글] 표의 머리글 행을 참조
표이름[#데이터] 표의 머리글 행과 요약 행을 제외한 영역을 참조
표이름[#요약] 표의 요약 행을 참조
표이름[#전체] 표의 머리글 행,데이터 행,요약 행을 모두 포함하여 참조

 

 

[ 엑셀 수식 강좌  - 엑셀 이름정의와 표기능]

 

 

 

답글 남기기

이메일 주소는 공개되지 않습니다.