구글 스프레드시트 중복 값 합치기 (QUERY 함수)

구글 스프레드시트에서의 중복 값

구글 스프레드시트에서 관련있거나 같은 데이터들이 여기저기 흩어져 있거나 분리되어 있다면 데이터를 읽고 이해하기 상당히 불편합니다. 데이터의 양이 적다면 수작업으로 정리해 버릴 수 있지만, 양이 많을 땐? 아~ 귀찮습니다. 이제부터는 수작업 대신에 QUERY 함수를 사용해 보세요.

본문에서 사용하는 쿼리(Query)문은 그냥 짧은 영어 문장이라는 생각으로 접근하고, 익숙해지면 다른 함수보다 더 자유롭게 데이터를 조작하면서 분석할 수 있습니다. 넓은 의미에서 요즘 많이들 배우는 데이터 사이언스와 마찬가지입니다.

group by, sum으로 중복 값 합치기

※ D1 셀에 QUERY 함수를 사용한 수식을 입력한다.

QUERY 함수 수식

=QUERY(A2:B5, "select A, sum(B) group by A")

설명

수식에서 함수 사용과 데이터 범위 지정에 관한 내용은 다른 함수와 별다른 차이가 없기에 생략하겠습니다.

가장 중심이 되는 부분인 쿼리문 select A, sum(B) group by A을 해석하면, 'A열 값을 기준으로 중복되는 행의 B열 값을 모두 더한다' 입니다.

중복 값 합치고, 정렬하기

※ D1 셀에 QUERY 함수를 사용한 수식을 입력한다.

QUERY 함수 수식

=QUERY(A2:B5, "select A, sum(B) group by A order by sum(B) desc")

설명

쿼리문 select A, sum(B) group by A order by sum(B) desc을 해석하면, 'A열 값을 기준으로 중복되는 행의 B열 값을 모두 더하고, 더한 값을 기준으로 내림차순으로 정렬한다' 입니다.

내림차순으로 정렬할 때는 예문과 같이 desc(descending, 내림차순)을 입력하지만, 오름차순으로 정렬할 때는 asc(ascending, 오름차순)를 입력합니다.

중복 값 합치고, 개수 세기

※ D1 셀에 QUERY 함수를 사용한 수식을 입력한다.

QUERY 함수 수식

=QUERY(A2:B5, "select A, sum(B), count(B) group by A")

설명

쿼리문 select A, sum(B), count(B) group by A을 해석하면, 'A열 값을 기준으로 중복되는 행의 B열 값을 모두 더하고, 중복되는 행의 B열 값의 개수를 센다' 입니다.

pivot, sum으로 중복 값 합치기

※ D1 셀에 QUERY 함수를 사용한 수식을 입력한다.

QUERY 함수 수식

=QUERY(A2:B5, "select sum(B) pivot A")

설명

쿼리문 select sum(B) pivot A을 해석하면, 'A열 값을 기준으로 중복되는 행의 B열 값을 모두 더한다' 입니다.

group by 구문과 비슷하지만 pivot(피벗) 구문은 집계 결과를 출력하는 형식이 다르군요. 비교해 보면 차이점을 알 수 있어요. pivot 구문은 저에게 낯선데 처음에는 그냥 암기해서 사용하다 보면 이해도와 응용력이 높아질 거라 믿습니다.

마무리

조금 생소하지만 기본 쿼리문을 영어 문장처럼 암기해서 사용하고 응용하고 조합하다 보면 비전공자도 자신의 업무와 관련된 데이터에서 가치있는 데이터만 추출하고 분석하고 통찰력을 얻고 새로운 아이디어를 생각해내고 업무 효율을 높이는 시민 데이터 사이언티스트가 될 수 있습니다.