(Google) 개발자를 위한 구글 스프레드 시트 팁

최근에 구글 스프레드 시트를 활용하면서 배운 팁들을 정리해보고자 합니다. 

 

이 글에서 사용한 샘플 데이터 시트는 다음과 같습니다. 

서울-카페 Sheet

제주도-카페 Sheet

상품공급가 Sheet

 

1. 여러개의 시트를 하나로 합치기

"제주도-카페" 시트와 "서울-카페" 시트를 합친 결과

={'제주도-카페'!A2:C2; '서울-카페'!A2:C2}

합칠 시트와 범위를 {} 괄호 안에 입력합니다.

수직으로 합치고 싶은 경우에는 Sheet를 세미콜론(;)으로 구분하고, 수평으로 합치고 싶은 경우에는 콤마(,)으로 구분합니다.

 

2. 시트 데이터에 쿼리 적용하기

카페 총합 Sheet에서 디저트 판매 정보만 가져오기

=QUERY('카페 총합'!A2:D, "select A,B,C,D where A is not null AND B = '디저트'")

스프레드 시트에 쿼리를 적용하여 데이터를 가져올 수 있습니다. 

 

3. 여러개의 시트에서 필요한 데이터만 가져와서, 하나의 시트에 합치기

음료 판매 데이터만 가져오기

={FILTER('제주도-카페'!A2:E, '제주도-카페'!B2:B="음료");FILTER('서울-카페'!A2:E, '서울-카페'!B2:B="음료")}

이 경우, FILTER 함수를 사용하면 됩니다.

 

4. 하나의 수식을 여러개의 행 / 범위에 적용하기

카페 총합 Sheet

ARRAYFORMULA 함수를 사용하면 됩니다. 특정 컬럼에 값이 있는 경우에만, 수식을 적용하고 싶은 경우에는 IF 함수를 추가하면 됩니다.

=ARRAYFORMULA(IF(C2:C<>"", D2:D*E2:E, ""))

 

5. 특정 값이 일치하는 행을 수평으로 합치기

상품명으로 공급가 데이터를 찾아서, 조인한 결과

이 경우, ARRAYFORMULA 함수와 VLOOKUP 함수를 사용합니다.

=ARRAYFORMULA(IF(C2:C<>"",vlookup(C2:C,'상품공급가'!B2:C,2,False),""))

 

6. 데이터 분석 그래프 추천받기

하단의 탐색 버튼을 누르면, 구글이 시각화에 도움을 주는 그래프를 추천해줍니다.

 

7. 기타

1. 구글 스프레드 시트에서 빅쿼리를 연동해서 사용할 수 있습니다. 만약 빅쿼리를 사용하고 있다면, 이 기능이 유용할 것 같네요. 

2. 테이블을 합치는 함수가 어렵다면, 구글 스프레드 시트의 부가기능 프로그램인 merge-values를 사용해도 좋을 것 같아요.

 

 

참고 자료

https://infoinspired.com/google-docs/spreadsheet/consolidate-data-from-multiple-sheets-using-formula-in-google-sheets/

Consolidate Data from Multiple Sheets Using Formula in Google Sheets

http://mindthelog.com/2016/12/google-sheets-analytics-database/

불러오는 중입니다...

https://www.ablebits.com/office-addins-blog/2017/07/05/vlookup-google-sheets-example/

불러오는 중입니다...