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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Текстовые операторы и возвращают позицию указанного символа в просматриваемом тексте.

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

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

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

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

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

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

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

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

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

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

  3. Открывается окно . В категории выделяем наименование и жмем на кнопку .
  4. Запускается окно аргументов оператора . Так как мы ищем пробел, то в поле ставим пробел, установив туда курсор и нажав соответствующую клавишу на клавиатуре.

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

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

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

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Источник: starhit
Читайте также  Применение ABC-анализа в Microsoft Excel

Полезные идеи