매년 친구들과 가는 여행에서 총무를 수행하며, 일일히 비용을 입력하고 계산하기 귀찮아서 구글 스프레드시트에서 제공하는 함수를 사용하여 최대한 자동화를 구현해보았다.
그 중 알아두면 파워풀하게 스프레드시트를 이용할 수 있는 몇가지 함수 사용법을 기록해둔다.
IF
인당 십만원 씩 예산을 잡았는데, 여행 갈 인원 수가 아직 결정되지 않았다. 그래서 한명씩 늘어날수록 총 예산이 증액되는데, 이 때마다 사람 이름 기입하고 총액 또 계산하기 싫어서 이름이 추가되면, 자동으로 총 인원 수와 총 예산 액을 계산하도록 IF 함수를 사용해보았다.
1 | IF("조건절 대상","true일 경우","false일 경우") |
A row의 값으로 빈 값("")이 들어오면, 0, 아니면 숫자 1이 카운팅되서 합산하도록 했다.
마찬가지로 B row는 A row가 빈 값이 아니면 100,000원씩 입력되도록 했다. B6는 =SUM(B2:B5) 로 자동으로 값이 합산되도록 했다.
이로써 이름이 한명씩 추가될 때마다 총원과 총 예산이 자동으로 계산되도록 하였다.
Reference : Google Docs - IF
IMPORTRANGE
스프레드시트는 시트를 여러개 만들수 있는데, 이 때 다른 시트에 있는 값을 가져올 수 있는 함수를 제공한다.
우리는 숙박비 선정을 위해 location 이라는 시트를 따로 만들었는데 여기에 숙박비를 입력한다. 그리고 이 시트에서 우리가 원하는 조건에 따라 셀의 색상을 다르게 표현하여 최종적으로 숙박지를 선정한다. 그럼 모든 비용을 계산하는 finance 시트에서 location 시트에 기입된 숙박비를 가져와야 한다.
1 | IMPORTRANGE("시트URL/edit","시트이름!셀") |
여기서 시트 URL은 아래의 이미지에서 spreadsheets/d와 edit 사이의 URL을 의미한다.
그래서 쿼리를 완성하면 아래와 같다.
1 | =IMPORTRANGE("1YK8n3MyWHjQ0qM5yVudT6gfncgMVq33I/edit","22'location#F2") |
잠깐 로딩이 되면서 셀에 값이 채워진다.
Reference : Google Docs - IMPORTRANGE
IMPORTXML
차를 운행해주는 친구들 덕분에 여행을 편하게 다녀오는데, 차를 빌려주는것은 물론 운전하는 친구들에게 모든 부담을 주고싶지 않아 숙박지의 거리와 각 차량의 공인 연비 그리고 휘발유 연비를 계산하여 차비를 운전해준 친구들의 예산에서 빼주고 있다.
그런데 휘발유 가격이 매일 조금씩 바뀌다보니 이걸 매번 입력하기 귀찮아서 이번엔 외부 URL에서 값을 가져와보았다.
1 | IMPORTXML("외부 URL","XPath") |
W3C - XML Path Language (XPath) 3.1
네이버 증권에서 휘발유 가격을 보여주는 페이지에 접속해서 개발자 도구를 통해 XPath 주소값을 가져왔다. 완성한 쿼리는 아래와 같다.
1 | =IMPORTXML("https://finance.naver.com/marketindex/?tabSel=gold#tab_section","//*[@id='content']/div[3]/table/tbody/tr[1]/td[3]") |
그럼 외부 URL에서 DOM 트리를 통해서 값을 가져오게된다.
Reference : Google Docs - IMPORTXML