지난 7편에서는 외부에서 가져온 뒤죽박죽 섞인 텍스트 데이터를 SPLIT 함수와 기본 기능을 활용해 깔끔하게 칸별로 쪼개는 방법을 배웠습니다. 이제 데이터를 알맞은 칸에 정리했으니, 다시 함수를 활용해 업무를 자동화할 차례입니다.
구글 스프레드시트를 쓰다 보면 가장 허탈할 때가 있습니다. 분명 첫 번째 행에 수식을 완벽하게 작성해서 원하는 결과값을 얻었는데, 이 수식을 아래로 길게 복사해서 붙여넣거나 마우스로 드래그(채우기 핸들)했더니 두 번째 행부터 값이 0으로 나오거나, #N/A, #REF! 같은 오류가 뜨며 수식이 와르르 깨지는 현상입니다.
"첫 줄은 분명히 잘 나왔는데 왜 밑으로 복사하면 계산이 엉망이 되지?" 하며 수식을 하나하나 더블클릭해 보면, 내가 지정했던 기준 셀의 위치가 아래로 한 칸씩 밀려 내려가 있는 모습을 발견하게 됩니다.
이것은 구글 스프레드시트의 기본 작동 원리인 '상대참조' 때문에 발생하는 현상입니다. 이 밀림 현상을 완벽하게 방지하고 수식을 단단하게 고정해 주는 비밀 무기가 바로 달러 기호($)를 사용하는 '절대참조'입니다.
처음 수식을 배울 때 이 달러 기호의 위치가 헷갈려 밤새 수작업으로 함수를 수정했던 제 경험을 바탕으로, 절대참조의 원리와 활용법을 아주 쉽게 풀어드리겠습니다.
1. 수식이 자꾸 움직이는 이유: 상대참조의 비밀
구글 스프레드시트는 기본적으로 매우 똑똑하고 유연하게 설계되어 있습니다. 예를 들어 C2 셀에 =A2+B2라는 수식을 넣고 이를 C3 셀로 복사하면, 컴퓨터는 자동으로 수식을 =A3+B3으로 변경합니다. 사용자가 일일이 행 번호를 바꿀 필요가 없도록 자동으로 행의 위치를 맞춰주는 것입니다. 이를 '상대적인 위치를 참조한다'고 해서 상대참조라고 부릅니다.
일반적인 가로 계산(품목별 수량 x 단가)을 할 때는 이 상대참조가 엄청나게 편리합니다. 하지만 다음과 같은 상황에서는 이 똑똑함이 오히려 독이 됩니다.
모든 제품의 가격에 '고정된 환율(예: E1 셀)'을 곱해야 할 때
VLOOKUP 함수를 쓸 때 '참조할 원본 표의 범위(예: A2:C50)'가 고정되어 있어야 할 때
전체 총합계 대비 각 항목의 '차지하는 비율'을 구해야 할 때
이럴 때 수식을 아래로 복사하면, 고정되어 있어야 할 환율 셀(E1)이나 원본 표의 범위(A2:C50)마저 아래로 E2, E3, A3:C51로 줄줄이 밀려 내려가면서 계산이 완전히 깨지게 됩니다.
2. 달러 기호($)는 셀을 붙잡아두는 '자석'입니다
이렇게 수식이 밀리는 것을 막으려면 컴퓨터에게 "다른 건 밀려도 좋은데, 이 셀 주소만큼은 절대로 움직이지 말고 꽉 붙잡고 있어!"라고 명령해야 합니다. 그때 주소 앞에 붙이는 자석이 바로 달러 기호($)입니다.
알파벳(열)과 숫자(행)로 이루어진 셀 주소에서 달러 기호가 어디에 붙느냐에 따라 고정되는 대상이 달라집니다.
$A$1(전체 고정 / 절대참조)
알파벳 앞에도 달러, 숫자 앞에도 달러가 붙었습니다.
이 수식은 아래로 복사하든, 오른쪽으로 복사하든 무조건 A1 셀만 바라봅니다. 실무에서 가장 많이 쓰는 형태입니다. 환율 고정이나 VLOOKUP 원본 범위를 지정할 때 필수적입니다.
A$1(행 고정 / 혼합참조)
숫자(행) 앞에만 달러가 붙었습니다.
수식을 아래로 아무리 복사해도 1행이라는 위치는 변하지 않습니다. 좌우로 복사할 때는 B1, C1으로 열이 바뀝니다. 가로로 긴 표에서 상단 타이틀 행을 고정할 때 유용합니다.
$A1(열 고정 / 혼합참조)
알파벳(열) 앞에만 달러가 붙었습니다.
수식을 오른쪽으로 아무리 복사해도 A열이라는 위치는 변하지 않습니다. 위아래로 복사할 때는 A2, A3로 행이 바뀝니다. 세로로 긴 표에서 좌측 기준 열을 고정할 때 사용합니다.
3. 실무자의 손목을 보호하는 단축키: F4
수식을 입력할 때 키보드로 일일이 특수문자 $를 타이핑하는 것은 매우 번거로운 일입니다. 작업 속도도 느려지죠. 구글 스프레드시트에는 이를 단 0.1초 만에 해결해 주는 마법의 단축키가 있습니다. 바로 키보드 맨 위의 F4 키입니다.
수식을 작성하면서 셀 주소(예: A1)를 클릭하거나 입력한 직후, 키보드의 F4를 눌러보세요. 누를 때마다 형태가 순서대로 자동으로 바뀝니다.
1번 누름:
$A$1(열과 행 모두 고정)2번 누름:
A$1(행만 고정)3번 누름:
$A1(열만 고정)4번 누름:
A1(고정 해제, 다시 처음으로)
내가 원하는 고정 형태가 나올 때까지 F4 키를 톡톡 눌러주기만 하면 됩니다. 특히 VLOOKUP 함수를 쓸 때 범위를 지정한 후 바로 F4를 한 번 눌러 전체 고정($A$2:$C$50)을 해주는 습관을 들이면, 수식이 깨져서 밤을 새우는 일은 완전히 사라집니다.
4. 수식이 깨졌을 때 빠르게 자가진단하는 법
수식을 복사했는데 결과가 이상하다면 당황하지 말고 에러가 난 셀을 더블클릭(또는 F2 누름)해 보세요. 그러면 수식에 사용된 셀들이 파란색, 빨간색 등 유치원 배경처럼 유색 박스로 표시됩니다.
이때 원래 고정되어 있어야 할 원본 표나 기준 셀의 박스가 엉뚱한 빈칸이나 밑으로 삐져나가 있다면 "아, 내가 절대참조($)를 빼먹었구나!"라고 바로 진단할 수 있습니다. 수식의 기준점으로 돌아가 에러가 난 인수에 F4를 눌러 고정해 준 뒤 다시 아래로 복사하면 문제가 아주 깔끔하게 해결됩니다.
핵심 요약
구글 스프레드시트는 수식을 복사할 때 위치가 자동으로 변하는 '상대참조'가 기본값입니다.
특정 기준 셀이나 원본 데이터의 범위가 밀리지 않도록 꽉 붙잡아두려면 주소에 달러 기호
($)를 붙이는 '절대참조'를 써야 합니다.수식 입력 중
F4단축키를 누르면 일일이 달러를 타이핑할 필요 없이 전체 고정, 행 고정, 열 고정으로 빠르게 전환할 수 있습니다.
다음 편 예고
수식을 안전하게 고정하는 법을 배웠으니, 이제 입력된 데이터의 품질을 높이는 정제 기술을 배울 차례입니다. 다음 편에서는 데이터에 중복으로 들어간 불필요한 값을 단숨에 정리해 주는 "데이터에 포함된 중복 값 제거 및 공백 한 번에 정리하는 법"에 대해 알아보겠습니다.
댓글 유도 질문
수식을 복사했다가 값이 밀려서 당황하셨던 적이 있으신가요? 어떤 데이터를 계산할 때 가장 자주 겪으셨는지 댓글로 공유해 주시면 참조 고정 팁을 알려드리겠습니다!
0 댓글