6.2 Работа с шаблоном, справочниками и словарями

6.2.1 Шаблон данных

Шаблон данных является базовым элементом для организации мониторинга АР и представляет файл формата .xlsx и включает себя следующие страницы:

«ИРК_1» – пример таблицы для ввода данных.
«Словари» – наборы справочных наименований (микроорганизмы и группы микроорганизмов, профиль отделения, локализация инфекций, клинический материал).
«Антибиотики справка» – наименования АМП с учетом метода определения чувствительности (суффиксы формата «_mic», «_dd», «_sir», подробнее см. раздел 4.4. Правила оформления и хранения данных.

Шаблон данных может быть скачан по ссылке https://monitoring.amrcloud.net/files/template.xlsx и адаптирован с учетом специфики конкретного учреждения.

6.2.2 Добавление новых строк в шаблон

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

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

6.2.3 Добавление новых значений в существующие словари/создание новых словарей

Допустим, в файл Шаблон_AMRcloud.xlsx потребовалось добавить новые значения во встроенные справочники. Например, в список отделений необходимо внести новое отделение – Нейрохирургия. Для этого необходимо сделать следующее:

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

На листе ИРК_1 выделить диапазон ячеек, в котором содержатся значения отделений. На панели инструментов во вкладке Данные, в разделе Работа с данными нажать на кнопку Проверка данных и выбрать пункт Проверка данных.

В открывшемся диалоговом окне Проверка вводимых значений выбрать Тип данных: Список, отметить галочку Список допустимых значений, и после клика в поле Источник выделить рамкой диапазон ячеек на листе Словари, в которых будут храниться значения подстановки. Нажать ОК.

Аналогичным образом можно создавать новые словари подстановки, добавлять новые столбцы со значениями на лист Словари и задавать Проверку данных для ячеек на листе ИРК_1.

6.2.4 Замена существующих значений

Допустим, в таблице имеется заполненный столбец с кодами отделений и его требуется перекодировать в полное наименование. Например, закодированы они как ХИР1, ХИР2, ХИР3, ХИР4. Необходимо их автоматически переименовать в «Общая хирургия», «Урология», «Гнойная хирургия», «Торакальная хирургия».

Для этого следует использовать встроенную в Excel функцию ВПР().

Синтаксис функции следующий:

ВПР(искомое_значение; таблица; номер_столбца; интервальный_ просмотр)

искомое значение – значение, которое необходимо заменить;
таблица – ссылка на диапазон ячеек со словарем, который будет использоваться при замене;
номер_столбца – номер столбца из таблицы, значения которого будут использоваться в качестве замены;
интервальный_просмотр – может принимать 2 значения: ИСТИНА (поиск значения ближайшего к критерию или совпадающее с ним) и ЛОЖЬ (поиск значения в точности совпадающее с критерием).

Общий порядок действий для осуществления такой замены следующий.

Создать словарь замен на новом листе.

На листе ИРК, рядом со столбцом с сокращенными названиями Отделение Сокр, добавить два новых пустых столбца – Отделение Полное 1 и Отделение Полное 2. В первый столбец вставить функцию =ВПР(I2; Замены!$A$2:$B$5;2;ЛОЖЬ), где:

I2 – ссылка на значение, которое предполагается заменить (в примере это столбец I, строка 2);
Замены!$A$2:$B$5 – ссылка на диапазон ячеек, в котором содержатся данные для замены;
2 – это номер столбца в диапазоне ячеек для замен, откуда будут браться полные значения;
ЛОЖЬ – мы сообщаем функции, что нужно использовать точное совпадение.

Для быстрой вставки вместо «перетягивания» формулы можно выделить диапазон ячеек для вставки, ввести формулу в первую ячейку и нажать Ctrl+Enter. Введенная формула автоматически распространится на все выделенные ячейки диапазона.

После растягивания функции на все ячейки скопировать результат и вставить в столбец Отделение Полное 2 в режиме Значения. Столбец (Отделение Полное 1) с функцией удалить.

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

6.2.5 «Разворачивание» таблицы

Возможна ситуация, когда ЛИС либо иная информационная система выдает отчет в формате, где информация по одному изоляту содержится более чем в одной строке. Например, результаты тестирования для каждого из изолятов написаны в строках, при этом идентификатор изолята повторяется.

Для приведения такой таблицы к формату, пригодному для загрузки в AMRcloud (где требуется, АМП находились в столбцах, а запись для каждого изолята была представлена единственной строкой) следует выполнить ряд действий.

Добавить новый столбец и пронумеровать все имеющиеся строки.

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

Excel предложит заполнить поля сводной таблицы, перетаскивая поля мышкой в соответствующие области. В качестве строк необходимо выбрать идентификатор изолята (RecordID), в качестве столбцов – АМП (Antibiotic), а для значений выбрать номера строк (Номер строки). Сводная таблица в Excel может работать только с числовыми значениями, поэтому и был дополнительно создан суррогатный столбец (Номер строки). Агрегирующая функция для значений не играет роли, так как все значения уникальны. После «разворота» таблица примет вид, показанный на рисунке ниже.

Скопировать таблицу без итогов и вставить как значения на новый лист.

Продублировать имена столбцов АМП для вставленной таблицы и добавить к ним коды («_sir», «_mic» или «_dd»), в представленном примере – _mic.

В новых столбцах с кодом «_mic» необходимо вставить значения МПК из исходного варанта таблицы (т.е. до разворота) с помощью функции ВПР(). Подробнее работа с функцией ВПР() описана выше. В качестве кодов выступает столбец Номера строк, в качестве замен – четвертый столбец ResultMICValue оригинальной таблицы.

В пустые значения необходимо вставить функцию для замены номеров строк на МПК =ЕСЛИ(ЕПУСТО(B2); «»;ВПР(B2; Лист1!$A$1:$D$189; 4; ЛОЖЬ))

В данной формуле проверяется, пустая ли ячейка, и если не пустая, то значение номера строки заменяется на МПК.
- B2 – ячейка, содержащая номер строки, который подлежит замене
- Лист1!$A$1:$D$189 – диапазон ячеек с данными исходной таблицы из пункта 1. Из этой таблицы мы и будем брать значения МПК для вставки вместо номеров ячеек
- 4 – для замены необходимо использовать четвертый столбец
- ЛОЖЬ – задается использование точного совпадения
Результат работы этой функции показан на рисунке ниже.

Скопировать полученные данные (столбцы с кодами АМП и значениями МПК) и вставить как значения на место столбцов с АМП и номерами строк. Ячейки с формулами удалить.

Изменено 15 января 2024