함수의 기본 - 상대참조, 절대참조, 혼합참조

지금부터 본격적으로 수식과 함수를 사용해서 엑셀로 계산해보는 시간을 갖도록 하겠습니다. 수식은 결과를 구할 셀을 먼저 선택하고 =을 입력한 다음 계산할 값이 들어있는 셀의 주소를 입력하면 됩니다. 일반적으로 수식은 실제 값을 입력해서 하기보다는 셀 주소를 사용해서 만들어지는데요. 셀 주소를 사용해서 수식을 작성하면 수식에 사용된 셀의 값이 변경되더라도 수식을 고쳐 쓸 필요가 없게 되기 때문에 매우 편리합니다. 셀의 주소의 값이 변하면서 자동으로 수식의 결과 값이 바뀌게 되니까요. 예를 들어 설명드리겠습니다. 서적 입고내역 데이터에서 저희는 입고금액을 구하려고 합니다. 입고금액은 단가와 수량을 곱한 값이죠. 자 그러면 금액을 구할 셀을 선택하고 =을 입력합니다. =을 입력하고 마우스로 단가 클릭, 그리고 곱하기 연산자인 별표를 누른 후에 수량 셀을 클릭합니다. 엔터를 치면 360만원이라는 금액이 구해졌구요. 여기서 우리 네모칸 우측 하단에 초록색 점이 있죠? 이 점을 드래그 해줍니다. 그럼 이 수식셀이 복사가 되어서 전체 자동으로 계산이 된 것을 볼 수가 있네요. 자 이번에는 수량셀의 아무 셀 쪽에 500으로 바꾸겠습니다. 그러면 우리는 이 수식을 바꾸지 않았음에도 불구하고 이 수량이 변한 것이 바로 반영이 된 것을 볼 수가 있습니다. 수량만 바꾸었는데 금액값도 같이 바뀌었죠. 여기서 중요한 개념이 등장합니다. 바로 참조라는 것인데요. 우리가 함수를 배우는 동안 참조라는 말을 굉장히 많이 들으실 거예요. 이 수식을 방금 아래로 드래그해서 복사했죠. 그래서 아래 셀들도 결과값도 바로 구해졌는데요. 이것이 바로 상대참조 원리에 의해서 반복되는 수식을 여러 번 작성하지 않아도 수식 복사만으로도 쉽고 빠르게 결과를 낼 수 있는 것입니다. C4 곱하기 D4였는데 내려오면서 C5 곱하기 D5가 되었고 그 다음 C6 곱하기 D6이 되었고 계속해서 반복되는 작업, 이렇게 상대참조 원리에 의해서 반복이 된 것이죠. 그렇다면 지금 참조가 된 참조셀은 어떤 것일까요? 여기선 바로 C4, D4, C5, D5 모두가 다 참조셀이라고 불리우게 됩니다. 그런데 이러한 참조도 상대참조, 절대참조, 혼합참조 등으로 나누게 되는데요. 갑자기 어려운 용어가 튀어나왔죠? 쉽게 설명드리겠습니다. 처음 수식에서 참조한 셀 C4가 입력되어 있었습니다. 그리고 이 셀을 아래로 드래그해보면 아까 우리 봤듯이 C5가 되고 C6이 되고 했었습니다. 셀의 위치에 따라 그 참조셀이 바뀌는 이러한 참조를 상대참조라고 부릅니다. 이때 필요에 따라 F4를 눌러서 상대참조를 절대참조 혹은 혼합참조로 바꾸어 사용할 수 있는데요. 자 제가 여기서 C4에 커서를 놓고 F4를 눌러 보겠습니다. 그러면 달러 표시가 C와 4 모두 앞에 붙어 있죠. 이게 바로 절대참조 주소입니다. 그리고 이렇게 절대참조 주소로 바뀌어진 이 수식을 밑으로 다시 복사해 보겠습니다. 값이 바뀌었습니다. 자 컨트롤 물결을 눌러서 한번 수식을 확인해 봤습니다. 아까는 분명 C5 곱하기 D5였고 C6 곱하기 D6이었는데 지금은 D만 바뀌고 C4는 전혀 변하지 않았습니다. 이게 바로 절대참조의 원리인데요. 밑으로 옆으로 아무리 드래그 해보아도 이 C4는 다른 주소로 바뀌지 않습니다. 절대로 그 셀 값만으로 계산을 하게 됩니다. 다시 한번 이 C4에 커서를 놓고 F4를 눌러보겠습니다. 이번엔 4에만 달러 표시가 붙었습니다. 이것은 4행은 고정이겠다고, C열만 바뀔 거다. 한번 이 셀을 옆으로 복사해 보겠습니다. 밑으로도 가보고 했는데 옆으로 갔더니 D4가 되었습니다. 4는 변하지 않았습니다. 밑으로 간 걸 확인했더니 C의 달러 표시가 없지만 C가 변하지 않았으므로 열이 변하지 않았으므로 그대로 C이고 4는 5가 되어야 하는데 5가 되지 않고 4로 그대로 고정되어 있는 모습을 볼 수가 있습니다. 이것은 행이 고정이라는 뜻입니다. 이번에는 C4에 커서를 놓고 다시 한번 F4를 눌러보겠습니다. 이번엔 C 앞에만 달러가 붙었네요. 이 셀을 다시 한번 옆으로 그리고 밑으로 복사해보겠습니다. 옆으로 간 이 셀, 아까는 D4였는데 이번에는 C는 고정 4는 내려가지 않았으니 바뀌지 않았죠. 여기를 보시면 C 고정, 이번에는 숫자가 바뀌었네요. 내려갔다고 5로 바뀌었습니다. 이건 열을 고정하라는 의미가 되겠네요. 아마 지금은 다 이해되지 않으실 텐데 함수를 공부하면서 차근차근 이해되실 거예요. 수식 작성을 익히는데 있어서 첫 고비가 바로 이 절대참조인데요. 절대참조는 항상 특정 위치의 셀을 가리킵니다. 수식을 작성하고 수식 셀을 복사하더라도 절대참조는 셀 주소가 절대 바뀌지 않고 그대로 유지됩니다. 한 번 더 강조하면 수식을 행이나 열, 어느 방향으로 복사해도 절대참조의 셀은 바뀌지 않습니다. 예제를 통해 알아보겠습니다. 우리 여기서 구성비를 한번 구해볼게요. 구성비를 구하는 식은 금액 나누기 전체 금액의 합계입니다. 먼저 구성비를 구할 셀을 선택하고 =을 입력합니다. 그리고 첫번째 금액 셀을 클릭하고 나누기 연산자인 슬래시 입력 후에 전체 금액의 합계인 E20 셀을 입력합니다. 엔터를 치고 결과를 확인합니다. 결과가 소수로 나왔네요. 홈의 표시 형식 탭에 %를 클릭하면 이렇게 백분율 표시로 변경이 됩니다. 이 수식을 나머지 밑에 셀들로 복사해 볼까요? 드래그. 어, 그런데 오류가 생겼네요. 이 오류는 나눗셈이 잘못되었다는 오류로, 어떤 값이든 0으로 나누면 오류라는 값이 나온다는 의미에요. 다시 말하자면 참조하는 셀의 값이 0이라는, 즉 없다는 뜻입니다. 오류가 생긴 수식을 더블클릭 하면요 수식을 수정할 수 있는 상태가 되는데요. 구성비를 구하는 식은 분명히 금액 나누기 전체 합계였습니다. 수식을 복사해서 구성비 수식이 복사된 위치만큼 지금 참조 셀이 이동된 것을 알 수가 있죠. 그래서 E11이 되어버린 겁니다. 자, 지금 빨간색 네모칸으로 되어있는 곳이 바로 E11인데요. 아무것도 없습니다. 이 참조 셀이 움직이지 않도록 우리가 고정을 해 줘야겠습니다. 다시 한번 맨 처음 구성비 셀로 돌아가서 E10에 커서를 놓고 F4를 한번 눌러줍니다. 그러면 열과 행 모두에 절대참조의 코드인 이 달러 표시가 붙을 거구요. 엔터를 치고 다시 한번 드래그해서 수식을 복사해보겠습니다. 정상적으로 결과가 나온 것을 볼 수 있습니다. 절대참조 방식을 이해하셨다면 혼합참조 방식도 익혀볼 수 있습니다. 이번엔 각 상품의 할인율을 통해 세일가를 구해보려는 건데요. 각 10%, 20%, 30% 할인을 하고 있네요. 세일가를 구하는 공식은 금액 곱하기 1 - 할인율입니다. 0.2, 0.3 이런 식으로, 우리 초등학교 때 수학 배웠을 때 생각나시죠? 먼저 D3 셀에 커서를 두고 =을 입력합니다. 금액 곱하기 괄호 치고 1 - 10%라고 되어있는 셀을 클릭하시고 괄호를 닫고 엔터를 칩니다. 좋습니다. 이렇게 10%의 할인 금액이 구해졌는데요. 나머지 상품들의 할인 금액을 구하기 위해 이 수식을 복사해 보겠습니다. 굉장히 이상한 결과가 나왔습니다. 문학서적의 10% 구하는 셀을 보면요, 참조된 셀이 조금 이상합니다. 10%를 뺀 값을 곱해야 하는데 지금 D3이라면 이 부분이 여기에 들어가 있네요. 다시 한번 보겠습니다. 이 부분을 어떻게 처리하면 될까요? 먼저 첫번째 셀로 가서 이 D2 값을 절대참조 씌워주면 될까요? 한번 씌워보겠습니다. 절대참조를 씌우고 복사를 하니 정상적인 결과값이 나왔습니다. 모두 D2 셀이 고정되어 있죠. 자 그럼 이거를 20%, 30%에도 적용을 해보기 위해 복사를 합니다. 그런데 나온 결과값이 너무 심하게 차이가 나는 것을 볼 수 있습니다. 한번 수식을 보았는데요. 지금 에세이 원래값인 C3이 아니라 D3에 곱해져 있고, 1 - 10%가 되어있네요. 20%로 되어있지 않고. 이럴 경우에는 절대참조도 아닌 혼합참조를 써야 합니다. 이 오류를 해결하기 위해서 이 C3 셀의 열이 변하지 않도록, 움직이더라도 열이 변하지 않도록 C에 절대참조 코드를 씌워줘야 하고요. 우리 할인율은 움직여야 하니까 열은 고정을 하면 안 되겠네요. 행만 고정되도록 달러 표시 하나를 없애줬습니다. 엔터를 누르고 다시 한번 밑으로, 그리고 옆으로. 결과값이 잘 계산이 된 것인지 확인해 보겠습니다. C3 셀이 참조되었고 20%가 참조되었으니 정상적으로 계산이 된 것이 맞네요.