컴활 1급 독학 - (공부하면서 헷갈렸던 부분) 참조를 언제 사용할까

※ 작성한 모든 글은 제가 공부한 내용과 강의를 바탕으로 하며, 부정확한 부분이 있을수도 있습니다. 

 

 

 

1. 참조(상대/혼합/절대) 

특정한 값(1,2,3...)이 아니라 A1, B3 같이 특정한 셀에 해당하는 값을 사용하고 싶을 때 참조를 합니다.  수식을 자동으로 채워넣으면 참조한 셀의 주소가 변하게 됩니다.

 

=average(A1,B3) = average(4,10) = 7

	A	B
1	4
2
3		10

 

 

 

1) 상대참조

	A	B	C	D
1	=average(A1,B1)	=average(B1,C1)
2	=average(A2,B2)
3	=average(A3,B3)
4	=average(A4,B4)

 

A1 셀에 수식 =average(A1,B1)을 입력하고 A4셀까지 자동으로 채워넣으면, 위의 표와 같이 참조범위가 변하는 것을 볼 수 있습니다. 

 

아래로 한칸 이동하면 행(1,2,3,4...) +1, 오른쪽으로 한칸 이동하면 열(A,B,C,D...)+1이 됩니다.. A2는 A1이 아래로 한칸 이동했으니 A(1+1), B(1+1) 즉 A2,B2가 참조됩니다. 이것이 바로 상대참조입니다. 

 

 

 

2) 절대참조

여기서 셀주소의 행과 열앞에 $를 붙이게 되면 고정하겠다는 뜻이 됩니다. 셀의 수식을 다른 곳에 복사해도 참조한 셀의 주소가 변하지 않게 하겠다는 겁니다.

	A	B	C
1	=AVERAGE($A$1,B1)	=AVERAGE($A$1,C1)	=AVERAGE($A$1,D1)
2	=AVERAGE($A$1,B2)
3	=AVERAGE($A$1,B3)
4	=AVERAGE($A$1,B4)

 

위 표와 같이 셀 주소를 $로 고정해주면 옆으로 이동해도 아래로 이동해도 값이 변하지 않게 됩니다. 이것이 절대참조입니다.

 

 

3) 혼합참조(절대참조+상대참조)

그런데 행과 열 중에 하나만 고정하는 경우가 있습니다. 이를 혼합참조라고 합니다. 

	A	B	C
1	=AVERAGE(A$1,B1)	=AVERAGE(B$1,C1)	=AVERAGE(C$1,C1)
2	=AVERAGE(A$1,B2)
3	=AVERAGE(A$1,B3)
4	=AVERAGE(A$1,B4)

 

위 표와같이 $1 행만 고정된 상태에서는 아래칸으로 내려가는 경우 행값이 변하지 않고, 옆으로 이동할 때 열값만 변하는 것을 볼 수 있습니다. 

 

 

 

---

2. 언제 적용하느냐

그렇다면 여기서 중요한 건 대체 어떤 경우에 상대참조, 절대참조, 혼합참조를 적용하냐 하는 것입니다. 고급필터, 조건부서식, 유효성검사 등과 같은 기능에서 셀을 참조할 때 헷갈리는 부분이 있었기 때문에, 그 부분을 중심으로 정리해보려고 합니다. 

 

 

1) 고급필터

기본적으로 하나의 정보는 하나의 행을 의미합니다.  보통 A,B,C에는 나이, 몸무게, 키가 들어가고 1행이 김철수, 김영희와 같은 한 사람을 나타냅니다. 따라서 1행은 나이 30세, 몸무게 60kg, 키 155cm인 김철수라는 한 사람의 정보를 나타냅니다. 

	A(나이)	B(몸무게)	C(키)
1(김철수)	30	60	155
2(김영희)	40	50	150
3(김누구)	50	50	160

 

A1에서 C3까지 범위에서 키가 160 이상인 사람의 정보만 보고 싶을 때, 고급필터라는 기능을 사용하게 됩니다. 고급필터란 특정한 조건에 맞는 정보(행)만 필터링해서 보여주는 기능입니다. 

 

따라서 행을 기준으로 하는 고급필터에서 '=c1(김철수의 키)>=160' 을 조건으로 입력하면 자동으로 c2(김영희의 키),c3(김누구의 키)처럼 행만 바꿔가며 비교하게 됩니다.

 

c1(155)>=160 false

c2(150)>=160 false

c3(160)>=160 True

 

그리고 조건이 True인 김누구라는 정보만 보여주게 되는 겁니다. 그래서 고급필터에서는 상대참조가 가능합니다. 

 

 

---

2) 조건부 서식

문제는 조건부 서식입니다. 행만 고정할 것이냐, 열만 고정할 것이냐, 상대참조를 할 것이냐 고민하게 됩니다.

 

조건부 서식은

• 해당하는 모든 셀에 서식을 적용
• 해당하는 조건에 맞는 행 전체에 서식을 적용
• 해당하는 조건의 열 전체에 서식을 적용

위와 같이 세 가지 경우가 나옵니다. 그래서 고급필터처럼 자동으로 아래로 행을 바꿔가며 비교하지 않습니다.  참조의 종류에 따라 다르게 적용됩니다.

 

 

 

