[정보] 엑셀022 : 상대참조, 절대참조, 혼합참조 관련 쉬운 설명과 사용 방법 + 상대참조, 절대참조, 혼합참조 변환방법 공유

엑셀-배우기022-썸네일

엑셀 상대참조, 절대참조, 혼합참조에 대해 설명드립니다.

 

 

 

 

| 상대참조

엑셀의 좋은 점 중 하나는 내가 입력한 수식을 옆이나 아래 셀에도 드래그 한 번에 적용이 가능하다는 것이 아닐까 합니다. 이렇게 드래그를 해서 내가 만든 수식을 자동으로 넣을 수 있는 이유는 엑셀이 상대참조를 해서 알아서 수식을 넣어주기 때문입니다.

 

상대참조 뜻 : 셀의 위치에 따라서 자동으로 내가 넣은 수식 혹은 함수를 인식하고, 해당 수식 혹은 함수를 인근 셀에 반영하는 것

 

이렇게 의미만으로는 잘 와닿지 않을 수도 있어서 아래 화면과 함께 설명드리려 합니다. 엑셀을 쓰다 보면 아래와 같이 가격 x 수량을 곱해서 각 항목별 총가격을 구하는 것과 비슷한 업무를 할 때가 있습니다.

 

 

 

아래의 예시를 보면 17500원인 페퍼로니 피자를 2개 구입한 가격에 대한 수식을 총 가격 란에 넣었습니다. 그리고 그 아래에 있는 치즈 크러스트 피자 → 베이컨 피자 → 불고기 피자 → 야채 피자에도 차례대로 넣고 싶은 경우에는 페퍼로니 피자의 총가격을 구한 셀의 오른쪽 아래 구석에 마우스 커서를 살며시 대봅니다 (한 1~2초 정도)

 

엑셀-상대참조-예시1

위에 있는 그림처럼 오른쪽 아래 구석에 마우스 커서를 대면 마우스 커서가 + 표시로 변하게 됩니다. 이때 왼쪽 버튼을 누른 채로 내가 같은 형식을 채우고 싶은 부분까지 쭉 내리고 → 원하는 곳까지 오면 마우스 버튼을 뗍니다.

 

 

마우스 버튼을 떼면 아래 그림의 빨간색 네모 부분과 같이 자동으로 각 피자의 총가격이 계산됩니다. 

엑셀-상대참조-예시2

 

이 부분을 수식으로 나타내 보면 아래의 빨간색 네모와 같은데요, 노란색 부분을 자세히 보면 처음에 입력했던 =D4*E4 수식의 형태를 유지한 채, 참조하는 위치만 조금씩 바뀌고 있는 것을 볼 수 있습니다.

 

이렇게 마우스를 드래그해서 옮길 때 내가 입력한 수식이나 함수의 형태를 유지하고 옮긴 위치에 대한 셀 정보를 알아서(상대적으로) 변경하는 참조를 상대 참조라고 합니다.

엑셀-상대참조-예시3

 

 

 

 

 

| 절대참조

이번에는 절대 참조에 대해서 알아보겠습니다. 상대와 절대라는 단어를 많이 들어보셨을 텐데요, 상대라는 뜻은 '상황에 따라서 바뀔 수 있다'는 의미를 갖고 있고, 이에 반해 절대는 '상황에 따라 바뀌지 않는다'라는 뜻을 갖고 있습니다.

 

이번에 볼 절대참조의 경우 절대라는 단어가 들어있기 때문에 상황에 따라 바뀌지 않는 함수라는 것을 예상할 수 있습니다. 

 

절대참조 뜻 : 셀의 위치에 관계없이 내가 설정한 셀만 참조하는 경우

 

이번에도 예시를 통해서 확인을 해보겠습니다. 해외 직구 등을 할 때, 혹은 직장에서 원자재 값이나 중간재 구매 등을 할 때, 협력사나 고객 측에 비용을 송금할 때 환율 적용을 상당히 많이 하게 됩니다.

 

이때 각 셀에 환율을 똑같이 적용시키기 위해서 쓸 수 있는 것이 바로 절대참조입니다. 

 

 

 

아래 화면은 직구로 의류를 구입할 때 총가격이 얼마인지 알아보는 화면입니다. 화면 우측 상단에는 1달러 일 때 한국 돈이 얼마인지를 나타내는 환율이 있고, 직구로 의류를 구매하기 때문에 우선 USD 총가격으로 계산을 하는 구조입니다.

엑셀-절대참조-예시1

폴로 티셔츠 Large 크기 옷 두 벌을 구매하는 경우 80달러이며, 2개를 구입하면 USD 총가격은 160달러가 됩니다. 이때 환율을 적용하면 위의 노란색과 같이 208000원이 나옵니다.

 

 

 

