☰

總結 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。1 x 0 = 0.

在 J2 中，它正在測試 Pat ID，= MAX(Test Date)Pat ID 的測試日期，Pat ID 的結果=MAX(Test Date)為“陰性”，並且 Pat ID 至少有 1 個“陽性”結果。如果沒有至少 1 個“陽性”，那麼您就無法“治愈”。

N2 包含在 IF 公式中，因為我不小心為 Pat 得到了錯誤的“當前感染” ID = 127。

相關內容