Recebi uma grande planilha de dados com uma solicitação para resumir os dados dos exames de sangue dos pacientes. 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 declarasse
Pat ID Summary
123 Cured
O critério para “curado” ser “já foi positivo, mas o resultado mais recente foi negativo”.
Outros itens do resumo seriam “curados, mas reinfectados”; “infecção atual”; "sempre negativo" - espero que seja autoexplicativo. Suponho que o principal seja o resultado mais recente e como o padrão dos resultados anteriores se relaciona com ele.
Eu tentei brincar com tabelas dinâmicas e instruções if aninhadas complexas, mas não consigo cobrir tudo sem chegar a uma grande bagunça.
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
Responder1
Sim, isso pode ser feito. Coloquei seus dados e algumas outras amostras de teste em uma tabela chamada Testes.
Em seguida, criei uma tabela de resultados com uma série de fórmulas para derivar o status (atual) para cada ID de Pat:
Tendo usado tabelas, eu poderia usar referências estruturadas para facilitar a leitura das fórmulas a seguir.
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")
As SUMPRODUCTfórmulas fazem o trabalho pesado e merecem mais explicações.
SUMPRODUCTpega arrayse os testa em relação às condições para retornar uma matriz de TRUEs e FALSEs. O --teste no início de cada condição converte os arrays TRUE / FALSE em arrays de 1s e 0s que podem ser multiplicados de modo que ambos os elementos do array sejam 1s, o resultado seja 1. Se um deles for 0, então1 x 0 = 0.
Em J2 está testando Pat IDs, data de teste = MAX(Test Date)para Pat ID, o resultado para Pat IDs =MAX(Test Date)é "Negativo" E Pat ID tem pelo menos 1 resultado "Positivo". Se não houve pelo menos 1 “Positivo”, então você não pode ser “Curado”.
N2 está envolvido em uma fórmula IF porque acidentalmente recebi uma falsa "Infecção Atual" para Pat ID = 127.