Разработка единой системы аналитики и интерактивного дашборда на основе Excel-файлов

Здравствуйте!

Мне нужен опытный специалист по Excel, Power Query, Power BI и построению аналитических систем для медицинских учреждений.

Я предоставлю 5 Excel-файлов, содержащих сведения о кассовых счетах, платежах, пациентах, оказанных услугах, амбулаторном и стационарном обслуживании, а также выполненных работах.

Главная цель проекта: объединить данные в единую аналитическую систему, в которой можно быстро находить любую информацию, применять несколько фильтров одновременно, анализировать суммы и получать детализированные отчёты вплоть до конкретного пациента, счёта или услуги.

1. Исходные файлы

1. 1_Kassa_API_hisobot.xlsx — данные кассового API: ID, UUID, дата создания, статус, сумма, кассир, пациент, прайс-лист и другие поля.

2. 2_Kassa_ambulator_yoyilma.xlsx — детализация счетов, пациенты, подразделения, врачи, услуги, суммы, скидки, оплаты, способы оплаты и возвраты.

3. 3_Ambulatoriyada_korsatilgan_xizmat_turlari.xlsx — оказанные амбулаторные услуги, пациенты, врачи, категории услуг, количество, стоимость и оплаченная сумма.

4. 4_Statsionar_korsatilgan_xizmat_turlari.xlsx — стационарные услуги, отделения, история болезни, диагнозы, лечащие врачи, исполнители, стоимость и оплата.

5. 5_Bajarilgan_ishlar_dalolatnomasi.xlsx — акт выполненных работ с большим количеством столбцов по различным медицинским услугам.

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

2. Единая база данных

Необходимо создать централизованную модель данных, которая объединит все пять файлов без потери информации.

Требования:

• Определить надёжные ключи связи между таблицами: ID, UUID, номер счёта, MedID пациента, дата, идентификатор услуги и другие подходящие поля.

• Не объединять записи только по ФИО, если существует более надёжный идентификатор.

• Предусмотреть связь между кассовыми счетами и детализацией услуг.

• Разделить данные о счетах, платежах, пациентах и оказанных услугах на логические таблицы.

• Исключить дублирование сумм при объединении таблиц с разным уровнем детализации.

• Сохранить исходные данные и обеспечить возможность проверки любой итоговой суммы по первоисточнику.

• Если прямое сопоставление невозможно, сформировать таблицу несопоставленных записей с объяснением причины.

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

3. Расширенная система фильтрации

Все основные фильтры должны работать совместно и взаимно влиять на результаты отчётов.

Необходимые фильтры:

По периоду

• Дата оплаты.

• Дата создания счёта.

• Дата оказания услуги.

• Дата перевода или проведения услуги.

• День, неделя, месяц, квартал, год.

• Произвольный диапазон дат.

По пациенту

• ФИО пациента.

• MedID.

• ID пациента.

• ПИНФЛ — только для уполномоченных пользователей.

• Номер истории болезни.

• Номер счёта.

• Место работы, должность, регион, район.

По медицинскому обслуживанию

• Тип обслуживания: амбулаторный, стационарный и другие доступные категории.

• Подразделение.

• Категория услуги.

• Группа услуги.

• Наименование услуги.

• Категория прайс-листа.

• Диагноз, если он присутствует в исходных данных.

По сотрудникам

• ФИО врача.

• Лечащий врач.

• Врач, направивший пациента.

• Исполнитель услуги.

• Специализация.

• Кассир.

По финансовым показателям

• Сумма счёта.

• Начисленная сумма.

• Сумма скидки.

• Оплаченная сумма.

• Неоплаченный остаток.

• Возвраты.

• Способ оплаты.

• Статус счёта или оплаты.

По дополнительным параметрам

• Статус обработки записи.

• Источник счёта.

• Страховая компания.

• Страна, область, город или район.

• Поиск по части текста и точное совпадение.

• Сортировка по сумме, дате, количеству и другим показателям.

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

4. Поиск пациента и просмотр его истории

Создать удобный механизм поиска пациента.

После ввода ФИО, MedID, ID или номера счёта пользователь должен видеть:

• Все найденные счета пациента.

• Даты создания счетов и проведения услуг.

• Тип обслуживания.

• Подразделение и врачей.

• Перечень оказанных услуг.

• Количество услуг и стоимость каждой.

• Сумму по счёту.

• Оплаченные суммы.

• Скидки и возвраты.

• Остаток задолженности, если он достоверно рассчитывается из исходных данных.

• Статус счёта и оплаты.

При выборе конкретного счёта необходимо открывать его детализацию с перечнем услуг, их стоимостью, количеством и платежами.

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

5. Финансовая аналитика

Создать систему контроля финансовых показателей:

• Общее количество счетов.

• Общее количество уникальных пациентов.

• Общая сумма счетов.

• Общая стоимость оказанных услуг.

• Фактически оплаченная сумма.

• Неоплаченная сумма.

• Сумма скидок.

• Сумма возвратов.

• Поступления по способам оплаты.

