엑셀 배우기(3) - 셀 주소 지정 방법(상대, 절대, 혼합 참조)

1. 상대 참조

아래와 같이 개인별, 과목별 점수가 있을 때 총점과 평균을 구하는데

홈 탭 오른쪽 끝에 있는 자동 합계 명령을 이용하면 쉽게 구할 수 있습니다.

자동 합계 명령을 누르면 자동으로 B4부터 E4까지 범위를 잡고 sum함수를 입력해줍니다. 맞으므로 엔터키를 누르면

합계 314가 구해지고 커서가 아래로 내려갑니다.

이때 F4셀을 다시 선택하고, 오른쪽 아래 네모 모양의 채우기 핸들을 더블 클릭하면 F5셀부터 F9셀까지 자동으로 합계를 구해주는데,

F5셀을 클릭해서 sum함수 안의 셀 범위를 확인해보면 B5에서 E5로 F4셀의 합계 범위가 B4에서 E4였는데, 한 줄 아래로 내려가니 자동적으로 4가 5로 바뀌었습니다. 이와 같이 상대적인 위치에 따라 셀 주소가 바뀔 수 있도록 지정하는 방식이 상대 참조입니다.

이때 F4셀의 채우기 핸들을 오른쪽으로 끌고, G4셀의 수식을 확인해 보면 합계 범위가 C4에서 F4로 행은 변하지 않고 오른쪽으로 복사했으므로 열만 오른쪽으로 한 칸씩, 다시 말해 B가 C로, E가 F로 수정됐습니다.

G4셀에서 평균을 구하려면 자동 합계 오른쪽의 콤보 상자 버튼을 누른 후 평균을 선택하는데,

범위가 B4에서 E4여야 하는데 F4로 지정됐으므로,

E4로 바꾸기 위해서는 Shift 키를 누른 상태에서 왼쪽 화살표 키를 누르거나, Shift 키를 누른 상태에서 마우스로 E4셀을 클릭합니다.

이제 엔터키를 누르면 평균 78.5가 구해집니다.

다른 셀에도 평균을 구하는 수식을 적용하기 위해 G4셀의 채우기 핸들을 더블 클릭합니다.

G8셀과 G9셀에 #DIV/0! 에러가 표시되는데, 합계 0을 4로 나눠서 0을 나눌 수 없다는 에러 메시지입니다.

8행의 총점을 구하면 G8셀의 에러 메시지가 없어집니다.

B8셀과 B9셀에 합계와 평균을 구하는 수식을 입력한 후 B8셀과 B9셀을 끌어서 두 셀을 선택한 다음 B9셀의 채우기 핸들을 오른쪽으로 끌면 두 줄이 한꺼번에 E열까지 복사됩니다.

셀의 수식을 확인하기 위해 Ctrl + ` 를 누르면 아래와 같이 수식이 보이고, 상대적인 위치에 따라 수식이 변경된 것을 알 수 있습니다.

원래대로 돌리기 위해서는 Ctrl + `(숫자 1 왼쪽 키)를 다시 누릅니다.

2. 절대 참조

절대 참조는 셀이 이동하더라도 셀 주소를 고정시키는 것입니다.

1년 후 이자를 계산하기 위해서는 원금에 이자율을 곱하는데, 15행과 16행에도 적용해야 하므로,

A14는 아래로 내려갈 때 행이 변해야 하므로 그대로 두면 되는데, 이자율 셀 B11은 바뀌면 안 되므로 B11셀을 $B$11로 입력해서 절대 참조 형식으로 지정해야 합니다.

절대 참조 형식으로 지정할 때는 F4키를 사용하는 것이 편리합니다.

B11을 마우스로 끌어서 선택한 후 F4키를 누를 때마다
$B$11 -> B$11 -> $B11 -> B11로 변합니다. 따라서, 다시 $B$11로 입력하기 위해서는 다시 한번 더 F4키를 누르고, 엔터키를 누르면 750원이 구해집니다.

B14셀의 채우기 핸들을 더블 클릭하면 B15셀과 B16셀에 수식이 복사되는데, 원금은 A15, A16으로 자동으로 바뀌는데, 이자율 B11셀은 셀이 이동하더라도 고정되므로 정확한 이자를 구할 수 있습니다.

3. 혼합 참조

혼합 참조는 상대 참조와 절대 참조를 결합해서 사용하는 것입니다.

아래와 같이 판매액 별로 할증, 할인액을 계산한다고 할 경우

판매액이 있는 E열은 오른쪽으로 복사할 때 변경되면 안 되고, 아래로 복사할 때는 행이 바뀌어야 하며,

할증률과 할인율이 있는 12행은 아래로 복사하더라도 변경되면 안 되고, 오른쪽으로 복사할 때는 F열과 G열이 자동으로 바뀌어야 하므로
혼합 참조 형식으로 입력해야 합니다.

다시 말해 F14셀의 수식은 E14*F12이고,
G14셀의 수식은 E14*G12이며,
F15셀의 수식은 E15*F12이고,
G15셀의 수식은 E15*G12여야 합니다.

이와 같은 조건을 충족하려면 F14셀의 수식은 E열과 12행을 고정시켜 그 앞에 $기호를 붙여 $E14*F$12로 입력해야 합니다.

혼합 참조 형식으로 입력하기 위해서는 셀 주소를 선택한 상태에서 F4 키를 원하는 형태가 나올 때까지 연속적으로 눌러 $표시를 넣을 수도 있고, 원하는 위치에서 Shift + 4키를 눌러서 $표시를 입력할 수도 있습니다.

F14셀에서 수식을 입력하고 엔터키를 누르면 23,000 * 5% 해서 1,150이 구해졌습니다.

이제 F14셀의 채우기 핸들을 오른쪽으로 끌고,

G14셀의 채우기 핸들을 더블 클릭하면 모든 셀에 수식이 채워집니다.

수식을 확인하기 위해 Ctrl + `(grave 또는 backtick, 숫자 1 왼쪽 키) 키를 누르면 아래와 같습니다.

G16셀의 수식이 2행 내려갔으므로 E14가 E16이 됐고, 할인율인 G12셀은 변하면 안 되므로 14행부터 16까지 동일합니다. F14셀부터 F16셀까지는 할증률 셀이 F12로 동일합니다.

이와 같이 복사해서 붙여 넣을 때 셀 주소가 적절하게 변할 수 있도록 혼합 참조 또는 절대 참조, 상대 참조 등을 사용해야 합니다.

엑셀배우기3.xlsx

0.01MB