Применение функции ПСТР в Microsoft Excel

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

Использование ПСТР

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

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

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

Аргумент «Текст» содержит адрес того элемента листа, в котором находится текстовое выражение с извлекаемыми знаками.

Аргумент «Начальная позиция» представлен в виде числа, которое указывает, с какого знака по счету, начиная слева, нужно производить извлечение. Первый знак считается за «1», второй за «2» и т.д. В подсчете учитываются даже пробелы.

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

Пример 1: единичное извлечение

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

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

1. Выделяем элемент листа, в который будет производиться извлечение. Щелкаем по кнопке «Вставить функцию», которая расположена около строки формул.

2. Запускается окошко Мастера функций. Переходим в категорию «Текстовые». Выделяем там наименование «ПСТР» и щелкаем по кнопке «OK».

3. Производится запуск окна аргументов оператора «ПСТР». Как видим, в этом окне число полей соответствует количеству аргументов данной функции.

   В поле «Текст» вводим координаты ячейки, которая содержит ФИО работников. Чтобы не вбивать адрес вручную, просто устанавливаем курсор в поле и кликаем левой кнопкой мыши по элементу на листе, в котором содержатся нужные нам данные.

   В поле «Начальная позиция» нужно указать номер символа, считая слева, с которого начинается фамилия работника. При подсчете учитываем также пробелы. Буква «Н», с которой начинается фамилия сотрудника Николаева, является пятнадцатым по счету символом. Поэтому в поле ставим число «15».

   В поле «Количество знаков» нужно указать количество символов, из которых состоит фамилия. Она состоит из восьми знаков. Но учитывая, что после фамилии в ячейке нет больше символов, мы можем указать и большее количество знаков. То есть, в нашем случае можно поставить любое число, которое равно или больше восьми. Ставим, например, число «10». Но если бы после фамилии в ячейке были бы ещё слова, цифры или другие символы, то нам бы пришлось устанавливать только точное число знаков («8»).

   После того, как все данные введены, жмем на кнопку «OK».


4. Как видим, после этого действия фамилия работника была выведена в указанную нами в первом шаге Примера 1 ячейку.

Урок: Мастер функций в Эксель

Пример 2: групповое извлечение

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

Имеем список смартфонов. Перед наименованием каждой модели стоит слово «Смартфон». Нам нужно вынести в отдельный столбец только названия моделей без этого слова.

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

   В поле «Текст» указываем адрес первого элемента колонки с исходными данными.

   В поле «Начальная позиция» нам нужно указать номер символа, начиная с которого будут извлекаться данные. В нашем случае в каждой ячейке перед наименованием модели стоит слово «Смартфон» и пробел. Таким образом, та фраза, которую нужно вывести в отдельную ячейку везде начинается с десятого символа. Устанавливаем число «10» в данное поле.

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

   После того, как данные введены, жмем на кнопку «OK».


2. После этого наименование первой модели смартфона выводится в заранее указанную ячейку таблицы.

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

4. Как видим, вся колонка после этого будет заполнена нужными нам данными. Секрет заключается в том, что аргумент «Текст» представляет собой относительную ссылку и по мере изменения положения целевых ячеек тоже изменяется.

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

   Чтобы «отвязать» результат от первоначальной колонки, производим следующие манипуляции. Выделяем столбец, который содержит формулу. Далее переходим во вкладку «Главная» и жмем на пиктограмму «Копировать», расположенную в блоке «Буфер обмена» на ленте.

   

   Как альтернативное действие, можно после выделения нажать комбинацию клавиш Ctrl+C.

6. Далее, не снимая выделения, щелкаем по колонке правой кнопкой мыши. Открывается контекстное меню. В блоке «Параметры вставки» щелкаем по пиктограмме «Значения».

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

Пример 3: использование комбинации операторов

Но все-таки указанный выше пример ограничен тем, что первое слово во всех исходных ячеек должно иметь равное количество символов. Применение вместе с функцией ПСТР операторов ПОИСК или НАЙТИ позволит значительно расширить возможности использования формулы.

Текстовые операторы ПОИСК и НАЙТИ возвращают позицию указанного символа в просматриваемом тексте.

Синтаксис функции ПОИСК следующий:

=ПОИСК(искомый_текст;текст_для_поиска;начальная_позиция)

Синтаксис оператора НАЙТИ выглядит таким образом:

=НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)

По большому счету аргументы этих двух функций тождественны. Их главное отличие состоит в том, что оператор ПОИСК при обработке данных не учитывает регистр букв, а НАЙТИ – учитывает.

Посмотрим, как использовать оператор ПОИСК в сочетании с функцией ПСТР. Имеем таблицу, в которую занесены наименования различных моделей компьютерной техники с обобщающим названием. Как и в прошлый раз, нам нужно извлечь наименование моделей без обобщающего названия. Трудность состоит в том, что если в предыдущем примере обобщающее наименование для всех позиций было одно и то же («смартфон»), то в настоящем списке оно разное («компьютер», «монитор», «колонки» и т.д.) с различным числом символов. Чтобы решить данную проблему нам и понадобится оператор ПОИСК, который мы вложим в функцию ПСТР.

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

   В поле «Текст», как обычно, указываем первую ячейку столбца с исходными данными. Тут все без изменений.


2. А вот значение поля «Начальная позиция» будет задавать аргумент, который формирует функция ПОИСК. Как видим, все данные в списке объединяет тот факт, что перед названием модели стоит пробел. Поэтому оператор ПОИСК будет искать первый пробел в ячейке исходного диапазона и сообщать номер этого символа функции ПСТР.

   Для того, чтобы открыть окно аргументов оператора ПОИСК, устанавливаем курсор в поле «Начальная позиция». Далее кликаем по пиктограмме в виде треугольника, направленного углом вниз. Данная пиктограмма расположена на том же горизонтальном уровне окна, где находится кнопка «Вставить функцию» и строка формул, но слева от них. Открывается список последних применяемых операторов. Так как среди них нет наименования «ПОИСК», то кликаем по пункту «Другие функции…».


3. Открывается окно Мастера функций. В категории «Текстовые» выделяем наименование «ПОИСК» и жмем на кнопку «OK».

4. Запускается окно аргументов оператора ПОИСК. Так как мы ищем пробел, то в поле «Искомый текст» ставим пробел, установив туда курсор и нажав соответствующую клавишу на клавиатуре.

   В поле «Текст для поиска» указываем ссылку на первую ячейку колонки с исходными данными. Эта ссылка будет тождественна той, которую мы ранее указали в поле «Текст» в окне аргументов оператора ПСТР.

   Аргумент поля «Начальная позиция» не обязателен к заполнению. В нашем случае его заполнять не нужно либо можно установить число «1». При любом из этих вариантов поиск будет осуществляться с начала текста.

   После того, как данные введены, не спешим жать на кнопку «OK», так как функция ПОИСК является вложенной. Просто кликаем по наименованию ПСТР в строке формул.


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

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

   После выполнения всех указанных манипуляций жмем на кнопку «OK» в нижней части окна.


6. Как видим, после этого наименование модели устройства было выведено в отдельную ячейку.

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

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

Функция НАЙТИ используется в сочетании с формулой ПСТР по тому же принципу, что и оператор ПОИСК.

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

Наш Telegram каналТолько полезная информация