이번 글에서는 엑셀 표 기능의 구조적 참조에 대해 알아보겠습니다.
※ 엑셀 표의 기본적인 기능은 아래 링크를 참조하세요.
[ 엑셀 표 기능으로 자료 관리하기 ]
엑셀의 표는 다음과 같은 구조로 되어 있습니다. 표의 이름을 먼저 정의하고 표 하위의 머리글, 데이터, 요약행을 참조하는 방식을 구조적 참조라고 합니다. 구조적 참조를 사용하면 기존의 셀주소 참조 방식보다 훨씬 직관적이고 사용하기 편리합니다.
위의 표에서 성유리 과장의 월급여는 [G2]셀에 입력되어 있는데 월급여를 참조할 때 =[G2]와 같이 셀 주소를 입력하는 것보다 =인사[@월급여]처럼 구조적 참조가 직관적입니다. 처음에는 낯설어서 더 어려워 보일 수도 있으나 조금만 익히면 수식을 바로 이해할 수 있고 참조의 위치가 바뀌어도 수식을 변경하지 않아도 되는 등 장점이 많습니다.
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(머리글,데이터,요약을 포함한 전체의 행수)
< #기호를 이용한 표의 구조적 참조 방식 정리 >
| 참조 방식 | 설명 |
| 표이름[#머리글] | 표의 머리글 행을 참조 |
| 표이름[#데이터] | 표의 머리글 행과 요약 행을 제외한 영역을 참조 |
| 표이름[#요약] | 표의 요약 행을 참조 |
| 표이름[#전체] | 표의 머리글 행,데이터 행,요약 행을 모두 포함하여 참조 |
엑셀 수식 강좌
[ 수식 다루기 ]
[ 엑셀에서 입력과 표시방법 ]
[ 수식에서 셀과 범위 참조 방식 ]
[ 엑셀을 제대로 쓰는 데이터처리 ]
- 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
- 수식으로 한 번에 텍스트 나누기
- 엑셀 고급필터로 다양하게 자료 필터링하기
- 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
- 엑셀 오류데이터 처리하기
- 파워 쿼리(Power Query) 소개
- 파워쿼리로 외부 데이터 가져오기
- 엑셀에서 중복 데이터 제거하기
- 엑셀 텍스트 나누기의 모든 것
- 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
- 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
- 사용자 지정 수식으로 데이터 유효성 검사하기
- 데이터 유효성 검사로 허용된 데이터만 입력
- 엑셀 데이터 다루기 첫 단추는 데이터 구조화
[ 엑셀 이름정의와 표기능 ]
[ 수식을 분석하고 오류 해결하기 ]
- 엑셀 순환참조 문제 해결하기
- 수식 계산 단계 알아보기
- 엑셀 수식 오류 검사하고 추적하기
- 엑셀에서 참조 셀 추적하기
- 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
