☰

在 Excel 中汇总数据模式

tag-icon • tag-icon microsoft-excel worksheet-function vba
在 Excel 中汇总数据模式

我收到一份包含大量数据的工作表，要求汇总患者的血液测试数据。这是针对患者可能治愈并再次感染的感染，我的同事希望为每个患者提供一份“智能摘要”。

例如，假设我有一个这样的电子表格：

Pat ID      Date      Result
123         1-Feb     Positive
123         1-Mar     Negative

我们希望有一个摘要（可能在第二个工作表中），其中简单地说明

Pat ID      Summary
123         Cured

“治愈”的标准是“曾经呈阳性，但最近一次结果呈阴性”。

其他总结项目包括“治愈但再次感染”；“当前感染”；“始终呈阴性”——希望所有这些都不言自明。我认为关键是最近的结果以及以前结果的模式与此有何关联。

我曾尝试使用数据透视表和复杂的嵌套 if 语句，但似乎无法涵盖所有​​内容，否则会陷入一片混乱。

我的问题是：a) 您认为不借助 VBA 可以做到这一点吗？b) 关于如何解决此问题的任何提示 - 我真的绞尽脑汁，不知道从哪里开始

答案1

是的，可以。我将您的数据以及一些其他测试样本放入名为 Tests 的表中。 测试表

然后我创建了一个结果表，其中包含一系列公式来得出每个 Pat ID 的（当前）状态： 结果摘要

使用表格后，我可以使用结构化引用使以下公式更易于阅读。

F2: =COUNTIFS(Tests[Pat ID],[@[Pat ID]],Tests[Result],"Positive")

G2:  =COUNTIFS(Tests[Pat ID],[@[Pat ID]],Tests[Result],"Negative")

H2:  =SUM(Summary[@[Pos Count]:[Neg Count]])

I2:  =MAXIFS(Tests[Date],Tests[Pat ID],[@[Pat ID]])

J2:  =AND(SUMPRODUCT(--(Tests[Pat ID]=[@[Pat ID]]),--(Tests[Date]=[@[Last date]]),--(Tests[Result]="Negative"))>0,[@[Pos Count]]>0)

K2:  =AND(SUMPRODUCT(--(Tests[Pat ID]=[@[Pat ID]]),--(Tests[Date]=[@[Last date]]),--(Tests[Result]="Positive")),[@[Pos Count]]>0,[@[Neg Count]]>0)

L2:   =[@[Pos Count]]=[@Tests]

M2:  =[@[Neg Count]]=[@Tests]

N2:  =IF([@Tests]>0,INDEX(Summary[[#Headers],[Cured]:[Always negative]],MATCH(TRUE,Summary[@[Cured]:[Always negative]],0)),"No data")

这些SUMPRODUCT公式起着重要作用，值得进一步解释。

SUMPRODUCT接受arrays并根据条件测试它们以返回 TRUE 和 FALSE 的数组。--在每个条件测试的开始处将 TRUE / FALSE 数组转换为 1 和 0 的数组，这些数组可以相乘，这样两个数组元素都是 1，结果就是 1。如果其中一个为 0，则1 x 0 = 0.

在 J2 中，它测试患者 ID、= MAX(Test Date)患者 ID 的测试日期、患者 ID 的结果=MAX(Test Date)为“阴性”且患者 ID 至少有 1 个“阳性”结果。如果没有至少 1 个“阳性”，则您无法“治愈”。

N2 被包裹在 IF 公式中，因为我意外地为 Pat 得到了错误的“Current Infection” ID = 127。

相关内容