我有一個電子表格，它使用“sumif”函數併計算日期範圍內的月份；
=SUMIFS(金額;DPIF;">=1/1/2021";DPIF;"<=31/1/2021")
此公式重複十二次（每個月），隨著從 2021 年到 2022 年的變化，我現在必須手動更改所有 12 個公式中的日期（年份）範圍。
我嘗試僅更改年份（公式中的 2021 年）以引用另一個單元格，但 Excel 不再將其視為日期。
如何使我的電子表格全年可用？
答案1
方法一
不要在SUMIFS函數中硬編碼開始/結束日期，而是使用 Excel 公式產生它們，然後引用包含這些計算的日期值的儲存格。這DATE和EOMONTH可以使用函數，如下面的螢幕截圖所示。
您SUMIFS一月份的公式可以改為：
=SUMIFS(Amount;DPIF;">="&Sheet1!B3;DPIF;"<="&Sheet1!C3)
對於一月，單元格Sheet1!B3具有該月的開始日期和Sheet1!C3結束日期。將這些儲存格參考變更為您放置計算日期值的位置。對於 2 月，開始/結束日期單元格為Sheet1!B4和Sheet1!C4，對於 3 月為和Sheet1!B5，Sheet1!C5等等。SUMIFS的11 個月。
公式中的與號（& 符號）每個都將一個比較運算符與引用單元格中的值連接起來，因此在更改後的公式中">="&Sheet1!B3實際上等效於，">=01/01/2024"因為Sheet1!B3提供了01/01/2024 的日期值。
要更改年份，只需更改對應儲存格中的年份值 -Sheet1!B1螢幕截圖中的儲存格。您的 12 個月SUMIFS公式將全部自動引用新年的開始/結束日期，無需編輯公式。
此方法還管理閏年二月的月底日期（如儲存格中的螢幕截圖所示C4）。
方法2
另一種方法是透過使用以下命令向現有範圍DPIF添加額外範圍，將日期值轉換為月份數值：Amount/DPIFMONTH充當
=MONTH(date)
其中date代表範圍內的日期單元格DPIF。
呼叫這些每月值MPIF意味著您可以使用以下公式
=SUMIFS(Amount;MPIF;1)
=SUMIFS(Amount;MPIF;2)
…
=SUMIFS(Amount;MPIF;12)
根本不用擔心年份。
這種每月方法假設您的資料範圍中沒有多年。如果您這樣做，它將匯總所有一月、所有二月等的數據，這可能不是您想要的。
在這種情況下，解決方案的改進是YPIF使用以下方法將年份值新增至某個範圍內的資料：YEAR函數來取得這些值。然後，您可以為公式新增第二個條件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)
ETC
這涉及到一項一次性編輯工作，該工作將在2021 年、2022 年和2023 年有效，但將從2024 年2 月的月度總和中省略29/02/2024，除非有人記得進行更改，如果不更改，可能會在2025 年出現問題後退。
答案2
- 將兩個完整的日期（不僅僅是年份）分別放在單獨的儲存格中。然後在公式中，將日期值替換為對儲存格的引用
- 若要立即變更所有公式，請在工作表中執行“全部替換”
- 明年，僅更改兩個單獨單元格中的日期值