Ich habe ein großes Arbeitsblatt mit Daten erhalten und die Bitte, die Bluttestdaten des Patienten zusammenzufassen. Dies betrifft eine Infektion, bei der ein Patient geheilt und erneut infiziert werden kann, und mein Kollege möchte für jeden Patienten eine „intelligente Zusammenfassung“.
Angenommen, ich habe eine Tabelle wie diese:
Pat ID Date Result
123 1-Feb Positive
123 1-Mar Negative
Wir möchten eine Zusammenfassung, vielleicht in einem zweiten Arbeitsblatt, die einfach nur besagt,
Pat ID Summary
123 Cured
Das Kriterium für „geheilt“ lautet „einmal positiv, aber letztes Ergebnis negativ“.
Andere zusammenfassende Elemente wären „geheilt, aber erneut infiziert“, „aktuelle Infektion“, „immer negativ“ – hoffentlich alles selbsterklärend. Ich nehme an, das Wichtigste ist das jüngste Ergebnis und wie das Muster früherer Ergebnisse damit zusammenhängt.
Ich habe versucht, mit Pivot-Tabellen und komplexen verschachtelten if-Anweisungen herumzuspielen, aber ich schaffe es scheinbar nicht, alles abzudecken, ohne ein großes Durcheinander zu verursachen.
Meine Fragen sind: a) Glauben Sie, dass dies ohne VBA möglich ist? b) Irgendwelche Tipps, wie ich das angehen soll? Ich zerbreche mir buchstäblich den Kopf und weiß nicht, wo ich anfangen soll.
Antwort1
Ja, das ist möglich. Ich habe Ihre Daten und einige andere Testbeispiele in eine Tabelle mit dem Namen „Tests“ eingefügt.
Anschließend habe ich eine Ergebnistabelle mit einer Reihe von Formeln erstellt, um den (aktuellen) Status für jede Patienten-ID abzuleiten:
Da ich bereits Tabellen verwendet habe, könnte ich strukturierte Referenzen verwenden, um die Lesbarkeit der folgenden Formeln zu verbessern.
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")
Die SUMPRODUCTFormeln erledigen die Schwerarbeit und bedürfen einer kleinen weiteren Erklärung.
SUMPRODUCTnimmt sie arraysund testet sie anhand von Bedingungen, um ein Array von TRUEs und FALSEs zurückzugeben. Der --Test zu Beginn jedes Bedingungstests konvertiert die TRUE / FALSE-Arrays in Arrays von 1en und 0en, die multipliziert werden können, sodass das Ergebnis 1 ist, wenn beide Array-Elemente 1en sind. Wenn eines von beiden 0 ist, dann1 x 0 = 0.
In J2 werden Pat-IDs getestet, das Testdatum = MAX(Test Date)für Pat-ID, das Ergebnis für Pat-IDs =MAX(Test Date)ist „negativ“ UND die Pat-ID hat mindestens 1 „positives“ Ergebnis. Wenn es nicht mindestens 1 „positives“ Ergebnis gab, können Sie nicht „geheilt“ sein.
N2 ist in eine WENN-Formel eingebettet, weil ich versehentlich eine falsche „Aktuelle Infektion“ für Pat erhalten habe ID = 127.