중복된 셀 삭제하기, SUMIFS 함수활용하기 - 투자자 엑셀 활용능력 8편

투자자 엑셀 활용능력이 어느덧 8번째 시간이 되었습니다.

지난번 투자자엑셀 활용능력 7편에서는 전자공시 시스템 DART에서 공시정보활용마당을 이용하여 상장기업의 재무정보를 내려받아 엑셀로 변경하는 것에 대해 알아봤었는데요..

오늘은 7편에서 예고했던대로 sumifs함수와 중복된 셀을 쉽게 제거하는 방법에 대해 알아보려고 합니다.

오늘 sumifs함수와 중복된 셀 제거방법을 배우게되면 공시정보 활용마당에서 내려받았던 방대한 자료를 내 입맛대로 원하는 계정만 골라서 정리를 할 수 있으실 거예요!

완성된 예제는 재무상태표와 손익계산서 파트인데요.. 재무제표를 볼 때 가장 많이 열어보게 되는 손익계산서의 계정들을 정리해 보도록 하겠습니다.

 

공시정보활용마당에서 내려받은 22년 1분기의 손익계산서를 엑셀로 열어보았습니다.

배열을 살펴보면 회사의 각 계정마다 셀이 배분이 되어있음을 확인할 수 있는데요..

현재 상태에서는 각각의 계정이 기업별로 나뉘어있지 않아 한눈에 알아보기가 힘이 들것 같습니다.

그래서 가장 먼저 해줘야 할 작업은 각 기업별로 내가 원하는 계정을 새 시트에 배열하는 작업인데요..

 

 

우선 공시정보 활용마당의 데이터중 종목코드와 회사명을 전부 복사하여 새로운 시트를 만들어 붙여 넣기를 해주었습니다.

그다음으로 해줘야 할 작업은 중복되어 있는 회사명을 제거하는 일인데요.

일일이 셀을 선택하여 삭제하자니 손도 많이 가고 시간도 많이 잡아먹게 됩니다.

여기서 사용할 기능이 중복된 항목 제거입니다.

 

 

중복된 항목을 제거한 데이터가 있는 A1셀에서 

데이터 - 중복된 항목제거

메뉴를 차례로 클릭하시면 팝업창이 하나 떠오르는데요. 중복된 항목을 제거할 셀을 선택한 후 확인을 눌러주시면 되겠습니다.

 

중복된 셀 제거를 한 후 필터를 씌워 종목코드 오름차순으로 정렬된 상태입니다.

여기까지 오셨으면 절반은 완성하셨네요.

여기서 다음단계는 개인 취향인데요.

 

공시정보 활용마당에서 내려받은 데이터중 수집하고 싶은 항목을 선택해야 하는데요.

 

 

 

저는 매출액, 매출원가, 매출총이익, 판매비와 관리비, 영업이익, 당기순이익을 수집해보고자 합니다.

투자자엑셀활용능력을 꾸준히 보신 분은 아시겠지만..

 

매출총이익 = 매출액 - 매출원가

영업이익 = 매출액 - 매출원가 - 판매비와 관리비

 

매출총이익과 영업이익은 위의 수식으로 계산을 할 예정이고요 sumifs를 이용하여 크롤링할 데이터는 매출액, 매출원가, 판매비관리비, 당기순이익 4 항목이 되겠습니다.

이제 sumifs함수를 이용해 볼 건데요.오늘 배울 SUMIFS 함수는 투자자 엑셀 활용능력 4편에서 공부했던 SUMIF와는 비슷하지만 다른 함수입니다.

 

SUMIFS VS SUMIF

 

먼저 겉으로 보기에 가장 쉽게 찾을 수 있는 다른 점은 S가 붙냐 안 붙냐의 차이겠네요. SUMIFS와 SUMIF함수는 둘 다 조건에 맞는 셀의 합을 구한다는 공통점이 있는데요. SUMIFS는 여러 개의 조건을 추가할 수 있다는 점이 다릅니다. 머리 아픈 함수에 대한 설명은 이쯤 줄이고 실전으로 들어가 보겠습니다.

 

SUMIFS를 통해 데이터 수집을 하기 전 정확도를 높이기 위해 조건을 추가를 해주었는데요. 매출액, 매출원가, 판매비와 관리비, 당기순이익의 항목코드도 공시정보활용마당의 원본데이터에서 가져왔습니다.

 

 

항목코드는 항목명과 1:1로 매칭이 되어있는데요. 기업바다 항목명은 다른 경우가 있어도 항목코드는 같기 때문에 데이터 수집을 할 때 항목코드와  항목명을 같이 활용하시는 게 정확도가 더 높습니다. 데이터 수집을 위한 준비는 모두 끝났고요.. 이제 SUMIFS함수를 이용하여 경방의 매출액, 매출원가, 판매비와 관리비, 당기순이익을 끌어와보도록 하겠습니다.

 

SUMIFS의 함수 인수

SUMRANGE : 합계할 데이터가 있는 셀의 범위 Criteria_range1 : 조건을 적용시킬 셀 범위 Criteria1 : 찾아야 할 조건

 