조금 전 구한 208000원을 KRW 총 가격 수식 셀에 노란색 부분과 같이 수식으로 나타내 보았습니다. 

엑셀-절대참조-예시2

 

이 수식을 보면 =F6*$I$2로 되어있는 것을 볼 수 있고, 참조하는 I2 셀은 아래 화면의 노란색 부분입니다. 그런데 참조하는 셀 I2 앞과 뒤에 특수문자 $가 붙어서 $I$2로 나타나 있는 것을 볼 수 있습니다.

엑셀-절대참조-예시3

 

여기에 붙은 $는 셀을 고정할 때 나타나는 특수 문자입니다. I2를 절대참조로 하고 싶다면 키보드의 F4 키를 누르면 되고 이렇게 누르면 I2 → $I$2로 변합니다.

 

이 부분을 자세히 풀어보면 아래와 같습니다. 

상대참조 → 절대참조로 가는 Key : F4
$I$2에서 $의 의미 : 셀 고정 (I는 열, 2는 행을 나타냄)이며, A와 5 앞에 모두 $가 붙었으므로 행과 열 모두 고정했다는 의미 → 행과 열 모두를 고정했기 때문에 한 개의 셀(I2)만 참조하도록 셀 하나를 고정한 상태를 의미

 

 

 

위에서 봤던 화면을 다시 보겠습니다. 지금 I2 셀에는 1300이라는 숫자가 입력되어 있고(노란색), 이 셀만 참조하도록 고정( $I$2) 해 놓은 상황입니다.

엑셀-절대참조-예시4

 

여기에서 다른 항목에도 동일하게 적용하려면 208000이 있는 셀을 노란색 부분처럼 드래그를 해서 채우면 됩니다.

엑셀-절대참조-예시5

 

 

 

드래그를 해서 채우면 아래의 빨간색 부분과 같이 폴로 티셔츠 → 폴로셔츠 → 폴로 양말 순으로 KRW 총가격이 잘 변환된 것을 볼 수 있습니다.

엑셀-절대참조-예시6

 

이번에는 수식이 어떻게 입력되어 있는지를 보겠습니다. 수식 부분을 보니 셀이 변경되어 있는데도 노란색 부분이 동일하게 들어 있는 것을 볼 수 있습니다. 셀이 변화해도 참조하는 셀을 고정(I2 셀의 1300이라는 숫자) 하는 참조가 바로 절대참조입니다.

엑셀-절대참조-예시7

 

위에서 말씀드린 것처럼 절대참조는 환율 등 고정된 정보를 참조할 때 쓰기 좋은 참조입니다.

 

 

 

 

 

| 혼합참조를 들어가기 전에

혼합참조는 언제나 헷갈리기 쉬운 참조입니다. 그렇기 때문에 혼합참조를 사용해야 하는 경우가 온다면, 아래 예시를 종종 참조하면서 진행하는 것을 추천드립니다. 외워서 하려면 참조가 이상하게 되는 경우가 종종 있습니다.

 

 

 

 

 

| 혼합참조

이번에는 혼합참조에 대해서 알아보겠습니다. 이름을 들으면 바로 감이 올 것 같습니다. 혼합이니까 '상대참조와 절대참조를 섞어 놓았겠구나..'라는 생각이 듭니다.

 

사실 혼합참조는 머리가 복잡해지기 때문에 활용도가 그렇게 높은 편은 아니지만 피벗으로 데이터를 1차 추출한 뒤 → 해당 데이터를 바탕으로 유사한 데이터를 추출해야 할 때 종종 사용하는 참조 방식입니다.

 

혼합참조는 어려울 수 있으니 예시를 통해서 말씀드리려 하고요, 우선 열을 고정하는 혼합참조에 대해서 알아보겠습니다. 아래 화면은 1000원에서 50000원까지의 금액을 각 나라의 환율을 적용할 때의 표입니다.

엑셀-혼합참조-예시1

우선 1000원을 미국 달러로 변환할 때 얼마인지를 나타내는 수식을 넣어 보았는데요, 수식 창의 노란색 부분을 보면 $C4라고 되어있는 것을 볼 수 있습니다. 절대 참조였다면 $C$4로 표시가 되었겠지만 이번에는 C앞에만 $표시가 붙은 것을 볼 수 있습니다.

 

$표시의 의미는 아래와 같습니다. 행과 열을 모두 고정시키면 절대참조, 행 혹은 열하나만 고정하면 혼합참조가 됩니다.

