G4 (1 月) から R4 (12 月) までの 12 個の月次列を持つセルの行があり、そこに月々の請求に対して支払われる金額を入力します。[以前のコメントでは、複数の行に複数の年が記載されていることが指摘されていました。しかし、現在はそうではなく、行 4 に 1 年だけ記載されています。]
請求書に支払金額がある場合、それは標準金額であり、その値はセル F4 に保存されます。
ある月に支払額がない場合、関連する G4:R4 セルを空白のままにします。支払額がある場合は、関連するセルに支払額 (ない場合はゼロ) を入力します。
セル A4 に未払い残高の累計 (支払額から支払額を引いたもの) を表示したいと思います。例: 毎月の請求額は 100.00 (F4) です。1 月に 75 ドル支払ったので (G4)、未払い残高は 25.00 ドル (A4) です。2 月に 2 月の請求額として 50 ドル支払ったので (H4)、現在 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)
これを分解して部品を分析する:
COUNTA($G4:$R4)
COUNTA は「Count All」の略です。範囲 G4:R4 内の空白でないセルの数をカウントします。これは、数式で空白を無視する必要があると指定したためです。さらに、これは経過した月数を追跡します。
F4*COUNTA($G4:$R4)
これは合計支払額です。（月々の支払い）*（月数）
SUM($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 月にすべての残高が揃い、翌年に繰り越されるものはないと想定しています。そうでない場合は、年末残高をセルに追加し、論理的には翌年の初めにして、それを合計に含める必要があります。