我有自動計算每個專案的損益表。目前他們使用的是固定工資，但我想改變它以納入未來的工資變化。專案持續時間是根據 E2 中的持續時間引數從 L1 排序的，使用=EDATE($C$2, SEQUENCE(1, $E$2, 0))
L 列中的每個單元格對持續時間進行排序，計算相關值。
工資成本公式參考了下面的資料集，該資料集也是從另一張表中的資料自動生成的，=FILTER(FILTER(AllStaffProjectAllocationTbl,AllStaffProjectAllocationTbl[Project Code Name]=B2), {1,0,0,1,1,1,0,0,0,1,0,0,0,1,0,0,0,0})并且可以具有可變數量的條目：
當前用于計算 L7 中的工資成本（來自第一張圖片）的公式是這里有人幫助我的，=MMULT(SEQUENCE(1,ROWS($A$18#),1,0),($L$1#>=OFFSET($A$18#,0,3,,1))*($L$1#<=OFFSET($A$18#,0,4,,1))*OFFSET($A$18#,0,5,,1))并使用了第二張圖片中的 F 列（我希望將其洗掉）。
我在 EmployeeSalaryTbl 表中有一組新的資料用于這里的薪水（我現在必須添加 31/12/9999 的編造結束日期，以確保下面的計算作業，理想情況下這將是空白的，我d 在以下計算中檢查）：
我已經開始修改 L7 中的 MMult 函式來執行以下操作：
- 專案持續時間的順序
- 檢查從 A18 起的員工是否在從 L1 開始的給定月份的專案中
- 如果是，則從 EmployeeSalaryTbl 中找到該日期范圍內的薪水，并將它們全部加在一起。
這是我到目前為止所擁有的，但不幸的是它給了我一個錯誤：
=MMULT(SEQUENCE(1,ROWS($A$18#),1,0),IF(AND(($L$1#>=OFFSET($A$18#,0,3,,1))*($L$1#<=OFFSET($A$18#,0,4,,1))),SUMIFS(EmployeeSalaryTbl[Salary Monthly], EmployeeSalaryTbl[Employee],$A$18#, EmployeeSalaryTbl[Salary Start Date],"<="&$L$1#, EmployeeSalaryTbl[Salary End Date],">="&$L$1#),0))
圖 2 定義的專案資料為：
| 員工 | 角色 | 紀律 | 開始日期 | 結束日期 | 月薪 |
|---|---|---|---|---|---|
| 鮑勃 | 高級程式員 | 編程 | 2020 年 12 月 1 日 | 06/05/2020 | 4,333 英鎊 |
| 戴夫 | 中級程式員 | 編程 | 2020 年 1 月 2 日 | 2020 年 5 月 30 日 | 3,167 英鎊 |
| 彼得 | 高級程式員 | 編程 | 2020 年 1 月 1 日 | 2020 年 1 月 31 日 | 4,583 英鎊 |
| 杰克 | 初級程式員 | 編程 | 2020 年 1 月 2 日 | 2020 年 6 月 30 日 | 2,083 英鎊 |
| 理查德 | 資深藝術家 | 藝術 | 2020 年 1 月 3 日 | 2020 年 4 月 30 日 | 3,750 英鎊 |
| 羅德尼 | 領導質量檢查 | 質量保證 | 2020 年 1 月 3 日 | 2020 年 6 月 30 日 | 4,333 英鎊 |
| 專案 1 - 雇用 1 | 資深制片人 | 生產 | 2020 年 1 月 2 日 | 2020 年 5 月 30 日 | 3,458 英鎊 |
| 羅杰 | 質量保證 | 質量保證 | 2020 年 1 月 1 日 | 2020 年 4 月 30 日 | 1,667 英鎊 |
| 衛斯理 | 中級程式員 | 編程 | 2020 年 1 月 2 日 | 2020 年 5 月 31 日 | 3,750 英鎊 |
| 雷切爾 | 資深藝術家 | 藝術 | 2020 年 1 月 1 日 | 2020 年 6 月 30 日 | 3,333 英鎊 |
| 專案 1 - 雇用 2 | 首席程式員 | 編程 | 2020 年 1 月 1 日 | 2020 年 7 月 31 日 | 4,417 英鎊 |
EmployeeSalaryTbl 中的資料是：
| 員工 | 工資開始日期 | 工資結束日期 | 薪水 | 月薪 | 每日工資 |
|---|---|---|---|---|---|
| 鮑勃 | 2020 年 1 月 1 日 | 2021 年 3 月 31 日 | 52,000 英鎊 | 4,333 英鎊 | 199 英鎊 |
| 鮑勃 | 2021 年 1 月 4 日 | 2022 年 3 月 31 日 | 55,000 英鎊 | 4,583 英鎊 | 211 英鎊 |
| 鮑勃 | 2022 年 1 月 4 日 | 9999 年 12 月 31 日 | 58,000 英鎊 | 4,833 英鎊 | 222 英鎊 |
| 戴夫 | 2020 年 1 月 1 日 | 2021 年 3 月 31 日 | 38,000 英鎊 | 3,167 英鎊 | 146 英鎊 |
| 戴夫 | 2021 年 1 月 4 日 | 9999 年 12 月 31 日 | 42,000 英鎊 | 3,500 英鎊 | 161 英鎊 |
| 衛斯理 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 | 45,000 英鎊 | 3,750 英鎊 | 173 英鎊 |
| 杰克 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 | 25,000 英鎊 | 2,083 英鎊 | 96 英鎊 |
| 理查德 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 | 45,000 英鎊 | 3,750 英鎊 | 173 英鎊 |
| 羅德尼 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 | 52,000 英鎊 | 4,333 英鎊 | 199 英鎊 |
| 專案 1 - 雇用 1 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 | 41,500 英鎊 | 3,458 英鎊 | 159 英鎊 |
| 羅杰 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 | 20,000 英鎊 | 1,667 英鎊 | 77 英鎊 |
| 史蒂夫 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 | 27,000 英鎊 | 2,250 英鎊 | 104 英鎊 |
| 雷切爾 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 | 40,000 英鎊 | 3,333 英鎊 | 153 英鎊 |
| 彼得 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 | 34,000 英鎊 | 2,833 英鎊 | 130 英鎊 |
| 莎拉 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 | 22,000 英鎊 | 1,833 英鎊 | 84 英鎊 |
| 克洛伊 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 | 33,000 英鎊 | 2,750 英鎊 | 127 英鎊 |
| 馬修 | 2020 年 1 月 1 日 | 2021 年 3 月 31 日 | 23,000 英鎊 | 1,917 英鎊 | 88 英鎊 |
| 馬修 | 2021 年 1 月 4 日 | 9999 年 12 月 31 日 | 28,000 英鎊 | 2,333 英鎊 | 107 英鎊 |
| 專案 1 - 雇用 2 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 | 36,000 英鎊 | 3,000 英鎊 | 138 英鎊 |
uj5u.com熱心網友回復：
使用您的輸入資料，并假設沒有 excel 版本限制（未在問題中指定），在單元格H2中輸入以下公式：
=LET(namePrj, TB_Prj[Employee], startPrj, TB_Prj[Start Date], endPrj,
TB_Prj[End Date],name, TB_Roster[Employee],start, TB_Roster[Salary Start Date],
end, TB_Roster[Salary End Date],salary, TB_Roster[Salary Monthly],
SOMs, H1:S1, EOMs, EOMONTH(SOM,0),
BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),
namesActive, FILTER(namePrj, (startPrj <= SOM) * (endPrj >= EOM)),
cost, FILTER(salary, (start <= SOM) * (IF(end > 0, end, EOM) >= EOM) *
(ISNUMBER(XMATCH(name,namesActive))),0), sum(cost)
)))
)
兩個Excel 表被定義為具有動態范圍：TB_Prj專案資訊和TB_Roster工資資訊。月份資訊水平生成如下H1：
=EDATE(DATE(2020,1,1), SEQUENCE(1, 12, 0))
它生成該月的第一天。在公式中表示為SOMs（月初）。同樣，我用它EOMs來表示每個相應的月末。
輸出生成一個1x12陣列作為結果。
這是輸出：
僅顯示部分工資表 ( TB_Roster)
備注：
請檢查結果，您的資料需要清理，例如薪水表中的彼得有兩個重疊日期間隔的條目。
如果您不能使用表格（例如輸入基于 SPILL 公式，如FILTER），則可以使用范圍。您可以定義以下兩個資料集范圍：prjSet，rosterSet，然后像這樣定義每個對應的列：INDEX(rng,,x)， wherex表示每個范圍上的對應列號 where rngisprjSet或rosterSet。例如name變數可以這樣定義：
namePrj, INDEX(prjSet,,1)
解釋
使用Excel 表格具有動態范圍，因此在添加其他資訊時不需要更改公式。我們使用LET函式使公式更易于維護，定義所需的輸入和中間結果。
BYCOL用于迭代所有SOMs值。對于每個月初 ( SOM)，我們執行以下操作：
從活動專案中查找 ，namesActive檢查專案的開始時間是否早于或等于，SOM專案的結束時間是否大于或等于EOM。如果專案表中沒有名稱滿足條件，則FILTER回傳錯誤 ( #CALC!)，因為 Excel 中不允許使用空陣列。如果要處理這種情況，則需要調整公式（使用第三個輸入引數FILTER和IF計算條件cost）或更好地清理資料。我不希望在給定的月份在公司層面沒有任何活躍的專案。
獲得姓名串列后，我們將轉到第二個 Excel 表 ( TB_Roster) 以查找基于start、end日期的有效薪水。
我們在 中處理空值end，假設如果沒有值（資料被格式化為日期，所以空意味著0）。以下IF條件確保它（在第二次FILTER呼叫中）：
(IF(end > 0, end, EOM) >= EOM)
因此，如果end陣列是正數（日期表示為從 開始的正整數0，即整數），則分配一個值，因此我們使用該值，否則 ( 0) 將其替換為EOM。當沒有結束日期時，條件始終為真（考慮所有end值）。IF與陣列一起作業，它評估陣列每個元素的條件并回傳每個元素的相應值。通過這樣做，我們將空值的情況固定為有效情況，因此無需使用虛構的 date。
與 ,相關的條件確保我們僅按上一次呼叫中的名稱進行XMATCH過濾（專案表中的有效名稱，即）。此過濾器 ( ) 的結果滿足所有條件并回傳所有匹配的月薪。nameFILTERnamesActivecost
如果根據過濾條件沒有匹配，我們回傳0（但可以使用另一個值，例如NA()）。為此，我們使用FILTER函式的第三個輸入引數。
最后，我們將所有符合條件的薪水相加。
獎勵：檢查區間一致性
基于對問題的回答：
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/524482.html
標籤：擅长excel公式