Пациенты, получившие лекарственные препараты

Отчетность, это железобетонный фундамент на котором стояли, стоят и будут стоять наши министерства и ведомства. Правда, давно уже возникает вопрос, почему они сами не обрабатывают данные, а требуют это от нижестоящих подчиненных организаций? Куда проще получи базу данных, и вытаскивай из неё все что тебе надо. Как говорится, всеобщая автоматизация и компьютеризация в действии. Тем более, что и зарплаты у них выше, значит можно привлечь высококлассных специалистов, и размещаются они в крупных городах где есть необходимые кадры, и нижестоящие организации смогут сосредоточиться на работе и не тратить время на бесконечную отчетность. Однако нет, надо представить уже обработанные данные по заданной форме и часто на бумажных носителях, а специалисты вышестоящих организаций, будут потом вручную забивать эти данные в свои документы. Вот и вся автоматизация.

Приведенный ниже пример подтверждает выше сказанное. Однако он интересен методом решения задачи.

Задача. Необходимо подсчитать, сколько пациентов получило по бесплатным рецептам в 2010 году лекарственные препараты, заданные в перечне вышестоящего министерства.

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

Пример базы исходных данных приведен в таблице на рис 1.

Рис. 1

Поле UKP - уникальный код пациента в системе (в строгом соответствии с Федеральным законом Российской Федерации от 27 июля 2006 г. N 152-ФЗ О персональных данных, данные обезличены). Поле SN_LR - серия и номер рецепта, поле MHH - международное непатентованное наименование лекарственного препарата. В таблице уже отобраны только те наименования лекарственных препаратов, которые заданы в перечне. Отбор выполнен с помощью функции ВПР.

Шаг 1. Через сводную таблицу сгруппируем МНН, чтобы получить список уникальных (т.е. неповторяющихся) значений, в таблице на рис. 2.

Рис. 2

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

Шаг 2. Надо сгруппировать отпуск препаратов по пациентам. Для этого делаем еще одну сводную таблицу, рис. 3.

Рис. 3

Теперь у нас в таблице отпущенные лекарственные препараты привязаны к конкретному пациенту, но как посчитать пациентов, которым был отпущен конкретный препарат из перечня в таблице на рис. 2?

Шаг 3. Для решения этой задачи используем функцию СЧЁТЕСЛИ, пример в таблице на рис. 4.

Рис. 4

В функции СЧЕТЕСЛИ($F$5:$F$26195$;A5) диапазон поиска значений $F$5:$F$26195$ задан абсолютным, что бы при копировании формулы диапазон поиска не менялся. Искомое значение (критерий поиска) A5 задан относительной ссылкой, что бы при копировании функции адрес ссылки изменялся. Т.о. Функция возвращает количество сгруппированных по пациентам наименований лекарственных препаратов. Т.к. для каждого пациента конкретный препарат встречается только один раз, то это и есть количество пациентов, которым был отпущен конкретный препарат.

Думаю, что есть и другие варианты решения задачи, но я сделал так.

Скачать архив с примером можно здесь.