파워쿼리 그룹화, SUMIF 없이 담당자별 매출 합계 구하기

매출 자료를 정리하다 보면 담당자별 합계가 필요할 때가 있습니다.

보통 SUMIF나 SUMIFS 함수를 사용합니다.

sumif 나 sumifs도 시트나 파일이 너무 늘어나면 다루기 어렵고

유지보수가 어려울 수 있어요

하지만 매달 새로운 행이 추가되면 수식 범위를 확인해야 합니다. 담당자나 조건이 많아질수록 수식 관리도 복잡해집니다.

이럴 때 파워쿼리 그룹화를 사용하면 담당자별 매출을 간단히 집계할 수 있습니다.

예제 자료는 날짜, 매출, 담당자로 구성되어 있습니다.

표로 변환하기

먼저 원본 자료 안에 있는 셀을 하나 선택합니다.

상단 메뉴에서 삽입 → 표를 누릅니다. 단축키 Ctrl+T를 사용해도 됩니다.

표 만들기 창이 나오면 데이터 범위를 확인합니다. 첫 번째 행에 날짜, 매출, 담당자가 있으므로 ‘머리글 포함’도 선택합니다.

확인을 누르면 일반 범위가 엑셀 표로 바뀝니다.

테이블/범위에서 불러오기

표 안의 셀을 선택한 상태에서 데이터 탭으로 이동합니다.

데이터 → 테이블/범위에서를 누르면 Power Query 편집기가 열립니다.

편집기에는 날짜, 매출, 담당자 열이 그대로 표시됩니다.

여기에서 날짜는 날짜 형식, 매출은 숫자 형식인지 확인합니다. 자료 형식이 맞아야 합계도 정상적으로 계산됩니다.

담당자를 기준으로 그룹화하기

Power Query 편집기의 홈 탭에서 그룹화를 선택합니다.

그룹화 창이 열리면 다음과 같이 설정합니다.

확인을 누르면 같은 담당자의 행이 하나로 묶입니다.

A 담당자의 매출 세 건은 하나의 합계로 계산됩니다. B 담당자의 매출도 같은 방식으로 합산됩니다.

예제 결과는 다음과 같습니다.

SUMIF 수식을 작성하지 않아도 담당자별 매출 합계가 완성됩니다.

엑셀로 결과 불러오기

출처 입력

집계 결과를 확인했다면 홈 → 닫기 및 다음으로 로드를 선택합니다.

데이터를 표시할 위치를 지정한 뒤 확인을 누르면 결과가 엑셀 시트에 표로 들어옵니다.

원본에 새로운 매출 자료가 추가되면 표 범위도 함께 늘어납니다. 이후 결과 표에서 우클릭 → 새로 고침을 누르면 담당자별 합계가 다시 계산됩니다.

쿼리 다시 편집하기

담당자 대신 다른 기준으로 집계하고 싶을 수도 있습니다.

엑셀 오른쪽의 쿼리 및 연결 창에서 해당 쿼리를 찾습니다. 쿼리 이름을 우클릭한 뒤 편집을 선택하면 Power Query 편집기가 다시 열립니다.

오른쪽의 적용된 단계에서 그룹화 단계를 수정할 수 있습니다.

파워쿼리 그룹화는 합계만 구하는 기능이 아닙니다. 담당자별 주문 건수, 부서별 평균 매출, 상품별 최대 판매량도 계산할 수 있습니다.

매달 같은 형식의 자료를 반복해서 집계한다면 수식을 계속 복사할 필요가 없습니다. 처음 한 번 기준을 설정하고 이후에는 원본 추가와 새로 고침만 하면 됩니다.

이상으로 뉴스레터 마칩니다

차주에도 파워쿼리 관련 실무 업로드 드리겠습니다

매주 목,일 업데이트 됩니다