엑셀 절대참조,상대참조,혼합참조 개념 정리 $

엑셀을 사용할 때 꼭 알아야할 참조에 대해 알아보겠습니다. 

엑셀의 참조는 절대참조, 상대참조, 혼합참조가 있습니다. 

잘 구분해서 사용하지 않으면 결과값이 엉뚱하게 나올 수 있으니 개념을 잘 이해하고 사용해야합니다. 

엑셀 상대참조

엑셀 상대참조는 가장 기본적인 참조인데요. 

상대참조는 수식을 채우기 혹은 복사했을 때 복사된 위치로 이동한 만큼 참조값(행,열)이 변하게 됩니다. 

C4, D5 등으로 사용하는 것이 상대참조입니다. 

D2셀의 수식이 B2+C2 일때 

F5셀로 수식을 복사하면 

F5의 수식은 C5+D5 이 됩니다. 

예제와 함께 알아보겠습니다.

김민정의 총점을 구하는 수식

=C4+D4+E4

위 화면처럼 F4 셀의 오른쪽 아래를 클릭해 아래로 드래그 하면 수식 채우기가 됩니다. 

수식 채우기 결과

수식 채우기를 하면 해당 채우기된 셀에 수식이 복사가 됩니다. 

이 때 참조가 상대참조인지,절대참조인지,혼합참조인지에 따라 결과값이 달라지게 됩니다. 

채우기 결과

F4의 수식 =C4+D4+E4 

F5의 수식 =C5+D5+E5

수식이 복사 될때 행이 +1 만큼 이동

따라서 상대참조또한 행이 +1 만큼 변경됩니다. 

F4 의 수식을 G5로 복사했다면 

F4의 수식 =C4+D4+E4 

Ｇ5의 수식 =Ｄ5+E5+F5

행  +1행 

열이름 +오른쪽1열 

만큼 이동한 값이 참조가 됩니다. 

 

이처럼 엑셀 상대참조가 들어있는 수식이 복사될 때 이동한 만큼 참조값이 변경됩니다. 

함수 속의 참조또한 마찬가지입니다. 

예)      F4 =SUM(C4:E4)

-> F5 =SUM(C5:E5)

    

    C11 =COUNT(C4:C10)

-> D11 =COUNT(D4:D10)

엑셀 절대참조

엑셀 절대참조는 상대참조와는 반대로 수식이 복사될 때 변경되지 않습니다. 

수식을 복사해서 사용해도 특정 참조는 절대 주소로 참조하고 싶을 때 사용하는 값입니다. 

절대참조의 사용은 $ 기호를 사용하는 것입니다. 

예)  $B$2

$B$2:$B$10

위 상대참조에서 했던 예제를 절대참조로 했을 경우 

총점을 절대참조를 이용해 적은 후 셀 채우기로 수식을 복사한다면 결과는 어떻게 될까요? 

셀 채우기를 했을 때 수식이 변경 적용 되었던 상대참조와는 달리 

절대참조는 변경되지 않고 $C$4 라는 절대참조를 사용하면 어디에 복사를 해도 계속 $C$4를 절대주소로 참조하게 됩니다. 

위 결과처럼 모두 같은 결과값이 나오게 됩니다.

상대참조와 절대참조를 같이 사용한 예

위 예제는 단어의 품사멸로 개수를 구하려고 합니다. 

개수를 검색할 범위는 절대참조

고정된 절대참조를 사용해야 동사,형용사에 수식을 복사했을때 범위가 변경되지 않을 것입니다. 

개수를 구하는 조건 : 명사라는 품사가 들어있는 G4셀 상대참조

동사,형용사를 적용하기 위해 수식을 복사했을 때 G5,G6 으로 변경되어야 하기 때문에 상대참조를 사용합니다. 

셀채우기 결과

각 셀의 수식이 어떻게 변화되었는지 보시면 절대참조는 그대로 상대참조는 변경된 것을 확인하실 수 있습니다. 

만약 범위를 상대참조로 사용했다면 

동사의 개수를 구하는 수식은 =COUNTIF(C5:C15,G5)로 변경이 되었을 것입니다. 

그럼 데이터 중 G4셀이 누락되어 개수는 3인 엉뚱한 결과값이 나오게 됩니다. 

위 예제 처럼 실무에서 사용할 때에도 절대참조와 상대참조를 섞어서 사용해 더 효율적인 수식을 만들 수 있습니다. 

엑셀 혼합참조

그럼 마지막으로 엑셀 혼합참조를 알아보겠습니다. 

절대참조와 상대참조를 이해하셨다면 혼합참조는 금방 이해 하실 것입니다. 

혼합참조는 말그대로 절대참조와 상대참조를 혼합해서 사용하는 방법입니다. 

행번호를 고정값으로 혹은 열 이름을 고정값으로 하는 것입니다. 

예)  B$3 $3은 고정값으로 행번호 3은 변경되지 않고 열 B만 변경됩니다. 

$B3

위와 같은 표가 있을 때 상대참조로 B3~G10까지의 범위를 채우기 한다면 어떻게 될까요?  

상대참조이기문에 셀주소가 변경되어 위와 같은 결과값이 나옵니다. 

절대참조를 했을 경우

모든 결과값이 같아집니다. 

혼합참조를 사용한 예

그럼 A열과 2행의 값을 곱하기 한 값을 각 셀에 표시 하고 싶다면 

=$A3*B$2 

$A3 : B,C,D등 다른 열의 셀로 복사 드래그 해도 $A는 변하지 않습니다. 행번호는 그래드로 변경된 만큼 같이 변경되었습니다.  

B$2 : 열이 이동한 만큼 C,D,E 열로 변경됩니다. 

  $2 행번호는 3,4,5 행으로 드래그,복사 해도 변경되지 않았습니다

엑셀 상대참조, 절대참조, 혼합참조 정리

엑셀 상대참조,절대참조,혼합참조

$ 부호를 이용해 절대참조를 표시한다. 

셀 채우기 드래그로 수식 복사했을 때 

상대참조 : B5     - 행번호 변경 O, 열이름 변경 O

절대참조 : $B$5  - 변경 X

혼합참조 : $B5    - 열이름 변경 X 행번호 변경 O

   B$5    - 열이름 변경 O 행번호 변경 X

절대참조 단축키 F4

키보드의 F4를 누르면 절대참조,혼합참조,상대참조로 변경됩니다. 

B4 -> $B$4 -> B$4 ->$B4 -> B4 

키보드의 F4를 누를 때마다 위와같은 순서로 참조가 변경됩니다.