「sumif」関数を使用して日付範囲内の月を集計するスプレッドシートがあります。
=SUMIFS(金額;DPIF;">=2021/1/1";DPIF;"<=2021/1/31")
この数式は 12 回 (各月) 繰り返され、2021 年から 2022 年への変更に伴い、12 個の数式すべての日付 (年) 範囲を手動で変更する必要があります。
別のセルを参照するように年 (数式内の 2021) だけを変更しようとしましたが、Excel では日付として認識されなくなりました。
スプレッドシートを1年から翌年まで使えるようにするにはどうすればよいでしょうか?
答え1
アプローチ1
関数に開始日と終了日をハードコードするのではなくSUMIFS
、Excelの数式を使用して生成し、計算された日付値を含むセルを参照します。DATE
そしてEOMONTH
以下のスクリーンショットに示すように、関数を使用できます。
1 月の式SUMIFS
は次のように変更できます。
=SUMIFS(Amount;DPIF;">="&Sheet1!B3;DPIF;"<="&Sheet1!C3)
1 月の場合、セル にSheet1!B3
はその月の開始日が、Sheet1!C3
には終了日があります。これらのセル参照を、計算された日付値を入力する場所に変更します。2 月の場合、開始/終了日のセルはSheet1!B4
とSheet1!C4
、3 月の場合はSheet1!B5
とSheet1!C5
などです。12 か月の計算式が 12 行 × 1 列の範囲として配置されている場合はSUMIFS
、1 月の計算式を次の 11 か月にコピーできます。
数式内のアンパサンド (& 記号) はそれぞれ、参照先セルの値と比較演算子を連結するため、変更された数式では日付値 01/01/2024 が返されるため">="&Sheet1!B3
、実質的には と同等になります。">=01/01/2024"
Sheet1!B3
Sheet1!B1
年を変更するには、適切なセル (スクリーンショットのセル) の年の値を変更するだけです。12 か月のSUMIFS
計算式はすべて自動的に新しい年の開始日と終了日を参照するため、計算式を編集する必要がなくなります。
このアプローチでは、うるう年の 2 月の月末日も管理されます (セルのスクリーンショットに示されているようにC4
)。
アプローチ2
別の方法としては、既存の範囲DPIF
に範囲を追加して日付値を月数値に変換する方法があります。Amount/DPIF
MONTH
機能として
=MONTH(date)
date
範囲内の日付セルを表しますDPIF
。
これらの月ごとの値を呼び出すMPIF
と、次のような式を使用できます。
=SUMIFS(Amount;MPIF;1)
=SUMIFS(Amount;MPIF;2)
...
=SUMIFS(Amount;MPIF;12)
年をまったく気にする必要がありません。
この月次アプローチでは、データ範囲に複数の年が含まれていないことを前提としています。複数の年が含まれている場合、1 月、2 月などのすべてのデータが合計されますが、これはおそらく望んでいる結果ではありません。
この場合、解決策を改良するには、YPIF
範囲内のデータに年の値を追加し、YEAR
これらの値を取得するには関数を使用します。次に、これらの年の値を含めるために、数式に2番目の条件を追加します。SUMIFS
たとえば、
=SUMIFS(Amount;MPIF;1;YPIF;2024)
もちろん、これは、編集が現在よりも少し簡単になったとしても、各年ごとに12個の数式を変更する必要があることを意味します。これを回避するには、年値を独自のセルに配置し、次のような数式を使用して参照します。
=SUMIFS(Amount;MPIF;1;YPIF;Sheet1!$B$1)
アプローチ3
推奨するものではないが、完全性のために含めた。数式を次のように変更する。
=SUMIFS(Amount;DPIF;">=01/01/"&Sheet1!$B$1;DPIF;"<=31/01/"&Sheet1!$B$1)
=SUMIFS(Amount;DPIF;">=01/02/"&Sheet1!$B$1;DPIF;"<=28/02/"&Sheet1!$B$1)
等
これには、2021 年、2022 年、2023 年に機能する 1 回限りの編集作業が含まれますが、誰かが変更することを覚えていない限り、2024 年の 2 月の月次合計から 2024 年 2 月 29 日が省略され、元に戻さない場合は 2025 年に問題が発生する可能性があります。
答え2
- 2つの完全な日付(年だけではなく)をそれぞれ別のセルに入力します。次に、数式で日付の値をセルへの参照に置き換えます。
- すべての数式を一度に変更するには、シート内で「すべて置換」を実行します。
- 来年は、2つの別々のセルの日付値のみを変更します