구글 스프레드 시트 – 흩어진 자료 모으기, 시트 Data 합치기, query, filter
구글 스프레드 시트는 엑셀같지는 않지만, 온라인 솔루션의 장점을 살려서 협업등에 활용될 수 있어서 유용한 점이 많아서 계속 공부하며 사용하고 있습니다. 특별히 가게부를 만들고, 매매거래 내역등을 만들면서 궁금한 것 찾아보고, 스크립트를 짜보고 그렇게 연습하고 있습니다. 이번에 할 것은 아래와 같습니다. 그게 2가지 영역입니다. 첫번째가 되면 두번째는 응용을 하면 더 다양한 솔루션을 만들수 있고, 업무에 적용할 수 있을 거라봅니다.
- 각 시트에 Data를 어떻게 합쳐서 불러올까?
- 다른 파일속에 있는 시트의 자료는 어떻게 불러올 수 있을까?
배열을 이용해서 합치는 방법과 Query를 이용해서 합치는 방법이 있습니다.
배열을 이용해서 합치는 방법
수직으로 합치고 싶은 경우에는 Sheet를 세미콜론(;)으로 구분하고, 수평으로 합치고 싶은 경우에는 콤마(,)으로 구분합니다.
={‘북미지점’!A2:F7;‘남미지점’!A2:F}
합칠때, 앞의 일련번호가 그대로 따라오네요. ^^;; 그리고 시트지의 행의 길이를 정해주지 않으면 수직으로 data를 합칠때 빈 행들이 삽입이 됩니다. -_-;;
다이나믹하게 자료가 계속 늘어날때 합치기 위해서는 필터기능을 이용해서 합치면 됩니다. 이렇게 하면 위에처럼 행의 범위를 fix하지 않기때문에 두번째 Data가 첫번째 Data 다음에 잘 붙습니다.
={filter(‘북미지점’!A2:F,‘북미지점’!B2:B<>“”);filter(‘남미지점’!A2:F,‘남미지점’!B2:B<>“”)}
혹시나 해서 이렇게 부르면 필터없이 될까 해보지만, 두번째 Data가 나타나지 않습니다.
={‘북미지점’!A2:F;‘남미지점’!A2:F}
Query를 이용해서 합치는 방법
query를 이용해서 각 시트의 자료를 합치면 다양한 조건으로 부를 수 있습니다. 필터기능으로도 가능합니다만, 저는 query가 좋더라구요. 다만, Where 절에서 특이한 점이 있습니다. 공부하기 위해서 다른 블러그들을 보면 Where A is not null 하면 검색 에러가 납니다. A를 알 수 없나봐요. 그래서 미국 사이트들을 보니깐 Col1 이런식으로 칼럼명을 넣더라구요., 그러면 됩니다.
=query({‘북미지점’!A2:F;‘남미지점’!A2:F},” where Col1 is not null “)
Query에 대한 Where, Group, Limit은 더 다양한 검색을 해보시면 찾을 수 있을 거예요. 아래는 구글 문서입니다.
https://support.google.com/docs/answer/3093343?hl=
다른 구글 스프레드 시트 파일을 불러서 넣는 방법
명령어: Importrange
위 자료에서 본점 시트에서 북미지점과 남미지점 시트의 자료를 불러왔는데요. 북미지점파일이 따로 존재하고, 남미지점 파일이 따로 존재해서 해당 메니저들이 자료를 입력한 것을 본점에서 자료를 합칠때 좋은 방법이겠습니다.
다른 파일을 엑세스 하는 것이때문에 권한 부분이 이슈가 됩니다. 스프레드 시트 파일마다 고유 URL이 있습니다. 그대로 복사해서 넣어도 되고, URL중간에 있는 고유주소를 넣어도 되는 것같습니다. 예를 들어 북미지점의 Full Url은 아래와 같습니다. 보안상 중간에 …. 처리를 했습니다만, 고유 주소가 아래 경로의 위치입니다. 저는 그냥 Full Url을 넣습니다.
https://docs.google.com/spreadsheets/d/13dLOFkQBHX……..iuQE7eQMgix7iF0w/edit#gid=0
중간에 파일이름이나, 폴더위치가 바뀌어도 공유하는데는 아무런 문제가 없습니다.
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/13dLOFkQBHX……..iuQE7eQMgix7iF0w/edit#gid=0″,“‘북미지점’!A1:F”)
위 Gif 캡춰한 이미지를 보시면 설명보다 잘 아실거예요. 위와 같이 명령어(함수)를 넣으면 엑세스에 대한 권한 문제를 물어봅니다. 저는 그냥 클릭해서 바로 넘어갔습니다. 이유는 제 문서에 대한 권한을 제가 가지고 있기때문입니다. 하지만 다른 사람의 accout의 문서라면 바로 access클릭한다고 안되겠죠? 해당 accout 주인이 허락을 해줘야 가능합니다.
하지만 위에서 보시면 것처럼 권한을 받았는데 안되면 마우스로 가만히 올려보면 Error에 대한 설명이 나옵니다. B2에 자료가 있어서 덥어쓰기가 안된답니다. 눈에는 없는데.. B2에 빈칸이 입력되어 있었나봅니다. 삭제하니깐 호출됩니다. 응용을 하면 각 지점을 이렇게 시트지마다 불러놓고, 본점에서 다시 Query난 Filter등을 이용해서 원하는 자료를 얻으시면 효과적인 업무가 될 듯합니다.
구글 스프레드 시트 다른 팁들
구글 스프래드시트 스크립크를 이용해서 다른 시트의 첫열에 영역 복사하기
