Всем привет!

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

Очень часто сталкиваешься со следующей задачей. Есть таблица с искомыми данными. Необходимо эти данные добавить в другую таблицу в соответствии с уникальными ключами (в моем случае это ID из ПО Bimeister, SAP и т. д.). Для такой задачи используются функции ВПР, ИНДЕКС + ПОИСКПОЗ, и относительно новая функция ПРОСМОТРX. Работа этих функций, на мой взгляд, хорошо описана на сайте «Планета Excel». Однако для более целостного восприятия материала, считаю нужным немного рассказать о ней.

Функция ПРОСМОТРX

В моей работе до 30% рабочего времени приходится использовать либо эту функцию, либо её аналоги. Данная функция появилась в январе 2020 года. Соответственно, более ранние версии MS Excel не имеют её в своем арсенале.

Рассмотрим пример. Имеется «Таблица 1», в которую надо внести данные в столбец «Максимальный расход» из «Таблицы 2», на основании «ключа» - столбца «Системный номер ID».

В упрощенном виде данная функция имеет следующий синтаксис:

=ПРОСМОТР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 для составления простых таблиц, графиков без применения сложных и составных формул. Столкнувшись с описанными в статье задачами, я потратил довольно много времени на осмысление и отработку навыков работы со всеми этими инструментами. Сейчас же всё кажется предельно простым и очевидным. Если даже одному человеку эта статья принесёт пользу, я буду считать свою задачу выполненной.