☰

复杂的 excel 双 INDEX MATCH 公式，返回并使用唯一值摘要数组

tag-icon • tag-icon microsoft-excel worksheet-function
复杂的 excel 双 INDEX MATCH 公式，返回并使用唯一值摘要数组

我在 Excel 中有一个急救记录，我正在尝试自动执行每年进行的一些计算。

我已附加一个示例文件（没有标识符）来说明该问题。Dropbox链接在这里。

我正在尝试使用来自其他工作表的数据，在工作表“统计”上计算每位医务人员每年的总工作时间。

每位医生所诊治的病例都记录在“总结”工作表的“记录”表中。对于每个病例，都列出了年份、班次、医生姓名以及记录中是否有错误。

每个班次都有一个唯一的标识符，列在工作表“TimeIndex”中名为“ShiftValueT”的表中。

我正在尝试编写一个执行以下操作的公式：

  1. 识别符合多个条件的行 (Year、Medic、ISERROR=FALSE())
  2. 使用这些行来识别与之对应的 UniqueShift 值
  3. 根据 UniqueShift 列中对应的唯一值创建一个数组（即删除重复项）。
  4. 使用 ShiftValueT 表中的这些唯一值 (INDEX MATCH) 查找每个 UniqueShift 类型的相应小时数
  5. 对与已识别的 UniqueShifts 对应的所有小时数实例进行求和

我已经走到这一步了（作为数组公式）：

=SUM(INDEX(ShiftValueT[[Value]:[Value]],MATCH(INDEX(Record[[UniqueShift]:[UniqueShift]],MATCH(B$1&FALSE()&$A2,Record[[Year]:[Year]]&Record[[ISERROR]:[ISERROR]]&Record[[Medic]:[Medic]],0)),ShiftValueT[[UniqueShift]:[UniqueShift]],0)))

但这仅返回 UniqueShift 的第一个实例和 ShiftValueT 中的单个小时引用。

我如何修改公式来返回所有小时的总和？

注意：我不想使用 VBA。

答案1

首先，我建议使用数据透视表进行此类操作。如果数据透视表不是一种选择，那么解决这个问题的方法是

  1. 将‘ShiftValueT’表上的 PRE 和 POST 的‘-’更改为 0。
  2. 在“记录”表中添加一个名为“时间索引值”的列，并使用 Vlookup 获取每条记录对应的时间值。

=VLOOKUP([@UniqueShift],ShiftValueT,2,FALSE)

  1. 然后在统计选项卡上，您有 2 个选项，具体取决于您的 Excel 年份。对于 2016 年，您可以使用 sumproduct 公式（这是在单元格 Stats!B2 中）

=SUMPRODUCT((Record[Medic]=Stats!$A2)*(Record[Year]=Stats!B$1)*(Record[ISERROR]=FALSE)*Record[Time Index Value])

或者如果你有以前版本的 Excel（要获取 {}，请在退出单元格时按 Ctrl + Shift + Enter）

{=SUM(IF(Record[Medic]=Stats!$A2,IF(Record[Year]=Stats!B$1,IF(Record[ISERROR]=FALSE,Record[Time Index Value],0),0),0))}

注意：如果您确实需要不包含任何重复项，请在使用公式之前对“记录”表数据执行删除重复项。或者按照以下步骤操作

  1. 在“记录”表中添加另一列，名为“重复项”（这应该在 F 列中），并在 F2 中插入此公式。注意这需要 Excel 2016

=IF(COUNTIFS($A$2:$A3,$A3,$B$2:$B3,$B3,$B$2:$B3,$B3,$C$2:$C3,$C3,$D$2:$D3,$D3,$E$2:$E3,$E3) >1, "Duplicate row", "")

  1. 更新公式至

=SUMPRODUCT((Record[Medic]=Stats!$A2)*(Record[Year]=Stats!B$1)*(Record[ISERROR]=FALSE)*(Record[Duplicate]<>"Duplicate row")*Record[Time Index Value])

或者

{=SUM(IF(Record[Medic]=Stats!$A2,IF(Record[Year]=Stats!B$1,IF(Record[ISERROR]=FALSE,IF(Record[Duplicate]<>"Duplicate row",Record[Time Index Value],0),0),0),0))}

答案2

按照另一个答案的建议，添加辅助列确实可以简化问题。如果你能做到这一点，我建议你这样做。

但是公式中的列需要被锁定：

=SUMPRODUCT((Record[[Medic]:[Medic]]=Stats!$G2)*(Record[[Year]:[Year]]=Stats!H$1)*(Record[[ISERROR]:[ISERROR]]=FALSE)*Record[[Hours]:[Hours]])

正如您所见，我将新列命名为“小时”。

如果你不能添加辅助列，那么解决方案需要非常奇怪的解除引用INDEX() 公式，您可以在链接的问题中阅读更多相关内容。

=SUM(IFERROR(INDEX(ShiftValueT[Value],MATCH(INDEX(Record[UniqueShift],N(IF(1,AGGREGATE(15,6,ROW(A$1:A$350)/((Record[Year]=Stats!B$1)*(Record[Medic]=Stats!$A2)*(Record[ISERROR]=FALSE)),ROW(A$1:A$70))))),ShiftValueT[UniqueShift],0)),0))

奇怪的是，此公式中的列不必锁定。这两个公式都给出了相同的答案，如下面的已完成表格所示：

在此处输入图片描述

这两个公式都不能删除重复项。如果您需要，请发表评论。

我希望这个帮助能祝你好运。

相关内容