나는 환자 혈액 검사 데이터를 요약해 달라는 요청과 함께 대규모 데이터 워크시트를 받았습니다. 이는 환자가 치료되고 재감염될 수 있는 감염에 대한 것이며, 동료는 각 환자에 대한 "스마트 요약"을 원합니다.
예를 들어 다음과 같은 스프레드시트가 있다고 가정해 보겠습니다.
Pat ID Date Result
123 1-Feb Positive
123 1-Mar Negative
우리는 아마도 두 번째 워크시트에 다음과 같은 요약을 원합니다.
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은 무거운 작업을 수행하고 약간의 추가 설명을 보장합니다.
SUMPRODUCTarraysTRUE 및 FALSE 배열을 반환하는 조건에 대해 이를 취하고 테스트합니다. 각 조건 테스트가 시작될 때 --TRUE / FALSE 배열을 1과 0의 배열로 변환합니다. 이는 두 배열 요소가 모두 1이 되도록 곱할 수 있으며 결과는 1입니다. 둘 중 하나가 0이면 다음과 같습니다.1 x 0 = 0.
J2에서는 Pat ID를 테스트하고 있으며 = MAX(Test Date)Pat ID에 대한 테스트 날짜, Pat ID에 대한 결과는 =MAX(Test Date)"음성"이고 Pat ID에는 최소 1개의 "양성" 결과가 있습니다. "양성"이 1개 이상 없으면 "치료"될 수 없습니다.
Pat 에 대해 실수로 잘못된 "현재 감염"이 발생했기 때문에 N2는 IF 수식으로 래핑됩니다 ID = 127.