Функция ПСТР для разделения текста на части в Excel — пример использования

ПСТР – довольно популярная функция, которая используется как профессиональными пользователями Excel, так и начинающими. Но также вторая категория людей может не понимать, в каких случаях уместно ее применять. А назначение у этой функции очень простое – она помогает вытащить маленькую подстроку из одной большой. Но при этом возможностей, которые она предоставляет, значительно больше. В частности, она применяется для разделения строк. Давайте рассмотрим эту волшебную функцию Excel более подробно.

Функция ПСТР (MID) — подробное описание

Итак, мы поняли, что ПСТР – это функция. которая используется для того, чтобы достать из одного фрагмента текста какой-то определенный. Но она несколько отличается от функции «Найти и заменить», которая может реализовываться как через меню Excel, так и посредством формул. Она возвращает строку, начинающуюся с определенного символа.

Синтаксис предельно прост:

=ПСТР(текст; начальная_позиция; число_знаков)

Давайте более подробно поймем, какие аргументы используются в этой функции:

1. Текст. Содержимым ячейки могут выступать значения в разных форматах как ссылки, так и текстовая строка. Впрочем, может использоваться и ячейка любого другого формата. Например, можно таким способом достать месяц из даты. Для этого нужно в поле «Текст» указать ссылку на ячейку с датой. Важно убедиться, что она была предварительно конвертирована в текстовый формат.
2. Первоначальная позиция. Это числовое значение того символа, с какого начинается извлечение подстроки.
3. Число знаков. Это количество знаков, которые необходимо достать из строки. Если же нужно извлечь дату, то она всегда имеет размер в 10 символов. 

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

=ПСТР(A1;17;10)

Функция ПСТРБ (новая функция)

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

В чем разница между ПСТР и ПСТРБ?

Появляется вопрос: а в чем принципиальная разница? Дело в том, что некоторые символы считаются двухбайтовыми, а ряд из них – однобайтные. В случае с функцией ПСТРБ те знаки, которые занимают два байта в памяти, считаются всегда как 2. Прежде всего, двухбайтовыми являются японские, китайские и корейские символы.

Функция ПСТР на английском

Если человек пользуется англоязычной версией Excel, ему необходимо знать, как записывается эта функция на английском языке. За то, чтобы вытащить определенное количество знаков из строки, отвечает функция MID. Соответственно, если необходимо ориентироваться на количество байтов, то используется формула с MIDB.

Функция ПСТР и VBA в Excel

С помощью языка программирования VBA можно писать подпрограммы, которые выполняют ряд действий автоматически. В этой среде также есть альтернатива функции ПСТР. Давайте приведем пример. 

Range(«A2»).Value = Mid(Range(«A1»), 17, 10)

Распространенные ошибки в использовании функции ПСТР

При использовании функции ПСТР возможны ошибки, если неправильно соблюдать синтаксис или нарушить некоторые основополагающие правила:

1. Нельзя писать в качестве аргумента этой функции отрицательное значение. В таком случае будет выдана ошибка #ЗНАЧ.
2. Важно следить, чтобы начальная позиция не была большим числом, чем длина искомой строки. В ином случае будет выдано пустое значение.

Как вырезать часть текста ячейки в Эксель

А теперь давайте начнем обзор практических примеров, как возможно применение функции ПСТР.

Допустим, у нас есть электронная таблица, где содержится информация о том, какие товары продаются, а конкретно: марка и название определенной продукции. И перед нами стоит задача разделить наименование и марку и разместить эту информацию в подходящих колонках.

Сама таблица выглядит следующим образом.

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

=ПСТР(A2;1;НАЙТИ(» «;A2))

В нашем примере функция НАЙТИ используется для того, чтобы определить порядковый номер позиции, где есть только один символ пробела. В качестве аргумента функции найти, соответственно, пишем пробел, заключенный в кавычки. 

После этого получаем такой результат.

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

=ПСТР(A2:A8;НАЙТИ(» «;A2:A8)+1;100)

Здесь мы снова видим, что использовали функцию НАЙТИ, с помощью какой в этом примере ищем первоначальную позицию, содержащую пробел. Также мы добавили единицу к содержимому аргумента, чтобы перенести взор программы на первый символ марки товара. Чтобы упростить задачу поиска последнего символа мы просто решили написать число 100, которое гарантированно превышает длину строки. 

Так можете делать и вы.

После того, как программа выполнит все необходимые расчеты, получаем следующую таблицу.

Как рассчитать возраст по дате рождения в Эксель

Теперь приведем еще один пример, с каким сотрудники, часто использующие Эксель, сталкиваются довольно часто. Перед нами есть база данных, содержащая три колонки: Фамилия, имя, отчество, а также дата рождения. И перед нами стоит задача определить, сколько лет человеку в данный момент, с использованием ПСТР.

Вот так выглядит таблица, с которой мы будем работать.

Чтобы в этом случае вернуть строку с фамилией и возрастом, необходимо осуществить запись в строку формул следующего текста (в соответствующих местах аргументы функций нужно заменить на свои).

=ПСТР(A2;1;НАЙТИ(» «;A2))&» — «&РАЗНДАТ(B2;СЕГОДНЯ();»Y»)

Эта функция сначала определяет ту часть строчки, которая содержится до символа пробела. Это делается с помощью функции НАЙТИ аналогично приведенным выше примерам. Далее с использованием функции РАЗНДАТ мы определяем количество полных лет. В нашем случае мы автоматически отсекаем тот участок дней, который остался до следующего дня рождения в этом году. Именно поэтому данная функция сможет указать всегда правильный возраст. 

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

