Excel INDEX MATCH MATCH 與 SUMProduct 結合使用？

Question

我正在嘗試更新內部作業成本計算的提案/定價表。我們的專案負責人按月估算員工的工時，然後將費率應用於該估算，得出總價。並不是每項工作都會用到每一位員工。

員工/小時	1月/21	2月21日	3月21日
約翰史密斯	10	15	20
簡·無名氏	20	20	10

費率表位於另一個標籤中，並在 A 列中列出所有員工，並在 B 列之後列出他們每月的小時費率，並具有與估計工時相同的日期標題。費率表也可能包括早於或晚於小時選項卡上的月份的月份。

費率表	12月21日	1月/21	2月21日	3月21日
約翰史密斯	12 美元	12 美元	12 美元	13 美元
吉姆·多伊	13 美元	13 美元	13 美元	13 美元
簡·無名氏	12 美元	12 美元	13 美元	13 美元

過去，我在工時估計選項卡的頂部有一個工時表，然後在正下方有另一個表，該表使用 INDEX MATCH MATCH 按月查找員工的適當費率，然後將其乘以該員工的工時多於。它有效，但這意味著我必須按月顯示每個員工的總成本，而我實際上不需要這樣做。我希望在每個月列下有一行，其中包含該月的總成本，同時避免多次編寫公式

=(B2 * INDEXMATCHMATCH)+(B3 * INDEXMATCHMATCH)+(B4 * INDEXMATCHMATCH) 

[顯然這不是真正的公式，因為它會非常長]。
有沒有一種方法可以將 B 列中的小時數乘以該月這些員工的相關費率，然後將其匯總到一個單元格中？

員工/小時	1月/21	2月21日	3月21日
約翰史密斯	10	15	20
簡·無名氏	20	20	10
總成本	$360	$380	$390

Answer 1

我認為您會願意嘗試以下一種方法：

=SUM(XLOOKUP($G$2:$G3,$A$2:$A$4,INDEX($B$2:$E$4,,MATCH(H$1,$B$1:$E$1,0)))*H$2:H$3)

（我發現了一些方法，有些很有趣，但是它們比這個公式更長，寫出來，並且您似乎對這種INDEX/MATCH方法很滿意，因此它可能很容易使用和維護。）

因此，對於所寫的公式，我將「費率表」的資料表複製到 A1:E4，將「員工/工時」表複製到 G1:J3。公式中的尋址適用於這些範圍。您需要根據您的具體情況進行調整。

通讀到最後：不要因為我愚蠢地談論輸出表而感到不安。（我只在重新閱讀問題和答案時注意到您希望在“員工/小時”表的數據行下獲得結果。）上面的公式滿足了這個願望，因為我修復了生成正式輸出表時所做的引用，因此您可以根據需要貼上。

為了建立輸出表，我只使用了類似的公式=H1來填寫輸出表的左側和頂部標題（名稱和月份）。這些顯然是您所知道的，因此很容易根據您設定的任何計算的需要進行調整。同樣，我使用了沼澤標準=SUM(M2#)求和公式，其優點是不需要調整，因為它們引用第一行中的公式生成的整個 SPILL 數組。

對於我嘗試過的所有方法來說，真正的困難是我最終會得到一個包含以下內容的公式：二在其最終輸出中溢出數組，Excel 不會獎勵此類事情。因為它們各自有不同的用途，所以我無法將它們巧妙地整理成單個 SPILL 數組，從而讓您在構成輸出表成本區域的單元格塊的左上角單元格中擁有單個公式。在決定將 SPILL 陣列向下放置而不是橫向放置後，我已經準備好應對問題的“核心”，即每位員工的每月成本。

對於人們可能會說的“肉”，即任何給定工作中每位員工每月的美元成本，您可以使用公式。

首先是INDEX/MATCH：（現在是第一個月列）它將輸出表的月份與「匯率表」列進行匹配，並發現需要美元區域的第 2 列。對美元區域進行索引給出一列乘三行的輸出數組大小，對應於外部XLOOKUP()的一列乘三行查找數組。如果它們不匹配，你就會得到一個錯誤。然後XLOOKUP()尋找員工的姓名，為此您使用一個陣列（$L2#在本例中）來取得輸出表中列出的所有員工的輸出。根據需要，這些溢出向下。

接下來，將公式複製到輸出表的每一列，並填寫每月所有員工的成本並更新總和。

（順便說一句，您的範例輸出「Feb/21」的總和不正確，這就是公式產生 $440 的原因（15*$12=$180、20*$13=$260、$180+$260=$440 而不是 $380。）

因此，要進行修復，我只需選擇輸出表中的第一個公式，按F2-Edit，突出顯示並複製該公式，然後移至“員工/工時”表，工時資料後的第一個空行，然後輸入“=SUM(”，貼上我複製的公式，輸入“)”，然後按Enter。調整參考以符合「員工/工時」表中的資料後，儲存格具有為不需要的輸出表計算的資訊總和。複製儲存格並貼上到右側即可獲得您想要的輸出。

最後一個想法：這對於能夠在總成本單元中使用一個 SPILL 陣列沒有幫助。很難過。無論使用多少列，您仍然需要複製並貼上其中。