☰

Excelでデータパターンを要約する

tag-icon • tag-icon microsoft-excel worksheet-function vba
Excelでデータパターンを要約する

患者の血液検査データを要約するようにという依頼で、大量のデータ ワークシートを渡されました。これは、患者が治癒して再感染する可能性のある感染症に関するもので、同僚は各患者について「スマート サマリー」を求めています。

たとえば、次のようなスプレッドシートがあるとします。

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

2枚目のワークシートに、簡潔にまとめた要約を記載していただければと思います。

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.

= MAX(Test Date)J2 では、Pat ID、 Pat ID のテスト日、Pat ID の結果=MAX(Test Date)が「陰性」であり、Pat ID に少なくとも 1 つの「陽性」結果があることをテストしています。少なくとも 1 つの「陽性」結果がなかった場合は、「治癒」できません。

N2 は IF 式で囲まれています。これは、 Pat の「現在の感染」が誤って間違っていたためですID = 127。

関連情報