엑셀 - 참조의 종류

1. 셀 참조와 범위 참조

1-1. 셀 참조(cell reference)

엑셀을 사용하는 사람이라면 개념은 잘 모르더라도 이 참조라는 것을 자주 사용했을 것이다.

흔히 셀 참조(cell reference) 혹은 셀 주소(cell address)라는 것으로, 이는 워크시트 상에서 셀을 식별하는 열 문자와 행 번호의 조합을 말한다. 예를 들어 A1은 A열과 1행의 교차점에 있는 셀을 말하며, A3은 A열과 3행의 교차점에 있는 셀을 말한다.

A1셀과 A3셀

수식에 사용될 때 셀 참조는 수식이 계산해야 하는 값을 엑셀에서 찾는 데 도움을 준다.

이를 테면, C7셀에 '=A1'이라는 수식을 입력했다면, C7에 'A1'에 기입된 내용이 자동 입력된다.

또, A1과 B1의 값을 더하려면 '+' 기호를 사용하면 된다.

=A1+B1

1-2. 범위 참조(range reference)

엑셀에서 범위(range)란 2개 이상의 셀로 이루어진 블록(block)을 말한다.

이렇게 여러 셀을 한 번에 설정하면 범위를 만들 수 있는데, 이렇게 워크시트 상에서 여러 셀의 조합의 모음을 범위 참조(range reference)라고 한다.

 

2. 상대 참조, 절대 참조, 혼합 참조

엑셀에서 참조는 상대 참조, 절대 참조, 혼합 참조로 나눌 수도 있는데, 단일 셀에 수식을 작성할 때 어떤 유형이든 사용가능하다.

상대 참조	셀 이동/복사 시, 해당 셀의 행과 열이 변경
절대 참조	셀 이동/복사 시, 해당 셀의 행과 열이 변경 안됨
혼합 참조	셀 이동/복사 시, 해당 셀의 행 혹은 열 중 하나가 변경

 

2-1. 상대 참조(relative reference)

상대 참조(relative reference)는 A1 혹은 A1:B5처럼 행과 열 좌표 모두에 기호 $가 없는 참조를 말한다. 엑셀의 모든 셀 주소(셀 참조)는 기본적으로 상대 참조가 적용된다. 상대 셀 참조는 여러 셀에서 이동하거나 복사하면 행과 열의 상대적인 위치에 따라 변경된다. 따라서, 여러 행이나 열에 걸쳐 같은 계산을 반복하려면, 상대 셀 참조를 이용해야한다.

A1~A4의 셀을 모두 5로 곱하고 싶은 경우, 상대 참조를 사용한다.

위는 처음 C1에 '=A1*5'를 입력한 것이다. 그리고 이를 아래로 드래그하면 A2~A4까지도 각각 5를 곱한 함수가 나타나는 걸 볼 수 있다.

 

2-2. 절대 참조(absolute reference)

절대 참조(absolute reference)는 $A$1 혹은  $A$1:$B$5처럼 모든 행과 열 좌표에 기호 $가 붙어 있는 참조를 말한다. 절대 셀 참조는 같은 공식으로 다른 셀을 채울 때 바뀌지 않는다. 이 절대 참조는 특히 특정 셀의 값으로 다중 계산을 수행하고자 할 때나 참조를 변경하지 않고 다른 셀에 복사해야 할 때 유용하다.

상대 참조로 계산(=A1*B1) 후 채우기 핸들을 끌어 내린 경우(좌측)과 절대 참조로 계산(=A1*$B$1) 후 채우기 핸들을 끌어 내린 경우(우측)

예를 들어, 열A에 있는 숫자와 B1에 있는 숫자를 곱하기 위해선, 공식 ='A1*$B$1'을 다른 행에 입력한 다음 채우기 핸들을 끌어서 열 아래로 복사하면 된다.

 

2-3. 혼합 참조(mixed reference) : 상대 참조 + 절대 참조

혼합 참조(mixed reference)는 $A1이나 A$1같이 하나의 상대적 좌표와 절대적 좌표가 모두 포함된 참조를 말한다.

