엑셀로 만드는 관리비 정산 (가스 / 전기세 등)

원룸, 오피스텔, 기숙사, 직원숙소 등에서 관리비 정산이 필요할 때

• 사용기간과 정산기간이 겹치는 “일수”만큼 공과금을 일할(일수비례) 계산해서 합산하면 됩니다.
• 핵심 로직은 겹치는일수 = MAX(0, MIN(정산종료, 사용종료) - MAX(정산시작, 사용시작) + 1) 이고,
  부과금액 = 금액 × (겹치는일수 / 정산일수) 입니다.

---

1) 표(데이터) 배치 예시 (권장)

[공과금 내역] 시트 (테이블명: Bill)

항목	호수	정산시작일	정산종료일	금액(원)
가스	@@@호	2025-10-22	2025-11-20	45,730
가스	@@@호	2025-11-21	2025-12-18	53,250

[사용자] 시트

이름호수사용시작일사용종료일항목합계

이름	호수	사용시작일	사용종료일	항목	합계
홍길동	@@@호	2025-10-30	2025-11-25	가스	(수식)

---

2) Excel 365/2021 이상 (LET 사용, 가장 깔끔)

사용자 시트의 합계 셀(예: F2) 에 아래 수식:

=LET(
  t, E2,        r, B2,
  uS, C2,       uE, D2,
  bt, Bill[항목], br, Bill[호수],
  bs, Bill[정산시작일], be, Bill[정산종료일],
  ba, Bill[금액],
  os, IF(bs>uS, bs, uS),
  oe, IF(be<uE, be, uE),
  od, IF(oe>=os, oe-os+1, 0),
  bd, be-bs+1,
  SUMPRODUCT((bt=t)*(br=r)*ba*od/bd)
)

근거(계산 원리)

• 정산기간(예: 10/22~11/20) 중에서 사용자 사용기간(10/30~11/25)과 겹치는 날짜만큼 비용을 부담하는 방식이 가장 공정한 정산 방식(일할정산) 입니다.

---

3) LET이 없는 버전(구버전 호환용, 길지만 동작)

테이블이 아니라 범위로 되어있다고 가정(예: 공과금 내역이 A2:E100):

• 공과금내역:
  • 항목 A2:A100, 호수 B2:B100, 시작 C2:C100, 종료 D2:D100, 금액 E2:E100
• 사용자행: 호수 B2, 사용시작 C2, 사용종료 D2, 항목 E2

=SUMPRODUCT(
 ($A$2:$A$100=E2)*
 ($B$2:$B$100=B2)*
 $E$2:$E$100*
 IF(
   IF($D$2:$D$100<D2,$D$2:$D$100,D2) >= IF($C$2:$C$100>C2,$C$2:$C$100,C2),
   IF($D$2:$D$100<D2,$D$2:$D$100,D2) - IF($C$2:$C$100>C2,$C$2:$C$100,C2) + 1,
   0
 )
 /($D$2:$D$100-$C$2:$C$100+1)
)

---

4) “겹치기만 하면 해당 정산기간 금액을 통째로 합산” (비추천이지만 가능)

일할이 아니라 조금이라도 겹치면 그 정산기간 전체 금액을 부과하려면:

=SUMIFS(Bill[금액], Bill[항목], E2, Bill[호수], B2, Bill[정산시작일], "<="&D2, Bill[정산종료일], ">="&C2)

근거

• 조건 정산시작 ≤ 사용종료 AND 정산종료 ≥ 사용시작 이면 기간이 겹칩니다.
• 다만 이 방식은 하루만 써도 한 달치가 붙는 문제가 있어, 기숙사 정산은 보통 2)의 일할 방식이 더 합리적입니다.

---

실무에서 자주 생기는 날짜 기준(중요)

• 위 수식은 **시작일/종료일을 “포함(+)”**하는 포함일수 기준입니다.
• 만약 퇴실일은 미사용(즉 종료일 제외) 규칙이면 +1을 빼는 식으로 조정해야 합니다.

---

[실습] 엑셀 샘플 파일 사용 방법

• 요청하신 기숙사 공과금(정산기간) + 사용자 사용기간(입·퇴실) 기반으로, 겹치는 일수만큼 일할정산하여 합산하는 샘플 엑셀 파일을 생성했습니다.
• 수식 로직 근거: 기간 겹침(Overlap) 일수 / 정산기간 일수 비율로 비용을 배분하는 방식이 가장 공정하고 실무에서 표준적으로 쓰입니다.

 

 

사용 방법(Step-by-step)

1. Bills 시트에 공과금 정산내역을 입력합니다.
   • 항목 / 호수 / 정산시작일 / 정산종료일 / 금액(원)
   • 현재 수식은 Bills!A2:E500 범위를 참조하므로 500행까지 자동 반영됩니다.
2. Users 시트에 사용자(입주자) 정보를 입력합니다.
   • 이름 / 호수 / 사용시작일 / 사용종료일 / 항목
   • 정산금액(원)(F열)은 수식이 이미 들어가 있어 자동 계산됩니다. (기본 50명까지 준비)
3. 정산 기준 변경(옵션)
   • “하루라도 겹치면 해당 기간 금액 전액” 같은 규칙으로 바꾸고 싶다면 Notes 시트에 대체 수식 예시를 적어두었습니다.

엑셀 - 1. Bills 시트

 

엑셀 - 2. Users 시트

공과금_정산_샘플.xlsx

0.01MB