첫번째, 조건에 해당하는 모든 셀에  서식을 적용 : 상대참조

 

값이 70 이상인 A1:C3의 모든 셀에 서식을 적용하고 싶다면

조건식 :  A1(상대참조) >= 70 

 

상대참조이기 때문에 해당하는 범위에서 옆칸 아래칸 모두 이동하며

B1, C1, A2, B2, C2, A3, B3, C3 이렇게 모든 셀에 조건을 비교하고 서식을 적용합니다.

	A	B	C
1	70	60	20
2	40	80	30
3	50	50	100

 

 

 

두번째, 해당하는 조건에 맞는 행 전체에 서식적용 : 혼합참조(열고정)

 

열고정을 하게 되면 A,B,C는 고정되어 있으므로 행주소값만 변화하게 됩니다.

 $A1 -> $A1, $A2, $A3 셀값만 조건에 맞는지 확인 => 1행, 2행, 3행에 서식적용

조건식 :  $A1(열고정) >= 70 

 

 

 

세번째, 해당하는 조건에 맞는 열 전체에 서식적용 : 혼합참조(행고정)

 

행고정을 하게 되면 1,2,3은 고정되어 있으므로 열주소값만 변화하게 됩니다.

A$1 ->  A$1, B$1, C$1 셀값만 조건에 맞는지 확인 =>  A열, B열, C열에 서식적용

조건식 :  A$1(행고정) >= 70 

 

 

 

---

3) 유효성 검사(정확한 값만 입력되게 하는 기능)

 

 

첫번째, 하나의 열에만 유효성검사를 적용하는 경우

유효성 검사는 보통 하나의 열에만 적용하게 됩니다. 그래서 일반적으로는 상대참조를 사용합니다.

예시> A1 부터 A3까지의 영역(A열)에만 50이상의 값이 입력되게 하고싶은 경우

조건식 :  A1(상대참조) >= 50 

	A	B	C
1	70	60	20
2	40	80	30
3	50	50	100

 

 

두번째, 하나의 열을 다른 열과 비교하는 경우

예시> A에  B보다 큰 값만 입력되게 하는 경우

조건식 :  A1(상대참조) > B1(상대참조)

	A	B	C
1	70	60	20
2	40	80	30
3	50	50	100

조건에 A1과 B1이 다들어가더라도, 유효성검사가 적용되는 범위가 A1:A3으로 하나의 열이기 때문에 상대참조를 사용하면 됩니다. 

A1 : A1 > B1

A2:  A2 > B2

A3 : A3 > B3

 

만약 여기서 범위를 A1에서 B3으로 잡았다면

A1: A1>B1

B1: (A+1)1 > (B+1)1 즉, B1 > C1 이런 조건이 적용될 것입니다.

 

 

세번째, 두개 이상의 열에 유효성검사를 적용하는 경우

세번째 경우 조건범위가 열 두개인 경우에는 혼합참조가 필요합니다. 

	A	B	C
1	70	60	20
2	40	80	30
3	50	50	100

'A1 + B1 = 150'이라는 조건이 A1에도 B1에도 적용되어야 하는데

 

상대참조를 하게 되면

A1 : A1 + B1 = 150

B1 : B1 + C1 = 150

 

이경우에는 열고정을 해줘야 합니다. 경우에 따라 행고정을 하는 경우도 있습니다. 

 

 

 

* 유효성검사를 할 때 절대참조를 사용하는 경우도 있습니다. 예를 들면 A열의 합이 160이 되게 하는 경우 상대참조를 하게 된다면 참조범위가 바뀌기 때문에 절대참조를 해줘야 합니다. A열과 B열의 합이 500이 되는 경우에도 절대참조를 해야합니다.(이와 관련한 내용은 커미조아 선생님의 유효성검사 강의를 참고하시면 좋을 듯 합니다. https://www.youtube.com/watch?v=-8oY5kyadNw)

상대참조	절대참조
A1 : sum(A1:A3) =160 A2 : sum(A2:A4) =160 A3 : sum(A3:A5) =160	A1 : sum($A$1:$A$3) =160 A2 : sum($A$1:$A$3) =160 A3 : sum($A$1:$A$3) =160
상대참조	절대참조
A1 : sum(A1:B3) = 500 B1 : sum(B1:C3) = 500 A2 : sum(A2:A4) = 500 B2 : sum(B2:C4) = 500 A3 : sum(A3:A5) = 500 B3 : sum(B3:C5) = 500	A1 : sum($A$1:$B$3) = 500 B2 : sum($A$1:$B$3) = 500 A2 : sum($A$1:$B$3) = 500 B2 : sum($A$1:$B$3) = 500 A3 : sum($A$1:$B$3) = 500 B3 : sum($A$1:$B$3) = 500

 

 

 

그래서 정리하자면

고급필터 : 상대참조(열고정 해도 상관은 없음)

조건부서식 : 모든 셀(상대참조), 행 전체(열고정), 열 전체(행고정)

유효성검사 : 보통 상대참조(상황에 따라 절대참조, 혼합참조 하는 경우도 있음)