엑셀 참조 개념 절대 참조 와 상대 참조 그리고 혼합참조

엑셀을 한번씩 글을 올리는데 자주 못올리게 되는이유가
설명이 너무 장황해지는 일이 생길수 있어서 입니다.

꼬리물기로 계속 추가할 내용이 늘어나게 됩니다 ^^

기본적인 내용부터 하나 씩 다루어 나가야 겠다고 생각했습니다.

그중에서 가장중요한 참조 의 개념에 대해 정리를 해보았습니다.

엑셀은 잘 다루면 몇사람이 할일을 혼자서 그것도 매우 간단하게 처리할수 있도록 도와 줍니다.

 

추후 메크로 VBA등을 활용한다면 그 이상의 신세계를 경험 할수도 있을 것입니다.

하나하나 배워 나가면 실무에서 활용할수 있게 되실것이니 하나씩 배워 보시길 권장합니다.

 

[ TOC ]

 

 

 

목차를 확인 후 클릭 하시면 원하는 부분으로 바로 이동이 가능합니다.

모바일 사용자 가독성 향상을 위해 이미지 의 확대 편집을 최대한 활용 하였습니다.

글이 도움이 되셨다면 공감과 댓글이 큰힘이 됩니다.

 

엑셀에서 참조란

참고 와 참조의 사전적 의미

참고는 <살피어 생각하는> 행위이고

참조는 <살피어 견주어 보는> 행위입니다.

참고는 생각하는 것이고 참조는 보는(대조하는) 것이라는 차이가 있습니다.

엑셀에서의 참조란 셀의 값을 대조 하여 정의 하는 것이라고 생각하면 되겠습니다.

절대 참조 와 상대 참조 그리고 혼합 참조 간단 설명

참조에는 상대 참조 와 절대 참조가 있으며 상대참조 와 절대 참조의 조합형이 혼합 참조도 사용되고 있습니다.

상대 참조 : 수식을 자동으로 채우기 하면 , 참조 된 셀값도 같이 이동

 

 

절대 참조 : 한번 입력한 셀 주소는 항상 고정 된 값으로 인식

 

혼합 참조 가로방향 : 가로 방향(A,B,C,D..)만 고정 된 값으로 지정하는 방식

혼합 참조 세로방향 : 세로 방향(1,2,3,4..)만 고정 된 값으로 지정하는 방식

 

셀 참조 방식의 종류

엑셀 시트에서는 각각의 칸(셀)마다 고유한 주소를 가지게 됩니다.

예를 들면 A1, B2, C3 ... 이런식으로 모든 셀에 값이 지정이 됩니다.

이 해당 셀의 주소는 함수를 사용할때 해당 셀에 입력된 값을 불러오는 용도(기준값)로 사용이 됩니다.

 

위 와 같이 =A1+A2+A3+A4+A5 라고 수식을 만들었지만

각각의 셀에 입력된 수치 값을 참조 하여 15 라는 결과값을 반환 하게 되는 것입니다.

이렇게셀에 값을 불러오는 것을 참조 라고 하며 이 참조 방식에는 크게 2 종류가 있습니다.

이 것이 상대참조 와 절대참조 가 있는 것입니다.

 

혼합 참조 는 위 상대참조 와 절대참조가 모두 필요한 상황에서 적절하게 사용 할수 있는 참조 방식입니다.

 

상대 참조와 절대 참조를 사용하는 이유

엑셀의 기본 적으로 적용되는 참조 방식은 상대참조 입니다.

위에 설명 드린 대로 수식을 자동으로 채우기 하면 , 참조 된 셀값도 같이 이동 하는 방식으로 적용이 되는 것입니다.

상대 참조를 사용하는 상황

바로 위에 설명한대로 엑셀에 기본적으로 적용이 되어있는 참조 방식입니다.

셀에 수식을 입력한 뒤 자동채우기를 하면 셀의 위치가 변하면서, 참조되는 셀 주소값 도 동시에 변하게 됩니다.

다른 통합문서 에서 참조 하는 경우에는 절대참조 가 기본으로 적용됩니다.

 

상대 참조 사용 예시

구매물품에 수량 을 곱하여 합계 를 구하는 수식을 만들어 보았습니다.

하지만 구매물품이 다수개로 모두 적용을 하는 경우가 생기게 됩니다.

 

마우스 커서가 '검은색 십자가 모양'으로 변하였으면, 마우스를 아래로 드래그합니다.

 

이렇게 마우스 커서를 셀의 오른쪽 아래로 이동합니다.

셀이 아래로 자동채우기 되면서, 참조되는 셀들의 주소도 동시에 변하는 것을 볼 수 있습니다.

이와 같이 구매물품이 다수개로 모두 적용을 하는 경우 간편하게 결과값을 구할수 있게 됩니다.

절대 참조를 사용하는 상황

수식이 입력된 위치에 상관없이, 항상 셀 주소가 고정되는 참조방식입니다.

절대 참조 사용 예시

위의 예시에서는 각 품목의 함계만을 구했습니다.

이번에는 마트 구매 포인트 라는 셀이 추가 되어 포인트적입 값을 구해야 합니다.

 

상대 참조의 방식으로 Drag해서 자동 채우기를 하게되면 어떤 셀은 '#VALUE!' 오류가 나오고, 어떤 셀은 잘못 계산된 값으로 결과값이 보여지게 됩니다.

 

