☰

如何比較 Excel 序列日期而不轉換為 mm/dd/yy 類型日期？

tag-icon • tag-icon microsoft-excel worksheet-function date-time
如何比較 Excel 序列日期而不轉換為 mm/dd/yy 類型日期？

我有一個表，其中包含許多表示 Excel 序列日期的值。在多次嘗試比較欄位失敗後，我目前的方法是在序列日期而不是日曆日期之間進行比較。我正在嘗試用公式總結數據（按天）。

考慮：

41021       some data
41021.625   some data
41021.63542 some data
41022       some data
41022.26042 some data
41022.91667 some data
41023       some data
41023.375   some data

期望的結果：

41021       sum of 41021, 41021.625 and 41021.63542 data
41022       sum of 41022, 41022.26042 and 41022.91667 data
41023       sum of 41023 and 41023.375 data

本質上，對於 SerialDate.SerialTime 的所有實例，SUM 資料值與 SerialDate.* 關聯，無論該日期的 *.SerialTime 為何。

雖然我可以透過建立格式化的附加日期列來了解如何做到這一點，因為=TEXT(<DateField>,"mm/dd/yyyy")我正在尋找一個解決方案，該解決方案將允許我處理公式中的這種“轉換”，例如SUMIF((TEXT(<dateRange>,"yy/mm/dd"),=(TEXT(<dateField,"yy/mm/dd")),<dataRange>

合理？有人有主意嗎？

謝謝

答案1

假設你的序列時間在A列，關聯資料在B列，則下列公式

=IF(INT($A1)=$A1,SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

當輸入到C1並擴展到該列時，將顯示C列中第一個整數時間值後面的總和。如果這就是您所需要的，那麼您就完成了。

但是，如果您需要將所有總和一個接一個地放在另一個總和之間且中間沒有空行，則必須使用數組公式並為參考標籤插入一個額外的列。在C 列中，選擇包含資料的行中的儲存格（即，如果您的資料填入第1 至100 行，則選擇C1 至C100），然後在公式列中插入下列公式（而不是直接在儲存格中！

=IFERROR(SMALL(IF(INT($A1:$A100)=$A1:$A100,$A1:$A100,""),ROW()),"")

請注意，要使其正常工作，您需要將其儲存為陣列公式透過確認您的公式輸入Ctrl+Shift+Enter（不是Enter- 如果您做得正確，公式將顯示在大括號中）。您還必須根據需要調整範圍，因為公式對於空白儲存格傳回 0。最後，請注意，在大型資料集上，陣列公式變得相當 slo.ooo...o...o...w。

完成後，您可以添加

=IF($C1<>"",SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

到 D 欄，您會看到：

數組公式的實際應用

答案2

如果您嘗試按天匯總資料（或取得每日小計），可以嘗試此陣列公式。在公式欄中輸入此內容，然後按Ctrl+ Shift+ Enter.：

=SUM((INT(datetimes)=D3)*somedata)

在哪裡

  • datetimes包含您的序列日期，採用General或mm/dd/yyyy h:mm格式
  • somedata包含一些要添加的數字（在下面的範例中為 B2:B25）

在此輸入影像描述

Excel 將日期視為整數（序列值），將時間視為小數（24 小時的一小部分）。例如，06/01/2012 3:00:00 PM等於41061.625（如果將儲存格格式變更為 ，您將看到該內容General）。整數代表日期，而0.625代表3:00 pm或15:00或15/24。要提取 DATE 段，您可以使用INT(A1).

答案3

最簡單的方法是使用 Excel (2007/2010) 的內建表格和資料透視表功能。

  1. 根據您的資料建立 Excel 表格。
  2. 使用 Excel 的「INT」函數為日期建立一個輔助列，該函數會將所有值向下捲動到適當的日期（日期/時間欄位的整數部分）。您也可以輕鬆地為欄位的時間值建立輔助列。
  3. 使用資料表作為來源建立資料透視表。
  4. 將行標籤設定為輔助日期，並將樞軸值設為資料點（並且您可以新增時間欄位作為列標籤）。

然後，每當您更新表格時，您都可以重新整理資料透視表以取得目前小計。作為獎勵，您還可以選擇任何其他聚合函數（最小值、最大值、平均值），可以選擇以傳統日期格式設定資料透視表標籤的格式，而不會影響資料表值。

如果您使用的是 Excel 2003，則可以使用清單函數（而不是表格）完成相同的操作。

Excel 2010 範例

Excel 2010

範例 Excel 2003

Excel 2003

相關內容