SUMIF함수 이용하기(기업의 5개년 배당지급내역을 필터링하자) - 투자자 엑셀 활용능력 4편

2022.12.24 - 한국의 배당챔피언 - 배당컷 없이 배당을 꾸준하게 지급해 온 기업들

한국의 배당챔피언 - 배당컷 없이 배당을 꾸준하게 지급해온 기업들

지난 포스팅에서 5년간 배당컷없이 꾸준한 배당을 지급해 온 코스피와 코스닥시장의 기업들을 리스트업 하여 엑셀로 공유를 해 드렸습니다.

공유를 해드리면서 "투자자엑셀활용능력" 시리즈로 어떤 과정을 거쳐서 리스트업이 되는지 설명해 드리겠다고 예고를 하였죠! 쇠뿔도 단김에 뺀다고 바로 시작해 보도록 할까 합니다!

시작하기에 앞서 seibro 홈페이지에서 기업들의 배당내역 데이터를 연단 위로 조회하여 내려받아야 하는데요~!

 

 

Step 1. 연도별 배당금 지급내역 다운로드(SEIBRO 이용)

 

2022.12.19 - 기업의 배당금 지급일 조회하는 방법(SEIBRO 사용법)

기업의 배당금 지급일 조회하는 방법(SEIBRO 사용법)

seibro를 활용하여 기업의 배당내역데이터를 내려받는 건 지난 12월 19일의 포스팅을 참고해 주세요!

 

seibro를 활용하여 2017년부터 2021년까지의 배당내역을 내려받아 주시고 하나의 파일에 연도별 배당데이터를 시트 화해서 보관하시는 게 작업이 편하니.. 그 작업을 먼저 해보겠습니다.

 

 

 

Step 2. 엑셀시트 이동기능으로 하나의 파일로 모아주기

저는 2017년 엑셀파일에 모든 데이터를 옮겨볼까 하는데요. 컨트롤 C, 컨트롤 V보다 좀 더 편한 방법으로 해볼게요.

먼저 2018년 엑셀파일을 열어주셔서 아래의 시트에 마우스 오른쪽클릭 - 이동/복사를 눌러주세요

 

이동/복사를 눌러주시면 팝업이 하나 뜨는데요.

대상 통합 문서 드롭박스를 클릭해 주시면 현재 열려있는 엑셀파일의 목록을 확인이 가능합니다.

2017년 파일을 클릭하고 확인을 눌러주세요.

 

결괏값으로 2017년의 파일에 앞서 열었던 2018년의 시트가 그대로 옮겨진 것을 확인이 가능합니다.

2019년부터 2021년 파일도 똑같이 반복을 해주시면 되겠습니다.

 

 

 

 

Step 3. 데이터 가공하기(필요한 자료만 남겨보자)

그다음은 데이터 가공을 시작해 볼 건데요.

내려받은 데이터 파일에서 필요한 부분만 남겨놓는 작업을 할 거예요.

노란색으로 음영을 씌워놓은 종목코드, 종목명, 시장구분, 주당배당금 4가지만 남기고 다른 데이터들은 삭제를 진행하겠습니다.

 

 

 

데이터 1차 가공을 끝낸 후 2차 가공은 코스닥시장과 코스피시장에 해당되는 기업만 남기는 작업을 진행할 건데요.

첫 번째 행에 필터기능을 적용하겠습니다(블록을 씌운 후 컨트롤+쉬프트+L 누르면 단축키로 적용이 됩니다)

필터기능을 적용한 후 시장구분의 드롭박스를 눌러주신 후 기타 비상장, 코넥스시장, 프리보드시장만 체크를 하신 후 확인을 눌러주세요.

결괏값으로 유가증권시장(코스피), 코스닥시장에 해당되는 기업들을 제외한 나머지 기업들이 남게 되는데요..

전부 삭제를 해주신 후 시장구분 필터기능을 다시 클릭하시고 시장구분에서 필터 해제를 클릭하시면 코스피와 코스닥시장의 데이터만 남게 됩니다.

이걸 2017년부터 2021년의 시트에 모두 똑같이 적용해 주시면 되겠습니다.

이 작업을 다 끝내시면 겨우 밑작업이 끝난 겁니다.

 

네....... 이제 겨우 밑작업이 끝난 겁니다.

자 이제 본게임으로 기업별로 연도별 얼마의 배당금을 지급했는가를 계산을 해야 하는데요!

기업 개수가 적고 간단하면 일일이 눈으로 체크하면서 계산기를 두드리는 게 빠를 건데요..

파일을 쳐다보시면 알겠지만 기업의 개수가 어마어마해서.. 계산기 두드리는 막일작업으로는 끝이 안 날 겁니다!

이럴 때 유용하게 쓸 수 있는 엑셀의 함수가 있는데요.. 바로 SUMIF함수입니다.

 

Step 4. SUMIF함수를 이용하여 기업별 배당금 계산

Step 4-1. SUMIF함수가 뭐 하는 함수지?

