好吧，就上下文而言，這個連結讓我開始了，但我現在被困在嘗試將其應用到我的用例中： Excel - 基於與另一個表中的另一列相符的列求和值。
我有一個每小時日誌，用於輸入工作內容，包括要計費的日期、項目和任務信息，以及工作時間的開始和結束時間，該日誌計算從該時間起我工作了多少小時輸入（四捨五入到最接近的15 分鐘）。
以下是日誌（“timeLog”）範例資料的（大約）樣子：
| A | 乙 | C | D | 乙 | |
|---|---|---|---|---|---|
| 1 | 日期 | 專案與任務 | 開始時間 | 時間結束 | 小時 |
| 2 | 2023 年 1 月 1 日 | 項目1：任務1 | 8:00 AM | 10:00 AM | 2 |
| 3 | 2023 年 1 月 1 日 | 項目2：任務1 | 10:00 AM | 4:00 PM | 6 |
| 4 | 2023 年 1 月 6 日 | 假日營業時間 | 8:00 AM | 4:00 PM | 8 |
| 5 | 2023 年 1 月 7 日 | 病假時間 | 8:00 AM | 12:00 PM | 4 |
| 6 | 2023年2月6日 | 項目1：任務1 | 8:00 AM | 4:00 PM | 8 |
[轉錄自這個圖片.]
在另一張紙上，我有一個表格，列出了所有項目和關聯的任務（「項目和任務 ID」列）。每個項目都有一個關聯的類型（即工作時間、假期、病假或休假）。
項目資訊表（“projectTable”）如下所示：
| A | 乙 | |
|---|---|---|
| 1 | 項目和任務 ID | 類型 ID |
| 2 | 項目1：任務1 | 工作過 |
| 3 | 項目2：任務1 | 工作過 |
| 4 | 假日營業時間 | 假期 |
| 5 | 病假時間 | 生病的 |
[轉錄自這個圖片.]
為了計算我累積的病假/假期數量，我需要找出在給定的計費週期內我工作了多少小時。但是，我還需要將我提交的總工時（包括病假/休假/假日）保留在每小時日誌中，因為我將這些工時用於總體 FTE 計算。
以下是我目前用來匯總總計費週期 (12/26/22-1/25/23) 中與具有「已工作」類型 ID 的項目關聯的所有小時數的方法：
=SUM(IFERROR(IF(COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")>0,timeLog[Hours],0),0))
首先，我使用 FILTER 取得指定時間段內的所有表格條目。
FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0)
然後，COUNTIFS 檢查是否有任何項目屬於「已工作」TypeID：
COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")
然後，IF 傳回每一行的小時值，IFERROR “修復”我不斷收到的錯誤，SUM 計算該期間的總“工作”小時數。
然而，這感覺並不是最好的方法——它很脆弱，會返回我意想不到的錯誤，而且總的來說，我覺得我正在以錯誤的方式解決這個問題。有什麼更好的方法來做到這一點？
答案1
我沒有研究過你的解決方案，但是乍一看，我同意它似乎不必要地複雜。我建議您在每小時日誌中新增第六列，其中包含
=VLOOKUP(B2, projectTable!$A$2:$B$5, 2, FALSE)
這會將每小時日誌中的“項目和任務”轉換為項目資訊表中的“類型 ID”，如下所示：
| A | 乙 | C | D | 乙 | F | |
|---|---|---|---|---|---|---|
| 1 | 日期 | 專案與任務 | 開始時間 | 時間結束 | 小時 | 類型 ID |
| 2 | 2023 年 1 月 1 日 | 項目1：任務1 | 8:00 AM | 10:00 AM | 2 | 工作過 |
| 3 | 2023 年 1 月 1 日 | 項目2：任務1 | 10:00 AM | 4:00 PM | 6 | 工作過 |
| 4 | 2023 年 1 月 6 日 | 假日營業時間 | 8:00 AM | 4:00 PM | 8 | 假期 |
| 5 | 2023 年 1 月 7 日 | 病假時間 | 8:00 AM | 12:00 PM | 4 | 生病的 |
| 6 | 2023年2月6日 | 項目1：任務1 | 8:00 AM | 4:00 PM | 8 | 工作過 |
COUNTIFS然後就變成了使用和的簡單問題SUMIFS。例如在其他地方（例如A8:B10），輸入A8:A10中的類型，輸入
=SUMIFS($E$2:$E$6, $A$2:$A$6,">"&DATE(2022,12,25), $A$2:$A$6,"<"&DATE(2023,1,23), $F$2:$F$6,A8)
在 B8 中，然後向下拖曳/填充：
| A | 乙 | ||
|---|---|---|---|
| 8 | 工作過 | 8 | |
| 9 | 假期 | 8 | |
| 10 | 生病的 | 4 |
當然，硬編碼日期可以用單元格引用替換。
請注意，我回答了一個模糊相似但更複雜的問題，問題 八年前。