엑셀 절대 참조, 상대 참조, 혼합 참조 3가지의 셀 참조 방식을 알아보는 글 입니다. 처음 엑셀 시작 시 반드시 알고 있어야하는 부분입니다. 지금부터 엑셀 셀 참조 방식 3가지에 관해 시작합니다!
연습용 예제 파일을 다운로드 해서 이 글을 읽으며 따라해 봐도 좋습니다.
셀 참조란?
우선 셀 참조가 무엇인지 알아 보도록 하겠습니다. 셀 참조란 특정 셀 주소를 입력해서, 우리가 직접 수식 또는 함수를 입력 하지 않아도 자동으로 입력이 될 수 있도록 도와주는 기능을 말합니다.
만약 셀 참조가 없었더라면 우리가 직접 모든 셀에 입력을 해야 되는데요. 셀 참조를 이용하면 하나의 셀에만 입력 한 후 자동 채우기를 통해 나머지 셀에도 자동으로 입력을 할 수 있습니다.
실제 예시를 통해 셀 참조의 개념을 알아 보도록 하겠습니다.
아래 사진을 보겠습니다. 과일 단가와 판매 수량을 곱한 판매 가격을 구하는 수식을 제가 입력을 해놨습니다. 즉, 2000원 ×10개라고 직접 입력을 해놨습니다.
그리고 엔터키를 누르면 20,000원이라는 값이 나오게 됩니다. 제가 여기서 판매 수량을 5로 변경을 해 봤습니다. 근데 판매 수량 셀을 변경 하더라도 판매 가격 셀은 자동으로 안 바뀝니다.
이번에는 셀 참조를 이용해서 동일한 수식을 입력 해 보도록 하겠습니다. 아래 사진을 보시면 D3 셀과 E3셀을 각각 지정을 해서 수식을 입력을 했습니다. 즉, 셀 참조를 이용해서 수식을 입력을 한 것입니다.
이 경우에도 마찬가지로 20,000 원이라는 같은 값이 나오게 됩니다. 그리고 제가 판매 수량을 10에서 5로 변경을 해 봤습니다. 그랬더니 이번엔 판매 가격이 10,000원으로 자동으로 변경 된 것을 확인할 수 있습니다.
즉, 셀 참조를 이용할 경우 직접 셀을 지정 하는 것이기 때문에, 셀 값이 변경 되는 경우 수식의 결과 값도 자동으로 같이 변하게 됩니다.
상대 참조
이번엔 상대 참조에 대해 알아 보도록 하겠습니다. 상대 참조란 셀의 위치가 변할 때마다 셀 주소가 함께 변하는 것을 말합니다.
엑셀의 경우 기본적으로 상대참조가 설정이 되어 있습니다. 따라서 우리가 따로 설정을 하지 않더라도 기본적으로 상대 참조가 적용이 됩니다.
지금부터 상대참조에 대해 알아보겠습니다.
1.행방향 상대참조
아래 사진을 보시면 과일 단가와 판매 수량을 곱한 판매 가격이 나와 있습니다. 여기서 제가 첫 번째 판매가격 셀에 과일 단가와 판매 수량을 곱한 수식을 입력을 했구요. 아래 셀에도 자동 채우기를 해서 모두 셀을 자동 완성을 시켜놨습니다.
여기서 판매 가격 셀을 보시면 D,E열은 동일하게 유지 되어 있는 반면, 셀 위치가 변경 되면서 행에 해당되는 셀 주소가 계속 바뀌는 것을 알 수 있습니다. 즉, 행 방향으로 상대 참조가 적용 된 것이라 할 수 있습니다.
2.열방향 상대참조
이번에는 아래 사진을 보시면 연도별 과일 종류의 합계를 구하는 표가 나와 있습니다. 여기서 제가 SUM이라는 함수를 이용해서 연도별로 과일 개수의 합계를 구해보도록 하겠습니다.
=SUM을 입력한 후 C3:C7 범위를 지정한 후 엔터키를 누릅니다.
그럼 아래 사진처럼 첫 번째 셀에 23이라는 값이 나오게 되구요. 여기서 오른쪽으로 자동 채우기를 해 주면 그 옆의 셀에도 값이 자동으로 입력이 됩니다.
각 셀의 결과 값을 수식으로 표시를 해 보면 아래 사진과 같습니다. 여기서 2021년에 해당되는 첫 번째 셀을 보시면 C3부터 C7 까지 범위가 지정이 되어 있습니다.
그리고 2022년에 해당 되는 두 번째 셀을 보시면 D3부터 D7까지 범위가 지정이 되어 있습니다. 이런 식으로 행은 그대로 고정이 되어 있는 반면, 열이 상대적으로 셀 위치가 변하면서 계속 바뀌게 되죠.
이러한 경우를 열 방향의 상대 참조 라고 말할 수 있습니다.
엑셀 절대 참조
이번에는 절대참조에 대해서 알아 보겠습니다. 우리가 수식이나 함수를 입력할 때 특정 셀을 지정 하게 되는데요. 셀 위치가 변할 때 절대 참조한 셀은 바뀌지 않고 그대로 고정 되어 있습니다. 즉 절대참조를 사용하면 셀이 딱 고정 되어서 바뀌지 않게 됩니다.
1.한개의 셀 절대참조
첫번째로 한 개의 셀에 절대참조를 적용 하는 방법을 알아 보겠습니다. 아래 사진은 사원 별 계약금액에 인센티브 비율을 곱한 금액을 구하는 것입니다.
계약 금액 아래의 10,000원이라고 되어 있는 셀을 클릭하고 곱하기 기호를 입력합니다. 그 후 인센티브 옆의 10% 라고 되어 있는 셀을 클릭 합니다. 그리고 엔터키를 누릅니다.
그럼 첫 번째 셀에 1000 원이라는 값이 나오게 됩니다. 첫 번째 셀은 값이 정상적으로 잘 나왔는데요. 아래 셀에도 자동 채우기를 해 봤더니 값이 이상하게 나오는 것을 확인할 수 있습니다.
이렇게 값이 이상하게 나오는 이유는 우리가 절대참조를 해 주지 않았기 때문입니다. 따라서 이번에는 절대참조를 적용에서 값을 구해보도록 하겠습니다.
아래 사진을 보겠습니다. 계약금액에 해당되는 10,000원 셀은 절대 참조를 해 주지 않습니다. 왜냐하면 사원 명이 달라질 때마다 계약 금액도 같이 바뀌어야 되기 때문이죠.
하지만 C2에 해당 되는 인센티브 10% 셀은 절대참조를 해줘야 합니다. 왜냐하면 셀이 이동을 하더라도 인센티브 셀은 바뀌면 안 되기 때문입니다.
절대참조를 하기 위해선 C2 셀을 블록 지정을 해 주시구요. F4 키를 눌러 주시면 됩니다. 맥북의 경우 Fn+ F4 키를 눌러 주시면 됩니다.
그 이후 다시 아래 셀에 자동 채우기를 해 보면 값이 정확하게 제대로 나온 것을 확인할 수 있습니다.
2.셀 범위 절대참조
위에서는 하나의 셀에 절대참조를 적용 하는 방법을 알아 봤는데요. 이번에는 범위로 절대참조를 지정 하는 방법을 알아 보겠습니다.
아래 사진에서 우리가 과일 종류 별 판매 가격 합계를 SUMIF 함수를 이용해서 구해보도록 하겠습니다.
▶︎SUMIF(조건범위, 조건, 합계범위)
우선 SUMIF 함수 내 인수는 위와 같이 구성되어 있습니다. 첫번째는 조건범위를 입력해 줘야 하고요. 두번째는 조건을 입력 해줘야 합니다. 그리고 세번째는 합계범위를 입력 해줘야 합니다.
각 인수를 입력 하는 방법을 알아 볼게요. 위 사진을 함께 보면서 아래 내용을 읽어 주세요.
①SUMIF($C$3:$C$10,B13,$F$3:$F$10)
우선 첫번째로 조건범위를 입력 해줘야 합니다. 우리는 사과의 합계를 구할 것입니다. 때문에 사과 셀이 포함되어 있는 과일 종류 열을 모두 범위 지정해 주면 됩니다.
여기서 중요한 것이 있죠. 바로 절대 참조를 해줘야 한다는 것입니다. 과일 종류 열은 셀 위치가 이동 하더라도 항상 고정이 되어 있어야 합니다. 따라서 F4 키를 눌러서 반드시 절대참조를 해 주세요.
②SUMIF($C$3:$C$10,B13,$F$3:$F$10)
두 번째로 조건을 입력 해줘야 합니다. 우리는 사과라는 조건을 만족 하는 합계를 구하는 것입니다. 따라서 조건은 사과가 됩니다.
때문에 사과가 적혀져 있는 셀을 클릭 해 주면 됩니다. B13에 해당 되는 셀이죠. 사과 셀의 경우 절대참조를 하지 않습니다. 왜냐하면 사과 외 포도, 오렌지, 토마토 등 다른 셀으로 이동이 되어야 하기 때문이죠.
③SUMIF($C$3:$C$10,B13,$F$3:$F$10)
세번째로 합계 범위를 입력 해줘야 합니다. 사과의 판매 가격 합계를 구할 것이기 때문에 판매 가격 열을 모두 범위 지정해 주시면 됩니다.
이번에도 마찬가지로 판매 가격 범위가 바뀌면 안 됩니다. 때문에 F4키를 눌러서 절대참조를 해야 합니다.
그후 엔터키를 누르면 결과 값이 나오게 되는데요. 나머지 셀에도 자동 채우기를 해 주면 정확한 값이 나오게 됩니다. 우리가 절대 참조를 해서 고정 해야 하는 범위를 딱 고정 시켜줬기 때문에 나머지 셀에도 정확한 값이 나오게되는 것이죠.
혼합 참조
이번에는 혼합 참조에 대해서 알아 보겠습니다. 혼합참조란 행이나 열 중 하나에만 절대 참조를 지정 하는 것입니다. 내용이 조금 복잡해 보이지만 아주 간단합니다. 천천히 따라와 주세요.
1.F4 키로 쉽게 혼합참조하는법
혼합 참조 예시를 알아 보기 전에 F4 키를 이용하는 방법을 알아 보겠습니다. 혼합 참조 예시를 알아 볼 때 훨씬 더 쉽게 이해를 할 수 있습니다.
▶︎C2 → (F4 누르면) → $C$2 → (F4 누르면) → C$2 → (F4 누르면) →$C2 → (F4 누르면) → C2
예를 들어 C2라는 셀이 있습니다. 여기서 F4키를 한 번 누르면 C열과 2행에 절대참조가 모두 적용됩니다. $표시는 절대참조가 되었다는 의미입니다.
$C$2 상태에서 F4 키를 한 번 더 누르면 C$2 와 같은 모양이 나오는데요. 이 경우 2행에만 절대참조가 적용되고, C열에는 절대참조가 적용되지 않았다는 의미입니다.
C$2 상태에서 F4키를 한 번 더 누르면 $C2와 같은 모양이 나옵니다. 이 것의 의미는 C열에만 절대참조가 적용되고, 2행에는 절대참조가 적용되지 않았다는 의미입니다.
그리고 $C2 상태에서 F4키를 한 번 더 누르면 절대참조가 적용되지 않은 원래 셀인 C2가 나오게 됩니다.
이 사실을 기억하면서 아래 내용을 좀 더 쉽게 이해해 보도록 하겠습니다.
2.인센티브 혼합참조
아래 사진은 판매 금액과 인센티브 비율을 곱한 표 입니다. 아래 사진을 보면 C3셀과 D2셀을 곱한 수식이 나와 있는데요. C3 셀의 경우 판매금액에 해당 되는 C열은 고정이 되어야 합니다. 따라서 C열에만 절대 참조를 적용을 해 줍니다.
그리고 D2 셀의 경우 인센티브 비율에 해당되는 2행은 고정이 되어야 합니다. 따라서 2행에만 절대 참조를 적용을 해줍니다.
▶︎&C3: C열에만 절대참조 적용
▶︎D$2: 2행에만 절대참조 적용
▶︎’=$C3*D$2’라고 입력을 한 후 엔터키를 누르면 값이 나옵니다. 그리고 나머지 셀에도 자동 채우기를 해주면 아래 사진처럼 값이 모두 정확하게 입력이 되는 것을 확인할 수 있습니다.
이해가 잘 안 될 수 있기 때문에 한번 더 조금 더 쉽게 알아 보겠습니다. 위 사진을 같이 볼게요. =C4*E2 라고 되어 있는 셀을 더블클릭을 해봤습니다.
이 때 C4 셀의 경우 판매금액에 해당되는 C열은 딱 고정이 되어 있어야 합니다. 따라서 C에만 절대참조가 되어 있습니다. 그리고 E2 셀의 경우 인센티브 비율에 해당되는 2행은 딱 고정이 되어 있어야 합니다. 따라서 2에만 절대참조가 되어 있습니다.
3.판매합계 혼합참조
이번에는 SUMIFS 함수를 통해 여러 가지 조건을 만족하는 셀의 합계를 구하는 예시를 통해 혼합 참조를 알아 보겠습니다. 아래 사진은 판매 일자별, 과일 종류별 판매 가격 합계를 구하는 예시 입니다.
▶︎SUMIFS(합계범위, 조건범위1, 조건1, 조건범위2, 조건2…)
SUMIFS 함수 내 인수는 위와 같이 구성됩니다. 여기서 각 인수를 직접 입력을 하면서 혼합참조를 함께 알아보겠습니다. 위 사진을 같이 보면서 아래 글을 읽어주세요.
①SUMIFS($F$3:$F$10,$B$3:$B$10,$B13,$C3:$C$10,C$12)
SUMIF 함수 내 첫 번째 인수는 합계 범위 입니다. 우리는 사과의 판매 가격 합계를 구할 것입니다. 따라서 판매 가격에 해당 되는 셀을 모두 범위 지정 해 주면 됩니다.
여기서 절대참조를 반드시 해줘야 합니다. 다른 셀로 이동을 하더라도 합계 범위는 바뀌면 안 되기 때문이죠.
②SUMIFS($F$3:$F$10,$B$3:$B$10,$B13,$C3:$C$10,C$12)
두 번째 인수는 조건 범위1 입니다. 우리는 2023년 10월 10일에 해당 되는 판매 합계를 구할 것입니다. 따라서 날짜에 해당 되는 조건범위를 지정해 주면 됩니다.
이번에도 마찬가지로 날짜 범위가 바뀌면 안 되기 때문에 절대 참조를 해줘야 합니다.
③SUMIFS($F$3:$F$10,$B$3:$B$10,$B13,$C3:$C$10,C$12)
세 번째 인수는 조건1 입니다. 2023년 10월 10일이 조건이기 때문에 2023년 10월 10일 셀을 클릭을 해 주면 됩니다. 여기서 날짜 열에 해당 되는 B열은 바뀌면 안 됩니다. 따라서 B열에만 절대참조를 해 주면 됩니다.
④SUMIFS($F$3:$F$10,$B$3:$B$10,$B13,$C3:$C$10,C$12)
네 번째 인수는 조건 범위2 입니다. 조건2가 사과 였기 때문에 조건 범위 2는 과일 종류에 해당되는 열 전체 입니다. 따라서 과일 종류 열을 전체 범위 지정해 주시면 됩니다 .여기서도 마찬가지로 절대 참조를 해줘야 합니다.
⑤SUMIFS($F$3:$F$10,$B$3:$B$10,$B13,$C3:$C$10,C$12)
마지막 다섯 번째 인수는 조건2 입니다. 조건2는 사과이죠. 따라서 사과 셀을 클릭 해 주면 됩니다. 사과 셀은 C 12인데요. 여기서 사과 종류에 해당 되는 행인 12행은 바뀌면 안 됩니다 .딱 고정이 되어 있어야 하기 때문에 12에 절대 참조를 해줘야 합니다.
그후 엔터키를 누르고 나머지 셀에도 자동 채우기를 해 주면 아래 사진과 같이 결과 값이 정확하게 나오는 것을 확인할 수 있습니다.
<결론>
▶︎엑셀 절대참조: F4 키를 눌러서 셀 고정하기
▶︎엑셀 상대참조: 엑셀에서 기본적으로 지정되어 있는 셀 참조 방식. 셀 위치가 변하면서 셀 주소도 함께 변하게 됨
▶︎엑셀 혼합참조: 셀의 일부에만 절대참조를 지정함으로서 특정 열 또는 행만 고정시키는 것
이상 엑셀 절대 참조, 상대 참조, 혼합 참조에 관한 글이었습니다.