Если вам приходится иметь дело с данными, которые выгружаются из сторонних программ, то наверняка вы сталкивались с необходимостью «вытащить» числовые данные из текста.
К сожалению, нет какого-то универсального и простого алгоритма решения подобных задач и в каждом конкретном случае нужно искать наиболее эффективный способ получения нужной информации.
Давайте на реальном примере разберем один из возможных вариантов решения.
Задача и алгоритм решения
Итак, была сделана выгрузка из какой-то системы и в одном из столбцов таблицы находится следующая информация.
Я взял лишь минимальный фрагмент данных, чтобы на его примере разработать решение задачи. В реальности данные могут занимать сотни и тысячи строк.
Задача состоит в том, чтобы «вытащить» значение комиссии в числовом формате, чтобы можно было далее работать с этой информацией.
Первое, что нужно сделать, это проанализировать содержимое ячеек, с которыми предстоит работать, и выявить какие-то общие черты. Обычно подобные данные формирует программа, а значит в них имеется некая система.
Вот и в данном случае мы видим, что фактически имеем дело с двумя шаблонами, то есть текст в ячейках один и тот же, а отличаются только цифры.
Нам нужно получить комиссии и эти значения находятся в одном случае после слова «Комиссия», а во втором - после слова «комиссии».
Это характерно для всех ячеек, поэтому уже можно предложить алгоритм - ищем слова «Комиссия» или «комиссии» и извлекаем последующие символы после них. В данном примере нас будут интересовать пять символов после этих слов - четыре цифры и разделитель.
Решение с помощью функций
Давайте подберем необходимые инструменты. Пока разберемся только с одним шаблоном, в котором нужное число идет после слова «Комиссия».
Во-первых, нам нужно определить позицию этого слова в тексте ячейки. Для этого воспользуемся функцией НАЙТИ. Она возвращает позицию первого символа искомого слова в указанном тексте.
Теперь необходимо извлечь пять символов, которые идут после этого слова. Поможет в этом функция ПСТР.
В качестве первого аргумента указываем ячейку с текстом, с которым мы работаем. Затем нужно указать позицию, начиная с которой мы будем извлекать символы. Пока укажем ячейку D1 ,в которой находится результат функции НАЙТИ. И последний аргумент - количество извлекаемых знаков. Нам нужны пять.
Нажимаем Enter и видим не совсем то, что нужно.
Я сделал это умышленно, чтобы было понятнее, какие именно изменения необходимо внести в формулу.
Итак, мы указали в качестве начальной позиции результат ранее задействованной функции НАЙТИ. Она вернула значение позиции слова «Комиссия» в тексте, а это позиция первого символа данного слова. Именно поэтому в результате мы получили пять первых буквы. Нам нужно сместиться на количество букв в слове «Комиссия», поэтому прибавим 8 к результату функции НАЙТИ.
Снова неполный результат.
Дело в том, что между словом «Комиссия» и значением есть пробел, который также является символом. Увеличим отступ до 9 и тогда значение будет извлечено верно.
Соберем формулы в одну и растянем ее на весь диапазон.
В результате мы получаем верные значения для первого шаблона, а для второго - ошибку ЗНАЧ.
Наличие ошибки подсказывает вариант решения - задействуем функцию ЕСЛИОШИБКА. Обернем ей ранее созданную формулу.
Функция имеет два аргумента - первый выводится если ошибки нет, а второй, если ошибка появляется. В нашем примере подставим точно такую же формулу, откорректировав ее - заменим слово «Комиссия» на «комиссии».
Снова протягиваем формулу по диапазону и получаем нужный результат.
Однако разделителем в этих значениях является точка, а для Excel разделителем целой и дробной части должна быть запятая.
Решить эту задачу поможет функция ПОДСТАВИТЬ. В ней указывается ячейка с текстом, а также символ, который нужно найти и символ, на который его нужно заменить.
Таким образом мы преобразовали значения в числовые, в чем легко убедиться, создав элементарную формулу, например, прибавив к любому значению комиссии единицу.
Безусловно, это упрощенное решение задачи - когда нужно извлечь определенное количество символов. В рассмотренном примере их было пять. В реальности комиссии могут быть более ста рублей и менее десяти и в такой ситуации нужно будет подбирать другой алгоритм решения. Однако рассмотренное решение отлично подойдет для получения номеров счетов, номеров заказов и другой информации, которая состоит из фиксированного количества символов.
Мгновенное заполнение
Ну и чтобы подчеркнуть, что выбор инструментов для решения всегда определяется конкретной задачей, хочу продемонстрировать вот такой вариант решения.
В данном случае текстовый шаблон везде одинаков и отличаются только числовые значения. Нам нужна комиссия, поэтому в соседней ячейке вручную введем ее значение. При этом будем использовать тот же разделитель (точку), что и в шаблоне.
Затем запустим мгновенное заполнение.
Excel сам «вытянет» нужные значения из столбца.
Останется лишь заменить точку на запятую, например, с помощью функции ПОДСТАВИТЬ.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
