절대 참조는 수식을 복사할 때 셀을 고정합니다. =B2*$E$1에서 달러 기호가 E1을 고정하므로, 수식을 아래로 채워도 모든 행이 여전히 E1을 곱하고 B2는 B3, B4로 바뀌어 갑니다. 달러 기호를 붙이려면 수식에서 참조를 클릭하고 F4(Mac에서는 Cmd+T)를 누르세요.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $425 | ||
| 4 | Chen | $15,200 | $760 | ||
| 5 | Dina | $9,800 | $490 | ||
| 6 | Eli | $11,000 | $550 |
C2는 한 번 입력하고 아래로 채운 것입니다. C4를 클릭하면 수식이 =B4*$E$1입니다. 매출 셀은 4행으로 옮겨졌고 비율은 E1에 남았습니다. E1의 비율을 8%로 바꾸면 모든 수수료가 바뀝니다.
상대 참조와 절대 참조
| 참조 | 이름 | 한 행 아래, 한 열 오른쪽으로 복사하면 |
|---|---|---|
A1 | 상대 참조 | B2 |
$A$1 | 절대 참조 | $A$1 |
A$1 | 혼합 참조: 행 고정 | B$1 |
$A1 | 혼합 참조: 열 고정 | $A2 |
B2 같은 일반 참조는 상대 참조입니다. 엑셀은 이것을 "나로부터 이 위치에 있는 셀"로 저장하므로, 한 행 아래로 복사하면 한 행 아래를 가리킵니다. 행마다 데이터를 다룰 때 바로 원하는 동작이고, 이것이 기본값입니다. 열 문자나 행 번호 앞의 $가 그 부분을 고정합니다.
대표적인 실수: $ 없이 아래로 채우기
다시 수수료 시트인데, 이번에는 C2에 달러 기호 없이 =B2*E1이 들어 있습니다. 첫 행은 맞지만 나머지는 0입니다.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $0 | ||
| 4 | Chen | $15,200 | $0 | ||
| 5 | Dina | $9,800 | $0 | ||
| 6 | Eli | $11,000 | $0 |
C3에는 =B3*E2가 있습니다. 비율 참조가 빈 셀인 E2로 내려갔고, 빈 셀은 0으로 계산됩니다. 여기서 고쳐 보세요. C2를 클릭하고 수식을 =B2*$E$1로 바꾼 뒤 Enter를 누르면 C3:C6이 C2의 복사본이므로 열 전체가 따라 바뀝니다. 합계 대비 비율을 구하는 =B2/B7처럼 고정할 셀이 나누는 수라면, 같은 실수가 0 대신 #DIV/0!으로 나타납니다(대표적인 경우가 합계 대비 퍼센트입니다).
F4를 눌러 달러 기호 붙이기
수식을 입력하거나 편집하는 중에 커서를 참조 안(또는 바로 뒤)에 두고 F4를 누릅니다. 누를 때마다 다음 형태로 바뀝니다:
E1 -> $E$1 -> E$1 -> $E1 -> E1
많은 노트북에서는 F4가 화면이나 소리를 조절하므로 Fn+F4를 누르세요. Mac에서는 Cmd+T 또는 Fn+F4를 씁니다. $ 기호를 직접 입력해도 됩니다.
혼합 참조: 행이나 열만 고정하기
혼합 참조에는 달러 기호가 하나 있습니다. $A2는 항상 A열을 읽지만 행은 움직이게 하고, B$1은 항상 1행을 읽지만 열은 움직이게 합니다. 두 참조를 한 수식에 넣으면, 수식 하나를 표 전체에 채워 곱셈표를 만들 수 있습니다:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
B2에는 =$A2*B$1이 있습니다. F6을 클릭하면 =$A6*F$1이 들어 있는데, A열의 행 번호에 1행의 열 번호를 곱하므로 25가 나옵니다. B2에서 달러 기호 하나를 지우면 복사본들이 머리글 대신 이웃 셀을 곱하기 시작해 표가 무너집니다.
같은 패턴으로 여러 할인율의 가격 목록도 만들 수 있습니다. A열 아래로 가격을, 1행에 할인율을 두고 =$A2*(1-B$1)을 씁니다.
한쪽만 고정한 범위로 누계 구하기
범위는 한쪽 끝만 고정할 수도 있습니다. =SUM($B$2:B2)는 시작점이 항상 B2이고, 끝은 수식을 채우면서 아래로 움직이므로 각 행이 자기 행까지의 모든 값을 더합니다:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 420 | 420 |
| 3 | Feb | 380 | 800 |
| 4 | Mar | 510 | 1310 |
| 5 | Apr | 450 | 1760 |
| 6 | May | 470 | 2230 |
C6에는 =SUM($B$2:B6)이 들어 있고, 다섯 달 전체의 합계인 2230을 보여 줍니다. 같은 방식으로 한쪽만 고정한 범위를 쓰면 =COUNTIF($A$2:A2,A2)가 값이 지금까지 몇 번 나왔는지 세는데, 처음 이후의 중복을 표시할 때 이 방법을 씁니다.
연습: 표 전체에 수식 하나
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | 10% | 20% | 30% |
| 2 | $40.00 | |||
| 3 | $25.00 | |||
| 4 | $60.00 | |||
| 5 | $18.00 |
직접 해 보세요: B2에 첫 번째 상품의 B1 할인 후 가격을 쓰세요. 같은 수식을 가로와 세로로 D5까지 채웠을 때 표의 모든 가격이 나오도록 $를 쓰세요.
시트는 채우기 핸들처럼 여러분의 수식을 B2:D5의 모든 셀에 복사하고, 확인 버튼이 열두 개의 결과를 모두 읽습니다. 달러 기호가 올바르지 않으면 3행이나 C열의 복사본이 잘못된 가격이나 잘못된 할인율을 읽습니다.
다른 시트나 조회 표에 대한 절대 참조
달러 기호는 시트 이름과 함께 써도 똑같이 동작합니다: =B2*Settings!$B$1. 가장 중요한 곳은 조회 함수입니다. 조회 값은 움직여도 표는 제자리에 있어야 하기 때문입니다. =VLOOKUP(A2,$E$2:$F$10,2,FALSE)를 아래로 채우면 계속 E2:F10을 검색하지만, =VLOOKUP(A2,E2:F10,2,FALSE)는 복사할 때마다 표가 한 행씩 내려가 첫 행들을 놓치기 시작합니다(VLOOKUP). 고정된 셀을 많은 수식에서 쓴다면 수식 > 이름 정의로 이름을 붙이고 =B2*Rate라고 쓸 수도 있습니다. 이렇게 정의한 이름은 $E$1처럼 모든 수식에서 같은 셀을 가리킵니다.
자주 묻는 질문
엑셀 수식에서 $ 기호는 무엇을 뜻하나요?
바로 뒤에 오는 참조 부분을 고정합니다. $E$1에서는 E열과 1행이 모두 고정되므로 수식을 어디에 복사해도 참조는 E1로 남습니다. E$1은 행만, $E1은 열만 고정합니다.
엑셀 절대 참조 단축키는 무엇인가요?
수식을 편집하는 중에 참조 안을 클릭하고 F4를 누릅니다(많은 노트북에서는 Fn+F4). 누를 때마다 $A$1, A$1, $A1, A1 순서로 바뀝니다. Mac에서는 Cmd+T나 Fn+F4를 누릅니다.
상대 참조와 절대 참조는 무엇이 다른가요?
B2 같은 상대 참조는 수식을 복사하면 움직여서, 한 행 아래에서는 B3이 됩니다. $B$2 같은 절대 참조는 $B$2로 남습니다. 비율이나 합계처럼 모든 행이 필요로 하는 셀 하나에는 절대 참조를 쓰세요.
엑셀의 혼합 참조는 무엇인가요?
달러 기호가 하나인 참조입니다. $A2는 열을 고정하고 행은 움직이게 하며, B$1은 행을 고정하고 열은 움직이게 합니다. =$A2*B$1을 표의 가로와 세로로 채우면 곱셈표가 만들어집니다.
수식을 아래로 끌면 0이나 #DIV/0!이 나오는 이유는 무엇인가요?
고정되어야 할 참조가 복사와 함께 움직였기 때문입니다. 2행에 =B2/B7이 있으면 3행에는 =B3/B8이 들어가고 B8은 비어 있습니다. =B2/$B$7로 합계를 고정하고 다시 채우세요.