SUMIFS의 함수의 기본은 이렇게 3가지의 인수로 구성이 되어있는데요. 2개의 조건을 만족시키고자 한다면 Criteria_range2, Criteria2로 함수식이 늘어나고 3개의 조건을 만족시키고자 한다면  Criteria_range3, Criteria3으로 함수식이 늘어나는 등 수식은 길어지지만 그 뼈대는 동일합니다.

 

SUMIFS함수를 이용하여 경방의 매출액 데이터를 크롤링해왔는데요. 함수식과 결괏값은 위의 사진과 같습니다. 함수식에 제법 복잡하니 좀 더 디테일하게 설명해 보도록 하겠습니다.

 

 

SUMRANGE : '2022_1분기 보고서_02_손익계산서_연결_2022113'!$E:$E

 

SUMRANGE는 합계할 데이터가 있는 셀을 선택해줘야 하는데요.공시정보 활용마당에서 1분기 실적데이터를 가져와야겠지요. 지난번 엑셀활용능력에서 다른 시트의 데이터를 인용할 때는 '시트이름'! 이런 식으로 표기가 된다고 말씀드렸었는데요.. 위에 인수에 입력된 걸 해석해 보면 2022_1분기 보고서_02_손익계산서_연결_2022113 시트에서 E열의 모든셀 이라는 뜻입니다. 범위이기 때문에 F4키를 눌러 절댓값($)을 사용하였습니다.

 

이제 조건을 지정해 줄 차례인데요. 제가 생각한 조건은 2가지입니다.

 

1. 종목코드가 같은 셀을 찾을 것

2. 항목코드가 같은 셀을 찾을 것

 

이제 이 2가지 조건을 설정하는 단계로 넘어가 보겠습니다.

 

 

Criteria_range1 : '2022_1분기 보고서_02_손익계산서_연결_2022113'!$A:$A

Criteria_range함수는 조건을 적용시킬 셀의 범위인데요.

첫 번째 조건은 종목코드가 같은 셀을 찾아야 합니다. 그래서 종목코드가 있는 A열을 전체 선택하였고 범위이기 때문에 절댓값을 지정해 주었습니다.

 

 

Criteria1 : Sheet1!$A3

조건의 범위는 지정해 주었고 매칭시켜야 할 조건은 각각의 종목코드이기 때문에 경방의 경우 A3셀을 선택해 주었습니다.

한 가지 특이점으로는 "$A3" A열에만 절댓값 코드가 적용이 되어있는데요.

이 의미는 A열은 고정한 상태로 행만 변화를 시키겠다는 뜻입니다.

이렇게 설정한 이유는 매출원가, 판매비관리비, 당기순이익을 구할 때 자동 채우기를 통해 완성시키려고 하는데요.

A열에 고정을 시키지 않고 자동채우기 툴을 잡고 우측으로 끌게 되면 B3셀로 이동이 되어 함수적용에 에러가 발생하기 때문입니다.

 

이제 조건 2번을 설정해 줄 건데요.. 조건 1번과 똑같은 방식이니.. 빠르게 넘어가 볼게요!

 

 

Criteria_range2 :'2022_1분기 보고서_02_손익계산서_연결_2022113'!$C:$C

조건 2번은 항목코드가 같은 셀을 찾는 것이었습니다.

항목코드가 있는 C열 전체를 선택하고 절댓값을 지정해 주었습니다.

 

 

Criteria2 : Sheet1! C$1

아까 $A3셀과는 달리 C$1로 열이 아닌 행에 절댓값을 지정해 주었는데요.

항목코드의 경우 앞서 1번 행에 입력을 해두었기 때문에 열이 아닌 행에 절댓값을 씌웠습니다.

 

복잡한 과정을 거쳐 드디어 전체를 완성시켰습니다.

같은 방식으로 현금흐름표와 재무상태표도 정리해 보시면 기업스크리닝할 때 도움이 많이 될 거라고 생각이 됩니다!

이상으로 투자자 엑셀 활용능력 8편을 마치도록 하겠습니다.

---

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

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

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

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

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

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

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

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

2022.12.29 - 엑셀 스파크라인 추가하기(기업의 5개년 배당지급내역을 필터링하자) - 투자자 엑셀 활용능력 5편

엑셀 스파크라인 추가하기(기업의 5개년 배당지급내역을 필터링하자) - 투자자 엑셀 활용능력 5

2022.12.31 - 구글스프레드시트로 현재가 불러오기(기업의 5개년 배당지급내역을 필터링하자) - 투자자 엑셀 활용능력 6편

구글스프레드시트로 현재가 불러오기(기업의 5개년 배당지급내역을 필터링하자) - 투자자 엑셀

2023.01.04 - 공시정보 활용마당을 이용하여 기업의 재무자료를 내려받자 - 투자자 엑셀 활용능력 7편

공시정보 활용마당을 이용하여 기업의 재무자료를 내려받자 - 투자자 엑셀 활용능력 7편

---