빌라·공동주택 관리비 부과 및 정산 엑셀
관리사무소가 없는 빌라는 보통 총무 한 사람이 한전·수도 고지서와 청소·승강기 계약서를 모아 세대별로 나눠요. 항목마다 나누는 기준이 달라서 공용전기는 면적, 청소비는 세대 균등, 수도는 사용량으로 나누는 게 일반적이에요. 이 통합 문서는 항목과 기준만 정하면 세대별 관리비와 계산 근거가 적힌 고지서를 만들고, 누가 냈는지까지 관리해요.
- 시트:
- 9
- 지원 버전:
- Microsoft 365용 Excel, Excel 2016 이후 버전, Mac용 Excel, 웹용 Excel
- 업데이트:
이 템플릿으로 할 수 있는 일
- 항목마다 배분 기준 선택: 전용면적, 세대 균등, 수도 사용량 (세대 계량기 지침으로 계산)
- 공실 세대에 부과할지 항목별로 선택, ㎡당·세대당·㎥당 단가 표시
- 반올림 단위(10원)와 항목별 반올림 차이, 전월 미납 포함 납부할 금액
- 계산 근거가 적힌 세대별 고지서(A4), 수납 상태(완납·일부 납부·미납·기한 경과), 월별 기록과 차트
구성
통합 문서에는 시트가 9개 있어요. 입력하는 시트는 몇 개뿐이고 나머지는 계산돼요.
| 시트 | 용도 | 입력하는 것 | 자동 계산되는 것 |
|---|---|---|---|
| 시작하기 | 설정과 이번 달 결과 | 건물명, 부과 연월, 납부기한, 계좌, 고지서 세대, 반올림 단위 | 세대 수, 총액, 부과 합계, 반올림 차이, 평균, 수납액, 수납률, 미납 세대 |
| 세대 | 세대(60) | 호수, 입주자, 전용면적, 입주 상태, 연락처, 수도 지침, 전월 미납액 | 수도 사용량, 확인 |
| 부과 항목 | 항목(20) | 항목, 이번 달 금액, 배분 기준, 공실 포함, 메모 | 기준 합계, 단가, 세대 부과 합계, 반올림 차이 |
| 부과 내역 | 세대 × 항목 표 | 없음 | 항목별 세대 부담액, 이번 달 관리비, 전월 미납, 납부할 금액 |
| 고지서 | 세대별 고지서(인쇄용) | 없음 | 항목, 배분 기준, 계산 근거, 금액, 합계, 납부기한, 계좌 |
| 수납 | 세대별 수납 | 납부일, 납부액, 메모 | 미납액, 상태 |
| 월별 기록 | 달마다 총액·수납 | 지난달 기록 | 이번 달 자동 |
| 목록 | 드롭다운 | 입주 상태, 배분 기준, 공실 포함 | 없음 |
| 점검 | 점검 6개 | 없음 | OK 또는 확인 필요 |
시작하기 전에
- 세대별 전용면적을 등기부등본이나 건축물대장에서 확인하세요.
- 이번 달 한전 공용 전기, 수도, 청소·승강기·소독 계약 금액, 장기수선충당금 적립액을 모으세요.
- 세대 수도 계량기 지침을 같은 날 읽어 두세요.
단계별 사용법
세대 등록
세대 시트에 호수, 입주자, 전용면적, 입주 상태를 넣어요. 처음 한 번만 하면 돼요.
계량기 지침
매달 수도 전월 지침과 당월 지침을 넣으면 사용량이 계산돼요.
항목과 금액
부과 항목 시트에 이번 달 금액을 넣고 배분 기준과 공실 포함 여부를 골라요.
부과 확인
부과 내역 시트에서 세대별 금액을, 부과 항목 H열에서 반올림 차이를 확인해요.
고지서 인쇄
시작하기 B10에서 호수를 바꿔 가며 고지서 시트를 인쇄하거나 PDF로 보내요.
수납 관리
수납 시트에 납부일과 납부액을 적어요. 다음 달에는 미납액을 세대 시트 I열로 옮겨요.
수식 지도
통합 문서의 모든 수식을 시트와 열별로 정리했어요. 복사된 수식 열은 한 번만 세고, 적용 범위를 함께 표시해요.
| 시트 | 셀 | 함수 | 계산 내용 | 읽는 값 | 쓰이는 곳 |
|---|---|---|---|---|---|
| 부과 항목 | E5 24행까지 아래로 복사됨 | IF, SUMPRODUCT | 기준 합계 | C5, D5 | 단가 = 이번 달 금액 ÷ 기준 합계. |
| 부과 내역 | E5 64행까지 아래로 복사됨 | IF, OR, AND, ROUND | 세대별 항목 부담액 | B5, C5, D5 | 오른쪽 열은 항목 2, 3 …이에요. Y열이 이번 달 관리비 합계예요. |
| 세대 | H5 64행까지 아래로 복사됨 | IF | 수도 사용량 | F5, G5 | 확인 열이 지침이 거꾸로이거나 공실에서 사용량이 나오면 알려줘요. |
| 부과 항목 | H5 24행까지 아래로 복사됨 | IF | 반올림 차이 | G5, B5 | 시작하기 B18이 전체 차이예요. +면 남는 돈, −면 모자라는 돈이니 다음 달에 반영하세요. |
| 고지서 | C12 | IF, TEXT | 계산 근거 | B12 | 입주자가 자기 관리비가 어떻게 나왔는지 바로 알 수 있어요. |
| 수납 | F5 64행까지 아래로 복사됨 | IF, TODAY | 수납 상태 | B5, D5 | 시작하기 B23이 미납 세대 수예요. |
모든 수식 설명
아래 수식은 파일에서 그대로 가져온 것이라 여기서 읽는 내용이 셀에 들어 있는 내용과 정확히 같아요. 엑셀에서 셀을 클릭하면 수식 입력줄에서 볼 수 있어요.
=IF(A5="", "", SUMPRODUCT(((세대!$A$5:$A$64<>"")*((세대!$D$5:$D$64<>"공실")+(D5="예")>0))*IF(C5="전용면적", N(+세대!$C$5:$C$64), IF(C5="수도 사용량", N(+세대!$H$5:$H$64), 1+0*N(+세대!$C$5:$C$64)))))부과 대상 세대(입주 세대, 공실 포함이면 공실도)의 전용면적 합계, 세대 수, 또는 수도 사용량 합계예요.
부분별 설명
(세대!$A$5:$A$64<>"")- 등록된 세대.
((세대!$D$5:$D$64<>"공실")+(D5="예")>0)- 입주 세대이거나, 공실 포함이 "예".
IF(C5="전용면적", 면적, IF(C5="수도 사용량", 사용량, 1))- 기준에 따른 값.
SUMPRODUCT(…)- 대상 세대만 더함.
예시 데이터로 보면: C5 = 전용면적, D5 = 예 → E5 결과 765.04
어떻게 쓰이나: 단가 = 이번 달 금액 ÷ 기준 합계.
바꾸는 방법: 기준과 공실 포함은 C·D열에서.
배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →=IF(OR($A5="", '부과 항목'!$A$5=""),
"", IF(OR(AND($C5="공실", '부과 항목'!$D$5<>"예"), N('부과 항목'!$E$5)=0),
0, ROUND('부과 항목'!$B$5*IF('부과 항목'!$C$5="전용면적", $B5, IF('부과 항목'!$C$5="수도 사용량", $D5, 1))/'부과 항목'!$E$5/시작하기!$B$11, 0)*시작하기!$B$11))항목 금액 × 이 세대의 기준값 ÷ 기준 합계를 반올림 단위로 반올림해요. 공실 세대이고 공실 포함이 아니면 0이에요.
부분별 설명
IF('부과 항목'!$C$5="전용면적", $B5, IF(… ="수도 사용량", $D5, 1))- 이 세대의 면적, 사용량, 또는 1.
'부과 항목'!$B$5*…/'부과 항목'!$E$5- 비율로 나눔.
ROUND(…/'시작하기'!$B$11, 0)*'시작하기'!$B$11- 10원 단위 반올림.
AND($C5="공실", … <>"예")- 공실 제외.
예시 데이터로 보면: B5 = 59.94, C5 = 입주, D5 = 13 → E5 결과 14,600
어떻게 쓰이나: 오른쪽 열은 항목 2, 3 …이에요. Y열이 이번 달 관리비 합계예요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 반올림, 올림, 내림 함수: ROUND, ROUNDUP, ROUNDDOWN 사용법 →=IF(A5="", "", N(G5)-N(F5))당월 지침 − 전월 지침(㎥)이에요.
부분별 설명
N(G5)-N(F5)- 계량기 지침 차이.
예시 데이터로 보면: F5 = 412, G5 = 425 → H5 결과 13
어떻게 쓰이나: 확인 열이 지침이 거꾸로이거나 공실에서 사용량이 나오면 알려줘요.
바꾸는 방법: 매달 지침을 바꾸세요.
배우기: 엑셀 기본 수식 작성법: 더하기, 빼기, 곱하기, 나누기 →=IF(A5="", "", G5-B5)세대에 부과한 합계 − 실제 금액이에요. 10원 단위로 반올림해서 생기는 몇십 원의 차이예요.
부분별 설명
G5 = SUM('부과 내역'!E5:E64)- 세대 부과 합계.
-B5- 실제 금액.
예시 데이터로 보면: G5 = 186,360, B5 = 186,400 → H5 결과 -40
어떻게 쓰이나: 시작하기 B18이 전체 차이예요. +면 남는 돈, −면 모자라는 돈이니 다음 달에 반영하세요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 SUM 함수 사용법 →=IF(A12="", "", IF(B12="전용면적",
TEXT($B$8, "0.00")&"㎡ × "&TEXT('부과 항목'!F5, "#, ##0.0")&"원",
IF(B12="수도 사용량", $B$9&"㎥ × "&TEXT('부과 항목'!F5, "#, ##0.0")&"원", "세대당 "&TEXT('부과 항목'!F5, "#, ##0")&"원")))배분 기준에 따라 "84.97㎡ × 243.6원", "16㎥ × 3,279.0원", "세대당 21,818원" 같은 설명을 만들어요.
부분별 설명
TEXT($B$8, "0.00")&"㎡ × "&TEXT(단가, "#,##0.0")&"원"- 전용면적 기준.
$B$9&"㎥ × "…- 수도 사용량 기준.
"세대당 "&TEXT(단가, "#,##0")&"원"- 세대 균등.
예시 데이터로 보면: B12 = 전용면적 → C12 결과 84.97㎡ × 243.6원
어떻게 쓰이나: 입주자가 자기 관리비가 어떻게 나왔는지 바로 알 수 있어요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 텍스트 함수와 날짜 함수 사용법 →=IF(A5="", "", IF(B5=0, "부과 없음",
IF(E5<=0, "완납", IF(TODAY()>시작하기!$B$8, "기한 경과", IF(N(D5)>0, "일부 납부", "미납")))))납부할 금액에서 낸 금액을 뺀 미납액으로 완납, 일부 납부, 미납을 정하고, 기한이 지나면 "기한 경과"예요.
부분별 설명
E5 = B5-N(D5)- 미납액.
E5<=0- "완납".
TODAY()>'시작하기'!$B$8- 기한 경과.
N(D5)>0- "일부 납부".
예시 데이터로 보면: B5 = 136,070, D5 = 136,070 → F5 결과 완납
어떻게 쓰이나: 시작하기 B23이 미납 세대 수예요.
바꾸는 방법: 납부기한은 시작하기 B8.
배우기: 엑셀 TODAY, NOW 함수 사용법: 오늘 날짜와 현재 시간 →팁과 한계
- 공동주택관리법상 의무관리대상(150세대 이상 등)은 관리비 항목과 공개 방법이 정해져 있어요. 이 양식은 소규모 빌라·연립·오피스텔용이에요.
- 세대 전기·가스는 보통 한전·도시가스가 세대마다 따로 고지해요. 공용분만 부과 항목에 넣으세요.
- 장기수선충당금은 원래 소유자가 내는 돈이에요. 세입자가 냈다면 이사 갈 때 돌려받을 수 있으니 따로 기록해 두세요.
- 배분 기준은 입주자 회의나 관리규약으로 정해 두면 분쟁이 줄어요.
- 반올림 차이는 월별 기록 메모에 남겨 다음 달에 정산하세요.
이 템플릿에 쓰인 기능 배우기
도움이 되었나요? 잘못된 내용이 있나요? 의견 보내기 · admin@arumsoft.com