칼퇴를 부르는 업무스킬 엑셀편3 - 엑셀의 행열고정 (if와 vlookup을 정복하기 전의 필수과정!)

 

직장생활을 하던 시절, 인사관리팀에서 가장 많이 질문했던 함수 두가지가

if와 vlookup 함수였다.

 

if함수와 vlookup이 어려운 이유는 수식을 복사 했을때 오류가 뜨기 때문이지 않을까 싶다.

왜 오류가 뜨는 것일까?

 

오늘은 이 오류가 뜨는 원인의 70% 이상을 차지하는 행열고정에 대해 설명하려고 한다.

 

 

 

 

(미리 한번 보고 들어가면 이해가 빠를 것 같아 먼저 적어본다.)

F4 - 한번 눌렀을 때	F4 - 두번 눌렀을 때	F4 - 세번 눌렀을 때	F4 - 네번 눌렀을 떄
행열고정 = 절대참조	행고정 = 혼합참조	열고정 = 혼합참조	원래대로 돌아옴

 

0) 행열 고정의 개념을 이해하자!

 

먼저 엑셀의 구조를 이해해야하는데, 가장 간단한 SUM 함수를 복사 했을 때로 확인을 해보자

 

행열고정의 이해_1

아주 간단하다. =sum(시작:끝) 으로 원하는 셀들의 합계를 구해주고 난 다음 F2를 눌러 범위를 확인해보자

행열고정의 이해_2

당연하게도 내가 지정한 셀들이 범위로 지정되어 있는 것을 알 수가 있다. 

그렇다면, sum 수식을 걸어둔 셀을 복사(컨트롤C 또는 커멘드C) 후,

아래 셀에 붙여넣기(컨트롤V 또는 커멘드V)를 해보자.

 

굳이 말하지 않아도, 다음 행의 합계가 나온다는 것을 알 수가 있다.

 

행열고정의 이해_3

바로 이렇게 말이다.

 

행열고정의 이해_4

나란히 두고 보면 이해가 더 쉬울 것 같아 한번 붙여보았다.

자, 이제 이해가 되는가?

엑셀에서 일반적인 복사> 붙여넣기를 하게되면 텍스트가 아닌, 수식이 그대로 복사가 되며

이동한 열의 수 만큼 지정한 열도 내려오게 되는 것이다.

 

행열고정의 이해_5

이번에는 똑같이 범위를 지정해준 다음, F4를 한번 눌러 보자.

지정한 범위에 $표시가 있는 것을 알 수가 있다.

이게 어떤 의미인지는 아래에서 설명을 할 예정이니, 일단 엔터를 눌러 값을 확인해보면,

그대로 지정한 셀의 합계가 결과값으로 나오는 것을 알 수가 있다.

 

이제 이 값을 아래 빈 셀에 붙여넣기 해보자

행열고정의 이해_6

이게 어떻게 된 일인가, 당황하지 말고 F2를 눌러 SUM의 지정된 범위를 확인해보면

열이 따라내려오지 않고, 위에 고정되어 있는 것을 알 수가 있다.

 

자, 이게 바로 행열고정이다!

범위를 지정한 후, F4 하나로 행열을 고정을 할 수가 있단 말이다.

 

 

1) 행열고정=절대참조의 이해 (F4 한번!)

 

행열 고정을 올바르게 이해하고 익히기 위해서 홀로 해볼 수 있는 것은 구구단 표 만들기이다.

 

엑셀을 열어 이런 표를 만들고, 수식을 하나씩 넣어보자

 

행열 고정을 하지 않고, 붙여넣기를 한다면 근본없는 숫자가 나오게 될 것이며

 

1에 행열고정 (절대참조)를 해준 후, 복사>붙여넣기를 해주면 오른쪽 처럼 올바르게 1단이 완성된 것을 볼 수가 있다.

절대참조는, 행열고정 상관없이 무조건!!! 그셀의 값을 참조한다는 것, 잊지말자.

 

 

2) 혼합참조 - 행고정의 이해 (F4 두번!!)

자 이번에는 F4를 두번 눌러서 행고정이라는 것을 해본 후, 복사>붙여넣기를 하면 또다시 근본없는 값이 나오는 것을 볼 수가 있을 것이다.

 

F2를 눌러서 도대체 어떻게 된 것인지 확인을 해보자.

행고정을 했기 때문에, 수식에 따라 행은 고정된 채로 열이 이동하고 있는 것을 볼 수가 있다.

 

3) 혼합참조 - 열고정의 이해 (F4 세번!!!)

 

이번에는 열고정을 해준 후, 복사>붙여넣기를 해보면 오른쪽과 같이 올바르게 구구단 표가 완성되는 것을 볼 수가 있다.

열을 이동하지 않고, 곱해지는 값만 이동을 하기 때문에 이런 값이 나오게 되는 것이다.

 

 

 

 

4) 언제 어떤 상황에 행열고정이 들어가며, 행고정 열고정을 구분하여 쓰는가?

 

아, 이것은 대답하기 굉장히 어려운 부분인데

솔직하게 이야기 하자면, 

F4 - 한번 눌렀을 때	F4 - 두번 눌렀을 때	F4 - 세번 눌렀을 때	F4 - 네번 눌렀을 떄
행열고정 = 절대참조	행고정 = 혼합참조	열고정 = 혼합참조	원래대로 돌아옴

이 부분을 기억하고, 혼자서 몸으로(?) 익히는 것 말고는 방법이 없다.

이론적으로 길게 설명을 해봤자 이해가 되지 않기 때문에 엑포자가 생겨나는 것이다.

 

모르겠다 싶으면 무조건 해보자

F4를 한번 눌러서 해보고 오류값이 뜨면 그땐 두번, 그래도 아니면 3번 

이런식으로 몇번의 시행착오를 겪다보면 '아' 하고 꺠달음이 찾아오는 순간이 반드시 있다.

 

이해가 안되면 위의 구구단표를 만들어서 직접 한번 행열고정을 해보자.

그리고 완벽히 이해가 되지 않았다고 해서 걱정하지 말자.

F4의 기능을 알고 있다는 것 만으로도 if함수와 vlookup함수의 오차는 이미 70%이상 줄어든 것이니.