[구글 스프레드시트] 구글시트 셀참조_절대 참조와 상대 참조, 열린참조 완벽이해

구글시트 셀참조는 구글 시트에서도 정말 중요한 내용중의 하나입니다. 구글 시트에서 수식을 하나 잘 만들어서 아래로 쭉~ 복사했는데, "어? 왜 값이 이상하지?" 이런 경험해 보신 적 있으실 겁니다. 복사만 했는데도 엉뚱한 결과만 나오는 현상.

구글시트 셀참조의 핵심은 절대참조($)와 상대참조 개념입니다. 이걸 제대로 모르면 보고서 만들 때 시간을 다 잡아먹고, 실수로 잘못된 데이터를 보고하는 일이 생길 수도 있습니다. 잘 알면 작업시간이 엄청 줄어들고요.

절대참조($)와 상대참조의 기술

1. 헷갈리기 쉬운 개념 정리: 상대참조란?

우리가 구글 시트에서 별도로 조치 없이($기호의 사용) 사용하는 모든 구글시트 셀참조는 기본적으로상대참조 입니다. 

상대참조는 말 그대로, 수식을 복사하는 상대적인 위치에 따라 참조하는 셀 주소도 자동으로 바뀌는 방식입니다. 예제를 보여드리겠습니다.

위 표에서 D2셀에 =B2*C2라는 수식을 입력하고, 이 D2 셀을 D3, D4로 복사해 보시면

    - D2 셀에는 B2*C2 (단가 B2 * 수량 C2) 값이 나옵니다. 값 15,000

    - D3 셀에는 B3*C3 (단가 B3 * 수량 C3) 값이 나옵니다. 값 10,000

    - D4 셀에는 B4*C4 (단가 B4 * 수량 C4) 값이 나옵니다. 값 10,000

보이시죠? 수식을 아래로 복사하니, 참조하던 행(Row, 2행 이 3행, 4행으로)이 자동을 따라서 바뀌었습니다. 이게 일반적인 상대참조 입니다. 대부분의 계산에는 이게 맞습니다.

2. 수식 복사 오류를 막는 마법 - 절대참조($) 기호 사용법

문제는, 모든 수식 복사가 이렇게 상대적인 변화를 원하지 않을 때 발생합니다. 특정 셀, 예를 들어 '공통 할인율'이나 '단일 세율'같은 값이 들어 있는 셀은 수식을 복사해도 주소가 절대 변하지 않고 고정되어야 할 때가 있습니다. 

이때 바로 $(달러기호)를 붙여서 절대참조로 만드는 겁니다. $를 붙인 열 이나 행은 수식을 복사 또는 드래그해도 그 주소가 변경되지 않습니다.

상황: 모든 매출액에 셀B2에 있는 10% 수수료율을 곱해 수수료(C열)를 계산고 있을때,

    - C2에 수식 =A2*B2를 입력하고 C3, C4로 복사 또는 드래그하하면 어떻게 될까요?

        👉 C3에서는 수식이 =A3*B3이 되면서, B3은 빈칸(또는 0)이 됨. 오류 발생!

        👉 B3은 수수료율이 아니라 빈칸입니다. 셀 주소가 상대참조이다 보니 주소가 변했습니다.

    - 해결책은 절대참조($)로 고정.

        👉 수수료율 B2를 복사해도 움직이지 않도록 $B$2로 고정합니다.

        👉 C2수식 =A2*$B&2

결과 입니다.

        👉 C2: = A2*$B$2(A2는 상대참조, $B$2는 절대참조)

        👉 C3: = A3*$B$2(A3는 상대참조, $B$2는 절대참조)

        👉 C4: = A4*$B$2(A4는 상대참조, $B$2는 절대참조)

💡TIP!!

    구글시트에서 수식 입력 중 $를 일일이 타이핑하기 귀찮다면, 참조할 셀 주소를 선택한 후 <F4>키를 누르면 자동으로 절대참조($A$1) 또는 혼합참조(A$1 or $A1), 상대참조(A1) 순서로 바꿀 수 있습니다.

3. 혼합참조의 활용_행만 참조하기 / 열만 참조하기

이제는 절대참조와 상대참조를 섞어 쓰는 혼합참조를 알아보겠습니다.

    👍A$1: 열(A)은 상대적으로 바뀌고, 행(1)은 절대적으로 고정.(아래로 복사해도 1행 고정)

    👍$A1: 열(A)은 절대적으로 고정되고, 행(1)은 상대적으로 바뀜.(오른쪽으로 복사해도 A열 고정)

