Обобщение шаблонов данных в Excel

Question

Мне дали большой лист данных с просьбой обобщить данные анализа крови пациента. Это для инфекции, когда пациент может быть вылечен и повторно заражен, и мой коллега хочет "умное резюме" для каждого пациента.

Например, скажем, у меня есть такая таблица:

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

Мы хотели бы получить резюме, возможно, на втором листе, в котором просто говорится

Pat ID      Summary
123         Cured

Критерии "вылеченного" бытия «когда-то были положительными, но самый недавний результат отрицательным».

Другие итоговые пункты будут «вылечены, но повторно заражены»; "текущая инфекция"; "всегда отрицательный" - надеюсь, все само за себя. Я полагаю, что ключевой момент - это самый последний результат, и как шаблон предыдущих результатов связан с этим.

Я пытался играть с сводными таблицами и сложными вложенными операторами if, но, похоже, не могу охватить все, не попав в большую неразбериху.

Мои вопросы: а) Как вы думаете, это можно сделать без обращения к VBA? б) Любые советы о том, как подойти к этому - я буквально чесаю голову и не знаю, с чего начать

Answer 1

Да, это может быть сделано. Я поместил ваши данные и некоторые другие тестовые образцы в таблицу под названием «Тесты».

Затем я создал таблицу результатов с рядом формул для получения (текущего) статуса для каждого идентификатора патта:

Используя таблицы, я мог использовать структурированные ссылки, чтобы облегчить чтение следующих формул.

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 в массивы 1 и 0, которые можно умножить так, чтобы оба элемента массива были равны 1, результат равен 1. Если либо 0, то 1 x 0 = 0.

В J2 это тестирование идентификаторов патов, дата тестирования = MAX(Test Date) для идентификатора патта, результат для идентификаторов патов =MAX(Test Date) - "отрицательный", а идентификатор патча имеет как минимум 1 "положительный" результат. Если не было хотя бы 1 "Позитива", то вы не можете быть "вылечен".

N2 обернут в формулу IF, потому что я случайно получил ложную "Current Infection" для Pat ID = 127 .