20 엑셀 상대참조 절대참조 깨닫기

20_엑셀 초급 강좌 – 함수 기본 (5) 

엑셀에서 채우기 핸들은 밥 먹듯이 쓸 일이 많지만 작동 원리를 모르고 사용한다면 예상 밖의 결과가 나올 때가 있죠? 함수를 복사하거나 채우기 핸들을 사용할 때 엑셀의 기본 설정은 상대 참조 입니다. 엑셀은 셀의 위치를 이용해서 연산을 한다고 했습니다. 채우기 핸들을 이용할 수 있는 이유도 엑셀의 기본 설정이 상대 참조 이기 때문에 핸들을 끌면 계산하는 셀의 위치가 알아서 바뀌기 때문입니다. 그런데 그 계산되는 셀들의 위치를 내가 함수를 복사를 하든, 채우기 핸들을 이용하든 같게 하고 싶을 때 사용하는 것이 절대 참조 입니다.

03함수기본_5.xlsx

03함수기본_5_완성본.xlsx

1. 상대 참조

   상대 참조는 이미 우리가 연습을 했던 것이 바로 상대 참조 입니다. 한번 더 복습을 하자면 채우기 핸들을 이용할 때 수식의 셀값이 변한다면 상대 참조 입니다.

   지금 연습파일에서 모든 직원의 월급을 1000 올려 준다고 칩시다.

   그러면 이렇게 현재 월급에 1000을 더한 후 채우기 핸들을 이용하면 됩니다.

   

    

   채우기 핸들을 이용하면 E2셀이 행이 바뀌면서 행 숫자가 같이 바뀝니다. 상대 참조 이기 때문입니다.

   

    

    

2. 절대 참조

   지금 연습파일에서 이번엔 새로운 월급을 현재 월급에 기초하여 주고 싶습니다. 현재 월급에 2.1프로를 올려서 주고 싶다면 ( 현재 월급 x 인상률 + 현재 월급) 은 새로운 월급이 됩니다.

   배운 내용을 기본으로 해당 수식을 입력 해봤습니다.

   

    

    

   채우기 핸들을 이용해 수식을 빨리 입력한 후 결과값을 보니 박세리를 제외한 모든 직원의 월급이 인상되지 않았습니다.

   

    

    

   이유는 바로 엑셀의 기본 설정은 상대 참조이며 월급의 인상률이 H1에서 다른 사람에게는 H2,3,…. 로 상대 참조하여 바뀌었기 때문입니다.

   

    

   즉 우리가 원하는 것은 월급의 인상률의 위치 셀 H1을 채우기 핸들을 이용하더라도 바꾸지 않는 것 입니다.

   상대 참조 하는 셀의 위치를 함수를 복사하거나 채우기 핸들을 이용하여도 바꾸지 않는 방법은 $ 사인을 이용해서 알파벳과 숫자 사이에 넣어 주는 것입니다.

   

   귀찮니즘의 절정이 이 필자가 단축키를 제일 많은 쓰는 프로그램이 엑셀입니다. 필수 단축키만 알려드리며 넘어 가겠습니다.

    

   상대 참조 절대 참조 변경

   해당 셀에 커서를 위치한 후 F4

   F4를 누를 때 마다 행열 절대 참조에서, 행 절대 참조 열 절대 참조로 바뀝니다.

   뒤에만 이렇게 $사인이 있는 것이 행 절대 참조이며 사실 지금 예제에서는 행만 고정하여도 됩니다. 왜냐면 세로로만 채우기 핸들을 내렸기 때문에 행만 고정하여도 참조하는 셀이 바뀌지 않습니다.

   

    

    

   한번더 F4키를 누르시면 열 절대 참조가 됩니다. 열만 고정하는 것이므로 우리의 예제에서 사용하면 아무 효과가 없습니다.

   

    

    

   절대 참조를 한 후 채우기 핸들 드래그를 해줍니다. 원하는 값을 얻었습니다.

   

    

    

   다른 셀들을 살펴 보면 참조하는 셀 H1의 위치가 고정 되어있습니다.

   

   상대 참조 절대 참조의 개념은 엑셀뿐만 아니라 모든 스프레드시트 프로그램의 기본이 되므로 반드시 이해 하셔야 합니다.