• Динамика начислений и оплат по дням и месяцам.

• Сравнение текущего и предыдущего периода.

• Анализ по подразделениям, врачам, категориям и видам услуг.

• Топ-20 услуг по количеству и сумме.

• Топ-20 врачей по количеству оказанных услуг и финансовым показателям.

Важно: начисленная сумма, сумма счёта, фактическая оплата и сумма выполненных услуг должны учитываться отдельно. Необходимо документировать правила расчёта показателей, чтобы исключить двойной подсчёт.

6. Контроль расхождений между файлами

Реализовать автоматическую сверку данных между источниками.

Система должна выявлять:

• Счета, присутствующие в кассовом файле, но отсутствующие в детализации услуг.

• Услуги, для которых не найден соответствующий счёт.

• Несовпадения сумм между кассовым счётом и детализацией.

• Повторяющиеся ID, UUID и номера счетов.

• Дублирующиеся записи об услугах.

• Неоплаченные или частично оплаченные счета, если это подтверждается данными.

• Несовпадения статусов оплаты.

• Пустые или некорректные идентификаторы.

• Записи, которые невозможно автоматически сопоставить.

Создать отдельный отчёт сверки с указанием источника, идентификатора записи, ожидаемой суммы, фактической суммы, разницы и причины расхождения, если её можно установить.

Не исправлять исходные сведения автоматически без согласованного правила.

7. Интерактивный dashboard

Создать профессиональный дашборд на русском языке с удобным интерфейсом.

Предусмотреть отдельные страницы:

1. Обзор — ключевые финансовые показатели, количество счетов и пациентов, динамика за период.

2. Касса и платежи — поступления, способы оплаты, возвраты, неоплаченные суммы и статусы.

3. Пациенты и счета — поиск пациента, список счетов и подробная история услуг.

4. Амбулаторные услуги — анализ услуг, врачей, подразделений и сумм.

5. Стационар — отделения, истории болезни, врачи, услуги и финансовые показатели.

6. Аналитика услуг — рейтинги услуг, групп и категорий.

7. Выполненные работы — данные акта выполненных работ и детализация по услугам.

8. Сверка данных — расхождения, дубликаты и несопоставленные записи.

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

8. Технические требования

Предложите оптимальную архитектуру решения с учётом объёма данных и ежемесячного обновления.

Предпочтительный вариант:

• Excel и Power Query — загрузка, очистка и преобразование исходных файлов.

• Power BI — модель данных, меры, интерактивные фильтры, визуализация и детализация.

• При необходимости — отдельная база данных для больших объёмов данных.

Обязательные требования:

• Обновление отчётов при добавлении новых ежемесячных Excel-файлов.

• Автоматическое распознавание новых данных по согласованной структуре.

• Отсутствие необходимости вручную переписывать формулы и связи каждый месяц.

• Приемлемая скорость работы при реальном объёме данных.

• Корректная обработка пустых значений, ошибок и повторяющихся записей.

• Единый формат дат и денежных значений.

• Защита персональных данных пациентов и ограничение доступа к чувствительным полям.

• Документирование связей между таблицами и логики расчётов.

9. Проверка и приёмка результата

Работа считается завершённой, если:

1. Все пять файлов загружаются в единую систему.

2. Ключевые связи между данными документированы и проверены.

3. При объединении не возникает необоснованного дублирования сумм.

4. Работают все согласованные фильтры и поиск по пациентам и счетам.

5. Итоговые суммы сверяются с исходными файлами.

6. Можно перейти от показателя dashboard к конкретным исходным записям.

7. После добавления нового месячного файла данные обновляются по согласованной процедуре.

8. Сформирован отчёт по найденным расхождениям.

9. Переданы исходный проект Power BI, запросы Power Query, необходимые меры DAX, инструкции и готовые файлы.

10. Проведена демонстрация работы системы на реальных данных.

10. Что прошу указать в ответе

Пожалуйста, ответьте на следующие вопросы:

1. Есть ли у вас практический опыт разработки Power BI для медицинских учреждений, бухгалтерии или кассовых систем?

2. Как вы будете связывать пять файлов, если их структура и детализация отличаются?

3. Как предотвратите двойной подсчёт сумм при объединении счетов и услуг?

4. Какие ключи связи вы планируете использовать?

5. Как будет реализован поиск пациента с просмотром всех его счетов и услуг?

6. Какие фильтры и страницы dashboard вы предлагаете?

7. Как организуете ежемесячное обновление данных?

8. Какой инструмент вы рекомендуете: Excel + Power Query, Power BI или отдельную базу данных? Почему?

9. Какова стоимость работы и срок выполнения?

10. Какие материалы, исходники и инструкции будут переданы после завершения проекта?

11. Есть ли примеры аналогичных проектов или демонстрационные работы?

Прошу сначала провести аудит структуры пяти файлов и предложить архитектуру решения. После согласования архитектуры можно приступать к разработке.

Приоритет проекта — достоверность финансовых показателей, полная фильтрация данных, удобный поиск и возможность проверить каждую сумму по первоисточнику.