엑셀 파워쿼리로 월별 데이터에서 연도별 합계 쉽게 구하는 법

가상의 주식인 B 종목의 최근 10년간 배당금 지급 내역 테이블이 있습니다. 테이블 형태로 되어 있어 분배금이 매년 늘어나고 있는지 한눈에 파악하기 어려우니 연도별 배당금 합계를 구한 후 연도별 배당금 지급 내역 막대 그래프를 만들어 보겠습니다.

 

배당금 지급 내역 로우 데이터 입니다. 우선 표로 서식을 변경해 주겠습니다. Ctrl + Shift를 누른 상태에서 방향키 →, ↓를 누르면 활성화된 셀을 한 번에 선택할 수 있습니다.

 

 

이렇게 표로 만들 셀을 선택한 상태에서 Ctrl + T 단축키를 눌러 표 만들기 기능을 활성화합니다. 지급일과 배당일이라는 머리글이 있으므로 머리글 포함을 체크하고 확인을 클릭합니다.

 

표의 아무 영역이 지정되어 있는 상태로 데이터 항목에서 테이블/범위에서 데이터 가져오기 아이콘을 클릭합니다. 클릭하면 파워쿼리 창이 자동으로 실행됩니다.

 

 

우리가 구하고 싶은 값은 연도별 배당금 합계이니, 우선 연도별로 데이터를 취합할 수 있게 지급일 데이터를 분할하도록 하겠습니다. 표에서 지급일을 클릭해 열 전체를 선택한 후에 홈 메뉴의 열 분할 항목에서 구분 기호 기준을 선택합니다.

 

 

구분 기호 선택 또는 입력 드롭다운을 --사용자 지정--으로 선택하고 아래 칸에 하이픈(-)을 입력하고 확인 버튼을 누르면 연, 월, 일별로 열이 새로 생성됩니다.

 

 

배당금 열을 보니 서식이 텍스트로 인식되어 있습니다. 텍스트로 인식되어 있을 경우 합계를 구할 수 없으므로, 통화 서식으로 변경해 주겠습니다. 우선 홈 메뉴의 값 바꾸기 아이콘을 클릭하고 찾을 값에 원을 입력하고 바꿀 항목을 공란으로 둔 상태로 확인 버튼을 누릅니다.

 

배당금 열 옆의 아이콘을 클릭해서 텍스트로 지정되어 있는 서식을 통화로 변경해 줍니다.

 

 

이제 마지막으로 연도별 분배금 합계를 구해보도록 하겠습니다. 홈 메뉴에서 그룹화 아이콘을 클릭합니다.

연, 월, 일로 분리된 열에서 연도가 표기된 열의 이름이 지급일.1로 설정되어 있으므로 첫 번째 드롭다운을 지급일.1로 선택합니다.

우리의 목표는 연도별 분배금 합계를 구하는 것이므로, 새 열 이름에 연도별 분배금 합계라고 기입하고, 연산은 합계를 선택, 열은 배당금을 선택합니다.

 

 

연도별 분배금 합계가 정리된 표가 출력되었습니다. 이제 해당 표를 엑셀로 출력해 보겠습니다. 홈 메뉴에서 닫기 및 로드 아이콘을 클릭하고 닫기 및 다음으로 로드 항목을 클릭합니다. 

 

 

저는 표 형태로 새 워크시트에 데이터를 불러오겠습니다. 데이터 가져오기 창에서 표와 새 워크시트를 선택한 후 확인 버튼을 클릭합니다.

 

 

새 시트에 연도별 분배금 지급 내역이 깔끔하게 표 형태로 출력되었습니다. 삽입 메뉴에서 추천 차트 오른쪽의 막대 차트 아이콘을 클릭하고, 2차원 세로 막대형 첫 번째 아이콘을 선택하면 연도별 분배금 지급 내역을 한눈에 비교할 수 있는 막대그래프가 생성됩니다.

 

 

연도별 분배금 지급 내역을 한눈에 비교할 수 있는 막대 그래프가 완성되었습니다. 그래프로 만들어서 보니 2019년에 가장 많은 분배금을 지급했으며, 배당금이 매년 지속적으로 늘어나지 않고 들쭉날쭉하여 안정적인 배당주로 보기 어렵다는 점을 쉽게 파악할 수 있습니다.

 

파워쿼리 기능을 활용해 월별 데이터의 표에서 연도별 합계를 쉽게 구하는 방법을 알아보았습니다.