안녕하세요! 엑셀 수식과 셀 참조 정복에 도전하는 제이입니다! 😄 '제이의 엑셀 초보 탈출기' 벌써 스무 번째 시간이네요! 와~ 정말 여기까지 오느라 고생 많으셨어요! 지난 시간에는 엑셀 수식에서 셀 주소를 참조하는 세 가지 방식, 즉 상대 참조(A1), 절대 참조($A$1), 혼합 참조($A1$, A$1)에 대해 자세히 배웠어요. 특히 F4 키로 쉽게 바꾸는 법을 익혔죠?
상대 참조는 수식을 아래나 옆으로 복사할 때 셀 주소가 자동으로 바뀌어서 아주 편리했습니다. 성적표에서 각 학생의 총점이나 평균을 구할 때 유용했죠. 그리고 절대 참조는 특정 셀(예: 보너스 비율)을 고정해서 수식을 복사해도 변하지 않게 할 때 사용했어요. 혼합 참조는 행이나 열 중 하나만 고정할 때 쓴다고 살짝 언급했었죠.
오늘은 이 혼합 참조를 제대로 이해하기 위한 **'수식을 가로, 세로 양방향으로 모두 복사할 때'** 상황에 대해 집중적으로 배워볼 거예요. 이게 처음에는 조금 헷갈릴 수 있지만, 혼합 참조를 능숙하게 사용하게 되면 엑셀로 훨씬 더 복잡하고 유용한 표를 쉽게 만들 수 있답니다. 곱셈표 만들기라는 아주 좋은 예시로 제이와 함께 확실하게 마스터해 봅시다! 💪
상대 참조 복습: 행/열 모두 바뀜 ↔️↕️
가장 기본적인 상대 참조(예: A1)는 수식이 복사되는 방향과 칸 수에 비례하여 참조하는 셀의 행과 열이 모두 바뀝니다.
- C1 셀에 `=A1` 이라고 입력하고 오른쪽으로 한 칸 (D1 셀) 복사하면 수식은 `=B1` 이 됩니다. (열만 바뀜)
- C1 셀에 `=A1` 이라고 입력하고 아래로 한 칸 (C2 셀) 복사하면 수식은 `=A2` 가 됩니다. (행만 바뀜)
- C1 셀에 `=A1` 이라고 입력하고 오른쪽으로 한 칸, 아래로 한 칸 (D2 셀) 복사하면 수식은 `=B2` 가 됩니다. (열, 행 모두 바뀜)
이처럼 상대 참조는 수식의 '상대적인' 위치를 따라갑니다.
절대 참조 복습: 행/열 모두 고정 🔒
절대 참조(예: $A$1)는 열 이름과 행 번호 모두에 $가 붙어있기 때문에, 수식을 어디로 복사하든 참조하는 셀 주소가 절대 바뀌지 않고 항상 $A$1 셀만 바라봅니다.
- C1 셀에 `=$A$1` 이라고 입력하고 오른쪽으로 복사해도 `= $A$1` 입니다.
- C1 셀에 `=$A$1` 이라고 입력하고 아래로 복사해도 `= $A$1` 입니다.
- C1 셀에 `=$A$1` 이라고 입력하고 가로/세로 모두 복사해도 `= $A$1` 입니다.
완전히 고정되어야 하는 특정 값을 참조할 때 사용한다고 지난 시간에 배웠죠!
혼합 참조 다시 보기: 행만 고정 vs 열만 고정 📍🔗
혼합 참조는 셀 주소 중 열 이름이나 행 번호 둘 중 하나에만 $가 붙어있습니다.
- **`A$1` (행 고정):** 행 번호(1) 앞에 $가 있으니, 수식을 아래로 복사해도 행 번호는 1로 고정됩니다. 하지만 열 이름(A) 앞에는 $가 없으니, 수식을 오른쪽으로 복사하면 열 이름은 B, C, D... 로 바뀝니다. '1행'의 값은 고정, 열은 상대적으로 움직일 때 사용합니다.
- **`$A1` (열 고정):** 열 이름(A) 앞에 $가 있으니, 수식을 오른쪽으로 복사해도 열 이름은 A로 고정됩니다. 하지만 행 번호(1) 앞에는 $가 없으니, 수식을 아래로 복사하면 행 번호는 2, 3, 4... 로 바뀝니다. 'A열'의 값은 고정, 행은 상대적으로 움직일 때 사용합니다.
말만 들으면 헷갈리죠? 이걸 가로/세로 모두 복사할 때 어떻게 활용하는지 직접 해보면 이해가 빠릅니다.
행/열 동시 복사 시 혼합 참조 활용: 곱셈표 만들기 ✖️
혼합 참조의 가장 대표적인 활용 예시가 바로 곱셈표(구구단표) 만들기입니다. 아래 표를 보세요. 첫 번째 행에는 1부터 5까지, 첫 번째 열에는 1부터 5까지의 숫자가 있습니다. 가운데 칸들은 첫 번째 행과 첫 번째 열의 숫자를 곱한 결과로 채울 거예요.
| × | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 |
곱셈표의 가장 왼쪽 위 칸 (1과 1이 만나는 곳)에 수식을 입력하고, 이 수식을 오른쪽으로 죽 복사한 다음, 다시 아래로 죽 복사하면 표 전체가 완성되겠죠?
B2 셀 (1 × 1)에 들어갈 수식은 이렇습니다.
`=$A2*B$1`
이 수식이 왜 이렇게 될까요?
- 첫 번째 인수는 곱셈표의 가장 왼쪽 열(A열)에 있는 숫자입니다. B2 셀에서는 A2 셀(숫자 1)이겠죠. 이 수식을 오른쪽으로 복사할 때는 계속 A열을 참조해야 하므로 열(A)을 고정해야 합니다 ($A$). 하지만 아래로 복사할 때는 다음 행의 숫자(A3, A4...)를 참조해야 하므로 행(2)은 상대 참조로 둡니다 (2). 그래서 `$A2` 가 됩니다.
- 두 번째 인수는 곱셈표의 가장 위쪽 행(1행)에 있는 숫자입니다. B2 셀에서는 B1 셀(숫자 1)이겠죠. 이 수식을 아래로 복사할 때는 계속 1행을 참조해야 하므로 행(1)을 고정해야 합니다 ($1$). 하지만 오른쪽으로 복사할 때는 다음 열의 숫자(C1, D1...)를 참조해야 하므로 열(B)은 상대 참조로 둡니다 (B). 그래서 `B$1` 이 됩니다.
결과적으로 B2 셀에는 `=$A2*B$1` 수식이 들어가고, 이걸 가로, 세로로 자동 채우기 하면 엑셀이 알아서 각 셀에 맞는 곱셈 결과를 계산해 줍니다!
| × | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 4 | 5 |
| 2 | 2 | 4 | 6 | 8 | 10 |
| 3 | 3 | 6 | 9 | 12 | 15 |
| 4 | 4 | 8 | 12 | 16 | 20 |
| 5 | 5 | 10 | 15 | 20 | 25 |
곱셈표처럼 행 방향으로도, 열 방향으로도 복사해야 하는 수식에서는 어떤 부분을 고정하고 어떤 부분을 상대 참조로 둘지 신중하게 결정해야 합니다. 이게 바로 혼합 참조의 진정한 힘이 발휘되는 순간이에요!
곱셈표 만들기처럼 간단한 예시로 혼합 참조 연습을 충분히 해보세요. 처음에는 A2*B1, A3*B1... 이렇게 각 셀에 필요한 수식을 먼저 써보고, 어떤 부분이 변하고 변하지 않는지 파악한 후에 혼합 참조를 적용해보는 것이 도움이 됩니다. F4 키를 반복해서 눌러보면서 셀 주소 형태가 어떻게 바뀌는지 눈으로 익히는 것도 중요해요!