엑셀 상대참조 및 절대참조와 혼합참조 사용 법 이해와 F4 단축키 활용하기
엑셀을 처음 학습하면서 어려운 부분중 하나가 상대 참조, 절대 참조, 혼합 참조입니다. 말뜻의 어려움도 있으며 사용상의 어려움이 따르기 때문에 반드시 이해하고 넘어가야하는 부분입니다. 상대 참조는 셀을 참조하는 위치가 상대적으로 바뀔 수 있는 방식을 말하며 절대 참조는 수식 내에서 특정 위치를 고정시키고 계산할 때 사용합니다. 마지막으로 혼합 참조는 행 방향 또는 열 방향으로만 제한해 절대 참조하는 방식을 가리킵니다. 상대 참조, 절대 참조, 혼합 참조를 알아보기 전에 참조와 참조 위치의 개념부터 이해하는 것이 중요합니다.
1. 참조 위치와 참조 알아보기
합계를 구하는 과정으로 살펴보겠습니다. 합계는 일정한 구간 내에 있는 값들을 모두 더해 계산하는 방식입니다. 위의 그림처럼 성적표를 사용하여 예를들어 보겠습니다.
위의 그림에서 홍길동 학생을 예로 들어 들어보겠습니다. 홍길동 학생의 과목당 점수 합계를 구하기 위해서는 C5셀에서 H5셀까지의 합으로 구해야 합니다. F5셀을 클릭하고 수식은 =SUM(C5:E5)를 사용하여 구합니다. 여기서 잘 봐야 하는 것이 있는데요. 바로 C5:E5 구간을 참조 위치 또는 참조 영역이라고 부릅니다.
이번에는 평균을 구하는 과정으로 알아보겠습니다. 합산 값을 합산한 개수로 나누는 평균을 구하는 함수 AVERAGE 계산 역시 참조 위치를 필요합니다. 위의 그림처럼 성적표를 사용하여 예를들어 보겠습니다.
홍길동 학생의 평균을 구하기 위해서 G5셀을 클릭하고 =AVERAGE(C5:E5)을 사용하며, C5셀과 E5셀까지를 참조 위치로 지정하며 계산합니다. 이렇게 최종값을 구하기 위해 참조 위치를 사용하는 과정을 참조라고 부릅니다.
2. 상대 참조 알아보기
상대 참조는 쉽게 말해 수식 계산을 각 행 또는 각 열에 맞춰 참조 위치를 상대적으로 변경하는 것을 가리킵니다. 즉, 셀에 수식을 입력한 뒤 자동채우기를 하면 셀의 위치가 변하면서, 참조되는 셀 주소도 동시에 변하게됩니다. 그림을 보면서 이해해 보도록 하겠습니다.
위의 그림에서 새우깡의 단가(2500)와 수량(1)을 곱해보겠습니다. E4셀을 선택하고 =C4 * D4를 입력하고 ENTER를 누릅니다.
E4셀에 2500이 구해진 것을 확인할 수 있습니다. 나머지 과자들의 합계를 구하기 위해 E4셀의 채우기 핸들을 사용하여 E7셀까지 드래그를 해봅니다.
그러면 E5셀에서 E7셀까지 자동으로 합계가 구해진것을 알 수 있습니다. 왜이런 결과가 나왔는지 다시 확인해 보도록 하겠습니다.
위의 그림처럼 자동으로 수식이 변경되어 구해진 것을 알 수 있습니다. 즉, 자동으로 참조되는 셀의 주소도 변하는 것을 알 수가 있는 것입니다. 이것이 바로 상대 참조입니다. 상대 참조를 이용하면, 동일한 방식으로 계산해야 될 여러개의 값을 손쉽게 처리할 수 있습니다.
3. 절대 참조 알아보기
절대 참조는 수식 내에서 특정 위치를 고정시키고 사용하는 방식입니다. 즉, 항상 셀 주소가 고정되는 참조방식을 말합니다. 그림을 보면서 이해해 보도록 하겠습니다.
위의 그림처럼 포인트 적립을 구하기 위해 F4셀을 선택하고 =C2 * E4를 입력하고 ENTER를 누릅니다.
F4셀에 포인트 적립 150이 입력된 것을 확인할 수 있습니다. 채우기 핸들을 사용하여 나머지 과자의 포인트 적립을 구하기 위해 F7셀까지 드래그 해봅니다.
F5셀부터 F7셀까지 값이 나타나긴 했으나 F5셀에는 오류가 나타났으며 F6셀과 F7셀은 값이 이상하게 나왔습니다. 왜 이런 결과가 나왔는지 확인해 보도록 하겠습니다.
위의 그림처럼 수식을 확인해 보니 C2셀이 고정되어 있어야 하는데 F5셀부터 F7셀까지는 변한 모습을 확인할 수 있습니다. 이때 C2셀을 고정해서 사용하고 싶을때 사용하는 방식이 절대 참조입니다. 그림으로 확인해 보도록 하겠습니다.
이번엔 과자들 마다 정확한 포인트 적릭을 구하기 위해 F4셀에 =$C$2 * E4를 입력하고 ENTER를 누릅니다. 여기서 주의깊게 봐야 하는 것이 바로 $C$2 입니다. 이것의 의미는 C2셀의 행과 열을 고정시키겠다는 의미이며 변하지 않는다고 하여 절대 참조라고 합니다.
과자들 마다 정확한 포인트 적립을 위해 F4셀의 채우기 핸들을 이용하여 F7셀까지 드래그를 합니다.
위의 그림고 같이 절대 참조를 사용하였더니 F5셀부터 F7셀까지 옳바른 값이 출력된 것을 알 수 있습니다. 왜 이런 결과가 나왔는지 확인해 보도록 하겠습니다.
위의 그림처럼 F4셀부터 F7셀까지 C2셀이 고정(절대 참조)되어 수식이 구해졌기 때문에 옳바른 값을 구할 수 있었던 것입니다.
4. 혼합 참조 알아보기
절대 참조를 사용하면서 $표시를 보았을 것입니다. $표시는 행 또는 열로 고정될 위치를 표시하는 역할을 합니다. 표를 보면서 정확히 이해를 해보도록 하며 혼합 참조인 열 절대 참조와 행 절대 참조를 사용하는 방법을 알아보겠습니다.
| $A$1 [절대 참조] |
열과 행 모두 고정됩니다. 수식을 상하좌우로 자동채우기해도 해당셀은 항상 고정됩니다. |
| $A1 [혼합 참조, 열 절대 참조] |
열만 고정됩니다. 수식을 위아래로 자동채우기 하면 1, 2, 3..의 행번호는 변하지만, 좌우로 자동채우기를 하여도 열번호인 A는 고정됩니다. |
| A$1 [혼합 참조, 행 절대 참조] |
행만 고정됩니다. 수식을 좌우로 자동채우기 하면 A, B, C...의 열번호는 변하지만, 위아래로 자동채우기 하여도 1인 행번호는 고정됩니다. |
혼합 참조 중에서 열 절대 참조를 사용해 보겠습니다. 위의 그림처럼 C4셀에 =$B2를 입력하고 ENTER를 누릅니다. 그리고 채우기 핸들을 사용하여 E4셀까지 드래그를 해봅니다.
E4셀까지 값 20이 입력된 것을 확인 할 수 있습니다. 어떻게 이런 결과가 나타났는지 수식으로 확인해 보도록 하겠습니다.
위의 그림처럼 B2열이 고정되어 표시된 것을 확인할 수 있습니다. 즉, B2열의 값이 고정되어 나타난 것입니다.
이번엔는 C4셀을 채우기 핸들을 사용하여 C8셀까지 드래그를 해보겠습니다.
위의 그림처럼 C5셀부터 C8셀까지 값이 0으로 등록된 것을 확인할 수 있습니다. 왜 이렇게 나왔는지 확인해 보도록 하겠습니다.
위와 같이 B2열의 행번호인 2가 고정되어 있지않고 변했기(3, 4, 5, 6) 때문에 나타난 결과입니다.
혼합 참조중에서 행 절대 참조를 사용해 보겠습니다. C4셀을 선택하고 =B$2를 입력 후 ENTER를 누릅니다. 그리고 C4셀의 채우기 핸들을 이용하여 E4셀까지 드래그를 합니다.
위의 그림처럼 D4셀부터 E4셀의 값이 0으로 등록된 것을 확인할 수 있습니다. 왜 그런지 확인해 보도록 하겠습니다.
위의 그림처럼 행(2)은 고정되어 있지만 열(B, C, D)이 변했기 때문입니다.
이번에는 C4셀의 채우기 핸들을 사용하여 C8셀까지 드래그를 해보겠습니다.
C8셀까지 20이 입력된 것을 확인 할 수 있습니다. 어떻게 이런 결과가 나타났는지 수식으로 확인해 보도록 하겠습니다.
위의 그림처럼 B2행이 고정되어 표시된 것을 확인할 수 있습니다. 즉, B2행의 값이 고정되어 나타난 것입니다.
5. 절대 참조, 혼합 참조를 빠르게 이용하는 법
절대 참조는 F4를 한 번 누르면 됩니다. 혼합 참조의 경우 행 방향으로 절대 좌표를 사용하려면 셀 위치에서 F4를 두 번 누르면 되며, 열 방향으로 절대 좌표를 사용하려면 F4를 세 번 누릅니다. 다시 원 상태인 상대 참조로 되돌리려면 F4를 네 번 눌러야 합니다. 표를 보면서 이해를 해보도록 하겠습니다.
| 참조 방식 | 방 법 | 설 명 | 표기 예 |
| 상대 참조 | 기본값 | 수식 계산시 위치가 바뀌면 계산하는 참조 셀도 바뀝니다. | A1 |
| 절대 참조 | F4 한 번 누름 | 수식 계산시 위치가 바뀌어도 계산하는 참조 셀을 고정시킵니다. | $A$1 |
| 혼합 참조 [행 절대 좌표] |
F4 두 번 누름 | 행 방향을 절대 참조로 고정시킵니다. | A$1 |
| 혼합 참조 [열 절대 좌표] |
F4 세 번 누름 | 열 방향을 절대 참조로 고정시킵니다. | $A1 |
'록스의 IT 창고 > 엑셀' 카테고리의 다른 글
| 엑셀 마우스 및 값을 사용하여 셀의 행 높이와 열 너비 조정하는 법 (0) | 2022.01.04 |
|---|---|
| 엑셀 시트 참조를 이용하여 1월과 2월 매출 합계 구하기 (0) | 2021.11.14 |
| 엑셀 자동 합산용 함수 SUM AVERAGE COUNT MAX MIN 알아보기 (0) | 2021.10.22 |
| 엑셀 산술 비교 문자 결합 참조 연산자 및 수식 입력식 알아보기 (0) | 2021.10.19 |
| 엑셀 전체 화면 수식 입력줄 눈금선 머리글 감추기와 보이기 하는 법 (0) | 2021.10.18 |