Несколько особенностей использования ПСТР в Excel

А теперь давайте немного пройдемся по теории после того, как была наглядно продемонстрирована работа функции. Итак, мы знаем, что функция ПСТР имеет следующий синтаксис:

=ПСТР(текст;начальная_позиция;число_знаков)

И еще раз, давайте рассмотрим каждый аргумент уже более глубоко и опишем некоторые особенности сквозь призму этой функции.

1. Текст. Это обязательный аргумент, который нужно передавать функции. Представляет собой или ссылку на ячейку, или непосредственно строку, из которой нужно извлекать требуемую информацию. Важно обратить внимание, что в последнем случае ее нужно облачать в кавычки. Независимо от формата строки, из нее будет доставаться определенная информация. Какая именно – задается следующими аргументами. 
2. Начальная позиция. Этот аргумент также обязательный. Его задача – задать стартовую точку отсчета. Являет собой обязательно целое число, которое относится к положительным числам. То бишь, минимальное значение – 1. Если оказывается, что в аргумент была передана дробная часть, она отсекается.
3. Число знаков. И этот аргумент является обязательным. Таким образом, все аргументы, используемые в этой функции, необходимо использовать. Здесь есть такой нюанс. Если оказывается, что было в него передано число, которое больше длины строки, то возвращается вся строка. 

Также важно понимать, что для функции ПТСРБ используется очень похожий синтаксис за тем лишь исключением, что вместо количества знаков после стартовой позиции задается количество байтов, начиная с этой точки.

Напоминаем, что синтаксис следующий:

=ПСТРБ(текст;начальная_позиция;число_байтов)

Как говорится, повторение – мать учения. Поэтому давайте подведем небольшие промежуточные итоги:

1. Если на месте стартовой позиции задавать значение, которое по размеру больше исходной строки, то после всех операций, выполняемых функцией ПСТР вернется пустое значение.
2. Если применять единицу в качестве исходной позиции, а количество знаков указать такое, которое будет больше строки или равняться ей, то в качестве итога будет выведено все содержимое этой строки. Таким образом, можно использовать эту функцию в роли альтернативы, пусть и не такой удобной, ссылки на ячейку. В Excel ситуации бывают разные, поэтому иногда приходится выкручиваться из любой ситуации, в том числе, и такими причудливыми способами. 
3. Будет возвращена ошибка #ЗНАЧ!, если использовать отрицательное значение в качестве начальной позиции. То же касается ситуации, если аргумент с числом знаков задается отрицательным значением. Важно запомнить навсегда, использовать нулевое или отрицательное значение в этом аргументе нельзя. 

Примеры использования функции ПСТР

А теперь давайте приведем один пример, как можно использовать функцию ПСТР на практике. Для начала нужно понять, что количество байтов, которые занимает один символ, зависит от языков. Если кодировка в языке однобайтовая, то и один символ занимает ровно один байт. В таком случае нет разницы, какую формулу использовать: ПСТР или ПСТРБ.

Но может быть и двухбайтовый язык из перечня выше. В таком случае есть разница, какую функцию использовать. 

Один из возможных вариантов, как можно использовать функцию ПСТР, является разделение текста на несколько ячеек по колонкам. Предположим, у нас есть такая таблица, в которой содержатся даты, разделенные по колонкам дата, день, месяц, год. Перед нами стоит задача автоматически, средствами Excel, элементы занести в соответствующие столбцы. 

Сама таблица выглядит следующим образом:

В этом случае нужно применить формулу массива, а именно такую.

Какие аргументы использовались в этом случае?

1. А2:А10. Здесь перечисляется набор ячеек, представление дат в которых выполнено в текстовой форме. Из них и будет доставаться день. 
2. 1 —  это число, обозначающее первоначальную позицию, с которой будет осуществляться извлечение. 
3. 2 – это последняя позиция.

Как видим, разница между первым и вторым числом не очень большая. Все потому, что нам достаточно извлекать символы, находящиеся по соседству. Поэтому с первого начинаем и вторым заканчиваем.

Точно таким же образом осуществляется выделение номеров месяца и лет, чтобы записать их в соответствующих колонках. Нужно при этом учитывать, что месяц в датах, представленных в таком формате, необходимо записывать, начиная четвертым символом, а год – с 7-го. 

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

Соответственно, для нахождения месяца и года и добавления их в соответствующие колонки, необходимо записать такие формулы в первые их ячейки:

=ПСТР(A2:A10;4;2)

=ПСТР(A2:A10;7;4)

В результате, у нас получается следующая таблица. 

Как видим, у нас так получилось разбить текстовую строку на несколько частей и записать соответствующие значения в ячейках. Но это возможно лишь при установлении текстового формата. Аналогичные операции можно провернуть со временем и любым другим форматом при условии предварительного конвертирования в формат строки. 

Таким образом, возможностей у функции ПСТР огромное количество. Ее можно использовать для обработки огромных массивов информации. А поскольку каждый год количество данных, которые нужно эффективно анализировать, постоянно увеличивается, необходимо искать качественные способы автоматизации. И хотя некоторые считают, что Эксель несколько устарел, в своей нише альтернатив этой программе нет. Хотя бы потому, что зная несколько формул, можно добиваться почти любого функционала электронных таблиц. Больших успехов вам и легкости в освоении этого ремесла.