G4(1월)부터 R4(12월)까지 12개의 월별 열이 있는 셀 행이 있는데 여기에 월별 청구 금액을 입력합니다.[초기 의견에서는 여러 행에서 여러 해에 대해 논의합니다. 그러나 더 이상 그렇지 않습니다. 4열에 딱 1년만이에요.]
청구서에 미결제 금액이 있는 경우 이는 표준 금액이며 그 값은 셀 F4에 저장됩니다.
해당 월에 납부해야 할 금액이 없으면 관련 G4:R4 셀을 비워 둡니다. 금액이 만기일인 경우 관련 셀에 지불 금액(아무것도 없으면 0)을 입력합니다.
A4 셀에 미결제 잔액의 누계(미결제 금액에서 결제 금액을 뺀 금액)를 표시하고 싶습니다. 예: 월 청구액은 100.00(F4)입니다. 1월(G4)에 75달러를 지불했으므로 아직 빚진 누계액은 25.00(A4)입니다. 나는 2월 청구서(H4)로 2월에 50달러를 지불했으므로 이제 75달러(A4)를 빚지고 있습니다.
청구 금액이 없지만 이전 잔액에 대해 지불하는 경우는 절대 없습니다. 이전 잔액이 있는 경우 항상 표준 금액이 청구됩니다. 따라서 G4:R4의 빈 셀은 요금이 청구되지 않았음을 의미합니다. G4:R4의 값은 표준 금액이 청구되고 입력된 금액이 지불되었음을 의미합니다.
답변1
A4에는 간단히 다음을 입력하십시오.
=$F4*COUNT($G4:$R4)-SUM($G4:$R4)
편집: 귀하의 의견이 일부 "$" 기호를 제거한 후 수식을 필요한 15개 행에 쉽게 복사할 수 있도록 했습니다.
답변2
귀하가 제공한 의견에서 해당 월의 셀 범위는 G4:R4입니다.
지금은너의 답:
누계(A4)에 입력할 공식은 다음과 같습니다.
=IF( SUM($G4:$R4) < $F$4*COUNTA($G4:$R4), $F$4*COUNTA($G4:$R4) - SUM($G4:$R4), 0)
이것을 분해하고부품 분석:
카운타($G4:$R4)
COUNTA는 "모두 계산"의 약자입니다. G4:R4 범위 내에서 비어 있지 않은 셀의 수를 계산합니다. 이는 수식에서 공백을 무시해야 한다고 말씀하셨기 때문입니다. 또한 이는 '지나간' 개월 수를 추적합니다.
F4*카운터($G4:$R4)
이것은 총 빚진 금액입니다. (월별 결제)*(개월 수)
합계($G4:$R4)
결제된 총액입니다. 기본적으로 공백은 무시됩니다.
그래서 우리는 기본적으로
IF( TotalPaid < TotalOwed )는 TotalOwed - TotalPaid를 표시합니다.
그렇지 않으면 0을 표시합니다.
답변3
참고: 이 답변은 질문이 명확해지기 전에 작성되었으며 현재 표현을 반영하지 않습니다. 의견에 질문 수정 이유가 기록되어 있기 때문에 그대로 두었습니다.
매달 일정 금액을 지불한다고 가정하는 몇 가지 답변이 있습니다. 그렇지 않으면 어떻게 되나요? 위약금이나 연체료 등의 문제가 발생하지 않고, 납부 내역과 관계없이 매월 청구 금액을 납부합니다. 결제 금액을 월 단위로 계산한다고 해서 정답이 보장되는 것은 아닙니다. 그러기 위해서는 청구서가 몇 장이나 있었는지 지정하는 방법이 필요합니다.
청구 주기에 특이한 점이 있을 수 있으며 참조 기간은 전년도일 수 있습니다. 현재까지의 청구 주기 수를 계산하는 것이 간단하지 않은 경우 해당 계산은 또 다른 슈퍼 유저 질문이 될 수 있습니다. 이 예에서는 간단하게 설명하기 위해 1월이 청구 기간 1이고 청구서가 청구되는 달의 날짜는 고려하지 않는다고 가정합니다.
이 간단한 예의 경우 A4에는 다음이 포함될 수 있습니다.
=$F$4*MONTH(TODAY())-SUM(G4:R4)
G5:R5의 값을 사용할 때 내년에 A4를 A5로 복사한다고 가정합니다. 몇 가지 참고사항:
- 표준 청구 금액이 해마다 변경될 수 있는 경우 F4를 달러 기호로 고정하지 마세요. 각 행에는 관련 F 열 값이 있습니다.
- 표준 청구 금액이 연도 중반에 변경될 수 있는 경우에는 더 복잡한 공식이 필요합니다.
- 이 공식은 12월에 모든 것이 균형을 이루며 다음 해로 이월되는 것이 없다고 가정합니다. 그렇지 않은 경우 셀에 연말 잔액을 추가하고 논리적인 위치는 다음 해의 시작이 되며 이를 합계에 포함할 수 있습니다.