엑셀 수식편(참조란? 활용)

안녕하세요. 봉자씨입니다.  

 

저번 강의에 이어서 이번 강의에서는 참조와 주소에 대해서 배운 부분을 한번 활용하는 간단한 예제를 다뤄 보도록 하겠습니다. 전 시간에 이야기 했던 절대 상대 혼합 참조는 꼭 이해하고 넘어 가야 한다고 제가 말씀을 드렸습니다. 이 것을 이해하고 나면 앞으로 엑셀에 큰 산을 하나 넘었다고 생각하시면 됩니다. 이제 그 이해를 확고히 하기 위해서 간단한 예를 들어서 이전에 배웠던 부분을 복습해 보도록 하겠습니다.

아주 간단한 표입니다. 여러분 이제 저기 보이는 학점합계를 구하도록 하겠습니다. 첫 번째 라인의 20081021의 사번에 있는 사람의 학점은 4+3+3 = 10학점이 됩니다. 그럼 저기에 학점 합계란에 무엇을 써야 할 가요? 간단하게 합을 구해 주는 함수가 있습니다. SUM 이라는 함수입니다. 

 = SUM(더할값1, 더할값2, 더할값3) 또는 = SUM(더할 범위)

SUM함수는 이런 식으로 사용을 하게 됩니다.

그럼 한번 저기 위에다가 한번 써보세요 어디다 어떻게 무엇을 써야 할 가요? E4셀에다가 이렇게 적으면 됩니다.

=SUM(B4:D4)

이렇게 적으시면 됩니다. 이 방법은 앞에서 이야기 했던 참조의 방식으로 바라보면 상대 참조 형식입니다. 이 식을 사용하게 되면 복사하거나 이동하게 되면 수식에 적혀있는 주소도 같이 움직인다고 했습니다. 한번 복사를 해보겠습니다. ctrl+c 그리고 나서 아래 부분에다가(E5) ctrl+v를 해보겠습니다. 

결과는 =SUM(B4:D4) ▷ =SUM(B5:D5)

수식 안에 참조값(주소)이 복사를 위해서 이동한 만큼 변경이 되었습니다. 아래로 한 칸 이동 한 것처럼 이 복사한 수식의 참조값도 아래로 한 칸 이동을 하게 된 것입니다. 아래로 계속 복사를 해도 똑 같은 일이 벌어 지게 되고 바로 이것이 상대 참조입니다.

  

이제 상대참조를 해봤으니 혼합 참조를 한번 보도록 하겠습니다. E4에 있는 수식을 한번 사용해서 인정학점 포함에 대해서 구하고 싶습니다. 어떻게 해야 하면 좋을까요? 똑같이 E4에서 복사를 해서 F4로 이동을 시켜서 붙여 넣기를 해봤습니다. 어떻게 되나요? 수식은 어떻게 변하나요?

=SUM(B4:D4) ▷ =SUM(C4:E4)

그런데 우리가 의도한 것은 이게 아닙니다. 옆으로 이동해도 주소의 식이 안 바뀌었으면 합니다.

그렇게 하려고 하면 어떻게 할 가요? E4에 가서 F4(function key)를 누르시면 됩니다. 그럼 수식이 어떻게 변하는지 한번 보도록 하겠습니다.

=SUM($B$4:$D$4)  ▷ =SUM(B$4:D$4) 

  ▷ =SUM($B4:$D4) ▷ =SUM(B4:D4)

=절대참조 ▷ 혼합참조(열고정) 

  ▷ 혼합참조(행고정) ▷ 상대참조

이렇게 변했습니다. 그럼 저희가 필요한 것은 행을 고정 시킨 혼합 참조입니다. 그래서 F4를 3번 눌러서  =SUM($B4:$D4) 이렇게 바꾸면 되고 이 상태에서 엔터를 누릅니다.

그리고 이제 그 식을 복사해서 아까와 똑같이 F4에 복사를 해보겠습니다.

=SUM($B4:$D4) ▷ =SUM($B4:$D4)

그리고 수식을 복사해 보겠습니다. 수식의 변화가 없습니다. 왜 일까요? 저희는 지금 행을 고정한 상태에서 행을 이동했습니다. 그래서 행이 바뀌지 않은 겁니다. 우리가 고정을 했기 때문이죠. 이제 그 상태에서 뒤에 다 인정학점을 더해 보겠습니다. 2점을 더해 주도록 하겠습니다.

그래서 수식은 =SUM($B4:$D4)+2

이렇게 완성이 됩니다. 이제 이 상태에서 아래로 쭉 복사를 해보겠습니다. 우리가 원하는 수식으로 복사가 되는 것을 확인 할 수 있습니다.

 

아직 끝난 것이 아닙니다. 우리는 엑셀에 나와 있는 인정학점(B2)을 활용해보도록 하겠습니다. 그 이유는 만약 우리가 인정학점을 사용한다면 학점이 2점에서 5점으로 바뀐다고 하더라도 수식을 수정하지 않아도 되기 때문입니다. 만약 +2를 입력해 놓은 상태라면 여러분들은 지금의 저 수식을 모조리 2에서 5로 바꿔야합니다. 너무 힘이 들겠죠? 머 한 두개의 식이니 상관 없어라고 생각하는 분들도 아마 있을 것입니다. 하지만 이 수식이 3000개라면 아니 그 이상이라면 여러분은 그래도 하나씩 고칠 수 있을 지에 대해선 호불호가 나뉠 것 같습니다. 그래서 저 수식을 B2의 점수를 참고하는 수식으로 바꿔보겠습니다. F4의 수식을 한번 수정해 보겠습니다. 그 수식에 가서 F2키를 눌러보겠습니다. 수식이 수정이 가능한 상태가 됩니다. 

이 상태에서 이제 해야 할 일은 

=SUM($B4:$D4)+B2 이렇게 수식을 바꾸는 것입니다.

바꾸셨다면 이제 수식을 아래로 계속 복사해 보도록 하겠습니다. 복사를 하니 B2가 계속 변합니다.이럴 때는 어떻게 하면 좋을 가요? 절대 참조로 B2를 바꾸는 것입니다. 그래서 수식에서 B2를 드래그 해서 선택하고 (수식에서 선택) 그리고 나서 F4키를 한번 눌러보겠습니다.

그럼 수식이 =SUM($B4:$D4)+ $B$2

이렇게 바뀌는 순간이 오게 됩니다. 이렇게 되면 엔터를 누르고 이제 그 수식을 아래로 쭉 복사를 해보겠습니다.B2가 변하지 않고 쭉 복사가 되죠? 바로 이것이 절대 참조입니다.

 

아주 간단하지만 꼭 알아야 하는 절대 상대 혼합 참조입니다.

참조에 대해서 이해하기 위해서 가장 좋은 예제는 아래의 예제입니다.

구구단을 한번 한 개의 수식을 써서 복사해서 모두 값이 나오도록 한번 해보세요

단, 한 개의 수식 만을 입력해서 그것을 복사해서 모두의 값이 나와야 합니다.

글이 조금이나마 도움이 되셨다면 공감 부탁드립니다.

오늘도 좋은 하루 되세요!

감사합니다.

클릭 클릭 공감 클릭~~