엑셀 절대참조,상대참조란 무엇인가요? :: 엑셀 위키
엑셀 절대참조, 상대참조 목차 바로가기
절대참조 vs 상대참조 5초 비교!
- 상대참조 : 수식을 자동채우기하면, 참조된 셀도 같이 이동
상대참조는 수식이 자동채우기 되면, 참조된 셀도 같이 이동합니다. - 절대참조 : 한번 입력한 셀 주소는 항상 고정
절대참조를 하게되면, 셀 주소는 항상 고정됩니다. - 혼합참조 : 가로 또는 세로방향을 선택해서 고정하는 방식!
혼합참조를 하게되면 행 또는 열, 한 방향으로만 고정됩니다.
엑셀 절대참조 vs 상대참조 vs 혼합참조 비교, 아주 간단하죠?^^*
엑셀 상대참조 vs 절대참조 vs 혼합참조 비교
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀위키] 엑셀 상대참조와 절대참조 완벽비교예제파일
엑셀 셀 참조 방식 알아보기
셀 참조방식의 종류
시트에서 각각의 셀은 그마다 고유한 셀주소(예: A1, A2, ...)를 갖고 있습니다. 그리고 함수를 입력하면, 이 셀 주소를 참조하여 해당 셀의 내용을 불러오거나 계산 할 때 사용하게 되는데요.
셀을 참조하여 수식을 작성합니다. 이때 셀을 참조하는 방식에는 크게 상대참조와 절대참조가 있습니다.
상대참조
셀 위치에 따라 참조되는 셀 주소가 동시에 변하는 참조방식
절대참조
셀 위치에 상관없이 참조되는 셀 주소는 항상 동일한 참조방식엑셀의 기본 참조방식은 무엇인가요?
엑셀의 기본 참조방식은 '상대참조' 입니다. 만약 다른 통합문서에서 셀을 참조할 경우, 기본 참조방식이 '절대참조'로 변경됩니다.
다른 통합문서의 셀은 '절대참조' 방식으로 입력됩니다. 엑셀 상대참조, 절대참조는 왜 필요한가요?
1. 상대참조
엑셀은 '상대참조'를 기본으로 셀을 참조합니다. 즉, 셀에 수식을 입력한 뒤 자동채우기를 하면 셀의 위치가 변하면서, 참조되는 셀 주소도 동시에 변하게 되는데요.
아래 예제로 간단한 매출표를 같이 보겠습니다. 아래 매출표에서 각 제품별 매출을 구하려면 어떻게 해야 할까요?
- 각 제품별로 '단가 x 수량' 을 계산합니다.
단가 x 수량으로 제품의 매출액을 계산합니다. - 첫번째 제품의 합계를 구하셨으면, 마우스 커서를 셀의 오른쪽 아래로 이동합니다. 마우스 커서가 '검은색 십자가 모양'으로 변하였으면, 마우스를 아래로 드래그합니다.
수식을 아래방향으로 자동채우기합니다. - 셀이 아래로 자동채우기 되면서, 참조되는 셀의 주소도 동시에 변하는 것을 볼 수 있습니다.
셀 주소가 이동되면서 각 제품별 매출이 자동으로 계산됩니다.
이렇게 상대참조를 이용하면, 동일한 방식으로 계산해야 될 여러개의 값을 아주 손쉽게 처리할 수 있습니다.
2. 절대참조
절대참조는 수식이 입력된 위치에 상관없이, 항상 셀 주소가 고정되는 참조방식을 이야기합니다. 이번에는 방금 전 계산한 제품별 매출 합계에서, 지정한 '적립율'에 따라 포인트를 계산하는 과정을 같이 알아보겠습니다.
- "합계 * 적립율"로 가장 맨 윗 제품의 적립금을 계산합니다.
적립률 x 합계로 제품의 적립금을 계산합니다. - 작성한 수식을 아래로 자동채우기 합니다. 어떤 셀은 '#VALUE!' 오류가 나오고, 어떤 셀은 옳지 않은 값으로 계산이 됩니다.
수식을 아래로 자동채우기 하면, 옳지 않은 값이 계산됩니다. - 왜 그런것일까요? #VALUE! 오류가 난 셀을 클릭합니다.
#VALUE! 오류가 발생한 셀을 클릭합니다. - #VALUE! 오류가 난 셀의 수식을 확인해보니, 적립율로 참조된 셀주소가 아래로 이동하여 오류가 난 것을 확인할 수 있습니다.
상대참조로 입력한 셀 주소가 이동하면서, 오류가 발생한 것을 확인할 수 있습니다.
따라서 이럴 경우에는 "적립율"이 입력된 셀 값은 고정하여 계산해줘야 합니다. 즉, 절대참조를 이용하면 되는데요. 방법은 아주 간단합니다.
- 수식을 수정하기 위해 셀을 클릭합니다.
수식을 변경할 셀을 클릭합니다. - 키보드 F4키를 한번 눌러주면, 셀주소에 '$' 기호가 붙으며 절대참조로 변경됩니다.
절대참조 할 셀을 선택한 후, 키보드 F4키를 눌러 절대참조로 변경합니다. - 이후 수식을 아래로 자동채우기하면, '적립율' 값은 고정되어 각 제품별 적립금이 올바르게 계산됩니다.
적립율 값이 항상 고정되어, 각 제품별 적립금이 올바르게 계산됩니다.
3. 절대참조표시 '$' 기호는 무엇인가요?
여기서 한가지 궁금한점이 생깁니다. 절대참조에 사용된 달러표시($) 는 무엇일까요?
달러표시는 '행' 또는 '열' 로 고정될 위치를 표시합니다.
참조방식 설명 $A$1
[절대참조]열과 행 모두 고정됩니다. 수식을 상하좌우로 자동채우기해도 해당셀은 항상 고정됩니다. $A1
[혼합참조, 열절대참조]열만 고정됩니다. (A만 고정) 다시말해, 수식을 위아래로 자동채우기 하면 '1,2,3...' 의 행번호는 변하지만, 좌우로 자동채우기를 하여도 열번호인 'A'는 고정됩니다. A$1
[혼합참조, 행절대참조]행만 고정됩니다. (1만 고정) 다시말해, 수식을 좌우로 자동채우기 하면 'A, B, C...'의 열번호는 변하지만, 위아래로 자동채우기 하여도 '1'인 행번호는 고정됩니다. 글로만 봐서는 바로 이해하기 어려우니, 아래에서 혼합참조의 사용예제를 살펴보겠습니다.
4. 혼합참조의 사용법
혼합참조를 실무에 적절히 응용할 수 있다면, 여러분의 함수 작업시간를 대폭 단축시킬 수 있습니다.
아래 각 제품의 월별 판매내역에서, 각 제품별 매출합계를 구하기 위하여 SUMIF 함수를 사용해보겠습니다.
= SUMIF ( 조건범위, 조건, 합계범위 )- 예제파일에 C24셀에 아래 수식을 입력한 뒤, 자동채우기 합니다.
=SUMIF(B19:B23,B19,C19:C23)
예제파일에 SUMIF 함수 수식을 입력합니다. - 이후 수식을 살펴보니, 제품별 매출합계가 옳바르지 않습니다. 자동채우기한 수식을 보면, 수식의 셀이 모두 '상대창조'로 입력되어 한칸씩 이동된 것을 볼 수 있습니다.
수식을 자동채우기 하면, 상대참조로 입력한 범위가 이동하면서 옳지않은 값이 계산됩니다.
이럴 경우, 어떻게 해야할까요? 매 월마다 SUMIF 함수를 따로 입력해줘야 할까요? 아닙니다. '절대참조'와 '혼합참조'를 같이 응용하면, 한번에 쉽게 해결할 수 있는데요. 아래 그림으로 혼합참조 사용법을 같이 알아보겠습니다.
- 조건범위: 조건범위는 수식이 상하좌우로 자동채우기 되어도 항상 고정되어야 합니다. 따라서 절대참조로 입력합니다.
- 조건 : 조건은 '좌우' 로는 이동하면 안되고, '위아래'로만 이동해야 합니다. 따라서 열절대참조로 입력합니다.
- 합계범위 : 합계범위는 '위아래'로는 이동하면 안되고, '좌우'로만 이동해야 합니다. 따라서 행절대참조로 입력합니다.
지금까지 엑셀 절대참조, 상대참조와 혼합참조의 기초 사용법 및 실전예제를 단계별로 알아봤습니다.
궁금하신 내용은 아래 댓글로 남겨주시면 확인 후 답변드리겠습니다.^^ 감사합니다!
- 각 제품별로 '단가 x 수량' 을 계산합니다.
