스프레드시트 2강. 수식과 참조의 원리
수식을 복사했더니 엉뚱한 값이 나왔다면, 엑셀은 정확히 시킨 대로 한 겁니다.
풀고 시작
상대 참조는 주소가 아니라 방향입니다
엑셀 초보가 가장 크게 놀라는 순간은 수식을 복사했을 때입니다. B2에 =A2*2를 넣고 아래로 끌면 B3은 =A3*2가 됩니다. 편리하죠. 그런데 세율이 든 E1을 곱하려고 =A2*E1을 복사하면 아래 칸은 =A3*E2가 되어 빈 셀을 곱하게 됩니다.
원인은 엑셀이 참조를 주소로 기억하지 않기 때문입니다. 엑셀이 실제로 기억하는 것은 "나로부터 왼쪽으로 한 칸"이라는 상대적 위치입니다. 그래서 수식이 아래로 내려가면 가리키는 곳도 함께 내려갑니다. 버그가 아니라 설계입니다.
이 움직임을 멈추는 것이 달러 기호입니다. 붙은 자리는 고정됩니다.
| 표기 | 뜻 | 복사하면 |
|---|---|---|
| A1 | 상대 참조 | 행과 열이 모두 따라 움직입니다 |
| $A$1 | 절대 참조 | 아무것도 움직이지 않습니다 |
| A$1 | 혼합 참조 | 열은 움직이고 행은 고정됩니다 |
| $A1 | 혼합 참조 | 열은 고정되고 행은 움직입니다 |
F4 키를 누르면 이 네 가지가 차례로 바뀝니다. 손으로 달러를 치지 마시고 F4를 누르세요.
혼합 참조는 구구단 표 같은 것을 만들 때 진가를 발휘합니다. 세로로는 열을 고정하고 가로로는 행을 고정해 두면, 수식 하나를 만들어 표 전체에 끌어 놓기만 하면 됩니다. 시험도 이 상황을 좋아합니다.
시트를 넘고 파일을 넘는 참조
다른 시트의 셀을 가리킬 때는 시트 이름 뒤에 느낌표를 붙입니다. =Sheet2!A1 같은 식이죠. 시트 이름에 공백이나 한글이 있으면 작은따옴표로 감쌉니다.
여러 시트의 같은 자리를 한꺼번에 더하는 방법도 있습니다. =SUM(Sheet1:Sheet3!A1)처럼 적으면 세 시트의 A1을 모두 더합니다. 이것을 3차원 참조라고 부릅니다. 월별 시트를 만들어 두고 연간 합계를 낼 때 쓰죠.
다른 통합 문서를 가리키면 대괄호로 파일 이름이 들어갑니다. 원본 파일이 닫혀 있으면 경로 전체가 나타나고, 원본이 옮겨지거나 지워지면 연결이 깨집니다.
오류 메시지는 원인을 이름으로 말해 줍니다
엑셀의 오류 표시는 짜증나는 기호가 아니라 진단 결과입니다. 무엇이 잘못됐는지 이름으로 알려 주고 있죠.
| 오류 | 무슨 일이 있었나 |
|---|---|
| #DIV/0! | 0으로 나눴습니다. 나누는 칸이 비었을 때도 납니다 |
| #N/A | 찾는 값이 없습니다. 찾기 함수에서 주로 납니다 |
| #NAME? | 이름을 못 알아봅니다. 함수 이름 오타나 따옴표 누락입니다 |
| #REF! | 참조가 사라졌습니다. 가리키던 셀이나 시트를 지웠습니다 |
| #VALUE! | 종류가 맞지 않습니다. 문자를 숫자처럼 계산했습니다 |
| #NUM! | 숫자가 너무 크거나 함수가 감당 못 할 값입니다 |
| #NULL! | 교차하지 않는 두 범위를 공백으로 이었습니다 |
이 가운데 #REF!가 가장 위험합니다. 다른 오류는 수식을 고치면 되지만 #REF!는 가리키던 대상 자체가 사라진 것이라 무엇을 가리켰는지 되살릴 단서가 없습니다. 행이나 시트를 지우기 전에 참조하는 곳이 있는지 확인해야 하는 이유죠.
셀 폭이 좁아 #####이 뜨는 것은 오류가 아닙니다. 값은 멀쩡하고 자리만 모자란 것이니 열 너비만 넓히면 됩니다.
이름을 붙이면 수식이 문장이 됩니다
=B2*$E$1보다 =매출*세율이 읽기 쉽습니다. 셀이나 범위에 이름을 정의하면 수식이 사람의 말에 가까워집니다.
이름에는 규칙이 있습니다. 첫 글자는 문자나 밑줄이어야 하고 숫자로 시작할 수 없습니다. 공백을 쓸 수 없어 밑줄이나 마침표로 대신합니다. 셀 주소와 같은 이름(A1, B2 같은)은 쓸 수 없고, 대소문자를 구분하지 않습니다. 정의한 이름은 기본적으로 절대 참조로 동작하므로 복사해도 흔들리지 않습니다.