[재무] 급여대장이나 입출금장부 작성시 자주 쓰는 엑셀 함수

비영리조직이건 소규모 회사건 엑셀 문서는 피할 수 없는 문서작성 도구입니다.

특히 각종 계산을 해야 하는 경우에는 더욱 그렇습니다.

 

한글 문서에서도 표를 그리면 합계나 평균값을 계산할 수는 있지만 엑셀처럼 복잡한 방식의 계산은 어렵습니다.

예를 들어,

다른 시트에 있는 특정 열의 데이터들을 합산하여 불러온다랄지,

다른 열에 있는 값들을 계산하여 결과값에서 소숫점이나 원단위를 절삭한 값으로 표시해야한다랄지 입니다.

 

1. 합계 =SUM(A1:A10)

엑셀에서 이 기호는 다들 아실 겁니다. A1 행에서 A10행 까지 모든 값을 합산한다는 뜻입니다.

이건 = A1+A2+A3+A4+A5+A6+A7+A7+A8+A9+A10 과 같습니다.

 

2. 다른 시트의 값을 불러와 표시하기

='시트이름'!$D$2

이것은 '시트이름'이라고 돼있는 시트에 D열의 2행에 있는 값을 표시하라는 함수입니다.

그렇다면 이걸 활용해서 다음 내용을 살펴보시기 바랍니다.

 

3. 다른 시트의 특정항목의 열에 있는 모든 값을 합산하여 표시하기

=SUMIFS('시트이름'!$D$2:$D$100, '시트이름'!$B$2:$B$100,"키워드조건", '시트이름'!$C$2:$C$100,"원하는키워드이름")

이 함수는 제가 실제로 써보았는데, 엑셀의 버전이나 컴퓨터 환경에 따라 함수의 작성방식이 약간 다른 것 같습니다.

제가 엑셀 전문가는 아니지만 검색을 해보면 블로그나 웹문서마다 다르게 표시하고 있더군요

예를 들어 시트이름을 표시할 때 '시트이름', 시트이름, "시트이름" 식으로 따옴표의 유무나 종류를 다르게 설명해놓기도 하고 또는 '$'표시할 때 열의 알파벳과 행번호를 붙이기도 하고 띄어쓰기도 하고 그래서 많이 헷갈리더군요.

 

SUMIFS는 다른 시트의 '조건'을 걸어 값을 합산(SUM)하는 함수입니다. 다시 간단히 설명하면

=SUMIFS('불러오려는시트이름'! 시트에있는값범위, '불러오려는시트이름'!첫번째조건범위,'키워드이름','불러오려는 시트이름'!'구하려는범위','구하려는조건키워드명')

또는 SUMIFS=('시트이름'!'값의범위','시트이름'!'제1조건범위',"제1조건키워드",'시트이름'!'제2조건(결과적인)범위',"제2조건키워드(결과적인)") 입니다.

만약 A라는 엑셀문서에  '입금내역'시트에 아래와 같은 내용이 있다고 가정해봅시다.

1	입금일 A	입금액 B	구분 C	회원명 D	회원종류 E	입금은행 F
2	2022.05.01.	5,000	회비	홍길동	평생	광주은행
3	2022.05.01.	10,000	후원금	임꺽정	정기	신협
4	2022.05.03.	5,000	기부금	양나영	일시	농협
...100번까지있음	.........................../	......	.....	.....	.....	....

여기서 '평생회원들이 낸 회비의 합계'를 구하고 싶다면,

회비의 금액이 적힌 B열,  E열의 평생회원은 첫번째 조건키워드, C열의 회비는 구하고자하는 조건키워드 값이 되겠죠.

그래서 =SUMIFS('입금내역'!$A$2:$A$100,'입금내역'!$E$2:$E$100,"평생",'입금내역'!$C$2:$C$100,"회비") 가 됩니다.

 

 

4. 4대보험요율을 계산한 값에 원단위를 절삭하여 표시하기

 가. 다음은 제가 작성한 4대보험요율 함수입니다.(2022.4.1.기준). 참고로 4대보험은 통상 사업주와 노동자가 5:5비율로 부담하지만 이때 산재보험은 사업주만 부담하는 것은 아시겠죠?

국민연금 (노동자부담)	건강보험 (노동자부담)	장기요양보험 (노동자부담)	고용보험 (노동자부담)	산재보험 (사업주부담)
=ROUNDDOWN(T2*0.045,-1)	=ROUNDDOWN(T2*0.03495,-1)	=ROUNDDOWN(Y2*0.1227,-1)	=ROUNDDOWN(T2*0.008,-1)	=ROUNDDOWN(T2*9.63*0.001,-1)

여기서 'ROUNDDOWN' 함수는 '절삭'하는 것이고 '-1'은 원단위를 절삭한다는 의미입니다. '0'으로 바꾸면 소숫점 이하를 절삭하는 게 되고 '1'로 바꾸면 소수점 첫째자리를 절삭하게 됩니다.

반올림하는 ROUNDUP과 교대해가면서 연습해보세요.