我正在尝试更新内部作业成本的提案/定价表。我们的项目负责人按月估算每个员工的工作时间，然后将费率应用于该估算以得出总价。并非每项工作都会使用每个员工。
| 员工/小时 | 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 |
答案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 数组。
我尝试过的所有方法的真正困难是，我最终得到的公式二SPILL 数组在其最终输出中，而 Excel 不会奖励此类事情。因为它们各自有不同的用途，我无法将它们巧妙地整合成一个 SPILL 数组，这样您就可以在构成输出表成本区域的单元格块的左上角单元格中拥有一个公式。在决定向下而不是横向执行 SPILL 数组后，我已准备好解决该问题的“核心”，即每位员工的每月成本。
对于人们所说的“肉”，即任何特定工作中每个员工每月的美元成本，您可以使用公式。
首先INDEX/MATCH：（目前是第一个月份列）它将输出表的月份与“费率表”列进行匹配，并发现需要美元区域的第 2 列。对美元区域进行索引将得到一个大小为一列乘三行的输出数组，这对应于外部XLOOKUP()的一列乘三行查找数组。如果它们不匹配，您将收到错误。然后，查找XLOOKUP()员工的姓名，为此您使用一个数组（$L2#在本例中）来获取输出表中列出的所有员工的输出。这些向下溢出，如所愿。
接下来，将公式复制到输出表的每一列，并填写所有员工每月的费用并更新总数。
（顺便说一下，您的示例输出中“2 月 21 日”的总和不正确，这就是为什么公式会生成 440 美元（15*$12=$180、20*$13=$260、$180+$260=$440 而不是 380 美元。）
因此，为了进行修复，我只需要选择输出表中的第一个公式，按F2-Edit，突出显示并复制公式，然后移动到“员工/小时”表，小时数据后的第一个空行，并键入“=SUM(”，粘贴我复制的公式，键入“)”，然后按 Enter。调整引用以匹配“员工/小时”表中的数据后，单元格中就有了为不需要的输出表计算的信息总和。复制单元格并粘贴到右侧即可得到您想要的输出。
最后的想法：这无助于在总成本单元中使用一个 SPILL 数组。很遗憾。无论使用多少列，您仍然需要复制和粘贴。