마지막의 참조된 셀 수식을 보면

음료수의 구매단가(C6) 와 장난감(E8)의 지출합계가 반영되어 결과값으로 적용된것을 볼수 있습니다.

자동으로 이동하는 셀만큼 셀의 위치가 변한 부분이 자동으로 반영 된것입니다.

 

수식을 수정하기 위해 셀을 클릭합니다.

키보드 F4키를 한번 눌러주면, 셀주소에 '$' 기호가 붙으며 선택한 수식부분이 절대참조로 변경됩니다.

 

수식을 아래로 드래그 하여 자동채우기하면, '마트 구매 포인트' 값은 고정되어 각 구매 물품별 포인트 적립액이 정상적으로 계산됩니다.

절대참조에서 사용되는 $ 표시는 무슨 의미 인지

수식이 들어 있는 셀을 선택합니다.

수식 입력줄 에서 변경할 참조를 선택합니다.

F4 키를 눌러 참조 형식 간에 전환합니다.

$ 표시는 '행' 또는 '열' 로 고정될 값의 위치를 표시합니다.

 

참조 방식	명칭	설명
$A$1	절대참조	열과 행 모두 고정됩니다.  (A와 1 모두 고정)
$A1	혼합참조/열절대참조	열만 고정됩니다.  (A만 고정)
A$1	혼합참조/행절대참조	행만 고정됩니다.  (1만 고정)

좀더 복잡한 상황에서는 혼합 참조

열과 행을 모두 고정 하는 것이 절대 참조 라면 이 혼합 참조는 특정 열 , 특정 행 만 고정 하는 방식입니다.

특정 열만 고정시에 행으로 자동채우기를 하면 행값은 변하지만 열값은 변동되지 않습니다.

반대로

특정 행만 고정시에 열로 자동채우기를 하면 열값은 변하지만 행값은 변동되지 않습니다.

혼합참조 사용 예시 정리 (3가지)

위와 같이 구매 금액의 10%,20%,30%,40% 에 해당하는 값을 구해야 하는 상황을 예로 들어 보겠습니다.

수식은 구매금액(C3) * %값(D2) 의 형태로 결과값(D3)이 구해지게 됩니다.

 

이렇게 그대로 자동채우기 하게 되면 상대 참조가 되어 오류값을 반환하게 됩니다.

 

이렇게 절대 참조를 하여 반영하게 되면 모두 같은 결과 값 (1,000) 만을 가져오게 됩니다.

이렇게 상대 참조 와 절대 참조 만으 로 명확하게 구분되지 않을때 혼합참조가 필요합니다.

 

혼합참조는 여러가지 경우의 수가 존재 하기에 여러가지 예시가 있을수 있습니다.

행 절대참조를 활용한 혼합 참조

값과 위치가 변동되는 C행 의 값(구매금액)은 상대 참조로 적용합니다.

값과 위치의 변화가 없는 D열의 2행의 값(%) 만 절대 참조로 반영을 하였습니다.

열 절대참조를 활용한 혼합 참조

하지만 위의 수식으로 가로로 자동채우기를 하게 되면 올바른 값을 가져오지 못하게 됩니다.

 

이경 우에는 위와 다르게

값과 위치가 변동되는 숫자 7 행 의 값(%)은 상대 참조로 적용합니다.

 

값과 위치의 변화가 없는 C열 의 값(구매금액)만 절대 참조로 반영을 하였습니다.

이렇게 하면 정상적으로 반영이 됩니다.

열 절대참조 와 행 절대 참조를 혼용한 혼합 참조

위의 내용은 혼합 참조를 설명하기위한 내용이었습니다.

위 처럼 여러 DATA를 참조 해야할때 혼합참조가 없다면 열또는 행에 한정되어 작업을 할수 밖에 없습니다.

 

그래서 가로 와 세로 를 모두 한번에 적용하고 싶을때 혼합 참조를 활용 할수 있습니다.

$C7*G$2 이와 같은 수식으로 적용하면 가능합니다.

$C7 를 풀이하면 C열은 고정하고 행의 숫자만 상대참조로 적용한다는 의미입니다.

 

G$2 이 또한 G열은 상대참조로 반영하고 숫자 행만 절대 참조로 반영한다 라고 이해 하면 되겠습니다.

마무리

이 참조 관련 내용은 엑셀의 기본 개념 이라고 할수 있습니다.

 

다양한 상황에서 대량의 DATA를 다루다보면 이 절대 참조 와 혼합참조 등의 활용을 하지 않는다면 간단하게 결과를 얻을수 있는 상황에서도 많은 반복작업을 하게 됩니다.

 

제대로 활용하여 한단계 발전된 엑셀을 경험 해보시길 바랍니다.

참고

[Learn] - 엑셀 기본 함수 7종 한번에 마스터

엑셀 기본 함수 7종 한번에 마스터

[Learn] - 엑셀 조건부 서식

엑셀 조건부 서식

[Learn] - 엑셀 if 함수 사용 방법 그리고 연산자 부등호 관련 안내

엑셀 if 함수 사용 방법 그리고 연산자 부등호 관련 안내

 

[Learn] - 엑셀 비밀번호 해제 설정 완벽하게 정리 해드립니다.

엑셀 비밀번호 해제 설정 완벽하게 정리 해드립니다.