В некоторых случаях перед пользователем стоит задача вернуть в целевую ячейку из другой ячейки определенное количество символов, начиная с указанного по счету знака слева. С этой задачей прекрасно справляется функция . Ещё больше увеличивается её функционал, если в сочетание с ней применять другие операторы, например или . Давайте подробнее разберем, в чем заключаются возможности функции и посмотрим, как она работает на конкретных примерах.
Использование ПСТР
Основная задача оператора заключается в извлечении из указанного элемента листа определенного числа печатных знаков, включая пробелы, начиная с указанного по счету слева символа. Данная функция относится к категории текстовых операторов. Её синтаксис принимает следующий вид:
=ПСТР(текст;начальная_позиция;количество_знаков)
Как видим, данная формула состоит из трех аргументов. Все они являются обязательными.
Аргумент содержит адрес того элемента листа, в котором находится текстовое выражение с извлекаемыми знаками.
Аргумент представлен в виде числа, которое указывает, с какого знака по счету, начиная слева, нужно производить извлечение. Первый знак считается за , второй за и т.д. В подсчете учитываются даже пробелы.
Аргумент содержит в себе числовой указатель количества символов, начиная от начальной позиции, которые нужно извлечь в целевую ячейку. При подсчете так же, как и у предыдущего аргумента, учитываются пробелы.
Пример 1: единичное извлечение
Описывать примеры применения функции начнем с самого простого случая, когда нужно извлечь единичное выражение. Конечно, подобные варианты на практике применяются крайне редко, поэтому мы приводим данный пример только в качестве ознакомления с принципами работы указанного оператора.
Итак, у нас имеется таблица работников предприятия. В первой колонке указаны имена, фамилии и отчества сотрудников. Нам нужно с помощью оператора извлечь только фамилию первого лица из списка Петра Ивановича Николаева в указанную ячейку.
- Выделяем элемент листа, в который будет производиться извлечение. Щелкаем по кнопке , которая расположена около строки формул.
- Запускается окошко . Переходим в категорию . Выделяем там наименование и щелкаем по кнопке .
- Производится запуск окна аргументов оператора . Как видим, в этом окне число полей соответствует количеству аргументов данной функции.
В поле вводим координаты ячейки, которая содержит ФИО работников. Чтобы не вбивать адрес вручную, просто устанавливаем курсор в поле и кликаем левой кнопкой мыши по элементу на листе, в котором содержатся нужные нам данные.
В поле нужно указать номер символа, считая слева, с которого начинается фамилия работника. При подсчете учитываем также пробелы. Буква , с которой начинается фамилия сотрудника Николаева, является пятнадцатым по счету символом. Поэтому в поле ставим число .
В поле нужно указать количество символов, из которых состоит фамилия. Она состоит из восьми знаков. Но учитывая, что после фамилии в ячейке нет больше символов, мы можем указать и большее количество знаков. То есть, в нашем случае можно поставить любое число, которое равно или больше восьми. Ставим, например, число . Но если бы после фамилии в ячейке были бы ещё слова, цифры или другие символы, то нам бы пришлось устанавливать только точное число знаков ().
После того, как все данные введены, жмем на кнопку .
- Как видим, после этого действия фамилия работника была выведена в указанную нами в первом шаге ячейку.
Пример 2: групповое извлечение
Но, естественно, в практических целях легче вручную вбивать одиночную фамилию, чем применять для этого формулу. А вот для перенесения группы данных использование функции будет вполне целесообразным.
Имеем список смартфонов. Перед наименованием каждой модели стоит слово . Нам нужно вынести в отдельный столбец только названия моделей без этого слова.
- Выделяем первый пустой элемент столбца, в который будет выводиться результат, и вызываем окно аргументов оператора тем же способом, что и в предыдущем примере.
В поле указываем адрес первого элемента колонки с исходными данными.
В поле нам нужно указать номер символа, начиная с которого будут извлекаться данные. В нашем случае в каждой ячейке перед наименованием модели стоит слово и пробел. Таким образом, та фраза, которую нужно вывести в отдельную ячейку везде начинается с десятого символа. Устанавливаем число в данное поле.
В поле нужно установить то число символов, которое содержит выводимое словосочетание. Как видим, в наименовании каждой модели разное число символов. Но спасает ситуацию тот факт, что после названия модели, текст в ячейках заканчивается. Поэтому мы можем установить в данное поле любое число, которое равно или больше количеству символов в самом длинном наименовании в данном списке. Устанавливаем произвольное количество знаков . Название ни одного из перечисленных смартфонов не превышает символов, поэтому указанный вариант нам подходит.
После того, как данные введены, жмем на кнопку .
- После этого наименование первой модели смартфона выводится в заранее указанную ячейку таблицы.
- Для того, чтобы не вводить в каждую ячейку столбца формулу отдельно, производим её копирование посредством маркера заполнения. Для этого ставим курсор в нижний правый угол ячейки с формулой. Курсор преобразуется в маркер заполнения в виде небольшого крестика. Зажимаем левую кнопку мышки и тянем его до самого конца столбца.
- Как видим, вся колонка после этого будет заполнена нужными нам данными. Секрет заключается в том, что аргумент представляет собой относительную ссылку и по мере изменения положения целевых ячеек тоже изменяется.
- Но проблема заключается в том, что если мы решим вдруг изменить или удалить столбец с первоначальными данными, то данные в целевом столбце станут отображаться некорректно, так как они связаны друг с другом формулой.
Чтобы «отвязать» результат от первоначальной колонки, производим следующие манипуляции. Выделяем столбец, который содержит формулу. Далее переходим во вкладку и жмем на пиктограмму , расположенную в блоке на ленте.
Как альтернативное действие, можно после выделения нажать комбинацию клавиш .
- Далее, не снимая выделения, щелкаем по колонке правой кнопкой мыши. Открывается контекстное меню. В блоке щелкаем по пиктограмме .
- После этого вместо формул в выделенный столбец будут вставлены значения. Теперь вы можете без опаски изменять или удалять исходную колонку. На результат это уже никак не повлияет.
Пример 3: использование комбинации операторов
Но все-таки указанный выше пример ограничен тем, что первое слово во всех исходных ячеек должно иметь равное количество символов. Применение вместе с функцией операторов или позволит значительно расширить возможности использования формулы.
Текстовые операторы и возвращают позицию указанного символа в просматриваемом тексте.
Синтаксис функции следующий:
=ПОИСК(искомый_текст;текст_для_поиска;начальная_позиция)
Синтаксис оператора выглядит таким образом:
=НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)
По большому счету аргументы этих двух функций тождественны. Их главное отличие состоит в том, что оператор при обработке данных не учитывает регистр букв, а – учитывает.
Посмотрим, как использовать оператор в сочетании с функцией . Имеем таблицу, в которую занесены наименования различных моделей компьютерной техники с обобщающим названием. Как и в прошлый раз, нам нужно извлечь наименование моделей без обобщающего названия. Трудность состоит в том, что если в предыдущем примере обобщающее наименование для всех позиций было одно и то же («смартфон»), то в настоящем списке оно разное («компьютер», «монитор», «колонки» и т.д.) с различным числом символов. Чтобы решить данную проблему нам и понадобится оператор , который мы вложим в функцию .
- Производим выделения первой ячейки столбца, куда будут выводиться данные, и уже привычным способом вызываем окно аргументов функции .
В поле , как обычно, указываем первую ячейку столбца с исходными данными. Тут все без изменений.
- А вот значение поля будет задавать аргумент, который формирует функция . Как видим, все данные в списке объединяет тот факт, что перед названием модели стоит пробел. Поэтому оператор будет искать первый пробел в ячейке исходного диапазона и сообщать номер этого символа функции .
Для того, чтобы открыть окно аргументов оператора , устанавливаем курсор в поле . Далее кликаем по пиктограмме в виде треугольника, направленного углом вниз. Данная пиктограмма расположена на том же горизонтальном уровне окна, где находится кнопка и строка формул, но слева от них. Открывается список последних применяемых операторов. Так как среди них нет наименования , то кликаем по пункту .
- Открывается окно . В категории выделяем наименование и жмем на кнопку .
- Запускается окно аргументов оператора . Так как мы ищем пробел, то в поле ставим пробел, установив туда курсор и нажав соответствующую клавишу на клавиатуре.
В поле указываем ссылку на первую ячейку колонки с исходными данными. Эта ссылка будет тождественна той, которую мы ранее указали в поле в окне аргументов оператора .
Аргумент поля не обязателен к заполнению. В нашем случае его заполнять не нужно либо можно установить число . При любом из этих вариантов поиск будет осуществляться с начала текста.
После того, как данные введены, не спешим жать на кнопку , так как функция является вложенной. Просто кликаем по наименованию в строке формул.
- После выполнения последнего указанного действия мы автоматически возвращаемся к окну аргументов оператора . Как видим, поле уже заполнено формулой . Но данная формула указывает на пробел, а нам нужен следующий символ после пробела, с которого и начинается наименование модели. Поэтому к существующим данным в поле дописываем выражение без кавычек.
В поле , как и в предыдущем примере, записываем любое число, которое больше или равно количеству символов в самом длинном выражении исходного столбца. Например, ставим число . В нашем случае этого вполне достаточно.
После выполнения всех указанных манипуляций жмем на кнопку в нижней части окна.
- Как видим, после этого наименование модели устройства было выведено в отдельную ячейку.
- Теперь при помощи Мастера заполнения, как и в предыдущем способе, копируем формулу на ячейки, которые расположены ниже в данном столбце.
- Наименования всех моделей устройств выведены в целевые ячейки. Теперь, в случае необходимости, можно оборвать связь в этих элементах со столбцом исходных данных, как и в предыдущий раз, применив последовательно копирование и вставку значений. Впрочем, указанное действие не всегда является обязательным.
Функция используется в сочетании с формулой по тому же принципу, что и оператор .
Как видим, функция является очень удобным инструментом для вывода нужных данных в заранее указанную ячейку. То, что она не так сильно популярна среди пользователей, объясняется тем фактом, что многие юзеры, используя Excel, большее внимание уделяют математическим функциям, а не текстовым. При использовании данной формулы в сочетании с другими операторами функциональность её ещё больше увеличивается.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.