참조를 사용하면서 좌표, 행 또는 열 하나만 수정해야 하는 경우가 많을 수 있다. 그런 경우 혼합 참조를 사용한다.

상대 참조로 계산(=A2*B1) 후 채우기 핸들을 끌어 내린 경우의 결과 값(좌측)과 그 변형식(우측)

위와 같은 형식은 각각 A2와 B1을 곱한 값, A2와 C1을 곱한 값, A2와 D1을 곱한 값 등 각각의 행과 열마다 다른 계산을 해줘야 한다. 이런 문제는 컴퓨터활용능력 필기뿐 아니라 실기 시험에서도 적용된다. 만일 상대 참조로 저 표를 계산했다면, 30*5가 어느샌가 150이 아닌30,000이 되버리게 된다...

그럼 모두 절대 참조로 하면 어떻게 될까?

절대 참조로 계산(=$A$2*$B$1) 후 채우기 핸들을 끌어 내린 경우의 결과 값(좌측)과 그 변형식(우측)

절대 참조로 함수를 만들면, 5*10도, 10*10도 5*30도 모두 50이 되어버리는 이상한 일이 일어난다!

 

지금껏 혼합 참조의 필요성 즉 상대 참조와 절대 참조의 한계에 대해서 이야기했다. 그럼 이제 위의 표를 계산하려면 어떻게 하면 되는지 알아보자.

혼합 참조로 계산(=$A2*B$1) 후 채우기 핸들을 끌어 내린 경우의 결과 값(좌측)과 그 변형식(우측)

 

예를 들어, 숫자 열(A열)에 3개의 다른 숫자(B1, C1, D1)를 곱하려면 다음 공식을 B2에 넣은 다음 아래로 복사하여 오른쪽에 놓습니다.

$A2에서 공식은 항상 열 A의 원래 숫자에 곱해야 하므로 열 좌표를 잠그니 절대 참조를 쓴다. 대신 행 좌표는 다른 행에 대해 변경해야 하므로 상대 참조를 쓴다.

B$1에서 항상 2행에 곱하는 수를 선택하기 위해 행 좌표를 잠그려 절대 참조를 쓴다. 곱하는 수가 3개의 다른 열에 있으므로 열 좌표는 상대 참조를 쓴다.

 

3. 워크시트 및 통합 문서 간 셀 참조 및 3차원 참조 수식 계산

3-1. 워크시트 및 통합 문서 간 셀 참조 수식 계산

	워크시트 간 셀 참조	통합 문서 간 셀 참조
다른 대상(워크시트, 통합 문서)에 있는 셀의 데이터를 참조할 경우	시트 이름과 셀 주소를 !(느낌표)로 구분 ex) =Sheet1!A1+Sheet2!A2	[](대괄호)로 통합 문서 이름을 묶음 ex) =A1+[매출현황표.xlsx]Sheet2!A1
다른 대상 이름에 한글, 영어 외 다른 문자나 공백이 있는 경우	''(작은따옴표)로 묶어줌 ex) =A1+'매출 현황'!A1	''(작은따옴표)로 묶어줌 ex) =A1+'[매출현황표.xlsx]매출 현황'!A1 =A1+'[매출 현황표.xlsx]매출현황'!A1

3-2. 3차원 참조(3D reference)

3차원 참조(3D reference, 3-D reference)는 여러 워크시드의 동일한 셀 또는 셀 범위에 대한 참조를 말한다.

참조하는 시트가 연속적으로 나열되어 있고 셀 주소를 한 번만 지정하면 된다. SUM(합계), AVERAGE(평균), COUNTA(입력 셀 갯수), STDEV(표준편차)와 같은 함수에서 사용할 수 있지만, 배열 수식에서는 사용할 수 없다.

4. 순환 참조 경고

순환 참조(circular reference)는 수식에서 직접 또는 간접적으로 수식이 입력된 그 셀을 그 수식에서 참조하는 경우를 말하며, 이런 경우 위와 같은 경고 메시지가 뜬다. 열려 있는 통합 문서 중 하나에 순환 참조가 있으면, 모든 통합 문서가 자동으로 계산되지 않는다는 특징이 있다.