엑셀 셀 참조방식 완벽정리

강의 요약

실무에서 넓은 범위에 많은 수식을 빠르게 입력하는 것은 작업 시간을 단축하는 데 매우 중요한 부분입니다. 특히 동일한 형태의 수식을 넓은 범위에 빠르게 입력하려면 셀의 참조방식을 정확히 이해하고 활용하는 것이 매우 중요합니다. 이번 강의에서는 '셀 참조방식'의 기초 원리부터 응용, 그리고 실전 예제까지 셀 참조방식의 모든 것을 단계별로 살펴봅니다.

엑셀 셀 참조방식은 '$(달러기호)'로 결정됩니다. 그리고 달러($) 기호가 어디에 붙는지에 따라 '상대참조', '절대참조', '혼합참조'로 구분되며, 각 참조방식은 아래와 같습니다.

• 상대참조 : 셀이 이동할 때 참조되는 셀 주소도 함께 이동하는 셀 참조방식입니다.
• 절대참조 : 셀이 이동해도 참조되는 셀 주소가 항상 고정되는 셀 참조방식입니다.
• 혼합참조 : $(달러기호)가 붙은 위치에 따라 행 또는 열 중 하나만 고정되는 셀 참조방식입니다.

상대참조와 절대참조를 언제 사용하는지 알아보기 위해, 각 제품별 판매 가격과 판매 비율을 구하는 예제를 살펴보겠습니다.

제품별 판매 가격 구하기 (상대참조 예제)

1. 예제파일 '2' 시트로 이동한 뒤, 가격의 첫 번째 셀인 E4 셀을 선택합니다. 이후 셀에 [수량 * 단가]를 입력하여 값을 계산합니다.
2. 마우스 커서를 셀의 오른쪽 아래로 이동하여 '검은색 십자가'로 만든 뒤, 아래로 드래그하여 수식을 자동 채우기합니다.
3. 각 제품별 판매 가격이 계산되었습니다.
4. '수식' - '수식 표시'를 클릭하거나 단축키 Ctrl+ ~를 눌러 사용된 수식을 표시합니다.
5. 셀을 자동 채우기 하면서 참조된 셀이 함께 이동한 것을 확인할 수 있습니다. 이러한 참조방식을 '상대참조'라고 합니다.
6. 다시 '수식 표시'를 해제한 뒤, '합계' 셀을 선택합니다. '홈' - '자동합계'를 클릭하거나 단축키 Alt + =를 입력하면 제품의 가격 합계가 계산됩니다.

제품 판매 비율 구하기 (절대참조 예제)

1. 비율 첫 번째 셀인 F4 셀을 선택한 후, [가격 / 합계]로 값을 계산합니다.
2. 이전과 동일하게 수식을 아래로 자동 채우기 합니다. 가격을 계산할 때와 달리 #DIV/0! (0으로 나뉨 오류)이 출력됩니다.
3. '수식' - '수식 표시'를 클릭하거나 단축키 Ctrl + ~를 눌러 사용된 수식을 표시합니다. 이후 비율의 첫 번째 셀을 클릭하면 '합계'가 올바르게 입력된 것을 확인할 수 있습니다.
4. 하지만 두 번째, 세 번째 셀을 클릭하면 '합계' 셀이 아래로 이동되면서 올바르지 않은 값이 분모로 참조된 것을 확인할 수 있습니다.
5. 수식 표시를 해제한 뒤, 비율의 첫 번째 셀로 이동합니다. 합계 셀(E12)을 선택한 후 키보드 F4키를 눌러 절대참조로 변경합니다.
6. 수식을 다시 자동 채우기 하면 비율이 올바르게 계산됩니다.

판매수량 별 커미션 구하기 (상대참조 예제)

혼합참조는 상대참조나 절대참조에 비해 다소 어렵게 느껴질 수 있는 셀 참조방식입니다. 하지만 원리만 이해하면 여러 개의 수식을 빠르게 입력하는 데 매우 유용합니다.

1. 혼합참조를 알아보기 위해, 판매량 대비 커미션 테이블 예제를 살펴보겠습니다. 예제파일의 4번 시트로 이동합니다.
   
2. 판매량 대비 커미션을 구할 때는, 첫 번째 셀에 수식을 입력한 뒤 오른쪽 및 아래 방향으로 수식을 자동 채우기하게 됩니다.
3. 수식을 자동 채우기 할 때, 표 상단에 위치한 '커미션' 값은 오른쪽으로는 이동하되 아래쪽으로는 이동하면 안 되는 값입니다. (숫자(=행)를 고정합니다.)
4. 또한 표 좌측에 위치한 '판매량' 값은 아래쪽으로는 이동하되 오른쪽으로는 이동하면 안 되는 값입니다. (알파벳(=열)을 고정합니다.)
5. 따라서 판매량 대비 커미션을 구하기 위해 표의 첫 번째 셀에 아래와 같이 수식을 입력합니다.
6. 이후 수식을 자동 채우기 하면 판매량 대비 커미션이 손쉽게 계산됩니다.

참조방식 실전 활용 (평균 구하기)

1. 셀 참조방식 실전 예제로 학생들의 차수별 시험 성적 평균을 계산해보겠습니다. 예제파일의 5번 페이지로 이동합니다.
2. 차수별 평균 성적을 계산하기 위해 '조건을 만족하는 평균 계산'에 사용되는 AVERAGEIF 함수를 사용합니다. AVERAGEIF 함수의 구문은 아래와 같습니다.
   = AVERAGEIF ( 조건범위, 조건, [평균범위] )
3. 첫 번째 인수인 '조건범위'에 '차수범위'를 입력합니다. 이 차수범위는 수식이 자동 채우기 될 때 이동하지 않고 항상 고정되어야 하는 범위이므로, 절대참조로 입력합니다.
4. 두 번째 인수인 '조건'으로 각 '차수'를 입력합니다. 이 차수는 오른쪽으로는 이동하면 안 되고 아래로만 이동해야 하므로, 알파벳(=열) 앞에만 $(달러표시)가 붙도록 셀 참조방식을 변경합니다.
5. 마지막 인수인 '평균범위'에는 각 '필기/실기 점수' 범위를 입력합니다. 이 범위는 오른쪽으로만 이동하고 아래로는 이동하면 안 되는 범위이므로, 숫자(=행) 앞에만 $(달러표시)가 붙도록 셀 참조방식을 변경합니다.
6. 따라서 차수별 시험 평균 성적을 구하기 위한 AVERAGEIF 함수 수식은 아래와 같이 입력됩니다. 이후 수식을 자동 채우기 하면 각 차수별 시험 평균 성적 계산이 완료됩니다.
   =AVERAGEIF($C$4:$C$14,$C15,D$4:D$14)