☰

Обобщение закономерностей данных в Excel

tag-icon • tag-icon microsoft-excel worksheet-function vba
Обобщение закономерностей данных в Excel

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

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

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

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

Pat ID      Summary
123         Cured

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

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

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

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

решение1

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

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

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

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и проверяет их на соответствие условиям, чтобы вернуть массив ИСТИНЫ и ЛОЖИ. --В начале каждой проверки условия преобразует массивы ИСТИНА / ЛОЖЬ в массивы 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 "положительного", то вы не можете быть "вылечены".

N2 заключен в формулу IF, потому что я случайно получил ложное «Текущее инфицирование» для Pat ID = 127.

Связанный контент