Управленческая задача
От поликлиники поступил запрос на помощь в расчёте количества посещений за 2025 год в разрезе филиалов. В «Автоматизированном рабочем месте экономиста» (АРМ экономиста), являющемся отчётной подсистемой МЭП «МедСофтЛаб», уже был предусмотрен готовый отчёт, формирующий количественные показатели работы организации и подразделений во многих аналитических срезах. В тоже время, в связи с большой частотой возникновения схожих задач, имеет смысл системно рассмотреть вопрос расчёта агрегатных показателей на основе детализированных данных. В целях демонстрации различных техник расчёта предлагается использовать отчёт «Реестр оказанных услуг», формирующий данные по услугам, оказанным пациентам за период.
Проблема состоит в том, что одна строка реестра соответствует услуге, а не посещению. Если пациент в один день в одном филиале получил несколько услуг, то в реестре будет несколько строк, но управленчески это может быть одно посещение.
В приведённом примере количество строк равно трём, количество оказанных услуг также равно трём, но количество посещений при выбранной методике равно одному. Поэтому прямой подсчёт строк приведёт к завышению показателя посещений.
Рабочее определение показателя
В рамках данного кейса посещение — это уникальная комбинация: пациент + филиал + календарная дата оказания услуги. Если в медицинской информационной системе есть номер визита, талона, обращения или случая, его следует рассмотреть, как более точный идентификатор посещения.
Учебные цели
Методический фокус
Главная задача - правильно определить единицу анализа, сформировать уникальный идентификатор этой единицы и агрегировать данные уже на нужном уровне детализации.
Общая логика решения
Ключевой принцип
Сначала определяется уникальное событие, затем событие маркируется числовым признаком, и только после этого данные агрегируются. Такой порядок защищает от типичной ошибки: суммирования строк услуг вместо подсчёта посещений.
Подготовка исходных данных
Перед добавлением формул необходимо привести исходную выгрузку к стабильной структуре. Рекомендуется использовать одну строку заголовков и не допускать пустых строк внутри диапазона данных. Для дальнейшей работы удобно преобразовать диапазон в таблицу Excel через команду «Вставка → Таблица». Это упростит фильтрацию, проверку пустых значений и расширение диапазона при обновлении данных.
Формирование технического ключа посещения
Технический ключ — это служебное поле, в котором несколько признаков объединяются в одну строку. В рассматриваемом кейсе ключ строится из ID пациента, филиала и даты оказания услуги.
=A2&"|"&C2&"|"&ТЕКСТ(D2;"ГГГГММДД")
Эта формула означает следующее:
Почему нужен разделитель
Без разделителя простое склеивание значений может дать неоднозначный ключ. Например, комбинации «12» + «34» и «1» + «234» визуально могут сливаться в похожие строки. Разделитель делает структуру ключа читаемой и устойчивой.
Если дата содержит время
Если в поле даты оказания услуги есть время, например 23.01.2025 09:15 и 23.01.2025 09:30, Excel будет воспринимать эти значения как разные. Это может привести к завышению количества посещений. В таком случае в формуле ключа нужно использовать только календарную дату.
=A2&"|"&C2&"|"&ТЕКСТ(ЦЕЛОЕ(D2);"ГГГГММДД")
Расчёт признака уникального посещения
После формирования ключа создаётся поле «Посещение». В нём единица ставится только для первого появления ключа, а повторные строки получают ноль. Именно сумма этого поля затем используется как количество посещений.
=ЕСЛИ(СЧЁТЕСЛИ($E$2:E2;E2)=1;1;0)
Почему используется нарастающий диапазон
Формула должна определить именно первое появление ключа. Поэтому для каждой строки проверяется не весь столбец, а диапазон от первой строки данных до текущей строки. Если в этом диапазоне ключ встретился один раз, значит текущая строка первая.
Короткий вариант той же формулы:
=--(СЧЁТЕСЛИ($E$2:E2;E2)=1)
Двойной минус преобразует логическое значение в число: ИСТИНА становится 1, ЛОЖЬ становится 0. Такой вариант короче, но для практического применения более понятна формула через ЕСЛИ.
Почему формулу с ДВССЫЛ лучше не использовать как основную
Возможно, Вы использовали альтернативную формулу для задачи через ДВССЫЛ:
=ЕСЛИ(СЧЁТЕСЛИ(ДВССЫЛ("E2:E"&СТРОКА());E2)=1;1;0)
Она может дать правильный результат, но методически не является лучшей для регулярной работы. Функция ДВССЫЛ относится к волатильным функциям: она чаще пересчитывается и может замедлять файл при большом количестве строк. Кроме того, формула сложнее читается.
Рекомендация
В качестве основной формулы используйте: =ЕСЛИ(СЧЁТЕСЛИ($E$2:E2;E2)=1;1;0). Она проще, быстрее и лучше объясняет логику первого появления ключа.
Построение сводной таблицы
После добавления полей «Ключ» и «Посещение» можно строить сводную таблицу. Важно: в область значений помещается не количество строк и не количество услуг, а сумма по полю «Посещение».
Формула результата
Количество посещений = сумма значений поля «Посещение», где 1 означает первое появление уникального ключа, а 0 — повторную строку того же посещения.
Контроль качества расчёта
Перед передачей результата руководству необходимо выполнить контрольные проверки. Это особенно важно, если расчёт используется для управленческой отчётности, планирования нагрузки, экономического анализа или обоснования показателей перед.
Альтернативные формулы Excel
Ниже приведены альтернативные формулы, которые решают ту же задачу.
- Без отдельного ключа: СЧЁТЕСЛИМН
=ЕСЛИ(СЧЁТЕСЛИМН($A$2:A2;A2;$C$2:C2;C2;$D$2:D2;D2)=1;1;0)
Формула проверяет первое появление комбинации сразу по трём полям: ID пациента, филиал и дата. Отдельное поле «Ключ» не требуется.
- Через СУММПРОИЗВ
=ЕСЛИ(СУММПРОИЗВ(($A$2:A2=A2)*($C$2:C2=C2)*($D$2:D2=D2))=1;1;0)
Формула сравнивает текущую строку с предыдущими строками по нескольким условиям. Каждое условие возвращает массив ИСТИНА/ЛОЖЬ, а умножение превращает их в набор единиц и нулей. Если сумма равна 1, комбинация встречается впервые.
- Через ПОИСКПОЗ по готовому ключу
=ЕСЛИ(ПОИСКПОЗ(E2;$E$2:$E$100000;0)=СТРОКА()-1;1;0)
Формула ищет позицию первого появления текущего ключа во всём диапазоне ключей. Если найденная позиция совпадает с номером текущей строки внутри диапазона, значит текущее появление является первым.
- Современный Excel: УНИК, ФИЛЬТР и СЧЁТЗ
В новых версиях Excel можно считать уникальные ключи без вспомогательного поля «Посещение». Например, если в колонке E уже создан ключ посещения, а в колонке C указан филиал, количество уникальных посещений по конкретному филиалу можно получить формулой:
=СЧЁТЗ(УНИК(ФИЛЬТР($E$2:$E$100000;$C$2:$C$100000=G2)))
Если нужно ограничить расчёт 2025 годом:
=СЧЁТЗ(УНИК(ФИЛЬТР($E$2:$E$100000;($C$2:$C$100000=G2)*(ГОД($D$2:$D$100000)=2025))))
Если нужно посчитать посещения по филиалу и месяцу, где H2 содержит дату начала месяца, например 01.01.2025:
=СЧЁТЗ(УНИК(ФИЛЬТР($E$2:$E$100000;($C$2:$C$100000=G2)*($D$2:$D$100000>=H2)*($D$2:$D$100000<=КОНМЕСЯЦА(H2;0)))))
- Через СУММЕСЛИМН после расчёта признака посещения
Если поле «Посещение» уже рассчитано, можно обойтись без сводной таблицы и получить итог по филиалу обычной формулой.
=СУММЕСЛИ($C$2:$C$100000;G2;$F$2:$F$100000)
Расчёт по филиалу за 2025 год:
=СУММЕСЛИМН($F$2:$F$100000;$C$2:$C$100000;G2;$D$2:$D$100000;">="&ДАТА(2025;1;1);$D$2:$D$100000;"<="&ДАТА(2025;12;31))
Расчёт по филиалу и месяцу, где H2 содержит дату начала месяца:
=СУММЕСЛИМН($F$2:$F$100000;$C$2:$C$100000;G2;$D$2:$D$100000;">="&H2;$D$2:$D$100000;"<="&КОНМЕСЯЦА(H2;0))
Рекомендуемая методика для поликлиники
Для рассматриваемой задачи рекомендуется использовать способ «Ключ + признак посещения + сводная таблица». Он лучше всего сочетает простоту, прозрачность и проверяемость. Экономист может визуально увидеть, какие строки были отнесены к одному посещению, а итоговая сводная таблица остаётся понятной для руководителя и проверяющего.
Рекомендуемый набор формул
Ключ: =A2&"|"&C2&"|"&ТЕКСТ(D2;"ГГГГММДД")
Признак посещения: =ЕСЛИ(СЧЁТЕСЛИ($E$2:E2;E2)=1;1;0)
Итог: сводная таблица с суммой по полю «Посещение».
Итоговый управленческий вывод
Рассмотренный кейс показывает универсальный принцип аналитики медицинской организации: исходный отчёт может не содержать нужный показатель в готовом виде, но может содержать данные, достаточные для его расчёта. Чтобы получить корректный результат, нужно определить единицу анализа, сформировать устойчивый ключ, отметить уникальные события и только затем выполнять агрегацию.
Скачать файл Excel с расчетом можно по ссылке: Материал для поста по экселю
Главная формула подхода
Детальные строки → уникальные события → агрегированный управленческий показатель.
Этот анализ — часть нашей большой работы.
Чтобы всегда иметь под рукой актуальную аналитику по экономике здравоохранения, цифровизации и эффективности медицинских организаций, присоединяйтесь к нашему профессиональному сообществу.
👉 Продолжение — в Telegram-канале «АРМ в белом»
Здесь мы регулярно публикуем:
- комментарии к последним нововведениям и отраслевым отчетам;
- кейсы и примеры из практики;
- анонсы наших собственных исследований.
Специальные вопросы, практические разборы и справочные материалы по теме предоставляются в рамках уникального практического курса.
Подписаться: https://t.me/medsoftlab_arm
👉 Полная информация о проектах и технологиях «МедСофтЛаб» — на официальном сайте: https://medsoftlab.ru/