엑셀을 하다보면 가장 많이 사용하는 기능이 바로 [자동 채우기] 기능인데요. 말 그대로 수식을 마우스로 드래그 앤 드롭해서 넣는 기능입니다. 간단하게 아래 그림만 보셔도 이해가 갈겁니다.
자동채우기 기능이 어떤건지 바로 이해가 되시죠? 셀의 우측하단 모서리를 마우스로 긁기만해도 수식이 복사되는 편리한 기능인데요. 이때 수식을 보시면 F5값이 'D5*E5'로 되어있고, F6값은 'D6*E6', F7수식은 'D7*E7'처럼 자동으로 한줄씩 밀리는데, 이것을 바로 상대참조라고 합니다.
그런데 이렇게 편리한 자동채우기 기능도 오류가 날 경우가 있는데요. 바로 절대참조때문입니다. 절대참조란것이 아주 편하기도하지만 주의해야할 필요도 있습니다. 그림으로 쉽게 이해해보겠습니다.
위 그림에서 $D$5라고 달러가 붙어있습니다. 이 수식을 한번 자동채우기 해볼까요?
어라? 매출액 수식에 오류가 발생했습니다. 위 그림에서 장난감D 부분만 보더라도 단가가 400원이고 판매수량이 1개인데, 매출액이 400원이 아닌 100원으로 나왔습니다. 그 이유가 바로 절대참조 때문인데요. 절대참조란 행이나 열, 또는 둘다를 고정시키는 역할을 합니다. 그리고 알기쉽게 달러($)를 붙여 표현을 하는데요. 그럼 절대참조는 언제 사용하는지 알아보겠습니다.
우리회사가 장난감을 만드는 회사라고 가정을 해봅시다. 위 그림을 보면 장난감A와 장난감B가 각각 5개, 2개 오더가 나왔는데요. 장난감A를 만들려면 머리 1개, 몸통 1개, 팔 2개, 다리 2개가 필요합니다.그럼 각각의 부품은 몇개씩 필요할지 필요량을 계산해야하는데요. 이를 쉽게 말해 B.O.M.이라고 하며, 소요량 계산을 한다고도 합니다. 그럼 예제로 풀어보겠습니다.
장난감 5개를 만드려면 머리는 5개 필요하니 수식에 'C5*D8'이라고 넣으면 됩니다.
이번엔 몸통과 팔, 다리도 계산을 해야하는데 하나하나 넣기 귀찮으므로 자동채우기 기능을 하고 싶습니다. 이럴때 장난감A 오더량인 C5만 고정시키면 자동채우기를 해도 쉽게 수식이 걸릴것 같죠? 그럼 C5를 절대참조로 바꾸면 됩니다. E8의 수식을 수정해 C5를 $C$5로 고쳐봅시다. 상대참조를 절대참조로 간단히 바꾸는 방법은 F4 단축키를 눌러주면 되니 하나하나 $를 붙일 필요는 없습니다.
그럼 자동채우기 기능을 사용하기위해 마우스로 한번 긁어볼까요? C5가 고정값이되면서 정상적으로 수식이 걸렸습니다. 참 간단하죠? 동일한 방법으로 장난감B도 적용하면 됩니다. 이제 상대참조와 절대참조의 개념을 아시겠죠? 말이 어려울뿐이지 달러를 붙여서 절대값을 만들수있다는 것만 아시면 되겠습니다.
마지막으로 혼합참조라는 것이 있는데, 상대참조와 절대참조를 섞어놓은거라 생각하면 됩니다. 이번에도 마찬가지로 간단히 예제로 설명해볼까요?
자, 위 그림처럼 동일한 제품을 고객사마다 다른 가격으로 판매하는 경우가 있는데요. 바로 업체별 인하율 때문입니다. 구매량이 많은 업체는 많이 인하를 해주고, 구매량이 적은 업체는 조금만 인하를 해주는 식인데요. 이럴 경우 위와 같은 표를 만들어서 관리할때가 있습니다. 그럼 E6 셀에는 어떤 수식을 걸어야할까요?
바로 'D6*(1-E5)'입니다. a업체는 5%인하를 해주기때문에 판매가에서 95%의 가격으로 단가 책정되는 것이죠. 그럼 이 수식을 활용하여 장난감A,B,C,D를 a,b,c,d업체에 대한 판매가를 한번에 구하고 싶습니다. 그럼 표를 이해를 해야하는데요.
판매단가가 D6, D7, D8, D9로 변하네요. 그럼 D열을 고정하고 행은 6, 7, 8, 9로 변하게 하면되겠네요. 열만 고정하고 행은 변하게 한다. 이게 바로 절대참조와 상대참조를 혼합한 혼합참조입니다. 어떻게하면 될까요? E6 셀수식의 D6를 $D6로 지정하면 됩니다. 한번 해볼까요?
자동채우기가 잘 적용되었습니다. 마찬가지로 장난감B,C,D를 구해보려니 할인율이 걸리네요. 기존 수식을 그대로 사용하면 할인율부분이 상대참조라서 수식에 오류가 발생합니다.
할인율은 E5, F5, G5, H5로 바뀌니 행을 고정하고 열을 변하게 만들면 됩니다. 다라서 E6셀수식의 E5를 E$5로 바꾸면 되는 것이죠.
그리고 E6셀 수식을 자동채우기나 복사해서 붙여넣으면 위 그림처럼 제대로 적용이 됩니다. 엑셀을 많이 사용하다보면 E6셀값을 'D6*(1-E5)'가 아니라 처음부터 '$D6*(1-E$5)'로 넣어야겠다는 것을 바로 알수있게되니, 조금씩 사용하다보면 금방 실력이 늘어날겁니다.
참고로 절대참조, 혼합참조를 사용하기위해 $를 하나하나 삽입할 필요는 없고, 수식에서 F4버튼을 누르면 됩니다. E6를 예로들면 F4 버튼을 한번씩 누를때마다 다음과 같이 변경됩니다.
E6 → $E$6 → E$6 → $E6 → E6
유용한 팁이죠? 이상으로 엑셀에서 사용하는 달러 ($)의 기능과 뜻에 대해 소개해드렸습니다. 카테고리 다른글에서 보다 많은 엑셀 꿀팁들을 확인해보세요!
