Me entregaron una gran hoja de trabajo con datos y me solicitaron resumir los datos de los análisis de sangre del paciente. Esto es para una infección en la que un paciente puede curarse y reinfectarse, y mi colega quiere un "resumen inteligente" para cada paciente.
Por ejemplo, digamos que tengo una hoja de cálculo como esta:
Pat ID Date Result
123 1-Feb Positive
123 1-Mar Negative
Nos gustaría un resumen, tal vez en una segunda hoja de trabajo, que simplemente indique
Pat ID Summary
123 Cured
El criterio para estar "curado" "alguna vez fue positivo, pero el resultado más reciente fue negativo".
Otros elementos del resumen serían "curados pero reinfectados"; "infección actual"; "siempre negativo"; esperemos que todo se explique por sí mismo. Supongo que la clave es el resultado más reciente y cómo se relaciona el patrón de resultados anteriores con este.
He intentado jugar con tablas dinámicas y declaraciones if anidadas complejas, pero parece que no puedo cubrir todo sin terminar en un gran lío.
Mis preguntas son: a) ¿Crees que esto se puede hacer sin recurrir a VBA? b) ¿Algún consejo sobre cómo abordar esto? Literalmente me estoy rascando la cabeza y no estoy seguro de por dónde empezar.
Respuesta1
Sí, se puede hacer. Puse sus datos más algunas otras muestras de prueba en una tabla llamada Pruebas.
Luego creé una tabla de Resultados con una serie de fórmulas para derivar el estado (actual) de cada ID de Pat:
Habiendo usado tablas, podría usar referencias estructuradas para hacer que las siguientes fórmulas sean más fáciles de leer.
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")
Las SUMPRODUCTfórmulas hacen el trabajo pesado y merecen un poco de explicación adicional.
SUMPRODUCTlos toma arraysy los prueba contra condiciones para devolver una serie de VERDADEROS y FALDOS. Al --comienzo de cada prueba de condición, convierte las matrices VERDADERO/FALSO en matrices de 1 y 0 que se pueden multiplicar de modo que si ambos elementos de la matriz son 1, el resultado es 1. Si alguno es 0, entonces1 x 0 = 0.
En J2 se están probando los ID de Pat, la fecha de la prueba = MAX(Test Date)para el ID de Pat, el resultado de los ID de Pat =MAX(Test Date)es "Negativo" Y el ID de Pat tiene al menos 1 resultado "Positivo". Si no hubo al menos 1 "Positivo", entonces no puedes "curarte".
N2 está envuelto en una fórmula IF porque accidentalmente obtuve una "Infección actual" falsa para Pat ID = 127.