SUMIF함수는 설정한 조건에 해당하는 범위의 값만 더하여 값을 추출하는 함수입니다.

SUMIF는 꼭 투자뿐 아니라 가계부를 엑셀로 정리할 때 식비, 교육비, 공과비 등 분류별로 지출이 얼마나 됐는지 합산을 해보고 싶을 때도 유용하게 쓸 수 있는 함수가 되겠습니다!

 

Step 4-2. SUMIF함수의 구조?

SUMIF는 총 3가지의 인수를 입력하게 구성되어 있습니다.

 

Range : 조건과 맞는지 검사할 셀의 범위

Criteria : 찾을 조건

Sum_Range : 덧셈을 실행할 셀의 범위

 

Step 4-3. SUMIF함수 응용!

설명이 길어졌습니다.

백문이 불여일견 백번 설명하는 것보다 한번 보는 게 좋을 것 같아요.

최종수식입니다.

 

SUMIF('2021년'!$A:$A, '연간 배당금 지급내역'! A2, '2021년'!$D:$D) 

 

시트를 넘나들다 보니 좀 복잡해졌네요.

하나씩 천천히 분석해 볼까요?

 

Step 4-3-1. SUMIF함수의 RANGE 인수

첫 번째 RANGE 인수를 채워 넣어줄 순서인데요.

RANGE인수는 조건과 맞는지 검사할 셀을 입력을 해줘야 하는 인수입니다.

그래서 2021년 시트를 클릭하여 2021년 시트로 넘어가 보겠습니다.

 

2021년 시트로 넘어오니 함수식에 '2021년'! 이렇게 입력이 되네요

'시트이름'! 함수를 입력하면서 다른 시트의 데이터를 참조하게 될 때 많이 보게 될 구조니 눈에 익혀두시면 되겠습니다.

 

 

A열을 전체선택을 하고 F4키를 눌러 절댓값으로 범위를 고정시켜 주시면 RANGE 인수는 완료입니다.

 

 

Step 4-3-2. SUMIF함수의 Criteria인수 

Criteria인수는 찾을 조건을 입력하는 셀입니다.

종목코드 000020의 2021년 지급된 배당금 총액을 합산할 예정이기 때문에 A2번 셀을 입력해 주었습니다.

 

Step 4-3-3. SUMIF함수의 Sum_Range 인수

드디어 마지막인서 Sum_Range입니다.

앞서 설정한 2가지 인수를 통해 조건을 설정해 주었다면 덧셈을 실행할 셀의 범위를 지정할 차례입니다.

배당금은 D열에 정리가 되어있으므로 D열을 전체 설정을 해주고 마찬가지로 범위이기 때문에 F4를 눌러 절댓값을 씌웠습니다.

 

서식을 완료한 결괏값으로 180이 나왔는데요. 2021년 시트에서 동화약품의 배당금을 확인해 보니 180원 딱 맞아떨어지네요. 동화약품은 결산배당으로 1년에 1번 배당을 하니 한 해 동안 더할 것이 없을 텐데...

분기배당을 지급하는 삼성은 어떤가 다시 검수를 해볼까요?

 

 

삼성전자와 삼성전자우는 분기배당으로 본주는 분기별 361원, 우선주는 1~3분기에는 361원을 지급하고 마지막분기에만 362원을 지급하였네요.

합산하면 본주는 1444원, 우선주는 1445원이네요.

 

함수를 이용해 계산한 결괏값도 본주는 1444원, 우선주는 1445원으로 일치합니다.

제대로 잘 작동하였다고 볼 수 있겠네요.

진도를 더 나가자니 너무 글이 길어질 거 같아 오늘은 sumif 함수를 사용하는 방법정도로 맺음을 해볼까 합니다.

다음 투자자 엑셀 활용능력 5편은 스파크라인을 삽입하는 방법에 대해서 다뤄볼 건데요..

오늘 정리한 자료를 이용하여 배당컷이 발생했는지 안 했는지 숫자만 보고 비교하는 것보다 스파크라인을 통해 시각적으로 비교해 보는 게 더 작업속도가 빨라집니다.

빠른 시일 내에 스파크라인사입에 대한 내용으로 포스팅을 들고 나타나겠습니다!

 

---

투자자 엑셀 활용능력 지난 시리즈 살펴보기

2022.12.15 - YoY, QoQ 증감률 계산하기 - 투자자 엑셀 활용능력 1편

YoY, QoQ 증감률 계산하기 - 투자자 엑셀 활용능력 1편

 

2022.12.21 - 재무제표 손익계산서 재무비율 계산하기(매출 총 이익률, 원가율, 판관비율, 영업이익률, 순이익률) - 투자자 엑셀 활용능력 2편

재무제표 손익계산서 재무비율 계산하기(매출총이익률, 원가율, 판관비율, 영업이익율, 순이익

 

2022.12.23 - Vlookup 함수로 손쉽게 재무정보를 정리하자- 투자자 엑셀 활용능력 3편

Vlookup 함수로 손쉽게 재무정보를 정리하자- 투자자 엑셀 활용능력 3편

---