C4 인 경우 : 고정된 것 없음 (상대참조)
$C$4인 경우 : 열(C)과 행(4)을 모두 고정 (절대참조)
$C4 인 경우 :열(C)만 고정 (혼합참조)
C$4인 경우 :행(4)만 고정 (혼합참조)

 

$C4을 드래그하면 $C5, $C6, $C7,$C8... 등으로 변환됩니다. 눈치 빠른 분은 눈치채셨겠지만 열이 계속 고정되는 것을 볼 수 있습니다. 이렇게 열 앞이 고정인 경우에는 아래 화면과 같이 고정된 열 영역(빨간색)으로만 셀이 참조됩니다 (위/아래로는 자동으로 참조되지만, 좌/우로는 참조를 못하는 상태)

엑셀-혼합참조-예시2

 

 

 

그런데... 앞에 보니 앞에 있는 노란색 부분도 혼합참조가 되어있습니다. F$3으로 되어있는 것을 보니 이번에는 행(가로)이 고정인 것을 알 수 있습니다. (행고정)

엑셀-혼합참조-예시3

이 수식을 드래그하면 어떻게 변화될까요? F$3, G$3, H$3, I$3... 등으로 변화하게 됩니다.

 

이번에는 행(가로)으로 고정되어 있기 때문에 좌/우 방향은 자유롭게 움직이지만, 위/아래방향은 고정이 되어있어서 움직일 수가 없고, 그림으로 나타내면 아래 화면과 같이 고정된 열 영역(빨간색)으로만 셀이 참조됩니다.

엑셀-혼합참조-예시4

 

 

 

이제 다시 화면으로 가보겠습니다. 드래그를 오른쪽으로 해보니 환율이 잘 적용되는 것을 볼 수 있습니다.

엑셀-혼합참조-예시5

 

이 부분을 수식으로 보면 아래와 같은데요, 미국과 관련된 모든 셀에 $C4라는 수식이 들어있는 것을 볼 수 있습니다.

엑셀-혼합참조-예시6

 

이번에는 마저 다른 수식을 채워보겠습니다. 수식을 다 채우고 보니 드래그를 여러 셀에 했지만, C열을 벗어나는 셀이 없는 것을 볼 수 있습니다. $C4의 $는 고정을 한다는 의미이고, 현재 고정하는 대상이 C열이기 때문에 아래와 같이 참조가 됩니다. (열은 자유롭게 이동, 행은 고정)

엑셀-혼합참조-예시7

 

 

 

같은 데이터이긴 하지만, 이번 예시에서는 사실 행고정도 함께 들어있습니다. 이번에는 행고정의 시각에서 보도록 하겠습니다. 드래그를 아래쪽으로 해보니 환율이 잘 적용되는 것을 볼 수 있습니다.

엑셀-혼합참조-예시8

 

이 부분을 수식으로 보면 아래와 같은데요, 숫자 1000과 관련된 모든 셀에 F$3라는 수식이 들어있는 것을 볼 수 있습니다.

엑셀-혼합참조-예시9

 

이번에도 마저 다른 수식을 채워보겠습니다. 수식을 다 채우고 보니 드래그를 여러 셀에 했지만, 3행을 벗어나는 셀이 없는 것을 볼 수 있습니다. F$3의 $는 고정을 한다는 의미이고, 현재 고정하는 대상이 3행이기 때문에 아래와 같이 참조가 됩니다. (행은 자유롭게 이동, 열은 고정)

엑셀-혼합참조-예시10

 

사실 혼합참조는 언제나 헷갈릴 수밖에 없는 부분이라서 이 부분이 바로 와닿지 않는 경우 굳이 외우려 하지 않으셔도 되고, 위의 예시를 그때, 그 때 필요한 경우에 찾아보는 것을 추천드립니다.

 

 

 

 

 

| 상대참조, 절대참조, 혼합참조 변경 순서는?

위에서 말씀드린 것처럼 상대참조 → 절대참조 → 혼합참조로 변경하려면 F4를 누르면 됩니다. F4로 변경하는 방식은 순환하는 방식(상대참조 → 절대참조 → 혼합참조 → 상대참조 → 절대참조... )이기 때문에 만일 내가 변경하고자 했던 함수를 실수로 지나친 경우 다시 F4를 눌러서 원하는 참조로 변경하면 됩니다.

 

C4행를 참조했을 때 F4를 누르는 횟수에 따라 변경되는 순서는 아래와 같습니다.

기본상태 : C4 (상대참조)
F4 1회 입력 : $C$4 (절대참조)
F4 2회 입력 : C$4 (혼합참조-행고정)
F4 3회 입력 : $C4 (혼합참조-열고정)
F4 4회 입력 : C4 (상대참조)
F4 5회 입력 : $C$4 (절대참조)
.
.
.