блог об Excel и не только

Html

ДЛСТР(), НАЙТИ()

Комментариев нет :

Обычно для объяснения функций ДЛСТР и НАЙТИ приводят пример с получением второго или n-ного слова из текстовой строки. Но на деле довольно редко приходится сталкиваться с задачей в которой бы пригодилось умение извлекать второе по порядку слово (разве что из ФИО, но для этого у Excel уже имеется готовый и более удобный инструмент "Текст по столбцам").
Поэтому мы рассмотрим другой пример с использованием этих функций - возможно довольно сложный, но который точно может Вам пригодится при работе с текстовыми строками.
Итак дана выписка со счёту клиента за последние два года (скриншот №1). Необходимо получить из столбца "Е" номера платёжных поручений по которым поступали средства на счёт клиента или же переводились с этого счёта на другие счета.

           Так как средства на счёт клиента поступали с разных источников, соответственно и номера платёжных поручений не одинаковы; некоторые из них пятизначны, некоторые шестизначны, некоторые содержат дроби. Единственное что у них общее так это знак "№". От этого символа и будем отталкиваться.
Для начала узнаем позицию символа "№" в каждой текстовой строке. Для этого используем функцию НАЙТИ, синтаксис которой НАЙТИ(искомый_текст; просматриваемый_текст;[нач_позиция]). Как видите с помощью этой функции можно искать не только отдельный символ но и целое слово. Находим позицию символа "№" в каждой строке с помощью формулы =НАЙТИ("№";E2) (скриншот №2).



Теперь, зная позицию символа "№" в строке, мы можем извлечь ту часть строки, которая начинается с этого символа, используя функцию ПРАВСИМВ. Если Вы помните самый первый пост, синтаксис функции ПРАВСИМВ(текст;число_знаков). Для нахождения числа_знаков отнимем позицию символа "№" от общего количества символов строки: Длина строки - позиция символа "№"=число_знаков . Длина строки находится с помощью функции ДЛСТР(текст). В итоге у нас получается формула =ПРАВСИМВ(E2;ДЛСТР(E2)-НАЙТИ("№";E2)) (скриншот №3).


Окей, нам удалось вычленить часть текста начиная с символа "№". Но, нам нужен только номер платежного поручения а все остальные слова идущие после этого номера тоже лишние.
Посмотрим внимательно на текст в столбце "G". Как видите текст в каждой строке данного столбца начинается с номера платежного поручения а потом идёт пробел и уже другие слова. Следовательно, мы можем используя функцию ЛЕВСИМВ (текст; число_знаков) вычленить номер платежного поручения, если найдём позицию в тексте первого пробела. В итоге мы получаем номер платежного поручения, используя формулу =ЛЕВСИМВ(G2;НАЙТИ(" ";G2)) (скриншот №4).



Итак, мы смогли справиться с поставленной задачей и вычленить номера платежных поручений из каждой строки. Но, вместо одной формулы мы использовали две (в столбце "G" и столбце "H"), А ЭТО НЕ ЕСТЬ КРУТО. Как же мы можем вычленить номер платежного поручения используя всего лишь одну формулу?
Присмотримся к нашей последней формуле =ЛЕВСИМВ(G2;НАЙТИ(" ";G2)). Как видим в качестве текстового параметра она ссылается на значение ячейки "G2", которое само является результатом формулы =ПРАВСИМВ(E2;ДЛСТР(E2)-НАЙТИ("№";E2)). Следовательно, мы можем объединить эти две формулы в одну, заменив в последней формуле "G2" на ПРАВСИМВ(E2;ДЛСТР(E2)-НАЙТИ("№";E2). Сделав это, получаем комбинированную формулу =ЛЕВСИМВ(ПРАВСИМВ(E2;ДЛСТР(E2)-НАЙТИ("№";E2));НАЙТИ(" ";ПРАВСИМВ(E2;ДЛСТР(E2)-НАЙТИ("№";E2)))) (скриншот №5).


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

Комментариев нет :

Отправить комментарий