구글시트 강좌8 자주 바뀌는 동적인 데이터를 처리 하는 방법 COUNT와 OFFSET

함께 해주시는 분들이 계셔서 즐거운 구글시트 강좌의 @youngbinlee 빈누입니다.

구글시트 강좌1 IMPORTHTML로 한방에 하는 웹페이지 크롤링
구글시트 강좌2 CONCATENATE 함수로 원하는 웹페이지 주소 만들기
구글시트 강좌3 VLOOKUP 함수와 드롭다운으로 선택메뉴 만들기
구글시트 강좌4 엑셀보다 편하게 함수로 데이터 나누기 SPLIT과 JOIN
구글시트 강좌5 IMPORTXML로 하는 더 파워풀한 웹크롤링
구글시트 강좌6 엑셀에도 있었으면 좋겠다 UNIQUE와 TRANSPOSE 함수
구글시트 강좌7 GOOGLEFINANCE 함수로 주식, 환율, 비트코인 가격 불러오기

과거 강좌를 차례대로 진행하시면 좋습니다.

구글 시트란?

구글 시트는 구글에서 제공하는 스프레드시트 프로그램입니다. 마이크로 소프트 엑셀과 거의 동일하지만, 클라우드 상에 파일이 존재하여, 여러 기기에서 접근하거나, 여러 사람이 동시에 수정을 하는 등 엑셀보다 편리한 점이 많이 있습니다.

기존의 엑셀 함수는 대부분 사용이 가능하고, 구글 시트에서만 작동하는 유용한 함수들도 많이 있습니다. 구글 시트를 익혀서 여러분의 생산성을 올려보세요! :)
구글 드라이브 바로가기

오늘의 함수 COUNT와 OFFSET

오늘은 특정 함수에 집중하기 보다는 우리가 스프레드시트를 사용하는 어떤 상황에 대해서 생각을 해볼까 합니다.

엑셀을 자주 사용하는 사람이라면 원본 데이터를 가지고 그것을 가공하여 또 다른 정보를 만들어내는 일들을 자주 하게 됩니다. 그 원본 데이터는 여러 부서에서 제공한 것일 수도 있고, 시기별로 받은 데이터일 수도 있어서 데이터의 크기나 내용 등은 항상 조금씩 차이가 납니다.

이를테면 이렇게 간단하게 매일의 주문 수량을 기록한 데이터가 있다고 했을때 이 전체 수량을 구하기 위해서

=sum(B2:B16)
와 같은 함수를 사용하게 되면 향후 해당 데이터가 B2:B16의 범위를 넘어가게 되면 함수를 계속 수정해주어야 하는 번거로움이 있습니다.

특히 구글스프레드 시트의 경우는 구글폼을 이용해서 고객 의견이나 다양한 설문조사 활동 등을 해서 데이터를 쌓을 수 있고, 다양한 import 함수를 이용해 외부 데이터를 쉽게 가져올 수 있는데, 이런 데이터들은 시시각각으로 변할 수 있기 때문에 그런 부분을 함수에 반영 시켜놓는 것이 중요합니다.

그런 의미에서 COUNT 함수는 볼때마다 변하는 데이터의 개수를 지정하기 위해 사용하는 함수이고, OFFSET 함수는 데이터의 개수에 따라서 달라질 수 있는 범위를 설정하기 위해 사용하는 함수입니다.

함수사용법

오늘 나오는 함수는 엑셀에서도 모두 동일하게 사용 가능합니다.

COUNT 함수

COUNT는 범위 내의 데이터를 세어서 그 숫자를 반환해주는 함수인데, 어떤 데이터를 필요로 하느냐에 따라 쓸 수 있는 함수 몇가지가 있습니다.

• COUNT : 범위내에서 숫자의 개수를 반환
• COUNTA : 범위내에서 값의 개수를 반환 (숫자 + 텍스트 모두)
• COUNTIF: 범위에서 조건에 맞는 개수를 반환합니다.
• COUNTBLANK: 지정된 범위에서 빈 셀의 개수를 반환합니다.
• COUNTUNIQUE : 지정된 값과 범위 목록에서 고유 값의 개수 (구글 시트 전용)

