엑셀 참조의 개념 및 혼합참조

상대참조/ 절대참조 /혼합참조

---

이번시간에는 혼합참조를 사용하여 할인 가격을 계산하기에 앞서, 참조의 개념과 종류 알아보고 각각의 참조를 이용해서 할인율을 계산해 보겠습니다.  

 

참조를 이용해 수식을 한 개만 작성하면 "채우기 핸들"로 드래그하면 나머지 셀에도 수식이 채워지는데, 이때 수식의 셀주소가 상대적으로  한 칸씩 바뀝니다. 이를 상대 참조라고 하는데, 즉 채우기 핸들로 드래그 한 위치에 따라 상대적으로 셀주소가 바뀌는 것입니다. 이렇게 되면 수식을 제대로 넣어도 행과 열이 고정되지 않음에 따라 채우기 핸들을 이용한 자동계산이 되지 않고, 엉뚱한 계산이 이루어지게 됩니다. 수식에 H5 셀을 참조한다고 했을 때, 수식시작기호인 " = "을 적고 H5 셀을 클릭하면 그대로 "상대참조"가 되어 이후 드래그를 해서 자동계산할 경우 셀주소가 한 칸씩 상대적으로 변하게 됩니다.

 

H5를 클릭하고 F4 를 한번 눌러 $H$5 식으로 수식을 만들면 "절대참조" 되어 드래그를 해도 셀주소가 변하지 않습니다.

 

H5를 클릭후 F4를 두 번 누르면 H$5로 수식이 만들어지고, 5행만 고정되는 "혼합참조"가 됩니다.

 

H5를 클릭후 F4를 세 번 누르면 $H5로 수식이 만들어지고 H열만 고정되는 "혼합참조"가 됩니다. 여기서 알 수 있듯이 F4키를 누르는 횟수에 따라 고정되는 행과 열이 바뀌게 되고 $ 바로 뒤의 행이나 열이 고정된다고 이해하시면 됩니다.

 

H5 : 상대참조 

$H$5: H5 셀이 고정 (절대참조,F4 한번 타이핑 ) 

H$5: 5행만 고정 (혼합참조, F4 두번 타이핑 ) 

$H5: H열만 고정 (혼합참조, F4 세번 타이핑 )

 

상대 참조를 이용한 할인가격 계산 

---

아래의 표가 있습니다.  C열은 판매가이고,  3행은 각각의 할인율이 적혀 있습니다. 10000 짜리를 20% 할인하는 것을 수식으로 나타내보면 10000*(1-20%) =8000입니다. 이것을 혼합참조를 하지 않고 그저 상대참조를 이용해서 계산하면 아래와 같이 잘못된 계산이 나옵니다.  

 

상대참조

절대 참조를 이용한 할인가격 계산 

 

  절대참조를 이용한다면 후에 설명드릴 혼합참조 보다는 식을  3개 만들어야 해서 번거롭지만 할인율이 적혀 있는 D3와 E3 , F3,  세 개의 셀을 각각 모두 절대 참조하여도 계산이 가능합니다.   즉 할인율인 20% , 30% , 40 % 가 적혀 있는 셀을 각각 절대 참조해서 D4, E4, F4 각각의 셀에 총 3개의 수식[D4 =C4*(1-$D$3) /  E4 =C4*(1-$E$3) /F4 =C4*(1-$F$3) ] 을 적어 아래 품목들은 자동 계산 되게 만들어 줍니다.

 

 

마지막으로 제대로된 엑셀 수식을 만들기 위해서는 상대 참조나 절대참조를 사용하지 않고,  할인율이 적혀있는 3행을 고정하고 판매가가 적혀 있는 C열을 고정한 혼합참조를 사용해야 합니다. 그럼 수식 1개만으로도 위 표를 모두 완성시킬 수입니다. =$C4*(1-D$3)

 

 

글을마치며

---

오늘은 엑셀 참조의 종류와 각각의 참조를 이용해서 계산을 해보았습니다. 앞서 설명드렸던 것처럼  각각의 참조를 표의 종류에 따라 적용하여, 계산해야지만 올바른 계산이 이루어집니다. 많은 연습을 통해 실무에서 사용되는 표들에 어떤 참조를 적용해야 할지 판단하는 눈을 만듬과 동시에  각 참조의 개념을 숙지하시어, 실무에 적용하도록 해야겠습니다.