Набор инженера НСИ для работы в EXCEL
Всем привет!
Хотел бы поделиться своим небольшим опытом работы в Excel при создании и наполнении базы данных оборудования и обработке большого количества данных разного формата. Данная статья поможет ускорить работу в Excel начинающим специалистам.
Очень часто сталкиваешься со следующей задачей. Есть таблица с искомыми данными. Необходимо эти данные добавить в другую таблицу в соответствии с уникальными ключами (в моем случае это ID из ПО Bimeister, SAP и т. д.). Для такой задачи используются функции ВПР, ИНДЕКС + ПОИСКПОЗ, и относительно новая функция ПРОСМОТРX. Работа этих функций, на мой взгляд, хорошо описана на сайте «Планета Excel». Однако для более целостного восприятия материала, считаю нужным немного рассказать о ней.
Функция ПРОСМОТРX.
В моей работе до 30% рабочего времени приходится использовать либо эту функцию, либо её аналоги. Данная функция появилась в январе 2020 года. Соответственно, более ранние версии MS Excel не имеют её в своем арсенале.
Рассмотрим пример. Имеется «Таблица 1», в которую надо внести данные в столбец «Максимальный расход» из «Таблицы 2», на основании «ключа» — столбца «Системный номер ID».
Таблица 1.
Таблица 2.
В упрощенном виде данная функция имеет следующий синтаксис:
=ПРОСМОТРX(искомое_значение; просматриваемый_массив; возвращаемый_массив)
«Искомое_значение» — значение из столбца «Системный номер ID» в текущей строке;
«Просматриваемый_массив» — Excel ищет значение и «запоминает» номер строки в этом столбце;
«Возвращаемый_массив» — Excel возвращает элемент этого столбца, под номером, который был найден в «просматриваемом массиве».
В ячейке D2 вводим следующую формулу:
=ПРОСМОТРX([@[Системный номер ID]];'[Таблица 2.xlsx]Лист1'!$A:$A;'[Таблица 2.xlsx]Лист1'!$B:$B)
Далее «протягиваем» формулу ПРОСМОТРX на весь столбец и получаем нужный результат.
Здесь стоит упомянуть один нюанс. Если вы выделяете не весь столбец с помощью символов $ (например, $B:$B), а конкретные ячейки в столбце, то возможна некорректная работа формулы из-за человеческого фактора.
Например, в «просматриваемом массиве» вы выделили ячейки вместе с заголовком, а в «возвращаемом массиве» без заголовка. В результате в вычисляемом столбце вы получите смещенные на одну ячейку значения.
Следует также отметить, что в нашем примере таблицы «отформатированы как таблица». Поэтому каждый столбец имеет название, указанное в формуле в квадратных скобках. А символ »@» перед названием столбца указывает на номер текущей строки в соответствующем столбце.
Чтобы получить такую таблицу, надо выделить область таблицы и на главной вкладке нажать «Форматировать как таблицу». Затем в раскрывшемся меню выбрать понравившийся вам вариант оформления.
Исправить формат данных столбца.
Однако бывает так, что часть числовых данных Excel «не видит», и формула соответственно не работает. Появляются сообщения об ошибках. Как решить эту проблему?
Рассмотрим пример. Имеется условная таблица:
Выделяем столбец с нужными числовыми значениями:
Заходим на вкладку «Данные», затем «Текст по столбцам».
Затем выбираем пункт «С разделителями» и нажимаем «Далее».
Выбираем символ-разделитель «знак табуляции», жмем «Далее».
На последнем этапе выбираем формат «Общий» и нажимаем «Готово».
После проведенных манипуляций значения имеют одинаковый формат и вышеперечисленные функции работают с этими данными без сбоев.
Найти символ в строке.
При обработке текстовых значений возникает задача заменить часть строки по определенному условию.
Рассмотрим пример. Имеется таблица с кодами оборудования. Необходимо заменить часть кода после шестого вхождения символа »-».
Функция НАЙТИ позволяет найти только первое вхождение символа. Для обхода этого ограничения используем функцию «ПОДСТАВИТЬ». Заменим шестое вхождение искомого символа на другой символ, которого нет в этой строке. Например, символ »@».
В соседнем столбце введем выражение:
«=ПОДСТАВИТЬ([@[Код оборудования]];"-";"@";6)», где 6 — это номер вхождения символа.
Дальше с помощью функции НАЙТИ, ДЛСТР и ЗАМЕНИТЬ можно заменить нужную часть строки. В столбец2 вводим следующую формулу:
«=ЗАМЕНИТЬ([@Столбец1];НАЙТИ("@";[@Столбец1];1);(ДЛСТР([@Столбец1])-НАЙТИ("@";[@Столбец1];1)+1);"-KIP")»
Таким образом мы получили нужный результат.
Найти количество вхождений символа в строку.
Имеется таблица. Необходимо посчитать количество вхождения символа »-» в строку.
Для решения этой задачи надо найти общее количество символов и вычесть из него количество символов этой строки без этого символа. С помощью функции ПОДСТАВИТЬ «удаляем» все вхождения этого символа.
Вводим формулу в соседний столбец:
=ДЛСТР([@[Код оборудования]])-ДЛСТР(ПОДСТАВИТЬ([@[Код оборудования]];"-";""))
Получаем количество вхождений символа в каждой из ячеек.
Сцепить строки.
Для соединения (конкатенации) текстовых значений разных ячеек используются функция СЦЕПИТЬ, СЦЕП или ОБЪЕДИНИТЬ. Я в своей практике пользуюсь следующим символом:»&».
Рассмотрим пример. Надо объединить два столбца, вставив между ними точку и пробел:
Формула с помощью функции СЦЕП:
=СЦЕП([@Имя];". ";[@Назначение])
Вариант c »&»:
=[@Имя]&". "&[@Назначение]
Результат, разумеется, будет одинаковый:
Найти кириллицу в названиях, написанных латиницей.
При обработке марок, маркировок оборудования в латинских названиях появляются буквы на кириллице. При обработке таких данных необходимо символы на кириллице найти и откорректировать. Для решения данной задачи я пользуюсь надстройкой «Ёxcel». Эта надстройка содержит много различных возможностей. Подробнее о ней можно почитать на сайте разработчика.
Рассмотрим пример. Имеется столбец с маркировкой оборудования по взрывозащите.
Выделяем столбец и переходим во вкладку «Ёxcel»
Далее переходим по пути «Редактировать» / «Кириллица и Латиница».
Далее выбираем «Выделить Кириллицу» и жмем галочку.
Видим выделенные красным нужные символы.
В случае необходимости заменить эти символы на латинские повторяем процедуру, описанную выше, но в меню выбираем «Заменить Кириллицу на Латиницу (x→x)».
Унификация названий марок оборудования.
Имеется база данных марок оборудования, в которую информацию вносили разные люди в разное время. Необходимо провести анализ и корректировку этих марок, исключить опечатки и дублирование.
Для решения этой задачи я использую Сводную таблицу.
Выделяем нужный столбец и на вкладке «Вставка» нажимаем «Сводная таблица». Затем выбираем «Из таблицы/диапазона».
Далее выбираем «Новый лист» и нажимаем «ОК»
В открывшемся новом листе в поле «Поля сводной таблицы» перетаскиваем название столбца «Марка» в поле «Строки» и в поле «Значения».
В полученной сводной таблице очень легко найти опечатки, дубли, а также легко можно увидеть разные варианты написания одной и той же марки и унифицировать их.
Например, найдя опечатку, переходим на лист с исходной таблицей и в фильтре данного столбца вводим поиск нужный текст. В итоге отобразится только искомое название. Корректируем его.
Для того, чтобы обновить значения в сводной таблице, нажимаем «Обновить все» на вкладке «Данные».
Заключение.
Очень надеюсь, данная статья окажется полезна тем, кто начинает работать с Excel, и немного упростит решение подобных задач. Работая инженером на различных предприятиях нашей страны, я многие годы использовал Excel для составления простых таблиц, графиков без применения сложных и составных формул. Столкнувшись с описанными в статье задачами, я потратил довольно много времени на осмысление и отработку навыков работы со всеми этими инструментами. Сейчас же всё кажется предельно простым и очевидным. Если даже одному человеку эта статья принесёт пользу, я буду считать свою задачу выполненной.