여기에서 제가 개인적으로 가장 빈번하게 쓰는 것은 값의 개수를 구하는 COUNTA 입니다. 숫자와 텍스트 구분 없이 데이터의 개수를 찾을 수 있으니까요. :)

그리고 여기에서는 함수에서 지정한 범위자체도 C1:C16과 같은 식으로 특정하였지만, 해당 칼럼의 데이터가 유동적인 경우라면 범위를 C:C 로 지정한다면 범위가 변동되는 것에 대해서는 신경을 안써도 됩니다. C1부터 아니라 중간부터 시작하는 경우에는 C2:C 와 같은 식으로도 사용이 가능합니다. (구글 시트에서만 가능. 엑셀에서는 C2:C1000 과 같은 식으로 큰 값을 넣으면 됨)

다만 그렇게 지정하는 경우에는 데이터가 없는 범위까지도 전체가 지정되므로 COUNTBLANK 같은 함수는 훨씬 더 큰 값이 나오게 됩니다. 이런 부분 주의!

OFFSET 함수

OFFSET(셀 참조, 오프셋 행, 오프셋 열, [높이], [너비])

• 셀_참조 - 오프셋 행과 열을 세는 시작점입니다.
• 오프셋_행 - 오프셋되는 행의 개수입니다.
• 오프셋_행은 정수여야 하며, 음수일 수 있습니다. 소수 값을 입력하면 소수점 이하는 생략됩니다.
• 오프셋_열 - 오프셋되는 열의 개수입니다. (정수. 음수도 가능)
  • 높이 - [ 선택사항 ] - 반환될 범위의 높이로 오프셋 대상에서 시작합니다.
  • 너비 - [ 선택사항 ] - 반환될 범위의 너비로 오프셋 대상에서 시작합니다.

오프셋 함수는 참조된 셀로부터 위아래로 몇칸, 좌우로 몇칸을 오프셋시킨 뒤, 해당 셀을 중심으로 범위를 지정할 수 있는 함수입니다.

위 예제에서 (C2:C16) 이라는 정해지 범위를 쓰기보다는 OFFSET 함수를 이용해서 범위를 만들어볼 수 있습니다.

=offset(C2,0,0,counta(C2:C),1)

C2 셀을 중심으로 오프셋 되는 행이 0이니, 행 방향으로 움직이지 않고, 오프셋되는 열 또한 0이라서 열 방향으로도 움직이지 않습니다. 계속 C2를 참조하면 됩니다.

[높이]에 해당하는 값에서 COUNTA 함수를 사용합니다. 데이터가 몇개나 있는지를 세어서 범위를 만드는 것이기 때문에, 데이터의 개수가 변할때도 적절하게 범위를 지정할 수 있게 됩니다. 마지막 [너비] 부분은 원하는 범위로 설정해주면 끝!

이제 데이터에 따라서 범위를 계속 바뀌는 함수가 되었기 때문에 몇개의 데이터를 넣더라도 추가적인 수정없이 사용할 수 있습니다.

물론 SUM같은 함수야 범위를 C:C를 이용해 전체 칼럼으로 지정해서 사용하면 가장 간단하겠지만, 범위를 지정해야하는 다른 함수를 사용하실때는 더 유용하게 사용하실 수 있을거에요. :)

숙제

SUMPRODUCT 함수는 많이들 아시죠? 2개의 범위(행렬)을 행렬 곱셈해서 계산해주는 함수인데요. 위와 같이 판매가격과 판매수량을 가지고 총 판매금액을 계산할때 일일히 곱하고 더해서 계산하는게 아니라, 간단하게 활용할 수 있는 함수입니다.

A2:A5, B2:B5처럼 정해져 있는 범위가 아닌, 데이터의 길이에 따라 달라질 수 있는 동적 범위로 바꿔보세요. ^_^