数式の日付の年を変更する必要がないようにする

Question 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/DPIFMONTH機能として

=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 年に問題が発生する可能性があります。

Answer