☰

理解 Excel 中的時間/日期

tag-icon • tag-icon microsoft-excel google-spreadsheets
理解 Excel 中的時間/日期

我在 Excel 中有一個簡單的資料表，我需要一個公式來找出今天的總金額。因此，這將是 TODAY() 日期 00:00 到 23:59 之間的所有金額的總和。我怎樣才能做到這一點？

為了（可能）使事情稍微複雜化，我無法在來源電子表格中添加更多列（如下圖）。所有數學計算都在單獨的表格中完成。因此，我無法為每一行新增另一列來新增一些輔助編號/臨時步驟。理想情況下，這將是一個大公式，我可以將其插入另一張紙上的一個單元格中。

範例資料：

在此輸入影像描述

答案1

首先要弄清楚您的日期是文字還是由 Excel 格式化以顯示為日期的數字。這是一個非常重要的區別，您需要後者，因為它允許您使用更多內建公式的 Excel。一些可能的測試是

  • =ISTEXT(A2)
  • =IS編號(A2)
  • 將儲存格格式變更為常規，看看顯示的內容是否有變化

為了實現此解決方案，您的日期將被假定為數字。如果不是，有很多關於如何將文字日期轉換為數字/Excel 格式的問題/答案。

理解 Excel 中的時間/日期

Excel 將日期儲存為整數。它是自 1900 年 1 月 0 日以來的天數。因此，Excel 中的所有日期都只是整數，並應用了一些特殊格式，以我們習慣看到的某種方式顯示它們。另一個時間則以一天的百分比存儲，或者如果您喜歡一天的小數。 0.5代表半天或午餐。因此，如果您只需要從混合時間日期中查找日期，則只需去掉整數部分。相反，如果您只需要時間，則只需去掉小數部分即可。

選項 1 求和

SUMPRODUCT是執行類似數組計算的常規函數。因此，您希望將範圍引用限制為粗略的數據，而不是使用完整的列/行引用。如果你最終選擇了後者，你的機器可能會因為對空白單元格進行過多無用的計算而陷入困境。

=SUMPRODUCT((INT($A$2:$A$12)=TODAY())*$B$2:$B$12)

注意TODAY()是一個易失性函數。這意味著每次工作表上的某些內容發生變化時，即使它不影響函數，它也會重新計算。非揮發性函數（大多數常規函數）僅在發生影響它們的變更時重新計算。因此，如果將易失性函數複製到許多單元格，則每次更改/輸入工作表上的內容時，計算機都會陷入困境。

選項 2 - SUMIFS

SUMIFS是一個常規公式，您可以放心地使用完整的列引用，也是我處理這種情況的首選選項。公式採用以下形式：

SUMIFS(Range to be summed, range for condition 1, Condition 1 check, ..., range for condition n, Condition n check)

因此，在您的情況下，您想要檢查一天的開始和一天的結束，以便您的公式必須進行條件檢查，並且最終應該看起來像下面這樣。

=SUMIFS($B$2:$B$12,$A$2:$A$12,">="&TODAY(),$A$2:$A$12,"<"&TODAY()+1)

相關內容