구글 스프레드시트의 강력한 기능 QUERY

구글 스프레드시트의 QUERY 기능을 사용해보셨나요?

QUERY를 사용하면 데이터의 관리와 분석이 훨씬 쉬워집니다.

QUERY는 열단위로 정렬, 합계, 필터링등을 진행할 수 있게 해주는 함수라고 생각하면 쉽습니다.

피벗테이블과 유사하지만, 여러 시트의 데이터를 활용해야하는 경우에 유용합니다.

예를 들어, 입출고대장을 만들고 재고를 피벗테이블로 확인하려면 일반적으로 하나의 시트에 입고와 출고를 동시에 기록하게 됩니다. 따로 기록하면, 피벗테이블에서 데이터를 선택하고 분석하기 위해 별도의 작업이 필요한데 관리 측면에서 불편함이 있습니다.

QUERY를 이용하면 다양한 데이터를 여러 시트에 입력해도 한번에 관리할 수 있다는 장점이 있습니다.

이제, 예를 살펴보시겠습니다.

다음과 같이 매입 시트와 매출 시트를 나누어서 관리하는 편이 직원 입장에서는 훨씬 편리합니다. 시중에 나와있는 물류관리시스템들도 매입과 매출을 별도로 관리하고 있으니 익숙한 시스템입니다.

피벗테이블을 사용하면 각각의 수량의 합계는 금방 구할 수 있습니다. 하지만 매입 – 매출 수량 = 재고 수량을 파악하려면 이제부터 시간이 좀 들겠지요? 피벗테이블은 범위를 1개만 선택할 수 있기 때문에 한 시트에 매입과 매출을 동시에 기록하지 않으면, 재고 수량을 파악하기가 힘이 듭니다. 예전에 저도 매입은 양수(+)로 매출은 음수(-)로 기록해서 한번에 피벗테이블로 해결하기도 했습니다만, QUERY를 사용하면 관리가 편해집니다.

이제 QUERY를 사용해보겠습니다. (쿼리에 대한 구글 링크는 여기)

쿼리 함수는 3가지 요소로 구성됩니다.

1	=QUERY(데이터범위,쿼리문,헤더행 갯수)

데이터 범위는 피벗테이블 범위처럼 생각하시면 됩니다. 범위에서 유의할 점은 쿼리문에서는 열 (column) 단위로 가공을 하기 때문에, 데이터의 라벨이 열 단위로 들어가 있어야 합니다. 데이터는 행 단위로 하나씩 추가되는 방식입니다.

이제 쿼리문을 쓰기 전에 열과 라벨을 기억해둡니다. 목표는 일자별로 품명의 재고를 확인하는 것이니, 해당 열만 기억하겠습니다.

B = 일자
F = 품명
I = 수량

이제 이렇게 써보겠습니다.

‘매입기록’!A:I 범위에서 B, F, I 열 전체를 선택하라는 쿼리입니다. 범위 뒤에 쉼표(,)를 넣고, 쿼리문을 큰 따옴표(” “)로 묶어주어야 합니다.

1	=QUERY('매입기록'!A:I,"SELECT B,F,I")

이제 다음과 같은 결과가 나오게 됩니다.

이제 동일한 방법으로 매출수량도 불러옵니다. 동작이 잘 되는지 오른쪽에 넣어서 확인해보면 좋습니다.

1	=QUERY('매출기록'!A:I,"SELECT B,F,I")

이제 두 쿼리를 합쳐서 표현하면, 해당 데이터로 피벗테이블을 만들어서 분석할 수 있게 됩니다.

다음과 같이 중괄호 {} 와 세미콜론 (;) 으로 두 쿼리를 이어서 표시할 수 있습니다.

1	={QUERY('매입기록'!A:I,"SELECT B,F,I");QUERY('매출기록'!A:I,"SELECT B,F,I")}

결과는 먼저 쓴 매입기록 쿼리를 불러오고, 그 아래에 매출기록 쿼리를 불러오게 됩니다.

데이터는 예쁘게 합쳐졌지만, 피벗테이블로 만들면 수량이 모두 양수이기 때문에 재고는 늘어나기만 합니다.

이 문제를 해결하려면 매출기록에서 불러오는 출고 수량을 마이너스 (-) 로 처리합니다. 매출 기록 부분 쿼리에서 I 를 I*-1로 바꾸는 것으로 가능합니다.

1	={QUERY('매입기록'!A:I,"SELECT B,F,I");QUERY('매출기록'!A:I,"SELECT B,F,I*-1")}

이제 매입기록 & 매출기록 쿼리 결과인 Q3:S로 피벗테이블을 만듭니다.

20-01-04 의 수량 합계는 13인데, 매입 +18, 매출 -5 가 더해진 결과입니다. 하단의 총계를 보면 현 시점에서의 재고를 확인할 수 있습니다. (더미 데이터로 20년말까지 들어 있어서 나오는 결과입니다. 시점 재고를 보고 싶다면, 피벗테이블 필터를 적용하면 됩니다.)