Excel функция пстр

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

Доброго времени суток друзья!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция ПСТР

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  В эксель в формуле закрепить ячейку

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Особенности использования функции ПСТР в Excel

Функция имеет следующую синтаксическую запись:

  • текст – обязательный для заполнения аргумент, принимающий ссылку на ячейку с текстом или текстовую строку, заключенную в кавычки, из которой будет извлечена подстрока определенной длины начиная с указанной позиции первого символа;
  • начальная_позиция – обязательный аргумент, принимающий целые числа из диапазона от 1 до N, где N – длина строки, из которой требуется извлечь подстроку заданного размера. Начальная позиция символа в строке соответствует числу 1. Если данный аргумент принимает дробное число из диапазона допустимых значений, дробная часть будет усечена;
  • число_знаков – обязательный аргумент, принимающий значение из диапазона неотрицательных чисел, которое характеризует длину в символах возвращаемой подстроки. Если в качестве этого аргумента передано число 0 (нуль), функция ПСТР вернет пустую строку. Если аргумент задан числом, превышающим количество символов в строке, будет возвращена вся часть строки начиная с указанной вторым аргументом позиции. В дробных числах, используемых в качестве данного аргумента, дробная часть усекается.

Функция ПСТРБ имеет схожий синтаксис:

Она отличается единственным аргументом:

  • число_байтов – обязательный аргумент, принимающий целые числа из диапазона от 1 до N, где N – число байтов в исходной строке, характеризующий количество байт в возвращаемой подстроке.
  1. Функция ПСТР вернет пустую строку, если в качестве аргумента начальная_позиция было передано число, превышающее количество символов в исходной строке.
  2. Если в качестве аргумента начальная_позиция было передано значение 1, а аргумент число_знаков определен числом, которое равно или больше общему числу знаков в исходной строке, функция ПСТР вернет всю строку целиком.
  3. Если аргумент начальная_позиция был указан числом из диапазона отрицательных чисел или 0 (нулем), функция ПСТР вернет код ошибки #ЗНАЧ!.
  4. Если аргумент число_знаков задан отрицательным числом, результатом выполнения функции ПСТР будет код ошибки #ЗНАЧ!.
Читать еще:  Как в excel скрыть формулы в ячейках

ПСТР, ПСТРБ (функции ПСТР, ПСТРБ)

В этой статье описаны синтаксис формулы и использование функций ПСТР и ПСТРБ в Microsoft Excel.

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

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

Эти функции могут быть доступны не на всех языках.

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

Функция ПСТР всегда считает каждый символ (одно- или двухбайтовый) за один вне зависимости от языка по умолчанию.

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

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Аргументы функций ПСТР и ПСТРБ описаны ниже.

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

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

Число_знаков Обязательный. Указывает, сколько знаков должна вернуть функция ПСТР.

Число_байтов Обязательный. Указывает, сколько знаков должна вернуть функция ПСТРБ (в пересчете на байты).

Если значение «начальная_позиция» больше, чем длина текста, то функция ПСТР возвращает строку «» (пустую строку).

Если значение «начальная_позиция» меньше, чем длина текста, но сумма значений «начальная_позиция» и «число_знаков» превышают длину текста, функция ПСТР возвращает знаки вплоть до конца текста.

Если значение «начальная_позиция» меньше 1, функция ПСТР возвращает значение ошибки #ЗНАЧ!.

Если значение «число_знаков» отрицательно, функция ПСТР возвращает значение ошибки #ЗНАЧ!.

Если значение «число_байтов» отрицательно, функция ПСТРБ возвращает значение ошибки #ЗНАЧ!.

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Функция ПСТР в Excel с примером и формулой

В этой статье я бы хотел рассмотреть очень полезную функцию в Excel для работы с текстом – функция ПСТР (в английской версии MID)

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

Синтаксис функции ПСТР выглядит следующим образом

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

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

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

В данном случае очень хорошо можно использовать функцию ПСТР со следующей формулой. В ячейке «C2» пропишем

=ПСТР( A2 ; 13 ; 50 )

A2 — текст, их которого нам необходимо взять определенное количество знаков. В нашем пример это текст «Товар 91823 Naomy из коллекции Naomy»

13 — начальная позиция с которой нам необходимо брать знаки. В этом пример я посчитал самостоятельно количество знаков «Товар 91823 «. Товар 5 знаков + 1 знак пробела + 5 знаков артикула + 1 знак пробела после артикула, итого 12 знаков. Следовательно, нам необходимо брать знаки с 13-й позиции.

50 — число знаков, которое нам необходимо взять, так как я не знаю максимальное количество знаков строки, то на глаз я взял побольше, зная, что нет строк больше, чем 50-ю символами.

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

Если статья помогла вам, будем благодарны, если вы нажмете на социальные кнопки +1 и «Мне нравится» под статьей. Так же вступайте в нашу группу ВКонтакте

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
×
×
Adblock
detector