Cd-m.ru

ПК Журнал СД-М
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция пстр в Excel примеры формулы

Функция пстр в Excel примеры формулы

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

Функция используется зачастую при работе с большим количеством символов и текстами, и имеет следующий внешний вид: =ПСТР(текст;начальная_позиция;количество_знаков).

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

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

Числовой указатель «Количество знаков» определяет непосредственно само количество знаков, которые нужно извлечь. Здесь также учитываются и пробелы, об этом не стоит забывать.


Попробуем на примере, как же работает функция «ПСТР».

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

Функция пстр в Excel примеры формулы

В мастере функций выбираем текстовую категорию и в ней оператор «ПСТР». Далее можно приступить к заполнению аргументов.

Функция пстр в Excel примеры формулы

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

Функция пстр в Excel примеры формулы

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

Функция пстр в Excel примеры формулы

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

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

Функция пстр в Excel примеры формулы

Заполним маркером авто заполнения весь столбец и получаем прекрасную работу – в ячейку были извлечены все названия смартфонов без самого слова «смартфон». Это на порядок удобнее и быстрее, чем вручную вводить каждое название отдельно.

Функция пстр в Excel примеры формулы

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

Для этого выделим всю область с полученными извлеченными названиями, вызовем контекстное меню и в параметрах вставки укажем «Значения».

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

Функция пстр в Excel примеры формулы

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

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

Function PSTR 1 Функция ПСТР в Excel Доброго времени суток друзья!

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

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

Читать еще:  Как восстановить удаленные письма на Mail.ru

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

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

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

Function PSTR 2 Функция ПСТР в Excel

  • текст – это текстовое значение, текст или ссылка на текстовую ячейку, в которой будут производиться манипуляции;
  • начальная позиция – это указание порядкового номера текстового символа, с которого будет производиться возврат значения;
  • количество знаков – указывается целое натуральное число, которое соответствует количеству символов и будет возвращено с указанной вами в критерии «начальная позиция».

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

Function PSTR 3 Функция ПСТР в Excel

=ПСТР(A2;11;4) Это конечно самый примитивный пример, но он служит для понимания работы функции ПСТР, а вот для большей эффективности нужно использовать комбинации функций, таких как НАЙТИ, ДЛСТР и т.д.

Рассмотрим еще один пример с применением функции НАЙТИ. Есть, к примеру, название товара в ячейке, с названием торговой марки, а вот нам надо вытянуть эту торговую марку для дальнейшей группировки значений. Название «Клавиатура Logitech» и формула для изъятия названия торговой марки будет следующая:

Function PSTR 4 Функция ПСТР в Excel

=ПСТР(A3;НАЙТИ(“ ”;A3)+1;200) В формуле, функция НАЙТИ ищет, где находится пробел « » и после нахождение передает эти данные функции ПСТР, которая вернет текст от найденного знака. Корректировочное дополнение «+1» позволит начать получать данные со следующего знака от искомого, так как он входит в диапазон подсчёта.

Я надеюсь, что функция ПСТР в Excel стала вам более понятной, и вы будете использовать ее в своей работе. С другими функциями вы можете познакомится в «Справочнике функций». Если статья вам помогла, я буду, благодарен вашему нажатию на социальные кнопочки! В случае если у вас есть замечания или дополнения пишите комментарии!

До встречи в новых статьях!

«Нет ничего более деморализующего, чем маленький, но постоянный заработок.
»
Э. Уилсон

Текстовые функции Excel

ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, самые полезные и интересные:

  1. ЛЕВСИМВ;
  2. ПРАВСИМВ;
  3. ДЛСТР;
  4. НАЙТИ;
  5. ЗАМЕНИТЬ;
  6. ПОДСТАВИТЬ;
  7. ПСТР;
  8. СЖПРОБЕЛЫ;
  9. СЦЕПИТЬ.

Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

Список текстовых функций Excel

Функция ЛЕВСИМВ

Возвращает подстроку из текста в порядке слева направо в заданном количестве символов.

Синтаксис: =ЛЕВСИМВ(текст; [количество_знаков])

  • текст – строка либо ссылка на ячейку, содержащую текст, из которого необходимо вернуть подстроку;
  • количество_знаков – необязательный аргумент. Целое число, указывающее, какое количество символов необходимо вернуть из текста. По умолчанию принимает значение 1.

Пример использования:

Формула: =ЛЕВСИМВ(«Произвольный текст»;8) – возвращенное значение «Произвол».

Функция ПРАВСИМВ

Данная функция аналогична функции «ЛЕВСИМВ», за исключением того, что знаки возвращаются с конца строки.

Пример использования:

Формула: =ПРАВСИМВ(«произвольный текст»;5) – возвращенное значение «текст».

Функция ДЛСТР

С ее помощью определяется длина строки. В качестве результата возвращается целое число, указывающее количество символов текста.

Синтаксис: =ДЛСТР(текст)

Пример использования:

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

Функция НАЙТИ

Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; [нач_позиция])

  • искомый_текст – строка, которую необходимо найти;
  • текст_для_поиска – текст, в котором осуществляется поиск первого аргумента;
  • нач_позиция – необязательный элемент. Принимает целое число, которое указывает, с какого символа текст_для_поиска необходимо начинать просмотр. По умолчанию принимает значение 1.
Читать еще:  Как восстановить удалённую переписку в WhatsApp

Пример использования:

Из отрывка стихотворения великого поэта С.А.Есенина находим вхождение первого символа строки «птица». Поиск осуществляется с начала строки. Если в приведенном примере поиск осуществлялся бы с 40 символа, то функция в результате вернула ошибку, т.к. позиции вхождения не было найдено.

Пример функции НАЙТИ

Функция ЗАМЕНИТЬ

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

Синтаксис: ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

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

Пример использования:

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

Пример функции ЗАМЕНИТЬ

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

  • Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
  • В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(«старый»;A1);ДЛСТР(«старый»);»новый»)

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

Пример вложения текстовых функций

Функция ПОДСТАВИТЬ

Данная функция заменяет в тексте вхождения указанной подстроки на новый текст, чем схожа с функцией «ЗАМЕНИТЬ», но между ними имеется принципиальное отличие. Если функция «ЗАМЕНИТЬ» меняет текст, указанный посимвольно вручную, то функция «ПОДСТАВИТЬ» автоматически находит вхождения указанной строки и меняет их.

Синтаксис: ПОДСТАВИТЬ(текст; старый_текст; новый_текст; [номер_вхождения])

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

Пример использования:

Строка в ячейке A1 содержит текст, в котором имеются 2 подстроки «старый». Нам необходимо подставить на место первого вхождения строку «новый». В результате часть текста «…старый-старый…», заменяется на «…новый-старый…».

Пример функции ПОДСТАВИТЬ

Если ли бы последний аргумент был опущен, то результатом бы стала строка «строка, содержащая новый-новый текст».

Функция ПСТР

ПСТР возвращает из указанной строки часть текста в заданном количестве символов, начиная с указанного символа.

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

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

Пример использования:

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

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

Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

Функция СЖПРОБЕЛЫ

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

Синтаксис: =СЖПРОБЕЛЫ(текст)

Пример использования:

=СЖПРОБЕЛЫ( » Текст с лишними пробелами между словами и по краям « )

Результатом выполнения функции будет строка: «Текст с лишними пробелами между словами и по краям» .

Читать еще:  Прошивка Lenovo IdeaPhone A328

Функция СЦЕПИТЬ

С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 255.

Синтаксис: =СЦЕПИТЬ(текст1; [текст2]; …)

Функция должна содержать не менее одного аргумента

Пример использования:

Функция возвратит строку: «Слово1 Слово2».

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

Вместо использования данной функции можно применять знак амперсанда «&». Он так же объединяет строки. Например: «=»Слово1″&» «&«Слово2″».

Пример функция ПСТР для разделения текста на части в Excel

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

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

Один символ в языках с однобайтовой кодировкой соответствует 1 байту. При работе с такими языками результаты функций ПСТР и ПСТРБ (возвращает подстроку из строки на основе количества заданных байт) не отличаются. Если на компьютере используется двухбайтовый язык, каждый символ при использовании ПСТРБ будет считаться за два. Двухбайтовыми языками являются корейский, японский и китайский.

Как разделить текст на несколько ячеек по столбцам в Excel?

Пример 1. В столбце таблицы содержатся даты, записанные в виде текстовых строк. Записать отдельно в соседних столбцах номер дня, месяца и год, выделенные из представленных дат.

Вид исходной таблицы данных:

Пример 1.

Для заполнения номера дня используем следующую формулу (использовать в качестве формулы массива):

ПСТР.

  • A2:A10 – диапазон ячеек с текстовым представлением дат, из которых будут выделены номера дней;
  • 1 – номер начальной позиции символа извлекаемой подстроки (первый символ в исходной строке);
  • 2 – номер последней позиции символа извлекаемой подстроки.

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

Вид заполненной таблицы данных:

разделить текст на несколько ячеек.

Таким образом нам удалось разрезать на части текст в ячейках столбца A. Удалось отдельно каждую дату разделить на несколько ячеек по столбцам: день, месяц и год.

Как вырезать часть текста ячейки в Excel?

Пример 2. В столбце таблицы хранятся текстовые записи с наименованием и маркой товаров. Разделить имеющиеся строки на подстроки с наименованием и маркой соответственно и записать полученные значения в соответствующие столбцы таблицы.

Вид таблицы данных:

Пример 2.

Для заполнения столбца «Наименование» используем следующую формулу:

Функция НАЙТИ возвращает номер позиции символа пробела « » в просматриваемой строке, который принимается в качестве аргумента число_знаков функции ПСТР. В результате расчетов получим:

НАЙТИ.

Для заполнения столбца «Марка» используем следующую формулу массива:

Функция НАЙТИ возвращает позицию символа пробела. К полученному числу прибавляется единица для нахождения позиции первого символа названия марки продукта. Итоговое значение используется в качестве аргумента начальная_позиция функции ПСТР. Для упрощения, вместо поиска номера последней позиции (например, с помощью функции ДЛСТР) указано число 100, которое в данном примере гарантированно превышает количество знаков в изначальной строке.

В результате расчетов получим:

вырезать часть текста ячейки.

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

Пример 3. В таблице содержатся данные о сотрудниках в столбцах ФИО и дата рождения. Создать столбец, в котором будет отображаться фамилия сотрудника и его возраст в формате «Иванов – 27».

Вид исходной таблицы:

Пример 3.

Для возврата строки с фамилией и текущим возрастом используем следующую формулу:

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

Для соединения (конкатенации) полученных строк используются символы «&». В результате вычислений получим:

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector