엑셀로 매매일지 양식을 만들어보자 Part 2(SUMPRODUCT 함수) - 투자자 엑셀 활용능력 14

지난번 포스팅에 이어서 엑셀로 매매일지를 만들어보기 두 번째 시간입니다.

지난 편에서는 SUMIF함수를 이용하여 보유주식수를 자동으로 계산하여 입력되도록 함수식을 만들었었는데요.

오늘은 예고했던 대로 SUMPRODUCT함수를 이용하여 내가 매수하고 매도했던 종목들의 평균단가를 자동으로 계산하여 입력되도록 함수식을 만들어볼 예정입니다.

 

 

매매일지 양식만들기 PART1까지 완료하면 이 사진과 같은 상태일 텐데요.

여기서부터 시작하도록 하겠습니다.

먼저 오늘 사용할 함수인 SUMPRODUCT에 대해 간략히 알아보고 시작해 볼까요?

 

SUMPRODUCT 함수란?

SUMPRODUCT 함수의 설명을 보면 배열 또는 범위의 대응되는 값끼리 곱해서 그 합을 구한다라고 되어있습니다

백문불여일견!

간단한 예제를 만들어서 SUMPRODUCT의 활용법을 익혀보도록 할까요?

매매일지 만들기 PART 1에서 만들어둔 자료 중 삼선전자의 매수수량만 편집해서 가져와 보았습니다.

=SUMPRODUCT($C$3:$C$5,$D$3:$D$5)

적용된 수식을 쪼개서 보면 수량이 있는 열(C3~C5)과 매수가가 있는 열(D) 2개의 인수를 각각 곱한 후 총합을 더하는 건데요. 이 말은 즉 E열의 금액의 총합과 같다고 볼 수 있습니다.

 

 

수식을 실행시켜서 값을 비교해 보면 15,800,000원으로 금액의 합과 SUMPRODUCT의 합이 같습니다.

즉 SUMPRODUCT함수는 지정한 범위의 값을 곱한 후 합계를 내는 함수라고 이해하시면 되겠습니다.

이 말을 수식으로 풀면?

=SUMPRODUCT($C$3:$C$5,$D$3:$D$5) = (100 X 100,000)+(50 X 80,000)+(30 X 60,000)

 

 

 

SUMPRODUCT 함수로 매수한 주식의 평균단가를 구해보자!

주식의 평균 단가라는 것은 총 투자한 금액을 보유하고 있는 주식수로 나누면 구할 수 있습니다.

 

총투자금액 / 보유한주식수 = 평균단가

 

이 평단가라는 것이 매수만 했을 경우에는 종목별 매수총액을 보유한 주식수로 나눠주기만 하면 되는데요.. 매도가 섞여버리게 되면 조금 복잡해지게 됩니다.

 

(매수총투자금액-매도총투자금액) / 보유한주식수 = 평균단가

 

 

그래서 매수총투자금액을 먼저 구하고 매도총투자금액을 구하는 방법으로 한 단계씩 나아가보겠습니다.

 

매수총투자금액 구하기

 

=SUMPRODUCT (      (  ($C$11:$C$19)   =  C4)  *  ($G$11:$G$19)    )

내가 원하는 종목의 투자금액만 더 해야 하기 때문에 처음 살펴보았던 sumproduct의 구조보다 조금 복잡해졌습니다.

하지만 조각조각 쪼개어 살펴보면 간단합니다.

삼선전자라는 종목의 금액만 더 해야 하기 때문에 삼선전자를 찾기 위한 조건을 설정해줘야 하는데요.

 

($C$11:$C$19) =C4

 

이 부분이 종목명중 삼선전자와 일치하는 조건을 찾기 위해  세팅한 조건절입니다.

 

($G$11:$G$19)

 

세팅된 조건에 맞는 투자금액을 더 해야 하기 때문에 위와 같이 합계를 구할 셀값을 지정해 주었습니다.

함수식을 작성할 때 괄호를 유의해야 하는데요.. 그래서 각 인자마다 다른 색의 괄호로 표기해 두었으니 참고해 주세요.

 

 

매도총투자금액 구하기

=SUMPRODUCT (      (  ($C$11:$C$19)   =  C4)  *  ($J$11:$J$19)    )

 

앞에서 매수총투자금액을 구한 것과 같은 방법입니다.

다만 합산할 금액셀을 매도금액이 있는 J열로만 바뀌었습니다.

이제 이 매수총투자금과 매도총투자금을 빼서 보유주식수로 나눠주기만 하면 평균단가가 구해지겠습니다

 

총투자금액 구하기

총투자금액은 매수총투자금-매도총투자금입니다

 

=SUMPRODUCT((($C$11:$C$19)=C4)*($G$11:$G$19))-SUMPRODUCT((($C$11:$C$19)=C4)*($J$11:$J$19))

 

그렇다면 미리 세팅해 두었던 두계의 조건식을 이어 붙이기만 하면 되겠네요.

 

 

하지만 한 가지 이변이 발생하였습니다.

이 매매일지의 주인공이 투자의 고수였는지.. 싸게 사서 비싸게 매도를 잘해버렸습니다.

매수총액보다 매도총액이 커서 결괏값이 음수로 나와버렸네요.

여기서는 중학수학에서 배웠던 절댓값이라는 개념을 이용해보려고 합니다.

엑셀에서도 절댓값으로 만들어주는 함수가 있는데요

 

이 함수를 이용해서 문제를 해결해 보지요.

 

=ABS(SUMPRODUCT((($C$11:$C$19)=C4)*($G$11:$G$19))-SUMPRODUCT((($C$11:$C$19)=C4)*($J$11:$J$19)))

 

해결 완료입니다

이제 총투자금액에서 보유주식수만 나누어주면 평균단가가 계산이 되겠네요.

 

나누기를 통해 평균단가를 구해주었는데요.. NEVER는 전량 매도하여 보유주식이 없는 관계로 나누기불가 에러코드가 발생하였습니다.

지난번에 배웠던 IFERROR 함수를 이용해서 보기 좋게 바꿔줘야겠군요.

 

=IFERROR(H4/D4, "0")

 

마침내 평균단가 구하기가 완료가 되었습니다.

SUMPRODUCT와 ABS, IFERROR함수까지 오늘은 다양하게 연습을 한 것 같습니다.

오늘 공부했던 내용은 SUMPRODUCT가 아니더라도 지난번에 연습했던 SUMIF로도 해결이 가능하지만.. SUMPRODUCT도 알아두면 유용한 함수라서 SUMPRODUCT로 만들어보았습니다.

투자자 엑셀 활용능력 15편은 쉬어가는 의미로 양식의 나머지 부분을 채워 넣고 보기 좋게 정리하면서 마무리를 짓는 시간으로 준비해 보겠습니다.

 

 

 

---

투자자 엑셀 활용능력 지난 편 보기

2023.02.02 - 엑셀로 매매일지 양식을 만들어보자 Part 1(SUMIF함수) - 투자자 엑셀 활용능력 13

엑셀로 매매일지 양식을 만들어보자 Part 1(SUMIF함수) - 투자자 엑셀 활용능력 13

 

---