초급 강좌에서 엑셀의 수식은 주소(참조)를 사용한다는 것을 배웠죠? 그 참조에도 여러 가지 방법이 있고 이것은 수식을 이용하는데 매우 중요한 개념이랍니다. 엑셀에서는 주소를 참조할 때 상대참조와 절대참조, 그리고 두가지를 혼합한 혼합참조의 3가지 방법을 나누어서 사용합니다.
상대참조란 셀이나 범위에 대한 참조영역을 수식이 있는 셀의 상대위치를 기준으로 하는 방식이고, 절대참조는 절대적인 위치를 기준으로 하는 방식입니다. 쉽게 생각해서, 상대참조로 된 수식을 복사하면 붙여 넣은 수식의 참조는 자동으로 고쳐져서 수식의 원래 상대 위치와는 다른 셀을 새롭게 참조하게 되지만, 절대 참조는 복사해서 붙여 넣기 하더라도 항상 고정된 셀만 절대적으로 참조하는 것입니다. (상대와 절대의 뜻을 잠시 생각해 보시기 바랍니다.)
예를 통해 이해해 보도록 하죠.
은행 이자를 구하기 위해 다음과 같은 간단한 데이터를 입력한 후 B4셀에 =A4*B1 수식(이율 * 원금)을 입력했습니다. 잘 구해졌죠?
이제 기초강좌에서 배운대로 자동 채우기를 이용해서 B5, B6 셀의 값도 자동으로 구해보세요. 그런데 이상한 값이 나오고 오류도 나오고 그렇죠?
왜 이런 오류가 발생했을까요? 이런 오류가 발생하면 제일 먼저 어디를 봐야할까요? 그렇죠! 수식입력줄을 살펴봐야 합니다. B5셀의 경우 =A5*B2라는 수식이 자동으로 입력되어져 있습니다. 자동 채우기가 수식에서 참조하는 셀을 자동으로 한행씩 아래로 변경을 했기 때문이죠. 그런데 B2셀에는 아무런 내용이 없죠? 그래서 곱하기가 실행되지 못한겁니다.
같은 원리로 B6셀은 =A6*B3 수식이 입력되어 있을겁니다. 수치 자료를 텍스트 자료와 곱했으니 #VALUE 에러가 발생한겁니다. (엑셀 에러 코드에 대해서는 다시 배우게 됩니다.)
우리가 원하는 정확한 계산은 원금은 한칸씩 아래로 내려가면서 상대적으로 계산이 되어야 하지만, 이율은 고정되어 있어야 정확하겠죠? 이럴 경우 은행의 이율이 있는 B1셀을 절대 참조로 고정을 시켜두어야 한다는 것입니다. 이렇게 복사나 자동채우기로 수식을 자동으로 입력할 때 참조가 변하지 않고 고정되어 있어야 하는 셀은 절대참조로 고정을 시키는 것입니다. 일반적으로 셀 주소만 입력되면 상대 참조가 되는 것이고, 셀 주소 앞에 $ 기호를 붙이면 절대 참조가 됩니다. $기호가 붙은 셀은 복사가 되더라도 항상 그 셀의 주소만 절대적으로 참조하게 됩니다.
자 그럼 수식에서 B1셀을 절대참조로 변경을 해보겠습니다. B4셀을 선택한 후 수식 입력줄에서 B1셀을 $를 입력해서 아래와 같이 절대참조로 변경하세요.
그 후 자동채우기로 다시 B6셀까지 채워주시면 우리가 원하던 계산값이 표시됩니다.
이해되셨죠? 그런데 고정 시키려는 주소에서 매번 $ 표시를 입력하는 것도 매우 번거로운 일입니다. 이럴 경우에는 참조의 종류를 변경하려는 수식에서 F4 키를 입력하면 차례대로 절대/혼합/상대 참조로 변경된답니다. 많이 사용하게 되니 꼭 기억해 두세요!
R1C1 참조 방식 이해하기
엑셀에서 기본적으로 셀의 위치를 나타내는 참조 방식은 A1 방식입니다. A1 방식에서 열은 알파벳순으로, 행은 번호순으로 레이블이 붙습니다. R1C1 방식은 열과 행을 모두 번호순으로 레이블을 붙이는 것으로 일반적인 작업보다는 매크로와 같은 프로그래밍에서 주로 사용됩니다. 매크로를 기록할 때 R1C1 참조 스타일을 사용하여 명령을 기록하므로, R1C1 방식에 대해서도 알아둘 필요가 있습니다.
R1C1 참조 방식에서는 “R” 다음에 행 번호, “C” 다음에 열 번호를 지정하여 셀 위치를 나타냅니다. 예를 들어, 절대 참조인 R1C1은 A1 참조 스타일의 절대 참조 $A$1과 같습니다. 만약 현재 셀이 A1이면 상대 참조 R[1]C[1]은 아래로 한 행, 오른쪽으로 한 열 옆에 있는 셀, 즉 B2셀을 참조하게 되는 것입니다.
다음은 R1C1 방식의 예입니다.
- R[-2]C 같은 열에서 두 행 위에 있는 셀에 대한 상대 참조
- R[2]C[2] 두 행 아래, 두 열 오른쪽에 있는 셀에 대한 상대 참조
- R2C2 둘째 행과 둘째 열에 있는 셀에 대한 절대 참조
- R[-1] 현재 셀 위에 있는 행 전체에 대한 상대 참조
- R 현재 행에 대한 절대 참조
현재 워크시트에서 R1C1 참조 스타일을 사용하거나 또는 사용하지 않으려면 [파일-옵션]을 선택한 후 [수식] 탭에서 “R1C1 참조 스타일” 확인란을 선택하거나 취소하면 됩니다.
절대참조(절대주소)와 상대참조(상대주소)는 고급 수식을 작성하는데 필수적인 개념이므로 꼭 기억을 해두시기 바랍니다. 또한 R1C1 방식의 주소 표현은 VBA 프로그래밍에서 사용되는 방식이므로 이것도 알아두는 것이 좋겠죠? ^^
