Resumindo padrões de dados no Excel

Question

Recebi uma grande planilha de dados com uma solicitação para resumir os dados de exames de sangue do paciente. Isto é para uma infecção em que um paciente pode ser curado e reinfectado, e meu colega quer um "resumo inteligente" para cada paciente.

Por exemplo, digamos que eu tenha uma planilha como esta:

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

Gostaríamos de um resumo, talvez em uma segunda planilha, que simplesmente declare

Pat ID      Summary
123         Cured

O critério para "ser curado" já foi positivo, mas o resultado mais recente foi negativo. "

Outros itens de resumo seriam "curados, mas reinfectados"; "infecção atual"; "sempre negativo" - espero que tudo seja auto-explicativo. Eu suponho que a chave é o resultado mais recente e como o padrão de resultados anteriores se relaciona com isso.

Eu tentei jogar com tabelas dinâmicas e declarações if aninhadas complexas, mas não consigo cobrir tudo sem chegar a uma grande confusão.

Minhas perguntas são: a) Você acha que isso pode ser feito sem recorrer ao VBA? b) Alguma dica sobre como abordar isso - estou literalmente coçando a cabeça e não sei por onde começar

    

Answer 1

Sim, isso pode ser feito. Eu coloquei seus dados mais algumas outras amostras de teste em uma tabela chamada Tests.

Eu,então,crieiumatabeladeresultadoscomumasériedefórmulasparaderivarostatus(atual)decadaidentificaçãodeidentificação:

Tendousadotabelas,eupoderiausarareferênciaestruturadaparafacilitaraleituradasseguintesfórmulas.

F2:=COUNTIFS(Tests[PatID],[@[PatID]],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")

As fórmulas SUMPRODUCT fazem o trabalho pesado e garantem um pouco mais de explicações.

SUMPRODUCT leva arrays e os testa em condições para retornar uma matriz de TRUEs e FALSEs. O -- no início de cada teste de condição converte os arrays TRUE / FALSE em arrays de 1s e 0s que podem ser multiplicados para que, de ambos os elementos do array, sejam 1s, o resultado é 1. Se um for 0, então 1 x 0 = 0.

Em J2, ele está testando IDs de amostra, Data de teste = MAX(Test Date) para ID Pat, Resultado para IDs de amostra =MAX(Test Date) é "Negativo" E Pat ID tem pelo menos 1 resultado "Positivo". Se não houver pelo menos 1 "Positivo", você não poderá ser "Curado".

O N2 está envolto em uma fórmula IF porque acidentalmente recebi uma "Infecção Atual" falsa para Pat ID = 127 .