구글 스프레드시트에서 계산할 때 데이터를 추가해도 자동으로 계산 범위가 늘어난다면 정말 편리하겠죠!
이렇게 셀을 참조하는 방식을 열린 참조라고 하는데 SUM 함수에 활용하는 방법을 소개하고 그리고 누계를 구하면서 절대 참조 방식을 소개하겠습니다.
수식을 작성할 때 절대 참조 꼭 알아야 할 참조 방식입니다. 예시를 보면서 설명하겠습니다.
실습 파일 다운로드
비용 집행 내역서에서 금액의 합계를 구할 [H4] 셀을 선택합니다. 그리고 =을 입력하고
=을 입력했더니 SUM 함수가 자동으로 입력되고 금액의 합계를 구하는 수식이 완성 되었네요.
<Enter>를 누르면 수식을 직접 작성할 필요 없이 금액의 합계를 구합니다.
수식을 더블클릭해서 참조하는 범위를 보면 금액 범위에 해당하는 [H7:H10] 셀 범위를 참조하고 있습니다.
그런데 이렇게 작성한 수식은 데이터가 추가되더라도 합계가 변하지 않습니다.
해당 범위를 고정해서 참조하고 있는 것을 알 수 있습니다.
그런데 우리는 데이터를 추가할 때마다 합계가 자동으로 계산되는 수식을 작성하려고 했었죠.
그러면 이 수식은 지우고 다시 수식을 작성합니다.
=을 입력하고 SUM 함수를 입력합니다. 그리고 완성된 수식은 무시하고 인수의 괄호를 입력합니다.
그리고 합계를 구할 금액의 첫번째 셀을 선택하고 :(콜론)을 입력합니다. 그리고 H 열 머리글을 입력합니다.
스프레드시트에서는 열 머리글이나 행 머리글을 수식에 사용할 수 있습니다.
그랬더니 참조하는 범위도 [H7] 셀에서 [H] 열 전체가 범위로 지정되었죠? 닫는 괄호를 입력하고 <Enter>를 누릅니다.
그리고 [H11] 셀에 값을 추가해 보겠습니다.
109만 원이었던 결과 값이 119만 원으로 변한 것을 알 수 있네요. 이렇게 스프레드시트에서는 열린 참조방식이라고 해서 열 머리글 또는 행 머리글을 수식에서 사용할 수 있습니다.
엑셀에서 이렇게 수식을 작성하면 오류가 발생합니다. 그래서 동적 범위는 OFFSET 함수를 사용해서 이름을 정의하거나 임의로 [H7:H2000] 셀 범위와 같이 큰 범위를 적용해야지만 구할 수 있는거죠.
이번에는 누적계를 구하면서 절대 참조방식을 소개하겠습니다.
남은 금액을 구하려면 입금액에서 사용 금액을 빼면 되겠죠? 그런데 이미 사용 금액에 음수 표시가 되어 있네요.
그러면 입금액에서 사용 금액을 빼는 것이 아니라 입금액에서 사용 금액을 더해야합니다.
이 경우에도 단순히 더하는 수식을 사용하는 것이 아니라 SUM 함수를 이용해서 참조 방식을 활용하면 쉽게 남은 금액을 구할 수 있습니다.
그럼 남은 금액을 구하는 수식을 작성해 보겠습니다.
=SUM 함수를 입력합니다. 그리고 괄호를 열고 입금액[C4] 셀을 선택한 다음 <F4> 키를 눌러 절대 참조 합니다.
그리고 :(콜론)을 입력합니다.
그리고 사용 금액에 해당하는 [D4] 셀을 선택합니다. 하지만 에러가 표시되죠! 스프레드시트에서는 콜론 이후 셀을 클릭해 참조하면 오류가 발생하므로 직접 [D4] 셀을 입력해야 합니다. 이런 부분이 불편하지만 방법 알고 사용해야겠죠.
남은 금액 수식을 더블클릭한 후 콜론 이후에 직접 D4를 입력합니다. 그리고 사용 금액 D4는 상대 참조로 그대로 둡니다.
<Enter>를 누르면 자동 완성 제안사항이 뜨는데 이 또한 스프레드시트의 편리한 기능입니다!
<Ctrl + Enter>를 누르거나 체크 표시를 하면 남은 금액이 구해집니다.
이 수식의 원리를 볼까요?
간식구입 후 남은 금액을 구한 수식을 확인해 보면 첫 번째 [C4] 셀은 절대 참조가 되었고 사용 금액에 해당하는 [D5] 셀은 상대 참조되어서 수식을 복사한 위치만큼 참조 셀이 변했습니다. 이렇게 변하는 주소를 참조하는 방식을 상대 참조라고 합니다.
상대 참조와 절대 참조 방식이 아직 익숙하지 않다면 관련 영상을 만들어둔 것이 있으니 학습하시면 됩니다.
https://youtu.be/vmD1qfCwhzs?si=gWw422r9y9QXIWye
자료구입 후 남은금액을 구한 수식도 더블클릭해 보면 [C4] 셀은 절대 참조되어 고정되어 있고 [D4] 셀은 상대 참조되어 [D6] 셀을 참조하고 있습니다. 그러면 입금액에서 사용 금액 범위를 모두 합쳐 남은 금액을 구하게 되는 거죠!
지원금이 입금된 내역에 남은 금액의 결과를 보면 입금액은 고정되고 사용 금액의 범위는 확장되어서 남은 금액에 지원금이 추가된 상태로 계산되었죠? 이렇게 절대 참조는 특정 값을 고정적으로 참조해야 할 경우 사용되는 방입니다.
만일 자동 완성 창이 실행되지 않는다면 [도구] - [추천 컨트롤] - [수식 추천 사용]에 체크 표시가 되어 있는지 확인해 보세요.
이번 시간에 배운 열린 참조와 절대 참조 어떠셨나요?
이 두 가지는 구글 스프레드시트를 효율적으로 사용하는데 정말 큰 도움이 되는 기능입니다.
꼭 직접 연습해 보시고 더 궁금한 점이나 배우고 싶은 내용이 있다면 댓글로 남겨주세요.
저는 더 유익한 내용으로 찾아뵙겠습니다.
수고하셨습니다.