상황: A열의 외화단위와 C열의 현재 환율을 이용하여, C1부터 E1의 금액에 따른 환산 가치를 C2:E4영역에 한번의 수식으로 쫙 채워 넣어 보기입니다.

    1. C2에 수식을 넣습니다. C2는 1,000원을 1달러 환율로 나눈 값(달러 가치)을 구합니다.

        = C1/B2

            👉 C1(금액): C는 변화 / 1은 고정 - 오른쪽으로 복사할 때 C~E열은 변해야 하지만, 아래로 복사할 때 1행은 절대 고정 되어야 합니다. C$1

            👉 $B2(환율): B는 고정 / 2는 변화 - 오른쪽으로 복사할 때 B열은 절대 고정되어야 하지만, 아래로 복사할 때 2~4행은 변해야 합니다. B$2

    2. 최종수식 = C$1/$B2

    3. C2에 수식을 넣고, 오른쪽으로 복사한 다음, 아래로 복사하면 모든셀이 완벽하게 채워집니다.

결과는 아래와 같습니다.(저는 결과를 보여드리기 위해서 셀에서 수식이 보이도록 수식보기(단축키 <Ctrl>+<~>)를 했습니다.

이게 바로 절대 참조와 상대참조를 섞은 혼합 참조의 위력입니다.

📢주의사항: 수식 복사 시 빈 셀 참조는 재앙입니다.

실무에서 수식 복사 오류가 나면 대부분 $를 빼먹거나 잘못 넣었기 때문입니다. 수식을 복사하기 전에 이 수식에서 움직이면 안 되는 셀 주소가 무엇인가? 를 꼭 따져보고 $를 붙여 주세요. 잘못된 참조가 수백, 수천 줄로 복사되면 데이터 전체가 엉망이 됩니다. 이거 잘못하면 큰일 납니다.

4. 끝없이 늘어나는 데이터 처리 - 열린참조

마지막으로 이건 정말 구글시트의 강력한 기능입니다. 보고서가 매일 혹은 매주 업데이트되면서 데이터가 아래로 계속 쌓이는 경우가 많습니다. 그때마다 수식의 끝 범위를 A100, A101 이렇게 수정하는 작업은 정말 비효율적이고 또 오류가 날 가능성도 높습니다.

이런 상황을 대비해서 구글 시트는 "열린참조"라는 강력한 기능을 제공합니다. 이건 정말 놀라운 기능입니다.

열린참조란, 행 번호를 생략하거나 범위의 끝 행 번호를 생략하여 참조 범위를 지정하는 방식입니다. 이렇게 하면 데이터가 A열의 끝까지 자동으로 늘어나도 수식을 수정할 필요가 없습니다.

예제 링크 : (예제) 구글시트 셀참조_열린참조 ← 클릭하셔서 사본으로 저장하시면 됩니다.

사본만드는 법: ↑[링크 클릭] - [파일] - [사본만들기 클릭] - [사본만들기]

상황: D2셀에 B열의 판매액 합계를 구하고 싶습니다. 그런데 상품 데이터는 시간이 지나면서 B5, B6...B1000까지 계속 쌓일 겁니다.

    1. D2에 수식을 입력합니다. B2셀로부터 B열의 마지막 행까지의 모든 값을 합산하라는 뜻입니다.

    2. 수식 =SUM(B2:B)

결과를 확인해 봅시다.

    - 이수식은 B2셀부터 B열의 마지막 행까지의 모든 값을 합산하라는 뜻입니다.

    - 내일 데이터가 B100에 추가되어도, 모데 B1000에 추가되어도 수식은 자동으로 그 값을 계산 포함합니다. 수정 작업 필요 없습니다.!!

💡TIP!!

    - =COUNTIF(A:A, "완료"): A열 전체에서 '완료' 텍스트의 개수를 셉니다.(A1부터 A 끝까지)

    - =VLOOKUP(D2, A:C, 3, FALSE): A열부터 C열 전체를 검색 범위로 사용합니다. 데이터가 늘어나도 VLOOKUP 범위 수정이 필요없습니다.

📢주의사항: 전체 열을 참조할 경우, B1처럼 수식 제목이나 불필요한 값이 포함되어 계산 오류가 나지 않도록 시작행을 B2등으로 정확히 지정해주는 습관이 중요합니다.