흔한 복사 실수 수정하기

대부분의 복사 실수에는 한 가지 근본 원인이 있습니다. 고정되어 있어야 할 참조가 이동했거나, 이동해야 할 참조가 고정되어 버린 것입니다.

상대 참조는 채울 때 이동하므로, 세율이나 머리글처럼 하나의 공유 셀을 가리키는 수식은 새 행마다 그 셀에서 멀어집니다. 그 결과 잘못된 숫자나 오류가 발생합니다.

이 단원에서는 흔히 발생하는 문제와 각각의 수정 방법을 살펴봅니다.

E1에 0.08의 세율이 있고 D2에 =C2*E1을 입력한 다음 아래로 채운다고 가정해 보겠습니다. 어떤 일이 일어나는지 확인해 보세요:

• D2 = =C2*E1 (정상)
• D3 = =C3*E2 (E2는 비어 있음!)
• D4 = =C4*E3 (여전히 비어 있음)

E 참조가 세율에서 벗어나 이동했습니다. 2행 아래의 행은 0을 곱하므로 모두 0이 됩니다.

공유 셀이 이동하지 않도록 고정합니다. 채우기 전에 수식을 =C2*$E$1로 바꾸세요.

달러 기호는 E 열과 1행을 모두 고정합니다. 이제 아래로 채우면 다음과 같이 됩니다:

• D3 = =C3*$E$1
• D4 = =C4*$E$1

모든 행에서 올바른 세율을 곱합니다. C는 계속 이동하지만 $E$1은 그대로 유지됩니다.

참조가 시트의 가장자리 밖으로 벗어날 위치에 수식을 복사하면 #REF!가 표시됩니다.

예를 들어 =A2-B2와 같은 수식을 A열에 입력하고 왼쪽으로 드래그하면 가리킬 유효한 위치가 없으므로 #REF!를 반환합니다. 이 오류는 "참조가 더 이상 존재하지 않음"을 의미합니다.

수정하려면 참조가 표 안에 남아 있도록 수식을 다른 곳에 붙여넣거나, 유효한 셀을 가리키도록 수식을 다시 작성해야 합니다.

복사한 나눗셈 수식이 빈 셀을 만나 0으로 나누게 되면 #DIV/0!이 표시될 수 있습니다.

일부 C 셀이 비어 있는 열에 =B2/C2를 아래로 채운다고 가정해 보겠습니다. 빈 셀로 나누면 오류가 발생합니다.

수식을 IFERROR로 감싸서 대신 알기 쉬운 빈칸이나 0을 표시하세요: =IFERROR(B2/C2,0). 나눗셈이 가능한 곳에서는 계속 계산하고, 계산할 수 없는 곳에서는 오류를 숨깁니다.

채우기 핸들을 너무 멀리 드래그하여 필요한 셀을 덮어쓰거나, 입력한 값 위에 수식을 붙여넣는 것은 흔한 실수입니다.

즉시 해결하려면 Ctrl+Z(Cmd+Z)를 눌러 실행 취소하세요. 스프레드시트에는 실행 취소 기록이 충분히 길게 유지되므로, 실수로 여러 번 채운 작업도 몇 단계 되돌릴 수 있습니다.

실행 취소한 후에는 올바른 마지막 행에서 드래그를 멈추도록 주의하여 다시 채우세요.

채우기 핸들을 드래그하면 수식과 함께 서식도 복사됩니다. 원하지 않는 곳에 테두리, 색상 또는 통화 서식이 퍼질 수 있습니다.

수식만 복사하려면 드래그한 후 자동 채우기 옵션 버튼을 사용하고 서식 없이 채우기를 선택하세요. 붙여넣을 때는 선택하여 붙여넣기를 사용하고 수식만 선택하면 대상 셀의 모양을 그대로 유지할 수 있습니다.

무언가 잘못되어 보일 때 가장 먼저 할 일은 항상 같습니다. 문제가 있는 셀을 클릭하고 수식 입력줄을 읽어 보세요.

각 참조가 의도한 위치를 가리키는지 확인하세요. $E$1이어야 할 상수가 E5로 표시된다면 고정이 빠졌다는 뜻입니다. 참조가 #REF!로 표시된다면 대상이 삭제되었다는 뜻입니다. 수식 입력줄을 확인하면 수수께끼 같은 잘못된 숫자를 분명한 해결책으로 바꿀 수 있습니다.

시트 전체를 한 번에 점검하려면 수식 표시 보기를 켜세요. Excel에서는 Ctrl+`(백틱 키)를 누르고, Google Sheets에서는 보기에서 수식 표시를 선택합니다.

모든 셀에 결과 대신 수식이 표시되므로, 열을 훑어보면서 참조가 이동한 행을 즉시 찾을 수 있습니다. 바로 가기 키를 다시 누르면 일반 보기로 돌아갑니다.

가장 좋은 해결책은 애초에 오류를 피하는 것입니다. 드래그하기 전에 모든 참조를 살펴보고 결정하세요. 각 행에 따라 이동해야 할까요, 아니면 고정되어야 할까요?

하나의 세율, 머리글 합계 또는 조회 표처럼 고정해야 하는 항목에는 달러 기호를 추가하세요. 간단히 추가하려면 참조 안을 클릭하고 F4를 누르세요. 그러면 고정 옵션이 차례로 바뀝니다. 먼저 고정하고, 그다음 채우세요.

첫 번째 행을 제외한 모든 행의 수수료 열이 0이라고 가정해 보겠습니다. 5행을 클릭했더니 수식 입력줄에 =C5*E4가 표시되지만, 세율은 E1에 있습니다.

원인은 분명합니다. E1을 고정하지 않아 E4로 이동한 것입니다. 원래 수식을 =C2*$E$1로 수정한 다음 열을 다시 채우세요. 이제 모든 행에 올바른 값이 표시됩니다. 달러 기호 하나로 열 전체를 수정했습니다.

이제 복사할 때 깨지는 수식을 수정할 수 있습니다:

• 이동하는 상수는 $E$1처럼 달러 기호로 고정하면 수정됩니다.
• #REF!는 참조가 표 밖으로 벗어났다는 뜻이고, #DIV/0!는 빈 셀로 나눴다는 뜻이며 IFERROR로 처리할 수 있습니다.
• Ctrl+Z를 사용하면 잘못 채운 작업을 실행 취소할 수 있고, 서식 없이 채우기를 사용하면 서식을 깔끔하게 유지할 수 있습니다.
• 수식 입력줄 또는 수식 표시 보기로 문제를 진단하고, 채우기 전에 참조를 고정하세요.

수식을 올바르게 복사하기를 완료했습니다.