안녕하세요! 엑셀 수식의 숨겨진 비밀을 파헤치는 재미에 푹 빠진 제이입니다! 😄 '제이의 엑셀 초보 탈출기' 어느덧 열아홉 번째 시간이네요! 지난 시간까지 SUM, AVERAGE, MAX, MIN 함수를 배우고 성적표의 다양한 정보를 계산해 봤어요. 그리고 첫 번째 학생의 수식을 입력하고 아래로 드래그하면 나머지 학생들의 결과가 자동으로 계산되는 편리함도 경험했죠?
그런데 이렇게 수식을 복사했을 때 셀 주소가 자동으로 바뀌는 원리가 무엇이고, 만약 **특정 셀 주소는 변하지 않고 항상 같은 셀을 참조하게 만들고 싶다면** 어떻게 해야 할까요? 오늘 배울 '셀 참조 방식'이 이 질문에 대한 답입니다! 엑셀 수식과 함수를 제대로 활용하려면 이 '셀 참조' 개념을 정확히 아는 것이 정말 중요해요. 🤓
이번 시간에는 수식을 복사할 때 셀 주소가 기본적으로 어떻게 변하는지 알아보고, 특정 셀 주소를 고정하는 방법인 '절대 참조'를 배워볼 거예요. 마지막으로 필요에 따라 행이나 열만 고정하는 '혼합 참조'까지! 성적표 예시를 계속 활용해서 제이와 함께 마스터해 봅시다! 🚀
수식을 복사할 때 셀 주소가 바뀌는 이유: 상대 참조 ➡️
우리가 `=B2+C2` (총점 계산)나 `=SUM(B2:C2)` (SUM 함수로 총점 계산) 수식을 입력하고 아래 셀로 자동 채우기 했을 때, 엑셀은 알아서 `=B3+C3`, `=SUM(B3:C3)`, 그리고 `=B4+C4`, `=SUM(B4:C4)` 와 같이 셀 주소의 **행 번호를 자동으로 변경**해 주었습니다. 이게 가능한 이유는 엑셀의 셀 주소가 기본적으로 **상대 참조(Relative Reference)** 방식을 사용하기 때문이에요.
**상대 참조**란 수식이 있는 셀의 **상대적인 위치에 따라 참조하는 셀 주소가 자동으로 바뀌는 것**을 의미합니다. 예를 들어 D2 셀에 있는 `=B2+C2` 수식은 '나(D2) 기준으로 왼쪽으로 두 칸, 같은 행에 있는 셀(B2)과 왼쪽으로 한 칸, 같은 행에 있는 셀(C2)을 더해줘' 라는 의미를 가집니다. 이 수식을 한 칸 아래인 D3 셀로 복사하면, 엑셀은 똑같은 상대적인 위치를 참조하도록 자동으로 조정해서 '나(D3) 기준으로 왼쪽 두 칸, 같은 행에 있는 셀(B3)과 왼쪽 한 칸, 같은 행에 있는 셀(C3)을 더해줘' 라는 의미의 `=B3+C3` 으로 바꿔주는 거죠.
| 셀 주소 | 수식 (입력) | 결과 | 복사 후 (D3) | 복사 후 (D4) |
|---|---|---|---|---|
| D2 | =B2+C2 | 175 | =B3+C3 | =B4+C4 |
우리가 지금까지 사용했던 모든 셀 주소 참조는 기본적으로 상대 참조였습니다. 그래서 자동 채우기 기능이 제대로 작동했던 거죠!
항상 같은 셀을 참조하게 하려면? 절대 참조! 🔒
그렇다면 만약 수식을 복사하더라도 특정 셀 주소는 **절대 변하지 않고 항상 같은 셀만 참조**하게 만들고 싶다면 어떻게 해야 할까요? 이때 **절대 참조(Absolute Reference)**를 사용합니다.
절대 참조를 만들려면 셀 주소의 열 이름과 행 번호 앞에 달러 기호(`$`)를 붙여줍니다. 예를 들어 A1 셀을 절대 참조로 만들면 `$A$1` 이 됩니다. 달러 기호는 '여기(열 또는 행)는 고정해!' 라는 의미를 가집니다.
- **`$A$1`:** 열(A)과 행(1) 모두 고정됩니다. 수식을 어디로 복사하든 항상 A1 셀만 참조합니다.
- **`A$1`:** 열(A)은 상대 참조, 행(1)은 절대 참조입니다. 수식을 위/아래로 복사해도 행 번호(1)는 변하지 않지만, 왼쪽/오른쪽으로 복사하면 열 이름(A)은 변합니다. (혼합 참조)
- **`$A1`:** 열(A)은 절대 참조, 행(1)은 상대 참조입니다. 수식을 왼쪽/오른쪽으로 복사해도 열 이름(A)은 변하지 않지만, 위/아래로 복사하면 행 번호(1)는 변합니다. (혼합 참조)
- **`A1`:** 열(A)과 행(1) 모두 상대 참조입니다. 수식을 복사하면 열과 행이 모두 변합니다. (기본값)
혼합 참조는 조금 헷갈릴 수 있지만, 일단 절대 참조 `$A$1` 이 '완전 고정'이라는 것만 확실히 기억해 두세요!
절대 참조 사용하는 방법: F4 키 ✨
셀 주소에 일일이 달러 기호를 입력하기 귀찮다면? 아주 편리한 단축키가 있습니다. 바로 `F4 키` 입니다!
- 수식을 입력하거나 편집하는 중에 절대/혼합 참조로 바꾸고 싶은 셀 주소 위에 커서를 놓거나 셀 주소를 블록 지정합니다. (예: 수식 입력 줄에서 `B2` 글자 위에 커서 놓기)
- `F4` 키를 한 번 누르면 `$B$2` (절대 참조) 로 바뀝니다.
- `F4` 키를 두 번 누르면 `B$2` (행 고정 혼합 참조) 로 바뀝니다.
- `F4` 키를 세 번 누르면 `$B2` (열 고정 혼합 참조) 로 바뀝니다.
- `F4` 키를 네 번 누르면 `B2` (상대 참조) 로 돌아옵니다.
F4 키는 정말 자주 사용하니 꼭 익혀두세요!
성적표에 보너스 점수 추가하고 절대 참조 활용하기 🎁📍
이번에는 성적표에 보너스 점수 항목을 추가하고 절대 참조를 활용해 봅시다. 총점에 일정 비율의 보너스 점수를 더해서 '최종 점수'를 계산한다고 가정해 볼게요.
| 학생 이름 | 국어 | 수학 | 총점 | 보너스 비율 | 최종 점수 |
|---|---|---|---|---|---|
| 김민준 | 90 | 85 | 175 | 5% | =D2*$E$2 |
| 박서연 | 95 | 92 | 187 | ||
| 이지후 | 88 | 78 | 166 |
위 표에서 보너스 비율은 E2 셀에 5%로 입력되어 있습니다. 김민준 학생의 최종 점수(F2)를 계산하는 수식은 이렇게 됩니다.
`=D2*(1+$E$2)`
(총점 + 총점 * 보너스 비율 = 총점 * (1+보너스 비율))
여기서 D2는 학생마다 바뀌어야 하니 상대 참조로 두고, 보너스 비율이 있는 E2 셀은 수식을 복사해도 항상 그 셀을 참조해야 하므로 `$E$2` 로 절대 참조를 사용했습니다. E2 셀 주소를 입력한 후 F4 키를 눌러 절대 참조로 바꾸면 돼요!
절대 참조 수식 자동 채우기 결과 확인 ✅
이제 F2 셀에 입력한 수식을 아래로 자동 채우기 해봅시다.
| 학생 이름 | 총점 | 보너스 비율 | 최종 점수 (수식) | 최종 점수 (결과) |
|---|---|---|---|---|
| 김민준 | 175 | 5% | =D2*$E$2 | 183.75 |
| 박서연 | 187 | 5% | =D3*$E$2 | 196.35 |
| 이지후 | 166 | 5% | =D4*$E$2 | 174.3 |
보이시나요? 총점을 참조하는 D2, D3, D4는 수식 복사 시 자동으로 바뀌었지만, 보너스 비율을 참조하는 `$E$2` 는 그대로 유지되었습니다. 덕분에 모든 학생의 최종 점수를 정확하게 계산할 수 있었어요! 🎉
행 또는 열만 고정하고 싶다면? 혼합 참조! 🔗
절대 참조는 열과 행 모두 고정하지만, 때로는 열만 고정하거나 행만 고정해야 할 때가 있습니다. 이럴 때 **혼합 참조(Mixed Reference)**를 사용합니다. F4 키를 누를 때마다 바뀌는 `$A1` 형태와 `A$1` 형태가 바로 혼합 참조입니다.
- **`A$1` (행 고정):** 수식을 아래로 복사해도 행 번호(1)는 변하지 않고, 오른쪽으로 복사하면 열 이름(A)이 변합니다. '1행'의 값은 항상 참조하고 싶은데, 열은 상대적으로 움직여야 할 때 사용해요.
- **`$A1` (열 고정):** 수식을 오른쪽으로 복사해도 열 이름(A)은 변하지 않고, 아래로 복사하면 행 번호(1)가 변합니다. 'A열'의 값은 항상 참조하고 싶은데, 행은 상대적으로 움직여야 할 때 사용해요.
혼합 참조는 주로 곱셈표를 만들거나, 특정 기준(예: 전체 평균)과 각 항목의 값을 비교하는 계산을 가로세로로 모두 적용할 때 사용됩니다. 당장 성적표 예시에서는 사용하지 않지만, 나중에 좀 더 복잡한 표를 다룰 때 아주 유용하게 쓰일 거예요!
혼합 참조는 열과 행 중 무엇을 고정할지 정확히 이해해야 합니다. 수식을 복사하려는 방향(가로 또는 세로)을 생각하면서 F4 키를 눌러 어떤 부분에 달러 기호가 붙어야 하는지 확인하는 연습이 필요해요. 처음에는 어렵게 느껴질 수 있지만, 여러 번 해보면 익숙해진답니다! 😉