엑셀 셀 참조방식의 모든것 | 상대참조 vs 절대참조 vs 혼합참조 총정리
엑셀 셀 참조방식 목차 바록가기
강의 요약
실무에서 넓은 범위에 많은 수식을 빠르게 입력하는 것은 작업시간 단축에 매우 중요한 부분 중 하나입니다. 특히 동일한 형태의 수식을 넓은 범위에 빠르게 입력하려면, 셀의 참조방식을 잘 이해하고 사용하는 것이 아주 중요한데요. 이번 강의에서는 '셀 참조방식'의 기초부터 응용, 그리고 실전 예제까지 셀 참조방식의 모든것을 단계별로 살펴봅니다.
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [기초입문] 엑셀 셀 참조방식 총정리예제파일
엑셀 셀 참조방식은 '$(달러기호)'로 결정됩니다. 그리고 달러($) 기호가 어디에 붙는지에 따라 '상대참조', '절대참조', '혼합참조'로 구분되며 각 참조방식은 아래와 같습니다. 상대참조 및 절대참조, 혼합참조에 대한 더욱 자세한 설명 및 기초 이론은 아래 상대참조 vs 절대참조 완벽비교 포스트를 참고하세요.
- 상대참조 : 셀이 이동할 때 참조되는 셀 주소도 같이 이동하는 셀 참조방식입니다.
상대참조는 셀이 이동할 때 참조되는 셀 주소도 같이 이동하는 참조방식입니다. - 절대참조 : 셀이 이동할 때 참조되는 셀 주소는 항상 고정되는 셀 참조방식입니다.
절대참조는 참조되는 셀 주소가 항상 고정되는 참조방식입니다. - 혼합참조 : $(달러기호)가 붙은 위치에 따라 행 또는 열 중에 하나만 고정되는 셀 참조방식입니다.
혼합참조는 행 또는 열 중 하나만 고정하는 참조방식입니다.
상대참조와 절대참조를 언제 사용하는지 알아보기 위해 각 제품별 판매가격와 판매비율을 구하는 예제를 살펴보겠습니다.
1. 제품별 판매 가격 구하기 (상대참조 예제)
- 예제파일 '2' 시트로 이동한 뒤, 가격의 첫번째 셀인 E4 셀을 선택합니다. 이후 셀에 [수량 * 단가]를 입력하여 값을 계산합니다.
수량 * 단가로 제품별 판매가격을 계산합니다. - 마우스 커서를 셀의 오른쪽 아래로 이동하여 '검은색 십자가'로 만든 뒤, 아래로 드래그하여 수식을 자동채우기 합니다.
마우스 커서를 셀의 오른쪽 아래로 이동한 뒤, 값을 아래로 자동채우기합니다. - 각 제품별 판매 가격이 계산되었습니다.
제품별 판매가격이 계산 되었습니다. - '수식' - '수식 표시'를 클릭하거나, 단축키 Ctrl+ ~ 를 눌러 사용된 수식을 표시합니다.
'수식' - '수식표시'를 클릭하여 각 셀에 사용된 수식을 표시합니다. - 셀 자동채우기를 하면서 참조된 셀이 같이 이동 된 것을 볼 수 있습니다. 이러한 참조방식을 '상대참조' 라고 합니다.
셀이 이동하면서 참조된 셀 주소도 같이 이동된 것을 확인할 수 있습니다. - 다시 '수식표시'를 해제한 뒤, '합계' 셀을 선택합니다. '홈' - '자동합계'를 클릭하거나, 단축키 Alt + =를 입력하면 제품의 가격 합계가 계산됩니다.
제품별 판매가격의 합계를 계산합니다.
2. 제품 판매 비율 구하기 (절대참조 예제)
- 비율 첫번째 셀인 F4셀을 선택한 후, [가격 / 합계] 로 값을 계산합니다.
각 제품별 판매 비율을 계산합니다. - 이전과 동일하게 수식을 아래로 자동채우기 합니다. 가격을 계산할 때와는 다르게 #DIV/0! (0으로 나눠짐 오류)가 출력됩니다.
수식을 아래로 자동채우기 하면 #DIV/0! 오류가 출력됩니다. - '수식' - '수식표시'를 클릭하거나, 단축키 Ctrl + ~ 를 눌러 사용된 수식을 표시합니다. 이후 비율의 첫번째 셀을 클릭하면 '합계'가 올바르게 입력된 것을 볼 수 있습니다.
수식표시를 한 뒤, 첫번째 셀을 클릭하면 합계가 올바르게 참조 된 것을 볼 수 있습니다. - 하지만 두번째, 세번째 셀을 클릭하면 '합계'셀이 아래로 이동되면서 올바르지 않은 값이 분모로 참조된 것을 볼 수 있습니다.
두번째 셀 이후부터는 합계셀이 아래로 이동하면서 옳지 않은 값이 참조된 것을 볼 수 있습니다. - 수식표시를 해제한 뒤, 비율의 첫번째 셀로 이동합니다. 합계셀(E12)를 선택 한 후, 키보드 F4키를 눌러 절대참조로 변경합니다.
합계셀을 선택한 뒤, 키보드 F4키를 눌러 절대참조로 변경합니다. - 수식을 다시 자동채우기 하면 비율이 올바르게 계산됩니다.
수식을 아래로 자동채우기 하면 각 제품별 판매 비율이 올바르게 계산됩니다.
혼합참조는 상대참조나 절대참조에 비해 다소 어렵다고 생각 될 수 있는 셀 참조방식입니다. 하지만 원리만 이해하면 여러개의 수식을 빠르게 입력하는데에 아주 큰 도움이 됩니다. 혼합참조를 알아보기 위해 각 판매량 대비 커미션 테이블을 예제로 살펴보겠습니다. 예제파일의 4번 시트로 이동합니다.
판매량 대비 커미션 표를 예제로 혼합참조 사용법을 알아봅니다. 판매량 대비 커미션을 구할 경우, 첫번째 셀에 수식을 입력한 뒤, 오른쪽 및 아래 방향으로 수식을 자동채우기 하게 됩니다.
입력한 수식은 오른쪽 및 아래 방향으로 자동채우기 됩니다. 수식을 자동채우기 할 때, 표 상단에 위치한'커미션' 값은 오른쪽으로는 이동하되 아래방향으로는 이동하면 안되는 값 입니다. (숫자(=행)를 고정합니다.)
커미션 범위는 '아래방향'으로는 이동하면 안 됩니다. (숫자(=행)을 고정합니다) 또한 표 좌측에 위치한 '판매량' 값은 아래로는 이동하되 오른쪽으로는 이동하는 안되는 값이 됩니다.(알파벳(=열)을 고정합니다.)
판매량은 오른쪽으로 이동하면 안됩니다. (알파벳(=열)을 고정합니다) 따라서, 판매량 대비 커미션을 구하기 위해 표의 첫번째 셀에 아래와 같이 수식을 입력합니다.
위와 같이 셀 참조방식 (혼합참조) 을 변경하여 수식을 입력합니다. 이후 수식을 자동채우기 하면 판매량 대비 커미션이 쉽게 계산됩니다.
수식을 자동채우기 하면 판매량 대비 커미션이 한번에 계산됩니다. 셀 참조방식 실전예제로 학생들의 차수별 시험성적 평균을 계산해보겠습니다. 에제파일의 5 페이지로 이동합니다.
셀 참조방식 실전 예제로 학생들의 차수별 시험 성적 평균을 계산합니다. 차수별 평균 성적 계산을 위해 '조건을 만족하는 평균 계산'에 사용되는 AVERAGEIF 함수를 사용합니다. AVERAGEIF 함수 구문은 아래와 같습니다.
= AVERAGEIF ( 조건범위, 조건, [평균범위] )- 첫번째 인수인 '조건범위'로 '차수범위'를 입력합니다. 이 차수범위는 수식이 자동채우기 될 때 이동되지 않고 항상 고정되어야 하는 범위입니다. 따라서 절대참조로 입력합니다.
차수범위는 자동채우기와 상관없이 항상 고정되어야 하는 범위입니다. - 두번째 인수인 '조건'으로 각 '차수'를 입력합니다. 이 차수는 오른쪽으로는 이동하면 안되고, 아래로만 이동되어야 합니다. 따라서 알파벳(=열) 앞에만 $(달러표시)가 붙도록 셀 참조방식을 변경합니다.
조건으로 입력되는 차수는 '아래방향'으로만 이동되어야 합니다. (알파벳(=열)을 고정합니다) - 마지막 인수인 '평균범위'는 각 '필기/실기점수' 범위가 입력됩니다. 이 범위는 오른쪽으로만 이동하고, 아래로는 이동하면 안되는 범위입니다. 따라서 숫자(=행) 앞에만 $(달러표시)가 붙도록 셀 참조방식을 변경합니다.
평균을 구할 범위는 오른쪽으로만 이동해야합니다. (숫자(=행)을 고정합니다) - 따라서 차수별 시험 평균성적을 구하기 위한 AVERAGEIF 함수 수식은 아래와 같이 입력됩니다. 이후 수식을 자동채우기 하면 각 차수별 시험 평균성적 계산이 완료됩니다.
=AVERAGEIF($C$4:$C$14,$C15,D$4:D$14)
위와같이 수식을 입력한 뒤 자동채우기 하면 각 차수별 평균점수가 한번에 계산됩니다.
- 상대참조 : 셀이 이동할 때 참조되는 셀 주소도 같이 이동하는 셀 참조방식입니다.
