Так как я уже больше 2-х лет работаю в регламентной службе Федерального реестра нормативно-справочной информации ЦНИИОИЗ, для меня Excel стал в определенный момент и блокнотом, и калькулятором, и частью жизни.
Одной из задач Регламентной службы является актуализация нормативно-справочной информации (НСИ), синхронизация и разработка объектов для использования в медицинских информационных системах медицинских организаций (МИС МО), региональных и федеральных сегментах Единой государственной информационной системы здравоохранения (ЕГИСЗ).
Очень просто говоря и очень грубо упрощая, делаем и заполняем таблички-справочники для того, чтобы врачи могли нормально работать в своих БАРС-ах, используя эти самые таблички.
Хотя некоторые задачи можно выполнить с помощью pandas-а, порой использовать Excel интуитивно проще и целесообразнее. А когда сделаешь себе определенный пул формул и функций, то и быстрее. Да и с Excel умеет обычно работать больше людей, чем писать код на Python.
Приступим.
Будем рассматривать примерно похожую на одну из практических задач, которую мне нужно было сделать.
Легенда следующая: нам отправили справочник МКБ-11, в котором помимо рубрик и подрубрик также присутствуют их синонимы. Нам необходимо подготовить его к публикации и добавить коды МКБ-10 там, где есть коды МКБ-11.
Из изображения видно, что здесь и лишние пробелы, и пустые строки, и разные буквы в начале названия. Надо исправлять.
1. Удаление лишних строк с помощью фильтра
Можно это сделать простым выделением и нажатием комбинации Ctrl+ «-», при котором удаляется целиком строка. А если этих строк много?
В данном случае, на выручку приходит обычный фильтр, где для каждого столбца мы выбираем пустые строки и удаляем все то, что получилось в результате с помощью комбинации Shift+Ctrl+ «Стрелочка направо» и повторяем то же с помощью «Стрелочки вниз» (это универсальный способ для любых объемов).
2. СЖПРОБЕЛЫ()
Функция удаляет из текста все пробелы, за исключением одиночных пробелов между словами. Особенно полезно, когда работаешь с данными из Word-овских файлов, которые порой в этом плане довольно «грязные». Это функция должна быть первая в работе с ними. Это как помыть руки после того как пришел с улицы. Быстро и несложно, зато какая профилактика возможных проблем. Пример использования можно увидеть ниже.
Сразу видно, что в таблице есть ячейки, в которых присутствуют лишние пробелы. Невооруженным взглядом мы можем увидеть только те, которые находятся между словами. А что насчет тех, которые находятся в конце значения ячейки? Да, мы их не увидим просто так, если только не будем проверять каждую ячейку на их наличие.
3. ИНДЕКС(ПОИСКПОЗ())
Одна из знаменитых комбинаций, о которой я узнал после ручной работы на протяжении нескольких часов по выставлению нужных значений в нужные места.
Функции Excel ИНДЕКС и ПОИСКПОЗ представляют собой комбинацию, которая является более гибкой альтернативой функции ВПР. ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца, а ПОИСКПОЗ ищет значение в диапазоне и возвращает его относительное положение.
Как это выглядит на практике:
Для начала скопируем соответствующие кодам МКБ-11 коды рубрик МКБ-10 из справочника из браузера МКБ-11 (https://icd.who.int/browse11/l-m/en).
4. Сделать первую букву в ячейке заглавной
Из изначального вида файла видно, что не во всех ячейках, а особенно в синонимах, текст начинается с прописной буквы. Нужно это исправить.
Для этого мы будем применять комбинацию ПРОПИСН(ЛЕВСИМВ())&ПРАВСИМВ(ДЛСТР()). Ага, страшная.
На самом деле не все так страшно, если разобрать ее по частям. В первой части формулы мы выбираем только первую букву значения нужной нам ячейки, во второй - мы выбираем все значение ячейки с правого конца, где количество символов в ней находим с помощью функции ДЛСТР(), но(!) из этого числа вычитаем 1. Это нужно нам, чтобы мы не учитывали старую первую букву.
Объединяем эти две части с помощью амперсанда (&). Теперь мы имеем прописную первую букву и неизмененный остальной текст.
Заключение
Отверткой, при большом желании и огромном упорстве, можно забить саморез в стену, как гвоздь молотком. Но зачем? Можно же его вкрутить.
Так и здесь. Excel - это действительно полезный инструмент, который позволяет быстро работать с таблицами от мала до велика (касается и таблиц и пользователей). Но, как и с любым другим инструментом, лучше знать как им пользоваться, чтобы повысить эффективность своей работы.
Здесь я рассмотрел несколько «лайфхаков», которые помогли и помогают мне в работе и могут помочь вам эффективнее работать с Excel.
Надеюсь, что прочтение данного туториала было полезно ;)