Застосування функції ПСТР в 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, більшу увагу приділяють математичних функцій, а не текстовим. При використанні цієї формули в поєднанні з іншими операторами функціональність її ще більше збільшується.

• ← Обчислення аннуитетного платежу в Microsoft Excel
• Роздруківка документа в Microsoft Excel →